SQL(結構化查詢語言)

入門講師：趙君IT資訊交流網WWW.IT315.ORGMySQL簡介數據庫簡介Oracle、SQLServer、DB2MySQL(Linux+Apache+Mysql+Php)SyBase、InformixMySQL數據庫的安裝和配置使用命令行窗口連接MYSQL數據庫mysql–u用戶名–p回車密碼數據庫服務器、數據庫和表的關系MySQL程序DB1DB2表1表3表2Client程序所謂安裝數據庫服務器，只是在機器上裝了一個數據庫管理程序，這個管理程序可以管理多個數據庫，一般開發(fā)人員會針對每一個應用創(chuàng)建一個數據庫。為保存應用中的數據，一般會在數據庫創(chuàng)建多個表，以保存程序中的數據。數據庫服務器、數據庫和表的關系如圖所示：IT資訊交流網WWW.IT315.ORG數據在數據庫中的存儲方式id=1name=“l(fā)isi”age=23User對象idnameage1lisi232wang24User表id=2name=“wang”age=24行(row)列(column)表的一行稱之為一條記錄表中一條記錄對應一個java對象的數據User對象IT資訊交流網WWW.IT315.ORG創(chuàng)建數據庫CREATEDATABASE[IFNOTEXISTS]db_name

[create_specification[,create_specification]...]

create_specification:

[DEFAULT]CHARACTERSETcharset_name

|[DEFAULT]COLLATEcollation_name

CHARACTERSET：指定數據庫采用的字符集COLLATE：指定數據庫字符集的比較方式、規(guī)則，比如排序練習：創(chuàng)建一個名稱為mydb1的數據庫。創(chuàng)建一個使用utf8字符集的mydb2數據庫。創(chuàng)建一個使用utf8字符集，并帶校對規(guī)則的mydb3數據庫。查看、刪除數據庫顯示數據庫語句：SHOWDATABASES顯示數據庫創(chuàng)建語句：SHOWCREATEDATABASEdb_name使用數據庫USEdb_name數據庫刪除語句：DROPDATABASE[IFEXISTS]db_name

練習：查看當前數據庫服務器中的所有數據庫查看前面創(chuàng)建的mydb2數據庫的定義信息刪除前面創(chuàng)建的mydb1數據庫修改、備份、恢復數據庫數據ALTERDATABASE[IFNOTEXISTS]db_name

[alter_specification[,alter_specification]...]

alter_specification:

[DEFAULT]CHARACTERSETcharset_name

|[DEFAULT]COLLATEcollation_name備份數據庫表中的數據(退到window命令行窗口) mysqldump-u用戶名-p數據庫名>D:\文件名.sql恢復數據庫(進入mysql控制臺)

source

D:\文件名.sql練習查看服務器中的數據庫，并把其中mydb3庫的字符集修改為utf8。

備份mydb3庫中的數據，并恢復。創(chuàng)建表(基本語句)CREATETABLEtable_name(

field1datatype, field2datatype, field3datatype,)characterset字符集collate校對規(guī)則field：指定列名datatype：指定列類型注意：創(chuàng)建表時，要根據需保存的數據創(chuàng)建相應的列，并根據數據的類型定義相應的列類型。例：user對象

idint

namestring passwordstring birthdaydateIdNamePasswordbirthday注意：創(chuàng)建表前，要先使用usedbname語句使用庫。MySQL常用數據類型VARCHAR、BLOB和TEXT類是變長類型。每個類型的存儲需求取決于列值的實際長度。

分類數據類型說明數值類型BIT(M)TINYINT[UNSIGNED][ZEROFILL]BOOL，BOOLEANSMALLINT[UNSIGNED][ZEROFILL]INT[UNSIGNED][ZEROFILL]BIGINT[UNSIGNED][ZEROFILL]FLOAT[(M,D)][UNSIGNED][ZEROFILL]DOUBLE[(M,D)][UNSIGNED][ZEROFILL]位類型。M指定位數，默認值1，范圍1-64帶符號的范圍是-128到127。無符號0到255。使用0或1表示真或假2的16次方2的32次方2的64次方M指定顯示長度，d指定小數位數表示比float精度更大的小數文本、二進制類型CHAR(size)char(20)VARCHAR(size)varchar(20)BLOBLONGBLOBTEXT(clob)LONGTEXT(longclob)固定長度字符串可變長度字符串二進制數據大文本時間日期DATE/DATETIME/TimeStamp(登錄時間)日期類型(YYYY-MM-DD)(YYYY-MM-DDHH:MM:SS)，TimeStamp表示時間戳，它可用于自動記錄insert、update操作的時間創(chuàng)建表練習創(chuàng)建/查看一個員工表（并指明字符集為UTF8）字段屬性Id整形name字符型sex字符型brithday日期型entry_date日期型Job字符型salary小數型resume大文本型修改表使用ALTERTABLE語句追加,修改,或刪除列的語法.ALTERTABLEtableADD

(columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableMODIFY

(columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableDROP

(column);修改表的名稱：Renametable

原表名

to

新表名修改表的字符集：altertablestudentcharactersetutf8;修改表練習在上面員工表的基本上增加一個image列。修改job列，使其長度為60。刪除sex列。表名改為user。修改表的字符集為utf8。列名name修改為username。altertableuserchangecolumnnameusernamevarchar(20);IT資訊交流網WWW.IT315.ORG數據庫CRUD語句Insert語句(增加數據)Update語句(更新數據)Delete語句(刪除數據)Select語句(查找數據)IT資訊交流網WWW.IT315.ORGInsert語句[employee.sql]INSERTINTO table[(column[,column...])]VALUES (value[,value...]);使用INSERT語句向表中插入數據。插入的數據應與字段的數據類型相同。數據的大小應在列的規(guī)定范圍內，例如：不能將一個長度為80的字符串加入到長度為40的列中。在values中列出的數據位置必須與被加入的列的排列位置相對應。字符和日期型數據應包含在單引號中。插入空值，不指定或insertintotablevalue(null)IT資訊交流網WWW.IT315.ORGInsert語句練習練習：使用insert語句向表中插入三個員工的信息（英中文）。注意：字符和日期要包含在單引號中。showvariableslike'character%';setcharacter_set_results=gbk;IT資訊交流網WWW.IT315.ORGUpdate語句UPDATE tbl_name

SETcol_name1=expr1[,col_name2=expr2...]

[WHEREwhere_definition]

使用update語句修改表中數據。UPDATE語法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要給予哪些值。WHERE子句指定應更新哪些行。如沒有WHERE子句，則更新所有的行。IT資訊交流網WWW.IT315.ORGUpdate語句練習練習：創(chuàng)建的employee表中修改表中的紀錄。要求將所有員工薪水修改為3000元。將姓名為’張小明’的員工薪水修改為4000元。將’張小明’的薪水在原有基礎上增加1000元。 IT資訊交流網WWW.IT315.ORGDelete語句deletefromtbl_name

[WHEREwhere_definition]

使用delete語句刪除表中數據。如果不使用where子句，將刪除表中所有數據。Delete語句不能刪除某一列的值（可使用update）使用delete語句僅刪除記錄，不刪除表本身。如要刪除表，使用droptable語句。同insert和update一樣，從一個表中刪除記錄將引起其它表的參照完整性問題，在修改數據庫數據時，頭腦中應該始終不要忘記這個潛在的問題。刪除表中數據也可使用TRUNCATETABLE語句，它和delete有所不同。TRUNCATE(復制表結構->銷毀表->重建表結構）DELETE(逐行刪除記錄)IT資訊交流網WWW.IT315.ORGDelete語句練習刪除表中名稱為’zs’的記錄。刪除表中所有記錄。使用truncate刪除表中記錄。IT資訊交流網WWW.IT315.ORGSelect語句(1)[student.sql]SELECT[DISTINCT]*|{column1,column2.column3..}

FROM table;Select指定查詢哪些列的數據。column指定列名。*號代表查詢所有列(開發(fā)中建議不要使用)。From指定查詢哪張表。DISTINCT可選，指顯示結果時，是否剔除重復數據基本select語句IT資訊交流網WWW.IT315.ORGSelect語句(1)練習：查詢表中所有學生的信息。查詢表中所有學生的姓名和對應的英語成績。過濾表中重復數據。IT資訊交流網WWW.IT315.ORGSelect語句(２)SELECT

*|{column1｜expression,column2｜expression，..}

FROM table;在select語句中可使用表達式對查詢的列進行運算在select語句中可使用as語句SELECTcolumnas別名from表名;IT資訊交流網WWW.IT315.ORGSelect語句(２)練習在所有學生分數上加10分特長分。統計每個學生的總分。使用別名表示學生分數。IT資訊交流網WWW.IT315.ORGSelect語句(3)使用where子句，進行過濾查詢。查詢姓名為’張小明’的學生成績查詢英語成績大于90分的同學查詢總分大于200分的所有同學IT資訊交流網WWW.IT315.ORGSelect語句(４)在where子句中經常使用的運算符比較運算符><<=>==<>大于、小于、大于(小于)等于、不等于BETWEEN

...AND...顯示在某一區(qū)間的值IN(set)顯示在in列表中的值，例：in(100,200)LIKE‘張pattern’模糊查詢ISNULL/ISNOTNULL判斷是否為空邏輯運算符and多個條件同時成立or多個條件任一成立not不成立，例：wherenot(salary>100);Like語句中，%代表零個或多個任意字符，_代表一個字符，例first_namelike‘_a%’;IT資訊交流網WWW.IT315.ORGSelect語句(4)查詢英語分數在80－90之間的同學。查詢數學分數為89,90,91的同學。查詢所有姓’李’的學生成績。查詢數學分>80且語文分>80的同學。IT資訊交流網WWW.IT315.ORGSelect語句(５)SELECT

column1,column2.column3..

FROM table; orderbycolumnasc|descOrderby指定排序的列，排序的列即可是表中的列名，也可以是select語句后指定的列名。Asc

升序、Desc

降序ORDERBY子句應位于SELECT語句的結尾。練習：對數學成績排序后輸出。對總分排序后輸出。對姓’李’的學生總分排序輸出。使用orderby子句排序查詢結果。IT資訊交流網WWW.IT315.ORG合計函數－count()Selectcount(*)|count(列名)fromtablename [WHEREwhere_definition]

練習：統計一個班級共有多少學生？統計數學成績大于80的學生有多少個？統計總分大于250的人數有多少？Count(列名)返回某一列，行的總數，除null外IT資訊交流網WWW.IT315.ORG合計函數－sum()Selectsum(列名)｛,sum(列名)…｝fromtablename [WHEREwhere_definition]

練習：統計一個班級數學總成績。統計一個班級語文、英語、數學各科的總成績。統計一個班級語文、英語、數學的成績總和。統計一個班級語文成績平均分。注意：sum僅對數值起作用，否則會報錯。注意：對多列求和，“，”號不能少。Sum函數返回滿足where條件的行的和IT資訊交流網WWW.IT315.ORG平均函數－avg()Selectsum(列名)｛,sum(列名)…｝fromtablename [WHEREwhere_definition]

練習：求一個班級數學平均分。求一個班級總分平均分。AVG函數返回滿足where條件的一列的平均值IT資訊交流網WWW.IT315.ORG最大最小值函數－max()/min()Selectmax(列名)

fromtablename [WHEREwhere_definition]

練習：求班級最高分和最低分。（數值范圍在統計中特別有用）Max/min函數返回滿足where條件的一列的最大/最小值IT資訊交流網WWW.IT315.ORGSelect語句(6)[orders.sql]SELECT

column1,column2.column3..FROM table; groupbycolumn練習：對訂單表中商品歸類后，顯示每一類商品的總價使用groupby子句對列進行分組SELECT

column1,column2.column3..

FROM table; groupbycolumnhaving...使用having子句組過濾練習：查詢購買了幾類商品，并且每類總價大于100的商品Having和where均可實現過濾，但在having可以使用合計函數,having通常跟在groupby后，它作用于組。IT資訊交流網WWW.IT315.ORG定義表的約束(數據安全的保證)定義主鍵約束

primarykey:不允許為空，不允許重復刪除主鍵：altertabletablenamedropprimarykey;定義主鍵自動增長

auto_increment(MySQL特有/UUID類生成)定義唯一約束

unique定義非空約束

notnull定義外鍵約束constraintordersid_FKforeignkey(ordersid)referencesorders(id),表的關聯設計（實體關系的體現）一對一人和身份證（外鍵唯一非空/主鍵外鍵合二唯一）一對多/多對一部門和員工多對多（中間表)老師和學生1號老師所對應的學員1號學員所對應的老師Tip：mysql中文亂碼mysql有六處使用了字符集，分別為：client、connection、database、results、server、system。client是客戶端使用的字符集。connection是連接數據庫的字符集設置類型，如果程序沒有指明連接數據庫使用的字符集類型就按照服務器端默認的字符集設置。database是數據庫服務器中某個庫使用的字符集設定，如果建庫時沒有指明，將使用服務器安裝時指定的字符集設置。results是數據庫給客戶端返回時使用的字符集設定，如果沒有指明，使用服務器默認的字符集。server是服務器安裝時指定的默認字符集設定/utf8。system是數據庫系統使用的字符集設定。IT資訊交流網WWW.IT315.ORG附錄：時間日期相關函數參考ADDTIME(date2,time_interval)將time_interval加到date2CURRENT_DATE(

)當前日期CURRENT_TIME(

)當前時間CURRENT_TIMESTAMP()當前時間戳DATE(datetime)返回datetime的日期部分DATE_ADD(date2,INTERVALd_valued_type)在date2中加上日期或時間DATE_SUB(date2,INTERVALd_valued_type)在date2上減去一個時間DATEDIFF(date1,date2)兩個日期差NOW(

)當前時間YEAR|Month|DAY(datetime)年月日示例：selectaddtime(‘02:30:30’,‘01:01:01’);注意：字符串、時間日期的引號問題

selectdate_add(entry_date,INTERVAL2year)fromstudent;／／增加兩年

selectaddtime(time,‘11-110:09:09’)fromstudent;／／時間戳上增加，注意年后沒有-IT資訊交流網WWW.IT315.ORG附錄：字符串相關函數參考CHARSET(str)返回字串字符集CONCAT(string2

[,...])連接字串INSTR(string,substring)返回substring在string中出現的位置,沒有返回0UCASE(