前言

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

---

一、初始MySQL

1.什么是數(shù)據(jù)庫

數(shù)據(jù)庫 ( DataBase , 簡稱DB )

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

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

數(shù)據(jù)庫總覽 :

• 關(guān)系型數(shù)據(jù)庫 ( SQL )

• • MySQL , Oracle , SQL Server , SQLite , DB2 , …
  • 關(guān)系型數(shù)據(jù)庫通過外鍵關(guān)聯(lián)來建立表與表之間的關(guān)系

• 非關(guān)系型數(shù)據(jù)庫 ( NOSQL )

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

2.什么是DBMS

數(shù)據(jù)庫管理系統(tǒng) ( DataBase Management System )

數(shù)據(jù)庫管理軟件 , 科學(xué)組織和存儲數(shù)據(jù) , 高效地獲取和維護(hù)數(shù)據(jù)

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

3.基本的數(shù)據(jù)庫操作命令

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

二、數(shù)據(jù)庫操作

1.結(jié)構(gòu)化查詢語句分類

2.數(shù)據(jù)庫操作

創(chuàng)建數(shù)據(jù)庫 :  create database [if not exists] 數(shù)據(jù)庫名;
刪除數(shù)據(jù)庫 : drop database [if exists] 數(shù)據(jù)庫名;
查看數(shù)據(jù)庫 : show databases;
使用數(shù)據(jù)庫 : use 數(shù)據(jù)庫名;

3.創(chuàng)建數(shù)據(jù)表

屬于DDL的一種，語法 :

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

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

4.數(shù)據(jù)值和列類型

列類型 : 規(guī)定數(shù)據(jù)庫中該列存放的數(shù)據(jù)類型

4.1數(shù)值類型

4.2字符串類型

4.3日期和時間型數(shù)值類型

4.4NULL值

• 理解為 “沒有值” 或 “未知值”
• 不要用NULL進(jìn)行算術(shù)運算 , 結(jié)果仍為NULL

5.數(shù)據(jù)字段屬性

UnSigned

• 無符號的
• 聲明該數(shù)據(jù)列不允許負(fù)數(shù) .

ZEROFILL

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

Auto_InCrement

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

• 通常用于設(shè)置主鍵 , 且為整數(shù)類型

• 可定義起始值和步長

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

NULL 和 NOT NULL

• 默認(rèn)為NULL , 即沒有插入該列的數(shù)值
• 如果設(shè)置為NOT NULL , 則該列必須有值

DEFAULT

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

-- 目標(biāo) : 創(chuàng)建一個school數(shù)據(jù)庫
-- 創(chuàng)建學(xué)生表(列,字段)
-- 學(xué)號int 登錄密碼varchar(20) 姓名,性別varchar(2),出生日期(datatime),家庭住址,email
-- 創(chuàng)建表之前 , 一定要先選擇數(shù)據(jù)庫
CREATE TABLE IF NOT EXISTS `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號',
  `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
-- 查看數(shù)據(jù)庫的定義
SHOW CREATE DATABASE school;
-- 查看數(shù)據(jù)表的定義
SHOW CREATE TABLE student;
-- 顯示表結(jié)構(gòu)
DESC student;  -- 設(shè)置嚴(yán)格檢查模式(不能容錯了)SET sql_mode='STRICT_TRANS_TABLES';

6.數(shù)據(jù)表的類型

6.1設(shè)置數(shù)據(jù)表的類型

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

MySQL的數(shù)據(jù)表的類型 : MyISAM , InnoDB , HEAP , BOB , CSV等…

常見的 MyISAM 與 InnoDB 類型：

經(jīng)驗 ( 適用場合 ) :

• 適用 MyISAM : 節(jié)約空間及相應(yīng)速度
• 適用 InnoDB : 安全性 , 事務(wù)處理及多用戶操作數(shù)據(jù)表

6.2數(shù)據(jù)表的存儲位置

MySQL數(shù)據(jù)表以文件方式存放在磁盤中

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

注意 :

• * . frm – 表結(jié)構(gòu)定義文件
• * . MYD – 數(shù)據(jù)文件 ( data )
• * . MYI – 索引文件 ( index )
• InnoDB類型數(shù)據(jù)表只有一個 *.frm文件 , 以及上一級目錄的ibdata1文件
• MyISAM類型數(shù)據(jù)表對應(yīng)三個文件

6.3設(shè)置數(shù)據(jù)表字符集

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

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

7.修改數(shù)據(jù)庫

7.1修改表 ( ALTER TABLE )

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

7.2刪除數(shù)據(jù)表

語法：DROP TABLE [IF EXISTS] 表名

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

7.3其他

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

三、DML語言

1.外鍵

1.1外鍵概念

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

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

外鍵作用

保持?jǐn)?shù)據(jù)一致性，完整性，主要目的是控制存儲在外鍵表中的數(shù)據(jù),約束。使兩張表形成關(guān)聯(lián)，外鍵只能引用外表中的列的值或使用空值。

1.2創(chuàng)建外鍵

建表時指定外鍵約束

-- 創(chuàng)建外鍵的方式一 : 創(chuàng)建子表同時創(chuàng)建外鍵
-- 年級表 (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
-- 學(xué)生信息表 (學(xué)號,姓名,性別,年級,手機,地址,出生日期,郵箱,身份證號)
CREATE TABLE `student` (
  `studentno` INT(4) NOT NULL COMMENT '學(xué)號',
  `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

建表后修改

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

1.3刪除外鍵

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

-- 刪除外鍵
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 發(fā)現(xiàn)執(zhí)行完上面的,索引還在,所以還要刪除索引
-- 注:這個索引是建立外鍵的時候默認(rèn)生成的
ALTER TABLE student DROP INDEX FK_gradeid;

2.DML語言

數(shù)據(jù)庫意義 ： 數(shù)據(jù)存儲、數(shù)據(jù)管理

管理數(shù)據(jù)庫數(shù)據(jù)方法：

• 通過SQLyog等管理工具管理數(shù)據(jù)庫數(shù)據(jù)
• 通過DML語句管理數(shù)據(jù)庫數(shù)據(jù)

DML語言 ：數(shù)據(jù)操作語言

• 用于操作數(shù)據(jù)庫對象中所包含的數(shù)據(jù)

• 包括 :

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

3.添加數(shù)據(jù)

3.1INSERT命令

語法：

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

注意 :

• 字段或值之間用英文逗號隔開 .
• ’ 字段1,字段2…’ 該部分可省略 , 但添加的值務(wù)必與表結(jié)構(gòu),數(shù)據(jù)列,順序相對應(yīng),且數(shù)量一致 .
• 可同時插入多條數(shù)據(jù) , 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
-- 結(jié)論:'字段1,字段2...'該部分可省略 , 但添加的值務(wù)必與表結(jié)構(gòu),數(shù)據(jù)列,順序相對應(yīng),且數(shù)量一致.
-- 一次插入多條數(shù)據(jù)
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

4.修改數(shù)據(jù)

4.1update命令

語法：

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

注意 :

• column_name 為要更改的數(shù)據(jù)列
• value 為修改后的數(shù)據(jù) , 可以為變量 , 具體指 , 表達(dá)式或者嵌套的SELECT結(jié)果
• condition 為篩選條件 , 如不指定則修改該表的所有列數(shù)據(jù)

4.2where條件子句

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

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

5.刪除數(shù)據(jù)

5.1DELETE命令

語法：

DELETE FROM 表名 [WHERE condition];

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

-- 刪除最后一個數(shù)據(jù)
DELETE FROM grade WHERE gradeid = 5

5.2 TRUNCATE命令

作用：用于完全清空表數(shù)據(jù) , 但表結(jié)構(gòu) , 索引 , 約束等不變 ;

語法：

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

注意：區(qū)別于DELETE命令

• 相同 : 都能刪除數(shù)據(jù) , 不刪除表結(jié)構(gòu) , 但TRUNCATE速度更快

• 不同 :

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

測試：

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

四、使用DQL查詢數(shù)據(jù)

1.DQL語言

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

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

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]  -- 聯(lián)合查詢
    [WHERE ...]  -- 指定結(jié)果需滿足的條件
    [GROUP BY ...]  -- 指定結(jié)果按照哪幾個字段來分組
    [HAVING]  -- 過濾分組的記錄必須滿足的次要條件
    [ORDER BY ...]  -- 指定查詢記錄按一個或多個條件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查詢的記錄從哪條至哪條

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

2.指定查詢字段

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

2.1AS 子句作為別名

作用：

• 可給數(shù)據(jù)列取一個新別名
• 可給表取一個新別名
• 可把經(jīng)計算或總結(jié)的結(jié)果用另一個新名稱來代替

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

2.2 DISTINCT關(guān)鍵字的使用

作用 : 去掉SELECT查詢返回的記錄結(jié)果中重復(fù)的記錄 ( 返回所有列的值都相同 ) , 只返回一條

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

2.3 使用表達(dá)式的列

數(shù)據(jù)庫中的表達(dá)式 : 一般由文本值 , 列值 , NULL , 函數(shù)和操作符等組成

應(yīng)用場景 :

• SELECT語句返回結(jié)果列中使用

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

• DML語句中的 where 條件語句中使用表達(dá)式

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

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

3.where條件語句

作用：用于檢索數(shù)據(jù)表中 符合條件 的記錄

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

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;
-- 模糊查詢(對應(yīng)的詞:精確查詢)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000號同學(xué),要其他同學(xué)的成績
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;
-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

3.2模糊查詢 ：比較操作符

注意：

• 數(shù)值數(shù)據(jù)類型的記錄之間才能進(jìn)行算術(shù)運算 ;
• 相同數(shù)據(jù)類型的數(shù)據(jù)之間才能進(jìn)行比較 ;

測試：

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

4.連接查詢

4.1JOIN 對比

測試

/*
連接查詢
    如需要多張數(shù)據(jù)表的數(shù)據(jù)進(jìn)行查詢,則可通過連接運算符實現(xiàn)多個查詢
內(nèi)連接 inner join
    查詢兩個表中的結(jié)果集中的交集
外連接 outer join
    左外連接 left join
        (以左表作為基準(zhǔn),右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
    右外連接 right join
        (以右表作為基準(zhǔn),左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
等值連接和非等值連接
自連接
*/
-- 查詢參加了考試的同學(xué)信息(學(xué)號,學(xué)生姓名,科目編號,分?jǐn)?shù))
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,確定查詢的列來源于兩個類,student  result,連接查詢
(2):確定使用哪種連接查詢?(內(nèi)連接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 右連接(也可實現(xiàn))
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
-- 左連接 (查詢了所有同學(xué),不考試的也會查出來)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
-- 查一下缺考的同學(xué)(左連接應(yīng)用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 思考題:查詢參加了考試的同學(xué)信息(學(xué)號,學(xué)生姓名,科目名,分?jǐn)?shù))
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自連接

/*
自連接
    數(shù)據(jù)表與自身進(jìn)行連接
需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中
     查詢父欄目名稱和其他子欄目名稱
*/
-- 創(chuàng)建一個表
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
-- 插入數(shù)據(jù)
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技術(shù)'),
('3','1','軟件開發(fā)'),
('4','3','數(shù)據(jù)庫'),
('5','1','美術(shù)設(shè)計'),
('6','3','web開發(fā)'),
('7','5','ps技術(shù)'),
('8','2','辦公信息');
-- 編寫SQL語句,將欄目的父子關(guān)系呈現(xiàn)出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然后將這兩張表連接查詢(自連接)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
-- 思考題:查詢參加了考試的同學(xué)信息(學(xué)號,學(xué)生姓名,科目名,分?jǐn)?shù))
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
-- 查詢學(xué)員及所屬的年級(學(xué)號,學(xué)生姓名,年級名)
SELECT studentno AS 學(xué)號,studentname AS 學(xué)生姓名,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
-- 查詢 數(shù)據(jù)庫結(jié)構(gòu)-1 的所有考試結(jié)果(學(xué)號 學(xué)生姓名 科目名稱 成績)
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='數(shù)據(jù)庫結(jié)構(gòu)-1'

5.排序和分頁

測試

/*============== 排序 ================
語法 : ORDER BY
    ORDER BY 語句用于根據(jù)指定的列對結(jié)果集進(jìn)行排序。
    ORDER BY 語句默認(rèn)按照ASC升序?qū)τ涗涍M(jìn)行排序。
    如果您希望按照降序?qū)τ涗涍M(jìn)行排序，可以使用 DESC 關(guān)鍵字。
*/
-- 查詢 數(shù)據(jù)庫結(jié)構(gòu)-1 的所有考試結(jié)果(學(xué)號 學(xué)生姓名 科目名稱 成績)
-- 按成績降序排序
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='數(shù)據(jù)庫結(jié)構(gòu)-1'
ORDER BY StudentResult DESC
/*============== 分頁 ================
語法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好處 : (用戶體驗,網(wǎng)絡(luò)傳輸,查詢壓力)
推導(dǎo):
    第一頁 : limit 0,5
    第二頁 : limit 5,5
    第三頁 : limit 10,5
    ......
    第N頁 : limit (pageNo-1)*pageSzie,pageSzie
    [pageNo:頁碼,pageSize:單頁面顯示條數(shù)]
*/
-- 每頁顯示5條數(shù)據(jù)
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='數(shù)據(jù)庫結(jié)構(gòu)-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- 查詢 JAVA第一學(xué)年 課程成績前10名并且分?jǐn)?shù)大于80的學(xué)生信息(學(xué)號,姓名,課程名,分?jǐn)?shù))
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第一學(xué)年'
ORDER BY StudentResult DESC
LIMIT 0,10

6.子查詢

*============== 子查詢 ================
什么是子查詢?
    在查詢語句中的WHERE條件子句中,又嵌套了另一個查詢語句
    嵌套查詢可由多個子查詢組成,求解的方式是由里及外;
    子查詢返回的結(jié)果一般都是集合,故而建議使用IN關(guān)鍵字;
*/
-- 查詢 數(shù)據(jù)庫結(jié)構(gòu)-1 的所有考試結(jié)果(學(xué)號,科目編號,成績),并且成績降序排列
-- 方法一:使用連接查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '數(shù)據(jù)庫結(jié)構(gòu)-1'
ORDER BY studentresult DESC;
-- 方法二:使用子查詢(執(zhí)行順序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
    SELECT subjectno FROM `subject`
    WHERE subjectname = '數(shù)據(jù)庫結(jié)構(gòu)-1'
)
ORDER BY studentresult DESC;
-- 查詢課程為 高等數(shù)學(xué)-2 且分?jǐn)?shù)不小于80分的學(xué)生的學(xué)號和姓名
-- 方法一:使用連接查詢
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 = '高等數(shù)學(xué)-2' AND StudentResult>=80
-- 方法二:使用連接查詢+子查詢
-- 分?jǐn)?shù)不小于80分的學(xué)生的學(xué)號和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上面SQL基礎(chǔ)上,添加需求:課程為 高等數(shù)學(xué)-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 = '高等數(shù)學(xué)-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 = '高等數(shù)學(xué)-2'
    )
)

五、MySQL函數(shù)

1.常用函數(shù)

1.1數(shù)據(jù)函數(shù)

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

1.2字符串函數(shù)

 SELECT CHAR_LENGTH('狂神說堅持就能成功'); /*返回字符串包含的字符數(shù)*/
 SELECT CONCAT('我','愛','程序');  /*合并字符串,參數(shù)可以有多個*/
 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('狂神說堅持就能成功'); /*反轉(zhuǎn)
 -- 查詢姓周的同學(xué),改成鄒
 SELECT REPLACE(studentname,'周','鄒') AS 新名字
 FROM student WHERE studentname LIKE '周%';

1.3日期和時間函數(shù)

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

1.4系統(tǒng)信息函數(shù)

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

2.聚合函數(shù)

-- 聚合函數(shù)
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推薦*/
 -- 從含義上講，count(1) 與 count(*) 都表示對全部數(shù)據(jù)行的查詢。
 -- count(字段) 會統(tǒng)計該字段在表中出現(xiàn)的次數(shù)，忽略字段為null 的情況。即不統(tǒng)計字段為null 的記錄。
 -- count(*) 包括了所有的列，相當(dāng)于行數(shù)，在統(tǒng)計結(jié)果的時候，包含字段為null 的記錄；
 -- count(1) 用1代表代碼行，在統(tǒng)計結(jié)果的時候，包含字段為null 的記錄 。
 /*
 很多人認(rèn)為count(1)執(zhí)行的效率會比count(*)高，原因是count(*)會存在全表掃描，而count(1)可以針對一個字段進(jìn)行查詢。其實不然，count(1)和count(*)都會對全表進(jìn)行掃描，統(tǒng)計所有記錄的條數(shù)，包括那些為null的記錄，因此，它們的效率可以說是相差無幾。而count(字段)則與前兩者不同，它會統(tǒng)計該字段不為null的記錄條數(shù)。
 下面它們之間的一些對比：
 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;

總結(jié)

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