文章目录
6.DQL:查询语句
1.查询语法:
select 字段列表 from 表明列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
2.基础查询:
(1).多个字段的查询
SELECT name,age FROM stu;
(2).去除重复
SELECT DISTINCT address from emp ; #去除重复的结果集
SELECT DISTINCT name,address from emp;#两个字段的值一样才会去除
(3).计算列(可以使用四则运算)
SELECT NAME,math,english,math+ifnull(english,0)from stu;#如果有null参与的计算,计算结果都为0
ifnull(表达式1,表达式2):
表达式1:哪个字段需要判断是否为null
表达式2:该字段为null后的替换值。
(4).起别名
as也可以省略
SELECT NAME,math 数学,english 英语,math+ifnull(english,0) (as) 总分 from stu;
3.条件查询:
(1)where子句后跟条件
(2)运算符
between...and
select * from stu where age between 20 and 30;#查询年龄大于等于20,小于等于30
in(集合)
select * from stu where age in(18,22,25);#查询年龄18,22,25岁的信息
like
占位符:_表示单个任意字符,%表示多个任意字符
select * from stu where name like '马%';#查询姓马的有哪些
select * from stu where name like '_化%';#查询姓名中第二个字是化的人
select * from stu where name like '___';#查询姓名是三个字的人
select * from stu where name like '%德%';#查询姓名中包含德的人
is null
select * from stu where english is (not) null;#null值不能使用 =(!=)判断
and或&&
or或||
not或!
4.排序查询:
order by 子句(排序字段1 排序方式1,排序字段2 排序方式2...)
排序方式:ASC(升序,默认升)、DESC(降序)
select * from stu order by math ASC,english ASC;#只有第一排序方式一样时,才会判断第二条件。
5.聚合函数:
将一列数据作为一个整体,进行纵向的计算。
(1)count:计算个数
①一般选择非空的列:主键。
②count(*)
(2)max:计算最大值
select max(math) from stu;
(3)min:计算最小值
select min(math) from stu;
(4)sum:计算和
select sum(math) from stu;
(5)avg:计算平均值
select avg(math) from stu;
注意:聚合函数的计算,会排除null值。
解决方案:1.选择不包含非空的列进行计算
2.IFNULL函数
6.分组查询:
group by 分组字段;
注意:分组之后查询的字段:要么是分组字段,要么是聚合函数。
where和having的区别?---1.where在分组之前进行限定,如果不满足条件,则不参与分组。
having在分组之后进行限定,如果不满足结果,则不会被查询出来。
2.where后不可以跟聚合函数,having可以进行聚合函数的判断。
①#按性别分组,分别查询男、女同学数学的平均分,人数
select sex , avg(math),count(id) from stu group by sex;
②#按性别分组,分别查询男、女同学数学的平均分,人数;分数小于70分的人,不参与分组
select sex , AVG(math),count(id) from stu where math >70 group by sex;
③#按性别分组,分别查询男、女同学数学的平均分,人数;分数小于70分的人,不参与分组,分组之后,人数要大于2个。
select sex , AVG(math),count(id) as 人数 from stu where math >70 group by sex having count(id) > 2;
7.分页查询:
limit 开始的索引,每页查询的条数;limit是mysql的"方言"
公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
select * from stu limit 0,3;#第1页,每页显示3条记录
select * from stu limit 3,3;#第2页,每页显示3条记录
select * from stu limit 6,3;#第3页,每页显示3条记录
8.约束:
对表中的数据进行限定,保证数据的正确性、有效性和完整性。
分类: (1)主键约束:primary key
(2)非空约束:not null
(3)唯一约束:unique
(4)外键约束:foreign key
(1)主键约束:primary key。
非空且唯一,并且一张表只有有一个字段为主键。主键就是表中记录的唯一标识。
①自动增长:如果某一列是数值类型的,使用auto_increment可以来完成值的自动增长。
②#创建表添加主键约束,并自动增长
create table s3(
id int primary key auto_increment,#给id添加主键约束
name varchar(20)
);
③#删除主键
alter table s3 modify id int;#错误
alter table s3 drop primary key;
#删除自动增长
alter table s3 modify id int;
④#创建表完成后,添加主键
alter table s3 modify id int primary key;
#添加自动增长
alter table s3 modify id int auto_increment;
(2)非空约束:not null,值不能为空
①#创建表添加非空约束
create table s1(
id int,
name varchar(20) not null #name不能为空
);
②#删除name的非空约束
alter table s1 modify name varchar(20);
③#创建表完成后,添加非空约束
alter table s1 modify name varchar(20) not null;
(3)唯一约束:unique,值不能重复。
注意:mysql中,唯一约束限定的列的值可以有多个null。
①#创建表时,添加唯一约束
create table s2(
id int,
phone_number varchar(20) unique #添加了唯一约束
);
②#删除唯一约束
alter table s2 drop index phone_number;#修改表删除索引
③#在创建表后,添加唯一约束
alter table s2 modify phone_number varchar(20) unique;
(4)外键约束:foregin key。
让表与表产生关系,从而保证数据的正确性,它可以是一列或者多列。一个表可以有一个或多个外键。
需要明确主表和从表:
主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表。
从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的表就是从表。
①#在创建表时,可以添加外键
create table(
...
外键列
(constraint 外键名称) foreign key (外键列名称) references 主表名称(主表列名称--一般是主表主键)
);
说明:constraint 外键名称可以省略
②#删除外键
alter table emp drop foreign key emp_dep_fk;
③#添加外键(添加约束foreign key,关联的表是---)
ALTER TABLE emp ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES dep(id)
④#级联操作(当主表的关联记录被更新或删除时,从表就会自动更新或删除相关联的记录)
1.添加级联的操作:ALTER TABLE emp ADD CONSTRAINT emp_dep_fk
FOREIGN KEY (dep_id) REFERENCES dep(id) ON UPDATE CASCADE; (ON DELETE CASCADE)
2.分类
级联更新:ON UPDATE CASCADE
级联删除:ON DELETE CASCADE