sql语句语法

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: 0

mysql> 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: 0

mysql> 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)


//modify
mysql> alter table shop modify  column insert_string int(4) unsigned zerofill;    //改变表的字段数据类型
Query OK, 7 rows affected (1.14 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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替换为50
Query OK, 1 row affected (0.41 sec)
Rows matched: 5  Changed: 1  Warnings: 0

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 |   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 t1
where (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 s1

select 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 in
select * 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;


//清空table
truncate 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')