MySQL的搭設和基本的增刪改運作

2024-07-24 12:35:16

字體：大中小

來源：轉載

供稿：網友

　　構建MySQL服務器---->CPU，內存，硬盤（存儲）
　　一、安裝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 //查看安裝玩后狀態
　　查看配置文件
　　ls /etc/my.cnf
　　啟動服務
　　systemctl status mysqld
　　systemctl status mysqld
　　ps -C mysqld
　　netstat -utnalp |grep :3306

　　數據目錄
　　ls /var/lib/mysql/

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

　　修改MySQL密碼
　　#ls /var/log/mysqld.log
　　#grep password /var/log/mysqld.log查看本地數據庫的初始化密碼
　　#rpm -qf /usr/bin/mysql
　　#mysql -hlocalhost -uroot -p‘密碼’
　　mysql> set global validate_password_policy=0; //修改密碼只驗證長度
　　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

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

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

　　查看表結構
　　describe 表名
　　查看表記錄
　　select * from 庫名.表名
　　刪除表記錄
　　delete from 庫名.表名
　　刪除表
　　drop table

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

　　數值類型：整型、浮點型
　　根據存儲數值的范圍整型類型為:
　　tinyint smallint mediumint int bigint
　　unsigned 無符號

　　浮點型：根據存儲數值的范圍分為
　　單精度（n，m）雙精度（n，m）
　　n表示總位數
　　m表示小數位的位數
　　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(字符數)
　　最大長度255字符
　　不夠指定字符數時再右邊用空格補齊
　　字符數超出時，無法寫入數據
　　--varchar（字符數）
　　按數據實際大小分配存儲空間
　　字符數超出時，無法寫入數據
　　--大文本類型：text/blob
　　字符數大與65535存儲時使用
　　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)
　　####################################################################
　　日期時間類型：
　　年 year YYYY 2017
　　日期 date YYYYMMDD 20171220
　　時間 time HHMMSS 155302
　　日期時間：
　　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)
　　####################################################
　　時間函數
　　now（）獲取調用次函數時的系統日期時間
　　sysdate（）執行時動態獲得系統日期時間
　　sleep（N）休眠N秒
　　curdate（）獲得當前的系統日期
　　curtime（）獲得當前的系統時刻
　　month（）獲得指定時間中的月份
　　date（）獲得指定時間中的日期
　　time（）獲取指定時間中的時刻

　　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)
　　###########################################################
　　枚舉類型：字段的值只能在列表的范圍內選擇
　　字段名 enum（值列表）只能選擇一個值,在賦值時可用數字選擇。
　　字段名 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 允許為空，默認設置
　　--NO NULL 不允許為空
　　Key 索引類型
　　Default 設置默認值，缺省為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)
　　#######################################################
　　修改表結構
　　mysql> alter table 表名執行動作；

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

　　刪除字段
　　-drop 字段名；

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

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

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

　　MySQL

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

　　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)
　　寬度不能限制字段大小，類型決定。可節約存儲空間，age（2）寬度是2，但賦值能大于2

　　mysql> create table t24(id int(2) zerofill,age int(5) zerofill);
　　設定寬度，不夠用0填充。（zerofii用0補位）
　　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 什么是索引？類似與“書的目錄”樹型目錄結構
　　eg：500頁----->目錄信息1-20----->正文21-500

　　1.1.2索引的優點
　　加快查詢的速度

　　1.1.2索引的缺點
　　減慢寫的速度（insert update delete）
　　占用物理存儲空間

　　1.1.3使用普通索引index
　　-索引使用規則
　　字段的值允許重復，可以賦NULL值
　　INDEX字段的KEY標志是MUL

　　-查看索引
　　desc 表名；
　　show index from 表名； //查看索引信息的具體值
　　創建索引
　　默認使用的索引類型：BTREE（二叉樹） 1-10 1-5 6-10 hash B+Tree
　　create index 索引名 on 表名（字段名）；
　　1）建表時創建索引
　　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); //一般習慣將索引名與字段名相同
　　Query OK, 0 rows affected (0.42 sec)
　　2）在已有表創建索引
　　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主鍵
　　注意事項
　　-一個表中只能有一個primary key字段
　　-對應的字段值不允許有重復，且不允許賦NULL值
　　-如果有多個字段都作為PRIMARY KEY，稱為復合主鍵，必須一起創建
　　-主鍵字段的KEY標志是PRI
　　通常與AUTO INCREMENT連用
　　-經常把表中能夠唯一標識記錄的字段設置為主鍵字段【記錄編號字段】
　　1)已有表設主鍵
　　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); //只能有一個主鍵
　　Query OK, 0 rows affected (0.47 sec)
　　Records: 0 Duplicates: 0 Warnings: 0
　　mysql> alter table t25 drop primary key; //刪除主鍵
　　2）新建表，設主鍵
　　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)
　　建表時上例主建加在中間也可以
　　eg：name char（10） primary key，
　　效果與t26相同
　　3）復合主鍵：多個字段一起做主鍵，字段不允許同時重復
　　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 連用
　　字段值自動增長
　　eg： id name age class
　　jim 21 1709
　　讓id字段的值自動增長 +1
　　條件：主鍵并且是數字
　　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"); //自動增長會選擇數字最大的值進行自動增長，之前設置id=9，再開啟自動增長則為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; //無法刪除主鍵，因為id設置為auto_increment自動增長，該命令必須是主鍵才可設立。
　　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; //修改字段類型，取消自動增長
　　mysql> alter table t29 drop primary key; //刪除主鍵成功
　　Query OK, 4 rows affected (1.00 sec)
　　##########################################################
　　UNIQUE唯一索引
　　唯一索引不可賦相同值，可以為NULL
　　1）建表的時候指定UNIQUE字段
　　mysql> create table t211( stu_id char(9), name char(10), sex enum("boy","girl"), unique(stu_id) ); 指定學號為唯一索引
　　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，但實際上主鍵不存在也無法刪除
　　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)
　　#######################################
　　練習
　　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索引的類型及操作方法，主要練習以下任務：
　　普通索引、唯一索引、主鍵索引的創建/刪除
　　自增主鍵索引的創建/刪除
　　建立員工表yg、工資表gz，設置外鍵實現同步更新與同步刪除
　　實現此案例需要按照如下步驟進行。
　　步驟一：索引的創建與刪除

　　創建表的時候指定INDEX索引字段
　　創建庫home：
　　mysql> create database home;
　　Query OK, 1 row affected (0.00 sec)
　　允許有多個INDEX索引字段。比如，以下操作在home庫中創建了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表的字段結構，可以發現兩個非空索引字段的KEY標志為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）刪除現有表的某個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; //確認刪除結果
　　+--------+--------------------+------+-----+---------+-------+
　　| 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）在已有的某個表中設置INDEX索引字段
　　比如，針對tea4表的age字段建立索引，名稱為 nianling：
　　mysql> CREATE INDEX nianling ON tea4(age); //針對指定字段創建索引
　　Query OK, 0 rows affected (0.62 sec)
　　Records: 0 Duplicates: 0 Warnings: 0
　　mysql> DESC tea4; //確認創建結果
　　+--------+--------------------+------+-----+---------+-------+
　　| 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）創建表的時候指定UNIQUE索引字段
　　UNIQUE表示唯一性的意思，同一個表中可以有多個字段具有唯一性。
　　比如，創建tea5表，將id、name字段建立設置UNIQUE索引，age字段設置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表的字段結構，可發現UNIQUE字段的KEY標志為UNI；另外，由于字段name必須滿足“NOT NULL”的非空約束，所以將其設置為UNIQUE后會自動變成了PRIMARY KEY主鍵字段：
　　mysql> DESC tea5; //確認設置結果
　　+-------+------------+------+-----+---------+-------+
　　| 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索引、在已有的表中設置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; //確認刪除結果
　　+-------+------------+------+-----+---------+-------+
　　| 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索引，并確認結果：
　　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; //確認設置結果
　　+-------+------------+------+-----+---------+-------+
　　| 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）建表時設置PRIMARY KEY主鍵索引
　　主鍵索引實際上在前面已經接觸過了，建表的時候可以直接指定。如果表內一開始沒有主鍵字段，則新設置的非空UNIQUE字段相當于具有PRIMARY KEY主鍵約束。
　　每個表中的主鍵字段只能有一個。
　　建表的時候，可以直接在某個字段的“約束條件”部分指定PRIMARY KEY；也可以在最后指定PRIMARY KEY(某個字段名)。比如：
　　mysql> CREATE TABLE biao01(
　　-> id int(4) PRIMARY KEY, //直接在字段定義時約束
　　-> 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)
　　在建表的時候，如果主鍵字段為int類型，還可以為其設置AUTO_INCREMENT自增屬性，這樣當添加新的表記錄時，此字段的值會自動從1開始逐個增加，無需手動指定。比如，新建一個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）刪除現有表的PRIMARY KEY主鍵索引
　　如果要移除某個表的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)
　　當嘗試刪除tea6表的主鍵時，會出現異常：
　　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
　　這是因為tea6表的主鍵字段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; //確認清除結果
　　+-------+------------+------+-----+---------+-------+
　　| 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）為現有表添加PRIMARY KEY主鍵索引
　　重新為tea6表指定主鍵字段，仍然使用id列：
　　mysql> ALTER TABLE tea6 ADD PRIMARY KEY(id); //設置主鍵字段
　　Query OK, 0 rows affected (0.35 sec)
　　Records: 0 Duplicates: 0 Warnings: 0
　　mysql> DESC tea6; //確認設置結果
　　+-------+------------+------+-----+---------+-------+
　　| 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)
　　步驟二：創建數據庫并設置外鍵實現同步更新與同步刪除

　　根據實驗任務要求，兩個表格的字段結構如表-1、表-2所示。
　　1）創建yg表，用來記錄員工工號、姓名
　　其中yg_id列作為主鍵，并設置自增屬性
　　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）創建gz表，用來記錄員工的工資信息
　　其中gz_id需要參考員工工號，即gz表的gz_id字段設為外鍵，將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屬性，會自動填充，所以只要為name列賦值就可以了。
　　插入表記錄可使用INSERT指令，這里先執行下列操作，具體在下一章學習：
　　mysql> INSERT INTO yg(name) VALUES('Jerry'),('Tom');
　　Query OK, 2 rows affected (0.16 sec)
　　Records: 2 Duplicates: 0 Warnings: 0
　　確認yg表的數據記錄：
　　mysql> SELECT FROM yg;
　　+-------+-------+
　　| yg_id | name |
　　+-------+-------+
　　| 1 | Jerry |
　　| 2 | Tom |
　　+-------+-------+
　　2 rows in set (0.00 sec)
　　4）為gz表添加2條工資信息記錄
　　同上，數據參考圖-2，插入相應的工資記錄（gz_id字段未指定默認值，也未設置自增屬性，所以需要手動賦值）：
　　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
　　確認gz表的數據記錄：
　　mysql> SELECT FROM gz;
　　+-------+-------+----------+
　　| gz_id | name | gz |
　　+-------+-------+----------+
　　| 1 | Jerry | 12000.00 |
　　| 2 | Tom | 8000.00 |
　　+-------+-------+----------+
　　2 rows in set (0.05 sec)
　　5）驗證表記錄的UPDATE更新聯動
　　將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
　　確認修改結果：
　　mysql> SELECT FROM yg;
　　+-------+-------+
　　| yg_id | name |
　　+-------+-------+
　　| 2 | Tom |
　　| 1234 | Jerry |
　　+-------+-------+
　　2 rows in set (0.00 sec)
　　同時也會發現，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）驗證表記錄的DELETE刪除聯動
　　刪除yg表中用戶Jerry的記錄：
　　mysql> DELETE FROM yg WHERE name='Jerry';
　　Query OK, 1 row affected (0.05 sec)
　　確認刪除結果：
　　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)
　　其中gz_ibfk_1即刪除外鍵約束時要用到的名稱。
　　刪除操作：
　　mysql> ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1;
　　Query OK, 0 rows affected (0.01 sec)
　　Records: 0 Duplicates: 0 Warnings: 0
　　確認刪除結果：
　　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存儲引擎

　　mysql服務體系結構：（8個功能模塊）
　　連接池
　　sql接口
　　分析器
　　優化器
　　查詢緩存
　　存儲引擎
　　文件系統
　　查看數據庫服務支持的存儲引擎
　　mysql> show engines;

　　設置默認存儲引擎
　　vim /etc/my.cnf
　　[mysqld]
　　... ...
　　default-storage-engine=InnoDB
　　常用的存儲引擎為InnoDB和MyISAM

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

　　常用存儲引擎的特點
　　MyISAM
　　表.MYI 索引信息
　　表.MYD 數據
　　表.frm 表結構
　　支持表級鎖（鎖一張表）
　　不支持事務事務回滾

　　InnoDB存儲引擎
　　表.idb 索引信息+數據
　　表.frm 表結構
　　支持行級鎖（只給當前被訪問的行加鎖）
　　支持事務事務回滾

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

（編輯：武林網）

上一篇：MySQL innobackupex全量備份復原

下一篇：mysql引擎筆記整頓