1、n數據庫基礎指導培訓SQL語言指導培訓北明軟件員工培訓講義2n數據的SQL語句n修改數據的SQL語句n數據定義的SQL語句3查詢數據的SQL語句nSELECT 語句4查詢數據的SQL語句-SELECTn本章講述如何使用SELECT語句的五個子句。n在SELECT語句中，你必須按以下順序使用這些子句 ：nSELECT子句nFROM子句nWHERE子句nORDER BY子句SELECT 字段1,字段2字段n FROM 表1,表 2表n5SELECT語句基礎n可以用多種方法來查詢數據庫中的一個表。你可以裁剪SELECT語句來達到以下目的：n檢索所有的或指定的列select id, name from

2、 student （查詢編號，姓名)select * from student(查詢所有字段)n對檢索所得的數據執行計算或其它函數select id, name, sum(mark) from student group by id, name(按照編號、姓名為分組，對“成績”進行匯總)(簡單的說，就是查詢每個學生的總成績)6Where子句nWhere 子句用于限制數據nselect id, name from student where id=222233444上句查找出編號為222233444的學生的學生編號和姓名nselect id,name from student where nam

3、e like 張%上句查找出姓名為“張”開頭的學生的學生編號和姓名其它約束方法：nWhere column1 like _b% (部分匹配, %為通配符,表示=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、, =, , , .)7Where子句(2)n多條件限制1) select id, name from student where id 23456 and name like 張%2) select id, name from student where id 23456 or name like 張%3) select id, name from student where (id 23456 or name like 張%) and mark60不要考慮條件匹配的先天優先級，在你想優先匹配的地方加上括號，永遠是對的8Order by 子句 (用于排序)n單列排序nSelect id, n

5、ame from student order by id asc (升序)nSelect id, name from student order by id desc (降序)n多列排序select id, name ,mark from student order by mark desc , id asc(先按照成績降序，如果成績相同，則按照編號升序)asc可以省略，因為默認是按照升序排列但是desc不可以如: select id, name from student order by id (升序)9Group by (用于匯總計算)nGROUP 中文意思： 分組nselect id,

6、name,sum(mark) from student group by id, name（上句按照id,name 為分組，計算mark的匯總值)nSelect id, name, avg(mark),sum(mark) from student group by id, name(上句按照id,name 為分組， 計算mark的平均值)nSelect id, name, avg(mark) from student group by id(上一句語法是錯誤的， 因為有一個字段name在分組之外，數據庫不知道怎么歸類計算)nSelect id, name, mark1+mark2 from s

7、tudent group by id,name(上一句也是錯誤的，mark1+mark2是兩個字段間的計算，沒有匯總計算，分組沒有意義)10UNION 組合查詢結果1) Union 組合查詢結果,合并重復記錄Select id, name from studentUnionSelect id, name from teacher2) Union All 組合查詢結果，不合并重復記錄Select id, name from studentUnion AllSelect id, name from teacher11多表關聯(1)n內連接(inner join)n寫法一: select a.id,

8、 , 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 （寫法二是標準語法)12多表關聯(2)n外連接(outer join)n左外連接(left outer join)寫法一: select a.id, , b.id, from student a, teacher b where a.teche

9、r_id *=b.id寫法二: select a.id, , b.id, from student a left outer join techer b on a.techer_id=b.id （寫法二是標準語法)n右外連接(right outer join)寫法一: select a.id, , b.id, from student a, teacher b where a.techer_id =+ b.id寫法二: select a.id, , b.id, from student a right outer j

10、oin techer b on a.techer_id=b.id（寫法二是標準語法)13子查詢n子查詢的結果相當于一張tableSelect A.id, A.name, sum(A.mark) from ( select id, name, mark from student where mark 60) A Group by A.id, A.namen子查詢的結果相當于一個集合Select id, name from student where id in (select id from student where mark 60)很多數據庫支持： Select id, name from

11、student where (id, name) in (select id, name from student where mark 60 ) 14Tea Break!15修改數據的SQL語句n修改數據的SQL語句nInsert (插入)nDelete (刪除)nUpdate (修改)16INSERT語句n基本語法:INSERT INTO table (column1, column2, columnN)Values ( value1, value2, valueN)例子:Insert into student (id, name, subject, mark)Values ( 25, 李

12、四, 語文, 60)17INSERT語句 (2)n根據查詢結果插入數據Insert into table1 ( select column1, column2, , columnN from table2)Insert into student (select id, name, subject, mark from student2)注意: student表一定要剛好有 id, name, subject, mark四個字段18DELETE 語句 - 刪除數據n情況一:全表刪除 Delete from table1 (刪除table1的全部數據)建議用 truncate 語句進行刪除truncate table studentn情況二: 組合Where子句 Delete from student where id = 223434319UPDATE語句修改數據n語法: