数据库的查询命令

条件

from / where / group by / having distinct / order by / limit

使用这些条件可以按照需求任意选择，但是顺序必须按照上面的顺序来
特殊点：distinct是放在最前面的，但是执行逻辑是上面的顺序

去重：distinct

- select  distinct  *  from  表;
- 对于你查询出的所有记录，所有字段必须完全一模一样才会去重
	- 可以改为  任意字段

常用函数

- concat()/concat_ws()
  - 拼接
  - concat(字段,['拼接符号'],字段)
    - 将字段按照你写的格式拼接
  - concat_ws('拼接符号',字段n)
    - 所有字段按照第一拼接符号拼接
- upper()/lower()
  - upper()将字符串都变成大写
  - lower()将字符串都变成小写
- ceil()/floor()/round()
  - ceil(浮点型字段)
    - 向上取整
  - floor(浮点型字段)
    - 向下取整
  - round(浮点型字段)
    - 小于0.5的舍去
    - 大于0.5的进一位
- 整型可以使用运算符直接运算

案例

"""
拼接：concat() | concat_ws()
大小写：upper() | lower()
浮点型操作：ceil() | floor() | round()
整型：可以直接运算
"""
mysql>: select name,area,port from emp;
mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp;  # 上海-浦东
mysql>: select name as 姓名, concat_ws('-',area,port,dep) 信息 from emp;  # 上海-浦东-教职部

mysql>: select upper(name) 姓名大写,lower(name) 姓名小写 from emp;

mysql>: select id,salary,ceil(salary)上薪资,floor(salary)下薪资,round(salary)入薪资 from emp;

mysql>: select name 姓名, age 旧年龄, age+1 新年龄 from emp;

条件：where

- where  判断规则;

  - 筛选出符合规则的记录

- 判断规则

  - 比较符合

    - > < >=  <=  =  !=

  - 区间符合

    - between  开始  and 结束
      - 在满足开始和结束中间都符合
    - in   (自定义容器)
      - 只要在里面的都符合

  - 逻辑符合

    - and  or   not

  - 相似符合

    - like   _或者%
    - _表示代替一个任意字符
    - %表示代替任意个任意字符

  - 正则符合

    - regexp   正则语法
    - 支持部分正则语法

案例

# 多条件协调操作导入：where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1

mysql>: select * from emp where id<5 limit 1;  # 正常
mysql>: select * from emp limit 1 where id<5;  # 异常，条件乱序

# 判断规则
"""
比较符合：>  |  <  |  >=  |  <=  |  =  |  !=
区间符合：between 开始 and 结束 |  in(自定义容器)
逻辑符合：and  |  or  |  not
相似符合：like _|%
正则符合：regexp 正则语法
"""
mysql>: select * from emp where salary>5;
mysql>: select * from emp where id%2=0;

mysql>: select * from emp where salary between 6 and 9;

mysql>: select * from emp where id in(1, 3, 7, 20);

# _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o*
mysql>: select * from emp where name like '%o%';
mysql>: select * from emp where name like '_o%';  
mysql>: select * from emp where name like '___o%';

# sql只支持部分正则语法
mysql>: select * from emp where name regexp '.*\d';  # 不支持\d代表数字，认为\d就是普通字符串
mysql>: select * from emp where name regexp '.*[0-9]';  # 支持[]语法

分组与筛选：group by | having

where与having

首先要知道having也是用来筛选的
在不分组的情况下，使用where 和 having是一样的
并且，where和having的使用方式完全一致
PS：having可以对聚合结果进行筛选，还可以对分组的数据进行筛选
PS：where可以分组前使用

# 表象：在没有分组的情况下，where与having结果相同
# 重点：having可以对 聚合结果 进行筛选
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;

mysql>: select * from emp where id in (5, 10, 15, 20);
mysql>: select * from emp having id in (5, 10, 15, 20);

聚合函数
- 将多条数据统一处理的方式叫做聚合
- max()：求最大值
- min()：求最大值
- avg()：求平均数
- sum()；求和
- count()：求个数
- group_concat()：组内字段拼接，用来查看组内其他字段
  - 无法设置拼接方式默认以，连接
```
"""
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
"""
```

分组查询 group by

# 修改my.ini配置重启mysql服务
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
# 有 ONLY_FULL_GROUP_BY 限制，报错
mysql>: select * from emp group by dep;

# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人

# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果

mysql>: 
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep;

mysql>: select 
	dep 部门,
	max(age) 最高年龄
from emp group by dep;

# 总结：分组后，查询条件只能为 分组字段 和 聚合函数操作的聚合结果

- 修改my.ini配置重启mysql服务

	- sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

- 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
- 有 ONLY_FULL_GROUP_BY 限制，报错
- 分组后的查询就不是单条记录了，而是一组一组的记录，对每组记录做同一处理
- 用聚合函数来处理这些数据

分组后的having

having可以对聚合结果再进行筛选，where不可以

mysql>: 
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep;

# 最低薪资小于2
mysql>:
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep having min(salary)<2;

# having可以对 聚合结果 再进行筛选，where不可以

联合分组
- group by 字段1,...,字段n
- 将所有字段的结果看作一个整体，进而来进行分组

排序

排序规则
```
# order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [asc|desc]
```
- order by 主排序字段 [asc/desc][,次排序字段 [asc/desc]...]
  - asc 升序排序，可以不写，默认升序
  - desc 降序排序

未分组状态下

mysql>: select * from emp;

# 按年龄升序
mysql>: select * from emp order by age asc;
# 按薪资降序
mysql>: select * from emp order by salary desc;

# 按薪资降序，如果相同，再按年龄降序
mysql>: select * from emp order by salary desc, age desc;
# 按龄降序，如果相同，再按薪资降序
mysql>: select * from emp order by age desc, salary desc;

分组状态下

mysql>:
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep;

# 最高薪资降序
mysql:
select 
	dep 部门,
	group_concat(name) 成员,
	max(salary) 最高薪资,
	min(salary) 最低薪资,
	avg(salary) 平均薪资,
	sum(salary) 总薪资,
	count(gender) 人数
from emp group by dep
order by 最高薪资 desc;

限制 limit

limit 条数/偏移量,条数

# 语法：limit 条数  |  limit 偏移量,条数
mysql>: select name, salary from emp where salary<8 order by salary desc limit 1;

mysql>: select * from emp limit 5,3;  # 先偏移5条满足条件的记录，再查询3条

单表查询

"""
增：
insert [into] 
	[数据库名.]表名[(字段1[, ..., 字段n])] 
values 
	(数据1[, ..., 数据n])[, ..., (数据1[, ..., 数据n])];

删：
delete from [数据库名.]表名 [条件];

改：
updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];

查：
select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];
"""

# 条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
# 注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全
# 可以缺失，但不能乱序

增

insert [into]
[数据库名].表名[字段n]
vlaues
(数据n);
- 数据与字段要一一对应
- 如果字段没有写表示所有的数据都要填写

删

delete from 数据库名].表名 [条件];

改

updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];

查

updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];

连表查询

连接

# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询

# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接

建立表与表间的联系，可以是两张及以上
不要求外键，有联系就行
连接后是一大张包所有表字段的大表—实际上是多张表
连表查询
- 在张大表的基础上进行查询叫做连表查询
建立表与表连接的方式有四种
- 内连接
- 左连接
- 右连接
- 全连接

笛卡尔积

# 笛卡尔积: 集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}

mysql>: select * from emp, dep;

# 总结：是两张表 记录的所有排列组合，数据没有利用价值

select * from 表1 , 表2;
将两张表的记录进行全排列

一对多

内连接

# 关键字：inner join on
# 语法：from A表 inner join B表 on A表.关联字段=B表.关联字段

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp inner join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：只保留两个表有关联的数据

from A表 [inner] join B表 on A表.关联字段=B表关联字段

左连接

# 关键字：left join on
# 语法：from 左表 left join 右表 on 左表.关联字段=右表.关联字段

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp left join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：保留左表的全部数据，右表有对应数据直接连表显示，没有对应关系空填充

from A表 left join B表 on A表.关联字段=B表关联字段

右连接

# 关键字：right join on
# 语法：from A表 right join B表 on A表.关联字段=B表关联字段

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 
order by emp.id;

# 总结：保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系空填充

from A表 right join B表 on A表.关联字段=B表关联字段

左右可以相互转化

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 
order by emp.id;

mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from dep left join emp on emp.dep_id = dep.id 
order by emp.id;

# 总结：更换一下左右表的位置，相对应更换左右连接关键字，结果相同

交AB表位置和left/right关键字
形成的新连接是一模一样的

全连接
```
mysql>: 
select 
	emp.id,emp.name,salary,dep.name,work 
from emp left join dep on emp.dep_id = dep.id 

union

select 
	emp.id,emp.name,salary,dep.name,work 
from emp right join dep on emp.dep_id = dep.id 

order by id;

# 总结：左表右表数据都被保留，彼此有对应关系正常显示，彼此没有对应关系均空填充对方
```
- select 表的字段名
  from A表 left join B表 on A表.关联字段=B表关联字段
  union
  select 表的字们
  from A表 right join B表 on A表.关联字段=B表关联字段
  - 因为mysql了不支持全连接，所以两张表的左右连接和union实现全连接
  - 注意：两个左右连接必都先select形成一张新表，再用union连接成一张信新表
  - 之后的操作只能对新表来完成
  - PS：两个select中的字段们必须完全一致，连顺序也要一样

一对一与一对多情况一致

# 创建一对一 作者与作者详情 表
create table author(
	id int,
    name varchar(64),
    detail_id int
);
create table author_detail(
	id int,
    phone varchar(11)
);
# 填充数据
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');

# 内连
select author.id,name,phone from author join author_detail on author.detail_id = author_detail.id order by author.id;

# 全连
select author.id,name,phone from author left join author_detail on author.detail_id = author_detail.id
union
select author.id,name,phone from author right join author_detail on author.detail_id = author_detail.id
order by id;

多对多：两表两表建立连接

# 在一对一基础上，建立 作者与书 的多对多关系关系

# 利用之前的作者表
create table author(
	id int,
    name varchar(64),
    detail_id int
);
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);

# 创建新的书表
create table book(
	id int,
    name varchar(64),
    price decimal(5,2)
);
insert into book values(1, 'python', 3.66), (2, 'Linux', 2.66), (3, 'Go', 4.66);

# 创建 作者与书 的关系表
create table author_book(
	id int,
    author_id int,
    book_id int
);
# 数据：author-book：1-1,2  2-2,3  3-1,3
insert into author_book values(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,3,1),(6,3,3);

# 将有关联的表一一建立连接，查询所以自己所需字段
select book.name, book.price, author.name, author_detail.phone from book 
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id
left join author_detail on author.detail_id = author_detail.id;

前提两张被关系表个关系表都创建完成了【表里有数据了】
将有关联的表一一建立连接，查询所以自己所需字段
select 表的字段 from A表
join 关系表 on A表.关联字段=关系表.关联字段
join B表 on 关系表.关联字段=B表.关联呢字段；
join可以写多个，但内链连接会导致数据的丢失

子查询——增删改都是对表的数据进行修改，所以子查询不要和被修改的表同表

增

insert into 表 select 子查询

删

delete from 表条件是子查询

改

update 表 set 字段=值条件是子查询

查

select 字段 from 表条件是子查询

posted @ 2019-10-29 20:41 ~李阅读(5562) 评论(0) 编辑收藏举报

刷新页面返回顶部

一条上镜的咸鱼

生命如旅行，不求速达终点，贵就是在过程精彩。我们以为那人生是出悲剧或者喜剧，其实不然的，你能走出悲剧，最终往往是喜剧；在哭的时候，是学会遗忘，笑的时候，与人分享，没人愿意哭，没人拒绝笑。

数据库的查询命令

条件

from / where / group by / having distinct / order by / limit

去重：distinct

常用函数

条件：where

分组与筛选：group by | having

排序

单表查询

增

删

改

查

连表查询

连接

笛卡尔积

一对多

一对一与一对多情况一致

多对多：两表两表建立连接

子查询——增删改都是对表的数据进行修改，所以子查询不要和被修改的表同表

增

删

改

查

公告