2019-03-27 常用管理SQL语句应用实践（3）
2019-03-27 本文已影响0人
阿丧小威
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)
