SQL語(yǔ)句---DDL語(yǔ)句
站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到淮陰網(wǎng)站設(shè)計(jì)與淮陰網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名注冊(cè)、網(wǎng)頁(yè)空間、企業(yè)郵箱。業(yè)務(wù)覆蓋淮陰地區(qū)。
==============================================================================
概述：
==============================================================================
---服務(wù)端命令：SQL語(yǔ)句，發(fā)往服務(wù)端運(yùn)行，并取回結(jié)果；需要顯式的語(yǔ)句結(jié)束符；
★DDL：數(shù)據(jù)定義語(yǔ)言，
☉作用：
主要用于數(shù)據(jù)庫(kù)組件，例如數(shù)據(jù)庫(kù)、表、索引、視圖、觸發(fā)器、事件調(diào)度器、存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)；
☉常用命令：
CREATE（創(chuàng)建）， ALTER（修改）， DROP（刪除）（？后跟命令可獲取幫助）
★DML：數(shù)據(jù)操縱語(yǔ)言
☉作用：
CRUD（增刪改查）操作，主要用于操作表中的數(shù)據(jù)；每一種操作之前都要先查詢；
☉命令
INSERT，DELETE，UPDATE，SELECT
★DCL：數(shù)據(jù)控制語(yǔ)言
☉作用：
授權(quán)用戶，登錄主機(jī)地址權(quán)限及回收權(quán)限
☉命令
GRANT（授權(quán)）, REVOKE（回收權(quán)限）
SQL MODE：定義mysqld對(duì)約束等違反時(shí)的響應(yīng)行為等設(shè)定；
★常用的MODE：
TRADITIONAL ： 傳統(tǒng)的模式，違反數(shù)據(jù)定義的統(tǒng)統(tǒng)都不被允許；
STRICT_TRANS_TABLES ： 僅對(duì)事物型表嚴(yán)格限定；
STRICT_ALL_TABLES ： 對(duì)所有的表都做嚴(yán)格限定；
★修改方式：
mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';
注意：
默認(rèn)為空模式，如果違反數(shù)據(jù)定義，會(huì)發(fā)出警報(bào)，會(huì)以允許的最大范圍去修減數(shù)據(jù)
sql mode為必改參數(shù)，要想永久生效，要寫(xiě)入配置文件
演示：
1.在sql mode模式為空的時(shí)候（默認(rèn)），向表中插入數(shù)據(jù)，可以插入成功，但對(duì)違反數(shù)據(jù)定義的會(huì)對(duì)數(shù)據(jù)進(jìn)行修減到允許的最大范圍，如下：
重慶分公司
重慶分公司
MariaDB [(none)]> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> create table tbl1(id tinyint unsigned,name CHAR(5));
Query OK, 0 rows affected (0.03 sec)
MariaDB [testdb]> insert into tbl1 (id) values (16),(256); # 默認(rèn)最大為255
Query OK, 2 rows affected, 1 warning (0.00 sec) # 報(bào)錯(cuò)
Records: 2 Duplicates: 0 Warnings: 1
MariaDB [testdb]> select * from tbl1;
+------+------+
| id | name |
+------+------+
| 16 | NULL |
| 255 | NULL | # 可以發(fā)現(xiàn)我們插入的256沒(méi)有成功，只到允許插入的最大范圍
+------+------+
2 rows in set (0.00 sec)
MariaDB [testdb]> insert into tbl1 (name) values ('jerry'),('taotaoxiuxiu');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
MariaDB [testdb]> show Warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 2 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [testdb]> select * from tbl1;
+------+-------+
| id | name |
+------+-------+
| 16 | NULL |
| 255 | NULL |
| NULL | jerry |
| NULL | taota | # 我們定義的最大只能插入5個(gè)字符，多以多出來(lái)的將會(huì)被修減
+------+-------+
4 rows in set (0.00 sec)2.現(xiàn)在我們定義sql mode模式為TRADITIONAL（傳統(tǒng)模式），即對(duì)數(shù)據(jù)進(jìn)行嚴(yán)格的限定，對(duì)違反數(shù)據(jù)要求的統(tǒng)統(tǒng)不予許插入，如下：
MariaDB [testdb]> SET @@session.sql_mode='TRADITIONAL'; # 設(shè)定當(dāng)前會(huì)話為傳統(tǒng)模式；
Query OK, 0 rows affected (0.00 sec)
MariaDB [testdb]> SELECT @@session.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [testdb]> insert into tbl1 (name) values ('jerry'),('taotaoxiuxiu');
ERROR 1406 (22001): Data too long for column 'name' at row 2 # 再次插入報(bào)錯(cuò)，不允許插入1.獲取幫助
mysql> help KEYWORD
mysql> help contents
演示：
MariaDB [(none)]> help contents You asked for help about help category: "Contents" For more information, type 'help- ', where
- is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Procedures Table Maintenance Transactions User-Defined Functions Utility MariaDB [(none)]> help Data Types # 獲取數(shù)據(jù)類型 You asked for help about help category: "Data Types" For more information, type 'help
- ', where
- is one of the following topics: AUTO_INCREMENT BIGINT BINARY BIT BLOB BLOB DATA TYPE BOOLEAN CHAR CHAR BYTE DATE DATETIME DEC DECIMAL DOUBLE DOUBLE PRECISION ENUM FLOAT INT INTEGER LONGBLOB LONGTEXT MEDIUMBLOB MEDIUMINT MEDIUMTEXT SET DATA TYPE SMALLINT TEXT TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT VARBINARY VARCHAR YEAR DATA TYPE MariaDB [(none)]> help INT Name: 'INT' Description: INT[(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. URL: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
2.數(shù)據(jù)庫(kù)管理
★創(chuàng)建數(shù)據(jù)庫(kù)：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
character 字符集，SHOW CHARACTER SET 可查看所支持的字符集
★修改數(shù)據(jù)庫(kù)
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name
★刪除數(shù)據(jù)庫(kù)
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
★相關(guān)命令：
SHOW CHARACTER SET //查看字符集；
SHOW COLLATION //查看排序規(guī)則；
SHOW CREATE DATABASE db_name //查看創(chuàng)建數(shù)據(jù)庫(kù)時(shí)所使用的語(yǔ)句；
命令演示：
MariaDB [(none)]> show create database mydb; # 查看創(chuàng)建數(shù)據(jù)庫(kù)mydb時(shí)的使用語(yǔ)句 +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> alter database mydb character set 'utf8'; # 修改字符集 Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show create database mydb; # 查看庫(kù)創(chuàng)建 +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
3.表管理
1）表創(chuàng)建
★語(yǔ)法：
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]
☉create_definition：由逗號(hào)分隔的列表
◆字段定義：
column_name column_defination 字段名稱+字段定義相關(guān)信息
◆約束定義：
PRIMARY KEY(col1[,col2, ....])
UNIQUE KEY
FOREIGN KEY
CHECK(expr)
◆索引定義：
{INDEX|KEY} 普通索引創(chuàng)建
{FULLTEXT|SPATIAL} 全文索引，空間索引
注意：column_definition：
data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
☉table_option:
ENGINE [=] engine_name 存儲(chǔ)引擎
★查看數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎種類：
mysql> SHOW ENGINES;
★查看指定表的存儲(chǔ)引擎：
mysql> SHOW TABLE STATUS LIKE clause;
★查看表結(jié)構(gòu)定義：
DESC tbl_name；
★查看表狀態(tài)屬性信息：
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
演示：
1.表創(chuàng)建：
[root@centos7 ~]# mysql -p134296
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 28
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| ultrax |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> USE mydb;
Database changed
MariaDB [mydb]> CREATE TABLE tbl1 (id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('F','M') DEFAULT 'M',UNIQUE KEY(name,gender),INDEX(name));
Query OK, 0 rows affected (0.04 sec)
MariaDB [mydb]> DESC tbl1;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('F','M') | YES | | M | |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)2.查看存儲(chǔ)引擎類型：
MariaDB [(none)]> show engines; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.00 sec)
3.查看表狀態(tài)信息：
MariaDB [mydb]> show table status\G *************************** 1. row *************************** Name: tbl1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 32768 Data_free: 0 Auto_increment: 1 Create_time: 2016-10-16 17:54:32 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: # 如果有多個(gè)表的話，可以使用where name 或者like 匹配相關(guān)的表 MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.02 sec) MariaDB [mysql]> show table status like 'proc%'\G # 匹配proc相關(guān)的表 *************************** 1. row *************************** Name: proc Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 292 Max_data_length: 281474976710655 Index_length: 4096 Data_free: 292 Auto_increment: NULL Create_time: 2016-10-12 20:06:15 Update_time: 2016-10-12 20:06:15 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: Stored Procedures *************************** 2. row *************************** Name: procs_priv Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 239253730204057599 Index_length: 4096 Data_free: 0 Auto_increment: NULL Create_time: 2016-10-12 20:06:15 Update_time: 2016-10-12 20:06:15 Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: Procedure privileges 2 rows in set (0.01 sec)
--------------------------------------------------------------------------------------------------------------------------------------
2）表修改
★語(yǔ)法：
ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]
☉alter_specification
◆表選項(xiàng)
ENGINE=engine_name
...
◆表定義
字段
ADD ：增
DRO：刪
CHANGE ：大改
MODIFY ：局部范圍小改動(dòng)
鍵和索引
ADD {PRIMARY|UNIQUE|FOREIGN} key (col1, col2, ...)
ADD INDEX(col1, col2, ...)
DROP {PRIMARY|UNIQUE|FOREIGN} KEY key_name；
DROP INDEX index_name；
★查看表上的索引信息：
SHOW INDEXES FROM tbl_name；
命令演示：
MariaDB [mydb]> use mydb
MariaDB [mydb]> show index from tbl1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl1 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| tbl1 | 0 | name | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | | |
| tbl1 | 1 | name_2 | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
MariaDB [mydb]> alter table tbl1 drop index name_2; # 刪除索引name_2
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb]> show index from tbl1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl1 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| tbl1 | 0 | name | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
MariaDB [mydb]> desc tbl1;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('F','M') | YES | | M | |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
MariaDB [mydb]> alter table tbl1 add ClassID TINYINT UNSIGNED NOT NULL; # 新增加一個(gè)字段
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb]> desc tbl1;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('F','M') | YES | | M | |
| ClassID | tinyint(3) unsigned | NO | | NULL | |
+---------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
# 使用modify局部修改放到age的行后面
MariaDB [mydb]> alter table tbl1 modify ClassID TINYINT UNSIGNED NOT NULL after age;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb]> desc tbl1;
+---------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(30) | NO | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| ClassID | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('F','M') | YES | | M | |
+---------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)-------------------------------------------------------------------------------
3）表刪除和查看表創(chuàng)建
★表刪除
DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...
可以一次刪除多個(gè)表
★查看表創(chuàng)建語(yǔ)句：
SHOW CREATE TABLE tbl_name
4.索引管理
★引入索引的作用：
MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。
實(shí)際上，索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。
★索引類型：
聚集索引、非聚集索引：索引是否與數(shù)據(jù)存在一起；
主鍵索引、輔助索引
稠密索引、稀疏索引：是否索引了每一個(gè)數(shù)據(jù)項(xiàng)；
BTREE（B+）、HASH、R Tree、FULLTEXT
BTREE：左前綴；
★創(chuàng)建
☉語(yǔ)法：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name,...) 索引字段名稱
index_col_name：
col_name [(length)] [ASC | DESC]
{INDEX|KEY} ：普通索引創(chuàng)建
{FULLTEXT|SPATIAL} ：全文索引，空間索引
★刪除：
DROP INDEX index_name ON tbl_name
★查看：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
★使用ALTER 命令添加和刪除索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
該語(yǔ)句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):
這條語(yǔ)句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會(huì)出現(xiàn)多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list):
添加普通索引，索引值可出現(xiàn)多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
該語(yǔ)句指定了索引為 FULLTEXT ，用于全文索引。
命令演示：
MariaDB [mydb]> show index from tbl1; # 查看索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl1 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| tbl1 | 0 | name | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.03 sec)
MariaDB [mydb]> drop index name on tbl1; # 刪除索引
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb]> show index from tbl1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
MariaDB [mydb]> create index name_and_gender on tbl1(name(5),gender); # 創(chuàng)建索引
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mydb]> show index from tbl1; # 查看如下
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| tbl1 | 1 | name_and_gender | 1 | name | A | 0 | 5 | NULL | | BTREE | | |
| tbl1 | 1 | name_and_gender | 2 | gender | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
MariaDB [mydb]> show index from tbl1 where Key_name like 'name%'; # 查看指定的索引
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl1 | 1 | name_and_gender | 1 | name | A | 0 | 5 | NULL | | BTREE |
網(wǎng)頁(yè)名稱：MySQL數(shù)據(jù)庫(kù)SQL語(yǔ)句---DDL語(yǔ)句
當(dāng)前URL：http://weahome.cn/article/piesjs.html
