一、安装下载

MySQl的安装以及环境的搭建博主已经在前面的博客有写过，所有大家点击链接就可看到相关步骤
链接: MySQL环境搭建

二、用户管理(三种权限，三个环境)

1. 项目经理 数据库而言：增删改查，Create、drop等权限，一般具备所有权限
2. 项目组长 数据库而言：只具备增删改查
   3）开发人员：数据库而言：查询(select)
3. 对于系统开发而言，三个环境
   线网、生产环境：系统开发完毕，客户使用的那个环境
   测试环境：公司内部模拟客户现场，搭建而成的环境
   开发环境：本地电脑
   权限的划分，针对的是生产环境

一、首先我们需要来看一下mysql默认数据库里面的四张表（user，db，tables_priv，columns_priv）。

1、user表（用户层权限）


因为字段太多，只截取了一部分。首先登陆的时候验证Host,User,Password(authentication_string)也就是ip,用户名，密码是否匹配，匹配登陆成功将会为登陆者分配权限，分配权限的顺序也是按照上面四张表的排列顺序进行的，举个例子，如果user表的Select_priv为Y说明他拥有所有表的查找权限，如果为N就需要到下一级db表中进行权限分配了。其中的%是通配符，代表任意的意思。

2、db表（数据库层权限）


来到db表之后会匹配Host,User然后会根据Db字段对应的表进行权限分配，像Select_priv这些字段对应的权限大家应该都能看出来是对应着什么权限了吧，这里不细说了（不偷懒，举个例子Select_priv，Insert_priv，Update_priv，Delete_priv，Create_priv，Drop_priv分别代表着查询，增加，更新，删除，创建，销毁）。其中Y代表这拥有此项权限，N则代表没有此项权限。

3、tables_priv表（表层权限）


与上面一样，这是通过Host，Db，User，Table来进行定位到表层的一个权限分配。不过它只有Table_priv和Column_priv两个字段来记录权限。

4、columns_priv表（字段层权限）


顾名思义，字段层权限，通过Host，Db，User，Table，Column来进行定位到字段层的一个权限分配，只有Column_priv来记录权限。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

相关的SQL语句

0.查询用户
SELECT * from user;
​
1.创建用户并设置登录密码
#MySQL5.7
#命令：create user 用户名 identified by '密码';
#注：identified by会将纯文本密码加密作为散列值存储
create user ls identified by '123456';
#MySQL8
#用户名密码创建需要分开
#命令：create user 用户名;
create user ls;
​
​
2.查看用户信息
#MySQL5.7
select host,user,password from user;
#MySQL8
select host,user,authentication_string from user;
​
3.删除用户（慎用）
#命令：drop user 用户名;
#drop user ls;
​
4.修改用户密码
4.1 修改密码
#MySQL5.7
#命令：set password for 用户名=password('新密码');
set password for zs=password('123456');
​
#MySQL8
#ALTER USER 用户 IDENTIFIED WITH mysql_native_password BY '密码';
ALTER USER 'ls'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
​
4.2 刷新配置
#命令：flush privileges;
​
#MySQL5.7与MySQL8关于权限操作没有差异性
5.设置权限(Grant)
#语法：grant privileges on databasename.tablename to username@'host';
#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 查询权限
grant SELECT on db_xiaoli.t_p1_user to zs@'%';
#给 zs用户 赋予 数据库db_xiaoli中的表t_p1_user 修改权限
grant UPDATE on db_xiaoli.t_p1_user to zs@'%';
#给 zs用户 赋予 数据库db_xiaoli中所有表 查询权限
grant SELECT on db_xiaoli.* to zs@'%';
#给 zs用户 赋予 数据库db_xiaoli中所有表 所有权限
grant ALL on db_xiaoli.* to zs@'%';
​
6.撤销权限(Revoke)
#语法：revoke privileges on databasename.tablename from username@'host';
#啥也不能回收,不会对GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`有任何影响
revoke DELETE on db_xiaoli.t_p1_user from zs@'%';
#可以回收GRANT SELECT, UPDATE ON `db_xiaoli`.`t_p1_user` TO `zs`@`%`这条权限语句
revoke all on db_xiaoli.t_p1_user from zs@'%';
#可以回收GRANT ALL PRIVILEGES ON `db_xiaoli`.* TO `zs`@`%`这条赋权语句带来的权限
revoke all on db_xiaoli.* from zs@'%';
#注：revoke只能回收grants列表中更小的权限；
​
设置权限(Grant)和撤销权限(Revoke)的参数说明：
1) privileges： 用户的操作权限,如SELECT,INSERT,UPDATE,DELETE等，如果要授予所有权限直接使用：all;
2) databasename：数据库名;
3) tablename：   表名，如果要授予用户对所有数据库和表的操作权限直接使用：*.*;  
​
7.查看用户权限
#命令：show grants for 用户名;
show grants for 'zs'@'%';
​
​
user表中host列的值的意义
%             匹配所有主机
localhost     localhost不会被解析成IP地址，直接通过UNIXsocket连接
127.0.0.1     会通过TCP/IP协议连接，并且只能在本机访问；
::1           ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

案例：
我们现在创建一个项目经理(zs)，一个项目组长(ls)，一个开发人员(ww)，项对应的权限也一并划分
目前我们数据库的用户

我们创建项目经理zs

添加用户
CREATE user zs;

给创建的用户设置密码
ALTER USER 'zs'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

查询用户的密码等信息
select host,user,authentication_string from user;

1
2
3
4
5
6
7
8
9

现在我们用zs登录连接数据库
点击确定即可
赋予权限，博主用博主数据库的db_0613这个库的tb_like这个表来操作

赋予权限给用户  grant to
grant all privileges on db_0613.* to zs@'%';
1
2

那么现在对于zs来言就拥有了所有权限

现在我们创建一个项目组长ls

添加用户
CREATE user ls;

给创建的用户设置密码
ALTER USER 'ls'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

赋予权限给用户  grant to
grant select,delete on db_0613.tb_like to ls@'%';

1
2
3
4
5
6
7
8
9

连接成功后我们可以对比一下项目经理和项目组长的权限

项目经理是整个库进行操作,而项目组长只能操作其中一张表
而且且项目组长不具有修改权限（这边打比方）
表数据

如果我们修改成跳1那么就会报错

但是有删除的权限

然后我们想要收回权限，那么该人员就不能具备以前的权限
先查看所有权限

show grants for 'zs'@'%';
1

第一个是登录权限，第二个是其他权限
现在我们撤回权限

收回该用户的权限
revoke DELETE on db_0613.* from zs@'%';
1
2

删除用户

删除用户
#drop user ls;
#DROP user zs;
1
2
3

三、建库建表

1) 表字段类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
1）字符类型：char varchar text blob
2）数值类型：int bigint float decimal
int -> int
bigint -> long
float -> 成绩
decimal -> 货币类型(精度,小数)
​
3）日期类型：date time datetime timestamp
​
date -> yyyy:MM:dd HH:mm:ss
time -> HH:mm:ss
datetime -> yyyy:MM:dd
timestamp（时间戳） -> 长整数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2) 约束

/*
​
​
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
​
​
分类：六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号

​
添加约束的时机：
1.创建表时
2.修改表时

​
约束的添加分类：
列级约束：
六大约束语法上都支持，但外键约束没有效果

表级约束：

除了非空、默认，其他的都支持


主键和唯一的大对比：
​
保证唯一性 是否允许为空   一个表中可以有多少个   是否允许组合
主键√×至多有1个           √，但不推荐
唯一√√可以有多个         √，但不推荐
外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
​
​
*/
​
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
​
)
CREATE DATABASE students;
#一、创建表时添加约束
​
#1.添加列级约束
/*
语法：
​
直接在字段名和类型后面追加 约束类型即可。
​
只支持：默认、非空、主键、唯一
​
​
​
*/
​
USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL UNIQUE,#非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
seat INT UNIQUE,#唯一
age INT DEFAULT 18,#默认约束
majorId INT REFERENCES major(id)#外键
​
);
​
​
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
​
#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
​
​
#2.添加表级约束
/*
​
语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/
​
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,

CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键

);
​
​
​
​
​
SHOW INDEX FROM stuinfo;
​
​
​
#通用的写法：★
​
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
​
);
​
​
​
#二、修改表时添加约束
​
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
​
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
​
​
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
​
#4.添加唯一
​
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
​
​
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
​
#三、修改表时删除约束
​
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
​
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
​
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
​
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
​
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
​
SHOW INDEX FROM stuinfo;
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200

3) 建库建表SQL语句

#1.表的创建 ★
​
/*
语法：
create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
...
列名 列的类型【(长度) 约束】
)
​
​
*/
#案例：创建表Book
CREATE TABLE book(
id INT,#编号
bName VARCHAR(20),#图书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期
​
​
​
);
​
​
DESC book;
​
#案例：创建表author
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)
DESC author;
​
​
#2.表的修改
/*
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/
​
#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
​
#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
​
#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
​
#④删除列
ALTER TABLE book_author DROP COLUMN annual;
​
#⑤修改表名
ALTER TABLE author RENAME TO book_author;
​
DESC book;
​
#3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
​
#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
​
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();
​
#4.表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
​
SELECT * FROM Author;
SELECT * FROM copy2;
​
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
​
#2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
​
#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
​
​
#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

四、查询

1）基础查询

/*
语法：
select 查询列表 from 表名;
​
​
类似于：System.out.println(打印东西);
​
特点：
​
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/
​
# USE myemployees;
​
#1.查询表中的单个字段
​
SELECT last_name FROM t_mysql_employees;
​
#2.查询表中的多个字段
SELECT last_name,salary,email FROM t_mysql_employees;
​
#3.查询表中的所有字段
​
#方式一：
SELECT
  `employee_id`,
  `first_name`,
  `last_name`,
  `phone_number`,
  `last_name`,
  `job_id`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate`
FROM
  t_mysql_employees ;
#方式二：  
SELECT * FROM t_mysql_employees;

#4.查询常量值
SELECT 100;
SELECT 'john';

#5.查询表达式
SELECT 100%98;

#6.查询函数

SELECT VERSION();


#7.起别名
/*
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来

*/
#方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM t_mysql_employees;
​
#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM t_mysql_employees;
​
​
#案例：查询salary，显示结果为 out put
SELECT salary AS "out put" FROM t_mysql_employees;
​
​
#8.去重
​
​
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM t_mysql_employees;
​
​
#9.+号的作用
​
/*
​
java中的+号：
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串
​
mysql中的+号：
仅仅只有一个功能：运算符
​
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90;如果转换失败，则将字符型数值转换成0
​
select null+10; 只要其中一方为null，则结果肯定为null
​
*/
​
#案例：查询员工名和姓连接成一个字段，并显示为 姓名
​
​
SELECT CONCAT('a','b','c') AS 结果;
​
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
t_mysql_employees;
​
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111

2）过滤查询

/*
​
语法：
select
查询列表
from
表名
where
筛选条件;
​
分类：
一、按条件表达式筛选

简单条件运算符：> < = != <> >= <=

二、按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
&& || !
and or not

&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false

三、模糊查询
like
between and
in
is null

*/
#一、按条件表达式筛选
​
#案例1：查询工资>12000的员工信息
​
SELECT
*
FROM
t_mysql_employees
WHERE
salary>12000;


#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
t_mysql_employees
WHERE
department_id<>90;
​
​
#二、按逻辑表达式筛选
​
#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
last_name,
salary,
commission_pct
FROM
t_mysql_employees
WHERE
salary>=10000 AND salary<=20000;
#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
#三、模糊查询
/*
like
​


between and
in
is null|is not null
​
*/
#1.like
/*
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符,包含0个字符
_ 任意单个字符
*、
​
#案例1：查询员工名中包含字符a的员工信息
​
select
*
from
employees
where
last_name like '%a%';#abc
#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select
last_name,
salary
FROM
t_mysql_employees
WHERE
last_name LIKE '__n_l%';
​
​
​
#案例3：查询员工名中第二个字符为_的员工名
​
SELECT
last_name
FROM
t_mysql_employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
#2.between and
/*
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
​
*/
​
​
#案例1：查询员工编号在100到120之间的员工信息
​
SELECT
*
FROM
t_mysql_employees
WHERE
employee_id <= 120 AND employee_id>=100;
#----------------------
SELECT
*
FROM
t_mysql_employees
WHERE
employee_id BETWEEN 100 AND 120;
​
​
#3.in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表中不支持通配符

​
*/
#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
​
SELECT
last_name,
job_id
FROM
t_mysql_employees
WHERE
job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
​
​
#------------------
​
SELECT
last_name,
job_id
FROM
t_mysql_employees
WHERE
job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
​
#4、is null
/*
=或<>不能用于判断null值
is null或is not null 可以判断null值
​
​
​
​
*/
​
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct IS NULL;
​
​
#案例1：查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct IS NOT NULL;
​
#----------以下为×
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
​
WHERE
salary IS 12000;


#安全等于 <=>
​
​
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct <=>NULL;


#案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
t_mysql_employees
​
WHERE
salary <=> 12000;

​
#is null pk <=>
​
IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
<=>   :既可以判断NULL值，又可以判断普通的数值，可读性较低
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244

3) order by字句查询

#进阶3：排序查询
/*
语法：
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;
​
​
特点：
1、asc代表的是升序，可以省略
desc代表的是降序
​
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
​
3、order by子句在查询语句的最后面，除了limit子句
​
*/
​
#1、按单个字段排序
SELECT * FROM t_mysql_employees ORDER BY salary DESC;
​
#2、添加筛选条件再排序
​
#案例：查询部门编号>=90的员工信息，并按员工编号降序
​
SELECT *
FROM t_mysql_employees
WHERE department_id>=90
ORDER BY employee_id DESC;
​
​
#3、按表达式排序
#案例：查询员工信息 按年薪降序
​
​
SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM t_mysql_employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
​
​
#4、按别名排序
#案例：查询员工信息 按年薪升序
​
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM t_mysql_employees
ORDER BY 年薪 ASC;
​
#5、按函数排序
#案例：查询员工名，并且按名字的长度降序
​
SELECT LENGTH(last_name),last_name
FROM t_mysql_employees
ORDER BY LENGTH(last_name) DESC;
​
#6、按多个字段排序
​
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM t_mysql_employees
ORDER BY salary DESC,employee_id ASC;
​


排序练习

#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
​
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM t_mysql_employees
ORDER BY 年薪 DESC,last_name ASC;
​
​
#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM t_mysql_employees
​
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
​
#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
​
SELECT *,LENGTH(email)
FROM t_mysql_employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
​
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

4）分组查询

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
​
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
​
特点：
1、sum、avg一般用于处理数值型
  max、min、count可以处理任何类型
2、以上分组函数都忽略null值
​
3、可以和distinct搭配实现去重的运算
​
4、count函数的单独介绍
一般使用count(*)用作统计行数
​
5、和分组函数一同查询的字段要求是group by后的字段
​
*/
​
​
#1、简单 的使用
SELECT SUM(salary) FROM t_mysql_employees;
SELECT AVG(salary) FROM t_mysql_employees;
SELECT MIN(salary) FROM t_mysql_employees;
SELECT MAX(salary) FROM t_mysql_employees;
SELECT COUNT(salary) FROM t_mysql_employees;
​
​
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;
​
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;
​
#2、参数支持哪些类型
​
SELECT SUM(last_name) ,AVG(last_name) FROM t_mysql_employees;
SELECT SUM(hiredate) ,AVG(hiredate) FROM t_mysql_employees;
​
SELECT MAX(last_name),MIN(last_name) FROM t_mysql_employees;
​
SELECT MAX(hiredate),MIN(hiredate) FROM t_mysql_employees;
​
SELECT COUNT(commission_pct) FROM t_mysql_employees;
SELECT COUNT(last_name) FROM t_mysql_employees;
​
#3、是否忽略null
​
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM t_mysql_employees;
​
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM t_mysql_employees;
​
SELECT COUNT(commission_pct) FROM t_mysql_employees;
SELECT commission_pct FROM t_mysql_employees;
​
​
#4、和distinct搭配
​
SELECT SUM(DISTINCT salary),SUM(salary) FROM t_mysql_employees;
​
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM t_mysql_employees;
​
​
​
#5、count函数的详细介绍
​
SELECT COUNT(salary) FROM t_mysql_employees;
​
SELECT COUNT(*) FROM t_mysql_employees;
​
SELECT COUNT(1) FROM t_mysql_employees;
​
效率：
MYISAM存储引擎下 ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
​
​
#6、和分组函数一同查询的字段有限制,employee_id是最小的那个
​
SELECT AVG(salary),employee_id FROM t_mysql_employees;
​
分组函数练习

#1.查询公司员工工资的最大值，最小值，平均值，总和
​
SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和
FROM t_mysql_employees;
#2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
​
SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM t_mysql_employees;
​
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM t_mysql_employees;
​
SELECT DATEDIFF('1995-2-7','1995-2-6');
​
​
#3.查询部门编号为90的员工个数
​
SELECT COUNT(*) FROM t_mysql_employees WHERE department_id = 90;
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102

五、视图

含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
​
比如：舞蹈班和普通班级的对比
创建语法的关键字是否实际占用物理空间使用
​
视图create view只是保存了sql逻辑增删改查，只是一般不能增删改
​
表create table保存了数据增删改查
​
#案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';
​
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;
​
SELECT * FROM v1 WHERE stuname LIKE '张%';
​
​
#一、创建视图
/*
语法：
create view 视图名
as
查询语句;
​
*/
USE myemployees;
​
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
​
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
​
​
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
​
​
​
​
​
​
#2.查询各部门的平均工资级别
​
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
​
#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
​
​
​
#3.查询平均工资最低的部门信息
​
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
​
#4.查询平均工资最低的部门名和工资
​
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
​
​
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
​
​
​
​
#二、视图的修改
​
#方式一：
/*
create or replace view 视图名
as
查询语句;
​
*/
SELECT * FROM myv3
​
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
​
#方式二：
/*
语法：
alter view 视图名
as
查询语句;
​
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
​
#三、删除视图
​
/*
​
语法：drop view 视图名,视图名,...;
*/
​
DROP VIEW emp_v1,emp_v2,myv3;
​
​
#四、查看视图
​
DESC myv3;
​
SHOW CREATE VIEW myv3;
​
​
#五、视图的更新
​
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;
​
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
​
​
SELECT * FROM myv1;
SELECT * FROM employees;
#1.插入
​
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
​
#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
​
#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';
​
#具备以下特点的视图不允许更新
​
​
#①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
​
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
​
SELECT * FROM myv1;
​
#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;
​
#②常量视图
CREATE OR REPLACE VIEW myv2
AS
​
SELECT 'john' NAME;
​
SELECT * FROM myv2;
​
#更新
UPDATE myv2 SET NAME='lucy';
​
​
​
​
​
#③Select中包含子查询
​
CREATE OR REPLACE VIEW myv3
AS
​
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
​
#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;
​
​
#④join
CREATE OR REPLACE VIEW myv4
AS
​
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
​
#更新
​
SELECT * FROM myv4;
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');
​
​
​
#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
​
SELECT * FROM myv3;
​
#更新
​
SELECT * FROM myv5;
​
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
​
​
​
#⑥where子句的子查询引用了from子句中的表
​
CREATE OR REPLACE VIEW myv6
AS
​
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
​
#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
​
​
视图练习

#一、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
​
#二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
​
​
SELECT d.*,m.mx_dep
FROM departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id`;
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276