SQL基礎(chǔ)培訓(xùn)課件

SQL培訓(xùn)2011年6月01日SQL培訓(xùn)目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)SQL介紹-1SQL(StructuredQueryLanguage，結(jié)構(gòu)查詢語言)是一個(gè)功能強(qiáng)大的數(shù)據(jù)庫語言。SQL通常使用于數(shù)據(jù)庫的通訊。ANSI（美國國家標(biāo)準(zhǔn)學(xué)會(huì)）聲稱，SQL是關(guān)系數(shù)據(jù)庫管理系統(tǒng)的標(biāo)準(zhǔn)語言。SQL語句通常用于完成一些數(shù)據(jù)庫的操作任務(wù)，比如在數(shù)據(jù)庫中更新數(shù)據(jù)，或者從數(shù)據(jù)庫中檢索數(shù)據(jù)。使用SQL的常見關(guān)系數(shù)據(jù)庫管理系統(tǒng)有：Oracle、DB2、MicrosoftSQLServer等等。雖然絕大多數(shù)的數(shù)據(jù)庫系統(tǒng)使用SQL，但是它們同樣有它們自立另外的專有擴(kuò)展功能用于它們的系統(tǒng)。但是，標(biāo)準(zhǔn)的SQL命令，比如“Select”、“Insert”、“Update”、“Delete”“Create”和“Drop”常常被用于完成絕大多數(shù)數(shù)據(jù)庫的操作。SQL功能強(qiáng)大，但是概括起來，它可以分成以下幾組：

DML（DataManipulationLanguage，數(shù)據(jù)操作語言）

用于檢索或者修改數(shù)據(jù)；

DDL（DataDefinitionLanguage，數(shù)據(jù)定義語言）

用于定義數(shù)據(jù)的結(jié)構(gòu)，比如創(chuàng)建、修改或者刪除數(shù)據(jù)庫對象；

DCL（DataControlLanguage，數(shù)據(jù)控制語言）

用于定義數(shù)據(jù)庫用戶的權(quán)限。

SQL介紹-1SQL(StructuredQueryLaSQL介紹-2DML[數(shù)據(jù)操作] 可以細(xì)分為以下的幾個(gè)語句

SELECT：用于檢索數(shù)據(jù)；

INSERT：用于增加數(shù)據(jù)到數(shù)據(jù)庫；

UPDATE：用于從數(shù)據(jù)庫中修改現(xiàn)存的數(shù)據(jù)

DELETE：用于從數(shù)據(jù)庫中刪除數(shù)據(jù)。

DDL[數(shù)據(jù)定義] 語句可以用于創(chuàng)建用戶和重建數(shù)據(jù)庫對象

CREATETABLE--創(chuàng)建一個(gè)數(shù)據(jù)庫表

DROPTABLE--從數(shù)據(jù)庫中刪除表

ALTERTABLE--修改數(shù)據(jù)庫表結(jié)構(gòu)

CREATEVIEW--創(chuàng)建一個(gè)視圖

DROPVIEW--從數(shù)據(jù)庫中刪除視圖

CREATEINDEX--為數(shù)據(jù)庫表創(chuàng)建一個(gè)索引

DROPINDEX--從數(shù)據(jù)庫中刪除索引

CREATEPROCEDURE--創(chuàng)建一個(gè)存儲過程

DROPPROCEDURE--從數(shù)據(jù)庫中刪除存儲過程

CREATETRIGGER--創(chuàng)建一個(gè)觸發(fā)器

DROPTRIGGER--從數(shù)據(jù)庫中刪除觸發(fā)器

DCL[數(shù)據(jù)控制] 用于創(chuàng)建關(guān)系用戶訪問以及授權(quán)的對象

GRANT--授予用戶訪問權(quán)限

DENY--拒絕用戶訪問

REVOKE--解除用戶訪問權(quán)限SQL介紹-2DML[數(shù)據(jù)操作] 可以細(xì)分為以下的幾個(gè)語句SQL介紹-3在正式學(xué)習(xí)SQL語言之前，首先讓我們對數(shù)據(jù)庫結(jié)構(gòu)有一個(gè)基本認(rèn)識:數(shù)據(jù)庫的數(shù)據(jù)體系結(jié)構(gòu)基本上是三級結(jié)構(gòu)，但使用術(shù)語與傳統(tǒng)關(guān)系模型術(shù)語不同。在SQL中，關(guān)系模式(模式)稱為“基本表”(basetable)；存儲模式(內(nèi)模式)稱為“存儲文件”(storedfile)；子模式(外模式)稱為“視圖”(view)；元組稱為“行”(row)；屬性稱為“列”(column)。表由行集構(gòu)成，一行是列的序列(集合)，每列與行對應(yīng)一個(gè)數(shù)據(jù)項(xiàng)。視圖是由若干基本表或其他視圖構(gòu)成的表的定義。一個(gè)基本表可以跨一個(gè)或多個(gè)存儲文件，一個(gè)存儲文件也可存放一個(gè)或多個(gè)基本表。每個(gè)存儲文件與外部存儲上一個(gè)物理文件對應(yīng)用戶可以用SQL語句對視圖和基本表進(jìn)行查詢等操作。在用戶角度來看，視圖和基本表是一樣的，沒有區(qū)別，都是關(guān)系(表格)。SQL介紹-3在正式學(xué)習(xí)SQL語言之前，首先讓我們對數(shù)據(jù)庫結(jié)執(zhí)行SQL的工具Oracle:PL/SQLDeveloper/SQLPlusPL/SQLDeveloper：

智能提示，SQL語句抽取，數(shù)據(jù)庫備份，SQL格式化等。安裝需要Oracle的OTN協(xié)議驅(qū)動(dòng)。SQLServer:SqlServerManagerStudioSqlServerManagerStudio:數(shù)據(jù)庫管理、數(shù)據(jù)庫備份、其他常見SQL工作。安裝需要SqlServer的NativeClient驅(qū)動(dòng)。DB2:QuestCentralQuestCentral：數(shù)據(jù)庫備份，其他常見SQL工作。需要安裝DB2Native驅(qū)動(dòng)。[萬能工具]AquaDataStudio基于JDBC。執(zhí)行SQL的工具Oracle:PL/SQLDevelo檢索數(shù)據(jù)SQL的基本語法無條件查詢有條件查詢比較運(yùn)算符算術(shù)運(yùn)算符邏輯運(yùn)算符子查詢多表連接查詢 排序查詢計(jì)算查詢檢索數(shù)據(jù)SQL的基本語法檢索數(shù)據(jù)-基本語法Select基本語法:SELECTselect_list

FROMtable_source

[WHEREsearch_condition]

[GROUPBYgroup_by_expression]

[HAVINGsearch_condition]

[ORDERBYorder_expression[ASC|DESC]]SELECT子句:

指定由查詢返回的列。FROM子句:Selec語句中使用的表、視圖WHERE子句:指定用于限制返回的行的搜索條件。GROUPBY子句:指定用來放置輸出行的組，并且如果SELECT子句<selectlist>中包含聚合函數(shù)，則計(jì)算每組的匯總值。指定GROUPBY時(shí)，選擇列表中任一非聚合表達(dá)式內(nèi)的所有列都應(yīng)包含在GROUPBY列表中，或者GROUPBY表達(dá)式必須與選擇列表表達(dá)式完全匹配。HAVING子句:指定組或聚合的搜索條件。HAVING通常與GROUPBY子句一起使用。ORDERBY子句:指定結(jié)果集的排序。檢索數(shù)據(jù)-基本語法Select基本語法:檢索數(shù)據(jù)-無條件查詢檢索整張表: select*fromtable *號表示選擇表中所有列檢索表中某些列: selectpk_corp,unitcode,unitnamefrombd_corp

從公司基本檔案中選擇pk_corp,公司編碼,公司名稱取消重復(fù)值:

selectdistinctecotypefrombd_corp

將bd_corp中重復(fù)ecotype值在結(jié)果中消除掉. DISTINCT關(guān)鍵字可從SELECT語句的結(jié)果中除去重復(fù)的行。如果沒有指定 DISTINCT，那么將返回所有行，包括重復(fù)的行。

檢索數(shù)據(jù)-無條件查詢檢索整張表:檢索數(shù)據(jù)-有條件查詢有條件查詢就是WHERE子句,指定用于限制返回的行的搜索條件。算術(shù)運(yùn)算符:

算術(shù)運(yùn)算符在兩個(gè)表達(dá)式上執(zhí)行數(shù)學(xué)運(yùn)算，這兩個(gè)表達(dá)式可以是數(shù)字?jǐn)?shù)據(jù) 類型分類的任何數(shù)據(jù)類型。

+（加）。-（減）。*（乘）。/（除）。%（模） 模的含義是除法的整數(shù)余數(shù) 如:selectchinese+englishasallscorefromscorewherename=‘a(chǎn)’

將a的語文成績和英語成績相加得到總分.

其中as是用于為結(jié)果集列指定別名

比較運(yùn)算符:

比較運(yùn)算符測試兩個(gè)表達(dá)式是否相同,可以用于數(shù)值,字符串,日期等

=（等于）>（大于）<（小于）>=（大于或等于）<=（小于或等于） <>（不等于） 如:selectnamefromscorewherechinese<60

將語文成績不及格的查找出來.檢索數(shù)據(jù)-有條件查詢有條件查詢就是WHERE子句,指定用于檢索數(shù)據(jù)-有條件查詢邏輯運(yùn)算符:

邏輯運(yùn)算符對某個(gè)條件進(jìn)行測試，以獲得其真實(shí)情況。邏輯運(yùn)算符和比較 運(yùn)算符一樣，返回帶有TRUE或FALSE值的布爾數(shù)據(jù)類型

運(yùn)算符含義AND如果兩個(gè)布爾表達(dá)式都為TRUE，那么就為TRUE。IN如果操作數(shù)等于表達(dá)式列表中的一個(gè)，那么就為TRUE。LIKE如果操作數(shù)與一種模式相匹配，那么就為TRUE。NOT對任何其它布爾運(yùn)算符的值取反。OR如果兩個(gè)布爾表達(dá)式中的一個(gè)為TRUE，那么就為TRUE。查詢空值: select*frombd_corpwheretaxcodeisnull select*frombd_corpwheretaxcodeisnotnull搜索串匹配: select*frombd_corpwhereunitnamelike'正航%’ select*frombd_corpwhereunitnamelike'%正航%' select*frombd_corpwhereunitnamelike'%正航‘查找一組值: selectpk_corp,unitcode,unitnamefrombd_corpwherepk_corp in('1001','1003')

查找pk_corp等于1001或者等于1003的公司名和編號檢索數(shù)據(jù)-有條件查詢邏輯運(yùn)算符:運(yùn)算符含義AND如果兩個(gè)布爾檢索數(shù)據(jù)-子查詢IN子查詢

通過IN（或NOTIN）引入的子查詢結(jié)果是一列零值或更多值。子查詢返 回結(jié)果之后，外部查詢將利用這些結(jié)果。

select*fromhi_psndoc_eduwherepk_psndocin(selectpk_psndocfrombd_psndocwherepsnname='木普鳳')

查找員工’木普鳳’的學(xué)歷子集信息EXISTS子查詢 使用EXISTS關(guān)鍵字引入一個(gè)子查詢時(shí)，就相當(dāng)于進(jìn)行一次存在測試。外部查詢的WHERE子句測試子查詢返回的行是否存在。子查詢實(shí)際上不產(chǎn)生任何數(shù)據(jù)；它只返回TRUE或FALSE值。

select*fromhi_psndoc_eduwhereexists (selecta.pk_psndocfrombd_psndocawherea.pk_psndoc=hi_psndoc_edu.pk_psndocanda.psnname='木普鳳')

此語句等價(jià)與上面的IN子查詢.

檢索數(shù)據(jù)-子查詢IN子查詢檢索數(shù)據(jù)-多表連接查詢內(nèi)連接[innerjoin]

selectpsn.psncode,psn.psnname,cation,edu.schoolfrombd_psndocaspsninnerjoinhi_psndoc_eduaseduonpsn.pk_psndoc=edu.pk_psndoc查詢結(jié)果返回人員編碼、人員姓名、學(xué)歷、學(xué)校內(nèi)連接指的是兩個(gè)表中結(jié)果集的交集,如圖:

工作信息學(xué)歷子集檢索數(shù)據(jù)-多表連接查詢內(nèi)連接[innerjoin] 工學(xué)歷子集檢索數(shù)據(jù)-多表連接查詢左連接[leftouterjoin] selectpsn.psncode,psn.psnname,cation,edu.schoolfrombd_psndocaspsnleftouterjoinhi_psndoc_eduaseduonpsn.pk_psndoc=edu.pk_psndoc左外連接首先要確保左邊的表數(shù)據(jù)是完全的

工作信息學(xué)歷子集檢索數(shù)據(jù)-多表連接查詢左連接[leftoute工作信息學(xué)歷子集檢索數(shù)據(jù)-多表連接查詢右連接[rigthouterjoin] selectpsn.psncode,psn.psnname,cation,edu.schoolfrombd_psndocaspsnrightouterjoinhi_psndoc_eduaseduonpsn.pk_psndoc=edu.pk_psndoc右外連接和左外連接正好相反,首先要確保右邊表的數(shù)據(jù)完整,左邊表的數(shù)據(jù)

工作信息學(xué)歷子集檢索數(shù)據(jù)-多表連接查詢右連接[rigth檢索數(shù)據(jù)-連接查詢Pk_psndocpsncodepsnnameA００１張穎Ｂ００２郭凱Ｃ００３付繼紅工作信息Pk_psndoc學(xué)歷學(xué)校A本科北京大學(xué)Ｃ專科北京醫(yī)專Ｄ研究生清華大學(xué)學(xué)歷子集思考：針對以下示例數(shù)據(jù)四種連接方式返回結(jié)果是怎樣的？檢索數(shù)據(jù)-連接查詢Pk_psndocpsncodepsnna檢索數(shù)據(jù)-排序查詢語法

[ORDERBY{order_by_expression[ASC|DESC]}

[,...n]]參數(shù)order_by_expression指定要排序的列?？梢詫⑴判蛄兄付榱忻蛄械膭e名（可由表名或視圖名限定）和表達(dá)式，或者指定為代表選擇列表內(nèi)的名稱、別名或表達(dá)式的位置的負(fù)整數(shù)?？芍付ǘ鄠€(gè)排序列。ORDERBY子句中的排序列序列定義排序結(jié)果集的結(jié)構(gòu)。ORDERBY子句可包括未出現(xiàn)在此選擇列表中的項(xiàng)目。然而，如果指定SELECTDISTINCT，或者如果SELECT語句包含UNION運(yùn)算符，則排序列必定出現(xiàn)在選擇列表中。此外，當(dāng)SELECT語句包含UNION運(yùn)算符時(shí)，列名或列的別名必須是在第一選擇列表內(nèi)指定的列名或列的別名。

說明

在ORDERBY子句中不能使用ntext、text和image列。ASC指定按遞增順序，從最低值到最高值對指定列中的值進(jìn)行排序。DESC指定按遞減順序，從最高值到最低值對指定列中的值進(jìn)行排序?？罩当灰暈樽畹偷目赡苤?。selectpk_corp,unitcode,unitnamefrombd_corporderbypk_corp將結(jié)果集按照pk_corp從小到大排序,反之加上desc 檢索數(shù)據(jù)-排序查詢語法檢索數(shù)據(jù)-計(jì)算查詢語法

[GROUPBYgroup_by_expression[,...n]參數(shù)group_by_expression是對其執(zhí)行分組的表達(dá)式。group_by_expression也稱為分組列。group_byexpression可以是列或引用列的非聚合表達(dá)式。在選擇列表內(nèi)定義的列的別名不能用于指定分組列。

說明

text、ntext

和image

類型的列不能用于group_by_expression。SELECTsex,count(pk_psnbasdoc)FROMbd_psnbasdoc

GROUPBYsex 常用的列函數(shù): MAX:取某列最大值

MIN:取某列最小值

AVG:取某列平均值

COUNT:返回集合中項(xiàng)目的數(shù)量 檢索數(shù)據(jù)-計(jì)算查詢語法插入數(shù)據(jù)基本語法:

Insertinto表名[（字段列表）]

{values（值列表）|select_statement}

如:Insertintobd_defdoc(doccode,docname,docsystype,dr,pk_corp,pk_defdoc,pk_defdoc1,pk_defdoclist,sealflag,ts)Values(‘CM001’,'固定期限、',0,0,'0001','0001AA10W',null,'0001CM001',null,'2003-10-2815:36:32')InsertIntobd_deptdoc_xselect*frombe_deptdoc

插入數(shù)據(jù)基本語法:

Insertinto表名更新數(shù)據(jù)基本語法:

Update{table_name|view_name}

Set{column_list}=expression[，...]

[Whereclause]

Update語句用來修改表中已存在的數(shù)據(jù)。Update語句既可以一次修改一行數(shù)據(jù)，也可以一次修改許多行，甚至可以一次修改表中的全部數(shù)據(jù)。 Update語句使用Where子句指定要修改的行，使用Set子句給出新的 數(shù)據(jù)。新數(shù)據(jù)可以是常量，也可以是指定的表達(dá)式，還可以是使用From子句來自其他表的數(shù)據(jù)。例子:

Updatebd_psnbasdoc Setsex=‘男’Wherepsnname=’黃紅’

更新數(shù)據(jù)基本語法:

Update{table_name|v刪除數(shù)據(jù)基本語法:DeleteFromtable_nameWheresearch_conditions

刪除數(shù)據(jù)使用Delete語句。Delete語句可以一次從一個(gè)表中刪除一條或者多條數(shù)據(jù)行。例子:Deletebd_corpWhereunitcodelike‘1010%’ 在刪除表中的全部數(shù)據(jù)時(shí)，還可以使用TruncateTable語句。Truncate Table語句和Delete語句都可以將表中的全部數(shù)據(jù)刪除，但是，兩條語 句又有不同的特點(diǎn)。當(dāng)用戶使用Delete語句刪除數(shù)據(jù)時(shí)，被刪除的數(shù)據(jù) 要記錄在日志中。并不將對數(shù)據(jù)的變化記錄在日志中。因此，使用TruncateTablebd_corp語句刪除記錄的速度快于使用Deletefrombd_corp語句刪除表中記錄的速度。 Truncate是DDL語句，DDL語句執(zhí)行后，隱含地提交一個(gè)事務(wù)，而Delete是DML語句。刪除數(shù)據(jù)基本語法:練習(xí)題查詢公司編碼為001的公司PK值.[公司基本檔案表]按照公司編碼對bd_corp表進(jìn)行倒序排序.[公司基本檔案表]取公司編碼列值包含001的記錄 [公司基本檔案表]取出不重復(fù)的公司簡稱.[公司基本檔案]如何找出公司簡稱相同的記錄.[公司基本檔案]找出人員編碼psncode=‘0001’的工作信息將公司編碼=001的公司簡稱更改為原簡稱+”new”練習(xí)題查詢公司編碼為001的公司PK值.[公司基本檔案表]目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)NC中間件SQL翻譯引擎NC的中間件SQL翻譯引擎

在NC的EJB層，存在著一個(gè)SQL翻譯引擎。此SQL翻譯引擎的主要工作是把前前端應(yīng)用發(fā)起的MSSQLServer的SQL語句翻譯成可以在底層數(shù)據(jù)庫運(yùn)行的語句。此翻譯工作主要體現(xiàn)在SQL函數(shù)的翻譯上。

[注意1]：并非所有的SQL語句都能正確翻譯成可以在底層數(shù)據(jù)庫上可以運(yùn)行的SQL，導(dǎo)致一些非常有用的函數(shù)和語法并不能使用。例如Update…From，Merger…Into….語法，ISDate函數(shù)等。

NC中間件SQL翻譯引擎NC的中間件SQL翻譯引擎NC中間件SQL執(zhí)行工具NC中間件SQL執(zhí)行工具：

位置：NC_HOME/bin/dbtRunSQL.bat。

使用方式:在文件dbtRunSQL.ini中書寫需要執(zhí)行的SQL，按照一行一條完整的SQL解析執(zhí)行。配置完成之后，在命令行提示符下，執(zhí)行：dbtRunSQL.batdsName ./dbtRunSQL.shdsName

NC中間件SQL執(zhí)行工具NC中間件SQL執(zhí)行工具：目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)目錄NC產(chǎn)品中的SQL常見SQL問題SQL基礎(chǔ)常見問題問題1：含有層級關(guān)系的樹形參照無法顯示。[原因分析]極有可能是層級關(guān)系字段出現(xiàn)循環(huán)引用導(dǎo)致。[判定方法]OracleConnectby語法。例如：

常見問題問題1：含有層級關(guān)系的樹形參照無法顯示。常見問題問題2：倒入的數(shù)據(jù)日期存在格式的問題。[原因分析]出現(xiàn)2009-02-31或者長度不夠10位的非法日期字符串。[判定方法]SQLServer中的IsDate函數(shù)。在Oracle中請使用自定義IsDate函數(shù)。

常見問題問題2：倒入的數(shù)據(jù)日期存在格式的問題。常見問題問題3：使用其他表中的數(shù)據(jù)快速更新目標(biāo)表中的數(shù)據(jù)。[方法]：SqlServer中使用Update……From…..語句.DB2和Oracle中使用:Mergeinto……Using語句.[舉例]：MERGEINTOarchivear

USING(SELECTactivity,descriptionFROMactivities)ac

ON(ar.activity=ac.activity)

WHENMATCHEDTHEN

UPDATESETdescription=ac.description

WHENNOTMATCHEDTHEN

INSERT(activity,description)VALUES(ac.activity,ac.description)

常見問題問題3：使用其他表中的數(shù)據(jù)快速更新目標(biāo)表中的數(shù)據(jù)。常見問題問題4：NULL值的比較。[問題根源]：NULL本身代表不確定的內(nèi)容。即，NULL既可能代表1也可能代表3，正是由于NULL的不確定性，故NULL和其他任何值進(jìn)行比較永遠(yuǎn)返回False.即使兩個(gè)NULL值進(jìn)行比較。對待NULL值各數(shù)據(jù)庫的奇特之處:SQLSERVER: