前言

知識無底，學海無涯，到今天進入MySQL的學習(xi) 5天了，知識點雖然簡單，但是比較多，所以寫(xie) 一篇博客將MySQL的基礎寫(xie) 出來，方便自己以後查找，還有就是分享給大家。

---

一、初始MySQL

1.什麽是數據庫

數據庫 ( DataBase , 簡稱DB )

概念 : 長期存放在計算機內(nei) ,有組織,可共享的大量數據的集合,是一個(ge) 數據 “倉(cang) 庫”

作用 : 保存,並能安全管理數據(如:增刪改查等),減少冗餘(yu) …

數據庫總覽 :

• 關(guan) 係型數據庫 ( SQL )

• • MySQL , Oracle , SQL Server , SQLite , DB2 , …
  • 關係型數據庫通過外鍵關聯來建立表與表之間的關係

• 非關(guan) 係型數據庫 ( NOSQL )

• • Redis , MongoDB , …
  • 非關係型數據庫通常指數據以對象的形式存儲在數據庫中，而對象之間的關係通過每個對象自身的屬性來決定

2.什麽是DBMS

數據庫管理係統 ( DataBase Management System )

數據庫管理軟件 , 科學組織和存儲(chu) 數據 , 高效地獲取和維護數據

連接數據庫語句 : mysql -h 服務器主機地址 -u 用戶名 -p 用戶密碼

3.基本的數據庫操作命令

update user set password=password('123456')where user='root'; 修改密碼 flush privileges; 刷新數據庫 show databases; 顯示所有數據庫 use dbname；打開某個(ge) 數據庫 show tables; 顯示數據庫mysql中所有的表 describe user; 顯示表mysql數據庫中user表的列信息 create database name; 創建數據庫 use databasename; 選擇數據庫

二、數據庫操作

1.結構化查詢語句分類

2.數據庫操作

創建數據庫 :  create database [if not exists] 數據庫名;
刪除數據庫 : drop database [if exists] 數據庫名;
查看數據庫 : show databases;
使用數據庫 : use 數據庫名;

3.創建數據表

屬於(yu) DDL的一種，語法 :

create table [if not exists] `表名`( '字段名1' 列類型 [屬性][索引][注釋], '字段名2' 列類型 [屬性][索引][注釋], #... '字段名n' 列類型 [屬性][索引][注釋] )[表類型][表字符集][注釋];

說明：反引號用於(yu) 區別MySQL保留字與(yu) 普通字符而引入的 (鍵盤esc下麵的鍵).

4.數據值和列類型

列類型 : 規定數據庫中該列存放的數據類型

4.1數值類型

4.2字符串類型

4.3日期和時間型數值類型

4.4NULL值

• 理解為 “沒有值” 或 “未知值”
• 不要用NULL進行算術運算 , 結果仍為NULL

5.數據字段屬性

UnSigned

• 無符號的
• 聲明該數據列不允許負數 .

ZEROFILL

• 0填充的
• 不足位數的用0來填充 , 如int(3),5則為005

Auto_InCrement

• 自動增長的 , 每添加一條數據 , 自動在上一個(ge) 記錄數上加 1(默認)

• 通常用於(yu) 設置主鍵 , 且為(wei) 整數類型

• 可定義(yi) 起始值和步長

• • 當前表設置步長(AUTO_INCREMENT=100) : 隻影響當前表
  • SET @@auto_increment_increment=5 ; 影響所有使用自增的表(全局)

NULL 和 NOT NULL

• 默認為NULL , 即沒有插入該列的數值
• 如果設置為NOT NULL , 則該列必須有值

DEFAULT

• 默認的
• 用於設置默認值
• 例如,性別字段,默認為"男" , 否則為 “女” ; 若無指定該列的值 , 則默認值為"男"的值

-- 目標 : 創建一個(ge) school數據庫 -- 創建學生表(列,字段) -- 學號int 登錄密碼varchar(20) 姓名,性別varchar(2),出生日期(datatime),家庭住址,email -- 創建表之前 , 一定要先選擇數據庫 CREATE TABLE IF NOT EXISTS `student` ( `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '學號', `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密碼', `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性別', `birthday` datetime DEFAULT NULL COMMENT '生日', `address` varchar(100) DEFAULT NULL COMMENT '地址', `email` varchar(50) DEFAULT NULL COMMENT '郵箱', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -- 查看數據庫的定義(yi) SHOW CREATE DATABASE school; -- 查看數據表的定義(yi) SHOW CREATE TABLE student; -- 顯示表結構 DESC student; -- 設置嚴(yan) 格檢查模式(不能容錯了)SET sql_mode='STRICT_TRANS_TABLES';

6.數據表的類型

6.1設置數據表的類型

CREATE TABLE 表名(
    -- 省略一些代碼
    -- Mysql注釋
    -- 1. # 單行注釋
    -- 2. /*...*/ 多行注釋
)ENGINE = MyISAM (or InnoDB)
-- 查看mysql所支持的引擎類型 (表類型)
SHOW ENGINES;

MySQL的數據表的類型 : MyISAM , InnoDB , HEAP , BOB , CSV等…

常見的 MyISAM 與(yu) InnoDB 類型：

經驗 ( 適用場合 ) :

• 適用 MyISAM : 節約空間及相應速度
• 適用 InnoDB : 安全性 , 事務處理及多用戶操作數據表

6.2數據表的存儲位置

MySQL數據表以文件方式存放在磁盤中

• 包括表文件 , 數據文件 , 以及數據庫的選項文件
• 位置 : Mysql安裝目錄\data\下存放數據表 . 目錄名對應數據庫名 , 該目錄下文件名對應數據表 .

注意 :

• * . frm – 表結構定義文件
• * . MYD – 數據文件 ( data )
• * . MYI – 索引文件 ( index )
• InnoDB類型數據表隻有一個 *.frm文件 , 以及上一級目錄的ibdata1文件
• MyISAM類型數據表對應三個文件

6.3設置數據表字符集

我們(men) 可為(wei) 數據庫,數據表,數據列設定不同的字符集，設定方法 :

• 創建時通過命令來設置 , 如 : CREATE TABLE 表名()CHARSET = utf8;
• 如無設定 , 則根據MySQL數據庫配置文件 my.ini 中的參數設定

7.修改數據庫

7.1修改表 ( ALTER TABLE )

修改表名 :ALTER TABLE 舊表名 RENAME AS 新表名 添加字段 : ALTER TABLE 表名 ADD字段名 列屬性[屬性] 修改字段 : - ALTER TABLE 表名 MODIFY 字段名 列類型[屬性] - ALTER TABLE 表名 CHANGE 舊字段名 新字段名 列屬性[屬性] 刪除字段 : ALTER TABLE 表名 DROP 字段名

7.2刪除數據表

語法：DROP TABLE [IF EXISTS] 表名

• IF EXISTS為可選 , 判斷是否存在該數據表
• 如刪除不存在的數據表會拋出錯誤

7.3其他

1. 可用反引號（`）為(wei) 標識符（庫名、表名、字段名、索引、別名）包裹，以避免與(yu) 關(guan) 鍵字重名！中文也可以作為(wei) 標識符！ 2. 每個(ge) 庫目錄存在一個(ge) 保存當前數據庫的選項文件db.opt。 3. 注釋： 單行注釋 # 注釋內(nei) 容 多行注釋 /* 注釋內(nei) 容 */ 單行注釋 -- 注釋內(nei) 容 (標準SQL注釋風格，要求雙破折號後加一空格符（空格、TAB、換行等）) 4. 模式通配符： _ 任意單個(ge) 字符 % 任意多個(ge) 字符，甚至包括零字符 單引號需要進行轉義(yi) \' 5. CMD命令行內(nei) 的語句結束符可以為(wei) ";", "\G", "\g"，僅(jin) 影響顯示結果。其他地方還是用分號結束。delimiter 可修改當前對話的語句結束符。 6. SQL對大小寫(xie) 不敏感 （關(guan) 鍵字） 7. 清除已有語句：\c

三、DML語言

1.外鍵

1.1外鍵概念

如果公共關(guan) 鍵字在一個(ge) 關(guan) 係中是主關(guan) 鍵字，那麽(me) 這個(ge) 公共關(guan) 鍵字被稱為(wei) 另一個(ge) 關(guan) 係的外鍵。由此可見，外鍵表示了兩(liang) 個(ge) 關(guan) 係之間的相關(guan) 聯係。以另一個(ge) 關(guan) 係的外鍵作主關(guan) 鍵字的表被稱為(wei) 主表，具有此外鍵的表被稱為(wei) 主表的從(cong) 表。

在實際操作中，將一個(ge) 表的值放入第二個(ge) 表來表示關(guan) 聯，所使用的值是第一個(ge) 表的主鍵值(在必要時可包括複合主鍵值)。此時，第二個(ge) 表中保存這些值的屬性稱為(wei) 外鍵(foreign key)。

外鍵作用

保持數據一致性，完整性，主要目的是控製存儲(chu) 在外鍵表中的數據,約束。使兩(liang) 張表形成關(guan) 聯，外鍵隻能引用外表中的列的值或使用空值。

1.2創建外鍵

建表時指定外鍵約束

-- 創建外鍵的方式一 : 創建子表同時創建外鍵 -- 年級表 (id\年級名稱) CREATE TABLE `grade` ( `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年級ID', `gradename` VARCHAR(50) NOT NULL COMMENT '年級名稱', PRIMARY KEY (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 -- 學生信息表 (學號,姓名,性別,年級,手機,地址,出生日期,郵箱,身份證號) CREATE TABLE `student` ( `studentno` INT(4) NOT NULL COMMENT '學號', `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `sex` TINYINT(1) DEFAULT '1' COMMENT '性別', `gradeid` INT(10) DEFAULT NULL COMMENT '年級', `phonesNum` VARCHAR(50) NOT NULL COMMENT '手機', `address` VARCHAR(255) DEFAULT NULL COMMENT '地址', `borndate` DATETIME DEFAULT NULL COMMENT '生日', `email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱', `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號', PRIMARY KEY (`studentno`), KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8

建表後修改

-- 創建外鍵方式二 : 創建子表完畢後,修改子表添加外鍵 ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

1.3刪除外鍵

注意 : 刪除具有主外鍵關(guan) 係的表時 , 要先刪子表 , 後刪主表

-- 刪除外鍵 ALTER TABLE student DROP FOREIGN KEY FK_gradeid; -- 發現執行完上麵的,索引還在,所以還要刪除索引 -- 注:這個(ge) 索引是建立外鍵的時候默認生成的 ALTER TABLE student DROP INDEX FK_gradeid;

2.DML語言

數據庫意義(yi) ： 數據存儲(chu) 、數據管理

管理數據庫數據方法：

• 通過SQLyog等管理工具管理數據庫數據
• 通過DML語句管理數據庫數據

DML語言 ：數據操作語言

• 用於(yu) 操作數據庫對象中所包含的數據

• 包括 :

• • INSERT (添加數據語句)
  • UPDATE (更新數據語句)
  • DELETE (刪除數據語句)

3.添加數據

3.1INSERT命令

語法：

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

注意 :

• 字段或值之間用英文逗號隔開 .
• ’ 字段1,字段2…’ 該部分可省略 , 但添加的值務必與表結構,數據列,順序相對應,且數量一致 .
• 可同時插入多條數據 , values 後用英文逗號隔開 .

-- 使用語句如何增加語句? -- 語法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3') INSERT INTO grade(gradename) VALUES ('大一'); -- 主鍵自增,那能否省略呢? INSERT INTO grade VALUES ('大二'); -- 查詢:INSERT INTO grade VALUE ('大二')錯誤代碼：1136 Column count doesn`t match value count at row 1 -- 結論:'字段1,字段2...'該部分可省略 , 但添加的值務必與(yu) 表結構,數據列,順序相對應,且數量一致. -- 一次插入多條數據 INSERT INTO grade(gradename) VALUES ('大三'),('大四');

4.修改數據

4.1update命令

語法：

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

注意 :

• column_name 為要更改的數據列
• value 為修改後的數據 , 可以為變量 , 具體指 , 表達式或者嵌套的SELECT結果
• condition 為篩選條件 , 如不指定則修改該表的所有列數據

4.2where條件子句

可以簡單的理解為(wei) : 有條件地從(cong) 表中篩選數據

-- 修改年級信息 UPDATE grade SET gradename = '高中' WHERE gradeid = 1;

5.刪除數據

5.1DELETE命令

語法：

DELETE FROM 表名 [WHERE condition];

注意：condition為(wei) 篩選條件 , 如不指定則刪除該表的所有列數據

-- 刪除最後一個(ge) 數據 DELETE FROM grade WHERE gradeid = 5

5.2 TRUNCATE命令

作用：用於(yu) 完全清空表數據 , 但表結構 , 索引 , 約束等不變 ;

語法：

TRUNCATE [TABLE] table_name; -- 清空年級表 TRUNCATE grade

注意：區別於(yu) DELETE命令

• 相同 : 都能刪除數據 , 不刪除表結構 , 但TRUNCATE速度更快

• 不同 :

• • 使用TRUNCATE TABLE 重新設置AUTO_INCREMENT計數器
  • 使用TRUNCATE TABLE不會對事務有影響 （事務後麵會說）

測試：

-- 創建一個(ge) 測試表 CREATE TABLE `test` ( `id` INT(4) NOT NULL AUTO_INCREMENT, `coll` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 -- 插入幾個(ge) 測試數據 INSERT INTO test(coll) VALUES('row1'),('row2'),('row3'); -- 刪除表數據(不帶where條件的delete) DELETE FROM test; -- 結論:如不指定Where則刪除該表的所有列數據,自增當前值依然從(cong) 原來基礎上進行,會(hui) 記錄日誌. -- 刪除表數據(truncate) TRUNCATE TABLE test; -- 結論:truncate刪除數據,自增當前值會(hui) 恢複到初始值重新開始;不會(hui) 記錄日誌. -- 同樣使用DELETE清空不同引擎的數據庫表數據.重啟數據庫服務後 -- InnoDB : 自增列從(cong) 初始值重新開始 (因為(wei) 是存儲(chu) 在內(nei) 存中,斷電即失) -- MyISAM : 自增列依然從(cong) 上一個(ge) 自增數據基礎上開始 (存在文件中,不會(hui) 丟(diu) 失)

四、使用DQL查詢數據

1.DQL語言

DQL( Data Query Language 數據查詢語言 )

• 查詢數據庫數據 , 如SELECT語句
• 簡單的單表查詢或多表的複雜查詢和嵌套查詢
• 是數據庫語言中最核心,最重要的語句
• 使用頻率最高的語句

1.1SELECT語法

SELECT [ALL | DISTINCT] {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} FROM table_name [as table_alias] [left | right | inner join table_name2] -- 聯合查詢 [WHERE ...] -- 指定結果需滿足的條件 [GROUP BY ...] -- 指定結果按照哪幾個(ge) 字段來分組 [HAVING] -- 過濾分組的記錄必須滿足的次要條件 [ORDER BY ...] -- 指定查詢記錄按一個(ge) 或多個(ge) 條件排序 [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查詢的記錄從(cong) 哪條至哪條

注意 : [ ] 括號代表可選的 , { }括號代表必選得

2.指定查詢字段

-- 查詢表中所有的數據列結果 , 采用 **" \* "** 符號; 但是效率低，不推薦 . -- 查詢所有學生信息 SELECT * FROM student; -- 查詢指定列(學號 , 姓名) SELECT studentno,studentname FROM student;

2.1AS 子句作為(wei) 別名

作用：

• 可給數據列取一個新別名
• 可給表取一個新別名
• 可把經計算或總結的結果用另一個新名稱來代替

-- 這裏是為(wei) 列取別名(當然as關(guan) 鍵詞可以省略) SELECT studentno AS 學號,studentname AS 姓名 FROM student; -- 使用as也可以為(wei) 表取別名 SELECT studentno AS 學號,studentname AS 姓名 FROM student AS s; -- 使用as,為(wei) 查詢結果取一個(ge) 新名字 -- CONCAT()函數拚接字符串 SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

2.2 DISTINCT關(guan) 鍵字的使用

作用 : 去掉SELECT查詢返回的記錄結果中重複的記錄 ( 返回所有列的值都相同 ) , 隻返回一條

-- # 查看哪些同學參加了考試(學號) 去除重複項 SELECT * FROM result; -- 查看考試成績 SELECT studentno FROM result; -- 查看哪些同學參加了考試 SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重複項 , (默認是ALL)

2.3 使用表達式的列

數據庫中的表達式 : 一般由文本值 , 列值 , NULL , 函數和操作符等組成

應用場景 :

• SELECT語句返回結果列中使用

• SELECT語句中的ORDER BY , HAVING等子句中使用

• DML語句中的 where 條件語句中使用表達式

  -- selcet查詢中可以使用表達式 SELECT @@auto_increment_increment; -- 查詢自增步長 SELECT VERSION(); -- 查詢版本號 SELECT 100*3-1 AS 計算結果; -- 表達式 -- 學員考試成績集體(ti) 提分一分查看 SELECT studentno,StudentResult+1 AS '提分後' FROM result;

• 避免SQL返回結果中包含 ’ . ’ , ’ * ’ 和括號等幹擾開發語言程序.

3.where條件語句

作用：用於(yu) 檢索數據表中 符合條件 的記錄

搜索條件可由一個(ge) 或多個(ge) 邏輯表達式組成 , 結果一般為(wei) 真或假.

3.1邏輯操作符

測試

-- 滿足條件的查詢(where) SELECT Studentno,StudentResult FROM result; -- 查詢考試成績在95-100之間的 SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100; -- AND也可以寫(xie) 成 && SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 && StudentResult<=100; -- 模糊查詢(對應的詞:精確查詢) SELECT Studentno,StudentResult FROM result WHERE StudentResult BETWEEN 95 AND 100; -- 除了1000號同學,要其他同學的成績 SELECT studentno,studentresult FROM result WHERE studentno!=1000; -- 使用NOT SELECT studentno,studentresult FROM result WHERE NOT studentno=1000;

3.2模糊查詢 ：比較操作符

注意：

• 數值數據類型的記錄之間才能進行算術運算 ;
• 相同數據類型的數據之間才能進行比較 ;

測試：

-- 模糊查詢 between and \ like \ in \ null -- ============================================= -- LIKE -- ============================================= -- 查詢姓劉的同學的學號及姓名 -- like結合使用的通配符 : % (代表0到任意個(ge) 字符) _ (一個(ge) 字符) SELECT studentno,studentname FROM student WHERE studentname LIKE '劉%'; -- 查詢姓劉的同學,後麵隻有一個(ge) 字的 SELECT studentno,studentname FROM student WHERE studentname LIKE '劉_'; -- 查詢姓劉的同學,後麵隻有兩(liang) 個(ge) 字的 SELECT studentno,studentname FROM student WHERE studentname LIKE '劉__'; -- 查詢姓名中含有 嘉 字的 SELECT studentno,studentname FROM student WHERE studentname LIKE '%嘉%'; -- 查詢姓名中含有特殊字符的需要使用轉義(yi) 符號 '\' -- 自定義(yi) 轉義(yi) 符關(guan) 鍵字: ESCAPE ':' -- ============================================= -- IN -- ============================================= -- 查詢學號為(wei) 1000,1001,1002的學生姓名 SELECT studentno,studentname FROM student WHERE studentno IN (1000,1001,1002); -- 查詢地址在北京,南京,河南洛陽的學生 SELECT studentno,studentname,address FROM student WHERE address IN ('北京','南京','河南洛陽'); -- ============================================= -- NULL 空 -- ============================================= -- 查詢出生日期沒有填寫(xie) 的同學 -- 不能直接寫(xie) =NULL , 這是代表錯誤的 , 用 is null SELECT studentname FROM student WHERE BornDate IS NULL; -- 查詢出生日期填寫(xie) 的同學 SELECT studentname FROM student WHERE BornDate IS NOT NULL; -- 查詢沒有寫(xie) 家庭住址的同學(空字符串不等於(yu) null) SELECT studentname FROM student WHERE Address='' OR Address IS NULL;

4.連接查詢

4.1JOIN 對比

測試

/* 連接查詢 如需要多張數據表的數據進行查詢,則可通過連接運算符實現多個(ge) 查詢 內(nei) 連接 inner join 查詢兩(liang) 個(ge) 表中的結果集中的交集 外連接 outer join 左外連接 left join (以左表作為(wei) 基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充) 右外連接 right join (以右表作為(wei) 基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充) 等值連接和非等值連接 自連接 */ -- 查詢參加了考試的同學信息(學號,學生姓名,科目編號,分數) SELECT * FROM student; SELECT * FROM result; /*思路: (1):分析需求,確定查詢的列來源於(yu) 兩(liang) 個(ge) 類,student result,連接查詢 (2):確定使用哪種連接查詢?(內(nei) 連接) */ SELECT s.studentno,studentname,subjectno,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno -- 右連接(也可實現) SELECT s.studentno,studentname,subjectno,StudentResult FROM student s RIGHT JOIN result r ON r.studentno = s.studentno -- 等值連接 SELECT s.studentno,studentname,subjectno,StudentResult FROM student s , result r WHERE r.studentno = s.studentno -- 左連接 (查詢了所有同學,不考試的也會(hui) 查出來) SELECT s.studentno,studentname,subjectno,StudentResult FROM student s LEFT JOIN result r ON r.studentno = s.studentno -- 查一下缺考的同學(左連接應用場景) SELECT s.studentno,studentname,subjectno,StudentResult FROM student s LEFT JOIN result r ON r.studentno = s.studentno WHERE StudentResult IS NULL -- 思考題:查詢參加了考試的同學信息(學號,學生姓名,科目名,分數) SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON sub.subjectno = r.subjectno

4.2自連接

/*
自連接
    數據表與(yu) 自身進行連接
需求:從(cong) 一個(ge) 包含欄目ID , 欄目名稱和父欄目ID的表中
     查詢父欄目名稱和其他子欄目名稱
*/
-- 創建一個(ge) 表
CREATE TABLE `category` (
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-- 插入數據
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技術'),
('3','1','軟件開發'),
('4','3','數據庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公信息');
-- 編寫(xie) SQL語句,將欄目的父子關(guan) 係呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩(liang) 張一模一樣的表,然後將這兩(liang) 張表連接查詢(自連接)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
-- 思考題:查詢參加了考試的同學信息(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
-- 查詢學員及所屬的年級(學號,學生姓名,年級名)
SELECT studentno AS 學號,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`
-- 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid
-- 查詢 數據庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='數據庫結構-1'

5.排序和分頁

測試

/*============== 排序 ================ 語法 : ORDER BY ORDER BY 語句用於(yu) 根據指定的列對結果集進行排序。 ORDER BY 語句默認按照ASC升序對記錄進行排序。 如果您希望按照降序對記錄進行排序，可以使用 DESC 關(guan) 鍵字。 */ -- 查詢 數據庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績) -- 按成績降序排序 SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON r.subjectno = sub.subjectno WHERE subjectname='數據庫結構-1' ORDER BY StudentResult DESC /*============== 分頁 ================ 語法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 好處 : (用戶體(ti) 驗,網絡傳(chuan) 輸,查詢壓力) 推導: 第一頁 : limit 0,5 第二頁 : limit 5,5 第三頁 : limit 10,5 ...... 第N頁 : limit (pageNo-1)*pageSzie,pageSzie [pageNo:頁碼,pageSize:單頁麵顯示條數] */ -- 每頁顯示5條數據 SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON r.subjectno = sub.subjectno WHERE subjectname='數據庫結構-1' ORDER BY StudentResult DESC , studentno LIMIT 0,5 -- 查詢 JAVA第一學年 課程成績前10名並且分數大於(yu) 80的學生信息(學號,姓名,課程名,分數) SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON r.subjectno = sub.subjectno WHERE subjectname='JAVA第一學年' ORDER BY StudentResult DESC LIMIT 0,10

6.子查詢

*============== 子查詢 ================
什麽(me) 是子查詢?
    在查詢語句中的WHERE條件子句中,又嵌套了另一個(ge) 查詢語句
    嵌套查詢可由多個(ge) 子查詢組成,求解的方式是由裏及外;
    子查詢返回的結果一般都是集合,故而建議使用IN關(guan) 鍵字;
*/
-- 查詢 數據庫結構-1 的所有考試結果(學號,科目編號,成績),並且成績降序排列
-- 方法一:使用連接查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '數據庫結構-1'
ORDER BY studentresult DESC;
-- 方法二:使用子查詢(執行順序:由裏及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = '數據庫結構-1'
)
ORDER BY studentresult DESC;
-- 查詢課程為(wei)  高等數學-2 且分數不小於(yu) 80分的學生的學號和姓名
-- 方法一:使用連接查詢
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等數學-2' AND StudentResult>=80
-- 方法二:使用連接查詢+子查詢
-- 分數不小於(yu) 80分的學生的學號和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上麵SQL基礎上,添加需求:課程為(wei)  高等數學-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = '高等數學-2'
)
-- 方法三:使用子查詢
-- 分步寫(xie) 簡單sql語句,然後將其嵌套起來
SELECT studentno,studentname FROM student WHERE studentno IN(
    SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
        SELECT subjectno FROM `subject` WHERE subjectname = '高等數學-2'
    )
)

五、MySQL函數

1.常用函數

1.1數據函數

 SELECT ABS(-8);  /*絕對值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*隨機數,返回一個(ge) 0-1之間的隨機數*/
 SELECT SIGN(0); /*符號函數: 負數返回-1,正數返回1,0返回0*/

1.2字符串函數

 SELECT CHAR_LENGTH('狂神說堅持就能成功'); /*返回字符串包含的字符數*/
 SELECT CONCAT('我','愛','程序');  /*合並字符串,參數可以有多個(ge) */
 SELECT INSERT('我愛編程helloworld',1,2,'超級熱愛');  /*替換字符串,從(cong) 某個(ge) 位置開始替換某個(ge) 長度*/
 SELECT LOWER('KuangShen'); /*小寫(xie) */
 SELECT UPPER('KuangShen'); /*大寫(xie) */
 SELECT LEFT('hello,world',5);   /*從(cong) 左邊截取*/
 SELECT RIGHT('hello,world',5);  /*從(cong) 右邊截取*/
 SELECT REPLACE('狂神說堅持就能成功','堅持','努力');  /*替換字符串*/
 SELECT SUBSTR('狂神說堅持就能成功',4,6); /*截取字符串,開始和長度*/
 SELECT REVERSE('狂神說堅持就能成功'); /*反轉
 -- 查詢姓周的同學,改成鄒
 SELECT REPLACE(studentname,'周','鄒') AS 新名字
 FROM student WHERE studentname LIKE '周%';

1.3日期和時間函數

 SELECT CURRENT_DATE();   /*獲取當前日期*/
 SELECT CURDATE();   /*獲取當前日期*/
 SELECT NOW();   /*獲取當前日期和時間*/
 SELECT LOCALTIME();   /*獲取當前日期和時間*/
 SELECT SYSDATE();   /*獲取當前日期和時間*/
 -- 獲取年月日,時分秒
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

1.4係統信息函數

 SELECT VERSION();  /*版本*/
 SELECT USER();     /*用戶*/

2.聚合函數

-- 聚合函數
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推薦*/
 -- 從(cong) 含義(yi) 上講，count(1) 與(yu)  count(*) 都表示對全部數據行的查詢。
 -- count(字段) 會(hui) 統計該字段在表中出現的次數，忽略字段為(wei) null 的情況。即不統計字段為(wei) null 的記錄。
 -- count(*) 包括了所有的列，相當於(yu) 行數，在統計結果的時候，包含字段為(wei) null 的記錄；
 -- count(1) 用1代表代碼行，在統計結果的時候，包含字段為(wei) null 的記錄 。
 /*
 很多人認為(wei) count(1)執行的效率會(hui) 比count(*)高，原因是count(*)會(hui) 存在全表掃描，而count(1)可以針對一個(ge) 字段進行查詢。其實不然，count(1)和count(*)都會(hui) 對全表進行掃描，統計所有記錄的條數，包括那些為(wei) null的記錄，因此，它們(men) 的效率可以說是相差無幾。而count(字段)則與(yu) 前兩(liang) 者不同，它會(hui) 統計該字段不為(wei) null的記錄條數。
 下麵它們(men) 之間的一些對比：
 1）在表沒有主鍵時，count(1)比count(*)快
 2）有主鍵時，主鍵作為(wei) 計算條件，count(主鍵)效率最高；
 3）若表格隻有一個(ge) 字段，則count(*)效率較高。
 */
 SELECT SUM(StudentResult) AS 總和 FROM result;
 SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result;
 SELECT MIN(StudentResult) AS 最低分 FROM result;

總結

重要：
查詢語句的書(shu) 寫(xie) 順序和執行順序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查詢語句的執行順序
from ===> where ===> group by ===> having ===> select ===> order by ===> limi