课程目标：掌握开发中最常见的SQL语句和表关系及授权相关知识点。
课程概要：
上一节讲解了最基础SQL语句：增删改查，其实在日常的开发中还有很多必备的SQL语句。
这一部分的SQL语句都是围绕着对 表中的数据进行操作的。
提示：今天的所有操作都只会在 MySQL自带的客户端工具上进行操作。
例如：现在创建如下两张表。
create database day26db default charset utf8 collate utf8_general_ci;
create table depart(
id int not null auto_increment primary key,
title varchar(16) not null
)default charset=utf8;
create table info(
id int not null auto_increment primary key,
name varchar(16) not null,
email varchar(32) not null,
age int,
depart_id int
)default charset=utf8;
insert into depart(title) values("开发"),("运营"),("销售");
insert into info(name,email,age,depart_id) values("白居易","baijuyi@live.com",19,1);
insert into info(name,email,age,depart_id) values("杜甫","dufu@live.com",49,1);
insert into info(name,email,age,depart_id) values("emma","emma@live.com",9,2);
insert into info(name,email,age,depart_id) values("tony","tony@live.com",29,1);
insert into info(name,email,age,depart_id) values("kelly","kelly@live.com",99,3);
insert into info(name,email,age,depart_id) values("james","james@live.com",49,1);
insert into info(name,email,age,depart_id) values("李白","libai@live.com",49,1);
修改表名
ALTER TABLE table_name RENAME TO new_table_name
not null 指示某列不能存储null值
unique 保证某列的每一行必须有唯一的值
primary key 与not null 和 unique的结合，确保某列或多列有唯一标识，有助于更容易快速的找到表中的一个特定的记录。
foreign key 外键，保证一个表中的数据匹配另一个表中的值的参照完整性。
什么是外键
若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。
A为基本表，B为信息表
外键的作用：
①为了一张表记录的数据不要太过冗余。
②保持数据的一致性、完整性。
举例：
比如有两张表格：A学生档案，B上学期间的成绩单
A：学号，姓名。(学号为主键)
B：学期号，学号，平均分数（学期号，学号两个同时为主键，学号同时为外键）
为了保证B成绩单上的数据有效，所以要求录入学号时，必须保证档案中有这个学号，否则就不能录入。
从而保证了成绩单上的成绩数据的引用完整，否则将会是垃圾数据。
check 保证列中的值符合指定的条件
default 规定没有给列赋值时的默认值
根据条件搜索结果。
select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4; -- id大于等于2、且小于等于4
select * from info where name = '白居易' and age = 19;
select * from info where name = 'emma' or age = 49;
# 括号控制优先级
select * from info where (name = '李白' or email="dufu@live.com") and age=49;
select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
# 子查询
select * from info where id in (select id from depart); # 子查询只能写一列
# select * from info where id in (1,2,3);
# exists select * from depart where id=5，去查数据是否存在，如果存在则执行，如果不存在前面的info则不会去搜索。
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);
# 重点记一下
select * from (select * from info where id>2) as T where age > 10;
# 相等于执行了select * from (select * from info where id>2) as T 给这条语句查询的数据起一个别名T，where的条件则是在T表的条件下再一次过滤。
mysql> select * from (select * from info where id>2) as T;
+----+--------+----------------+------+-----------+
| id | name | email | age | depart_id |
+----+--------+----------------+------+-----------+
| 3 | emma | emma@live.com | 9 | 2 |
| 4 | tony | tony@live.com | 29 | 1 |
| 5 | kelly | kelly@live.com | 99 | 3 |
| 6 | james | james@live.com | 49 | 1 |
| 7 | 李白 | libai@live.com | 49 | 1 |
+----+--------+----------------+------+-----------+
5 rows in set (0.00 sec)
mysql> select * from (select * from info where id>2) as T where age > 10;
+----+--------+----------------+------+-----------+
| id | name | email | age | depart_id |
+----+--------+----------------+------+-----------+
| 4 | tony | tony@live.com | 29 | 1 |
| 5 | kelly | kelly@live.com | 99 | 3 |
| 6 | james | james@live.com | 49 | 1 |
| 7 | 李白 | libai@live.com | 49 | 1 |
+----+--------+----------------+------+-----------+
select * from info where info.id > 10;
select * from info where id > 10;
# 查两张表时则使用上面这种格式,注意下，连表查询时需要区分
select * from info,depart where info.id > 10;
一般用于模糊搜索。
select * from info where name like "%居%";
select * from info where name like "%居";
select * from info where email like "%@live.com";
select * from info where name like "白%易";
select * from info where name like "k%y";
select * from info where email like "baijuyi%";
# _和like可以拼接起来使用，_表示一个字符
select * from info where email like "_@live.com";
select * from info where email like "_aijuyi@live.com";
select * from info where email like "__ijuyi@live.com";
select * from info where email like "__ijuyi_live.co_";
注意：数量少，数据量大的搜索。
想要获取的列。
# 展示了所有列和所有行
select * from info;
# 指定列和行
select id, name from info;
# as 别名
# 将name列改成了NM
select id, name as NM from info;
mysql> select id as nub, name as nm from info;
+-----+-----------+
| nub | nm |
+-----+-----------+
| 1 | 白居易 |
| 2 | 杜甫 |
| 3 | emma |
| 4 | tony |
| 5 | kelly |
| 6 | james |
| 7 | 李白 |
+-----+-----------+
# 额外添加一列age,每一行都是123
select id, name as NM, 123 from info;
mysql> select id,name as NM, 123 from info;
+----+-----------+-----+
| id | NM | 123 |
+----+-----------+-----+
| 1 | 白居易 | 123 |
| 2 | 杜甫 | 123 |
| 3 | emma | 123 |
| 4 | tony | 123 |
| 5 | kelly | 123 |
| 6 | james | 123 |
| 7 | 李白 | 123 |
+----+-----------+-----+
7 rows in set (0.00 sec)
#
mysql> select id,name,123 as age from info;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | 白居易 | 123 |
| 2 | 杜甫 | 123 |
| 3 | emma | 123 |
| 4 | tony | 123 |
| 5 | kelly | 123 |
| 6 | james | 123 |
| 7 | 李白 | 123 |
+----+-----------+-----+
7 rows in set (0.00 sec)
# 添加多列
mysql> select id,name,123 as xx,456 as age from info;
+----+-----------+-----+-----+
| id | name | xx | age |
+----+-----------+-----+-----+
| 1 | 白居易 | 123 | 456 |
| 2 | 杜甫 | 123 | 456 |
| 3 | emma | 123 | 456 |
| 4 | tony | 123 | 456 |
| 5 | kelly | 123 | 456 |
| 6 | james | 123 | 456 |
| 7 | 李白 | 123 | 456 |
+----+-----------+-----+-----+
7 rows in set (0.00 sec)
### 注意：少写select * ,自己需求。
# 有什么应用场景？
select
id,
name,
666 as num,
( select max(id) from depart ) as mid, -- max/min/sum，且子查询中只能有一个值
( select min(id) from depart) as nid, -- max/min/sum
age
from info;
# 查看执行效果
mysql> select
-> id,
-> name,
-> 666 as num,
-> ( select max(id) from depart ) as mid, -- max/min/sum，且子查询中只能有一个值
-> ( select min(id) from depart) as nid, -- max/min/sum
-> age
-> from info;
+----+-----------+-----+------+------+------+
| id | name | num | mid | nid | age |
+----+-----------+-----+------+------+------+
| 1 | 白居易 | 666 | 6 | 1 | 19 |
| 2 | 杜甫 | 666 | 6 | 1 | 49 |
| 3 | emma | 666 | 6 | 1 | 9 |
| 4 | tony | 666 | 6 | 1 | 29 |
| 5 | kelly | 666 | 6 | 1 | 99 |
| 6 | james | 666 | 6 | 1 | 49 |
| 7 | 李白 | 666 | 6 | 1 | 49 |
+----+-----------+-----+------+------+------+
select
id,
name,
( select title from depart where depart.id=info.depart_id) as x1
from info;
# 注意：效率很低
mysql> select
-> id,
-> name,
-> ( select title from depart where depart.id=info.depart_id) as x1
-> from info;
+----+-----------+--------+
| id | name | x1 |
+----+-----------+--------+
| 1 | 白居易 | 开发 |
| 2 | 杜甫 | 开发 |
| 3 | emma | 运营 |
| 4 | tony | 开发 |
| 5 | kelly | 销售 |
| 6 | james | 开发 |
| 7 | 李白 | 开发 |
+----+-----------+--------+
7 rows in set (0.00 sec)
select
id,
name,
( select title from depart where depart.id=info.depart_id) as x1,
( select title from depart where depart.id=info.id) as x2
from info;
mysql> select
-> id,
-> name,
-> ( select title from depart where depart.id=info.depart_id) as x1,
-> ( select title from depart where depart.id=info.id) as x2
-> from info;
+----+-----------+--------+--------+
| id | name | x1 | x2 |
+----+-----------+--------+--------+
| 1 | 白居易 | 开发 | 开发 |
| 2 | 杜甫 | 开发 | 运营 |
| 3 | emma | 运营 | 销售 |
| 4 | tony | 开发 | 开发 |
| 5 | kelly | 销售 | 运营 |
| 6 | james | 开发 | 销售 |
| 7 | 李白 | 开发 | NULL |
+----+-----------+--------+--------+
7 rows in set (0.00 sec)
# 如果满足则显示 “第一部门”，不满足则默认显示null
select
id,
name,
case depart_id when 1 then "第1部门" end v1
from info;
# 如果满足则显示 “第一部门”，不满足则显示其他
select
id,
name,
case depart_id when 1 then "第1部门" else "其他" end v2
from info;
# 如果等于 1 显示 第一部门，等于2显示其第二部门，否则显示其他，v1表示列名
select
id,
name,
case depart_id when 1 then "第1部门" end v1,
case depart_id when 1 then "第1部门" else "其他" end v2,
case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
case when age<18 then "少年" end v4,
case when age<18 then "少年" else "油腻男" end v5,
case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;
select * from info order by age desc; -- 降序
select * from info order by age asc; -- 升序
select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大；如果age相同则按照id从大到小。
# 先按条件搜索出来结果再去排序
select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;
order by 还可以配合first()，last()，函数使用，取出指定列第一个或最后一个数据。
first()函数语法: SELECT FIRST(column_name列名) FROM table_name表名
last()函数语法：SELECT LAST(column_name) FROM table_name
一般要用于获取部分数据。
select * from info limit 5; -- 获取前5条数据
select * from info order by id desc limit 3; -- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3; -- 先排序，再获取前3条数据
# offset偏移量
select * from info limit 3 offset 2; -- 从位置2开始，向后获取前3数据
# 偏移量offset的最小值是0
语法:
limit <获取的行数> [offset <跳过的行数>]
或者 limit [<跳过的行数>，] <获取的行数>
尽量配合order by 才有意义。
数据库表中：1000条数据。
select * from info limit 10 offset 0;
select * from info limit 10 offset 10;
select * from info limit 10 offset 20;
select * from info limit 10 offset 30;
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
# 根据age进行分组
select xxx from info group by age;
# 那么问题来了，age合并后，前面不同数据怎么显示，一般不需要写，直接写分组的列就行，或者配合聚合函数，如下：
select age from info group by age;
# 聚合函数max,min,统计count(id)有多少个age相同，也可以写成count(name)或count(1)，sum和，avg平均数
select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
# 查询出每个年龄的人有多少个
select age,count(1) from info group by age;
# 查询每个部门有多少个人
select depart_id,count(id) from info group by depart_id;
# 查询每个部门有多少个人，且条件是部门人数大于二，不能使用where，使用having
select depart_id,count(id) from info group by depart_id having count(id) > 2;
# 如果基于 group by 进行分组，分组聚合后的结果进行二次搜索必须使用having
# 聚合函数可以单独使用，更多基本上都是基于group by进行搭配使用
select count(id) from info;
select max(id) from info;
select age,name from info group by age; -- 不建议
select * from info where id in (select max(id) from info group by age);
select age,count(id) from info group by age having count(id) > 2;
select age,count(id) from info where id > 4 group by age having count(id) > 2; -- 聚合条件放在having后面
到目前为止SQL执行顺序：
where
group by
having
order by
limit
select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条
count()情况：
1.count(1):会统计表中的所有的记录数，包含字段为null 的记录。
2.count(字段):会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
3.count(*)统计所有的列，相当于行数，统计结果中会包含字段值为null的列
count执行效率：
列名为主键，count(列名)比count(1)快，列名不为主键，count(1)会标count(列名)快
如果表中多个列且没有主键，则coun(1)的执行效率优于count(*)
如果有主键，则select count(主键)的执行效率是最优的，如果表中只有一个字段，则select count(*)最优
多个表可以连接起来进行查询。
展示用户信息&部门名称：
# 多表连接继续在后面添加
select
列1,列2
from
主表
left outer join 从表 on 主表.x = 从表.id
left outer join 从表 on 主表.x = 从表.id
where 条件
# 例如：
select
student.sid,student.sname,score.num,course.cname
from
score
left join student on score.student_id=student.sid
left join course on score.course_id=course.cid
where num < 60;
select * from info left outer join depart on info.depart_id = depart.id;
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
从表 right outer join 主表 on 主表.x = 从表.id
select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
为了更加直接的查看效果，我们分别在 depart 表 和 info 中额外插入一条数据。
insert into depart(title) values("运维");
这样一来主从表就有区别：
info主表，就以info数据为主，depart为辅。
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
# 只显示主表与辅表关联的数据，没有关联的则不显示
depart主表，，就以depart数据为主，info为辅。
select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
# 主表数据与辅表中数据没有关联的依然显示，辅表中则显示为null
总结：谁是主表，就以谁的数据为主，有关联则显示，没有关联的则通过null去填充
# 推荐使用左连接。
# info为从表，depart为从表
select * from info left outer join depart on ....
# depart 为主表，info为从表
select * from depart left outer join info on ....
简写：select * from depart left join info on ....
-- 内连接(不分主从): 表 inner join 表 on 条件
select * from info inner join depart on info.depart_id=depart.id;
+----+-----------+------------------+------+-----------+----+--------+
| id | name | email | age | depart_id | id | title |
+----+-----------+------------------+------+-----------+----+--------+
| 1 | 白居易 | baijuyi@live.com | 19 | 1 | 1 | 开发 |
| 2 | 杜甫 | dufu@live.com | 49 | 1 | 1 | 开发 |
| 3 | emma | emma@live.com | 9 | 2 | 2 | 运营 |
| 4 | tony | tony@live.com | 29 | 1 | 1 | 开发 |
| 5 | kelly | kelly@live.com | 99 | 3 | 3 | 销售 |
| 6 | james | james@live.com | 49 | 1 | 1 | 开发 |
| 7 | 李白 | libai@live.com | 49 | 1 | 1 | 开发 |
+----+-----------+------------------+------+-----------+----+--------+
# 只显示有关联的信息
到目前为止SQL执行顺序：
join
on
where
group by
having
order by
limit
写在最后：多张表也可以连接。
小结：
A inner join B 取交集。
A left join B 取 A 全部，B 没有对应的值为 null。
A right join B 取 B 全部 A 没有对应的值为 null。
A full outer join B 取并集，彼此没有对应的值为 null。 mysql中不支持full
对应条件在 on 后面填写
select id,title from depart
union
select id,name from info;
select id,title from depart
union
select email,name from info;
-- 列数需相同
select id from depart
union
select id from info;
-- 自动去重
select id from depart
union all
select id from info;
-- 不去重，保留所有
一定要注意，必须是完全一样的数据才可以去重。
一定不要将主键忽视， 有主键存在的情况下是不可能去重的
[
{'id': 1,'name':'jason','age':18},
{'id': 2,'name':'jason','age':18},
{'id': 3,'name':'egon','age':18}
]
select distinct id,age from emp; # id是主键，不能去重
select ditinct age form emp; # 不带id可以去重
ORM 对象关系映射
到目前为止，你已经掌握了如下相关指令（SQL语句）：
之前我们无论是基于Python代码 or 自带客户端 去连接MySQL时，均使用的是 root 账户，拥有对MySQL数据库操作的所有权限。
如果有多个程序的数据库都放在同一个MySQL中，如果程序都用root账户就存在风险了。
# 清除SQL语句
system clear;
这种情况怎么办呢？
在MySQL中支持创建账户，并给账户分配权限，例如：只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。
在MySQL的默认数据库 mysql 中的 user 表中存储着所有的账户信息（含账户、权限等）。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| day26 |
| mysql |
| performance_schema |
| sys |
+--------------------+
10 rows in set (0.00 sec)
mysql> select user,authentication_string,host from mysql.user;
+----------------------------------+-------------------------------------------+-------------------------------+
| user | authentication_string | host |
+----------------------------------+-------------------------------------------+-------------------------------+
| root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
+----------------------------------+-------------------------------------------+-------------------------------+
3 rows in set (0.00 sec)
创建和删除用户
create user '用户名'@'连接者的IP地址' identified by '密码';
create user baijuyi1@127.0.0.1 identified by 'root123';
drop user baijuyi1@127.0.0.1;
create user baijuyi2@'127.0.0.%' identified by 'root123';
drop user baijuyi2@'127.0.0.%';
create user baijuyi3@'%' identified by 'root123';
drop user baijuyi3@'%';
create user 'baijuyi4'@'%' identified by 'root123';
drop user 'baijuyi4'@'%';
修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
rename user baijuyi1@127.0.0.1 to baijuyi1@localhost;
rename user 'baijuyi1'@'127.0.0.1' to 'baijuyi1'@'localhost';
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
set password for 'baijuyi4'@'%' = Password('123123');
创建好用户之后，就可以为用户进行授权了。
授权
grant 权限 on 数据库.表 to '用户'@'IP地址'
grant all privileges on *.* TO 'baijuyi'@'localhost'; -- 用户baijuyi拥有所有数据库的所有权限
grant all privileges on day26.* TO 'baijuyi'@'localhost'; -- 用户baijuyi拥有数据库day26的所有权限
grant all privileges on day26.info TO 'baijuyi'@'localhost'; -- 用户baijuyi拥有数据库day26中info表的所有权限
grant select on day26.info TO 'baijuyi'@'localhost'; -- 用户baijuyi拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'baijuyi'@'localhost'; -- 用户baijuyi拥有数据库day26所有表的查询和插入权限
grant all privileges on day26db.* to 'baijuyi4'@'%';
注意：flush privileges; -- 将数据读取到内存中，从而立即生效。
对于权限
all privileges 除grant外的所有权限
select 仅查权限
select,insert 查和插入权限
...
usage 无访问权限
alter 使用alter table
alter routine 使用alter procedure和drop procedure
create 使用create table
create routine 使用create procedure
create temporary tables 使用create temporary tables
create user 使用create user、drop user、rename user和revoke all privileges
create view 使用create view
delete 使用delete
drop 使用drop table
execute 使用call和存储过程
file 使用select into outfile 和 load data infile
grant option 使用grant 和 revoke
index 使用index
insert 使用insert
lock tables 使用lock table
process 使用show full processlist
select 使用select
show databases 使用show databases
show view 使用show view
update 使用update
reload 使用flush
shutdown 使用mysqladmin shutdown(关闭MySQL)
super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
replication client 服务器位置的访问
replication slave 由复制从属使用
对于数据库和表
数据库名.* 数据库中的所有
数据库名.表名 指定数据库中的某张表
数据库名.存储过程名 指定数据库中的存储过程
*.* 所有数据库
查看授权
show grants for '用户'@'IP地址'
show grants for 'baijuyi'@'localhost';
show grants for 'baijuyi4'@'%';
取消授权
revoke 权限 on 数据库.表 from '用户'@'IP地址'
revoke ALL PRIVILEGES on day26.* from 'baijuyi'@'localhost';
revoke ALL PRIVILEGES on day26db.* from 'baijuyi4'@'%';
注意：flush privileges; -- 将数据读取到内存中，从而立即生效。
一般情况下，在很多的 正规 公司，数据库都是由 DBA 来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IZconatg-1648180045392)(images/image-20210520173921204.png)]
本节主要讲解的三大部分的知识点：