SQL(結(jié)構(gòu)化查詢語言)入門課件

1、數(shù)據(jù)庫入門MySQL+SQL 第1頁，共54頁。MySQL簡介數(shù)據(jù)庫簡介Oracle、 SQLServer、 DB2MySQL(Apache+Mysql+Php)SyBaseMySQL數(shù)據(jù)庫的安裝和配置第2頁，共54頁。數(shù)據(jù)庫數(shù) 據(jù) 表存儲過程視 圖. 訂單 客戶 產(chǎn)品 產(chǎn)品數(shù)據(jù)庫表第3頁，共54頁。數(shù)據(jù)表行(記錄)列(字段)第4頁，共54頁。數(shù)據(jù)在表中的存放編號姓名年齡民族部門1王濤33漢族人事管理部2李梅27漢族人事管理部1127李梅21133王濤1部門編碼民族編碼年齡姓名編號回族2漢族1民族民族編碼市場營銷部2人事管理部1部門部門編碼存在冗余為減少數(shù)據(jù)查找的麻煩，允許數(shù)據(jù)有一定的冗余第5

2、頁，共54頁。數(shù)據(jù)存儲的完整性Roll NumberNameAddressBookTaken12Alicia Ruth12, Temple StreetAC09114Jason Darren123, Sunset Blvd.AC04315Mary Beth32, Golden AvenueAC02112Alicia Ruth12, Temple StreetAC04312Alicia Ruth12, Tmple StreetAC01115Mary Beth33, Golden AvenueAC011不同的地址!存在不正確、不準(zhǔn)確的數(shù)據(jù)，數(shù)據(jù)庫“失去了完整性”姓名第6頁，共54頁。數(shù)據(jù)的完整性數(shù)

3、據(jù)完整性+=可靠性準(zhǔn)確性第7頁，共54頁。完整性分類數(shù)據(jù)實體完整性字段完整性引用完整性自定義完整性第8頁，共54頁。數(shù)據(jù)實體完整性河南新鄉(xiāng)趙可以0010016河南新鄉(xiāng)張麗鵑0010015江西南昌雷銅0010014湖南新田吳蘭0010013山東定陶李山0010012.地址姓名學(xué)號江西南昌雷銅0010014約束方法：唯一約束、主鍵約束、標(biāo)識列第9頁，共54頁。字段完整性河南新鄉(xiāng)趙可以0010016河南新鄉(xiāng)張麗鵑0010015江西南昌雷銅0010014湖南新田吳蘭0010013山東定陶李山0010012.地址姓名學(xué)號湖北江門李亮870000000約束方法：限制數(shù)據(jù)類型、檢查約束、外鍵約束、默認(rèn)值、非

4、空約束第10頁，共54頁。引用完整性河南新鄉(xiāng)趙可以0010016河南新鄉(xiāng)張麗鵑0010015江西南昌雷銅0010014湖南新田吳蘭0010013山東定陶李山0010012地址姓名學(xué)號980010021數(shù)學(xué)約束方法：外鍵約束科目學(xué)號分?jǐn)?shù)數(shù)學(xué)001001288數(shù)學(xué)001001374語文001001267語文001001381數(shù)學(xué)001001698第11頁，共54頁。自定義完整性AV121322喬峰CV0016AV372133玄痛CV0015AV378291沙悟凈AV0014AV378290豬悟能AV0013AV378289孫悟空AV0012.會員證用戶姓名用戶編號約束方法：規(guī)則、存儲過程、觸發(fā)器帳

5、號姓名信用.00192孫悟空700288豬悟能612333段譽(yù)890111虛竹4093000岳不群-10觸發(fā)器：檢查信用值第12頁，共54頁。完整性包括輸入的類型是否正確？年齡必須是數(shù)字輸入的格式是否正確？身份證號碼必須是18位是否在允許的范圍內(nèi)？性別只能是”男”或者”女”是否存在重復(fù)輸入？學(xué)員信息輸入了兩次是否符合其他特定要求？信譽(yù)值大于5的用戶才能夠加入會員列表列值要求（約束）整行要求（約束）第13頁，共54頁。數(shù)據(jù)庫服務(wù)器、數(shù)據(jù)庫和表的關(guān)系MySQL程序DB1DB2表1表3表2Client程序所謂安裝數(shù)據(jù)庫服務(wù)器，只是在機(jī)器上裝了一個數(shù)據(jù)庫管理程序，這個管理程序可以管理多個數(shù)據(jù)庫，一般開

6、發(fā)人員會針對每一個應(yīng)用創(chuàng)建一個數(shù)據(jù)庫。為保存應(yīng)用中的數(shù)據(jù)，一般會在數(shù)據(jù)庫創(chuàng)建多個表，以保存程序中的數(shù)據(jù)。數(shù)據(jù)庫服務(wù)器、數(shù)據(jù)庫和表的關(guān)系如圖所示：第14頁，共54頁。數(shù)據(jù)在數(shù)據(jù)庫中的存儲方式id=1name=“l(fā)isi”age=23User對象idnameage1lisi232wang24User表id=2name=“wang”age=24行(row)列(column)表的一行稱之為一條記錄表中一條記錄對應(yīng)一個對象的數(shù)據(jù)User對象第15頁，共54頁。phpMyAdminhttp:/localhost/phpMyAdmin/用戶名:root密碼:123第16頁，共54頁。創(chuàng)建數(shù)據(jù)庫CREATE

7、DATABASE IF NOT EXISTS db_name create_specification , create_specification . create_specification: DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name CHARACTER SET：指定數(shù)據(jù)庫采用的字符集COLLATE：指定數(shù)據(jù)庫字符集的比較方式、規(guī)則，比如排序練習(xí)：創(chuàng)建一個名稱為mydb1的數(shù)據(jù)庫。創(chuàng)建一個使用utf8字符集的mydb2數(shù)據(jù)庫。創(chuàng)建一個使用gb2312字符集的mydb3數(shù)據(jù)庫。第17頁，共54頁。查

8、看、刪除數(shù)據(jù)庫顯示數(shù)據(jù)庫語句：SHOW DATABASES顯示數(shù)據(jù)庫創(chuàng)建語句：SHOW CREATE DATABASE db_name使用數(shù)據(jù)庫USE db_name數(shù)據(jù)庫刪除語句：DROP DATABASE IF EXISTS db_name 練習(xí)：查看當(dāng)前數(shù)據(jù)庫服務(wù)器中的所有數(shù)據(jù)庫查看前面創(chuàng)建的mydb2數(shù)據(jù)庫的定義信息刪除前面創(chuàng)建的mydb1數(shù)據(jù)庫第18頁，共54頁。修改、備份、恢復(fù)數(shù)據(jù)庫數(shù)據(jù)ALTER DATABASE IF NOT EXISTS db_name alter_specification , alter_specification . alter_specificatio

9、n: DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name備份數(shù)據(jù)庫表中的數(shù)據(jù)(退到window命令行窗口)mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 D:文件名.sql恢復(fù)數(shù)據(jù)庫(進(jìn)入mysql控制臺)source D:文件名.sql練習(xí) 查看服務(wù)器中的數(shù)據(jù)庫，并把其中mydb3庫的字符集修改為utf8。 備份mydb3庫中的數(shù)據(jù)，并恢復(fù)。第19頁，共54頁。創(chuàng)建表(基本語句)CREATE TABLE table_name(field1 datatype,field2 datatype,field3 da

10、tatype,)character set 字符集 collate 校對規(guī)則field：指定列名datatype：指定列類型注意：創(chuàng)建表時，要根據(jù)需保存的數(shù)據(jù)創(chuàng)建相應(yīng)的列，并根據(jù)數(shù)據(jù)的類型定義相應(yīng)的列類型。例：user對象id int name stringpassword stringbirthday dateIdName Passwordbirthday注意：創(chuàng)建表前，要先使用use dbname語句使用庫。第20頁，共54頁。MySQL常用數(shù)據(jù)類型分類備注和說明數(shù)據(jù)類型說明二進(jìn)制數(shù)據(jù)類型存儲非子符和文本的數(shù)據(jù)BLOB可用來存儲圖像文本數(shù)據(jù)類型字符數(shù)據(jù)包括任意字母、符號或數(shù)字字符的組合ch

11、ar固定長度的非 Unicode 字符數(shù)據(jù)varchar可變長度非 Unicode 數(shù)據(jù)text存儲長文本信息日期和時間日期和時間在單引號內(nèi)輸入time時間date日期datetime 日期和時間數(shù)值型數(shù)據(jù)該數(shù)據(jù)僅包含數(shù)字，包括正數(shù)、負(fù)數(shù)以及浮點數(shù)intsmallint整數(shù)floatdouble浮點數(shù)貨幣數(shù)據(jù)類型用于財務(wù)數(shù)據(jù)decimal定點數(shù)Bit數(shù)據(jù)類型表示是/否的數(shù)據(jù)bit存儲布爾數(shù)據(jù)類型第21頁，共54頁。PRIMARY KEY 主碼約束(主鍵)UNIQUE唯一性約束NOT NULL非空值約束AUTO_INCREMENT用于整數(shù)列默認(rèn)自增1UNSIGNED 無符號整數(shù)DEFAULT d

12、efault_value默認(rèn)值約束DEFAULT cur_timestamp 創(chuàng)建新記錄時默認(rèn)保存當(dāng)前時間（僅適用timestamp數(shù)據(jù)列）ON UPDATE cur_timestamp 修改記錄時默認(rèn)保存當(dāng)前時間（僅適用timestamp數(shù)據(jù)列）CHARACTER SET name 指定字符集（僅適用字符串）常見完整性約束第22頁，共54頁。AUTO_INCREMENT 自動標(biāo)識列，在需要產(chǎn)生唯一標(biāo)志符號或者順序值時候，可用此屬性。值一般從1開始，每行增加1，在插入NULL到一個AUTO_INCREMENT列時，MySQL會插入一個比該列中當(dāng)前最大值大1 的值，一個表中最多能有一個有此屬性的

13、列。對于想使用此屬性的列應(yīng)該定義為NOT NULL,并定義為PRIMARY KEY 或者定義為UNIQUE鍵。舉例：create table t(id int auto_increment not null primary key);UNSIGNED 無符號，此屬性禁用負(fù)值，將數(shù)值的取值范圍從零開始。第23頁，共54頁。NULL和NOT NULL默認(rèn)為NULL，即插入值時沒有在此字段插入值時自動填NULL，如果指定了NOT NULL，則必須在插入值時在此字段添入值，不允許插入NULL值。DEFAULT可以通過此屬性來指定一個缺省值，即如果沒有在此列添加值，那么默認(rèn)添加DEFAULT后指定值。第

14、24頁，共54頁。修改表使用 ALTER TABLE 語句追加, 修改, 或刪除列的語法.ALTER TABLE tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableDROP (column);修改表的名稱：Rename table 原表名 to 新表名修改表的字符集：alter table student character set utf8;第2

15、5頁，共54頁。練習(xí)：建立一個學(xué)生信息表(user)定義列sno學(xué)號，類型為5位定長字符串，非空，主鍵定義列sname姓名，類型為8位定長字符串，非空定義列ssex性別，取值1或0定義列sage年齡，類型為短整型定義列sdept系名，數(shù)據(jù)類型為20位變長字符串練習(xí) ：建立一個課程信息表（course）定義列courseid課程號，類型為整型，非空，自動增長，主鍵定義列cname課程名，類型為16位變長字符串，非空練習(xí) ：建立一個學(xué)生考試成績信息表(sc)定義列g(shù)rade成績，類型為整型要求把表字段補(bǔ)充完整，可以通過學(xué)號查看學(xué)生相關(guān)課程的成績第26頁，共54頁。外鍵的方法ALTER TABLE

16、表名 ADD FOREIGN KEY (字段名) REFERENCES 表名(字段名)ALTER TABLE sc ADD FOREIGN KEY (uid) REFERENCES user(id)第27頁，共54頁。數(shù)據(jù)庫CRUD語句Insert語句 (增加數(shù)據(jù))Update語句 (更新數(shù)據(jù))Delete語句 (刪除數(shù)據(jù))Select語句(查找數(shù)據(jù))第28頁，共54頁。Insert語句employee.sqlINSERT INTOtable (column , column.)VALUES(value , value.);使用 INSERT 語句向表中插入數(shù)據(jù)。插入的數(shù)據(jù)應(yīng)與字段的數(shù)據(jù)類型相

17、同。數(shù)據(jù)的大小應(yīng)在列的規(guī)定范圍內(nèi)，例如：不能將一個長度為80的字符串加入到長度為40的列中。在values中列出的數(shù)據(jù)位置必須與被加入的列的排列位置相對應(yīng)。字符和日期型數(shù)據(jù)應(yīng)包含在單引號中。插入空值，不指定或insert into table value(null)第29頁，共54頁。Insert語句練習(xí)練習(xí)：使用insert語句向表user中插入三個學(xué)生的信息（英中文）。sno學(xué)號，類型為5位定長字符串，非空，主鍵sname姓名，類型為8位定長字符串，非空ssex性別，字符型 取值男或女sage年齡，類型為短整型sdept系名，數(shù)據(jù)類型為20位變長字符串第30頁，共54頁。Update語句U

18、PDATE tbl_name SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition 使用 update語句修改表中數(shù)據(jù)。UPDATE語法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要給予哪些值。WHERE子句指定應(yīng)更新哪些行。如沒有WHERE子句，則更新所有的行。第31頁，共54頁。Update語句練習(xí)練習(xí)：創(chuàng)建的user表中修改表中的紀(jì)錄。要求將所有學(xué)生系名修改為計算機(jī)工程系。將所有同學(xué)的年齡在原有基礎(chǔ)上增加2。將張三同學(xué)的年齡減3第32頁，共54頁。Delete語句delete from tbl_nam

19、e WHERE where_definition 使用 delete語句刪除表中數(shù)據(jù)。如果不使用where子句，將刪除表中所有數(shù)據(jù)。Delete語句不能刪除某一列的值（可使用update）使用delete語句僅刪除記錄，不刪除表本身。如要刪除表，使用drop table語句。同insert和update一樣，從一個表中刪除記錄將引起其它表的參照完整性問題，在修改數(shù)據(jù)庫數(shù)據(jù)時，頭腦中應(yīng)該始終不要忘記這個潛在的問題。刪除表中數(shù)據(jù)也可使用TRUNCATE TABLE 語句，它和delete有所不同。TRUNCATE(復(fù)制表結(jié)構(gòu)-銷毀表-重建表結(jié)構(gòu)）DELETE(逐行刪除記錄)第33頁，共54頁。De

20、lete語句練習(xí)刪除user表中名稱姓名為zhangsan的記錄。刪除course表中所有記錄。刪除user表中名稱性別為男,且年齡大于20的記錄。第34頁，共54頁。Select語句(1)student.sqlSELECT DISTINCT *|column1, column2. column3.FROMtable;Select 指定查詢哪些列的數(shù)據(jù)。column指定列名。*號代表查詢所有列(開發(fā)中建議不要使用)。From指定查詢哪張表。DISTINCT可選，指顯示結(jié)果時，是否剔除重復(fù)數(shù)據(jù)基本select語句第35頁，共54頁。Select語句(1)練習(xí)：查詢表中所有學(xué)生的信息。查詢表中所有

21、學(xué)生的姓名和對應(yīng)的年齡成績。查詢表中所有學(xué)生的性別過濾表中重復(fù)數(shù)據(jù)。第36頁，共54頁。Select語句()SELECT *|column1expression, column2expression，.FROMtable;在select語句中可使用表達(dá)式對查詢的列進(jìn)行運(yùn)算在select語句中可使用as語句SELECT column as 別名 from 表名;第37頁，共54頁。Select語句()練習(xí)查詢sc表,在所有學(xué)生分?jǐn)?shù)上加4分特長分。統(tǒng)計所有學(xué)生所有課程的平均分。sum avg求編號1的課程的分?jǐn)?shù)和使用別名表示學(xué)生分?jǐn)?shù)。第38頁，共54頁。Select語句(3)使用where子句，進(jìn)

22、行過濾查詢。查詢所有同學(xué)的成績查詢姓名為張三的學(xué)生成績第39頁，共54頁。Select語句()在where子句中經(jīng)常使用的運(yùn)算符比較運(yùn)算符 = = 大于、小于、大于(小于)等于、不等于BETWEEN .AND.顯示在某一區(qū)間的值IN(set)顯示在in列表中的值，例：in(100,200)LIKE 張pattern模糊查詢IS NULL/IS NOT NULL判斷是否為空邏輯運(yùn)算符and多個條件同時成立or多個條件任一成立not不成立，例：where not(salary100);Like語句中，% 代表零個或多個任意字符，_ 代表一個字符，例first_name like _a%;第40頁，

23、共54頁。Select語句(4)查詢有分?jǐn)?shù)在 8090之間的同學(xué)的學(xué)號。查詢所有姓李的學(xué)生信息。第41頁，共54頁。Select語句()SELECT column1, column2. column3.FROMtable;order by column asc|descOrder by 指定排序的列，排序的列即可是表中的列名，也可以是select 語句后指定的列名。Asc 升序、Desc 降序ORDER BY 子句應(yīng)位于SELECT語句的結(jié)尾。練習(xí)：對成績排序后輸出。對學(xué)生信息按照年齡排序輸出。使用order by 子句排序查詢結(jié)果。第42頁，共54頁。合計函數(shù)count()Select co

24、unt(*)|count(列名) from tablenameWHERE where_definition 練習(xí)：統(tǒng)計共有多少學(xué)生？Count(列名)返回某一列，行的總數(shù)，除null外第43頁，共54頁。合計函數(shù)sum()Select sum(列名),sum(列名) from tablenameWHERE where_definition 練習(xí)：統(tǒng)計一個課程編號是1的總成績。注意：sum僅對數(shù)值起作用，否則會報錯。Sum函數(shù)返回滿足where條件的行的和第44頁，共54頁。平均函數(shù)avg()Select sum(列名),sum(列名) from tablenameWHERE where_def

25、inition 練習(xí)：求一所有同學(xué)平均分。AVG函數(shù)返回滿足where條件的一列的平均值第45頁，共54頁。最大最小值函數(shù)max()/min()Select max(列名)from tablenameWHERE where_definition 練習(xí)：求班級最高分和最低分。（數(shù)值范圍在統(tǒng)計中特別有用）Max/min函數(shù)返回滿足where條件的一列的最大/最小值第46頁，共54頁。Select語句(6)SELECT column1, column2. column3. FROMtable;group by column練習(xí)：使用group by 子句對列進(jìn)行分組SELECT column1, c

26、olumn2. column3.FROMtable;group by column having .使用having 子句組過濾練習(xí)：Having和where均可實現(xiàn)過濾，但在having可以使用合計函數(shù),having通常跟在group by后，它作用于組。第47頁，共54頁。定義表的約束(數(shù)據(jù)安全的保證)定義主鍵約束 primary key:不允許為空，不允許重復(fù)刪除主鍵：alter table tablename drop primary key ;定義主鍵自動增長 auto_increment(MySQL特有/UUID類生成)定義唯一約束 unique定義非空約束 not null定義外

27、鍵約束constraint ordersid_FK foreign key(ordersid) references orders(id),第48頁，共54頁。表的關(guān)聯(lián)設(shè)計（實體關(guān)系的體現(xiàn)）一對一人和身份證（外鍵唯一非空/主鍵外鍵合二唯一）一對多/多對一部門和員工多對多（中間表)老師和學(xué)生1號老師所對應(yīng)的學(xué)員1號學(xué)員所對應(yīng)的老師第49頁，共54頁。Tip：mysql中文亂碼mysql有六處使用了字符集，分別為：client 、connection、database、results、server 、system。client是客戶端使用的字符集。 connection是連接數(shù)據(jù)庫的字符集設(shè)置類型

28、，如果程序沒有指明連接數(shù)據(jù)庫使用的字符集類型就按照服務(wù)器端默認(rèn)的字符集設(shè)置。 database是數(shù)據(jù)庫服務(wù)器中某個庫使用的字符集設(shè)定，如果建庫時沒有指明，將使用服務(wù)器安裝時指定的字符集設(shè)置。 results是數(shù)據(jù)庫給客戶端返回時使用的字符集設(shè)定，如果沒有指明，使用服務(wù)器默認(rèn)的字符集。 server是服務(wù)器安裝時指定的默認(rèn)字符集設(shè)定/utf8。 system是數(shù)據(jù)庫系統(tǒng)使用的字符集設(shè)定。第50頁，共54頁。附錄：時間日期相關(guān)函數(shù)參考ADDTIME (date2 ,time_interval )將time_interval加到date2CURRENT_DATE ( )當(dāng)前日期CURRENT_TIM

29、E ( )當(dāng)前時間CURRENT_TIMESTAMP ( )當(dāng)前時間戳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 ( )當(dāng)前時間YEAR|Month|DAY (datetime )年月日示例：select addtime(02:30:30,01:01:01); 注意：字符串、時間日期的引號問題 select date_add(entry_date,INTERVAL 2 year) from student;增加兩年select addtime(time,1 1-1 10:09:09) from student;時間戳上增加，注意年后沒有-第5