DQL 数据查询语言

#DQL  数据查询语言
SELECT ID,namea,SEX from student

#AS  给数据列取别名
SELECT ID as 编号,namea as 姓名,SEX as 性别 from student as 学生表
SELECT ID,namea,age + 5 as 年龄 from student
#AS  可省略
SELECT ID 编号,namea 姓名,SEX 性别 from student as 学生表

#条件查询 SELECT * FROM 表名 WHERE 字段=值
SELECT * from student where id = 10
SELECT * from student where namea = '二喜'
#多条件查询 AND  OR  IN
SELECT * from student where namea = '二喜' AND id = 10
SELECT * from student where age = 19 or age = 20
SELECT * from student where age in (19,20)
#如何使用DQL中的BETWEEN AND关键字
SELECT * from student where age BETWEEN 15 AND 20
SELECT * from student where age age >= 15 AND age <= 20

#如何使用DQL中的运算符
SELECT * from student where age <> 18
SELECT * from student where age != 18
SELECT * from student where NOT age = 18

#模糊查询
#查询名称第二个字是喜字的   _ 占位符
select * from student where namea like '_喜'
select * from student where namea like '__喜'
#查询大开头的学生信息  % 任意长度的字符串
select * from student where namea like '大%'
select * from student where namea Rlike '大[喜,欢]'
select * from student where namea Rlike '大喜喜[^2-3]'
select * from student where namea Rlike '大[1-5]'

#去掉重复列   DISTINCT关键字
select DISTINCT hobby from student 
#排序  order by关键字
select * from student order by age asc 
select * from student order by age DESC
select * from student order by age asc,classes DESC
#分页 LIMIT关键字
select * from student LIMIT 1,2 #从第2行开始，总行数2
select * from student LIMIT 2,4 #从第3行开始，总行数4
#分页 每页显示3条数据
select * from student LIMIT 0,3
select * from student LIMIT 3,3
select * from student LIMIT 6,3
#（当前页-1）*每页显示记录数,每页显示记录数
#第n页  m条
LIMIT(n-1)*m,m

#空值 NULL关键字  查出null空值  空格不算
SELECT * from student where hobby is null;
SELECT * from student where hobby is NOT NULL;

#聚合函数

#查询总记录数
SELECT count(*) from student
SELECT count(1) from student
SELECT count(Id) from student
#年龄总和
SELECT sum(age) from student
SELECT avg(age) from student
SELECT namea,max(age) from student
SELECT min(age) from student

#分组查询  GROUP BY 
#查询每个班有多少人
SELECT classes,count(*) from student GROUP BY classes
#计算各班级学生总年龄
SELECT classes,SUM(age) from student GROUP BY classes
#查询各班名字中含有‘喜’字的学生数量，按照班级降序
SELECT classes,count(*) from student WHERE namea LIKE'%喜%' GROUP BY classes
ORDER BY classes DESC
LIMIT 2,2

#HAVING关键字
#班级人数小于等于2人的
SELECT classes,count(*) count from student GROUP BY classes HAVING count <= 2
#having 必须和group by 连用，筛选与分组相关的结果
#where 选择与分组无关