1. sql语句

返回主页

1.1 对表和库的管理：

1.2 对数据行的增删改查：

2. 查询数据（重点单独拿出来）

  select * from 表名;	-- 查看所有列
  select 列名,列名,列名 from 表名;	-- 查看指定列
  select * from 表名 where 条件;	-- 符合条件在查看

例如：现在创建如下两张表。

image-20210517184445757

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

2.1 条件

根据条件搜索结果

image-20210517184445757

where

between

exists

这些都可以加not变成反义

2.2 通配符

image-20210517184445757

name like

select * from info where name like "%沛%"; -- 只要name中间是“沛”的那行数据

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

注：两个可以一起用

2.3 映射

image-20210517184445757

想要获取的列。

注意：少些select * ,自己需求。

max/min/sum 最大值/最小值/总和

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
	( select sum(id) from depart) as sid, -- max/min/sum
	age
from info;

**当然也可以搭配 where ** 如：

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1
from info;
# 注意：效率很低
+----+--------+------+
| id | name   | x1   |
+----+--------+------+
|  1 | 武沛齐 | 开发 |
|  2 | 于超   | 开发 |
|  3 | alex   | 运营 |
|  4 | tony   | 开发 |
|  5 | kelly  | 销售 |
|  6 | james  | 开发 |
|  7 | 李杰   | 开发 |
+----+--------+------+

case when then 映射

select 
	id,
	name,
	case depart_id when 1 then "第1部门" else "其他" end v2
from info;
+----+--------+---------+
| id | name   | v2      |
+----+--------+---------+
|  1 | 武沛齐 | 第1部门 |
|  2 | 于超   | 第1部门 |
|  3 | alex   | 其他    |
|  4 | tony   | 第1部门 |
|  5 | kelly  | 其他    |
|  6 | james  | 第1部门 |
|  7 | 李杰   | 第1部门 |
+----+--------+---------+

# 当然你可以一直 when then
case depart_id when 1 then "第1部门" 
when 2 then "第2部门" else "其他" end v3,

2.4 排序

image-20210517184445757

order by

desc – 倒序

asc – 顺序

也可以跟上述方法结合

select * from info where id>6 or name like "%y" order by age asc,id desc; -- 找 id>6 或 name里有%y的 且优先按照age顺序排序,如果相同，则按照id倒序排序

2.5 取部分

一般要用于获取部分数据。

limit

select * from info limit 5;   -- 获取前5条数据
select * from info where id > 4 order by id desc limit 3;  -- 先排序，再获取前3条数据

limit offset

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

2.6 分组

image-20210517184445757

group by

group by

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

count(1) 该组的个数

select depart_id,count(id) from info group by depart_id;
+-----------+-----------+
| depart_id | count(id) |
+-----------+-----------+
|         1 |         5 |
|         2 |         1 |
|         3 |         1 |
+-----------+-----------+

group by + having

select depart_id,count(id) from info group by depart_id having count(id) > 2;
+-----------+-----------+
| depart_id | count(id) |
+-----------+-----------+
|         1 |         5 |
+-----------+-----------+

2.7 左右连表

image-20210517184445757

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

left outer join on

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

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

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;
+----+--------+------------------+-------+
| id | name   | email            | title |
+----+--------+------------------+-------+
|  1 | 武沛齐 | wupeiqi@live.com | 开发  |
|  2 | 于超   | pyyu@live.com    | 开发  |
|  4 | tony   | tony@live.com    | 开发  |
|  6 | james  | james@live.com   | 开发  |
|  7 | 李杰   | lijie@live.com   | 开发  |
|  3 | alex   | alex@live.com    | 运营  |
|  5 | kelly  | kelly@live.com   | 销售  |
+----+--------+------------------+-------+

简写：select * from depart left join info on ....

-- 内连接:    表  inner join 表  on 条件
select * from info inner join depart on info.depart_id=depart.id;
# 写在最后：多张表也可以连接。

2.8 联合

image-20210517184445757

union

select id from depart 
union -- union all 保留所有
select id from info;

union -- 自动去重
union all -- 保留所有
-- 联合只需要列数相同就行

2.9 SQL执行顺序

到目前为止SQL执行顺序：
	join 链表
	on	后面为条件
    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条

2.10 表的关系

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

外键约束：

当一个表的一列要关联另一种表的一列时，为了让数据变的合理，我们要在”创建表时”加上约束

一对多示例：

这个depart表的id，与info表的depart_id有联系

image-20210517184445757

在创建info表的depart_id哪列时，加上constraint fk_info_depart foreign key (depart_id) references depart(id)，当在往depart_id列插入数据是如果不是depart表id那列的数值的话就会报错

alter table 当前表名 add constraint fk_info_depart foreign key 当前表(要关联的列名) references 被关联表(被关联列名);

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)

多对多时就是多加几个外键

小Tips

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

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

2.11 用户管理

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

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

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表的所有权限

show grants for '用户'@'IP地址' -- 查看
show grants for 'wupeiqi4'@'%';

revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消
revoke ALL PRIVILEGES on day26db.* from 'wupeiqi4'@'%';
flush privileges; -- 将数据读取到内存中，从而立即生效。