SQL-多表

 

create table course (
    id int auto_increment primary key comment '主键id',
    name varchar(10) comment '课程名字'
);
insert into course (name) values ('java'),('linux'),('SQL');

create table course_student (
    id int auto_increment primary key comment '主键id',
    studentid int not null comment '学生id',
    courseid int not null comment '课程id',
    constraint ky_cs_student foreign key(studentid) references student(id),
    constraint ky_cs_course foreign key(courseid) references course(id)
);
insert into course_student values (null,1,1),(null,2,1),(null,2,2),(null,1,2);

alter table user add userid int unique auto_increment primary key comment '用户id';

 

 

 select u.name,k.name from user u,kept k where u.kept_id = k.ky_id;  -- 使用多表连接可以使用别名
select kept.name,user.name from kept inner join user on kept.ky_id = user.kept_id;-- 显示连接比隐式连接扫描快

 

-- 查询员工所有信息包括部门(左外和右外区别是想显示哪个表的所有信息（null))
select u.*,k.name from user u left join kept k on k.ky_id = u.kept_id;
select u.*,k.name from user u right join kept k on k.ky_id = u.kept_id;

 

all则显示重复字段，不加则显示不重复字段
select u.*,k.name from user u left join kept k on k.ky_id = u.kept_id
union
select u.*,k.name from user u right join kept k on k.ky_id = u.kept_id;

 

  

select * from user where entrydata > (select entrydata from user where name = '嘉');

 

select * from user where user.kept_id in (select ky_id from kept where name = '科研部' or name = '财务部');

(select user.soces from user where user.kept_id = (select ky_id from kept where name = '财务部'));

select * from user where soces > any(select user.soces from user where user.kept_id = (select ky_id from kept where name = '科研部'));

 

 行子查询返回的是多个列

-- 查询与陈相同分数和相同部门的员工信息
select * from user where (user.soces,user.kept_id) = (select user.soces,user.kept_id from user where name = '陈');

 

 查询与陈和颖分数和部门相同的员工信息

注意第 3，10，11，12

select u.name,u.age,u.soces,k.name from user u left join kept k on u.kept_id = k.ky_id;

select u.name,u.age,u.soces,k.name from user u left join kept k on u.kept_id = k.ky_id where age <= 22;

insert into kept (name) values ('销售部');
select distinct k.name,k.ky_id from kept k,user u where k.ky_id = u.kept_id;-- 去重字段istinct
select u.name,k.name from user u left join kept k on k.ky_id = u.kept_id where u.age >= 22;
select
       name,
       case when soces >= 90 then '优秀' when user.soces between 89 and 80 then '良好' when user.soces between 60 and 79 then '及格' else '不及格' end
from user;

select
       u.*,
       case when u.soces >= 90 then '优秀' when u.soces between 80 and 89 then '良好' when u.soces between 60 and 79 then '及格' else '不及格' end
from user u where u.kept_id = (select ky_id from kept where name = '科研部');

select avg(soces) from user where user.kept_id = (select ky_id from kept where name = '科研部');

select * from user where user.soces >= (select user.soces from user where name = '煎包');

select * from user where user.soces >= (select avg(user.soces) from user);
-- 查询本部门时应用循环
select * from user u1 where u1.soces < (select avg(u2.soces) from user u2 where u2.kept_id = u1.kept_id);

select k1.* ,(select count(*) from user where user.kept_id = k1.ky_id) '人数' from kept k1;

select distinct s.name,s.id,c.name from course_student cs,student s,course c where cs.studentid = s.id and cs.courseid = c.id;