MySQL大全（基础篇）_mysql >

本篇MySQL总纲

一、 MySQL基本介绍

什么是数据库？

为了避免数据丢失、遗忘，人们创为了储存文件和数据，造出了一个储存数据的类库——数据库。

数据库三层结构

1. 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。DBMS（database manage system）
2. 一个数据库中可以创建多个表，以保存数据（信息）。
3. 数据库管理系统（DBMS）、数据库和表的关系如图：

4. MySQL数据库——普通表的本质任然是文件

SQL语句分类

• DDL：数据定义语句 [create 表 ，库…]
• DML：数据操作语句 [增加（insert），修改（undate），删除 （delate）]
• DQL：数据查询语句 [select]
• DCL：数据控制语句 [管理数据库：比如用户权限（grant、revoke）]

连接到Mysql服务（Mysql数据库）的指令

mysql -h 主机IP -P 端口 -u 用户名 -p密码
1

注意：

1. -p密码不能有空格
2. -p后面没有写密码，回车会要求输入密码
3. 如果没有写-h 主机，默认是本机
4. 如果没有写-P 端口，默认是3306

二、数据库

1. 创建数据库

1. CHARACTER SET : 指定数据库采用的字符集，如果不指定字符集，默认 utf8

2. COLLATE : 指定数据库字符集的校对规则（常用的utf9_bin【区分大小写】、utf8_general_ci【不区分大小写】，注意默认是 utf8_general_ci）

3. 创建数据库指令：
   CREATE DATEBASE 库名;

4. 创建一个使用utf8字符集的数据库
   CREATE DATEBASE 库名 CHARACTER SET utf8

5. 创建一个使用utf8字符集，并带校对规则的数据库（区分大小写）
   CREATE DATEBASE 库名 CHARACTER SET utf8 COLLATE utf8_bin

6. 注意: 在创建数据库表的时候，为了规避关键字，可以使用反引号解决

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】

1. 如果没有指定unsigned ， 则TINYINT 就是有符号【-128 ~ 127】

create table t1(id tinyint); //默认是有符号的

2. 如果指定 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;                    
1
2
3
4
5
6
7
8
9

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. 注意事项1

• char(4) // 这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放四个，按字符计算。
• varchar(4) // 这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据。
• 上面的两种4 都代表，不管是中文还是英文字母，都是最多存放4个，因为他是按照字符来存放的。

2. 注意事项2

• char(4)是定长（固定的大小），也就是说，即使你插入 ‘aa’，也会占用分配的4个字符的空间。

• varchar(4)是变长（变化大小），就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（varchar本身还需要占用1-3个字节来记录存放内容长度）。 L（实际数据大小）+ （1-3）字节

3. 注意事项3

什么时候使用char，什么时候使用varchar？
①. 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等，char（32）
②. 如果一个字段的长度是不确定，则使用varchar，比如留言，文章等等
③. 查询速度：char > varchar

4. 注意事项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时间戳
1
2
3
4
5
6

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              日期
1
2
3
4
5
6

代码实现

CREATE TABLE 'user'(
         id INT,
         `name` VARCHAR(255),
         `password` VARCHAR(255),
         `birthday` DATE
) CHARACTER SET utf8 CCOLLATE utf8_bin ENGINE INNODB;
1
2
3
4
5
6

创建表练习

创建一个员工表 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,'大王叫我来巡山')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

运行结果：

● 修改表

修改表应用实例

查看初始表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;

1
2
3
4
5
6
7
8
9
10
11
12
13

修改完成，查询新表employee

○ 复制

自我复制数据（蠕虫复制）

有时，为了对某个 sql 语句进行效率测试，我们需要海量数据，可以使用此法为表创建海量数据。

自我复制使用案例

创建一张新表

CREATE TABLE fz_tab01
		( 
		   id INT,
	 	  `name` VARCHAR(32),
	 	  sal DOUBLE,
	  	 job VARCHAR(32),
		   deptno INT
		);
1
2
3
4
5
6
7
8

创建效果

演示如何自我复制

1.把 emp 表的记录复制到 fz_tab01

INSERT INTO fz_tab01
		(id, `name`, sal, job, deptno)
		SELECT empno,ename,sal,job, deptno FROM emp;
1
2
3

复制结果

2. 自我复制

把 fz_tab01 表的所有数据复制到 fz_tab01 表中

INSERT INTO fz_tab01
		SELECT * FROM fz_tab01;
1
2

3.复制表结构

CREATE TABLE fz_tab02 LIKE emp;
1

这个语句的作用是：把emp表的结构（列），复制到 fz_tab02 中

复制效果

○ 表去重

使用蠕虫复制连续复制两次 tab02 表，得到26条记录，现在需要把多余的数据去除，得到一张13行记录的表。

重复的 fz_tab02表

分析思路：

1. 先创建一张临时表 my_tmp ，该表的结构和 fz_tab02 一样
2. 把 fz_tab02 的记录通过 distinct 关键字处理后，把记录复制到 my_tmp
3. 清除掉 fz_tab02 记录
4. 把 my_tmp 表的记录复制到 fz_tab02
5. drop 掉临时表 my_tmp

代码实现

1. 先创建一张临时表 my_tmp ，该表的结构和 fz_tab02 一样

CREATE TABLE my_tmp LIKE fz_tab02
1

2. 把 mfz_tmp 的记录通过 distinct 关键字处理后，把记录复制到 fz_tmp

INSERT INTO my_tmp
		SELECT DISTINCT * 
		FROM fz_tab02
1
2
3

3. 清除掉 fz_tab02 记录

DELETE FROM fz_tab02
1

4. 把 my_tmp 表的记录复制到 fz_tab02

INSERT INTO fz_tab02
		SELECT * 
		FROM my_tmp
1
2
3

5. drop 掉临时表 my_tmp

DROP  TABLE my_tmp
1

得到最终去重的表：fz_tab02

SELECT * FROM fz_tab02
1

运行效果截图

五、CRUD语句

数据库CRUD语句

1. 数据库C[创建、添加] R[读取] U[修改] D[删除]
2. 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
1
2
3
4
5

代码实现

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);	
1
2
3
4
5
6
7
8
9
10
11

运行结果

Insert语句注意事项

1. 插入的数据应与字段的数据类型相同。
   比如把 ‘abc’ 添加到 int 类型会错误。

2. 数据的长度应在列的规定范围内。
   例如：不能将一个长度为80的字符串加入到长度为40的列中。

3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。

4. 字符和日期类型数据应包含在单引号中。

5. 列可以插入空值【前提是该字段允许为空】
   nsert into table value(null)

6. insert into tab_name(列名…) values (),(),() 形式添加多条记录

7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称

8. 默认值的使用，当不给某个字段时，如果有默认值就会添加默认值，否则报错，如果某个列没有指定 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);
1
2

• 第七条 如果是给表中的所有字段添加数据，可以不写前面的字段名称

还是以goods表为例，如果我添加新的数据：编号4，名字为诺基亚，价格为2000，那么就可以省略字段的名称，例如：

INSERT INTO `goods`
           values(4,'诺基亚',2000);
1
2

但注意一个点：如果我添加的数据数量与字段最大数量不符，多一条或少一条数据，都会导致运行报错。

● 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;
1
2
3
4
5
6
7
8
9

update语句注意事项：

1. UPDATE 语句可以用新值更新原有表行中的各列。
2. SET子句指示要修改哪些列和要给予哪些值。
3. WHERE子句指定应更新哪些行。如果没有where子句，则更新所有的行（记录），因此一定要小心使用。
4. 如果需要修改多个字段，可以通过 set 字段1 = 值1，字段2 = 值2…

● delete语句 (删除数据)

delete语句基本语法

delete语句使用案例

代码实现

-- 删除表中名称为 '老妖怪' 的记录
DELETE FROM employee WHERE user_name = '老妖怪';
-- 删除表中所有记录,一定要小心使用
DELETE FROM employee ;
1
2
3
4

delete语句使用注意事项

1. 如果不使用where子句，将删除表中所有数据（注意使用！！！）
2. Delete语句不能删除某一列的值（可使用update 设为 null 或者 ’ '）
3. 使用delete语句仅能删除记录，不能删除表本身。如果要删除表，则需使用drop table语句（drop table 表名;）

● select语句 （☆）

1. select基础与distinct（去重）

基本语法

select语句注意事项：

1. Select指定查询哪些列的数据。
2. column指定列名。
3. 号代表查询所有列。
4. From指定查询哪张表
5. distinct可选，指显示结果时，是否去掉重复数据。

select语句使用案例1

学生表的创建

代码实现

-- 查询表中所有学生的信息。
SELECT * FROM student;

-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;

-- 过滤表中重复数据 distinct。
#要查询的记录,每个字段都相同,才会去重
SELECT DISTINCT english FROM student;
1
2
3
4
5
6
7
8
9

注意：要查询的记录,每个字段都相同,才会去重，比如说查询english时发现有两个78和98，那么就会去掉相同的78和98，效果如下所示：

去重前

去重后

但如果再添加名字的话，就是对比两条数据，此时虽然说78与98都有重复，但是由于前面的name是不同的人名，不满足每个字段都相同,所以并不会去重

添加name的查重

2. select运算与as语句（改名）

基本语法

select语句2使用案例

代码实现
1.统计每个学生的总分

-- 1.统计每个学生的总分
SELECT `name`, (chinese + english + math) FROM student;
1
2

运行效果

2.查看所有学生总分加10的情况

-- 2.查看所有学生总分加10的情况
SELECT `name`,(chinese + english + math + 10) FROM student;
1
2

运行结果

3.使用别名表示学生分数。

-- 3.使用别名表示学生分数。
SELECT `name` AS '名字',(chinese + english + math) AS '总分' FROM student;
1
2

运行结果

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;
1
2
3
4
5
6
7
8
9

运行结果

where子句使用案例2

使用where子句,进行过滤查询
1.查询math大于60并且(and)id大于4的学生成绩。
2.查询英语成绩大于语文成绩的同学。 
3.查询总分大于200分并且数学成绩小于语文成绩的姓赵的学生。

1
2
3
4
5

代码实现

--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 '韩%'; 	 	     
1
2
3
4
5
6
7
8
9
10
11

注意：查找名字姓氏使用 like ， （like 姓或名） 如果是’赵%‘，就近似查找姓赵的人，若是%在前：’%赵’，则代表查找最后一个字是赵的人。

where子句使用案例3

1.查询英语分数在 80 - 90 之间的同学。
2.查询数学分数为 89, 90, 91的同学。
3.查询所有姓李的学生成绩。
4.查询数学分 > 80, 语文分 > 80 的同学。
1
2
3
4

代码实现

--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;
1
2
3
4
5
6
7
8
9
10
11

4. order by子句（排序）

order by : 排序查询结果

Asc升序【默认】、Desc 降序

基础语法

注意：

1. Order by 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
2. Asc升序【默认】、Desc 降序
3. 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

六、 函数

● 合计函数 与 统计函数

○ 统计函数（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;
1
2
3
4
5
6
7
8
9
10

注意：

count(*) 与 count(列) 的区别
count(*) : 返回满足条件的记录的行数
count(列) : 统计满足条件的某列有多少个,但是会排出NULL的情况
1
2
3

例如：

创建一个新表 t05

3个值，一个空值

count (*)

SELECT COUNT(*) FROM t05;
1

count (列)

SELECT COUNT(`name`) FROM t05;
1

○ 合计函数（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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

○ 合计函数（avg）

合计函数 avg 基础语法

avg使用案例

代码实现

-- 求一个班级数学平均分
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + chinese + english) FROM student;
1
2
3
4

○ 合计函数（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;
1
2
3
4
5
6

○ 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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

员工便的创建

-- 创建员工表
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

工资级别表的创建

-- 创建工资级别表

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

代码实现

-- 显示每个部门的平均工资和最高工资
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

● 字符串函数

字符串函数相关语法

字符串相关函数使用案例

○ charsert（返回字符串字符集）

1. CHARSET(str) 返回字串字符集 （常用）

SELECT CHARSET(ename) FROM emp;
1

○ concat（链接字符串）

2. CONCAT (string2 [,…]) 链接字符串,将多个列拼接成一列 （常用）

SELECT CONCAT(ename,' 工作是 ',job) FROM emp;
1

○ instr(string,substring)

3. INSTR (string ,substring) 返回substring在string中出现的位置,没有返回0

SELECT INSTR('tong','g') FROM DUAL;
1

○ ucase（转换成大写）

4. UCASE (string2) 转换成大写 （常用）

SELECT UCASE(ename) FROM emp;
1

○ lacase（转换成小写）

5. LACASE(string2) 转换成小写 （常用）

SELECT LCASE(ename) FROM emp;
1

○ left（左起取字符）

6. LEFT(string2,length) 从string2中的左边起取length个字符

SELECT LEFT(ename,2) FROM emp;
1

○ right（右起取字符）

7. RIGHT(string2,length)从string2中的右边起取length个字符

SELECT RIGHT(ename,2) FROM emp;
1

○ length（查长度）

8. LENGTH(string) string长度[按照字节] （常用）

#亚元表,系统自带表
SELECT LENGTH('tong'),LENGTH('陈') FROM DUAL;
1
2

○ replace（替换）

9. REPLACE(str,search_str,replace_str) 在str中用replace_str替换search_str （常用）

SELECT REPLACE(job,'MANAGER','经理') FROM emp;
1

○ strcmp（比字符串大小）

10. strcmp(string1,string2) 逐字符比两字符串大小

SELECT STRCMP('tong','tong') FROM DUAL;
1

○ substring（取字符）

11. SUBSTRING(str,position[length]) 从str的position开始【从1开始计算】,取length个字符 （常用）

# 从ename列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename,1,2) FROM emp;
1
2

○ ltrim、rtrim、trim（去空格）

12. LTRIM(string2) RTRIM(string2) TRIM(string)去除前段空格或后端空格

#去除前端空格
SELECT LTRIM('   彤   ') FROM DUAL;
#去除后端空格
SELECT RTRIM('   彤   ') FROM DUAL;
#去除两端空格
SELECT TRIM('   彤   ') FROM DUAL;
1
2
3
4
5
6

去除前端空格

去除后端空格

去除两端空格

字符串函数练习

代码实现

方法一：

SELECT REPLACE(ename,LEFT(ename,1),LCASE(LEFT(ename,1))) 
	AS '姓氏小写' 
	FROM emp;
1
2
3

方法二：

SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2))
	AS '姓氏小写'
	FROM emp;
1
2
3

方法三：

SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2))
	AS '姓氏小写'
	FROM emp;
1
2
3

● 数学函数

数学相关函数基本语法

数学相关函数使用介绍

○ ABS（绝对值）

1. ABS(num) 绝对值

SELECT ABS(-10) FROM DUAL;
1

○ BIN（十进制转二进制）

2. BIN(decimal_number) 十进制转换二进制

SELECT BIN(10) FROM DUAL;
1

○ ceiling（向上取整）

3. CEILING(number2) 向上取整,得到比num2大的最小整数

SELECT CEILING(1.1) FROM DUAL;
1

○ conv（进制转换）

4. conv(number2,from_base,to_base) 进制转换

# 8 是 十进制的 8,转换为 2 进制输出
SELECT CONV(8,10,2) FROM DUAL;
1
2

○ floor（向下取整）

5. FLOOR(number2) 向下取整，得到比 num2 小的最大整数

#保留 2 位小数,四舍五入
SELECT FORMAT(78.12548,2) FROM DUAL; 
1
2

○ HEX（转十六进制）

6. HEX(DecimalNumber) 转十六进制

SELECT HEX(66) FROM DUAL;
1

○ least （求最小值）

7. LEAST(number , number2 [,…]) 求最小值

SELECT LEAST(0,1,10,20,-5) FROM DUAL;
1

○ MOD（求余）

8. MOD(numerator , denominator) 求余

SELECT MOD(10,3) FROM DUAL;
1

○ rand（返回随机数）

9. RAND([seed]) 返回随机数 范围为 0<= v <=1.0

SELECT RAND() FROM DUAL;
1

注意：

• 如果使用rand() 每次都会返回不同的随机数 ， 在0<= v <=1.0
• 如果使用 rand(seed) 返回一个随机数，范围也是0<= v <=1.0，但是如果seed不变，该随机数也不变，固定下来。

● 时间日期函数

时间日期函数语法

时间日期相关函数使用示范

○ current_date（当前日期）

语法

SELECT CURRENT_DATE() FROM DUAL;
1

效果

○ current_time（当前时间）

语法

SELECT CURRENT_TIME() FROM DUAL;
1

效果

○ current_timestamp（当前时间戳）

语法

SELECT CURRENT_TIMESTAMP() FROM DUAL;
1

效果

当前时间戳日常使用：

INSERT INTO mes
	VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
1
2

○ now（当前日期）

语法

NOW(当前日期)
1

使用

INSERT INTO mes
	VALUES(2,'上海新闻',NOW());
INSERT INTO mes
	VALUES(3,'广州新闻',NOW());
1
2
3
4

效果

○ date（返回datetime的日期部分）

语法

DATE(datetime) FROM 返回datetime的日期部分
1

使用案例

显示所有新闻信息,发布日期只显示 日期,不用显示时间

代码实现

-- 显示所有新闻信息,发布日期只显示 日期,不用显示时间
SELECT id, content,DATE(send_time) FROM mes;
1
2

运行截图

○ date_add（加日期或时间）

语法

DATE_ADD(date2, INTERVAL d_value d_type) 在date2中加上日期或时间
1

使用案例

查询在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);	
1
2
3
4
5
6
7
8
9

运行截图

○ date_sub（减去一个时间）

语法

DATE_SUB(date2, INTERVAL d_value d_type) 在date2上减去一个时间
1

使用案例

在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天？

代码实现

SELECT DATEDIFF('2011-11-11','1990-01-01')FROM DUAL;
1

运行截图

○ datediff（两个日期差）

语法

DATEDIFF(date1,date2) 两个日期差（结果是天）【小日期写前面】
1

使用案例

用mysql 的sql语句求出你活了多少天？

代码实现

SELECT DATEDIFF('2022-10-14','1999-09-07')FROM DUAL;
1

运行截图

上面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 两个时间差（多少小时多少分钟多少秒）
1

代码实现

SELECT TIMEDIFF('10:11:11','06:10:10') FROM DUAL;
1

运行截图

○ year | month | day | date

语法

YEAR|Month|DAY| DATE(datetime)
1

当前年份

SELECT YEAR(NOW()) FROM DUAL; #当前年份
1

当前月份

SELECT MONTH(NOW()) FROM DUAL; #当前月份
1

当前日期

SELECT DAY(NOW()) FROM DUAL;
1

○ unix_timestamp（从1970-1-1至今的秒数）

语法

unix_timestamp() 返回1970-1-1到现在的秒数
1

使用

SELECT UNIX_TIMESTAMP() FROM DUAL;
1

○ from_unixtime（转换指定格式日期）

语法

FROM_UNIXTIME() 可以把一个unix_timestamp 秒数，转换成指定格式的日期
1

注意：%Y-%m-%d 格式是规定好的,表示年月日 %H:%i:%s表示时分秒

显示年月日

SELECT FROM_UNIXTIME(1665987311,'%Y-%m-%d') FROM DUAL;
1

显示年月日，时分秒

SELECT FROM_UNIXTIME(1665987311,'%Y-%m-%d %H:%i:%s') FROM DUAL;
1

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;
1
2
3
4

运行截图

● 加密和系统函数

加密和系统函数基本语法

加密和系统函数使用示范

○ user（查询用户）

语法

USER() 查询用户 【可以查看登录到mysql的有哪些用户,以及登录的IP】
1

使用

SELECT USER() FROM DUAL;
1

○ database（查当前数据库名）

语法

DATABASE() 查询当前使用数据库名称
1

使用

SELECT DATABASE();
1

○ MD5（加密存储）

语法

MD5(str) 为字符串算出一个 MD5 32的字符串，常用（用户密码）加密

【root密码设为coco， coco -> 加密MD5 -> 在数据库中存放的是加密后的密码】

使用

SELECT MD5('coco') FROM DUAL;
1

运行

○ password（MySQL自带加密）

语法

PASSWORD(str) 加密函数，MySQL数据库的用户密码就是 PASSWORD函数加密，自带

SELECT PASSWORD('coco') FROM DUAL;
1

○ mysql.user（查看用户权限）

语法

mysql.user 查看数据库权限

使用

SELECT * FROM mysql.user;
1

效果

● 流程控制函数

流程控制函数基本语法

流程控制函数使用示范

○ if（判断语句）

语法

IF(expr1,expr2,expr3) 如果expr1为True,则返回expr2 否则返回expr3

【if语句类比java中的三目运算符，若为真则返回第一个值，若为假则返回第二个值】

使用

SELECT IF(TRUE,'北京','上海') FROM DUAL;
1

效果

○ ifnull（判断空值）

语法

IFNULL(expr1,expr2) 如果expr1不为空(NULL),则返回expr1，否则返回expr2

为NULL的情况

SELECT IFNULL(NULL,'假') FROM DUAL; #返回假
1

不为NULL的情况

SELECT IFNULL('真','假') FROM DUAL; #返回真
1

○ 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;
1
2
3
4

流程控制函数案例

1. 查询emp表，如果comm是null，则显示0.0
2. 如果emp表的 job 是 CLERK 则显示 职员，如果是MANAGER 则 显示经理，如果是SALESMAN 则显示 销售人员，其他正常显示。

代码实现

1. 查询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;
1
2
3
4
5
6

2. 如果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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

七、 连接

● 单表查询（加强）

使用案例来介绍加强的单表查询

○ 使用where子句

○ 需求：

查找1992.1.1后入职的员工
1

※ 说明：在mysql中，日期类型可以直接比较，但需注意格式

代码实现

SELECT * FROM emp
	WHERE hiredate > '1992-01-01';
1
2

运行效果

like操作符（模糊查询）

like操作符

• % :表示0到多个任意字符

• _ :表示单个任意字符

○ 需求1

显示首字母为S的员工姓名和工资
1

代码实现

#显示首字母为S的员工姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE 'S%';
1
2
3

运行效果

○ 需求2

显示第三个字符为大写O的所有员工的姓名和工资
1

代码实现

#显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE '__O%'; #注意:这是两个下划线	
1
2
3

运行效果

○ 需求3

显示没有上级的雇员的情况
1

代码实现

#显示没有上级的雇员的情况
SELECT * FROM emp
	WHERE mgr IS NULL;
1
2
3

运行效果

○ 需求4

查询表结构
1

代码实现

#查询表结构
DESC emp;
1
2

运行效果

○ 使用order by子句

○ 需求1

按照工资的从低到高的顺序[升序]，显示雇员的信息
1

代码实现

SELECT * FROM emp
	     ORDER BY sal;
1
2

运行截图

○ 需求2

按照部门号升序而雇员的工资降序排列，显示雇员信息
1

代码实现

SELECT * FROM emp
		 ORDER BY deptno ASC , sal DESC;
1
2

运行效果

标注

limit（分页查询）

基本语法

select ... limit start,rows
表示从start + 1 行开始取,取出rows行,start从0开始计算
1
2

推导公式

SELECT * FROM emp
	   ORDER BY empno
	   LIMIT 每页显示记录数 * (第几页 - 1) , 每页显示记录数
1
2
3

○ 实例
按雇员的id号升序取出，每页显示3条记录，请分别显示 （第一页、第二页、第三页）

原表

第一页

代码

SELECT * FROM emp
		ORDER BY empno
		LIMIT 0,3;	
1
2
3

运行

第二页

代码

SELECT * FROM emp
		ORDER BY empno
		LIMIT 3,3;
1
2
3

运行

第三页

代码

SELECT * FROM emp
		ORDER BY empno
		LIMIT 6,3;
1
2
3

运行

○ 分组增强

演示案例

1.显示每种岗位的雇员总数、平均工资
2.显示雇员总数、平均工资
3.统计没有获得补助的雇员数
4.显示管理者的总人数
5.显示雇员工资的最大差额

1. 显示每种岗位的雇员总数、平均工资

代码

SELECT COUNT(*),AVG(sal), job
		FROM emp
		GROUP BY job;
1
2
3

运行

2. 显示雇员总数、平均工资

代码

#思路: 获得补助的雇员数 等于 comm列为非null, 使用count(列),
#如果该列的值为null,count则不会统计
SELECT COUNT(*), COUNT(comm)
		FROM emp;
1
2
3
4

运行

3. 统计没有获得补助的雇员数

方法1

若comm是null，则记录为1，不为null，则标记为null，统计1的值就是没用获得补助的雇员数

SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL))
			FROM emp;
1
2

运行1

方法2

总人数 减掉 获得补助的人数

SELECT COUNT(*), COUNT(*) - COUNT(comm)
			FROM emp;
1
2

运行2

4. 显示管理者的总人数

代码

SELECT COUNT(DISTINCT mgr)
		FROM emp;
1
2

运行

5. 显示雇员工资的最大差额

代码

SELECT MAX(sal) - MIN(sal)
		FROM emp;
1
2

运行

数据分组总结

如果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;
1
2
3
4
5

总结案例

– 统计各个部门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
1
2
3
4
5
6

运行

● 多表查询（内连接）

以往的经验都是基于mysql中单表的基本查询，所有的操作都是在一张表上所完成的，但这在实际的软件开发中，还远远的不够，所以我们将迎来多表的查询。

○ 笛卡尔积

笛卡尔积表的出现

以一个案例来说明

– 1. 显示雇员名,雇员工资及所在部门的名字
– 2. 显示部门号为10的部门名、员工名和工资
– 3. 显示各个员工的姓名,工资,及其工资的级别

emp表

SELECT * FROM emp;
1

dept表

SELECT * FROM dept;
1

从上面两张表可以看到，emp表共有13行数据，而dept表则有4条数据，而在默认情况下，当两个表查询时，规则如下：

1. 从第一张表中，取出一行 和 第二张表的每一行进行组合，返回结果[含有两张表的所有列]。
2. 一共返回的记录数 第一张表行数 * 第二张表的行数，这样多表查询默认处理返回的结果，称之为笛卡尔积（13*4=52）
3. 而笛卡尔积表往往都会有许多重复的条件，解决这个多表的关键就是要写出正确的过滤条件（where）

笛卡尔积表

emp表 13 行 * dept表 4 行 = 52行表（13*4=52）

SELECT *
		FROM emp,dept;
1
2

为了防止笛卡尔积表的出现，我们需要过滤表中的条件，如上面两张表所示，emp表和dept表都有一个共同的条件【deptno】，如果筛选这个条件，则能得出最终我们需要的那张13行的表（二合一表），如下所示：

代码

SELECT *
		FROM emp,dept
		WHERE emp.deptno = dept.deptno;
1
2
3

运行

题目1

显示雇员名,雇员工资及所在部门的名字

案例的代码实现

SELECT  ename,sal,dname
		FROM emp,dept
		WHERE emp.deptno = dept.deptno;
1
2
3

运行效果

注意：

如果分组里有deptno，则会报错，因为表里有两个deptno，报错信息会显示指代不明。

防止笛卡尔集的出现

1. 当我们需要指定显示某个表的列时，需要：表.列表
2. 多表查询的条件不能少于 表的个数-1，否则会出现笛卡尔集，例如：2表得1过滤条件，3表得2过滤条件…

题目2

2. 显示部门号为10的部门名、员工名和工资

代码

SELECT  ename,sal,dname,emp.deptno
	FROM emp,dept
	WHERE emp.deptno = dept.deptno
	AND emp.deptno = 10;
1
2
3
4

运行

题目3

3. 显示各个员工的姓名,工资,及其工资的级别

代码

SELECT *  FROM salgrade
			FROM salgrade,emp;
1
2

运行

练习4

4. 显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序]

代码

SELECT ename,sal,loc
		FROM emp,dept
		WHERE emp.deptno = dept.deptno
		ORDER BY loc DESC;
1
2
3
4

运行

○ 自连接

自连接：

自连接是指在同一张表的链接查询【将同一张表当做两张表使用】

介绍案例

显示公司员工名字和他的上级的名字

分析：

• 员工名字 在emp，上级的名字的名字
• 员工和上级通过 emp 表的 mgr 列关联

代码

SELECT  worker.ename AS '员工名' , boss.ename AS '上级名'  
		FROM emp AS worker , emp AS boss
		WHERE worker.mgr = boss.empno;
1
2
3

运行

自连接特点总结：

1. 把同一张表当做两张表使用
2. 需要给表取别名 （表明 AS 表别名）
3. 列名不明确，可以指定列的别名（列名 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'
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行

多行子查询使用案例

查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不包含10自己的

分析

1.查询到10号部门有哪些工作

-- 1.查询到10号部门有哪些工作

-- select distinct job
-- 	from emp
-- 	where deptno = 10;
1
2
3
4
5

2.把上面查询的结果当做子查询使用

-- 2.把上面查询的结果当做子查询使用
-- select ename, job, sal, deptno
-- 	from emp
-- 	where job in (
-- 		SELECT DISTINCT job
-- 			FROM emp
-- 			WHERE deptno = 10
-- 	)
1
2
3
4
5
6
7
8

3.排除10号部门的员工

-- 3.排除10号部门的员工

#deptno != 10
1
2
3

完整代码实现

 SELECT ename, job, sal, deptno
		FROM emp
		WHERE job IN (
			SELECT DISTINCT job
				FROM emp
				WHERE deptno = 10
		) AND deptno != 10
1
2
3
4
5
6
7

运行

2. 临时表

在mysql中，可以将子查询的结果当做一张临时表来使用

使用案例

查询ecshop中各个类别中，价格最高的商品

分析

1. 查询ecshop中各个类别中,价格最高的商品
2. 查询商品表
3. 先得到各个类别中,价格最高的商品 max + group by cat_id,当做临时表
4. 把子查询当做一张临时表,可以解决很多复杂查询

代码

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
1
2
3
4
5
6
7
8

运行

3. all 与 any

all 操作符使用案例

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

代码

方法1:使用all操作符

使用all操作符

SELECT ename, sal, deptno
		FROM emp
		WHERE sal > ANY (
				SELECT sal
					   FROM emp
					   WHERE deptno = 30
	);
1
2
3
4
5
6
7
8
9

方法2：使用 max 操作符

使用 max 操作符

SELECT ename, sal, deptno
		FROM emp
		WHERE sal > (
			SELECT MAX(sal)
			FROM emp
			WHERE deptno = 30
		);
1
2
3
4
5
6
7
8
9

运行

any 操作符使用案例

显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号

分析：只要比其中一个人的工资高就满足

代码

方法1：使用 any 操作符

使用 any 操作符

 SELECT ename, sal, deptno
		FROM emp
		WHERE sal > ANY(
				SELECT sal
					FROM emp
					WHERE deptno = 30
		);
1
2
3
4
5
6
7
8
9

方法2：使用 min 操作符

SELECT ename, sal, deptno
		FROM emp
		WHERE sal > (
			SELECT MIN(sal)
			FROM emp
			WHERE deptno = 30
		);
1
2
3
4
5
6
7

运行

4. 多列子查询

多列子查询是指查询返回多个列数据的子查询语句

基本语法

(字段1, 字段2...) = (select 字段1, 字段2 from....)
1

多列子查询使用案例

查询与allen的部门和岗位完全相同的所有雇员（并且不含smith本人）

分析：

1. 得到allen的部门和岗位

SELECT deptno, job
		FROM emp
		WHERE ename = 'ALLEN'
1
2
3

2. 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配

完整代码

SELECT *
	FROM emp
	WHERE (deptno,job) = (
		SELECT deptno, job
			FROM emp
			WHERE ename = 'ALLEN'
		) AND ename != 'ALLEN';
1
2
3
4
5
6
7

运行

使用案例2

在student表中查询 : 和宋江数学、英语、语文成绩完全相同的学生

分析

1.先得到宋江的数学、英语和语文成绩

SELECT math,english,chinese
			FROM student
			WHERE `name` = '宋江'
1
2
3

2.把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配

SELECT *
		FROM student
		WHERE (math, english, chinese) = (
			SELECT math,english,chinese
				FROM student
				WHERE `name` = '宋江'	
		);
1
2
3
4
5
6
7

运行

5. 子查询综合练习

题目1

查询每个部门工资高于本部门平均工资的人的资料

分析

1.先得到每个部门的部门号和对应的平均工资

SELECT deptno,AVG(sal) AS avg_sal
		FROM emp
		GROUP BY deptno 
1
2
3

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
1
2
3
4
5
6
7
8

运行

题目2

查找每个部门工资最高的人的详细资料

分析

1.先找出每个部门的最高工资

SELECT	deptno, MAX(sal)
		FROM emp
		GROUP BY deptno
1
2
3

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
1
2
3
4
5
6
7
8

运行

题目3

查询每个部门的信息(包括:部门名,编号,地址)和人员数量

分析

1.先得到每个部门有多少人

SELECT deptno,COUNT(*) AS emp_num
		FROM  emp
		GROUP BY deptno
1
2
3

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
1
2
3
4
5
6
7
8

运行

注意：

1. 还有一种写法，表.* 表示将该表所有列都显示出来m可以简化sql
2. 在多表查询中，当多个表的列不重复时，才可以直接写列名

● 合并查询

合并查询：

有时在实际应用中，为了合并多个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'
1
2
3

第一条语句返回5行数据，第二条语句返回3行数据，如果我想
得到它们两条数据合并的结果，可以使用union all 来实现

代码

SELECT ename,sal,job FROM emp WHERE sal>2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER'
1
2
3

结果

但是从上面的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'
1
2
3

运行结果

可以看到上面的8条结果精炼为了6行结果。

● 外连接

前面我们是利用 where 子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示

如果我现在想列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门，又该如何实现？

我们使用where语句查询，可以得到10-30部门的人员情况，但是第40部门虽然存在，但是无人的情况，使用where是查询不到的，那如果我想得到10-40部门的情况，就可以使用外连接的方式来实现，

左右外连接

1. 左外连接

如果左侧的表完全显示，我们就说是左外连接

2. 右外连接

如果右侧的表完全显示，我们就说是右外连接

左右外连接的基础语法

左连接

select .. from 表1 left join 表2 on 条件
 			[表1:左表 表2:右表]
1
2

右链接

select .. from 表1 right join 表2 on 条件
 			[表1:左表 表2:右表]
1
2

以下面的两张表作为使用案例

条件1

使用左连接（显示所有人的成绩，如果 没有成绩，也要显示该人的姓名和id号，成绩显示为空）

代码

SELECT `name`, stu.id, grade
		FROM stu LEFT JOIN exam
		ON stu.id = exam.id;
1
2
3

运行

条件2

使用右外连接（显示所有成绩，如果没有名字匹配，显示空）

代码

SELECT `name`, stu.id, grade
		FROM stu RIGHT JOIN exam
		ON stu.id = exam.id;
1
2
3

运行

综合练习

列出部门名称和这些部门的员工信息（名字和工作），同时列出那些没有员工的部门。（使用左右链接实现）

代码

左连接实现

SELECT dname,ename,job
		FROM dept LEFT JOIN emp
		ON emp.deptno = dept.deptno
1
2
3

右链接实现

SELECT dname,ename,job
		FROM emp RIGHT JOIN dept
		ON emp.deptno = dept.deptno
1
2
3

效果

八、 约束

约束是指用于确保数据库的数据满足特定的商业规则。

在MySQL中，约束包括：not null（非空）、unique（唯一）、primary key（主键）、foreign key 和 check 以及自增长。

约束总纲

● primary key（主键）

primary key（主键）：用于唯一的标示表行的数据，当定义主键约束后，该列不能重复

语法

字段名 字段类型 primary key
1

使用演示

创建一张新表把id设置为主键

CREATE TABLE t17
		(
			  id INT PRIMARY KEY, -- 表示id为主键
			  `name` VARCHAR(32),
	 	  	  email VARCHAR(32)
		);
1
2
3
4
5
6

此时我往表中添加两条数据：

INSERT INTO t17
		VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
		VALUES(2,'tom','tom@sohu.com');
1
2
3
4

效果

添加完两条数据后，我还想往里再添加一条数据：

INSERT INTO t17
		VALUES(1,'coco','coco@sohu.com');
1
2

执行完这句话后就会报错，因为表中的id为主键，而我已经添加了id为1的jack报错信息提示我不能再添加一条相同的数据，所以会报错。

primary key的使用细节

1. primary key 不能重复而且不能为 null
2. 一张表最多只能有一个主键，但可以是符合主键
3. 主键的指定方式有两种
   ① 直接在字段后指定：字段名 primary key
   ② 在表定义最后写 primary key(列名)
4. 使用 desc 表名，可以看到primary key 的情况

使用细节的解释

1. primary key 不能重复而且不能为 null

设置id为主键

INSERT INTO t17
		VALUES(NULL,'tom','tom@sohu.com');
1
2

因为前面设置过id为主键，添加NULL值则会报错，因为主键不能重复且不能为null

2. 一张表最多只能有一个主键，但可以是复合主键

演示复合主键 （id + name）

CREATE TABLE t20
		(
 		   id INT,
 		   `name` VARCHAR(32),
 		   email VARCHAR(32),
 	 	  PRIMARY KEY (id, `name`)  -- 复合主键
		)
1
2
3
4
5
6
7

这里的 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),
		);
1
2
3
4
5
6

在表定义最后写 primary key(列名)

CREATE TABLE t20
		(
 	   		id INT,
 	   		`name` VARCHAR(32),
 	        email VARCHAR(32),
 	   		PRIMARY KEY (`name`) 
		);
1
2
3
4
5
6
7

4. 使用 desc 表名，可以看到primary key 的情况

desc查看表的且过，显示约束的情况

DESC t20;  -- 查看t20表的结果,显示约束情况
1

效果

● not null（非空）

如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

语法

字段名 字段类型 not null
1

● unique（唯一）

当定义了唯一约束后，该列值是不能重复的。

语法

字段名 字段类型 unique
1

使用示范

CREATE TABLE t32
		(
 		   id INT UNIQUE, -- 表示id列不能重复
 	 	   `name` VARCHAR(32),
 		   email VARCHAR(32),
 		);
1
2
3
4
5
6

现在添加两条语句

INSERT INTO t17
		VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
		VALUES(1,'tom','tom@sohu.com');
1
2
3
4

运行过后会报错，原因是id重复了，因为unique字段修饰过后不能有重复值。

primary key 与 unique的区别

按理说primary key的作用也是不能重复，那么unique与primary key的区别在哪呢？区别就在：primary key不能存入非空的值，但是unique却可以，可以存入一个或多个null值。

unique 使用细节：

1. 如果没有指定not null，则unique 字段可以有多个null

2. 如果一个列（字段），是unique not null，使用效果类似primary key

3. 一张表可以有多个unique字段（primary key 只能存在一个）

● foreign key（外键）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

语法

 FOREIGN KEY (本表字段名) REFERENCES
 主表名(主键名或unique字段名)
1
2

外键约束介绍

现在有两张表，一张学生表，一张班级表

学生表

班级表

• 如果要求每个学生所在的班级号 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 ''
 		);
1
2
3
4
5

创建 从表 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)
		);
1
2
3
4
5
6
7
8
9

现在向主表添加两条数据

INSERT INTO my_ class
		VALUES(100,'java'),(200,'web');
1
2

再向从表添加两条数据

INSERT INTO my_ stu
		VALUES(1,'tom',100);
	
INSERT INTO my_ stu
		VALUES(2,'jack',200);
1
2
3
4
5

从上面到次为止的代码运行都没有任何问题，但是如果我再想向从表添加一条额外的数据

INSERT INTO my_ stu
		VALUES(3,'coco',300);
1
2

此时由于主表并没有设置id为300的数据，所以从表的这张数据会添加失败

注意一个点：若我修改其中的一个值，重新插入这条字段

INSERT INTO my_ stu
		VALUES(3,'coco',NULL);
1
2

如果外键字段允许为null，那么我新修改的这条数据可以插入进去，即使主表没有相同的字段；但如果主表中class_id 是由not
null修饰的，则不能加入空值

插入成功的演示

foreign key（外键）细节注意事项

1. 外键指向的表的字段，要求是primary key 或者是unique。
2. 表的类型是innodb，这样的表才支持外键。
3. 外键字段的类型要和主键字段的类型一致（长度可以不同）
4. 外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】
5. 一旦建立主外键的关系，数据就不能随意删除了。

● 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)
1
2
3

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)
		);
1
2
3
4
5
6

添加数据

INSERT INTO t23
		VALUES(1,'jack','mid',1);
1
2

运行效果

再次注意：

目前使用的是5.7.3的MySQL，所以目前还不支持check，这里仅仅是做了语法校验，但是并没有生效，这里只做了解，所以不符合规则的数据依然添加进了表中，check 在8.0及以上的MySQL中可以完整使用。

● 自增长

在某些情况下，需要在某张表中设置一个列（整数列），在添加记录的时候，该列从1开始，自动的增长，就有了新的字段，自增长。

语法

字段名 整型 primary key auto_increment
1

添加自增长的字段方式

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');	
1
2
3
4
5

自增长使用细节

1. 一般来说自增长是和primary key配合使用的
2. 自增长也可以单独使用【但是需要配合一个unique】
3. 自增长修饰的字段为整数型的（虽然小数也可以但是极少这样使用）
4. 自增长默认从1开始，也可以通过如下命令修改：

alter table 表明 auto_increment = 新的开始值;
1

5. 如果添加数据时，给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据

● 本章重点及思维导图（☆）

primary key（主键）：唯一性，不能为空，一张表只能存在一个。
not null（非空）：必须插入数据。
unique（唯一）：唯一性，可以为空，一张表可以存在多个。
foreign key（外键）：外键约束：约束列的值在被约束列已存在

primary key 与 unique 的区别：

1. primary key 不能为空，而unique 可以
2. 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 ''
		);
1
2
3
4
5
6
7
8
9

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) 
		);
1
2
3
4
5
6
7
8
9

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)
	); 
1
2
3
4
5
6
7
8
9

九、索引

说起提高数据库性能，索引是最方便的东西了，不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍。

索引总纲

索引的原理

当我们想要查询一张表内的数据时，没有索引，就只能通过 select * from emp where id = 1来进行全表扫描，查询速度非常慢。

表t24

如果我想查询id为9的数据时，系统会从id = 1开始，从1查询到9，而我想查询id为1的数据时，即使第一个id就为1，但是系统还是会继续查找完9个id，效率非常低，而使用索引后效率为什么会变快？那是因为索引会形成一种数据结构，比如二叉树，用来查询，查询方式有点类似java中的二分查询法

索引的底层二叉树

索引查询的代价

1. 磁盘占用
2. 对dml（update、delete。insert）语句的效率影响

以上表为例，如果比较次数达到30次，那么覆盖的表范围高达 2^30，所以索引可以说是牺牲空间的方法来换来时间的减少，因此会对表有一定的影响，比如：对表进行dml（修改，删除，添加）会对索引进行维护，对速度有影响。

索引的类型

1. 主键索引，主键自动的为主索引（类型Primary）
   【（in int primary key,）主键，同时也是索引，称为主键即索引】
2. 唯一索引（unique）
   【（id int unique,）id是唯一的，同时也是索引，称为unique索引】
3. 普通索引（index）
4. 全文索引（fulltext）【MySQL自带，适用于MyISAM】
   【一般开发，不使用自带的全文索引，而是使用：全文搜索Solr 和 ElasticSearch（ES）】

● 索引的使用

1. 添加索引

create [unique] index index_name on tbl_name(col_name[(length)][ASC | DESC],....);

alter table table_name ADD index[index_name](index_col_name,...)
1
2
3

2. 添加主键（索引）

alter table 表名 ADD primary key(列名,...);
1

3. 删除索引

drop index index_name ON table_name;

alter table table_name drop index index_name;
1
2
3

4. 删除主键索引

alter table t_b drop primary key;
1

○ 创建索引

查询表是否有索引

SHOW INDEXES FROM t25;
1

添加索引

添加唯一索引

CREATE UNIQUE INDEX id_index ON t25 (id);
1

添加普通索引

方式1：

CREATE INDEX id_index ON t25 (id);
1

方式2：

ALTER TABLE t25 ADD INDEX id_index (id)
1

如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引。

添加主键索引

方式1：在创建表的时候设置

CREATE TABLE t25 (
		id INT PRIMARY KEY,
		`name` VARCHAR(32)
		);
1
2
3
4

方式2：添加进去

ALTER TABLE t26 ADD PRIMARY KEY (id);
1

○ 删除索引

删除索引

DROP INDEX id_index ON t25;
1

删除主键索引

ALTER TABLE t26 DROP PRIMARY KEY
1

○ 修改索引

先删除，再添加新的索引

查询索引

方式1：

SHOW INDEX FROM t25;
1

方式2：

SHOW INDEXES FROM t25;
1

方式3：

SHOW KEYS FROM t25;
1

方式4：

DESC t25;
1

● 小结

小结：在哪些列上适合使用索引

1. 较频繁的作为查询条件字段应该创建索引

select * from emp where empno = 1
1

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex = '男'
1

3. 更新非常频繁的字段不适合创建索引

select * from emp where logincount = 1
1

4. 不会出现在WHERE子句中字段不该创建索引

十、 事务

事务总纲

○ 什么是事务

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。

○ 事务的运行机制

事务就像是一个单机游戏的流程，一个百分之百的游戏流程可以在不同的时间点，存不同的档，当你觉得过不去的时候，可以回滚到上一个或是从前的任何一个存档。

○ 事务和锁

当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户改表的数据。（这对用户来讲是非常重要的）

○ 回退事务

在介绍回退事务前，先介绍一下保存点（savepoint），保存点是事务中的点，用于取消部分事务，当结束事务时，会自动的删除该事物所定义的所有保存点。

当执行回退事务时，通过指定保存点可以回退到指定的点。

○ 提交事务

使用commit语句可以提交事务，当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其他会话将可以查看到事务变化后的新数据【所有数据就正式生效】。

● 事务基本使用

○ mysql 数据库控制台事务的几个重要基本操作

1. start transaction 【开始一个事务】
2. savepoint 保存点名 【设置保存点】
3. rollback to 保存点名 【回退事务】
4. rollback 【回退全部事务】
5. 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

○ 事务使用细节

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚。
2. 如果开始一个事务，你没有创建保存点，但可以执行rollback，默认就是回退到事务开始的状态【初始状态】。
3. 这个事务中（还没有提交时），创建多个保存点。
   比如： savepoint a; 执行dml ， savepoint b;
4. 事务在没有提交前，可以选择回退到哪个保存点。
5. MySQL的事务机制需要innodb的存储引擎才可以使用，myisam不行。
   【InnoDB 存储引擎支持事务，MsISAM不支持】
6. 开始一个事务 start transaction，set autocommit = off ;

● 事务隔离级别

MySQL隔离级别定义了事务与事务之间的隔离程度。

注意： V可能出现，X不会出现。

事务隔离级别介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
2. 如果不考虑隔离性，可能会引发如下问题：
   脏读
   不可重复读
   幻读

查看事务隔离级别

查看当前会话隔离级别 : SELECT @@ tx_isolation;
1

• 脏读（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;
1

此时mysql1的情况

+-------------------+
|  @@ tx_isolation  |
+-------------------+
|  REPEATABLE-READ  |
+-------------------+
1
2
3
4
5

把其中一个控制台(mysql2)的隔离级别设置 Read uncommitted

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1

mysql2的级别：

+-------------------+
|  @@ tx_isolation  |
+-------------------+
|  READ-UNCOMMITTED |
+-------------------+
1
2
3
4
5

创建表

CREATE TABLE `account` (
	id INT ,
	`name` VARCHAR (32),
	money INT
	);
1
2
3
4
5

在mysql1中插入数据(此时并未提交)

INSERT INTO account VALUES(100.'tom',1000);
1

此时mysql2中查看到的数据：

+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom  |  1000 | -- 脏读
+-------------------+
1
2
3
4
5

此时mysql1中的信息未上传,但在mysql2中却能查看到mysql1中的信息，这种情况就为脏读

现在修改mysql1中的数据并提交

UPDATE account SET money = 800 WHERE id =1000;

INSERT INTO account VALUES(200,'jack',2000);

COMMIT;
1
2
3
4
5

此时在mysql2中查看到了mysql1中插入与修改的两条数据

+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom  |  800  | -- 不可重复读
| 200| jack |  2000 | -- 幻读
+-------------------+
1
2
3
4
5
6

事务的读取是开启事务时数据库的状态，mysql2在开启的时候，数据库中并没有这两条记录，但此时mysql2中却读出来了mysql1的数据，这就说明修改的数据（money = 800） 就是不可重复读，而插入的数据（200，jack，2000）是幻读。

综上所述，使用读未提交级别的数据库会发生三种问题：脏读、不可重复读与幻读

○ 读已提交（Read committed）

修改mysql2的级别为读已提交

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1

mysql > SELECT @@ tx_isolation;

+------------------+
|  @@ tx_isolation |
+------------------+
|  READ-COMMITTED  |
+------------------+
1
2
3
4
5

在mysql1中加入记录

INSERT INTO account VALUES(300,'scott',8000);
1

此时mysql1中的情况：

+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom  |  800  | 
| 200| jack |  2000 |
| 300| scott|  8000 |
+-------------------+
1
2
3
4
5
6
7

这时在mysql2中查看到的数据：

+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom  |  800  | 
| 200| jack |  2000 |
+-------------------+
1
2
3
4
5
6

这时并没有像上一个级别那样，出现脏读的情况。

继续在mysql1中修改记录

UPDATE account SET money = 1800 WHERE id = 200;
1

此时mysql2中的情况为：

+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom  |  800  | 
| 200| jack |  1800 | -- 不可重复读
| 300| scott|  8000 | -- 幻读
+-------------------+
1
2
3
4
5
6
7

此时的mysql2并未提交，但从上面的库中可以看出发生了不可重复读（修改）与幻读（插入）的情况。

所以综上所述，读已提交级别的数据库使用时会造成两种情况：不可重复读与幻读。

○ 可重复读（Repeatable read）

修改mysql2为可重复读级别数据库

SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
1

mysql2的级别：

+------------------+
|  @@ tx_isolation |
+------------------+
|  REPEATABLE-READ |
+------------------+
1
2
3
4
5

在mysql1中插入与修改数据

INSERT INTO account VALUES(400,'milan',6000);

UPDATE account SET money = 100 WHERE id = 300;
1
2
3

此时mysql1的表中共有4条数据

+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom  |  800  | 
| 200| jack |  1800 | 
| 300| scott|  100  | 
| 400| milan|  6000 | 
+-------------------+
1
2
3
4
5
6
7
8

但在此时mysql2中的数据依旧是原来的3条,并没有受到mysql1的影响，而即使mysql1提交(commit)过后mysql2也是原来的3条数据，此时可以确定并没有发生脏读、不可重复读与幻读

+-------------------+
| id | NAME | money |
+-------------------+
| 100| tom  |  800  | 
| 200| jack |  1800 | 
| 300| scott|  2800 | 
+-------------------+
1
2
3
4
5
6
7

综上所述：可重复读级别的库并不会发生脏读、不可重复读与幻读

○ 可串行化(Serializable)

修改mysql2为可串行化

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1

此时mysql2的情况：

+------------------+
|  @@ tx_isolation  |
+------------------+
|   SERIALIZABLE   |
+------------------+
1
2
3
4
5

在mysql1中修改数据(不提交)

INSERT INTO account VALUES(500,'terry',80000);

UPDATE account SET money = 900 WHERE id = 300;
1
2
3

这时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 |  
+-------------------+
1
2
3
4
5
6
7
8
9

综上所述，可串行化同可重复读一样。不会出现脏读、不可重复读与幻读的情况，两者最大的区别就是锁，可重复读是无锁的。

● ACID

事务的acid特性

1. 原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另一个一致性状态。

3. 隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4. 持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

十一、 存储引擎

基本介绍

1. MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。
3. 这六种又分为两类，一类是"事务安全型"（transaction-safe），比如：InnoDB；其余都属于第二类，称为"非事务安全型"（non-transaction-safe）【mysiam 和 memory】。

查看MySQL中所有存储引擎

SHOW ENGINES;
1

主要的存储引擎 / 表类型特点

重点介绍三种引擎：MyISAM、InnoDB、MEMORY

1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。
2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
3. 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 = 存储引擎;
1

十二、视图（view）

什么是视图？

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）

视图的优点

○ 安全。 一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样用户就可以查询自己需要的字段，不能查看保密的字段。

○ 性能。 关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低 。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。

○ 灵活。 如果系统中有一张旧表，这张表由于设计的问题，即将被废弃。然而很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

视图

1. 视图是根据基（可以是多个基表）表来创建的 视图是虚拟的表
2. 视图也有列，数据来自基表
3. 通过视图可以修改基表的数据
4. 基本的改变，也会影响到视图的数据

视图的基本使用

1. create view 视图名 as select 语句
2. alter view 视图名 as select 语句
3. SHOW CREATE VIEW 视图名
4. drop view 视图名1，视图名2

视图使用演示

创建并查看视图

-- 创建视图
CREATE VIEW emp_view01
	AS 
	SELECT empno,ename,job,deptno FROM emp;
	
-- 查看视图结构	
DESC emp_view01;
1
2
3
4
5
6
7

效果

查看视图

SELECT * FROM emp_view01;
1

效果

只查看其中两列

SELECT empno , job FROM emp_view01;
1

效果

查看创建视图的指令

SHOW CREATE VIEW emp_view01;
1

效果

试图使用细节

1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.from）
2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图【insert update delete】
3. 视图中可以再使用视图，数据仍然来自基表。

细节演示

-- 针对前面的雇员管理系统
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;
1
2
3
4
5
6
7

视图使用案例

针对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;	
1
2
3
4
5
6
7
8
9

十三、MySQL用户管理

○ 用户管理

当我们做项目开发时，可以根据不同的开发人员，赋给他们相应的MySQL操作权限，所以MySQL数据库管理人员（root），根据需要创建不同的用户，赋给相应的权限，供人员使用

○ MySQL用户

mysql中的用户，都存储在系统数据库mysql的user表中

其中user表的重要字段说明：

1. host： 允许登录的"位置"， localhost表示该用户只允许本机登录，也可以指定IP地址，比如：192.168.1.100【只能从这个ip登录】
2. user： 用户名；
3. authentication_string：密码，是用过mysql的password（）函数加密之后的密码。

○ 创建用户

创建用户，同时指定密码。

create user '用户名' @ '允许登录位置' identified by '密码'
1

○ 删除用户

drop user '用户名' @ '允许登录位置';
1

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样

○ 用户修改密码

修改自己的密码：

set password = password('密码');
1

修改他人的密码（需要有修改用户密码权限）：

set password for '用户名' @ '登录位置' = password('密码');
1

使用演示

（1）创建新的用户

1. ‘coco @ localhost’ 表示用户的完整信息，'coco’是用户名，'localhost’是登录的IP
2. 123456 是密码，但是注意：存放到mysql.user表时，是password（‘123456’）加密后的密码

CREATE USER 'coco'@'localhost' IDENTIFIED BY '123456';
1

（2）删除用户

DROP USER 'coco'@'localhost';
1

（3）修改自己密码

set password = password('abcde');
1

○ MySQL中的权限

○ 给用户授权

基本语法

grant 权限列表 on 库.对象名 to '用户名' @ '登录位置' 【identified by '密码'】
1

说明：

1. 权限列表，多个权限用逗号分开

grant select on .....
grant select, delete,create no ....
grant all 【privileges】 on ....   //表示赋予该用户在该对象上的所有权限
1
2
3

2. 特别说明

*.*  : 代表本系统中的所有数据库的所有对象(表,视图,存储过程)
库.* : 表示某个数据库中的所有数据对象(表,视图,存储过程等)
1
2

3. identified by可以省略，，也可以写出
①.如果用户存在，就是修改该用户的密码。
②.如果该用户不存在，就是创建该用户。

○ 回收用户授权

基本语法：

revoke 权限列表 on 库.对象名 from '用户名' @ "登录位置";
1

○ 权限生效指令

如果权限没有生效，可以执行下面命令：

基本语法：

FLUSH PRIVILEGES;
1

用户管理练习

1. 创建一个用户名，密码 123，并且只可以从本地登录，不让远程登录mysql
2. 创建库和表 testdb 下的news 表，要求：使用root用户创建
3. 测试看用户是否只有这几个权限
4. 修改密码为abc，要求：使用root用户完成
5. 重新登录
6. 使用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';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

用户管理使用细节

1. 在创建用户的时候，如果不指定Host，则为 %，%表示所有IP都有链接权限
2. 也可以指定：

create user 'xxx'@'192.168.1.%' 表示 xxx 用户在192.168.1.*的ip可以登录mysql
1

3. 在删除用户的时候，如果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.%'
1
2
3
4
5
6
7
8
9