MySQL的搭設(shè)和基本的增刪改操作

MySQL
構(gòu)建MySQL服務(wù)器---->CPU，內(nèi)存，硬盤（存儲(chǔ)）
一、安裝MySQL
yum -y install perl-Data-Dumper perl-JSON perl-Time-HiRes //安裝依賴文件
mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-5.7.17-1.el7.x86_64.rpm
mysql-community-common-5.7.17-1.el7.x86_64.rpm
mysql-community-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-5.7.17-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
mysql-community-server-5.7.17-1.el7.x86_64.rpm
mysql-community-test-5.7.17-1.el7.x86_64.rpm
rpm -Uvh mysql-community-*.rpm //源碼包安裝
rpm -qa |grep -i mysql //查看安裝玩后狀態(tài)
查看配置文件
ls /etc/my.cnf
啟動(dòng)服務(wù)
systemctl status mysqld
systemctl status mysqld
ps -C mysqld
netstat -utnalp |grep :3306

數(shù)據(jù)目錄
ls /var/lib/mysql/

grep mysql /etc/passwd 查看所有者所屬組
修改表

修改MySQL密碼
#ls /var/log/mysqld.log
#grep password /var/log/mysqld.log查看本地?cái)?shù)據(jù)庫的初始化密碼
#rpm -qf /usr/bin/mysql
#mysql -hlocalhost -uroot -p‘密碼’
mysql> set global validate_password_policy=0; //修改密碼只驗(yàn)證長度
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=6; //修改密碼長度為6
Query OK, 0 rows affected (0.00 sec)
mysql> alter user user() identified by "123456" //修改登陸密碼

使用永久配置文件
vim /etc/my.cnf
validate_password_policy=0
validate_password_length=6

把數(shù)據(jù)存儲(chǔ)到數(shù)據(jù)庫服務(wù)器上的過程
1、連接數(shù)據(jù)庫服務(wù)器
客戶端自己提供連接工具（圖形 命令行）
--命令行使用mysql
2、創(chuàng)建庫（文件夾）
創(chuàng)建庫
--庫名可用數(shù)字，字母，下劃線
--不能是純數(shù)字，關(guān)鍵詞，特殊符號(hào)
create database 庫名；
查看已有庫
show databases；
刪除庫
drop database 庫名；
切換庫
use 庫名；
查看庫里已有的表
show tables； //表，相當(dāng)于系統(tǒng)文件
查看當(dāng)前所在的庫
select database（）；

3、建表（文件）
create table 庫名.表名（
字段名 字符類型，
字段名 數(shù)值類型，
...... name char（10）
...... age int
）；
插入表記錄
insert into 庫名.表名 values（值列表）；

查看表結(jié)構(gòu)
describe 表名
查看表記錄
select * from 庫名.表名
刪除表記錄
delete from 庫名.表名
刪除表
drop table

mysql數(shù)據(jù)類型
支持的數(shù)據(jù)類型有那些？
--數(shù)值型：體重、身高、成績、工資
--字符型：姓名、工作單位、通信地址
--枚舉型：興趣愛好、性別
--日期時(shí)間型：出生日期、注冊(cè)時(shí)間

數(shù)值類型：整型、浮點(diǎn)型
根據(jù)存儲(chǔ)數(shù)值的范圍整型類型為:
tinyint smallint mediumint int bigint
unsigned 無符號(hào)

浮點(diǎn)型：根據(jù)存儲(chǔ)數(shù)值的范圍分為
單精度（n，m） 雙精度（n，m）
n表示總位數(shù)
m表示小數(shù)位的位數(shù)
pay float（5，2）
最大999.99
最小-999.99

mysql> create table t1(id tinyint unsigned zerofill);
Query OK, 0 rows affected (0.40 sec)
mysql> desc t1;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table t2(pay float(7,2));
Query OK, 0 rows affected (0.46 sec)

mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| pay | float(7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table t4(
-> age float(7,2),
-> high float(3,2)
-> );
Query OK, 0 rows affected (0.36 sec)

mysql> desc t4;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| age | float(7,2) | YES | | NULL | |
| high | float(3,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
insert into t4 values（11211，1.82）;
###########################################################
字符類型
--定長:char(字符數(shù))
最大長度255字符
不夠指定字符數(shù)時(shí)再右邊用空格補(bǔ)齊
字符數(shù)超出時(shí)，無法寫入數(shù)據(jù)
--varchar（字符數(shù)）
按數(shù)據(jù)實(shí)際大小分配存儲(chǔ)空間
字符數(shù)超出時(shí)，無法寫入數(shù)據(jù)
--大文本類型：text/blob
字符數(shù)大與65535存儲(chǔ)時(shí)使用
mysql> create table t8(
-> name char(10),
-> class char(7),
-> address char(15),
-> mail varchar(30)
-> );
mysql> desc t8;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| class | char(7) | YES | | NULL | |
| address | char(15) | YES | | NULL | |
| mail | varchar(30) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into t8 values("jim","nsd1709","beijing","123456@qq.com")
Query OK, 1 row affected (0.04 sec)

mysql> select * from t8;
+------+---------+---------+---------------+
| name | class | address | mail |
+------+---------+---------+---------------+
| jim | nsd1709 | beijing | 123456@qq.com |
+------+---------+---------+---------------+
1 row in set (0.00 sec)
####################################################################
日期時(shí)間類型：
年 year YYYY 2017
日期 date YYYYMMDD 20171220
時(shí)間 time HHMMSS 155302
日期時(shí)間：
datetime YYYYMMDDHHMMSS
timestamp YYYYMMDDHHMMSS

mysql> create table t9(
-> name char(10),
-> age tinyint,
-> s_year year,
-> uptime time,
-> birthday date,
-> party datetime
-> );
Query OK, 0 rows affected (0.37 sec)

mysql> desc t9;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| s_year | year(4) | YES | | NULL | |
| uptime | time | YES | | NULL | |
| birthday | date | YES | | NULL | |
| party | datetime | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> insert into t9 values("Tom",24,1992,073000,19920221122020,20180131122100);
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> select * from t9;
+------+------+--------+----------+------------+---------------------+
| name | age | s_year | uptime | birthday | party |
+------+------+--------+----------+------------+---------------------+
| Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |
+------+------+--------+----------+------------+---------------------+
1 row in set (0.00 sec)
####################################################
時(shí)間函數(shù)
now（） 獲取調(diào)用次函數(shù)時(shí)的系統(tǒng)日期時(shí)間
sysdate（） 執(zhí)行時(shí)動(dòng)態(tài)獲得系統(tǒng)日期時(shí)間
sleep（N） 休眠N秒
curdate（） 獲得當(dāng)前的系統(tǒng)日期
curtime（） 獲得當(dāng)前的系統(tǒng)時(shí)刻
month（） 獲得指定時(shí)間中的月份
date（） 獲得指定時(shí)間中的日期
time（） 獲取指定時(shí)間中的時(shí)刻

mysql> select from t9;
+-------+------+--------+----------+------------+---------------------+
| name | age | s_year | uptime | birthday | party |
+-------+------+--------+----------+------------+---------------------+
| Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |
| Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 |
+-------+------+--------+----------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> insert into t9 values("kenji",19,year(now()),time(now()),date(now()),now());
Query OK, 1 row affected (0.04 sec)
mysql> select from t9;
+-------+------+--------+----------+------------+---------------------+
| name | age | s_year | uptime | birthday | party |
+-------+------+--------+----------+------------+---------------------+
| Tom | 24 | 1992 | 07:30:00 | 1992-02-21 | 2018-01-31 12:21:00 |
| Jerry | 25 | 1991 | 06:50:55 | 1991-08-19 | 2018-01-31 12:21:00 |
| kenji | 19 | 2017 | 03:55:12 | 2017-12-20 | 2017-12-20 03:55:12 |
+-------+------+--------+----------+------------+---------------------+
3 rows in set (0.00 sec)
###########################################################
枚舉類型：字段的值只能在列表的范圍內(nèi)選擇
字段名 enum（值列表） 只能選擇一個(gè)值,在賦值時(shí)可用數(shù)字選擇。
字段名 set（值列表） 多選

mysql> create table t12( name char(10), sex enum("boy","girl"), yourlikes set("book","film","game","study") );
Query OK, 0 rows affected (0.43 sec)

mysql> desc t12;
+-----------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
| yourlikes | set('book','film','game','study') | YES | | NULL | |
+-----------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t12 values("ZhouMing","boy","book,film");
Query OK, 1 row affected (0.04 sec)

mysql> select * from t12;
+----------+------+-----------+
| name | sex | yourlikes |
+----------+------+-----------+
| ZhouMing | boy | book,film |
+----------+------+-----------+
1 row in set (0.00 sec)
##############################################################
約束條件:作用限制賦值
--Null 允許為空，默認(rèn)設(shè)置
--NO NULL 不允許為空
Key 索引類型
Default 設(shè)置默認(rèn)值，缺省為NULL

mysql> create table t13( name char(10) not null, sex enum('man','woman') not null default "man", age tinyint not null default 23 );
Query OK, 0 rows affected (0.37 sec)

mysql> desc t13;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name | char(10) | NO | | NULL | |
| sex | enum('man','woman') | NO | | man | |
| age | tinyint(4) | NO | | 23 | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t13(name) values("chihiro");
Query OK, 1 row affected (0.04 sec)

mysql> select * from t13;
+---------+-----+-----+
| name | sex | age |
+---------+-----+-----+
| chihiro | man | 23 |
+---------+-----+-----+
1 row in set (0.00 sec)
#######################################################
修改表結(jié)構(gòu)
mysql> alter table 表名 執(zhí)行動(dòng)作；

添加新字段
-add 字段（寬度）約束條件；
-add 字段（寬度）約束條件 first； //添加至表的最前面
-add 字段（寬度）約束條件 after 字段名； // 添加至指定字段名的后面

刪除字段
-drop 字段名；

修改字段類型
-modify 字段 類型（寬度） 約束條件； //不可修改為與已有值沖突的類型

修改字段名
-change 源字段名 新字段名 類型（寬度） 約束條件；

修改表名
alter table 源表名 rename 新表名

MySQL

數(shù)字類型的寬度是顯示寬度，不能夠限制給字段賦值的大小，大小由類型決定。

mysql> create table t21(
-> name char(5),
-> age int(2)
-> );
mysql> insert into t21 values("coco",1992);
mysql> select * from t21;
+------+------+
| name | age |
+------+------+
| coco | 1992 |
+------+------+
1 row in set (0.00 sec)
寬度不能限制字段大小，類型決定?？晒?jié)約存儲(chǔ)空間，age（2）寬度是2，但賦值能大于2

mysql> create table t24(id int(2) zerofill,age int(5) zerofill);
設(shè)定寬度，不夠用0填充。（zerofii用0補(bǔ)位）
mysql> insert into t24 values(7,7);
mysql> select * from t24;
+------+-------+
| id | age |
+------+-------+
| 07 | 00007 |
+------+-------+
1 row in set (0.00 sec)

#################################################################
一、mysql鍵值（限制如何給字段賦值）
1.1 普通索引index
1.1.1 什么是索引？ 類似與“書的目錄”樹型目錄結(jié)構(gòu)
eg：500頁----->目錄信息1-20----->正文21-500

1.1.2索引的優(yōu)點(diǎn)
加快查詢的速度

1.1.2索引的缺點(diǎn)
減慢寫的速度（insert update delete）
占用物理存儲(chǔ)空間

1.1.3使用普通索引index
-索引使用規(guī)則
字段的值允許重復(fù)，可以賦NULL值
INDEX字段的KEY標(biāo)志是MUL

-查看索引
desc 表名；
show index from 表名； //查看索引信息的具體值
創(chuàng)建索引
默認(rèn)使用的索引類型：BTREE（二叉樹） 1-10 1-5 6-10 hash B+Tree
create index 索引名 on 表名（字段名）；
1）建表時(shí)創(chuàng)建索引
mysql> create table t25(
-> name char(10),
-> age int,
-> sex enum("boy","girl"),
-> index(sex),
-> index(name)
-> );
mysql> desc t25;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| name | char(10) | YES | MUL | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('boy','girl') | YES | MUL | NULL | |
+-------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> create index name on t21(name); //一般習(xí)慣將索引名與字段名相同
Query OK, 0 rows affected (0.42 sec)
2）在已有表創(chuàng)建索引
mysql> desc t21;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(5) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create index name on t21(name);
Query OK, 0 rows affected (0.42 sec)
ysql> desc t21;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name | char(5) | YES | MUL | NULL | |
| age | int(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show index from t21\G
1. row
Table: t21
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)

刪除
drop index 索引名 on 表名；
###########################################################
primary key主鍵
注意事項(xiàng)
-一個(gè)表中只能有一個(gè)primary key字段
-對(duì)應(yīng)的字段值不允許有重復(fù)，且不允許賦NULL值
-如果有多個(gè)字段都作為PRIMARY KEY，稱為復(fù)合主鍵，必須一起創(chuàng)建
-主鍵字段的KEY標(biāo)志是PRI
通常與AUTO INCREMENT連用
-經(jīng)常把表中能夠唯一標(biāo)識(shí)記錄的字段設(shè)置為主鍵字段【記錄編號(hào)字段】
1)已有表設(shè)主鍵
mysql> drop index name on t25;
mysql> desc t25;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | enum('boy','girl') | YES | MUL | NULL | |
+-------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from t25;
Empty set (0.00 sec)
mysql> alter table t25 add primary key(name); //只能有一個(gè)主鍵
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t25 drop primary key; //刪除主鍵
2）新建表，設(shè)主鍵
mysql> create table t26(
-> name char(10),
-> age int,
-> likes set("a","b","c"),
-> primary key(name)
-> );
mysql> desc t26;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name | char(10) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| likes | set('a','b','c') | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
建表時(shí)上例主建加在中間也可以
eg：name char（10） primary key，
效果與t26相同
3）復(fù)合主鍵：多個(gè)字段一起做主鍵，字段不允許同時(shí)重復(fù)
mysql> create table t28( cip char(15), port smallint, status enum("allow","deny") default "deny", primary key(cip,port) );
Query OK, 0 rows affected (0.31 sec)

mysql> desc t28;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip | char(15) | NO | PRI | NULL | |
| port | smallint(6) | NO | PRI | NULL | |
| status | enum('allow','deny') | YES | | deny | |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t28 drop primary key; //刪除主鍵
mysql> alter table t28 add primary key(cip,port); //添加主鍵

4）與auto_increment 連用
字段值自動(dòng)增長
eg： id name age class
jim 21 1709
讓id字段的值自動(dòng)增長 +1
條件：主鍵并且是數(shù)字
mysql> create table t29(
-> id int(2) zerofill primary key auto_increment,
-> name char(10),
-> class char(10),
-> index(name)
-> );
Query OK, 0 rows affected (0.22 sec)

mysql> desc t29;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+----------------+
| id | int(2) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | MUL | NULL | |
| class | char(10) | YES | | NULL | |
+-------+--------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into t29(name,class) values("tom","1709");
mysql> insert into t29(name,class) values("jerry","1709");
mysql> insert into t29 values(9,"jack","1709"); //可自己賦值id，但id屬于主鍵，不能同名
mysql> insert into t29(name,class) values("rose","1709"); //自動(dòng)增長會(huì)選擇數(shù)字最大的值進(jìn)行自動(dòng)增長，之前設(shè)置id=9，再開啟自動(dòng)增長則為10
mysql> select * from t29;
+----+-------+-------+
| id | name | class |
+----+-------+-------+
| 01 | tom | 1709 |
| 02 | jerry | 1709 |
| 09 | jack | 1709 |
| 10 | rose | 1709 |
+----+-------+-------+
4 rows in set (0.00 sec)
mysql> alter table t29 drop primary key; //無法刪除主鍵，因?yàn)閕d設(shè)置為auto_increment自動(dòng)增長，該命令必須是主鍵才可設(shè)立。
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> alter table t29 modify id int; //修改字段類型，取消自動(dòng)增長
mysql> alter table t29 drop primary key; //刪除主鍵成功
Query OK, 4 rows affected (1.00 sec)
##########################################################
UNIQUE唯一索引
唯一索引不可賦相同值 ，可以為NULL
1）建表的時(shí)候指定UNIQUE字段
mysql> create table t211( stu_id char(9), name char(10), sex enum("boy","girl"), unique(stu_id) ); 指定學(xué)號(hào)為唯一索引
mysql> desc t211;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| stu_id | char(9) | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#########################################################
mysql> create table t212( stu_id char(9) not NULL, name char(10), sex enum("boy","girl"), unique(stu_id) );
Query OK, 0 rows affected (0.26 sec)
//指定stu_id為唯一索引，但不允許它為空值，則描述信息顯示stu_id為RPI，但實(shí)際上主鍵不存在也無法刪除
mysql> desc t212;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| stu_id | char(9) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
| sex | enum('boy','girl') | YES | | NULL | |
+--------+--------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#######################################
練習(xí)
mysql> desc stuinfo
+-------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-----------------------------+
| name | varchar(15) | YES | | NULL | |
| class | char(7) | YES | | NULL | |
| party | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
alter table stuinfo add stu_id char(7) first
create ubique index stu_id on stuinfo(name)
alter table stuinfo add id int(2) zerofill primary key auto_increment;
###############################################################
外鍵
foreign key（字段名） references 表名（字段名）on uptate cascade on delete cascade 同步更新，同步刪除
作用：限制給字段賦值的，值必須在指定表中指定字段值的范圍里選擇
mysql> create table jfb(
-> id int(2) primary key auto_increment,
-> name char(10),
-> pay float(7,2)
-> )engine=innodb;
建立參照表
insert into jfb(name,pay)values("tom",20000),("lucy",20000);
Query OK, 2 rows affected (0.07 sec)

mysql> create table xsb( num int(2), name char(10), class char(9), foreign key(num) references jfb(id) on update cascade on delete cascade )engine=innodb;

同步修改
update 表名 set 字段名=值 where 條件； //條件就是原有的（字段名=值）
同步刪除
delete from 表名 where 條件；
被參考的表不能隨意刪除

刪除外鍵字段
show create table 表名；
alter table 表名 drop foreign key 外鍵名；

eg：

本案例要求熟悉MySQL索引的類型及操作方法，主要練習(xí)以下任務(wù)：
普通索引、唯一索引、主鍵索引的創(chuàng)建/刪除
自增主鍵索引的創(chuàng)建/刪除
建立員工表yg、工資表gz，設(shè)置外鍵實(shí)現(xiàn)同步更新與同步刪除
實(shí)現(xiàn)此案例需要按照如下步驟進(jìn)行。
步驟一：索引的創(chuàng)建與刪除

創(chuàng)建表的時(shí)候指定INDEX索引字段
創(chuàng)建庫home：
mysql> create database home;
Query OK, 1 row affected (0.00 sec)
允許有多個(gè)INDEX索引字段。比如，以下操作在home庫中創(chuàng)建了tea4表，將其中的id、name作為索引字段：
mysql> USE home;
Database changed
mysql> CREATE TABLE tea4(
-> id char(6) NOT NULL,
-> name varchar(6) NOT NULL,
-> age int(3) NOT NULL,
-> gender ENUM('boy','girl') DEFAULT 'boy',
-> INDEX(id),INDEX(name)
-> );
Query OK, 0 rows affected (0.59 sec)
查看新建tea4表的字段結(jié)構(gòu)，可以發(fā)現(xiàn)兩個(gè)非空索引字段的KEY標(biāo)志為MUL：
mysql> DESC tea4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | NO | MUL | NULL | |
| name | varchar(6) | NO | MUL | NULL | |
| age | int(3) | NO | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2）刪除現(xiàn)有表的某個(gè)INDEX索引字段
比如，刪除tea4表中名稱為named的INDEX索引字段：
mysql> drop INDEX name ON tea4; //刪除name字段的索引
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea4; //確認(rèn)刪除結(jié)果
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | NO | MUL | NULL | |
| name | varchar(6) | NO | | NULL | |
| age | int(3) | NO | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3）在已有的某個(gè)表中設(shè)置INDEX索引字段
比如，針對(duì)tea4表的age字段建立索引，名稱為 nianling：
mysql> CREATE INDEX nianling ON tea4(age); //針對(duì)指定字段創(chuàng)建索引
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea4; //確認(rèn)創(chuàng)建結(jié)果
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | NO | MUL | NULL | |
| name | varchar(6) | NO | | NULL | |
| age | int(3) | NO | MUL | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4）查看指定表的索引信息
使用SHOW INDEX 指令：
mysql> SHOW INDEX FROM tea4\G
1. row
Table: tea4
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE //使用B樹算法
Comment:
Index_comment:
2. row
Table: tea4
Non_unique: 1
Key_name: nianling //索引名稱
Seq_in_index: 1
Column_name: age //字段名稱
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
5）創(chuàng)建表的時(shí)候指定UNIQUE索引字段
UNIQUE表示唯一性的意思，同一個(gè)表中可以有多個(gè)字段具有唯一性。
比如，創(chuàng)建tea5表，將id、name字段建立設(shè)置UNIQUE索引，age字段設(shè)置INDEX索引：
mysql> CREATE TABLE tea5(
-> id char(6),
-> name varchar(4) NOT NULL,
-> age int(3) NOT NULL,
-> UNIQUE(id),UNIQUE(name),INDEX(age)
-> );
Query OK, 0 rows affected (0.30 sec)
查看新建tea5表的字段結(jié)構(gòu)，可發(fā)現(xiàn)UNIQUE字段的KEY標(biāo)志為UNI；另外，由于字段name必須滿足“NOT NULL”的非空約束，所以將其設(shè)置為UNIQUE后會(huì)自動(dòng)變成了PRIMARY KEY主鍵字段：
mysql> DESC tea5; //確認(rèn)設(shè)置結(jié)果
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | char(6) | YES | UNI | NULL | |
| name | varchar(4) | NO | PRI | NULL | |
| age | int(3) | NO | MUL | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
6）刪除UNIQUE索引、在已有的表中設(shè)置UNIQUE索引字段
先刪除tea5表name字段的唯一索引（與刪除INDEX索引的方法相同）：
mysql> DROP INDEX name ON tea5; //清除UNIQUE索引
Query OK, 0 rows affected (0.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea5; //確認(rèn)刪除結(jié)果
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | char(6) | YES | UNI | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(3) | NO | MUL | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
重新為tea5表的name字段建立UNIQUE索引，并確認(rèn)結(jié)果：
mysql> CREATE UNIQUE INDEX name ON tea5(name); //建立UNIQUE索引
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea5; //確認(rèn)設(shè)置結(jié)果
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | char(6) | YES | UNI | NULL | |
| name | varchar(4) | NO | PRI | NULL | |
| age | int(3) | NO | MUL | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
7）建表時(shí)設(shè)置PRIMARY KEY主鍵索引
主鍵索引實(shí)際上在前面已經(jīng)接觸過了，建表的時(shí)候可以直接指定。如果表內(nèi)一開始沒有主鍵字段，則新設(shè)置的非空UNIQUE字段相當(dāng)于具有PRIMARY KEY主鍵約束。
每個(gè)表中的主鍵字段只能有一個(gè)。
建表的時(shí)候，可以直接在某個(gè)字段的“約束條件”部分指定PRIMARY KEY；也可以在最后指定PRIMARY KEY(某個(gè)字段名)。比如：
mysql> CREATE TABLE biao01(
-> id int(4) PRIMARY KEY, //直接在字段定義時(shí)約束
-> name varchar(8)
-> );
Query OK, 0 rows affected (0.19 sec)
或者：
mysql> CREATE TABLE biao02(
-> id int(4),
-> name varchar(8),
-> PRIMARY KEY(id) //所有字段定義完，最后指定
-> );
Query OK, 0 rows affected (0.17 sec)
在建表的時(shí)候，如果主鍵字段為int類型，還可以為其設(shè)置AUTO_INCREMENT自增屬性，這樣當(dāng)添加新的表記錄時(shí)，此字段的值會(huì)自動(dòng)從1開始逐個(gè)增加，無需手動(dòng)指定。比如，新建一個(gè)tea6表，將id列作為自增的主鍵字段：
mysql> CREATE TABLE tea6(
-> id int(4) AUTO_INCREMENT,
-> name varchar(4) NOT NULL,
-> age int(2) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.29 sec)
8）刪除現(xiàn)有表的PRIMARY KEY主鍵索引
如果要移除某個(gè)表的PRIMARY KEY約束，需要通過ALTER TABLE指令修改。比如，以下操作將清除biao01表的主鍵索引。
清除前（主鍵為id）：
mysql> DESC biao01;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
清除操作：
mysql> ALTER TABLE biao01 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
清除后（無主鍵）：
mysql> DESC biao01;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
當(dāng)嘗試刪除tea6表的主鍵時(shí)，會(huì)出現(xiàn)異常：
mysql> ALTER TABLE tea6 DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
這是因?yàn)閠ea6表的主鍵字段id具有AUTO_INCREMNET自增屬性，提示這種字段必須作為主鍵存在，因此若要清除此主鍵必須先清除自增屬性——修改id列的字段定義：
mysql> ALTER TABLE tea6 MODIFY id int(4) NOT NULL;
Query OK, 0 rows affected (0.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
然后再清除主鍵屬性就OK了：
mysql> ALTER TABLE tea6 DROP PRIMARY KEY; //清除主鍵
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tea6; //確認(rèn)清除結(jié)果
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
9）為現(xiàn)有表添加PRIMARY KEY主鍵索引
重新為tea6表指定主鍵字段，仍然使用id列：
mysql> ALTER TABLE tea6 ADD PRIMARY KEY(id); //設(shè)置主鍵字段
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea6; //確認(rèn)設(shè)置結(jié)果
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
步驟二：創(chuàng)建數(shù)據(jù)庫并設(shè)置外鍵實(shí)現(xiàn)同步更新與同步刪除

根據(jù)實(shí)驗(yàn)任務(wù)要求，兩個(gè)表格的字段結(jié)構(gòu)如表-1、表-2所示。
1）創(chuàng)建yg表，用來記錄員工工號(hào)、姓名
其中yg_id列作為主鍵，并設(shè)置自增屬性
mysql> CREATE TABLE yg(
-> yg_id int(4) AUTO_INCREMENT,
-> name char(16) NOT NULL,
-> PRIMARY KEY(yg_id)
-> );
Query OK, 0 rows affected (0.15 sec)
2）創(chuàng)建gz表，用來記錄員工的工資信息
其中g(shù)z_id需要參考員工工號(hào)，即gz表的gz_id字段設(shè)為外鍵，將yg表的yg_id字段作為參考鍵：
mysql> CREATE TABLE gz(
-> gz_id int(4) NOT NULL,
-> name char(16) NOT NULL,
-> gz float(7,2) NOT NULL DEFAULT 0,
-> INDEX(name),
-> FOREIGN KEY(gz_id) REFERENCES yg(yg_id)
-> ON UPDATE CASCADE ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.23 sec)
3）為yg表添加2條員工信息記錄
因yg_id有AUTO_INCREMENT屬性，會(huì)自動(dòng)填充，所以只要為name列賦值就可以了。
插入表記錄可使用INSERT指令，這里先執(zhí)行下列操作，具體在下一章學(xué)習(xí)：
mysql> INSERT INTO yg(name) VALUES('Jerry'),('Tom');
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
確認(rèn)yg表的數(shù)據(jù)記錄：
mysql> SELECT FROM yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | Jerry |
| 2 | Tom |
+-------+-------+
2 rows in set (0.00 sec)
4）為gz表添加2條工資信息記錄
同上，數(shù)據(jù)參考圖-2，插入相應(yīng)的工資記錄（gz_id字段未指定默認(rèn)值，也未設(shè)置自增屬性，所以需要手動(dòng)賦值）：
mysql> INSERT INTO gz(gz_id,name,gz)
-> VALUES(1,'Jerry',12000),(2,'Tom',8000)
-> ;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
確認(rèn)gz表的數(shù)據(jù)記錄：
mysql> SELECT FROM gz;
+-------+-------+----------+
| gz_id | name | gz |
+-------+-------+----------+
| 1 | Jerry | 12000.00 |
| 2 | Tom | 8000.00 |
+-------+-------+----------+
2 rows in set (0.05 sec)
5）驗(yàn)證表記錄的UPDATE更新聯(lián)動(dòng)
將yg表中Jerry用戶的yg_id修改為1234：
mysql> update yg SET yg_id=1234 WHERE name='Jerry';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
確認(rèn)修改結(jié)果：
mysql> SELECT FROM yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 2 | Tom |
| 1234 | Jerry |
+-------+-------+
2 rows in set (0.00 sec)
同時(shí)也會(huì)發(fā)現(xiàn)，gz表中Jerry用戶的gz_id也跟著變了：
mysql> SELECT FROM gz;
+-------+-------+----------+
| gz_id | name | gz |
+-------+-------+----------+
| 1234 | Jerry | 12000.00 |
| 2 | Tom | 8000.00 |
+-------+-------+----------+
2 rows in set (0.00 sec)
6）驗(yàn)證表記錄的DELETE刪除聯(lián)動(dòng)
刪除yg表中用戶Jerry的記錄：
mysql> DELETE FROM yg WHERE name='Jerry';
Query OK, 1 row affected (0.05 sec)
確認(rèn)刪除結(jié)果：
mysql> SELECT FROM yg;
+-------+------+
| yg_id | name |
+-------+------+
| 2 | Tom |
+-------+------+
1 row in set (0.00 sec)
查看gz表中的變化（Jerry的記錄也沒了）：
mysql> SELECT FROM gz;
+-------+------+---------+
| gz_id | name | gz |
+-------+------+---------+
| 2 | Tom | 8000.00 |
+-------+------+---------+
1 row in set (0.00 sec)
7）刪除指定表的外鍵約束
先通過SHOW指令獲取表格的外鍵約束名稱：
mysql> SHOW CREATE TABLE gz\G
1. row
Table: gz
Create Table: CREATE TABLE gz (
gz_id int(4) NOT NULL,
name char(16) NOT NULL,
gz float(7,2) NOT NULL DEFAULT '0.00',
KEY name (name),
KEY gz_id (gz_id),
CONSTRAINT gz_ibfk_1 FOREIGN KEY (gz_id) REFERENCES yg (yg_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
其中g(shù)z_ibfk_1即刪除外鍵約束時(shí)要用到的名稱。
刪除操作：
mysql> ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
確認(rèn)刪除結(jié)果：
mysql> SHOW CREATE TABLE gz\G
1. row
Table: gz
Create Table: CREATE TABLE gz (
gz_id int(4) NOT NULL,
name char(16) NOT NULL,
gz float(7,2) NOT NULL DEFAULT '0.00',
KEY name (name),
KEY gz_id (gz_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
##################################################

MySQL存儲(chǔ)引擎

mysql服務(wù)體系結(jié)構(gòu)：（8個(gè)功能模塊）
連接池
sql接口
分析器
優(yōu)化器
查詢緩存
存儲(chǔ)引擎
文件系統(tǒng)
查看數(shù)據(jù)庫服務(wù)支持的存儲(chǔ)引擎
mysql> show engines;

設(shè)置默認(rèn)存儲(chǔ)引擎
vim /etc/my.cnf
[mysqld]
... ...
default-storage-engine=InnoDB
常用的存儲(chǔ)引擎為InnoDB和MyISAM

create table 表名（約束條件） ENGINE=存儲(chǔ)引擎名

常用存儲(chǔ)引擎的特點(diǎn)
MyISAM
表.MYI 索引信息
表.MYD 數(shù)據(jù)
表.frm 表結(jié)構(gòu)
支持表級(jí)鎖（鎖一張表）
不支持事務(wù) 事務(wù)回滾

InnoDB存儲(chǔ)引擎
表.idb 索引信息+數(shù)據(jù)
表.frm 表結(jié)構(gòu)
支持行級(jí)鎖（只給當(dāng)前被訪問的行加鎖）
支持事務(wù) 事務(wù)回滾

MySQL鎖機(jī)制
鎖粒度：表級(jí)鎖、行級(jí)鎖、頁級(jí)鎖
鎖類型：讀鎖（select）和寫鎖（insert delete update）

事務(wù)：訪問數(shù)據(jù)從開始到結(jié)束的過程
事務(wù)回滾：一次訪問過程中，任意一步執(zhí)行失敗，都會(huì)恢復(fù)所有操作
事務(wù)的特性：一致性 原子性 隔離性
事務(wù)日志文件：記錄對(duì)innodb存儲(chǔ)引擎的表執(zhí)行過的操作
工作中如何決定表使用的存儲(chǔ)引擎
接收寫操作多的表適合使用innodb存儲(chǔ)引擎
接收讀操作多的表適合使用myisam存儲(chǔ)引擎