首先准备五张表:
student表:
category表:
grade表:
result表:
subject表:
1.语法:
select * from table_name [where ...] [group by ...] [having ...] [order by ...] [limit ...];
[where ...] #指定结果需满足的条件
[group by ..] #指定结果按照哪几个字段来分组
[having ...] #过滤分组的记录必须满足的次要条件
[order by ...] #指定查询记录按一个或者多个条件排序
[limit ...] #指定查询的记录从哪条至哪条
注意顺序
2.DQL常用函数等:
别名as
将'sex'以别名'性别'表示
select sex from student;
select sex as '性别' from student;
多个别名用逗号隔开
select studentNo as '编号',studentResult as '成绩' from result;
空字符串和null
空字符串和null不一样,查询时候需要区分,varchar类型
select * from student where phone is null;
select * from student where phone = "";
order by
默认从小到大排序
select studentNo as '编号',studentResult as '成绩' from result order by studentResult ;
末尾加desc为从大到小排列
select studentNo as '编号',studentResult as '成绩' from result order by studentResult desc;
末尾加asc为从大到小排列
select studentNo as '编号',studentResult as '成绩' from result order by studentResult asc;
小案例综合
把成绩都降低10%后加5分,再查询及格成绩,并按照成绩从高到低排序
select studentNo as '编号',(studentResult*0.9+5) as '成绩' from result where (studentResult*0.9+5) >=60 order by studentResult desc;
limit 分页
select * from result limit 0,5;
从0开始,5条数据
select * from result limit 6,10;
从6条数据开始,10条数据
语法:
limit m,n;
m 制定第一个返回记录行的偏移量
n 制定返回记录行的最大数目
(m不指定则偏移量为0,从第一条开始返回前n条记录)
in
单条查询
select * from student where studentNo = 10000;
多条查询
select * from student where studentNo = 10000 or studentNo = 10001;
多条关键字查询
select * from student where studentNo in (10000, 10001);
聚合函数 返回值是单个值
count() 返回某字段的行数,函数内部只能添加一个,多个会报错
select count(*) from student;
select count(1) from student;
select count(studentNo) from student;
avg() 返回某字段的平均值
select avg(studentResult) [as '学生成绩平均值'] from student;
max() 返回某字段的最大值
select max(studentResult) [as '学生成绩最大值'] from student;
min() 返回某字段的最小值
select min(studentResult) [as '学生成绩最小值'] from student;
sum() 返回某字段的和
select avg(studentResult) [as '学生成绩和'] from student;
字符串函数:
concat(str1,str2,str3) 字符串连接
select concat('a','b','c');
insert(str,pos,len,newstr) 字符串替换 字符串,起始(从1开始),长度,替换内容
select insert('这是SQL Server数据库',3,10,'MySQL');
lower() 转小写 都是小写也不报错
select lower('MYSQL');
upper() 转大写
select upper('mysql');
substring(str,num,len) 截取字符串 字符串,起始位置,截取长度
select substring('JavaMYSQLOracle',5,5);
时间日期函数:
now() 获取当前日期和时间
curdate() 获取当前日期
curtime() 获取当前时间
week(date) 返回日期date为一年的第几周
year(date) 返回日期date的年份
hour(time) 返回time的小时
minute(time) 返回time的分钟
second(time) 返回time的秒数
select curdate();
select curtime();
select now();
select week(now());
select year(now());
select hour(now());
select minute(now());
select second(now());
。。。
数学函数:
ceil() 天花板 进
select ceil(2.0); 2
select ceil(2.9); 3
floor() 地板 舍
select floor(2.9); 2
select floor(3.0); 3
round() 四舍五入,只判断一位
select round(2.4); 2
select round(2.5); 3
select round(2.45); 2
3.多表关联查询:
3.1内连接:inner join
select * from student inner join result on student.studentNo = result.studentNo;
交集,设置两张表的关联字段为判断条件,结果为两张表的字段,数据为关联字段的相同值的元素
即两张表中判断的字段中都有的数据
3.2左连接:left join
select * from student left join result on student.studentNo = result.studentNo;
设置两张表的关联字段为判断条件,结果为左边的表的所有字段+右边表的字段,全部列出,左边表的所有元素列出,将右边的关联数据填入,没有的为null
3.3右连接:right join
select * from student right join result on student.studentNo = result.studentNo;
设置两张表的关联字段为判断条件,结果为右边的表的字段+左边的表的字段,全部列出,右边表的所有元素列出,将左边的关联数据填入,没有的为null
3.4自连接:
自连接:
将两张字段相同的表,一张表作为父表,另一张为子表,此时将两张表连接起来,添加一个pid元素,父表的pid可以自定义,子类引用父表中的id元素,糅合在一起,此时形成一张表,自连接查询的时候,利用select * from 表名 as a,表名 as b where a.pid = b.id;将此表自连接并添加判断条件,可以还原原来两张表的场景。
4.DQL子查询:
4.1 子查询
查询“Logic Java”课程考试成绩为60分的学生名单(subject表,student表,result表)
SELECT `studentName` FROM `student` # 找到课程名字为Logic Java的编号的学生成绩并判断是否为60的学生名字
WHERE `studentNo` IN(
SELECT `studentNo` FROM `result`
WHERE `subjectNo` = ( # 找到课程名字为Logic Java的编号的学生成绩
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java' # 找到课程名字为Logic Java的编号
)AND `studentResult` = 60 # 找到课程名字为Logic Java的编号的学生成绩并判断是否为60
);
4.2 分组group by
select avg(studentResult) from result group by subjectNo;
按照课程名称subjectNo分组求平均值
后接having添加判断条件