13.1. 数据定义语句    13.1.1. ALTER DATABASE语法    13.1.2. ALTER TABLE语法    13.1.3. CREATE DATABASE语法    13.1.4. CREATE INDEX语法    13.1.5. CREATE TABLE语法    13.1.6. DROP DATABASE语法    13.1.7. DROP INDEX语法    13.1.8. DROP TABLE语法    13.1.9. RENAME TABLE语法13.2. 数据操作语句    13.2.1. DELETE语法    13.2.2. DO语法    13.2.3. HANDLER语法    13.2.4. INSERT语法    13.2.5. LOAD DATA INFILE语法    13.2.6. REPLACE语法    13.2.7. SELECT语法    13.2.8. Subquery语法    13.2.9. TRUNCATE语法    13.2.10. UPDATE语法13.3. MySQL实用工具语句    13.3.1. DESCRIBE语法（获取有关列的信息）    13.3.2. USE语法13.4. MySQL事务处理和锁定语句    13.4.1. START TRANSACTION, COMMIT和ROLLBACK语法    13.4.2. 不能回滚的语句    13.4.3. 会造成隐式提交的语句    13.4.4. SAVEPOINT和ROLLBACK TO SAVEPOINT语法    13.4.5. LOCK TABLES和UNLOCK TABLES语法    13.4.6. SET TRANSACTION语法    13.4.7. XA事务13.5. 数据库管理语句    13.5.1. 账户管理语句    13.5.2. 表维护语句    13.5.3. SET语法    13.5.4. SHOW语法    13.5.5. 其它管理语句13.6. 复制语句    13.6.1. 用于控制主服务器的SQL语句    13.6.2. 用于控制从服务器的SQL语句13.7. 用于预处理语句的SQL语法==============================================================================================alter database  //用于更改数据库的全局特性alter table    //用于更改原有表的结构               //您可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。您还可以更改表的评注和表的类型mysql> alter table shop add insert_string char(5);  //给表添加列Query OK, 0 rows affected (0.95 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> describe shop;  //查看表结构+---------------+--------------------------+------+-----+---------+-------+| Field         | Type                     | Null | Key | Default | Extra |+---------------+--------------------------+------+-----+---------+-------+| article       | int(4) unsigned zerofill | NO   | PRI | 0000    |       || dealer        | char(20)                 | NO   | PRI |         |       || price         | double(16,2)             | NO   |     | 0.00    |       || column_name   | char(4)                  | YES  |     | NULL    |       || insert_string | char(5)                  | YES  |     | NULL    |       |+---------------+--------------------------+------+-----+---------+-------+5 rows in set (0.00 sec)mysql> alter table shop  drop column column_name;     //删除指定列/字段Query OK, 0 rows affected (1.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> describe shop;+---------------+--------------------------+------+-----+---------+-------+| Field         | Type                     | Null | Key | Default | Extra |+---------------+--------------------------+------+-----+---------+-------+| article       | int(4) unsigned zerofill | NO   | PRI | 0000    |       || dealer        | char(20)                 | NO   | PRI |         |       || price         | double(16,2)             | NO   |     | 0.00    |       || insert_string | char(5)                  | YES  |     | NULL    |       |+---------------+--------------------------+------+-----+---------+-------+4 rows in set (0.00 sec)//modifymysql> alter table shop modify  column insert_string int(4) unsigned zerofill;    //改变表的字段数据类型Query OK, 7 rows affected (1.14 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> describe shop;+---------------+--------------------------+------+-----+---------+-------+| Field         | Type                     | Null | Key | Default | Extra |+---------------+--------------------------+------+-----+---------+-------+| article       | int(4) unsigned zerofill | NO   | PRI | 0000    |       || dealer        | char(20)                 | NO   | PRI |         |       || price         | double(16,2)             | NO   |     | 0.00    |       || insert_string | int(4) unsigned zerofill | YES  |     | NULL    |       |+---------------+--------------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> show tables;    //对表重命名+----------------+| Tables_in_test |+----------------+| animals        || event          || pet            || shop           || t1             |+----------------+5 rows in set (0.00 sec)mysql> alter table t1 rename t2;    //alter table t1 rename t2对表重命名Query OK, 0 rows affected (0.45 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| animals        || event          || pet            || shop           || t2             |+----------------+5 rows in set (0.02 sec)mysql> alter table t2 add index (d) , add index (a);  //在列d和列a中添加索引Query OK, 0 rows affected (0.81 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> describe t2;+-------+--------------------------+------+-----+-------------------+-----------------------------+| Field | Type                     | Null | Key | Default           | Extra                       |+-------+--------------------------+------+-----+-------------------+-----------------------------+| year  | year(4)                  | YES  |     | NULL              |                             || month | int(2) unsigned zerofill | YES  |     | NULL              |                             || day   | int(2) unsigned zerofill | YES  |     | NULL              |                             || d     | timestamp                | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || a     | char(4)                  | YES  | MUL | NULL              |                             |+-------+--------------------------+------+-----+-------------------+-----------------------------+5 rows in set (0.00 sec)//添加一个新的AUTO_INCREMENT整数列，名称为c//给数据加序号mysql> alter table t2 add c int unsigned not null auto_increment,    -> add primary key(c);Query OK, 0 rows affected (1.12 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> describe t2;+-------+--------------------------+------+-----+-------------------+-----------------------------+| Field | Type                     | Null | Key | Default           | Extra                       |+-------+--------------------------+------+-----+-------------------+-----------------------------+| year  | year(4)                  | YES  |     | NULL              |                             || month | int(2) unsigned zerofill | YES  |     | NULL              |                             || day   | int(2) unsigned zerofill | YES  |     | NULL              |                             || d     | timestamp                | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || a     | char(4)                  | YES  | MUL | NULL              |                             || c     | int(10) unsigned         | NO   | PRI | NULL              | auto_increment              |+-------+--------------------------+------+-----+-------------------+-----------------------------+6 rows in set (0.00 sec)//CREATE DATABASE用于创建数据库，并进行命名。//如果要使用CREATE DATABASE，您需要获得数据库CREATE权限//type: TINYINT[(length)] [UNSIGNED] [ZEROFILL]  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]  | INT[(length)] [UNSIGNED] [ZEROFILL]  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]  | DATE  | TIME  | TIMESTAMP  | DATETIME  | CHAR(length) [BINARY | ASCII | UNICODE]  | VARCHAR(length) [BINARY]  | TINYBLOB  | BLOB  | MEDIUMBLOB  | LONGBLOB  | TINYTEXT [BINARY]  | TEXT [BINARY]  | MEDIUMTEXT [BINARY]  | LONGTEXT [BINARY]  | ENUM(value1,value2,value3,...)  | SET(value1,value2,value3,...)// DROP 语法mysql> drop table animals;  //删除表Query OK, 0 rows affected (0.50 sec)DROP DATABASE database_name  //删除数据库truncate table table_nam   //清空表中数据，表不删除//RENAME TABLE语法mysql> rename table t2 to t1;    //对表重命名Query OK, 0 rows affected (0.18 sec)//delete 语句mysql> select * from t1;+------+-------+------+---------------------+------+---+| year | month | day  | d                   | a    | c |+------+-------+------+---------------------+------+---+| 2000 |    01 |   01 | 2016-11-05 12:14:53 | NULL | 1 || 2000 |    02 |   20 | 2016-11-05 12:14:53 | NULL | 2 || 2000 |    01 |   30 | 2016-11-05 12:14:53 | NULL | 3 || 2000 |    02 |   02 | 2016-11-05 12:14:53 | NULL | 4 || 2000 |    02 |   23 | 2016-11-05 12:14:53 | NULL | 5 || 2000 |    02 |   23 | 2016-11-05 12:14:53 | NULL | 6 |+------+-------+------+---------------------+------+---+6 rows in set (0.00 sec)mysql> delete from t1 where c=6;    //从t1表中删除c=6的数据Query OK, 1 row affected (0.42 sec)mysql> select * from t1;+------+-------+------+---------------------+------+---+| year | month | day  | d                   | a    | c |+------+-------+------+---------------------+------+---+| 2000 |    01 |   01 | 2016-11-05 12:14:53 | NULL | 1 || 2000 |    02 |   20 | 2016-11-05 12:14:53 | NULL | 2 || 2000 |    01 |   30 | 2016-11-05 12:14:53 | NULL | 3 || 2000 |    02 |   02 | 2016-11-05 12:14:53 | NULL | 4 || 2000 |    02 |   23 | 2016-11-05 12:14:53 | NULL | 5 |+------+-------+------+---------------------+------+---+5 rows in set (0.00 sec)//replace 语法mysql> update t1 set day=replace(day,23,50);    //23替换为50Query OK, 1 row affected (0.41 sec)Rows matched: 5  Changed: 1  Warnings: 0mysql> select * from t1;+------+-------+------+---------------------+------+---+| year | month | day  | d                   | a    | c |+------+-------+------+---------------------+------+---+| 2000 |    01 |   01 | 2016-11-05 12:14:53 | NULL | 1 || 2000 |    02 |   20 | 2016-11-05 12:14:53 | NULL | 2 || 2000 |    01 |   30 | 2016-11-05 12:14:53 | NULL | 3 || 2000 |    02 |   02 | 2016-11-05 12:14:53 | NULL | 4 || 2000 |    02 |   50 | 2016-11-05 15:54:03 | NULL | 5 |+------+-------+------+---------------------+------+---+5 rows in set (0.00 sec)select  *  from  t1 where column=(select column from t12)  //子查询结构mysql> select *  from t4;+------+| s1   |+------+|    2 ||    5 ||    4 ||    3 ||    4 |+------+5 rows in set (0.00 sec)mysql> select *  from t3;+------+| s1   |+------+|    5 |+------+1 row in set (0.00 sec)mysql> select s1 from t3 where s1=(select max(s1) from t4);   //使用子查询进行比较+------+| s1   |+------+|    5 |+------+1 row in set (0.00 sec)//表t1中的有些行含有的值会在给定的列中出现两次。该例子可以查找出所有这些mysql> select * from t4  as t    //重命名为t    -> where 2=(select count(*) from t3 where t3.s1=t.s1);Empty set (0.00 sec)//行子查询,以下2个句子等价select * from  t1 where  (1,2) =(select s1,s2 from t2);select * from  t1 where  row(1,2)=(select s1,s2 from t2);//表达式（1，2）和ROW（1，2）有时被称为行构造符//看下面2个等价语句select * from  t1 where (s1,s2)=(1,1,);select * from  t1 where s1=1 and  s2=1;//在表t1中查找同时也存在于表t2中的所有的行select s1,s2,s3  from t1where (s1,s2,s3) in (select s1,s2,s3 from t2);//from 子句中的子查询//将t11进行子查询之后，取别名为sb，再从sb中取sb1>1的数据mysql> create table t11 (s1 int,s2 char(5),s3 float);mysql> insert into t11 values(1,'1',1.0);mysql> insert into t11 values(2,'2',2.0);mysql> select sb1,sb2,sb3    -> from (select s1 as sb1,s2 as sb2,s3*2 as sb3 from t11) as sb    -> where sb1>1;+------+------+------+| sb1  | sb2  | sb3  |+------+------+------+|    2 | 2    |    4 |+------+------+------+1 row in set (0.00 sec)//分类后的表的一组和的平均值select avg(sum(s1)) from t11 group by s1select avg(sum_s1)  from (select sum(s1) as sum_s1 from t1 group by s1 ) as t1;select distinct t1.s1 from t1,t2 where t1.s1=t2.s2;select * from t1 where s1 in (select s1 from t1) or s1 in(select s1 from t2)select (select s1+5 from t1) from t2;代替此查询select (select s1 from t1) +5 from t2;//把子查询作为用于早期MySQL版本的联合进行改写select * from t1 where id in (select id from t2);//可以改成：select distinct t1.* from t1,t2 where t1.id=t2.id;//not inselect * from t1 where id not in (select id from t2);select * from t1 where not exists (select id from t2 where t1.id=t2.id);//等价于:select table1.* from table1 left join table2 on table1.id=table2.id  where table2.id is null;//清空tabletruncate table//update//把年龄列设置为比当前值多一update persondata set age=age+1;//对年龄列加倍，然后再进行增加update persondata set age=age*2,age=age+1;//create user 创建用户，可以使用其桌面客户端去创建//drop user 取消一个账号及其权限//rename user 对mysql账号重命名//set password 赋予一个密码set password=password('some password')set password for user=password('some password')