MySQL管理表和索引

SQL语句

数据库
表

数据库

操作	语法定义	实例
创建数据库	CREATE {DATABASE | SCHEMA（方案）} [IF NOT EXISTS] db_name [create_specification]	mysql> create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci';创建一个字符集为gbk，排序规则是’gbk_chinese_ci’的方案或者数据库
修改数据库	ALTER {DATABASE | SCHEMA} [db_name] alter_specification … ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME
删除数据库	DROP {DATABASE	SCHEMA} [IF EXISTS] db_name

数据库重命名，将存放数据库的文件名称修改。

表

操作	语法格式	实例1	实例2
创建表	CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,…) [table_options] [partition_options]	mysql> create table tb2 (id int unsigned not null auto_increment primary key, name char(30) not null, age tinyint not null);	索引或者主键单独占一个字段 mysql> create table tb3 (id int unsigned not null auto_increment, name char(30) not null, age tinyint not null,primary key(id));
删除表	DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] … [RESTRICT | CASCADE]	mysql> drop table courses;
修改表	ALTER [ONLINE |OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] …] [partition_options]	添加、删除、修改字段；添加、删除、修改索引；该表名；修改表属性

创建表的三种方式：

1.直接定义一张空表

mysql> create table tb3 (id int unsigned not null auto_increment, name char(30) not null, age tinyint not null,primary key(id));

2.从其他的表中查询的数据，并以之为新表数据

mysql> create table testcourses select * from courses where cid <=2;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

3.以其他的表的类型创建新的表。

mysql> create table test like courses;
Query OK, 0 rows affected (0.08 sec)

mysql> desc test;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| cid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| course | varchar(50)         | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

键

键，也称作约束，可以用作索引，是特殊的索引（有特殊的限定），：B+Tree索引

主键、唯一键、索引

创建表的时候创建主键、唯一键、索引
primary key
unique key

单或多字段
primary key （col,….）
unique key（col,…）
index(col,…)

单个字段为主键

• 在字段上设置主键

  mysql> create table tb2 (id int unsigned not null auto_increment primary key, name char(30) not null, age tinyint not null);

• 单独一个字段设置主键

  mysql> create table tb3 (id int unsigned not null auto_increment, name char(30) not null, age tinyint not null,primary key(id));

多个字段组合成主键

mysql> create table tb3 (id int unsigned not null auto_increment, name char(30) not null, age tinyint not null,primary key(id，name));

外键

• Innodb存储引擎是支持事务的存储引擎才能支持外键
• 设置外键关联，必须要将设置的字段的type确保一致，否则无法关联。

格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)


create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...

  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL （完全匹配）| MATCH PARTIAL（部分匹配） | MATCH SIMPLE]
      [ON DELETE reference_option]  被参考的表修改，该表也修改
      [ON UPDATE reference_option]  被参考的表更新，该表也更新

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

将student的cid和courses的cid设置外键关联。

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
| cid   | tinyint(3)  | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc courses;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| cid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| course | varchar(50)         | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> alter table student modify cid tinyint(3) unsigned not null;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table student add foreign key foreign_cid(cid) references courses(cid);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

索引

创建索引

Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...
index_col_name:
    col_name [(length)] [ASC | DESC]
    length：从左向右比较的个数
    ASC：升序排列
    DESC：降序排列

在student的表的name字段上创建一个名称为name_on_student_ind的索引

mysql> create index name_on_student_ind on student (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引

mysql> drop index name_on_student_ind on student;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表的状态

mysql> show table status like 'courses'\G
*************************** 1. row ***************************
           Name: courses
         Engine: InnoDB      #innodb存储引擎
        Version: 10
     Row_format: Compact     # 行格式，压缩格式，紧凑格式
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-06-24 00:44:22
    Update_time: NULL
     Check_time: NULL
      Collation: gbk_chinese_ci   # 排序规则，从数据库继承
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

查看表结构

mysql> desc testcourses;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| cid    | tinyint(3) unsigned | NO   |     | 0       |       |
| course | varchar(50)         | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

查看表的索引

字段简介	含义
Non_unique	是不是非唯一，如果是0代表唯一；如果是1，代表唯一
Key_name	键名称，primary 主键
Seq_in_index	一个表上的第几个索引
Column_name	索引所在的字段
Collation	排序规则
Cardinality
Sub_part
Packed
Null
Index_type	索引类型
Comment
Index_comment

mysql> show indexes from courses;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

修改表

添加、删除、修改字段

添加新字段

mysql> alter table test add starttime date default '2013-04-12';
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表字段

mysql> alter table tb3 drop cid;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段名称

mysql> alter table test change course object varchar(50) not null;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加、删除、修改索引

添加唯一索引

mysql> alter table test add unique key (course);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引

mysql> alter table tb3 drop primary key ;
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表名

alter将test表名修改为testcourse

mysql> alter table test rename to testcourse;
Query OK, 0 rows affected (0.00 sec)

rename命令修改tb2表为tbtwo

mysql> rename table tb2 to tbtwo;
Query OK, 0 rows affected (0.03 sec)

修改表约束

mysql> alter table tb3 modify speedweek tinyint(2) not null default 12;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表的存储引擎

修改存储引擎底层的操作原理：自动创建一个新表，将数据存入到 新表中，将旧表删除。

mysql> alter table tb3 engine=Innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

实例1,创建一个课程表并查看状态

mysql> create table courses(cid tinyint unsigned not null auto_increment primary key,course varchar(50) not null);
Query OK, 0 rows affected (0.04 sec)

查看表的状态

mysql> show table status like 'courses'\G
*************************** 1. row ***************************
           Name: courses
         Engine: InnoDB      #innodb存储引擎
        Version: 10
     Row_format: Compact     # 行格式，压缩格式，紧凑格式
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-06-24 00:44:22
    Update_time: NULL
     Check_time: NULL
      Collation: gbk_chinese_ci   # 排序规则，从数据库继承
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)