mysql的select查询语句和函数大全（基础进阶）含sql执行顺序
1.首先我们提一下sql的执行顺序
1.from （表）
2.join （内外连接）
3.on （内外连接条件）
4.where （判断语句）
5.group by(表的列名，开始使用select中的别名，后面的语句中都可以使用)
6.avg,sum,count,max,min（聚合函数）
7.having （筛选条件）
8.select （正式将符合要求的数据查询出来）
9.distinct （去除重复）
10.order by （升序降序）
11.limit （分页限制）
2.开始我们的准备工作
——创建我们要用到的表stu
create table stu(
id int primary key ,
name varchar(20) not null,
score int ,
subject varchar(20)
)
--插入几条数据
INSERT INTO `stu`(`id`, `name`, `score`, `subject`) VALUES (1, 'lxl', 99, '语文');
INSERT INTO `stu`(`id`, `name`, `score`, `subject`) VALUES (2, 'hah', 89, '语文');
INSERT INTO `stu`(`id`, `name`, `score`, `subject`) VALUES (3, 'ww', 49, '语文');
INSERT INTO `stu`(`id`, `name`, `score`, `subject`) VALUES (4, 'lxl', 78, '数学');
INSERT INTO `stu`(`id`, `name`, `score`, `subject`) VALUES (5, 'hah', 97, '数学');
INSERT INTO `stu`(`id`, `name`, `score`, `subject`) VALUES (6, 'ww', 98, '数学');
3.开始
1.分组查询
–单独使用group by
select * from stu group by subject;--查询出每一个第一条suject不同的数据 （无效数据，没什么用）
–使用聚合函数
select subject,avg(score) from stu group by subject;
–查询出平均分大于80的数据
–注意聚合函数不能在where中使用，要放在having后面才行
select subject ,avg(score) from stu group by subject having avg(score)>80;
–分页SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset;
–分页（使用单个参数offset时，是指从第一条数据开始返回最多offset条数据）
select * from stu LIMIT 10;
–分页（使用两个参数offset1，offset2时，是指从第offset1+1开始返回最多offset条数据）
select * from stu limit 0,5;
2.数学函数
–绝对值
select abs(-5);
–返回非负2次方根
select sqrt(5);
–返回余数
select mod(10,3);
–返回不小于x的最小整数(向上取整)
select ceiling(2.5);
–返回不大于x的最大整数(向下取整)
select floor(3.4);
–对x进行四舍五入，小数点保留y位
select round(3.815,2);--3.82
select round(3.815);--4
select round(5,3)--5
–舍去x中小数点y位后面的数
select truncate(7.845,2);--7.845
–返回x的符号 -1、0或者1
select sign(-1);-- -1
3.–字符串函数
–返回字符串的长度
select length('lxl');--3
–返回连接字符串 abcd
select concat('a','b','cd');
–删除字符串两侧的空格 a bb dd
select trim(' a bb dd ');
–使用s2替换str中的全部s1 alovecdefgdddgerdd
select replace('abcdefgdddgerdd','b','love');
–截取str的字符串 从n开始截取len长度 bc 注意索引从0开始，截取的字符串包括 n
select substr('abcde',2,2);
–反转字符串 edcba
select reverse('abcde');
–返回第一次找到str的位置 索引从1开始
select locate('bb','baabbccbb');
4.–时间函数
–获取系统当前日期
select curdate();
–获取系统当前时间
select curtime();
–获取当前系统日期和时间
select now();
select sysdate();
–是不是表面看上去没区别，其实不然
select now(),SLEEP(2),now();--2021-04-17 17:12:35 0 2021-04-17 17:12:35
select sysdate(),sleep(2),sysdate();--2021-04-17 17:12:46 0 2021-04-17 17:12:48
–可以看出sysdate会因为线程休眠，发生时间不同，而now则是不会受到影响，可以得出sysdate是动态时间
–返回将时间转换成秒的结果 从00:00:00开始
select time_to_sec(sysdate());
select time_to_sec('00:01:20');--80
–加日期时间 时间 间隔
select adddate(now(),1);
–减时间
select SUBDATE(CURDATE(),-2);
–格式化时间 时间转化字符串
select DATE_FORMAT(now(),'%y年%m月%d日-%h时%i分%s秒');
–字符串转换时间
select STR_TO_DATE('2021-01-01 01:01:01','%Y-%m-%d %h:%i:%s');
select STR_TO_DATE('21-01-01 01:01:01','%y-%m-%d %h:%i:%s');
5.条件判断函数
–if
select if(0=0,'true','false');--true
select if(1=0,'true','false');--false
–ifnull
select ifnull(null,'空值');--空值
select ifnull('haha','空值')--haha
–case语句使用方法：case 列名 when 值1 then 结果1 else 结果2
select name as '姓名',case subject when '语文' then score end as'语文分数',
case subject when '数学' then score end as '数学分数'
from stu;
那么如何将这两条信息如何整到一条信息中呢
当我们使用group by的时候，select 后面的列要么放在聚合函数中，要么放在group by后面
select name as '姓名',sum(case subject when '语文' then score end) as'语文分数',
sum(case subject when '数学' then score end) as '数学分数'
from stu GROUP BY name;
–case when else end
–判断等于的情况
select case 1 when 1 then '是1哦' else '反正不是1' end;--是1哦
select case 2 when 1 then '是1哦' else '反正不是1' end;--反正不是1
–判断范围的情况
select case when 80>s_score>60 then '及格' when s_score>=80 then '良好' else '不及格' end from score;
–6.加密函数
select md5('123');--202cb962ac59075b964b07152d234b70;
select '202cb962ac59075b964b07152d234b70'=md5('123');--1
select '202cb962ac59075b964b07152d234b70'=md5('789');--0
4.来点测试题练练吧
1.准备工作
表名和字段
–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
测试数据
--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
要求如下：
1.检索"01"课程分数小于60，按分数降序排列的学生信息
2.查询不同老师所教不同课程平均分从高到低显示
3.查询本月过生日的学生
4.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
5.查询同名同性学生名单，并统计同名人数
6.查询‘李’姓老师的数量
7.查询学习‘01’课程的学生信息（显示学生姓名、课程名称、成绩）
8.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
9.查询各个课程的最高分
10.查询所有男生中‘02’课程，课程成绩大于60分的学生信息
答案附上：
--1.检索"01"课程分数小于60，按分数降序排列的学生信息
select st.* from student st where st.s_id in ( select s.s_id from score s where s.c_id=01 and s.s_score<60 order by s.s_score desc)
--2.查询不同老师所教不同课程平均分从高到低显示
select c_id,avg(s_score) from score s where s.c_id in(select c_id from course c GROUP BY t_id) GROUP BY c_id ORDER BY avg(s_score) desc;
--3.查询本月过生日的学生
select * from student where SUBSTR(s_birth from 6 for 2) = DATE_FORMAT(SYSDATE(),'%m') order by s_id;
--4.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select se.s_id,(select s_name from student where s_id=se.s_id),(select avg(s_score) from score where s_id=se.s_id group by s_id)from (select s_id,count(s_id) from score where s_score<60 GROUP BY s_id) as se;
--5.查询同名同性学生名单，并统计同名人数
select s_name,s_sex,count(s_name) from student GROUP BY s_name,s_sex having count(s_name)>1;
--拓展
--同名同姓
select s_name,count(s_name) from student GROUP BY s_name having count(s_name)>1;
--6.查询‘李’姓老师的数量
select count(t_id) from teacher where SUBSTR(t_name,1,1)='李'
--7..查询学习‘01’课程的学生信息（显示学生姓名、课程名称、成绩）
select (select s_name from student where s_id=se.s_id) as '学生姓名',
(select c_name from course where c_id=se.c_id),s_score from score se where c_id=01;
--8.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select ss.s_id,(select s_name from student where s_id=ss.s_id ) AS '姓名',ss.count_course as '课程总数',ss.allscore as '总分' from (select s_id,count(c_id) as count_course,sum(s_score) as allscore from score GROUP BY s_id) ss;
--9.查询各个课程的最高分
select c_id, (select c_name from course where c_id=se.c_id) as '课程名', max(s_score) as '最高分' from score se GROUP BY c_id;
--10.查询所有男生中‘02’课程，课程成绩大于60分的学生信息
select * from student where s_id in (select s_id from score where s_id in( select s_id from student st where st.s_sex='男') and c_id=02 and s_score>60);
浙公网安备 33010602011771号