數(shù)據(jù)庫基礎(chǔ)培訓(xùn)SQL語言培訓(xùn)1ppt課件

1、數(shù)據(jù)庫根底: SQL語法(1)北明軟件員工培訓(xùn)講義2章節(jié)n查詢數(shù)據(jù)的SQL語句n修正數(shù)據(jù)的SQL語句n管理數(shù)據(jù)定義的SQL語句3查詢數(shù)據(jù)的SQL語句nSELECT 語句4查詢數(shù)據(jù)的SQL語句-SELECTn本章講述如何運(yùn)用SELECT語句的五個子句。n在SELECT語句中，他必需按以下順序運(yùn)用這些子句 ：nSELECT子句nFROM子句nWHERE子句nORDER BY子句nSELECT 字段1,字段2字段n FROM 表1,表 2表n5SELECT語句根底n可以用多種方法來查詢數(shù)據(jù)庫中的一個表。他可以裁剪SELECT語句來到達(dá)以下目的：n檢索一切的或指定的列nselect id, name

2、from student 查詢編號，姓名)nselect * from student(查詢一切字段)n對檢索所得的數(shù)據(jù)執(zhí)行計(jì)算或其它函數(shù)nselect id, name, sum(mark) from student group by id, namen(按照編號、姓名為分組，對“成果進(jìn)展匯總)n(簡單的說，就是查詢每個學(xué)生的總成果)6Where子句nWhere 子句用于限制數(shù)據(jù)nselect id, name from student where id=222233444n上句查找出編號為222233444的學(xué)生的學(xué)生編號和姓名nselect id,name from student wh

3、ere name like 張%n上句查找出姓名為“張開頭的學(xué)生的學(xué)生編號和姓名n其它約束方法：nWhere column1 like _b% n(部分匹配, %為通配符,表示=0個恣意字符, _表示一個恣意字符)nWhere id between 10000 and 20000 (范圍匹配)nWhere id =10000 and id = 20000nWhere id in (100001,100005,133434) (集合匹配)nWhere name is NULL(空值匹配)nWhere name is not NULL (非空值匹配)nWhere column1=column2 (

4、字段間匹配) (=, =, , , .)n7Where子句(2)n多條件限制n1) select id, name from student where id 23456 and name like 張%n2) select id, name from student where id 23456 or name like 張%n3) select id, name from student where (id 23456 or name like 張%) and mark60nn不要思索條件匹配的先天優(yōu)先級，在他想優(yōu)先匹配的地方加上括號，永遠(yuǎn)是對的8Order by 子句 (用于排序)n單列

5、排序nSelect id, name from student order by id asc (升序)nSelect id, name from student order by id desc (降序)n多列排序nselect id, name ,mark from student order by mark desc , id ascn(先按照成果降序，假設(shè)成果一樣，那么按照編號升序)nasc可以省略，由于默許是按照升序陳列但是desc不可以n如: select id, name from student order by id (升序)9Group by (用于匯總計(jì)算)nGROUP

6、中文意思： 分組nselect id, name,sum(mark) from student group by id, namen上句按照id,name 為分組，計(jì)算mark的匯總值)nSelect id, name, avg(mark),sum(mark) from student group by id, namen(上句按照id,name 為分組， 計(jì)算mark的平均值)nSelect id, name, avg(mark) from student group by idn(上一句語法是錯誤的， 由于有一個字段name在分組之外，數(shù)據(jù)庫不知道怎樣歸類計(jì)算)nSelect id, na

7、me, mark1+mark2 from student group by id,namen(上一句也是錯誤的，mark1+mark2是兩個字段間的計(jì)算，沒有匯總計(jì)算，分組沒有意義)10UNION 組合查詢結(jié)果1) Union 組合查詢結(jié)果,合并反復(fù)記錄Select id, name from studentUnionSelect id, name from teacher2) Union All 組合查詢結(jié)果，不合并反復(fù)記錄Select id, name from studentUnion AllSelect id, name from teacher11多表關(guān)聯(lián)(1)n內(nèi)銜接(inner

8、join)n寫法一: select a.id, , b.id, from student a, teacher b where a.techer_id=b.idn寫法二: select a.id, , b.id, from student a inner join techer b on a.techer_id=b.id n寫法二是規(guī)范語法)12多表關(guān)聯(lián)(2)n外銜接(outer join)n左外銜接(left outer join)n寫法一: select a.id, , b.id, from student a,

9、 teacher b where a.techer_id *=b.idn寫法二: select a.id, , b.id, from student a left outer join techer b on a.techer_id=b.id 寫法二是規(guī)范語法)n右外銜接(right outer join)n寫法一: select a.id, , b.id, from student a, teacher b where a.techer_id =+ b.idn寫法二: select a.id, , b.id, fr

10、om student a right outer join techer b on a.techer_id=b.idn寫法二是規(guī)范語法)13子查詢n子查詢的結(jié)果相當(dāng)于一張tablenSelect A.id, A.name, sum(A.mark) from ( nselect id, name, mark from student where mark 60n) A Group by A.id, A.namen子查詢的結(jié)果相當(dāng)于一個集合nSelect id, name from student where id in (nselect id from student where mark 60

11、n)n很多數(shù)據(jù)庫支持： Select id, name from student where (id, name) in (select id, name from student where mark 60 ) 14Tea Break!15修正數(shù)據(jù)的SQL語句n修正數(shù)據(jù)的SQL語句nInsert (插入)nDelete (刪除)nUpdate (修正)16INSERT語句n根本語法:nINSERT INTO table (column1, column2, columnN)nValues ( value1, value2, valueN)n例子:nInsert into student (i

12、d, name, subject, mark)nValues ( 25, 李四, 語文, 60)17INSERT語句 (2)n根據(jù)查詢結(jié)果插入數(shù)據(jù)nInsert into table1 ( nselect column1, column2, , columnN from table2n)nnInsert into student (nselect id, name, subject, mark from student2n)n留意: student表一定要剛好有 id, name, subject, mark四個字段18DELETE 語句 - 刪除數(shù)據(jù)n情況一:全表刪除 nDelete from table1 (刪除table1的全部數(shù)據(jù))n建議用 truncate 語句進(jìn)展刪除ntruncate table studentn情況二: 組合Where子句n Delete from student where id = 223434319UPDATE語句修正數(shù)據(jù)n語法:nU