MySQL:DQL语句和多表设计

DQL

Data Query Language(数据查询语言)

用来查询数据库中表的记录

查询关键字:SELECT

在业务系统中,查询频次是远高于增删改的.

语法

SELECT
#字段列表(基本查询)
From
#表名列表
WHERE
#条件列表(条件查询)
GROUP BY
#分组字段列表(分组查询)
HAVING
#分组后条件列表
ORDER BY
#排序字段列表(排序查询)
LIMIT
#分页参数(分页查询)

基本查询

select 字段1,字段2,字段3....from 表名;
select * from 表名;

select 字段1 [as 别名1],字段2 [as 别名2].....from 表名;
select 字段1[别名1],字段2[别名2]..from 表名;
#设置表名和通过表名查询

select distinct 字段列表 from 表名;
#去重

条件查询

select 字段列表 from 表名 where 条件列表;
between...and..#在某个范围内(左闭右闭)
in(....)#在in之中
like#模糊匹配(_匹配单个字符,%匹配多个字符)
is NULL#为空

排序查询

select 字段列表 from 表名 order by字段1 排序方式1,字段2 排序方式2;
ASC:升序(默认)
DESC:降序
#可以使用convert(字段名 using 编码)的方式对中文进行排序 

分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;
limit 0,10#查询第一页,每页展示10条
limit 10,10#查询第二页,每页展示10条
#(分页查询)->起始索引=(查询页码-1)*每页显示记录数
#起始索引即为偏移量,相当于不展示之前的偏移量条数据

聚合函数

将一列数据作为一个整体,进行纵向计算

count#统计数量
max#最大值
min#最小值
avg#平均值
sum#求和
select 聚合函数(字段列表) as 别名 from 表名;

null值不参与聚合函数运算

分组查询

select 字段列表 from 表名 [where条件] group by 分组字段名[having 过滤条件]
select gender ,count(*) from tab group by gender;
#按照性别分组,分组后分别对男女员工进行统计

group by

一般和聚合函数一起使用

eg:找出每个岗位的最高薪资

先找出岗位

select job from tab;

再找出最高薪资

max(sal)

最后按照岗位分组(每个不同岗位的最高薪资)

select job,max(sal) from emp group by job;

注:多个字段一起分组,优先按照第一个字段分组,如果第一个字段相同,按第二个字段分组.

where 和 having区别

1,执行时机不同:where 在分组前过滤,不满足where条件的不会参与分组,having是分组后对结果进行过滤.

执行顺序:where>聚合函数>having

2,判断条件不同:where不能对聚合函数进行判断,但having可以

数据转换函数

if(gender = 1 ? '男' , '女')
#类似三元运算符,表达式成立值转换为男,表达式不成立转换为女
case 字段名
	when 条件1
	 then 结果1
	when 条件2
	 then 结果2
	 ....
	 else 结果default
	 end,
#类似switch,满足哪个条件就会转换为哪个结果.

多表设计

多表查询

一对多表设计

子表的多条数据对应主表的一条数据

在数据库表中多的一方(子表)关联另一方(主表)的主键

通过外键约束来进行关联,保证数据的一致性和完整性

foreign key
#建表时指定外键
create table 表名{
	[constraint] [外键名称] foreign key(外键字段名) references 主表
	(主表字段名)
}
#修改时添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表字段名);

外键名字一般为主表名_主键名

添加外键后,主表数据无法被直接删除

(容易引发死锁,降低效率,不推荐)

建议在逻辑中直接解决外键关联而非在数据库中使用外键解决

一对一表设计

在一张表中加入外键关联另一方的主键,设置外键为UNIQUE.

多用于单表拆分,提升操作效率(减少数据携带)

多对多表设计

建立第三张中间表存放外键,分别关联两边的主键.