老杜MySQL視頻學習記錄_筆記

本文是MySQL資料庫筆記，是動力節點教學總監杜老師講述，

視頻地址：https://www.bilibili.com/video/BV1fx411X7BD?p=1

---

一、SQL陳述句分類

DQL：資料查詢陳述句(Data Query Language)

select

DML：資料操作語言(Data Manipulation Language)

對表中的資料進行增刪改

insert delete update

主要操作的是表中的資料

DDL：資料定義語言(Data Definition Language)

create、drop、alter

DDL主要操作的表的結構，不是表中的資料，

TCL：事務控制語言(Transactional Control Language)

事務提交：commit

事務回滾：rollback

DCL：資料控制語言(Data Control Language)

授權：grant

撤銷：revoke

二、常用命令

1.查看MySQL版本：

mysql -V

mysql --version

select version();

2.創建資料庫

create database <資料庫名稱>;
使用：use <資料庫名>;	

3.查看當前使用的資料庫

select database();

4.終止一條陳述句

鍵入\c

5.退出mysql

exit、quit

三、查看表結構

1.查看現有的資料庫

show databases;

2.查看當前預設的資料庫

use <database name>;

3.查看當前使用的資料庫

select database();

4.查看當前庫中的表

show tables;

5.查看其他庫中的表

show tables from <database name>;

6.查看表結構

desc <table name>;

7.查看表的創建陳述句

show create table <table name>;

四、簡單的查詢

1.查詢一個欄位

select <field> from  <table name>;

2.查詢多個欄位

select <field1>,<field2> from <table name>;

3.查詢全部欄位

select * from <table name>;

4.計算員工的年薪

select empno, ename, sal*12 from emp;

5.起別名

select empno as '員工編號', ename as '員工姓名', sal*12 as '年薪' from emp;

注意:字串必須添加單引號 | 雙引號

五、條件查詢(where)

1.等號操作

查詢工資=5000的員工

select empno,ename,sal from emp where sal=5000;

查詢字串必須加上引號

查詢job="manager"的員工

select empno, ename from emp where job="manager";

2.<>符操作

查詢薪水不等于5000的員工

select empno,ename from emp where sal<>5000;

3.between…and…運算子

查詢薪水為1600到3000的員工(第一種方式，采用>=和<=)

select empno,ename from emp where sal>=1600 and sal<=3000;

查詢薪水為1600到3000的員工(第一種方式，采用between … and …) 閉區間

select empno,ename from emp where sal between 1600 and 3000;

4.is null

空和空字串不是一回事，null必須用is來比較

select empno,ename from emp where comm is null;

5.and

作業崗位為MANAGER,薪水大于2500的員工

select * from emp where job="manager" and sal>2500;

6.or

太簡單了，略，

7.運算式的優先級

查詢薪水大于1800，并且部門代碼為20或30的員工

select * from emp where sal>1800 and (deptno=20 or deptno=30);

沒把握盡量用括號

8.in

in表示包含的意思，完全可以采用or來表示，采用in會更簡潔一些

查詢出job為manager或者job為salesman的員工

select * from emp where job in("manager","salesman");

9.not

查詢出薪水不包含1600和薪水不包含3000的員工

select * from emp where sal<>1600 and sal<>3000;
select * from emp where not(sal=1600 or sal=3000);
select * from emp where sal not in(1600,3000);

查出津貼不為null的所有員工

select * from emp where comm is not null;

10.like

Like可以實作模糊查詢，like支持%和下劃線匹配

%匹配任意字符出現的個數

下劃線只匹配一個字符

Like 中的運算式必須放到單引號中|雙引號中

查詢姓名以M開頭的所有員工

select * from emp where ename like "M%";

查詢姓名以N結尾的所有的員工

select * from emp where ename like "%N";

查詢姓名中包含O的所有的員工

select * from emp where ename like "%o%";

查詢姓名中第二個字符為A的所有員工

select * from emp where ename like "_a%";

六、排序資料

1.單一欄位排序

排序采用order by子句，order by后面跟上排序欄位，排序欄位可以放多個，多個采用逗號間隔，order by默認采用升序，如果存在where子句那么order by必須放到where陳述句的后面，

按照薪水由小到大排序(系統默認由小到大)

select * from emp order by sal;

取得job為MANAGER的員工，按照薪水由小到大排序(系統默認由小到大)

select * from emp where job="manager"  order by sal;

按照多個欄位排序，如：首先按照job排序，再按照sal排序

select * from emp order by job,sal;

2.手動指定順序排序

手動指定按照薪水由小到大排序

select * from  emp order by sal asc;

手動指定按照薪水由大到小排序

select * from emp order by sal desc;

3.多個欄位排序

按照job和薪水倒序

select * from emp order by job desc,sal desc;

4.使用欄位的位置來排序

按照薪水升序 不建議使用位置

select * from emp order by 6;

七、資料處理函式/單行處理函式

函式名	作用
count	求和
avg	取平均數
max	取最大的數
min	取最小的數
lower	轉換小寫
upper	轉換大寫
substr	取子串(substr(被截取的字串，起始下標，截取的長度))
length	取長度
trim	去空格
str_to_date	將字串轉換成日期
data_format	格式化日期
format	設定千分位 format(數字,‘格式’)
round	四舍五入
rand()	生成亂數
ifnull	可以將null轉換成一個具體的值

注意：分組函式自動忽略空值，不需要手動的加where條件排除空值，

select count(*) from emp where xxx; 符合條件的所有記錄總數，

select count(comm) from emp; comm這個欄位中不為空的元素總數，

注意：分組函式不能直接使用在where關鍵字后面，

mysql> select ename,sal from emp where sal > avg(sal);

ERROR 1111 (HY000): Invalid use of group function

1.count

統計該欄位下所有不為NULL的元素的總數

取得所有的員工數

select count(*) from emp;

取得津貼不為null員工數

select count(comm) from emp;

取得作業崗位的個數

select count(distinct job) from emp;

2.sum

sum可以取得某一個列的和，null會被忽略

取得薪水的合計

select sum(sal) from emp;

取得津貼的合計

select sum(comm) from emp;

取得薪水的合計(sal+comm)

select sum(sal+IFNULL(comm,0))) from emp;

3.substr

找出員工名字第一個字母是A的員工資訊

select ename from emp where substr(ename,1,1) ="A";

4.trim去前后空格

select ename from emp where ename=trim("  king");

5.round四舍五入

select round(12345.678,1) from emp;

6.ifnull

select ename,(sal+ifnull(comm,0))*12 from emp;

7.case…when…then…when…then…else…end

當員工的作業崗位是MANAGER的時候，工資上調10%,當作業崗位是SALESMAN的時候，工資上調50%，其他正常， select不會修改原來的資料

select ename,job,(case job when "manager" then sal*1.1 when "salesman" then sal*1.5 else sal end) as newsal from emp;

八、分組函式

必須先進行分組才能使用，否則就是整張表，

分組查詢主要涉及到兩個子句，分別是：group by 和 having

1.group by

取得每個作業崗位的工資合計，要求顯示崗位名稱和工資合計?

select job,sum(sal) from emp group by job;

找出每個部門最高薪資大于3000的？

select deptno,max(sal) from emp where sal>3000 group by deptno;

2.having

如果想對分組資料再進行過濾需要使用 having 子句

取得每個崗位的平均工資大于 2000 ？

select job,avg(sal) from emp group by job having avg(sal)>2000;

3.distinct

去除重復記錄，如果出現在所有欄位的前方表示所有欄位聯合起來去除重復記錄？

select distinct job from emp;

統計作業崗位的數量？

select count(distinct job) from emp;

大總結（單表查詢）

執行順序？

1. from 從某張表中查詢資料
2. where 先經過where條件篩選出有價值的資料
3. group by 對這些有價值的資料進行分組
4. having 分組之后可以使用having進行篩選
5. select select查詢出來
6. order by 最后排序輸出

找出每個崗位的平均薪資，要求顯示薪資大于1500的并保留1位小數，除MANAGER崗位之外，要求按照平均薪資降序排

select job,round(avg(sal),1) avgsal from emp where job<>"manager" group by job having avg(sal)>1500 order by avgsal desc;

九、連接查詢

連接查詢：也可以叫跨表查詢，需要關聯多個表進行查詢

根據表連接的方式：

? 內連接：

? 等值連接

? 非等值連接

? 自連接

? 外連接：

? 左外連接(左連接)

? 右外連接(右連接)

? 全連接(略)

如果多表查詢沒有加條件，就會出現出笛卡爾積現象

笛卡爾乘積是指在數學中，兩個集合X和Y的笛卡爾積（Cartesian product），又稱直積，表示為X × Y，第一個物件是X的成員而第二個物件是Y的所有可能有序對的其中一個成員 ，

1.內連接之等值連接

查詢每個員工所在的部門名稱，顯示員工名和部門名？

sql92語法
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

sql99語法
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

sql92的缺點：結構不清晰，表的連接條件，和后期進一步篩選條件都放到了where后面

sql99的優點：表連接的條件是獨立的，連接之后如果還需要進一步的篩選，再往后添加where條件

2.內連接之非等值連接

找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級？

select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

3.內連接之自連接

查詢員工的上級領導，要求顯示員工名和對應的領導名？

select e.ename '員工名',m.ename '領導名' from emp e join emp m on m.mgr=e.empno; 

4.外連接(右外連接)

在外連接當中兩張表產生了主次關系

right代表什么: 表示將右邊的這張表看成主表，主要是為了將右邊這張表的資料全查出來，捎帶著關聯左邊的表，

查詢所有部門對應的員工姓名？

select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;

5.外連接(左外連接)

帶有left的是左外連接

查詢每個員工的上級領導，要求顯示所有員工的名字和領導名？

select a.ename '員工',b.ename '領導' from emp a left join emp b on a.mgr=b.empno;

6.三張表、四張表連接

語法：

select … from a

? join b on a和b的連接條件

? left join c on a 和c的連接條件

? join d on a和d的連接條件;

找出每個員工的部門名稱以及工資等級，要求顯示員工名、部門名、薪資、薪資等級、領導名字？

select e.ename,d.dname,e.sal,s.grade,l.ename from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between losal and hisal left join emp l on e.mgr=l.empno;

十、子查詢

子查詢就是嵌套的 select 陳述句，可以理解為子查詢是一張表

1.子查詢出現在哪里？

select

? …(select)

from

? …(select)

where

? …(select)

2.where子句中的子查詢

找出比最低工資高的員工姓名和工資？

select ename,sal from emp where sal > (select min(sal) from emp);

3.from子句中的子查詢

注意：from后面的子查詢，可以將子查詢的結果當成一張臨時表，

找出每個崗位的平均薪資的薪資等級

select t.job,s.grade,t.a from (select job,avg(sal) a from emp group by job) t join salgrade s  on t.a between losal and hisal; 

4.select后面出現的子查詢

找出員工的部門名，要求顯示員工名，部門名？

select e.ename,(select d.dname from dept d where d.deptno=e.deptno ) as dname from emp e;

十一、union、limit

union的效率更高一些，對于表連接來說，每連接一次新表，則匹配的次數滿足笛卡爾積，成倍的翻，但是union可以減少匹配的次數，在減少匹配次數的情況下，還可以完成兩個結果集的拼接，

1.union

查詢作業崗位是manager或者salesman

select ename,job from emp where job="manager" or job="salesman";
select ename,job from emp where job in("manager","salesman");
select ename,job from emp where job="manager" union select ename,job from emp where job="salesman";

2.limit

limit是將查詢結果集的一部分取出來

完整用法：limit startIndex,length

按照薪資降序，取出前5條資料

select ename,sal from emp order by sal desc limit 0,5；

3.分頁

每頁顯示3條記錄

? 第1頁： limit 0,3 [0 1 2]

? 第2頁： limit 3,3 [3 4 5]

? 第3頁： limit 6,3 [6 7 8]

每頁顯示pasgeSize條記錄

第pageNo頁： limit (pageNo-1)*pageSize,pagesize;

十二、表

1.表的創建(DDL)

語法格式

create table tableName(
 columnName dataType(length),
 ………………
 columnName dataType(length)
);
set character_set_results='gbk';

show variables like '%char%';

2.關于MySQL中的資料型別

部分常用型別：

型別	描述
char(長度)	定長字串，存盤空間大小固定，適合作為主鍵或外鍵
varchar(長度)	變長字串，存盤空間等于實際資料空間
double(有效數字位數,小數位)	數值型
float(有效數字位數,小數位)	數值型
int(長度)	整數
bigint(長度)	長整型
Date	日期型
BLOB	Binary Large Object（二進制大物件）
CLOB	Character Large Object (字符大物件)

3.創建一個學生表

學號、姓名、年齡、性別、郵箱地址

create table t_student(
 no int,
 name varchar(32),
 sex char(1) default('m'),
 age int(3),
 email varchar(255)
);

4.插入資料insert (DML)

語法格式：

? inser into 表名(欄位名1,欄位名2,欄位名3) values(值1,值2,值3);

注意：

? 欄位名和值要一一對應

? insert陳述句但凡執行成功，那么必然會多一條記錄

? 欄位名省略的話，就相當于都寫上了~ 所以值也要都寫上！

insert into t_student(no,name,sex,age,email) values(1,'jack','m',21,'test@163.com');
insert into t_student values(2,'tom','m',22,'test1@163.com');

一次插入多條記錄：
insert into t_student values(2,'tom','m',22,'test1@163.com'),(3,'zs','m','9','test2@163.com');

將查詢結果插入到一張表當中

create table dept_bak as select * from dept;
查詢結果必須符合表的結構
insert into dept_bak select * from dept;

5.insert插入日期

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

str_to_date: 將字串varchar型別轉換為date型別,具體格式 str_to_date (字串，匹配格式)

date_format:將date型別轉換成具有一定格式的字串型別

create table t_user(
	id int,
    name varchar(32),
    birth date
);

插入資料

insert into t_user(id,name,birth) values(1,'jack',str_to_date('12-06-2021','%d-%m-%Y'));
如果輸入的字串是YYYY-mm-dd,還可以自動轉換
insert into t_user(id,name,birth) values(2,'tom','2021-6-12');

查詢的時候還可以以某個特定的日期格式展示

? date_format

select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;

6.date和datetime兩個型別的區別

date是短日期：只包括年月資訊

datetime是長日期：包括年月日時分秒資訊

drop table if exists t_user;
create table t_user(
	id int,
    name varchar(32),
    birth date,
    create_time datetime
);

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

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

now()函式可以獲取當前系統時間，是datetime型別的格式

insert into t_user(id,name,birth,create_time) values(1,'jack','2021-6-12','2021-6-12 19:50:22');
insert into t_user(id,name,birth,create_time) values(2,'tom','2021-6-12',now());

7.修改update陳述句(DML)

語法格式：

? update 表名 set 欄位名1=值1,欄位名2=值2,欄位名3=值3 where 條件;

update t_user set name="zhangsan",birth="2020-01-01" where id=1;

注意：如果不加where條件默認更新表中的所有資料，

8.洗掉delete陳述句(DML)

語法格式：

? delete from 表名 where 條件

delete from t_user where id=2;

注意：如果不加where條件默認洗掉表中的所有資料，

9.表快速復制

原理：

? 將一個查詢結果當做一張表創建

? 這個可以完成表的快速復制

? 表創建出來，同時表中的資料也存在了

create table t_user_bak as select * from t_user;

10.洗掉表

drop table t_student;  //如果存在就洗掉
drop table if exists t_student;  //如果不存在就不會報錯

11.快速洗掉表中的資料

不支持回滾

truncate table 表名;

12.對表結構的增刪改查

采用 alter table 來增加/洗掉/修改表結構，不影響表中的資料

一般不用，實際開發中，需求一旦確定之后，表結構確定之后，很少進行表的修改，因為開發進行中的時候，修改表結構，成本比較高，這里簡單了解一下，如果修改結構可以用可視化工具，

1.添加欄位

如需求發生改變，需要向t_user中加入聯系電話欄位，欄位名稱為：contact_tel型別為varchar(40)

alter table t_user add contact_tel varchar(40);

2.修改欄位

如：name 無法滿足需求，長度需要更改為 100

alter table t_user modify name varchar(100);

如 sex 欄位名稱感覺不好，想用 gender 那么就需要更愛列的名稱

alter table t_user change sex gender char(2) not null;

3.洗掉欄位

如：洗掉聯系電話欄位

alter table t_user drop contact_tel;

十三、約束

約束對應的單詞：constraint

在創建表的時候，我們可以給表中的欄位加上一些約束，來保證表中的資料完整性、有效性

約束的作用就是為了保證：表中的資料有效！！

1.常見的約束？

非空約束：not null

唯一性約束：unique

主鍵約束：primary key

外鍵約束：foreign key

檢查約束：check(mysql不支持，oracle支持)

1.1非空約束：not null

非空約束not null約束的欄位不能為NULL，

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(255) not null
);
insert into t_user(id,name) values(1,'zhangsan');
insert into t_user(id,name) values(2,'lisi');
---------------------------------------------------
insert into t_user(id,name) values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

1.2唯一性約束：unique

唯一性約束的unique約束的欄位不能重復，但是可以為null

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(255) unique
);
insert into t_user(id,name) values(1,'zs');
insert into t_user(id,name) values(2,'ls');
insert into t_user(id) values(3);
insert into t_user(id) values(4);
--------------------------------------------------------
insert into t_user(id,name) values(3,'zs');
ERROR 1062 (23000): Duplicate entry 'zs' for key 'name'

name和email聯合起來具有唯一性

? 如果約束沒有添加到欄位后面，成為表級約束

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(255),
 	email varchar(255),
    unique(name,email)
);
insert into t_user(id,name,email) values(1,'zhangsasn','123@qq.com');
insert into t_user(id,name,email) values(2,'zhangsasn','1234@qq.com');
-------------------------------------------------------------------
insert into t_user(id,name,email) values(2,'zhangsasn','1234@qq.com');
ERROR 1062 (23000): Duplicate entry 'zhangsasn-1234@qq.com' for key 'name'

在mysql中，如果一個欄位同時被not null和unique約束，自動變成主鍵欄位，

1.3主鍵約束：primary key

相關術語：主鍵約束、主鍵欄位、主鍵值

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

任何一張表都應該有主鍵，建議使用int、bigint、char等型別

主鍵值不能是null同時也不能重復

drop table if exists t_user;
create table t_user(
	id int primary key,
	name varchar(255) 
);
insert into t_user(id,name) values(1,'zs');
insert into t_user(id,name) values(2,'ls');
---------------------------------------------------------
insert into t_user(id,name) values(1,'wz');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

可以倆主鍵聯合起來做表級約束，這樣的主鍵叫復合主鍵，比較復雜，建議單一主鍵，

主鍵除了單一主鍵和復合主鍵之外，還可以這樣分類：

? 自然主鍵：主鍵值是一個自然數，和業務沒關系

? 單一主鍵：主鍵值和業務緊密相連，例如銀行卡號，

主鍵值可以采用auto_increment自動維護

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment,
	name varchar(255) 
);
insert into t_user(name) values('zs');
insert into t_user(name) values('ls');
select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | ls   |
+----+------+

1.4外鍵約束：foreign key

相關術語：外鍵約束、外鍵欄位、外鍵值

子表中的一個欄位參考父表中的一個欄位，如果洗掉表，應該先刪子表，

這里學生表中的cno欄位參考班級表中的classno欄位

drop table if exists t_student;
drop table if exists t_class;
create table t_class(
	classno int primary key,
    classname varchar(255)
);
create table t_student(
	id int primary key auto_increment,
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)
);
insert into t_class(classno,classname) values(100,'某高級中學1班');
insert into t_class(classno,classname) values(101,'某高級中學2班');
insert into t_student(name,cno) values('zhhangsan',100);
insert into t_student(name,cno) values('lisi',100);
insert into t_student(name,cno) values('wangwu',100);
insert into t_student(name,cno) values('tom',101);
insert into t_student(name,cno) values('jack',101);
insert into t_student(name,cno) values('jreey',101);
------------------------------------------------------------
insert into t_student(name,cno) values('wangwu',109);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`))

字表中的外鍵參考父表中的某個欄位，被參考的欄位不一定是主鍵，至少具有unique約束(唯一性)，

外鍵值可以為null，

2.增加/洗掉/修改表約束

2.1洗掉約束

洗掉外鍵約束：alter table 表名 drop foreign key 外鍵 (區分大小寫);
洗掉主鍵約束：alter table 表名 drop primary key;
洗掉約束：alter table 表名 drop key 約束名稱;

2.2添加約束

添加外鍵約束：alter table 從表 add constraint 約束名稱 foreign key 從表(外鍵欄位) references 主表(主鍵欄位);
添加主鍵約束：alter table 表 add constraint 約束名稱 primary key 表(主鍵欄位);
添加唯一性約束：alter table 表 add constraint 約束名稱 unique 表(欄位)

2.3修改約束，其實就是修改欄位

alter table t_student modify student_name varchar(30) unique;

十四、存盤引擎(了解)

存盤引擎是MySQL中特有的術語，

存盤引擎是一個表存盤/組織資料的方式，

不同的存盤引擎，表存盤資料的方式不同，

資料庫中的各表均被（在創建表時）指定的存盤引擎來處理，

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

在建表的時候可以在最后的")"的右邊使用：

? engine來指定存盤引擎 ，默認是InnoDB

? charset來指定這張表的字符編碼方式，默認是：utf-8

為了解當前服務器中有哪些存盤引擎可用，可使用 SHOW ENGINES 陳述句 ，

1.常用存盤引擎

MyISAM

它管理的表具有以下特征：

? – 使用三個檔案表示每個表：

? ? 格式檔案 — 存盤表結構的定義（mytable.frm）

? ? 資料檔案 — 存盤表行的內容（mytable.MYD）

? ? 索引檔案 — 存盤表上索引（mytable.MYI）

? – 靈活的 AUTO_INCREMENT 欄位處理

? – 可被轉換為壓縮、只讀表來節省空間

InnoDB

它管理的表具有下列主要特征：

? – 每個 InnoDB 表在資料庫目錄中以.frm 格式檔案表示

? – InnoDB 表空間 tablespace 被用于存盤表的內容

? – 提供一組用來記錄事務性活動的日志檔案

? – 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滾)支持事務處理

? – 提供全 ACID 兼容

? – 在 MySQL 服務器崩潰后提供自動恢復

? – 多版本（MVCC）和行級鎖定

? – 支持外鍵及參考的完整性，包括級聯洗掉和更新

MEMORY

使用 MEMORY 存盤引擎的表，其資料存盤在記憶體中，且行的長度固定，這兩個特點使得 MEMORY 存盤引擎非 常快，

? MEMORY 存盤引擎管理的表具有下列特征：

? – 在資料庫目錄內，每個表均以.frm 格式的檔案表示，

? – 表資料及索引被存盤在記憶體中，

? – 表級鎖機制，

? – 不能包含 TEXT 或 BLOB 欄位，

? MEMORY 存盤引擎以前被稱為 HEAP 引擎

2.選擇合適的存盤引擎

? MyISAM 表最適合于大量的資料讀而少量資料更新的混合操作，MyISAM 表的另一種適用情形是使用壓縮的只 讀表，

? 如果查詢中包含較多的資料更新操作，應使用 InnoDB，其行級鎖機制和多版本的支持為資料讀取和更新的混合 操作提供了良好的并發機制，

? 可使用 MEMORY 存盤引擎來存盤非永久需要的資料，或者是能夠從基于磁盤的表中重新生成的資料，

十五、事務(重點)

事務就是一個完整的業務邏輯，是一個最小的作業單元，

假如轉賬：從A賬戶向b賬戶轉10000.

? 將A賬戶的錢減去10000(update陳述句)

? 將B賬戶的錢加上10000(update陳述句)

? 這就是一個完整的業務邏輯，

以上就是最小的業務單元，不可再分，要么同時成功，要么同時失敗，

只有DML陳述句才有事務：insert update delete，一旦涉及增刪改查就要考慮安全問題，
事務是怎么做到多條DML陳述句同時成功或同時失敗的呢？

? InnoDB存盤引擎： 提供一組用來記錄事務性活動的日志檔案

事務開啟：

? insert

? insert

? delete

? update

? insert

事務結束！

在事務的執行程序中,每一條DML的操作都會記錄到“事務性活動的日志檔案”，

我們可以提交事務，可以回滾事務，

提交事務：清空事務性活動的日志檔案，將資料全部徹底持久化到資料庫表中，標志著事務全部成功結束，

回滾事務：將之前所有的DML操作全部撤銷，并清空事務性活動的日志檔案，標志著事務全部失敗的結束，

2.提交/回滾事務

提交事務：commit; 陳述句 （MySQL默認自動提交事務）

回滾事務：rollback; 陳述句 （回滾永遠只能回滾到上一次的提交點）

關閉自動提交事務

start transaction;

3.事務四個特性

? A：原子性

? 說明事務是最小的作業單元，不可再分，

? C：一致性

? 所有事物要求，在同一個事務中，所有操作必須同時成功，或者同時失敗，以保證資料的一致性，

? I：隔離性

? A事務和B事務之間具有一定的隔離

? D：持久性

? 失誤最終結束的一個保障，事務提交，就相當于沒有保存到硬碟上的資料保存到硬碟上，

隔離性

當多個客戶端并發地訪問同一個表時，可能出現下面的一致性問題：

? – 臟讀取（Dirty Read） 一個事務開始讀取了某行資料，但是另外一個事務已經更新了此資料但沒有能夠及時提交，這就出現了臟讀取，

? – 不可重復讀（Non-repeatable Read） 在同一個事務中，同一個讀操作對同一個資料的前后兩次讀取產生了不同的結果，這就是不可重復讀，

? –幻像讀（Phantom Read） 幻像讀是指在同一個事務中以前沒有的行，由于其他事務的提交而出現的新行，

InnoDB 實作了四個隔離級別，用以控制事務所做的修改，并將修改通告至其它并發的事務：

? – 讀未提交（READ UMCOMMITTED） 允許一個事務可以看到其他事務未提交的修改，(最低的隔離級別)

 – 讀已提交（READ COMMITTED） 允許一個事務只能看到其他事務已經提交的修改，未提交的修改是不可見的， 

? – 可重復讀（REPEATABLE READ） 確保如果在一個事務中執行兩次相同的 SELECT 陳述句，都能得到相同的結果，不管其他事務是否提交這些修改，（銀 行總賬） 該隔離級別為 InnoDB 的預設設定，

? – 串行化（SERIALIZABLE） 【序列化】 將一個事務與其他事務完全地隔離， (最高的隔離級別)

十六、索引

十七、視圖

視圖可以看成是一張新表，

視圖修改的是表的原資料，可以直接操作表，

視圖的創建

create view 視圖名 as DQL陳述句;

可以對視圖進行CRUD，修改的是表的資料，

十八、DBA命令

1.創建用戶

CREATE USER username IDENTIFIED BY 'password';

2.授權

命令詳解
mysql> grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
1) dbname=*表示所有資料庫
2) tbname=*表示所有表
3) login ip=%表示任何 ip
4) password 為空，表示不需要密碼即可登錄
5) with grant option; 表示該用戶還可以授權給其他用戶
  細粒度授權
首先以 root 用戶進入 mysql，然后鍵入命令：grantselect,insert,update,delete on *.* to p361 @localhost Identified by "123";
如果希望該用戶能夠在任何機器上登陸 mysql，則將 localhost 改為 "%"，
  粗粒度授權
我們測驗用戶一般使用該命令授權，
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123";
注意:用以上命令授權的用戶不能給其它用戶授權,如果想讓該用戶可以授權,用以下命令:
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123" WITH GRANT OPTION;
privileges 包括：
1) alter：修改資料庫的表
2) create：創建新的資料庫或表
3) delete：洗掉表資料
4) drop：洗掉資料庫/表
5) index：創建/洗掉索引
6) insert：添加表資料
7) select：查詢表資料
8) update：更新表資料
9) all：允許任何操作
10) usage：只允許登錄

3.回收權限

命令詳解
revoke privileges on dbname[.tbname] from username;
revoke all privileges on *.* from p361;
use mysql
select * from user
進入 mysql 庫中
修改密碼;
update user set password = password('qwe') where user = 'p646';
重繪權限;
flush privileges

4.匯出

匯出整個資料庫

在 windows 的 dos 命令視窗中執行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123

匯出指定庫下的指定表

在 windows 的 dos 命令視窗中執行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123

5.匯入

登錄 MYSQL 資料庫管理系統之后執行：source D:\ bjpowernode.sql;

十九、資料庫設計的三范式

第一：要求任何一張表必須有主鍵，每一個欄位原子性不可再分，

? 口訣：一對一，外鍵唯一，

第二：建立在第一范式的基礎之上，要求所有非主鍵欄位完全依賴主鍵，不要產生部分依賴，

? 口訣：多對多，三張表，關系表，兩個外鍵，

第三：建立在第二范式的基礎之上，要求所有非主鍵欄位直接依賴主鍵，不要產生傳遞依賴，

? 口訣：一對多，兩張表，多的表加外鍵，