目录
一、基础增删改查
数据库表展示
数据
首先是增加
#增加
insert into t_mysql_employees(first_name,last_name,email,phone_number,salary,commission_pct,manager_id,hiredate) values('li','si','2212371722@qq.com','17775320054',10000,0.25,100,now());
查询增加的数据
#查询
select * from t_mysql_employees where phone_number = '17775320054'
结果
修改
#修改
update t_mysql_employees set last_name = 'xiao' where phone_number = '17775320054'
在查询一下
删除
delete from t_mysql_employees where phone_number = '17775320054';
查询
#9.+号的作用
#java中的+号:
#①运算符,两个操作数都为数值型
#②连接符,只要有一个操作数为字符串
#mysql中的+号:
#仅仅只有一个功能:运算符
二、过滤查询
1.按条件表达式筛选
案例一:查询工资>12000的员工信息
select * from t_mysql_employees where salary > 12000;
结果
案例二:查询部门编号不等于90号的员工名和部门编号
第一种
select last_name,department_id from t_mysql_employees where department_id <> 90
第二种
select last_name,department_id from t_mysql_employees where not(department_id = 90)
结果
2.按逻辑表达式筛选
#案例1:查询工资z在10000到20000之间的员工名、工资以及奖金
select last_name,salary,commission_pct from t_mysql_employees where salary BETWEEN 10000 and 12000
结果
#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
select * from t_mysql_employees where not(department_id BETWEEN 90 and 110) or salary > 15000
结果
三、模糊查询
1.like
#案例1:查询员工名中包含字符a的员工信息
select * from t_mysql_employees where last_name like '%a%'
结果
#案例2:查询员工名中第二个字符为e,第四个字符为a的员工名和工资
select * from t_mysql_employees where last_name like '_e_a%'
结果
#案例3:查询员工名中第二个字符为_的员工名
select * from t_mysql_employees where last_name like '_$_%' ESCAPE '$';
结果
2.between and
#案例1:查询员工编号在100到120之间的员工信息
select * from t_mysql_employees where employee_id BETWEEN 100 and 200
结果
3.in
#案例:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
select last_name,commission_pct from t_mysql_employees where commission_pct is null
结果
4.is null
#案例1:查询没有奖金的员工名和奖金率
select last_name,commission_pct from t_mysql_employees where commission_pct is null
结果
#案例2:查询有奖金的员工名和奖金率
#查询所有空的奖金员工信息
select last_name,commission_pct from t_mysql_employees where commission_pct <=> null
#查询奖金率为0.4的员工信息
select last_name,commission_pct from t_mysql_employees where commission_pct <=> 0.4
结果
#is null pk <=>
IS NULL:仅仅可以判断NULL值,可读性较高,建议使用
<=> :既可以判断NULL值,又可以判断普通的数值,可读性较低
四、order by 子句
1、按单个字段排序
#案例:按员工表薪资排序
select * from t_mysql_employees ORDER BY salary
结果
2、添加筛选条件再排序
#案例:查询部门编号>=90的员工信息,并按员工编号降序
select * from t_mysql_employees where department_id >=90 ORDER BY employee_id desc;
结果
3、按表达式排序
#案例:查询员工信息 按年薪降序
select em.salary*12*(1+IFNULL(commission_pct,0)),em.* from t_mysql_employees em ORDER BY em.salary*12*(1+IFNULL(commission_pct,0)) desc;
结果
4、按函数排序
#案例:查询员工名,并且按名字的长度降序
select CONCAT(first_name,last_name),LENGTH(CONCAT(first_name,last_name)) from t_mysql_employees ORDER BY LENGTH(CONCAT(first_name,last_name)) desc
结果
5、按多个字段排序
#案例:查询员工信息,要求先按工资降序,再按employee_id升序
select * from t_mysql_employees order by salary desc,employee_id ASC;
结果
五、案例
– 1.学生表-t_student
– sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别
– 2.教师表-t_teacher
– tid 教师编号,tname 教师名称
– 3.课程表-t_course
– cid 课程编号,cname 课程名称,tid 教师名称
– 4.成绩表-t_score
– sid 学生编号,cid 课程编号,score 成绩
表数据
– 学生表
insert into t_student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into t_student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into t_student values(‘03’ , ‘孙风’ , ‘1990-12-20’ , ‘男’);
insert into t_student values(‘04’ , ‘李云’ , ‘1990-12-06’ , ‘男’);
insert into t_student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into t_student values(‘06’ , ‘吴兰’ , ‘1992-01-01’ , ‘女’);
insert into t_student values(‘07’ , ‘郑竹’ , ‘1989-01-01’ , ‘女’);
insert into t_student values(‘09’ , ‘张三’ , ‘2017-12-20’ , ‘女’);
insert into t_student values(‘10’ , ‘李四’ , ‘2017-12-25’ , ‘女’);
insert into t_student values(‘11’ , ‘李四’ , ‘2012-06-06’ , ‘女’);
insert into t_student values(‘12’ , ‘赵六’ , ‘2013-06-13’ , ‘女’);
insert into t_student values(‘13’ , ‘孙七’ , ‘2014-06-01’ , ‘女’);
– 教师表
insert into t_teacher values(‘01’ , ‘张三’);
insert into t_teacher values(‘02’ , ‘李四’);
insert into t_teacher values(‘03’ , ‘王五’);
– 课程表
insert into t_course values(‘01’ , ‘语文’ , ‘02’);
insert into t_course values(‘02’ , ‘数学’ , ‘01’);
insert into t_course values(‘03’ , ‘英语’ , ‘03’);
– 成绩表
insert into t_score values(‘01’ , ‘01’ , 80);
insert into t_score values(‘01’ , ‘02’ , 90);
insert into t_score values(‘01’ , ‘03’ , 99);
insert into t_score values(‘02’ , ‘01’ , 70);
insert into t_score values(‘02’ , ‘02’ , 60);
insert into t_score values(‘02’ , ‘03’ , 80);
insert into t_score values(‘03’ , ‘01’ , 80);
insert into t_score values(‘03’ , ‘02’ , 80);
insert into t_score values(‘03’ , ‘03’ , 80);
insert into t_score values(‘04’ , ‘01’ , 50);
insert into t_score values(‘04’ , ‘02’ , 30);
insert into t_score values(‘04’ , ‘03’ , 20);
insert into t_score values(‘05’ , ‘01’ , 76);
insert into t_score values(‘05’ , ‘02’ , 87);
insert into t_score values(‘06’ , ‘01’ , 31);
insert into t_score values(‘06’ , ‘03’ , 34);
insert into t_score values(‘07’ , ‘02’ , 89);
insert into t_score values(‘07’ , ‘03’ , 98);
#01)查询" 01 "课程比" 02"课程成绩高的学生的信息及课程分数
SELECT st.*,sc.score AS '01',sc2.score AS '02' FROM t_student st
LEFT JOIN t_score sc ON sc.sid=st.sid AND sc.cid='01'
LEFT JOIN t_score sc2 ON sc2.sid=st.sid AND sc2.cid='02'
WHERE sc.score>sc2.score
#02)查询同时存在" 01 "课程和" 02 "课程的情况
SELECT st.* FROM t_student st INNER JOIN t_score sc ON sc.sid=st.sid AND sc.cid='01'
WHERE st.sid IN (
SELECT st2.sid FROM t_student st2 INNER JOIN t_score sc2 ON sc2.sid=st2.sid AND sc2.cid='02'
)
#03)查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT st.* FROM t_student st INNER JOIN t_score sc ON sc.sid=st.sid AND sc.cid='01'
WHERE st.sid NOT IN (
SELECT st2.sid FROM t_student st2 INNER JOIN t_score sc2 ON sc2.sid=st2.sid AND sc2.cid='02'
)
#04)查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM t_student st INNER JOIN t_score sc ON sc.sid=st.sid AND sc.cid='02'
WHERE st.sid NOT IN (
SELECT st2.sid FROM t_student st2 INNER JOIN t_score sc2 ON sc2.sid=st2.sid AND sc2.cid='01'
)
#05)查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT st.sid,st.sname,ROUND(AVG(sc.score),2) 平均分
FROM t_student st
LEFT JOIN t_score sc ON st.sid=sc.sid
GROUP BY st.sid HAVING AVG(sc.score)>=60;
#06)查询在t_score表存在成绩的学生信息
SELECT st.* FROM t_student st WHERE sid IN(
SELECT sc.sid FROM t_score sc
)
#07)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT st.sid,st.sname,COUNT(sc.cid),ROUND(SUM(sc.score),2) AS '总成绩' FROM t_student st
LEFT JOIN t_score sc ON sc.sid=st.sid GROUP BY st.sid
#08)查询「李」姓老师的数量
SELECT COUNT(*) FROM t_teacher WHERE tname LIKE '李%'
#09)查询学过「张三」老师授课的同学的信息
SELECT st.* FROM t_student st WHERE sid IN(
SELECT sid FROM t_score WHERE cid = (
SELECT cid FROM t_course WHERE tid = (
SELECT tid FROM t_teacher WHERE tname = '张三'
)
)
)
#10)查询没有学全所有课程的同学的信息
SELECT st.*FROM t_student st WHERE sid NOT IN(
SELECT sid FROM t_score GROUP BY sid HAVING COUNT(cid)>=(SELECT COUNT(cid) FROM t_course)
)
#11)查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT st.sname FROM t_student st WHERE st.sid NOT IN (
SELECT sc.sid FROM t_score sc
INNER JOIN t_course c ON c.cid=sc.cid
INNER JOIN t_teacher t ON t.tid=c.tid AND t.tname="张三"
)
#12)查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT st.sid,st.sname,AVG(sc.score) FROM t_student st
LEFT JOIN t_score sc ON sc.sid=st.sid WHERE sc.sid IN(
SELECT sc.sid FROM t_score sc WHERE sc.score<60 OR sc.score IS NULL GROUP BY sc.sid HAVING COUNT(1)>=2
)
GROUP BY st.sid
#13)检索" 01 "课程分数小于 60,按分数降序排列的学生信息
SELECT st.*,sc.score FROM t_student st LEFT JOIN t_score sc ON sc.sid=st.sid WHERE sc.cid='01' AND sc.score<60 ORDER BY sc.score DESC ;
#14)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT st.sid,st.sname,
(IFNULL((sc4.score),0)) "平均分",
(IFNULL((sc.score),0)) "语文",
(IFNULL((sc2.score),0)) "数学",
(IFNULL((sc3.score),0))"英语"
FROM t_student st
LEFT JOIN t_score sc ON sc.sid=st.sid AND sc.cid="01"
LEFT JOIN t_score sc2 ON sc2.sid=st.sid AND sc2.cid="02"
LEFT JOIN t_score sc3 ON sc3.sid=st.sid AND sc3.cid="03"
LEFT JOIN t_score sc4 ON sc4.sid=st.sid
GROUP BY st.sid
ORDER BY AVG(sc4.score) DESC
#15)查询各科成绩最高分、最低分和平均分:
#以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
#要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT c.cid,c.cname,MAX(sc.score) "最高分",MIN(sc.score) "最低分",AVG(sc.score) "平均分"
,((SELECT COUNT(sid) FROM t_score WHERE score>=60 AND cid=c.cid )/(SELECT COUNT(sid) FROM t_score WHERE cid=c.cid)) "及格率"
,((SELECT COUNT(sid) FROM t_score WHERE score>=70 AND score<80 AND cid=c.cid )/(SELECT COUNT(sid) FROM t_score WHERE cid=c.cid)) "中等率"
,((SELECT COUNT(sid) FROM t_score WHERE score>=80 AND score<90 AND cid=c.cid )/(SELECT COUNT(sid) FROM t_score WHERE cid=c.cid)) "优良率"
,((SELECT COUNT(sid) FROM t_score WHERE score>=90 AND cid=c.cid )/(SELECT COUNT(sid) FROM t_score WHERE cid=c.cid)) "优秀率"
FROM t_course c
LEFT JOIN t_score sc ON sc.cid=c.cid
GROUP BY c.cid
结果