您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
马哥linux运维学习笔记-MySQL系列之六——MySQL管理表和索引
发布时间：2019-03-15 17:49:39编辑：雪饮阅读（）
创建数据库
mysql> create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci';
Query OK, 1 row affected (0.00 sec)
schema：创建数据库的关键字
character:指定字符集
collate:指定字符排序
查看一个表的索引列表
mysql> show indexes from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | |
| user | 0 | PRIMARY | 2 | User | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
通过已知表来创建一个新表并复制已知表部分数据到新表
新表会丢失掉原表的某些格式、属性
mysql> create table testCourses select * from courses where cid <=2;
Query OK, 2 rows affected (0.02 sec)
Records: 2 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 testCourses;
+-------+---------------------+------+-----+---------+-------+
| 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 test2 like courses;
Query OK, 0 rows affected (0.01 sec)
原表属性
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 test2;
+-------+---------------------+------+-----+---------+----------------+
| 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> alter table test2 add unique key (couse);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改表名
mysql> alter table testCourses rename to test1;
Query OK, 0 rows affected (0.01 sec)
修改表引擎
mysql> alter table courses engine=innodb;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show table status like 'courses'\G
*************************** 1. row ***************************
Name: courses
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4
Create_time: 2019-03-15 04:58:45
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
为一个表添加外键
前提条件：当前表与其所参考表的引擎都必须是innodb
mysql> alter table student add foreign key foreign_cid (cid) references courses (cid);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
为一个表指定字段添加索引
mysql> create index name_on_student on student (name) using btree;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
这里指定了索引类型是btree，其实mysql默认索引类型就是btree，所以也可以不指定
从一个表中删除指定索引
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.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
为某个字段添加索引时若指定索引对该字段比较长度则在性能上会有优势，一般的比较长度越小性能越好
为某个字段添加索引时若指定索引对该字段的排序规则，则一般的在sql查询时若正好和该排序规则相符合则sql执行性能也会有所提升。
查看一个表的索引列表
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 | 1 | foreign_cid | 1 | cid | A | 1 | NULL | NULL | | BTREE | | |
| student | 1 | name_on_student | 1 | name | A | 1 | 5 | NULL | | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
关键字词：linux,mysql,索引