MySQL(2)：SQL進階_ZenDei技術網路在線

一、資料庫相關理論 1、系統資料庫 information_schema：虛擬庫，不占用磁碟空間，存儲的是資料庫啟動後的一些參數，如用戶表信息、列信息、許可權信息、字元信息等performance_schema： MySQL 5.5開始新增一個資料庫：主要用於收集資料庫伺服器性能參數，記錄處理查詢請 ...

一、資料庫相關理論

1、系統資料庫

information_schema：虛擬庫，不占用磁碟空間，存儲的是資料庫啟動後的一些參數，如用戶表信息、列信息、許可權信息、字元信息等
performance_schema： MySQL 5.5開始新增一個資料庫：主要用於收集資料庫伺服器性能參數，記錄處理查詢請求時發生的各種事件、鎖等現象
mysql：授權庫，主要存儲系統用戶的許可權信息
test： MySQL資料庫系統自動創建的測試資料庫

2、創建庫

1 語法(help create database)

CREATE DATABASE 資料庫名 charset utf8;

2 資料庫命名規則：

可以由字母、數字、下劃線、＠、＃、＄
區分大小寫
唯一性
不能使用關鍵字如 create select
不能單獨使用數字
最長128位

3、庫相關操作

詳細見 SQL(1)初識SQL

二、表相關理論

表的本質即為 ‘文件’

1、存儲引擎

文件(表)的類型在MySQL中稱之為存儲引擎

不同的類型會對應不同的處理機制

引擎的區別(主要)

#引擎 
# innodb   支持事務，原子性操作
#               支持行鎖
#               支持外鍵
# myisam   查詢速度快 不支持事務
#               支持表鎖
#               不支持外鍵

sql底層流程

'''
sql底層流程原理：
1、一堆介面(Python、Perl、Ruby等等)

2、連接池(併發，為了機器的負載均衡引入池)

3、sql介面(資料庫操作命令，存儲過程，視圖，觸發器等等)

4、解析器(sql語句(有執行優先順序之分)，查詢\事務)

5、優化查詢(索引技術,最少的IO)

6、緩存\緩衝池(記憶體空間，存儲常用的數據，提升速度)

7、存儲引擎
　　myisam    
　　innodb   支持事務，行鎖，支持外鍵 
　　memory 記憶體

8、文件系統
'''

查看存儲引擎圖

2、表操作之約束條件

約束條件與數據類型的寬度一樣，是可選參數

作用：用於保證數據的完整性和一致性

# PRIMARY KEY (PK)    標識該欄位為該表的主鍵，可以唯一的標識記錄
# FOREIGN KEY (FK)    標識該欄位為該表的外鍵
# NOT NULL            標識該欄位不能為空
# UNIQUE KEY (UK)     標識該欄位的值是唯一的
# AUTO_INCREMENT      標識該欄位的值自動增長（整數類型，而且為主鍵）
# DEFAULT             為該欄位設置預設值

# UNSIGNED  無符號
# ZEROFILL  使用0填充

說明：

1. 是否允許為空，預設NULL，可設置NOT NULL，欄位不允許為空，必須賦值
2. 欄位是否有預設值，預設的預設值是NULL，如果插入記錄時不給欄位賦值，此欄位使用預設值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必須為正值（無符號） 不允許為空 預設是20
3. 是否是key
主鍵 primary key
外鍵 foreign key
索引 (index,unique...)

2.1 not null 和 default

是否可空，null表示空，非字元串
not null - 不可空
null - 可空

預設值，創建列時可以指定預設值，當插入數據時如果未主動設置，則自動添加預設值
create table tb1(
nid int not null defalut 2,
num int not null
)

==================not null====================
mysql> create table t1(id int); #id欄位預設可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); #可以插入空


mysql> create table t2(id int not null); #設置欄位id不為空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value



==================default====================
#設置id欄位有預設值後，則無論id欄位是null還是not null，都可以插入空，插入空預設填入default指定的預設值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;



==================綜合練習====================
mysql> create table student(
    -> name varchar(20) not null,
    -> age int(3) unsigned not null default 18,
    -> sex enum('male','female') default 'male',
    -> hobby set('play','study','read','music') default 'play,music'
    -> );
mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(3) unsigned                    | NO   |     | 18         |       |
| sex   | enum('male','female')              | YES  |     | male       |       |
| hobby | set('play','study','read','music') | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('mogu');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex  | hobby      |
+------+-----+------+------------+
| mogu |  18 | male | play,music |
+------+-----+------+------------+

View Code

2.2 unique key

============設置唯一約束 UNIQUE===============
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


mysql> insert into department1 values(1,'IT','技術');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技術');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

View Code

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #聯合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

聯合唯一

2.3 primary key

從約束角度看primary key欄位的值不為空且唯一，那我們直接使用not null+unique不就可以了嗎，要它乾什麼？

主鍵primary key是innodb存儲引擎組織數據的依據，innodb稱之為索引組織表，一張表中必須有且只有一個主鍵。

一個表中可以：

單列做主鍵
多列做主鍵（複合主鍵）

============單列做主鍵===============
#方法一：not null+unique
create table department1(
id int not null unique, #主鍵
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：在某一個欄位後用primary key
create table department2(
id int primary key, #主鍵
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：在所有欄位後單獨定義primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #創建主鍵併為其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

單列主鍵

==================多列做主鍵================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

多列主鍵

2.4 auto_increment

約束欄位為自動增長，被約束的欄位必須同時被key約束

#不指定id，則自動增長
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | mogu     | male |
|  2 | xiaoming | male |
+----+----------+------+
2 rows in set (0.00 sec)
#也可以指定id
mysql> insert into student values (4,'xiaonvhai','female');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values (7,'xiaohuochai','male');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+--------+
| id | name        | sex    |
+----+-------------+--------+
|  1 | mogu        | male   |
|  2 | xiaoming    | male   |
|  4 | xiaonvhai   | female |
|  7 | xiaohuochai | male   |
+----+-------------+--------+
4 rows in set (0.00 sec)
#對於自增的欄位，在用delete刪除後，再插入值，該欄位仍按照刪除前的位置繼續增長
mysql> delete from student;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student (name) values ('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  8 | zhangsan | male |
+----+----------+------+
1 row in set (0.00 sec)
#應該用truncate清空表，比起delete一條一條地刪除記錄，truncate是直接清空表，在刪除大表時用它
mysql> truncate student;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into student (name) values ('lisi');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | lisi | male |
+----+------+------+
1 row in set (0.00 sec)

View Code

#在創建完表後，修改自增欄位的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=4;#修改起始值

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

mysql> insert into student (name)values('xiaomogu');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  4 | xiaomogu | male |
+----+----------+------+
1 row in set (0.00 sec)

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
#也可以創建表時指定auto_increment的初始值，註意初始值的設置為表選項，應該放到括弧外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;

瞭解自增

2.5 foreign key 外鍵

一、快速理解foreign key

員工信息表有三個欄位：工號姓名部門

公司有3個部門，但是有1個億的員工，那意味著部門這個欄位需要重覆存儲，部門名字越長，越浪費

解決方法：

我們完全可以定義一個部門表

然後讓員工信息表關聯該表，如何關聯，即foreign key

#表類型必須是innodb存儲引擎，且被關聯的欄位，即references指定的另外一個表的欄位，必須保證唯一
create table department(
id int primary key,
name varchar(20)