MySQL 学习笔记（二）
1、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字段
-- 1.定义外键key
-- 2.给这个外键添加约束（执行引用）
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
方式二：
或者也可以直接在UI上添加
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XjnsLXi2-1648043540882)(C:\Users\86180\AppData\Roaming\Typora\typora-user-images\image-20220323083932622.png)]](https://img-blog.csdnimg.cn/f7010ca757234d238c0cd61f140f4b30.png)
方式三：
或者也可以在创建表的时候不添加，创建之后再添加约束
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 那个表（那个字段）
以上的操作都是物理外键，数据库级别的外键，不建议使用
最佳实践
- 数据库就是张单纯的表，只用来存数据，只有行数据和列字段
- 我们想使用多张表的数据，想使用外键（程序实现）
1.2、 DML语言
数据库意义：数据存储和数据管理
DML 语言： 数据操作语言
- Insert
- Update
- Delete
1.3、 添加
-- 插入语句（添加）
-- insert into 表名([字段名1，字段名2，字段名3]) values ('值1'),('值2'),('值3'),(....) 其中值1 代表的是[字段名1，字段名2，字段名3]
INSERT INTO `grade` (`gradename`) VALUES ('大一')
-- 主键自增可以省略 如果不写表的字段，他就会一一匹配
INSERT INTO `grade` VALUES ('大三')
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES ('大三','NULL')
-- 一般写插入语句，一定要数据和字段一一对应
-- 插入多个数据
INSERT INTO `grade` (`gradename`) VALUES ('大四'),('大五')
INSERT INTO `student` (`name`,`pwd`,`sex`,`gradeid`) VALUE ('张三','aaaaaaa','男','2')
1.4、 修改
update 条件 set 原值=修改值
-- 修改学员名字 带条件会修改条件对应 否则会默认将所有记录都改掉
UPDATE `student` SET `name` ='杨杨' WHERE id=1;
UPDATE `student` SET `name` ='杨杨';
-- 语法 update table_name set column_name='value' where condition='value'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='杨',`email`='xx@...' WHERE id=1;
条件：where 子句 运算符 id等于某个值
操作符会返回boolean
| 操作符 | 意义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | id=1 | 仅修改id等于1的行 |
| <> 或!= | 不等于 | id<>1 | 仅不修改id等于1的行 |
| BETWEEN | 区间 | BETWEEN 2 AND 3 | 2,3之间 |
| AND | 与 | id>1 AND id ❤️ | 仅修改2 |
| OR | 或 | id>3 OR id<2 | 修改4以上和1 |
-- 通过多个条件定位数据 AND连接或者OR连接
UPDATE `student` SET `name`='长江七号' WHERE `name`='杨杨' AND `sex`='男'
注意：
- value 可以是一个具体的值，也可以是一个变量 比如CURRENT_TIME
- 多个value之间 逗号隔开
1.5、 删除
delete 命令
语法：delete from table_name [where condition=values]
-- 删除数据
DELETE FROM `student` WHERE id=1;
TRUNCATE 命令
清空数据表
- 相同点：都能删除数据，都不会删除表结构
- 不同
- TRUNCATE 重新设置自增列 计数器会归0
- TRUNCATE 不会影响事务
-- 测试delete和TRUNCATE 区别
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test` (`coll`) VALUES ('1'),('2'),('3')
DELETE FROM `test` -- 不会改变自增
TRUNCATE TABLE `test`
DELETE删除的问题：重启数据库，现象
- INNODB 自增列会从1开始（存在内存当中，断电即失）
- MyISAM 继续从上一个自增量开始 （存在文件当中，不会丢失）
2、 DQL查询数据
data query language 数据查询语言
- 所有的查询操作都用它 Select
- 简单的查询，复杂的查询都实现
- 数据库中最核心的语言
2.1、指定查询字段
-- 查询全部的学生 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名，给结果起一个名字 AS 也可以给字段起别名 也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 姓名 FROM student
-- 函数 Concat(a,b)
SELECT CONCAT('姓名 :' ,`StudentName`) AS 新名字 FROM student
语法: SELECT 字段… FROM 表
有时候，列名字不是那么的明显，我们可以取别名 AS
去重 distinct
-- 查询一下那些同学参加了考试，（从成绩)
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有那些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result -- 发现重复数据
数据库的列表达式
SELECT VERSION() -- 查询系统版本
SELECT 100*3-1 AS 计算结果 -- 用来计算
SELECT @@auto_increment_increment -- 查询自增步长
-- 学员考试成绩+1分查看
SELECT `StudentNo` ,`StudentResult`+1 AS `提分` FROM result
数据库中的表达式： 文本值，列，Null,函数，计算表达式，系统变量…
select 表达式
2.2、where条件子句
作用：检索数据中符合条件的值
搜索的条件：由一个或多个表达式组成
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b (a&&b) | 与，两个都为真，结果为真 |
| or || | a or b(a||b) | 或，其中一个为真，结果为真 |
| not ! | not a (!a) | 非，真为假，假为真 |
尽量使用英文字母
-- 查询考试成绩在95 到100之间的
SELECT studentNo,studentresult FROM result
WHERE StudentResult>=95 AND Studentresult<=100
-- 查询除了1000号学生之外的同学的成绩
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo!=1000;
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a is not null | 如果操作符不为NULL,结果为真 |
| BETWEEN | a between b and c | 若a在b和c之间，则结果为真 |
| Like | a like b | SQL匹配，如果a匹配到b，则结果为真 |
| In | a in(a1,a2,a3…) | 假设a在a1,或者a2 …其中的某一个值中，则结果为真 |
-- 模糊查询
-- 查询姓刘的同学
-- like 接合% (代表0到任意个字符 ） _(一个字符)
SELECT `StudentNo`,`Studentname` FROM `student`
WHERE StudentgName LIKE '刘%'
-- 查询姓张的同学 名字后面只有一个字符
SELECT `StudentNo`,`Studentname` FROM `student`
WHERE StudentName LIKE '张_'
-- 查询姓张的同学 名字后面只有两个字的
SELECT `StudentNo`,`Studentname` FROM `student`
WHERE StudentName LIKE '张__'
-- in 一个具体或者多个的值 没有正则表达式匹配 是完全匹配
-- 查询1001，1002，1003 号学员
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京的学生
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `Address` IN ('北京');
-- null not null
-- 查询地址为NULL的学生
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `Address` ='' OR `Address` IS NULL;
-- 查询有出生日期的同学 不为空
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `BornDate` ='' IS NOT NULL;
2.3、联表查询
-- ============联表查询 join================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result
/* 思路
1. 分析需求，分析查询的字段来自那些表
2. 确定使用那种连接查询 7中
确定交叉点 （这两个表中那个数据是相同的）
判断的条件： 学生表中的 studentNo= 成绩表 studentgNo
*/
-- join on 连接查询
-- where 等值查询
-- 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
-- 查询缺考的同学
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO=r.studentNO
WHERE Studentresult IS NULL
| 操作 | 描述 |
|---|---|
| inner join | 两个表中都要有才会返回 |
| left join | 返回左表中所有的值，即使右表中没有匹配（以左表为基准） |
| right join | 返回右表中所有的值，即使左表中没有匹配（以右表为基准） |
-- 查询参加了考试的同学信息：学号 学生姓名 科目名称 分数
-- 这里需要注意join 和on是固定的 中间不能插入where where最好放在整个的最后面
/* 思路
1. 分析需求，分析查询的字段来自那些表 student/result/subject 三表联查
2. 确定使用那种连接查询 7中
确定交叉点 （这两个表中那个数据是相同的）
判断的条件： 学生表中的 studentNo= 成绩表 studentgNo
*/
SELECT s.studentNO,studentName,SubjectName,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO=r.studentNO
INNER JOIN `subject` AS ss
ON r.subjectNO=ss.subjectNO
-- 考虑需要查那些数据
-- 从那几个表里查 找到基准
自连接
自己和自己连接：一张表拆成两张一样的表
父类：
| categoryid | categoryid |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryid |
|---|---|---|
| 3 | 4 | 数据库 |
| 8 | 2 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | PS技术 |
操作：查询父母对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 美术设计 | PS技术 |
| 软件开发 | web 开发 |
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE b.pid=a.`categoryid`
ORDER BY a.`categoryid`
一些联表查询的练习
-- 查询学员所属的年级 （学号，学生的姓名，年级名称）
SELECT studentNo,studentNAME,`gradename`
FROM student AS s
INNER JOIN `grade` AS g
ON s.`gradeid`= g.`gradeid`
-- 查询科目所属的年级 科目名称 年级名称
SELECT `subjectname`,`gradename`
FROM `subject` AS s
INNER JOIN `grade` AS g
ON s.gradeid=g.gradeid
-- 查询参加了 高等数学考试的同学 信息 学号 学生姓名 科目名 分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN `result` r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
2.4、分页和排序
-- 排序: 升序ASC, 降序DESC
-- 查询的结构根据成绩 降序排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN `result` r
ON s.studentno=r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC -- ASC是升序 DESC是降序
-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好
-- 分页，每页只显示5条数据
-- 网页应用：
-- LIMIT 0,5
-- LIMIT 5,5
-- pagesize: 页面大小
-- (n-1)*pagesize: 起始值
-- n: 当前页
-- 数据总数/页面大小=总页数
-- 语法： limit （查询起始值，pagesize)
2.5、子查询
where (整个值是计算出来的)
本质： 在where语句中嵌套一个子查询语句
where(select *)
注意：子查询的内容必须只有一行，如果存在多行会报错，需要加any修饰
-- ===================where=====
-- 1、查询高等数学-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：通过连接查询
SELECT r.`StudentNo`,`SubjectName`,`StudentResult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE Subjectname='高等数学-1'
ORDER BY studentresult DESC
-- 方式二：适用子查询 由里及外
SELECT r.`StudentNo`,`subjectno`,`StudentResult`
FROM `result` AS r
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-1'
)
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT `StudentNo`,`StudentName`
FROM Student AS s
WHERE studentno=ANY(
SELECT studentno FROM `result`
WHERE studentresult>=80
) -- 这个地方必须要加any 不然会报错
-- 采用联表查询的方式
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM Student AS s
INNER JOIN `result` AS r
ON s.studentno=r.`studentno` AND r.`studentresult`>80
-- 在这个基础上限定科目是高等数学-1
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM Student AS s
INNER JOIN `result` AS r
ON s.studentno=r.`studentno` AND r.`studentresult`>80
WHERE `subjectno`=(
SELECT subjectno FROM `subject`
WHERE `subjectname`='高等数学-1'
)
-- inner join
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM Student AS s
INNER JOIN `result` AS r
ON r.`studentresult`>=80 AND r.`studentno`=s.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectname`='高等数学-1'
-- 嵌套！
SELECT DISTINCT `StudentNo`,`StudentName`
FROM Student
WHERE studentno IN ( -- 这里要注意是in
SELECT studentno FROM `result`
WHERE studentresult>=80 AND subjectNo=ANY( -- 这里由于是=要加any 嵌套再嵌套实在精彩！
SELECT subjectno FROM `subject`
WHERE subjectname='高等数学-1'
)
)
-- 查询 C语言-1 前5名同学的成绩的信息 （学号姓名分数）
SELECT DISTINCT s.`studentno`,`studentname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.studentno=r.`studentno` AND r.`subjectno`=(
SELECT subjectno FROM `subject`
WHERE `subjectname`='C语言-1'
)
ORDER BY `studentresult`
LIMIT 0,5
3、 MySQL函数
3.1、 常用函数
-- ==============常用函数=============--
-- 数学运算
SELECT ABS(-8)
SELECT CEIL(6.2)
SELECT FLOOR(6.2)
SELECT RAND()
SELECT SIGN() -- 判断一个数的符号
-- 字符串函数
SELECT CHAR_LENGTH ('杨杨') -- 字符串长度
SELECT CONCAT('我','爱数据库') -- 拼接字符串
SELECT INSERT('我爱数据库',2,2,'超级') -- 查询，替换
SELECT LOWER('A')
SELECT UPPER('a')
SELECT INSTR('yangyang','s') -- 第一次出现的位置
SELECT REPLACE('yangyang','yang','young')-- 替换
SELECT SUBSTRING('yangyang',2)
SELECT REVERSE('YOUNG') -- 反转
-- 查询姓张 的同学，名字 赵
SELECT REPLACE(studentname,'赵','张') FROM student
WHERE studentname LIKE '张%'
-- 时间函数
SELECT CURRENT_DATE()-- 获取当前日期
SELECT CURDATE() -- 获取当日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
3.2、聚合函数
-- ====聚合函数========
SELECT COUNT(studentname) FROM student; -- 查出有多少条记录 会忽略掉所有的NULL值
SELECT COUNT(*) FROM student; -- 不会忽略NULL值
SELECT COUNT(1) FROM result; -- 不会忽略NULL值
SELECT SUM(`studentresult`) AS 总和 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result
-- 查询不同课程的平均分、最高分和最低分
-- 核心： 根据不同的课程分组
SELECT `subjectname`,AVG(studentresult)AS 平均分,MAX(studentresult),MIN(studentresult)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>80 -- 这个地方不能使用where!!! 聚合函数分组过滤用HAVING
| 函数名称 | 描述 |
|---|---|
| count | 计数 |
| sum | 求和 |
| avg | 平均 |
| $\cdots $ | ⋯ \cdots ⋯ |
3.3、MD5加密
MD5不可逆
-- ===========测试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
ALTER TABLE `testmd5` MODIFY `pwd` VARCHAR(50)
-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入地时候加密
INSERT INTO testmd5 VALUES(4,'张三',MD5('123456')),(5,'李四','123456'),(6,'王五','123456')
-- 如何校验？将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='张三' AND pwd=MD5(MD5('123456'))
3.4、Select总结
SELECT (ALL | DISTINCT)
{* | table.* | {table.field1{AS a} {table.field2 AS b}}
FROM TABLE_NAME{AS table_alias}
{LEFT |RIGHT|INNER JOIN table_name2} -- 联合查询
[WHERE ..] -- 指定结果需要满足的条件
[GROUP BY ..] -- 通过那个字段来分组
[HAVING ..] -- 过滤分组后的信息 同where 一样 只是位置不同
[ORDER BY ..] -- 通过那个字段来排序 DESC ASC
[LIMIT startindex pagesize] -- 分页
4、事务
将一组SQL放在一个批次中进行
事务原则：ACID 原子 原子性 一致性 隔离性 持久性
- 原子性：
要么都成功，要么都失败
- 一致性：
事务前后数据的完整性要保持一致
- 持久性：
事务一旦提交则不可逆，被持久化到数据库中
- 隔离性：
多个用户并发访问数据库时，数据库为每一个用户开启的服务，不能被其他事务的操作数据所干扰，事务之间要相互隔离
隔离所导致的一些问题：
- 脏读
- 不可重复度
- 虚读
执行事务
-- ================事务===================
-- 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 保存点名 -- 删除保存点名
模拟银行转账事务
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(8,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `account` (`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)
-- 模拟转账：事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET money=money-500 WHERE `name`='A';
UPDATE `account` SET money=money+500 WHERE `name`='B';
COMMIT;-- 提交事务
ROLLBACK; -- 回滚
SET autocommit=1; -- 恢复
5、索引
5.1、索引分类
提取句子主干，就可以得到索引的本质；索引是数据结构
- 主键索引 primary key
- 唯一标识，主键不可重复，只能有一个列作为主键
- 唯一索引 unique key
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
- 常规索引 key/index
- 默认的，index、key关键字来设置
- 全文索引 Fulltext
- 在特定的数据库引擎下才有
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
USE school
SHOW INDEX FROM student
-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)
SHOW INDEX FROM student
-- 分析 sql 执行的情况
EXPLAIN SELECT * FROM student; -- 常规索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('周');
5.2 测试索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' 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 DETERMINISTIC -- 8.0版本需要添加DETERMINISTIC
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO 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`= '用户999999';
-- id_表明_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
EXPLAIN SELECT * FROM app_user WHERE `name`='用户999999';
索引在小数据量时，用处不大，但是在大数据时十分明显！
5.3、索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash类型的数据结构
B树：InnoDB所对应
6、权限管理
6.1、用户管理
SQLyog 可视化操作
SQL命令操作
用户表：mysql.user
本质：对用户表进行增删改查
-- 创建用户
CREATE USER young IDENTIFIED BY '123456'
-- 用户授权
-- 除了给别人授权都能干
GRANT ALL PRIVILEGES ON *.* TO young
-- 查看权限
SHOW GRANTS FOR young
SHOW GRANTS FOR root@localhost
-- 撤销权限 revoke 在那个库撤销
REVOKE ALL PRIVILEGES ON *.* FROM young
7、MySQL备份
- 保证重要的数据不丢失
- 数据转移A->b
备份方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
- 使用命令行 mysqldump
mysqldump -hlocalhost -uroot -p"password" database [table_name] > {directory}
导入先登入
use school
source 备份文件
8、规范数据库设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
8.1、 三大范式
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式（1NF）
原子性：保证每一列都不可再分
第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情
第三范式（3NF）
前提：满足第一范式和第二范式
确保数据表中每一列数据和主键直接相关，二不能间接相关
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段，从多表查询变为单表查询
- 故意增加一些计算列 （从大数据量降低为小数据量的查询：索引）
4091
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
