直接創(chuàng)建
create index 索引名稱 on 表名(字段名稱(字段長(zhǎng)度));  #字段長(zhǎng)度可添加
修改表的方式創(chuàng)建
alter table 表名 add index 索引名稱 (字段名稱(字段長(zhǎng)度));
創(chuàng)建表時(shí)創(chuàng)建
create table 表名（字段1,字段2,字段3,primary key(字段名稱),index 索引名稱(字段名稱(字段長(zhǎng)度));

[root@localhost ~]# mysql -uroot -p  #登錄mysql
mysql> create database auth;  #創(chuàng)建auth庫(kù)
mysql> use auth;  #進(jìn)入auth庫(kù)
mysql> create table auth.users(id int(10) not null,  創(chuàng)建users表，建立id字段，數(shù)據(jù)類型int，不為空
    -> name char(20) not null,  
    -> age varchar(10) default '',
    -> primary key(id));
mysql> desc users;  #查看表結(jié)構(gòu)，存在3個(gè)字段
mysql> insert into users values('1','zhangsan','20');  
mysql> insert into users values('2','lisi','23');
mysql> insert into users values('3','wangwu','26');  #字段下分別添加內(nèi)容
mysql> select * from users;  #查看表中數(shù)據(jù)
直接創(chuàng)建方法
mysql> create index index_age on auth.users(age);  #在age上創(chuàng)建索引在users表的age字段，index_age：索引名稱
修改表的方式創(chuàng)建
mysql> alter table users add index index_name(name);  #在users表里的name字段創(chuàng)建索引，索引名稱為index_name
mysql> desc users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   | PRI | NULL    |       |  #主鍵索引對(duì)應(yīng)PRL
| name  | char(20)    | NO   | MUL | NULL    |       |  #普通索引對(duì)應(yīng)MUL
| age   | varchar(10) | YES  | MUL |         |       |
+-------+-------------+------+-----+---------+-------+
mysql> show index from users; 
或者mysql> show keys from users; #查看users表的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-表名--+索引值得唯一性+--索引名稱--+-索引列的序列號(hào)-+-對(duì)應(yīng)的字段 --+-----------+-------------+----------+--------+------+--索引的類型-+---------+---------------+
| users |  唯一    0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| users |不是唯一  1 | index_age  |            1 | age         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_name |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> create table auth.kc31(id int(10) not null,  #創(chuàng)建kc31表，存在3個(gè)字段
    -> name char(20) not null,
    -> address varchar(30) default '',
    -> unique index index_address(address));  #unique：唯一性索引
mysql> use auth;
mysql> desc kc31;  #查看kc31表結(jié)構(gòu)
| address | varchar(30) | YES  | UNI |         |       |  #address的UNI：唯一性索引
mysql> show keys from kc31;  #查看Non_unique為0：代表唯一索引
mysql> insert into kc31 values('1','aaa','zhoukou');  #插入數(shù)據(jù)
mysql> insert into kc31 values('2','bbb','zhoukou');  #驗(yàn)證address字段的唯一性
ERROR 1062 (23000): Duplicate entry 'zhoukou' for key 'index_address'  #address字段zhoukou的關(guān)鍵字具有唯一性，所以不能創(chuàng)建
mysql> insert into kc31 values('2','bbb','luoyang');  #將zhoukou改為luoyang可以創(chuàng)建成功
mysql> insert into kc31 values('3','bbb','luohe');  #name字段不具有唯一性，可以創(chuàng)建
mysql> select * from kc31;  
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | aaa  | zhoukou |
|  2 | bbb  | luoyang |
|  3 | bbb  | luohe   |
+----+------+---------+  #第二字段可以有重復(fù)，第三字段因?yàn)橛形ㄒ恍圆荒苤貜?fù)

mysql> create table auth.test(id int(10) not null,  #創(chuàng)建test表
    -> class char(20) not null,
    -> primary key(id));  #直接指定主鍵為id字段
mysql> desc test;
| id    | int(10)  | NO   | PRI | NULL    |       |  #PRI主鍵索引
mysql> show index from test\G;
*************************** 1. row ***************************
        Table: test  #表名
   Non_unique: 0  #索引唯一值
     Key_name: PRIMARY  #索引名稱，關(guān)鍵字；PRIMARY
  Column_name: id  #id作為主鍵
mysql> alter table test drop primary key;  #清空主鍵索引
mysql> show index from test  #主鍵索引為空值
mysql> alter table test add primary key(class);  #將test表的class添加為主鍵
mysql> show index from test  #再查詢表結(jié)構(gòu)，存在class主鍵

mysql> alter table test add primary key(id);  #驗(yàn)證主鍵的唯一性
ERROR 1068 (42000): Multiple primary key defined  #多余的主鍵沒有被定義，不能同時(shí)存在2個(gè)主鍵
mysql> alter table test add unique index_id(id);  #創(chuàng)建id字段唯一性索引，可以有多個(gè)唯一性索引
mysql> show keys from test\G;  #查看test存在2個(gè)唯一性索引
*************************** 1. row ***************************
        Table: test
   Non_unique: 0
     Key_name: PRIMARY  #主鍵索引
  Column_name: class
*************************** 2. row ***************************
        Table: test
   Non_unique: 0
     Key_name: index_id  #唯一索引
  Column_name: id

mysql> create table auth.kgc(name char(10) not null,  #創(chuàng)建kgc表
    -> passwd char(50) not null,
    -> sex int(10) not null,
    -> index index_kgc(name,passwd,sex));  #在name,passwd,sex創(chuàng)建組合索引
mysql> show index from kgc\G;  #查看存在3個(gè)索引

mysql> create table auth.content(id int(10) not null,
    -> title char(255) not null,
    -> time int(10) null,
    -> primary key(id),
    -> fulltext index_id(id));
ERROR 1283 (HY000): Column 'id' cannot be part of FULLTEXT index  #id字段不是3種數(shù)據(jù)類型，不能在設(shè)置全文索引
mysql> create table auth.content(id int(10) not null, title char(255) not null, time int(10) null, primary key(id), fulltext index_id(title));  #替換為支持的數(shù)據(jù)類型即可
mysql> show index from content\G;
*************************** 2. row ***************************
   Index_type: FULLTEXT  #數(shù)據(jù)類型為全文索引

show index from 表名;
show keys from 表名;

drop index 索引名稱 on 表名; #刪除普通索引
alter table 表名 drop index 索引名稱; #刪除普通索引
alter table 表名 drop primary key; #刪除主鍵索引

mysql> drop index index_id on content;  #刪除content表下的index_id索引
mysql> alter table content drop primary key;  #刪除ontent表下的主鍵索引
mysql> show index from content;  
Empty set (0.00 sec)  #索引已刪除

[root@localhost ~]# mysql -uroot -p  #啟動(dòng)數(shù)據(jù)庫(kù)
mysql> create database imployee_salary;  #創(chuàng)建imployee_salary數(shù)據(jù)庫(kù)
mysql> use imployee_salary;  #進(jìn)入imployee_salary數(shù)據(jù)庫(kù)
mysql> create table IT_salary(員工ID char(10),
    -> 崗位類別 char(20),
    -> 姓名 char(10),
    -> 年齡 char(10),
    -> 學(xué)歷 char(10),
    -> 薪資 char(10),
    -> primary key(員工ID));
mysql> desc it_salary;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| 員工ID       | char(10) | NO   | PRI | NULL    |       |
| 崗位類別     | char(20) | YES  |     | NULL    |       |
| 姓名         | char(10) | YES  |     | NULL    |       |
| 年齡         | char(10) | YES  |     | NULL    |       |
| 學(xué)歷         | char(10) | YES  |     | NULL    |       |
| 薪資         | char(10) | YES  |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
mysql> begin;  #開啟事務(wù)
mysql> insert into it_salary values('1','硬件工程師','小王','22','?？?,'8000');
mysql> insert into it_salary values('2','windows工程師','小張','23','本科','9000');
mysql> insert into it_salary values('4','Linux工程師','小楊','25','本科','12000');
mysql> select * from it_salary;
+----------+------------------+--------+--------+--------+--------+
| 員工ID   | 崗位類別          | 姓名   | 年齡    | 學(xué)歷   | 薪資   |
+----------+------------------+--------+--------+--------+--------+
| 1        | 硬件工程師        | 小王   | 22     | 專科    | 8000   |
| 2        | windows工程師     | 小張   | 23     | 本科    | 9000   |
| 4        | Linux工程師       | 小楊    | 25    | 本科    | 12000  |
+----------+------------------+--------+--------+--------+--------+
mysql> exit  #退出
[root@localhost ~]# mysql -uroot -p  #重新登錄數(shù)據(jù)庫(kù)
mysql> show tables from imployee_salary;  #能夠查到it_salary數(shù)據(jù)表
mysql> select * from it_salary;  #數(shù)據(jù)已經(jīng)不存在
說(shuō)明：此時(shí)數(shù)據(jù)庫(kù)中并沒有之前插入的數(shù)據(jù)，說(shuō)明使用begin開啟服務(wù)，執(zhí)行數(shù)據(jù)操作語(yǔ)句后，必須使用commit提交事務(wù)，否則數(shù)據(jù)是不能自動(dòng)提交的。
（2）rollback（回滾）的使用
mysql> use imployee_salary;
mysql> begin;  #開啟事務(wù)
mysql> insert into it_salary values('1','硬件工程師','小王','22','?？?,'8000');
mysql> insert into it_salary values('2','windows工程師','小張','23','本科','9000');
mysql> select * from it_salary;  #存在添加的兩行數(shù)據(jù)
mysql> rollback;  #回滾事務(wù)
mysql> select * from it_salary;  #數(shù)據(jù)已經(jīng)不存在
說(shuō)明：使用begin開始事務(wù)，然后執(zhí)行了插入語(yǔ)句后可以看到插入的數(shù)據(jù)，但是執(zhí)行rollback回滾命令后，之前的數(shù)據(jù)已經(jīng)沒有了
（3）savepoint（定義回滾點(diǎn)）的使用
mysql> begin;  #開啟事務(wù)
mysql> insert into it_salary values('1','硬件工程師','小王','22','?？?,'8000');  #插入一個(gè)數(shù)據(jù)
mysql> savepoint s1;  #定義s1回滾點(diǎn)
mysql> insert into it_salary values('2','windows工程師','小張','23','本科','9000');  #插入第二個(gè)數(shù)據(jù)
mysql> savepoint s2;  #定義s2回滾點(diǎn)
mysql> select * from it_salary;  #存在插入的2行數(shù)據(jù)

mysql> rollback to savepoint s1;  #回滾到s1
mysql> select * from it_salary;  #只存在一條數(shù)據(jù)
+----------+-----------------+--------+--------+--------+--------+
| 員工ID   | 崗位類別        | 姓名   | 年齡   | 學(xué)歷   | 薪資   |
+----------+-----------------+--------+--------+--------+--------+
| 1        | 硬件工程師      | 小王   | 22     | 專科   | 8000   |
+----------+-----------------+--------+--------+--------+--------+
mysql> rollback to savepoint s2;  #嘗試回滾到s2
ERROR 1305 (42000): SAVEPOINT s2 does not exist  #數(shù)據(jù)無(wú)法回滾
說(shuō)明：回滾到s1，第一條插入的數(shù)據(jù)可以查詢出來(lái)，但是無(wú)法回退到s2了
（4）使用set命令控制事務(wù)
mysql> set autocommit=0;  #改為手動(dòng)提交事務(wù)
mysql> insert into it_salary values('2','windows工程師','小張','23','本科','9000');
mysql> commit;  #提交事務(wù)，數(shù)據(jù)永久保存
說(shuō)明：若commit提交事務(wù)后，則數(shù)據(jù)無(wú)法進(jìn)行回滾

mysql> show engines;  #默認(rèn)存在9款存儲(chǔ)引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> show variables like '%storage_engine%';
（2）查看表正在使用的存儲(chǔ)引擎
mysql> show table status where name='表名';
mysql> show create table 表名;

mysql> alter table user_info engine=引擎;

[root@localhost ~]# vim /etc/my.cnf
    default-storage-engine=INNODB  
[root@localhost ~]# systemctl restart mysqld

mysql> create table 表名(字段1) engine=引擎;

mysql> set default_storage_engine=引擎;