基于Linux的MySQL操作实例(修改表结构，MySQL索引，MySQL数据引擎)
前言
本篇是基于Linux下针对MySQL表结构的修改，MySQL索引的操作以及MySQL数据引擎的配置和说明。
本篇结合上一篇文档，基于Linux的MySQL操作实例(软件安装，mysql基本操作，mysql数据类型，建表、插入数据操作)
若是有兴趣的朋友可以去看看。
/p/444482ff5986
根据本人的一贯风格，本篇依旧是前面就这几点做一些概述，并不会涉及太多的概念或理论，大篇幅的进行实际的命令实例操作。
最后依旧欢迎各路大神批评指教，鄙人不胜感激。谢谢大家。
修改数据库表结构
alter table 表名 执行动作；
执行动作：添加新字段(add)
删除已有字段(drop)
修改字段类型(modify)
修改字段名(change)
语法格式
添加新字段
add 添加新字段
add 字段名 类型(宽度)；
add 字段名 类型(宽度) 约束条件 ；
add 字段名 类型(宽度) 约束条件 first；
add 字段名 类型(宽度) 约束条件 after 字段名；
eg：altertablet1addclasschar(7)default"nsd1609"first,addtelchar(11),addsexenum("boy","girl")default"boy"aftername;
删除字段
drop 删除字段
drop 字段名
eg：altertablet1dropname，dropsex;
修改字段类型
modify 修改字段类型
不能与字段已经存储的数据冲突
modify 字段名 类型(宽度) 约束条件；
eg：mysql>altertablet1
->modify
->sexenum("boy","girl","no")notnulldefault"no";
修改字段名
change 修改字段名
change 原字段名 新字段名 类型(宽度) 约束条件；
eg：altertablet1changeteliphonechar(11);
修改表名
alter table 原表名 rename [to] 新表名；
eg:altertablet1renamet111;
mysql索引
概述
索引:相当于 "书的目录"索引的优点加快查询记录的速度.
索引的缺点会减慢写的速度( insert update delete ).
占用物理存储空间.
在表里建索引 设置在字段上
索引类型普通索引 index
唯一索引 unique
主键 primary key
外键 foreign key
全文索引 fulltext
使用索引查看
创建
使用规则
删除
查看索引desc 表名； ---> 显示结果中的Key列即是索引值
show index from 表名\G；
Table: user
Column_name: Host
Key_name: PRIMARY //即是索引值
Index_type: BTREE //共有三种：BTREE(二叉树)，B+tree，hash
index普通索引的使用规则一个表中可以有多个INDEX字段
字段的值允许有重复，且可以赋NULL值
经常把做查询条件的字段设置为INDEX字段
INDEX字段的KEY标志是MUL创建普通索引
1.在已有表里创建index字段
create index 索引名 on 表名(字段名);
create index sex on t111(sex);
2.建表时创建index字段
create table 表名 (
字段名列表，
index(字段名)，index(字段名)
);删除普通索引
drop index 索引名 on 表名；
drop index sex on t24;
primary key主键的使用规则一个表中只能有一个primary key字段
对应的字段值不允许有重复，且不允许赋NULL值
如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一起创建。
主键字段的KEY标志是PRI
通常与 AUTO_INCREMENT 连用
经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]
建表时创建主键字段createtablet25(namechar(10),ageint(2),primarykey(name));createtablet26(namechar(10)primarykey,ageint(2));
删除主键
alter table 表名 drop primary key;
在已有表里创建主键
alter table 表名 add primary key(字段名)；
复合主键的使用
多个字段一起做主键是复合主键 必须一起创建。
字段的值不允许同时相同。
建表时创建：
create table t29(host char(10),db char(10),user char(10),primary key(host,db,user));
对已有的表进行添加：
alter table t29 add primary key(host,user,db);
通常和aUTO_INCREMENT 连用，实现字段值的字段增长
经常把表中能够唯一标识记录的字段设置为主键字段[记录编号字段]
唯一索引 unique字段的值可以为Null 但不可以重复
一个表里可以有多个unique字段
标志 UNI
一般使用于：姓名，身份证，考试证，护照，驾驶证
建表时创建：createtablet29(
namechar(10),
stu_idchar(9),
ageint(2),
unique(stu_id)
);
在已有表里创建unique字段
create unique index 索引名 on 表名(字段名);
create unique index stu_id on t29(stu_id);
外键(foreign key)
功能:
让当前表某个字段的值，在另一个表某个字段值的范围内选择。
使用规则:表的存储引擎必须是innodb
字段的数据类型要匹配
被参考的字段必须是key 中的一种 (primary key)createtablejfb(
jfb_idint(2)primarykeyauto_increment,
namechar(10),
payfloat(7,2)
)engine=innodb;
createtablebjb(
bjb_idint(2),
namechar(10),
foreignkey(bjb_id)referencesjfb(jfb_id)onupdatecascadeondeletecascade
)engine=innodb;
删除外键
show create table 表名；
alter table 表名 drop foreign key 外键名；
alter table bjb drop foreign key bjb_ibfk_1;
mysql存储引擎
概述
存储引擎：
表的处理器，是mysql数据库服务软件自动程序，不同处理器有不同的功能和数据存储方式。
基本操作查看数据库服务支持哪些存储引擎：showengines;
InnoDBDEFAULT修改mysql数据库服务默认使用的存储引擎:vim/etc/f
[mysqld]default-storage-engine=myisam
servicemysqlrestart建表时指定表使用的存储引擎
create table t31(name char(10))engine=memory;修改表使用的存储引擎
alter table 表名 engine=存储引擎名；
eg:
alter table t31 engine=innodb;查看表使用的存储引擎
show create table 表名；
工作中使用哪种存储引擎？myisam
innodb
myisam的特点独享表空间
t1.frm 表结构
t1.MYD 表记录
t1.MYI 表索引
innodb的特点支持行级锁
支持外键 、 事务 、事务回滚
共享表空间
t3.frm 表结构
t3.ibd 表记录+表索引
事务
事务：一次sql操作从开始到结束的过程。
事务回滚：执行一次事务，只要执行过程中，任何一步执行失败，就恢复之前所有的sql操作。
事务日志文件记录对所有inondb存储引擎的表执行过的sql命令。
ibdata1 记录sql命令产生的数据信息
ib_logfile0----|
|---> 记录SQL 命令
ib_logfile1----|
锁机制
锁机制是为了解决客户端的并发访问冲突问题。
锁粒度： 表级锁 行级锁 页级锁
锁类型：读锁 (共享锁) select * from t1;
写锁 (互斥锁 排它锁)
建表时如何决定表使用的存储引擎：
执行写操作多的表适合使用inondb存储引擎，这样并发访问大。
执行读操作多的表适合使用myisam存储引擎.
实例操作(前面基本描述的具体实现)//进入mysql数据库mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|game_db|
|mysql|
|performance_schema|
|test|
|user_db|
+--------------------+6rowsinset(0.00sec)
mysql>useuser_db;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>showtables;
+-------------------+
|Tables_in_user_db|
+-------------------+
|user_list|
+-------------------+1rowinset(0.00sec)
mysql>descuser_list;
+-----------+-------------------------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-----------+-------------------------------------+------+-----+---------+----------------+
|id|int(10)|NO|PRI|NULL|auto_increment|
|u_name|char(10)|NO||NULL||
|u_sex|enum('boy','girl')|NO||NULL||
|u_subject|enum('computer','chinese','engish')|NO||NULL||
|u_grade|double(6,2)|NO||NULL||
+-----------+-------------------------------------+------+-----+---------+----------------+5rowsinset(0.01sec)//添加字段mysql>altertableuser_listadd
->mail2varchar(25)default"stuff@";
QueryOK,0rowsaffected(0.91sec)
Records:0Duplicates:0Warnings:0mysql>select*fromuser_list;Emptyset(0.00sec)
mysql>descuser_list;
+-----------+-------------------------------------+------+-----+---------------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-----------+-------------------------------------+------+-----+---------------+----------------+
|id|int(10)|NO|PRI|NULL|auto_increment|
|u_name|char(10)|NO||NULL||
|u_sex|enum('boy','girl')|NO||NULL||
|u_subject|enum('computer','chinese','engish')|NO||NULL||
|u_grade|double(6,2)|NO||NULL||
|mail1|varchar(25)|YES||NULL||
|mail2|varchar(25)|YES||stuff@||
+-----------+-------------------------------------+------+-----+---------------+----------------+7rowsinset(0.00sec)
mysql>altertableuser_listadd
->u_idchar(11)notnullfirst;
QueryOK,0rowsaffected(0.80sec)
Records:0Duplicates:0Warnings:0mysql>descuser_list;
+-----------+-------------------------------------+------+-----+---------------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-----------+-------------------------------------+------+-----+---------------+----------------+
|u_id|char(11)|NO||NULL||
|id|int(10)|NO|PRI|NULL|auto_increment|
|u_name|char(10)|NO||NULL||
|u_sex|enum('boy','girl')|NO||NULL||
|u_subject|enum('computer','chinese','engish')|NO||NULL||
|u_grade|double(6,2)|NO||NULL||
|mail1|varchar(25)|YES||NULL||
|mail2|varchar(25)|YES||stuff@||
+-----------+-------------------------------------+------+-----+---------------+----------------+8rowsinset(0.00sec)
mysql>insertintouser_list(u_id,u_name,u_sex,u_subject,u_grade,mail1)values(1,"tom","boy","computer","200.00","123456@");
QueryOK,1rowaffected(0.07sec)
mysql>insertintouser_list(u_id,u_name,u_sex,u_subject,u_grade,mail1)values(2,"jerry","boy","chinese","300.00","654321@");
QueryOK,1rowaffected(0.03sec)
mysql>insertintouser_list(u_id,u_name,u_sex,u_subject,u_grade,mail1)values(3,"cool","boy","chinese","240.00","654321@");
QueryOK,1rowaffected(0.15sec)
mysql>select*fromuser_list;
+------+----+--------+-------+-----------+---------+-------------------+---------------+
|u_id|id|u_name|u_sex|u_subject|u_grade|mail1|mail2|
+------+----+--------+-------+-----------+---------+-------------------+---------------+
|1|1|tom|boy|computer|200.00|123456@|stuff@|
|2|2|jerry|boy|chinese|300.00|654321@|stuff@|
|3|3|cool|boy|chinese|240.00|654321@|stuff@|
+------+----+--------+-------+-----------+---------+-------------------+---------------+3rowsinset(0.00sec)//删除字段mysql>altertableuser_listdropmail1,dropu_id;
QueryOK,0rowsaffected(0.78sec)
mysql>altertableuser_listaddhomeaddrchar(50);
QueryOK,0rowsaffected(0.66sec)
Records:0Duplicates:0Warnings:0//改变字段名mysql>altertableuser_listchangemail2mailaddrvarchar(25)default"userinfo@"
->;
mysql>altertableuser_listrenameuser_info;
QueryOK,0rowsaffected(0.20sec)
mysql>showtables;
+-------------------+
|Tables_in_user_db|
+-------------------+
|user_info|
+-------------------+1rowinset(0.00sec)
mysql>modifyu_gradedouble(7,2)notnull;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'modifyu_gradedouble(7,2)notnull'atline1mysql>altertableuser_infomodifyu_gradedouble(7,2)notnull;
QueryOK,0rowsaffected(0.09sec)
Records:0Duplicates:0Warnings:0//修改字段参数mysql>altertableuser_infomodifyu_gradefloat(3,2)notnull;
ERROR1264(2):Outofrangevalueforcolumn'u_grade'atrow1mysql>selectu_name,u_gradefromuser_info;
+--------+---------+
|u_name|u_grade|
+--------+---------+
|tom|200.00|
|jerry|300.00|
|cool|240.00|
+--------+---------+3rowsinset(0.00sec)
mysql>altertableuser_infoaddtelchar(15)notnull,addphonechar(11);
QueryOK,0rowsaffected(0.73sec)
Records:0Duplicates:0Warnings:0mysql>insertintouser_info(u_name,u_sex,u_subject,u_grade,mailaddr,homeaddr,tel,phone)values("uzi","boy","computer","3000.00",NULL,NULL,"123456789",NULL);
mysql>select*fromuser_info;
+----+--------+-------+-----------+---------+---------------+----------+-----------+-------+
|id|u_name|u_sex|u_subject|u_grade|mailaddr|homeaddr|tel|phone|
+----+--------+-------+-----------+---------+---------------+----------+-----------+-------+
|1|tom|boy|computer|200.00|stuff@|NULL||NULL|
|2|jerry|boy|chinese|300.00|stuff@|NULL||NULL|
|3|cool|boy|chinese|240.00|stuff@|NULL||NULL|
|4|uzi|boy|computer|3000.00|NULL|NULL|123456789|NULL|
+----+--------+-------+-----------+---------+---------------+----------+-----------+-------+4rowsinset(0.00sec)
mysql>descuser_info;
+-----------+-------------------------------------+------+-----+---------------------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-----------+-------------------------------------+------+-----+---------------------+----------------+
|id|int(10)|NO|PRI|NULL|auto_increment|
|u_name|char(10)|NO||NULL||
|u_sex|enum('boy','girl')|NO||NULL||
|u_subject|enum('computer','chinese','engish')|NO||NULL||
|u_grade|double(7,2)|NO||NULL||
|mailaddr|varchar(25)|YES||userinfo@||
|homeaddr|char(50)|YES||NULL||
|tel|char(15)|NO||NULL||
|phone|char(11)|YES||NULL||
+-----------+-------------------------------------+------+-----+---------------------+----------------+9rowsinset(0.00sec)
mysql>altertableuser_infomodifyphonechar(11)notnull;
ERROR1138(2):InvaliduseofNULLvaluemysql>altertableuser_infomodifymailaddrvarchar(25)notnull;
ERROR1138(2):InvaliduseofNULLvalue//index索引操作实例mysql>createindexu_nameonuser_info(u_name);
QueryOK,0rowsaffected(0.61sec)
Records:0Duplicates:0Warnings:0mysql>createtabletab1(
->idchar(10),
->namechar(15),
->ageint,
->index(name),
->index(age)
->);
QueryOK,0rowsaffected(0.86sec)
mysql>desctab1;
+-------+----------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+----------+------+-----+---------+-------+
|id|char(10)|YES||NULL||
|name|char(15)|YES|MUL|NULL||
|age|int(11)|YES|MUL|NULL||
+-------+----------+------+-----+---------+-------+3rowsinset(0.00sec)
mysql>showindexfromtab1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|tab1|1|name|1|name|A|0|NULL|NULL|YES|BTREE|||
|tab1|1|age|1|age|A|0|NULL|NULL|YES|BTREE|||
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2rowsinset(0.00sec)
mysql>showindexfromtab1\G;
***************************1.row***************************
Table:tab1
Non_unique:1
Key_name:name
Seq_in_index:1
Column_name:name
Collation:A
Cardinality:0
Sub_part:NULL
Packed:NULL
Null:YES
Index_type:BTREE
Comment:
Index_comment:
***************************2.row***************************
Table:tab1
Non_unique:1
Key_name:age
Seq_in_index:1
Column_name:age
Collation:A
Cardinality:0
Sub_part:NULL
Packed:NULL
Null:YES
Index_type:BTREE
Comment:
Index_comment:
2rowsinset(0.00sec)
ERROR:
Noqueryspecified
mysql>dropindexageontab1;
QueryOK,0rowsaffected(0.19sec)
Records:0Duplicates:0Warnings:0mysql>desctab1;
+-------+----------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+----------+------+-----+---------+-------+
|id|char(10)|YES||NULL||
|name|char(15)|YES|MUL|NULL||
|age|int(11)|YES||NULL||
+-------+----------+------+-----+---------+-------+3rowsinset(0.00sec)//主键primarykey操作实例mysql>createtabletab2(idintprimarykey,namechar(10));
QueryOK,0rowsaffected(0.67sec)
mysql>desctab2;
+-------+----------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+----------+------+-----+---------+-------+
|id|int(11)|NO|PRI|NULL||
|name|char(10)|YES||NULL||
+-------+----------+------+-----+---------+-------+2rowsinset(0.00sec)
mysql>createtabletab3(idint(2),namechar(10),primarykey(id));
QueryOK,0rowsaffected(0.79sec)
mysql>desctab3;
+-------+----------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+----------+------+-----+---------+-------+
|id|int(2)|NO|PRI|0||
|name|char(10)|YES||NULL||
+-------+----------+------+-----+---------+-------+2rowsinset(0.00sec)
mysql>altertabletab3dropprimarykey;
QueryOK,0rowsaffected(1.10sec)
Records:0Duplicates:0Warnings:0mysql>desctab3;
+-------+----------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+----------+------+-----+---------+-------+
|id|int(2)|NO||0||
|name|char(10)|YES||NULL||
+-------+----------+------+-----+---------+-------+2rowsinset(0.00sec)
mysql>createtabletab4(
->cipchar(16),
->portint(2),
->statusenum("deny","allow")default"deny"
->);
QueryOK,0rowsaffected(0.65sec)
mysql>desctab4
->;
+--------+----------------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+----------------------+------+-----+---------+-------+
|cip|char(16)|YES||NULL||
|port|int(2)|YES||NULL||
|status|enum('deny','allow')|YES||deny||
+--------+----------------------+------+-----+---------+-------+3rowsinset(0.00sec)
mysql>altertabletab4addprimarykey(cip,port);
QueryOK,0rowsaffected(1.05sec)
Records:0Duplicates:0Warnings:0mysql>desctab4;
+--------+----------------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+----------------------+------+-----+---------+-------+
|cip|char(16)|NO|PRI|||
|port|int(2)|NO|PRI|0||
|status|enum('deny','allow')|YES||deny||
+--------+----------------------+------+-----+---------+-------+3rowsinset(0.00sec)
mysql>insertintotab4values("1.1.1.1",22,"deny");
QueryOK,1rowaffected(0.05sec)
mysql>insertintotab4values("1.1.1.1",25,"allow");
QueryOK,1rowaffected(0.07sec)
mysql>insertintotab4values("1.1.1.1",22,"allow");
ERROR1062(23000):Duplicateentry'1.1.1.1-22'forkey'PRIMARY'mysql>insertintotab4values("2.1.1.1",22,"deny");
QueryOK,1rowaffected(0.04sec)
mysql>select*fromtab4;
+---------+------+--------+
|cip|port|status|
+---------+------+--------+
|1.1.1.1|22|deny|
|1.1.1.1|25|allow|
|2.1.1.1|22|deny|
+---------+------+--------+3rowsinset(0.00sec)//删除tab4表的主键mysql>altertabletab4dropprimarykey;
QueryOK,3rowsaffected(1.16sec)
Records:3Duplicates:0Warnings:0//当没有主键约束后，相同数据可以添加成功mysql>insertintotab4values("1.1.1.1",22,"allow");
QueryOK,1rowaffected(0.07sec)
mysql>insertintotab4values("1.1.1.1",25,"allow");
QueryOK,1rowaffected(0.04sec)//重新添加主键，由于表中已经存在不符合约束条件的数据，所以无法添加主键成功mysql>altertabletab4addprimarykey(cip,port);
ERROR1062(23000):Duplicateentry'1.1.1.1-22'forkey'PRIMARY'//删除不符合数据mysql>deletefromtab4whereport=22;
QueryOK,3rowsaffected(0.04sec)
mysql>altertabletab4addprimarykey(cip,port);
ERROR1062(23000):Duplicateentry'1.1.1.1-25'forkey'PRIMARY'mysql>deletefromtab4whereport=25;
QueryOK,2rowsaffected(0.08sec)//主键添加成功mysql>altertabletab4addprimarykey(cip,port);
QueryOK,0rowsaffected(0.80sec)
Records:0Duplicates:0Warnings:0//可以直接在创建表时就指定复合主键，主键个数可以多个，语法格式不变mysql>createtabletab5(u_idint,namechar(10),otherchar(40),primarykey(u_id,name));
QueryOK,0rowsaffected(0.63sec)
mysql>desctab5;
+-------+----------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+----------+------+-----+---------+-------+
|u_id|int(11)|NO|PRI|0||
|name|char(10)|NO|PRI|||
|other|char(40)|YES||NULL||
+-------+----------+------+-----+---------+-------+3rowsinset(0.00sec)//自增auto_increment只能修饰主键，必须是数值类型，最好是整形mysql>createtabletab6(idint(2)zerofillprimarykeyauto_increment,
->namechar(10)notnull,
->agetinyint(2)notnulldefault18
->,sexenum("boy","girl")default"boy",
->otherchar(50));
QueryOK,0rowsaffected(0.65sec)
mysql>desctab6;
+-------+--------------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------+--------------------------+------+-----+---------+----------------+
|id|int(2)unsignedzerofill|NO|PRI|NULL|auto_increment|
|name|char(10)|NO||NULL||
|age|tinyint(2)|NO||18||
|sex|enum('boy','girl')|YES||boy||
|other|char(50)|YES||NULL||
+-------+--------------------------+------+-----+---------+----------------+5rowsinset(0.00sec)//插入数据mysql>insertintotab6(name,age,sex,other)values("tom",12,"boy","ThisisTom");
QueryOK,1rowaffected(0.02sec)
mysql>insertintotab6(name,age,sex,other)values("jerry",14,"boy","ThisisJerry");
QueryOK,1rowaffected(0.05sec)
mysql>insertintotab6(name,age,sex,other)values("natasha",17,"girl","Thisisnatasha.");
QueryOK,1rowaffected(0.10sec)
mysql>select*fromtab6;
+----+---------+-----+------+------------------+
|id|name|age|sex|other|
+----+---------+-----+------+------------------+
|01|tom|12|boy|ThisisTom|
|02|jerry|14|boy|ThisisJerry|
|03|natasha|17|girl|Thisisnatasha.|
+----+---------+-----+------+------------------+3rowsinset(0.00sec)
mysql>insertintotab6values(7,"cool",22,"boy","Thisiscool");
QueryOK,1rowaffected(0.05sec)//会根据表中最大的数字进行自增计算mysql>insertintotab6(name,age,sex,other)values("uzi",19,"boy","Thisisuzi.");
QueryOK,1rowaffected(0.05sec)
mysql>select*fromtab6;
+----+---------+-----+------+------------------+
|id|name|age|sex|other|
+----+---------+-----+------+------------------+
|01|tom|12|boy|ThisisTom|
|02|jerry|14|boy|ThisisJerry|
|03|natasha|17|girl|Thisisnatasha.|
|07|cool|22|boy|Thisiscool|
|08|uzi|19|boy|Thisisuzi.|
+----+---------+-----+------+------------------+5rowsinset(0.00sec)//删除所有数据后，再次添加新值mysql>deletefromtab6;
QueryOK,5rowsaffected(0.07sec)
mysql>select*fromtab6;Emptyset(0.00sec)
mysql>insertintotab6(name,age,sex,other)values("uzi",19,"boy","Thisisuzi.");
QueryOK,1rowaffected(0.06sec)
mysql>insertintotab6(name,age,sex,other)values("uzi",19,"boy","Thisisuzi.");
QueryOK,1rowaffected(0.03sec)//内部有计数器，会按照上次结果继续增加，这样可以保证数据不会出现重复mysql>select*fromtab6;
+----+------+-----+------+--------------+
|id|name|age|sex|other|
+----+------+-----+------+--------------+
|09|uzi|19|boy|Thisisuzi.|
|10|uzi|19|boy|Thisisuzi.|
+----+------+-----+------+--------------+2rowsinset(0.00sec)//unique约束实例//创建表时，unique参数需要分开创建mysql>createtabletab7(idint(2)zerofillprimarykeyauto_increment,
->per_idchar(9),
->car_idchar(7),
->unique(per_id,car_id)
->);
QueryOK,0rowsaffected(0.85sec)
mysql>desctab7;
+--------+--------------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+--------+--------------------------+------+-----+---------+----------------+
|id|int(2)unsignedzerofill|NO|PRI|NULL|auto_increment|
|per_id|char(9)|YES|MUL|NULL||
|car_id|char(7)|YES||NULL||
+--------+--------------------------+------+-----+---------+----------------+3rowsinset(0.00sec)//删除表，重新创建表mysql>droptabletab7;
QueryOK,0rowsaffected(0.23sec)
mysql>createtabletab7(idint(2)zerofillprimarykeyauto_increment,per_idchar(9),car_idchar(7),unique(per_id),unique(car_id));
QueryOK,0rowsaffected(0.88sec)
mysql>desctab7;
+--------+--------------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+--------+--------------------------+------+-----+---------+----------------+
|id|int(2)unsignedzerofill|NO|PRI|NULL|auto_increment|
|per_id|char(9)|YES|UNI|NULL||
|car_id|char(7)|YES|UNI|NULL||
+--------+--------------------------+------+-----+---------+----------------+3rowsinset(0.00sec)
mysql>insertintotab7(per_id,car_id)values("123456789","1234567");
QueryOK,1rowaffected(0.05sec)
mysql>insertintotab7(per_id,car_id)values(NULL,"1234567");
ERROR1062(23000):Duplicateentry'1234567'forkey'car_id'mysql>insertintotab7(per_id,car_id)values(NULL,"7654321");
QueryOK,1rowaffected(0.05sec)
mysql>insertintotab7(per_id,car_id)values(NULL,NULL);
QueryOK,1rowaffected(0.04sec)
mysql>select*fromtab7;
+----+-----------+---------+
|id|per_id|car_id|
+----+-----------+---------+
|01|123456789|1234567|
|03|NULL|7654321|
|04|NULL|NULL|
+----+-----------+---------+3rowsinset(0.00sec)
mysql>insertintotab7(per_id,car_id)values("123456789","1234567");
QueryOK,1rowaffected(0.05sec)
mysql>insertintotab7(per_id,car_id)values(NULL,"1234567");
ERROR1062(23000):Duplicateentry'1234567'forkey'car_id'mysql>insertintotab7(per_id,car_id)values(NULL,"7654321");
QueryOK,1rowaffected(0.05sec)
mysql>insertintotab7(per_id,car_id)values(NULL,NULL);
QueryOK,1rowaffected(0.04sec)
mysql>select*fromtab7;
+----+-----------+---------+
|id|per_id|car_id|
+----+-----------+---------+
|01|123456789|1234567|
|03|NULL|7654321|
|04|NULL|NULL|
+----+-----------+---------+3rowsinset(0.00sec)
mysql>insertintotab7(per_id,car_id)values(NULL,NULL);
QueryOK,1rowaffected(0.04sec)
mysql>insertintotab7(per_id,car_id)values("012345678","1234567");
ERROR1062(23000):Duplicateentry'1234567'forkey'car_id'mysql>altertabletab7dropindexcar_id;
QueryOK,0rowsaffected(0.21sec)
Records:0Duplicates:0Warnings:0mysql>insertintotab7(per_id,car_id)values("012345678","1234567");
QueryOK,1rowaffected(0.05sec)
mysql>insertintotab7(per_id,car_id)values("012345678","1234567");
mysql>createuniqueindexcar_idontab7(car_id);
ERROR1062(23000):Duplicateentry'1234567'forkey'car_id'mysql>desctab7;
+--------+--------------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+--------+--------------------------+------+-----+---------+----------------+
|id|int(2)unsignedzerofill|NO|PRI|NULL|auto_increment|
|per_id|char(9)|YES|UNI|NULL||
|car_id|char(7)|YES||NULL||
+--------+--------------------------+------+-----+---------+----------------+3rowsinset(0.00sec)
mysql>select*fromtab7;
+----+-----------+---------+
|id|per_id|car_id|
+----+-----------+---------+
|01|123456789|1234567|
|03|NULL|7654321|
|04|NULL|NULL|
|05|NULL|NULL|
|07|012345678|1234567|
+----+-----------+---------+5rowsinset(0.00sec)
mysql>deletefromtab7whereper_id=012345678;
QueryOK,1rowaffected(0.05sec)
mysql>createuniqueindexcar_idontab7(car_id);
QueryOK,0rowsaffected(0.73sec)
Records:0Duplicates:0Warnings:0mysql>desctab7;
+--------+--------------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+--------+--------------------------+------+-----+---------+----------------+
|id|int(2)unsignedzerofill|NO|PRI|NULL|auto_increment|
|per_id|char(9)|YES|UNI|NULL||
|car_id|char(7)|YES|UNI|NULL||
+--------+--------------------------+------+-----+---------+----------------+3rowsinset(0.00sec)//外键操作实例//由于使用的是MySQL5.6版本，默认的存储引擎即是：innodb//该默认的存储引擎根据数据库的版本有所不同。//创建员工表mysql>createtablework_tab(w_idint(4)zerofillprimarykeyauto_increment,
->namechar(10)notnull,
->sexenum("man","woman")default"man",
->detialschar(40)default""
->);
QueryOK,0rowsaffected(0.06sec)
mysql>descwork_tab;
+---------+--------------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+---------+--------------------------+------+-----+---------+----------------+
|w_id|int(4)unsignedzerofill|NO|PRI|NULL|auto_increment|
|name|char(10)|NO||NULL||
|sex|enum('man','woman')|YES||man||
|detials|char(40)|YES||||
+---------+--------------------------+------+-----+---------+----------------+4rowsinset(0.00sec)//插入测试数据mysql>insertintowork_tab(name,sex,detials)values("tom","man","Thisistom.");
QueryOK,1rowaffected(0.00sec)
mysql>insertintowork_tab(name,sex,detials)values("jack","man","Thisisjack.");
QueryOK,1rowaffected(0.00sec)
mysql>insertintowork_tab(name,sex,detials)values("natasha","woman","Thisisnatasha.");
QueryOK,1rowaffected(0.00sec)/*
创建工资表，将该表的p_id与员工表的w_id进行外键绑定，即用来标识唯一用户(员工)
mysql>
*/mysql>createtablepay_tab(p_idint(4)zerofill,namechar(10)notnull,
->paysdouble(8,2)notnull,otherschar(30)default"",
->foreignkey(p_id)referenceswork_tab(w_id)onupdatecascadeondeletecascade)
->engine=innodb;
mysql>descpay_tab;
+--------+--------------------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------+--------------------------+------+-----+---------+-------+
|p_id|int(4)unsignedzerofill|YES|MUL|NULL||
|name|char(10)|NO||NULL||
|pays|double(8,2)|NO||NULL||
|others|char(30)|YES||||
+--------+--------------------------+------+-----+---------+-------+4rowsinset(0.00sec)//查看建表过程，验证创建表的结果是否正确mysql>showcreatetablepay_tab\G;
***************************1.row***************************
Table:pay_tab
CreateTable:CREATETABLE`pay_tab`(
`p_id`int(4)unsignedzerofillDEFAULTNULL,
`name`char(10)NOTNULL,
`pays`double(8,2)NOTNULL,
`others`char(30)DEFAULT'',
KEY`p_id`(`p_id`),
CONSTRAINT`pay_tab_ibfk_1`FOREIGNKEY(`p_id`)REFERENCES`work_tab`(`w_id`)ONDELETECASCADEONUPDATECASCADE
)ENGINE=InnoDBDEFAULTCHARSET=latin11rowinset(0.00sec)
ERROR:
Noqueryspecified
mysql>select*fromwork_tab;
+------+---------+-------+------------------+
|w_id|name|sex|detials|
+------+---------+-------+------------------+
|0001|tom|man|Thisistom.|
|0002|jack|man|Thisisjack.|
|0003|natasha|woman|Thisisnatasha.|
+------+---------+-------+------------------+3rowsinset(0.00sec)//向工资表插入在员工表存在的数据，可以插入mysql>insertintopay_tabvalues(2,"jack",9000.00,"jackpays");
QueryOK,1rowaffected(0.06sec)//向工资表插入在员工表不存在的数据，受外键约束无法插入mysql>insertintopay_tabvalues(4,"bob",8000.00,"jackpays");
ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`user_db`.`pay_tab`,CONSTRAINT`pay_tab_ibfk_1`FOREIGNKEY(`p_id`)REFERENCES`work_tab`(`w_id`)ONDELETECASCADEONUPDATECASCADE)//由于目前进行的约束仅仅是id，所以当向工资表插入数据时，name不一致的情况下，依旧可以插入//一般我们在这里编写的SQL指令，一般都是由开发进行操作的，在开发操作时，一般都是去员工表查询对应的用户，然后将查询的结果和新值进行添加操作，这样一般是不会出现该错误mysql>insertintopay_tabvalues(3,"wolf",10000.00,"wolfornatasha??");
QueryOK,1rowaffected(0.08sec)//删除受约束表的记录时，可以正常操作，并且不会对员工表产生影响mysql>deletefrompay_tabwherename="jack";
QueryOK,1rowaffected(0.05sec)
mysql>select*frompay_tab;
+------+------+----------+-------------------+
|p_id|name|pays|others|
+------+------+----------+-------------------+
|0003|wolf|10000.00|wolfornatasha??|
+------+------+----------+-------------------+1rowinset(0.00sec)
mysql>insertintopay_tabvalues("tom",6000.00,"tompays");
ERROR1136(21S01):Columncountdoesn'tmatchvaluecountatrow1
mysql>insertintopay_tabvalues(1,"tom",6000.00,"tompays");
QueryOK,1rowaffected(0.06sec)
mysql>select*fromwork_tab;
+------+---------+-------+------------------+
|w_id|name|sex|detials|
+------+---------+-------+------------------+
|0001|tom|man|Thisistom.|
|0002|jack|man|Thisisjack.|
|0003|natasha|woman|Thisisnatasha.|
+------+---------+-------+------------------+
3rowsinset(0.00sec)
mysql>select*frompay_tab;
+------+------+----------+-------------------+
|p_id|name|pays|others|
+------+------+----------+-------------------+
|0003|wolf|10000.00|wolfornatasha??|
|0001|tom|6000.00|tompays|
+------+------+----------+-------------------+
2rowsinset(0.00sec)
//在定义外键取值范围的表(work_tab员工表)删除数据时，对应的受外键约束的表(工资表)的对应记录也会被删除
mysql>deletefromwork_tabwherename="tom";
QueryOK,1rowaffected(0.04sec)
mysql>select*frompay_tab;
+------+------+----------+-------------------+
|p_id|name|pays|others|
+------+------+----------+-------------------+
|0003|wolf|10000.00|wolfornatasha??|
+------+------+----------+-------------------+
1rowinset(0.00sec)
mysql>select*fromwork_tab;
+------+---------+-------+------------------+
|w_id|name|sex|detials|
+------+---------+-------+------------------+
|0002|jack|man|Thisisjack.|
|0003|natasha|woman|Thisisnatasha.|
+------+---------+-------+------------------+
2rowsinset(0.00sec)
//在受外键约束的表中(pay_tab工资表)，删除不受外键约束的字段时，可以正常删除
mysql>altertablepay_tabdropname;
QueryOK,0rowsaffected(1.06sec)
Records:0Duplicates:0Warnings:0
//删除受约束的字段，无法成功，会被告知外键约束
mysql>altertablepay_tabdropp_id;
ERROR1553(HY000):Cannotdropindex'p_id':neededinaforeignkeyconstraint
//当删除外键约束的定义或直接删除外键字段，即可删除员工表或员工表内对应的记录
mysql>altertablepay_tabdropforeignkeypay_tab_ibfk_1;
QueryOK,0rowsaffected(0.16sec)
Records:0Duplicates:0Warnings:0
//mysql>droptablepay_tab;
mysql>droptablework_tab;
QueryOK,0rowsaffected(0.28sec)
//存储引擎操作实例
//default所在的行即是当前默认的存储引擎，Support表示当前可以使用，为NO即表示不可使用，
//Transactions表示存储引擎不支持事务，Comment表示描述信息
//查看当前MySQL支持的数据引擎
mysql>showengines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|Engine|Support|Comment|Transactions|XA|Savepoints|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|PERFORMANCE_SCHEMA|YES|PerformanceSchema|NO|NO|NO|
|CSV|YES|CSVstorageengine|NO|NO|NO|
|MRG_MYISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO|
|BLACKHOLE|YES|/dev/nullstorageengine(anythingyouwritetoitdisappears)|NO|NO|NO|
|MyISAM|YES|MyISAMstorageengine|NO|NO|NO|
|MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO|
|ARCHIVE|YES|Archivestorageengine|NO|NO|NO|
|InnoDB|DEFAULT|Supportstransactions,row-levellocking,andforeignkeys|YES|YES|YES|
|FEDERATED|NO|FederatedMySQLstorageengine|NULL|NULL|NULL|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9rowsinset(0.00sec)
//不同的存储引擎生成的表文件也不同
.frm--->存放表结构
//指定不同的数据引擎创建数据表
mysql>createtabletab8(idint)engine=MyISAM;
QueryOK,0rowsaffected(0.11sec)
mysql>createtabletab9(idint)engine=MEMORY;
QueryOK,0rowsaffected(0.11sec)
mysql>createtabletab9(idint)engine=InnoDB;
ERROR1050(42S01):Table'tab9'alreadyexists
mysql>createtabletab10(idint)engine=InnoDB;
QueryOK,0rowsaffected(0.56sec)
//退出mysql，进入文件目录，查看对应文件
[root@mysqluser_db]#pwd
/var/lib/mysql/user_db
[root@mysqluser_db]#lstab8*
tab8.frmtab8.MYDtab8.MYI
[root@mysqluser_db]#lstab9*
tab9.frm//临时表，存放到内存中，当系统将内存收回，即停止mysql服务时，该表数据丢失。
[root@mysqluser_db]#lstab10*
tab10.frmtab10.ibd//共享表空间
//查看建表过程
mysql>showcreatetab9;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'tab9'atline1
mysql>showcreatetabletab9;
+-------+------------------------------------------------------------------------------------------+
|Table|CreateTable|
+-------+------------------------------------------------------------------------------------------+
|tab9|CREATETABLE`tab9`(
`id`int(11)DEFAULTNULL
)ENGINE=MEMORYDEFAULTCHARSET=latin1|
+-------+------------------------------------------------------------------------------------------+
1rowinset(0.00sec)
mysql>altertabletab9engine=innodb;
QueryOK,0rowsaffected(0.64sec)
Records:0Duplicates:0Warnings:0
mysql>showcreatetabletab9;
+-------+------------------------------------------------------------------------------------------+
|Table|CreateTable|
+-------+------------------------------------------------------------------------------------------+
|tab9|CREATETABLE`tab9`(
`id`int(11)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=latin1|
+-------+------------------------------------------------------------------------------------------+
1rowinset(0.00sec)
//修改mysql默认的数据引擎
[root@mysqluser_db]#vim/etc/f
[mysqld]
default-storage-engine=myisam
[root@mysqluser_db]#servicemysqlrestart
ShuttingdownMySQL..SUCCESS!
StartingMySQL..SUCCESS!
[root@mysqluser_db]#mysql-uroot-p123456user_db
……
//DEFAULT所在的位置已经发生改变
mysql>showengines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|Engine|Support|Comment|Transactions|XA|Savepoints|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
|PERFORMANCE_SCHEMA|YES|PerformanceSchema|NO|NO|NO|
|CSV|YES|CSVstorageengine|NO|NO|NO|
|MRG_MYISAM|YES|CollectionofidenticalMyISAMtables|NO|NO|NO|
|BLACKHOLE|YES|/dev/nullstorageengine(anythingyouwritetoitdisappears)|NO|NO|NO|
|MyISAM|DEFAULT|MyISAMstorageengine|NO|NO|NO|
|MEMORY|YES|Hashbased,storedinmemory,usefulfortemporarytables|NO|NO|NO|
|ARCHIVE|YES|Archivestorageengine|NO|NO|NO|
|InnoDB|YES|Supportstransactions,row-levellocking,andforeignkeys|YES|YES|YES|
|FEDERATED|NO|FederatedMySQLstorageengine|NULL|NULL|NULL|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9rowsinset(0.00sec)
//数据库的锁，是为了进行并发操作时，操作冲突的情况。
//锁有读锁和写锁。
作者：海渊_haiyuan
链接：/p/1ca8da8ff190
