MySQL基礎(chǔ)知識總結(jié)

1、.day15-MySQL數(shù)據(jù)庫1數(shù)據(jù)庫概念（了解）1.1什么是數(shù)據(jù)庫數(shù)據(jù)庫就是用來存儲和管理數(shù)據(jù)的倉庫！數(shù)據(jù)庫存儲數(shù)據(jù)的優(yōu)先：l 可存儲大量數(shù)據(jù)；l 方便檢索；l 保持?jǐn)?shù)據(jù)的一致性、完整性；l 安全，可共享；l 通過組合分析，可產(chǎn)生新數(shù)據(jù)。1.2數(shù)據(jù)庫的發(fā)展歷程l 沒有數(shù)據(jù)庫，使用磁盤文件存儲數(shù)據(jù)；l 層次結(jié)構(gòu)模型數(shù)據(jù)庫；l 網(wǎng)狀結(jié)構(gòu)模型數(shù)據(jù)庫；l 關(guān)系結(jié)構(gòu)模型數(shù)據(jù)庫：使用二維表格來存儲數(shù)據(jù)；l 關(guān)系-對象模型數(shù)據(jù)庫；MySQL就是關(guān)系型數(shù)據(jù)庫！1.3常見數(shù)據(jù)庫l Oracle（神喻）：甲骨文（最高?。?；l DB2：IBM；l SQL Server：微軟；l Sybase：賽爾斯；l MySQ

2、L：甲骨文；1.4理解數(shù)據(jù)庫l RDBMS = 管理員（manager）+倉庫（database）l database = N個tablel table：Ø 表結(jié)構(gòu)：定義表的列名和列類型！Ø 表記錄：一行一行的記錄！我們現(xiàn)在所說的數(shù)據(jù)庫泛指“關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS - Relational database management system）”，即“數(shù)據(jù)庫服務(wù)器”。當(dāng)我們安裝了數(shù)據(jù)庫服務(wù)器后，就可以在數(shù)據(jù)庫服務(wù)器中創(chuàng)建數(shù)據(jù)庫，每個數(shù)據(jù)庫中還可以包含多張表。數(shù)據(jù)庫表就是一個多行多列的表格。在創(chuàng)建表時，需要指定表的列數(shù)，以及列名稱，列類型等信息。而不用指定表格的行數(shù)

3、，行數(shù)是沒有上限的。下面是tab_student表的結(jié)構(gòu)：當(dāng)把表格創(chuàng)建好了之后，就可以向表格中添加數(shù)據(jù)了。向表格添加數(shù)據(jù)是以行為單位的！下面是s_student表的記錄：s_ids_names_ages_sexS_1001zhangSan23maleS_1002liSi32femaleS_1003wangWu44male大家要學(xué)會區(qū)分什么是表結(jié)構(gòu)，什么是表記錄。1.5應(yīng)用程序與數(shù)據(jù)庫應(yīng)用程序使用數(shù)據(jù)庫完成對數(shù)據(jù)的存儲！2安裝MySQL數(shù)據(jù)庫2.1安裝MySQL參考：MySQL安裝圖解.doc2.2MySQL目錄結(jié)構(gòu)MySQL的數(shù)據(jù)存儲目錄為data，data目錄通常在C:Documents a

4、nd SettingsAll UsersApplication DataMySQLMySQL Server 5.1data位置。在data下的每個目錄都代表一個數(shù)據(jù)庫。MySQL的安裝目錄下：l bin目錄中都是可執(zhí)行文件；l my.ini文件是MySQL的配置文件；3基本命令3.1啟動和關(guān)閉mysql服務(wù)器l 啟動：net start mysql；l 關(guān)閉：net stop mysql；在啟動mysql服務(wù)后，打開windows任務(wù)管理器，會有一個名為mysqld.exe的進程運行，所以mysqld.exe才是MySQL服務(wù)器程序。3.2客戶端登錄退出mysql在啟動MySQL服務(wù)器后，我們

5、需要使用管理員用戶登錄MySQL服務(wù)器，然后來對服務(wù)器進行操作。登錄MySQL需要使用MySQL的客戶端程序：mysql.exel 登錄：mysql -u root -p 123 -h localhost；Ø -u：后面的root是用戶名，這里使用的是超級管理員root；Ø -p：后面的123是密碼，這是在安裝MySQL時就已經(jīng)指定的密碼；Ø -h：后面給出的localhost是服務(wù)器主機名，它是可以省略的，例如：mysql -u root -p 123；l 退出：quit或exit；在登錄成功后，打開windows任務(wù)管理器，會有一個名為mysql.exe的進程

6、運行，所以mysql.exe是客戶端程序。SQL語句1 SQL概述1.1什么是SQLSQL（Structured Query Language）是“結(jié)構(gòu)化查詢語言”，它是對關(guān)系型數(shù)據(jù)庫的操作語言。它可以應(yīng)用到所有關(guān)系型數(shù)據(jù)庫中，例如：MySQL、Oracle、SQL Server等。SQ標(biāo)準(zhǔn)（ANSI/ISO）有：l SQL-92：1992年發(fā)布的SQL語言標(biāo)準(zhǔn)；l SQL:1999：1999年發(fā)布的SQL語言標(biāo)簽；l SQL:2003：2003年發(fā)布的SQL語言標(biāo)簽；這些標(biāo)準(zhǔn)就與JDK的版本一樣，在新的版本中總要有一些語法的變化。不同時期的數(shù)據(jù)庫對不同標(biāo)準(zhǔn)做了實現(xiàn)。雖然SQL可以用在所有關(guān)系

7、型數(shù)據(jù)庫中，但很多數(shù)據(jù)庫還都有標(biāo)準(zhǔn)之后的一些語法，我們可以稱之為“方言”。例如MySQL中的LIMIT語句就是MySQL獨有的方言，其它數(shù)據(jù)庫都不支持！當(dāng)然，Oracle或SQL Server都有自己的方言。1.2語法要求l SQL語句可以單行或多行書寫，以分號結(jié)尾；l 可以用空格和縮進來來增強語句的可讀性；l 關(guān)鍵字不區(qū)別大小寫，建議使用大寫；2分類l DDL（Data Definition Language）：數(shù)據(jù)定義語言，用來定義數(shù)據(jù)庫對象：庫、表、列等；l DML（Data Manipulation Language）：數(shù)據(jù)操作語言，用來定義數(shù)據(jù)庫記錄（數(shù)據(jù)）；l DCL（Data C

8、ontrol Language）：數(shù)據(jù)控制語言，用來定義訪問權(quán)限和安全級別；l DQL（Data Query Language）：數(shù)據(jù)查詢語言，用來查詢記錄（數(shù)據(jù)）。3DDL3.1基本操作l 查看所有數(shù)據(jù)庫名稱：SHOW DATABASES；l 切換數(shù)據(jù)庫：USE mydb1，切換到mydb1數(shù)據(jù)庫；3.2操作數(shù)據(jù)庫l 創(chuàng)建數(shù)據(jù)庫：CREATE DATABASE IF NOT EXISTS mydb1；創(chuàng)建數(shù)據(jù)庫，例如：CREATE DATABASE mydb1，創(chuàng)建一個名為mydb1的數(shù)據(jù)庫。如果這個數(shù)據(jù)已經(jīng)存在，那么會報錯。例如CREATE DATABASE IF NOT EXISTS m

9、ydb1，在名為mydb1的數(shù)據(jù)庫不存在時創(chuàng)建該庫，這樣可以避免報錯。l 刪除數(shù)據(jù)庫：DROP DATABASE IF EXISTS mydb1；刪除數(shù)據(jù)庫，例如：DROP DATABASE mydb1，刪除名為mydb1的數(shù)據(jù)庫。如果這個數(shù)據(jù)庫不存在，那么會報錯。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不會的報錯。l 修改數(shù)據(jù)庫編碼：ALTER DATABASE mydb1 CHARACTER SET utf8修改數(shù)據(jù)庫mydb1的編碼為utf8。注意，在MySQL中所有的UTF-8編碼都不能使用中間的“-”，即UTF-8要書寫為UTF8。3.3數(shù)

10、據(jù)類型MySQL與Java一樣，也有數(shù)據(jù)類型。MySQL中數(shù)據(jù)類型主要應(yīng)用在列上。常用類型：l int：整型l double：浮點型，例如double(5,2)表示最多5位，其中必須有2位小數(shù)，即最大值為999.99；l decimal：泛型型，在表單錢方面使用該類型，因為不會出現(xiàn)精度缺失問題；l char：固定長度字符串類型；l varchar：可變長度字符串類型；l text：字符串類型；l blob：字節(jié)類型；l date：日期類型，格式為：yyyy-MM-dd；l time：時間類型，格式為：hh:mm:ssl timestamp：時間戳類型；3.4操作表l 創(chuàng)建表：CREATE TA

11、BLE 表名( 列名 列類型, 列名 列類型, .);例如：CREATE TABLE stu(sid CHAR(6),snameVARCHAR(20),ageINT,genderVARCHAR(10) );再例如：CREATE TABLE emp(eidCHAR(6),enameVARCHAR(50),ageINT,genderVARCHAR(6),birthdayDATE,hiredateDATE,salaryDECIMAL(7,2),resumeVARCHAR(1000);l 查看當(dāng)前數(shù)據(jù)庫中所有表名稱：SHOW TABLES；l 查看指定表的創(chuàng)建語句：SHOW CREATE TABLE

12、emp，查看emp表的創(chuàng)建語句；l 查看表結(jié)構(gòu)：DESC emp，查看emp表結(jié)構(gòu)；l 刪除表：DROP TABLE emp，刪除emp表；l 修改表：1. 修改之添加列：給stu表添加classname列：ALTER TABLE stu ADD (classname varchar(100);2. 修改之修改列類型：修改stu表的gender列類型為CHAR(2)：ALTER TABLE stu MODIFY gender CHAR(2);3. 修改之修改列名：修改stu表的gender列名為sex：ALTER TABLE stu change gender sex CHAR(2);4. 修

13、改之刪除列：刪除stu表的classname列：ALTER TABLE stu DROP classname;5. 修改之修改表名稱：修改stu表名稱為student：ALTER TABLE stu RENAME TO student;4DML4.1插入數(shù)據(jù)語法：INSERT INTO 表名(列名1,列名2, ) VALUES(值1, 值2)INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');INSERT INTO stu(sid, sna

14、me) VALUES('s_1001', 'zhangSan');語法：INSERT INTO 表名 VALUES(值1,值2,)因為沒有指定要插入的列，表示按創(chuàng)建表時列的順序插入所有列的值：INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');注意：所有字符串?dāng)?shù)據(jù)必須使用單引用！4.2修改數(shù)據(jù)語法：UPDATE 表名 SET 列名1=值1, 列名n=值n WHERE 條件UPDATE stu SET sname=zhangSanSan, age=32, ge

15、nder=female WHERE sid=s_1001;UPDATE stu SET sname=liSi, age=20 WHERE age>50 AND gender=male;UPDATE stu SET sname=wangWu, age=30 WHERE age>60 OR gender=female;UPDATE stu SET gender=female WHERE gender IS NULLUPDATE stu SET age=age+1 WHERE sname=zhaoLiu;4.3刪除數(shù)據(jù)語法：DELETE FROM 表名 WHERE 條件DELETE FR

16、OM stu WHERE sid=s_1001003BDELETE FROM stu WHERE sname=chenQi OR age > 30;DELETE FROM stu;語法：TRUNCATE TABLE 表名TRUNCATE TABLE stu;雖然TRUNCATE和DELETE都可以刪除表的所有記錄，但有原理不同。DELETE的效率沒有TRUNCATE高！TRUNCATE其實屬性DDL語句，因為它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE刪除的記錄是無法回滾的，但DELETE刪除的記錄是可以回滾的（回滾是事務(wù)的知識?。?。5DCL5.1創(chuàng)建用戶

17、語法：CREATE USER 用戶名地址 IDENTIFIED BY '密碼'CREATE USER user1localhost IDENTIFIED BY 123;CREATE USER user2% IDENTIFIED BY 123;5.2給用戶授權(quán)語法：GRANT 權(quán)限1, , 權(quán)限n ON 數(shù)據(jù)庫.* TO 用戶名GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1localhost;GRANT ALL ON mydb1.* TO user2localhost;5.3撤銷授權(quán)語

18、法：REVOKE權(quán)限1, , 權(quán)限n ON 數(shù)據(jù)庫.* FORM 用戶名REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1localhost;5.4查看用戶權(quán)限語法：SHOW GRANTS FOR 用戶名SHOW GRANTS FOR user1localhost;5.5刪除用戶語法：DROP USER 用戶名DROP USER user1localhost;5.6修改用戶密碼語法：USE mysql;UPDATE USER SET PASSWORD=PASSWORD(密碼) WHERE User=用戶名 and Host=IP;FLUSH PRIVI

19、LEGES;UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=localhost;FLUSH PRIVILEGES;數(shù)據(jù)查詢語法（DQL）DQL就是數(shù)據(jù)查詢語言，數(shù)據(jù)庫執(zhí)行DQL語句不會對數(shù)據(jù)進行改變，而是讓數(shù)據(jù)庫發(fā)送結(jié)果集給客戶端。語法：SELECT selection_list /*要查詢的列名稱*/ FROM table_list /*要查詢的表名稱*/ WHERE condition /*行條件*/ GROUP BY grouping_columns /*對結(jié)果分組

20、*/ HAVING condition /*分組后的行條件*/ ORDER BY sorting_columns /*對結(jié)果分組*/ LIMIT offset_start, row_count /*結(jié)果限定*/創(chuàng)建名：l 學(xué)生表：stu字段名稱字段類型說明sidchar(6)學(xué)生學(xué)號snamevarchar(50)學(xué)生姓名ageint學(xué)生年齡gendervarchar(50)學(xué)生性別CREATE TABLE stu (sidCHAR(6),snameVARCHAR(50),ageINT,genderVARCHAR(50);INSERT INTO stu VALUES('S_1001&#

21、39;, 'liuYi', 35, 'male');INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');INSERT INTO stu V

22、ALUES('S_1005', 'wangWu', 55, 'male');INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female'

23、;);INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');INSERT INTO stu VALUES('S_1011', '*x', NULL, NULL);l 雇員表：emp字段名稱字段類型說明empnoint員工編號enamevarchar(50)員工姓名jobvarchar(50)員工工作m

24、grint領(lǐng)導(dǎo)編號hiredatedate入職日期saldecimal(7,2)月薪commdecimal(7,2)獎金deptnoint部分編號CREATE TABLE emp(empnoINT,enameVARCHAR(50),jobVARCHAR(50),mgrINT,hiredateDATE,salDECIMAL(7,2),commdecimal(7,2),deptnoINT) ;INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);INSER

25、T INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);

26、INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,N

27、ULL,10);INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08

28、9;,1500,0,30);INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',

29、3000,NULL,20);INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);l 部分表：dept字段名稱字段類型說明deptnoint部分編碼dnamevarchar(50)部分名稱locvarchar(50)部分所在地點CREATE TABLE dept(deptnoINT,dnamevarchar(14),locvarchar(13);INSERT INTO dept values(10, 'ACCOUNTING', &#

30、39;NEW YORK');INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');INSERT INTO dept values(30, 'SALES', 'CHICAGO');INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');1基礎(chǔ)查詢1.1查詢所有列SELECT * FROM stu;1.2查詢指定列SELECT sid, sname, age FROM stu;2條件查詢2.1條件查詢介

31、紹條件查詢就是在查詢時給出WHERE子句，在WHERE子句中可以使用如下運算符及關(guān)鍵字：l =、!=、<>、<、<=、>、>=；l BETWEENAND；l IN(set)；l IS NULL；l AND；l OR；l NOT；2.2查詢性別為女，并且年齡50的記錄SELECT * FROM stu WHERE gender='female' AND ge<50;2.3查詢學(xué)號為S_1001，或者姓名為liSi的記錄SELECT * FROM stu WHERE sid ='S_1001' OR sname='l

32、iSi'2.4查詢學(xué)號為S_1001，S_1002，S_1003的記錄SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');2.5查詢學(xué)號不是S_1001，S_1002，S_1003的記錄SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');2.6查詢年齡為null的記錄SELECT * FROM stuWHERE age IS

33、 NULL;2.7查詢年齡在20到40之間的學(xué)生記錄SELECT * FROM stuWHERE age>=20 AND age<=40;或者SELECT * FROM stu WHERE age BETWEEN 20 AND 40;2.8查詢性別非男的學(xué)生記錄SELECT * FROM stuWHERE gender!='male'或者SELECT * FROM stuWHERE gender<>'male'或者SELECT * FROM stuWHERE NOT gender='male'2.9查詢姓名不為null的學(xué)

34、生記錄SELECT * FROM stuWHERE NOT sname IS NULL;或者SELECT * FROM stuWHERE sname IS NOT NULL;3模糊查詢當(dāng)想查詢姓名中包含a字母的學(xué)生時就需要使用模糊查詢了。模糊查詢需要使用關(guān)鍵字LIKE。3.1查詢姓名由5個字母構(gòu)成的學(xué)生記錄SELECT * FROM stuWHERE sname LIKE '_'模糊查詢必須使用LIKE關(guān)鍵字。其中 “_”匹配任意一個字母，5個“_”表示5個任意字母。3.2查詢姓名由5個字母構(gòu)成，并且第5個字母為“i”的學(xué)生記錄SELECT * FROM stuWHERE sn

35、ame LIKE '_i'3.3查詢姓名以“z”開頭的學(xué)生記錄SELECT * FROM stuWHERE sname LIKE 'z%'其中“%”匹配0n個任何字母。3.4查詢姓名中第2個字母為“i”的學(xué)生記錄SELECT * FROM stuWHERE sname LIKE '_i%'3.5查詢姓名中包含“a”字母的學(xué)生記錄SELECT * FROM stuWHERE sname LIKE '%a%'4字段控制查詢4.1去除重復(fù)記錄去除重復(fù)記錄（兩行或兩行以上記錄中系列的上的數(shù)據(jù)都相同），例如emp表中sal字段就存在相同的記

36、錄。當(dāng)只查詢emp表的sal字段時，那么會出現(xiàn)重復(fù)記錄，那么想去除重復(fù)記錄，需要使用DISTINCT：SELECT DISTINCT sal FROM emp;4.2查看雇員的月薪與傭金之和因為sal和comm兩列的類型都是數(shù)值類型，所以可以做加運算。如果sal或comm中有一個字段不是數(shù)值類型，那么會出錯。SELECT *,sal+comm FROM emp;comm列有很多記錄的值為NULL，因為任何東西與NULL相加結(jié)果還是NULL，所以結(jié)算結(jié)果可能會出現(xiàn)NULL。下面使用了把NULL轉(zhuǎn)換成數(shù)值0的函數(shù)IFNULL：SELECT *,sal+IFNULL(comm,0) FROM emp

37、;4.3給列名添加別名在上面查詢中出現(xiàn)列名為sal+IFNULL(comm,0)，這很不美觀，現(xiàn)在我們給這一列給出一個別名，為total：SELECT *, sal+IFNULL(comm,0) AS total FROM emp;給列起別名時，是可以省略AS關(guān)鍵字的：SELECT *,sal+IFNULL(comm,0) total FROM emp;5排序5.1查詢所有學(xué)生記錄，按年齡升序排序SELECT *FROM stuORDER BY sage ASC;或者SELECT *FROM stuORDER BY sage;5.2查詢所有學(xué)生記錄，按年齡降序排序SELECT *FROM st

38、uORDER BY age DESC;5.3查詢所有雇員，按月薪降序排序，如果月薪相同時，按編號升序排序SELECT * FROM empORDER BY sal DESC,empno ASC;6聚合函數(shù)聚合函數(shù)是用來做縱向運算的函數(shù)：l COUNT()：統(tǒng)計指定列不為NULL的記錄行數(shù)；l MAX()：計算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運算；l MIN()：計算指定列的最小值，如果指定列是字符串類型，那么使用字符串排序運算；l SUM()：計算指定列的數(shù)值和，如果指定列類型不是數(shù)值類型，那么計算結(jié)果為0；l AVG()：計算指定列的平均值，如果指定列類型不是數(shù)值類

39、型，那么計算結(jié)果為0；6.1COUNT當(dāng)需要縱向統(tǒng)計時可以使用COUNT()。l 查詢emp表中記錄數(shù)：SELECT COUNT(*) ASt FROM emp;l 查詢emp表中有傭金的人數(shù)：SELECT COUNT(comm)t FROM emp;注意，因為count()函數(shù)中給出的是comm列，那么只統(tǒng)計comm列非NULL的行數(shù)。l 查詢emp表中月薪大于2500的人數(shù)：SELECT COUNT(*) FROM empWHERE sal > 2500;l 統(tǒng)計月薪與傭金之和大于2500元的人數(shù)：SELECT COUNT(*) ASt FROM emp WHERE sal+IFNU

40、LL(comm,0) > 2500;l 查詢有傭金的人數(shù)，以及有領(lǐng)導(dǎo)的人數(shù)：SELECT COUNT(comm), COUNT(mgr) FROM emp;6.2SUM和AVG當(dāng)需要縱向求和時使用sum()函數(shù)。l 查詢所有雇員月薪和：SELECT SUM(sal) FROM emp;l 查詢所有雇員月薪和，以及所有雇員傭金和：SELECT SUM(sal), SUM(comm) FROM emp;l 查詢所有雇員月薪+傭金和：SELECT SUM(sal+IFNULL(comm,0) FROM emp;l 統(tǒng)計所有員工平均工資：SELECT SUM(sal), COUNT(sal) F

41、ROM emp;或者SELECT AVG(sal) FROM emp;6.3MAX和MINl 查詢最高工資和最低工資：SELECT MAX(sal), MIN(sal) FROM emp;7分組查詢當(dāng)需要分組查詢時需要使用GROUP BY子句，例如查詢每個部門的工資和，這說明要使用部分來分組。7.1分組查詢l 查詢每個部門的部門編號和每個部門的工資和：SELECT deptno, SUM(sal)FROM empGROUP BY deptno;l 查詢每個部門的部門編號以及每個部門的人數(shù)：SELECT deptno,COUNT(*)FROM empGROUP BY deptno;l 查詢每個部

42、門的部門編號以及每個部門工資大于1500的人數(shù)：SELECT deptno,COUNT(*)FROM empWHERE sal>1500GROUP BY deptno;7.2HAVING子句l 查詢工資總和大于9000的部門編號以及工資和：SELECT deptno, SUM(sal)FROM empGROUP BY deptnoHAVING SUM(sal) > 9000;注意，WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那么這行記錄不會參加分組；而HAVING是對分組后數(shù)據(jù)的約束。8LIMITLIMIT用來限定查詢結(jié)果的起始行，以及總行數(shù)。8.1查

43、詢5行記錄，起始行從0開始SELECT * FROM emp LIMIT 0, 5;注意，起始行從0開始，即第一行開始！8.2查詢10行記錄，起始行從3開始SELECT * FROM emp LIMIT 3, 10;8.3分頁查詢?nèi)绻豁撚涗洖?0條，希望查看第3頁記錄應(yīng)該怎么查呢.l 第一頁記錄起始行為0，一共查詢10行；l 第二頁記錄起始行為10，一共查詢10行；l 第三頁記錄起始行為20，一共查詢10行；完整性約束完整性約束是為了表的數(shù)據(jù)的正確性！如果數(shù)據(jù)不正確，那么一開始就不能添加到表中。1主鍵當(dāng)某一列添加了主鍵約束后，那么這一列的數(shù)據(jù)就不能重復(fù)出現(xiàn)。這樣每行記錄中其主鍵列的值就是這一

44、行的唯一標(biāo)識。例如學(xué)生的學(xué)號可以用來做唯一標(biāo)識，而學(xué)生的姓名是不能做唯一標(biāo)識的，因為學(xué)習(xí)有可能同名。主鍵列的值不能為NULL，也不能重復(fù)！指定主鍵約束使用PRIMARY KEY關(guān)鍵字l 創(chuàng)建表：定義列時指定主鍵：CREATE TABLE stu(sidCHAR(6) PRIMARY KEY,snameVARCHAR(20),ageINT,genderVARCHAR(10) );l 創(chuàng)建表：定義列之后獨立指定主鍵：CREATE TABLE stu(sidCHAR(6),snameVARCHAR(20),ageINT,genderVARCHAR(10),PRIMARY KEY(sid);l 修改表

45、時指定主鍵：ALTER TABLE stuADD PRIMARY KEY(sid);l 刪除主鍵（只是刪除主鍵約束，而不會刪除主鍵列）：ALTER TABLE stu DROP PRIMARY KEY;2主鍵自增長MySQL提供了主鍵自動增長的功能！這樣用戶就不用再為是否有主鍵是否重復(fù)而煩惱了。當(dāng)主鍵設(shè)置為自動增長后，在沒有給出主鍵值時，主鍵的值會自動生成，而且是最大主鍵值+1，也就不會出現(xiàn)重復(fù)主鍵的可能了。l 創(chuàng)建表時設(shè)置主鍵自增長（主鍵必須是整型才可以自增長）：CREATE TABLE stu(sid INT PRIMARY KEY AUTO_INCREMENT,snameVARCHAR(

46、20),ageINT,genderVARCHAR(10);l 修改表時設(shè)置主鍵自增長：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;l 修改表時刪除主鍵自增長：ALTER TABLE stu CHANGE sid sid INT;3非空指定非空約束的列不能沒有值，也就是說在插入記錄時，對添加了非空約束的列一定要給值；在修改記錄時，不能把非空列的值設(shè)置為NULL。l 指定非空約束：CREATE TABLE stu(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(10) NOT NULL,age

47、INT,genderVARCHAR(10);當(dāng)為sname字段指定為非空后，在向stu表中插入記錄時，必須給sname字段指定值，否則會報錯：INSERT INTO stu(sid) VALUES(1);插入的記錄中sname沒有指定值，所以會報錯！4唯一還可以為字段指定唯一約束！當(dāng)為字段指定唯一約束后，那么字段的值必須是唯一的。這一點與主鍵相似！例如給stu表的sname字段指定唯一約束：CREATE TABLE tab_ab(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(10) UNIQUE);INSERT INTO sname(sid,

48、 sname) VALUES(1001,'zs');INSERT INTO sname(sid, sname) VALUES(1002, 'zs');當(dāng)兩次插入相同的名字時，MySQL會報錯！5外鍵主外鍵是構(gòu)成表與表關(guān)聯(lián)的唯一途徑！外鍵是另一張表的主鍵！例如員工表與部門表之間就存在關(guān)聯(lián)關(guān)系，其中員工表中的部門編號字段就是外鍵，是相對部門表的外鍵。我們再來看BBS系統(tǒng)中：用戶表（t_user）、分類表（t_section）、帖子表（t_topic）三者之間的關(guān)系。例如在t_section表中sid為1的記錄說明有一個分類叫java，版主是t_user表中uid為1

49、的用戶，即zs！例如在t_topic表中tid為2的記錄是名字為“Java是咖啡”的帖子，它是java版塊的帖子，它的作者是ww。外鍵就是用來約束這一列的值必須是另一張表的主鍵值！l 創(chuàng)建t_user表，指定uid為主鍵列：CREATE TABLE t_user(uidINT PRIMARY KEY AUTO_INCREMENT,unameVARCHAR(20) UNIQUE NOT NULL);l 創(chuàng)建t_section表，指定sid為主鍵列，u_id為相對t_user表的uid列的外鍵：CREATE TABLE t_section(sidINT PRIMARY KEY AUTO_INCRE

50、MENT,snameVARCHAR(30),u_idINT,CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid);l 修改t_section表，指定u_id為相對t_user表的uid列的外鍵：ALTER TABLE t_section ADD CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid);l 修改t_section表，刪除u_id的外鍵約束：ALTER TABLE t_sectionDROP FOREIGN KEY fk_t_user;6表與表之

51、間的關(guān)系l 一對一：例如t_person表和t_card表，即人和身份證。這種情況需要找出主從關(guān)系，即誰是主表，誰是從表。人可以沒有身份證，但身份證必須要有人才行，所以人是主表，而身份證是從表。設(shè)計從表可以有兩種方案：Ø 在t_card表中添加外鍵列（相對t_user表），并且給外鍵添加唯一約束；Ø 給t_card表的主鍵添加外鍵約束（相對t_user表），即t_card表的主鍵也是外鍵。l 一對多（多對一）：最為常見的就是一對多！一對多和多對一，這是從哪個角度去看得出來的。t_user和t_section的關(guān)系，從t_user來看就是一對多，而從t_section的角度來

52、看就是多對一！這種情況都是在多方創(chuàng)建外鍵！l 多對多：例如t_stu和t_teacher表，即一個學(xué)生可以有多個老師，而一個老師也可以有多個學(xué)生。這種情況通常需要創(chuàng)建中間表來處理多對多關(guān)系。例如再創(chuàng)建一張表t_stu_tea表，給出兩個外鍵，一個相對t_stu表的外鍵，另一個相對t_teacher表的外鍵。編碼1查看MySQL編碼SHOW VARIABLES LIKE 'char%'因為當(dāng)初安裝時指定了字符集為UTF8，所以所有的編碼都是UTF8。l character_set_client：你發(fā)送的數(shù)據(jù)必須與client指定的編碼一致！服務(wù)器會使用該編碼來解讀客戶端發(fā)送過來的

53、數(shù)據(jù)；l character_set_connection：通過該編碼與client一致！該編碼不會導(dǎo)致亂碼！當(dāng)執(zhí)行的是查詢語句時，客戶端發(fā)送過來的數(shù)據(jù)會先轉(zhuǎn)換成connection指定的編碼。但只要客戶端發(fā)送過來的數(shù)據(jù)與client指定的編碼一致，那么轉(zhuǎn)換就不會出現(xiàn)問題；l character_set_database：數(shù)據(jù)庫默認(rèn)編碼，在創(chuàng)建數(shù)據(jù)庫時，如果沒有指定編碼，那么默認(rèn)使用database編碼；l character_set_server：MySQL服務(wù)器默認(rèn)編碼；l character_set_results：響應(yīng)的編碼，即查詢結(jié)果返回給客戶端的編碼。這說明客戶端必須使用resul

54、t指定的編碼來解碼；2控制臺編碼修改character_set_client、character_set_results、character_set_connection為GBK，就不會出現(xiàn)亂碼了。但其實只需要修改character_set_client和character_set_results?？刂婆_的編碼只能是GBK，而不能修改為UTF8，這就出現(xiàn)一個問題?？蛻舳税l(fā)送的數(shù)據(jù)是GBK，而character_set_client為UTF8，這就說明客戶端數(shù)據(jù)到了服務(wù)器端后一定會出現(xiàn)亂碼。既然不能修改控制臺的編碼，那么只能修改character_set_client為GBK了。服務(wù)器發(fā)送給客戶

55、端的數(shù)據(jù)編碼為character_set_result，它如果是UTF8，那么控制臺使用GBK解碼也一定會出現(xiàn)亂碼。因為無法修改控制臺編碼，所以只能把character_set_result修改為GBK。l 修改character_set_client變量：set character_set_client=gbk;l 修改character_set_results變量：set character_set_results=gbk;設(shè)置編碼只對當(dāng)前連接有效，這說明每次登錄MySQL提示符后都要去修改這兩個編碼，但可以通過修改配置文件來處理這一問題：配置文件路徑：D:Program FilesMyS

56、QLMySQL Server 5.1 my.ini3MySQL工具使用MySQL工具是不會出現(xiàn)亂碼的，因為它們會每次連接時都修改character_set_client、character_set_results、character_set_connection的編碼。這樣對my.ini上的配置覆蓋了，也就不會出現(xiàn)亂碼了。MySQL數(shù)據(jù)庫備份與還原備份和恢復(fù)數(shù)據(jù)1生成SQL腳本在控制臺使用mysqldump命令可以用來生成指定數(shù)據(jù)庫的腳本文本，但要注意，腳本文本中只包含數(shù)據(jù)庫的內(nèi)容，而不會存在創(chuàng)建數(shù)據(jù)庫的語句！所以在恢復(fù)數(shù)據(jù)時，還需要自已手動創(chuàng)建一個數(shù)據(jù)庫之后再去恢復(fù)數(shù)據(jù)。mysqldump

57、u用戶名 p密碼 數(shù)據(jù)庫名>生成的腳本文件路徑現(xiàn)在可以在C盤下找到mydb1.sql文件了！注意，mysqldump命令是在Windows控制臺下執(zhí)行，無需登錄mysql！2執(zhí)行SQL腳本執(zhí)行SQL腳本需要登錄mysql，然后進入指定數(shù)據(jù)庫，才可以執(zhí)行SQL腳本！執(zhí)行SQL腳本不只是用來恢復(fù)數(shù)據(jù)庫，也可以在平時編寫SQL腳本，然后使用執(zhí)行SQL 腳本來操作數(shù)據(jù)庫！大家都知道，在黑屏下編寫SQL語句時，就算發(fā)現(xiàn)了錯誤，可能也不能修改了。所以我建議大家使用腳本文件來編寫SQL代碼，然后執(zhí)行之！SOURCE C:mydb1.sql注意，在執(zhí)行腳本時需要先行核查當(dāng)前數(shù)據(jù)庫中的表是否與腳本文件中的語句有沖突！例如在腳本文件中存在create table a的語句，而當(dāng)前數(shù)據(jù)庫中已經(jīng)存在了a表，那么就會出錯！還可以通過下面的方式來執(zhí)行腳本文件：mysql -uroot -p123 mydb1<c:mydb1.sqlmysql u用戶名 p密碼 數(shù)據(jù)庫<要執(zhí)行腳本文件路徑這種方式無需登錄mysql！多表查詢多表查詢有如下幾種：l 合并結(jié)果集；l 連接查詢Ø 內(nèi)連接Ø 外連接² 左外連接² 右外連接&#