【python开发】必备SQL和表关系及授权

一、必备SQL语句

现在，需要创建如下两张表：

表info

id	name	email	age	depart_id
1	高宇星	gao.yuxing@live.com	20	1
2	alex	alex@live.com	19	1
3	闫曦月	yan.xiyue@live.com	29	2
4	Tony	tony@livw.com	22	1
5	kelly	kelly@live.com	45	3
6	james	james@live.com	52	1
7	ammy	ammy@live.com	23	1

表depart

id	title
1	开发
2	运营
3	销售

创建表格

create database deployer 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 depart(title) values("运营");
insert into depart(title) values("销售");

insert into info(name, email, age, depart_id) values("高宇星","gao.yuxing@live.com", 20, 1);
insert into info(name, email, age, depart_id) values("alex","alex@live.com", 19, 1);
insert into info(name, email, age, depart_id) values("闫曦月","yan.xiyue@live.com", 29, 2);
insert into info(name, email, age, depart_id) values("Tony","tony@live.com", 22, 1);
insert into info(name, email, age, depart_id) values("kelly","kelly@live.com", 45, 3);
insert into info(name, email, age, depart_id) values("james","james@live.com", 52, 1);
insert into info(name, email, age, depart_id) values("ammy","ammy@live.com", 23, 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=20;
select * from info where name='高宇星' or age=53;
select * from info where (name='高宇星' or email='yan.xiyue@live.com') and age=20;


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>5) as T where age>10;
select * from info where not exists (select * from depart where id=5);

2、模糊查询

模糊查询中主要用到的是百分号%和下划线_，百分号代表多个字符，一个下划线表示一个字符。

select * from info where name like '%宇%'; --搜索名字为（****宇****）的数据

在这里插入代码片

select * from info where name like "%宇"; --搜索名字为（宇***）的数据!

select * from info where email like "%@live.com";--搜索邮箱名称为“******@live.com”的数据

select * from info where email like "_@live.com"; --搜索邮箱名称为“*@live.com"的数据

select * from info where email like "___.yuxing@live.com";

模糊查询适合数据量小的数据集。

3、映射

select id,name from info; --搜索info表中的id和name字段

select id,name as NM from info;  --搜索info表中id和name字段，并将name映射为NM字段

select id, name, (select max(id) from depart) as max, (select min(id) from depart) as min, age from info; --从info表中查询字段id、name，查询depart表中id最大值作为max字段的数值，查询depart表id最小值作为min字段的数值

select id, name, (select title from depart where depart.id=info.id) as X1, (select title from depart where depart.id=info.depart_id) as X2 from info;--从info表中查询id、name字段，当depart表中id等于info表中的id时，从depart表中搜索title字段作为X1，当depart表中id等于info表中的depart_id时，搜索depart表中的title作为字段X2

select id, name, case depart_id when 1 then "第一部门" end v1 from info;--从info中查询id，name字段，当depart_id=1时返回“第一个部门”这一字符串，并将该列命名为v1；

类似于python中的if else 语句
sql中用case when来充当if，用then来充当else

select 
id, 
name, 
case depart_id when 1 then "第一部门" end v1,
case depart_id when 1 then "第一部门" else "其他" end v2,
case depart_id when 1 then "第一部门" when 2 then "第二部门" 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;

增加一个v1字段，当depart_id=1时，v1字段的值为第一部门，其他情况为空值；
增加一个v2字段，当depart_id=1时，v1字段的值为第一部门，其他情况为“第二部门”；
增加一个v3字段，当depart_id=1时，v1字段的值为第一部门，当depart_id=2时，为第二部门，else为“其他”；
增加一个v4字段，当年龄小于18时，v4字段为少年，其他情况下为空值；
增加一个v5字段，当年龄小于18时，v4字段为少年，其他情况下为油腻男；
增加一个v6字段，当年龄小于18时，v4字段为少年，当年龄大于18小于30时为青年，其他情况下为油腻男；

4、排序

降序排列

select * from info order by age desc;--从info表中搜索数据，按照age从大到小的顺序排列；

增序排列

select * from info order by age desc;--从info表中搜索数据，按照age从小到大的顺序排列；

多个排序条件

select * from info order by age asc, id desc; --从info表中进行查询，按照年龄从小到大的顺序进行排列，当年龄相同时，按照id从大到小的顺序排列；

增加条件语句

select * from info where id>10 order by age asc, id desc;--查询info表中id大于10的数据，并按照age从小到大的顺序排列，当年龄相同时，按照id从大到小的顺序排列

select * from info where id>6 or name like "%y" order by age asc;--从info中查询id大于6或者名字最后一位字母为y的数据，并按照年龄从小打到的顺序排列。

5、取部分值

用limit来限制取多少数据量

 select * from info limit 5;

增加排序条件

select * from info order by age desc limit 3;--从info表中查询，按照年龄从大到小的顺序排列并取前三条数据展示

select * from info limit 3 offset 2;--从第二条数据开始，向后获取三条数据

6、分组

select name from info group by age;

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

select age, name from info group by age;

 select age,count(id) from info group by age having count(id)>2;--聚合条件放在having中：从info中查询数据，并根据age进行分组，当组内数据大于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的数据，根据年龄进行分组，查询组内数据大于2条的组别，按照年龄从大到小的顺序进行排列，取第一条数据。

7、左右连表

多个表可以连接起来进行查询
表info

id	name	email	age	depart_id
1	高宇星	gao.yuxing@live.com	20	1
2	alex	alex@live.com	19	1
3	闫曦月	yan.xiyue@live.com	29	2
4	Tony	tony@livw.com	22	1
5	kelly	kelly@live.com	45	3
6	james	james@live.com	52	1
7	ammy	ammy@live.com	23	1

表depart

id	title
1	开发
2	运营
3	销售

将info作为主表，depart作为次表进行数据查询，查询 depart中id等同于info表中depart_id的数值。查询结果如下图所示，我们可以看出，把info.depart_id = depart.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;

为了能够体现主表和次表查询的效果，我们可以多加几条数据：

insert into depart(title) values("运维")
insert into info(name, email, age, depart_id) values("铁锤", "xxx@xx.com", 19, 999)

然后将depart表作为主表，将info作为次表进行查询

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

结果显示，此时会以主表为主，运维部门虽然没有员工，但是title仍会显示运维，只不过id, name 和email 都显示为空值。

外部连接查询语句：

select * from info left outer join depart on ...
select * from depart left outer join info on...

该查询语句可以简写为：

select * from info left join depart on ...

内部连接查询语句：
表 inner join 表 on 条件

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

SQL执行顺序为：
select * from、join、on、where、group by、having、order by、limit

8、联合

select * from table1
union
select * from table2;

联合使用条件：查询的列数需要相同
联合自带功能：去重

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

需要保留所有数据，不进行去重的话，sql语句如下：

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

二、表关系

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

1. 单表，单独一张表就可以将信息保存

id	name	code
1	北京	010
2	上海	021
3	深圳	0755
4	天津	0755

2. 一对多，需要两张表来储存信息，且两张表存在一对多或多对一的关系

表info

id	name	email	age	depart_id
1	高宇星	gao.yuxing@live.com	20	1
2	alex	alex@live.com	19	1
3	闫曦月	yan.xiyue@live.com	29	2
4	Tony	tony@livw.com	22	1
5	kelly	kelly@live.com	45	3
6	james	james@live.com	52	1
7	ammy	ammy@live.com	23	1

表depart

id	title
1	开发
2	运营
3	销售

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

表boy

id	name
1	李杰
2	alex
3	于超
4	日天

表girl

id	name
1	闫曦月
2	小星星
3	丽丽
4	凤凤

关系表:boy_girl

id	boy_id	girl_id
1	1	1
2	1	2
3	2	1
4	2	3

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

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

一对多表格创建时，增加外键的mysql语言是：
constraint fk_info_depart(外键名) foreign key (depart_id) references depart(id)

（一）一对多示例

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


create table info_1(
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_info1_depart1 foreign key (depart_id) references depart_1(id)
)default charset=utf8;

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

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

如果需要删除外键的话：

alter table info drop foreign key fk_into_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 boy_girl drop foreign key fk_boy_girl_boy;
alter table boy_girl drop foreign key fk_boy_girl_girl;

（三）案例

制作简易版课程后台，后台所需要的表格如下图所示，创建所需要的表格(包括外键)：

create table info(
id int not null auto_increment primary key,
username varchar(16) 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 module(course_id) references course(id)
)default charset=utf8;


create table day_(
id int auto_increment primary key,
title varchar(16) not null,
module_id int not null,
constraint fk_day_module foreign key day_(module_id) references module(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_modulerecord_user foreign key module_record(user_id) references info(id),
constraint fk_modulerecord_module foreign key module_record(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 video(day_id) references day_(id)
)default charset=utf8;

三、授权管理

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

如果有多个程序的数据库都放在同一个Mysql中，程序都用root账户就存在风险了。这种情况下，我们可以创建账户，并给窗户分配权限，例如：只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的权限等。

（一）用户管理

在mysql的默认数据库mysql中，user表存储着所有的账户信息（含账户、权限等）。

desc mysql.user;

可以查看user表的明细

1、创建和删除用户

创建用户：create user ‘用户名’@‘连接者的IP地址’ identified by ‘密码’;
删除用户：drop user ‘用户名’@‘连接者的IP地址’；

create user gaoyuxing@127.0.0.1 identified by '112233';
create user QAQ@127.0.0.2 identified by '445566';

drop user gaoyuxing@127.0.0.1;
drop user QAQ@127.0.0.2;

2、修改用户

rename user ‘用户名’@‘连接者的IP地址’ to ‘新用户名’@‘连接者的IP地址’

rename user gaoyuxing@127.0.0.1 to gaoxingxing@127.0.0.1;

3、修改密码

alter user ‘用户名’@‘连接者的IP地址’ identified by ‘新密码’

alter user gaoxingxing@127.0.0.1 identified by 'gyx123';