精品000在线观看,亚洲熟妇欧美一区二区,色在线色色中出亚洲

?筆記：

一、聚合函數(shù)：計(jì)數(shù) 最大值? 最小值? 平均數(shù)? 求和

1.計(jì)數(shù) COUNT()? 忽略NULL值

方式1：COUNT(*)? *代表所有字段一般用于查詢表中共有多少條記錄（實(shí)體）

SELECT COUNT(*) zongshu FROM emp;

方式2：計(jì)算某個(gè)字段中非NULL值的個(gè)數(shù)

SELECT COUNT(ALL comm) FROM emp;-- all 可以省略

方式3：去重計(jì)數(shù)? 計(jì)算某列中不重復(fù)非NULL值的個(gè)數(shù)

SELECT COUNT( DISTINCT job) FROM emp;

2.最大值? MAX() 求某一列中的最大值，不分組的情況下不可與其他字段一起使用

SELECT MAX(sal) FROM emp ;

3.最小值? MIN()? 不分組的情況下不可與其他字段一起使用

SELECT? MIN(sal) FROM emp ;

4.平均值? AVG()? 不分組的情況下不可與其他字段一起使用

SELECT AVG(sal) FROM emp;

5.求和? SUM() 不分組的情況下不可與其他字段一起使用

SELECT SUM(sal) FROM emp;

練習(xí)：

1.查詢員工的總?cè)藬?shù)；

SELECT COUNT(*) FROM emp;

2.查詢員工的平均工資；

SELECT AVG(sal) FROM emp;

3.查詢文員的總?cè)藬?shù)；

SELECT COUNT(*) FROM emp WHERE job='文員';

4. 查詢30號(hào)部門中文員的總工資；

SELECT SUM(sal+IFNULL(comm,0)) FROM emp WHERE deptno=30 AND job ='文員';

5.查詢10號(hào)部門中員工的最低工資；

SELECT MIN(sal) FROM emp WHERE deptno=10;

6.查詢?nèi)肼殨r(shí)間在2001-2005的員工的最高工資

SELECT MAX(sal) FROM emp WHERE hiredate BETWEEN '2001' AND '2005';

7.查詢2005年之前入職的員工的平均工資

SELECT AVG(sal) FROM emp WHERE hiredate <'2005';

8.查詢2002年之后入職的員工的最高工資和最低工資和平均工資。

SELECT MAX(sal) ,MIN(sal),AVG(sal) FROM emp? WHERE hiredate >'2002';

9.查詢30號(hào)部門的最高工資、最低工資、平均工資；

SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE deptno =30;

10.查詢10號(hào)或20號(hào)部門的文員的最高工資、最低工資、平均工資；

SELECT MAX(sal),MIN(sal),AVG(sal) FROM emp WHERE (deptno =10 OR deptno =20 ) AND job ='文員';

二、分組? GROUP BY

分組計(jì)算時(shí)SELECT 語句中可以有分組字段和聚合函數(shù)，但不要放其他字段；

SELECT deptno, AVG(sal),MAX(sal) ,MIN(sal) ,SUM(sal)FROM? emp WHERE sal >20000 GROUP BY deptno;

HAVING 用戶過濾聚合函數(shù)的值只能用在分組后面

SELECT deptno ,MIN(sal),MAX(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>8000 AND MAX()<50000;

SELECT deptno ,MIN(sal) minSal ,MAX(sal) b FROM emp GROUP BY deptno HAVING minSal>8000 AND b<50000;

使用多個(gè)字段分組：

計(jì)算每個(gè)部門中每個(gè)崗位的平均工資：

SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno ,job ;

分組練習(xí)：

-- 1.查詢各個(gè)部門的最高工資、最低工資、平均工資。

SELECT deptno,MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY deptno;

-- 2.查詢各個(gè)職位的平均工資降序排列

SELECT job,AVG(sal) avgSal FROM emp GROUP BY job ORDER BY avgSal DESC ;

-- 3.查詢平均工資大于10000的崗位

SELECT job ,AVG(sal) avgSal FROM emp GROUP BY job HAVING avgSal >10000 ;

-- 4.查詢每個(gè)部門的人數(shù)

SELECT deptno ,COUNT(ename) FROM emp GROUP BY deptno;

-- 5.查詢?nèi)藬?shù)大于5的部門

SELECT deptno ,COUNT(ename) qty FROM emp GROUP BY deptno HAVING qty>5;

-- 6.查詢部門人數(shù)小于3的部門的平均工資、最高工資、最低工資

SELECT deptno,AVG(sal),MAX(sal),MIN(sal) FROM emp? GROUP BY deptno HAVING COUNT(*)<=3;

-- 7.查詢各個(gè)部門中工資大于10000的人數(shù) 降序顯示

SELECT deptno ,COUNT(empno) qty FROM emp WHERE sal>10000 GROUP BY deptno ORDER BY qty DESC ;

-- 8.查詢各個(gè)崗位中工資大于10000的人數(shù) 升序顯示

-- 9.查詢平均工資大于10000的前兩個(gè)部門。

SELECT AVG(sal) ,deptno FROM emp? GROUP BY deptno HAVING AVG(sal)>10000 LIMIT 2;

-- 10.查詢每個(gè)部門中沒有津貼的人數(shù)。

SELECT deptno,COUNT(*) FROM emp WHERE comm IS NULL GROUP BY deptno

三、組合查詢 - 子查詢

1.單行單列? 跟在WHERE子句后，用于判斷的條件

-- 比李世民工資高的所有員工的信息

SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='李世民')

2.單行多列? 跟在WHERE子句后

-- 查詢和李世民工資及職位都一樣的人的所有信息

SELECT * FROM emp WHERE (job,sal) IN (SELECT job ,sal FROM emp WHERE ename='李世民')

AND ename !='李世民';

3.多行單列

-- 查詢大于30號(hào)部門中任意一個(gè)員工工資的人的所有信息

-- any:任意的意思

SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);

SELECT * FROM emp WHERE sal >? (SELECT MIN(sal) FROM emp WHERE deptno=30);

-- 查詢大于30號(hào)部門中所有人工資的員工信息

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

4.多行多列用在FROM子句后面作為虛表使用

SELECT e.ename FROM (SELECT * FROM emp WHERE deptno =30) e WHERE e.comm IS NULL;

組合查詢-- 合并結(jié)果集? 上下兩條查詢語句的字段個(gè)數(shù)要一樣；

UNION --去除重復(fù)數(shù)據(jù)的合并；

SELECT ename,sal FROM emp WHERE deptno=10 UNION SELECT? ename,sal FROM emp WHERE deptno=20;

UNION ALL? -- 不去除重復(fù)記錄

SELECT ename,sal? FROM emp WHERE deptno=10 UNION ALL SELECT? ename,sal FROM emp WHERE deptno=20;

四、鏈接

1. 內(nèi)連接去除笛卡爾積：去除匹配錯(cuò)誤的數(shù)據(jù)；

1.1 mysql方言方式：

SELECT * FROM emp ,dept? WHERE emp.deptno= dept.deptno AND dept.dname='學(xué)工部' ORDER BY emp.sal DESC ;

1.2 標(biāo)準(zhǔn)SQL方式：SELECT * FROM emp INNER JOIN dept? ON emp.deptno= dept.deptno;

1.3自然連接：SELECT * FROM emp NATURAL JOIN dept;

2.外連接

2.1 左外連接 LEFT OUTER JOIN? -- out可以省略,以左邊表的行數(shù)為準(zhǔn)，若右邊表沒有與之匹配的數(shù)據(jù)，那么用null值填充

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno =d.deptno;

SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.deptno =d.deptno;

2.2 右外連接? RIGHT OUTER JOIN? 與左外連接意思相反

SELECT * FROM emp e RIGHT? JOIN dept d ON e.deptno =d.deptno;

二。1：查詢雇員表中工資最高的雇員的員工號(hào)、員工姓名、工資和部門號(hào)。

2：薪水大于12000的雇員，按照部門編號(hào)進(jìn)行分組，分組后的平均薪水必須大于15000，查詢各分組的平均工資，按照工資的倒序進(jìn)行排列

3:查詢每個(gè)雇員和其所在的部門名

4.查詢每個(gè)雇員姓名及其工資所在的等級(jí)

5：查詢雇員名第2個(gè)字不是‘中‘的雇員的姓名、所在的部門名、工資所在的等級(jí)。

6：查詢每個(gè)雇員和其經(jīng)理的姓名

-- 7：查詢每個(gè)雇員和其經(jīng)理的姓名（包括公司老板本身（他上面沒有經(jīng)理））

SELECT e1.ename,IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

-- 8：查詢每個(gè)雇員的姓名及其所在部門的部門名（包括沒有雇員的部門）

SELECT e.ename,d.dname FROM dept d? LEFT JOIN emp e? ON? d.deptno=e.deptno;

-- 9：查詢每個(gè)部門中工資最高的人的姓名、薪水和部門編號(hào)

SELECT * FROM emp WHERE (deptno,sal)IN (SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno);

SELECT * FROM emp e1,(SELECT deptno,MAX(sal) sal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal = e2.sal;

-- 10：查詢每個(gè)部門平均工資所在的等級(jí)

SELECT? e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

SELECT deptno,AVG(sal) avgSal? FROM emp GROUP BY deptno

) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

-- 11：查詢每個(gè)部門內(nèi)平均的薪水等級(jí)

SELECT? e1.deptno,e1.avgSal,s.grade FROM salgrade s,(

SELECT deptno,AVG(sal+IFNULL(comm,0)) avgSal? FROM emp GROUP BY deptno

) e1 WHERE e1.avgSal BETWEEN s.losal AND s.hisal;

-- 12：查詢雇員中有哪些人是經(jīng)理人

-- 13：不準(zhǔn)用max函數(shù)，求雇員表中薪水的最高值。

SELECT * FROM emp ORDER BY sal+IFNULL(comm,0)? DESC LIMIT 0,1;

-- 14：平均薪水最高的部門的部門編號(hào)

SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

-- 15：求平均薪水最高的部門的部門名稱

SELECT emp.deptno,dname FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

-- 16：查詢手下有員工的領(lǐng)導(dǎo)的信息

SELECT DISTINCT IFNULL(e2.ename,'老板') FROM emp e1 LEFT JOIN emp e2 ON e1.mgr =e2.empno;

-- mysql聯(lián)表查詢練習(xí)：

-- 1.查詢銷售部工資大于20000的所有人；

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname='銷售部' AND emp.sal >20000;

-- 2.查詢每個(gè)部門的人數(shù)，要求顯示部門名稱；

SELECT dept.deptno,dept.dname,dept.loc,IFNULL(d1.co,0) FROM dept LEFT JOIN (

SELECT deptno,COUNT(*) co FROM emp GROUP BY deptno) d1? ON dept.deptno=d1.deptno;

-- 3.查詢每個(gè)部門的最高工資，平均工資，最低工資，要求顯示部門名稱；

SELECT * FROM dept LEFT JOIN (

SELECT deptno ,MAX(sal),AVG(sal) ,MIN(sal) FROM emp GROUP BY deptno) d2? ON dept.deptno = d2.deptno

-- 4.查詢教研部中入職時(shí)間最早的員工信息，要顯示部門名稱，姓名，入職時(shí)間；

SELECT * FROM emp,dept WHERE emp.deptno =dept.deptno AND dept.dname='教研部'? ORDER BY hiredate? LIMIT 1;

-- 5.查詢當(dāng)前沒有員工的部門信息；

SELECT * FROM (

SELECT dept.deptno,dept.dname ,dept.loc? ,emp.empno FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno) a WHERE a.empno IS NULL;

三。課后作業(yè)：

-1.學(xué)生表

Student(Sid,Sname,Sage,Ssex) --Sid 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別

--創(chuàng)建測(cè)試數(shù)據(jù)

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' , '孫風(fēng)' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');

insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--2.課程表

Course(Cid,Cname,Tid) --Cid --課程編號(hào),Cname 課程名稱,Tid 教師編號(hào)

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));

insert into Course values('01' , '語文' , '02');

insert into Course values('02' , '數(shù)學(xué)' , '01');

insert into Course values('03' , '英語' , '03');

--3.教師表

Teacher(Tid,Tname) --Tid 教師編號(hào),Tname 教師姓名

create table Teacher(Tid varchar(10),Tname varchar(10));

insert into Teacher values('01' , '張三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--4.成績表

SC(Sid,Cid,score) --Sid 學(xué)生編號(hào),Cid 課程編號(hào),score 分?jǐn)?shù)

create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1));

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);

課后作業(yè)：