这里写目录标题
一、必备SQL语句
现在,需要创建如下两张表:
表info
id | name | 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 | 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;
二、表关系
在开发项目时,需要根据业务需求去创建很多表结构,以此来实现业务逻辑,一般表结构有三类:
- 单表,单独一张表就可以将信息保存
id | name | code |
---|---|---|
1 | 北京 | 010 |
2 | 上海 | 021 |
3 | 深圳 | 0755 |
4 | 天津 | 0755 |
- 一对多,需要两张表来储存信息,且两张表存在一对多或多对一的关系
表info
id | name | 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 | 销售 |
- 多对多,需要三张表来存储信息,两张单表+关系表,创造出两个单表之间的多对多关系
表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;
当我们不对ip地址做限制时:
create user gaoyuxing@'%' identified by 'root123';
这样的话就不对ip地址做要求了,用户gaoyuxing在任何ip地址下通过该账户来连接mysql。
2、修改用户
rename user ‘用户名’@‘连接者的IP地址’ to ‘新用户名’@‘连接者的IP地址’
rename user gaoyuxing@127.0.0.1 to gaoxingxing@127.0.0.1; --更换用户名
rename user gaoxingxing@127.0.0.1 to gaoxingxing@localhost;--更换IP地址
3、修改密码
alter user ‘用户名’@‘连接者的IP地址’ identified by ‘新密码’
alter user gaoxingxing@127.0.0.1 identified by 'gyx123';
(二)授权管理
创建好用户后,就可以为用户进行授权了。
创建了一个账户:gaoyuxing@‘%’ 密码为:151265@mylove
1、授权
grant 权限 on 数据库.表 to 用户名@‘IP地址’,例如:
grant all privileges on example.course to gaoxingxing@localhost --将example数据库中的course表的所有权限都授权给高星星用户,IP地址为localhost
grant all privileges on example.* to gaoxingxing@localhost --将example库中所有表的所有权限都授权给高星星用户,该用户的ip地址为localhost
grant all privileges on *.* to gaoyuxing@localhost --将所有数据库的所有表的所有权限都授权给高星星用户,该用户的ip地址为localhost
grant select on deployer.boy to gaoxingxing@localhost --将deployer数据库中boy表的查询权限授予给高星星用户,其IP地址限定为localhost
grant select,insert on deployer.boy_girl to gaoxingxing@localhost --将deployer数据库中boy_girl表的查询和插入权限授予给高星星用户,其IP地址限定为localhost
以上命令仅仅是进行了授权,还没有生效,生效命令为:
flush privileges;
授权后我们登陆一下名为"gaoyuxing"的账户,查看一下数据库,然后就会发现,我们只能看到被赋予权限的数据库。
在授权的时候尤其注意:我们要分配什么权限给哪个用户,授权哪个数据库中的那个表给该用户。能够赋予的权限如下图中所示。
2、查看权限
处于root账户下查看
show grants for 账户
show grants for gaoxingxing@localhost;
3、取消授权
revoke 权限 on 数据库.表 from 用户名
revoke all privileges on example.* from gaoxingxing@localhost;
一般情况下,很多正规公司,数据库是由DBA来统一处理的,DBA为每个项目的数据创建用户,并授予相关的权限。