mysql case when 子查询_mysql 综合查询练习（关系查询、子查询）

建表语句见文末

emp

salarygrade

1、找到名字为两位的员工信息

-- 方法一

select * from emp
where length(ename) = 6; #一个汉字的长度为3

-- 方法二

select * from emp
where ename like '__';  -- 两根下划线

2、查询工资多于宋江的员工信息 (子查询)

select * from emp
where salary > (select salary from emp where ename = '宋江');

3、查询销售部门的员工姓名

-- 法一：join

select dname , ename from 
emp left join dept on emp.dept_id = dept.id 
where dname = '销售部';

-- 法二：关系查询

select ename , dname
from emp , dept
where emp.dept_id = dept.id and dname = '销售部';  -- 关系连接 ,结果和内连接一样

-- 法三：子查询

select ename from emp
where dept_id = (select id from dept where dname = '销售部');  -- 子查询没法将部门名称放上去

4、返回工资级别处于第4的员工姓名及工资(子查询作为虚拟表) ***

select ename , salary from emp ,(select losalary , hisalary from salarygrade where grade = 4) as a
where  salary between losalary and hisalary;

5、-- 查询所有员工对应的工资等级 ***

6、查询选修编号为“3-105“课程且成绩至少高于课程选修编号为“3-245”的记录,并按Degree从高到低降序排序。

select * from score
where cno = '3-105' and degree >any (select degree from score where cno = '3-245')
order by degree desc;

7、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的记录

select * from score
where cno = '3-105' and degree >all (select degree from score where cno = '3-245')
order by degree desc;

8、查询至少有2名男生的班号 ****

select * from student;
select class , ssex , count(sno) from student
where ssex = '男'
group by class having count(sno) >= 2;

9、查询所有“女”教师和“女”同学的name、sex和birthday

(select tname , tsex , tbirthday from teacher where tsex = '女')
union
(select sname , ssex , sbirthday from student where ssex = '女');

-----------------------------------------建表语句---------------------------------------------

1、emp表

-- 创建员工表emp
create table emp (
    id int primary key,
    ename varchar(50),
    job_id int,
    mgr int,
    joindate date,
    salary decimaL(7 , 2 ),
    bonus decimal(7 , 2 ),
    dept_id int
    );

#添加员工
insert into emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) 
values      (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
            (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
            (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
            (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
            (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
            (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
            (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
            (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
            (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
            (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
            (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
            (1012,'白骨精',4,1006,'2001-12-03','9500.00',NULL,30),
            (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
            (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

select * from emp;

salarygrade表

-- 创建薪资等级表
create table salarygrade (
              grade int primary key,
              losalary int,  
              hisalary int
            );
 
#添加5个工资等级
insert into salarygrade(grade,losalary,hisalary) 
values  (1,7000,12000),
		(2,12010,14000),
		(3,14010,20000),
		(4,20010,30000),
		(5,30010,99990);
            
select * from salarygrade;

2、student 表 、score表、course表、teacher表

/*-------------------建表-------------------------*/
CREATE TABLE STUDENT  
(  
 SNO       VARCHAR(3) NOT NULL,   
 SNAME     VARCHAR(4) NOT NULL,  
 SSEX      VARCHAR(2) NOT NULL,   
 SBIRTHDAY DATETIME,  
 CLASS     VARCHAR(5)  
 );

CREATE TABLE COURSE  
(  
 CNO   VARCHAR(5)  NOT NULL,   
 CNAME VARCHAR(10) NOT NULL,   
 TNO   VARCHAR(3) NOT NULL  
 );

CREATE TABLE SCORE   
(  
 SNO    VARCHAR(3)     NOT NULL,   
 CNO    VARCHAR(5)     NOT NULL,   
 DEGREE NUMERIC(10, 1) NOT NULL  
 ) ; 

CREATE TABLE TEACHER   
(  
 TNO       VARCHAR(3)  NOT NULL,   
 TNAME     VARCHAR(4)  NOT NULL,   
 TSEX      VARCHAR(2)  NOT NULL,   
 TBIRTHDAY DATETIME    NOT NULL,   
 PROF      VARCHAR(6),   
 DEPART    VARCHAR(10) NOT NULL  
 );
 
 /*---------------添加主键--------------*/
ALTER TABLE STUDENT ADD PRIMARY KEY (SNO); 
ALTER TABLE SCORE   ADD PRIMARY KEY (SNO,CNO);  
ALTER TABLE COURSE  ADD PRIMARY KEY (CNO);  
ALTER TABLE TEACHER ADD PRIMARY KEY (TNO);


/*---------------输入记录-------------------*/
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)   
 VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033),  
        (105 ,'匡明' ,'男' ,'1975-10-02',95031),   
        (107 ,'王丽' ,'女' ,'1976-01-23',95033),  
        (101 ,'李军' ,'男' ,'1976-02-20',95033),    
        (109 ,'王芳' ,'女' ,'1975-02-10',95031),  
        (103 ,'陆君' ,'男' ,'1974-06-03',95031);
        
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)   
 VALUES (804,'李诚','男','1958-12-02','副教授','计算机系'),  
        (856,'张旭','男','1969-03-12','讲师','电子工程系'),  
        (825,'王萍','女','1972-05-05','助教','计算机系'),   
        (831,'刘冰','女','1977-08-14','助教','电子工程系');
        
        
INSERT INTO COURSE(CNO,CNAME,TNO)
       VALUES ('3-105' ,'计算机导论',825),  
              ('3-245' ,'操作系统' ,804), 
              ('6-166' ,'数据电路' ,856),  
              ('9-888' ,'高等数学' ,831);
              
INSERT INTO SCORE(SNO,CNO,DEGREE)
        VALUES (103,'3-245',86),(105,'3-245',75), 
               (109,'3-245',68),(103,'3-105',92),  
               (105,'3-105',88),(109,'3-105',76), 
               (101,'3-105',64),(107,'3-105',91),  
               (108,'3-105',78),(101,'6-166',85),  
               (107,'6-166',79),(108,'6-166',81); 


select * from student;
select * from course;
select * from teacher;
select * from score;