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;
扫一扫
319
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
