一. 安裝
下載下傳位址 : https://dev.mysql.com/downloads/mysql/
1. 安裝步驟
(1) 選擇5.7版本
(2) 針對作業系統的不同下載下傳不同的版本
(3) 解壓
将解壓後的檔案夾解壓到你所指定的目錄
(4) 添加環境變量
【右鍵計算機】--》【屬性】--》【進階系統設定】--》【進階】--》【環境變量】--》【在第二個内容框中找到 變量名為Path 的一行，輕按兩下】 --> 【将MySQL的bin目錄路徑追加到變值值中，用 ； 分割】
window10系統可以在點選Path後,可以選擇添加,把MySQL的bin目錄粘貼到新的行裡.
(5) 初始化
添加完環境變量後, 一定要先初始化,用管理者權限打開cmd視窗,寫入按回車 : mysqld --initialize-insecure 按回車然後等待初始化完成,在mysql-5.7.23-winx64檔案下出現一個新的檔案夾data
(6) 啟動mysql服務端
在cmd(管理者身份)輸入 mysqld, 啟動MySQL服務
(7) 啟動mysql用戶端并連接配接mysql服務端(新開一個cmd視窗)
再開啟一個新的cmd視窗,輸入 mysql -u root -p 指令,按enter出現輸入密碼行,不用輸入,繼續按enter.
(8) install mysql
在install之前複制bin檔案夾的絕對路徑,例如: D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin
關閉任務管理器裡關閉mysqld.exe程式程序.
用管理者權限再開啟一個cmd視窗,
開啟MySQL的window服務,輸入 : D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin\mysqld --install 按enter鍵
移除MySQL的window服務,輸入 : D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin\mysqld --remove 按enter鍵
在服務裡面檢視是否有MySQL
滑鼠右鍵有啟動,停止等選項
也可以在cmd視窗進行操作 :
在cmd視窗開啟MySQL服務 : net start MySQL (以管理者身份開啟cmd)
在cmd視窗關閉MySQL服務 : net stop MySQL
(9) 統一字元編碼
進入mysql用戶端，執行\s,檢視編碼格式
把編碼格式改成utf-8,執行以下操作:
1）my.ini檔案是mysql的配置檔案，在D:\mysql-5.7.23-winx64\mysql-5.7.23-winx64(安裝路徑)檔案下建立my.ini檔案
2) 把下面的代碼拷貝到my.ini檔案裡,并儲存.
#mysql5.5以上：修改方式為
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8 3）以管理者身份重新開機服務, 執行如下指令
C:\Windows\system32>net stop MySQL # 先停止服務
MySQL 服務正在停止..
MySQL 服務已成功停止。
C:\Windows\system32>net start MySQL
MySQL 服務正在啟動 .
MySQL 服務已經啟動成功。 4）在cmd中輸入mysql進入mysql環境，執行\s,顯示編碼格式都為utf-8,表示成功.
二. 庫的操作
1. 系統資料庫
執行下面指令,檢視系統的資料庫
show databases; nformation_schema： 虛拟庫，不占用磁盤空間，存儲的是資料庫啟動後的一些參數，如使用者表資訊、列資訊、權限資訊、字元資訊等
performance_schema： MySQL 5.5開始新增一個資料庫：主要用于收集資料庫伺服器性能參數，記錄處理查詢請求時發生的各種事件、鎖等現象
mysql： 授權庫，主要存儲系統使用者的權限資訊
test： MySQL資料庫系統自動建立的測試資料庫
2. 建立資料庫
文法:
CREATE DATABASE 資料庫名; 命名規則:
可以由字母、數字、下劃線、＠、＃、＄
區分大小寫
唯一性
不能使用關鍵字如 create select
不能單獨使用數字
最長128位
# 基本上跟python或者js的命名規則一樣 3. 資料庫的相關操作
#檢視資料庫
show databases;
#檢視目前庫
show create database db1;
#檢視所在的庫
select database();
#選擇資料庫
use 資料庫名
#删除資料庫
DROP DATABASE 資料庫名;
# 修改資料庫
alter database db1 charset utf8; 三. 表的操作
1. 存儲引擎
現實生活中我們用來存儲資料的檔案有不同的類型，每種檔案類型對應各自不同的處理機制：比如處理文本用txt類型，處理表格用excel，處理圖檔用png等資料庫中的表也應該有不同的類型，表的類型不同，會對應mysql不同的存取機制，表類型又稱為存儲引擎. MySql資料庫提供了多種存儲引擎, 使用者可以根據不同的需求為資料表選擇不同的存儲引擎，使用者也可以根據自己的需要編寫自己的存儲引擎.
mysql> show engines\G; # 檢視所有支援的引擎
mysql> show variables like \'storage_engine%\'; # 檢視正在使用的存儲引擎
create table t1(id int)engine=innodb; # 預設不寫就是innodb 2. 建立表
表相當于檔案，表中的一條記錄就相當于檔案的一行内容，不同的是，表中的第一條記錄有對應的标題，稱為表的字段.
文法:
create table 表名(
字段名1 類型[(寬度) 限制條件],
字段名2 類型[(寬度) 限制條件],
字段名3 類型[(寬度) 限制條件]
);
#注意：
1. 在同一張表中，字段名是不能相同
2. 寬度和限制條件可選
3. 字段名和類型是必須的 步驟 :
(1) 建立資料庫
CREATE DATABASE db1; (2) 使用資料庫
USE db1; (3) 建立表
create table t1(
id int, # 字段id
name varchar(50), # 字段 name
age int() # 字段age
); (4) 插入表的記錄
insert into t1 values # values 也可以寫成values(id,name,age)括号呢可以指定字段進行插入
(1,\'jack\',18), # 插入多條記錄,用逗号隔開
(2,\'tom\',22); (5) 對記錄修改
update db1.t1 set name=\'ben\';
update db1.t1 set name=\'steve\' where id=2; # 指定id為2的記錄 (6) 删除記錄
delete from t1;
delete from t1 where id=2; 3. 表的其它操作
(1) 查詢表的存儲資料
文法 : select * from 表名;
mysql> select * from t1;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | jack | 18 |
| 2 | tom | 22 |
+------+-------+------+
2 rows in set (0.02 sec) (2) 查詢表的結構
文法 : desc 表名
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int() | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.16 sec) (3) 檢視表的詳細結構
mysql> show create table a1\G;
*************************** 1. row ***************************
Table: a1
Create Table: CREATE TABLE `a1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec) (4) 複制表
1) 新建立一個資料庫db2
create database db2; 2) 使用db2
use db2; 3) 複制db1中的表t1
# 這就是複制表的操作（既複制了表結構，又複制了記錄）
mysql> create table t2 select * from db1.t1;
Query OK, 2 rows affected (0.03 sec) 4) 檢視db2中的表t2
#再去檢視db3檔案夾下的t3表發現 跟db3檔案下的t1表資料一樣
mysql> select * from db3.b1;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | jack | 18 |
| 2 | tom | 22 |
+------+-------+------+
2 rows in set (0.00 sec) # 檢視表結構
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int() | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec) 隻拷貝表結構,不要記錄:
#在db2資料庫下新建立一個t2表，給一個where條件，條件要求不成立，條件為false，隻拷貝表結構
mysql> create table t2 select * from db2.a1 where 1>5; # where 為條件判斷,隻要where後面的條件為假即可
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0 #檢視表結構中的資料，發現是空資料
mysql> select * from t2;
Empty set (0.00 sec) 使用like也是隻拷貝表結構，不拷貝記錄
mysql> create table t2 like db1.t1;
Query OK, 0 rows affected (0.01 sec) (5) 删除表
drop table 表名; 四. 資料類型
1. mysql 常用資料類型
#1. 數字：
整型：tinyint int bigint
小數：
float ：在位數比較短的情況下不精準
double ：在位數比較長的情況下不精準
0.000001230123123123
存成：0.000001230000
decimal：（如果用小數，則用推薦使用decimal）
精準
内部原理是以字元串形式去存
#2. 字元串：
char（10）：簡單粗暴，浪費空間，存取速度快
root存成root000000
varchar：精準，節省空間，存取速度慢
sql優化：建立表時，定長的類型往前放，變長的往後放
比如性别 比如位址或描述資訊
>255個字元，超了就把檔案路徑存放到資料庫中。
比如圖檔，視訊等找一個檔案伺服器，資料庫中隻存路徑或url。
#3. 時間類型：
最常用：datetime
#4. 枚舉類型與集合類型
enum 和set 2. 數值類型
整數類型：TINYINT SMALLINT MEDIUMINT INT BIGINT
作用：存儲年齡，等級，id，各種号碼等
=======================================================
tinyint[(m)] [unsigned] [zerofill]
小整數，資料類型用于儲存一些範圍的整數數值範圍：
有符号：
-128 ～ 127
無符号：0～ 255
PS： MySQL中無布爾值，使用tinyint(1)構造。
=======================================================
int[(m)][unsigned][zerofill]
整數，資料類型用于儲存一些範圍的整數數值範圍：
有符号：
-2147483648 ～ 2147483647
無符号：0 ～ 4294967295
=======================================================
bigint[(m)][unsigned][zerofill]
大整數，資料類型用于儲存一些範圍的整數數值範圍：
有符号：
-9223372036854775808 ～ 9223372036854775807
無符号：0 ～ 18446744073709551615 注意:
1) 整數類型都是預設有符号的,可以設定成無符号
mysql> create table t3(x tinyint unsigned); # 申明資料類型後加上 unsigned 變為無符号 2) int類型後面的存儲是顯示寬度，而不是存儲寬度. 整形類型，其實沒有必要指定顯示寬度，使用預設的就ok
mysql> create table t3(id int(1) unsigned);
#插入255555記錄也是可以的
mysql> insert into t3 values(255555);
mysql> select * from t3;
+--------+
| id |
+--------+
| 255555 |
+--------+
ps:以上操作還不能夠驗證，再來一張表驗證用zerofill 用0填充
# zerofill 用0填充
mysql> create table t4(id int(5) unsigned zerofill);
mysql> insert into t4 value(1);
Query OK, 1 row affected (0.00 sec)
#插入的記錄是1，但是顯示的寬度是00001
mysql> select * from t4;
+-------+
| id |
+-------+
| 00001 |
+-------+
row in set (0.00 sec) 3. 浮點型
定點數類型: DEC等同于DECIMAL
浮點類型：FLOAT DOUBLE
作用：存儲薪資、身高、體重、體質參數等
-------------------------FLOAT-------------------
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
#參數解釋：單精度浮點數（非準确小數值），M是全長，D是小數點後個數。M最大值為255，D最大值為30
#有符号：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
#無符号：
1.175494351E-38 to 3.402823466E+38
#精确度：
**** 随着小數的增多，精度變得不準确 ****
-------------------------DOUBLE-----------------------
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
#參數解釋： 雙精度浮點數（非準确小數值），M是全長，D是小數點後個數。M最大值為255，D最大值為30
#有符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
#無符号：
2.2250738585072014E-308 to 1.7976931348623157E+308
#精确度：
****随着小數的增多，精度比float要高，但也會變得不準确 ****
======================================
--------------------DECIMAL------------------------
decimal[(m[,d])] [unsigned] [zerofill]
#參數解釋：準确的小數值，M是整數部分總個數（負号不算），D是小數點後個數。 M最大值為65，D最大值為30。
#精确度：
**** 随着小數的增多，精度始終準确 ****
對于精确數值計算時需要用此類型
decaimal能夠存儲精确值的原因在于其内部按照字元串存儲。 驗證三種浮點數類型建表
#1驗證FLOAT類型建表：
mysql> create table t5(x float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for column \'x\'. Maximum is 30.
mysql> create table t5(x float(256,30));
ERROR 1439 (42000): Display width out of range for column \'x\' (max = 255)
mysql> create table t5(x float(255,30)); #建表成功
Query OK, 0 rows affected (0.03 sec)
#2驗證DOUBLE類型建表：
mysql> create table t6(x double(255,30)); #建表成功
Query OK, 0 rows affected (0.03 sec)
#3驗證deimal類型建表：
mysql> create table t7(x decimal(66,31));
ERROR 1425 (42000): Too big scale 31 specified for column \'x\'. Maximum is 30.
mysql> create table t7(x decimal(66,30));
ERROR 1426 (42000): Too big precision 66 specified for column \'x\'. Maximum is 65.
mysql> create table t7(x decimal(65,30)); #建表成功
Query OK, 0 rows affected (0.00 sec) 4. 日期類型
DATE TIME DATETIME TIMESTAMP YEAR
作用：存儲使用者注冊時間，文章釋出時間，員工入職時間，出生時間，過期時間等
文法：
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（\'-838:59:59\'/\'838:59:59\'）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時） 5. 字元串類型
# 注意：char和varchar括号内的參數指的都是字元的長度
# char類型：定長，簡單粗暴，浪費空間，存取速度快
字元長度範圍：0-255（一個中文是一個字元，是utf8編碼的3個位元組）
存儲：
存儲char類型的值時，會往右填充空格來滿足長度
例如：指定長度為10，存>10個字元則報錯，存<10個字元則用空格填充直到湊夠10個字元存儲
檢索：
在檢索或者說查詢時，查出的結果會自動删除尾部的空格，除非我們打開pad_char_to_full_length SQL模式（設定SQL模式：SET sql_mode = \'PAD_CHAR_TO_FULL_LENGTH\';
查詢sql的預設模式：select @@sql_mode;）
# varchar類型：變長，精準，節省空間，存取速度慢
字元長度範圍：0-65535（如果大于21845會提示用其他類型 。mysql行最大限制為65535位元組，字元編碼為utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存儲：
varchar類型存儲資料的真實内容，不會用空格填充，如果\'ab \',尾部的空格也會被存起來
強調：varchar類型會在真實資料前加1-2Bytes的字首，該字首用來表示真實資料的bytes位元組數（1-2Bytes最大表示65535個數字，正好符合mysql對row的最大位元組限制，即已經足夠使用）
如果真實的資料<255bytes則需要1Bytes的字首（1Bytes=8bit 2**8最大表示的數字為255）
如果真實的資料>255bytes則需要2Bytes的字首（2Bytes=16bit 2**16最大表示的數字為65535）
檢索：
尾部有空格會儲存下來，在檢索或者說查詢時，也會正常顯示包含空格在内的内容 char填充空格來滿足固定長度，但是在查詢時卻會删除尾部的空格，修改sql_mode讓其現出原形。
length()：檢視位元組數; char_length():檢視字元數 # 建立t1表,分别指明字段x為char類型，字段y為varchar類型
mysql> create table t1(x char(5),y varchar(4));
Query OK, 0 rows affected (0.16 sec)
# char存放的是5個字元，而varchar存4個字元
mysql> insert into t1 values(\'你瞅啥 \',\'你瞅啥 \');
Query OK, 1 row affected (0.01 sec)
# 在檢索時char将自己浪費的2個字元給删掉了，裝的好像自己沒浪費過空間一樣，而varchar很老實，存了多少，就顯示多少
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-----------+----------------+------------+----------------+
| 你瞅啥 | 3 | 你瞅啥 | 4 |
+-----------+----------------+------------+----------------+
row in set (0.02 sec)
#略施小計，讓char現原形
mysql> SET sql_mode = \'PAD_CHAR_TO_FULL_LENGTH\';
Query OK, 0 rows affected (0.00 sec)
#檢視目前mysql的mode模式
mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
row in set (0.00 sec)
#原形畢露了吧。。。。
mysql> select x,char_length(x) y,char_length(y) from t1;
+-------------+------+----------------+
| x | y | char_length(y) |
+-------------+------+----------------+
| 你瞅啥 | 5 | 4 |
+-------------+------+----------------+
row in set (0.00 sec)
# 檢視位元組數
#char類型：3個中文字元+2個空格=11Bytes
#varchar類型:3個中文字元+1個空格=10Bytes
mysql> select x,length(x),y,length(y) from t1;
+-------------+-----------+------------+-----------+
| x | length(x) | y | length(y) |
+-------------+-----------+------------+-----------+
| 你瞅啥 | 11 | 你瞅啥 | 10 |
+-------------+-----------+------------+-----------+
row in set (0.02 sec) 6. 枚舉類型和集合類型
枚舉和集合可以讓字段的值隻能在給定範圍中選擇，如單選框，多選框
enum 單選 隻能在給定的範圍内選一個值，如性别 sex 男male/女female
set 多選 在給定的範圍内可以選擇一個或一個以上的值（愛好1,愛好2,愛好3...）
mysql> create table consumer(
-> id int,
-> name varchar(50),
-> sex enum(\'male\',\'female\',\'other\'),
-> level enum(\'vip1\',\'vip2\',\'vip3\',\'vip4\'),#在指定範圍内，多選一
-> fav set(\'play\',\'music\',\'read\',\'study\') #在指定範圍内，多選多
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into consumer values
-> (1,\'jack\',\'male\',\'vip2\',\'read,study\'),
-> (2,\'steve\',\'other\',\'vip4\',\'play\');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from consumer;
+------+---------+-------+-------+------------+
| id | name | sex | level | fav |
+------+---------+-------+-------+------------+
| 1 | jack | male | vip2 | read,study |
| 2 | steve | other | vip4 | play |
+------+---------+-------+-------+------------+
rows in set (0.00 sec) 五. 完整性限制
1. 介紹
限制條件與資料類型的寬度一樣，都是可選參數
作用：用于保證資料的完整性和一緻性
主要分為:
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\'
#必須為正值（無符号） 不允許為空 預設是20
age int unsigned NOT NULL default 20
# 3. 是否是key
主鍵 primary key
外鍵 foreign key
索引 (index,unique...) 2. not null 和 default
是否可空，null表示空，非字元串
not null - 不可空
null - 可空
預設值，建立列時可以指定預設值，當插入資料時如果未主動設定，則自動添加預設值 create table tb1(
nid int not null defalut 2, # 限制nid不能為空,預設值為2, 設定nid字段有預設值後，則無論id字段是null還是not null，都可以插入空，插入空預設填入default指定的預設值
num int not null # 限制num不能為空
); 3. unique
在mysql中稱為單列唯一
舉例說明:
建立表:
# 建立公司部門表（每個公司都有唯一的一個部門）。
mysql> create table department(
-> id int,
-> name char(10)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into department values(1,\'IT\'),(2,\'IT\');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from department;
+------+------+
| id | name |
+------+------+
| 1 | IT |
| 2 | IT |
+------+------+
rows in set (0.00 sec)
# 發現： 同時插入兩個IT部門也是可以的，但這是不合理的，是以我們要設定name字段為unique 解決這種不合理的現象。
驗證之前重複插入記錄的操作是可行的，但是不符合場景 使用限制條件unique，來對公司部門的字段進行設定
#第一種建立unique的方式
#例子1：
create table department(
id int,
name char(10) unique
);
mysql> insert into department values(1,\'it\'),(2,\'it\');
ERROR 1062 (23000): Duplicate entry \'it\' for key \'name\'
#例子2：
create table department(
id int unique,
name char(10) unique
);
insert into department values(1,\'it\'),(2,\'sale\');
#第二種建立unique的方式
create table department(
id int,
name char(10) ,
unique(id),
unique(name)
);
insert into department values(1,\'it\'),(2,\'sale\'); 聯合唯一: 隻要兩列記錄，有一列不同，既符合聯合唯一的限制
# 建立services表
mysql> create table services(
-> id int,
-> ip char(15),
-> port int,
-> unique(id),
-> unique(ip,port)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | | | ip | char(15) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ rows in set (0.01 sec) #聯合唯一，隻要兩列記錄，有一列不同，既符合聯合唯一的限制 mysql> insert into services values -> (1,\'192,168,11,23\',80), -> (2,\'192,168,11,23\',81), -> (3,\'192,168,11,25\',80); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from services; +------+---------------+------+ | id | ip | port | +------+---------------+------+ | 1 | 192,168,11,23 | 80 | | 2 | 192,168,11,23 | 81 | | 3 | 192,168,11,25 | 80 | +------+---------------+------+ rows in set (0.00 sec) mysql> insert into services values (4,\'192,168,11,23\',80); ERROR 1062 (23000): Duplicate entry \'192,168,11,23-80\' for key \'ip\' 4. primary key : 主鍵
一個表中可以：
單列做主鍵
多列做主鍵（複合主鍵）
限制：等價于 not null unique,字段的值不為空且唯一
存儲引擎預設是（innodb）:對于innodb存儲引擎來說，一張表必須有一個主鍵。
單列主鍵:
# 建立t14表，為id字段設定主鍵，唯一的不同的記錄
create table t14(
id int primary key,
name char(16)
);
insert into t14 values
(1,\'xiaoma\'),
(2,\'xiaohong\');
mysql> insert into t14 values(2,\'wxxx\');
ERROR 1062 (23000): Duplicate entry \'6\' for key \'PRIMARY\'
# not null + unique的化學反應，相當于給id設定primary key
create table t15(
id int not null unique,
name char(16)
);
mysql> create table t15(
-> id int not null unique,
-> name char(16)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc t15;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.02 sec) 複合主鍵:
create table t16(
ip char(15),
port int,
primary key(ip,port)
);
insert into t16 values
(\'1.1.1.2\',80),
(\'1.1.1.2\',81); 5. auto_increment
讓限制的字段為自動增長，限制的字段必須同時被key限制
# 不指定id，則自動增長
# 建立student
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 | |
+-------+-----------------------+------+-----+---------+----------------+
rows in set (0.17 sec)
#插入記錄
mysql> insert into student(name) values (\'老白\'),(\'小白\');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 老白 | male |
| 2 | 小白 | male |
+----+--------+------+
rows in set (0.00 sec) 也可以指定id
mysql> insert into student values(4,\'asb\',\'female\');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(7,\'wsb\',\'female\');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | 老白 | male |
| 2 | 小白 | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+--------+--------+
rows in set (0.00 sec)
# 再次插入一條不指定id的記錄，會在之前的最後一條記錄繼續增長
mysql> insert into student(name) values (\'大白\');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | 老白 | male |
| 2 | 小白 | male |
| 4 | asb | female |
| 7 | wsb | female |
| 8 | 大白 | male |
+----+--------+--------+
rows in set (0.00 sec) 對于自增的字段，在用delete删除後，再插入值，該字段仍按照删除前的位置繼續增長
mysql> delete from student;
Query OK, 5 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(name) values(\'ysb\');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 9 | ysb | male |
+----+------+------+
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(\'xiaobai\');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | xiaobai | male |
+----+---------+------+
row in set (0.00 sec)
mysql> 清空表區分delete和truncate的差別：
delete from t1, 如果有自增id，新增的資料，仍然是以删除前的最後一樣作為起始.
truncate table t1, 資料量大，删除速度比上一條快，且直接從零開始.
6. foreign key
一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY.
employee表
| id | name | age | dep_id |
|---|---|---|---|
| 1 | a | 19 | 2 |
| 2 | b | 23 | 1 |
| 3 | c | 27 | 2 |
| 4 | d | 24 | 3 |
department 表
| id | Address |
|---|---|
| 1 | 技術部 |
| 2 | 銷售部 |
| 3 | 财務部 |
注意:
1) 先建主表(獨立的表),即department表,再建被關聯表(也叫從表,有外鍵),即employee表.
2) 在建關聯表時,要加入一下sql語句:
on delete cascade 同步删除
on update cascade 同步更新
create table employee(
id int primary key,
name varchar(20) not null,
age int not null,
dep_id int,
constraint fk_dep foreign key(dep_id) references dep(id) # 建立外鍵, fk_dep是我們起的外鍵名
on delete cascade # 同步删除
on update cascade # 同步更新
); 六. 單表查詢
一、單表查詢的文法
SELECT 字段1,字段2... FROM 表名
WHERE 條件
GROUP BY field
HAVING 篩選
ORDER BY field
LIMIT 限制條數
二、關鍵字的執行優先級（重點）
重點中的重點：關鍵字的執行優先級
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的限制條件，去檔案/表中取出一條條記錄
3.将取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組
4.将分組的結果進行having過濾
5.執行select
6.去重
7.将結果按條件排序：order by
8.限制結果的顯示條數 公司員工表，表的字段和資料類型如下
company.employee
員工id id int
姓名 name varchar
性别 sex enum
年齡 age int
入職日期 hire_date date
崗位 post varchar
職位描述 post_comment varchar
薪水 salary double
辦公室 office int
部門編号 depart_id int sql語句建公司員工表,并插入記錄
#建立表,設定字段的限制條件
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
sex enum(\'male\',\'female\') not null default \'male\', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,# 一個部門一個屋
depart_id int
);
# 檢視表結構
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment|
| emp_name | varchar(20) | NO | | NULL | |
| sex | enum(\'male\',\'female\') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
rows in set (0.08 sec)
#插入記錄
#三個部門：教學，銷售，營運
insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values
(\'jack\',\'male\',18,\'20170301\',\'辦事處\',7300.33,401,1), #以下是教學部
(\'tom\',\'male\',78,\'20150302\',\'teacher\',1000000.31,401,1),
(\'wusir\',\'male\',81,\'20130305\',\'teacher\',8300,401,1),
(\'ben\',\'male\',73,\'20140701\',\'teacher\',3500,401,1),
(\'nezha\',\'male\',28,\'20121101\',\'teacher\',2100,401,1),
(\'steve\',\'female\',18,\'20110211\',\'teacher\',9000,401,1),
(\'jerry\',\'male\',18,\'19000301\',\'teacher\',30000,401,1),
(\'xiaomage\',\'male\',48,\'20101111\',\'teacher\',10000,401,1),
(\'歪歪\',\'female\',48,\'20150311\',\'sale\',3000.13,402,2),#以下是銷售部門
(\'丫丫\',\'female\',38,\'20101101\',\'sale\',2000.35,402,2),
(\'丁丁\',\'female\',18,\'20110312\',\'sale\',1000.37,402,2),
(\'星星\',\'female\',18,\'20160513\',\'sale\',3000.29,402,2),
(\'格格\',\'female\',28,\'20170127\',\'sale\',4000.33,402,2),
(\'a\',\'male\',28,\'20160311\',\'operation\',10000.13,403,3), #以下是營運部門
(\'b\',\'male\',18,\'19970312\',\'operation\',20000,403,3),
(\'c\',\'female\',18,\'20130311\',\'operation\',19000,403,3),
(\'d\',\'male\',18,\'20150411\',\'operation\',18000,403,3),
(\'e\',\'female\',18,\'20140512\',\'operation\',17000,403,3)
; (1) where 限制
where子句中可以使用
1.比較運算符：>、<、>=、<=、<>、!=
2.between 80 and 100 ：值在80到100之間
3.in(80,90,100)值是10或20或30
4.like \'xiaomagepattern\': pattern可以是%或者_。%小時任意多字元，_表示一個字元
5.邏輯運算符：在多個條件直接可以使用邏輯運算符 and or not #1 :單條件查詢
mysql> select id,emp_name from employee where id > 5;
+----+------------+
| id | emp_name |
+----+------------+
| 6 | steve |
| 7 | jerry |
| 8 | xiaomage |
| 9 | 歪歪
| 10 | 丫丫
| 11 | 丁丁
| 12 | 星星
| 13 | 格格
| 14 | a
| 15 | b
| 16 | c
| 17 | d
| 18 | e
#2 多條件查詢
mysql> select emp_name from employee where post=\'teacher\' and salary>10000;
+----------+
| emp_name |
+----------+
| tom |
| jerry |
+----------+
#3.關鍵字BETWEEN AND
SELECT name,salary FROM employee
WHERE salary BETWEEN 10000 AND 20000;
SELECT name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000;
#注意\'\'是空字元串，不是null
SELECT name,post_comment FROM employee WHERE post_comment=\'\';
ps：
執行
update employee set post_comment=\'\' where id=2;
再用上條檢視，就會有結果了
#5:關鍵字IN集合查詢
mysql> SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
+------------+---------+
| name | salary |
+------------+---------+
| ben | 3500.00 |
| steve | 9000.00 |
+------------+---------+
rows in set (0.00 sec)
mysql> SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;
+------------+---------+
| name | salary |
+------------+---------+
| ben | 3500.00 |
| steve | 9000.00 |
+------------+---------+
mysql> SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;
+-----------+------------+
| name | salary |
+-----------+------------+
| jack | 7300.33 |
| tom | 1000000.31 |
| wusir | 8300.00 |
| nezha | 2100.00 |
| jerry | 30000.00 |
| xiaomage | 10000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
| a | 10000.13 |
| b | 20000.00 |
| c | 19000.00 |
| d | 18000.00 |
| e | 17000.00 |
+-----------+------------+
rows in set (0.00 sec)
#6:關鍵字LIKE模糊查詢
通配符’%’
mysql> SELECT * FROM employee WHERE name LIKE \'jin%\';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | steve | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jerry | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
rows in set (0.00 sec)
通配符\'_\'
mysql> SELECT age FROM employee WHERE name LIKE \'to_\';
+-----+
| age |
+-----+
| 78 |
+-----+
row in set (0.00 sec)
練習：
1. 檢視崗位是teacher的員工姓名、年齡
2. 檢視崗位是teacher且年齡大于30歲的員工姓名、年齡
3. 檢視崗位是teacher且薪資在9000-1000範圍内的員工姓名、年齡、薪資
4. 檢視崗位描述不為NULL的員工資訊
5. 檢視崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資
6. 檢視崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資
7. 檢視崗位是teacher且名字是jin開頭的員工姓名、年薪
#對應的sql語句
select name,age from employee where post = \'teacher\';
select name,age from employee where post=\'teacher\' and age > 30;
select name,age,salary from employee where post=\'teacher\' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post=\'teacher\' and salary in (10000,9000,30000);
select name,age,salary from employee where post=\'teacher\' and salary not in (10000,9000,30000);
select name,salary*12 from employee where post=\'teacher\' and name like \'jin%\'; (2) group by 分組查詢
#1、首先明确一點：分組發生在where之後，即分組是基于where之後得到的記錄而進行的
#2、分組指的是：将所有記錄按照某個相同字段進行歸類，比如針對員工資訊表的職位分組，或者按照性别進行分組等
#3、為何要分組呢？
取每個部門的最高工資
取每個部門的員工數
取男人數和女人數
小竅門：‘每’這個字後面的字段，就是我們分組的依據
#4、大前提：
可以按照任意字段分組，但是分組完畢後，比如group by post，隻能檢視post字段，如果想檢視組内資訊，需要借助于聚合函數 mysql> select * from employee group by post;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 14 | a | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 1 | jack | male | 18 | 2017-03-01 | 辦事處 | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
rows in set (0.00 sec)
#由于沒有設定ONLY_FULL_GROUP_BY,于是也可以有結果，預設都是組内的第一條記錄，但其實這是沒有意義的
如果想分組，則必須要設定全局的sql的模式為ONLY_FULL_GROUP_BY
mysql> set global sql_mode=\'ONLY_FULL_GROUP_BY\';
Query OK, 0 rows affected (0.00 sec)
#檢視MySQL 5.7預設的sql_mode如下：
mysql> select @@global.sql_mode;
+--------------------+
| @@global.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
row in set (0.00 sec)
mysql> exit;#設定成功後，一定要退出，然後重新登入方可生效
# group by分組之後，隻能檢視目前字段,如果想檢視組内資訊
mysql> select * from emp group by post;# 報錯
ERROR 1054 (42S22): Unknown column \'post\' in \'group statement\'
mysql> select post from employee group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 辦事處 |
+-----------------------------------------+
rows in set (0.00 sec) (3) 聚合函數
group by分組之後，隻能檢視目前字段,如果想檢視組内資訊，可以借助于聚合函數
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求總個數
#強調：聚合函數聚合的是組的内容，若是沒有分組，則預設一組
# 每個部門有多少個員工
select post,count(id) from employee group by post;
# 每個部門的最高薪水
select post,max(salary) from employee group by post;
# 每個部門的最低薪水
select post,min(salary) from employee group by post;
# 每個部門的平均薪水
select post,avg(salary) from employee group by post;
# 每個部門的所有薪水
select post,sum(age) from employee group by post; (4) having 過濾
HAVING與WHERE不一樣的地方在于
#！！！執行優先級從高到低：where > group by > having
#1. Where 發生在分組group by之前，因而Where中可以有任意字段，但是絕對不能使用聚合函數。
#2. Having發生在分組group by之後，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數 mysql> select * from employee where salary>1000000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
row in set (0.00 sec)
mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##錯誤，分組後無法直接取到salary字段
ERROR 1054 (42S22): Unknown column \'post\' in \'field list\' (5) order by 查詢順序
按單列排序
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序，如果年紀相同，則按照id降序
SELECT * from employee
ORDER BY age ASC,
id DESC; 驗證多列排序：
SELECT * from employee ORDER BY age ASC,id DESC;
mysql> SELECT * from employee ORDER BY age ASC,id DESC;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 18 | d | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 17 | c | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | b | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | a | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 7 | jerry | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 6 | steve | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 1 | jack | male | 18 | 2017-03-01 | 辦事處 | NULL | 7300.33| 401 | 1 |
| 14 | a | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 5 | nezha | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 4 | ben | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 3 | wusir | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
rows in set (0.01 sec)
mysql> (6) limit 限制查詢的記錄數
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #預設初始位置為0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #從第0開始，即先查詢出第一條，然後包含這一條在内往後查5條
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #從第5開始，即先查詢出第6條，然後包含這一條在内往後查5條 # 第1頁資料
mysql> select * from employee limit 0,5;
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | jack | male | 18 | 2017-03-01 | 河辦事 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 3 | wusir i | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | ben | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | nezha | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
rows in set (0.00 sec)
# 第2頁資料
mysql> select * from employee limit 5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | steve | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jerry | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
rows in set (0.00 sec)
# 第3頁資料
mysql> select * from employee limit 10,5;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | a | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | b | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
rows in set (0.00 sec) 七. 多表查詢
準備兩張表，部門表（department）、員工表（employee）
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum(\'male\',\'female\') not null default \'male\',
age int,
dep_id int
);
#插入資料
insert into department values
(200,\'技術\'),
(201,\'人力資源\'),
(202,\'銷售\'),
(203,\'營運\');
insert into employee(name,sex,age,dep_id) values
(\'egon\',\'male\',18,200),
(\'alex\',\'female\',48,201),
(\'wupeiqi\',\'male\',38,201),
(\'yuanhao\',\'female\',28,202),
(\'nvshen\',\'male\',18,200),
(\'xiaomage\',\'female\',18,204)
;
# 檢視表結構和資料
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
rows in set (0.19 sec)
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum(\'male\',\'female\') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
rows in set (0.01 sec)
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技術 |
| 201 | 人力資源 |
| 202 | 銷售 |
| 203 | 營運 |
+------+--------------+
rows in set (0.02 sec)
mysql> select * from employee;
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | a | male | 18 | 200 |
| 2 | b | female | 48 | 201 |
| 3 | c | male | 38 | 201 |
| 4 | d | female | 28 | 202 |
| 5 | e | male | 18 | 200 |
| 6 | f | female | 18 | 204 |
+----+----------+--------+------+--------+
rows in set (0.00 sec) 1. 多表連接配接查詢
外連結文法:
SELECT 字段清單
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段; (1) 交叉連接配接
mysql> select * from employee,department;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | a | male | 18 | 200 | 200 | 技術 |
| 1 | a | male | 18 | 200 | 201 | 人力資源 |
| 1 | a | male | 18 | 200 | 202 | 銷售 |
| 1 | a | male | 18 | 200 | 203 | 營運 |
| 2 | b | female | 48 | 201 | 200 | 技術 |
| 2 | b | female | 48 | 201 | 201 | 人力資源 |
| 2 | b | female | 48 | 201 | 202 | 銷售 |
| 2 | b | female | 48 | 201 | 203 | 營運 |
| 3 | c | male | 38 | 201 | 200 | 技術 |
| 3 | c | male | 38 | 201 | 201 | 人力資源 |
| 3 | c | male | 38 | 201 | 202 | 銷售 |
| 3 | c | male | 38 | 201 | 203 | 營運 |
| 4 | d | female | 28 | 202 | 200 | 技術 |
| 4 | d | female | 28 | 202 | 201 | 人力資源 |
| 4 | d | female | 28 | 202 | 202 | 銷售 |
| 4 | d | female | 28 | 202 | 203 | 營運 |
| 5 | e | male | 18 | 200 | 200 | 技術 |
| 5 | e | male | 18 | 200 | 201 | 人力資源 |
| 5 | e | male | 18 | 200 | 202 | 銷售 |
| 5 | e | male | 18 | 200 | 203 | 營運 |
| 6 | f | female | 18 | 204 | 200 | 技術 |
| 6 | f | female | 18 | 204 | 201 | 人力資源 |
| 6 | f | female | 18 | 204 | 202 | 銷售 |
| 6 | f | female | 18 | 204 | 203 | 營運 | (2) 内連接配接：隻連接配接比對的行
#找兩張表共有的部分，相當于利用條件從笛卡爾積結果中篩選出了比對的結果
#department沒有204這個部門，因而employee表中關于204這條員工資訊沒有比對出來
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+---------+------+--------+--------------+
| id | name | age | sex | name |
+----+---------+------+--------+--------------+
| 1 | a | 18 | male | 技術 |
| 2 | b | 48 | female | 人力資源 |
| 3 | c | 38 | male | 人力資源 |
| 4 | d | 28 | female | 銷售 |
| 5 | e | 18 | male | 技術 |
+----+---------+------+--------+--------------+
rows in set (0.00 sec)
#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id; (3) 外連結之左連接配接：優先顯示左表全部記錄
#以左表為準，即找出所有員工資訊，當然包括沒有部門的員工
#本質就是：在内連接配接的基礎上增加左邊有，右邊沒有的結果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+----------+--------------+
| id | name | depart_name |
+----+----------+--------------+
| 1 | a | 技術 |
| 5 | e | 技術 |
| 2 | b | 人力資源 |
| 3 | c | 人力資源 |
| 4 | d | 銷售 |
| 6 | f | NULL |
+----+----------+--------------+
rows in set (0.00 sec) (4) 外連結之左連接配接：優先顯示左表全部記錄
#以右表為準，即找出所有部門資訊，包括沒有員工的部門
#本質就是：在内連接配接的基礎上增加右邊有，左邊沒有的結果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+---------+--------------+
| id | name | depart_name |
+------+---------+--------------+
| 1 | a | 技術 |
| 2 | b | 人力資源 |
| 3 | c | 人力資源 |
| 4 | d | 銷售 |
| 5 | e | 技術 |
| NULL | NULL | 營運 |
+------+---------+--------------+
rows in set (0.00 sec) (5) 全外連接配接：顯示左右兩個表全部記錄
#外連接配接：在内連接配接的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果
#注意：mysql不支援全外連接配接 full JOIN
#強調：mysql可以使用此種方式間接實作全外連接配接
文法：select * from employee left join department on employee.dep_id = department.id
union all
select * from employee right join department on employee.dep_id = department.id;
mysql> select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | a | male | 18 | 200 | 200 | 技術 |
| 5 | b | male | 18 | 200 | 200 | 技術 |
| 2 | c | female | 48 | 201 | 201 | 人力資源 |
| 3 | d | male | 38 | 201 | 201 | 人力資源 |
| 4 | e | female | 28 | 202 | 202 | 銷售 |
| 6 | f | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 營運 |
+------+----------+--------+------+--------+------+--------------+
rows in set (0.01 sec)
#注意 union與union all的差別：union會去掉相同的紀錄 2.子查詢
#1：子查詢是将一個查詢語句嵌套在另一個查詢語句中。
#2：内層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。
#3：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關鍵字
#4：還可以包含比較運算符：= 、 !=、> 、<等 (1) 帶 in 關鍵字的子查詢
#查詢平均年齡在25歲以上的部門名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
# 檢視技術部員工姓名
select name from employee
where dep_id in
(select id from department where name=\'技術\');
#檢視不足1人的部門名
select name from department
where id not in
(select dep_id from employee group by dep_id); (2) 帶比較運算符的子查詢
#比較運算符：=、!=、>、>=、<、<=、<>
#查詢大于所有人平均年齡的員工名與年齡
mysql> select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| name | age |
+---------+------+
| a | 48 |
| c | 38 |
+---------+------+
#查詢大于部門内平均年齡的員工名、年齡
思路：
（1）先對員工表(employee)中的人員分組(group by)，查詢出dep_id以及平均年齡。
(2)将查出的結果作為臨時表,再對根據臨時表的dep_id和employee的dep_id作為篩選條件将employee表和臨時表進行内連接配接。
(3)最後再将employee員工的年齡是大于平均年齡的員工名字和年齡篩選。
mysql> select t1.name,t1.age from employee as t1
inner join
(select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
+------+------+
| name | age |
+------+------+
| b | 48 | (3) 帶EXISTS關鍵字的子查詢
#EXISTS關字鍵字表示存在。在使用EXISTS關鍵字時，内層查詢語句不傳回查詢的記錄。而是傳回一個真假值。True或False
#當傳回True時，外層查詢語句将進行查詢；當傳回值為False時，外層查詢語句不進行查詢
#department表中存在dept_id=203，Ture
mysql> select * from employee where exists (select id from department where id=200);
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | a | male | 18 | 200 |
| 2 | b | female | 48 | 201 |
| 3 | c | male | 38 | 201 |
| 4 | d | female | 28 | 202 |
| 5 | e | male | 18 | 200 |
| 6 | f | female | 18 | 204 |
+----+----------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee where exists (select id from department where id=204);
Empty set (0.00 sec) 八. 索引
1. 索引
資料庫中專門用于幫助使用者快速查找資料的一種資料結構. 類似于字典中的目錄, 查找字典内容時可以根據目錄查找到資料的存放位置, 然後直接擷取. 索引的作用是限制和查找.
(1) 建索引的目的:
a.額外的檔案儲存特殊的資料結構
b.查詢快，但是插入更新删除依然慢
c.建立索引之後，必須命中索引才能有效 (2) 索引的種類
hash索引和BTree索引
（1）hash類型的索引：查詢單條快，範圍查詢慢
（2）btree類型的索引：b+樹，層數越多，資料量指數級增長（我們就用它，因為innodb預設支援它） 2. 常見的索引
- 普通索引
- 唯一索引
- 主鍵索引
- 聯合索引（多列）
- 聯合主鍵索引
- 聯合唯一索引
- 聯合普通索引
3. 普通索引
作用：僅有一個加速查找
建立表
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_name(name) # 建立普通索引
);
建立普通索引
create index 索引的名字 on 表名(列名)
删除索引
drop index 索引的名字 on 表名
檢視索引
show index from 表名 4. 唯一索引
唯一索引有兩個功能：加速查找和唯一限制（可含null）
建立表+唯一索引
create table userinfo(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
unique index ix_name(name)
); 建立唯一索引
create unique index 索引名 on 表名(列名)
删除唯一索引
drop index 索引名 on 表名; 5. 主鍵索引
主鍵索引有兩個功能： 加速查找和唯一限制（不含null）
3 建立表+主鍵索引
create table userinfo(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
unique index ix_name(name)
)
or
create table userinfo(
id int not null auto_increment,
name varchar(32) not null,
email varchar(64) not null,
primary key(nid),
unique index ix_name(name)
)
建立主鍵索引
alter table 表名 add primary key(列名);
删除主鍵索引
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key; 6. 組合索引
組合索引是将n個列組合成一個索引, 其應用場景為：頻繁的同時使用n列來進行查詢，
建立組合索引
create index 索引名 on 表名(列名1,列名2); 7. 索引的注意事項
(1)避免使用select *
(2)count(1)或count(列) 代替count(*)
(3)建立表時盡量使用char代替varchar
(4)表的字段順序固定長度的字段優先
(5)組合索引代替多個單列索引（經常使用多個條件查詢時）
(6)盡量使用短索引 （create index ix_title on tb(title(16));特殊的資料類型 text類型）
(7)使用連接配接（join）來代替子查詢
(8)連表時注意條件類型需一緻
(9)索引散列（重複少）不适用于建索引，例如：性别不合适