sql语句练习(未完待续,sqlserver暂时没有接触过)

it2023-03-02  86

sql语句练习(未完待续,sqlserver暂时没有接触过)

1.mysql

练习1

--建表 --学生表 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"课程比"02"课程成绩高的学生的信息及课程分数 SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score FROM student a JOIN score b ON a.s_id=b.s_id AND b.c_id='01' LEFT JOIN score c ON a.s_id=c.s_id AND c.c_id='02' OR c.c_id = NULL WHERE b.s_score>c.s_score -- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score FROM student a LEFT JOIN score b ON a.s_id=b.s_id AND b.c_id='01' OR b.c_id=NULL JOIN score c ON a.s_id=c.s_id AND c.c_id='02' WHERE b.s_score<c.s_score -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score FROM student b JOIN score a ON b.s_id = a.s_id GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)>=60; -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的) SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score FROM student b LEFT JOIN score a ON b.s_id = a.s_id GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60 UNION SELECT a.s_id,a.s_name,0 AS avg_score FROM student a WHERE a.s_id NOT IN ( SELECT DISTINCT s_id FROM score); -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 SELECT a.s_id,a.s_name,COUNT(b.c_id) AS sum_course,SUM(b.s_score) AS sum_score FROM student a LEFT JOIN score b ON a.s_id=b.s_id GROUP BY a.s_id,a.s_name; -- 6、查询"李"姓老师的数量 SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '李%'; -- 7、查询学过"张三"老师授课的同学的信息 SELECT a.* FROM student a JOIN score b ON a.s_id=b.s_id WHERE b.c_id IN( SELECT c_id FROM course WHERE t_id =( SELECT t_id FROM teacher WHERE t_name = '张三')); -- 8、查询没学过"张三"老师授课的同学的信息 SELECT * FROM student c WHERE c.s_id NOT IN( SELECT a.s_id FROM student a JOIN score b ON a.s_id=b.s_id WHERE b.c_id IN( SELECT c_id FROM course WHERE t_id =( SELECT t_id FROM teacher WHERE t_name = '张三'))); -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 SELECT a.* FROM student a,score b,score c WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id='01' AND c.c_id='02'; -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 SELECT a.* FROM student a WHERE a.s_id IN (SELECT s_id FROM score WHERE c_id='01' ) AND a.s_id NOT IN(SELECT s_id FROM score WHERE c_id='02') -- 11、查询没有学全所有课程的同学的信息 SELECT s.* FROM student s WHERE s.s_id IN( SELECT s_id FROM score WHERE s_id NOT IN( SELECT a.s_id FROM score a JOIN score b ON a.s_id = b.s_id AND b.c_id='02' JOIN score c ON a.s_id = c.s_id AND c.c_id='03' WHERE a.c_id='01')) -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 SELECT * FROM student WHERE s_id IN( SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN(SELECT a.c_id FROM score a WHERE a.s_id='01') ); -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 SELECT a.* FROM student a WHERE a.s_id IN( SELECT DISTINCT s_id FROM score WHERE s_id!='01' AND c_id IN(SELECT c_id FROM score WHERE s_id='01') GROUP BY s_id HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE s_id='01')); -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 SELECT a.s_name FROM student a WHERE a.s_id NOT IN ( SELECT s_id FROM score WHERE c_id = (SELECT c_id FROM course WHERE t_id =( SELECT t_id FROM teacher WHERE t_name = '张三')) GROUP BY s_id); -- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 SELECT a.s_id,a.s_name,ROUND(AVG(b.s_score)) FROM student a LEFT JOIN score b ON a.s_id = b.s_id WHERE a.s_id IN( SELECT s_id FROM score WHERE s_score<60 GROUP BY s_id HAVING COUNT(1)>=2) GROUP BY a.s_id,a.s_name -- 16、检索"01"课程分数小于60,按分数降序排列的学生信息 SELECT a.*,b.c_id,b.s_score FROM student a,score b WHERE a.s_id = b.s_id AND b.c_id='01' AND b.s_score<60 ORDER BY b.s_score DESC; -- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 SELECT a.s_id,(SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='01') AS 语文, (SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='02') AS 数学, (SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='03') AS 英语, ROUND(AVG(s_score),2) AS 平均分 FROM score a GROUP BY a.s_id ORDER BY 平均分 DESC; -- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 --及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 SELECT a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2), ROUND(100*(SUM(CASE WHEN a.s_score>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 及格率, ROUND(100*(SUM(CASE WHEN a.s_score>=70 AND a.s_score<=80 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 中等率, ROUND(100*(SUM(CASE WHEN a.s_score>=80 AND a.s_score<=90 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 优良率, ROUND(100*(SUM(CASE WHEN a.s_score>=90 THEN 1 ELSE 0 END)/SUM(CASE WHEN a.s_score THEN 1 ELSE 0 END)),2) AS 优秀率 FROM score a LEFT JOIN course b ON a.c_id = b.c_id GROUP BY a.c_id,b.c_name -- 19、按各科成绩进行排序,并显示排名(实现不完全) -- mysql没有rank函数 SELECT a.s_id,a.c_id, @i:=@i +1 AS i保留排名, @k:=(CASE WHEN @score=a.s_score THEN @k ELSE @i END) AS rank不保留排名, @score:=a.s_score AS score FROM ( SELECT s_id,c_id,s_score FROM score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(SELECT @k:=0,@i:=0,@score:=0)s UNION SELECT a.s_id,a.c_id, @i:=@i +1 AS i, @k:=(CASE WHEN @score=a.s_score THEN @k ELSE @i END) AS rank, @score:=a.s_score AS score FROM ( SELECT s_id,c_id,s_score FROM score WHERE c_id='02' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(SELECT @k:=0,@i:=0,@score:=0)s UNION SELECT a.s_id,a.c_id, @i:=@i +1 AS i, @k:=(CASE WHEN @score=a.s_score THEN @k ELSE @i END) AS rank, @score:=a.s_score AS score FROM ( SELECT s_id,c_id,s_score FROM score WHERE c_id='03' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(SELECT @k:=0,@i:=0,@score:=0)s -- 20、查询学生的总成绩并进行排名 SELECT a.s_id, @i:=@i+1 AS i, @k:=(CASE WHEN @score=a.sum_score THEN @k ELSE @i END) AS rank, @score:=a.sum_score AS score FROM (SELECT s_id,SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC)a, (SELECT @k:=0,@i:=0,@score:=0)s -- 21、查询不同老师所教不同课程平均分从高到低显示 SELECT a.t_id,c.t_name,a.c_id,ROUND(AVG(s_score),2) AS avg_score FROM course a LEFT JOIN score b ON a.c_id=b.c_id LEFT JOIN teacher c ON a.t_id=c.t_id GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC; -- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 SELECT d.*,c.排名,c.s_score,c.c_id FROM ( SELECT a.s_id,a.s_score,a.c_id,@i:=@i+1 AS 排名 FROM score a,(SELECT @i:=0)s WHERE a.c_id='01' )c LEFT JOIN student d ON c.s_id=d.s_id WHERE 排名 BETWEEN 2 AND 3 UNION SELECT d.*,c.排名,c.s_score,c.c_id FROM ( SELECT a.s_id,a.s_score,a.c_id,@j:=@j+1 AS 排名 FROM score a,(SELECT @j:=0)s WHERE a.c_id='02' )c LEFT JOIN student d ON c.s_id=d.s_id WHERE 排名 BETWEEN 2 AND 3 UNION SELECT d.*,c.排名,c.s_score,c.c_id FROM ( SELECT a.s_id,a.s_score,a.c_id,@k:=@k+1 AS 排名 FROM score a,(SELECT @k:=0)s WHERE a.c_id='03' )c LEFT JOIN student d ON c.s_id=d.s_id WHERE 排名 BETWEEN 2 AND 3; -- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 SELECT DISTINCT f.c_name,a.c_id,b.`85-100`,b.百分比,c.`70-85`,c.百分比,d.`60-70`,d.百分比,e.`0-60`,e.百分比 FROM score a LEFT JOIN (SELECT c_id,SUM(CASE WHEN s_score >85 AND s_score <=100 THEN 1 ELSE 0 END) AS `85-100`, ROUND(100*(SUM(CASE WHEN s_score >85 AND s_score <=100 THEN 1 ELSE 0 END)/COUNT(*)),2) AS 百分比 FROM score GROUP BY c_id)b ON a.c_id=b.c_id LEFT JOIN (SELECT c_id,SUM(CASE WHEN s_score >70 AND s_score <=85 THEN 1 ELSE 0 END) AS `70-85`, ROUND(100*(SUM(CASE WHEN s_score >70 AND s_score <=85 THEN 1 ELSE 0 END)/COUNT(*)),2) AS 百分比 FROM score GROUP BY c_id)c ON a.c_id=c.c_id LEFT JOIN (SELECT c_id,SUM(CASE WHEN s_score >60 AND s_score <=70 THEN 1 ELSE 0 END) AS `60-70`, ROUND(100*(SUM(CASE WHEN s_score >60 AND s_score <=70 THEN 1 ELSE 0 END)/COUNT(*)),2) AS 百分比 FROM score GROUP BY c_id)d ON a.c_id=d.c_id LEFT JOIN (SELECT c_id,SUM(CASE WHEN s_score >=0 AND s_score <=60 THEN 1 ELSE 0 END) AS `0-60`, ROUND(100*(SUM(CASE WHEN s_score >=0 AND s_score <=60 THEN 1 ELSE 0 END)/COUNT(*)),2) AS 百分比 FROM score GROUP BY c_id)e ON a.c_id=e.c_id LEFT JOIN course f ON a.c_id = f.c_id -- 24、查询学生平均成绩及其名次 SELECT a.s_id, @i:=@i+1 AS '不保留空缺排名', @k:=(CASE WHEN @avg_score=a.avg_s THEN @k ELSE @i END) AS '保留空缺排名', @avg_score:=avg_s AS '平均分' FROM (SELECT s_id,ROUND(AVG(s_score),2) AS avg_s FROM score GROUP BY s_id)a,(SELECT @avg_score:=0,@i:=0,@k:=0)b; -- 25、查询各科成绩前三名的记录 -- 1.选出b表比a表成绩大的所有组 -- 2.选出比当前id成绩大的 小于三个的 SELECT a.s_id,a.c_id,a.s_score FROM score a LEFT JOIN score b ON a.c_id = b.c_id AND a.s_score<b.s_score GROUP BY a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3 ORDER BY a.c_id,a.s_score DESC -- 26、查询每门课程被选修的学生数 SELECT c_id,COUNT(s_id) FROM score a GROUP BY c_id -- 27、查询出只有两门课程的全部学生的学号和姓名 SELECT s_id,s_name FROM student WHERE s_id IN( SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id)=2); -- 28、查询男生、女生人数 SELECT s_sex,COUNT(s_sex) AS 人数 FROM student GROUP BY s_sex -- 29、查询名字中含有"风"字的学生信息 SELECT * FROM student WHERE s_name LIKE '%风%'; -- 30、查询同名同性学生名单,并统计同名人数 SELECT a.s_name,a.s_sex,COUNT(*) FROM student a JOIN student b ON a.s_id !=b.s_id AND a.s_name = b.s_name AND a.s_sex = b.s_sex GROUP BY a.s_name,a.s_sex -- 31、查询1990年出生的学生名单 SELECT s_name FROM student WHERE s_birth LIKE '1990%' -- 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 SELECT c_id,ROUND(AVG(s_score),2) AS avg_score FROM score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC -- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 SELECT a.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score FROM score a LEFT JOIN student b ON a.s_id=b.s_id GROUP BY s_id HAVING avg_score>=85 -- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数 SELECT a.s_name,b.s_score FROM score b LEFT JOIN student a ON a.s_id=b.s_id WHERE b.c_id=( SELECT c_id FROM course WHERE c_name ='数学') AND b.s_score<60 -- 35、查询所有学生的课程及分数情况; SELECT a.s_id,a.s_name, SUM(CASE c.c_name WHEN '语文' THEN b.s_score ELSE 0 END) AS '语文', SUM(CASE c.c_name WHEN '数学' THEN b.s_score ELSE 0 END) AS '数学', SUM(CASE c.c_name WHEN '英语' THEN b.s_score ELSE 0 END) AS '英语', SUM(b.s_score) AS '总分' FROM student a LEFT JOIN score b ON a.s_id = b.s_id LEFT JOIN course c ON b.c_id = c.c_id GROUP BY a.s_id,a.s_name -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; SELECT a.s_name,b.c_name,c.s_score FROM course b LEFT JOIN score c ON b.c_id = c.c_id LEFT JOIN student a ON a.s_id=c.s_id WHERE c.s_score>=70 -- 37、查询不及格的课程 SELECT a.s_id,a.c_id,b.c_name,a.s_score FROM score a LEFT JOIN course b ON a.c_id = b.c_id WHERE a.s_score<60 --38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名; SELECT a.s_id,b.s_name FROM score a LEFT JOIN student b ON a.s_id = b.s_id WHERE a.c_id = '01' AND a.s_score>80 -- 39、求每门课程的学生人数 SELECT COUNT(*) FROM score GROUP BY c_id; -- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩 -- 查询老师id SELECT c_id FROM course c,teacher d WHERE c.t_id=d.t_id AND d.t_name='张三' -- 查询最高分(可能有相同分数) SELECT MAX(s_score) FROM score WHERE c_id='02' -- 查询信息 SELECT a.*,b.s_score,b.c_id,c.c_name FROM student a LEFT JOIN score b ON a.s_id = b.s_id LEFT JOIN course c ON b.c_id=c.c_id WHERE b.c_id =(SELECT c_id FROM course c,teacher d WHERE c.t_id=d.t_id AND d.t_name='张三') AND b.s_score IN (SELECT MAX(s_score) FROM score WHERE c_id='02') -- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 SELECT DISTINCT b.s_id,b.c_id,b.s_score FROM score a,score b WHERE a.c_id != b.c_id AND a.s_score = b.s_score -- 42、查询每门功成绩最好的前两名 -- 牛逼的写法 SELECT a.s_id,a.c_id,a.s_score FROM score a WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2 ORDER BY a.c_id -- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 SELECT c_id,COUNT(*) AS total FROM score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC -- 44、检索至少选修两门课程的学生学号 SELECT s_id,COUNT(*) AS sel FROM score GROUP BY s_id HAVING sel>=2 -- 45、查询选修了全部课程的学生信息 SELECT * FROM student WHERE s_id IN( SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM course)) --46、查询各学生的年龄 -- 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一 SELECT s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - (CASE WHEN DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') THEN 0 ELSE 1 END)) AS age FROM student; -- 47、查询本周过生日的学生 SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth) SELECT * FROM student WHERE YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d')) SELECT WEEK(DATE_FORMAT(NOW(),'%Y%m%d')) -- 48、查询下周过生日的学生 SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth) -- 49、查询本月过生日的学生 SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth) -- 50、查询下月过生日的学生 SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)

练习2

数据库脚本

-- ----------------------------------- -- 创建db10库、emp表并插入记录 -- ----------------------------------- -- 删除db10库(如果存在) drop database if exists db10; -- 重新创建db10库 create database db10 charset utf8; -- 选择db10库 use db10; -- 删除员工表(如果存在) drop table if exists emp; -- 创建员工表 create table emp( id int primary key auto_increment, -- 员工编号 name varchar(50), -- 员工姓名 gender char(1), -- 员工性别 birthday date, -- 员工生日 dept varchar(50), -- 所属部门 job varchar(50), -- 所任职位 sal double, -- 薪资 bonus double -- 奖金 ); -- 往员工表中插入记录 insert into emp values(null,'王海涛','男','1995-10-25','培优部','金牌讲师','1800','300'); insert into emp values(null,'齐雷','男','1994-11-6','培优部','金牌讲师','2500','600'); insert into emp values(null,'刘沛霞','女','1996-09-14','培优部','金牌讲师','1400','300'); insert into emp values(null,'陈子枢','男','1991-05-18','培优部','部门总监','4200','500'); insert into emp values(null,'刘昱江','男','1993-11-18','培优部','金牌讲师','1600','500'); insert into emp values(null,'王克晶','女','1998-07-18','就业部','金牌讲师','3700','600'); insert into emp values(null,'苍老师','男','1995-08-18','就业部','部门总监','4850','400'); insert into emp values(null,'范传奇','男','1999-09-18','就业部','金牌讲师','3200','600'); insert into emp values(null,'刘涛','男','1990-10-18','就业部','金牌讲师','2700','400'); insert into emp values(null,'韩少云','男','1980-12-18',null,'CEO','5000',null); -- ----------------------------------- -- 创建db20库、dept表、emp表并插入记录 -- ----------------------------------- -- 删除db20库(如果存在) drop database if exists db20; -- 重新创建db20库 create database db20 charset utf8; -- 选择db20库 use db20; -- 删除部门表, 如果存在 drop table if exists dept; -- 重新创建部门表, 要求id, name字段 create table dept( id int primary key auto_increment, -- 部门编号 name varchar(20) -- 部门名称 ); -- 往部门表中插入记录 insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部'); insert into dept values(null, '销售部'); -- 删除员工表, 如果存在 drop table if exists emp; -- 创建员工表, 要求id, name, dept_id create table emp( id int primary key auto_increment, -- 员工编号 name varchar(20), -- 员工姓名 dept_id int -- 部门编号 -- ,foreign key(dept_id) references dept(id) ); insert into emp values(null, '张三', 1); insert into emp values(null, '李四', 2); insert into emp values(null, '老王', 3); insert into emp values(null, '赵六', 4); insert into emp values(null, '刘能', 4); -- ----------------------------------- -- 创建db30库、dept表、emp表并插入记录 -- ----------------------------------- -- 删除db30库(如果存在) drop database if exists db30; -- 重新创建db30库 create database db30 charset utf8; -- 选择db30库 use db30; -- 删除部门表, 如果存在 drop table if exists dept; -- 重新创建部门表, 要求id, name字段 create table dept( id int primary key auto_increment, -- 部门编号 name varchar(20) -- 部门名称 ); -- 往部门表中插入记录 insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部'); insert into dept values(null, '销售部'); -- 删除员工表, 如果存在 drop table if exists emp; -- 创建员工表(员工编号、员工姓名、所在部门编号) create table emp( id int primary key auto_increment, -- 员工编号 name varchar(20), -- 员工姓名 dept_id int -- 部门编号 ); -- 往员工表中插入记录 insert into emp values(null, '张三', 1); insert into emp values(null, '李四', 2); insert into emp values(null, '老王', 3); insert into emp values(null, '赵六', 5); -- ----------------------------------- -- 创建db40库、dept表、emp表并插入记录 -- ----------------------------------- -- 删除db40库(如果存在) drop database if exists db40; -- 重新创建db40库 create database db40 charset utf8; -- 选择db40库 use db40; -- 创建部门表 create table dept( -- 创建部门表 id int primary key, -- 部门编号 name varchar(50), -- 部门名称 loc varchar(50) -- 部门位置 ); -- 创建员工表 create table emp( -- 创建员工表 id int primary key, -- 员工编号 name varchar(50), -- 员工姓名 job varchar(50), -- 职位 topid int, -- 直属上级 hdate date, -- 受雇日期 sal int, -- 薪资 bonus int, -- 奖金 dept_id int, -- 所在部门编号 foreign key(dept_id) references dept(id) ); -- 往部门表中插入记录 insert into dept values ('10', '培优部', '北京'); insert into dept values ('20', '就业部', '上海'); insert into dept values ('30', '大数据部', '广州'); insert into dept values ('40', '销售部', '深圳'); -- 往员工表中插入记录 insert into emp values ('1001', '王克晶', '办事员', '1007', '1980-12-17', '800', 500, '20'); insert into emp values ('1003', '齐雷', '分析员', '1011', '1981-02-20', '1900', '300', '10'); insert into emp values ('1005', '王海涛', '推销员', '1011', '1981-02-22', '2450', '600', '10'); insert into emp values ('1007', '刘苍松', '经理', '1017', '1981-04-02', '3675', 700, '20'); insert into emp values ('1009', '张慎政', '推销员', '1011', '1981-09-28', '1250', '1400', '10'); insert into emp values ('1011', '陈子枢', '经理', '1017', '1981-05-01', '3450', 400, '10'); insert into emp values ('1013', '张久军', '办事员', '1011', '1981-06-09', '1250', 800, '10'); insert into emp values ('1015', '程祖红', '分析员', '1007', '1987-04-19', '3000', 1000, '20'); insert into emp values ('1017', '韩少云', '董事长', null, '1981-11-17', '5000', null, null); insert into emp values ('1019', '刘沛霞', '推销员', '1011', '1981-09-08', '1500', 500, '10'); insert into emp values ('1021', '范传奇', '办事员', '1007', '1987-05-23', '1100', 1000, '20'); insert into emp values ('1023', '赵栋', '经理', '1017', '1981-12-03', '950', null, '30'); insert into emp values ('1025', '朴乾', '分析员', '1023', '1981-12-03', '3000', 600, '30'); insert into emp values ('1027', '叶尚青', '办事员', '1023', '1982-01-23', '1300', 400, '30'); -- ------------------- 执行完毕 ----------------------- -- -------------------------------------------------- -- 创建db20库、dept表、emp表并插入记录 -- -------------------------------------------------- -- 删除db20库(如果存在) drop database if exists db20; -- 重新创建db20库 create database db20 charset utf8; -- 选择db20库 use db20; -- 删除部门表, 如果存在 drop table if exists dept; -- 重新创建部门表, 要求id, name字段 create table dept( id int primary key auto_increment, -- 部门编号 name varchar(20) -- 部门名称 ); -- 往部门表中插入记录 insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部'); insert into dept values(null, '销售部'); -- 删除员工表, 如果存在 drop table if exists emp; -- 创建员工表, 要求id, name, dept_id create table emp( id int primary key auto_increment, -- 员工编号 name varchar(20), -- 员工姓名 dept_id int -- 部门编号 ,foreign key(dept_id) references dept(id) -- 指定外键 ); insert into emp values(null, '张三', 1); insert into emp values(null, '李四', 2); insert into emp values(null, '老王', 3); insert into emp values(null, '赵六', 4); insert into emp values(null, '刘能', 4); -- ----------------------------------- -- 一、创建建数据库、创建建数据表、查看数据库、查看数据表 -- ----------------------------------- -- 01.查看mysql服务器中所有数据库 show databases; -- 02.进入某一数据库(进入数据库后,才能操作库中的表和表记录) -- 语法:use 库名; use mysql; use test; use jtsys -- 查看已进入的库 select database(); -- 03.查看当前数据库中的所有表 show tables; -- 04.删除mydb1库 -- 语法:drop database 库名; drop database mydb1; -- 思考:当删除的表不存在时,如何避免错误产生? drop database if exists mydb1; -- 05.重新创建mydb1库,指定编码为utf8 -- 语法:create database 库名 charset 编码;(mysql不能识别横杠 - ) create database mydb1 charset utf8; -- 如果不存在则创建mydb1; create database if not exists mydb1 charset utf8; -- 06.查看建库时的语句(并验证数据库库使用的编码) show create database mydb1; -- 语法:show create database 库名; -- 07.进入mydb1库,删除stu学生表(如果存在) -- 语法:drop table 表名; drop table if exists stu; -- 08.创建stu学生表(编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]) /* 建表的语法: create table 表名( 列名 数据类型, 列名 数据类型, ... ); */ use mydb1; drop table if exists stu; create table stu( id int primary key auto_increment, -- id主键自增 name varchar(20), gender varchar(10), birthday date, score double ); -- 09.查看stu学生表结构 -- 语法:desc 表名 desc stu; -- 查看建表时的语句 show create table stu; -- ----------------------------------- -- 二、****** 新增、修改、删除表记录 ******* -- ----------------------------------- -- 10.往学生表(stu)中插入记录(数据) -- 插入记录:insert into 表名(列1,列2,列3...) values(值1,值2,值3...); insert into stu(id,name,gender,birthday,score) values(null,'tony','male','1988-1-1',78); insert into stu values(null,'tom','female','1978-1-1',85); insert into stu values(5,'王海涛','male','1990-2-1',68); insert into stu values(3,'陈子枢','male','1990-2-1',68); insert into stu(name,gender,birthday,score) values('tony','male','1988-1-1',78); /* 提示: 设置编码:set names gbk; mysql --default-character-set=gbk -uroot -proot */ -- 11.查询stu表所有学生的信息 select * from stu; -- 12.修改stu表中所有学生的成绩,加10分特长分 -- 修改语法: update 表名 set 列=值,列=值,列=值...; update stu set score=score+10; update stu set score+=10;-- 错误,mysql不支持+= -- 13.修改stu表中王海涛的成绩,将成绩改为88分。 update stu set score=88 where name='王海涛'; /* 提示:where子句用于对记录进行筛选过滤, 保留符合条件的记录,将不符合条件的记录剔除。*/ -- 14.删除stu表中所有的记录 -- 删除记录语法: delete from 表名 [where条件] delete from stu; -- 不会删除自增变量的值 -- 仅删除符合条件的 delete from stu where id>=3; truncate table stu; -- 清空表记录并重置表 -- ----------------------------------- -- 三、***** 基础查询、where子句查询 ****** -- ----------------------------------- -- 准备数据: 以下练习将使用db10库中的表及表记录,请先进入db10数据库!!! -- 准备数据: 以下练习将使用db10库中的表及表记录,请先进入db10数据库!!! -- 15.查询emp表中的所有员工,显示所有列 select * from emp; -- select后跟要查询那些列,* 表示所有列 -- from后面跟要查询的是哪张表 -- 16.查询emp表中的所有员工,显示姓名,薪资,奖金 select name,sal,bonus from emp; /* 使用 *(星号)的缺点:把不必要的列也查询出来了,而且效率不如直接指定列名 */ -- 17.查询emp表中的所有部门和职位 select dept,job from emp; /* 思考:如果查询的结果中,存在大量重复的记录,如何剔除重复记录,只保留一条? */ -- distinct 用于剔除重复的记录 select distinct dept,job from emp; -- ----------------------------------- -- *********** where子句查询 *********** -- ----------------------------------- -- where子句用于筛选过滤,将符合条件的记录保留,剔除不符合条件的记录 -- 18.查询emp表中薪资大于3000的所有员工,显示员工姓名、薪资 select name,sal from emp where sal > 3000; -- 19.查询emp表中总薪资(薪资+奖金)大于3500的所有员工,显示员工姓名、总薪资 select name,sal+ifnull(bonus,0) from emp where sal+ifnull(bonus,0) > 3500; -- --------------------- select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp where sal+ifnull(bonus,0) > 3500; -- --------------------- as可以省略 select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0) > 3500; -- ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值 -- 注意查看上面查询结果中的表头,如何将表头中的 sal+bonus 修改为 "总薪资" -- where子句中不能使用列别名 -- 20.查询emp表中薪资在3000和4500之间的员工,显示员工姓名和薪资 select name,sal from emp where sal >= 3000 and sal <= 4500; -- --------------------------- -- 提示: between...and... 在...之间 select name,sal from emp where sal between 3000 and 4500; -- 21.查询emp表中薪资为 1400、1600、1800的员工,显示员工姓名和薪资 select name,sal from emp where sal=1400 or sal=1600 or sal=1800; -- ------------------------- select name,sal from emp where sal in(1400,1600,1800); -- --------------------批量删除 delete from stu where id in(1,3,5,7,9); -- 22.查询薪资不为1400、1600、1800的员工 select name,sal from emp where sal!=1400 and sal!=1600 and sal!=1800; select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800); select name,sal from emp where sal not in(1400,1600,1800); -- 23.查询emp表中薪资大于4000和薪资小于2000的员工,显示员工姓名、薪资。 select name,sal from emp where sal>4000 or sal<2000; -- 24.查询emp表中薪资大于3000并且奖金小于600的员工,显示员工姓名、薪资、奖金。 select name,sal,bonus from emp where sal>3000 and ifnull(bonus,0)<600; -- 处理null值 -- 25.查询没有部门的员工(即部门列为null值) select * from emp where dept is null; -- 思考:如何查询有部门的员工(即部门列不为null值) select * from emp where dept is not null; -- ----------------------------------- -- *********** Like模糊查询 ************ -- ----------------------------------- -- 26.查询emp表中姓名中以"刘"字开头的员工,显示员工姓名。 select name from emp where name like '刘%'; /* like进行模糊查询,"%" 表示通配,表示0或多个任意的字符。 "_"表示一个任意的字符 */ -- 27.查询emp表中姓名中包含"涛"字的员工,显示员工姓名。 select name from emp where name like '%涛%'; -- 28.查询emp表中姓名以"刘"开头,并且姓名为两个字的员工,显示员工姓名。 select name from emp where name like '刘_'; -- ----------------------------------- -- 三、分组查询、聚合函数、排序查询 -- ----------------------------------- -- 29.对emp表按照部门对员工进行分组,查看分组后效果 /* 分组的语法: select 查询的列 from 表名 group by 列名 根据指定的列进行分组 */ select * from emp group by dept; -- 30.对emp表按照职位进行分组, 并统计每个职位的人数, 显示职位和对应人数 -- count(列|*) 统计行数 -- 统计emp表中的所有员工的人数(按组统计,若没有分组,查询结果默认为一组) select count(*) from emp; select job,count(*) from emp group by job; select dept,count(*) from emp group by dept; select bonus,count(*) from emp group by bonus; -- 31.对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金),显示部门名称和最高薪资 -- max(列名) 求当前列中的最大值 -- min(列名) 求当前列中的最小值 -- 求所有员工中的最高薪资/最低薪资 select max(sal) from emp; select min(sal) from emp; -- 求每个部门(每个组)中的最高薪资 select dept, max(sal) from emp group by dept; -- 32.统计emp表中薪资大于3000的员工个数(- count(column)统计某列的行数) select count(*) from emp where sal>3000; -- 33.统计emp表中所有员工的薪资总和(不包含奖金)(- sum(column)对某列的值求和) select sum(sal) from emp; select sum(bonus) from emp; -- 求所有员工的薪资和奖金的总和 select sum(sal+bonus) from emp;-- 误差 select sum(sal+ifnull(bonus,0)) from emp; select sum(sal)+sum(bonus) from emp; -- 34.统计emp表员工的平均薪资(不包含奖金)(- avg(column)对某列的值求平均值) select avg(sal) from emp; select avg(bonus) from emp; select avg(ifnull(bonus,0)) from emp; -- 35.查询emp表中所有在1993和1995年之间出生的员工,显示姓名、出生日期。 select name,birthday from emp where birthday between '1993-1-1' and '1995-12-31'; -------------------------------- select name,birthday from emp where year(birthday) between 1993 and 1995; -- 36.查询本月过生日的所有员工 select name,birthday from emp where month(birthday)=month(curdate()) -- 查询下个月过生日的所有员工 select name,birthday from emp where month(birthday)=month(curdate())+1 /* curdate() 获取当前日期 年月日 curtime() 获取当前时间 时分秒 sysdate() 获取当前日期+时间 年月日 时分秒 */ select bonus from emp; select count(*) from emp; select count(bonus) from emp; -- ----------------------------------- -- ************* 排序查询 ************** -- ----------------------------------- /* order by 排序的列 asc 升序(从低到高) order by 排序的列 desc 降序(从高到低) 默认就是升序,所以asc可以省略不写 */ -- 37.对emp表中所有员工的薪资进行升序(从低到高)排序,显示员工姓名、薪资。 select name,sal from emp order by sal asc; -- 38.对emp表中所有员工奖金进行降序(从高到低)排序,显示员工姓名、奖金。 select name,bonus from emp order by bonus desc; -- ----------------------------------- -- ************* 分页查询 ************** -- ----------------------------------- /* 在mysql中,通过limit进行分页查询: limit (页码-1)*每页显示记录数, 每页显示记录数 */ -- 39.查询emp表中的所有记录,分页显示:每页显示3条记录,返回第 1 页。 select * from emp limit 0, 3; select * from emp limit 3, 3; select * from emp limit 6, 3; select * from emp limit 9, 3; -- 40.查询emp表中的所有记录,分页显示:每页显示3条记录,返回第 2 页。 select * from emp limit 3, 3; -- ----------------------------------- -- 三、外键 -- ----------------------------------- -- 准备数据: 以下练习将使用db20库中的表及表记录,请先进入db20数据库!!! -- 准备数据: 以下练习将使用db20库中的表及表记录,请先进入db20数据库!!! -- 41.尝试删除dept表中的某一个部门 /* 上面的部门删除成功后,员工表里的某些员工就没有了对应的部门, 这种我们称之为数据的完整性被破坏了, 为了避免这种情况,可以在删除之前,查看将要删除的部门下是否还有员工存在,如果有就不要删除; 或者,让数据库帮我们去维护这样的对应关系,也就是当将要被删除的部门下如果还有员工, 就阻止删除操作,让数据库帮我们维护这样的对应关系,就需要指定外键。*/ -- 42.重新创建db20中的dept和emp表,在创建时,指定emp表中的dept_id列为外键,即这一列要严格参考dept表中的id列, 再次尝试删除dept表中的某一个部门,查看是否能删除成功 -- ----------------------------------- -- 四、关联查询、外连接查询 -- ----------------------------------- -- 准备数据: 以下练习将使用db30库中的表及表记录,请先进入db30数据库!!! -- 准备数据: 以下练习将使用db30库中的表及表记录,请先进入db30数据库!!! -- 43.查询部门和部门对应的员工信息 select * from dept,emp; 上面这种查询叫做"笛卡尔积查询":如果同时查询两张表,其中一张表中有m条数据,另外一张表中有n条数据,笛卡尔积查询的结果是 m*n条. 由于这个查询结果中存在大量错误的数据,所以我们一般不会直接使用这种查询。 可以使用where子句,通过条件将错误的数据剔除,保留正确的数据. select * from dept,emp where emp.dept_id=dept.id; -- 44.查询所有部门和部门下的员工,如果部门下没有员工,员工显示为null select * from dept left join emp on emp.dept_id=dept.id; select * from emp right join dept on emp.dept_id=dept.id; -- 45.查询部门和所有员工,如果员工没有所属部门,部门显示为null select * from emp left join dept on emp.dept_id=dept.id; select * from dept right join emp on emp.dept_id=dept.id; -- union将两条SQL语句查询的结果合并在一起,并剔除重复记录 select * from dept left join emp on emp.dept_id=dept.id union select * from dept right join emp on emp.dept_id=dept.id; -- ----------------------------------- -- 五、子查询、多表查询 -- ----------------------------------- -- 准备数据:以下练习将使用db40库中的表及表记录,请先进入db40数据库!!! -- 准备数据:以下练习将使用db40库中的表及表记录,请先进入db40数据库!!! -- 46.列出薪资比'王海涛'薪资高的所有员工,显示姓名、薪资 -- 求出'王海涛'的薪资 select sal from emp where name='王海涛'; -- 列出薪资比2450高的所有员工 select name,sal from emp where sal > (select sal from emp where name='王海涛'); -- 47.列出与'刘沛霞'从事相同职位的所有员工,显示姓名、职位。 -- 求出'刘沛霞'从事的职位 select job from emp where name='刘沛霞'; -- 求出与'刘沛霞'从事相同职位的所有员工 select name,job from emp where job=(select job from emp where name='刘沛霞'); -- 48.列出薪资比'大数据部'部门(已知部门编号为30)所有员工薪资都高的员工信息,显示员工姓名、薪资和部门名称。 -- 关联查询部门表和员工表(外连接) select emp.name,sal,dept.name from emp left join dept on emp.dept_id=dept.id; -- 求出'大数据部'部门的最高薪资 select max(sal) from emp where dept_id=30; -- 求出比大数据部门最高薪资还高的员工 select emp.name,sal,dept.name from emp left join dept on emp.dept_id=dept.id where sal > ( select max(sal) from emp where dept_id=30 ); -- 49.列出在'培优部'任职的员工,假定不知道'培优部'的部门编号,显示部门名称,员工名称。 -- 关联查询两张表 select dept.name,emp.name from dept,emp where dept.id=emp.dept_id; -- 求出在培优部的员工 select dept.name,emp.name from dept,emp where dept.id=emp.dept_id and dept.name='培优部'; -- 50.(自查询)列出所有员工及其直接上级,显示员工姓名、上级编号,上级姓名 /* emp e1 员工表 emp e2 上级表 显示的列: e1.name, e2.id, e2.name 查询的表: emp e1, emp e2 关联条件: e1.topid=e2.id */ select e1.name, e2.id, e2.name from emp e1, emp e2 where e1.topid=e2.id; -- 51.列出最低薪资大于1500的各种职位,显示职位和该职位最低薪资 -- 根据职位进行分组,求出每种职位的最低薪资 select job,min(sal) from emp group by job; -- 求最低薪资大于1500的职位有哪些 select job,min(sal) from emp group by job having min(sal)>1500; -- 52.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量,平均薪资。 select dept_id 部门编号,count(*) 部门人数,avg(sal) 平均薪资 from emp group by dept_id; -- 53.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。 -- 关联查询两张表(dept, emp) select * from dept d, emp e where d.id=e.dept_id; -- 替换要显示的列和统计部门人数 select d.id, d.name, d.loc, count(*) from dept d, emp e where d.id=e.dept_id group by d.name; -- 54.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。 /* emp e1 员工表 emp e2 上级表 dept d 部门表 要显示的列: e1.id, e1.name, d.name 要查询的表: emp e1, emp e2, dept d 关联条件: e1.topid=e2.id e1.dept_id=d.id e1.hdate<e2.hdate */ select e1.id, e1.name, d.name from emp e1, emp e2, dept d where e1.topid=e2.id and e1.dept_id=d.id and e1.hdate<e2.hdate; -- 55.列出每个部门薪资最高的员工信息,显示部门编号、员工姓名、薪资 -- 求出每个部门薪资最高的员工信息 select dept_id,max(sal) from emp group by dept_id; -- 将上面查询的结果作为一张临时表 和 emp表进行关联 select e.dept_id,e.name,t.maxsal from emp e,(select dept_id,max(sal) maxsal from emp group by dept_id) t where e.sal=t.maxsal and e.dept_id=t.dept_id union select e.dept_id,e.name,max(sal) from emp e where dept_id is null; ====================================== 数据库复习 ====================================== 一、数据库概述 什么数据库 关系型数据库 常见的关系型数据库 数据库相关概念 数据库服务器 数据库 表 表记录 二、数据库及表的操作 创建数据库 create database if not exists mydb1 charset utf8; 删除数据库 drop database if exists 库名; 进入数据库 use 库名; 创建表 use mydb1; create table stu( id int primary key auto_increment, name varchar(50), gender char(1), birthday date, score double ) ; 删除表 drop table if exists stu; 三、表记录的增删改操作 新增表记录 insert into stu values(null,'张三','男','1987-1-1',67); 修改表记录 update stu set score=87 where name='张三'; 删除表记录 delete from stu where name='张三'; delete from stu; truncate table stu; 四、表记录的查询操作 基础查询 select * from emp; where子句查询 select * from emp where sal>3000; 模糊查询 select * from emp where name like '刘%'; select * from emp where name like '%涛%'; select * from emp where name like '刘_'; 分组查询 select * from emp group by dept; select * from emp group by job; 聚合函数(多行函数)--不能用在where子句中 select count(*) from emp; select count(*) from emp group by dept; select max(sal) from emp; select min(sal) from emp; select sum(sal) from emp; select avg(sal) from emp; year(date) month(date) day(date) curdate() curtime() sysdate() 排序查询 select * from emp order by sal asc; select * from emp order by sal desc; 分页查询 每页显示4条,查询第1页: select * from emp limit 0,4; select * from emp limit 4,4; select * from emp limit 8,4; select * from emp limit 12,4; select * from emp limit 16,4; 五、外键及表关系 什么是外键: 用于表示数据库中表和表之间关系的一个键(列),可以通知数据库两张表之间存在关系,并且让数据库帮我们维护这种关系. 如何指定外键: emp dept_id -> dept(id) foreign key(dept_id) references dept(id) 一对多(多对一): 在多的一方添加列,保存一的一方的主键 一对一: 在任意一方添加列保存另一方的主键 多对多: 在一张第三方的表中添加列,分别保存两张表的主键 ====================================== 补充1、笛卡尔积查询: 笛卡尔积查询:如果同时查询两张表,左边表有m条数据,右边表有n条数据,那么笛卡尔积查询是结果就是 m*n 条记录。这就是笛卡尔积查询。例如: select * from dept,emp; 上面的查询中包含大量错误的数据, 一般不使用这种查询。 如果只想保留正确的记录,可以通过where条件进行筛选,将符合条件的保留下来,不符合条件的自然就会被剔除,例如: select * from dept,emp where dept.id=emp.dept_id; 补充2、左外连接和右外连接查询: (1) 左外连接查询:是将左边表中所有数据都查询出来, 如果在右边表中没有对应的记录, 右边表显示为null即可。 (2) 右外连接查询:是将右边表中所有数据都查询出来, 如果在左边表中没有对应的记录, 左边表显示为null即可。 补充3、where和having都用于筛选过滤,但是: (1) where用于在分组之前进行筛选, having用于在分组之后进行筛选 (2) 并且where中不能使用列别名, having中可以使用别名 (3) where子句中不能使用列别名(可以使用表别名), 因为where子句比select先执行!! 补充4、SQL语句的书写顺序和执行顺序: SQL语句的书写顺序: select... from... where... group by... order by... ... SQL语句的执行顺序: from... -- 确定要查询的是哪张表 (定义表别名) where... -- 从整张表的数据中进行筛选过滤 select... -- 确定要显示哪些列 (定义列别名) group by... -- 根据指定的列进行分组 order by... -- 根据指定的列进行排序 ... ======================================

2.oracle

--学生表 create table student( sno varchar2(10) primary key, sname varchar2(20), sage number(2), ssex varchar2(5) ); --教师表 create table teacher( tno varchar2(10) primary key, tname varchar2(20) ); --课程表 create table course( cno varchar2(10), cname varchar2(20), tno varchar2(20), constraint pk_course primary key (cno,tno) ); --成绩表 create table sc( sno varchar2(10), cno varchar2(10), score number(4,2), constraint pk_sc primary key (sno,cno) ); insert into student values ('s001','张三',23,'男'); insert into student values ('s002','李四',23,'男'); insert into student values ('s003','吴鹏',25,'男'); insert into student values ('s004','琴沁',20,'女'); insert into student values ('s005','王丽',20,'女'); insert into student values ('s006','李波',21,'男'); insert into student values ('s007','刘玉',21,'男'); insert into student values ('s008','萧蓉',21,'女'); insert into student values ('s009','陈萧晓',23,'女'); insert into student values ('s010','陈美',22,'女'); commit; insert into teacher values ('t001', '刘阳'); insert into teacher values ('t002', '谌燕'); insert into teacher values ('t003', '胡明星'); commit; insert into course values ('c001','J2SE','t002'); insert into course values ('c002','Java Web','t002'); insert into course values ('c003','SSH','t001'); insert into course values ('c004','Oracle','t001'); insert into course values ('c005','SQL SERVER 2005','t003'); insert into course values ('c006','C#','t003'); insert into course values ('c007','JavaScript','t002'); insert into course values ('c008','DIV+CSS','t001'); insert into course values ('c009','PHP','t003'); insert into course values ('c010','EJB3.0','t002'); commit; insert into sc values ('s001','c001',78.9); insert into sc values ('s002','c001',80.9); insert into sc values ('s003','c001',81.9); insert into sc values ('s004','c001',60.9); insert into sc values ('s001','c002',82.9); insert into sc values ('s002','c002',72.9); insert into sc values ('s003','c002',81.9); insert into sc values ('s001','c003','59'); commit; select * from student; select * from sc; select * from course; select * from teacher; --1、查询“c001”课程比“c002”课程成绩高的所有学生的学号; select a.* from (select * from sc a where a.cno='c001')a,(select * from sc b where b.cno='c002')b where a.sno=b.sno and a.score>b.score; select * from sc a where a.cno='c001' and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno); --2、查询平均成绩大于60 分的同学的学号和平均成绩; select sno,avg(score) from sc group by sno having avg(score)>60; --3、查询所有同学的学号、姓名、选课数、总成绩; select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno; --4、查询姓“刘”的老师的个数; select count(*) from teacher where tname like'刘%'; --5、查询没学过“谌燕”老师课的同学的学号、姓名; select * from student st where st.sno not in (select distinct sno from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno where tname='谌燕'); --6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名; select st.* from sc a join sc b on a.sno=b.sno join student st on st.sno=a.sno where a.cno='c001' and b.cno='c002' and st.sno=a.sno; --7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名; select st.* from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno join teacher t on c.tno=t.tno where t.tname='谌燕'; --8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名; select * from student st join sc a on st.sno=a.sno join sc b on st.sno=b.sno where a.cno='c002' and b.cno='c001' and a.score<b.score; --9、查询所有课程成绩小于60 分的同学的学号、姓名; select st.*,s.score from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno where s.score<60; --10、查询没有学全所有课的同学的学号、姓名; select * from student where sno in (select sno from(select stu.sno,c.cno from student stu cross join course c minus select sno,cno from sc)); --11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名; select st.* from student st,(select distinct a.sno from(select * from sc)a,(select * from sc where sc.sno='s001')b where a.cno=b.cno)h where st.sno=h.sno and st.sno<>'s001'; --12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名; select * from sc left join student st on st.sno=sc.sno where sc.sno<>'s001' and sc.cno in (select cno from sc where sno='s001'); --13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩; update sc c set score=(select avg(c.score) from course a,teacher b where a.tno=b.tno and b.tname='谌燕' and a.cno=c.cno group by c.cno)where cno in(select cno from course a,teacher b where a.tno=b.tno and b.tname='谌燕'); --14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名; select * from sc where sno<>'s001' minus (select * from sc minus select * from sc where sno='s001'); --15、删除学习“谌燕”老师课的SC 表记录; delete from sc where sc.cno in (select cno from course c left join teacher t on c.tno=t.tno where t.tname='谌燕'); --16、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩; insert into sc(sno,cno,score) select distinct st.sno,sc.cno,(select avg(score) from sc where cno='c002') from student st,sc where not exists(select * from sc where cno='c002' and sc.sno=st.sno)and sc.cno='c002'; --17、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分 select cno,max(score),min(score) from sc group by cno; --18、按各科平均成绩从低到高和及格率的百分数从高到低顺序 select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率 from sc group by cno order by avg(score) --19、查询不同老师所教不同课程平均分从高到低显示 select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course c,teacher t where sc.cno=c.cno and c.tno=t.tno group by c.cno order by avg(score) desc; --20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] select sc.cno,c.cname, sum(case when score between 85 and 100 then 1 else 0 end) AS "[100-85]", sum(case when score between 70 and 85 then 1 else 0 end) AS "[85-70]", sum(case when score between 60 and 70 then 1 else 0 end) AS "[70-60]", sum(case when score <60 then 1 else 0 end) AS "[<60]" from sc,course c where sc.cno=c.cno group by sc.cno ,c.cname; --21、查询各科成绩前三名的记录:(不考虑成绩并列情况) select * from (select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc) where rn<4; --22、查询每门课程被选修的学生数 select cno,count(sno)from sc group by cno; --23、查询出只选修了一门课程的全部学生的学号和姓名 select sc.sno,st.sname,count(cno) from student st left join sc on sc.sno=st.sno group by st.sname,sc.sno having count(cno)=1; --24、查询男生、女生人数 select ssex,count(*) from student group by ssex; --25、查询姓“张”的学生名单 select * from student where sname like '张%'; --26、查询同名同性学生名单,并统计同名人数 select sname,count(*) from student group by sname having count(*)>1; --27、1981 年出生的学生名单(注:Student 表中Sage 列的类型是number) select sno,sname,sage,ssex from student t where to_char(sysdate,'yyyy')-sage=1988; --28、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 select cno,avg(score) from sc group by cno order by avg(score)asc,cno desc; --29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩 select st.sno,st.sname,avg(score) from student st left join sc on sc.sno=st.sno group by st.sno,st.sname having avg(score)>85; --30、查询课程名称为“数据库”,且分数低于60 的学生姓名和分数 select sname,score from student st,sc,course c where st.sno=sc.sno and sc.cno=c.cno and c.cname='Oracle' and sc.score<60; --31、查询所有学生的选课情况; select st.sno,st.sname,c.cname from student st,sc,course c where sc.sno=st.sno and sc.cno=c.cno; --32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数; select st.sname,c.cname,sc.score from student st,sc,course c where sc.sno=st.sno and sc.cno=c.cno and sc.score>70; --33、查询不及格的课程,并按课程号从大到小排列 select sc.sno,c.cname,sc.score from sc,course c where sc.cno=c.cno and sc.score<60 order by sc.cno desc; --34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名; select st.sno,st.sname,sc.score from sc,student st where sc.sno=st.sno and cno='c001' and score>80; --35、求选了课程的学生人数 select count(distinct sno) from sc; --36、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩 select st.sname,score from student st,sc ,course c,teacher t where st.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕' and sc.score= (select max(score)from sc where sc.cno=c.cno); --37、查询各个课程及相应的选修人数 select cno,count(sno) from sc group by cno; --38、查询不同课程成绩相同的学生的学号、课程号、学生成绩 select a.* from sc a,sc b where a.score=b.score and a.cno<>b.cno; --39、查询每门功课成绩最好的前两名 select * from ( select sno,cno,score,row_number()over(partition by cno order by score desc) my_rn from sc t )where my_rn<=2; --40、统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 select cno,count(sno) from sc group by cno having count(sno)>10 order by count(sno) desc,cno asc; --41、检索至少选修两门课程的学生学号 select sno from sc group by sno having count(cno)>1; --42、查询全部学生都选修的课程的课程号和课程名 select cno,cname from course c where c.cno in (select cno from sc group by cno); --43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名 select st.sname from student st where st.sno not in (select distinct sc.sno from sc,course c,teacher t where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕'); --44、查询两门以上不及格课程的同学的学号及其平均成绩 select sno,avg(score)from sc where sno in (select sno from sc where sc.score<60 group by sno having count(sno)>1 ) group by sno; --45、检索“c004”课程分数小于60,按分数降序排列的同学学号 select sno from sc where cno='c004' and score<90 order by score desc; --46、删除“s002”同学的“c001”课程的成绩 delete from sc where sno='s002' and cno='c001'; select * from sc;
最新回复(0)