-- 創(chuàng)建表
CREATE TABLE `student`(
	`StudentNo` INT(4) NOT NULL COMMENT "學(xué)號(hào)",
	`LoginPwd` VARCHAR(20) DEFAULT NULL,
	`StudentName` VARCHAR(20) DEFAULT NULL COMMENT "學(xué)生姓名",
	`Sex` TINYINT(1) DEFAULT NULL COMMENT "性別：0-1",
	`GradeId` INT(11) DEFAULT NULL COMMENT "年紀(jì)編號(hào)",
	`Phone` VARCHAR(50) NOT NULL COMMENT "聯(lián)系電話",
	`Address` VARCHAR(255) NOT NULL COMMENT "地址",
	`BornDate` DATETIME DEFAULT NULL COMMENT "出生日期",
	`Email` VARCHAR(50) NOT NULL COMMENT "郵箱",
	`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT "身份證號(hào)",
	PRIMARY KEY (`StudentNo`), -- 主鍵索引
	UNIQUE KEY `IdentityCard` (`IdentityCard`), -- 唯一索引，前面是索引名稱，括號(hào)里是字段名
	KEY `Email` (`Email`) -- 常規(guī)索引
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 增加一個(gè)全文索引類型，前面是索引名稱，括號(hào)里是字段名
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName` (`StudentName`);

-- 創(chuàng)建表
CREATE TABLE `app_user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT '昵稱',
	`email` VARCHAR(50) DEFAULT NULL COMMENT "郵箱",
	`phone` VARCHAR(20) DEFAULT NULL COMMENT "手機(jī)號(hào)",
	`gender` TINYINT(4) DEFAULT NULL COMMENT "性別 0-男, 1-女",
	`password` VARCHAR(100) NOT NULL COMMENT "密碼",
	`age` TINYINT(4) NOT NULL COMMENT "年齡",
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='app用戶表';

-- 插入百萬數(shù)據(jù)
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i < num DO
        -- 插入語句
        INSERT INTO `school`.`app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES
        (CONCAT('用戶',i), '123456@qq.com', CONCAT('18', FLOOR(RAND()*((999999999-100000000)+100000000))),
        FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data();

SELECT * FROM `app_user` WHERE `name`='用戶9999'

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用戶9999'

CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM `app_user` WHERE `name`='用戶9999'

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用戶9999'