javaweb總結(jié)sql強(qiáng)化課程

1、北京傳智播客教育 SQL 強(qiáng)化于 洋 北京傳智播客教育 多表設(shè)計(jì)中三種實(shí)體關(guān)系北京傳智播客教育 M:N的聯(lián)系引入關(guān)系表聯(lián)系的屬性及兩個(gè)實(shí)體的主標(biāo)識(shí)形成關(guān)系表。關(guān)系表的主鍵為兩個(gè)實(shí)體主標(biāo)識(shí)的組合參加表（E1,P1,percent）雇員表(E1,E2)項(xiàng)目表(P1,P2,P3)北京傳智播客教育 1:N 的聯(lián)系可不引入關(guān)系表將關(guān)系的屬性及非多方的主標(biāo)識(shí)加入到多方表多方表的外鍵是非多方實(shí)體的主標(biāo)識(shí)老師表（Tid，Tname，Tsex）課程表（Cid，Cname，Tid，allowance）北京傳智播客教育 1:1的聯(lián)系不必引入關(guān)系表方案一presidents（Eid，Ename）studios（Sid

2、，Sname，Eid ，year ）方案二presidents（Eid，Ename ，Sid ，year）studios（Sid，Sname）北京傳智播客教育 多表設(shè)計(jì)實(shí)例設(shè)計(jì)學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)表1、每個(gè)教師可以教多門課程2、每個(gè)課程由一個(gè)老師負(fù)責(zé)3、每門課程可以由多個(gè)學(xué)生選修4、每個(gè)學(xué)生可以選修多門課程5、學(xué)生選修課程要有成績(jī)北京傳智播客教育 多表查詢笛卡爾積笛卡爾積的概念只要有意義的結(jié)果？WHERE A.A_ID=B.A_IDSELECT * FROM AA_ID A_NAME-1 Apple2 Orange3 PeachSELECT * FROM BA_ID B_PRICE-1 2.3

3、02 3.504 (null)X=SELECT * FROM A,BA_ID A_NAME A_ID B_PRICE-1 Apple 1 2.301 Apple 2 3.501 Apple 4 (null)2 Orange 1 2.302 Orange 2 3.502 Orange 4 (null)3 Peach 1 2.303 Peach 2 3.503 Peach 4 (null)北京傳智播客教育 多表查詢內(nèi)連接查詢idname1張三2李四3王五idjob1人力主管2軟件工程師4軟件工程師idnameJob1張三人力主管2李四軟件工程師員工表 a工作表 b內(nèi)連接寫法一：select * f

4、rom a inner join b on a.id=b.id;寫法二：select * from a,b where a.id = b.id;北京傳智播客教育 左外連接SELECT * FROM AA_ID A_NAME-1 Apple2 Orange3 PeachSELECT * FROM BA_ID B_PRICE-1 2.302 3.504 (null)LeftOuterJoinOn=SELECT * FROM A LEFT OUTER JOIN B ON A.A_ID=B.A_IDA_ID A_NAME A_ID B_PRICE-1 Apple 1 2.302 Orange 2 3.

5、503 Peach (null) (null)北京傳智播客教育 右外連接SELECT * FROM AA_ID A_NAME-1 Apple2 Orange3 PeachSELECT * FROM BA_ID B_PRICE-1 2.302 3.504 (null)RightOuterJoin=SELECT * FROM A RIGHT OUTER JOIN B ON A.A_ID=B.A_IDA_ID A_NAME A_ID B_PRICE-1 Apple 1 2.302 Orange 2 3.50(null) (null) 4 (null)北京傳智播客教育 全外連接SELECT * FRO

6、M AA_ID A_NAME-1 Apple2 Orange3 PeachSELECT * FROM BA_ID B_PRICE-1 2.302 3.504 (null)FullOuterJoin=SELECT * FROM A Full OUTER JOIN B ON A.A_ID=B.A_IDA_ID A_NAME A_ID B_PRICE-1 Apple 1 2.302 Orange 2 3.503 Peach (null) (null) (null) (null) 4 (null)Mysql不支持全外連接select * from A left outer join B on A.A_

7、ID = B.A_IDunionselect * from A right outer join B on A.A_ID = B.A_ID;北京傳智播客教育 關(guān)聯(lián)子查詢什么是子查詢將一個(gè)查詢作為另一個(gè)查詢的一部分查詢student表中年齡最大學(xué)員的信息select * from student where age = (select max(age) from student);北京傳智播客教育 IN/EXISTS 存在查詢所有成績(jī)小于60分的同學(xué)名稱select name from student where id in (select student_id from studentcour

8、ce where score 60);select name from student where exists (select * from studentcource where score any(1,2,3)ALL 所有數(shù)據(jù) all(1,2,3)查詢獲得最高分的學(xué)生學(xué)號(hào)select student_id from studentcource where score= all(select score from studentcource);查詢編號(hào)2課程比編號(hào)1課程成績(jī)高所有學(xué)號(hào)select score from studentcource where cource_id = 2 an

9、d score any(select score from studentcource where cource_id = 1);北京傳智播客教育 結(jié)果集操作UNION ALL 取結(jié)果集并集(SELECT A_ID FROM A) UNION (SELECT A_ID FROM B) 排重重復(fù)結(jié)果(SELECT A_ID FROM A) UNION ALL (SELECT A_ID FROM B) 不排除重復(fù)結(jié)果北京傳智播客教育 時(shí)間日期相關(guān)函數(shù)ADDTIME (date2 ,time_interval )將time_interval加到date2CURRENT_DATE ( )當(dāng)前日期CUR

10、RENT_TIME ( )當(dāng)前時(shí)間CURRENT_TIMESTAMP ( )當(dāng)前時(shí)間戳DATE (datetime )返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type )在date2中加上日期或時(shí)間DATE_SUB (date2 , INTERVAL d_value d_type )在date2上減去一個(gè)時(shí)間DATEDIFF (date1 ,date2 )兩個(gè)日期差NOW ( )當(dāng)前時(shí)間YEAR|Month|DATE (datetime )年月日示例：select addtime(02:30:30,01:01:01); 注意：字符

11、串、時(shí)間日期的引號(hào)問題 select date_add(entry_date,INTERVAL 2 year) from student;增加兩年select addtime(time,1 1-1 10:09:09) from student;時(shí)間戳上增加，注意年后沒有-北京傳智播客教育 字符串相關(guān)函數(shù)CHARSET(str)返回字串字符集CONCAT (string2 ,. )連接字串INSTR (string ,substring )返回substring在string中出現(xiàn)的位置,沒有返回0UCASE (string2 )轉(zhuǎn)換成大寫LCASE (string2 )轉(zhuǎn)換成小寫LEFT (s

12、tring2 ,length )從string2中的左邊起取length個(gè)字符LENGTH (string )string長(zhǎng)度REPLACE (str ,search_str ,replace_str )在str中用replace_str替換search_strSTRCMP (string1 ,string2 )逐字符比較兩字串大小,SUBSTRING (str , position ,length )從str的position開始,取length個(gè)字符LTRIM (string2 ) RTRIM (string2 ) trim去除前端空格或后端空格北京傳智播客教育 數(shù)學(xué)相關(guān)函數(shù)ABS (number2 )絕對(duì)值BIN (decimal_number )十進(jìn)制轉(zhuǎn)二進(jìn)制CEILING (number2 )向上取整CONV(number2,from_base,to_base)進(jìn)制轉(zhuǎn)換FLOOR (number2 )向下取整FORMAT (number,decimal_places )保留小數(shù)位數(shù)HEX (DecimalNumber )轉(zhuǎn)十六進(jìn)制LEAST (number , number2 ,.)求最小值MOD (numerator ,denominator )求余RAND(see