本文详细介绍了MySQL数据库的创建、修改和删除，包括数据类型、字段属性、外键约束、DML操作（增删改）、DQL查询，以及索引、权限管理和备份等方面。重点讲解了如何创建和管理数据库表，以及如何使用SQL语句进行数据查询和操作，同时探讨了数据库设计的规范和最佳实践。
一、数据库简介
0、数据类型
1、数据库的字段属性
PS int类型的限制宽度只是显示宽度，和插入的值的位数无关，影响零填充
2、创建数据库表
-- 注意点 使用英文括号 表的名称和字段尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用单引号
-- 所有的语句后面加, 除了最后一个
-- PRIMARY KEY 主键， 一般一个表只有一个
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
常用命令
-- 查看创建数据库的语句
SHOW CREATE DATABASE `school`;
-- 查看创建表的语句
SHOW CREATE TABLE `student`
-- 显示表的结构
DESC student
3、数据库类型
1️⃣在物理空间存在的位置
2️⃣设置数据库表的字符集编码
4、修改删除表字段
-- 修改表
ALTER TABLE student RENAME AS cty
-- 添加表字段
ALTER TABLE cty ADD age INT(11)
-- 修改表的字段, 修改约束
ALTER TABLE cty MODIFY age VARCHAR(11)
-- 修改表的名字
ALTER TABLE cty CHANGE age ccc INT(3)
-- 删除表
DROP TABLE IF EXISTS cty
-- 所有的操作尽量加上判断以免报错
二、MySQL数据管理
1、外键
1️⃣ 在创建表时增加约束 比较麻烦
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束 （执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
2️⃣ 创建表成功和添加外键约束
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束 （执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
-- ALTER TABLE 表
-- ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 表(字段)
以上操作都是物理外键，数据库级别的外键， 不建议使用
2、DML语言
3、添加
-- 插入语句（添加）
-- insert into 表名([字段1, 字段2, 字段3]) values ('值1', '值2', '值3', '值4')
INSERT INTO grade(`gradename`) VALUES('大四')
-- 由于主键自增我们可以省略(如果不写表的字段，他就会一一匹配)
INSERT INTO grade VALUES('大三') -- 报错
-- 一般写插入语句，我们一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大二'), ('大一')
INSERT INTO `student` (`name`) VALUES ('张三')
INSERT INTO `student` (`name`, `pwd`, `sex`) VALUES ('张三', 'aaaaaa', '男')
INSERT INTO `student` (`name`, `pwd`, `sex`)
VALUES ('李四', 'aaaaaa', '男'), ('王五', 'aaaaaa', '男')
4、修改
-- 修改学员的名字
UPDATE `student` SET `name` = 'cty' WHERE id = 1;
-- 不指定条件的情况下，会改动所有的表
UPDATE `student` SET `name` = '全改了';
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name` = 'cty', `email` = '1734@qq.com' WHERE id = 1;
-- 语法:
-- UPDATE 表名 SET colnum_name = value where [条件];
5、删除
-- 删除数据
DELETE FROM `student` WHERE id = 1
-- 清空student表
TRUNCATE `student`
三、DQL查询数据
1、DQL简介
1️⃣ select完整语法
2、查询指定字段
1️⃣ 建表
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-- 插入成绩数据 这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
2️⃣ 查询指定字段
-- 查询全部的学生
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`, `studentname` FROM student
-- 别名， 给结果起一个名字 AS 可以给字段起别名， 也可以给表起别名
SELECT `studentno` AS 学号, `studentname` AS 学生姓名 FROM student AS s
-- 函数 Concat(a, b)
SELECT CONCAT('姓名：', studentname) AS 新名字 FROM student
①去重 distinct
-- 查询一下哪些同学有成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `studentno` FROM result -- 查询有哪些同学参加了考试
-- 发现重复数据，去重
SELECT DISTINCT `studentno` FROM result
作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条
②特殊
SELECT VERSION() -- 查询系统版本 (函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算 (表达式)
SELECT @@auto_increment_increment -- 查询自增的步长 (变量)
-- 学员成绩 + 1分查看
SELECT `studentno`, `studentresult` + 1 AS '提分后' FROM result
3️⃣ where 条件子句
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ey28m9ZS-1626063619816)(C:\Users\caoyb\AppData\Roaming\Typora\typora-user-images\image-20210709122156016.png)]
SELECT `studentno`, `studentresult` FROM result
-- 查询考试成绩在 95 - 100 分之间
SELECT `studentno`, `studentresult` FROM result
WHERE `studentresult` >= 95 AND `studentresult` <= 100
-- 模糊查询
SELECT `studentno`, `studentresult` FROM result
WHERE `studentresult` BETWEEN 80 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT `studentno`, `studentresult` FROM result
WHERE NOT studentno = 1000
①模糊查询：比较运算符
-- 模糊查询
-- like结合 %(代表0到任意个字符) _（一个字符）
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '王%'
-- 查询姓王的同学，名字后面只有一个字的
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '王_'
-- 查询姓王的同学，名字后面有两个字的
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '王__'
-- 查询有张字的同学
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '%张%'
-- 查询 1001, 1002, 1003 号学员
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentno` IN (1001, 1002, 1003)
-- in 是一个具体的值
-- 查询在北京的学生
SELECT `studentno`, `studentname` FROM `student`
WHERE `address` IN ('北京朝阳', '河南洛阳');
-- 查询地址为空的学生
SELECT `studentno`, `studentname` FROM `student`
WHERE `address` = '' OR `address` IS NULL;
-- 查询有出生日期的同学
SELECT `studentno`, `studentname` FROM `student`
WHERE `borndate` IS NOT NULL
4️⃣ 联表查询
①七种join理论
-- 联表查询
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result
/* 思路
1. 分析需求， 分析查询的字段来自哪些表
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件 ： 学生表中的 studentno = 成绩表 studentno
*/
-- inner join
SELECT s.studentno, studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno
-- right join
SELECT s.studentno, studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
-- left join
SELECT s.studentno, studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
-- join on 连接查询
-- where 等值查询
-- 查询缺考的同学
SELECT s.studentno, studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
-- 查询参加了考试的同学信息： 学号，学生姓名。科目名，分数
/* 思路
1. 分析需求， 分析查询的字段来自哪些表 student, result, subject 连接查询
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件 ： 学生表中的 studentno = 成绩表 studentno
*/
SELECT s.`studentno`, `studentname`, `subjectname`, `studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.subjectno
5️⃣ 自连接
自己的表和自己的表连接。 核心：一张表拆为两张一样的表即可
① 建表
CREATE TABLE `school`.`category`(
`categoryid` INT(3) NOT NULL COMMENT 'id',
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
INSERT INTO `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库');
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发');
INSERT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');
-- 查询父子信息
SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目'
FROM `category` a, `category` b
WHERE a.`categoryid` = b.`pid`
② 练习
-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT studentno, studentname, `gradename`
FROM `student` s
INNER JOIN `grade` g
ON s.gradeid = g.`gradeid`
-- 查询科目所属的年级 (科目名称， 年级名称)
SELECT `subjectname`, `gradename`
FROM `subject` s
INNER JOIN `grade` g
ON s.`gradeid` = g.`gradeid`
6️⃣ 分页和排序 limit and order by
-- 排序： 升序 ASC, 降序 DESC
-- order by 通过哪个字段排序，怎么排
-- 查询的结果根据成绩降序排序
SELECT s.`studentno`, `studentname`, `subjectname`, `studentresult`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY `studentresult` DESC
-- 分页，每页只显示五条数据
-- 语法 limit 开始，大小
LIMIT 0,5
7️⃣子查询
-- 查询高等数学-1 的所有考试结果(学号，科目，成绩)降序排列
-- 使用连接查询
SELECT `studentno`,`subjectname`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY `studentresult` DESC
-- 使用子查询
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` r
WHERE subjectno = (
-- 查询所有高等数学-1的学生的学号
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`, `studentname`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE `studentresult` >= 80
-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2 的编号
SELECT DISTINCT s.`studentno`, `studentname`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE `studentresult` >= 60 AND `subjectno` = (
SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-2'
)
四、MySQL函数
1、常用函数
2、聚合函数
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT subjectname, AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.subjectno -- 通过什么字段分组
HAVING 平均分 > 80
3、数据库级别的MD5加密
CREATE TABLE testmd5 (
id INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
pwd VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 铭文密码
INSERT INTO testmd5 VALUES
(1,'cty1','123456'),
(2,'cty2','123456'),
(3,'cty3','1234562'),
(4,'cty4','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入的时候加密
INSERT INTO testmd5 VALUES (5, 'cty5', MD5('123456'))
-- 如何校验：将用户传递进来的密码进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` LIKE 'cty%' AND pwd=MD5('123456')
五、事务
1、什么是事务
2、ACID原则
3、事务流程
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 // 关闭
SET autocommit = 1 // 开启
-- 手动处理事务
SET autocommit = 0 -- 关闭自动条件
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交： 持久化
COMMIT
-- 回滚：回到原来的样子
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
实例
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 // 关闭
SET autocommit = 1 // 开启
-- 手动处理事务
SET autocommit = 0 -- 关闭自动条件
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交： 持久化
COMMIT
-- 回滚：回到原来的样子
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
六、索引的分类
1、索引的定义
2、索引的分类
-- 索引的使用
-- 1、在创建表的时候增加索引
-- 2、创建完毕后增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentName`(`studentName`);
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('张')
3、测试索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT 0 COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT 0 COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`, `password`)
VALUES(CONCAT('用户',i),'19224305@qq.com',123456789,FLOOR(RAND()*2),'123456');
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据
SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 1.301 秒
-- id _表名 _字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 0.047 秒
索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显
4、索引原则
七、权限管理和备份
1、用户管理
1️⃣ SQLyog可视化操作
2️⃣ sql命令
用户表：mysql.user
本质：对这张表进行增删改查
-- 创建用户 create user 用户名 By '密码'
CREATE USER cty IDENTIFIED BY '123456'
-- 修改密码（修改当前用户的密码）
SET PASSWORD = PASSWORD('123456')
-- 修改密码（修改指定用户的密码）
SET PASSWORD FOR cty = PASSWORD('123456')
-- 重命名 rename user 原来名字 to 新的名字
RENAME USER cty TO ctyking
-- 用户授权 all privileges 全部的权限，on 库.表
-- 除了给别人授权，别的权限都有
GRANT ALL PRIVILEGES ON *.* TO ctyking
-- 查询权限
SHOW GRANTS FOR ctyking
SHOW GRANTS FOR root@localhost
-- root用户权限： GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限， 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM ctyking
-- 删除用户
DROP USER ctyking
2、数据库备份
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M8DoIK5z-1626063619829)(C:\Users\caoyb\AppData\Roaming\Typora\typora-user-images\image-20210711145801758.png)]
八、规范数据库设计
1、为什么需要设计
2、三大范式
扫一扫
2273
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
