1. 复习
库的操作
创建库：create database 库名 character set 编码表 collate 排序规则;
删除库：drop database 库名;
查询库：show databases;
查看库的编码表：show create database 库名;
更改库：use 库名;
查看当前正在使用的库：selectdatabase();
修改库的编码表：alter database 库名 character set 编码表 collate 排序规则;
表本身的操作
创建表：create table 表名( 列名 列的类型(长度) 类的约束 ,列名 列的类型(长度) 类的约束...... );
删除表：drop table 表名;
查询表：show tables;
查看表的结构：desc 表名;
查看表的编码表：show create table 表名;
修改表：alter table 表名 增/删/改 列名列的类型(长度) 约束;
add/drop/change/modify
修改表名：rename table 旧表名 to 新表名；
表中数据的操作
增：insert into 表名(列名) values(值);
删：delete from 表名 where 条件; truncate
改：update 表名 set 列名=值 ，列名=值 where 条件；
查：select 列名 as 别名 ,列名 as 别名。。。。 from 表名 where 条件；
查询排重：select distinct 列名 from 表名 where 条件;
聚集函数：
count 统计个数、sum求和、avg 平均值、max、min
在使用这几个函数进行数据的统计分析时，有时需要对数据表中的列进行数据的分组处理。group by
分组 group by :
排序：order by asc | desc；
2. 多表查询
需求：查询学生的考试成绩，一张学生表可以查询到所有的信息吗？
一个项目中肯定会有多张数据表，而数据表之间会存在各种各样的关系。这时我们需要的数据，可能不会直接通过一张表全部获取到，这时就需要同时查询多张数据表，得到最后想要的数据。
2.1. 笛卡尔积介绍
准备工作：
create table A(
A_ID int primary keyauto_increment,
A_NAME varchar(20) not null
);
insert into A values(1,'苹果');
insert into A values(2,'橘子');
insert into A values(3,'香蕉');
create table B(
A_ID int primary keyauto_increment,
B_PRICE double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);
笛卡尔积的数据，对程序是没有意义的，我们需要对笛卡尔积中的数据再次进行过滤。
对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。
2.2. 内连接查询
内连接查询的结果：两表的公共部分。
内连接：
语法一：
select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;
-- 需求：查询出每个水果的价格：
语法二：
====select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;
select *from 表名1 inner join 表名2 on 条件
需求：查询出每个水果的价格：
2.3. 外链接查询
外链接：左外连接、右外连接、全连接、自连接。
2.3.1. 左外链接：
左外连接：左表数据不动，右边表的数据往左表上去添加，不管是否找到，都将显示左边表中全部记录。
语法：select * from 表1 left outer join 表2 on 条件;
-- 需求：不管能否查到水果对应的价格，都要把水果显示出来。
2.3.2. 右外连接：
用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示。
即：不管左方能够找到右方价格对应的水果，都要把左方的价格显示出来。
语法：select * from 表1 right outer join 表2 on 条件;
-- 需求：不管能否查到价格对应的价格，都要把水果显示出来。
全连接：
全外连接：左外连接和右外连接的结果合并，单会去掉重复的记录。
select *from 表1 full outer join 表2 on 条件
select * from A ull outer join B on A.A_ID =B.A_ID; 但是mysql数据库不支持此语法。
在sql语句全连接，其实就是左外链接和右外连接之和，去掉重复的数据。这时可以使用union
-- 需求1：将左连接和右连接整合在一起显示出来。（使用union all）
-- 需求2：将左连接和右连接整合在一起 去除重复的 显示出来。（使用union）
2.4. SQL关联子查询
子查询：把一个sql的查询结果作为另外一个查询的参数存在。
经常用到临时表
数据库中A水果表，B价格表。
-- 需求1：查询价格最贵的水果名称
-- 需求1：查询价格最贵的水果名称
-- 1.1首先查询出最贵的价格 ///3.5
select MAX(b_price) from b
-- 1.2查询价格为3.5的水果的id //2
select a_id from b where b_price = (select MAX(b_price) from b)
-- 1.3根据id查询出水果的名称
select a_name from a
wherea_id=(select a_id from b
where b_price = (select MAX(b_price) fromb))
准备工作：
teacher 教师表
student 学生表
cource 课程表
studentcource 选课表 学生和课程的关系表
CREATE TABLE teacher (
id int(11) NOT NULL primarykey auto_increment,
name varchar(20) not nullunique
);
CREATE TABLE student (
id int(11) NOT NULL primarykey auto_increment,
name varchar(20) NOT NULLunique,
city varchar(40) NOT NULL,
age int
) ;
CREATE TABLE course(
id int(11) NOT NULL primarykey auto_increment,
name varchar(20) NOT NULLunique,
teacher_id int(11) NOT NULL,
FOREIGN KEY (teacher_id)REFERENCES teacher (id)
);
CREATE TABLE studentcourse (
student_id int NOT NULL,
course_id int NOT NULL,
score double NOT NULL,
FOREIGN KEY (student_id)REFERENCES student (id),
FOREIGN KEY (course_id)REFERENCES course (id)
);
insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');
insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','广州',20);
insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);
insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);
-- 需求1：查询获得最高分的学生信息：
-- 需求1：查询获得最高分的学生信息：
-- 1.1先获取最高分 95
select MAX(score) from studentcourse
-- 1.2 获取最高分学生的id
select student_id from studentcourse
where score = (selectMAX(score) from studentcourse)
-- 1.3根据学生的id找到学生的信息
select * from student
where id = (selectstudent_id from studentcourse
where score = (select MAX(score) from studentcourse))
优化：子查询变为关联查询：
SELECT * FROM student ,(SELECT MAX(score),student_idFROM studentcourse) stu WHERE student.id=stu.student_id
-- 需求2：查询编号2课程比编号1课程最高成绩高学生信息：
-- 需求2：查询编号2课程 比 编号1课程最高成绩 高 的学生信息：
-- 2.1首先查询出编号1课程的最高成绩
select MAX(score) from studentcourse wherecourse_id = 1
-- 2.2查询编号2课程的分数比80高的学生的id
select student_id from studentcourse
wherecourse_id = 2 and score>(select MAX(score) from studentcourse
where course_id = 1)
-- 2.3根据学生的id查询出学生的信息
select * from student
whereid in (selectstudent_id from studentcourse
where course_id = 2 and score>(selectMAX(score) from studentcourse
where course_id = 1))
SELECT * FROM student WHERE id=(SELECT s2.student_id FROM studentcourse s2
WHERE s2.score>(SELECT MAX(score) FROMstudentcourse
WHERE student_id=1))
-- 需求3：查询编号2课程比编号1课程最高成绩高学生姓名和成绩 （临时表）
-- 需求3：查询编号2课程 比 编号1课程最高成绩 高 的学生信息和成绩：
-- 3.1首先查询出编号1课程的最高成绩
select MAX(score) from studentcourse wherecourse_id = 1
-- 3.2查询编号2课程的分数比80高的学生的id
select student_id ,score from studentcourse
wherecourse_id = 2 and score>(select MAX(score) from studentcourse
where course_id = 1)
-- 3.3根据学生的id查询出学生的信息
select student.*,temp.score from student,(selectstudent_id,score from studentcourse
wherecourse_id = 2 and score>(select MAX(score) from studentcourse
where course_id = 1)) as temp where student.id = temp.student_id
注意：sql语句的结果可以作为临时表
2.4.1. in和exists的用法：
关联子查询其他的关键字使用：
回忆：age=23 or age=24 等价于 age in (23,24)
in 表示条件应该是在多个列值中。
in：使用在where后面，经常表示是一个列表中的数据，只要被查询的数据在这个列表中存在即可。
-- 需求：查询不及格的学生(使用in完成)
-- 需求：查询不及格的学生(使用in完成)
-- 1.1首先获取不及格学生的id
select student_id from studentcourse wherescore<60
-- 1.2根据id查询学生的信息
select * from student where id in (select student_id[A1] from studentcourse where score<60)
使用exists完成：
-- 需求：查询不及格的学生(使用exists完成)
select * from student where exists (select * fromstudentcourse where student.id=studentcourse.student_id and score<60)
使用注意事项：
如果外表是大表，内表是小表，那么使用in
如果外表是小表，内表是大表。那么使用exists
in的算法
Var outer 1000
Var inner 1002
For(var i=0;i<outer.length;i++){
For(var j=0;j<inner.length;j++){
If(outer[i]==inner[j])
}
}
Exists
For(var i=0;i<outer.length;i++){
If(exists())
}
sql优化：
1.在开发中，尽量的手写sql。
2.我们需要什么，就查询什么。
3.in和exists的用法。
4.Index的用法。
如果说表中的列经常被用作查询的条件来查询的时候，这时候可以考虑使用索引。
如果说该列中的值的重复率非常的低，值特别多。给当前列添加索引。
如果列中的值重复率特别高，值特别少，那么不要添加索引。
5.当有多张表联合查询的时候。我们一般首先考虑两两查询。
6.优先运行where过滤数据多的语句。
A,b,c 100
Select * from a,b,c where .......
9000 100
(Select * form a,b where ...) ,c
10 100
(select * from a,c where...),b
2.4.2. all、any和some的使用法
any 和 some用法一致：
any和some是没有区别的，some和any 效果一样，代表一部分记录。
any部分数据
问题1：>any(1,2,3)
问题2：<any(1,2,3) 等价于 <1 or <2 or<3 等价与 <3 等价于 <max(1,2,3) or
all 所有数据
问题3:>all(1,2,3) 等价与>1 and >2 and >3 等价与>3 等价与 >max(1,2,3)
问题4：<all(1,2,3) 等价于<1 and <2 and <3 等价于 <1 等价与<min(1,2,3)
Some,all,any ( 1,2)
2.5. mysql的自带函数
msyql数据库中自带了一些函数。在我们使用sql语句操作mysql数据库的时候，可以直接使用这些函数。
3. SQL强化练习
3.1. 学生选课信息查询
teacher 教师表
student 学生表
cource 课程表
studentcource 选课表 学生和课程的关系表
准备工作：
CREATE TABLE teacher (
id int(11) NOT NULL primarykey auto_increment,
name varchar(20) not nullunique
);
CREATE TABLE student (
id int(11) NOT NULL primarykey auto_increment,
name varchar(20) NOT NULLunique,
city varchar(40) NOT NULL,
age int
) ;
CREATE TABLE course(
id int(11) NOT NULL primarykey auto_increment,
name varchar(20) NOT NULLunique,
teacher_id int(11) NOT NULL,
FOREIGN KEY (teacher_id)REFERENCES teacher (id)
);
CREATE TABLE studentcourse (
student_id int NOT NULL,
course_id int NOT NULL,
score double NOT NULL,
FOREIGN KEY (student_id)REFERENCES student (id),
FOREIGN KEY (course_id)REFERENCES course (id)
);
insert into teacher values(null,'关羽');
insert into teacher values(null,'张飞');
insert into teacher values(null,'赵云');
insert into student values(null,'小王','北京',20);
insert into student values(null,'小李','上海',18);
insert into student values(null,'小周','北京',22);
insert into student values(null,'小刘','北京',21);
insert into student values(null,'小张','上海',22);
insert into student values(null,'小赵','北京',17);
insert into student values(null,'小蒋','上海',23);
insert into student values(null,'小韩','北京',25);
insert into student values(null,'小魏','上海',18);
insert into student values(null,'小明','广州',20);
insert into course values(null,'语文',1);
insert into course values(null,'数学',1);
insert into course values(null,'生物',2);
insert into course values(null,'化学',2);
insert into course values(null,'物理',2);
insert into course values(null,'英语',3);
insert into studentcourse values(1,1,80);
insert into studentcourse values(1,2,90);
insert into studentcourse values(1,3,85);
insert into studentcourse values(1,4,78);
insert into studentcourse values(2,2,53);
insert into studentcourse values(2,3,77);
insert into studentcourse values(2,5,80);
insert into studentcourse values(3,1,71);
insert into studentcourse values(3,2,70);
insert into studentcourse values(3,4,80);
insert into studentcourse values(3,5,65);
insert into studentcourse values(3,6,75);
insert into studentcourse values(4,2,90);
insert into studentcourse values(4,3,80);
insert into studentcourse values(4,4,70);
insert into studentcourse values(4,6,95);
insert into studentcourse values(5,1,60);
insert into studentcourse values(5,2,70);
insert into studentcourse values(5,5,80);
insert into studentcourse values(5,6,69);
insert into studentcourse values(6,1,76);
insert into studentcourse values(6,2,88);
insert into studentcourse values(6,3,87);
insert into studentcourse values(7,4,80);
insert into studentcourse values(8,2,71);
insert into studentcourse values(8,3,58);
insert into studentcourse values(8,5,68);
insert into studentcourse values(9,2,88);
insert into studentcourse values(10,1,77);
insert into studentcourse values(10,2,76);
insert into studentcourse values(10,3,80);
insert into studentcourse values(10,4,85);
insert into studentcourse values(10,5,83);
根据上述的4张表练习：
1、查询平均成绩大于70分的同学的学号和平均成绩
2、查询所有同学的学号、姓名、选课数、总成绩
3、查询学过赵云老师所教课的同学的学号、姓名
4、查询没学过关羽老师课的同学的学号、姓名
5、查询没有学三门课以上的同学的学号、姓名
6、查询各科成绩最高和最低的分
7、查询学生信息和平均成绩
8、查询上海和北京学生数量
9、查询不及格的学生信息和课程信息
10、统计每门课程的学生选修人数（超过四人的进行统计）
-=====================
11、查询平均成绩大于70分的同学的学号和平均成绩
SELECT student_id,AVG(score) ag FROM studentcourse GROUP BYstudent_id HAVING ag>70
12、查询所有同学的学号、姓名、选课数、总成绩
13、查询学过赵云老师所教课的同学的学号、姓名
14、查询没学过关羽老师课的同学的学号、姓名
15、查询没有学三门课以上的同学的学号、姓名
16、查询各科成绩最高和最低的分
17、查询学生信息和平均成绩
18、查询上海和北京学生数量
19、查询不及格的学生信息和课程信息
20、统计每门课程的学生选修人数（超过四人的进行统计）
-- 1、查询平均成绩大于70分的同学的学号和平均成绩
-- 1、查询平均成绩大于70分的同学的学号和平均成绩
-- 1.1 分组
select student_id,avg(score) fromstudentcourse group by student_id having avg(score)>70
-- 2、查询所有同学的学号、姓名、选课数、总成绩
-- 2、查询所有同学的学号、姓名、选课数、总成绩
-- 2.1 分组，首先查询出学生的id,选课数，总成绩
select student_id,COUNT(*),SUM(score) fromstudentcourse group by student_id
-- 2.1 将2.1的结果作为临时表联合student表查询
-- 在sql语句中，不能直接通过聚合函数来代表表中的某列
select student.id,student.name,temp.cou,temp.sumsocre from student,
(selectstudent_id,COUNT(*) cou,SUM(score) sumsocre from studentcourse group bystudent_id ) as temp
wherestudent.id = temp.student_id
-- 3、查询学过赵云老师所教课的同学的学号、姓名
-- 3、查询学过赵云老师所教课的同学的学号、姓名
-- 3.1首先查询出赵云所教授课程的id
select id from teacher where name='赵云'
select id from course where teacher_id =(select id from teacher where name='赵云')
-- 3.2根据课程的id查询学生的id
select student_id from studentcourse
wherecourse_id in (select id from course
where teacher_id = (select id fromteacher
wherename='赵云'))
-- 3.3根据学生的id，查询出学生的信息
select * from student
whereid in (select student_id from studentcourse
where course_id in (select id from course
whereteacher_id = (select id from teacher
where name='赵云')) )
-- 4、查询没学过关羽老师课的同学的学号、姓名
-- 4、查询没学过关羽老师课的同学的学号、姓名
-- 4.1查询学过关羽老师课程的学生的id
select student_id from studentcourse
wherecourse_id in (select id from course
where teacher_id = (select id fromteacher
wherename='关羽'))
-- 4.2查询没有学过关羽老师课程的学生的学号和姓名
select * from student
whereid not in(select student_id from studentcourse
where course_id in (select id from course
where teacher_id = (select id fromteacher
wherename='关羽')))
-- 5、查询没有学三门课以上的同学的学号、姓名
-- 5、查询没有学三门课以上的同学的学号、姓名
-- 5.1查询没有学3门课以上的学生的学号
select student_id from studentcourse groupby student_id having COUNT(*)<3
-- 5.2根据学生的id查询学生的姓名和学号
select id ,name from student
whereid in (select student_id from studentcourse group by student_id havingCOUNT(*)<3)
-- 6、查询各科成绩最高和最低的分
-- 6、查询各科成绩最高和最低的分
-- 6.1分组
select course_id,MAX(score),MIN(score) fromstudentcourse group by course_id
-- 7、查询学生信息和平均成绩
-- 7、查询学生信息和平均成绩
-- 7.1按照学生的id分组，
select student_id,avg(score) fromstudentcourse group by student_id
-- 7.2将7.1的结果作为临时表联合student表查询
select student.*,FORMAT(temp.avgscore,2)from student,(select student_id,avg(score) as avgscore from studentcourse groupby student_id ) as temp
wherestudent.id = temp.student_id
-- 8、查询上海和北京学生数量
-- 8、查询上海和北京学生数量
-- 8.1 按照地区分组
select city,COUNT(*) from student wherecity in ('上海','北京') groupby city;
-- 9、查询不及格的学生信息和课程信息
-- 9、查询不及格的学生信息和课程信息
-- 9.1查询出不及格学生的id和课程的id
select course_id ,student_id fromstudentcourse where score<60
-- 9.2将9.1的结果作为临时表联合student表查询
select student.*,temp.course_id fromstudent, (select course_id ,student_id from studentcourse where score<60) astemp
wherestudent.id = temp.student_id
-- 9.3将9.2的结果作为临时表联合course表查询
select temp2.*,course.name from course ,
(selectstudent.*,temp.course_id from student, (select course_id ,student_id fromstudentcourse
wherescore<60) as temp
where student.id = temp.student_id) astemp2
wherecourse.id = temp2.course_id
-- 10、统计每门课程的学生选修人数（超过四人的进行统计）
-- 10、统计每门课程的学生选修人数（超过四人的进行统计）
select course_id,COUNT(*) fromstudentcourse group by course_id having COUNT(*)>4
-- 10.2关联course表联合查询
select course.name,temp.cou fromcourse,(select course_id,COUNT(*) cou from studentcourse group by course_idhaving COUNT(*)>4) as temp
wherecourse.id = temp.course_id
--11查询每位老师教课的最高成绩
3.2部门员工查询练习
准备工作：
-- 部门表
create table dept(
deptno int primary keyauto_increment, -- 部门编号
dname varchar(14) , -- 部门名字
loc varchar(13) -- 地址
) ;
-- 员工表
create table emp(
empno int primary keyauto_increment,-- 员工编号
ename varchar(10), -- 员工姓名 -
job varchar(9), -- 岗位
mgr int, -- 直接领导编号
hiredate date, -- 雇佣日期，入职日期
sal int, -- 薪水
comm int, -- 提成
deptno int not null, -- 部门编号
foreign key (deptno)references dept(deptno)
);
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭靖','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'令狐冲','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'张无忌','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'杨过','职员',7782,'1983-01-23',1300,null,10);
练习：
-- 1．列出至少有一个员工的所有部门。
-- 1．列出至少有一个员工的所有部门。
SELECT * FROM dept
SELECT * FROM emp
--列出大于一人的部门信息
SELECT deptno FROM emp GROUP BY deptno HAVINGCOUNT(1)>=1
SELECT deptno,COUNT(1) FROM emp GROUP BYdeptno HAVING COUNT(1)>=1
--根据部门id差部门信息
SELECT * FROM dept ,( SELECT deptno FROM emp GROUP BY deptno HAVINGCOUNT(1)>=1)AS temp
WHERE temp.deptno= dept.deptno
-- 2．列出薪金比"刘一"多的所有员工。
SELECT * FROMemp WHERE sal>(SELECT sal FROM emp WHERE ename='刘一')
-- 3．***** 列出所有员工的姓名及其直接上级的姓名。
--列出员工姓名和上级及对应的名字
SELECT temp1.ename,temp1.job,temp1.mgr,temp2.ename,temp2.jobFROM emp AS temp1,emp AS temp2
WHEREtemp1.mgr = temp2.empno
--左外连接查询
SELECT temp1.ename,temp1.job,temp1.mgr,temp2.ename,temp2.job FROM emp AS temp1 LEFT OUTER JOIN empAS temp2
ONtemp1.mgr = temp2.empno
-- 4．列出受雇日期早于其直接上级的所有员工。
SELECT temp1.ename,temp1.job,temp1.hiredate ,Temp2.ename,temp2.hiredate FROM emp AS temp1 LEFTOUTER JOIN emp AS TEMP2
ON TEMP1.HIREDATE>TEMP2.HIREDATE ANDTEMP1.MGR=TEMP2.EMPNO
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT * FROM emp
--左外连接
SELECT * FROMdept LEFT OUTER JOIN emp ON dept.deptno=emp.deptno
-- 6．列出所有job为“职员”的姓名及其部门名称。
SELECTemp.ename,emp.job,dept.dname FROM emp, deptWHERE emp.job='职员' ANDemp.deptno=dept.deptno
-- 7．列出最低薪金大于1500的各种工作。
SELECT job,salFROM emp WHERE sal>1500
-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
--模糊查询
SELECT *FROM dept WHERE dname LIKE '销售部'
SELECTename,dname FROM emp,(SELECT * FROM dept WHERE dname LIKE '销售部')AS temp
WHERE temp.deptno=emp.deptno
-- 9．列出薪金高于公司平均薪金的所有员工。
--求出平均工资
SELECTAVG(sal) ,COUNT(*) FROM emp
--求出工资大于平均工资的员工
SELECT*,av FROM emp ,(SELECT AVG(sal)av FROMemp)AS temp
HAVING sal > av
-- 10．列出与"周八"从事相同工作的所有员工。
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
-- 13．列出在每个部门工作的员工数量、平均工资。
-- 14．列出所有员工的姓名、部门名称和工资。
-- 15．列出所有部门的详细信息和部门人数。
-- 16．列出各种工作的最低工资。
-- 17．列出各个部门的 经理 的最低薪金。
-- 18．列出所有员工的年工资,按年薪从低到高排序。
-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
-- 20.查询出所有薪水在'陈二'之上的所有人员信息。
-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
-- 22.查询出emp表中所有的工作种类（无重复）
-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
-- 27.查询出名字第三个汉字是“忌”的所有员工信息。
-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。order by convert(name using gbk) asc;
-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
-- 32.显示出薪水最高人的职位。
-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
-- 34.删除10号部门薪水最高的员工。
-- 35.将薪水最高的员工的薪水降30%。
-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when ... then ... when ...then ... else ... end
-- 34.删除10号部门薪水最高的员工。
-- 获取10号部门薪水最高的员工
select max(sal) from emp
where deptno=10;
-- 找到10号部门薪水最高对应的员工id
select empno from emp
where deptno=10
and sal=(select max(sal)from emp
where deptno=10);
-- 注意: 执行删除或者修改时,子表不能和主表是同一个表
-- 解决方案: 将子查询作为临时表包裹一下
select temp.empno
from (select empno from emp
where deptno=10
and sal=(select max(sal) from emp
where deptno=10)) as temp;
-- 根据员工编号删除员工
delete from emp
where empno in (select temp.empno
from (select empno from emp
where deptno=10
and sal=(select max(sal) from emp
where deptno=10)) as temp);
-- 35.将薪水最高的员工的薪水降30%。
-- 找到最高薪水
select max(sal) from emp;
-- 找到最高薪水对应的员工编号
select empno from emp
where sal=(select max(sal) from emp);
-- 需要将子表使用临时表报过一下
-- 注意: 子表和主表不能是同一张表,执行更新时,删除和修改添加时
-- 35.将薪水最高的员工的薪水降30%。
-- 1.1查询最高的薪水
select MAX(sal) from emp
-- 1.2 查询最高薪水的员工的id
select empno from emp where sal = (select MAX(sal) from emp)
-- 1.3将id为7839的员工的薪水降30%
where empno in
( select temp.empno from(select empno from emp where sal = (select MAX(sal) from emp)) as temp )
-- 1．列出至少有一个员工的所有部门。
select dept.deptno,count(empno) from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno
having count(empno)>=1
;
-- 2．列出薪金比"刘一"多的所有员工。
-- 2.1 刘一的薪金
select sal from emp where ename='刘一';
-- 2.2 比刘一高的员工
select * from emp where sal>(select sal from emp where ename='刘一');
-- 3．***** 列出所有员工的姓名及其直接上级的姓名。
select e1.ename,e2.ename from emp as e1,emp as e2
where e1.mgr=e2.empno;
-- 4．列出受雇日期早于其直接上级的所有员工。
select e1.ename,e2.ename from emp as e1,emp as e2
where e1.mgr=e2.empno
and e1.hiredate<e2.hiredate;
;
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select dept.dname,emp.* from dept
left outer join emp
on dept.deptno=emp.deptno;
-- 6．列出所有job为“职员”的姓名及其部门名称。
select emp.ename,dept.dname
from emp,dept
where emp.deptno=dept.deptno
and emp.job='职员'
;
-- 7．列出最低薪金大于1500的各种工作。
select distinct job
from emp
where sal>1500;
-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
select emp.ename
from emp,dept
where emp.deptno=dept.deptno
and dept.dname='销售部';
-- 9．列出薪金高于公司平均薪金的所有员工。
select * from emp
where sal>(select avg(sal) from emp);
-- 10．列出与"周八"从事相同工作的所有员工。
-- 周八的工作
select job from emp where ename='周八';
select * from emp
where job = (select job from emp where ename='周八');
-- 方式二 使用exists
select * from emp e1
where exists (select * from emp e2 where e2.job=e1.job ande2.ename='周八' );
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select * from emp
where sal in (select sal from emp where deptno=30);
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select * from emp
where sal > all(select sal from emp where deptno=30);
-- 13．列出在每个部门工作的员工数量、平均工资。
select dept.deptno,count(emp.empno),avg(sal)
from dept
left outer join emp
on dept.deptno=emp.deptno
group by dept.deptno;
-- 14．列出所有员工的姓名、部门名称和工资。
select emp.ename, dept.dname,emp.sal
from emp, dept
where emp.deptno=dept.deptno;
-- 15．列出所有部门的详细信息和部门人数。
select deptno,count(empno) from emp
group by deptno;
select dept.*, ifnull(empCount,0) as 数量 from dept
left outer join (select deptno,count(empno) as empCount from emp
groupby deptno) as temp
on dept.deptno=temp.deptno;
-- 16．列出各种工作的最低工资。
select job,min(sal)
from emp
group by job;
-- 17．列出各个部门的 经理 的最低薪金。
select deptno, min(sal)
from emp
where job='经理'
group by deptno;
-- 18．列出所有员工的年工资,按年薪从低到高排序。
select emp.*,(sal+ifnull(comm,0))*12 nianxin from emp
order by nianxin asc
;
-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,ename,sal from emp
where sal>=3000;
-- 20.查询出所有薪水在'陈二'之上的所有人员信息。
select * from emp
where sal>(select sal from emp where ename='陈二');
-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，
-- 显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
select empno as 员工编号, ename as 员工名字, sal as 薪水
from emp
where deptno=20
and sal>2000;
-- 22.查询出emp表中所有的工作种类（无重复）
select distinct job from emp;
-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
select * from emp
where comm is not null;
-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
select * from emp
where sal >=800 and sal<=2500;
select * from emp
where sal between 800 and 2500;
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
select * from emp
where empno=7521 or empno=7900 or empno=7782;
select * from emp
where empno in (7521, 7900, 7782);
-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select * from emp
where ename like '%张%'
and sal>1000
;
-- 27.查询出名字第三个汉字是“忌”的所有员工信息。
select * from emp
where ename like '__忌';
-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select * from emp
order by sal asc, hiredate desc;
-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。
-- order by convert(name using gbk) asc;
select * from emp
order by convert(substring(ename,1,1) using gbk) asc, sal desc;
-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
select min(hiredate) from emp;
select * from emp
where hiredate = (select min(hiredate) from emp);
-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
select ename,sal,ifnull(comm,100)
from emp;
-- 32.显示出薪水最高人的职位。
select max(sal) from emp;
-- 方式一：
select job from emp
where sal = (select max(sal) from emp);
-- 方式二：
select job from emp
where sal >= all(select sal from emp);
-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
select deptno,max(sal) as maxSal,min(sal) as minSal from emp
group by deptno;
select dept.dname as 部门名称,maxSal 最高薪水,minSal 最低薪水 from dept
left outer join (select deptno,max(sal) as maxSal,min(sal) as minSalfrom emp
groupby deptno) temp
on dept.deptno=temp.deptno
where dept.deptno<>10;
-- 34.删除10号部门薪水最高的员工。
select empno from emp
where deptno=10 and sal>=all(select sal from emp wheredeptno=10);
delete from emp
where empno = (select empno from (select empno from emp
where deptno=10
andsal>=all(select sal from emp where deptno=10)) as temp);
-- 35.将薪水最高的员工的薪水降30%。
select empno from emp
where sal >= all(select sal from emp);
update emp
set sal=sal*(1-0.3)
where empno in (select temp.empno from (select empno from emp
where sal >= all(select sal from emp))temp);
select * from emp;
-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when... then ... when ... then ... else ... end
select ename,
case when sal>3000then '3级'
when sal>2000 then'2级'
else '1级'
end
from emp;
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
