MySQL笔记（2020-11-29）
#DQL查询（重点）
模糊查询
like
通配符==%==：0-任意个字符
通配符==_==：匹配一个字符
例子
-- username带有a的
SELECT uname FROM tb_user
WHERE uname LIKE 'a%';
-- 查询用户名中间有se的
SELECT uname FROM tb_user
WHERE uname LIKE '%se%';
SELECT uname FROM tb_user
WHERE uname LIKE 'user0_';
关联查询
涉及到多表查询，需要整理好思路
思路
1、分析需求：分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询（一般有7种）
3.确定交叉点（这两个表中哪个数据是相同的）–>判断条件
left join
会从左表中返回所有的值，即使右表中没有匹配
right join
会从右 表中返回所有的值，即使左表中没有匹配
inner join
如果表中至少有一个匹配，则返回行
自连接
自己的表和自己的表连接，核心：一张表拆分为两张一样的表即可
数据库表：
父表
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子表
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | PS技术 |
操作：查询父类对应的子类关系
| 父表 | 子表 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | PS技术 |
select a.categoryName as '父表',b.categoryName as '子表'
from category as a,category as b
where a.category = b.pid;
分页和排序
分页 LIMIT
语法：limit 起始值，页面大小
网页中的应用，当前页/总页数 ，共有多少页
limit 0,5 ->1-5
limit 1,5 ->2-6
limit 3,6 ->4-9
通用公式：（n-1）* pagesize
第一页 （1-1）*5,5
第二页 （2-1）*5,5
第三页 （3-1）*5,5
第N页 （n-1）*5,5
排序 ORDER BY
升序：ASC（默认）
降序：DESC
语法：… ORDER BY 字段 [ASC|DESC]
案例
题目:查询学科是高等数学-2，且分数不小于80分的学生的学号和姓名
-- 查询语句一(由里及外查询)
select distinct s.studentNo,studentName
from student s
inner join result r
on r.studentNo =s.studentNo
where studentResult>=80 and subjectNo = (
select subjectNo
from subject
where subjectName='高等数学-2'
);
-- 查询语句二(由里及外查询)
select studentNo,studentName
from student
where studentNo
in( select studentNo from result
where studentResult >=80 and subjectNo=(
select subjectNo
from subject
where subjectName='高等数学-2'
)
);
练习：查询 C语言-1 前五名同学的成绩信息（学号，姓名，分数）
要求：使用子查询
SELECT s.studentno,s.studentname,r.studentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo
IN(
SELECT r.studentNo FROM result
WHERE r.subjectNo=(
SELECT r.subjectNo
FROM `subject`
WHERE subjectName='C语言-1'
)
)
ORDER BY studentResult DESC
LIMIT 0,5;
结果：
因为需要查询学生的分数，所以内连接了result表。
MySQL函数
常用函数
数学运算
select abs(-8) -- 绝对值
select ceiling(9.4) --向上取整10
select floor(9.4) --向下取整9
select rand() --0-1的随机数
select sign() -- 判断一个数的符号 负数：-1 0:0 正数：1
字符串函数
select char_length('abc') --字符串长度 3
SELECT CONCAT('i','love you') --连接字符串 i love you
select insert('i love java',8,11,'mysql') --查询替换 i love mysql
select lower('ABC') --转小写
select upper('abc') --转大写
select instr('abcd','a') --查询子串第一次出现的位置 1
select replace('abcd','ab','ef') --替换字符串 efcd
SELECT SUBSTR('abcdef',2,2) --截取字符串（源字符串，截取位置，长度）bc
select reverse('12345') --反转字符串 54321
测试：
SELECT REPLACE(studentname,'张','张大')
FROM student
WHERE studentname LIKE '张%'
--张伟 -> 张大伟
时间和日期函数（需要记住）
SELECT CURRENT_DATE() --获取当前日 2020-11-30
SELECT CURDATE() --获取当前日 2020-11-30
select now() --2020-11-30 00:34:17
SELECT LOCALTIME() --2020-11-30 00:34:20
SELECT SYSDATE() --2020-11-30 00:35:11
SELECT YEAR(NOW()) --2020
系统
SELECT USER() --获取当前用户
SELECT SYSTEM_USER() --获取当前用户
SELECT VERSION() --获取版本
##聚合函数（真常用）
| 函数名 | 描述 |
|---|---|
| COUNT() | 统计列数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
SELECT COUNT(studentname) FROM student --count(指定列)
SELECT COUNT(*) FROM student --count(*列)
SELECT COUNT(1) FROM student --count(1)
-- 区别
-- count(指定列)会忽略所有的null值
-- count(*)不会忽略所有的null值 本质：计算所有行数
-- count(1)不会忽略所有的null值 本质：计算行数
SELECT sum(studentresult) FROM student
SELECT avg(studentresult) FROM student
SELECT max(studentresult) FROM student
SELECT min(studentresult) FROM student
测试：查询不同课程的平均分、最高分、最低分
核心：根据不同课程分组
SELECT subjectname AS 科目,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
结果：
注：group by 和having 连用！！！
2020-12-1更新
MD5加密
update 表 set 字段=MD5(字段) where 条件
例子：
updae testMD5 set pwd=MD5(pwd) where id=1;
事务（ACID）
-
原子性（Atomicity）
要么都成功要么都失败
-
一致性（Consistency）
事务前后的数据完整性要保证一致
-
持久性（Durability）
事物一旦提交则不可逆，被持久化到数据库中
-
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务 ，不能被其他事务的操作数据所干扰，事物之间要相互隔离。
隔离导致的问题
脏读：只一个事物读取了另外一个事务未提交的数据
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读：一个事物内读取到了别的事务插入的数据，呆滞前后读取不一致
使用
SET autocommit=0 /*关闭*/
SET autocommit=1 /*开启*/
START TRANSACTION -- 事务开启
#索引
索引是帮助MySQL高效获取数据的数据结构*
分类
主键索引（PRIMARY KEY）：唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）：唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY/INDEX）：默认的，可以用index/key关键字设置
全文索引（FULLTEXT）：在特定的数据引擎才有，MyISAM
快速定位数据
测试
向数据库插入100万条数据
-- 创建函数
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 1;
WHILE i<num DO
-- 插入语句
INSERT userinfo(`name`,age) VALUES(CONCAT('user',i),FLOOR(RAND()*2+1));
SET i=i+1;
END WHILE;
RETURN i;
END $$;
-- 执行函数
SELECT mock_data();
所用时间是：1分6秒
1 queries executed, 1 success, 0 errors, 0 warnings
查询：select mock_data()
返回了 1 行
执行耗时 : 1 min 6 sec
传送时间 : 0 sec
总耗时 : 1 min 6 sec
索引创建
索引名：id_表名_字段
创建索引：CREATE INDEX 索引名 ON 表(字段)
例：CREATE INDEX id_userinfo_name ON userinfo(name);
创建索引后查询数据：
1 queries executed, 1 success, 0 errors, 0 warnings
查询：EXPLAIN SELECT * FROM userinfo WHERE `name`='user999999'
返回了 1 行
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec
未创建索引时查询：
1 queries executed, 1 success, 0 errors, 0 warnings
查询：SELECT * FROM userinfo WHERE `name`='user999999' LIMIT 0, 1000
返回了 1 行
执行耗时 : 0.428 sec
传送时间 : 0.003 sec
总耗时 : 0.431 sec
当数据比较多时，使用索引查询速度有着明显的提升。
原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 一般用在常用来查询的字段
权限管理
使用sqlyog
sql命令
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER cheng IDENTIFIED BY '123456';
-- 修改密码（当前） SET PASSWORD = PASSWORD('密码');
SET PASSWORD = PASSWORD('root');
-- 修改密码（指定用户） SET PASSWORD FOR 用户名 = PASSWORD(密码');
SET PASSWORD FOR cheng =PASSWORD('root');
-- 重命名 RENAME USER 原名 TO 新名;
RENAME USER cheng TO codeofcheng;
-- 用户授权 GRANT ALL PRIVILEGES 全部的权限 库，表
-- ALL PRIVILEGES 除了授权，其它权限都有
GRANT ALL PRIVILEGES ON *.* TO codeofcheng;
-- 查询权限
SHOW GRANTS FOR codeofcheng;
SHOW GRANTS FOR root@localhost;
-- 撤销权限 REVOKE 哪些权限 哪个库 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM codeofcheng;
-- 删除用户
DROP USER codeofcheng;
数据备份
为什么要备份
- 保证重要的数据不丢失
- 数据转移
方式
- 直接备份物理文件 安装目录下的data文件夹
- 在可视化工具手动导出
- 使用命令行导出 ：mysqldump
# 导出
#mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置
mysqldump -hlocalhost -uroot -proot school > D:/school.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
因为没有删除前面测试的100万条数据，所以文件比较大。
#导入 source sql文件的位置
#前提是先要登录 mysql -urot -p密码
#如果是导入表还需要选择数据库
#方式1
source D:/school.sql
#方式2
mysql -urot -p密码 库名<备份文件
规范数据库设计
为什么需要设计
糟糕的数据库设计
- 数据冗余，浪费控件
- 数据库插入和删除都会很麻烦，异常（屏蔽使用物理外键）
- 程序的性能差
良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
设计数据库的步骤
如个人博客：
- 收集信息，需求分析
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友情链接的信息）
- 自定义表（系统信息，某个关键的字，key：value）
- 说说表（发表心情，id content create_time（数据库字段没有大小写区 分，所以推荐用’_'分割））
- 标识实体（把需求落实到每个表）
- 标识实体之间的关系
- 写博客：user–>blog
- 创建分类：user–>category
- 关注：user–>user
- 友链：links
- 评论：user--user--blog
三大范式
三大范式有哪些
第一范式
原子性：保证每一列不可再分
第二范式
前提：满足第一范式
每张表只描述一件事情
第三范式
前提：满足第一范式和第二范式
确保数据表中的每一列数据都和主键相关，而不是间接相关
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，成本、用户体验，数据库的性能更加重要
- 在规范性能问题的时候，需要适当考虑规范性
- 故意给某些字段增加一些荣誉的字段（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
扫一扫
2016
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
