前言

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

一、初始MySQL

1.什么是數據庫

數據庫 ( DataBase , 簡稱DB )

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

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

數據庫總覽 :

• 關系型數據庫 ( SQL )

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

• 非關系型數據庫 ( NOSQL )

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

2.什么是DBMS

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

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

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

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

update user set password=password('123456')where user='root'; 修改密碼
flush privileges;  刷新數據庫
show databases; 顯示所有數據庫
use dbname；打開某個數據庫
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.創建數據表

屬于DDL的一種，語法 :

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

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

4.數據值和列類型

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

4.1數值類型

4.2字符串類型

4.3日期和時間型數值類型

4.4NULL值

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

5.數據字段屬性

UnSigned

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

ZEROFILL

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

Auto_InCrement

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

• 通常用于設置主鍵 , 且為整數類型

• 可定義起始值和步長

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

NULL 和 NOT NULL

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

DEFAULT

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

-- 目標 : 創建一個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
-- 查看數據庫的定義
SHOW CREATE DATABASE school;
-- 查看數據表的定義
SHOW CREATE TABLE student;
-- 顯示表結構
DESC student;  -- 設置嚴格檢查模式(不能容錯了)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 與 InnoDB 類型：

經驗 ( 適用場合 ) :

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

6.2數據表的存儲位置

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

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

注意 :

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

6.3設置數據表字符集

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

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

三、DML語言

1.外鍵

1.1外鍵概念

如果公共關鍵字在一個關系中是主關鍵字，那么這個公共關鍵字被稱為另一個關系的外鍵。由此可見，外鍵表示了兩個關系之間的相關聯系。以另一個關系的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。

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

外鍵作用

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

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 '年級',
  `phoneNum` 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刪除外鍵

注意 : 刪除具有主外鍵關系的表時 , 要先刪子表 , 后刪主表

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

2.DML語言

數據庫意義 ： 數據存儲、數據管理

管理數據庫數據方法：

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

DML語言 ：數據操作語言

• 用于操作數據庫對象中所包含的數據

• 包括 :

• • 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...'該部分可省略 , 但添加的值務必與表結構,數據列,順序相對應,且數量一致.
-- 一次插入多條數據
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條件子句

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

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

5.刪除數據

5.1DELETE命令

語法：

DELETE FROM 表名 [WHERE condition];

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

-- 刪除最后一個數據
DELETE FROM grade WHERE gradeid = 5

5.2 TRUNCATE命令

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

語法：

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

注意：區別于DELETE命令

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

• 不同 :

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

測試：

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

四、使用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 ...]  -- 指定結果按照哪幾個字段來分組
    [HAVING]  -- 過濾分組的記錄必須滿足的次要條件
    [ORDER BY ...]  -- 指定查詢記錄按一個或多個條件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查詢的記錄從哪條至哪條

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

2.指定查詢字段

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

2.1AS 子句作為別名

作用：

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

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

2.2 DISTINCT關鍵字的使用

作用 : 去掉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 計算結果; -- 表達式
  -- 學員考試成績集體提分一分查看
  SELECT studentno,StudentResult+1 AS '提分后' FROM result;
  

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

3.where條件語句

作用：用于檢索數據表中 符合條件 的記錄

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

3.1邏輯操作符

測試

-- 滿足條件的查詢(where)
SELECT Studentno,StudentResult FROM result;
-- 查詢考試成績在95-100之間的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以寫成 &&
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到任意個字符)  _ (一個字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉%';
-- 查詢姓劉的同學,后面只有一個字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉_';
-- 查詢姓劉的同學,后面只有兩個字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '劉__';
-- 查詢姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查詢姓名中含有特殊字符的需要使用轉義符號 '\'
-- 自定義轉義符關鍵字:  ESCAPE ':'
-- =============================================
-- IN
-- =============================================
-- 查詢學號為1000,1001,1002的學生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查詢地址在北京,南京,河南洛陽的學生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛陽');
-- =============================================
-- NULL 空
-- =============================================
-- 查詢出生日期沒有填寫的同學
-- 不能直接寫=NULL , 這是代表錯誤的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查詢出生日期填寫的同學
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查詢沒有寫家庭住址的同學(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4.連接查詢

4.1JOIN 對比

測試

/*
連接查詢
    如需要多張數據表的數據進行查詢,則可通過連接運算符實現多個查詢
內連接 inner join
    查詢兩個表中的結果集中的交集
外連接 outer join
    左外連接 left join
        (以左表作為基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
    右外連接 right join
        (以右表作為基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
等值連接和非等值連接
自連接
*/
-- 查詢參加了考試的同學信息(學號,學生姓名,科目編號,分數)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,確定查詢的列來源于兩個類,student  result,連接查詢
(2):確定使用哪種連接查詢?(內連接)
*/
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
-- 左連接 (查詢了所有同學,不考試的也會查出來)
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自連接

/*
自連接
    數據表與自身進行連接
需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中
     查詢父欄目名稱和其他子欄目名稱
*/
-- 創建一個表
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','辦公信息');
-- 編寫SQL語句,將欄目的父子關系呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然后將這兩張表連接查詢(自連接)
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 語句用于根據指定的列對結果集進行排序。
    ORDER BY 語句默認按照ASC升序對記錄進行排序。
    如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。
*/
-- 查詢 數據庫結構-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
好處 : (用戶體驗,網絡傳輸,查詢壓力)
推導:
    第一頁 : 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名并且分數大于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.子查詢

*============== 子查詢 ================
什么是子查詢?
    在查詢語句中的WHERE條件子句中,又嵌套了另一個查詢語句
    嵌套查詢可由多個子查詢組成,求解的方式是由里及外;
    子查詢返回的結果一般都是集合,故而建議使用IN關鍵字;
*/
-- 查詢 數據庫結構-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;
-- 查詢課程為 高等數學-2 且分數不小于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
-- 方法二:使用連接查詢+子查詢
-- 分數不小于80分的學生的學號和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上面SQL基礎上,添加需求:課程為 高等數學-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'
)
-- 方法三:使用子查詢
-- 分步寫簡單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();  /*隨機數,返回一個0-1之間的隨機數*/
 SELECT SIGN(0); /*符號函數: 負數返回-1,正數返回1,0返回0*/

1.2字符串函數

 SELECT CHAR_LENGTH('狂神說堅持就能成功'); /*返回字符串包含的字符數*/
 SELECT CONCAT('我','愛','程序');  /*合并字符串,參數可以有多個*/
 SELECT INSERT('我愛編程helloworld',1,2,'超級熱愛');  /*替換字符串,從某個位置開始替換某個長度*/
 SELECT LOWER('KuangShen'); /*小寫*/
 SELECT UPPER('KuangShen'); /*大寫*/
 SELECT LEFT('hello,world',5);   /*從左邊截取*/
 SELECT RIGHT('hello,world',5);  /*從右邊截取*/
 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;  /*推薦*/
 -- 從含義上講，count(1) 與 count(*) 都表示對全部數據行的查詢。
 -- count(字段) 會統計該字段在表中出現的次數，忽略字段為null 的情況。即不統計字段為null 的記錄。
 -- count(*) 包括了所有的列，相當于行數，在統計結果的時候，包含字段為null 的記錄；
 -- count(1) 用1代表代碼行，在統計結果的時候，包含字段為null 的記錄 。
 /*
 很多人認為count(1)執行的效率會比count(*)高，原因是count(*)會存在全表掃描，而count(1)可以針對一個字段進行查詢。其實不然，count(1)和count(*)都會對全表進行掃描，統計所有記錄的條數，包括那些為null的記錄，因此，它們的效率可以說是相差無幾。而count(字段)則與前兩者不同，它會統計該字段不為null的記錄條數。
 下面它們之間的一些對比：
 1）在表沒有主鍵時，count(1)比count(*)快
 2）有主鍵時，主鍵作為計算條件，count(主鍵)效率最高；
 3）若表格只有一個字段，則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;

總結

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