第一部分web sql入門

1、北京傳智播客教育 SQL 入門 北京傳智播客教育 常見(jiàn)關(guān)系化數(shù)據(jù)庫(kù) 北京傳智播客教育 MySQL簡(jiǎn)介MySQL數(shù)據(jù)庫(kù)的安裝和配置配置Mysql按照bin目錄到Path中使用命令行窗口連接MYSQL數(shù)據(jù)庫(kù)mysql u用戶名 p密碼MySQL數(shù)據(jù)庫(kù)Root密碼重置（見(jiàn)資料）北京傳智播客教育 MySQL數(shù)據(jù)庫(kù)服務(wù)器、數(shù)據(jù)庫(kù)和表的關(guān)系MySQLDBDB表表表Client所謂安裝數(shù)據(jù)庫(kù)服務(wù)器，只是在機(jī)器上裝了一個(gè)數(shù)據(jù)庫(kù)管理程序，這個(gè)管理程序可以管理多個(gè)數(shù)據(jù)庫(kù)，一般開發(fā)人員會(huì)針對(duì)每一個(gè)應(yīng)用創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)。為保存應(yīng)用中實(shí)體的數(shù)據(jù)，一般會(huì)在數(shù)據(jù)庫(kù)創(chuàng)建多個(gè)表，以保存程序中實(shí)體的數(shù)據(jù)。數(shù)據(jù)庫(kù)服務(wù)器、數(shù)據(jù)庫(kù)和表的

2、關(guān)系如圖所示：北京傳智播客教育 數(shù)據(jù)在數(shù)據(jù)庫(kù)中的存儲(chǔ)方式id=1name=“l(fā)isi”age=23User對(duì)象idnameage1lisi232wang24User表id=2name=“wang”age=24行(row)列(column)表的一行稱之為一條記錄表中一條記錄對(duì)應(yīng)一個(gè)java對(duì)象的數(shù)據(jù)User對(duì)象北京傳智播客教育 SQL語(yǔ)言 - 只要是關(guān)系型數(shù)據(jù)庫(kù)都支持它Structured Query Language, 結(jié)構(gòu)化查詢語(yǔ)言非過(guò)程性語(yǔ)言為加強(qiáng)SQL的語(yǔ)言能力，各廠商增強(qiáng)了過(guò)程性語(yǔ)言的特征如Oracle的PL/SQL 過(guò)程性處理能力SQL Server、Sybase的T-SQLSQL是

3、用來(lái)存取關(guān)系數(shù)據(jù)庫(kù)的語(yǔ)言，具有查詢、操縱、定義和控制關(guān)系型數(shù)據(jù)庫(kù)的四方面功能北京傳智播客教育 SQL分類DDL （數(shù)據(jù)定義語(yǔ)言）數(shù)據(jù)定義語(yǔ)言 - Data Definition Language用來(lái)定義數(shù)據(jù)庫(kù)的對(duì)象，如數(shù)據(jù)表、視圖、索引等DML （數(shù)據(jù)操縱語(yǔ)言）數(shù)據(jù)處理語(yǔ)言 - Data Manipulation Language在數(shù)據(jù)庫(kù)表中更新，增加和刪除記錄如 update， insert， deleteDCL （數(shù)據(jù)控制語(yǔ)言）數(shù)據(jù)控制語(yǔ)言 Data Control Language指用于設(shè)置用戶權(quán)限和控制事務(wù)語(yǔ)句如grant，revoke，ifelse，while，begin trans

4、actionDQL （數(shù)據(jù)查詢語(yǔ)言）數(shù)據(jù)查詢語(yǔ)言 Data Query Languageselect北京傳智播客教育 創(chuàng)建數(shù)據(jù)庫(kù)CREATE 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ù)庫(kù)采用的字符集COLLATE：指定數(shù)據(jù)庫(kù)字符集的比較方式練習(xí)：創(chuàng)建一個(gè)名稱為mydb1的

5、數(shù)據(jù)庫(kù)。創(chuàng)建一個(gè)使用utf8字符集的mydb2數(shù)據(jù)庫(kù)。創(chuàng)建一個(gè)使用utf8字符集，并帶校對(duì)規(guī)則的mydb3數(shù)據(jù)庫(kù)。北京傳智播客教育 查看、刪除數(shù)據(jù)庫(kù)顯示數(shù)據(jù)庫(kù)語(yǔ)句：SHOW DATABASES顯示數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句：SHOW CREATE DATABASE db_name 數(shù)據(jù)庫(kù)刪除語(yǔ)句：DROP DATABASE IF EXISTS db_name 練習(xí)：查看當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器中的所有數(shù)據(jù)庫(kù)查看前面創(chuàng)建的mydb2數(shù)據(jù)庫(kù)的定義信息刪除前面創(chuàng)建的mydb1數(shù)據(jù)庫(kù)北京傳智播客教育 修改數(shù)據(jù)庫(kù)ALTER DATABASE IF NOT EXISTS db_name alter_specification

6、 , alter_specification . alter_specification: DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name切換數(shù)據(jù)庫(kù) use db_name;查看當(dāng)前使用的數(shù)據(jù)庫(kù) select database();練習(xí) 查看服務(wù)器中的數(shù)據(jù)庫(kù)，并把其中某一個(gè)庫(kù)的字符集修改為utf8;北京傳智播客教育 創(chuàng)建表(基本語(yǔ)句)CREATE TABLE table_name(field1 datatype,field2 datatype,field3 datatype)character set 字符

7、集 collate 校對(duì)規(guī)則field：指定列名datatype：指定列類型注意：創(chuàng)建表時(shí)，要根據(jù)需保存的數(shù)據(jù)創(chuàng)建相應(yīng)的列，并根據(jù)數(shù)據(jù)的類型定義相應(yīng)的列類型。例：user對(duì)象id int name stringpassword stringbirthday dateIdName Passwordbirthday注意：創(chuàng)建表前，要先使用use db語(yǔ)句使用庫(kù)。北京傳智播客教育 MySQL常用數(shù)據(jù)類型字符串型 VARCHAR、CHAR大數(shù)據(jù)類型BLOB、TEXT數(shù)值型TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE邏輯性 BIT日期型DATE、TIME、DATET

8、IME、TIMESTAMP北京傳智播客教育 創(chuàng)建表練習(xí)創(chuàng)建一個(gè)員工表employee - 查看表結(jié)構(gòu): desc 表名;字段屬性id整形name字符型gender字符型birthday日期型entry_date日期型job字符型salary小數(shù)型resume大文本型北京傳智播客教育 定義單表字段的約束(保證數(shù)據(jù)的有效性和完整性)定義主鍵約束 primary key:不允許為空，不允許重復(fù)刪除主鍵：alter table tablename drop primary key ; 主鍵自動(dòng)增長(zhǎng) ：auto_increment定義唯一約束 unique 例如：name varchar(20) uni

9、que定義非空約束 not null 例如：salary double not null北京傳智播客教育 修改表使用 ALTER TABLE 語(yǔ)句追加, 修改, 或刪除列的語(yǔ)法.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 新表名;修改列的名

10、稱： change column old_col_name column_definition;修改表的字符集：alter table student character set utf8;北京傳智播客教育 修改表練習(xí)在上面員工表的基本上增加一個(gè)image列。修改job列，使其長(zhǎng)度為60。刪除gender列。表名改為user。修改表的字符集為utf8列名name修改為usernamealter table user change column name username varchar(20);北京傳智播客教育 數(shù)據(jù)庫(kù)CRUD語(yǔ)句Insert語(yǔ)句 (增加數(shù)據(jù))Update語(yǔ)句 (更新數(shù)據(jù))De

11、lete語(yǔ)句 (刪除數(shù)據(jù))Select語(yǔ)句(查找數(shù)據(jù))北京傳智播客教育 Insert語(yǔ)句INSERT INTOtable (column , column.)VALUES(value , value.);使用 INSERT 語(yǔ)句向表中插入數(shù)據(jù)。插入的數(shù)據(jù)應(yīng)與字段的數(shù)據(jù)類型相同。數(shù)據(jù)的大小應(yīng)在列的規(guī)定范圍內(nèi)，例如：不能將一個(gè)長(zhǎng)度為80的字符串加入到長(zhǎng)度為40的列中。在values中列出的數(shù)據(jù)位置必須與被加入的列的排列位置相對(duì)應(yīng)。字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中。插入空值：不指定或insert into table value(null)北京傳智播客教育 Insert語(yǔ)句練習(xí)練習(xí)：使用insert

12、語(yǔ)句向表中插入三個(gè)員工的信息。字段名字段類型id整形name字符串型gender字符串型birthday日期型salary浮點(diǎn)型entry_date日期型resume大文本型show variables like character%;北京傳智播客教育 Tip：mysql中文亂碼mysql有六處使用了字符集，分別為：client 、connection、database、results、server 、system。client是客戶端使用的字符集。 connection是連接數(shù)據(jù)庫(kù)的字符集設(shè)置類型，如果程序沒(méi)有指明連接數(shù)據(jù)庫(kù)使用的字符集類型就按照服務(wù)器端默認(rèn)的字符集設(shè)置。 database是

13、數(shù)據(jù)庫(kù)服務(wù)器中某個(gè)庫(kù)使用的字符集設(shè)定，如果建庫(kù)時(shí)沒(méi)有指明，將使用服務(wù)器安裝時(shí)指定的字符集設(shè)置。 results是數(shù)據(jù)庫(kù)給客戶端返回時(shí)使用的字符集設(shè)定，如果沒(méi)有指明，使用服務(wù)器默認(rèn)的字符集。 server是服務(wù)器安裝時(shí)指定的默認(rèn)字符集設(shè)定。 system是數(shù)據(jù)庫(kù)系統(tǒng)使用的字符集設(shè)定。（utf-8不可修改）通過(guò)修改my.ini 修改字符集編碼北京傳智播客教育 Update語(yǔ)句UPDATE tbl_name SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition 使用 update語(yǔ)句修改表中數(shù)據(jù)。UPDATE語(yǔ)法可以用新值更新原

14、有表行中的各列。SET子句指示要修改哪些列和要給予哪些值。WHERE子句指定應(yīng)更新哪些行。如沒(méi)有WHERE子句，則更新所有的行。北京傳智播客教育 Update語(yǔ)句練習(xí)練習(xí)：在上面創(chuàng)建的employee表中修改表中的紀(jì)錄。要求將所有員工薪水修改為5000元。將姓名為zs的員工薪水修改為3000元。將姓名為ls的員工薪水修改為4000元,job改為ccc。將wu的薪水在原有基礎(chǔ)上增加1000元。北京傳智播客教育 Delete語(yǔ)句delete from tbl_name WHERE where_definition 使用 delete語(yǔ)句刪除表中數(shù)據(jù)。如果不使用where子句，將刪除表中所有數(shù)據(jù)。D

15、elete語(yǔ)句不能刪除某一列的值（可使用update）使用delete語(yǔ)句僅刪除記錄，不刪除表本身。如要?jiǎng)h除表，使用drop table語(yǔ)句。同insert和update一樣，從一個(gè)表中刪除記錄將引起其它表的參照完整性問(wèn)題，在修改數(shù)據(jù)庫(kù)數(shù)據(jù)時(shí)，頭腦中應(yīng)該始終不要忘記這個(gè)潛在的問(wèn)題。外鍵約束刪除表中數(shù)據(jù)也可使用TRUNCATE TABLE 語(yǔ)句，它和delete有所不同，參看mysql文檔。北京傳智播客教育 Delete語(yǔ)句練習(xí)刪除表中名稱為zs的記錄。刪除表中所有記錄。使用truncate刪除表中記錄。北京傳智播客教育 Select語(yǔ)句(1)SELECT DISTINCT *|column1,

16、 column2. column3.FROMtable;select 指定查詢哪些列的數(shù)據(jù)。column指定列名。*號(hào)代表查詢所有列。from指定查詢哪張表。DISTINCT可選，指顯示結(jié)果時(shí)，是否剔除重復(fù)數(shù)據(jù)基本select語(yǔ)句北京傳智播客教育 Select語(yǔ)句(1)練習(xí)：查詢表中所有學(xué)生的信息。查詢表中所有學(xué)生的姓名和對(duì)應(yīng)的英語(yǔ)成績(jī)。過(guò)濾表中重復(fù)數(shù)據(jù)。北京傳智播客教育 Select語(yǔ)句()SELECT *|column1expression, column2expression，.FROMtable;在select語(yǔ)句中可使用表達(dá)式對(duì)查詢的列進(jìn)行運(yùn)算在select語(yǔ)句中可使用as語(yǔ)句SEL

17、ECT column as 別名 from 表名;北京傳智播客教育 Select語(yǔ)句()練習(xí)在所有學(xué)生分?jǐn)?shù)上加10分特長(zhǎng)分。統(tǒng)計(jì)每個(gè)學(xué)生的總分。使用別名表示學(xué)生分?jǐn)?shù)。北京傳智播客教育 Select語(yǔ)句(3)使用where子句，進(jìn)行過(guò)濾查詢。練習(xí)：查詢姓名為XXX的學(xué)生成績(jī)查詢英語(yǔ)成績(jī)大于90分的同學(xué)查詢總分大于200分的所有同學(xué)北京傳智播客教育 Select語(yǔ)句()在where子句中經(jīng)常使用的運(yùn)算符比較運(yùn)算符 = = 大于、小于、大于(小于)等于、不等于 between . and.顯示在某一區(qū)間的值in(set)顯示在in列表中的值，例：in(100,200)like 張pattern模糊查

18、詢Is null判斷是否為空邏輯運(yùn)算符and多個(gè)條件同時(shí)成立or多個(gè)條件任一成立not不成立，例：where not(salary100);Like語(yǔ)句中，% 代表零個(gè)或多個(gè)任意字符，_ 代表一個(gè)字符，例first_name like _a%;北京傳智播客教育 Select語(yǔ)句(4)查詢英語(yǔ)分?jǐn)?shù)在 8090之間的同學(xué)。查詢數(shù)學(xué)分?jǐn)?shù)為89,90,91的同學(xué)。查詢所有姓李的學(xué)生成績(jī)。查詢數(shù)學(xué)分80，語(yǔ)文分80的同學(xué)。北京傳智播客教育 Select語(yǔ)句()SELECT column1, column2. column3.FROMtable;order by column asc|descOrder

19、by 指定排序的列，排序的列即可是表中的列名，也可以是select 語(yǔ)句后指定的列名。Asc 升序、Desc 降序ORDER BY 子句應(yīng)位于SELECT語(yǔ)句的結(jié)尾。練習(xí)：對(duì)數(shù)學(xué)成績(jī)排序后輸出。對(duì)總分排序按從高到低的順序輸出對(duì)姓李的學(xué)生成績(jī)排序輸出使用order by 子句排序查詢結(jié)果。北京傳智播客教育 聚集函數(shù)countSelect count(*)|count(列名) from tablenameWHERE where_definition 練習(xí)：統(tǒng)計(jì)一個(gè)班級(jí)共有多少學(xué)生？統(tǒng)計(jì)數(shù)學(xué)成績(jī)大于90的學(xué)生有多少個(gè)？統(tǒng)計(jì)總分大于250的人數(shù)有多少？Count(列名)返回某一列，行的總數(shù)北京傳智播客

20、教育 聚集函數(shù)SUMSelect sum(列名),sum(列名) from tablenameWHERE where_definition 練習(xí)：統(tǒng)計(jì)一個(gè)班級(jí)數(shù)學(xué)總成績(jī)？統(tǒng)計(jì)一個(gè)班級(jí)語(yǔ)文、英語(yǔ)、數(shù)學(xué)各科的總成績(jī)統(tǒng)計(jì)一個(gè)班級(jí)語(yǔ)文、英語(yǔ)、數(shù)學(xué)的成績(jī)總和統(tǒng)計(jì)一個(gè)班級(jí)語(yǔ)文成績(jī)平均分注意：sum僅對(duì)數(shù)值起作用，否則會(huì)報(bào)錯(cuò)。注意：對(duì)多列求和，“，”號(hào)不能少。Sum函數(shù)返回滿足where條件的行的和北京傳智播客教育 聚集函數(shù)AVG averageSelect sum(列名),sum(列名) from tablenameWHERE where_definition 練習(xí)：求一個(gè)班級(jí)數(shù)學(xué)平均分？求一個(gè)班級(jí)總分平

21、均分AVG函數(shù)返回滿足where條件的一列的平均值北京傳智播客教育 聚集函數(shù)MAX/MINSelect max(列名)from tablenameWHERE where_definition 練習(xí)：求班級(jí)最高分和最低分（數(shù)值范圍在統(tǒng)計(jì)中特別有用）Max/min函數(shù)返回滿足where條件的一列的最大/最小值北京傳智播客教育 Select語(yǔ)句(6)北京傳智播客教育 Select語(yǔ)句(6)SELECT column1, column2. column3. FROMtable;group by column having .練習(xí)：對(duì)訂單表中商品歸類后，顯示每一類商品的總價(jià)使用group by 子句對(duì)列

22、進(jìn)行分組使用having 子句 對(duì)分組結(jié)果進(jìn)行過(guò)濾練習(xí)：查詢購(gòu)買了幾類商品，并且每類總價(jià)大于100的商品Having和where均可實(shí)現(xiàn)過(guò)濾，但在having可以使用聚集函數(shù),having通常跟在group by后，它作用于分組北京傳智播客教育 備份、恢復(fù)數(shù)據(jù)庫(kù)備份數(shù)據(jù)庫(kù)表中的數(shù)據(jù)cmd mysqldump -u 用戶名 -p 數(shù)據(jù)庫(kù)名 文件名.sql例如: mysqldump -u root -p day12 c:day12.sql恢復(fù)數(shù)據(jù)庫(kù)source 文件名.sql / 在mysql內(nèi)部使用mysql u 用戶名 p 數(shù)據(jù)庫(kù)名 文件名.sql / 在cmd下使用練習(xí)備份test庫(kù)中的數(shù)據(jù)

23、，并恢復(fù)北京傳智播客教育 多表設(shè)計(jì)外鍵約束定義外鍵約束 foreign key foreign key(ordersid) references orders(id)新建部門表department (id,name)通過(guò)外鍵約束建立與員工表employee關(guān)系北京傳智播客教育 多表設(shè)計(jì)中三種實(shí)體關(guān)系北京傳智播客教育 M:N的聯(lián)系引入關(guān)系表聯(lián)系的屬性及兩個(gè)實(shí)體的主標(biāo)識(shí)形成關(guān)系表。關(guān)系表的主鍵為兩個(gè)實(shí)體主標(biāo)識(shí)的組合參加表（E1,P1,percent）雇員表(E1,E2)項(xiàng)目表(P1,P2,P3)北京傳智播客教育 1:N 的聯(lián)系可不引入關(guān)系表將關(guān)系的屬性及非多方的主標(biāo)識(shí)加入到多方表多方表的外鍵是非多方實(shí)體的主標(biāo)識(shí)老師表（Tid，Tname，Tsex）課程表（Cid，Cname，Tid，allowance）北京傳智播客教育 1:1的聯(lián)系不必引入關(guān)系表方案一presidents（Eid，Ename）studios（Sid，Sname，Eid ，year ）方案二presidents（Eid，Ename ，Sid ，year）studios（Sid，Sname）北京傳智播客教育 多表設(shè)計(jì)實(shí)例CREATE TABLE teacher ( id int(11) NOT