前言

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

一、初始MySQL

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

數(shù)據(jù)庫(kù) ( DataBase , 簡(jiǎn)稱DB )

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

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

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

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

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

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

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

2.什么是DBMS

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

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

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

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

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

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

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

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

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

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

屬于DDL的一種，語(yǔ)法 :

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

說(shuō)明：反引號(hào)用于區(qū)別MySQL保留字與普通字符而引入的 (鍵盤(pán)esc下面的鍵).

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

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

4.1數(shù)值類型

4.2字符串類型

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

4.4NULL值

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

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

UnSigned

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

ZEROFILL

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

Auto_InCrement

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

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

• 可定義起始值和步長(zhǎng)

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

NULL 和 NOT NULL

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

DEFAULT

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

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

常見(jiàn)的 MyISAM 與 InnoDB 類型：

經(jīng)驗(yàn) ( 適用場(chǎng)合 ) :

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

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

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

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

注意 :

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

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

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

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

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

7.1修改表 ( ALTER TABLE )

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

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

語(yǔ)法：DROP TABLE [IF EXISTS] 表名

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

7.3其他

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

三、DML語(yǔ)言

1.外鍵

1.1外鍵概念

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

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

外鍵作用

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

1.2創(chuàng)建外鍵

建表時(shí)指定外鍵約束

-- 創(chuàng)建外鍵的方式一 : 創(chuàng)建子表同時(shí)創(chuàng)建外鍵
-- 年級(jí)表 (id\年級(jí)名稱)
CREATE TABLE `grade` (
  `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年級(jí)ID',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年級(jí)名稱',
  PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 學(xué)生信息表 (學(xué)號(hào),姓名,性別,年級(jí),手機(jī),地址,出生日期,郵箱,身份證號(hào))
CREATE TABLE `student` (
  `studentno` INT(4) NOT NULL COMMENT '學(xué)號(hào)',
  `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `sex` TINYINT(1) DEFAULT '1' COMMENT '性別',
  `gradeid` INT(10) DEFAULT NULL COMMENT '年級(jí)',
  `phoneNum` VARCHAR(50) NOT NULL COMMENT '手機(jī)',
  `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
  `borndate` DATETIME DEFAULT NULL COMMENT '生日',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
  `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號(hào)',
  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)系的表時(shí) , 要先刪子表 , 后刪主表

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

2.DML語(yǔ)言

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

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

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

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

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

• 包括 :

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

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

3.1INSERT命令

語(yǔ)法：

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

注意 :

• 字段或值之間用英文逗號(hào)隔開(kāi) .
• ’ 字段1,字段2…’ 該部分可省略 , 但添加的值務(wù)必與表結(jié)構(gòu),數(shù)據(jù)列,順序相對(duì)應(yīng),且數(shù)量一致 .
• 可同時(shí)插入多條數(shù)據(jù) , values 后用英文逗號(hào)隔開(kāi) .

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

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

4.1update命令

語(yǔ)法：

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條件子句

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

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

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

5.1DELETE命令

語(yǔ)法：

DELETE FROM 表名 [WHERE condition];

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

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

5.2 TRUNCATE命令

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

語(yǔ)法：

TRUNCATE [TABLE] table_name;
-- 清空年級(jí)表
TRUNCATE grade

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

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

• 不同 :

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

測(cè)試：

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

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

1.DQL語(yǔ)言

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

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

1.1SELECT語(yǔ)法

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

注意 : [ ] 括號(hào)代表可選的 , { }括號(hào)代表必選得

2.指定查詢字段

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

2.1AS 子句作為別名

作用：

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

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

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

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

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

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

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

應(yīng)用場(chǎng)景 :

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

• SELECT語(yǔ)句中的ORDER BY , HAVING等子句中使用

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

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

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

3.where條件語(yǔ)句

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

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

3.1邏輯操作符

測(cè)試

-- 滿足條件的查詢(where)
SELECT Studentno,StudentResult FROM result;
-- 查詢考試成績(jī)?cè)?5-100之間的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以寫(xiě)成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;
-- 模糊查詢(對(duì)應(yīng)的詞:精確查詢)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000號(hào)同學(xué),要其他同學(xué)的成績(jī)
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ù)運(yùn)算 ;
• 相同數(shù)據(jù)類型的數(shù)據(jù)之間才能進(jìn)行比較 ;

測(cè)試：

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

4.連接查詢

4.1JOIN 對(duì)比

測(cè)試

/*
連接查詢
    如需要多張數(shù)據(jù)表的數(shù)據(jù)進(jìn)行查詢,則可通過(guò)連接運(yùn)算符實(shí)現(xiàn)多個(gè)查詢
內(nèi)連接 inner join
    查詢兩個(gè)表中的結(jié)果集中的交集
外連接 outer join
    左外連接 left join
        (以左表作為基準(zhǔn),右邊表來(lái)一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
    右外連接 right join
        (以右表作為基準(zhǔn),左邊表來(lái)一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
等值連接和非等值連接
自連接
*/
-- 查詢參加了考試的同學(xué)信息(學(xué)號(hào),學(xué)生姓名,科目編號(hào),分?jǐn)?shù))
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,確定查詢的列來(lái)源于兩個(gè)類,student  result,連接查詢
(2):確定使用哪種連接查詢?(內(nèi)連接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 右連接(也可實(shí)現(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é),不考試的也會(huì)查出來(lái))
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
-- 查一下缺考的同學(xué)(左連接應(yīng)用場(chǎ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é)號(hào),學(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)行連接
需求:從一個(gè)包含欄目ID , 欄目名稱和父欄目ID的表中
     查詢父欄目名稱和其他子欄目名稱
*/
-- 創(chuàng)建一個(gè)表
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','軟件開(kāi)發(fā)'),
('4','3','數(shù)據(jù)庫(kù)'),
('5','1','美術(shù)設(shè)計(jì)'),
('6','3','web開(kāi)發(fā)'),
('7','5','ps技術(shù)'),
('8','2','辦公信息');
-- 編寫(xiě)SQL語(yǔ)句,將欄目的父子關(guān)系呈現(xiàn)出來(lái) (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然后將這兩張表連接查詢(自連接)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
-- 思考題:查詢參加了考試的同學(xué)信息(學(xué)號(hào),學(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é)員及所屬的年級(jí)(學(xué)號(hào),學(xué)生姓名,年級(jí)名)
SELECT studentno AS 學(xué)號(hào),studentname AS 學(xué)生姓名,gradename AS 年級(jí)名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`
-- 查詢科目及所屬的年級(jí)(科目名稱,年級(jí)名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級(jí)名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid
-- 查詢 數(shù)據(jù)庫(kù)結(jié)構(gòu)-1 的所有考試結(jié)果(學(xué)號(hào) 學(xué)生姓名 科目名稱 成績(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ù)庫(kù)結(jié)構(gòu)-1'

5.排序和分頁(yè)

測(cè)試

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

*============== 子查詢 ================
什么是子查詢?
    在查詢語(yǔ)句中的WHERE條件子句中,又嵌套了另一個(gè)查詢語(yǔ)句
    嵌套查詢可由多個(gè)子查詢組成,求解的方式是由里及外;
    子查詢返回的結(jié)果一般都是集合,故而建議使用IN關(guān)鍵字;
*/
-- 查詢 數(shù)據(jù)庫(kù)結(jié)構(gòu)-1 的所有考試結(jié)果(學(xué)號(hào),科目編號(hào),成績(jī)),并且成績(jī)降序排列
-- 方法一:使用連接查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '數(shù)據(jù)庫(kù)結(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ù)庫(kù)結(jié)構(gòu)-1'
)
ORDER BY studentresult DESC;
-- 查詢課程為 高等數(shù)學(xué)-2 且分?jǐn)?shù)不小于80分的學(xué)生的學(xué)號(hào)和姓名
-- 方法一:使用連接查詢
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é)號(hào)和姓名
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'
)
-- 方法三:使用子查詢
-- 分步寫(xiě)簡(jiǎn)單sql語(yǔ)句,然后將其嵌套起來(lái)
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);  /*絕對(duì)值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*隨機(jī)數(shù),返回一個(gè)0-1之間的隨機(jī)數(shù)*/
 SELECT SIGN(0); /*符號(hào)函數(shù): 負(fù)數(shù)返回-1,正數(shù)返回1,0返回0*/

1.2字符串函數(shù)

 SELECT CHAR_LENGTH('狂神說(shuō)堅(jiān)持就能成功'); /*返回字符串包含的字符數(shù)*/
 SELECT CONCAT('我','愛(ài)','程序');  /*合并字符串,參數(shù)可以有多個(gè)*/
 SELECT INSERT('我愛(ài)編程helloworld',1,2,'超級(jí)熱愛(ài)');  /*替換字符串,從某個(gè)位置開(kāi)始替換某個(gè)長(zhǎng)度*/
 SELECT LOWER('KuangShen'); /*小寫(xiě)*/
 SELECT UPPER('KuangShen'); /*大寫(xiě)*/
 SELECT LEFT('hello,world',5);   /*從左邊截取*/
 SELECT RIGHT('hello,world',5);  /*從右邊截取*/
 SELECT REPLACE('狂神說(shuō)堅(jiān)持就能成功','堅(jiān)持','努力');  /*替換字符串*/
 SELECT SUBSTR('狂神說(shuō)堅(jiān)持就能成功',4,6); /*截取字符串,開(kāi)始和長(zhǎng)度*/
 SELECT REVERSE('狂神說(shuō)堅(jiān)持就能成功'); /*反轉(zhuǎn)
 -- 查詢姓周的同學(xué),改成鄒
 SELECT REPLACE(studentname,'周','鄒') AS 新名字
 FROM student WHERE studentname LIKE '周%';

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

 SELECT CURRENT_DATE();   /*獲取當(dāng)前日期*/
 SELECT CURDATE();   /*獲取當(dāng)前日期*/
 SELECT NOW();   /*獲取當(dāng)前日期和時(shí)間*/
 SELECT LOCALTIME();   /*獲取當(dāng)前日期和時(shí)間*/
 SELECT SYSDATE();   /*獲取當(dāng)前日期和時(shí)間*/
 -- 獲取年月日,時(shí)分秒
 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(*) 都表示對(duì)全部數(shù)據(jù)行的查詢。
 -- count(字段) 會(huì)統(tǒng)計(jì)該字段在表中出現(xiàn)的次數(shù)，忽略字段為null 的情況。即不統(tǒng)計(jì)字段為null 的記錄。
 -- count(*) 包括了所有的列，相當(dāng)于行數(shù)，在統(tǒng)計(jì)結(jié)果的時(shí)候，包含字段為null 的記錄；
 -- count(1) 用1代表代碼行，在統(tǒng)計(jì)結(jié)果的時(shí)候，包含字段為null 的記錄 。
 /*
 很多人認(rèn)為count(1)執(zhí)行的效率會(huì)比count(*)高，原因是count(*)會(huì)存在全表掃描，而count(1)可以針對(duì)一個(gè)字段進(jìn)行查詢。其實(shí)不然，count(1)和count(*)都會(huì)對(duì)全表進(jìn)行掃描，統(tǒng)計(jì)所有記錄的條數(shù)，包括那些為null的記錄，因此，它們的效率可以說(shuō)是相差無(wú)幾。而count(字段)則與前兩者不同，它會(huì)統(tǒng)計(jì)該字段不為null的記錄條數(shù)。
 下面它們之間的一些對(duì)比：
 1）在表沒(méi)有主鍵時(shí)，count(1)比count(*)快
 2）有主鍵時(shí)，主鍵作為計(jì)算條件，count(主鍵)效率最高；
 3）若表格只有一個(gè)字段，則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é)

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