ja筆記凡day16day1 單表的查詢練習(xí)

1、崔西凡JavaWeb筆記day16day18day161.單表的練習(xí)首先，要用到SQLyog v8.32工具輸入用戶名以及Name: Any碼這個(gè)軟件Registration Code: 26f359fc-e3f6-4727-8af1-72a1a4a0819d然后使用一開(kāi)始用的時(shí)候要新建連接然后新建數(shù)據(jù)庫(kù)，新建表開(kāi)始做練習(xí)練習(xí)：1.出部門(mén)編號(hào)為30的所有員工2. 所有銷售員的姓名、編號(hào)和部門(mén)編號(hào)。3. 找出獎(jiǎng)金高于工資的員工。4. 找出獎(jiǎng)金高于工資60%的員工。5. 找出部門(mén)編號(hào)為10中所有經(jīng)理，和部門(mén)編號(hào)為20中所有銷售員的詳細(xì)資料。6. 找出部門(mén)編號(hào)為10中所有經(jīng)理，部門(mén)編號(hào)為20中所有銷

2、售員，還有即不是經(jīng)理又不是銷售員但其工資大或等于20000的所有員工詳細(xì)資料。8. 無(wú)獎(jiǎng)金或獎(jiǎng)金低于1000的員工。9.10.11.12.13.名字由三個(gè)字組成的員工。2000年入職的員工。所有員工詳細(xì)信息，用編號(hào)升序排序所有員工詳細(xì)信息，用工資降序排序，如果工資相同使用入職日期升序排序每個(gè)部門(mén)的平均工資14.15.每個(gè)部門(mén)的雇員數(shù)量。每種工作的最高工資、最低工資、人數(shù)先切換數(shù)據(jù)庫(kù)第一題第二題/*1.出部門(mén)編號(hào)為30的所有員工*/SELECT *FROM empWHERE deptno=30;/*2.所有銷售員的姓名、編號(hào)和部門(mén)編號(hào)。*/SELECT ename, empno, deptnoF

3、ROM empWHERE job='銷售員'/*3. 找出獎(jiǎng)金高于工資的員工。*/SELECT *FROM empWHERE comm > sal/*4. 找出獎(jiǎng)金高于工資60%的員工。*/SELECT *FROM empWHERE comm > sal*0.6;/*5. 找出部門(mén)編號(hào)為10中所有經(jīng)理，和部門(mén)編號(hào)為20中所有銷售員的詳細(xì)資料。*/SELECT *FROM empWHERE (deptno=10 AND job='經(jīng)理') OR (deptno=20 AND job='銷售員')注意中文中的"和"與英

4、文中的"OR"之間的關(guān)系/*6. 找出部門(mén)編號(hào)為10中所有經(jīng)理，部門(mén)編號(hào)為20中所有銷售員，還有即不是經(jīng)理又不是銷售員但其工資大或等于20000的所有員工詳細(xì)資料。*/ SELECT *FROM empWHERE (deptno=10 AND job='經(jīng)理') OR (deptno=20 AND job='銷售員') OR (job NOT IN ('經(jīng)理', '銷售員') AND sal >= 20000)/*8. 無(wú)獎(jiǎng)金或獎(jiǎng)金低于1000的員工。*/SELECT *FROM empWHERE com

5、m IS NULL OR comm < 1000/*9.名字由三個(gè)字組成的員工。*/SELECT *FROM empWHERE ename LIKE ' '/*10.SELECT *2000年入職的員工。*/FROM empWHERE hiredate LIKE '2000-%'/*11.SELECT *所有員工詳細(xì)信息，用編號(hào)升序排序*/FROM empORDER BY empno/*12.SELECT *所有員工詳細(xì)信息，用工資降序排序，如果工資相同使用入職日期升序排序*/FROM empORDER BY sal DESC, hiredate ASC/

6、*13.每個(gè)部門(mén)的平均工資*/SELECT deptno, AVG(sal)FROM emp GROUP BY deptno/*14.每個(gè)部門(mén)的雇員數(shù)量。*/SELECT deptno, COUNT(*)FROM emp GROUP BY deptno/*15.每種工作的最高工資、最低工資、人數(shù)*/SELECT job, MAX(sal), MIN(sal), COUNT(*)FROM emp GROUP BY job做題的時(shí)候，遇到"每個(gè)"的字樣基本上就是需要用到group by 關(guān)鍵字！2.mysql編碼問(wèn)題編碼1. 查看MySQL數(shù)據(jù)庫(kù)編碼* SHOW VARIABL

7、ES LIKE 'char%'2. 編碼解釋* character_set_client：MySQL使用該編碼來(lái)解讀客戶端過(guò)來(lái)的數(shù)據(jù)不是UTF8，那么就會(huì)出現(xiàn)亂碼過(guò)來(lái)的數(shù)據(jù)，例如該編碼為UTF8，那么如果客戶端* character_set_results：MySQL會(huì)把數(shù)據(jù)轉(zhuǎn)換成該編碼后，再不使用UTF8來(lái)解讀，那么就會(huì)出現(xiàn)亂碼其它編碼只要支持中文即可，也就是說(shuō)不能使用latin1給客戶端，例如該編碼為UTF8，那么如果客戶端3.*臺(tái)亂碼問(wèn)題或修改時(shí)出現(xiàn)亂碼：> 這時(shí)因?yàn)閏md下默認(rèn)使用GBK，而character_set_client不是GBK的。我們只需讓這兩個(gè)編碼

8、相同即可。> 因?yàn)樾薷腸md的編碼不方便，所以我們?nèi)ピO(shè)置character_set_client為GBK即可。出的數(shù)據(jù)為亂碼：*> 這是因?yàn)閏haracter_set_results不是GBK，而cmd默認(rèn)使用GBK的。我們只需讓這兩個(gè)編碼相同即可。> 因?yàn)樾薷腸md的編碼不方便，所以我們?nèi)ピO(shè)置character_set_results為GBK即可。* 設(shè)置變量的語(yǔ)句：> set character_set_client=gbk;> set character_set_results=gbk;注意，設(shè)置變量只對(duì)當(dāng)前連接有效，當(dāng)為了一勞永逸，可以在my.ini中設(shè)置

9、： 設(shè)置default-character-set=gbk即可。窗口后，再次登錄mysql，還需要再次設(shè)置變量?。╩afan）4. 指定默認(rèn)編碼我們?cè)诎惭bMySQL時(shí)已經(jīng)指定了默認(rèn)編碼為UTF8，所以我們?cè)趧?chuàng)建數(shù)據(jù)庫(kù)、創(chuàng)建表時(shí)，都無(wú)需再次指定編碼。 為了一勞永逸，可以在my.ini中設(shè)置：設(shè)置character-set-server=utf8即可。character_set_client| utf8 -> mysql把我們客戶端傳遞的數(shù)據(jù)都當(dāng)成是utf8！一是給它傳遞utf8，二是如果我們傳遞的是gbk，那么需要修改這個(gè)變量為gbkcharacter_set_connection | u

10、tf8 character_set_database | utf8 character_set_results| utf8 -> mysql給客戶端的數(shù)據(jù)都是utf8的。一是客戶端用utf8編碼，二是如果客戶端使用gbk來(lái)編碼，那么需要修改這個(gè)變量為gbk的。character_set_server| utf8character_set_system| utf8character_set_client=utf8，無(wú)論客戶端的是什么編碼的數(shù)據(jù)，mysql都當(dāng)成是utf8的數(shù)據(jù)！> 若客戶端的是GBK> 服務(wù)器會(huì)當(dāng)成utf8對(duì)待> 總結(jié)：必然亂碼！處理問(wèn)題的> 讓客戶

11、端有兩種；utf8的數(shù)據(jù)(行不通)> 把character_set_client修改為gbkset character_set_client=gbk; -> 只在當(dāng)前窗口內(nèi)有效，也就是說(shuō)，關(guān)閉窗口后，再打開(kāi)，又回到utf8了。character_set_results=utf8，把數(shù)據(jù)用什么編碼給客戶端！> 若服務(wù)器給客戶端的是utf8的數(shù)據(jù)> 客戶端會(huì)把它當(dāng)成gbk，因?yàn)槲覀兊男『谄粒荒茱@示gbk> 總結(jié)：必然亂碼！處理問(wèn)題的> 讓服務(wù)器有兩種：gbk的數(shù)據(jù)：set character_set_results=gbk> 讓小黑屏使用utf8來(lái)解讀（

12、行不通）my.ini在總配置文件中進(jìn)行配置，可以一勞永逸clientport=3306mysqldefault-character-set=gbk /*它可以一勞永逸！它可以修改三個(gè)變量：client、results、connection*/、下面為演示上圖有誤，uft8改為utf8my.ini中修改之后但是要注意一個(gè)問(wèn)題在SQLyog中，編碼依然是utf8為什么呢？但是即便這樣，我們也不用擔(dān)心編碼問(wèn)題，因?yàn)檫@個(gè)工具的編碼會(huì)自動(dòng)設(shè)置的亂碼問(wèn)題只有在黑窗口中出現(xiàn)3.mysql備份與恢復(fù)數(shù)據(jù)庫(kù)（1）. 數(shù)據(jù)庫(kù)導(dǎo)出SQL(備份數(shù)據(jù)庫(kù)內(nèi)容，并不是備份數(shù)據(jù)庫(kù)！)> mysqldump u用戶名 p

13、數(shù)據(jù)庫(kù)名>生成的文件路徑> 例如：mysqldump -uroot -p123 mydb1>C:mydb1.sql (與mysql.exe和mysqld.exe一樣, 都在bin目錄下)> 注意，不要打分號(hào)，不要登錄mysql，直接在cmd下運(yùn)行> 注意，生成的文件中不包含create database語(yǔ)句（2）. 執(zhí)行SQL第式> mysql -u用戶名 -p> 例如：數(shù)據(jù)庫(kù)<文件路徑* 先刪除mydb1庫(kù)，再重新創(chuàng)建mydb1庫(kù)* mysql -uroot -p123 mydb1<C:mydb1.sql> 注意，不要打分號(hào)，不要登

14、錄mysql，直接在cmd下運(yùn)行第二種方式> 登錄mysql> source SQL> 例如：路徑* 先刪除mydb1庫(kù)，再重新創(chuàng)建mydb1庫(kù)* 切換到mydb1庫(kù)* source c:mydb1.sql下面為演示但是如果不借助工具呢？mysqldump -uroot -p123 mydb3>c:/a.sql打開(kāi)a.sql發(fā)現(xiàn)并沒(méi)有創(chuàng)建數(shù)據(jù)庫(kù)，而是備份了數(shù)據(jù)庫(kù)的內(nèi)容所以備份只是備份了數(shù)據(jù)庫(kù)里面的內(nèi)容，而不是備份數(shù)據(jù)庫(kù)本身因此，在恢復(fù)數(shù)據(jù)庫(kù)的時(shí)候，先手動(dòng)創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，然后再把內(nèi)容恢復(fù)于當(dāng)前的數(shù)據(jù)庫(kù)中mysql -uroot -p123 mydb3<c:/a.sq

15、l然后重新登錄就可以了再完整演示一遍drop 數(shù)據(jù)庫(kù)之后，不能恢復(fù)數(shù)據(jù)庫(kù)，但是能恢復(fù)數(shù)據(jù)庫(kù)的內(nèi)容最后重新登錄數(shù)據(jù)庫(kù)查看就可以了使用第二種方式之前要登錄！總結(jié)就是三條語(yǔ)句：mysqldump -uroot -p123 mydb3>c:/a.sql -> mysql -uroot -p123 mydb3<c:/a.sql -> 恢復(fù) source c:/a.sql -> 恢復(fù)備份4.約束之主鍵約束約束是添加在列上的，用來(lái)約束列的！1. 主鍵約束（唯一標(biāo)識(shí)）*非空*唯一*被*（學(xué)習(xí)外鍵時(shí)）* 當(dāng)表的某一列被指定為主鍵后，該列就不能為空，不能有重復(fù)值出現(xiàn)。* 創(chuàng)建表時(shí)指定

16、主鍵的兩種方式：>CREATE TABLE stu(sidCHAR(6) PRIMARY KEY,sname VARCHAR(20),age INT,gender VARCHAR(10);指定sid列為主鍵列，即為sid列添加主鍵約束>CREATE TABLE stu(sidCHAR(6),sname VARCHAR(20),age INT,gender VARCHAR(10), PRIMARY KEY(sid);指定sid列為主鍵列，即為sid列添加主鍵約束* 修改表時(shí)指定主鍵：ALTER TABLE stu ADD PRIMARY KEY(sid);* 刪除主鍵：ALTER T

17、ABLE stu DROP PRIMARY KEY;一個(gè)回顧5.主鍵自增長(zhǎng)主鍵自增長(zhǎng)* 因?yàn)橹麈I列的特性是：必須唯一、不能為空，所以我們通常會(huì)指定主鍵類為整型，然后設(shè)置其自動(dòng)增長(zhǎng)，這樣可以保證在數(shù)據(jù)時(shí)主鍵列的唯一和非空特性。* 創(chuàng)建表時(shí)指定主鍵自增長(zhǎng)CREATE TABLE stu(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(20),age INT,gender VARCHAR(10);* 修改表時(shí)設(shè)置主鍵自增長(zhǎng)：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;* 修改表時(shí)刪除主鍵自增長(zhǎng)：

18、ALTER TABLE stu CHANGE sid sid INT;* 測(cè)試主鍵自增長(zhǎng)：> INSERT INTO stu VALUES(NULL, 'zhangSan',23,'male');> INSERT INTO stu(sname,age,gender) VALUES('zhangSan',23,'male');主鍵的值可以指定為具體值如果不指定就自增長(zhǎng)6. 非空和唯一約束非空約束* 因?yàn)槟承┝胁荒茉O(shè)置為NULL值，所以可以對(duì)列添加非空約束。* 例如：CREATE TABLE stu(sid INT PRI

19、MARY KEY AUTO_INCREMENT, sname VARCHAR(20) NOT NULL,age INT,gender VARCHAR(10);* 對(duì)sname列設(shè)置了非空約束非空的意思就是值不能為空，但是可以是重復(fù)值唯一約束* 車庫(kù)某些列不能設(shè)置重復(fù)的值，所以可以對(duì)列添加唯一約束。* 例如：CREATE TABLE stu(sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20) NOT NULL UNIQUE,age INT,gender VARCHAR(10);* 對(duì)sname列設(shè)置了非空約束唯一的意思是值必須是唯一的，但是

20、可以為NULL（為空）那么非空約束加上唯一約束是否就等同于主鍵約束了呢？不是！因?yàn)橹麈I約束還有一個(gè)特性就是被！7.概述模型、對(duì)象模型、關(guān)系模型(1)概念模型對(duì)象模型：可以雙向關(guān)聯(lián)，而且的是對(duì)象，而不是一個(gè)主鍵！的只是主鍵，而不是一整行關(guān)系模型：只能多方一方，而且。對(duì)象模型：在java中是domain（領(lǐng)域類）！例如：User、Studentis ahas a(關(guān)聯(lián))> 1對(duì)1> 1對(duì)多> 多對(duì)多use a關(guān)系模型：在數(shù)據(jù)庫(kù)中表！當(dāng)我們要完成一個(gè)軟件系統(tǒng)時(shí)，需要把系統(tǒng)中的實(shí)體抽取出來(lái)，形成概念模型。例如部門(mén)、員工都是系統(tǒng)中的實(shí)體。概念模型中的實(shí)體最終會(huì)成為Java中的類、數(shù)據(jù)庫(kù)

21、中表。實(shí)體之間還存在著關(guān)系，關(guān)系有三種：* 1對(duì)多：例如每個(gè)員工都從屬一個(gè)部門(mén)，而一個(gè)部門(mén)可以有多個(gè)員工，其中員工是多方，而部門(mén)是一方。* 1對(duì)1：例如老公和老婆就是一對(duì)一的關(guān)系，一個(gè)老公只能有一個(gè)老婆，而一個(gè)老婆只能有一個(gè)老公。* 多對(duì)多：與學(xué)生的關(guān)系就是多對(duì)多，一個(gè)可以有多個(gè)學(xué)生，一個(gè)學(xué)生可以有多個(gè)。概念模型在Java中成為實(shí)體類（javaBean）類就使用成員變量來(lái)完成關(guān)系，一般都是雙向關(guān)聯(lián)！ 多對(duì)一雙向中關(guān)聯(lián)，即員工關(guān)聯(lián)部門(mén)，部門(mén)也關(guān)聯(lián)員工class Employee /多方關(guān)聯(lián)一方.private Department department;class Department /一方關(guān)

22、聯(lián)多方.private List<Employee> employees;class Husband .private Wife wife;class Wife .private Husbandclass Student .private List<Teacher> teachersclass Teacher .private List<Student> students;(2). 外鍵約束* 外鍵必須是另一表的主鍵的值(外鍵要* 外鍵可以重復(fù)* 外鍵可以為空* 一張表中可以有多個(gè)外鍵！主鍵！)一張表有主鍵和外鍵外鍵另外一張表的主鍵概念模型在數(shù)據(jù)庫(kù)中成為表數(shù)

23、據(jù)庫(kù)表中的多對(duì)一關(guān)系，只需要在多方使用一個(gè)的列來(lái)1方的主鍵即可8.外鍵約束下面的紅字就是要注意的語(yǔ)法CREATE TABLE dept(deptno INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(50);INSERT INTO dept VALUES (10,'研發(fā)部');INSERT INTO dept VALUES (20,'人力部'); INSERT INTO dept VALUES (30,'財(cái)務(wù)部');SELECT * FROM dept;SELECT * FROM emp;CREATE TA

24、BLE emp(empno INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(50),dno INT,CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno);INSERT INTO emp(empno,ename)VALUES (1001,'zhangSan');INSERT INTO emp(empno,ename,dno)VALUES (1002,'zhangSan',30);INSERT INTO emp(empno,ename,dno)VAL

25、UES (1003,'liSi',10);9.一對(duì)一關(guān)系數(shù)據(jù)庫(kù)一對(duì)一關(guān)系在表中建立一對(duì)一關(guān)系比較特殊，需要讓其中一張表的主鍵，即是主鍵又是外鍵。create table husband(hid int PRIMARY KEY,.);create table wife(wid int PRIMARY KEY,.ADD CONSTRAINT fk_wife_wid FOREIGN KEY(wid) REFERENCES husband(hid);其中wife表的wid即是主鍵，又是相對(duì)husband表的外鍵！ husband.hid是主鍵，不能重復(fù)！wife.wid是主鍵，不能重復(fù)

26、，又是外鍵，必須來(lái)自husband.hid。所以如果在wife表中有一條的wid為1，那么wife表中的其他的wid就不能再是1了，因?yàn)樗侵麈I。同時(shí)在husband.hid中必須存在1這個(gè)值，因?yàn)閣id是外鍵。這就完成了一對(duì)一關(guān)系。*從表的主鍵即是外鍵！以下為舉例CREATE TABLE husband(hid INT PRIMARY KEY AUTO_INCREMENT,hname VARCHAR(50);INSERT INTO husband VALUES(NULL,'劉備');INSERT INTO husband VALUES(NULL,'關(guān)羽');

27、INSERT INTO husband VALUES(NULL,'張飛');SELECT * FROM husband;CREATE TABLE wife(wid INT PRIMARY KEY AUTO_INCREMENT,wname VARCHAR(50),CONSTRAINT fk_wife_husband FOREIGN KEY(wid) REFERENCES husband(hid);INSERT INTO wife VALUES (1,'楊貴妃');INSERT INTO wife VALUES (2,'妲己');SELECT * F

28、ROM wife;10.多對(duì)多關(guān)系在表中建立多對(duì)多關(guān)系需要使用中間表，即需要三張表，在中間表中使用兩個(gè)外鍵，分別其他兩個(gè)表的主鍵。一個(gè)例子CREATE TABLE student(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(50);CREATE TABLE teacher(tid INT PRIMARY KEY AUTO_INCREMENT,tname VARCHAR(50);CREATE TABLE stu_tea(sid INT, tid INT,CONSTRAINT fk_student FOREIGN KEY(sid)REFEREN

29、CES student(sid), CONSTRAINT fk_teacher FOREIGN KEY(tid) REFERENCES teacher(tid);INSERT INTO student VALUES(NULL,'劉德華');INSERT INTO student VALUES(NULL,'梁朝偉'); INSERT INTO student VALUES(NULL,'黃日華'); INSERT INTO student VALUES(NULL,'苗僑偉'); INSERT INTO student VALUES(N

30、ULL,'湯鎮(zhèn)業(yè)');INSERT INTO teacher VALUES(NULL,'崔INSERT INTO teacher VALUES(NULL,'劉 INSERT INTO teacher VALUES(NULL,'石');');');SELECT * FROM student;SELECT * FROM teacher;INSERT INTO stu_tea VALUES (1,1);INSERT INTO stu_tea VALUES (2,1); INSERT INTO stu_tea VALUES (3,1);

31、INSERT INTO stu_tea VALUES (4,1);INSERT INTO stu_tea VALUES (5,1);INSERT INTO stu_tea VALUES (2,2);INSERT INTO stu_tea VALUES (3,2); INSERT INTO stu_tea VALUES (4,2);INSERT INTO stu_tea VALUES (3,3);INSERT INTO stu_tea VALUES (4,3); INSERT INTO stu_tea VALUES (5,3);SELECT * FROM stu_tea;學(xué)生表教師表關(guān)聯(lián)表（中間

32、表）11.合并結(jié)果集* 要求被合并的表中，列的類型和列數(shù)相同* UNION，去除重復(fù)行* UNION ALL，不去除重復(fù)行SELECT * FROM cdUNION ALL SELECT * FROM ab;下面是例子CREATE TABLE ab(a INT ,b VARCHAR(50);INSERT INTO ab VALUES(1,'1');INSERT INTO ab VALUES(2,'2'); INSERT INTO ab VALUES(3,'3');CREATE TABLE cd(c INT ,d VARCHAR(50);INSER

33、T INTO cd VALUES(3,'3');INSERT INTO cd VALUES(4,'4'); INSERT INTO cd VALUES(5,'5');SELECT * FROM abUNION ALL SELECT * FROM cd;ab表cd表加上union all關(guān)鍵字后合并結(jié)果集注意如果是union而不是union all呢12.連接連接1. 分類* 內(nèi)連接* 外連接之內(nèi)連接（方言）> 左外連接> 右外連接> 全外連接(MySQL不支持)* 自然連接（屬于一種簡(jiǎn)化方式）2. 內(nèi)連接* 方言：SELECT

34、* FROM 表1 別名1, 表2 別名2 WHERE 別名1.xx=別名2.xx* 標(biāo)準(zhǔn)：SELECT * FROM 表1 別名1 INNER JOIN 表2 別名2 ON 別名1.xx=別名2.xx* 自然：SELECT * FROM 表1 別名1 NATURAL JOIN 表2 別名2* 內(nèi)連接出的所有都滿足條件。下面是方言-上面是笛卡爾積笛卡爾積a, b, c 1,2a1, a2, b1, b2, c1, c2去除之后SELECT *FROM emp,deptWHERE emp.deptno=dept.deptno;一個(gè)例子打印所有員工的姓名，工資，以及部門(mén)的名稱起別名簡(jiǎn)化書(shū)寫(xiě)13.連

35、接之內(nèi)連接（和自然）標(biāo)準(zhǔn)：SELECT * FROM 表1 別名1 INNER JOIN 表2 別名2 ON 別名1.xx=別名2.xx自然：SELECT * FROM 表1 別名1 NATURAL JOIN 表2 別名2自然連接條件自動(dòng)匹配，但是可讀性比較差。建議用標(biāo)準(zhǔn)形式14.連接外連接之外連接* 左外：SELECT * FROM 表1 別名1 LEFT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx> 左表無(wú)論是否滿足條件都會(huì)出來(lái)，而右表只有滿足條件才能出來(lái)。左表中不滿足條件的，右表部分都為NULL（也可以用IFNULL函數(shù)修改這個(gè)值）* 左外自然：SELEC

36、T * FROM 表1 別名1 NATURAL LEFT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx* 右外：SELECT * FROM 表1 別名1 RIGHT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx> 右表無(wú)論是否滿足條件都會(huì)出來(lái)，而左表只有滿足條件才能出來(lái)。右表不滿足條件的，其左表部分都為NULL（也可以用IFNULL函數(shù)修改這個(gè)值）* 右外自然：SELECT * FROM 表1 別名1 NATURAL RIGHT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx* 全：可以使用UNION來(lái)完成全可以從上一

37、節(jié)的例子中發(fā)現(xiàn)內(nèi)連接并沒(méi)有把張三這個(gè)特殊的值找出來(lái)，而外連接可以解決這個(gè)問(wèn)題外連接初步體驗(yàn)外連接注意ifnull函數(shù)改進(jìn)右外模擬全外聯(lián)15.子子：中有（查看select關(guān)鍵字的個(gè)數(shù)?。?）. 出現(xiàn)的位置：* where后作為條件存在（例如工資最大值對(duì)應(yīng)的值）* from后作為表存在(多行多列)（也就是對(duì)表進(jìn)行二次的）（2）條件* (*)單行單列：SELECT * FROM 表1 別名1 WHERE 列1 =、>、<、>=、<=、!= (SELECT 列 FROM 表2 別名2 WHERE 條件)* (*)多行單列：SELECT * FROM 表1 別名1 WHERE

38、列1 IN, ALL, ANY (SELECT 列 FROM 表2 別名2 WHERE 條件)* (*)單行多列：SELECT * FROM 表1 別名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 別名2 WHERE 條件)* (*)多行多列：SELECT * FROM 表1 別名1 , (SELECT ) 別名2 WHERE 條件多行單列可以使用ALL也可以使用ANY代表該列數(shù)據(jù)中任意一項(xiàng)同理也可以用IN( )單行多列子子總結(jié)：通常出現(xiàn)在：FROM之后-作為一張表（多行多列）WHERE之后-單行單列，多行單列(集合)(可使用ANY,ALL)，單行多列(少

39、用)16./*1. 查出至少有一個(gè)員工的部門(mén)。顯示部門(mén)編號(hào)、部門(mén)名稱、部門(mén)位置、部門(mén)人數(shù)。列：d.deptno, d.dname, d.loc, 部門(mén)人數(shù)表：dept d, emp e條件：e.deptno=d.deptno*/SELECT d.*,tFROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1WHERE d.deptno = z1.deptno以下是做法（個(gè)人認(rèn)為比講的清晰）/*1. 查出至少有一個(gè)員工的部門(mén)。顯示部門(mén)編號(hào)、部門(mén)名稱、部門(mén)位置、部門(mén)人數(shù)。*/SELECT dept.deptno,d

40、ept.dname,dept.loc,COUNT(dept.deptno) 部門(mén)人數(shù)FROM emp INNER JOIN dept ON dept.deptno=emp.deptno GROUP BY dept.deptno;以下是第三題/*3. 列出所有員工的姓名及其直接的姓名。列：?jiǎn)T工姓名、表：emp e, emp m姓名條件：?jiǎn)T工的mgr =的empno*/ SELECT *FROM emp e, emp m WHERE e.mgr=m.empnoSELECT e.ename, IFNULL(m.ename, 'BOSS')FROM emp e LEFT OUTER J

41、OIN emp m ON e.mgr=m.empno以下是第四題/*4. 列出受雇日期早于直接的所有員工的編號(hào)、姓名、部門(mén)名稱。列：e.empno, e.ename, d.dname表：emp e, emp m, dept d 條件：e.hiredate<m.hiredate思路：1. 先不查部門(mén)名稱，只查部門(mén)編號(hào)!列：e.empno, e.ename, e.deptno表：emp e, emp m條件：e.mgr=m.empno, e.hiredate<m.hireadate*/SELECT e.empno, e.ename, e.deptno FROM emp e, emp m

42、WHERE e.mgr=m.empno AND e.hiredate<m.hiredateSELECT e.empno, e.ename, d.dnameFROM emp e, emp m, dept dWHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno下面第五題/*5. 列出部門(mén)名稱和這些部門(mén)的員工信息，同時(shí)列出那些沒(méi)有員工的部門(mén)。列：*表：emp e, dept d條件：e.deptno=d.deptno*/ SELECT *FROM emp e RIGHT OUTER JOIN dept dO

43、N e.deptno=d.deptno第七題/*7. 列出最低薪金大于15000的各種工作及從事此工作的員工人數(shù)。列：job, count(*)表：emp e條件：min(sal) > 15000 分組：job*/SELECT job, COUNT(*)FROM emp e GROUP BY jobHAVING MIN(sal) > 15000是having還是where出現(xiàn)聚合函數(shù)，就是having的！第八題-子/*8. 列出在銷售部工作的員工的姓名，假定不知道銷售部的部門(mén)編號(hào)。列：e.ename表：emp條件：e.deptno=(select deptno from dept

44、where dname='銷售部')*/SELECT *FROM emp eWHERE e.deptno=(SELECT deptno FROM dept WHERE dname='銷售部')第九題-四表左外連接&&子第九題比較復(fù)雜，但是值得細(xì)細(xì)思考（尤其是下面的紅字部分）！/*9. 列出薪金高于公司平均薪金的所有員工信息，所在部門(mén)名稱，列：*表：emp e，工資等級(jí)。條件：sal>(*/出公司的平均工資)SELECT e.*, d.dname, m.ename, s.gradeFROM emp e, dept d, emp m, salg

45、rade sWHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisalSELECT e.*, d.dname, m.ename, s.gradeFROMemp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno LEFT OUTER JOIN emp m ON e.mgr=m.empnoLEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.

46、losal AND s.hisal WHERE e.sal>(SELECT AVG(sal) FROM emp)SELECT * FROM emp;SELECT * FROM dept; SELECT * FROM salgrade;前提知識(shí)=第十題-注意子的意識(shí)/*10.列出與龐統(tǒng)從事相同工作的所有員工及部門(mén)名稱。列：e.*, d.dname表：emp e, dept d條件：job=(*/出龐統(tǒng)的工作)SELECT e.*, d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WH

47、ERE ename='龐統(tǒng)');第11題/*11.列出薪金高于在部門(mén)30工作的所有員工的薪金 的員工姓名和薪金、部門(mén)名稱。列：e.ename, e.sal, d.dname表：emp e, dept d條件；sal>all (30部門(mén)薪金)*/SELECT e.ename, e.sal, d.dname FROM emp e, dept dWHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30)本人有第二種做法SELECT e.ename,e.sal,d.dnameFROM

48、 emp e INNER JOIN dept dON e.deptno=d.deptno AND sal >(SELECT MAX(sal) FROM emp WHERE deptno=30);結(jié)果是一樣的=第13題（面試題！） 前提表/*13.查出年份、利潤(rùn)、年度增長(zhǎng)比*/SELECT y1.*, IFNULL(CONCAT(y1.zz-y2.zz)/y2.zz*100, '%'), '0%') 增長(zhǎng)比FROM tb_year y1 LEFT OUTER JOIN tb_year y2 ON y1.year=y2.year+1;好好看最后一題day171

49、.獲取Connection對(duì)象jdbc快速入門(mén)首先，新建項(xiàng)目，導(dǎo)入jar包 mysql-connector-java-5.1.13-bin.jar然后必須背下來(lái)的東西：driverClassName:com.mysql.jdbc.Driverurl:jdbc:mysql:/localhost:3306/數(shù)據(jù)庫(kù)名初始版本=public class Demo1 /* ClassNotFoundException：* > 沒(méi)導(dǎo)驅(qū)動(dòng)包（jar包）* >* SQLException：* > 檢查3個(gè)參數(shù)：url、username、password是否正確* > 檢查是否開(kāi)啟了my

50、sql服務(wù)器！*/ Testpublic void fun1() throws ClassNotFoundException, SQLException /* jdbc四大配置參數(shù)：* > driverClassName：com.mysql.jdbc.Driver* > url：jdbc:mysql:/localhost:3306/mydb3* > username：root* > password：123*/簡(jiǎn)化寫(xiě)就是兩個(gè)步驟Class.forName("com.mysql.jdbc.Driver");/ 加載驅(qū)動(dòng)類(驅(qū)動(dòng))/ 使用url、user

51、name、password，得到連接對(duì)象Connection con = DriverManager.getConnection( "jdbc:mysql:/localhost:3306/mydb3", "root", "123"); System.out.println(con);=問(wèn)題注意2.JDBC的原理JDBC是接口，而JDBC驅(qū)動(dòng)才是接口的實(shí)現(xiàn)，沒(méi)有驅(qū)動(dòng)無(wú)法完成數(shù)據(jù)庫(kù)連接！每個(gè)數(shù)據(jù)庫(kù)廠商都有接公司的數(shù)據(jù)庫(kù)。的驅(qū)動(dòng)，用來(lái)連當(dāng)然還有第一個(gè)知識(shí)公司專門(mén)為某一數(shù)據(jù)庫(kù)提供驅(qū)動(dòng)，這樣的驅(qū)動(dòng)往往不是開(kāi)源的！Class.forName(&q

52、uot;com.mysql.jdbc.Driver");上面這行代碼可以被下面兩行代碼替換com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver(); DriverManager.registerDriver(driver);所有的java.sql.Driver實(shí)現(xiàn)類，都提供了static塊，塊內(nèi)的代碼就是把DriverManager中！到j(luò)dbc4.0之后，每個(gè)驅(qū)動(dòng)jar包中，在META-INF/services目錄下提供了一個(gè)名為java.sql.Driver的文件。文件的內(nèi)容就是該接口的實(shí)現(xiàn)類名稱！3.JDBC完成增、刪

53、、改、查下面是具體代碼，關(guān)于連接數(shù)據(jù)庫(kù)以及增刪改操作(executeUpdate)/* 連接數(shù)據(jù)庫(kù)，得到Connection就算* 對(duì)數(shù)據(jù)庫(kù)做增、刪、改*/ Test！public void fun1() throws ClassNotFoundException, SQLException /* 一、得到Connection* 1. 準(zhǔn)備四大參數(shù)* 2. 加載驅(qū)動(dòng)類* 3. 得到Connection*/ 準(zhǔn)備四大參數(shù)String driverClassName = "com.mysql.jdbc.Driver"/ jdbc協(xié)議的格式！jdbc:工商的名稱:子協(xié)議（由工商來(lái)

54、規(guī)定）/ 對(duì)mysql而言，它的子協(xié)議結(jié)構(gòu)：/主機(jī):端/數(shù)據(jù)庫(kù)名稱String url = "jdbc:mysql:/localhost:3306/mydb3"String username = "root" String password = "123"/ 加載驅(qū)動(dòng)類Class.forName(driverClassName);/ 使用DriverManager，以及省下的3個(gè)參數(shù)，得到ConnectionConnection con = DriverManager.getConnection(url, username, pass

55、word);/* 二、對(duì)數(shù)據(jù)庫(kù)做增、刪、改* 1. 通過(guò)Connection對(duì)象創(chuàng)建Statement* > Statement語(yǔ)句的器，它的功能就是向數(shù)據(jù)庫(kù)sql語(yǔ)句?。ㄟ@是關(guān)于statement的中文DML、DDL）* 2. 調(diào)用它的int executeUpdate(String sql)，它可以*/ 1. 通過(guò)Connection得到Statement對(duì)象Statement stmt = con.createStatement();/ 2. 使用Statementsql語(yǔ)句！/String sql = "INSERT INTO stu VALUES('ITCAST_0003', 'wangWu', 88, 'male')"String sql = "UPDATE stu SET name='zhaoLiu', age=22, " + "gender='female' WHERE number='ITCAST_0003'"Stri