一印蔬、初識(shí)MySQL

1.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ān)聯(lián)來建立表與表之間的關(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)系通過每個(gè)對(duì)象自
  身的屬性來決定

1.2、什么是DBMS

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

1.png

1.3有决、MySQL簡(jiǎn)介

概念 : 是現(xiàn)在流行的開源的,免費(fèi)的 關(guān)系型數(shù)據(jù)庫(kù)
歷史 : 由瑞典MySQL AB 公司開發(fā)拼余，目前屬于 Oracle 旗下產(chǎn)品污桦。
特點(diǎn) :

• 免費(fèi) , 開源數(shù)據(jù)庫(kù)
• 小巧 , 功能齊全
• 使用便捷
• 可運(yùn)行于Windows或Linux操作系統(tǒng)
• 可適用于中小型甚至大型網(wǎng)站應(yīng)用
  官網(wǎng) :https://www.mysql.com/

1.4、安裝MySQL

mysql5.7 64位下載地址:
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
電腦是64位的就下載使用64位版本的匙监！

1. 下載后得到zip壓縮包.
2. 解壓到自己想要安裝到的目錄凡橱，本人解壓到的是D:\Environment\mysql-5.7.19
3. 添加環(huán)境變量：我的電腦->屬性->高級(jí)->環(huán)境變量

選擇PATH,在其后面添加: 你的mysql 安裝文件下面的bin文件夾

4. 編輯 my.ini 文件 ,注意替換路徑位置

 [mysqld] 
 basedir=D:\Program Files\mysql-5.7\
 datadir=D:\Program Files\mysql-5.7\data\ 
 port=3306 
 skip-grant-tables 

5. 啟動(dòng)管理員模式下的CMD，并將路徑切換至mysql下的bin目錄舅柜，然后輸入mysqld –install (安裝
   mysql)
6. 再輸入 mysqld --initialize-insecure --user=mysql 初始化數(shù)據(jù)文件
7. 然后再次啟動(dòng)mysql 然后用命令 mysql –u root –p 進(jìn)入mysql管理界面（密碼可為空）
8. 進(jìn)入界面后更改root密碼

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

9. 刷新權(quán)限

flush privileges;

10. 修改 my.ini文件刪除最后一句skip-grant-tables
11. 重啟mysql即可正常使用

net stop mysql 
net start mysql 

12. 連接上測(cè)試出現(xiàn)以下結(jié)果就安裝好了

    
    
    2.png

1.5梭纹、SQLyog

可手動(dòng)操作,管理MySQL數(shù)據(jù)庫(kù)的軟件工具
特點(diǎn) : 簡(jiǎn)潔 , 易用 , 圖形化

3.png

4.png

使用SQLyog管理工具自己完成以下操作 :

• 連接本地MySQL數(shù)據(jù)庫(kù)
• 新建MySchool數(shù)據(jù)庫(kù)
  數(shù)據(jù)庫(kù)名稱MySchool
  新建數(shù)據(jù)庫(kù)表(grade)
  字段
  GradeID : int(11) , Primary Key (pk)
  GradeName : varchar(50)
  在歷史記錄中可以看到相對(duì)應(yīng)的數(shù)據(jù)庫(kù)操作的語(yǔ)句 .

1.6躲惰、連接數(shù)據(jù)庫(kù)

打開MySQL命令窗口
在DOS命令行窗口進(jìn)入 安裝目錄\mysql\bin
可設(shè)置環(huán)境變量致份，設(shè)置了環(huán)境變量，可以在任意目錄打開础拨！
連接數(shù)據(jù)庫(kù)語(yǔ)句 : mysql -h 服務(wù)器主機(jī)地址 -u 用戶名 -p 用戶密碼
注意 : -p后面不能加空格,否則會(huì)被當(dāng)做密碼的內(nèi)容,導(dǎo)致登錄失敗 !

幾個(gè)基本的數(shù)據(jù)庫(kù)操作命令 :

 update user set password=password('123456')where user='root'; 修改密碼
  flush privileges; 刷新數(shù)據(jù)庫(kù)
  show databases; 顯示所有數(shù)據(jù)庫(kù) 
  use dbname氮块； 打開某個(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ù) 
  
  exit; 退出Mysql 
  ? 命令關(guān)鍵詞 : 尋求幫助 
  -- 表示注釋 

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

2.1诡宗、結(jié)構(gòu)化查詢語(yǔ)句分類

2.2吊洼、數(shù)據(jù)庫(kù)操作

命令行操作數(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ù)名;

對(duì)比工具操作數(shù)據(jù)庫(kù)

[圖片上傳失敗...(image-ee420d-1635499095133)]

2.3、創(chuàng)建數(shù)據(jù)表

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

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

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

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

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

數(shù)值類型

6.png

字符串類型

7.png

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

8.png

null值

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

2.5冒窍、數(shù)據(jù)字段屬性

UnSigned

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

ZEROFILL

• 0填充的
• 不足位數(shù)的用0來填充 , 如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 , 即沒有插入該列的數(shù)值
• 如果設(shè)置為NOT NULL , 則該列必須有值

DEFAULT

• 默認(rèn)的
• 用于設(shè)置默認(rèn)值
• 例如,性別字段,默認(rèn)為"男" , 否則為 "女" ; 若無指定該列的值 , 則默認(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';

2.6、數(shù)據(jù)表的類型

設(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)驗(yàn) ( 適用場(chǎng)合 ) :

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

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

• MySQL數(shù)據(jù)表以文件方式存放在磁盤中
  包括表文件 , 數(shù)據(jù)文件 , 以及數(shù)據(jù)庫(kù)的選項(xiàng)文件
  位置 : Mysql安裝目錄\data\下存放數(shù)據(jù)表 . 目錄名對(duì)應(yīng)數(shù)據(jù)庫(kù)名 , 該目錄下文件名對(duì)應(yīng)數(shù)據(jù)表
  .
• 注意 :
  InnoDB類型數(shù)據(jù)表只有一個(gè) *.frm文件 , 以及上一級(jí)目錄的ibdata1文件
  MyISAM類型數(shù)據(jù)表對(duì)應(yīng)三個(gè)文件 :
  . frm -- 表結(jié)構(gòu)定義文件
  . MYD -- 數(shù)據(jù)文件 ( data )
  . MYI -- 索引文件 ( index )

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

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

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

2.7儒飒、修改數(shù)據(jù)庫(kù)

修改表 ( ALTER TABLE )

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

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

語(yǔ)法： <kbd>DROP TABLE [IF EXISTS] 表名</kbd>
<kbd>IF EXISTS</kbd> 為可選 , 判斷是否存在該數(shù)據(jù)表
如刪除不存在的數(shù)據(jù)表會(huì)拋出錯(cuò)誤

其他

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ì)大小寫不敏感 （關(guān)鍵字）
7. 清除已有語(yǔ)句：\c

三共郭、MySQL數(shù)據(jù)管理

3.1、外鍵

外鍵概念

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

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

外鍵作用

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

創(chuàng)建外鍵

-- 創(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`);

刪除外鍵

操作：刪除 grade 表坛怪，發(fā)現(xiàn)報(bào)錯(cuò)

9.png

注意 : 刪除具有主外鍵關(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;

3.2、DML語(yǔ)言

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

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

• 通過SQLyog等管理工具管理數(shù)據(jù)庫(kù)數(shù)據(jù)
• 通過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.3袜匿、添加數(shù)據(jù)

INSERT命令

語(yǔ)法：

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

注意 :

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

-- 使用語(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 ('大三'),('大四');

3.4、修改數(shù)據(jù)

update命令

語(yǔ)法：

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

注意 :

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

where條件子句

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

測(cè)試：

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

3.5居灯、刪除數(shù)據(jù)

DELETE命令

語(yǔ)法：

DELETE FROM 表名 [WHERE condition];

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

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

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ì)說）

測(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)前值依然從原來基礎(chǔ)上進(jìn)行,會(huì)記錄日志.

-- 刪除表數(shù)據(jù)(truncate) 
TRUNCATE TABLE test;
-- 結(jié)論:truncate刪除數(shù)據(jù),自增當(dāng)前值會(huì)恢復(fù)到初始值重新開始;不會(huì)記錄日志. 

-- 同樣使用DELETE清空不同引擎的數(shù)據(jù)庫(kù)表數(shù)據(jù).重啟數(shù)據(jù)庫(kù)服務(wù)后 
-- InnoDB : 自增列從初始值重新開始 (因?yàn)槭谴鎯?chǔ)在內(nèi)存中,斷電即失) 
-- MyISAM : 自增列依然從上一個(gè)自增數(shù)據(jù)基礎(chǔ)上開始 (存在文件中,不會(huì)丟失)

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

4.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ǔ)句

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

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

4.2怪嫌、指定查詢字段

-- 查詢表中所有的數(shù)據(jù)列結(jié)果 , 采用 **" \* "** 符號(hào); 但是效率低义锥，不推薦 . 

-- 查詢所有學(xué)生信息 
SELECT * FROM student; 

-- 查詢指定列(學(xué)號(hào) , 姓名) 
SELECT studentno,studentname FROM student;

AS 子句作為別名

作用：

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

-- 這里是為列取別名(當(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;

:::tip
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)

:::tip
使用表達(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)等干擾開發(fā)語(yǔ)言程序.

4.3、where條件語(yǔ)句

作用：用于檢索數(shù)據(jù)表中 符合條件 的記錄
搜索條件可由一個(gè)或多個(gè)邏輯表達(dá)式組成 , 結(jié)果一般為真或假.
:::tip
邏輯操作符
:::

測(cè)試

-- 滿足條件的查詢(where) 
SELECT Studentno,StudentResult FROM result; 

-- 查詢考試成績(jī)?cè)?5-100之間的 
SELECT Studentno,StudentResult 
FROM result 
WHERE StudentResult>=95 AND StudentResult<=100; 

-- AND也可以寫成 && 
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;

模糊查詢 ： 比較操作符

注意：

• 數(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 空 
-- ============================================= 
-- 查詢出生日期沒有填寫的同學(xué) 
-- 不能直接寫=NULL , 這是代表錯(cuò)誤的 , 用 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泵琳、連接查詢

JOIN 對(duì)比

10.png

11.png

測(cè)試

/*
連接查詢
  如需要多張數(shù)據(jù)表的數(shù)據(jù)進(jìn)行查詢,則可通過連接運(yùn)算符實(shí)現(xiàn)多個(gè)查詢 
內(nèi)連接 inner join 
  查詢兩個(gè)表中的結(jié)果集中的交集 
外連接 outer join 
  左外連接 left join 
    (以左表作為基準(zhǔn),右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
  右外連接 right join 
    (以右表作為基準(zhǔn),左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充) 

等值連接和非等值連接 

自連接
 */
-- 查詢參加了考試的同學(xué)信息(學(xué)號(hào),學(xué)生姓名,科目編號(hào),分?jǐn)?shù)) 
SELECT * FROM student;
SELECT * FROM result;

/*
思路: 
(1):分析需求,確定查詢的列來源于兩個(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ì)查出來) 
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

自連接

/*
  自連接
    數(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','軟件開發(fā)'),
 ('4','3','數(shù)據(jù)庫(kù)'), 
 ('5','1','美術(shù)設(shè)計(jì)'), 
 ('6','3','web開發(fā)'), 
 ('7','5','ps技術(shù)'), 
 ('8','2','辦公信息');
 
-- 編寫SQL語(yǔ)句,將欄目的父子關(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é)號(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'

4.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

4.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' 
)

-- 方法三:使用子查詢 
-- 分步寫簡(jiǎn)單sql語(yǔ)句,然后將其嵌套起來 
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' 
  ) 
)