Mysql数据库理论基础之四 --- 表和索引的管理
由MySQL AB公司开发，是最流行的开放源码SQL数据库管理系统，主要特点：
1、是一种数据库管理系统
2、是一种关联数据库管理系统
3、是一种开放源码软件，且有大量可用的共享MySQL软件
4、MySQL数据库服务器具有快速、可靠和易于使用的特点
5、MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中
2.1.单字段：
PRIMARY KEY 主键
UNIQUE KEY 唯一键
2.2 单或者多字段：
PRIMARY KEY(col,...)
UNIQUE KEY(col,...)
INDEX(col,...)
2.3 数据类型：
BIT[(length)]比特
|TINYINT[(length)] [UNSIGNED] [ZEROFILL] 非常小的整数（1字节）
|SMALLINT[(length)] [UNSIGNED] [ZEROFILL] 小的整数（2字节）
|MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]中等的整数（3字节）
|INT[(length)] [UNSIGNED] [ZEROFILL] 整数（4字节）
|INTEGER[(length)] [UNSIGNED] [ZEROFILL] 整数（4字节）相当于INT
|BIGINT[(length)] [UNSIGNED] [ZEROFILL] 大的整数（8个字节）
|REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] 实数
|DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]双精度整型
|FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] 浮点型
|DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]十进制小数点型
|NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]数值型
时间：
|DATE 日期型
| TIME 时间型
| TIMESTAMP 时区型
| DATETIME 日期时间型
|YEAR 年
|CHAR[(length)] 定长字符型 255 characters
VARCHAR(length)变长字符型65535 characters
[CHARACTER SET charset_name] [COLLATE collation_name]
|BINARY[(length)] 二进制数
|VARBINARY(length)变长二进制数
|TINYBLOB 非常小的大对数
|BLOB 大对数
|MEDIUMBLOB 中等的大对数
|LONGBLOB 长的大对数
文本：（不大小写）
|TINYTEXT[BINARY] 非常小的文本串255 characters2~8
[CHARACTER SET charset_name] [COLLATE collation_name]
|TEXT[BINARY] 文本串 65535 characters2~16
[CHARACTER SET charset_name] [COLLATE collation_name]
|MEDIUMTEXT[BINARY]中等的文本串 16,777,215 characters 2~24
[CHARACTER SET charset_name] [COLLATE collation_name]
|LONGTEXT[BINARY] 长的文本串4,294,967,295 characters2~32
[CHARACTER SET charset_name] [COLLATE collation_name]
|ENUM(value1,value2,value3,...) 枚举型
[CHARACTER SET charset_name] [COLLATE collation_name]
|SET(value1,value2,value3,...) 集合型
[CHARACTER SET charset_name] [COLLATE collation_name]
|spatial_type 空间的类型
2.4、创建数据库：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name [CHARACTER SET=] [COLLATE]
创建数据库可以设置字符集，排序规则
mysql>SHOW CHARACTER SET; #查看字符集
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
.......
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
........
39 rows in set (0.00 sec)
mysql>SHOW COLLATION; #查看排序规则
+-------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| cp1250_polish_ci | cp1250 | 99 | | Yes | 1 |
...
197 rows in set (0.00 sec)
mysql> show global variables like '%char%';
+------------------+-----------------+
| Variable_name | Value |
+------------------+----------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1|
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
8 rows in set (0.00 sec)
mysql>CREATE DATABASE IF NOT EXISTS students CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';
#创建一个students数据库，字符集为gbk，排序规则为gbk_chinese_ci
Query OK, 1 row affected (0.01 sec)
mysql>\q
Bye
[root@lamp ~]#ls /mydata/data #查看students是否新建成功
ib_logfile1 mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 students
[root@lamp ~]#file /mydata/data/students/db.opt #查看students数据库中db.opt文件类型
/mydata/data/students/db.opt: ASCII text
2.5、修改数据库：
ALTER {DATABASE | SCHEMA} [db_name]alter_specification ...#修改数据库的属性，比如字符集或者排序规则，alter_specification CHARACTER SET = charset_name COLLATE = collation_name
alter_specification包含:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
ALTER {DATABASE | SCHEMA} db_nameUPGRADE DATA DIRECTORY NAME#升级数据库的数据目录
2.6、删除数据库：
DROP{DATABASE | SCHEMA} [IF EXISTS] db_name#删除数据库
2.7、创建表：
2.7.1.直接定义一张空表；col_name 字段名称 col_defination 字段定义
CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination,)
col_defination字段定义包含：data_type字段类型
data_type[NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
Usage:CREATE TABLE tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(20) NOT NULL,Age TINYINT NOT NULL);
#创建一个表tb1，包含三个字段:
id字段为无符号(UNSIGNED)，非空(NOT NULL)，自动增长(AUTO_INCREMENT)，为主键(PRIMARY KEY)的整型.
Name字段为定长20(CHAR(20)),非空的字符型。
Age字段为非空的非常小的整型。
或者 CREATE TABLE tb2(id INT UNSIGNED NOT NULL AUTO_INCREMENT,Name CHAR(20) NOT NULL,Age TINYINT NOT NULL,PRIMARY KEY(id),Unique KEY (Name),INDEX(age)); #Unique KEY 唯一键，INDEX索引
2.7.2.从其他表中查询出数据，并以之创建新表；
CREATE TABLE testcourses SELECT * FROM courses WHERE CID <= 2;
#从courses表中查找CID小于等于2的数据，并作为新建testcourses表的内容。
2.7.3.以其他表为模板创建一个空表；
查看表索引：
SHOW INDEXES FROM courses;显示制定表索引
查看表结构：
DESC tb_name;查看表结构
mysql>DESC courses;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| CID | tinyint(3) unsigned | NO | PRI |NULL|auto_increment|
| Couse | varchar(50) | NO | | NULL | |
2 rows in set (0.00 sec)
2.8、修改表定义：ALTER TABLE
添加、删除、修改字段，添加、删除、修改索引，改表名，修改表属性。
mysql>ALTER TABLE test ADD INDEX(Couse); #给test表增加以Couse字段为索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>SHOW INDEXES FROM test; #查看test表的索引
+------+-------+-------+------+------+-------+-------+-------+------+------+-------+-------+-------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------+-------+-------+------+------+-------+-------+
| test | 0 | PRIMARY | 1 | CID | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | Couse | 1 | Couse | A | NULL | NULL | NULL | | BTREE | | |
2 rows in set (0.00 sec)
mysql>DESC test; #查看表结构
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+
| CID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
|Couse | varchar(50)| NO | MUL | NULL | |
2 rows in set (0.00 sec)
mysql>ALTER TABLE test CHANGE Couse Course VARCHAR(50) NOT NULL; #修改test表的Couse字段名称为Course并定义为变长50字符长度，非空
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>DESC test; 查看表结构
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+
| CID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
|Course | varchar(50)| NO | MUL | NULL | |
2 rows in set (0.00 sec)
mysql>DROP TABLE testcourses; #删除testcourses表
Query OK, 0 rows affected (0.00 sec)
mysql>SHOW TABLES;查看所有表
+--------------------+
| Tables_in_students |
+--------------------+
| courses |
|test|
+--------------------+
2 rows in set (0.00 sec)
mysql>ALTER TABLE test RENAME TO testcourses; #修改test表的名称为testcourses
Query OK, 0 rows affected (0.00 sec)
mysql>SHOW TABLES;查看所有表
+--------------------+
| Tables_in_students |
+--------------------+
| courses |
|testcourses |
2 rows in set (0.00 sec)
mysql>RENAME TABLE testcourses TO test; #也可以直接使用RENAME重命名。
Query OK, 0 rows affected (0.00 sec)
2.9、新增索引：（索引只能新建删除，不能修改）
CREATE INDEX index_name ON tb_name (col,...)col_name (length) ASC|DESC
指定以字段前几的长度为索引，ASC升序排列，DESC降序排列。
在tb_name表上的col字段创建一个索引index_name
CREATE INDEX name_on_student ON student (Name) USING BTREE;
#在student表中Name字段上建立一个名为name_on_student索引，类型为BTREE索引，默认为BTREE类型。
mysql>CREATE INDEX name_on_student ON student (Name) USING BTREE ;
mysql>SHOW INDEXES FROM student ;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name| Seq_in_index|Column_name|Collation|Cardinality| Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+
| student | 0 |PRIMARY| 1 | SID | A| 4 |NULL | NULL| | BTREE| | |
| student | 1 |foreign_cid| 1 | CID | A| 4 |NULL | NULL| | BTREE | | |
| student | 1 |name_on_student| 1 | Name| A| 4 | NULL | NULL| YES| BTREE | | |
3 rows in set (0.00 sec)
mysql>DROP INDEX name_on_student ON student;#删除student表中的索引name_on_student
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>CREATE INDEX name_on_student ON student (Name(5) DESC);#为student表以Name字段
的前5个字符建立一个降序（DESC）排列的索引.
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
实例1：创建一个students数据库，以及表的创建，查找等功能的练习；
1.1.创建一个students数据库
mysql>CREATE DATABASE IF NOT EXISTS students CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';
#字符集为gbk，排序规则为gbk_chinese_ci
Query OK, 1 row affected (0.01 sec)
mysql>USE students;
Database changed
mysql>CREATE TABLE courses(CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Couse VARCHAR(50) NOT NULL);
Query OK, 0 rows affected (0.07 sec)
mysql>SHOW TABLE STATUS LIKE 'courses'\G;
************************ 1. row ***********************
Name: courses
Engine: InnoDB #数据库引擎
Version: 10
Row_format: Compact #类似压缩格式存储
Rows: 0
Avg_row_length: 0
Data_length: 16384
...
1.2.删除表，新建引擎为MyISAM的新表
mysql>DROP TABLES courses; #删除表
Query OK, 0 rows affected (0.01 sec)
mysql>CREATE TABLE courses(CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Couse VARCHAR(50) NOT NULL) ENGINE=MyISAM; #ENGINE设定引擎为MyISAM
Query OK, 0 rows affected (0.00 sec)
mysql>SHOW TABLE STATUS LIKE 'courses' \G;
*************** 1. row *************
Name: courses
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
...
1 row in set (0.00 sec)
1.3.1新表中加入值
mysql>INSERT INTO courses (Couse) values ('physics'),('english'),('chemistry'),('maths');
#插入Couse课程字段数据，添加物理，英语，化学，数学等课程。
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>SELECT * FROM courses; #查询courses表的条目
+-----+-----------+
| CID | Couse |
+-----+-----------+
| 1 | physics |
| 2 | english |
| 3 | chemistry|
| 4 | maths |
+-----+-----------+
4 rows in set (0.00 sec)
mysql>SHOW INDEXES FROM courses; #查看courses表的索引 -B树索引
+---------+------------+----------+--------------+
| Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+
| courses | 0 | PRIMARY | 1 | CID | A | 4 | NULL | NULL | | BTREE | | |
1 row in set (0.00 sec)
1.3.2新表中加入查询值，但生成的表与原表格式会不一致，可先创建表再插入值；
mysql>CREATE TABLE testcourses SELECT * FROM courses WHERE CID <= 2;
#查找courses表中CID字段小于等于2的数据，并把查找到的数据作为新建testcourses表的数据内容。
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>SHOW TABLES; #查看当前数据库表的信息
+--------------------+
| Tables_in_students |
+--------------------+
|courses |
|testcourses |
+--------------------+
2 rows in set (0.00 sec)
mysql>SELECT * FROM testcourses; #查看testcourses表的内容
+-----+---------+
| CID | Couse |
+-----+---------+
| 1 |physics|
| 2 |english|
+-----+---------+
2 rows in set (0.00 sec)
mysql>DESC courses; #查看courses表结构
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+
| CID | tinyint(3) unsigned | NO | PRI | NULL| auto_increment |
| Couse | varchar(50) | NO | | NULL | |
2 rows in set (0.00 sec)
mysql>DESC testcourses;#查看testcourses表结构与上面的courses不一样
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+
| CID | tinyint(3) unsigned | NO | | 0 | |
| Couse | varchar(50) | NO | | NULL | |
+-------+---------------------+
2 rows in set (0.00 sec)
mysql>CREATE TABLE test LIKE courses; #以courses表为模板创建test空表。
Query OK, 0 rows affected (0.00 sec)
mysql>DESC test; #查看test表结构
+-------+---------------------+------+-----+---------+----------------+
| Field | Type| Null | Key | Default | Extra |
+-------+---------------------+
| CID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| Couse | varchar(50) | NO | | NULL | |
2 rows in set (0.00 sec)
mysql>SHOW TABLE STATUS LIKE 'test'\G; #查看test表的状态
*************** 1. row *********
Name: test
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: 1
Create_time: 2017-04-25 11:31:46
Update_time: 2017-04-25 11:31:46
Check_time: NULL
Collation: gbk_chinese_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
实例2.建立student表，并进行相关数据的插入，查询操作练习，修改引擎，修改字段修饰，增加外键索引；
2.1.建立student表
mysql>CREATE TABLE student (SID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,NameVARCHAR(30),CID INT NOT NULL);
#创建student表，包含3个字段，SID字段 为无符号、非空、自动增长、主键的整数型，
Name字段 为变长30字符，
CID字符 为非空整数型。
mysql>SHOW TABLES;
+--------------------+
| Tables_in_students |
+--------------------+
| courses |
|student|
| testcourses |
3 rows in set (0.00 sec)
2.2.在student表中插入数据
mysql>INSERT INTO student (Name,CID) VALUES ('Li Li',1),('ChengChen',2);#对Name,CID字段插入2条数据。
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>SELECT * FROM student; #查询student表
+-----+------------+-----+
| SID | Name | CID |
+-----+------------+-----+
| 1 | Li Li | 1 |
| 2 | ChengChen | 2 |
2 rows in set (0.00 sec)
2.3.在student表中查询数据
mysql>SELECT * FROM courses; #查询courses表
+-----+-----+
| CID| Couse|
+-----+-----+
|1|physics|
|2|english|
|3|chemistry|
|4| maths|
|5| Hamagong|
|6| Pixiejianfa|
|7| Kuihuabaodian |
7 rows in set (0.00 sec)
mysql>SELECT Name,Couse FROM student,courses WHERE student.CID=courses.CID; #查询student表和courses表中CID相同的Name和Couse字段内容
+-----+------+
| Name|Couse|
+------+-----+
| Li Li| physics |
| Cheng Chen | english |
2 rows in set (0.00 sec)
2.4.在student表中删除、修改数据
mysql>DELETE FROM courses WHERE CID > 5; #删除SID大于5的行。
Query OK, 5 rows affected (0.01 sec)
mysql>ALTER TABLE courses ENGINE=Innodb; #修改courses表的引擎为Innodb;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>ALTER TABLE student MODIFY CID TINYINT UNSIGNED NOT NULL;#修改student表中CID字段的修饰MODIFY
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>DESC courses;
+-----+--------+------+-----+---------+-----------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+
| CID | tinyint(3) unsigned | NO | PRI |NULL | auto_increment |
| Couse | varchar(50) | NO | | NULL | |
2 rows in set (0.00 sec)
mysql>DESC student;
+-------+--------+------+-----+---------+-----------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+
| SID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(30)| YES | | NULL | |
| CID | tinyint(3) unsigned | NO | | NULL | |
3 rows in set (0.00 sec)
mysql>ALTER TABLE student ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses (CID);
#为student表的CID字段增加一个外键foreign_cid关联courses表的CID字段。
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>SHOW INDEXES FROM student; #查看student表的索引
+---------+------------+----------+-----------+----------+---------+----------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part| Packed| Null| Index_type| Comment| Index_comment|
+---------+------------+------------+
|student |0 | PRIMARY | 1 | SID | A|2 |NULL | NULL| | BTREE| | |
|student |1 |foreign_cid| 1 | CID | A|2 |NULL | NULL| | BTREE| | |
2 rows in set (0.00 sec)
mysql>INSERT INTO student (Name,CID) VALUES ('Guo Xiang',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`students`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`))
#提示报错，由于CID外键索引courses表中CID没有5，所以无法增加。
mysql>ALTER TABLE student AUTO_INCREMENT=5;#设定student表下一条数据的自动增长主键SID从5开始增长。
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>SELECT * FROM student; #查询表的内容
+-----+----------+-----+
| SID | Name| CID |
+-----+--------+-----+
| 1 | Li Lianjie | 1 |
| 2 | Cheng Long | 2 |
| 3 | Xiao Longnv | 3 |
| 4 | Yang Guo | 4 |
4 rows in set (0.00 sec)
mysql>INSERT INTO student (Name,CID) VALUES ('Guo Xiang',3);
#插入一条数据，SID主键由于上面设置从5开始增长，所以刚插入的数据是从5开始；
Query OK, 1 row affected (0.00 sec)
mysql>SELECT * FROM student;
+-----+----------+-----+
| SID | Name | CID |
+-----+-------------+-----+
| 1 | Li Lianjie| 1 |
| 2 | Cheng Long | 2 |
| 3 | Xiao Longnv | 3 |
| 4 | Yang Guo | 4 |
|5| Guo Xiang | 3 |
5 rows in set (0.00 sec)
mysql>INSERT INTO student (Name,CID) VALUES ('Qiao Feng',2);插入数据
Query OK, 1 row affected (0.00 sec)
mysql>SELECT * FROM student;
mysql>DELETE FROM student WHERE SID >2 AND SID <5; #删除2<SID<5的行
Query OK, 2 rows affected (0.00 sec)
mysql>SELECT * FROM student; #验证
+-----+------------+-----+
| SID | Name | CID |
| 1 | Li Lianjie | 1 |
| 2 | Cheng Long | 2 |
| 5 | Guo Xiang | 3 |
| 6 | Qiao Feng | 2 |
4 rows in set (0.00 sec)
mysql>DELETE FROM student WHERE SID in (5,6); #删除SID为5和6的行
Query OK, 2 rows affected (0.00 sec)
mysql>SELECT * FROM student;
+-----+------------+-----+
| SID | Name | CID |
+-----+------------+-----+
| 1 | Li Lianjie | 1 |
| 2 | Cheng Long | 2 |
2 rows in set (0.00 sec)
mysql>ALTER TABLE student AUTO_INCREMENT=3;#设定表下一条数据的自动增长主键SID从3开始增长
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>INSERT INTO student (Name,CID) VALUES ('Yang Guo',3),('Guo Jing',4);#插入2条数据
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>SELECT * FROM student;
+-----+------------+-----+
| SID | Name | CID |
+-----+------------+-----+
| 1 | Li Lianjie | 1 |
| 2 | Cheng Long | 2 |
| 3| Yang Guo | 3 |
| 4| Guo Jing | 4 |
4 rows in set (0.00 sec)
2.5.在student表中创建索引，实现比较或排序
mysql> SELECT * FROM student;
+-----+------------+-----+
| SID | Name | CID |
+-----+------------+-----+
| 1 | Li Li | 1 |
| 2 | Cheng Chen | 2 |
| 3 | YangGuo | 3 |
| 4 | GuoJing | 4 |
4 rows in set (0.00 sec)
mysql> CREATE INDEX name_on_student ON student (Name) USING BTREE;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP INDEX name_on_student ON student; #索引只能新建或删除,因其是结构无法修改
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE INDEX name_on_student ON student (Name(5) DESC) USING BTREE;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEXES FROM student;
+---------+------------+-----------------+--------------+-------------+-----------+
| Table| Non_unique| Key_name| Seq_in_index| Column_name| Collation| Cardinality| Sub_part | Packed | Null| Index_type| Comment| Index_comment |
+---------+------------+-----------------+
| student|0 | PRIMARY| 1 | SID | A | 2 | NULL | NULL | BTREE | | |
| student|1 | foreign_cid | 1| CID | A |2 | NULL | NULL| | BTREE | | |
| student|1 | name_on_student|1| Name| A |4 | 5| NULL | YES| BTREE | | |
3 rows in set (0.00 sec)
---end-6---
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
