MySQL學習筆記-day03-有解無憂

1、約束

1.1、唯一性約束(unique)

唯一性約束修飾的欄位具有唯一性，不能重復，但可以為NULL，

案例：給某一列添加`unique`

drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255) unique
);
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
# 錯誤：ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);

案例：給兩個列或者多個列添加`unique`

表集約束--聯合起來具有唯一性

drop table if exists t_user;
create table t_user(
    id int,
    usercode varchar(255),
    username varchar(255),
    unique(usercode,username) //多個欄位聯合添加約束
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;
	+------+----------+----------+
	| id   | usercode | username |
	+------+----------+----------+
	|    1 | 111      | zs       |
	|    2 | 111      | ls       |
	|    3 | 222      | zs       |
	+------+----------+----------+
insert into t_user values(4,'111','zs');
# 錯誤：ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'

列集約束--單個列具有唯一性

drop table if exists t_user;
create table t_user(
    id int,
    usercode varchar(255) unique,
    username varchar(255) unique
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
# 錯誤：ERROR 1062 (23000): Duplicate entry '111' for key 'usercode'
select * from t_user;
	 +------+----------+----------+
	 | id   | usercode | username |
	 +------+----------+----------+
	 |    1 | 111      | zs       |
	 +------+----------+----------+
# 注意：not null只有列級約束，沒有表集約束，

1.2、主鍵約束

怎么給一張表添加主鍵約束？

drop table if exists t_user;
create table t_user(
    id int primary key, # 列級約束
    username varchar(255),
    email varchar(255)
);
insert into t_user(id,username,email) values(2,'ls','[email protected]');
insert into t_user(id,username,email) values(3,'ww','[email protected]');
select * from t_user;
    +----+----------+------------+
	| id | username | email      |
	+----+----------+------------+
	|  1 | zs       | [email protected] |
	|  2 | ls       | [email protected] |
	|  3 | ww       | [email protected] |
	+----+----------+------------+
# 不能重復
insert into t_user(id,username,email) values(1,'jack','[email protected]');
# 錯誤：ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

# 不能為空
insert into t_user(username,email) values('jack','[email protected]');
# 錯誤：ERROR 1364 (HY000): Field 'id' doesn't have a default value

根據以上的測驗得出：id是主鍵，因為添加了主鍵約束，主鍵欄位中的資料不能為NULL，也不能重復，

主鍵的特點：不能為NULL，也不能重復，

主鍵相關的術語

主鍵約束：primary key

主鍵欄位：id欄位添加primary key之后，id叫做主鍵欄位，

主鍵值：id欄位中的每一個值都是主鍵值，

主鍵有什么用？

表的設計三范式中有要求，第一范式就要求任何一張表都應該有主鍵，
主鍵的作用：主鍵值就是這行記錄在這張表當中的唯一標識，(就像一個人的身份證)

主鍵的分類？

根據主鍵欄位的欄位數量來劃分：

單一主鍵(建議的，常用的，)

復合主鍵(多個欄位聯合起來添加一個主鍵約束，不建議使用)

根據主鍵性質來劃分：

自然主鍵：主鍵值最好就是一個和業務沒有任何關系的自然數，(這種方法是推薦的，)
業務主鍵：主鍵值和系統的業務掛鉤，例如：拿著銀行卡的卡號做主鍵，拿著身份證號碼作為主鍵，(不推薦使用)
最好不要拿著和業務掛鉤的欄位作為主鍵，因為以后的業務一旦發生改變的時候，主鍵值可能也需要隨著發生變化，但有的時候沒有辦法變化，因為變化可能導致主鍵值重復，

一張表的主鍵約束只能有一個，

使用表集約束定義主鍵

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

mysql主鍵值自增 auto_increment(非常重要)

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,	//id欄位自動維護一個自增數字，從1開始，以1遞增，
    username varchar(255)
);
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');
select * from t_user;
    +----+----------+
	| id | username |
	+----+----------+
	|  1 | a        |
	|  2 | b        |
	|  3 | c        |
	|  4 | d        |
	|  5 | e        |
	|  6 | f        |
	+----+----------+

提示：Oracle當中也提供了一個自增機制，叫做：序列(sequence)物件，

1.3、外鍵約束

關于外鍵約束的相關術語

外鍵約束：foreign key

外鍵欄位：添加有外鍵約束的欄位

外鍵值：外鍵欄位中的每一個值

業務背景：請設計資料庫表，用來維護學生和班級的資訊

第一種方案：一張表存盤所有資料

缺點：冗余，【不推薦】學生屬于同一班級，則他們的班級編號和班級名稱都是相同的，要修改一個班，就需要修改很多行，

第二種方案：兩種表(學生表和班級表) 每一種資料，使用一個表來存盤，

t_class 班級表————父表

cno(pk)		cname
-----------------------------
101			高三1班
102			高三2班
....

t_student 學生表————子表

sno(pk)		sname		classno(fk：該欄位添加外鍵約束)
------------------------------------------------------------------
1			zhangsan	101
2			lisi		101
3			wangwu		101
4			zhaoliu		102
5			tangqi		102

將以上表的建表陳述句寫出：
t_student 學生表中的classno欄位參考t_class 班級表中的cno欄位，此時t_student表叫做子表，t_class表叫做父表，

順序要求：
洗掉資料的時候，先洗掉子表，再洗掉父表，
添加資料的時候，先添加父表，再添加子表，
創建表的時候，先創建父表，再創建子表，
洗掉表的時候，先洗掉子表，再洗掉父表，

drop table if exists t_student;
drop table if exists t_class;
create table t_class(
    cno int,
    cname varchar(255),
    primary key(cno)
);
create table t_student(
    sno int,
    sname varchar(255),
    classno int,
    primary key(sno),
    foreign key(classno) references t_class(cno)
);

insert into t_class values(101,'xxx');
insert into t_class values(102,'yyy');

insert into t_student values(1,'zs',101);
insert into t_student values(2,'ls',101);
insert into t_student values(3,'wu',102);
insert into t_student values(4,'zl',102);
insert into t_student values(5,'tq',102);

select * from t_class;
select * from t_student;

insert into t_student values(6,'zh',103);
# 錯誤：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_student`,CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

外鍵值可以為NULL嗎？

外鍵值可以為NULL，

insert into t_student(sno,sname) values(6,'sl');
select * from t_student;
	+------+-------+---------+
	| sno  | sname | classno |
	+------+-------+---------+
	|    1 | zs    |     101 |
	|    2 | ls    |     101 |
	|    3 | wu    |     102 |
	|    4 | zl    |     102 |
	|    5 | tq    |     102 |
	|    6 | sl    |    NULL |
	+------+-------+---------+

外鍵欄位參考其他表的某個欄位的時候，被參考的欄位必須是主鍵嗎？

不是，不一定是主鍵，但必須有唯一性，

2、存盤引擎(了解) 描述表的存盤方式

mysql默認使用的存盤引擎是InnoDB方式，默認采用的字符集是UTF-8，

2.1、存盤引擎的使用

資料庫中的各表均被(在創建表時)指定的存盤引擎來處理，
服務器可用的引擎依賴以下因素：
- MySQL的版本
- 服務器在開發時如何被配置
- 啟動選項：為了解當前服務器中有哪些存盤引擎可用，可使用SHOW ENGINES陳述句;(末尾加“\G”可以讓結果顯示更加易讀)

2.2、什么叫存盤引擎？

存盤引擎這個名字只有在mysql中存在，(Oracle中有對應的機制，但不叫存盤引擎，Oracle中沒有特殊的名字，就是“表的存盤方式”，)

mysql支持很多存盤引擎，每一種存盤引擎都對應了一種不同的存盤方式，

2.3、查看當前mysql支持的存盤引擎？

show engines \G;

2.4、常用的存盤引擎？

MyISAM存盤引擎是MySQL最常用的引擎，但是這種引擎不是默認的，

Engine: MyISAM
	     Support: YES
	     Comment: MyISAM storage engine
	Transactions: NO	// 不支持事務
		  XA: NO
	  Savepoints: NO

MyISAM這種存盤引擎不支持事務，

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

使用三個檔案表示每個表：
- 格式檔案 —— 存盤表結構的定義(mytable.frm)
- 資料檔案 —— 存盤表行的內容(mytable.MYD)
- 索引檔案 —— 存盤表上索引(mytable.MYI)
靈活的ATUO_INCREMENT欄位處理，
可被轉換成壓縮、只讀表來節省空間，

優點：可被壓縮，節省存盤空間，并且可以轉換成只讀表，提高檢索效率，

缺點：不支持事務，

InnoDB存盤引擎是MySQL的預設引擎，

Engine: InnoDB
	     Support: DEFAULT
	     Comment: Supports transactions, row-level locking, and foreign keys
	Transactions: YES
		  XA: YES   //支持事務
	  Savepoints: YES

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

每個InnoDB表在資料庫目錄中以.frm格式檔案表示
InnoDB表空間tablespace被用于存盤表的內容(tablespace只是一個邏輯概念)
提供一組用來記錄事務性活動的日志檔案
用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滾)支持事務處理
提供全ACID兼容，ACID事務具有的四個特征
在MySQL服務器崩潰后提供自動恢復機制
多版本(MVCC)和行級鎖定
支持外鍵及參考的完整性，包括級聯洗掉和更新

表的結構存盤在xxx.frm檔案中

資料存盤在tablespace這樣的表空間中

優點：支持事務、行級鎖、外鍵等，這種存盤引擎資料的安全得到保障，

缺點：(資料存盤在tablespace這樣的表空間中)無法被壓縮，無法轉換成只讀，

MEMORY存盤引擎

Engine: MEMORY 
	     Support: YES
	     Comment: Hash based, stored in memory, useful for temporary tables
	Transactions: NO
		  XA: NO //不支持事務
	  Savepoints: NO

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

在資料庫目錄內，每個表均以.frm格式的檔案表示，
表資料及索引被存盤在記憶體當中，
表級鎖機制，
不能包含TEXT或者BLOB欄位，

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

優點：查詢速度最快，

缺點：不支持事務，資料容易丟失，因為所有資料和索引都是存盤在記憶體當中的，斷電就沒，以前叫做HEPA引擎，

2.5、每一個存盤引擎都有自己的優缺點，需要在合適的時機選擇合適的存盤引擎，

MyISAM表最合適于大量的資料讀而少量資料更新的混合操作，MyISAM表的另一種適用場景是使用壓縮的只讀表，
如果查詢中包含較多的資料更新，應使用InnoDB，其行級鎖機制和多版本的支持為資料讀取和更新的混合操作提供了良好的并發機制，
可使用MEMORY存盤引擎來存盤非永久需要的資料，或者是能夠從基于磁盤的表中重新生成的資料，

3、事務(Transaction)

3.1、什么是事務？

一個事務是一個完整的業務邏輯單元，不可再分，

比如：銀行賬戶轉賬，從A賬戶向B賬戶轉賬10000，需要執行兩條update陳述句:

update t_act set balance = blance - 10000 where actno = 'act-001';
update t_act set balance = blance + 10000 where actno = 'act-002';

以上兩條DML陳述句必須同時成功，或者同時失敗，不允許出現一條成功，一條失敗，

要想保證以上兩條DML陳述句同時成功，或者同時失敗，那么就需要使用資料庫的“事務機制”，

3.2、和事務相關的陳述句只有：DML陳述句，(insert delete update)

為什么？

因為它們這三個陳述句都是和資料庫表當中的“資料”相關的，

事務的存在是為了保證資料的完整性、安全性，

3.3、假設所有的業務都能使用1條DML陳述句搞定，還需要事務機制嗎？

所有的業務都能使用1條DML陳述句搞定就不需要事務了，但實際情況不是這樣的，通常一個“事兒(事務【業務】)”需要多條DML陳述句共同聯合完成，

3.4、事務的四大特性

事務包括四大特性：ACID

A:原子性：事務是最小的作業單元，不可再分，
C:一致性：事務必須保證多條DML陳述句同時成功或者同時失敗，
I:隔離性：事務A和事務B之間具有隔離，
D:持久性：持久性說的是最終資料必須持久化到硬碟檔案中，事務才算成功的結束，

3.5、關于事務之間的隔離性(4個級別) 一般不會使用 Serializable 和Read uncommitted 這兩種隔離級別

隔離性是指，多個用戶的并發事務訪問同一個資料庫時，一個用戶的事務不應該被其他用戶的事務干擾，多個并發事務之間要相互隔離，

事務隔離性存在隔離級別，理論上隔離級別包括4個：

第一級別：讀未提交(read uncommitted)

對方事務還沒有提交，我們當前事務可以讀取到對方未提交的資料，

這種隔離級別解決了臟讀現象，

第二級別：讀已提交(read committed)

對方事務提交之后的資料我方可以讀取到，

讀已提交存在的問題是：不可重復讀，

第三級別：可重復讀(repeatable read)

這種隔離級別解決了：不可重復讀問題，

可重復讀存在的問題：讀取到的資料是幻象，

第四級別：序列化/串行化

解決了所有問題，

效率低，需要事務排隊，一致性最好的，性能最差的，

Oracle資料庫默認的隔離級別是：讀已提交，

mysql資料庫默認的隔離級別是：可重復讀，

3.6、演示事務

mysql事務默認情況下是自動提交的，(什么是自動提交？只要執行任意一條DML陳述句，則提交一次，)

怎么關閉自動提交？ ———— start transaction;

準備表

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,
    username varchar(255)
);

演示

insert into t_user(username) values('zs');

select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	+----+----------+
	
rollback;

select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	+----+----------+

演示：使用start transaction;關閉自動提交機制，

start transaction;

insert into t_user(username) values('lisi');

select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	|  2 | lisi     |
	+----+----------+
	
insert into t_user(username) values('wangwu');

select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	|  2 | lisi     |
	|  3 | wangwu   |
	+----+----------+
	
rollback;

select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	+----+----------+

mysql> start transaction;
	Query OK, 0 rows affected (0.00 sec)

	mysql> insert into t_user(username) values('wangwu');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t_user(username) values('rose');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t_user(username) values('jack');
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	|  4 | wangwu   |
	|  5 | rose     |
	|  6 | jack     |
	+----+----------+
	4 rows in set (0.00 sec)

	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)

	mysql> select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	|  4 | wangwu   |
	|  5 | rose     |
	|  6 | jack     |
	+----+----------+
	4 rows in set (0.00 sec)

	mysql> rollback;
	Query OK, 0 rows affected (0.00 sec)

	mysql> select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | zs       |
	|  4 | wangwu   |
	|  5 | rose     |
	|  6 | jack     |
	+----+----------+
	4 rows in set (0.00 sec)

3.7、使用兩個事務演示以上的隔離級別

第一：演示第一級別：讀未提交read uncommitted

設定事務的全域隔離級別：

set global transaction isolation level read uncommitted;

查看事務的全域隔離級別：

select @@global.tx_isolation;
		+-----------------------+
		| @@global.tx_isolation |
		+-----------------------+
		| READ-UNCOMMITTED      |
		+-----------------------+

第二：演示read committed

set global transaction isolation level read committed;

第三：演示repeatable read

set global transaction isolation level repeatable read;

第四：演示serializable

set global transaction isolation level serializable;

mysql遠程登錄：mysql -h192.168.151.18 -uroot -p123

4、索引

4.1、什么是索引？有什么用？

索引相當于一本書的目錄，通過目錄可以快速的找到對應的資源，

在資料庫方面，查詢一張表的時候有兩種檢索方式：

第一種方式：全表掃描

第二種方式：根據索引檢索(效率很高)

索引為什么可以提高檢索效率？

其實最根本的原理是縮小了掃描的范圍，

索引雖然可以提高檢索效率，但是不能隨意的添加索引，因為索引也是資料庫當中的物件，也是需要資料庫不斷的維護，是有維護成本的，比如，表中的資料經常

被修改，這樣就不適合添加索引，因為資料一旦修改，索引需要重新排序，進行維護，

索引被用來快速找出在一個列上用一特定值的行，沒有索引，MySQL不得不首先以第一條記錄開始，然后讀完整個表直到它找出相關的行，表越大，花費時間越

多，對于一個有序欄位，可以運用二分查找(Binary Search)，這就是為什么性能能得到本質上的提高，

MYISAM和InnoDB都是B+Tree作為索引結構，

(主鍵：unique都會默認的添加索引)

添加索引是給某一個欄位，或者說某些欄位添加索引，

select ename,sal from emp where ename = 'SIMITH';

當ename欄位上沒有添加索引的時候，以上sql陳述句會進行全表掃描，掃描ename欄位中所有的值，

當ename欄位上添加索引的時候，以上sql陳述句會根據索引掃描，快速定位，

4.2、怎么創建索引物件？怎么洗掉索引物件？

創建索引物件

create index 索引名稱 on 表名(欄位名);

洗掉索引物件

drop index 索引名稱 on 表名;

4.3、什么時候考慮給欄位添加索引？(滿足什么條件)

資料量龐大，(根據客戶的需求，根據線上的環境)
該欄位很少的DML操作，(因為欄位進行修改操作，索引也需要維護)
該欄位經常出現在where子句中，(經常根據哪個欄位查詢)

4.4、注意：主鍵和具有unique約束的欄位會自動添加索引，

根據主鍵查詢效率較高，盡量根據主鍵檢索，

4.5、查看sql陳述句的執行計劃：

explain select ename,job from emp where sal = 5000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

explain主要用于分析查詢陳述句或表結構的性能瓶頸，

1、explain的作用

通過explain+sql陳述句可以知道如下內容：

①表的讀取順序，（對應id）

②資料讀取操作的操作型別，（對應select_type）

③哪些索引可以使用，（對應possible_keys）

④哪些索引被實際使用，（對應key）

⑤表直接的參考，（對應ref）

⑥每張表有多少行被優化器查詢，（對應rows）

explain select ename,job,sal from emp where sal > 1500 group by sal;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+

給薪資sal欄位添加索引：

create index emp_sal_index on emp(sal);
explain select ename,job from emp where sal = 5000;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

4.6、索引底層采用的資料結構是：B + Tree

4.7、索引的實作原理？

通過B Tree縮小掃描范圍，底層索引進行了排序，磁區，索引會攜帶資料在表中的“物理地址”，最終通過索引檢索到資料之后，獲取到關聯的物理地址，通過物理

地址定位表中的資料，效率是最高的，

create index emp_ename_index on emp(ename);
select ename from emp where ename = 'SIMITH';

通過索引轉換為：

select ename from emp where 物理地址 = 0x3;

4.8、索引的分類？

單一索引：給單個欄位添加索引

復合索引：給多個欄位聯合起來添加1個索引

主鍵索引：主鍵上會自動添加索引

唯一索引：有unique約束的欄位上會自動添加索引

4.9、索引什么時候失效？

模糊查詢like，

查詢ename欄位中包含有“A”的，

create index emp_ename_index on emp(ename);
select ename from emp where ename like '%A%';

模糊查詢的時候，第一個通配符使用的是%，這個時候索引是失效的，會進行全表掃描，

模糊查詢時，第一個位置最好不要寫%！

5、視圖(view)(了解)

5.1、什么是視圖？

站在不同的角度去看待資料，(同一張表的資料，通過不同的角度去看待，)

視圖是一種根據查詢(也就是SELECT運算式)定義的資料庫物件，用于獲取想要看到和使用的區域資料，
視圖有時也被稱為“虛擬表”，
視圖可以被用來從常規表(稱為“基表”)或其他視圖中查詢資料，
相對于從基表中直接獲取資料，視圖有以下好處：
- 訪問資料變的簡單
- 可被用來對不同用戶顯示不同的表的內容，
  - 用來協助適配表的結構以適應前端現有的應用程式
  - 視圖作用：
    - 提高檢索效率
    - 隱藏表的實作細節【面向視圖檢索】

5.2、怎么創建視圖？怎么洗掉視圖？

create view myview as select empno,ename from emp;
drop view myview;

注意：只有DQL陳述句才能以視圖物件的方式創建出來，

5.3、對視圖進行增刪改查，會影響到原表資料，(通過視圖影響原表資料的，不是直接操作的原表，)

可以對視圖進行CRUD操作，

5.4、面向視圖操作？

(create view myview as select empno,ename from emp;)
select * from myview;
	+-------+--------+
	| empno | ename  |
	+-------+--------+
	|  7369 | SIMITH |
	|  7499 | ALLEN  |
	|  7521 | WARD   |
	|  7566 | JONES  |
	|  7654 | MARTIN |
	|  7698 | BLAKE  |
	|  7782 | CLARK  |
	|  7788 | SCOTT  |
	|  7839 | KING   |
	|  7844 | TURNER |
	|  7876 | ADAMS  |
	|  7900 | JAMES  |
	|  7902 | FORD   |
	|  7934 | MILLER |
	+-------+--------+
create table emp_bak as select * from emp;# 復制表結構及資料到新表emp_bak，
create view myview1 as select empno,ename,sal from emp_bak;# 通過原表emp_bak，創建視圖myview1，
update myview1 set ename = 'hehe',sal = 1 where empno = 7369;# 通過視圖修改原表資料，
delete from myview1 where empno = 7369;# 通過視圖洗掉原表資料，

5.5、視圖的作用？—— 保密

視圖可以隱藏表的實作細節，保密級別較高的系統，資料庫只對外提供相關的視圖，java程式員只對視圖物件進行CRUD，視圖并不會提高查詢效率，

create view myview2 as select empno a,ename b,sal c from emp_bak;# 創建視圖
select * from myview2;
	+------+--------+---------+
	| a    | b      | c       |
	+------+--------+---------+
	| 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 |
	+------+--------+---------+
insert into myview2(a,b,c) values(8000,'GOD',9999);# 插入資料
select * from myview2;
	+------+--------+---------+
	| a    | b      | c       |
	+------+--------+---------+
	| 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 |
	| 8000 | GOD    | 9999.00 |
	+------+--------+---------+
	select * from emp_bak;# 視圖myview2對應的原表emp_bak
	+-------+--------+-----------+------+------------+---------+---------+--------+
	| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
	+-------+--------+-----------+------+------------+---------+---------+--------+
	|  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 |    NULL |     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 |
	|  8000 | GOD    | NULL      | NULL | NULL       | 9999.00 |    NULL |   NULL |
	+-------+--------+-----------+------+------------+---------+---------+--------+

6、DBA命令(了解) —— 最重要的是匯入匯出

6.1、新建用戶

create user username identified by 'password';

說明：username————你將創建的用戶名，password————該用戶的登錄密碼，如果為空，則該用戶不需要密碼登錄服務器，

6.2、授權

grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;

dbname=* 表示所有資料庫，
tbname=* 表示所有表(如果要授予該用戶對所有資料庫和表的相應操作權限則可用表示, 如.*)，
login ip=%表示任何ip，
password為空，表示不需要密碼即可登錄，
with grant option表示該用戶還可以授權其他用戶，
說明:privileges——用戶的操作權限，如select , insert , update 等，如果要授予所有的權限則使用all，

6.1.1、細粒度授權

首先以root用戶進入mysql，然后鍵入命令：grant select,insert,update,delect on *.* to p361 @localhost identified by "123";如果希望該用戶能夠在任何機器上登錄mysql，則將localhost改為“%”;

6.1.2、粗粒度授權

我們測驗用戶一般使用該命令授權，grant all privileges *.* to 'p3610'@'%' identified by "123";

注意：用以上命令授權的用戶不能給其它用戶授權，如果想要該用戶可以授權，用以下命令：

grant privileges on databasename.tablename to 'mysql_user_name'@'host' with grant option;

privileges包括：

alter：修改資料庫的表
create：創建新的資料庫或表
delete：洗掉表資料
drop：洗掉資料庫/表
index：創建/洗掉索引
insert：添加表資料
select：查詢表資料
update：更新表資料
all：運行任何操作
usage：只允許登錄

6.3、回收權限

revoke privileges on dbname[.tbname] from username;
revoke privileges on *.* from p361;

修改密碼

進入mysql庫中

use mysql;
select * from user;
update user set password = password('qwe') where user = 'p646';

重繪權限

flush privileges;

6.4、匯入匯出

6.4.1、匯出

6.4.1.1、匯出整個資料庫

在windows的dos命令視窗(不要登錄到mysql系統當中)中執行：mysqldump bjpowernonde>D:\bjpowernode.sql -uroot -p123;

6.4.1.2、匯出指定庫下的指定表

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

6.4.2、匯入

登錄MySQL資料庫管理系統之后，

create database bjpowernode;
use bjpowernode;

執行：

source D:\bjpowernode.sql;

7、資料庫設計三范式(重點內容，面試經常問)

7.1、什么是設計范式？

設計表的依據，按照這個三范式設計的表不會出現資料冗余，

7.2、三范式都是哪些？

第一范式：任何一張表都應該有范式一，并且每一欄位原子性不可再分，

資料庫表中不能出現重復記錄，每個欄位是原子性的不能再分，

關于第一范式，每一行必須唯一，也就是每個表必須有主鍵，這是我們資料庫設計的最基本要求，

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

多對多？三張表，關系表兩個外鍵，

t_student學生表
sno(pk)		sname
-----------------
1		張三
2		李四
3		王五

t_teacher講師表
t(pk)		tname
-----------------
1		王老師		
2		張老師
3		李老師

t_student_teacher_relation學生教師關系表
id(pk)		sno(fk)		tno(fk)
-------------------------------
1		1		3
2		1		1
3		2		2
4		2		3
5		3		1
6		3		3

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

一對多？兩張表，多的加外鍵，

班級t_class
cno(pk)	cname
-------------
1		班級1
2		班級2

學生t_student
sno(pk)	sname		classno(fk)
-------------------------------
101		張1		1
102		張2		1
103		張3		2
104		張4		2
105		張5		2

提醒：在實際開發中，以滿足用戶的需求為主，有的時候會拿冗余換執行速度，

7.3、一對一怎么設計？

一對一設計有兩種方案：主鍵共享和外鍵唯一，

第一種：主鍵共享

t_user_login	用戶登錄表
id(pk)		username	password
--------------------------------
1		zs		123
2		ls		456

t_user_detail	用戶詳細資訊表
id(pk+fk)		realname	tel	...
-----------------------------------
1		張三		12345678901
2		李四		09876543211

第二種：外鍵唯一

id(pk+fk)		realname	tel	userid(fk+unique)	...
-------------------------------------------------------
1		張三		12345678901	1
2		李四		09876543211	2

8、擴展

MYSQL中處理插入程序主鍵或唯一重復值的解決辦法

1.IGNORE:有則忽略，無則插入

2.REPLACE：有則洗掉再插入，無則插入

3.ON DUPLIACATE KEY UPDATE:有則更新，無則插入

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/460872.html

標籤：其他

上一篇：IDEA通過Jedis操作Linux上的Redis；Failed to connect to any host resolved for DNS name問題

下一篇：Mysql 執行流程

MySQL學習筆記-day03