SQL语句实践

1. DDL语句之管理表

1、建立表

执行student建表语句：

mysql> use oldboy;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> create table student( id int(4) not null, name char(20) not null, age tinyint(2)  NOT NULL default '0', dept varchar(16) default NULL );
Query OK, 0 rows affected (0.15 sec)
mysql> show tables;    ---查看所有表
+------------------+
| Tables_in_oldboy |
+------------------+
| student          |
+------------------+
1 row in set (0.01 sec)
mysql> show create table student\G    ---查看建表
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (    ---CREATE TABLE是创建表的固定关键字，student为表名
  `id` int(4) NOT NULL,    ---学号列，数字类型，长度为4，不为空值
  `name` char(20) NOT NULL,    ---名字列，定长字符类型，长度为20，不为空值
  `age` tinyint(2) NOT NULL DEFAULT '0',    ---年龄列，很小的数字类型，长度为2，不为空，默认为0值
  `dept` varchar(16) DEFAULT NULL    ---系别列，变长字符类型，长度为16，默认为空
) ENGINE=InnoDB DEFAULT CHARSET=utf8    ---引擎和字符集，引擎默认为InnoDB，字符集，继承库的utf8
1 row in set (0.01 sec)

mysql> desc student;    ---查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

• 字段类型表得对应列的类型说明：
  TINYINT：微小整数类型，可存储的容量为1字节
  INT：整数类型，可存储得容量为4字节
  CHAR(M)：定长字符串类型，当存储时，总是用空格填满右边到指定的长度。最大可存储1<=M字节<=255
  VARCHAR(M)：变长字符串类型，最大可存储1<=M字节<=255

CHAR和VARCHAR之间的差别

VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串（L）的长度，加上一个记录字符串长度的字节。对于字符串'abcd'，L是4，存储需要5字节。
CHAR和VARCHAR的差别小结：
char类型是定长，不够的在右边用空格补全，这会浪费存储空间，以此列为查询条件时，速度更快，多数系统表的字段都是定长。
varchar类型是变长，节省存储空间，以此列为查询条件时速度较慢。

2、查看表结构

方法1：先通过use进入到指定库，然后再查看。

mysql> use oldboy;
Database changed
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

方法2：无须进入指定库，通过如下命令直接查看。

mysql> show columns from oldboy.student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show full columns from student from oldboy;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(4)      | NULL            | NO   |     | NULL    |       | select,insert,update,references |         |
| name  | char(20)    | utf8_general_ci | NO   |     | NULL    |       | select,insert,update,references |         |
| age   | tinyint(2)  | NULL            | NO   |     | 0       |       | select,insert,update,references |         |
| dept  | varchar(16) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (0.00 sec)

3、更改表名

方法1：采用rename命令更改表名

mysql> rename table student to test;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| test             |
+------------------+
1 row in set (0.00 sec)

方法2：采用alter法修改表名

mysql> alter table test rename to student;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

4、增、删、改表的字段

mysql> create table test ( id int(4) not null auto_increment, name char(20) not null, primary key(id) );    ---先创建一个test表
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

若要在表test中添加字段sex、age和qq，类型分别为char(4)、int(4)、varchar(15)，可以通过如下命令来完成
先添加性别列，长度为4，内容非空：

mysql> alter table test add sex char(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |    ---新增了sex列
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

指定添加年龄列到name后面的位置：

mysql> alter table test add age int(4) after name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| age   | int(4)   | YES  |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

在第一列添加qq字段：

mysql> alter table test add qq varchar(15) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| qq    | varchar(15) | YES  |     | NULL    |                |
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | int(4)      | YES  |     | NULL    |                |
| sex   | char(4)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

若要删除字段，可采用如下命令：

mysql> alter table test drop qq;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table test drop age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| sex   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

同时添加两个字段：

mysql> alter table test add age tinyint(2) first, add qq varchar(15);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

以下是生产环境下的命令使用案例
增加1个字段的命令：

ALTER TABLE `etiantian` ADD `FIRSTPHOTO_URL` varchar(255) default NULL COMMENT ' 第一张图片 URL'

增加2个字段得命令：

ALTER TABLE `basic` ADD `adhtml_top` varchar(1024) default NULL COMMENT ' 顶部广告 html ' ,
                    ADD `adhtml_right` varchar(1024) default NULL COMMENT ' 右侧广告 html ' ;

改变字段的命令：

alter table ett_ambiguity change ambiguity_state ambiguity_state tinyint comment ' 状态，默认 1=正常，0=失败'；
ALTER TABLE `ett_photo`
MODIFY_COLUMN `PHOTO_DESCRIPTION` varchar(512) CHARACTER SET utf8 COLLATE utf8_generral_ci NOT NULL COMMENT ' 描述 ' AFTER `PHOTO_TITLE` ;

修改字段类型的命令：

mysql> alter table test modify age char(4) after name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段名称的命令：

mysql> alter table test change age oldboyage char(4) after name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

企业里更改数据的流程：开发人员写出SQL语句，发给运维人员或DBA检验并执行。对数据表的修改，应尽量选在代码上线的时候或者业务低谷的时候执行，不要在流量高峰期处理大表的更改。下班的时候尽量不要独自在生产线上更改东西。

5、创建和删除索引

数据库的索引就像书的目录一样，如果在字段上建立了索引，那么以索引列为查询条件时可以加快查询数据的速度，这是MySQL优化的重要内容之一。
常见的为表内字段建立索引的方法有如下两种。
方法1：建表后利用alter命令增加普通索引
在此之前，要删除建表时创建的index_name索引：

mysql> alter table student drop index index_name;    ---前提时有索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

然后，就可以在student表的name列上添加索引了，索引名为index_name：

mysql> alter table student add index index_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

方法2：使用create为test表的qq列创建普通索引：

mysql> create index index_qq on test(qq);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc test;    ---查看结果
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(4)      | NO   | PRI | NULL    | auto_increment |
| name      | char(20)    | NO   |     | NULL    |                |
| oldboyage | char(4)     | YES  |     | NULL    |                |
| sex       | char(4)     | YES  |     | NULL    |                |
| qq        | varchar(15) | YES  | MUL | NULL    |                |    ---MUL这里原来为空
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

其中PRI为主键索引得标识，MUL为普通索引的标识
删除建表时创建的index_name索引的命令为：

mysql> alter table student drop index index_name;

生产场景下的经验：当数据量以及访问量很大的时候，不适合临时建立索引，因为会影响用户访问。有运维曾经在生产上为有着四五百万条记录的表建立索引，花了90~180秒。所以应尽量选择在业务流量低谷时建立索引，以避免重蹈覆辙。

6、查看建表语句

mysql> show create table test\G    ---\G表示垂直显示结果
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `oldboyage` char(4) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  `qq` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_qq` (`qq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

7、删除表

删除表名为student的表

mysql> show tables from oldboy;
+------------------+
| Tables_in_oldboy |
+------------------+
| student          |
| test             |
+------------------+
2 rows in set (0.00 sec)
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables from oldboy;
+------------------+
| Tables_in_oldboy |
+------------------+
| test             |
+------------------+
1 row in set (0.00 sec)