day26 必备SQL和表关系及授权
课程目标:掌握开发中最常见的SQL语句和表关系及授权相关知识点。
课程概要:
- 必备SQL(8个必备)
- 表关系
- 授权
1. 必备SQL语句
上一节讲解了最基础SQL语句:增删改查,其实在日常的开发中还有很多必备的SQL语句。
这一部分的SQL语句都是围绕着对表中的数据进行操作。
例如:现在创建如下两张表。
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("轩小陌","xuan@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);
1.1 条件
根据条件搜索结果。
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;
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);
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 info where info.id > 1;
select * from (select * from info where id>2) as T where age > 10;
select * from (select * from info where id>2) as T where T.age > 10;
1.2 通配符
一般在数据表中用于模糊搜索。
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 "xuan%";
select * from info where email like "_@live.com";
select * from info where email like "_uan@live.com";
select * from info where email like "__an@live.com";
select * from info where email like "_ua_@live.co_";
注意:数据量少可以使用以上模糊搜索,但如果数据量大便不适合使用该模糊搜索的方式。
1.3 映射
获取指定的列。
select * from info;
建议:少用select * ,根据自己需求选取需要的列。
select id, name from info;
select id, name as NM from info;
select id, name as NM, 123 as age from info;
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;
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;
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;
1.4 排序
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;
1.5 取部分
用于获取部分数据。
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条数据
数据库表中: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;
- …
1.6 分组
-- 按age分组后取每组id最大的,取每组id最小的,每组计数,每组id求和,每组id求平均值
select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
-- 按age分组后对每组数据计数
select age,count(1) from info group by age;
-- 按depart_id分组后对每组数据计数
select depart_id,count(id) from info group by depart_id;
-- 按depart_id分组后对每组数据计数,再取计数大于2的数据
select depart_id,count(id) from info group by depart_id having count(id) > 2;
-- 按age分组后取每组id最大的那条数据,再取info表中有这些id的数据
select * from info where id in (select max(id) from info group by age);
-- 按age分组后对每组数据计数,再取计数大于2的数据
select age,count(id) from info group by age having count(id) > 2;
-- 先取info中id>4的数据,再按age分组,再对每组数据计数,最后取计数大于2的数据
select age,count(id) from info where id > 4 group by age having count(id) > 2;
到目前为止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条
1.7 左右连表
将多个表连接起来进行查询。
展示 info 表中所有的用户信息 + 部门名称列:
-- 格式一:主表 left outer join 从表 on 连接条件(主表.x = 从表.id)
-- 简写:主表 left join 从表 on 连接条件(主表.x = 从表.id)
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)
-- 简写:主表 right 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 表中额外插入一条数据:
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;
-- 内连接格式:表1 inner join 表2 on 连接条件(表1.x = 表2.id)
select * from info inner join depart on info.depart_id=depart.id;
到目前为止SQL执行语句的优先级顺序:
join
on
where
group by
having
order by
limit
注意:不仅两张表可以连接,多张表也可以连接。
1.8 联合
-- 列数相同即可上下连接,数据类型不同也可以连接成功
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;
小结
到目前为止,已经掌握了如下相关指令(SQL语句):
- 数据库
- 数据表
- 数据行
- 增加
- 删除
- 修改
- 查询(各种变着花样的查询)
2.表关系
在开发项目时,需要根据业务需求去创建很多的表结构,以此来实现业务逻辑,一般表结构有三类:
2.1 单表,单独一张表就可以将信息保存。
2.2 一对多,需要两张表来存储信息,且两张表存在 一对多
或 多对一
关系。
、
2.3 多对多,需要三张表来存储信息,两张单表 + 关系表,创造出两个单表之间多对多关系
。
-
在上述的表:一对多的
info.depart_id
字段、多对多的boy_girl.boy_id
、girl_id
直接用整型存储就可以,因为他们只要存储关联表的主键ID即可。 -
另外在开发中往往还会为他们添加一个外键约束,保证某一个列的值必须是其他表中的特定列已存在的值,例如:
info.depart_id
的值必须是depart.id
中已存在的值。
一对多示例:
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;
如果表结构已创建好了,额外想要增加外键约束:
alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);
删除外键约束:
alter table info drop foreign key fk_info_depart;
多对多示例:
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;
在以后项目开发时,设计表结构及其关系的是一个非常重要的技能。一般项目开始开发的步骤:
- 需求调研
- 设计数据库表结构(根据需求)
- 项目开发(写代码)
大量的工作应该放在前2个步骤,前期的设计完成之后,后续的功能代码开发就比较简单了。
案例:
-- 创建数据库
create database course default charset utf8 collate utf8_general_ci;
-- 进入数据库
use course;
-- 创建用户表
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;
-- 创建课时表:day
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;
-- 创建课时表:video
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;
3. 授权
之前我们无论是基于 Python 代码还是基于 MYSQL 自带的客户端 去连接 MySQL 时,均使用的是root
账户,拥有对 MySQL 数据库操作的所有权限。
如果有多个程序的数据库都放在同一个MySQL中,且这些程序都使用同一个root
账户,那就存在风险了。这种情况该怎么办呢?
在MySQL中支持创建账户,并给账户分配权限,例如:只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。
3.1 用户管理
在 MySQL 的默认数据库 mysql
中的 user
表中存储着所有的账户信息(含账户、权限等)。
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)
3.1.1 创建和删除用户
-- 格式:create user '用户名'@'连接者的IP地址' identified by '密码';
-- 创建用户,限制连接ip为:127.0.0.1
create user xuanxiaomo@127.0.0.1 identified by 'root123';
-- 删除用户
drop user xuanxiaomo@127.0.0.1;
-- 创建用户,限制连接ip为:127.0.0.*
create user xuanxiaomo@'127.0.0.%' identified by 'root123';
-- 删除用户
drop user xuanxiaomo@'127.0.0.%';
-- 创建用户,不限制连接ip
create user xuanxiaomo@'%' identified by 'root123';
-- 删除用户
drop user xuanxiaomo@'%';
-- 创建用户,用户名统一用引号(可加可不加)
create user 'xuanxiaomo'@'%' identified by 'root123';
-- 删除用户
drop user 'xuanxiaomo'@'%';
3.1.2 修改用户
-- 格式:rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
rename user xuanxiaomo@127.0.0.1 to xuanxiaomo@localhost;
rename user 'xuanxiaomo'@'127.0.0.1' to 'xuanxiaomo'@'localhost';
3.1.3 修改密码
-- 格式:set password for '用户名'@'IP地址' = Password('新密码')
set password for xuanxiaomo@'%' = Password('123123');
set password for 'xuanxiaomo'@'%' = Password('123123');
3.2 授权管理
创建好用户之后,就可以为用户进行授权了。
3.2.1 授权
-- 格式:grant 权限 on 数据库.表 to '用户'@'IP地址'
grant all privileges on *.* TO 'xuanxiaomo'@'localhost'; -- 用户xuanxiaomo拥有所有数据库的所有权限
grant all privileges on day26.* TO 'xuanxiaomo'@'localhost'; -- 用户xuanxiaomo拥有数据库day26的所有权限
grant all privileges on day26.info TO 'xuanxiaomo'@'localhost'; -- 用户xuanxiaomo拥有数据库day26中info表的所有权限
grant select on day26.info TO 'xuanxiaomo'@'localhost'; -- 用户xuanxiaomo拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'xuanxiaomo'@'localhost'; -- 用户xuanxiaomo拥有数据库day26所有表的查询和插入权限
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 -- 由复制从属使用
-
对于数据库和表
数据库名.* -- 数据库中的所有表 数据库名.表 -- 数据库中的某张表 数据库名.存储过程名 -- 数据库中的存储过程 *.* -- 所有数据库的所有表
3.2.2 查看授权
-- 格式:show grants for '用户'@'IP地址'
show grants for 'xuanxiaomo'@'localhost';
show grants for 'xuanxiaomo'@'%';
3.2.3 取消授权
-- 格式:revoke 权限 on 数据库.表 from '用户'@'IP地址'
revoke ALL PRIVILEGES on day26.* from 'xuanxiaomo'@'localhost';
revoke ALL PRIVILEGES on day26db.* from 'xuanxiaomo'@'%';
flush privileges; -- 将取消授权数据读取到内存中,从而让取消授权立即生效
一般情况下,在很多的公司,数据库都是由 DBA 来统一进行管理,DBA为每个项目的数据库创建用户,并赋予相关的权限。
今日总结
本节主要讲解的三大部分的知识点:
- 常见SQL语句,项目开发中使用最频繁的知识点。
- 表关系,项目开发前,项目表结构设计时必备知识点。
- 单表
- 一对多
- 多对多
- 授权,在 MySQL 中创建用户并赋予相关权限。
今日作业
详见 day27