mysql创建表索引，视图，修改表设计

表索引创建

在已存在的表示创建索引

# 常规索引
MariaDB [my_test]> create index ename_index on emp(ename);

# 唯一索引
create unique index deptno_index on emp(deptno);

# 全文索引：
create fulltext index job_index on emp(job);

# 多列索引：
create index comm_index on emp(dept_name,comment)

MariaDB [my_test]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| empno    | int(11)       | NO   | PRI | NULL    |       |
| ename    | varchar(20)   | NO   |     | NULL    |       |
| job      | varchar(20)   | YES  |     | NULL    |       |
| mgp      | int(11)       | YES  |     | NULL    |       |
| hiredate | datetime      | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| comm     | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(11)       | NO   | MUL | NULL    |       |
+----------+---------------+------+-----+---------+-------+
8 rows in set (0.04 sec)
MariaDB [my_test]> create index ename_index on emp(ename);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [my_test]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| empno    | int(11)       | NO   | PRI | NULL    |       |
| ename    | varchar(20)   | NO   | MUL | NULL    |       |
| job      | varchar(20)   | YES  |     | NULL    |       |
| mgp      | int(11)       | YES  |     | NULL    |       |
| hiredate | datetime      | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| comm     | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(11)       | NO   | MUL | NULL    |       |
+----------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

MariaDB [my_test]> 

alter table 在已存在的表上创建索引

语法：

alter table 表名  add [unique | fulltext | spatial ] index 索引名称 (字段名[(长度)] [asc | desc]);

# 创建唯一索引示例：
alter table emp add unique index deptno_index(deptno);

管理索引

• 查看索引：show create table 表名\G;

索引删除

• drop index 索引名 on 表名;

MariaDB [my_test]> drop index ename_index on emp;

MariaDB [my_test]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| empno    | int(11)       | NO   | PRI | NULL    |       |
| ename    | varchar(20)   | NO   | MUL | NULL    |       |
| job      | varchar(20)   | YES  |     | NULL    |       |
| mgp      | int(11)       | YES  |     | NULL    |       |
| hiredate | datetime      | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| comm     | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(11)       | NO   | MUL | NULL    |       |
+----------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

MariaDB [my_test]> drop index ename_index on emp;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [my_test]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| empno    | int(11)       | NO   | PRI | NULL    |       |
| ename    | varchar(20)   | NO   |     | NULL    |       |
| job      | varchar(20)   | YES  |     | NULL    |       |
| mgp      | int(11)       | YES  |     | NULL    |       |
| hiredate | datetime      | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| comm     | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(11)       | NO   | MUL | NULL    |       |
+----------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

MariaDB [my_test]> 

有索引时导入海量数据非常耗时间，这时候可以将所有删除，数据导入成功后在创建索引

创建视图

创建视图

• 视图是一个虚表，是储存在数据库中的SQL查询语句，它主要处于两种原因：安全原因，他可以隐藏一部分数据；第二个原因是是复杂的查询已于理解和使用。

• 语法：create view 视图名 as select语句

# 创建视图：emp_base
MariaDB [my_test]> create view emp_base as select empno,deptno,ename,job from emp;
Query OK, 0 rows affected (0.06 sec)

MariaDB [my_test]> select * from emp_base;
+-------+--------+--------+----------+
| empno | deptno | ename  | job      |
+-------+--------+--------+----------+
|  7369 |     20 | SMITH  | CLERK    |
|  7499 |     30 | ALLEN  | SALESMAN |
|  7521 |     30 | WARD   | SALESMAN |
|  7566 |     20 | JOENS  | MANAGER  |
|  7654 |     30 | MARTIN | SALESMAN |
|  7698 |     30 | BLAKE  | MANAGER  |
|  7782 |     10 | CLARK  | MANAGER  |
|  7788 |     20 | SCOTT  | ANALYST  |
|  7844 |     30 | TURNER | SALESMAN |
|  7876 |     20 | ADAMS  | CLERK    |
|  7900 |     30 | JAMES  | CLERK    |
|  7902 |     20 | FORD   | ANALYST  |
|  7934 |     10 | MILLER | CLERK    |
+-------+--------+--------+----------+
13 rows in set (0.00 sec)

MariaDB [my_test]> 

当视图不需要时，可以将其删除

• 删除视图，必须拥有DROP权限
• 语法格式
  DROP VIEW [IF EXISTS]
  View_name [,view_name1]……
  [RESTRICT | CASCADE]

MariaDB [my_test]> drop view if exists uu;
Query OK, 0 rows affected (0.00 sec)

MariaDB [my_test]> select * from uu;
ERROR 1146 (42S02): Table 'my_test.uu' doesn't exist
MariaDB [my_test]> 

修改表

# 修改表名
alter table  emp_tatal rename to emp_total(total int(4));
# 修改列名
alter table emp_total change tatal total int(4);