MySQL查询篇——（一）简单查询

#查询所有学生信息
SELECT * from student
 
#查询学生表中的学号与姓名
select sno,sname FROM student

#查询学生表中的学号与姓名，并且给一个字段名
SELECT sno as studentNo,sname as '姓名' from student

#查询学生表中的姓名信息，并过滤掉相同姓名信息
select distinct sname from student

#查询学生的个数，年龄总和，平均年龄，最大年龄，最小年龄，并给他们一个别名
select count(*) as '学生个数' , sum(age) as '年龄总和' ,avg(age) as '平均年龄' , max(age) as '最大年龄' ,min(age) as '最小年龄' from student

where条件子句

#查询所有男生
select * from student where sex = '男'

#查询所有21岁的男生
select * from student where sex = '男'
and age = 21

模糊查询like

#查询姓陈的同学
select * from student where sname like '陈%'

#查询在名字中出现陈的同学
select * from student where sname like '%陈%'

#查询姓陈的两个字的同学
select * from student where sname like '陈_'

#查询名字结尾是’樱‘的三个字的同学
select * from student where sname like '__樱'

排序

#根据学生的年龄从大到小进行排序
select * from student order by age desc

#根据学生的年龄从大到小进行排序男同学
select * from student where sex = '男' order by age  

#第一排序根据学生年龄升序进行排序，第二排序根据‘学号’降序排序的同学信息
select * from student ORDER BY age,sno desc

分组查询

#根据性别进行分组，并分别统计各组的人数
select sex as '性别', count(*) as '人数' from student GROUP BY sex

#根据性别进行分组，并且分别统计各组的同学的平均年龄
select sex as '性别', avg(age) as '平均年龄' from student GROUP BY sex

HAVING子句

#HAVING子句一般是配合group by来使用
#根据性别进行分组，并统计各组的人数大于3人的分组信息
select sex， count(*) as sexGroup from student GROUP BY sex HAVING sexGroup >3

#MYSQL 中HAVING 子句可以单独使用 相当于where
select * from student HAVING age = 21

#MYSQL 中HAVING 子句也可以和where 使用 ，但是要放在最后
select * from student where sex = '男' HAVING age = 21

限制显示条数 --limit

#显示学生表信息的前3条
select * from student LIMIT 3
select * from student LIMIT 0,3

#显示学生表信息的2-4条
select * from student LIMIT 1,3

#显示年龄第二大和第三的男同学
select * from student where sex = '男' ORDER BY age desc limit 1,2

比较和逻辑运算

#查询年龄大于20岁小于23岁的男生
select * FROM student WHERE age>20 and age<23 and sex = '男'

#区间的另一种写法 BETWEEN 大于等于20岁小于等于23岁
SELECT * FROM student WHERE age BETWEEN 20 AND 23

#查询性别是男或者年龄大于等于21岁的学生
SELECT * FROM student WHERE sex = '男' OR age >= 21

#查询地址为''的学生数据
SELECT * FROM student where address = ''

#查询地址为null的学生数据
SELECT * FROM student WHERE address IS NULL

#查询年龄不是21岁的学生
SELECT * FROM student	WHERE age != 21
SELECT * FROM student where age <>21

#查询地址不为null 的学生信息
select * FROM student where address is not null

配套视频：0-查询篇-课程导读_哔哩哔哩_bilibili

sql文件：百度网盘 请输入提取码