MySQL数据库（2/5）必备SQL、表关系、授权

即将讲解的8个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("武沛齐","wupeiqi@live.com",19,1);
insert into info(name,email,age,depart_id) values("于超","pyyu@live.com",49,1);
insert into info(name,email,age,depart_id) values("alex","alex@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("李杰","lijie@live.com",49,1);

image

一、必备SQL语句（主要围绕对表中数据进行操作）

1.1 条件查询

select * from 表名 where 条件；
（1）运算符：> < >= <= = !=
（2）between 1 and 8 # 大于等于1并且小于等于8
（3）id in (1, 2, 3) # id在（1， 2， 3）中
（4）id not in (1,2,3) # id不在(1,2,3)中
（5）[not] exists(select * from depart where id=5)
select * from depart where id=5 如果该搜索结果存在，则执行主查询，否则主查询不执行
（6）select * from (select * from info where id>2) as T where age > 10;
先执行子查询select * from info where id>2，如果有结果则 as T，再执行外边的查询。

举例：

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 = 'alex' or age = 49;
select * from info where (name = '李杰' or email="pyyu@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，去查数据是否存在，如果存在继续主查询，如果不存在停止主查询。
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 info where info.id > 10;
select * from info where id > 10;

1.2 通配符（百分号% 、下划线 _、like）

作用：一般用于模糊搜索。
% ：代表n个字符；
_ : 代表一个字符。
like：配合like关键字使用。

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 "wupeiqi%";


select * from info where email like "_@live.com";
select * from info where email like "_upeiqi@live.com";
select * from info where email like "__peiqi@live.com";
select * from info where email like "__peiqi_live.co_";

【注意】：模糊搜索只适合数量少的搜索，数据量大的搜索绝对不能使用，因为效率低。

1.3 映射（指定要搜索那几列，select * 搜索所有列）

select * from info;

select id, name				from info;
select id, name as NM 		from info;   -- as取别名，搜索结果表头显示为NM
select id, name as NM, 123  from info;   -- 固定值123，搜索结果会增加一列，表头是123，每条记录的该列值都为123，也可以写成123  as  value，则表头变成value，值都为123. 
注意：少些select * ,自己需求。

select 
	id,
	name,
	666 as num,
	( select max(id) from depart ) as mid, -- max/min/sum，这里子查询会返回一个值3，语法要求，这里只能是一个值才行， 因此这行相当于 3 as  mid
	( select min(id) from depart) as nid, -- max/min/sum
	age
from info;

【总结】以后的开发中少用select * ，而是根据需求，指查找需要的列。

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1
from info;

# 注意：效率很低

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;

image

【工作原理】将info表中每行的depart_id拿出来，找到depart表中与它相同id的那一行，把那一行的title值取出，作为这一行x1的值。缺点：这样查询的效率很低，因为info里面的每一条depart_id都要到depart表中对比一遍，因此效率很低。用的很少，了解即可。

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;

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;

Sql 中使用case when then 判断某字段是否为null，和判断是否为 字符 或 数字 时的写法不一样:

如果不注意，很容易搞错:

正确方法:

CASE WHEN columnName is null THEN 0 ELSE columnName END 自定义字段名

如果 'columnName ’ 值是 ‘null’ 那么返回 ‘0’ ，否则返回‘columnName ’值。
image

如上图，找到符合条件的（depart_id = 1）则v1列的值为“第一部门”，否则为NULL。

image

还可以写多个when then ， 依次类推。

【总结】case 字段名 when 值1 then 值2 else 值3 end 表头 ，相当于python中的if else 结构。case后面也可以不跟字段名。（case用的不多）

1.4 排序 （order by）

作用：将查询到的数据集排序。

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;

image

【总结】desc 倒序，asc 顺序。【记忆技巧：字母表中a在d前面，asc为顺序，desc为倒序】

SQL语句执行优先级：先执行where，查询到符合条件的数据集后再按照排序规则排序。

1.5 取部分 （limit）

作用：一般用于获取部分数据。

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条数据


select * from info limit 3 offset 2;	-- 从位置2开始，向后获取前3数据

image

【limit 3 offset 2】从位置2开始向后取3条数据，表的起始记录offset值为 0.

【应用场景】假如数据库表中有1000条数据，那么给客户展示的时候不能一下子展示1000条，可以基于limit offset 分页显示：

  • 第一页：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;
  • ...

1.6 分组 （group by）

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

image

【总结】group by 后面的字段名可以写在select后面，但是其他字段名不能写在select后面，因为搜索结果集中每行只能有一个数据，那么按照age分组后，可能3条数据符合分组的条件，那么sql不知道该把其他3个字段值中的哪个放在结果中，因此，其他字段名只能通过聚合函数的形式放在select后面，常用的聚合函数有 max、min、count、sum、avg等。

select age,count(1) from info group by age;

【总结】 count(1) 相当于临时增加了一列，值和表头都为1，用于统计每个年龄的人的数量。

select depart_id,count(id) from info group by depart_id;

image

按照部门统计每个部门的人数。

select depart_id,count(id) from info group by depart_id having count(id) > 2;

image

【注意】对于按照部门分组后的数据再次进行筛选部门人数大于2的条件，不能使用where，而是使用 having。

select count(id) from info;
select max(id) from info;

image

【总结】聚合函数可以单独使用，但是更多的是跟group by结合使用。

select age,max(id),min(id),sum(id),count(id) from info group by age;

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执行顺序：
（1）where
（2）group by
（3）having
（4）order by
（5）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条

1.7 左右连表 （left outer join、inner join）

作用： 多个表可以连接起来进行查询。

展示用户信息&部门名称：

主表 left outer join 从表 on 主表.x = 从表.x 

select * from info left outer join depart on info.depart_id = depart.id;

image

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

image

【总结】可以指定要搜索的字段。

从表 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;

image

【总结】right outer join 左边是从表，右边是主表。此时

为了更加直接的查看效果，我们分别在 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;

image

  • depart主表，，就以depart数据为主，info为辅。

    select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

image

【总结】上图可见， info里面没有的depart_id,info表信息显示为NULL。

select * from info left outer join depart on ....
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 | 武沛齐    | wupeiqi@live.com |   19 |         1 |  1 | 开发   |
|  2 | 于超      | pyyu@live.com    |   49 |         1 |  1 | 开发   |
|  3 | alex      | alex@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 | 李杰      | lijie@live.com   |   49 |         1 |  1 | 开发   |
+----+-----------+------------------+------+-----------+----+--------+

【总结】到目前为止，SQL执行顺序变化为：

到目前为止SQL执行顺序：
    join 
    on 
    where 
    group by
    having 
    order by
    limit

写在最后：多张表也可以连接。

1.8 联合（union）

【用的很少，了解即可。】

可以简称上下连接。

select id,title from depart 
union
select id,name from info;

image

select id,title from depart 
union
select email,name from info;
-- 列数需相同

【注意】两个表选择的列数必须相同。

select id from depart 
union
select id from info;

-- 自动去重

【自动去重】

image

select id from depart 
union all
select id from info;

-- 保留所有

【保留所有数据，不去重】

image

小结

到目前为止，你已经掌握了如下相关指令（SQL语句）：

  • 数据库
  • 数据表
  • 数据行
    • 增加
    • 删除
    • 修改
    • 查询（必备8种SQL，各种变着花样的查询）

二、表关系

在开发项目时，需要根据业务需求去创建很多的表结构，以此来实现业务逻辑，一般表结构有三类：

  • 单表，单独一张表就可以将信息保存。
    image

  • 一对多，需要两张表来存储信息，且两张表存在 一对多 或 多对一关系。
    image

  • 多对多，需要三张表来存储信息，两张单表 + 关系表，创造出两个单表之间多对多关系。
    image

在上述的表：一对多的 info.depart_id字段、多对多的 boy_girl.boy_id 、girl_id 直接用整型存储就可以，因为他们只要存储关联表的主键ID即可。

在开发中往往还会为他们添加一个 外键约束，保证某一个列的值必须是其他表中的特定列已存在的值，例如：info.depart_id的值必须是 depart.id中已存在的值。

【一对多示例】
image

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 not null,
    constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;

【新建外键约束】对于新建的一张表，可以使用上面sql语句，最后一句解析：constraint ... foreign key ... references ... , 创建一个外键约束。 fk_info_depart命名规则：fk：foreign key缩写，info 需要被约束的表，depart用来约束的表。 foreign key 后面括号中是创建表被约束的字段名， references 后面是用来约束的表名，括号中是用来约束的字段名。

【添加外键约束】如果表结构已创建好了，额外想要增加外键：

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

image

如上图，插入的值不在外键值范围内会报错。

删除外键：

alter table info drop foreign key fk_info_depart;

【多对多示例】

image

create table boy(
	id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;

create table girl(
	id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;


create table boy_girl(
	id int not null auto_increment primary key,
    boy_id int not null,
    girl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;

如果表结构已创建好了，额外想要增加外键：

alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);

删除外键：

alter table info drop foreign key fk_boy_girl_boy;
alter table info drop foreign key fk_boy_girl_girl;

【注意】表关系的设计和创建非常重要，如果一个项目的时间是100天，那么50-60天用来做项目功能需求调研和合理的表结构的设计，只有这些都做好了，才会做功能开发。其实一个项目耗时比较多的是前期的设计，尤其是表结构设计，等设计好了，后期开发就很快了，如果前期表结构没有设计好，后面发现不对劲再去修改、返工，那么时间反而浪费了，更耗时了。

【总结】

在以后项目开发时，设计表结构及其关系的是一个非常重要的技能。一般项目开始开发的步骤：

  • 需求调研
  • 设计数据库表结构（根据需求）
  • 项目开发（写代码）

大量的工作应该放在前2个步骤，前期的设计完成之后，后续的功能代码开发就比较简单了。

案例：简易版路飞学城

image

create database luffy default charset utf8 collate utf8_general_ci;

use luffy;

create table info(
	id int not null auto_increment primary key,
    username varchar(16) not null,
    mobile char(11) not null,
    password varchar(64) not null
)default charset=utf8;

create table course(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table module(
	id int not null auto_increment primary key,
    title varchar(16) not null,
    course_id int not null,
    constraint fk_module_course foreign key (course_id) references course(id)
)default charset=utf8;


create table day(
	id int not null auto_increment primary key,
    title varchar(16) not null,
	module_id int not null,
    constraint fk_day_module foreign key (module_id) references module(id)
)default charset=utf8;


create table video(
	id int not null auto_increment primary key,
    title varchar(16) not null,
	day_id int not null,
    constraint fk_video_day foreign key (day_id) references day(id)
)default charset=utf8;

create table module_record(
	id int not null auto_increment primary key,
    user_id int not null,
    module_id int not null,
    constraint fk_user_id foreign key module_record(user_id) references info(id),
    constraint fk_module_id foreign key module_record(module_id) references module(id)
)default charset=utf8;

三、授权 （MySQL用户及权限管理）

之前我们无论是基于Python代码 or 自带客户端 去连接MySQL时，均使用的是 root 账户，拥有对MySQL数据库操作的所有权限。

image

如果有多个程序的数据库都放在同一个MySQL中，如果程序都用root账户就存在风险了。

这种情况怎么办呢？

在MySQL中支持创建账户，并给账户分配权限，例如：只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。

3.1 用户管理

在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)

如上表，localhost表示用户允许登录的ip地址，这里代表本机。
原理上来说，对mysql.user表进行增删改查就能够对用户进行管理，但是这里使用了更简洁的方法来管理mysql的用户。

  • 创建和删除用户

    create user '用户名'@'连接者的IP地址' identified by '密码';

    create user wupeiqi1@127.0.0.1 identified by 'root123';  -- 创建一个账户，用户名wupeiqi，密码root123，可登录ip为127.0.0.1    
drop user wupeiqi1@127.0.0.1;  -- 删除该账户   

create user wupeiqi2@'127.0.0.%' identified by 'root123';  -- % 可以匹配任意一个值
drop user wupeiqi2@'127.0.0.%';

create user wupeiqi3@'%' identified by 'root123'; -- 这里表示可以在任意ip地址的主机上登录MySQL数据库。   
drop user wupeiqi3@'%';

create user 'wupeiqi4'@'%' identified by 'root123';
drop user 'wupeiqi4'@'%';

【注意】用户名和ip地址可以加引号，也可以不加，但是ip地址中出现%等特殊符号的时候，必须加引号，因此，统一都加上引号，这样就不容易出错。

  • 修改用户

    rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

    rename user wupeiqi1@127.0.0.1 to wupeiqi1@localhost;

rename user 'wupeiqi1'@'127.0.0.1' to 'wupeiqi1'@'localhost';

  • 修改密码

    set password for '用户名'@'IP地址' = Password('新密码')

    set password for 'wupeiqi4'@'%' = Password('123123');

3.2 授权管理

创建好用户之后，就可以为用户进行授权了。

  • 授权

    grant 权限 on 数据库.表 to   '用户'@'IP地址'

    grant all privileges on *.* TO 'wupeiqi'@'localhost';         -- 用户wupeiqi拥有所有数据库的所有权限
grant all privileges on day26.* TO 'wupeiqi'@'localhost';     -- 用户wupeiqi拥有数据库day26的所有权限
grant all privileges on day26.info TO 'wupeiqi'@'localhost';  -- 用户wupeiqi拥有数据库day26中info表的所有权限

grant select on day26.info TO 'wupeiqi'@'localhost';          -- 用户wupeiqi拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'wupeiqi'@'localhost';      -- 用户wupeiqi拥有数据库day26所有表的查询和插入权限

grant all privileges on day26db.* to 'wupeiqi4'@'%';


注意：flush privileges;   -- 将数据读取到内存中，从而立即生效。

举例：
image
image

  • 对于权限

    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       由复制从属使用

【注意】对于开发人员来说，用的最多的权限是 all privileges。

  • 对于数据库和表

    数据库名.*            数据库中的所有
数据库名.表名          指定数据库中的某张表
数据库名.存储过程名     指定数据库中的存储过程
*.*                  所有数据库

  • 查看授权

    show grants for '用户'@'IP地址'

    show grants for 'wupeiqi'@'localhost';
show grants for 'wupeiqi4'@'%';

  • 取消授权

    revoke 权限 on 数据库.表 from '用户'@'IP地址'

    revoke ALL PRIVILEGES on day26.* from 'wupeiqi'@'localhost';

revoke ALL PRIVILEGES on day26db.* from 'wupeiqi4'@'%';
注意：flush privileges;   -- 将数据读取到内存中，从而立即生效。

一般情况下，在很多的 正规 公司，数据库都是由 DBA 来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限。

总结

本节主要讲解的三大部分的知识点：

  • 常见SQL语句，项目开发中使用最频繁的知识点。
  • 表关系，项目开发前，项目表结构设计时必备知识点。
    • 单表
    • 一对多
    • 多对多
  • 授权，在MySQL中创建用户并赋予相关权限。

【需要必备技能】能够根据项目的业务需求，把它转换成数据库的表，转化成数据库表关系。

posted @ 2021-11-03 23:57  #缘起  阅读(91)  评论(0)  编辑  收藏  举报