MySQL数据库以及表的管理

　　　　　　　　　　　　　　　MySQL数据库以及表的管理

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版权声明：原创作品，谢绝转载！否则将追究法律责任。

 

 

　　今天我们探讨的话题就是如何使用MySQL做开发，我们运维的主要工作不是去开发SQL的，但尽管如此，我们有可能需要对别人写出来的复杂SQL语句做性能评估，去分析他们写的SQL语句是不是足够高效，如果不是的话，我们还要能够达到对对方的SQL语句做改写的能力。所以，我们无需自行去开发SQL，但是我们一定要有一个看懂别人写的SQL的能力哟！
 
 
 
一.DBA的职责
1.开发DBA：
　　负责数据库设计（E-R关系图）、sql开发、内置函数、存储历程（存储过程和存储函数）、触发器、时间调度器（event scheduler）
2.管理DBA：
　　负责安装、升级、备份、恢复、用户管理、权限管理、监控、分析、基准测试，语句优化（SQL语句）、数据字典，按需要配置服务器（服务器变量：MyISAM，InnoDB，缓存，日志）
 
二.SQL语言组成部分
1.DDL：
　　全称Data Defination,我们叫它数据定义语言，典型命令指令有CREAT/ALTER/DROP
2.DML：
　　全称Data Manipulation,我们叫它数据操作语言，典型命令有INSERT/DELETE/SELECT/UPDATE
3.完整性定义语言，DDL的一部分功能
　　主键约束、外键约束、唯一键约束、条件约束、非空约束、事务约束
4.视图定义：即虚表，它是存储下来的select语句
5.事务控制：
　　例如Transactions（在mysql交互界面执行“HELP content”可以查看相关信息。）
6.嵌入式SQL和动态SQL：
7.DCL：
　　我们叫它数据控制语言，如实现授权和权限收回的典型命令有GRANT/REVOKE.
 
三.数据类型的功用
1.存储的值类型;
2.占据的存储空间大小;
3.定长，变长;
4.如何被索引及排序;
5.是否能够被索引;
 
四.数据字典：依赖系统编目（花名册）（system catalog）
　　对于关系型数据库来讲，它的数据字典也是另外找个地方存起来的。对于MySQL数据库来讲，这个位置就是名称为mysql的数据库。我们在第一次启动MySQL时，它第一步工作就是初始化系统表，所谓初始化系统表就是用来创建mysql这个数据库的。我们也可以称这个mysql数据库叫做MySQL的数据字典。数据字典是用来保存数据库服务器上的元数据。那么什么是元数据呢？我总结有以下几点：
1>.保存关系（表）的名字
2>.保存每个关系（表）的各字段的名字
3>.保存各字段的数据类型和长度
4>.保存约束条件
5>.保存每个关系（表）上的视图的名字及视图的定义
6>.保存授权用户（user表）的名字
7>.保存用户的授权和账户信息等
8>.统计类的数据，如每个关系字段的个数，每个关系中行数，每个关系的存储方法
9>.保存元数据的数据库（ 即：information_schema，mysql， performance_schema）
 
 
五.MySQL内部组件
　　如下图所示，连接器（Connectors）和连接池他们之间是建立连接关系的。连接池（Connection Pool）的所有SQL语句都得发送给SQL接口（SQL Interface）进行接收，然后再由分析器（Parser）进行分析，由优化器（Optimizer）进行优化处理，最终我们有可能在缓存（Caches&Buffers）中获取数据，实在再不行在交由存储引擎（Pluggable Storage Engines）去执行SQL语句。

 

六.MySQL中字符大小写情况说明
1.SQL关键字及函数不区分大小写;
2.数据库、表及视图名称的大小写区分与否取决于底层OS(操作系统)及FS（文件系统）;
3.存储过程、存储函数及事件调度器的名字不区分大小写，但触发器区分大小写;
4.表别名区分大小写;
5.对字段中的数据，如果字段类型为binary类型，则区分大小写，非binary不区分大小写;
 
七.SQL指令详解-数据库操作
1.数据库的创建
a>.查看创建库时的帮助信息
 1 mysql> help create database            
 2 Name: 'CREATE DATABASE'
 3 Description:
 4 Syntax:
 5 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
 6     [create_specification] ...
 7 
 8 create_specification:
 9     [DEFAULT] CHARACTER SET [=] charset_name            　　 #设置字符集
10   | [DEFAULT] COLLATE [=] collation_name                    #设置排序方式
11 
12 CREATE DATABASE creates a database with the given name. To use this
13 statement, you need the CREATE privilege for the database. CREATE
14 SCHEMA is a synonym for CREATE DATABASE.
15 
16 URL: http://dev.mysql.com/doc/refman/5.1/en/create-database.html
17 
18 
19 mysql>
b>.创建不存在的数据库
 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | test               |
 8 +--------------------+
 9 3 rows in set (0.00 sec)
10 
11 mysql> 
12 mysql> create database yinzhengjie;
13 Query OK, 1 row affected (0.01 sec)
14 
15 mysql> show databases;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mysql              |
21 | test               |
22 | yinzhengjie        |
23 +--------------------+
24 4 rows in set (0.00 sec)
25 
26 mysql>
c>.已经存在数据库的如何使用创建命令
 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | test               |
 8 | yinzhengjie        |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 mysql> create database if not exists yinzhengjie;
13 Query OK, 1 row affected, 1 warning (0.00 sec)
14 
15 mysql> show databases;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mysql              |
21 | test               |
22 | yinzhengjie        |
23 +--------------------+
24 4 rows in set (0.00 sec)
25 
26 mysql>
2.数据库的删除
a>.查看删除数据库时的帮助信息
  
 1 mysql> help drop database;
 2 Name: 'DROP DATABASE'
 3 Description:
 4 Syntax:
 5 DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
 6 
 7 DROP DATABASE drops all tables in the database and deletes the
 8 database. Be very careful with this statement! To use DROP DATABASE,
 9 you need the DROP privilege on the database. DROP SCHEMA is a synonym
10 for DROP DATABASE.
11 
12 *Important*: When a database is dropped, user privileges on the
13 database are not automatically dropped. See [HELP GRANT].
14 
15 IF EXISTS is used to prevent an error from occurring if the database
16 does not exist.
17 
18 URL: http://dev.mysql.com/doc/refman/5.1/en/drop-database.html
19 
20 
21 mysql>
b>.删除数据库操作
 1 mysql> show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | test               |
 8 | yinzhengjie        |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 mysql> drop database if exists yinzhengjie;
13 Query OK, 0 rows affected (0.00 sec)
14 
15 mysql> show databases;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mysql              |
21 | test               |
22 +--------------------+
23 3 rows in set (0.00 sec)
24 
25 mysql>
3.修改数据库的字符集和排序字符以及数据字典
 1 mysql> help alter database
 2 Name: 'ALTER DATABASE'
 3 Description:
 4 Syntax:
 5 ALTER {DATABASE | SCHEMA} [db_name]
 6     alter_specification ...
 7 ALTER {DATABASE | SCHEMA} db_name
 8     UPGRADE DATA DIRECTORY NAME
 9 
10 alter_specification:
11     [DEFAULT] CHARACTER SET [=] charset_name        #设置字符集
12   | [DEFAULT] COLLATE [=] collation_name                #修改排序方式
13 
14 ALTER DATABASE enables you to change the overall characteristics of a
15 database. These characteristics are stored in the db.opt file in the
16 database directory. To use ALTER DATABASE, you need the ALTER privilege
17 on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.
18 ....
19 mysql>
4.数据库改名操作
　　如果我们在创建数据库的时候把数据库的名字起错了，这个时候你想要讲数据库名字改正过来。其实目前修改数据库名称基本上没有什么很好的办法，一个比较明智的做法就是讲该数据库的数据全部备份出来，然后将该库删除掉，创建你想要的数据库名称然后再把数据导入进去。还有一种非常暴力的做法就是去MySQL数据库目录下将创建错的目录进行改名操作，这种做法虽然是把数据库名称改正过来了，但是对于该库的数据字典并没有修改哟，因此这种做法我是不推荐去这样做的。
 
 
八.SQL指令详解-表的基本操作
1.MyISAM和InnoDB存储的区别概要
 1 MyISAM表，每个表有三个文件，都位于数据库目录中
 2 　　tb_name.frm:表结构定义
 3 　　tb_name.MYD:数据文件
 4 　　tb_name.MYI:索引文件
 5 InnoDB表，有两种存储方式
 6 　　第一种(默认方式)：每表有一个独立文件和一个多表共享的文件
 7 　　　　tb_name.frm：表结构的定义，位于数据库目录中
 8 　　　　ibdata#：共享的表空间文件，默认位于数据目录（datadir指向的目录）中
 9 　　第二种（独立的表空间文件，推荐使用这一种方式）：
10 　　　　tb_name.frm：每表有一个表结构文件
11 　　　　tb_name.ibd：一个独立的表空间文件
12 　　应该修改innodb_file_per_table为ON，我们可以在mysql的配置文件中的[msyqld]下的字段修改它的值为NO即可完成永久生效哟。
2.表的第一种方式，即自定义新表格式
 1 CREATE [TEMPORARY（临时表，保存在内存中）] TABLE [IF NOT EXISTS] tbl_name
 2 (create_definition,...)
 3 [table_options]
 4 (create_definition,...)
 5 字段的定义：字段名、类型和类型修饰符
 6 键、索引和约束
 7 primary key，unique key，foreign key，check
 8 {index|key}
 9 [table_options]
10 engine [=] engine_name
11 AUTO_INCREMENT [=] value 指定AUTO_INCREMENT的起始值
12 [DEFAULT] CHARACTER SET [=] charset_name 指定默认字符集
13 CHECKSUM [=] {0 | 1} 是否使用校验值
14 [DEFAULT] COLLATE [=] collation_name 排序规则
15 COMMENT [=] 'string' 注释
16 DELAY_KEY_WRITE [=] {0 | 1} 是否启用键延迟写入
17 ROW_FORMAT [=] {DEFAULT（默认）|DYNAMIC（动态）|FIXED（静态）|COMPRESSED（压缩）|REDUNDANT（冗余）|COMPACT（紧致）} 表格式
18 TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 表空间
 1 mysql> help create table
 2 Name: 'CREATE TABLE'
 3 Description:
 4 Syntax:
 5 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 6     (create_definition,...)
 7     [table_options]
 8     [partition_options]
 9 ....
10 mysql>
查看创建表的帮助信息
 1 mysql> select database();
 2 +------------+
 3 | database() |
 4 +------------+
 5 | NULL       |
 6 +------------+
 7 1 row in set (0.00 sec)
 8 
 9 mysql> 
10 mysql> show databases;
11 +--------------------+
12 | Database           |
13 +--------------------+
14 | information_schema |
15 | mysql              |
16 | test               |
17 +--------------------+
18 3 rows in set (0.00 sec)
19 
20 mysql> create database if not exists yinzhengjie;
21 Query OK, 1 row affected (0.00 sec)
22 
23 mysql> use yinzhengjie
24 Database changed
25 mysql> 
26 mysql> select database();
27 +-------------+
28 | database()  |
29 +-------------+
30 | yinzhengjie |
31 +-------------+
32 1 row in set (0.00 sec)
33 
34 mysql> create table t1 (Name varchar(50) not null,Age tinyint unsigned not null,primary key(Name,Age));            #创建一个表，定义Name字段类型自动变化长度的字符类型（varchar），不能为空，定义一个Age字段类型为微整形（tinyint），也不能为空，定义主键（primary key）是Name和Age两个字段。
35 Query OK, 0 rows affected (0.08 sec)
36 
37 mysql> desc t1;
38 +-------+---------------------+------+-----+---------+-------+
39 | Field | Type                | Null | Key | Default | Extra |
40 +-------+---------------------+------+-----+---------+-------+
41 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
42 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
43 +-------+---------------------+------+-----+---------+-------+
44 2 rows in set (0.00 sec)
45 
46 mysql>
创建一个表包含主键的表
 1 mysql> show table status like 't1'\G;        #查看之前创建表的存储引擎。
 2 *************************** 1. row ***************************
 3            Name: t1
 4          Engine: MyISAM
 5         Version: 10
 6      Row_format: Dynamic
 7            Rows: 0
 8  Avg_row_length: 0
 9     Data_length: 0
10 Max_data_length: 281474976710655
11    Index_length: 1024
12       Data_free: 0
13  Auto_increment: NULL
14     Create_time: 2017-11-17 03:10:55
15     Update_time: 2017-11-17 03:10:55
16      Check_time: NULL
17       Collation: latin1_swedish_ci
18        Checksum: NULL
19  Create_options: 
20         Comment: 
21 1 row in set (0.00 sec)
22 
23 ERROR: 
24 No query specified
25 
26 mysql> 
27 mysql> drop table t1;
28 Query OK, 0 rows affected (0.00 sec)
29 
30 mysql> create table t1 (Name varchar(50) not null,Age tinyint unsigned not null,primary key(Name,Age)) engine='InnoDB';
31 Query OK, 0 rows affected (0.05 sec)
32 
33 mysql> show table status like 't1'\G;
34 *************************** 1. row ***************************
35            Name: t1
36          Engine: InnoDB
37         Version: 10
38      Row_format: Compact
39            Rows: 0
40  Avg_row_length: 0
41     Data_length: 16384
42 Max_data_length: 0
43    Index_length: 0
44       Data_free: 4194304
45  Auto_increment: NULL
46     Create_time: 2017-11-17 03:19:53
47     Update_time: NULL
48      Check_time: NULL
49       Collation: latin1_swedish_ci
50        Checksum: NULL
51  Create_options: 
52         Comment: 
53 1 row in set (0.00 sec)
54 
55 ERROR: 
56 No query specified
57 
58 mysql>
创建表的存储引擎
3.表的第二种创建方式，即复制表数据
1 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
2     [(create_definition,...)]
3     [table_options]
4     select_statement    #将select的结果来作为字段创建新表的字段，但是可能失去属性定义的。
用法格式
 1 mysql> select * from t1;
 2 Empty set (0.01 sec)
 3 
 4 mysql> 
 5 mysql> insert into t1(Name,Age) values ("yinzhengjie",18);
 6 Query OK, 1 row affected (0.00 sec)
 7 
 8 mysql> 
 9 mysql> select * from t1;
10 +-------------+-----+
11 | Name        | Age |
12 +-------------+-----+
13 | yinzhengjie |  18 |
14 +-------------+-----+
15 1 row in set (0.00 sec)
16 
17 mysql> 
18 mysql> create table t2 select * from t1;
19 Query OK, 1 row affected (0.01 sec)
20 Records: 1  Duplicates: 0  Warnings: 0
21 
22 mysql> select * from t2;
23 +-------------+-----+
24 | Name        | Age |
25 +-------------+-----+
26 | yinzhengjie |  18 |
27 +-------------+-----+
28 1 row in set (0.00 sec)
29 
30 mysql>  
31 mysql> desc t1;
32 +-------+---------------------+------+-----+---------+-------+
33 | Field | Type                | Null | Key | Default | Extra |
34 +-------+---------------------+------+-----+---------+-------+
35 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
36 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
37 +-------+---------------------+------+-----+---------+-------+
38 2 rows in set (0.00 sec)
39 
40 mysql> desc t2;            #我们可以发现t2的表中的字段属性和t1的并不一致哟！只是数值一致而已。
41 +-------+---------------------+------+-----+---------+-------+
42 | Field | Type                | Null | Key | Default | Extra |
43 +-------+---------------------+------+-----+---------+-------+
44 | Name  | varchar(50)         | NO   |     | NULL    |       |
45 | Age   | tinyint(3) unsigned | NO   |     | NULL    |       |
46 +-------+---------------------+------+-----+---------+-------+
47 2 rows in set (0.00 sec)
48 
49 mysql>
案例展示
4.表的第三种创建方式，即复制表结构
1  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
2     { LIKE old_tbl_name | (LIKE old_tbl_name) }
用法格式
 1 mysql> select database();
 2 +-------------+
 3 | database()  |
 4 +-------------+
 5 | yinzhengjie |
 6 +-------------+
 7 1 row in set (0.00 sec)
 8 
 9 mysql> show tables;
10 +-----------------------+
11 | Tables_in_yinzhengjie |
12 +-----------------------+
13 | t1                    |
14 | t2                    |
15 +-----------------------+
16 2 rows in set (0.00 sec)
17 
18 mysql> create table t3 like t1;
19 Query OK, 0 rows affected (0.04 sec)
20 
21 mysql> select * from t3;
22 Empty set (0.01 sec)
23 
24 mysql> desc t3;
25 +-------+---------------------+------+-----+---------+-------+
26 | Field | Type                | Null | Key | Default | Extra |
27 +-------+---------------------+------+-----+---------+-------+
28 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
29 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
30 +-------+---------------------+------+-----+---------+-------+
31 2 rows in set (0.00 sec)
32 
33 mysql> desc t1;
34 +-------+---------------------+------+-----+---------+-------+
35 | Field | Type                | Null | Key | Default | Extra |
36 +-------+---------------------+------+-----+---------+-------+
37 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
38 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
39 +-------+---------------------+------+-----+---------+-------+
40 2 rows in set (0.01 sec)
41 
42 mysql>
用法展示
5.创建一张表的思想
　　我们可以自定义表，重新创建表的结构，当然也可以通过第三种方式找一个符合我们要求的表结构复制出来，然后通过insert语句将数据插入到这个心创建的表中即可。
6.表的删除
1 DROP [TEMPORARY] TABLE [IF EXISTS]
2       tbl_name [, tbl_name] ...
3       [RESTRICT | CASCADE]
用法格式
 1 mysql> select database();
 2 +-------------+
 3 | database()  |
 4 +-------------+
 5 | yinzhengjie |
 6 +-------------+
 7 1 row in set (0.00 sec)
 8 
 9 mysql> show tables;
10 +-----------------------+
11 | Tables_in_yinzhengjie |
12 +-----------------------+
13 | t1                    |
14 | t2                    |
15 | t3                    |
16 +-----------------------+
17 3 rows in set (0.00 sec)
18 
19 mysql> drop table t2,t3;
20 Query OK, 0 rows affected (0.07 sec)
21 
22 mysql> show tables;
23 +-----------------------+
24 | Tables_in_yinzhengjie |
25 +-----------------------+
26 | t1                    |
27 +-----------------------+
28 1 row in set (0.00 sec)
29 
30 mysql>
用法展示
7.表的修改操作
 1 ALTER TABLE tbl_name
 2       [alter_specification [, alter_specification] ...]
 3     修改字段定义：
 4        插入新字段：
 5          ADD [COLUMN] col_name column_definition
 6          [FIRST | AFTER col_name ]
 7        删除字段
 8          DROP [COLUMN] col_name
 9        修改字段
10           修改字段名称
11           CHANGE [COLUMN] old_col_name new_col_name column_definition
12           [FIRST|AFTER col_name]
13           修改字段类型及属性等
14           MODIFY [COLUMN] col_name column_definition
15           [FIRST | AFTER col_name]
16        表改名：
17           rename to|as new tb_name
18        修改存储引擎
19            engine = 
20        指定排序标准的字段
21            ORDER BY col_name [, col_name] ...
用法格式集合
 1 mysql> desc t1;
 2 +-------+---------------------+------+-----+---------+-------+
 3 | Field | Type                | Null | Key | Default | Extra |
 4 +-------+---------------------+------+-----+---------+-------+
 5 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
 6 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
 7 +-------+---------------------+------+-----+---------+-------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> alter table t1 add ID tinyint unsigned not null;
11 Query OK, 1 row affected (0.02 sec)
12 Records: 1  Duplicates: 0  Warnings: 0
13 
14 mysql> desc t1;
15 +-------+---------------------+------+-----+---------+-------+
16 | Field | Type                | Null | Key | Default | Extra |
17 +-------+---------------------+------+-----+---------+-------+
18 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
19 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
20 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |
21 +-------+---------------------+------+-----+---------+-------+
22 3 rows in set (0.00 sec)
23 
24 mysql>
插入新字段用法展示
 1 mysql> desc t1;
 2 +-------+---------------------+------+-----+---------+-------+
 3 | Field | Type                | Null | Key | Default | Extra |
 4 +-------+---------------------+------+-----+---------+-------+
 5 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
 6 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
 7 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |
 8 +-------+---------------------+------+-----+---------+-------+
 9 3 rows in set (0.00 sec)
10 
11 mysql> 
12 mysql> alter table t1 add Gender enum('boy','girl') not null default 'boy' after Age;    #插入的字段我们用关键字“after”指定在“Age”之后。（注意，如果你使用first则表示插入在第一个字段哟）
13 Query OK, 1 row affected (0.05 sec)
14 Records: 1  Duplicates: 0  Warnings: 0
15 
16 mysql> desc t1;
17 +--------+---------------------+------+-----+---------+-------+
18 | Field  | Type                | Null | Key | Default | Extra |
19 +--------+---------------------+------+-----+---------+-------+
20 | Name   | varchar(50)         | NO   | PRI | NULL    |       |
21 | Age    | tinyint(3) unsigned | NO   | PRI | NULL    |       |
22 | Gender | enum('boy','girl')  | NO   |     | boy     |       |
23 | ID     | tinyint(3) unsigned | NO   |     | NULL    |       |
24 +--------+---------------------+------+-----+---------+-------+
25 4 rows in set (0.00 sec)
26 
27 mysql>
插入指定的位置用法展示
 1 mysql> desc t1;
 2 +--------+---------------------+------+-----+---------+-------+
 3 | Field  | Type                | Null | Key | Default | Extra |
 4 +--------+---------------------+------+-----+---------+-------+
 5 | Name   | varchar(50)         | NO   | PRI | NULL    |       |
 6 | Age    | tinyint(3) unsigned | NO   | PRI | NULL    |       |
 7 | Gender | enum('boy','girl')  | NO   |     | boy     |       |
 8 | ID     | tinyint(3) unsigned | NO   |     | NULL    |       |
 9 +--------+---------------------+------+-----+---------+-------+
10 4 rows in set (0.00 sec)
11 
12 mysql> 
13 mysql> alter table t1 drop Gender;
14 Query OK, 1 row affected (0.02 sec)
15 Records: 1  Duplicates: 0  Warnings: 0
16 
17 mysql> desc t1;
18 +-------+---------------------+------+-----+---------+-------+
19 | Field | Type                | Null | Key | Default | Extra |
20 +-------+---------------------+------+-----+---------+-------+
21 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
22 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
23 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |
24 +-------+---------------------+------+-----+---------+-------+
25 3 rows in set (0.00 sec)
26 
27 mysql>
删除字段案例展示
 1 mysql> desc t1;
 2 +-------+---------------------+------+-----+---------+-------+
 3 | Field | Type                | Null | Key | Default | Extra |
 4 +-------+---------------------+------+-----+---------+-------+
 5 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
 6 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
 7 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |
 8 +-------+---------------------+------+-----+---------+-------+
 9 3 rows in set (0.00 sec)
10 
11 mysql> alter table t1 modify ID tinyint unsigned not null first;
12 Query OK, 1 row affected (0.02 sec)
13 Records: 1  Duplicates: 0  Warnings: 0
14 
15 mysql> desc t1;
16 +-------+---------------------+------+-----+---------+-------+
17 | Field | Type                | Null | Key | Default | Extra |
18 +-------+---------------------+------+-----+---------+-------+
19 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |
20 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
21 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
22 +-------+---------------------+------+-----+---------+-------+
23 3 rows in set (0.00 sec)
24 
25 mysql>
修改字段类型及属性等（改变字段的位置）
 1 mysql> desc t1;
 2 +-------+---------------------+------+-----+---------+-------+
 3 | Field | Type                | Null | Key | Default | Extra |
 4 +-------+---------------------+------+-----+---------+-------+
 5 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |
 6 | Name  | varchar(50)         | NO   | PRI | NULL    |       |
 7 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
 8 +-------+---------------------+------+-----+---------+-------+
 9 3 rows in set (0.00 sec)
10 
11 mysql> alter table t1 change Name StudentName char(50) not null;
12 Query OK, 1 row affected (0.03 sec)
13 Records: 1  Duplicates: 0  Warnings: 0
14 
15 mysql> desc t1;
16 +-------------+---------------------+------+-----+---------+-------+
17 | Field       | Type                | Null | Key | Default | Extra |
18 +-------------+---------------------+------+-----+---------+-------+
19 | ID          | tinyint(3) unsigned | NO   |     | NULL    |       |
20 | StudentName | char(50)            | NO   | PRI | NULL    |       |
21 | Age         | tinyint(3) unsigned | NO   | PRI | NULL    |       |
22 +-------------+---------------------+------+-----+---------+-------+
23 3 rows in set (0.00 sec)
24 
25 mysql>
修改字段名称
 1 mysql> show indexes from t1;                #查看当前索引信息
 2 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
 3 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
 4 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
 5 | t1    |          0 | PRIMARY  |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         |
 6 | t1    |          0 | PRIMARY  |            2 | Age         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
 7 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
 8 2 rows in set (0.00 sec)
 9 
10 mysql> alter table t1 add index(StudentName);        #创建一个索引
11 Query OK, 1 row affected (0.02 sec)
12 Records: 1  Duplicates: 0  Warnings: 0
13 
14 mysql> show indexes from t1;
15 +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
16 | Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
17 +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
18 | t1    |          0 | PRIMARY     |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         |
19 | t1    |          0 | PRIMARY     |            2 | Age         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
20 | t1    |          1 | StudentName |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         |
21 +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
22 3 rows in set (0.00 sec)
23 
24 mysql> alter table t1 drop INDEX  StudentName;        #删除索引信息
25 Query OK, 1 row affected (0.04 sec)
26 Records: 1  Duplicates: 0  Warnings: 0
27 
28 mysql> show indexes from t1;
29 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
30 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
31 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
32 | t1    |          0 | PRIMARY  |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         |
33 | t1    |          0 | PRIMARY  |            2 | Age         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
34 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
35 2 rows in set (0.00 sec)
36 
37 mysql>
创建索引和删除索引用法展示
 1 mysql> select database();
 2 +-------------+
 3 | database()  |
 4 +-------------+
 5 | yinzhengjie |
 6 +-------------+
 7 1 row in set (0.01 sec)
 8 
 9 mysql> show tables;
10 +-----------------------+
11 | Tables_in_yinzhengjie |
12 +-----------------------+
13 | t1                    |
14 +-----------------------+
15 1 row in set (0.00 sec)
16 
17 mysql> alter table t1 rename to mysql_test_table;        #可以用alter命令进行修改标明
18 Query OK, 0 rows affected (0.00 sec)
19 
20 mysql> show tables;
21 +-----------------------+
22 | Tables_in_yinzhengjie |
23 +-----------------------+
24 | mysql_test_table      |
25 +-----------------------+
26 1 row in set (0.00 sec)
27 
28 mysql> 
29 mysql> show tables;
30 +-----------------------+
31 | Tables_in_yinzhengjie |
32 +-----------------------+
33 | mysql_test_table      |
34 +-----------------------+
35 1 row in set (0.00 sec)
36 
37 mysql> rename table mysql_test_table to t1;            #当然我们也可以直接用rename命令进行修改哟~
38 Query OK, 0 rows affected (0.01 sec)
39 
40 mysql> show tables;
41 +-----------------------+
42 | Tables_in_yinzhengjie |
43 +-----------------------+
44 | t1                    |
45 +-----------------------+
46 1 row in set (0.00 sec)
47 
48 mysql>
修改表名的两种常见的姿势
 1 mysql> show table status like 't1'\G
 2 *************************** 1. row ***************************
 3            Name: t1
 4          Engine: InnoDB
 5         Version: 10
 6      Row_format: Compact
 7            Rows: 1
 8  Avg_row_length: 16384
 9     Data_length: 16384
10 Max_data_length: 0
11    Index_length: 0
12       Data_free: 4194304
13  Auto_increment: NULL
14     Create_time: 2017-11-17 05:07:56
15     Update_time: NULL
16      Check_time: NULL
17       Collation: latin1_swedish_ci
18        Checksum: NULL
19  Create_options: 
20         Comment: 
21 1 row in set (0.00 sec)
22 
23 mysql> alter table t1 engine=MyISAM;
24 Query OK, 1 row affected (0.02 sec)
25 Records: 1  Duplicates: 0  Warnings: 0
26 
27 mysql> show table status like 't1'\G
28 *************************** 1. row ***************************
29            Name: t1
30          Engine: MyISAM
31         Version: 10
32      Row_format: Fixed
33            Rows: 1
34  Avg_row_length: 53
35     Data_length: 53
36 Max_data_length: 14918173765664767
37    Index_length: 2048
38       Data_free: 0
39  Auto_increment: NULL
40     Create_time: 2017-11-17 05:11:57
41     Update_time: 2017-11-17 05:11:57
42      Check_time: NULL
43       Collation: latin1_swedish_ci
44        Checksum: NULL
45  Create_options: 
46         Comment: 
47 1 row in set (0.00 sec)
48 
49 mysql>
修改表选项案例展示

 

posted @ 2017-11-17 21:24  尹正杰  阅读(1437)  评论(0)  编辑  收藏  举报