CREATE TABLE dept ( deptno INT COMMENT "部门编号", NAME VARCHAR (20) COMMENT "部门名称", loc VARCHAR (40) COMMENT "所在城市") charset = utf8;INSERT INTO deptVALUES (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,avgselect avg(comm) from employee;-- 3,sumselect sum(sal) from employee;-- 4,maxselect max(sal) from employee;-- 5,minselect 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，我一定要显示nameselect 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,HAVINGselect 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;-- 只能用whereselect name from employee where sal>2000;-- 只能用havingselect 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.4unionselect name,age from studentUNIONselect 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) employeeon d.deptno=employee.dno;