CREATE TABLE dept (
deptno INT COMMENT "部门编号",
NAME VARCHAR (20) COMMENT "部门名称",
loc VARCHAR (40) COMMENT "所在城市"
) charset = utf8;
INSERT INTO dept
VALUES
(10, "accounting", "new_york")(20, "research", "dalas")(30, "sales", "beijing")(40, "financial", "shanghai");
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SId varchar(10),CId varchar(10),score float);
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
select name,job,mgr,hiredate from employee;
select job from employee;
select distinct job from employee;
select name as "姓名",sal*12 as "薪水" from employee;
select name from employee where job="clerk";
select name,job from employee where job="clerk" and sal>900;
select name,job from employee where job="clerk" or job="salesman";
select name,job from employee where job!="clerk";
select name,sal from employee where sal BETWEEN 1000 and 2000;
select name,sal from employee where sal not BETWEEN 1000 and 2000;
select name,comm from employee where comm is not null;
select name,job from employee where job in ("clerk","salesman");
select name from employee where name like "A%";
select name from employee where name like "%A";
select name from employee where name like "%A%";
select name from employee where name like "_A%";
select name from employee where name NOT like "%A%";
-- 排序
select name,sal from employee order by sal asc;
select name,sal from employee order by sal desc;
select name,sal from employee order by sal asc,hiredate desc;
select * from employee where comm is null limit 2;
select * from employee where comm is null limit 2,2;
select * from employee where comm is null ;
select count(*) from employee;
select count(comm) from employee;
select count(*) from employee where comm is not null;
-- 2,avg
select avg(comm) from employee;
-- 3,sum
select sum(sal) from employee;
-- 4,max
select max(sal) from employee;
-- 5,min
select min(sal) from employee;
-- 6,如果为Null,注意返回值
select count(sal),avg(sal),sum(sal),min(sal),max(sal) from employee where sal is null;
-- 1.5.7分组记录查询
-- 1，简单分组
select job,avg(sal) from employee group by job;
select job,group_concat(name) "所含员工",avg(sal) from employee group by job;
-- 2，我一定要显示name
select deptno,group_concat(name,":",sal) 姓名 from employee group by deptno;
select deptno,group_concat(name) names,count(name) 计数 from employee group by deptno;
-- 3,实现多个字段分组查询
select deptno,hiredate,group_concat(name) names,count(name) 计数 from employee group by deptno,hiredate;
-- 4,HAVING
select job,avg(sal) avg_sal from employee group by job HAVING avg_sal>2000;
-- 1.6having子句
-- where having 都可以用
select name,sal from employee where sal>2000;
select name,sal from employee having sal>2000;
-- 只能用where
select name from employee where sal>2000;
-- 只能用having
select deptno,avg(sal) as ag from employee group by deptno having ag>2000;
-- 1.7去重
select distinct deptno from employee;
-- 2.多表查询
-- 两种方式指明外键：
--
--
-- creat TABLE employee (
-- ,,, CONSTRAINT "fk" FOREIGN KEY ("deptno") REFERENCES dept ("deptno") ON DELETE
-- SET NULL ON UPDATE
-- SET NULL
-- ) charset = utf8mb4;
--
--
--
-- ALTER TABLE employee ADD CONSTRAINT "fkdd" FOREIGN KEY ("deptno") REFERENCES dept ("deptno") ON DELETE
-- SET NULL ON UPDATE
-- SET NULL
-- ) charset = utf8mb4;
--
-- 2.4union
select name,age from student
UNION
select age,name from teacher;
--
-- 2.5笛卡尔积
select * from dept,employee;
-- 2.6表连接
--
-- 2.6内连接
select * from employee f,employee j WHERE f.mgr=j.no;
select * from employee f join employee j on f.mgr=j.no;
SELECT * from employee as f join employee as j on f.mar=j.no;
-- 三张表连接
select e.no,e.name,e.sal,e.job,l.name leader,d.name,d.loc from employee e
inner join employee l on e.mgr=l.no
inner join dept d on l.deptno=d.deptno;
-- 外连接
select * from employee left join dept using(deptno);
select * from employee f right outer join employee j on f.mgr=j.no;
select * from employee right join dept using(deptno);
-- 2.10子查询
-- 2.10.2返回单行单列和单行多列子查询
select sal from employee where name="smith";
select * from employee where sal>(select sal from employee where name="smith");
select sal,job from employee where name="smith";
select name,sal,job from employee where (sal,job)=(select sal,job from employee where name="smith");
-- 2.10.3返回多行单列子查询
-- 1.带in关键字
select deptno from dept;
select * from employee where deptno in (select deptno from dept);
select * from employee where deptno not in (select deptno from dept);
-- 2，带any关键字
select sal from employee where job="manager";
select name,sal from employee where sal>any(select sal from employee where job="manager");
-- 3，all关键字
select name,sal from employee where sal>all(select sal from employee where job="manager");
-- 4，exists关键字
--
select * from dept where exists(select * from employee where deptno=dept.deptno);
-- 2.4.10返回多行多列子查询
-- 1.通过连接
select d.deptno,d.name,d.loc,count(e.no) number,avg(e.sal) average from employee e
inner join dept d on e.deptno=d.deptno group by d.deptno desc,d.name,d.loc;
-- 2，通过子查询
select d.deptno,d.name,d.loc,number,average
from dept d
inner join(select deptno dno,count(no) number,avg(sal) average from employee group by deptno desc) employee
on d.deptno=employee.dno;
