當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

【内容详细、源码详尽】MySQL极简学习笔记

發(fā)布時間：2024/3/13 数据库 25 豆豆

生活随笔收集整理的這篇文章主要介紹了【内容详细、源码详尽】MySQL极简学习笔记小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

碎話

本人整理資料時，發(fā)現(xiàn)了之前學(xué)習(xí)MySQL時做的筆記。筆記內(nèi)容做的蠻好的，值得分享給大家。所以根據(jù)自己的學(xué)習(xí)能力、精力、空余時間情況，把筆記整理成了四天的學(xué)習(xí)章節(jié)。

每一個DQL、DML、DDL語句的學(xué)習(xí)，我都給出了具體的MySQL程序和運行結(jié)果。大家可以跟著敲一敲，或者當(dāng)做復(fù)習(xí)資料。總的來說，實操用于是檢驗學(xué)習(xí)的不二法門。

相信大家全部看完會大有所獲，有時間的話，我會將此篇文章單獨整理成一個專欄（現(xiàn)在沒時間）。

文中所用的數(shù)據(jù)庫，有需要的同學(xué)可以在評論區(qū)留言郵箱號，我看到了就會發(fā)給大家。

文章目錄

- - - 碎話
- @[toc]
- - 一、Day 01
  - - 1-3、略
    - 4、看一下計算機上的服務(wù)
    - 5、在windows操作系統(tǒng)當(dāng)中，怎么使用命令來啟動和關(guān)閉MySQL服務(wù)？
    - 6、 mysql安裝成功了，服務(wù)啟動成功了，怎么使用客戶端登錄mysql數(shù)據(jù)庫?
    - 7、mysql的常用命令【不見分號不執(zhí)行】【不區(qū)分大小寫】
    - 8、數(shù)據(jù)庫中最基本的單元是表：table
    - 9、關(guān)于SQL語句的分類【要特別熟悉】？
    - 10、導(dǎo)入提前準備好的數(shù)據(jù)
    - 11、關(guān)于導(dǎo)入的這幾張表？
    - 13、簡單查詢
    - - 13.1 查詢1個字段？
      - 13.2 查詢2個字段，或多個字段？
      - 13.3 查詢所有字段？
      - 13.4 給查詢的列起別名？
      - 13.5 計算員工年薪？sal*12
    - 14、條件查詢
    - - 14.1 什么是條件查詢
    - 15、排序
    - - 15.1 排序查詢所有員工薪資？
      - 15.2 可以兩個字段排序嗎？或者說按照多個字段排序？
      - 15.3 了解：根據(jù)字段的位置也可以排序
    - 16、綜合一點的案例
    - - 16.1 找出工資在1250到3000之間的員工信息，要求按照薪資降序排列
    - 17、數(shù)據(jù)處理函數(shù)/單行處理函數(shù)
    - - 17.1 數(shù)據(jù)處理函數(shù)又被稱為單行處理函數(shù)
      - 17.2 常見單行處理函數(shù)
    - 18、分組函數(shù)（多行處理函數(shù)）
    - 19、分組查詢（*****非常重要：五顆星*****）
    - - 19.1 什么是分組查詢
      - 19.2 將之前關(guān)鍵字全部組合在一起，看他們的執(zhí)行順序
      - 19.3 找出每個工作崗位的工資和?
      - 19.4 找出部門的最高薪資
      - 19.5 找出每個部門，不同工作崗位的最高薪資
      - 19.6 找出每個部門最高薪資，要求顯示最高薪資大于3000的？
      - 19.7 where沒辦法處理的情況？
    - 20、【回顧】
  - 二、Day 02[最重要]
  - - 1、去除重復(fù)記錄
    - 2、連接查詢
    - - 2.1 什么是連接查詢
      - 2.2 連接查詢的分類？
      - 2.3 當(dāng)兩張表進行連接查詢時，沒有任何條件的限制會發(fā)生什么現(xiàn)象？
      - 2.4 避免笛卡爾積現(xiàn)象
      - 2.5 內(nèi)連接：等值連接
      - 2.6 內(nèi)連接：非等值連接
      - 2.7 內(nèi)連接之自連接
      - 2.8 外連接：右外連接、左外連接
      - 2.9 三張表、四張表怎么連接
    - 3、子查詢
    - - 3.1 什么是子查詢？
      - 3.2 子查詢都可以出現(xiàn)在哪里？
      - 3.3 where子句中的子查詢
      - 3.4 from子句中的子查詢
      - 3.5 select后面出現(xiàn)的子查詢（了解）
    - 4、union合并查詢結(jié)果集
    - 5、limit（非常重要）
    - - 5.1 limit作用
      - 5.2 limit怎么用
      - 5.3 分頁
    - 6、DQL語句的大總結(jié)
    - 7、表的創(chuàng)建（建表）
    - - 7.1 建表的語法格式：（DDL語句，包括：create, drop, alter）
      - 7.2 關(guān)于mysql中的數(shù)據(jù)類型
      - 7.3 創(chuàng)建一個學(xué)生表？
      - 7.4 插入數(shù)據(jù)insert ( DML )
      - 7.5 insert插入日期
      - 7.6 date和datetime兩個類型的區(qū)別
      - 7.7 update ( DML )
      - 7.8 delete(DML)
  - 三、Day 03
  - - 1、查詢每個員工的所在部門名稱？要求顯示員工名和部門名？
    - 2、insert語句可以一次插入多條記錄【掌握】
    - 3、快速建表
    - 4、將查詢結(jié)果插入到一張表中？insert相關(guān)
    - 5、快速刪除表中的數(shù)據(jù)？【truncate比較重要，必須掌握，DDL操作】
    - 6、對表結(jié)構(gòu)的增刪改？【DDL，create drop alter，針對字段】
    - 7、約束（極其重要\****\*）
    - - 7.1 什么是約束？
      - 7.2 約束包括哪些？
      - 7.3 非空約束：not null
      - 7.4 唯一性約束：unique
      - 7.5 主鍵約束（Primary key, PK，非常重要\****\*）
      - 7.6 外鍵約束（foreign key, PK, 非常重要）
      - 7.7 級聯(lián)更新與級聯(lián)刪除
      - 7.8 刪除約束
      - 7.9 添加約束
      - 7.10 修改約束
    - 8、存儲引擎
    - - 8.1 存儲引擎的作用
      - 8.2 給表添加/指定“存儲引擎”
      - 8.3 查看mysql支持的存儲引擎
      - 8.4 mysql常用存儲引擎
    - 9、事務(wù)（非常重要\****\*）
    - - 9.1 什么是事務(wù)
      - 9.2 什么sql語句支持事務(wù)
      - 9.3 事務(wù)存在的意義
      - 9.4 事務(wù)的原理
      - 9.5 如何提交事務(wù)，如何回滾事務(wù)
      - 9.6 事務(wù)的ACID性質(zhì)
      - 9.7 事務(wù)的5種狀態(tài)
      - 9.7 **事務(wù)隔離級別**
      - 9.8 驗證各種隔離級別
      - 9.9 設(shè)置服務(wù)器的缺省隔離級別
  - 三、Day 04
  - - 1、索引
    - - 1.1 什么是索引
      - 1.2 索引的實現(xiàn)原理
      - 1.3、mysql自動添加索引
      - 1.4 索引的優(yōu)化
      - 1.5 索引的創(chuàng)建、刪除
      - 1.6 查看SQL語句是否使用了索引進行檢索
      - 1.7 索引失效(優(yōu)化策略)
      - 1.8 數(shù)據(jù)庫優(yōu)化重要手段：索引
    - 2、視圖（View）
    - - 2.1 什么是視圖
      - 2.2 創(chuàng)建/刪除視圖對象
      - 2.3 視圖的作用：類似JAVA引用
      - 2.4 視圖對象在實際開發(fā)中的作用
    - 3、DBA常用命令
    - - 3.1 重點掌握
      - 3.2 數(shù)據(jù)導(dǎo)出
      - 3.3 數(shù)據(jù)導(dǎo)入
    - 4、數(shù)據(jù)庫設(shè)計的三范式（重要）
    - - 4.1 什么是數(shù)據(jù)庫設(shè)計范式
      - 4.2 數(shù)據(jù)庫設(shè)計三大范式
      - 4.3 第一范式
      - 4.4 第二范式
      - 4.5 第三范式
      - 4.6 總結(jié)表的設(shè)計（牢記）
      - 4.7 總結(jié)

一、Day 01

1-3、略

4、看一下計算機上的服務(wù)

5、在windows操作系統(tǒng)當(dāng)中，怎么使用命令來啟動和關(guān)閉MySQL服務(wù)？

語法：

net stop 服務(wù)名稱; net start 服務(wù)名稱；

其他服務(wù)的啟、停都可以用上述命令

6、 mysql安裝成功了，服務(wù)啟動成功了，怎么使用客戶端登錄mysql數(shù)據(jù)庫?

使用bin目錄下面的mysql.exe命令來連接mysql數(shù)據(jù)庫服務(wù)器

本地登錄（密碼可見）：

mysql -uroot -p123456

本地登錄（密碼不可見）：

mysql -uroot -p

7、mysql的常用命令【不見分號不執(zhí)行】【不區(qū)分大小寫】

退出mysql：

exit

查看數(shù)據(jù)庫管理系統(tǒng)中有哪些數(shù)據(jù)庫：

show databases;

**【注意】**以英文分號‘ ; ’結(jié)尾；

查看數(shù)據(jù)庫下有那些表？

show tables;

mysql默認自帶4個數(shù)據(jù)庫；

怎么選擇使用某個數(shù)據(jù)庫？

mysql> use test;

怎么創(chuàng)建數(shù)據(jù)庫？

mysql>create database bjpowernode;

怎么查看表中的數(shù)據(jù)？

select * from 表名;

不看表中的數(shù)據(jù)，只看表的結(jié)構(gòu)？

desc 表名；

查看mysql數(shù)據(jù)庫的版本號

select version();

查看當(dāng)前所使用的是哪個數(shù)據(jù)庫？

select database;

【注意：不見 ; 不執(zhí)行，; 是終止符】

\c用來終止命令與ctrl+c作用一樣

8、數(shù)據(jù)庫中最基本的單元是表：table

什么是表table?為什么用表來存儲數(shù)據(jù)呢？

? 數(shù)據(jù)庫當(dāng)中以表格形式表示數(shù)據(jù)，因為表比較直觀。

? 任何一張表都有行和列：

? 行（row）：被稱為數(shù)據(jù)/記錄。

? 列（column）：被稱為字段。

? 姓名字段、性別字段、年齡字段。

【注意】：每一個字段都有：字段名、數(shù)據(jù)類型、約束等屬性

字段名：見名知意即可；

數(shù)據(jù)類型：字符串、數(shù)字、日期等

約束：約束有較多

? 唯一性約束：這種約束添加后，該字段中的數(shù)據(jù)不能重復(fù)

9、關(guān)于SQL語句的分類【要特別熟悉】？

? SQL語句有很多，最好進行分門別類，這樣更容易記憶。

? DQL：數(shù)據(jù)參訓(xùn)語言（凡是帶select關(guān)鍵字的都是查詢語言）

? select…

? DML：數(shù)據(jù)操作語言（凡是對表當(dāng)中的數(shù)據(jù)進行增刪改的都是DML）

? insert delete update

? DDL：數(shù)據(jù)定義語言

? 凡是帶有creat、drop、alter的都是DDL

? DDL主要操作的是表的結(jié)構(gòu)，不是表中的數(shù)據(jù)

? create：新建，等同于增

? drop：刪除

? alter：修改

? 這個增刪改與DML不同，DDL主要是對表結(jié)構(gòu)進行操作

? TCL：事務(wù)控制語言

? 事務(wù)提交：commit；

? 事務(wù)回滾：rollback；

? DCL：數(shù)據(jù)控制語言

? 例如：授權(quán)grant、撤銷權(quán)限r(nóng)evoke…

10、導(dǎo)入提前準備好的數(shù)據(jù)

? bjpowernode.sql 導(dǎo)入sql腳本文件，里面有表的創(chuàng)建

? source table.sql直接執(zhí)行table.sql腳本文件

? source D:\course\03-MySQL\document\bjpowernode.sql

? 【要先use 數(shù)據(jù)庫;】

? 【注意：路徑不要有中文！！！】

? 【導(dǎo)入時出現(xiàn)問題

? **Unknown command ‘\J’. **

? **ERROR: Unknown command ‘\M’. **

? ERROR: Unknown command ‘\M’.

? 這還是因為windows下路徑的問題只需要改為把"“改為”/"】

11、關(guān)于導(dǎo)入的這幾張表？

? mysql> show tables;

? dept：部門表

? emp：員工表

mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+

? salgrade：工資等級表

? 查看表中數(shù)據(jù)？

select * from 表名;

? 不看表中的數(shù)據(jù)，只看表結(jié)構(gòu)？

desc 表名;//【describe縮寫為desc】 mysql> desc emp; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | EMPNO | int | NO | PRI | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | int | YES | | NULL | | | HIREDATE | date | YES | | NULL | | | SAL | double(7,2) | YES | | NULL | | | COMM | double(7,2) | YES | | NULL | | | DEPTNO | int | YES | | NULL | | +----------+-------------+------+-----+---------+-------+

13、簡單查詢

13.1 查詢1個字段？

select 字段名 from 表名; 【注意】：select和from是關(guān)鍵字，字段名和表名是標識符【強調(diào)】：對所有SQL語句都是通用的，所有的SQL語句以“;”結(jié)尾SQL語句，不區(qū)分大小寫查詢部門名字？ select dname from dept;

13.2 查詢2個字段，或多個字段？

使用逗號隔開“,”查詢部門編號和部門名？select deptno, dname from dept;

13.3 查詢所有字段？

第一種方式：可以把所有字段都寫上第二種方式：可以使用*（先轉(zhuǎn)換為字段，再查詢）缺點：1-效率低；2- 可讀性差。在實際開發(fā)中不建議，在DOS命令中快速看一看全表數(shù)據(jù)可以采用這種方式

13.4 給查詢的列起別名？

select deptno, dname as deptname from dept;

使用as關(guān)鍵字起別名

【注意】：只是將顯示的查詢結(jié)果列名顯示為deptname，原列名還是叫dname

【記住】：select語句永遠都不會進行修改操作（因為只負責(zé)查詢）

as關(guān)鍵字可以省略嗎？可以的

select deptno, dname deptname from dept;

假設(shè)起別名的時候，別名里面有空格，怎么辦？【編譯器會報錯】

select deptno, dname dept name from dept;

怎么解決？【單引號】

select deptno, dname ’dept name‘ from dept;//單引號select deptno, dname ”dept name“ from dept;//雙引號

【注意】：在所有數(shù)據(jù)庫中，字符串統(tǒng)一使用單引號’ ‘括起來，單引號是標準。雙引號在oracle數(shù)據(jù)庫中用不了。但是在mysql中可以使用。

【再次強調(diào)】：數(shù)據(jù)庫中的字符串都是采用單引號括起來的。單引號是標準，雙引號不標準。

13.5 計算員工年薪？sal*12

mysql> select ename, sal*12 from emp;//字段可以使用數(shù)學(xué)表達式 mysql> select ename, sal*12 as '年薪' from emp; +--------+----------+ | ename | 年薪 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+

14、條件查詢

14.1 什么是條件查詢

? 不是將表中所有數(shù)據(jù)都查出來，而是查詢符合條件的數(shù)據(jù)。

? 語法格式：

? select

? 字段1,字段2,字段3,…

? from

? 表名

? where

? 條件;

? = 等于薪資等于3000的員工的empno和ename

mysql> select empno, ename from emp where sal = 3000; +-------+-------+ | empno | ename | +-------+-------+ | 7788 | SCOTT | | 7902 | FORD | +-------+-------+

? <>或!=不等于

mysql> select empno, ename from emp where sal != 3000; +-------+--------+ | empno | ename | +-------+--------+ | 7369 | SMITH | | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7934 | MILLER |

? < <= > >=

? between … and … 兩個值之間【增區(qū)間】，等同于 >= and <=

查詢薪資在3000和5000之間的員工信息？包括3000和5000 mysql> select empno, ename from emp where sal between 3000 and 5000; +-------+-------+ | empno | ename | +-------+-------+ | 7788 | SCOTT | | 7839 | KING | | 7902 | FORD | +-------+-------+mysql> select empno as '工號', ename as '姓名' from emp where sal >= 3000 and sal <= 5000; +------+-------+ | 工號 | 姓名 | +------+-------+ | 7788 | SCOTT | | 7839 | KING | | 7902 | FORD | +------+-------+

? is null 為 null (is not null，不為空)

查詢哪些員工的津貼為null?mysql> select empno as '工號', ename as '姓名' from emp where comm is null; +------+--------+ | 工號 | 姓名 | +------+--------+ | 7369 | SMITH | | 7566 | JONES | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +------+--------+

? and

? or

查詢工作崗位是MANAGER并且工資大于2500的員工信息？ mysql> select empno,ename,job,sal from emp where job = 'MANAGER'or 'SALESMAN' and sal > 2500; +-------+-------+---------+---------+ | empno | ename | job | sal | +-------+-------+---------+---------+ | 7566 | JONES | MANAGER | 2975.00 | | 7698 | BLAKE | MANAGER | 2850.00 | | 7782 | CLARK | MANAGER | 2450.00 |

and和or同時出現(xiàn)的話，有優(yōu)先級問題嗎？【and優(yōu)先級高于or，故加( )】

【在開發(fā)中不確定優(yōu)先級，就加( )】

mysql> select * from emp where sal > 2500 and deptno = 10 or deptno = 20; +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+mysql> select * from emp where sal > 2500 and (deptno = 10 or deptno = 20); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+

? in 包含，相當(dāng)于多個or（not in 不在這個范圍內(nèi)）

【in不是一個區(qū)間，in后面跟的是具體的元素】

查詢工作崗位是MANAGER和SALSMAN的員工 mysql> select empno,ename,job,sal from emp where job = 'manager' or 'salesman';mysql> select empno,ename,job,sal from emp where job in( 'manager' , 'salesman'); +-------+--------+----------+---------+ | empno | ename | job | sal | +-------+--------+----------+---------+ | 7499 | ALLEN | SALESMAN | 1600.00 | | 7521 | WARD | SALESMAN | 1250.00 | | 7566 | JONES | MANAGER | 2975.00 | | 7654 | MARTIN | SALESMAN | 1250.00 | | 7698 | BLAKE | MANAGER | 2850.00 | | 7782 | CLARK | MANAGER | 2450.00 | | 7844 | TURNER | SALESMAN | 1500.00 | +-------+--------+----------+---------+

? not 可以去非，主要用在is 或 in中

? is null

? is not null

? in

? not in

? like like模糊查詢，支持%或下劃線匹配

? %匹配任意多個字符

? _下劃線：匹配任意一個字符

? （%是一個特殊字符，_也是一個特殊字符）

找到名字中含有o的員工？ select ename, empno from emp where ename like '%o%'; mysql> select ename, empno from emp where ename like '%o%'; +-------+-------+ | ename | empno | +-------+-------+ | JONES | 7566 | | SCOTT | 7788 | | FORD | 7902 | +-------+-------+ 找出名字以T結(jié)尾的員工？ mysql> select ename, empno from emp where ename like '%T'; +-------+-------+ | ename | empno | +-------+-------+ | SCOTT | 7788 | +-------+-------+ 找出名字以姓A的員工？ mysql> select ename, empno from emp where ename like 'A%'; +-------+-------+ | ename | empno | +-------+-------+ | ALLEN | 7499 | | ADAMS | 7876 | +-------+-------+ 找出名字以第三個字符是R的員工？ mysql> select ename, empno from emp where ename like '__R%'; 找出名字中有下劃線'_'的員工？【 \ 轉(zhuǎn)移字符】 mysql> select name, no from t_student where name like '%\_%'; +----------+----+ | name | no | +----------+----+ | jack_son | 9 | +----------+----+

15、排序

15.1 排序查詢所有員工薪資？

mysql> select ename, sal from emp order by sal;//【默認升序】 +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+、【指定降序】 mysql> select ename, sal from emp order by sal desc;//【descend】 mysql> select ename, sal from emp order by sal*-1;//【descend】【指定升序】 mysql> select ename, sal from emp order by sal asc;【默認升序】

15.2 可以兩個字段排序嗎？或者說按照多個字段排序？

查詢員工名字和薪資，要求按照【薪資升序】，【如果薪資一樣】，則按照【名字降序】 mysql> select ename,sal from emp order by sal asc, ename desc; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+

15.3 了解：根據(jù)字段的位置也可以排序

【不健壯】：開發(fā)中不建議這樣寫，因為列的順序容易改變

按照查詢ename和sal，【根據(jù)第2列進行排序】 mysql> select ename, sal from emp order by 2; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+

16、綜合一點的案例

16.1 找出工資在1250到3000之間的員工信息，要求按照薪資降序排列

mysql> select empno, ename, job, sal from emp where sal between 1250 and 3000 order by sal desc; +-------+--------+----------+---------+ | empno | ename | job | sal | +-------+--------+----------+---------+ | 7788 | SCOTT | ANALYST | 3000.00 | | 7902 | FORD | ANALYST | 3000.00 | | 7566 | JONES | MANAGER | 2975.00 | | 7698 | BLAKE | MANAGER | 2850.00 | | 7782 | CLARK | MANAGER | 2450.00 | | 7499 | ALLEN | SALESMAN | 1600.00 | | 7844 | TURNER | SALESMAN | 1500.00 | | 7934 | MILLER | CLERK | 1300.00 | | 7521 | WARD | SALESMAN | 1250.00 | | 7654 | MARTIN | SALESMAN | 1250.00 | +-------+--------+----------+---------+ 關(guān)鍵字順序不能變：select...from...where...order by...以上語句的執(zhí)行順序必須掌握：第一步：from第二步：where第三步：select第四步：order by（排序總是在最后執(zhí)行！）

17、數(shù)據(jù)處理函數(shù)/單行處理函數(shù)

17.1 數(shù)據(jù)處理函數(shù)又被稱為單行處理函數(shù)

單行處理函數(shù)特點：一個輸入對應(yīng)一個輸出（SISO）

和單行處理函數(shù)相對的是：多行處理函數(shù)，多個輸入對應(yīng)一個輸出（MISO）。

17.2 常見單行處理函數(shù)

lower轉(zhuǎn)換大小寫函數(shù)（SISO）

upper轉(zhuǎn)換大小寫（SISO）

substr取子串(substr(被截取的字符串，起始下標，截取長度))

【不寫長度，默認截取到最后】

【注意】：下標從1開始，沒有0

找出員工名字第一個字符是A的員工信息？

第一種方式：模糊查詢 mysql> select empno, ename, job, sal from emp where substr(ename, 1, 1) = 'A'; 第二種方式：where中substr函數(shù) mysql> select empno, ename, job, sal from emp where ename like 'A%'; +-------+-------+----------+---------+ | empno | ename | job | sal | +-------+-------+----------+---------+ | 7499 | ALLEN | SALESMAN | 1600.00 | | 7876 | ADAMS | CLERK | 1100.00 | +-------+-------+----------+---------+

首字母大寫?【concat、upper、lower】

concat函數(shù)進行字符串拼接

select concat(empno, ename) from emp;

length取長度

mysql> select length(ename) enameLength from emp; +-------------+ | enameLength | +-------------+ | 5 | | 5 | | 4 | | 5 | | 6 | | 5 | | 5 | | 5 | | 4 | | 6 | | 5 | | 5 | | 4 | | 6 | +-------------+

trim去空格

mysql> select * from emp where ename = trim(' King'); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | +-------+-------+-----------+------+------------+---------+------+--------+ 1 row in set (0.00 sec)

str_to_date將字符串轉(zhuǎn)換成日期

date_format格式化日期

format設(shè)置千分位

round(數(shù)據(jù)，小數(shù)點后位數(shù)) 【四舍五入】【小于0，則向小數(shù)點前置0】

【select 后跟某個表的字段名，也可以跟字面量和字面值】 select 字段 from 表名； select ename from emp; select abc from emp;//報錯 select 'abc' from emp;//有幾行記錄，生成幾行 select 1000 from emp;//有幾行記錄，生成幾行 mysql> select 'abc' from dept; +-----+ | abc | +-----+ | abc | | abc | | abc | | abc | +-----+mysql> select round(1234.567, 1) result from dept; +--------+ | result | +--------+ | 1234.6 | | 1234.6 | | 1234.6 | | 1234.6 | +--------+

rand()生成隨機數(shù)

mysql> select rand()*100 result from emp; +--------------------+ | result | +--------------------+ | 66.96882645317244 | | 55.673224251413004 | | 77.45964497091215 | | 20.278555173686293 | | 69.01384402905948 | | 84.23355769760306 | | 14.126259474201369 | | 17.930627351562144 | | 47.27436140857112 | | 82.57992806153365 | | 71.0765591553194 | | 7.6430146653605755 | | 24.98539893420916 | | 1.997953748328568 | +--------------------+ mysql> select empno, ename from emp where rand() > 0.5; +-------+--------+ | empno | ename | +-------+--------+ | 7499 | ALLEN | | 7566 | JONES | | 7654 | MARTIN | | 7782 | CLARK | | 7788 | SCOTT | | 7902 | FORD | | 7934 | MILLER | +-------+--------+

ifnull(數(shù)據(jù)，為Null時當(dāng)作哪個值) 可以將null轉(zhuǎn)換為一個具體值

ifnull是空處理函數(shù)，專門處理空的

【注意】：因為在所有數(shù)據(jù)庫中，只要null參與的數(shù)學(xué)運算，最終結(jié)果就一定是null。為了避免這個現(xiàn)象，需要使用ifnull函數(shù)。

計算每個月供的年薪？年薪 = （月薪 + 月補助） * 12； mysql> select empno, ename, (sal + comm) * 12 as yearsal from emp; +-------+--------+----------+ | empno | ename | yearsal | +-------+--------+----------+ | 7369 | SMITH | NULL | | 7499 | ALLEN | 22800.00 | | 7521 | WARD | 21000.00 | | 7566 | JONES | NULL | | 7654 | MARTIN | 31800.00 | | 7698 | BLAKE | NULL | | 7782 | CLARK | NULL | | 7788 | SCOTT | NULL | | 7839 | KING | NULL | | 7844 | TURNER | 18000.00 | | 7876 | ADAMS | NULL | | 7900 | JAMES | NULL | | 7902 | FORD | NULL | | 7934 | MILLER | NULL | +-------+--------+----------+ mysql> select empno, ename, (sal + ifnull(comm,0)) * 12 as yearsal from emp; +-------+--------+----------+ | empno | ename | yearsal | +-------+--------+----------+ | 7369 | SMITH | 9600.00 | | 7499 | ALLEN | 22800.00 | | 7521 | WARD | 21000.00 | | 7566 | JONES | 35700.00 | | 7654 | MARTIN | 31800.00 | | 7698 | BLAKE | 34200.00 | | 7782 | CLARK | 29400.00 | | 7788 | SCOTT | 36000.00 | | 7839 | KING | 60000.00 | | 7844 | TURNER | 18000.00 | | 7876 | ADAMS | 13200.00 | | 7900 | JAMES | 11400.00 | | 7902 | FORD | 36000.00 | | 7934 | MILLER | 15600.00 | +-------+--------+----------+

case…when…then…when…then…else…end【相當(dāng)于switch case default】

當(dāng)員工崗位是MANAGER時，工資上調(diào)10%，為SALESMAN時上調(diào)50% mysql> select empno, ename, job, sal, case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end newsal from emp; +-------+--------+-----------+---------+---------+ | empno | ename | job | sal | newsal | +-------+--------+-----------+---------+---------+ | 7369 | SMITH | CLERK | 800.00 | 800.00 | | 7499 | ALLEN | SALESMAN | 1600.00 | 2400.00 | | 7521 | WARD | SALESMAN | 1250.00 | 1875.00 | | 7566 | JONES | MANAGER | 2975.00 | 3272.50 | | 7654 | MARTIN | SALESMAN | 1250.00 | 1875.00 | | 7698 | BLAKE | MANAGER | 2850.00 | 3135.00 | | 7782 | CLARK | MANAGER | 2450.00 | 2695.00 | | 7788 | SCOTT | ANALYST | 3000.00 | 3000.00 | | 7839 | KING | PRESIDENT | 5000.00 | 5000.00 | | 7844 | TURNER | SALESMAN | 1500.00 | 2250.00 | | 7876 | ADAMS | CLERK | 1100.00 | 1100.00 | | 7900 | JAMES | CLERK | 950.00 | 950.00 | | 7902 | FORD | ANALYST | 3000.00 | 3000.00 | | 7934 | MILLER | CLERK | 1300.00 | 1300.00 | +-------+--------+-----------+---------+---------+

18、分組函數(shù)（多行處理函數(shù)）

特點：

輸出多行，輸出一行

分組函數(shù)自動忽略null（認為沒有）

分組函數(shù)中count(*)：【統(tǒng)計總表行不為null的元素總數(shù)】和count(字段)：【統(tǒng)計該字段下所有不為null的元素的總數(shù)】

數(shù)據(jù)庫中不可能存在某行記錄列元素均為null

分組函數(shù)不能直接使用在where子句中

mysql> select empno from emp where sal > sum(sal);//報錯

所有的分組函數(shù)可以組合使用，不能嵌套

mysql> select sum(sal), count(sal) from emp; +----------+------------+ | sum(sal) | count(sal) | +----------+------------+ | 29025.00 | 14 | +----------+-----------+

【5個：

? count計數(shù)

? sum求和

? avg平均值

? max最大值

? min最大值】

【注意】：分組函數(shù)在使用前必須先進行分組，再使用。否則，整張表作為一組。

找出最高工資？

mysql> select empno, ename, max(sal) from emp; +-------+-------+----------+ | empno | ename | max(sal) | +-------+-------+----------+ | 7369 | SMITH | 5000.00 | +-------+-------+----------+

找出最低工資？

mysql> select empno, ename, min(sal) from emp; +-------+-------+----------+ | empno | ename | min(sal) | +-------+-------+----------+ | 7369 | SMITH | 800.00 | +-------+-------+----------+

計算工資和？

mysql> select sum(comm) from emp; +-----------+ | sum(comm) | +-----------+ | 2200.00 | +-----------+

計算平均工資？

mysql> select empno, ename, avg(sal) from emp; +-------+-------+-------------+ | empno | ename | avg(sal) | +-------+-------+-------------+ | 7369 | SMITH | 2073.214286 | +-------+-------+-------------+ mysql> select avg(ifnull(comm,0)) from emp; +---------------------+ | avg(ifnull(comm,0)) | +---------------------+ | 157.142857 | +---------------------+

統(tǒng)計員工數(shù)量？

mysql> select empno, ename, count(sal) umpNumber from emp; +-------+-------+-----------+ | empno | ename | umpNumber | +-------+-------+-----------+ | 7369 | SMITH | 14 | +-------+-------+-----------+ mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+

19、分組查詢（非常重要：五顆星）

19.1 什么是分組查詢

在實際應(yīng)用中，可能有這樣需求，需要先分組【比如按照工作崗位分組】，再對每一組數(shù)據(jù)進行操作，這時需要分組查詢。不分組默認整個表為1組

select... from.... group by....

? 計算每個部門的工資和？

? 計算每個工作崗位的平均薪資？

? 找出每個工作崗位的最高薪資？

? …

19.2 將之前關(guān)鍵字全部組合在一起，看他們的執(zhí)行順序

select... from... where... group by... order by...

執(zhí)行順序不能顛倒，需要記憶：【具有優(yōu)先級，不能顛倒】

1. from 2. where 3. group by 4. order by 5. select

為什么分組函數(shù)不能直接使用在where后面？

mysql> select empno from emp where sal > sum(sal);//報錯

因為分組函數(shù)在使用的時候必須先分組才能使用，【where執(zhí)行的時候還沒有分組】，所以where后面不能出現(xiàn)分組函數(shù)，【出現(xiàn)了語法錯誤】

select sum(sal) from emp;

這個沒分組，為什么sum()可以使用？

因為select在group by之后執(zhí)行

19.3 找出每個工作崗位的工資和?

【思路】：按照工作崗位分組，然后對工資求和

mysql> select job,sum(sal) from emp group by job; +-----------+----------+ | job | sum(sal) | +-----------+----------+ | CLERK | 4150.00 | | SALESMAN | 5600.00 | | MANAGER | 8275.00 | | ANALYST | 6000.00 | | PRESIDENT | 5000.00 | +-----------+----------+

執(zhí)行順序？

1. 先從emp標中查詢數(shù)據(jù) 2. 過呢據(jù)job字段進行分組 3. 然后對每一組的數(shù)據(jù)進行sum(sal)

下列語句在mysql中可以執(zhí)行，但是毫無意義。在oracle中會報錯（mysqll語法相對松散）

【重點結(jié)論】：

在一條select語句中，如果有g(shù)roup by語句，那么select后面只能跟：

參加分組的字段

分組函數(shù)

其它的一律不能跟

19.4 找出部門的最高薪資

【思路】：先按照部門編號分組，求每一組的最大值

【表連接】：這樣能找到最大值薪資是誰。通過臨時表進行連接

mysql> select deptno, avg(sal),sum(sal), max(sal) from emp group by deptno order by deptno [desc]; +--------+-------------+----------+----------+ | deptno | avg(sal) | sum(sal) | max(sal) | +--------+-------------+----------+----------+ | 10 | 2916.666667 | 8750.00 | 5000.00 | | 20 | 2175.000000 | 10875.00 | 3000.00 | | 30 | 1566.666667 | 9400.00 | 2850.00 | +--------+-------------+----------+----------+

19.5 找出每個部門，不同工作崗位的最高薪資

【思路】：同時分組。先對部門分組，再對崗位分組

【技巧】：多個字段聯(lián)合成1個字段

select deptno, job, max(sal) fromemp group bydeptno, job; mysql> select deptno, job, max(sal) from emp group by deptno,job order by deptno; +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 10 | CLERK | 1300.00 | | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 20 | ANALYST | 3000.00 | | 20 | CLERK | 1100.00 | | 20 | MANAGER | 2975.00 | | 30 | CLERK | 950.00 | | 30 | MANAGER | 2850.00 | | 30 | SALESMAN | 1600.00 | +--------+-----------+----------+

19.6 找出每個部門最高薪資，要求顯示最高薪資大于3000的？

【思路1】：先分組再篩選

按照部門編號分組group by，求每一組最大值max

mysql> select deptno, max(sal) > 3000 from emp group by deptno; +--------+-----------------+ | deptno | max(sal) > 3000 | +--------+-----------------+ | 20 | 0 | | 30 | 0 | | 10 | 1 | +--------+-----------------+ mysql> select deptno, max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 20 | 3000.00 | | 30 | 2850.00 | | 10 | 5000.00 | +--------+----------+

要求顯示最高薪資大于3000

selectdeptno, max(sal) fromemp group bydeptno havingmax(sal) > 3000;mysql> select deptno, max(sal) from emp group by deptno having max(sal) > 3000; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+

【思路2】：先篩選再分組

實際上思路1效率比較低，可以先將>3000的找出來，再分組

selectdeptno, max(sal) fromemp wheresal > 3000 group bydeptno;mysql> select empno, max(sal) from emp where max(sal) > 3000 group by deptno; ERROR 1111 (HY000): Invalid use of group function 【屬于先where再max分組，報錯】mysql> select empno, max(sal) from emp where sal > 3000 group by deptno; +-------+----------+ | empno | max(sal) | +-------+----------+ | 7839 | 5000.00 | +-------+----------+ 1 row in set (0.00 sec)

【where效率高于having】

19.7 where沒辦法處理的情況？

找出每個部門的平均薪資高于2500的工作

【where后面只能用表里已知的字段】

【思路】：不能不使用having

找出每個部門的平均【分組了】薪資

mysql> select deptno, avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 20 | 2175.000000 | | 30 | 1566.666667 | | 10 | 2916.666667 | +--------+-------------+

要求顯示平均薪資【分組了】高于2500的

mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) > 2500; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+

20、大總結(jié)（單表的查詢學(xué)完了）

select... where... group by... having... order by... 語法只能嚴格按照上述順序，不能顛倒執(zhí)行順序： 1. from 2. where 3. group by 4. having 5. select 6. order by執(zhí)行的邏輯順序： 1. from 某張表中查詢數(shù)據(jù)； 2. 先經(jīng)過 where 條件【表中已存在的字段】篩選出有價值的數(shù)據(jù)； 3. 對這些有價值的數(shù)據(jù)進行分組 group by， 4. 分組后可以繼續(xù)用 having 繼續(xù)篩選； 5. 接著使用 select 查詢出來； 6. 最后用 order by排序。

【綜合題】：找出部門的每個崗位的平均薪資，要求顯示平均薪資大于1500的，除了manager崗位之外，要求按照平均薪資降序排列。

mysql> select deptno, job, avg(sal) from emp where job <> 'manager' group by job having avg(sal) > 1500 order by avg(sal) desc; +--------+-----------+-------------+ | deptno | job | avg(sal) | +--------+-----------+-------------+ | 10 | PRESIDENT | 5000.000000 | | 20 | ANALYST | 3000.000000 | +--------+-----------+-------------+ mysql> select deptno, job, avg(sal) avgSal from emp where job not in ( 'manager') group by job having avg(sal) > 1500 order by avgSal desc; +--------+-----------+-------------+ | deptno | job | avgSal | +--------+-----------+-------------+ | 10 | PRESIDENT | 5000.000000 | | 20 | ANALYST | 3000.000000 | +--------+-----------+-------------+

20、【回顧】

DBMS ---> SQL ---> DBMySQL（開源免費）、oracle（世界上速度最快、安全級別最高、后期服務(wù)昂貴）、db2、sybase...是DBMSmysql安裝：port 3306用戶名：root密碼：123456設(shè)置字符集：utf8SQL 分類DQL(Data Query Language 數(shù)據(jù)查詢語言): select，只查詢不會修改表DML(Data Manipulation Language 數(shù)據(jù)操縱語言->表數(shù)據(jù)): insert, delete, updateDDL(Data Definition Language 數(shù)據(jù)定義語言->表結(jié)構(gòu)): create, drop, alterTCL(Transactional Control Language 事務(wù)控制語言): commit, rollbackDCL(Data Control Language 數(shù)據(jù)控制語言->權(quán)限): grant, revoke導(dǎo)入演示數(shù)據(jù)1. 連接MySQL2. 創(chuàng)建'bjpowernode'數(shù)據(jù)庫create database bjpowernode;3. 選擇數(shù)據(jù)庫use bjpowernode;4. source導(dǎo)入數(shù)據(jù)庫source D:\course\03-MySQL\document\bjpowernode.sql5. 查看表結(jié)構(gòu)desc dept;常用MySQL命令【 select 語句總結(jié)】select ...from...where...group by...having...order by...以上關(guān)鍵字只能按照這個順序來，不能顛倒。執(zhí)行順序？1. from2. where3. group by4. having5. select6. order by

二、Day 02[最重要]

1、去除重復(fù)記錄

【注意】：元彪數(shù)據(jù)不會被修改，只是修改了查詢結(jié)果

【關(guān)鍵字】：distinct，只能出現(xiàn)在所有字段的最前方，表示后面的字段聯(lián)合起來去重

統(tǒng)計工作崗位的數(shù)量？

mysql> select count(distinct job) from emp; +---------------------+ | count(distinct job) | +---------------------+ | 5 | +---------------------+

2、連接查詢

2.1 什么是連接查詢

從一張表中單獨查詢，稱為單表查詢

emp表和dept表聯(lián)合起來查詢數(shù)據(jù)，從emp表中取員工名字，從dept表中查詢工作地點

這種跨表查詢，多張表聯(lián)合起來查詢數(shù)據(jù)，被稱為【連接查詢】

2.2 連接查詢的分類？

根據(jù)語法的年代分類：

SQL92：1992年出現(xiàn)的語法

SQL99：1999年出現(xiàn)的語法【重點學(xué)習(xí)】

根據(jù)表連接的方式分類：

內(nèi)連接【完全能匹配條件的數(shù)據(jù)，查詢出來】

等值連接

非等值連接

自連接

外連接【外連接】：相當(dāng)于內(nèi)連接+主表沒有匹配的行

左外連接（左連接）

右外連接（右連接）

全連接（不講）

2.3 當(dāng)兩張表進行連接查詢時，沒有任何條件的限制會發(fā)生什么現(xiàn)象？

【案例】：查詢每個員工所在部門名稱

當(dāng)兩張表進行連接查詢，沒有任何條件限制的時候，最終查詢結(jié)果條數(shù)是兩個表條數(shù)的乘積。

【笛卡爾積現(xiàn)象】：查詢多個表時，沒有添加有效的條件，導(dǎo)致多個表所有行完全連接。

2.4 避免笛卡爾積現(xiàn)象

連接時加條件 where ，滿足這個條件的才會被篩選出來！

【匹配次數(shù)并沒有被減少，還是匹配了56次，只是被篩選出了1/4】

【需要找到兩個表中重合的鍵】

【起別名】：表中起別名，對查詢范圍進行限制。很重要。效率問題。

【注意】：由笛卡爾積現(xiàn)象得出：表的連接次數(shù)越多，效率越低，盡量避免表的連接次數(shù)。

2.5 內(nèi)連接：等值連接

【案例】：查詢每個員工所在部門名稱，顯示員工名和部門名？

【思路】：emp e和dept d表進行連接，條件是：e.deptno = d.deptno

SQL:1992select e.ename,d.dnamefromemp e, dept dwheree.deptno = d.deptno and 后面加條件;#條件是等量關(guān)系，故為等值連接【缺點】：結(jié)構(gòu)不清晰，表的連接條件，和后期進一步篩選的條件，都放到了where后面。SQL:1999【表連接和表篩選分離】#inner可以省略，寫的話可讀性更好select e.ename,d.dnamefromemp einner joindept done.deptno = d.deptno//條件是等量關(guān)系，故為等值連接where篩選條件【優(yōu)點】：表連接的條件是獨立的，連接之后如果還需要繼續(xù)篩選，再往后繼續(xù)添加 where

2.6 內(nèi)連接：非等值連接

【案例】：找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級

【思路】：先看每個表結(jié)構(gòu)，再思考如何選取

mysql> select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ mysql> select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; +--------+---------+-------+ | ename | sal | grade | +--------+---------+-------+ | SMITH | 800.00 | 1 | | ALLEN | 1600.00 | 3 | | WARD | 1250.00 | 2 | | JONES | 2975.00 | 4 | | MARTIN | 1250.00 | 2 | | BLAKE | 2850.00 | 4 | | CLARK | 2450.00 | 4 | | SCOTT | 3000.00 | 4 | | KING | 5000.00 | 5 | | TURNER | 1500.00 | 3 | | ADAMS | 1100.00 | 1 | | JAMES | 950.00 | 1 | | FORD | 3000.00 | 4 | | MILLER | 1300.00 | 2 | +--------+---------+-------+ 14 rows in set (0.00 sec)

2.7 內(nèi)連接之自連接

【案例】：查詢員工的上級領(lǐng)導(dǎo)，要求顯示員工名對應(yīng)的領(lǐng)導(dǎo)名

【思路】：自己表連接自己表（看作：1個員工表，1個領(lǐng)導(dǎo)表）

select e1.empno '員工編號', e1.ename '員工名', e2.empno '領(lǐng)導(dǎo)編號', e2.ename '領(lǐng)導(dǎo)名' from emp e1 join emp e2 on e1.mgr = e2.empno; +----------+--------+----------+--------+ | 員工編號 | 員工名 | 領(lǐng)導(dǎo)編號 | 領(lǐng)導(dǎo)名 | +----------+--------+----------+--------+ | 7369 | SMITH | 7902 | FORD | | 7499 | ALLEN | 7698 | BLAKE | | 7521 | WARD | 7698 | BLAKE | | 7566 | JONES | 7839 | KING | | 7654 | MARTIN | 7698 | BLAKE | | 7698 | BLAKE | 7839 | KING | | 7782 | CLARK | 7839 | KING | | 7788 | SCOTT | 7566 | JONES | | 7844 | TURNER | 7698 | BLAKE | | 7876 | ADAMS | 7788 | SCOTT | | 7900 | JAMES | 7698 | BLAKE | | 7902 | FORD | 7566 | JONES | | 7934 | MILLER | 7782 | CLARK | +----------+--------+----------+--------+ 13 rows in set (0.00 sec)

2.8 外連接：右外連接、左外連接

思考：外連接的查詢結(jié)果條目一定是 >= 內(nèi)連接的結(jié)果條目：（外連接=內(nèi)連接+主表沒有匹配的行）

【外連接】：相當(dāng)于內(nèi)連接+主表沒有匹配的行, outer 可省略，加了可讀性好

【right】：將 join 關(guān)鍵字右邊的這張表看成主表，主要是為了將這張表中的數(shù)據(jù)全部都查詢出來，捎帶著關(guān)聯(lián)查詢左邊的表。在外連接中，兩張表連接產(chǎn)生了主次關(guān)系。

【left】：…左邊…

任意左連接和右連接可以相互轉(zhuǎn)換

【案例】：查詢每個員工的上級領(lǐng)導(dǎo)，要求顯示所有員工的名字和領(lǐng)導(dǎo)名

【思路】：相比于外連接，根領(lǐng)導(dǎo)的領(lǐng)導(dǎo)會寫null，即查詢結(jié)果具有14行數(shù)據(jù)

selecte1.empno '員工編號', e1.ename '員工名', e2.empno '領(lǐng)導(dǎo)編號', e2.ename '領(lǐng)導(dǎo)名' fromemp e1 left outer joinemp e2 one1.mgr = e2.empno; +----------+--------+----------+--------+ | 員工編號 | 員工名 | 領(lǐng)導(dǎo)編號 | 領(lǐng)導(dǎo)名 | +----------+--------+----------+--------+ | 7369 | SMITH | 7902 | FORD | | 7499 | ALLEN | 7698 | BLAKE | | 7521 | WARD | 7698 | BLAKE | | 7566 | JONES | 7839 | KING | | 7654 | MARTIN | 7698 | BLAKE | | 7698 | BLAKE | 7839 | KING | | 7782 | CLARK | 7839 | KING | | 7788 | SCOTT | 7566 | JONES | | 7839 | KING | NULL | NULL | | 7844 | TURNER | 7698 | BLAKE | | 7876 | ADAMS | 7788 | SCOTT | | 7900 | JAMES | 7698 | BLAKE | | 7902 | FORD | 7566 | JONES | | 7934 | MILLER | 7782 | CLARK | +----------+--------+----------+--------+ 14 rows in set (0.00 sec)

2.9 三張表、四張表怎么連接

【語法】一條SQL語句中內(nèi)連接和外連接可以混合使用 select... froma joinb on a,b連接條件 joinc ona和c的連接條件 left joind ona和d的連接條件 where查詢條件;

【案例1】：找出每個員工的部門名稱，以及工資等級，要求顯示員工名，部門名，薪資，薪資等級

selecte1.ename '員工姓名', e2.dname '部門名', e1.sal '薪資', e3.grade '薪資等級' fromemp e1 left joindept e2 on e1.deptno = e2.deptno left joinsalgrade e3 one1.sal between e3.losal and e3.hisal; +----------+------------+---------+----------+ | 員工姓名 | 部門名 | 薪資 | 薪資等級 | +----------+------------+---------+----------+ | SMITH | RESEARCH | 800.00 | 1 | | ALLEN | SALES | 1600.00 | 3 | | WARD | SALES | 1250.00 | 2 | | JONES | RESEARCH | 2975.00 | 4 | | MARTIN | SALES | 1250.00 | 2 | | BLAKE | SALES | 2850.00 | 4 | | CLARK | ACCOUNTING | 2450.00 | 4 | | SCOTT | RESEARCH | 3000.00 | 4 | | KING | ACCOUNTING | 5000.00 | 5 | | TURNER | SALES | 1500.00 | 3 | | ADAMS | RESEARCH | 1100.00 | 1 | | JAMES | SALES | 950.00 | 1 | | FORD | RESEARCH | 3000.00 | 4 | | MILLER | ACCOUNTING | 1300.00 | 2 | +----------+------------+---------+----------+ 14 rows in set (0.00 sec)

【案例2】：找出每個員工的部門名稱，工資等級，以及上級領(lǐng)導(dǎo)，要求顯示員工名，上級領(lǐng)導(dǎo)名，部門名，薪資，薪資等級

3、子查詢

3.1 什么是子查詢？

select語句中嵌套select語句，被嵌套的select語句被稱為子查詢

子查詢一定要在首位加括號

3.2 子查詢都可以出現(xiàn)在哪里？

select...(select)...#作為顯示，只能逐次輸出1個結(jié)果 from...(select)...#相當(dāng)于臨時表 where...(select)...#select出的結(jié)果當(dāng)作條件

3.3 where子句中的子查詢

【案例】找出比最低工資高的員工姓名和工資？

【思路1】

查詢最低工資是多少

找出 >800 的

合并

selectempno, ename, sal fromemp wheresal > (select min(sal) from emp); +-------+--------+---------+ | empno | ename | sal | +-------+--------+---------+ | 7499 | ALLEN | 1600.00 | | 7521 | WARD | 1250.00 | | 7566 | JONES | 2975.00 | | 7654 | MARTIN | 1250.00 | | 7698 | BLAKE | 2850.00 | | 7782 | CLARK | 2450.00 | | 7788 | SCOTT | 3000.00 | | 7839 | KING | 5000.00 | | 7844 | TURNER | 1500.00 | | 7876 | ADAMS | 1100.00 | | 7900 | JAMES | 950.00 | | 7902 | FORD | 3000.00 | | 7934 | MILLER | 1300.00 | +-------+--------+---------+

3.4 from子句中的子查詢

【注意】：from后面的子查詢，可以將子查詢的結(jié)果當(dāng)作一張臨時表

【案例】：找出每個崗位的平均薪資的等級

【思路】

分組，找出每個崗位的平均工資

把以上查詢結(jié)果作為臨時表T

把T表和S表進行表連接

selectt.*, s.grade from (select job, avg(sal) as avgSal from emp group by job) t joinsalgrade s ont.avgSal between s.losal and s.hisal; +-----------+-------------+-------+ | job | avgSal | grade | +-----------+-------------+-------+ | CLERK | 1037.500000 | 1 | | SALESMAN | 1400.000000 | 2 | | MANAGER | 2758.333333 | 4 | | ANALYST | 3000.000000 | 4 | | PRESIDENT | 5000.000000 | 5 | +-----------+-------------+-------+

3.5 select后面出現(xiàn)的子查詢（了解）

【案例】：找出每個員工的部門名稱，要求顯示員工名、部門名

【思路1】：連接表

【思路2】：子查詢語句

【注意】：對于select后面的子查詢來說，這個子查詢只能一次返回一條結(jié)果，多余一條，報錯！！！

4、union合并查詢結(jié)果集

【案例】：查詢工作崗位是MANAGER和SALESMAN的員工

【思路1】：in, or

【思路2】：union合并（效率更高，why）。對于表連接來說，每連接1次新表，匹配次數(shù)滿足笛卡爾積，而union可以在減少匹配次數(shù)情況下，同時完成兩個結(jié)果集的拼接。

【表連接】：a 連接 b 連接 c

? a 10條記錄

? b 10條記錄

? c 10條記錄

匹配次數(shù)是：10*10*10 = 1000

【union合并】：a 連接 b 連接 c

? a->b 10*10

? a->c 10*10

匹配次數(shù)：10*10+10*10 = 200

【注意事項】：union進行結(jié)果集合并時，要求兩個結(jié)果集的列數(shù)和列的數(shù)據(jù)類型也相同

//報錯 select empno, job from emp where job = 'manager' union select empno, ename, job from emp where job = 'salesman';

//mysql可以通過，但沒意義。但是oracle語法嚴格，不通過，要求數(shù)據(jù)類型一樣， select ename, job from emp where job = 'manager' union select empno, ename from emp where job = 'salesman';

5、limit（非常重要）

5.1 limit作用

【提高用戶體驗】：將查詢結(jié)果集的一部分取出，通常用于分頁查詢中。一次全都查出來，用戶閱讀體驗差。

百度：一頁顯示10條記錄

5.2 limit怎么用

【用法】：limit startIndex, length。從0開始

【執(zhí)行順序】： mysql中，limit在order by之后執(zhí)行

【案例1】：按照薪資降序，取出排名在前5的員工

select ename, sal from emp order by sal desc limit 0,5; mysql> select ename, sal from emp order by sal desc limit 0,5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+ 5 rows in set (0.00 sec)

【案例2】：取出工資排名在[3-5]名的員工

mysql> select ename, sal from emp order by sal desc limit 2,3; +-------+---------+ | ename | sal | +-------+---------+ | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+

【案例3】：取出工資排名在[5-9]名的員工？

mysql> select ename, sal from emp order by sal desc limit 4,5; +--------+---------+ | ename | sal | +--------+---------+ | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | +--------+---------+

5.3 分頁

每頁顯示3條記錄第1頁：limit 0,3 [0 1 2]第2頁：limit 3,3 [3 4 5]第3頁：limit 6,3 [6 7 8]第4頁：limit 9,3 [9 10 11]每頁顯示pageSize條記錄第pageNo頁：limit (pageNo - 1) * pageSize , pageSizepublic static void main(String[] args){// 用戶提交過來一個頁碼，以及每頁顯示的記錄條數(shù)int pageNo = 5; //第5頁int pageSize = 10; //每頁顯示10條int startIndex = (pageNo - 1) * pageSize;//JDBCString sql = "select ...limit " + startIndex + ", " + pageSize;}

6、DQL語句的大總結(jié)

1. 語法順序 select... from... where... group by... having... order by... limit...2. 執(zhí)行順序1. from2. where3. group by4. having5. select6. order by7. limit 3. 去重復(fù)distinct 4. 多表聯(lián)查1. 內(nèi)連接1. 等值連接2. 非等值連接3. 自連接2. 外連接1. 左外連接（左連接）2. 右外連接（右連接）3.全連接（不講） 5. 子查詢 6. union合并查詢結(jié)果 7. limit

-------------------------------------------------------------DDL------------------------------------------------------------------

7、表的創(chuàng)建（建表）

7.1 建表的語法格式：（DDL語句，包括：create, drop, alter）

create table 表名(字段名1 數(shù)據(jù)類型, 字段名2 數(shù)據(jù)類型, 字段名3 數(shù)據(jù)類型);create table 表名(字段名1 數(shù)據(jù)類型(length), 字段名2 數(shù)據(jù)類型(length), ...字段名n 數(shù)據(jù)類型(length));

【表名】：建議以t_或者tbl_開始，可讀性強。見名知意。

【字段名】：見名知意

表名和字段名都屬于標識符

7.2 關(guān)于mysql中的數(shù)據(jù)類型

有很多，我們只需要掌握常見的數(shù)據(jù)類型即可。

varchar(最長255)

聲明最大長度，存儲可變長度的字符串，會根據(jù)實際的數(shù)據(jù)長度動態(tài)分配空間

比較智能，節(jié)省空間

【優(yōu)點】：節(jié)省空間

【缺點】：需要動態(tài)分配空間，速度慢

char

定長字符串

不管實際的數(shù)據(jù)長度是多少，分配固定長度的空間取存儲數(shù)據(jù)

使用不恰當(dāng)時，可能會導(dǎo)致空間的浪費

【優(yōu)點】：不需要動態(tài)分配空間

【缺點】：使用不當(dāng)可能會導(dǎo)致空間的浪費

性別字段選用char，因為固定長度

int(最長11)

? 1. 數(shù)字中的整數(shù)型。等同于java中的int

bigint

數(shù)字中的長整型。等同于java中的long

float

單精度浮點型數(shù)據(jù)

double

雙精度浮點型數(shù)據(jù)

date

短日期類型

datetime

長日期類型

clob

字符大對象，最多可存儲4G的字符串

比如：存儲一篇文章，一個說明

超過255個字符的都要采用CLOB字符大對象來存儲

Chracter Large Object: CLOB

blob

二進制大對象

Binary Large Object: BLOB

專門用來存儲圖片、聲音、視頻等流媒體數(shù)據(jù)

網(wǎng)BLOB類型的字段上插入數(shù)據(jù)的時候，例如插入一個圖片、視頻等，需要用IO流

【案例】t_moive 電影表（專門存儲電影信息的）

編號姓名故事情節(jié)上映日期時長海報類型

no(bigint)	name(varchar)	discription(clob)	playtime(date)	time(double)	image(blob)	type(char)
1000

7.3 創(chuàng)建一個學(xué)生表？

括號內(nèi)的只是建議長度，超過也不會報錯，char：1文字1長度

學(xué)號、姓名、年齡、性別、郵箱地址

create table t_student4_4(student_id int,student_name varchar(255),sex char(6),age int(3),email varchar(255) );

刪除表

drop table if exists t_student4_4;

7.4 插入數(shù)據(jù)insert ( DML )

【注意】: insert 只要執(zhí)行成功，就會多一行記錄。沒有指定默認值的字段，默認值為null。

語法格式：insert into 表名(字段名1, 字段名2, 字段名3, ...) values(值1, 值2, 值3, ...); 【注意】： 1. 字段名和值，要一一對應(yīng) 2. 前面的字段名省略的話，等于都寫上了！所以值也要【全都】寫上!!并且【不能顛倒順序】！! 3. insert 只要執(zhí)行成功，就會多一行記錄。沒有【指定默認值】的字段，默認值為null。 drop table if exists t_student4_4; create table t_student4_4(student_id int,student_name varchar(255),sex char(6),age int(3),email varchar(255),測試 varchar(255) default '沒輸入' ); insert into t_student4_4 values(001, 'Kate', 'f', 17, 'Kate@edu.csu.cn', '輸入了'); insert into t_student4_4(student_id, student_name, sex, age, email) values(002, 'Jack', 'm', 17, 'Jack@edu.csu.cn'); select * from t_student4_4;

7.5 insert插入日期

數(shù)字格式化：format(數(shù)字, ‘格式’)

1. 查詢員工薪水加入千分位 select ename,format(sal, 0) as sal from emp; 2. 查詢員工薪水加入千分位和保留兩位小數(shù) select ename, format(sal, 2) as sal from emp;

str_to_date 將字符串varchar類型轉(zhuǎn)換成date類型

通常用于insert語句，因為需要將date->varchar

drop table if exists t_student4_4; create table t_student4_4(id int,姓名 varchar(255),生日 char(10)#可以用data類型 #生日 date ); mysql> desc t_student4_4; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | 姓名 | varchar(255) | YES | | NULL | | | 生日 | char(10) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 插入數(shù)據(jù)？ insert into t_student4_4(id, 姓名, 生日) values(1, 'jack', '01-10-2001');

【命名規(guī)范】：所有標識符都是小寫，用下劃線銜接

insert into t_student4_4(id, 姓名, 生日) values(1, 'jack', str_to_date('01-10-2001', '%d-%m-%Y')); 【注意】：如果是'%Y-%m-%d'形式，無需 str_to_date insert into t_student4_4(id, 姓名, 生日) values(1, 'jack', '2001-02-11');

mysql日期格式，對大小寫敏感

%Y 年【記住這里是大寫】

%m 月

%d 日

%h 時

%i 分

%s 秒

date_format ：將date類型轉(zhuǎn)換成具有一定格式的varchar字符串類型

【用法】：date_format(日期類型數(shù)據(jù), ‘日期格式’)；

通常用于查詢?nèi)掌跁r，修改展示的日期格式。不指定的話，mysql會默認指定為：‘%Y-%m-%d’ 形式

mysql> select id, 姓名 'name', date_format(生日,'%Y年%m月%d日') as birth from t_student4_4; +------+------+----------------+ | id | name | birth | +------+------+----------------+ | 1 | jack | 2001年10月01日 | | 1 | jack | 2001年02月11日 | +------+------+----------------+mysql> select id, 姓名 'name', 生日 as birth from t_student4_4;#默認形式 +------+------+------------+ | id | name | birth | +------+------+------------+ | 1 | jack | 2001-10-01 | | 1 | jack | 2001-02-11 | +------+------+------------+

7.6 date和datetime兩個類型的區(qū)別

date：短日期，只包括年月日信息

datetime：是長日期，包括年月日時分秒信息

drop table if exists t_user; create table t_user(id int,name varchar(32),birth date,create_time datetime ); id 是整數(shù) name 是字符串 birth 是短日期 create_time 是這條記錄的創(chuàng)建時間：長日期類型

mysql短日期默認格式：%Y-%m-%d

mysql長日期默認格式：%Y-%m-%d %h: %i: %s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

mysql通過now() 函數(shù)獲取系統(tǒng)當(dāng)前時間，并且獲取的時間帶有：時分秒信息！！！！是datetime類型的。

insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now()); +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 | | 2 | lisi | 1991-10-01 | 2022-04-04 22:13:26 | | 3 | wangwu | 1991-10-01 | 2022-04-04 22:14:42 | +------+----------+------------+---------------------+

7.7 update ( DML )

語法格式： update 表名 set 字段名1 = 值1, 字段名2 = 值2, 字段名3 = 值3... where 條件; 【注意】：沒有條件會導(dǎo)致所有數(shù)據(jù)全部更新，所以條件要確定行【如，主鍵】 update t_user set name = 'jack' where id = 2; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 | | 2 | jack | 1991-10-01 | 2022-04-04 22:13:26 | | 3 | wangwu | 1991-10-01 | 2022-04-04 22:14:42 | +------+----------+------------+---------------------+ 3 rows in set (0.00 sec) update t_user set birth = '2002-02-26', create_time = now() where id = 2; mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 | | 2 | jack | 2002-02-26 | 2022-04-04 22:24:33 | | 3 | wangwu | 1991-10-01 | 2022-04-04 22:14:42 | +------+----------+------------+---------------------+ update t_user set create_time = now(); mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2022-04-04 22:25:08 | | 2 | jack | 1991-10-01 | 2022-04-04 22:25:08 | | 3 | wangwu | 1991-10-01 | 2022-04-04 22:25:08 | +------+----------+------------+---------------------+

7.8 delete(DML)

語法格式： delete from 表名 where 條件; 【注意】：沒有條件，整表全部刪除 delete from t_user where id = 2;mysql> select * from t_user; +------+----------+------------+---------------------+ | id | name | birth | create_time | +------+----------+------------+---------------------+ | 1 | zhangsan | 1990-10-01 | 2022-04-04 22:25:08 | | 3 | wangwu | 1991-10-01 | 2022-04-04 22:25:08 | +------+----------+------------+---------------------+ 2 rows in set (0.00 sec)insert into t_user(id) values(2); delete from t_user;#刪除所有內(nèi)容mysql> select * from t_user; Empty set (0.01 sec)drop table if exists t_user;#drop是刪除整個表結(jié)構(gòu)

CRUD操作（create 添加數(shù)據(jù)read讀取數(shù)據(jù) update 修改數(shù)據(jù)delete刪除數(shù)據(jù)）

三、Day 03

1、查詢每個員工的所在部門名稱？要求顯示員工名和部門名？

2、insert語句可以一次插入多條記錄【掌握】

【語法】： insert into 表名(字段名1, 字段名2) values(值1, 值2),(值1, 值2),(值1, 值2);insert into t_user(id, name, birth, create_time) values(1,'zs','1980-10-11',now()), (2,'lisi','1981-10-11',now()),(3,'wangwu','1982-10-11',now());

3、快速建表

【原理】：

將一個查詢結(jié)果當(dāng)做一張表新建！！！！！

這個可以完成表的快速復(fù)制！！！！

表創(chuàng)建出來，同時表中的數(shù)據(jù)也存在了！！！

create table mytable as select empno, ename from emp where job = 'manager';drop table if exists t_user;#drop是刪除整個表結(jié)構(gòu)

4、將查詢結(jié)果插入到一張表中？insert相關(guān)

#結(jié)果和表的結(jié)構(gòu)（字段個數(shù)和數(shù)據(jù)類型）要對應(yīng) insert into mytable (select * from mytable);#用的比較少

5、快速刪除表中的數(shù)據(jù)？【truncate比較重要，必須掌握，DDL操作】

【用法】：

truncate table dept_bak; #這種操作屬于DDL操作

【注意】：使用truncate之前，必須仔細詢問客戶是否真的要刪除，并警告刪除之后不可恢復(fù)！

delete原理：

? 表中數(shù)據(jù)被刪除了，但是數(shù)據(jù)在硬盤中的真實存儲空間不會被釋放！

缺點：刪除效率低

優(yōu)點：可以回滾數(shù)據(jù)

delete from mytable where empno = 7566;

truncate原理：

? 表被一次性截斷，物理刪除！

a. 缺點：不支持回滾

? b. 優(yōu)點：快速，效率高

大表，包含上億條記錄？？？？
使用 delete，也許需要執(zhí)行1個小時才能刪除完！效率較低。
使用 truncate，只需要不到1秒鐘的時間就刪除結(jié)束。效率較高。
truncate是刪除表中的數(shù)據(jù)，表還在！

與drop區(qū)別：

drop table 表名; # 這不是刪除表中的數(shù)據(jù)，這是把表刪除。

6、對表結(jié)構(gòu)的增刪改？【DDL，create drop alter，針對字段】

什么是對表結(jié)構(gòu)的修改？alter

添加一個字段、刪除一個字段、修改一個字段

#添加字段[默認字段于尾，可設(shè)置首，及任意位置。可添加默認值。comment里為備注信息] alter table 表名 add 新字段名數(shù)據(jù)類型[(長度)]; alter table 表名 add 字段名 varchar(255) first; alter table 表名 add 字段名 varchar(255) default null comment '申請詳情'; alter table 表名 add 字段名 decimal(10,2) DEFAULT '0.00' comment '抵扣消費金額' after 字段名(在其后插入新字段); #修改數(shù)據(jù)類型/長度 alter table 表名 modify 舊字段名數(shù)據(jù)類型; #修改字段名 alter table 表名 change 舊字段名新字段名數(shù)據(jù)類型; #刪除字段 alter table 表名 drop 字段名;

【測試】：快速建表，然后修改字段

#刪除整個表結(jié)構(gòu)mytable drop table if exists mytable; #根據(jù)select查詢結(jié)果快速創(chuàng)建表mytable create table mytable as (select e.deptno, e.empno, e.ename, e.sal, job, d.loc from emp e join dept d on e.deptno = d.deptno); #顯示表結(jié)構(gòu)和表內(nèi)容 desc mytable; select * from mytable; #新增字段gender alter table mytable add gender int; #設(shè)置新增gender所有內(nèi)容為1 update mytable set gender = 1; #顯示表結(jié)構(gòu)和表內(nèi)容 select * from mytable; desc mytable; #把所有列顯示為小寫 update mytable set ename = lower(ename), loc = lower(loc), job = lower(job); #把所有列都顯示為大寫 update mytable set ename = upper(ename), loc = upper(loc), job = upper(job); #把gender字段改為sex字段 alter table mytable change gender sex int; #刪除新增的sex字段 alter table mytable drop sex; #在第一列新增字段：number, not null表示字段值不能為空 alter table mytable add number int not null comment '測試序號' first; #在序號后新增字段：university alter table mytable add university varchar(10) default 'CSU' after number; #顯示表結(jié)構(gòu)和表內(nèi)容 desc mytable; select * from mytable;

對表修改的修改使用：alter

屬于DDL語句

DDL包括：create、drop、alter

第一：在實際開發(fā)中，需求一旦確定后，表一旦設(shè)計好，很少進行表結(jié)構(gòu)的修改。因為開發(fā)過程中，修改表結(jié)構(gòu)，成本較高。修改表結(jié)構(gòu)之后，對應(yīng)的java代碼就需要進行大量的修改。

第二：修改表結(jié)構(gòu)的操作較少，如果需要修改表結(jié)構(gòu)，使用工具

第三：修改表結(jié)構(gòu)的操作不需要寫道java程序中，實際上也不是java程序員的范疇。

7、約束（極其重要*****）

7.1 什么是約束？

約束對應(yīng)的英語單詞：constraint

在創(chuàng)建表的時候，我們可以給表的字段加上一些約束，來保證這個表數(shù)據(jù)的完整性、有效性。

約束的作用：保證表中的數(shù)據(jù)有效！！！

只是約束，對字段刪除/增加約束，不會增加/減少字段

7.2 約束包括哪些？

非空約束：not null
唯一性約束：unique
主鍵約束：primary key（PK）
外鍵約束：foreign key（FK）
檢查約束：check key（mysql不支持，orcale支持）

7.3 非空約束：not null

非空約束not null約束的字段不能為null

列級約束

drop table if exists t_vip; create table t_vip(id int,name varchar(255) not null // not null只有列級約束，沒有表級約束！ ); insert into t_vip(id,name) values(1,'zhangsan'); insert into t_vip(id,name) values(2,'lisi');insert into t_vip(id) values(3); ERROR 1364 (HY000): Field 'name' doesn't have a default value

7.4 唯一性約束：unique

唯一性約束unique約束的字段不能重復(fù)，但是可以為null

列級約束

表級約束：需要給多個字段聯(lián)合起來添加某一個約束的時候，需要使用表級約束。

drop table if exists t_vip; create table t_vip(id int,name varchar(255) unique,email varchar(255));insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');select * from t_vip;insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name' #name字段雖然被unique約束了，但是可以為NULL。insert into t_vip(id) values(4);insert into t_vip(id) values(5);

兩個字段聯(lián)合起來具有唯一性

# 1. 如下不符合，因為這代表各自唯一，不是聯(lián)合 drop table if exists t_vip; create table t_vip(id int,#約束直接添加到列后面的，叫做【列級約束】。name varchar(255) unique,email varchar(255) unique#但如果采用以上方式創(chuàng)建表的話，肯定創(chuàng)建失敗，因為'zhangsan'和'zhangsan'重復(fù)了。insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com'); ); # 2. 應(yīng)該這樣創(chuàng)建 drop table if exists t_vip; create table t_vip(id int,name varchar(255) unique not null,email varchar(255) unique not null,#約束沒有添加在列的后面，這種約束被稱為【表級約束】。unique(name,email) );insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');select * from t_vip;

unique和null聯(lián)合

在mysql當(dāng)中，如果一個字段同時被not null和unique約束的話，該字段自動變成主鍵字段（非空且不重復(fù)，當(dāng)然是主鍵特征）。多個字段被同時聲明not null unique，則第一個為主鍵。

【注意】：oracle中不一樣！

7.5 主鍵約束（Primary key, PK，非常重要*****）

PK的相關(guān)術(shù)語：

主鍵約束：就是一種約束

主鍵字段：該字段上添加了主鍵約束，這樣的字段叫：主鍵字段

主鍵值：主鍵字段中的每個值都叫做：主鍵值

什么是主鍵？作用？

主鍵值是每一行記錄的唯一標識

任何表都應(yīng)該有主鍵，否則是無效表

主鍵特征

not null+unique，非空且不重復(fù)

給表添加主鍵約束

列級約束：

表級約束：復(fù)合主鍵（聯(lián)合主鍵）

建議單一主鍵，不建議復(fù)合主鍵

結(jié)論：一張表，主鍵約束只能添加1個。（主鍵只能有1個）

主鍵值建議類型：

int

bigint

char

主鍵值通常是遞增的數(shù)字，一般是定長，不建議使用varchar

主鍵分類2種

單一主鍵

復(fù)合主鍵

自然主鍵：主鍵值是一個自然數(shù)，和業(yè)務(wù)沒關(guān)系

業(yè)務(wù)主鍵：主鍵值和業(yè)務(wù)緊密關(guān)聯(lián)，例如拿銀行卡賬號作主鍵值。

自然主鍵使用較多，因為不需要意義，只要不重復(fù)即可。如果與業(yè)務(wù)掛鉤，則業(yè)務(wù)發(fā)生變動時，可能會影響主鍵值。

MySQL自動維護主鍵值

創(chuàng)建表時，可以使用 auto_increment 表示自增，從1開始，以1遞增

drop table if exists t_vip; create table t_vip(id int primary key auto_increment,name varchar(255) ); insert into t_vip(name) values ('jack'), ('jack'), ('jack'), ('jack');

已存在的表，添加主鍵約束

alter table 表名 modify 列名數(shù)據(jù)類型 primary key；alter table 表名 add primary key(列名);alter table 表名 add constraint 主鍵約束的名字(自定義) primary key(列名);

? 3. 刪除遞增主鍵約束時，要先去掉遞增屬性

#遞增主鍵字段id改為普通int alter table 表名 modify 字段名 int; #刪除普通主鍵約束（只有一個主鍵，不用指定字段） alter table 表名 drop primary key;

【代碼測試】

drop table if exists t_vip; #1個字段做主鍵，叫做：單一主鍵 create table t_vip(id int primary key, #列級約束name varchar(255) ); insert into t_vip(id,name) values(1,'zhangsan'),values(2,'lisi');#錯誤：不能重復(fù) insert into t_vip(id,name) values(2,'wangwu'); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'#錯誤：不能為NULL insert into t_vip(name) values('zhaoliu'); ERROR 1364 (HY000): Field 'id' doesn't have a default value#可以使用表級約束添加約束 drop table if exists t_vip;create table t_vip(id int,name varchar(255),primary key(id) #表級約束);insert into t_vip(id,name) values(1,'zhangsan');#錯誤insert into t_vip(id,name) values(1,'lisi'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' #表級約束主要是給多個字段聯(lián)合起來添加約束, #id和name聯(lián)合起來做主鍵：復(fù)合主鍵！！！！ drop table if exists t_vip;create table t_vip(id int, name varchar(255),email varchar(255),primary key(id,name) );insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');#錯誤：不能重復(fù)insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'#一個表中主鍵約束能加兩個嗎？不，有且僅有1個drop table if exists t_vip;create table t_vip(id int primary key,name varchar(255) primary key); #ERROR 1068 (42000): Multiple primary key defined

7.6 外鍵約束（foreign key, PK, 非常重要）

相關(guān)術(shù)語

外鍵約束：一種約束：foreign key

外鍵字段：該字段被添加了外鍵約束

外鍵值：主鍵字段當(dāng)中的每一個值

外鍵不一定是主表的主鍵，但是一定要具有唯一性unique約束，添加外鍵后，外鍵值可以為空

業(yè)務(wù)背景

設(shè)計數(shù)據(jù)庫表，描述班級和學(xué)生信息

【方案1】：班級和學(xué)生存儲在一張表

no(PK)nameclassnoclassname

1	jack	100	北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三1班
2	luck	100	北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三1班
3	rose	101	北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三2班
4	bourne	101	北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三2班

缺點：數(shù)據(jù)冗余、空間浪費
評價：設(shè)計比較失敗

【方案2】：班級表、學(xué)生表

classno(pk)classname

100	北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三1班
101	北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三2班

no(pk)namecsno

1	jack	100
2	luck	100
3	rose	101
4	bourne	101

當(dāng)csno沒有任何約束的時候，可能導(dǎo)致數(shù)據(jù)無效（102不在班級表中）

為了保證cno字段中的值都是100和101，需要給cnoi段添加外鍵約束

【注意】：如果classno不具有唯一性，那么就無法得知csno所屬了

【故】：csno就是外鍵字段，csno字段中每一個值都是外鍵值。

【注意】：t_class是父表（被引用的表）

? t_student是子表（添加外鍵的表）

drop table if exists t_class; drop table if exists t_students; #因為希望有外鍵，故先創(chuàng)建父表，再子表 create table t_class(classno int primary key,classname varchar(255) ); create table t_students(no int primary key auto_increment,#主鍵自增name varchar(255),csno int,#【添加外鍵約束】foreign key(csno) references t_class(classno) );insert into t_class(class, classname) values(100, '北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三1班'); insert into t_class(class, classname) values(101, '北京市大興區(qū)亦莊鎮(zhèn)第二中學(xué)高三2班');insert into t_students(name, csno) values ('jack', 100), ('lucy', 100), ('rose', 101), ('bourne', 101); insert into t_students(name) values ('kate');#報錯 insert into (csno) values(102); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_students`, CONSTRAINT `t_students_ibfk_1` FOREIGN KEY (`csno`) REFERENCES `t_class` (`classno`))

創(chuàng)建外鍵【約束】的兩種方式

創(chuàng)建表時直接創(chuàng)建外鍵約束

備注：必須先創(chuàng)建參照表，才能在創(chuàng)建外鍵約束，即必須現(xiàn)有表Category，再有book
foreign key(子表字段) references 父表名(父表字段); #刪除外鍵字段 alter table 表名 drop foreign key 外鍵約束的名字;
先創(chuàng)建表，表創(chuàng)建成功后，單獨添加外鍵約束
alter table 子表名 add constraint 外鍵約束的名字(自定義) foreign key(字段1) references 主表名(字段2);

【測試】

create table books(bookid number(10) not null primary key,bookName varchar2(20) not null,price number(10,2),categoryId number(10) not null ); ALTER TABLE books ADD CONSTRAINT FK_Book_categoryid FOREIGN KEY(categoryId ) REFERENCES Category(id); create table t_students(no int primary key auto_increment,#主鍵自增name varchar(255) not null,csno int not null default 100,#【添加外鍵約束】foreign key(csno) references t_class(classno) );#刪除外鍵約束【只是刪除約束，并沒有刪除字段】 alter table t_students drop foreign key class_id; alter table t_students add class_id int default 100 after name; #如下【constaint關(guān)鍵字一定要在】 alter table t_students add constraint class_id foreign key(class_id) references t_class(classno);

主表和子表的創(chuàng)建和刪除具有順序

依照他們的時間周期

7.7 級聯(lián)更新與級聯(lián)刪除

級聯(lián)更新 on update cascade

先刪除子表的外鍵約束

然后同時添加外鍵約束和級聯(lián)更新

這樣，當(dāng)更新父表被引用的字段時，子表會被自動更新

alter table t_students drop foreign key fk_class_id;alter table t_students add constraint fk_class_id foreign key(class_id) references t_class(classno) on update cascade;#要保證所有沒有被設(shè)置級聯(lián)更新的外鍵都被刪除了 update t_class set classno = 20 where classno = 101;

級聯(lián)刪除 on delete cascade

先刪除子表的外鍵約束

然后同時添加外鍵約束和級聯(lián)刪除

這樣，當(dāng)刪除父表被引用的字段時，子表會被自動刪除

alter table t_students drop foreign key fk_class_id;alter table t_students add constraint fk_class_id foreign key(class_id) references t_class(classno) on delete cascade; #只刪除父表中數(shù)據(jù)，但是子表中的數(shù)據(jù)也會被刪除 delete from t_class where classno = 100;

7.8 刪除約束

1. alter table 子表名 drop foreign key 外鍵約束名; 2. alter table 子表名 drop primary key 主鍵約束名; 3. alter table 子表名 drop key 約束名;

7.9 添加約束

1. alter table 子表名 add constraint 外鍵約束名 foreign key(子表字段) references 父表名(父表字段); 2. alter table 子表名 add constraint 外鍵約束名 primary key(字段);

7.10 修改約束

alter table 表名 modify 字段名類型約束;

8、存儲引擎

8.1 存儲引擎的作用

#看表的詳細信息：存儲引擎、結(jié)構(gòu)、約束等 show create table 表名;mysql> show create table t_students; +------------+-------------------------------------------------------------- | Table | Create Table | t_students | CREATE TABLE `t_students` (`no` int NOT NULL,`name` varchar(255) DEFAULT NULL,`class_id` int DEFAULT '100',`csno` int DEFAULT NULL,PRIMARY KEY (`no`),KEY `fk_csno` (`csno`),KEY `fk_class_id` (`class_id`),CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`classno`),CONSTRAINT `fk_csno` FOREIGN KEY (`csno`) REFERENCES `t_class` (`classno`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +------------+--------------------------------------------------------------

存儲引擎是MySQL中特有的一個術(shù)語，其他數(shù)據(jù)庫中沒有。

Oracle中有，但是不叫這個名字

存儲引擎是存儲/組織數(shù)據(jù)的方式

不同的存儲引擎，存儲數(shù)據(jù)的方式不同

8.2 給表添加/指定“存儲引擎”

查看表的詳細信息：存儲引擎、結(jié)構(gòu)、約束…

1. show create table 表名; 2. show table status '表名';

建表時使用關(guān)鍵字ENGINE顯示指定存儲引擎

CREATE TABLE `t_student` (`no` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`cno` int(11) DEFAULT NULL,PRIMARY KEY (`no`),KEY `cno` (`cno`),CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

對現(xiàn)有表使用alter table語句改變存儲引擎

alter table 表名 engine = innodb;

8.3 查看mysql支持的存儲引擎

查看mysql版本

mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.28 | +-----------+

查看支持引擎

mysql> show engines\G *************************** 1. row ***************************Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tables Transactions: NOXA: NOSavepoints: NO *************************** 2. row ***************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tables Transactions: NOXA: NOSavepoints: NO *************************** 3. row ***************************Engine: CSVSupport: YESComment: CSV storage engine Transactions: NOXA: NOSavepoints: NO *************************** 4. row ***************************Engine: FEDERATEDSupport: NOComment: Federated MySQL storage engine Transactions: NULLXA: NULLSavepoints: NULL *************************** 5. row ***************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance Schema Transactions: NOXA: NOSavepoints: NO *************************** 6. row ***************************Engine: MyISAMSupport: YESComment: MyISAM storage engine Transactions: NOXA: NOSavepoints: NO *************************** 7. row ***************************Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keys Transactions: YESXA: YESSavepoints: YES *************************** 8. row ***************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears) Transactions: NOXA: NOSavepoints: NO *************************** 9. row ***************************Engine: ARCHIVESupport: YESComment: Archive storage engine Transactions: NOXA: NOSavepoints: NO 9 rows in set (0.00 sec)

8.4 mysql常用存儲引擎

【主鍵】：對于一張表，主要是主鍵，或者加有unique約束的字段上會自動創(chuàng)建索引

MyISAM存儲引擎

最常用的存儲引擎

具有如下特征：

使用三個文件表示每個表

格式文件-存儲表結(jié)構(gòu)的定義（mytable.frm）

數(shù)據(jù)文件-存儲表行的內(nèi)容（mytable.myd）

索引文件-存儲表上的索引（mytable.myi）

靈活的auto-increment字段處理

【優(yōu)勢】可被轉(zhuǎn)換為壓縮、只讀表來節(jié)省空間

MyISAM不支持事務(wù)機制，安全性低

全表鎖

InnoDB存儲引擎

MySQL默認采用InnoDB存儲引擎，是重量級的存儲引擎（行鎖）

innodb支持事務(wù)，支持數(shù)據(jù)庫崩潰后自動恢復(fù)機制

主要特點是十分安全

具有如下特征：

每個innodb表在數(shù)據(jù)庫目錄中以.frm格式文件表示

用commit（提交）、savepoint及rollback（回滾）支持事務(wù)處理

提供ACID兼容

在mysql服務(wù)器崩潰后，可提供自動恢復(fù)

多版本（MVCC）和行級鎖定

支持外鍵及引用的完整性，包括級聯(lián)刪除和更新

主要特點：

支持事務(wù)，以保證數(shù)據(jù)的安全性

效率不是很高，不能壓縮，不能轉(zhuǎn)換為只讀，不能很好的節(jié)省磁盤空間

memory存儲引擎

【速度快】使用memory存儲引擎的表，數(shù)據(jù)存儲在內(nèi)存中，且行的長度固定

具有如下特征：

在數(shù)據(jù)庫目錄內(nèi)，每個表均以.frm格式的文件表示

表數(shù)據(jù)及索引被存儲在內(nèi)存中（目的就是快）

表級鎖

不能包含TEXT或者BLOB字段

以前被稱作heap引擎

主要特點：

查詢效率是最高的，不需要和硬盤交互

不安全，關(guān)機之后數(shù)據(jù)消失，因為數(shù)據(jù)和索引都是在內(nèi)存中

9、事務(wù)（非常重要*****）

9.1 什么是事務(wù)

? 事務(wù)就是一組DML語句對數(shù)據(jù)庫進行操作，它們要么全部完成，要么全部不完成，具有ACID性質(zhì)：

9.2 什么sql語句支持事務(wù)

只有以下三個DML語句才會有事務(wù)，其他語句與事務(wù)無關(guān)

insert

delete

update

因為以上三個數(shù)據(jù)對數(shù)據(jù)庫表中數(shù)據(jù)進行增、刪、改，涉及到了安全問題，故引入事務(wù)。

9.3 事務(wù)存在的意義

因為所有的業(yè)務(wù)不可能一條DML語句就能完成，多條DML涉及到安全問題，故必須引入事務(wù)。

一個事務(wù)其實就是多條DML語句同時成功，或者同時失敗。

9.4 事務(wù)的原理

1. InnoDB存儲引擎：提供了一組用來記錄事務(wù)性活動的日志文件事務(wù)開啟了：insertinsertinsertdeleteupdateupdateupdate事務(wù)結(jié)束了！

在事務(wù)執(zhí)行過程中，每一條DML操作都會記錄到“事務(wù)性活動的日志文件“中；在事務(wù)的執(zhí)行過程中，我們可以提交事務(wù)，也可以回滾事務(wù)。

提交事務(wù)

清空事務(wù)性活動的日志文件，將數(shù)據(jù)全部徹底持久化到數(shù)據(jù)庫表中

提交事務(wù)標志著：事務(wù)結(jié)束，并且是一種全部成功的結(jié)束。

回滾事務(wù)

將之前所有的DML操作全部撤銷，并且清空事務(wù)性活動的日志文件

回滾事務(wù)標志著：事務(wù)的結(jié)束，并且是一種全部失敗的結(jié)束。

9.5 如何提交事務(wù)，如何回滾事務(wù)

提交事務(wù)：commit語句

回滾事務(wù)：rollback語句（永遠只能回滾到上一次的提交點）

【transaction：事務(wù)】

mysql中默認自動提交事務(wù)

每執(zhí)行一條DML語句，就提交一次

并不符合我們的開發(fā)習(xí)慣，因為我們通常需要多條DML語句作為一個業(yè)務(wù)，同時成功后才能提交，而不能

自定義事務(wù)提交

先開啟事務(wù)：

start transaction

執(zhí)行事務(wù)操作

【回滾】：演示事務(wù)回滾

create table dept_bak(no int, name varchar(255), hometown varchar(255)); start transaction; insert into dept_bak values(10,'abc', 'bj'); insert into dept_bak values(20,'abc', 'tj'); select * from dept_bak; rollback; select * from dept_bak;

【提交】：演示事務(wù)提交

start transaction; insert into dept_bak values(10,'abc','bj'); insert into dept_bak values(20,'abc','tj'); commit; select * from dept_bak;

9.6 事務(wù)的ACID性質(zhì)

原子性（atomicty）

事務(wù)是最小的工作單元，不可再分，要么全部提交，要么全部回滾，沒有中間狀態(tài)

一致性（consistency）

事務(wù)執(zhí)行前后，數(shù)據(jù)從一個合法性狀態(tài)變換成另一個合法性狀態(tài)，這種狀態(tài)是語義上的（滿足預(yù)定的約束狀態(tài)），不是語法上的。

隔離性（Isolation）

一個事務(wù)的執(zhí)行，不能被其它事務(wù)，即一個事務(wù)內(nèi)部的操作及其使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個事務(wù)之間不能相互干擾（資源互斥）

持久性（durability）

一個事務(wù)一旦被提交，他對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久性的（通過重做日志和回滾日志實現(xiàn)）。事務(wù)提交：將沒有保存到硬盤上的數(shù)據(jù)保存到硬盤上。

【原子性是基礎(chǔ)，隔離性是手段，一致性是約束條件，持久性則是我們的目的】

9.7 事務(wù)的5種狀態(tài)

活動的：事務(wù)對應(yīng)的數(shù)據(jù)庫操作正在執(zhí)行

部分提交的：事務(wù)中的最后一個操作完成，但是操作都在內(nèi)存中，還沒有把結(jié)果刷新到磁盤中

失敗的：處于活動的，或者部分提交時出現(xiàn)了錯誤

中止的：執(zhí)行了一部分而變?yōu)槭〉?#xff0c;就需要把已經(jīng)修改的事務(wù)的操作還原到執(zhí)行之前，這個回滾操作完成后，數(shù)據(jù)庫恢復(fù)到了執(zhí)行事務(wù)之前的狀態(tài)，我們就說該事務(wù)處于中止

提交的：修改過的數(shù)據(jù)都同步到了磁盤

9.7 事務(wù)隔離級別

數(shù)據(jù)并發(fā)問題

（不保證串行）：【數(shù)據(jù)不一致】
**1. 臟寫：**事務(wù)A修改了另一個未提交事務(wù)B修改過（可能會回滾）的數(shù)據(jù)，就發(fā)生了臟寫（提交了的數(shù)據(jù)被回滾掉了，即數(shù)據(jù)沒有變化）
**2. 臟讀：**事務(wù)A讀取了B更新但沒有被提交的字段（若B回滾了，則A讀取的內(nèi)容是臨時且無效的）
3. 不可重復(fù)讀：A讀取了一個字段，然后B更新了該字段，則A再次讀取同一個字段，值就不同了
4. 幻讀（多讀：讀到了之前沒讀到的記錄）：A從一個表讀取了一個字段，B插入了新行（幻影記錄），A再讀就多了幾行

四種隔離級別（都解決了臟寫）：都解決了，并發(fā)性特別差，先給問題嚴重性排序：
? 臟寫>臟讀>不可重復(fù)讀>幻讀

四個隔離級別（逐漸加強）

我們愿意舍棄一部分隔離性來換取一部分性能：設(shè)立4個隔離級別，越低則并發(fā)問題發(fā)生越多，越往下并發(fā)越差
1. READ UNCOMMITTED：讀未提交，允許一個事務(wù)讀其他事務(wù)未提交的字段，有臟讀可能
2. READ COMMITTED：讀已提交(oracle默認)，一個事務(wù)只能讀其他事務(wù)已提交的字段，有不可能臟讀
3. REPEATABLE READ：可重復(fù)讀(mysql默認)，確保如果在一個事務(wù)中多次執(zhí)行相同的select語句，都能得到相同的結(jié)果，不管其他事務(wù)是否提交修改（銀行轉(zhuǎn)賬）。
4. SERIALIZABLE：可串行化，效率最低，事務(wù)排隊，不能并發(fā)。synchronized，線程同步（事務(wù)同步）。

隔離級別臟讀可能不可重復(fù)讀可能幻讀可能性加鎖讀

讀未提交	yes	yes	yes	no
讀已提交	no	yes	yes	no
可重復(fù)讀	no	no	InnoDB解決了	no
可串行化	no	no	no	yes

9.8 驗證各種隔離級別

#設(shè)置全局隔離級別 set global transaction isolation level read uncommitted; #- read-uncommitted #- read-committed #- repeatable-read #- serializable#退出后重進，查看隔離級別 sql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec) #打開兩個mysql端，進行事務(wù)的隔離級別驗證

9.9 設(shè)置服務(wù)器的缺省隔離級別

通過修改配置文件設(shè)置

可以在my.ini文件中使用transaction-isolation選項來設(shè)置服務(wù)器的缺省事務(wù)隔離級別
- read-uncommitted
- read-committed
- repeatable-read
- serializable
- [mysqld] - transaction-isolation = read-committed

通過動態(tài)命令設(shè)置隔離級別

#設(shè)置全局隔離級別，要退出后重進，才修改了隔離級別 set global transaction isolation level read uncommitted; #global -> session則是僅改變當(dāng)前會話的隔離級別，不用退出重進 set session transaction isolation level read uncommitted; #查看事務(wù)隔離級別 sql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | read uncommitted | +-------------------------+ 1 row in set (0.00 sec)
- read uncommitted
- read committed
- repeatable read
- serializable

三、Day 04

1、索引

1.1 什么是索引

MySQL在查詢方面主要就是兩種方式：
第一種方式：全表掃描
第二種方式：根據(jù)索引檢索。

? 索引【自主添加】是在數(shù)據(jù)庫表的字段上添加的，是為了提高查詢效率存在的一種機制。一張表的一個字段可以添加一個索引，多個字段聯(lián)合起來也可以添加索引。

? 索引相當(dāng)于一本書的目錄，是為了縮小掃描范圍而存在的機制。

? 對于一本字典來說，查找某個漢字有兩種方式：
? 第一種方式：一頁一頁挨著找，直到找到為止，這種查找方式屬于全字典掃描。
? 效率比較低。
? 第二種方式：先通過目錄（索引）去定位一個大概的位置，然后直接定位到這個
? 位置，做局域性掃描，縮小掃描的范圍，快速的查找。這種查找方式屬于通過
? 索引檢索，效率較高。

select * from t_user where name = ‘jack’;

以上的這條SQL語句會去name字段上掃描，為什么？
因為查詢條件是：name=‘jack’

如果name字段上沒有添加索引，或者說沒有給name字段創(chuàng)建索引，
MySQL會進行全掃描，會將name字段上的每一個值都比對一遍。效率比較低。

【注意】：
在實際中，漢語字典前面的目錄是排序的，按照a b c d e f…排序，
為什么排序呢？因為只有排序了才會有區(qū)間查找這一說！（縮小掃描范圍
其實就是掃描某個區(qū)間罷了！）

在mysql數(shù)據(jù)庫當(dāng)中索引也是需要排序的，并且這個所以的排序和TreeSet
數(shù)據(jù)結(jié)構(gòu)相同。TreeSet（TreeMap）底層是一個自平衡的二叉樹！在mysql
當(dāng)中索引是一個B-Tree數(shù)據(jù)結(jié)構(gòu)。

? 遵循左小右大原則存放。采用中序遍歷方式遍歷取數(shù)據(jù)。

1.2 索引的實現(xiàn)原理

假設(shè)有一張用戶表：t_user

id(PK) name 每一行記錄在硬盤上都有物理存儲編號 ------------------------------------------------------------------------ 100 zhangsan 0x1111 120 lisi 0x2222 99 wangwu 0x8888 88 zhaoliu 0x9999 101 jack 0x6666 55 lucy 0x5555 130 tom 0x7777

【提醒1】：在任何數(shù)據(jù)庫當(dāng)中主鍵上都會自動添加索引對象，id字段上自動有索引，因為id是PK。另外在mysql中，一個字段上如果有unique約束的話，也會自動創(chuàng)建索引對象。

【提醒2】：在任何數(shù)據(jù)庫中，任何一張表的任何一條記錄在硬盤存儲上都有一個物理地址

【提醒3】：在mysql中，索引是一個單獨的對象，不同的存儲引擎以不同的形式存在，在myisam存儲引擎中，索引存儲在.myi文件中。在innodb中，存儲在一個邏輯名稱為tablespace中。在memory中，存儲在內(nèi)存中。

1.3、mysql自動添加索引

在mysql當(dāng)中，主鍵上，以及unique字段上都會自動添加索引的！！！！

1.4 索引的優(yōu)化

什么條件下，我們會考慮給字段添加索引呢？
條件1：數(shù)據(jù)量龐大（到底有多么龐大算龐大，這個需要測試，因為每一個硬件環(huán)境不同）
條件2：該字段經(jīng)常出現(xiàn)在where的后面，以條件的形式存在，也就是說這個字段總是被掃描。
條件3：該字段很少的DML(insert delete update)操作。（因為DML之后，索引需要重新排序！！！時間倍增！！！）

建議不要隨意添加索引，因為索引也是需要維護的，太多的話反而會降低系統(tǒng)的性能。
建議通過主鍵查詢，建議通過unique約束的字段進行查詢（因為這里有索引），效率是比較高的。

1.5 索引的創(chuàng)建、刪除

創(chuàng)建索引

#給emp表的ename字段添加索引，起名：emp_ename_index create index emp_ename_index on emp(ename);

刪除索引

#將emp表上的emp_ename_index索引對象刪除 drop index emp_ename_index on emp;

1.6 查看SQL語句是否使用了索引進行檢索

使用explain關(guān)鍵字

#未添加索引，直接查詢非主鍵 explain select * from emp where ename = 'KING'; #直接查詢主鍵（自動添加索引） explain select * from emp where empno = 7566; #給非主鍵添加索引后查詢 create index emp_ename_index on emp(ename); explain select * from emp where ename = 'KING';

1.7 索引失效(優(yōu)化策略)

對含索引字段進行模糊查詢時，如果以%開頭，那么不會使用索引

explain select * from emp where ename like '%T';

優(yōu)化策略：模糊查詢是不使用%開頭

如果使用or，則兩邊字段都要有索引，否則索引失效

explain select * from emp where ename = 'KING' or job = 'MANAGER';

使用復(fù)合索引，需要使用左側(cè)的列查找，否則索引失效

復(fù)合索引：兩個及兩個以上字段，聯(lián)合起來添加一個索引
create index emp_job_sal_index on emp(job,sal); explain select * from emp where job = 'MANAGER'; explain select * from emp where sal = 5000;

where中索引列不能參與運算，否則索引失效

create index emp_sal_index on emp(sal); explain select * from emp where sal*2 = 1600;

where中索引列不能使用函數(shù)

explain select * from emp where lower(ename) = 'smith';

1.8 數(shù)據(jù)庫優(yōu)化重要手段：索引

數(shù)據(jù)庫優(yōu)化時，首先考慮的就是索引。

單一索引：一個字段上添加索引

復(fù)合索引：兩個及兩個以上字段上添加索引

主鍵索引：主鍵上添加索引（主鍵和unique鍵自動添加索引）

唯一性索引：具有unique約束的字段上添加索引

…

【注意】：唯一性比較弱的字段上，添加索引用處不大

2、視圖（View）

2.1 什么是視圖

view：站在不同角度，看待同一份數(shù)據(jù)

2.2 創(chuàng)建/刪除視圖對象

只有DQL語句才能以view的形式創(chuàng)建，相當(dāng)于地址

create view dept2_view as（DQL語句）;

#先建一個表用于演示視圖 create table dept as (select * from dept); #創(chuàng)建視圖對象 create view dept2_view as select * from dept2; #刪除視圖 drop view dept2_view;

2.3 視圖的作用：類似JAVA引用

我們可以面向視圖對象進行增刪改查，對視圖對象的增刪改，會導(dǎo)致原表被操作！

特點：對視圖的操作，會影響到原表數(shù)據(jù)。

#面向視圖查詢 select * from dept2_view; #面向視圖插入 insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');#查詢原表數(shù)據(jù) select * from dept2; #創(chuàng)建兩個表連接的視圖 create view emp_dept_view as(select e.ename, e.sal, d.dnamefrom emp ejoindept done.deptno = d.deptno ); #查詢視圖對象 select * from emp_dept_view; +--------+---------+------------+ | ename | sal | dname | +--------+---------+------------+ | SMITH | 800.00 | RESEARCH | | ALLEN | 1600.00 | SALES | | WARD | 1250.00 | SALES | | JONES | 2975.00 | RESEARCH | | MARTIN | 1250.00 | SALES | | BLAKE | 2850.00 | SALES | | CLARK | 2450.00 | ACCOUNTING | | SCOTT | 3000.00 | RESEARCH | | KING | 5000.00 | ACCOUNTING | | TURNER | 1500.00 | SALES | | ADAMS | 1100.00 | RESEARCH | | JAMES | 950.00 | SALES | | FORD | 3000.00 | RESEARCH | | MILLER | 1300.00 | ACCOUNTING | +--------+---------+------------+ #面向視圖更新【謹慎修改，原表數(shù)據(jù)也變了】 update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING'; #查詢原表 select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 1000.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1000.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+

2.4 視圖對象在實際開發(fā)中的作用

提高代碼復(fù)用、簡化開發(fā)、利于維護

比如視圖emp_dept_view就代表了下面的兩表連接查詢語句，該視圖對象即代表了復(fù)雜的查詢語句。

存儲在硬盤內(nèi)。如果將查詢結(jié)果直接新建表，并不能修改原表

#創(chuàng)建兩個表連接的視圖 create view emp_dept_view as(select e.ename, e.sal, d.dnamefrom emp ejoindept done.deptno = d.deptno );

【注意】：
1. 視圖對應(yīng)的語句只能是DQL語句。
2. 但是視圖對象創(chuàng)建完成之后，可以對視圖進行增刪改查等操作。

【CRUD】:
增刪改查，又叫做：CRUD。
CRUD是在公司中程序員之間溝通的術(shù)語。一般我們很少說增刪改查。
一般都說CRUD。

C: Create （增） R: Retrieve（查：檢索） U: Update （改） D: Delete （刪）

3、DBA常用命令

database administrator，數(shù)據(jù)庫管理員

3.1 重點掌握

數(shù)據(jù)的導(dǎo)入和導(dǎo)出（數(shù)據(jù)備份）
- 導(dǎo)入
- 導(dǎo)出
其他命令了解即可
- 新建用戶
- 授權(quán)
- 回收權(quán)限

3.2 數(shù)據(jù)導(dǎo)出

? 在windows的dos命令窗口：

mysqldump 數(shù)據(jù)庫名>文件路徑存放文件名.sql -uroot -p123456 #刪除庫 drop database bjpowernode;
導(dǎo)出指定表
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

3.3 數(shù)據(jù)導(dǎo)入

【source】

? step 1. 先登錄到mysql數(shù)據(jù)庫服務(wù)器上

? step 2. 創(chuàng)建數(shù)據(jù)庫：create database bjpowernode;

? step 3. 使用數(shù)據(jù)庫：use bjpowernode

? step 4. 初始化數(shù)據(jù)庫：source D:\bjpowernode.sql

4、數(shù)據(jù)庫設(shè)計的三范式（重要）

4.1 什么是數(shù)據(jù)庫設(shè)計范式

數(shù)據(jù)庫表的設(shè)計規(guī)則

4.2 數(shù)據(jù)庫設(shè)計三大范式

第一范式：要求任何一張表都必須要有主鍵，每一個字段原子性不可再分
第二范式：建立在的第一范式基礎(chǔ)之上，要求所有非主鍵字段完全依賴主鍵，不要產(chǎn)生部分依賴
第三范式：建立在第二范式基礎(chǔ)之上，要求所有非主鍵字段直接依賴主鍵，不要產(chǎn)生傳遞依賴

【注意】：面試官經(jīng)常問三范式，要熟記于心

按照三大范式進行數(shù)據(jù)庫表設(shè)計，可以避免表中數(shù)據(jù)的冗余，空間的浪費

單一主鍵滿足第二范式，但是復(fù)合主鍵可能產(chǎn)生部份依賴，不滿足第二范式

4.3 第一范式

最核心，最重要的范式，所有表的設(shè)計都需要滿足

必須要有主鍵，并且每一個字段都是原子不可再分

學(xué)生編號學(xué)生姓名聯(lián)系方式 ------------------------------------------ 1001 張三 zs@gmail.com,1359999999 1002 李四 ls@gmail.com,13699999999 1001 王五 ww@163.net,13488888888

上表不滿足第一范式：沒有主鍵；聯(lián)系方式可以分為郵箱地址和電話

學(xué)生編號(pk) 學(xué)生姓名郵箱地址聯(lián)系電話 ---------------------------------------------------- 1001 張三 zs@gmail.com 1359999999 1002 李四 ls@gmail.com 13699999999 1003 王五 ww@163.net 13488888888

4.4 第二范式

建立在第一范式的基礎(chǔ)之上

要求所有的非主鍵字段必須【完全依賴】主鍵字段，不要產(chǎn)生【部分依賴】

學(xué)生編號學(xué)生姓名教師編號教師姓名 ---------------------------------------------------- 1001 張三 001 王老師 1002 李四 002 趙老師 1003 王五 001 王老師 1001 張三 002 趙老師

沒有主鍵，不滿足第一范式

不滿足第二范式：

這張表滿足了學(xué)生和老師的關(guān)系：（1個學(xué)生可能對應(yīng)多個老師，1個老師有多個學(xué)生）

這是非常典型的【多對多關(guān)系】！！！：3表2外鍵

【修改1】滿足第一范式：復(fù)合主鍵（PK: 學(xué)生編號+教師編號）

單一主鍵滿足第二范式，但是復(fù)合主鍵可能產(chǎn)生部份依賴，不滿足第二范式

學(xué)生編號+教師編號(pk) 學(xué)生姓名教師姓名

? 1001 001 張三王老師
? 1002 002 李四趙老師
? 1003 001 王五王老師
? 1001 002 張三趙老師

經(jīng)過修改之后，上表仍然不滿足第二范式：

“張三”依賴1001，“王老師”不依賴1001，而依賴001，顯然產(chǎn)生了部分依賴。

產(chǎn)生部分依賴造成：數(shù)據(jù)冗余了。空間浪費了。“張三”重復(fù)了，“王老師”重復(fù)了。

【修改2】：創(chuàng)建三張表：學(xué)生表、教師表、學(xué)生教師關(guān)系表

【多對多設(shè)計原則】：多對多->三張表->關(guān)系表有兩個外鍵

學(xué)生表學(xué)生編號(pk) 學(xué)生名字 ------------------------------------ 1001 張三 1002 李四 1003 王五教師表教師編號(pk) 教師姓名 -------------------------------------- 001 王老師 002 趙老師學(xué)生教師關(guān)系表 id(pk) 學(xué)生編號(fk) 教師編號(fk)------------------------------------------------------ 1 1001 001 2 1002 002 3 1003 001 4 1001 002

4.5 第三范式

第三范式建立在第二范式的基礎(chǔ)之上

要求所有非主鍵字段必須【直接依賴】主鍵，不產(chǎn)生【傳遞依賴】

學(xué)生編號（PK）學(xué)生姓名班級編號班級名稱 -------------------------------------1001 張三 01 一年一班1002 李四 02 一年二班1003 王五 03 一年三班1004 趙六 03 一年三班

以上表的設(shè)計是描述：班級和學(xué)生的關(guān)系。很顯然是1對多關(guān)系！

滿足第一范式：有主鍵

滿足第二范式：不是復(fù)合主鍵，沒有產(chǎn)生部份依賴。主鍵是單一主鍵

不滿足第三范式：產(chǎn)生了傳遞依賴（班級名稱->班級編號->學(xué)生編號），不符合第三范式的要求，產(chǎn)生了數(shù)據(jù)冗余

【修改】:一對多，兩張表，多的表加外鍵！！！

班級表：一班級編號(pk) 班級名稱 01 一年一班 02 一年二班 03 一年三班學(xué)生表：多學(xué)生編號（PK）學(xué)生姓名班級編號(fk) -------------------------------- 1001 張三 01 1002 李四 02 1003 王五 03 1004 趙六 03

4.6 總結(jié)表的設(shè)計（牢記）

【一對多】：

一對多，兩張表，多的表加外鍵fk！！！！！！！！！！！！

【多對多】

多對多，三張表，關(guān)系表兩個外鍵fk！！！！！！！！！！！！！！！

【一對一】

口訣：一對一，外鍵fk且唯一unique！！！！！！！！！！

在實際的開發(fā)中，可能存在一張表字段太多，太龐大。這個時候要拆分表。
一對一怎么設(shè)計？

沒有拆分表之前：一張表 t_user id login_name login_pwd real_name email address........ ------------------------------------------------------------------------- 1 zhangsan 123 張三 zhangsan@xxx 2 lisi 123 李四 lisi@xxx ...

這種龐大的表建議拆分為兩張

t_login 登錄信息表 id(pk) login_name login_pwd --------------------------------- 1 zhangsan 123 2 lisi 123 t_user 用戶詳細信息表 id(pk) real_name email login_id(fk+unique) ------------------------------------------------------------------------- 100 張三 zhangsan@xxx 1 200 李四 lisi@xxx 2

4.7 總結(jié)

數(shù)據(jù)庫設(shè)計指導(dǎo)：

數(shù)據(jù)庫設(shè)計三范式是理論上的，但是理論和實踐具有偏差，最終的目的都是為了滿足客戶的需求，有的時候就要拿冗余換執(zhí)行速度。

因為在SQL中，表和表之間連接次數(shù)越多，效率越低（笛卡爾積）。

有時存在冗余，卻會減少表的連接次數(shù)，這樣也是合理的，同時數(shù)據(jù)庫表的SQL開發(fā)和維護難度也會降低。

總結(jié)

以上是生活随笔為你收集整理的【内容详细、源码详尽】MySQL极简学习笔记的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。