MySQL
本篇MySQL总纲
一、 MySQL基本介绍
什么是数据库？
为了避免数据丢失、遗忘，人们创为了储存文件和数据，造出了一个储存数据的类库——数据库。
数据库三层结构
- 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。DBMS（database manage system）
- 一个数据库中可以创建多个表，以保存数据（信息）。
- 数据库管理系统（DBMS）、数据库和表的关系如图：
- MySQL数据库——普通表的本质任然是文件
SQL语句分类
- DDL：数据定义语句 [create 表 ，库…]
- DML：数据操作语句 [增加（insert），修改（undate），删除 （delate）]
- DQL：数据查询语句 [select]
- DCL：数据控制语句 [管理数据库：比如用户权限（grant、revoke）]
连接到Mysql服务（Mysql数据库）的指令
mysql -h 主机IP -P 端口 -u 用户名 -p密码
注意：
- -p密码不能有空格
- -p后面没有写密码，回车会要求输入密码
- 如果没有写-h 主机，默认是本机
- 如果没有写-P 端口，默认是3306
二、数据库
1. 创建数据库
-
CHARACTER SET : 指定数据库采用的字符集，如果不指定字符集，默认 utf8
-
COLLATE : 指定数据库字符集的校对规则（常用的utf9_bin【区分大小写】、utf8_general_ci【不区分大小写】，注意默认是 utf8_general_ci）
-
创建数据库指令：
CREATE DATEBASE 库名; -
创建一个使用utf8字符集的数据库
CREATE DATEBASE 库名 CHARACTER SET utf8 -
创建一个使用utf8字符集，并带校对规则的数据库（区分大小写）
CREATE DATEBASE 库名 CHARACTER SET utf8 COLLATE utf8_bin -
注意: 在创建数据库表的时候，为了规避关键字，可以使用反引号解决
2. 查看、删除数据库
查看数据库
①：查看所有数据库
SHOW DATABASES
②：查看指定数据库（db01）
SHOW CREATE DATABASE db01
删除数据库
DROP DATABASE 库名字
3. 备份恢复数据库 （☆）
- 备份数据库（注意：在DOS执行）命令行
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
- 恢复数据库（注意：进入MySQL命令行再执行）
Source 文件名.sql
具体操作：
- 备份库的表
mysqldump -u 用户名 -p 数据库 表1 表2 表n > f:\文件名.sql
三、 MySQL列类型（MySQL的数据类型）
● MySQL数据类型导图
常用数据类型大全
1. 常用数据类型
MySQL中常用的数据类型
整型：
int 【4个字节】
小数型：
double【双精度 8个字节】
decima[M,D]【大小不确定】
日期类型：
datetime【年月日 时分秒 YYYY-MM-DD HH:mm:ss】
timestamp【时间戳】
2. 整型
MySQL中所有的整型，常用的有int
以 tinyint 来演示，范围：有符号【-128 ~ 127】，无符号【0-255】
- 如果没有指定unsigned ， 则TINYINT 就是有符号【-128 ~ 127】
create table t1(id tinyint); //默认是有符号的
- 如果指定 unsigned ，则TINYINT就是无符号【0 ~ 255】
create table t2(id tinyint unsigned); 无符号
CREATE TABLE db01( //默认有符号
id TINYNT);
CREATE TABLE db02( //指定无符号
id TINYINT UNSIGNED);
INSERT INTO db01 VALUES(127); //-128 - 127
SELECT * FROM db01;
INSERT INTO db02 VALUES(255); //0-255
SELECT * FROM db02;
3. 数值型（bit）
bit 的介绍及基本用法
① 基本使用
mysql > create table t02(num bit(8));
mysql > create table t02(1,3);
mysql > insert into t02 values(2,65);
② 注意事项
- bit 字段显示时，按照位的方式显示。
- 查询的时候仍然可以用使用添加的数值
- 如果一个值只有 0,1 可以考虑使用 bit(1) ，可以节约空间
- 位类型：M指定位数，默认值1，范围1-64
③ 演示bit类的使用
代码实现
结果
4. 数值型（小数）
float、double 与 decimal
① FLOAT/DOUBLE [UNSIGNED]
Float 单精度 ， Double 双精度
② DECIMAL[M,D] [UNSIGNED]
- 可以支持更加精确的小数位。M是小数位数（精度）的总数，D是小数点（标度）
- 如果D是0，则值没有小数点或分数部分。M最大65，D最大是30。如果D被省略，默认是0.如果M被省略，则默认是10.
- 建议：如果希望小数的精度高，推荐使用decimal
小数的演示
结果
可以看见float保留的是4位小数，double保留了14位小数，而decimal还多出了6个0，这说明decimal可以存放很大的数。
5. 字符串
char 与 varchar的使用及差别
字符串的基本使用
CHAR(size)
固定长度字符串 最大255字符
VARCHAR(size)
可变长度字符串 最大65532字节 【utf8编码最大21844字符 1-3个字节用于记录大小】
注意：
每种编码大小不同，VARCHAR能储存的最大值也会随着给定的编码变化，比如utf8编码存65535就会报错，如下所示：
报错信息
varchar能存放最大值的情况：
- 如果表的编码是 utf8 varchar(size) size = (65535 - 3) / 3 = 21844
- 如果表的编码是 gbk varchar(size) size = (65535 - 3) /2 = 32766
字符串使用注意事项
- 注意事项1
- char(4) // 这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放四个，按字符计算。
- varchar(4) // 这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据。
- 上面的两种4 都代表，不管是中文还是英文字母，都是最多存放4个，因为他是按照字符来存放的。
- 注意事项2
-
char(4)是定长（固定的大小），也就是说，即使你插入 ‘aa’，也会占用分配的4个字符的空间。
-
varchar(4)是变长（变化大小），就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（varchar本身还需要占用1-3个字节来记录存放内容长度）。 L（实际数据大小）+ （1-3）字节
- 注意事项3
什么时候使用char，什么时候使用varchar？
①. 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等，char（32）
②. 如果一个字段的长度是不确定，则使用varchar，比如留言，文章等等
③. 查询速度：char > varchar
- 注意事项4
在存放文本时，也可以使用Text数据类型，可以将TEXT列视为VARCHAR列，注意 Text 不能有默认值，大小在 0 ~ 2^16字节。
如果希望存放更多字符，可以选择：
MEDIUMTEXT 0 ~ 2^24 或者 LONGTEXT 0 ~ 2^32
6. 日期类型
MySQL常见的日期类型
- 日期类型的基本使用
CREATE TABLE birthday(t1 DATE,t2 DATETIME,
t3, TIMESTAMP
NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP );
#timestamp时间戳
mysql > INSERT INTO birthday(t1,t2)
VALUES(‘2022-11-11’,‘2022-11-11 10:10:10’);
- 日期类型的细节说明
TImeStamp在Insert和update时，自动更新
代码演示
运行结果
四、 表
● 创建表
注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。
举个例子：
在数据库db01创建一个user表，需求为：
id 整型
name 字符串
password 字符串
birthday 日期
代码实现
CREATE TABLE 'user'(
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE
) CHARACTER SET utf8 CCOLLATE utf8_bin ENGINE INNODB;
创建表练习
创建一个员工表 emp，选用适当的数据类型
代码实现：
# 创建表
CREATE TABLE emp(
id INT,
`name` VARCHAR(32),
sex CHAR(2),
brithday DATE,
entry_date DATETIME,
job VARCHAR,
salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
# 添加一条数据
INSERT INTO `emp`
VALUES(100,'小妖怪','男','2000-11-11',
'2010-11-10 11:11:11','巡山的',3000,'大王叫我来巡山')
运行结果：
● 修改表
修改表应用实例
查看初始表emp
修改表信息代码实现
#员工表emp上增加一个image列,varchar类型（要求在resume后）
ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER RESUME;
#修改job列,使其长度为60。
ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT '';
#删除sex列。
ALTER TABLE emp DROP sex;
#表名改为employee
RENAME TABLE emp TO employee;
#修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8;
#列明name修改为user_name
RENAME TABLE employee CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT;
修改完成，查询新表employee
○ 复制
自我复制数据（蠕虫复制）
有时，为了对某个 sql 语句进行效率测试，我们需要海量数据，可以使用此法为表创建海量数据。
自我复制使用案例
创建一张新表
CREATE TABLE fz_tab01
(
id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT
);
创建效果
演示如何自我复制
1.把 emp 表的记录复制到 fz_tab01
INSERT INTO fz_tab01
(id, `name`, sal, job, deptno)
SELECT empno,ename,sal,job, deptno FROM emp;
复制结果
2. 自我复制
把 fz_tab01 表的所有数据复制到 fz_tab01 表中
INSERT INTO fz_tab01
SELECT * FROM fz_tab01;
3.复制表结构
CREATE TABLE fz_tab02 LIKE emp;
这个语句的作用是：把emp表的结构（列），复制到 fz_tab02 中
复制效果
○ 表去重
使用蠕虫复制连续复制两次 tab02 表，得到26条记录，现在需要把多余的数据去除，得到一张13行记录的表。
重复的 fz_tab02表
分析思路：
- 先创建一张临时表 my_tmp ，该表的结构和 fz_tab02 一样
- 把 fz_tab02 的记录通过 distinct 关键字处理后，把记录复制到 my_tmp
- 清除掉 fz_tab02 记录
- 把 my_tmp 表的记录复制到 fz_tab02
- drop 掉临时表 my_tmp
代码实现
- 先创建一张临时表 my_tmp ，该表的结构和 fz_tab02 一样
CREATE TABLE my_tmp LIKE fz_tab02
- 把 mfz_tmp 的记录通过 distinct 关键字处理后，把记录复制到 fz_tmp
INSERT INTO my_tmp
SELECT DISTINCT *
FROM fz_tab02
- 清除掉 fz_tab02 记录
DELETE FROM fz_tab02
- 把 my_tmp 表的记录复制到 fz_tab02
INSERT INTO fz_tab02
SELECT *
FROM my_tmp
- drop 掉临时表 my_tmp
DROP TABLE my_tmp
得到最终去重的表：fz_tab02
SELECT * FROM fz_tab02
运行效果截图
五、CRUD语句
数据库CRUD语句
- 数据库C[创建、添加] R[读取] U[修改] D[删除]
- C [create] R [read] U [update] D [delete]
● Insert语句 (添加数据)
Insert基本语法
Insert使用案例
要求为：创建一张商品表
1. goods(id int, goods_name vharchar(10),price double);
2. 添加两条记录
①. vivo,编号1,价格2000
②. oppo,编号2,价格2000
代码实现
CREATE TABLE goods(
id INT,
goods_name VARCHAR(10),
price DOUBLE
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO goods(id, goods_name, price)
VALUES(1,'vivo',2000);
INSERT INTO goods(id, goods_name, price)
VALUES(2,'oppo',2000);
运行结果
Insert语句注意事项
-
插入的数据应与字段的数据类型相同。
比如把 ‘abc’ 添加到 int 类型会错误。 -
数据的长度应在列的规定范围内。
例如：不能将一个长度为80的字符串加入到长度为40的列中。 -
在values中列出的数据位置必须与被加入的列的排列位置相对应。
-
字符和日期类型数据应包含在单引号中。
-
列可以插入空值【前提是该字段允许为空】
nsert into table value(null) -
insert into tab_name(列名…) values (),(),() 形式添加多条记录
-
如果是给表中的所有字段添加数据，可以不写前面的字段名称
-
默认值的使用，当不给某个字段时，如果有默认值就会添加默认值，否则报错，如果某个列没有指定 not null，那么当添加数据时，就没有给定值，则会默认给null
注意事项的注解：
- 第一条
插入的数据应与字段的数据类型相同。
例如’abc’ 插入到int类型会报错，但是如果是 '30’的话，mysql数据库的底层会尝试转换数据类型，所以就有了’30’能插入进去的说法。
- 第六条
insert into tab_name(列名..) values (),(),() 形式添加多条记录
以goods表为例，如果添加多条语句，不用重复打多条insert语句，而是直接在一条语句后加多条values值就可以。
例如：
INSERT INTO `goods`(id,goods_name,pirce)
VALUES(1,'vivo',2000),(2,'oppo',3000),(3,'huawei',4000);
- 第七条
如果是给表中的所有字段添加数据，可以不写前面的字段名称
还是以goods表为例，如果我添加新的数据：编号4，名字为诺基亚，价格为2000，那么就可以省略字段的名称，例如：
INSERT INTO `goods`
values(4,'诺基亚',2000);
但注意一个点：如果我添加的数据数量与字段最大数量不符，多一条或少一条数据，都会导致运行报错。
● update语句 (修改数据)
注意：如果where没写，就表示对表中所有数据进行修改。
update使用案例
代码实现
UPDATE employee SET salary = 5000; #[如果没有带where,会修改所有的记录]
UPDATE employee SET salary = 3000 WHERE user_name = '小妖怪';
UPDATE employee
SET salary = salary + 1000,job = '打鼓的';
WHERE user_name = '老妖怪';
SELECT * FROM employee;
update语句注意事项：
- UPDATE 语句可以用新值更新原有表行中的各列。
- SET子句指示要修改哪些列和要给予哪些值。
- WHERE子句指定应更新哪些行。如果没有where子句，则更新所有的行（记录），因此一定要小心使用。
- 如果需要修改多个字段，可以通过 set 字段1 = 值1，字段2 = 值2…
● delete语句 (删除数据)
delete语句基本语法
delete语句使用案例
代码实现
-- 删除表中名称为 '老妖怪' 的记录
DELETE FROM employee WHERE user_name = '老妖怪';
-- 删除表中所有记录,一定要小心使用
DELETE FROM employee ;
delete语句使用注意事项
- 如果不使用where子句，将删除表中所有数据（注意使用！！！）
- Delete语句不能删除某一列的值（可使用update 设为 null 或者 ’ '）
- 使用delete语句仅能删除记录，不能删除表本身。如果要删除表，则需使用drop table语句（drop table 表名;）
● select语句 （☆）
1. select基础与distinct（去重）
基本语法
select语句注意事项：
- Select指定查询哪些列的数据。
- column指定列名。
- 号代表查询所有列。
- From指定查询哪张表
- distinct可选，指显示结果时，是否去掉重复数据。
select语句使用案例1
学生表的创建
代码实现
-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct。
#要查询的记录,每个字段都相同,才会去重
SELECT DISTINCT english FROM student;
注意：要查询的记录,每个字段都相同,才会去重，比如说查询english时发现有两个78和98，那么就会去掉相同的78和98，效果如下所示：
去重前
去重后
但如果再添加名字的话，就是对比两条数据，此时虽然说78与98都有重复，但是由于前面的name是不同的人名，不满足每个字段都相同,所以并不会去重
添加name的查重
2. select运算与as语句（改名）
基本语法
select语句2使用案例
代码实现
1.统计每个学生的总分
-- 1.统计每个学生的总分
SELECT `name`, (chinese + english + math) FROM student;
运行效果
2.查看所有学生总分加10的情况
-- 2.查看所有学生总分加10的情况
SELECT `name`,(chinese + english + math + 10) FROM student;
运行结果
3.使用别名表示学生分数。
-- 3.使用别名表示学生分数。
SELECT `name` AS '名字',(chinese + english + math) AS '总分' FROM student;
运行结果
3. where子句（过滤）
基本语法
where子句使用案例1
代码实现
-- 1.查询姓名为赵云的学生成绩
SELECT * FROM student
WHERE `name` = '赵云';
-- 2.查询英语成绩大于90分的同学
SELECT * FROM student
WHERE english > 90;
-- 3.查询总分大于200分的所有同学
SELECT * FROM student
WHERE (chinese + english + math) > 200;
运行结果
where子句使用案例2
使用where子句,进行过滤查询
1.查询math大于60并且(and)id大于4的学生成绩。
2.查询英语成绩大于语文成绩的同学。
3.查询总分大于200分并且数学成绩小于语文成绩的姓赵的学生。
代码实现
--1.查询math大于60并且(and)id大于4的学生成绩。
SELECT * FROM student
WHERE math > 60 AND id > 4;
--2.查询英语成绩大于语文成绩的同学。
SELECT * FROM student
WHERE english > chinese;
--3.查询总分大于200分并且数学成绩小于语文成绩的姓赵的学生。
SELECT * FROM student
WHERE (chinese + math + english) > 200
AND math < chinese
AND `name` LIKE '韩%';
注意：查找名字姓氏使用 like ， （like 姓或名） 如果是’赵%‘，就近似查找姓赵的人，若是%在前：’%赵’，则代表查找最后一个字是赵的人。
where子句使用案例3
1.查询英语分数在 80 - 90 之间的同学。
2.查询数学分数为 89, 90, 91的同学。
3.查询所有姓李的学生成绩。
4.查询数学分 > 80, 语文分 > 80 的同学。
代码实现
--1.查询英语分数在 80 - 90 之间的同学。
SELECT * FROM student WHERE 80 <= english <= 90;
# 注意: between and 语句是闭区间。
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
--2.查询数学分数为 89, 90, 91的同学。
SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student WHERE math IN (89,90,91);
--3.查询所有姓韩的学生成绩。
SELECT * FROM student WHERE `name` LIKE '韩%';
--4.查询数学分 > 80, 语文分 > 80 的同学。
SELECT * FROM student WHERE math > 80 AND chinese > 80;
4. order by子句（排序）
order by : 排序查询结果
Asc升序【默认】、Desc 降序
基础语法
注意：
- Order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
- Asc升序【默认】、Desc 降序
- ORDER BY 子句应位于SELECT语句的结尾。
order by 使用案例
代码实现
-- 对数学成绩排序后输出【升序】
SELECT * FROM student
ORDER BY math ASC;
-- 对总分按从高到低的顺序输出
SELECT `name`, (math + chinese + english)
AS '总分'
FROM student
ORDER BY '总分' DESC;
-- 对姓张的学生成绩排序输出(升序)
# 按三科成绩排序
SELECT * FROM student
WHERE `name` LIKE '张%'
ORDER BY (math + chinese + english) ASC;
#按总分排序
SELECT `name` ,(math + chinese + english) AS '总分' FROM student
WHERE `name` LIKE '张%'
ORDER BY (math + chinese + english) ASC;
六、 函数
● 合计函数 与 统计函数
○ 统计函数（count）
统计函数count基本语法
count使用案例
代码实现
-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的学生有几个？
SELECT COUNT(*) FROM student
WHERE math > 90;
-- 统计总分大于250的人数有多少？
SELECT COUNT(*) AS '总分 > 250' FROM student
WHERE (chinese + math + english)>250;
注意：
count(*) 与 count(列) 的区别
count(*) : 返回满足条件的记录的行数
count(列) : 统计满足条件的某列有多少个,但是会排出NULL的情况
例如：
创建一个新表 t05
3个值，一个空值
count (*)
SELECT COUNT(*) FROM t05;
count (列)
SELECT COUNT(`name`) FROM t05;
○ 合计函数（sum）
合计函数 sum 基础语法
sum 使用案例
代码实现
-- 统计一个班级数学总成绩
SELECT SUM(math) FROM student;
#改别名
SELECT SUM(math) AS '数学总分' FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
#改别名
SELECT SUM(math) AS '数学总分',
SUM(chinese) AS '语文总分',
SUM(english) AS '英语总分' FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese + math + english) FROM student
#改别名
SELECT SUM(math + chinese + english) AS '总分' FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese) / COUNT(*) FROM student;
#改别名
SELECT SUM(chinese) / COUNT(*) AS '平均分' FROM student;
○ 合计函数（avg）
合计函数 avg 基础语法
avg使用案例
代码实现
-- 求一个班级数学平均分
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + chinese + english) FROM student;
○ 合计函数（Max / min）
合计函数 max / min 基本语法
max / min 使用案例
代码实现
-- 求一个班级最高分和最低分
SELECT MAX(chinese + math + english) AS '最高分',
MIN(chinese + math + english) AS '最低分' FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math),MIN(math) FROM student;
○ group by子句 与 having 子句
group by : 对列进行分组
having : 对分组后的结果进行过滤
group by 与 having 基础语法
group by + having 使用案例
部门表的创建
-- 创建部门表
CREATE TABLE dept (
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
-- 添加数据
INSERT INTO dept
VALUES(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');
员工便的创建
-- 创建员工表
CREATE TABLE emp(
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "", /*工作*/
mgr MEDIUMINT UNSIGNED, /*上级编号*/
hiredate DATE NOT NULL, /*入职时间*/
sal DECIMAL(7,2) NOT NULL, /*薪水*/
comm DECIMAL(7,2), /*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
-- 添加数据
INSERT INTO emp
VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1991-2-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1991-9-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1997-4-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23',1300.00,NULL,10);
工资级别表的创建
-- 创建工资级别表
CREATE TABLE salgrade(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/
losal DECIMAL(17,2) NOT NULL, /*该级别的最低工资*/
hisal DECIMAL(17,2) NOT NULL /*该级别的最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
代码实现
-- 显示每个部门的平均工资和最高工资
SELECT MAX(sal),AVG(sal),deptno
FROM emp
GROUP BY deptno ;
-- 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal), deptno, job
FROM emp
GROUP BY deptno,job;
-- 显示平均工资低于2000的部门号和它的平均工资
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) < 2000;
● 字符串函数
字符串函数相关语法
字符串相关函数使用案例
○ charsert（返回字符串字符集）
- CHARSET(str) 返回字串字符集 （常用）
SELECT CHARSET(ename) FROM emp;
○ concat（链接字符串）
- CONCAT (string2 [,…]) 链接字符串,将多个列拼接成一列 （常用）
SELECT CONCAT(ename,' 工作是 ',job) FROM emp;
○ instr(string,substring)
- INSTR (string ,substring) 返回substring在string中出现的位置,没有返回0
SELECT INSTR('tong','g') FROM DUAL;
○ ucase（转换成大写）
- UCASE (string2) 转换成大写 （常用）
SELECT UCASE(ename) FROM emp;
○ lacase（转换成小写）
- LACASE(string2) 转换成小写 （常用）
SELECT LCASE(ename) FROM emp;
○ left（左起取字符）
- LEFT(string2,length) 从string2中的左边起取length个字符
SELECT LEFT(ename,2) FROM emp;
○ right（右起取字符）
- RIGHT(string2,length)从string2中的右边起取length个字符
SELECT RIGHT(ename,2) FROM emp;
○ length（查长度）
- LENGTH(string) string长度[按照字节] （常用）
#亚元表,系统自带表
SELECT LENGTH('tong'),LENGTH('陈') FROM DUAL;
○ replace（替换）
- REPLACE(str,search_str,replace_str) 在str中用replace_str替换search_str （常用）
SELECT REPLACE(job,'MANAGER','经理') FROM emp;
○ strcmp（比字符串大小）
- strcmp(string1,string2) 逐字符比两字符串大小
SELECT STRCMP('tong','tong') FROM DUAL;
○ substring（取字符）
- SUBSTRING(str,position[length]) 从str的position开始【从1开始计算】,取length个字符 （常用）
# 从ename列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename,1,2) FROM emp;
○ ltrim、rtrim、trim（去空格）
- LTRIM(string2) RTRIM(string2) TRIM(string)去除前段空格或后端空格
#去除前端空格
SELECT LTRIM(' 彤 ') FROM DUAL;
#去除后端空格
SELECT RTRIM(' 彤 ') FROM DUAL;
#去除两端空格
SELECT TRIM(' 彤 ') FROM DUAL;
去除前端空格
去除后端空格
去除两端空格
字符串函数练习
代码实现
方法一：
SELECT REPLACE(ename,LEFT(ename,1),LCASE(LEFT(ename,1)))
AS '姓氏小写'
FROM emp;
方法二：
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2))
AS '姓氏小写'
FROM emp;
方法三：
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2))
AS '姓氏小写'
FROM emp;
● 数学函数
数学相关函数基本语法
数学相关函数使用介绍
○ ABS（绝对值）
- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL;
○ BIN（十进制转二进制）
- BIN(decimal_number) 十进制转换二进制
SELECT BIN(10) FROM DUAL;
○ ceiling（向上取整）
- CEILING(number2) 向上取整,得到比num2大的最小整数
SELECT CEILING(1.1) FROM DUAL;
○ conv（进制转换）
- conv(number2,from_base,to_base) 进制转换
# 8 是 十进制的 8,转换为 2 进制输出
SELECT CONV(8,10,2) FROM DUAL;
○ floor（向下取整）
- FLOOR(number2) 向下取整，得到比 num2 小的最大整数
#保留 2 位小数,四舍五入
SELECT FORMAT(78.12548,2) FROM DUAL;
○ HEX（转十六进制）
- HEX(DecimalNumber) 转十六进制
SELECT HEX(66) FROM DUAL;
○ least （求最小值）
- LEAST(number , number2 [,…]) 求最小值
SELECT LEAST(0,1,10,20,-5) FROM DUAL;
○ MOD（求余）
- MOD(numerator , denominator) 求余
SELECT MOD(10,3) FROM DUAL;
○ rand（返回随机数）
- RAND([seed]) 返回随机数 范围为 0<= v <=1.0
SELECT RAND() FROM DUAL;
注意：
- 如果使用rand() 每次都会返回不同的随机数 ， 在0<= v <=1.0
- 如果使用 rand(seed) 返回一个随机数，范围也是0<= v <=1.0，但是如果seed不变，该随机数也不变，固定下来。
● 时间日期函数
时间日期函数语法
时间日期相关函数使用示范
○ current_date（当前日期）
语法
SELECT CURRENT_DATE() FROM DUAL;
效果
○ current_time（当前时间）
语法
SELECT CURRENT_TIME() FROM DUAL;
效果
○ current_timestamp（当前时间戳）
语法
SELECT CURRENT_TIMESTAMP() FROM DUAL;
效果
当前时间戳日常使用：
INSERT INTO mes
VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
○ now（当前日期）
语法
NOW(当前日期)
使用
INSERT INTO mes
VALUES(2,'上海新闻',NOW());
INSERT INTO mes
VALUES(3,'广州新闻',NOW());
效果
○ date（返回datetime的日期部分）
语法
DATE(datetime) FROM 返回datetime的日期部分
使用案例
显示所有新闻信息,发布日期只显示 日期,不用显示时间
代码实现
-- 显示所有新闻信息,发布日期只显示 日期,不用显示时间
SELECT id, content,DATE(send_time) FROM mes;
运行截图
○ date_add（加日期或时间）
语法
DATE_ADD(date2, INTERVAL d_value d_type) 在date2中加上日期或时间
使用案例
查询在20分钟内发布的帖子
代码实现
#使用 ADD
SELECT *
FROM mes
WHERE DATE_ADD(send_time,INTERVAL 20 MINUTE) >= NOW();
#使用 SUB
SELECT *
FROM mes
WHERE DATE_SUB(send_time >= DATE_SUB(NOW(),INTERVAL 20 MINUTE);
运行截图
○ date_sub（减去一个时间）
语法
DATE_SUB(date2, INTERVAL d_value d_type) 在date2上减去一个时间
使用案例
在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天？
代码实现
SELECT DATEDIFF('2011-11-11','1990-01-01')FROM DUAL;
运行截图
○ datediff（两个日期差）
语法
DATEDIFF(date1,date2) 两个日期差（结果是天）【小日期写前面】
使用案例
用mysql 的sql语句求出你活了多少天？
代码实现
SELECT DATEDIFF('2022-10-14','1999-09-07')FROM DUAL;
运行截图
上面4个函数注意事项：
- DATE_ADD()中的 interval后面可以是year minute second day 等
- DATE_SUB()中的interval后面可以是year minute second day 等
- DATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
- 这四个函数的日期类型可以是date ， datetime 或者 timestamp
○ timediff（时间差）
语法
TIMEDIFF 两个时间差（多少小时多少分钟多少秒）
代码实现
SELECT TIMEDIFF('10:11:11','06:10:10') FROM DUAL;
运行截图
○ year | month | day | date
语法
YEAR|Month|DAY| DATE(datetime)
当前年份
SELECT YEAR(NOW()) FROM DUAL; #当前年份
当前月份
SELECT MONTH(NOW()) FROM DUAL; #当前月份
当前日期
SELECT DAY(NOW()) FROM DUAL;
○ unix_timestamp（从1970-1-1至今的秒数）
语法
unix_timestamp() 返回1970-1-1到现在的秒数
使用
SELECT UNIX_TIMESTAMP() FROM DUAL;
○ from_unixtime（转换指定格式日期）
语法
FROM_UNIXTIME() 可以把一个unix_timestamp 秒数，转换成指定格式的日期
注意：%Y-%m-%d 格式是规定好的,表示年月日 %H:%i:%s表示时分秒
显示年月日
SELECT FROM_UNIXTIME(1665987311,'%Y-%m-%d') FROM DUAL;
显示年月日，时分秒
SELECT FROM_UNIXTIME(1665987311,'%Y-%m-%d %H:%i:%s') FROM DUAL;
FROM_UNIXTIME() 的意义：在开发中，可以存放一个整数，表示时间，通过FROM_UNIXTIME()来转换
习题： 如果您能活80岁,求出你还能活多少天？
代码实现
#先求出活到 80 岁
DATE_ADD('1999-09-07',INTERVAL 80 YEAR)
#再求出现在到80岁还能活多少天 datediff(x,now());
SELECT DATEDIFF(DATE_ADD('1999-09-07',INTERVAL 80 YEAR), NOW()) FROM DUAL;
运行截图
● 加密和系统函数
加密和系统函数基本语法
加密和系统函数使用示范
○ user（查询用户）
语法
USER() 查询用户 【可以查看登录到mysql的有哪些用户,以及登录的IP】
使用
SELECT USER() FROM DUAL;
○ database（查当前数据库名）
语法
DATABASE() 查询当前使用数据库名称
使用
SELECT DATABASE();
○ MD5（加密存储）
语法
MD5(str) 为字符串算出一个 MD5 32的字符串，常用（用户密码）加密
【root密码设为coco， coco -> 加密MD5 -> 在数据库中存放的是加密后的密码】
使用
SELECT MD5('coco') FROM DUAL;
运行
○ password（MySQL自带加密）
语法
PASSWORD(str) 加密函数，MySQL数据库的用户密码就是 PASSWORD函数加密，自带
SELECT PASSWORD('coco') FROM DUAL;
○ mysql.user（查看用户权限）
语法
mysql.user 查看数据库权限
使用
SELECT * FROM mysql.user;
效果
● 流程控制函数
流程控制函数基本语法
流程控制函数使用示范
○ if（判断语句）
语法
IF(expr1,expr2,expr3) 如果expr1为True,则返回expr2 否则返回expr3
【if语句类比java中的三目运算符，若为真则返回第一个值，若为假则返回第二个值】
使用
SELECT IF(TRUE,'北京','上海') FROM DUAL;
效果
○ ifnull（判断空值）
语法
IFNULL(expr1,expr2) 如果expr1不为空(NULL),则返回expr1，否则返回expr2
为NULL的情况
SELECT IFNULL(NULL,'假') FROM DUAL; #返回假
不为NULL的情况
SELECT IFNULL('真','假') FROM DUAL; #返回真
○ select case when （类多重分支）
语法
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;[类似多重分支]
【如果expr1 为 TRUE，则返回expr2，如果expr2为true，返回expr4，否则返回expr5】
SELECT CASE
WHEN FALSE THEN 'jack'
WHEN TRUE THEN 'tom'
ELSE 'mary' END;
流程控制函数案例
- 查询emp表，如果comm是null，则显示0.0
- 如果emp表的 job 是 CLERK 则显示 职员，如果是MANAGER 则 显示经理，如果是SALESMAN 则显示 销售人员，其他正常显示。
代码实现
- 查询emp表，如果comm是null，则显示0.0
#使用if
#判断是否为null 要使用 is null,判断不为 null 使用is not
SELECT IF(comm IS NULL , 0.0,comm) FROM emp;
#使用ifnull
SELECT IFNULL(comm,'0.0') FROM emp;
- 如果emp表的 job 是 CLERK 则显示 职员，如果是MANAGER 则 显示经理，如果是SALESMAN 则显示 销售人员，其他正常显示。
SELECT ename,(SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS'job',job
FROM emp;
#不显示名字与原本工作
SELECT (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS'job'
FROM emp;
七、 连接
● 单表查询（加强）
使用案例来介绍加强的单表查询
○ 使用where子句
○ 需求：
查找1992.1.1后入职的员工
※ 说明：在mysql中，日期类型可以直接比较，但需注意格式
代码实现
SELECT * FROM emp
WHERE hiredate > '1992-01-01';
运行效果
like操作符（模糊查询）
like操作符
-
% :表示0到多个任意字符
-
_ :表示单个任意字符
○ 需求1
显示首字母为S的员工姓名和工资
代码实现
#显示首字母为S的员工姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE 'S%';
运行效果
○ 需求2
显示第三个字符为大写O的所有员工的姓名和工资
代码实现
#显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE '__O%'; #注意:这是两个下划线
运行效果
○ 需求3
显示没有上级的雇员的情况
代码实现
#显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
运行效果
○ 需求4
查询表结构
代码实现
#查询表结构
DESC emp;
运行效果
○ 使用order by子句
○ 需求1
按照工资的从低到高的顺序[升序]，显示雇员的信息
代码实现
SELECT * FROM emp
ORDER BY sal;
运行截图
○ 需求2
按照部门号升序而雇员的工资降序排列，显示雇员信息
代码实现
SELECT * FROM emp
ORDER BY deptno ASC , sal DESC;
运行效果
标注
limit（分页查询）
基本语法
select ... limit start,rows
表示从start + 1 行开始取,取出rows行,start从0开始计算
推导公式
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数 * (第几页 - 1) , 每页显示记录数
○ 实例
按雇员的id号升序取出，每页显示3条记录，请分别显示 （第一页、第二页、第三页）
原表
第一页
代码
SELECT * FROM emp
ORDER BY empno
LIMIT 0,3;
运行
第二页
代码
SELECT * FROM emp
ORDER BY empno
LIMIT 3,3;
运行
第三页
代码
SELECT * FROM emp
ORDER BY empno
LIMIT 6,3;
运行
○ 分组增强
演示案例
1.显示每种岗位的雇员总数、平均工资
2.显示雇员总数、平均工资
3.统计没有获得补助的雇员数
4.显示管理者的总人数
5.显示雇员工资的最大差额
- 显示每种岗位的雇员总数、平均工资
代码
SELECT COUNT(*),AVG(sal), job
FROM emp
GROUP BY job;
运行
- 显示雇员总数、平均工资
代码
#思路: 获得补助的雇员数 等于 comm列为非null, 使用count(列),
#如果该列的值为null,count则不会统计
SELECT COUNT(*), COUNT(comm)
FROM emp;
运行
- 统计没有获得补助的雇员数
方法1
若comm是null，则记录为1，不为null，则标记为null，统计1的值就是没用获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp;
运行1
方法2
总人数 减掉 获得补助的人数
SELECT COUNT(*), COUNT(*) - COUNT(comm)
FROM emp;
运行2
- 显示管理者的总人数
代码
SELECT COUNT(DISTINCT mgr)
FROM emp;
运行
- 显示雇员工资的最大差额
代码
SELECT MAX(sal) - MIN(sal)
FROM emp;
运行
数据分组总结
如果select语句同时包含有group by，having，limit，order by 那么他们的顺序是group by，having，order by，limit
如下所示
SELECT column1, column2, column3.. FROM table
group by column
having condition
order by column
limit start, rows;
总结案例
– 统计各个部门group by的平均工资 (avg),
– 并且是大于1000的having,并且按照平均工资从高到低排序 (order),
– 取出前两行记录 (limit)
代码实现
SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2
运行
● 多表查询（内连接）
以往的经验都是基于mysql中单表的基本查询，所有的操作都是在一张表上所完成的，但这在实际的软件开发中，还远远的不够，所以我们将迎来多表的查询。
○ 笛卡尔积
笛卡尔积表的出现
以一个案例来说明
– 1. 显示雇员名,雇员工资及所在部门的名字
– 2. 显示部门号为10的部门名、员工名和工资
– 3. 显示各个员工的姓名,工资,及其工资的级别
emp表
SELECT * FROM emp;
dept表
SELECT * FROM dept;
从上面两张表可以看到，emp表共有13行数据，而dept表则有4条数据，而在默认情况下，当两个表查询时，规则如下：
- 从第一张表中，取出一行 和 第二张表的每一行进行组合，返回结果[含有两张表的所有列]。
- 一共返回的记录数 第一张表行数 * 第二张表的行数，这样多表查询默认处理返回的结果，称之为笛卡尔积（13*4=52）
- 而笛卡尔积表往往都会有许多重复的条件，解决这个多表的关键就是要写出正确的过滤条件（where）
笛卡尔积表
emp表 13 行 * dept表 4 行 = 52行表（13*4=52）
SELECT *
FROM emp,dept;
为了防止笛卡尔积表的出现，我们需要过滤表中的条件，如上面两张表所示，emp表和dept表都有一个共同的条件【deptno】，如果筛选这个条件，则能得出最终我们需要的那张13行的表（二合一表），如下所示：
代码
SELECT *
FROM emp,dept
WHERE emp.deptno = dept.deptno;
运行
题目1
显示雇员名,雇员工资及所在部门的名字
案例的代码实现
SELECT ename,sal,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno;
运行效果
注意：
如果分组里有deptno，则会报错，因为表里有两个deptno，报错信息会显示指代不明。
防止笛卡尔集的出现
- 当我们需要指定显示某个表的列时，需要：表.列表
- 多表查询的条件不能少于 表的个数-1，否则会出现笛卡尔集，例如：2表得1过滤条件，3表得2过滤条件…
题目2
- 显示部门号为10的部门名、员工名和工资
代码
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.deptno = 10;
运行
题目3
- 显示各个员工的姓名,工资,及其工资的级别
代码
SELECT * FROM salgrade
FROM salgrade,emp;
运行
练习4
- 显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序]
代码
SELECT ename,sal,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY loc DESC;
运行
○ 自连接
自连接：
自连接是指在同一张表的链接查询【将同一张表当做两张表使用】
介绍案例
显示公司员工名字和他的上级的名字
分析：
- 员工名字 在emp，上级的名字的名字
- 员工和上级通过 emp 表的 mgr 列关联
代码
SELECT worker.ename AS '员工名' , boss.ename AS '上级名'
FROM emp AS worker , emp AS boss
WHERE worker.mgr = boss.empno;
运行
自连接特点总结：
- 把同一张表当做两张表使用
- 需要给表取别名 （表明 AS 表别名）
- 列名不明确，可以指定列的别名（列名 AS 列的别名）
○ 子查询
什么是子查询？
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
子查询的分类
子查询分为两种，一种是单行子查询，一种是多行子查询。
- 单行子查询：
单行子查询是指只返回一行数据的子查询语句
- 多行子查询
多行子查询是指返回多行数据的子查询【使用关键字 in】
1. 多行子查询
单行子查询使用案例
显示与SMITH同一部门的所有员工
分析
1.先拿到smith的部门号
2.再将smith的部门号作为子句来查询
3.只返回一条语句（相同部门号）
代码
#先拿到smith的部门号
-- select deptno
-- from emp
-- where ename = 'SMITH'
#再将smith的部门号作为子句来查询
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
)
运行
多行子查询使用案例
查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不包含10自己的
分析
1.查询到10号部门有哪些工作
-- 1.查询到10号部门有哪些工作
-- select distinct job
-- from emp
-- where deptno = 10;
2.把上面查询的结果当做子查询使用
-- 2.把上面查询的结果当做子查询使用
-- select ename, job, sal, deptno
-- from emp
-- where job in (
-- SELECT DISTINCT job
-- FROM emp
-- WHERE deptno = 10
-- )
3.排除10号部门的员工
-- 3.排除10号部门的员工
#deptno != 10
完整代码实现
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) AND deptno != 10
运行
2. 临时表
在mysql中，可以将子查询的结果当做一张临时表来使用
使用案例
查询ecshop中各个类别中，价格最高的商品
分析
- 查询ecshop中各个类别中,价格最高的商品
- 查询商品表
- 先得到各个类别中,价格最高的商品 max + group by cat_id,当做临时表
- 把子查询当做一张临时表,可以解决很多复杂查询
代码
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price
FROM (
SELECT cat_id , MAX(shop_price) AS max_price
FROM ecs_goods
GROUP BY cat_id
) AS temp , ecs_goods
WHERE temp.cat_id = ecs_goods.cat_id
AND temp.max_price = ecs_goods.shop_price
运行
3. all 与 any
all 操作符使用案例
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
代码
方法1:使用all操作符
使用all操作符
SELECT ename, sal, deptno
FROM emp
WHERE sal > ANY (
SELECT sal
FROM emp
WHERE deptno = 30
);
方法2：使用 max 操作符
使用 max 操作符
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MAX(sal)
FROM emp
WHERE deptno = 30
);
运行
any 操作符使用案例
显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
分析：只要比其中一个人的工资高就满足
代码
方法1：使用 any 操作符
使用 any 操作符
SELECT ename, sal, deptno
FROM emp
WHERE sal > ANY(
SELECT sal
FROM emp
WHERE deptno = 30
);
方法2：使用 min 操作符
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MIN(sal)
FROM emp
WHERE deptno = 30
);
运行
4. 多列子查询
多列子查询是指查询返回多个列数据的子查询语句
基本语法
(字段1, 字段2...) = (select 字段1, 字段2 from....)
多列子查询使用案例
查询与allen的部门和岗位完全相同的所有雇员（并且不含smith本人）
分析：
- 得到allen的部门和岗位
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN'
- 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
完整代码
SELECT *
FROM emp
WHERE (deptno,job) = (
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN';
运行
使用案例2
在student表中查询 : 和宋江数学、英语、语文成绩完全相同的学生
分析
1.先得到宋江的数学、英语和语文成绩
SELECT math,english,chinese
FROM student
WHERE `name` = '宋江'
2.把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
FROM student
WHERE (math, english, chinese) = (
SELECT math,english,chinese
FROM student
WHERE `name` = '宋江'
);
运行
5. 子查询综合练习
题目1
查询每个部门工资高于本部门平均工资的人的资料
分析
1.先得到每个部门的部门号和对应的平均工资
SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
2.把上面的结构当做子查询,和emp进行多表查询
完整代码
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp,(
SELECT deptno,AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) AS temp
WHERE emp.deptno = temp.deptno
AND emp.sal > temp.avg_sal
运行
题目2
查找每个部门工资最高的人的详细资料
分析
1.先找出每个部门的最高工资
SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno
2.把上面的结构当做子查询,进行多表查询
完整代码
SELECT *
FROM emp,(
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
) AS temp
WHERE emp.deptno = temp.deptno
AND emp.sal = temp.max_sal
运行
题目3
查询每个部门的信息(包括:部门名,编号,地址)和人员数量
分析
1.先得到每个部门有多少人
SELECT deptno,COUNT(*) AS emp_num
FROM emp
GROUP BY deptno
2.把上面的结构当做子查询,进行多表查询
完整代码
SELECT dept.deptno,dname,loc, emp_num
FROM dept,
(
SELECT deptno,COUNT(*) AS emp_num
FROM emp
GROUP BY deptno
) AS temp
WHERE temp.deptno = dept.deptno
运行
注意：
- 还有一种写法，表.* 表示将该表所有列都显示出来m可以简化sql
- 在多表查询中，当多个表的列不重复时，才可以直接写列名
● 合并查询
合并查询：
有时在实际应用中，为了合并多个select语句的效果，可以使用集合操作符号 union 、 union all
○ union 与 union all
union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
使用案例
有两条语句：
SELECT ename,sal,job FROM emp WHERE sal>2500
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'
第一条语句返回5行数据，第二条语句返回3行数据，如果我想
得到它们两条数据合并的结果，可以使用union all 来实现
代码
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'
结果
但是从上面的8行数据可以看到，有重复的数据，由此可见union all的缺点是只能合并数据，而不能去重，所以想得到去重的结果，得用另一个合并查询的关键字 ： union
union
该操作符与union all相似，但是会自动去掉结果集中重复行
实际操作
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'
运行结果
可以看到上面的8条结果精炼为了6行结果。
● 外连接
前面我们是利用 where 子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
如果我现在想列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门，又该如何实现？
我们使用where语句查询，可以得到10-30部门的人员情况，但是第40部门虽然存在，但是无人的情况，使用where是查询不到的，那如果我想得到10-40部门的情况，就可以使用外连接的方式来实现，
左右外连接
- 左外连接
如果左侧的表完全显示，我们就说是左外连接
- 右外连接
如果右侧的表完全显示，我们就说是右外连接
左右外连接的基础语法
左连接
select .. from 表1 left join 表2 on 条件
[表1:左表 表2:右表]
右链接
select .. from 表1 right join 表2 on 条件
[表1:左表 表2:右表]
以下面的两张表作为使用案例
条件1
使用左连接（显示所有人的成绩，如果 没有成绩，也要显示该人的姓名和id号，成绩显示为空）
代码
SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
运行
条件2
使用右外连接（显示所有成绩，如果没有名字匹配，显示空）
代码
SELECT `name`, stu.id, grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
运行
综合练习
列出部门名称和这些部门的员工信息（名字和工作），同时列出那些没有员工的部门。（使用左右链接实现）
代码
左连接实现
SELECT dname,ename,job
FROM dept LEFT JOIN emp
ON emp.deptno = dept.deptno
右链接实现
SELECT dname,ename,job
FROM emp RIGHT JOIN dept
ON emp.deptno = dept.deptno
效果
八、 约束
约束是指用于确保数据库的数据满足特定的商业规则。
在MySQL中，约束包括：not null（非空）、unique（唯一）、primary key（主键）、foreign key 和 check 以及自增长。
约束总纲
● primary key（主键）
primary key（主键）：用于唯一的标示表行的数据，当定义主键约束后，该列不能重复
语法
字段名 字段类型 primary key
使用演示
创建一张新表把id设置为主键
CREATE TABLE t17
(
id INT PRIMARY KEY, -- 表示id为主键
`name` VARCHAR(32),
email VARCHAR(32)
);
此时我往表中添加两条数据：
INSERT INTO t17
VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
VALUES(2,'tom','tom@sohu.com');
效果
添加完两条数据后，我还想往里再添加一条数据：
INSERT INTO t17
VALUES(1,'coco','coco@sohu.com');
执行完这句话后就会报错，因为表中的id为主键，而我已经添加了id为1的jack报错信息提示我不能再添加一条相同的数据，所以会报错。
primary key的使用细节
- primary key 不能重复而且不能为 null
- 一张表最多只能有一个主键，但可以是符合主键
- 主键的指定方式有两种
① 直接在字段后指定：字段名 primary key
② 在表定义最后写 primary key(列名) - 使用 desc 表名，可以看到primary key 的情况
使用细节的解释
- primary key 不能重复而且不能为 null
设置id为主键
INSERT INTO t17
VALUES(NULL,'tom','tom@sohu.com');
因为前面设置过id为主键，添加NULL值则会报错，因为主键不能重复且不能为null
- 一张表最多只能有一个主键，但可以是复合主键
演示复合主键 （id + name）
CREATE TABLE t20
(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (id, `name`) -- 复合主键
)
这里的 id 与 name就成为了复合主键。
注意：
如果我添加了（1,‘tom’,‘tom@sohu.com’）与（1，‘jack’,‘jak@sihu,com’），我还可以再添加（1,‘coco’,‘coco@sohu.com’），但是添加（1,‘tom’,‘xxx@sohu.com’）就不行，因为我定义的复合主键是id与name，当id与name都相同的时候，才会报错，否则只要有一个值不同就可以添加进去。
3.主键的指定方式有两种
① 直接在字段后指定：字段名 primary key
② 在表定义最后写 primary key(列名)
直接在字段后指定：字段名 primary key
CREATE TABLE t20
(
id INT,
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32),
);
在表定义最后写 primary key(列名)
CREATE TABLE t20
(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (`name`)
);
- 使用 desc 表名，可以看到primary key 的情况
desc查看表的且过，显示约束的情况
DESC t20; -- 查看t20表的结果,显示约束情况
效果
● not null（非空）
如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
语法
字段名 字段类型 not null
● unique（唯一）
当定义了唯一约束后，该列值是不能重复的。
语法
字段名 字段类型 unique
使用示范
CREATE TABLE t32
(
id INT UNIQUE, -- 表示id列不能重复
`name` VARCHAR(32),
email VARCHAR(32),
);
现在添加两条语句
INSERT INTO t17
VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
VALUES(1,'tom','tom@sohu.com');
运行过后会报错，原因是id重复了，因为unique字段修饰过后不能有重复值。
primary key 与 unique的区别
按理说primary key的作用也是不能重复，那么unique与primary key的区别在哪呢？区别就在：primary key不能存入非空的值，但是unique却可以，可以存入一个或多个null值。
unique 使用细节：
-
如果没有指定not null，则unique 字段可以有多个null
-
如果一个列（字段），是unique not null，使用效果类似primary key
-
一张表可以有多个unique字段（primary key 只能存在一个）
● foreign key（外键）
用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null
语法
FOREIGN KEY (本表字段名) REFERENCES
主表名(主键名或unique字段名)
外键约束介绍
现在有两张表，一张学生表，一张班级表
学生表
班级表
- 如果要求每个学生所在的班级号 class_id 是存在的班级编号（在班级表id字段必须有相同的值），就可以把class_id
做成外键约束。 - 但如果再添加一条字段：（‘sn_003’,‘coco’,300），这条字段就会添加失败（因为学生表class_id=300不能在班级表找到相同的id）
- 如果我想删掉班级表的其中一条数据（200,‘web2’,‘上海’），是不被允许的，因为有了外键的约束，必须先删除学生表class_id =
200的那行数据才可以删除班级表id 为200的数据。
使用案例演示
创建 主表 my_class
CREATE TABLE my_class
(
id INT PRIMARY KEY, -- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
创建 从表 my_stu
CREATE TABLE my_stu
(
id INT PRIMARY KEY, -- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',
# 此时的class_id 就等于外键
class_id INT, -- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id)
);
现在向主表添加两条数据
INSERT INTO my_ class
VALUES(100,'java'),(200,'web');
再向从表添加两条数据
INSERT INTO my_ stu
VALUES(1,'tom',100);
INSERT INTO my_ stu
VALUES(2,'jack',200);
从上面到次为止的代码运行都没有任何问题，但是如果我再想向从表添加一条额外的数据
INSERT INTO my_ stu
VALUES(3,'coco',300);
此时由于主表并没有设置id为300的数据，所以从表的这张数据会添加失败
注意一个点：若我修改其中的一个值，重新插入这条字段
INSERT INTO my_ stu
VALUES(3,'coco',NULL);
如果外键字段允许为null，那么我新修改的这条数据可以插入进去，即使主表没有相同的字段；但如果主表中class_id 是由not
null修饰的，则不能加入空值
插入成功的演示
foreign key（外键）细节注意事项
- 外键指向的表的字段，要求是primary key 或者是unique。
- 表的类型是innodb，这样的表才支持外键。
- 外键字段的类型要和主键字段的类型一致（长度可以不同）
- 外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】
- 一旦建立主外键的关系，数据就不能随意删除了。
● check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000 ~ 2000之间不再1000 ~ 2000之间就会提示出错。
oracle 和 sql server 均支持check，但是mysql5.7不支持check，只做语法校验，但不会生效，这里只做了解，目前的8.0.16及以上版本的mysql支持check。
语法
列名 类型 check (check 条件)
user 表
id, name, sex(man,woman), sal(大于100 小于900)
mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成。
check 的使用演示
创建一张新表
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN('man','woman')),
sal DOUBLE CHECK (sal > 1000 AND sal < 2000)
);
添加数据
INSERT INTO t23
VALUES(1,'jack','mid',1);
运行效果
再次注意：
目前使用的是5.7.3的MySQL，所以目前还不支持check，这里仅仅是做了语法校验，但是并没有生效，这里只做了解，所以不符合规则的数据依然添加进了表中，check 在8.0及以上的MySQL中可以完整使用。
● 自增长
在某些情况下，需要在某张表中设置一个列（整数列），在添加记录的时候，该列从1开始，自动的增长，就有了新的字段，自增长。
语法
字段名 整型 primary key auto_increment
添加自增长的字段方式
insert into xxx (字段1 , 字段2…) values (null , ‘值’…);
insert into xxx (字段2…) values (‘值1’ , ‘值2’ …);
insert into xxx values (null , ‘值1’, …)
使用方法
INSERT INTO t23
VALUES (NULL,'tom@qq.com','tom');
INSERT INTO t23 (email,`name`)
VALUES ('jack@qq.com','jack');
自增长使用细节
- 一般来说自增长是和primary key配合使用的
- 自增长也可以单独使用【但是需要配合一个unique】
- 自增长修饰的字段为整数型的（虽然小数也可以但是极少这样使用）
- 自增长默认从1开始，也可以通过如下命令修改：
alter table 表明 auto_increment = 新的开始值;
- 如果添加数据时，给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据
● 本章重点及思维导图（☆）
primary key（主键）：唯一性，不能为空，一张表只能存在一个。
not null（非空）：必须插入数据。
unique（唯一）：唯一性，可以为空，一张表可以存在多个。
foreign key（外键）：外键约束：约束列的值在被约束列已存在
primary key 与 unique 的区别：
- primary key 不能为空，而unique 可以
- primary key 一张表只能存在一个，而unique 则可以存在多个
MySQL约束及表的综合设计案例
- 商店售货系统表设计案例
现有一个商店的数据库shop_db,记录客户及其购物情况,由下面三个表组成 ：
- 商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider）；
- 客户customer（客户号customer_id，姓名name，住址address，电邮email，性别sex，身份证card_id）；
- 购买purchase（购买订单号order_id，客户号customer_id，商品号goods_id，购买数量nums）；
建表，在定义中要求声明【进行合理设计】：
（1）每个表的主外键
（2）客户的姓名不能为空值
（3）电邮不能够重复；
（4）客户的性别【男 | 女】
（5）单价unitprice 在1.0 - 9999.99 之间check
1.商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider）
CREATE TABLE goods
(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice >1.0 AND unitprice < 9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT ''
);
2.客户customer（客户号customer_id，姓名name，住址address，电邮email，性别sex，身份证card_id）
CREATE TABLE customer
(
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(6) NOT NULL,
address VARCHAR(32),
email VARCHAR(32) UNIQUE NOT NULL,
sex VARCHAR(6) CHECK (sex IN('男','女')),
card_id CHAR(18)
);
3.购买purchase（购买订单号order_id，客户号customer_id，商品号goods_id，购买数量nums）
CREATE TABLE purchase
(
order_id INT PRIMARY KEY,
customer_id INT NOT NULL DEFAULT 0, -- 外键约束在后
goods_id INT NOT NULL DEFAULT 0, -- 外键约束在后
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);
九、索引
说起提高数据库性能，索引是最方便的东西了，不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍。
索引总纲
索引的原理
当我们想要查询一张表内的数据时，没有索引，就只能通过 select * from emp where id = 1来进行全表扫描，查询速度非常慢。
表t24
如果我想查询id为9的数据时，系统会从id = 1开始，从1查询到9，而我想查询id为1的数据时，即使第一个id就为1，但是系统还是会继续查找完9个id，效率非常低，而使用索引后效率为什么会变快？那是因为索引会形成一种数据结构，比如二叉树，用来查询，查询方式有点类似java中的二分查询法
索引的底层二叉树
索引查询的代价
- 磁盘占用
- 对dml（update、delete。insert）语句的效率影响
以上表为例，如果比较次数达到30次，那么覆盖的表范围高达 2^30，所以索引可以说是牺牲空间的方法来换来时间的减少，因此会对表有一定的影响，比如：对表进行dml（修改，删除，添加）会对索引进行维护，对速度有影响。
索引的类型
- 主键索引，主键自动的为主索引（类型Primary）
【（in int primary key,）主键，同时也是索引，称为主键即索引】 - 唯一索引（unique）
【（id int unique,）id是唯一的，同时也是索引，称为unique索引】 - 普通索引（index）
- 全文索引（fulltext）【MySQL自带，适用于MyISAM】
【一般开发，不使用自带的全文索引，而是使用：全文搜索Solr 和 ElasticSearch（ES）】
● 索引的使用
- 添加索引
create [unique] index index_name on tbl_name(col_name[(length)][ASC | DESC],....);
alter table table_name ADD index[index_name](index_col_name,...)
- 添加主键（索引）
alter table 表名 ADD primary key(列名,...);
- 删除索引
drop index index_name ON table_name;
alter table table_name drop index index_name;
- 删除主键索引
alter table t_b drop primary key;
○ 创建索引
查询表是否有索引
SHOW INDEXES FROM t25;
添加索引
添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
添加普通索引
方式1：
CREATE INDEX id_index ON t25 (id);
方式2：
ALTER TABLE t25 ADD INDEX id_index (id)
如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引。
添加主键索引
方式1：在创建表的时候设置
CREATE TABLE t25 (
id INT PRIMARY KEY,
`name` VARCHAR(32)
);
方式2：添加进去
ALTER TABLE t26 ADD PRIMARY KEY (id);
○ 删除索引
删除索引
DROP INDEX id_index ON t25;
删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY
○ 修改索引
先删除，再添加新的索引
查询索引
方式1：
SHOW INDEX FROM t25;
方式2：
SHOW INDEXES FROM t25;
方式3：
SHOW KEYS FROM t25;
方式4：
DESC t25;
● 小结
小结：在哪些列上适合使用索引
1. 较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = '男'
3. 更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1
4. 不会出现在WHERE子句中字段不该创建索引
十、 事务
事务总纲
○ 什么是事务
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。
○ 事务的运行机制
事务就像是一个单机游戏的流程，一个百分之百的游戏流程可以在不同的时间点，存不同的档，当你觉得过不去的时候，可以回滚到上一个或是从前的任何一个存档。
○ 事务和锁
当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户改表的数据。（这对用户来讲是非常重要的）
○ 回退事务
在介绍回退事务前，先介绍一下保存点（savepoint），保存点是事务中的点，用于取消部分事务，当结束事务时，会自动的删除该事物所定义的所有保存点。
当执行回退事务时，通过指定保存点可以回退到指定的点。
○ 提交事务
使用commit语句可以提交事务，当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其他会话将可以查看到事务变化后的新数据【所有数据就正式生效】。
● 事务基本使用
○ mysql 数据库控制台事务的几个重要基本操作
- start transaction 【开始一个事务】
- savepoint 保存点名 【设置保存点】
- rollback to 保存点名 【回退事务】
- rollback 【回退全部事务】
- commit 【提交事务，所有的操作生效，不能回退】
事务使用演示
-- 1. 开始事务
START TRANSACTION
-- 2. 设置保存点a
SAVEPOINT a
-- 3. 执行(dml)操作
INSERT INTO t27 VALUES(100,'tom');
SELECT * FROM t27;
-- 4. 设置保存点b
SAVEPOINT b
-- 5. 执行(dml) 操作
INSERT INTO t27 VALUES(200,'jack');
-- 6. 回退到b
ROLLBACK TO b
-- 7. 继续回退到a
ROLLBACK TO a
-- 8. 如果rollback后不加任何东西,表示直接回到事务初始的状态
ROLLBACK
-- 9. 如果在b点使用commit后就没有机会回退了
COMMIT
○ 事务使用细节
- 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚。
- 如果开始一个事务，你没有创建保存点，但可以执行rollback，默认就是回退到事务开始的状态【初始状态】。
- 这个事务中（还没有提交时），创建多个保存点。
比如： savepoint a; 执行dml ， savepoint b; - 事务在没有提交前，可以选择回退到哪个保存点。
- MySQL的事务机制需要innodb的存储引擎才可以使用，myisam不行。
【InnoDB 存储引擎支持事务，MsISAM不支持】 - 开始一个事务 start transaction，set autocommit = off ;
● 事务隔离级别
MySQL隔离级别定义了事务与事务之间的隔离程度。
注意： V可能出现，X不会出现。
事务隔离级别介绍
- 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
- 如果不考虑隔离性，可能会引发如下问题：
脏读
不可重复读
幻读
查看事务隔离级别
查看当前会话隔离级别 : SELECT @@ tx_isolation;
-
脏读（dirty read）：当一个事务读取另一个事务尚未提交的改变（update，insert，delete）时，产生脏读。
【别的人修改了但是还没提交这部分数据，我却拿到了别人修改后的数据】 -
不可重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
【事务的读取是开启事务时数据库的状态，但mysql2在开启的时候，数据库中并没有这两条记录，但此时mysql2中并没有commit，却读出来了mysql1修改的数据】 -
幻读（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
【事务的读取是开启事务时数据库的状态，但mysql2在开启的时候，数据库中并没有这两条记录，但此时mysql2中并没有commit，却读出来了mysql1插入的数据】
○ 隔离级别的设置语法
1.查看当前会话隔离级别
select @@tx_isolation;
2.查看系统当前隔离级别
select @@global.tx_isolation
3.设置当前会话隔离级别
set session transaction isolation level repeatable read;
4.设置系统当前隔离级别
set global transaction isolation level 【你需要的级别】;
5.mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足绝大部分项目要求）
○ 修改事务级别
全局修改
修改my.ini配置文件，在最后加上【transaction-isolation = 你要的级别参数】，可选的参数有：
READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ，SERIALIZABLE。
修改演示
mysql事务隔离级别使用案例
举一个案例来说明mysql的事务隔离级别，以对account表进行操作为例。（id，name，money）
○ 读未提交（Read uncommitted）
开起两个mysql的控制台，分别为mysql1和mysql2，
查看当前mysql的隔离级别：
mysql > SELECT @@ tx_isolation;
此时mysql1的情况
+-------------------+
| @@ tx_isolation |
+-------------------+
| REPEATABLE-READ |
+-------------------+
把其中一个控制台(mysql2)的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
mysql2的级别：
+-------------------+
| @@ tx_isolation |
+-------------------+
| READ-UNCOMMITTED |
+-------------------+
创建表
CREATE TABLE `account` (
id INT ,
`name` VARCHAR (32),
money INT
);
在mysql1中插入数据(此时并未提交)
INSERT INTO account VALUES(100.'tom',1000);
此时mysql2中查看到的数据：
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 1000 | -- 脏读
+-------------------+
此时mysql1中的信息未上传,但在mysql2中却能查看到mysql1中的信息，这种情况就为脏读
现在修改mysql1中的数据并提交
UPDATE account SET money = 800 WHERE id =1000;
INSERT INTO account VALUES(200,'jack',2000);
COMMIT;
此时在mysql2中查看到了mysql1中插入与修改的两条数据
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 800 | -- 不可重复读
| 200| jack | 2000 | -- 幻读
+-------------------+
事务的读取是开启事务时数据库的状态，mysql2在开启的时候，数据库中并没有这两条记录，但此时mysql2中却读出来了mysql1的数据，这就说明修改的数据（money = 800） 就是不可重复读，而插入的数据（200，jack，2000）是幻读。
综上所述，使用读未提交级别的数据库会发生三种问题：脏读、不可重复读与幻读
○ 读已提交（Read committed）
修改mysql2的级别为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql > SELECT @@ tx_isolation;
+------------------+
| @@ tx_isolation |
+------------------+
| READ-COMMITTED |
+------------------+
在mysql1中加入记录
INSERT INTO account VALUES(300,'scott',8000);
此时mysql1中的情况：
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 800 |
| 200| jack | 2000 |
| 300| scott| 8000 |
+-------------------+
这时在mysql2中查看到的数据：
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 800 |
| 200| jack | 2000 |
+-------------------+
这时并没有像上一个级别那样，出现脏读的情况。
继续在mysql1中修改记录
UPDATE account SET money = 1800 WHERE id = 200;
此时mysql2中的情况为：
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 800 |
| 200| jack | 1800 | -- 不可重复读
| 300| scott| 8000 | -- 幻读
+-------------------+
此时的mysql2并未提交，但从上面的库中可以看出发生了不可重复读（修改）与幻读（插入）的情况。
所以综上所述，读已提交级别的数据库使用时会造成两种情况：不可重复读与幻读。
○ 可重复读（Repeatable read）
修改mysql2为可重复读级别数据库
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
mysql2的级别：
+------------------+
| @@ tx_isolation |
+------------------+
| REPEATABLE-READ |
+------------------+
在mysql1中插入与修改数据
INSERT INTO account VALUES(400,'milan',6000);
UPDATE account SET money = 100 WHERE id = 300;
此时mysql1的表中共有4条数据
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 800 |
| 200| jack | 1800 |
| 300| scott| 100 |
| 400| milan| 6000 |
+-------------------+
但在此时mysql2中的数据依旧是原来的3条,并没有受到mysql1的影响，而即使mysql1提交(commit)过后mysql2也是原来的3条数据，此时可以确定并没有发生脏读、不可重复读与幻读
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 800 |
| 200| jack | 1800 |
| 300| scott| 2800 |
+-------------------+
综上所述：可重复读级别的库并不会发生脏读、不可重复读与幻读
○ 可串行化(Serializable)
修改mysql2为可串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
此时mysql2的情况：
+------------------+
| @@ tx_isolation |
+------------------+
| SERIALIZABLE |
+------------------+
在mysql1中修改数据(不提交)
INSERT INTO account VALUES(500,'terry',80000);
UPDATE account SET money = 900 WHERE id = 300;
这时mysql1的数据并未提交,在mysql2中查询数据时会卡在控制台的查询界面上
发生这种情况的原因是因为mysql2已经修改为可串行化的级别，而可串行化与可重复读的区别是：可串行化有锁，在mysql1并未提交前，库被锁上了，导致mysql2无法查看，直到mysql1提交过后，mysql2才能看到mysql1提交后的库
mysql1提交后mysql2库查询到的情况
+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom | 800 |
| 200| jack | 1800 |
| 300| scott| 900 |
| 400| milan| 6000 |
| 500| terry| 80000 |
+-------------------+
综上所述，可串行化同可重复读一样。不会出现脏读、不可重复读与幻读的情况，两者最大的区别就是锁，可重复读是无锁的。
● ACID
事务的acid特性
- 原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另一个一致性状态。
- 隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
- 持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
十一、 存储引擎
基本介绍
- MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
- MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。
- 这六种又分为两类，一类是"事务安全型"（transaction-safe），比如：InnoDB；其余都属于第二类，称为"非事务安全型"（non-transaction-safe）【mysiam 和 memory】。
查看MySQL中所有存储引擎
SHOW ENGINES;
主要的存储引擎 / 表类型特点
重点介绍三种引擎：MyISAM、InnoDB、MEMORY
- MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。
- InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
- MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。
INNODB 存储引擎
特点：
1.支持事务
2.支持外键
3.支持行级锁
MYISAM 存储引擎
特点：
1.添加速度快
2.不支持外键和事务
3.支持表级锁
MEMORY 存储引擎
特点：
1.数据存储在内存中【关闭了mysql服务，数据丢失，但是表结构还在】
2.执行速度很快（没有IO读写）
3.默认支持索引（hash表）
引擎的选择
- 如果应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快。
- 如果需要支持事务，就选择InnoDB。
- Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态）
修改存储引擎
语法
ALTER TABLE `表名` ENGINE = 存储引擎;
十二、视图（view）
什么是视图？
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
视图的优点
○ 安全。 一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样用户就可以查询自己需要的字段，不能查看保密的字段。
○ 性能。 关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低 。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
○ 灵活。 如果系统中有一张旧表，这张表由于设计的问题，即将被废弃。然而很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
视图
- 视图是根据基（可以是多个基表）表来创建的 视图是虚拟的表
- 视图也有列，数据来自基表
- 通过视图可以修改基表的数据
- 基本的改变，也会影响到视图的数据
视图的基本使用
- create view 视图名 as select 语句
- alter view 视图名 as select 语句
- SHOW CREATE VIEW 视图名
- drop view 视图名1，视图名2
视图使用演示
创建并查看视图
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno,ename,job,deptno FROM emp;
-- 查看视图结构
DESC emp_view01;
效果
查看视图
SELECT * FROM emp_view01;
效果
只查看其中两列
SELECT empno , job FROM emp_view01;
效果
查看创建视图的指令
SHOW CREATE VIEW emp_view01;
效果
试图使用细节
- 创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.from）
- 视图的数据变化会影响到基表，基表的数据变化也会影响到视图【insert update delete】
- 视图中可以再使用视图，数据仍然来自基表。
细节演示
-- 针对前面的雇员管理系统
mysql > create view myview as select empno,ename,job,comm from emp;
mysql > select * from myview;
//修改视图,对基表都有变化
mysql > update myview set comm = 200 where empno = 7369;
//修改基表,对视图也有变化
mysql > update emp set comm = 100 where empno = 7369;
视图使用案例
针对emp，dept 和 salgrade 三张表，创建一个视图emp_view03，可以显示雇员编号，雇员名，雇员部门名称和薪水级别【即使用三张表，构建成一个视图】
分析：
使用三表联合查询，得到结果，
将得到的结构构成视图
-- 创建三表构成的视图
CREATE VIEW emp_view03
AS
SELECT empno, ename, dname, grade
FROM emp,dept,salgrade
WHERE emp.deptno = dept.deptno
AND (sal BETWEEN losal AND hisal);
SELECT * FROM emp_view03;
十三、MySQL用户管理
○ 用户管理
当我们做项目开发时，可以根据不同的开发人员，赋给他们相应的MySQL操作权限，所以MySQL数据库管理人员（root），根据需要创建不同的用户，赋给相应的权限，供人员使用
○ MySQL用户
mysql中的用户，都存储在系统数据库mysql的user表中
其中user表的重要字段说明：
- host： 允许登录的"位置"， localhost表示该用户只允许本机登录，也可以指定IP地址，比如：192.168.1.100【只能从这个ip登录】
- user： 用户名；
- authentication_string：密码，是用过mysql的password（）函数加密之后的密码。
○ 创建用户
创建用户，同时指定密码。
create user '用户名' @ '允许登录位置' identified by '密码'
○ 删除用户
drop user '用户名' @ '允许登录位置';
不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样
○ 用户修改密码
修改自己的密码：
set password = password('密码');
修改他人的密码（需要有修改用户密码权限）：
set password for '用户名' @ '登录位置' = password('密码');
使用演示
（1）创建新的用户
- ‘coco @ localhost’ 表示用户的完整信息，'coco’是用户名，'localhost’是登录的IP
- 123456 是密码，但是注意：存放到mysql.user表时，是password（‘123456’）加密后的密码
CREATE USER 'coco'@'localhost' IDENTIFIED BY '123456';
（2）删除用户
DROP USER 'coco'@'localhost';
（3）修改自己密码
set password = password('abcde');
○ MySQL中的权限
○ 给用户授权
基本语法
grant 权限列表 on 库.对象名 to '用户名' @ '登录位置' 【identified by '密码'】
说明：
1. 权限列表，多个权限用逗号分开
grant select on .....
grant select, delete,create no ....
grant all 【privileges】 on .... //表示赋予该用户在该对象上的所有权限
2. 特别说明
*.* : 代表本系统中的所有数据库的所有对象(表,视图,存储过程)
库.* : 表示某个数据库中的所有数据对象(表,视图,存储过程等)
3. identified by可以省略，，也可以写出
①.如果用户存在，就是修改该用户的密码。
②.如果该用户不存在，就是创建该用户。
○ 回收用户授权
基本语法：
revoke 权限列表 on 库.对象名 from '用户名' @ "登录位置";
○ 权限生效指令
如果权限没有生效，可以执行下面命令：
基本语法：
FLUSH PRIVILEGES;
用户管理练习
- 创建一个用户名，密码 123，并且只可以从本地登录，不让远程登录mysql
- 创建库和表 testdb 下的news 表，要求：使用root用户创建
- 测试看用户是否只有这几个权限
- 修改密码为abc，要求：使用root用户完成
- 重新登录
- 使用root用户删除你创建的用户
代码实现
-- 创建用户
CREATE USER 'COCO'@'localhost' IDENTIFIED BY '123';
-- 使用root用户创建testdb,news表
CREATE DATABASE testdb
CREATE TABLE news (
id INT,
content VARCHAR(32)
);
-- 添加一条测试数据
INSERT INTO news VALUES(100,'北京新闻');
SELECT * FROM NEWS;
-- 给coco分配查看news表和添加news的权限
GRANT SELECT , INSERT
ON testdb.news
TO 'coco'@'localhost'
-- 增加update权限
GRANT UPDATE
ON testdb.news
TO 'coco'@'localhost'
-- 修改coco的密码为abc
SET PASSWORD FOR 'coco'@'localhost' = PASSWORD('abc');
-- 回收coco用户在testdb.news表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'coco'@'localhost';
REVOKE ALL ON testdb.news FROM 'coco'@'localhost';
-- 删除coco
DROP USER 'coco'@'localhost';
用户管理使用细节
- 在创建用户的时候，如果不指定Host，则为 %，%表示所有IP都有链接权限
- 也可以指定：
create user 'xxx'@'192.168.1.%' 表示 xxx 用户在192.168.1.*的ip可以登录mysql
- 在删除用户的时候，如果host不是%需要明确指定’用户@host值’
演示
-- smith 用户在192.168.1.*的ip可以登录mysql
CREATE USER 'smith'@'192.168.1.%'
-- 在删除用户的时候,如果host不是%,需要明确指定'用户'@'host值'
DROP USER smith -- 默认就是drop user 'smith'@'%'
--删除smith
DROP USER 'smith'@'192.168.1.%'
712
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
