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

2、，Sname，Eid ，year ）方案二presidents（Eid，Ename ，Sid ，year）studios（Sid，Sname）北京傳智播客教育 多表設計實例設計學生成績管理系統(tǒng)數(shù)據(jù)表1、每個教師可以教多門課程2、每個課程由一個老師負責3、每門課程可以由多個學生選修4、每個學生可以選修多門課程5、學生選修課程要有成績北京傳智播客教育 多表查詢笛卡爾積笛卡爾積的概念只要有意義的結(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;北京傳智播客教育 關聯(lián)子查詢什么是子查詢將一個查詢作為另一個查詢的一部分查詢student表中年齡最大學員的信息select * from student where age = (select max(age) from student);北京傳智播客教育 IN/EXISTS 存在查詢所有成績小于60分的同學名稱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)查詢獲得最高分的學生學號select student_id from studentcource where score= all(select score from studentcource);查詢編號2課程比編號1課程成績高所有學號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) 排重重復結(jié)果(SELECT A_ID FROM A) UNION ALL (SELECT A_ID FROM B) 不排除重復結(jié)果北京傳智播客教育 時間日期相關函數(shù)ADDTIME (date2 ,time_interval )將time_interval加到date2CURRENT_DATE ( )當前日期CUR

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

11、串、時間日期的引號問題 select date_add(entry_date,INTERVAL 2 year) from student;增加兩年select addtime(time,1 1-1 10:09:09) from student;時間戳上增加，注意年后沒有-北京傳智播客教育 字符串相關函數(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個字符LENGTH (string )string長度REPLACE (str ,search_str ,replace_str )在str中用replace_str替換search_strSTRCMP (string1 ,string2 )逐字符比較兩字串大小,SUBSTRING (str , position ,length )從str的position開始,取length個字符LTRIM (string2 ) RTRIM (string2 ) trim去除前端空格或后端空格北京傳智播客教育 數(shù)學相關函數(shù)ABS (number2 )絕對值BIN (decimal_number )十進制轉(zhuǎn)二進制CEILING (number2 )向上取整CONV(number2,from_base,to_base)進制轉(zhuǎn)換FLOOR (number2 )向下取整FORMAT (number,decimal_places )保留小數(shù)位數(shù)HEX (DecimalNumber )轉(zhuǎn)十六進制LEAST (number , number2 ,.)求最小值MOD (numerator ,denominator )求余RAND(see