聚合函数中的小应用以及DQL语句中的应用

– DQL语句里面查询某个字段为null的语法
– select 字段列表 from 表名 where 字段名称 is null ;
– 需求1:查询英语成绩为null的学生信息;
/*
select
*
from
student
where english == null ;
*/
SELECT
*
FROM
student
WHERE english IS NULL ;

– 查询某个字段值不等于null的语法
– select 字段列表 from 表名 where 某个字段 is not null ;
/*
SELECT
*
FROM
student
WHERE english != NULL ;
*/
– 需求2:查询英语成绩不为null的学生信息;
SELECT
*
FROM
student
WHERE english IS NOT NULL ;

– 模糊查询:like关键字
/*
%:代表任意字符 (使用居多)
_:一个下划线代表一个字符

select 字段列表 from 表名 where 某个字段名称 like ‘%马%’
select 字段列表 from 表名 where 某个字段名称 like ‘马%’
*/
– 查询mysql中全局变量包含 character
SHOW VARIABLES LIKE ‘%character%’ ;

– 需求3:要查询姓马的学生信息
SELECT
*
FROM
student
WHERE NAME LIKE ‘%马%’ ; – 字符串拼接
– 需求4:查询姓名为三个字符的学生信息;
SELECT
*
FROM
student
WHERE NAME LIKE ‘___’ ; – 一个_代表一个字符

– 应用场景：给一个表单—搜索框----输入信息---->看到搜索结果(百度搜索引擎)
– 搜索框—搜索按钮---->数据库完成模糊查询

– 聚合函数查询
– 语法:select 聚合函数 from 表名;

– count(列名或者字段名称):查询总记录数 (统计) (使用最多的)
– 统计有多少条记录,就是count函数,而且里面的字段名称一般用非业务字段(id)
– 需求5:统计这个student表有多少条记录
– select count(english) ‘总记录数’ from student ; 有问题:业务字段如果某个值null,不会统计这条记录
– select count(ifnull(english,0)) ‘总记录数’ from student ; – 不建议使用业务字段去统计
– 使用非业务字段 id来统计
SELECT COUNT(id) ‘total’ FROM student ;

– avg(字段名称/某个列的名称) :查询平均分 —都是int类型
– 需求6:查询数学的平均分
SELECT AVG(math) ‘数学平均分’ FROM student ;
– 需求7:查询英语成绩的平均分
SELECT AVG(IFNULL(english,0)) ‘英语平均分’ FROM student ;

– sum(字段列表) :求和函数
– 需求8:要查询数学成绩的总分
– 数学和英语的总分成绩
SELECT SUM(math+IFNULL(english,0)) FROM student ;

– max(列名/字段名称) /min(列名/字段名称):最大值和最小值

– 需求9:要查询数学的最高成绩
SELECT MAX(math) FROM student ;
SELECT MIN(math) FROM student ;

– 聚合函数的复杂使用:单表 select嵌套select

– 需求10:查询数学成绩出大于数学平均分的学生信息;
– 分步:1)查询出数学平均分是多少
– select avg(math) from student ; – 79.5000
– 2)查询出大于79.5000的学生信息
– select * from student where math > 79.5000 ;
– 一步走
SELECT
*
FROM
student
WHERE math >
(SELECT
AVG(math)
FROM
student) ;

– 需求11:查询出数学最高分的学生信息
– 分步:1)查询出最高分的数学成绩
– select max(math) from student ; – 99
– 2)查询出math是99分的学生信息
– select * from student where math = 99 ;
– 一步走
SELECT * FROM student WHERE math =
(SELECT MAX(math) FROM student) ;

– 排序查询:order by
– 语法:select 字段列表 from 表名 order by 字段名称 排序规则(asc:升序(默认)/desc:降序)
– 需求12:数学成绩升序排序 (应用场景:针对某个字段排序)
SELECT * FROM student ORDER BY math ASC ;-- 字段后面没有带排序规则:asc

– 查询:多个字段同时排序
– 语法:select 字段列表 from 表名 order by 字段名称1 排序规则,字段名称2 排序规则;
– 前面第一个字段:按照主要条件排

UPDATE student SET english = 95 WHERE id = 6 ;
UPDATE student SET english = 100 WHERE id = 5 ;

– 需求12:查询学生的所有信息,数学成绩升序,英语成绩降序同时排序
SELECT
*
FROM
student
ORDER BY math ASC, – 主要条件排序
english DESC ; – 次要条件排序

– 分组查询:group by
– 注意事项:分组查询的时候,使用select可以查询出分组的字段
– 语法 :select 字段列表(包括分组字段) from 表名 group by 分组字段名称;

– 需求:按照性别分组,计算出他们组的数学的平均分
SELECT
sex ‘性别’,
AVG(math) ‘数学平均分’
FROM
student
GROUP BY sex ;

– 注意实现 group by如果 和where 一块使用?
– where条件必须置于group by 之前,先满足条件,才能分组;
– group by 的后面不能使用聚合函数!

– 需求:按照性别分组,查询他们的数据平均分,条件:数学成绩不大于70分的人不参与分组

/*select
sex ‘性别’,
avg(math) ‘数学平均分’
from
student
group by sex
where math > 70 ;
错误语法
*/
SELECT
sex ‘性别’,
AVG(math) ‘数学平均分’
FROM
student
WHERE math > 70
GROUP BY sex ;

– 筛选having
– 一条语句既有分组,又有筛选,还有where,先后顺序?
– having和group by:having后面可以使用聚合函数,而且group by 不能使用聚合函数
– where 在前面,下来group by,再是having 筛选

– 需求:按照性别分组,查询他们的数据平均分,
– 条件:数学成绩不大于70分的人不参与分组,筛选出总人数大于2的一组
SELECT
sex ‘性别’,
AVG(math) ‘数学平均分’,
COUNT(id) ‘总人数’
FROM
student
WHERE math > 70 – 数学成绩大于70分的同学参与分组
GROUP BY sex
HAVING COUNT(id) > 2 ;

SELECT
sex ‘性别’,
AVG(math) ‘数学平均分’,
COUNT(id) total
FROM
student
WHERE math > 70 – 数学成绩大于70分的同学参与分组
GROUP BY sex
HAVING total > 2 ;

– 分页查询:limit (重点)
– 语法:select 字段列表 from 表名 limit 起始行数,每页显示的条数;
– 起始行数= (当前页码-1)*每页显示的条数
– 已知参数:每页显示条数3条, 查询第一页的数据;
SELECT * FROM student LIMIT 0,3 ;
– 第二页数据 (每页显示3条)
SELECT * FROM student LIMIT 3,3;
– 第三页数据 (每页显示3条)
SELECT * FROM student LIMIT 6,3 ;
– 第四页数据(每页显示3条)
SELECT * FROM student LIMIT 9,3 ;

– 插入一些数据
INSERT INTO student VALUES(9,‘盲僧’,25,‘男’,‘西安’,78,76),
(10,‘ez’,20,‘男’,‘北京’,80,78),
(11,‘螳螂’,22,‘男’,‘咸阳’,87,98) ,
(12,‘木木’,28,‘男’,‘渭南’,76,66) ;

– 查询所有学生信息
SELECT * FROM student ;

– 数据库约束
– 约束:是约束用户操作数据的一种非法行为
– 举例:插入全表数据,直接插入null,null就是没有值,没有意义

– 分类
– 默认约束default
– 非空约束not null
– 唯一约束unique
– 主键约束 primary key
– 自增长约束 auto_increment—和主键约束一块用
– 外键约束 foreign key
– 级联操作CACADE:级联修改和级联删除,是在外键约束基础上使用的

– 创建一张表 worker表
CREATE TABLE worker(
id INT ,
NAME VARCHAR(10),
gender VARCHAR(3) DEFAULT ‘男’ – 默认约束
) ;

– 插入两条正常的数据
INSERT INTO worker VALUES(1,‘高圆圆’,‘女’),(2,‘张佳宁’,‘女’) ;
– 如果插入数据,插入部分字段,性别字段没有插入数据,默认值null(没有意义)
INSERT INTO worker(id,NAME) VALUES(3,‘文章’) ;
DELETE FROM worker WHERE id = 3 ;

– 默认约束default :当某个表插入部分字段,没有给指定字段插入数据,默认约束就可以给默认值

DROP TABLE worker ;

– 非空约束 not null,当用户在插入数据的时候,直接插入null,非空约束起作用
CREATE TABLE worker(
id INT NOT NULL, – 非空约束 (插入数据,必须存在)
NAME VARCHAR(10)
) ;
– 插入正常数据
INSERT INTO worker VALUES(1,‘张三丰’),(2,‘张无忌’) ;

INSERT INTO worker VALUES(NULL,‘张三’) ; – 非法行为:插入null值,不应该出现
– Column ‘id’ cannot be null :列id字段不能为null
INSERT INTO worker(NAME) VALUES(‘高圆圆’) ; – 这种sql不行

DELETE FROM worker WHERE NAME = ‘高圆圆’ ;
– 通过sql语句修改字段类型:将not null去掉了
ALTER TABLE worker MODIFY id INT ;
– 修改字段类型
ALTER TABLE worker MODIFY id INT NOT NULL ;

– 唯一约束 unique,限定某个字段值不能重复
– 实际业务中:权限校验(邮箱/手机号:身份信息):手机号登录 —这个身份:超级管理员
– 实际业务中:手机短信验证/邮箱邮箱激活…
CREATE TABLE worker(
id INT NOT NULL,
NAME VARCHAR(20) NOT NULL ,
phone_number VARCHAR(11) UNIQUE

) ;

– 插入正常数据
INSERT INTO worker VALUES(1,‘高圆圆’,‘13366668888’),(2,‘张佳宁’,‘18866662222’);
– 插入数据
INSERT INTO worker VALUES(3,‘李四’,‘13366668888’) ;-- Duplicate entry ‘13366668888’ for key ‘phone_number’ :重复数据出现

– 想通过sql语句将唯一约束删除
– 唯一约束—建立一种索引,索引名称index_name默认就是当前字段名称
– 删除唯一约束就是将唯一索引类型的索引名称删除
– alter table 表名 drop index 索引名称
ALTER TABLE worker DROP INDEX phone_number;

DELETE FROM worker WHERE id = 3 ;
– 通过sql语句:添加唯一约束,就是和修改表字段类型语法一致
ALTER TABLE worker MODIFY phone_number VARCHAR(11) UNIQUE ;

DROP TABLE worker ;
SELECT * FROM worker;