跳至主要內容

基础教程

Mr.Liu2019年5月27日大约 62 分钟数据库MySQLSQL

基础教程

学习数据库安装后，最重要的就是学习SQL语句。

数据语句操作类型

SQL是操作数据库的核心，

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL是最重要的关系数据库操作语言，并且它的影响已经超出数据库领域，得到其他领域的重视和采用，如人工智能领域的数据检索等。

SQL是关系模型的数据库应用语言，由IBM在20世纪70年代为其关系型数据库 System R 所开发。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。1989年4月，ISO提出了具有完整性特征的SQL89标准，1992年11月又公布了SQL92标准。

虽然各个数据库系统略有不同，但是他们基本均遵循SQL 92标准。或者在SQL 92上做了一些简单的扩展和变化。

学好了MySQL 的SQL 语法，其他的SQL语法学习起来均是万变不离其中。

SQL语句按照其功能范围不同可分为3个类别：

  1. 数据定义语言(DDL ，Data Defintion Language)语句：数据定义语句，用于定义不同的数据段、数据库、表、列、索引等。常用的语句关键字包括create、drop、alter等。
  2. 数据操作语言(DML ， Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据的完整性。常用的语句关键字主要包括insert、delete、update和select等。
  3. 数据控制语言(DCL， Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

管理数据库命令

元数据

以下命令语句可以在 MySQL 的命令提示符使用，获取服务器元数据

命令描述
select version( )服务器版本信息
select database( )当前数据库名 (或者返回空)
select user( )当前用户名
show status服务器状态
show variables服务器配置变量

创建数据库

  • create database 数据库名:
    创建数据库
mysql> create database data_test;
Query OK, 1 row affected (0.01 sec)

删除数据库

  • drop database 数据库名:
    删除数据库
mysql> drop database data_test;
Query OK, 0 rows affected (0.01 sec)

展示所有数据库

  • show databases:
    列出 MySQL 数据库管理系统的数据库列表。
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| data_test          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

改变数据库

  • use 数据库名 :
    选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
mysql> use data_test;
Database changed

展示当前数据库中所有的表

  • show tables:
    显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。
mysql> show tables;
+---------------------+
| Tables_in_data_test |
+---------------------+
| customers           |
| orderitems          |
| orders              |
| productnotes        |
| products            |
| vendors             |
+---------------------+
6 rows in set (0.00 sec)

展示特定表中每列的信息

  • show columns from 数据表(desc 数据表):
    显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
mysql> show columns from customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

展示数据表的详细索引信息

  • show index from 数据表:
    显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
mysql> show index from customers;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customers |          0 | PRIMARY  |            1 | cust_id     | A         |           5 |  NULL    |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

展示数据库的性能及统计信息

  • show table status [from db_name] [like 'pattern'] \G:
    该命令将输出Mysql数据库管理系统的性能及统计信息。

显示数据库 data_test 中所有表的信息

mysql> show table status from data_test;
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| customers    | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |            0 |         0 |          10006 | 2019-05-26 10:15:02 | 2019-05-26 10:15:25 | NULL       | utf8_general_ci |     NULL |                |         |
| orderitems   | InnoDB |      10 | Dynamic    |   11 |           1489 |       16384 |               0 |        16384 |         0 |           NULL | 2019-05-26 10:15:02 | 2019-05-26 10:15:26 | NULL       | utf8_general_ci |     NULL |                |         |
| orders       | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |        16384 |         0 |          20010 | 2019-05-26 10:15:02 | 2019-05-26 10:15:25 | NULL       | utf8_general_ci |     NULL |                |         |
| productnotes | MyISAM |      10 | Dynamic    |   14 |            135 |        1892 | 281474976710655 |         6144 |         0 |            115 | 2019-05-26 10:15:02 | 2019-05-26 10:15:26 | NULL       | utf8_general_ci |     NULL |                |         |
| products     | InnoDB |      10 | Dynamic    |   14 |           1170 |       16384 |               0 |        16384 |         0 |           NULL | 2019-05-26 10:15:02 | 2019-05-26 10:15:25 | NULL       | utf8_general_ci |     NULL |                |         |
| vendors      | InnoDB |      10 | Dynamic    |    6 |           2730 |       16384 |               0 |            0 |         0 |           1007 | 2019-05-26 10:15:02 | 2019-05-26 10:15:25 | NULL       | utf8_general_ci |     NULL |                |         |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
6 rows in set (0.02 sec)

表名以cus开头的表的信息

mysql> show table status from data_test like "cus%";
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| customers | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |            0 |         0 |          10006 | 2019-05-26 10:15:02 | 2019-05-26 10:15:25 | NULL       | utf8_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

表名以cus开头的表的信息;
加上 \G，查询结果按列打印;\G后面不能再加分号;
因为\G在功能上等同于;
如果加了分号，那么就是;;(2个分号)，SQL语法错误ERROR:No query specified

mysql> show table status from data_test like "cus%"\G
*************************** 1. row ***************************
           Name: customers
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 10006
    Create_time: 2019-05-26 10:15:02
    Update_time: 2019-05-26 10:15:25
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

数据类型

数值类型

mysql支持所有标准sql数值数据类型。

这些类型包括严格数值数据类型（integer、smallint、decimal和numeric），以及近似数值数据类型（float、real和double precisi键字int是integer的同义词，关键字dec是decimal的同义词。

bit数据类型保存位字段值，并且支持myisam、memory、innodb和bdb表。

作为sql标准的扩展，mysql也支持整数类型tinyint、mediumint和bigint。下面的表显示了需要的每个整数类型的存储和范围。

类型大小范围（有符号）范围（无符号）用途
tinyint1 字节(-128，127)(0，255)小整数值
smallint2 字节(-32 768，32 767)(0，65 535)大整数值
mediumint3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值
int 或 integer4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
bigint8 字节(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
float4 字节(-3.402 823 466 e+38，-1.175 494 351 e-38)，0，(1.175 494 351 e-38，3.402 823 466 351 e+38)0，(1.175 494 351 e-38，3.402 823 466 e+38)单精度
浮点数值
double8 字节(-1.797 693 134 862 315 7 e+308，-2.225 073 858 507 201 4 e-308)，0，(2.225 073 858 507 201 4 e-308，1.797 693 134 862 315 7 e+308)0，(2.225 073 858 507 201 4 e-308，1.797 693 134 862 315 7 e+308)双精度
浮点数值
decimaldecimal(m,d)依赖于 m 和 d 的值依赖于 m 和 d 的值小数值

日期和时间类型

表示时间值的日期和时间类型为 datetime、date、timestamp、time 和 year。

每个时间类型有一个有效值范围和一个 "零" 值，当指定不合法的 mysql 不能表示的值时使用 "零" 值。

timestamp 类型有专有的自动更新特性，将在后面描述。

类型大小(字节)范围格式用途
date31000-01-01/9999-12-31yyyy-mm-dd日期值
time3'-838:59:59'/'838:59:59'hh:mm:ss时间值或持续时间
year11901/2155yyyy年份值
datetime81000-01-01 00:00:00/9999-12-31 23:59:59yyyy-mm-dd hh:mm:ss混合日期和时间值
timestamp41970-01-01 00:00:00/2038
结束时间是第 2147483647 秒
北京时间 2038-1-19 11:14:07
格林尼治时间2038-1-19 03:14:07		yyyymmdd hhmmss混合日期和时间值，时间戳

字符串类型

字符串类型指 char、varchar、binary、varbinary、blob、text、enum 和 set。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型大小用途
char0-255 字节定长字符串
varchar0-65535 字节变长字符串
tinyblob0-255 字节不超过 255 个字符的二进制字符串
tinytext0-255 字节短文本字符串
blob0-65 535 字节二进制形式的长文本数据
text0-65 535 字节长文本数据
mediumblob0-16 777 215 字节二进制形式的中等长度文本数据
mediumtext0-16 777 215 字节中等长度文本数据
longblob0-4 294 967 295 字节二进制形式的极大文本数据
longtext0-4 294 967 295 字节极大文本数据

char 和 varchar 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

binary 和 varbinary 类似于 char 和 varchar，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

blob 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 blob 类型：tinyblob、blob、mediumblob 和 longblob。它们区别在于可容纳存储范围不同。

有 4 种 text 类型：tinytext、text、mediumtext 和 longtext。对应的这 4 种 blob 类型，可存储的最大长度不同，可根据实际情况选择。

注意：

1、一个汉字占多少长度与编码有关：

UTF－8：一个汉字＝3个字节

GBK：一个汉字＝2个字节

2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别

3、MySQL 检查长度，可用 SQL 语言来查看：

select length(fieldname) from table_name

创建数据表

创建MySQL数据表需要以下信息：

  • 表名
  • 表字段名
  • 定义每个表字段

语法
以下为创建MySQL数据表的SQL通用语法：

create table table_name (column_name column_type);

实例
以下例子中我们将在 my_data 数据库中创建数据表students：

mysql> create table if not exists students(id int unsigned auto_increment, name varchar(40) not null, adress varchar(100) , birthday date not null, primary key(id)) engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.03 sec)

mysql> show columns from students;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(40)      | NO   |     | NULL    |                |
| adress   | varchar(100)     | YES  |     | NULL    |                |
| birthday | date             | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

实例解析：

  • 如果你不想字段为 null 可以设置字段的属性为 not null， 在操作数据库时如果输入该字段的数据为null ，就会报错。
  • auto_increment定义列为自增的属性，一般用于主键，数值会自动加1。
  • primary key关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
  • engine 设置存储引擎，charset 设置编码。

删除数据表

MySQL中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

语法
以下为删除MySQL数据表的通用语法：

drop table table_name;

实例
以下实例中我们将删除 students 表:

mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

插入数据

MySQL 表中使用 insert into SQL语句来插入数据。

语法
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

insert into table_name ( field1, field2,...fieldN ) values ( value1, value2,...valueN );

insert 插入多条数据

insert into table_name  (field1, field2,...fieldn)  values  (valuea1,valuea2,...valuean),(valueb1,valueb2,...valuebn),(valuec1,valuec2,...valuecn)......;

如果数据是字符型，必须使用单引号或者双引号，如："value"。

实例
以下实例中我们将向 students 表插入数据:

# 主键默认从1开始
mysql> insert into students (name,birthday) values("丽丽","1996-05-06");
Query OK, 1 row affected (0.00 sec)

# 主键设为0，即从最后一个id值自动增长
mysql> insert into students values(0,"李明",null,"1993-12-16");
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(10,"张磊",'北京市海淀区',"1995-04-12");
Query OK, 1 row affected (0.00 sec)

# 主键不设置，从最后一个id值自动增长
mysql> insert into students set name="孙雨",adress='河北省石家庄市',birthday="1989-07-18";
Query OK, 1 row affected (0.01 sec)

如果添加过主键自增（PRINARY KEY AUTO_INCREMENT）第一列在增加数据的时候，可以写为0或者null，这样添加数据可以自增

条件语句

  • 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用where语句来设定查询条件。
  • where 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
  • 以下为操作符列表，可用于 where 子句中。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以使用 and 或者 or 指定一个或多个条件。
  • WHERE 子句也可以运用于 SQL 的 delete 或者 update 命令。

下表中实例假定 A 为 10, B 为 20

操作符描述实例
=等号，检测两个值是否相等，如果相等返回true(A = B) 返回false。
!=不等于，检测两个值是否相等，如果不相等返回true(A != B) 返回 true。
>大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true(A > B) 返回false。
<小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true(A < B) 返回 true。
>=大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true(A >= B) 返回false。
<=小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true(A <= B) 返回 true。

如果我们想在 MySQL 数据表中读取指定的数据，where 子句是非常有用的。

使用主键来作为 where 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

查询数据

MySQL 数据库使用SQL select语句来查询数据。

语法
以下为在MySQL数据库中查询数据通用的 select 语法

select column_name,column_name
from table_name
[where Clause]
[limit N][ offset M]
  • 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用where语句来设定查询条件。
  • select 命令可以读取一条或者多条记录。
  • 你可以使用星号（*）来代替其他字段，select语句会返回表的所有字段数据
  • 你可以使用 limit 属性来设定返回的记录数。
  • 你可以通过offset指定select语句开始查询的数据偏移量。默认情况下偏移量为0。

实例
以下实例我们将通过 SQL select 命令来获取 MySQL 数据表 students 的数据：

mysql> select * from students;
+----+------+----------------+------------+
| id | name | adress         | birthday   |
+----+------+----------------+------------+
|  1 | 丽丽 | NULL           | 1996-05-06 |
|  2 | 李明 | NULL           | 1993-12-16 |
| 10 | 张磊 | 北京市海淀区    | 1995-04-12 |
| 11 | 孙雨 | 河北省石家庄市  | 1989-07-18 |
+----+------+----------------+------------+
4 rows in set (0.00 sec)

mysql> select id,name from students limit 2 offset 1;
+----+------+
| id | name |
+----+------+
|  2 | 李明 |
| 10 | 张磊 |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from students where id>=10;
+----+------+----------------+------------+
| id | name | adress         | birthday   |
+----+------+----------------+------------+
| 10 | 张磊 | 北京市海淀区   | 1995-04-12 |
| 11 | 孙雨 | 河北省石家庄市 | 1989-07-18 |
+----+------+----------------+------------+
2 rows in set (0.00 sec)

mysql> select * from students where id>=10 limit 1 offset 1;
+----+------+----------------+------------+
| id | name | adress         | birthday   |
+----+------+----------------+------------+
| 11 | 孙雨 | 河北省石家庄市 | 1989-07-18 |
+----+------+----------------+------------+
1 row in set (0.00 sec)

mysql> select * from students where name="李明";
+----+------+--------+------------+
| id | name | adress | birthday   |
+----+------+--------+------------+
|  2 | 李明 | NULL   | 1993-12-16 |
+----+------+--------+------------+
1 row in set (0.00 sec)

修改数据

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL update 命令来操作。

语法
以下是 update 命令修改 MySQL 数据表数据的通用 SQL 语法：

update table_name set field1=new-value1, field2=new-value2 [where clause]

当我们需要将字段中的特定字符串批量修改为其他字符串时，可已使用以下操作：

update table_name set field=replace(field, 'old-string', 'new-string') [where clause]
  • 你可以同时更新一个或多个字段。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在一个单独表中同时更新数据。
    当你需要更新数据表中指定行的数据时 where 子句是非常有用的。

实例
以下我们将在 SQL update 命令使用 where 子句来更新 students 表中指定的数据：

mysql> update students set adress="北京市昌平区" where adress is null;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from students;
+----+------+----------------+------------+
| id | name | adress         | birthday   |
+----+------+----------------+------------+
|  1 | 丽丽 | 北京市昌平区   | 1996-05-06 |
|  2 | 李明 | 北京市昌平区   | 1993-12-16 |
| 10 | 张磊 | 北京市海淀区   | 1995-04-12 |
| 11 | 孙雨 | 河北省石家庄市 | 1989-07-18 |
+----+------+----------------+------------+
4 rows in set (0.00 sec)

mysql> update students set adress=replace(adress, "河北省石家庄","湖北省武汉") wh
ere id=11;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
| 10 | 张磊 | 北京市海淀区 | 1995-04-12 |
| 11 | 孙雨 | 湖北省武汉市 | 1989-07-18 |
+----+------+--------------+------------+
4 rows in set (0.00 sec)

mysql> update students set id=id-7 where id=10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
|  3 | 张磊 | 北京市海淀区 | 1995-04-12 |
| 11 | 孙雨 | 湖北省武汉市 | 1989-07-18 |
+----+------+--------------+------------+
4 rows in set (0.00 sec)

删除数据

你可以使用 sql 的 delete from 命令来删除 mysql 数据表中的记录。

语法
以下是 sql delete 语句从 mysql 数据表中删除数据的通用语法：

delete from table_name [where clause]
  • 如果没有指定 where 子句，mysql 表中的所有记录将被删除。
  • 你可以在 where 子句中指定任何条件
  • 您可以在单个表中一次性删除记录。
    当你想删除数据表中指定的记录时 where 子句是非常有用的。

实例
这里我们将在 sql delete 命令中使用 where 子句来删除 mysql 数据表 students 所选的数据:

mysql> delete from students where id=11;
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
|  3 | 张磊 | 北京市海淀区 | 1995-04-12 |
+----+------+--------------+------------+
3 rows in set (0.00 sec)

delete，drop，truncate 都有删除表的作用，区别在于：

1、drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;

实例，删除学生表：

drop table students;

2、truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;

实例，删除学生表：

truncate table students;

3、delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;

实例，删除学生表：

delete from students;

4、delete from table_name where xxx : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;

实例，删除学生表中姓名为 "张三" 的数据：

delete from student where name = "张三";

5、delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;

实例，删除学生表中姓名为 "张三" 的数据：

delete from student where name = "张三";

实例，释放学生表的表空间：

optimize table students;

6、delete from 表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

包含条件

我们知道在 mysql 中使用 sql select 命令来读取数据， 同时我们可以在 select 语句中使用 where 子句来获取指定的记录。

where 子句中可以使用等号 = 来设定获取数据的条件，如 "adress = '北京市昌平区'"。

但是有时候我们需要获取 adress 字段含有 "北京市" 字符的所有记录，这时我们就需要在 where 子句中使用 sql like 子句。

语法
以下是 sql select 语句使用 like 子句从数据表中读取数据的通用语法：

select field1, field2,...fieldn from table_name where field1 like condition1 [and [or]] filed2 = 'somevalue'
  • sql like 子句中使用百分号 %字符来表示任意字符，类似于unix或正则表达式中的星号 *。
  • 如果没有使用百分号 %, like 子句与等号 = 的效果是一样的。
  • like 通常与 % 一同使用，类似于一个元字符的搜索。
  • 可以使用 and 或者 or 指定一个或多个条件。
  • 可以在 delete、select或 update 命令中使用 where...like 子句来指定条件。

实例
以下我们将在 sql select 命令中使用 where...like 子句来从mysql数据表 students 中读取数据。

mysql> select * from students where adress like "北京市%";
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
|  3 | 张磊 | 北京市海淀区 | 1995-04-12 |
+----+------+--------------+------------+
3 rows in set (0.00 sec)

在 where like 的条件查询中，SQL 提供了四种匹配方式。

  • %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
  • _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
  • []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
  • [^]：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
  • 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

like 匹配/模糊匹配，会与 % 和 _ 结合使用。

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

正则表达式

在前面的章节我们已经了解到mysql可以通过 like ...% 来进行模糊匹配。

mysql 同样也支持其他正则表达式的匹配， mysql中使用 regexp 操作符来进行正则表达式匹配。

下表中的正则模式可应用于 regexp 操作符中。

模式描述
^匹配输入字符串的开始位置。如果设置了 regexp 对象的 multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$匹配输入字符串的结束位置。如果设置了regexp 对象的 multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
[...]字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
`p1p2`
*匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

实例
了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的sql语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：

  • 查找name字段中以'st'为开头的所有数据：
mysql> select name from person_tbl where name regexp '^st';
  • 查找name字段中以'ok'为结尾的所有数据：
mysql> select name from person_tbl where name regexp 'ok$';
  • 查找name字段中包含'mar'字符串的所有数据：
mysql> select name from person_tbl where name regexp 'mar';
  • 查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
mysql> select name from person_tbl where name regexp '^[aeiou]|ok$';

NULL 值处理

为了处理这种情况，mysql提供了三大运算符:

  • is null: 当列的值是 null,此运算符返回 true。
  • is not null: 当列的值不为 null, 运算符返回 true。
  • <=>: 比较操作符（不同于=运算符），当比较的的两个值为 null 时返回 true。

关于 null 的条件比较运算是比较特殊的。你不能使用 = null 或 != null 在列中查找 null 值 。

在 mysql 中，null 值与任何其它值的比较（即使是 null）永远返回 false，即 null = null 返回false 。

mysql 中处理 null 使用 is null 和 is not null 运算符。

实例
在数据库my_data中创建student表，并插入相应数据

mysql> create table student(ID int unsigned auto_increment primary key,name varchar(10) not null,age int unsigned) default charset=utf8;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into student values(0, "李华",25);
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(0, "敏柔",null);
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(0, "赵强",null);
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(0, "罗晴",23);
Query OK, 1 row affected (0.03 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
+----+--------+------+
4 rows in set (0.00 sec)

以下实例中你可以看到 = 和 != 运算符是不起作用的：

mysql> select id,name,age=null from student;
+----+--------+----------+
| id | name   | age=null |
+----+--------+----------+
|  1 | 李华   |     NULL |
|  2 | 敏柔   |     NULL |
|  3 | 赵强   |     NULL |
|  4 | 罗晴   |     NULL |
+----+--------+----------+
4 rows in set (0.00 sec)

mysql> select id,name,age!=null from student;
+----+--------+-----------+
| id | name   | age!=null |
+----+--------+-----------+
|  1 | 李华   |      NULL |
|  2 | 敏柔   |      NULL |
|  3 | 赵强   |      NULL |
|  4 | 罗晴   |      NULL |
+----+--------+-----------+
4 rows in set (0.00 sec)

查找数据表中 age 列是否为 null，必须使用 <=> 、is null 和 is not null，如下实例：

mysql> select id,name,age is null from student;
+----+--------+-------------+
| id | name   | age is null |
+----+--------+-------------+
|  1 | 李华   |           0 |
|  2 | 敏柔   |           1 |
|  3 | 赵强   |           1 |
|  4 | 罗晴   |           0 |
+----+--------+-------------+
4 rows in set (0.00 sec)

mysql> select id,name,age<=>null from student;
+----+--------+------------+
| id | name   | age<=>null |
+----+--------+------------+
|  1 | 李华   |          0 |
|  2 | 敏柔   |          1 |
|  3 | 赵强   |          1 |
|  4 | 罗晴   |          0 |
+----+--------+------------+
4 rows in set (0.00 sec)

排序

我们知道从 mysql 表中使用 sql select 语句来读取数据。

如果我们需要对读取的数据进行排序，我们就可以使用 mysql 的 order by 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

语法
以下是 sql select 语句使用 order by 子句将查询数据排序后再返回数据：

select field1, field2,...fieldn table_name1, table_name2... order by field1 [asc [desc][默认 asc]], [field2...] [asc [desc][默认 asc]]
  • 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 asc 或 desc 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
  • 你可以添加 where...like 子句来设置条件。

实例
尝试以下实例，结果将按升序及降序排列。

mysql> select * from students where adress like "北京市%" order by birthday;
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
|  3 | 张磊 | 北京市海淀区 | 1995-04-12 |
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
+----+------+--------------+------------+
3 rows in set (0.00 sec)

mysql> select * from students where adress like "北京市%" order by birthday desc;
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
|  3 | 张磊 | 北京市海淀区 | 1995-04-12 |
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
+----+------+--------------+------------+
3 rows in set (0.00 sec)

mysql> select * from students where adress like "北京市%" order by birthday asc;
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
|  3 | 张磊 | 北京市海淀区 | 1995-04-12 |
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
+----+------+--------------+------------+
3 rows in set (0.00 sec)

分组

group by 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 count, sum, avg等函数。

语法
以下是gruop by语句的语法示例：

select column_name, function(column_name) from table_name where column_name operator value group by column_name;

实例
接下来我们使用 group by 语句 将数据表按名字进行分组，并统计每个商品有多少条记录：

mysql> select * from goods;
+----+--------+------+
| id | name   | nums |
+----+--------+------+
|  1 | 西瓜   |   50 |
|  2 | 甜瓜   |   15 |
|  3 | 甜瓜   |   15 |
|  4 | 苹果   |   25 |
|  5 | 西瓜   |   25 |
|  6 | 西瓜   |   63 |
+----+--------+------+
6 rows in set (0.00 sec)

mysql> select name, count(name) from goods group by name;
+--------+-------------+
| name   | count(name) |
+--------+-------------+
| 甜瓜   |           2 |
| 苹果   |           1 |
| 西瓜   |           3 |
+--------+-------------+
3 rows in set (0.00 sec)

with rollup 可以实现在分组统计数据基础上再进行相同的统计（sum,avg,count…）。

例如我们将以上的数据表按商品名称进行分组，再统计每类商品的总数量，或者求其均值：

mysql> select name, sum(nums) as count_num from goods group by name;
+--------+-----------+
| name   | count_num |
+--------+-----------+
| 甜瓜   |        30 |
| 苹果   |        25 |
| 西瓜   |       138 |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> select name, avg(nums) as avg_num from goods group by name with rollup;
+--------+---------+
| name   | avg_num |
+--------+---------+
| 甜瓜   | 15.0000 |
| 苹果   | 25.0000 |
| 西瓜   | 46.0000 |
| NULL   | 32.1667 |
+--------+---------+
4 rows in set (0.00 sec)

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> select coalesce(name, '总计') as 名称, sum(nums) as 数量 from goods group by name with rollup;
+--------+--------+
| 名称   | 数量   |
+--------+--------+
| 甜瓜   |     30 |
| 苹果   |     25 |
| 西瓜   |    138 |
| 总计   |    193 |
+--------+--------+
4 rows in set (0.00 sec)

关联查询

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

语法
mysql union 操作符语法格式：

select expression1, expression2, ... expression_n
from tables
[where conditions]
union [all | distinct]
select expression1, expression2, ... expression_n
from tables
[where conditions];

参数

  • expression1, expression2, ... expression_n: 要检索的列。
  • tables: 要检索的数据表。
  • where conditions: 可选， 检索条件。
  • distinct: 可选，删除结果集中重复的数据。默认情况下 union 操作符已经删除了重复数据，所以 distinct 修饰符对结果没啥影响。
  • all: 可选，返回所有结果集，包含重复数据。

实例
下面的 SQL 语句从 "students" 和 "teachers" 表中选取所有不同的adress（只有不同的值）：

mysql> select * from students;
+----+------+--------------+------------+
| id | name | adress       | birthday   |
+----+------+--------------+------------+
|  1 | 丽丽 | 北京市昌平区 | 1996-05-06 |
|  2 | 李明 | 北京市昌平区 | 1993-12-16 |
|  3 | 张磊 | 北京市海淀区 | 1995-04-12 |
+----+------+--------------+------------+
3 rows in set (0.00 sec)

mysql> select * from teachers;
+----+--------+--------------+----------+
| id | name   | adress       | birthday |
+----+--------+--------------+----------+
|  1 | 刘老师 | 北京市海淀区 | NULL     |
|  2 | 孙老师 | 北京市朝阳区 | NULL     |
|  3 | 常老师 | 北京市昌平区 | NULL     |
+----+--------+--------------+----------+
3 rows in set (0.00 sec)

mysql> select adress from students union select adress from teachers ;
+--------------+
| adress       |
+--------------+
| 北京市昌平区 |
| 北京市海淀区 |
| 北京市朝阳区 |
+--------------+
3 rows in set (0.00 sec)

mysql> select name from students where adress like "%昌平区" union select name from teachers where adress like "%昌平区" ;
+--------+
| name   |
+--------+
| 丽丽   |
| 李明   |
| 常老师 |
+--------+
3 rows in set (0.00 sec)

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

连接

SQL join 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

u5NYj0
u5NYj0
  • INNER JOIN 关键字在表中存在至少一个匹配时返回行。
  • LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
  • RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
  • FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
  • CROSS JOIN(交叉连接)又可称为笛卡尔积，将左表中每一行与右表中每一行分别连接形成新记录。实际业务中运用较少，需要大量运算成本，但它是其他连接的基础。

语法

SQL CROSS JOIN语法

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

或：

SELECT column_name(s)
FROM table1
JOIN table2

注释： CROSS JOIN 与 JOIN 是相同的。

SQL INNER JOIN语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

注释： INNER JOIN 与 JOIN 是相同的。

SQL RIGHT JOIN语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

SQL LEFT JOIN语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL JOIN语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

实例

下面是选自 "Websites" 表的数据：

idnameurlalexacountry
1Googlehttps://www.google.cm/1USA
2淘宝https://www.taobao.com/13CN
3微博http://weibo.com/20CN
4Facebookhttps://www.facebook.com/3USA
5stackoverflowhttp://stackoverflow.com/0IND
6京东https://www.jd.com/19CN

下面是 "access_log" 网站访问记录表的数据：

idsite_idcountdate
11452016-05-10
231002016-05-13
31112302016-05-14
42102016-05-14
552052016-05-14
64132016-05-15
732202016-05-15
855452016-05-16
932012016-05-17

SQL INNER JOIN实例

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;
+----+---------------+-------+------------+
| id | name          | count | date       |
+----+---------------+-------+------------+
|  1 | Google        |    45 | 2016-05-10 |
|  3 | 微博        |   100 | 2016-05-13 |
|  1 | Google        |   230 | 2016-05-14 |
|  2 | 淘宝        |    10 | 2016-05-14 |
|  5 | stackoverflow |   205 | 2016-05-14 |
|  4 | Facebook      |    13 | 2016-05-15 |
|  3 | 微博        |   220 | 2016-05-15 |
|  5 | stackoverflow |   545 | 2016-05-16 |
|  3 | 微博        |   201 | 2016-05-17 |
+----+---------------+-------+------------+
9 rows in set (0.00 sec)

SQL RIGHT JOIN实例

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id;
+------+---------------+-------+------------+
| id   | name          | count | date       |
+------+---------------+-------+------------+
|    1 | Google        |    45 | 2016-05-10 |
|    3 | 微博        |   100 | 2016-05-13 |
| NULL | NULL          |   230 | 2016-05-14 |
|    2 | 淘宝        |    10 | 2016-05-14 |
|    5 | stackoverflow |   205 | 2016-05-14 |
|    4 | Facebook      |    13 | 2016-05-15 |
|    3 | 微博        |   220 | 2016-05-15 |
|    5 | stackoverflow |   545 | 2016-05-16 |
|    3 | 微博        |   201 | 2016-05-17 |
+------+---------------+-------+------------+
9 rows in set (0.00 sec)


mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id WHERE Websites.id IS NULL;
+------+------+-------+------------+
| id   | name | count | date       |
+------+------+-------+------------+
| NULL | NULL |   230 | 2016-05-14 |
+------+------+-------+------------+
1 row in set (0.00 sec)

SQL LEFT JOIN实例

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;
+----+---------------+-------+------------+
| id | name          | count | date       |
+----+---------------+-------+------------+
|  1 | Google        |    45 | 2016-05-10 |
|  2 | 淘宝        |    10 | 2016-05-14 |
|  3 | 微博        |   201 | 2016-05-17 |
|  3 | 微博        |   220 | 2016-05-15 |
|  3 | 微博        |   100 | 2016-05-13 |
|  4 | Facebook      |    13 | 2016-05-15 |
|  5 | stackoverflow |   545 | 2016-05-16 |
|  5 | stackoverflow |   205 | 2016-05-14 |
|  6 | 京东        | NULL  | NULL       |
+----+---------------+-------+------------+
9 rows in set (0.00 sec)


mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id WHERE access_log.site_id IS NULL;
+----+--------+-------+------+
| id | name   | count | date |
+----+--------+-------+------+
|  6 | 京东 | NULL  | NULL |
+----+--------+-------+------+
1 row in set (0.00 sec)

SQL FULL JOIN实例

MySQL中不支持 FULL OUTER JOIN，你可以用以下SQL语句实现效果。

mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
UNION
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id;
+------+---------------+-------+------------+
| id   | name          | count | date       |
+------+---------------+-------+------------+
|    1 | Google        |    45 | 2016-05-10 |
|    2 | 淘宝        |    10 | 2016-05-14 |
|    3 | 微博        |   201 | 2016-05-17 |
|    3 | 微博        |   220 | 2016-05-15 |
|    3 | 微博        |   100 | 2016-05-13 |
|    4 | Facebook      |    13 | 2016-05-15 |
|    5 | stackoverflow |   545 | 2016-05-16 |
|    5 | stackoverflow |   205 | 2016-05-14 |
|    6 | 京东        | NULL  | NULL       |
| NULL | NULL          |   230 | 2016-05-14 |
+------+---------------+-------+------------+
10 rows in set (0.00 sec);


mysql> SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
RIGHT JOIN access_log
ON Websites.id=access_log.site_id WHERE Websites.id IS NULL
UNION 
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id WHERE access_log.site_id IS NULL;
+------+--------+-------+------------+
| id   | name   | count | date       |
+------+--------+-------+------------+
| NULL | NULL   |   230 | 2016-05-14 |
|    6 | 京东 | NULL  | NULL       |
+------+--------+-------+------------+
2 rows in set (0.00 sec)

ALTER命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL alter命令。

删除表字段

如下命令使用了 alter 命令及 drop 子句来删除以上创建表的 age 字段：

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
| age   | int(10) unsigned | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table table_name  drop field_name;
Query OK, 0 rows affected (0.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

如果数据表中只剩余一个字段则无法使用drop来删除字段。

增加表字段

mysql 中使用 add 子句来向数据表中添加列，如下实例在表 student 中添加 age 字段，并定义数据类型:

mysql> alter table student add age int unsigned not null;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
| age   | int(10) unsigned | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

执行以上命令后，field_name 字段会自动添加到数据表字段的末尾。

如果你需要指定新增字段的位置，可以使用mysql提供的关键字 first (设定位第一列)， after 字段名（设定位于某个字段之后）。

尝试以下 alter table 语句, 在执行成功后，使用 show columns 查看表结构的变化：

mysql> desc student;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | varchar(10)      | NO   |     | NULL    |       |
| age   | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table student add id int unsigned auto_increment primary key first;
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     | NULL    |                |
| age   | int(10) unsigned | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table student add class int unsigned after id;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| class | int(10) unsigned | YES  |     | NULL    |                |
| name  | varchar(10)      | NO   |     | NULL    |                |
| age   | int(10) unsigned | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

first 和 after 关键字可用于 add 与 modify 子句，所以如果你想重置数据表字段的位置就需要先使用 drop 删除字段然后使用 add 来添加字段并设置位置。

修改表字段

如果需要修改字段类型及名称, 你可以在alter命令中使用 modify 或 change 子句 。

例如，把字段 class 的类型从 int unsigned 改为 varchar(10)，可以执行以下命令:

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| class | int(10) unsigned | YES  |     | NULL    |                |
| name  | varchar(10)      | NO   |     | NULL    |                |
| age   | int(10) unsigned | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table student modify class varchar(10);
Query OK, 8 rows affected (0.69 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| class | varchar(10)      | YES  |     | NULL    |                |
| name  | varchar(10)      | NO   |     | NULL    |                |
| age   | int(10) unsigned | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

使用 change 子句, 语法有很大的不同。 在 change 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

当你修改字段时，你可以指定是否包含值或者是否设置默认值。

如果你不设置默认值，mysql会自动设置该字段默认为 null。

mysql> alter table student change class class_room varchar(20) default "203";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| class_room | varchar(20)      | YES  |     | 203     |                |
| name       | varchar(10)      | NO   |     | NULL    |                |
| age        | int(10) unsigned | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

你可以使用 alter 来修改字段的默认值，尝试以下实例：

mysql> alter table student alter class_room set default "205";
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| class_room | varchar(20)      | YES  |     | 205     |                |
| name       | varchar(10)      | NO   |     | NULL    |                |
| age        | int(10) unsigned | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

你也可以使用 alter 命令及 drop子句来删除字段的默认值，如下实例：

mysql> alter table student alter class_room drop default;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| class_room | varchar(20)      | YES  |     | NULL    |                |
| name       | varchar(10)      | NO   |     | NULL    |                |
| age        | int(10) unsigned | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

修改数据表

修改数据表类型，可以使用 alter table 命令来完成。

尝试以下实例，我们将表 student 的类型修改为 MyISAM ，然后再修改为 InnoDB：

mysql> alter table student engine=myisam;
Query OK, 8 rows affected (0.28 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show table status like "student"\G
*************************** 1. row ***************************
           Name: student
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 20
    Data_length: 160
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: 9
    Create_time: 2019-06-09 17:47:23
    Update_time: 2019-06-09 17:47:23
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> alter table students engine=innodb;
Query OK, 8 rows affected (0.76 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show table status where name="students"\G
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 9
    Create_time: 2019-06-09 17:54:53
    Update_time: 2019-06-09 17:54:53
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

如果需要修改数据表的名称，可以在 alter table 语句中使用 rename 子句来实现。

尝试以下实例将数据表 student 重命名为 students：

mysql> alter table student rename to students;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_my_data |
+-------------------+
| students          |
+-------------------+
1 row in set (0.00 sec)

视图

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦

解决办法：定义视图

  • 通俗的讲，视图就是一条SELECT语句执行后返回的结果集。
  • 视图是对若干张基本表的引用，一张虚表，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
  • 方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

视图的作用

  • 提高了重用性，就像一个函数
  • 对数据库重构，却不影响程序的运行
  • 提高了安全性能，可以对不同的用户
  • 让数据更加清晰

创建视图

create view view_name as select ...；

查看视图

查看某个库下的所有视图

select TABLE_NAME from information_schema.views where TABLE_SCHEMA='db_name'

查看视图详情

show create view `view_name`;
select * from information_schema.VIEWS where TABLE_SCHEMA='db_name' and TABLE_NAME='view_name';

使用视图

视图的用途就是查询

select * from v_05A;

修改视图

ALTER VIEW view_name AS select ...;

删除视图

drop view view_name;

实例

创建视图

create view v_05A as select "05A"  as "班级",s.name as "姓名",s.gender as "性别" from students as s where s.cid in (select c.id from class as c where c.name="05A");

修改视图

alter view v_05A as select * from students where cid=3;

删除视图

drop view v_05A;

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

什么是触发器:触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

使用触发器可以帮助我们实现监控、日志...

触发器由 action_timing和 event_manipulation两个参数决定什么情况下触发：

  • action_timing：AFTER、BEFORE
  • event_manipulation：INSERT、DELETE、UPDATE

因此，触发器总共由六种触发时机。

创建触发器

/* 
修改MySQL默认的语句结束符,只作用于当前窗口;
将默认的结束符号由;改为$$ 
*/
DELIMITER $$

CREATE TRIGGER `database_name`.`new_trigger_name`
action_timing event_manipulation ON `database_name`.`table_name`
FOR EACH ROW
begin
    /**trigger body**/
    action_statement
end$$

DELIMITER ;

查看触发器

查看某个数据库中的所有触发器

select * from information_schema.triggers where TRIGGER_SCHEMA = 'db_name';

查看触发器详情

select * from information_schema.triggers where TRIGGER_SCHEMA='db_name' and TRIGGER_NAME='trigger_name';

查看创建触发器的语句

show create trigger `trigger_name`;

修改触发器

DROP TRIGGER IF EXISTS `database_name`.`trigger_name`;

DELIMITER $$

CREATE TRIGGER `database_name`.`new_trigger_name`
action_timing event_manipulation ON `database_name`.`table_name`
FOR EACH ROW
begin
    /**trigger body**/
    
end$$

DELIMITER ;

删除触发器

DROP TRIGGER `database_name`.`trigger_name`;

案例

创建数据表：cmd、errlog

CREATE TABLE cmd (
  id INT PRIMARY KEY auto_increment,
  USER CHAR (32),
  priv CHAR (10),
  cmd CHAR (64),
  sub_time datetime, #提交时间
  success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
  id INT PRIMARY KEY auto_increment,
  err_cmd CHAR (64),
  err_time datetime
);

创建触发器

delimiter $$
/* 创建一个触发器，在cmd表添加命令之后触发 */
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
		/* 当插入命令失败时，将错误信息记录到errlog中 */
    if NEW.success = 'no' then
        /* new指代的cmd里面的数据对象 */
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('jason','0755','ls -l /etc',NOW(),'yes'),
    ('jason','0755','cat /etc/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

事件

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可 周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

事件作用

一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。

可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

创建事件

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

查看事件

查看库中所有事件

SELECT * FROM information_schema.events WHERE EVENT_SCHEMA='database_name';

查看事件详情

select * from information_schema.events where TRIGGER_SCHEMA='db_name' and EVENT_NAME='event_name';

查看创建存储过程的语句

show create function `event_name`;

修改事件

ALTER
    [DEFINER = user]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]

删除事件

事件过期之后，会自动删除

DROP EVENT [IF EXISTS] `event_name`;

实例

创建事件

delimiter $$

/* 1分钟后在t2 表插入一条数据 */
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE DO
BEGIN
	INSERT INTO t2 values(3, "a3");
END$$
      
/* 从现在开始，到5分20秒之后，每20秒插入1条数据 */     
CREATE EVENT myevent2
ON SCHEDULE EVERY 20 SECOND
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL "5:20" MINUTE_SECOND DO
BEGIN
  DECLARE total INT;
  select count(*) INTO total from t1;
	INSERT INTO t1 values(total);
END$$

delimiter ;

存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

优势

  • 效率高：存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本 上），都要先分析编译才会执行。所以想对而言存储过程效率更高。
  • 降低网络流量：存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。
  • 复用性高：存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。
  • 可维护性高：当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。
  • 安全性高：完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

创建存储过程

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] `procedure_name` ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'  /* 描述信息 */
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC    /* 确定性 */
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }   /* 数据访问权限 */
  | SQL SECURITY { DEFINER | INVOKER }   /* SQL Security */
}

routine_body:
    Valid SQL routine statement

修改存储过程

ALTER PROCEDURE proc_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

调用存储过程

CALL `procedure_name`([proc_parameter[,...]]);

删除存储过程

在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

DROP PROCEDURE [IF EXISTS] `procedure_name`;

查看存储过程

查看数据库中所有的存储过程

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'database_name';

查看存储过程详情

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_SCHEMA = 'database_name' and ROUTINE_NAME='procedure_name';

查看创建存储过程的语句

show create procedure `procedure_name`;

案例

创建存储过程

DELIMITER $$

CREATE PROCEDURE `students` (IN start INT, IN end INT)
BEGIN
	select * from t2 limit start, end;
END$$

CREATE PROCEDURE `studentCount` (IN first_name varchar(10), OUT total INT)
BEGIN
	select count(*) INTO total from t2 where name like CONCAT(first_name, '%');
END$$

DELIMITER ;

执行存储过程

mysql> CALL students(0, 1);
+----+------+
| id | name |
+----+------+
|  1 | a1   |
+----+------+
1 row in set (0.00 sec)

mysql> CALL studentCount('a1', @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total$$;
+--------+
| @total |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

删除存储过程

mysql> drop PROCEDURE studentCount;
Query OK, 0 rows affected (0.00 sec)

mysql> drop PROCEDURE students;
Query OK, 0 rows affected (0.00 sec)

函数

一组预先编译好的SQL语句集合，可以理解成批处理语句

  1. 提高代码的重用性
  2. 简化操作
  3. 减少了编译次数并且减少了和数据库服务器的连接次数， 提高了效率

区别

  • 存储过程为第三方提供可以处理业务逻辑的接口，返回的是结果集；可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
  • 函数为第三方提供可以处理业务逻辑的接口，返回单个值；有且只有一个返回，适合做处理数据后返回一个结果
  • 视图为第三方提供接口，但是不能处理业务逻辑，返回结果集；可以有0个返回，也可以有多个返回，适合用来简化复杂的SQL查询

创建函数

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] `function_name` ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

修改函数

ALTER FUNCTION func_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

调用函数

SELECT `function_name`([func_parameter[,...]]);

查看函数

查看数据库中所有的存储过程

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'FUNCTION' and ROUTINE_SCHEMA = 'database_name';

查看存储过程详情

SELECT * FROM information_schema.routines where ROUTINE_TYPE = 'FUNCTION' and ROUTINE_SCHEMA = 'database_name' and ROUTINE_NAME='function_name';

查看创建存储过程的语句

show create function `function_name`;

删除函数

DROP FUNCTION [IF EXISTS] `function_name`;

案例

创建函数

DELIMITER $$

CREATE FUNCTION studentCount(first_name varchar(10))
RETURNS int
DETERMINISTIC
BEGIN 
  DECLARE total INT;
  select count(*) INTO total from t2 where name like CONCAT(first_name, '%');
  RETURN total;
END$$

DELIMITER ;

执行函数

mysql> SELECT studentCount('a1');
+--------------------+
| studentCount('a1') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

删除函数

mysql> DROP FUNCTION studentCount;
Query OK, 0 rows affected (0.00 sec)

事务

mysql 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 mysql 中只有使用了 innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 sql 语句要么全部执行，要么全部不执行。
  • 修改数据的命令会自动的触发事务，包括insert、update、delete

一般来说，事务是必须满足4个条件（acid）：：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

  • begin 或 start transaction 显式地开启一个事务；

  • commit 也可以使用 commit work，不过二者是等价的。commit 会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • rollback 也可以使用 rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • savepoint identifier，savepoint 允许在事务中创建一个保存点，一个事务中可以有多个 savepoint；

  • release savepoint identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

  • rollback to identifier 把事务回滚到标记点；

  • set transaction 用来设置事务的隔离级别。 innodb 存储引擎提供事务的隔离级别有:

    • read uncommitted(未提交读)
    • read committed(提交读)
    • repeatable read(可重复读)
    • serializable(序列化)

mysql 事务处理方法

在 mysql 命令行的默认设置下，事务都是自动提交的，即执行 sql 语句后就会马上执行 commit 操作。因此要显式地开启一个事务有以下两种办法：

1、使用事务控制语句

  • begin 开始一个事务
  • rollback 事务回滚
  • commit 事务确认

2、直接用 set 来改变 mysql 的自动提交模式:

  • set autocommit=0 禁止自动提交
  • set autocommit=1 开启自动提交

实例

下面具体演示MySQL事务的使用

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> begin; # 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(0,"寒梅",28); # 插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 寒梅   |   28 |
+----+--------+------+
5 rows in set (0.00 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.04 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> insert into student values(0,"寒梅",28); # 插入数据
Query OK, 1 row affected (0.15 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  6 | 寒梅   |   28 |
+----+--------+------+
5 rows in set (0.00 sec)

mysql> insert into student values(5,"李磊",26);
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(0,"张雷",20);
Query OK, 1 row affected (0.04 sec)

mysql> set autocommit=0; # 禁止自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(0,"韩美美",20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-----------+------+
| ID | name      | age  |
+----+-----------+------+
|  1 | 李华      |   25 |
|  2 | 敏柔      | NULL |
|  3 | 赵强      | NULL |
|  4 | 罗晴      |   23 |
|  5 | 李磊      |   26 |
|  6 | 寒梅      |   28 |
|  7 | 张雷      |   20 |
|  8 | 莉莉      |   20 |
|  9 | 韩美美    |   20 |
+----+-----------+------+
9 rows in set (0.00 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.16 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   | NULL |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> update student set age=23 where name="敏柔"; # 修改数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   |   23 |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> savepoint point1; # 创建保存点
Query OK, 0 rows affected (0.00 sec)

mysql> update student set age=22 where name="赵强";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   |   23 |
|  3 | 赵强   |   22 |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> rollback to point1; # 回滚到保存点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+--------+------+
| ID | name   | age  |
+----+--------+------+
|  1 | 李华   |   25 |
|  2 | 敏柔   |   23 |
|  3 | 赵强   | NULL |
|  4 | 罗晴   |   23 |
|  5 | 李磊   |   26 |
|  6 | 寒梅   |   28 |
|  7 | 张雷   |   20 |
|  8 | 莉莉   |   20 |
+----+--------+------+
8 rows in set (0.00 sec)

mysql> commit; # 事务提交
Query OK, 0 rows affected (0.04 sec)

索引

在图书馆中是如何找到一本书的？

一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，

而且插入操作和更新操作很少出现性能问题，

遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

当数据库中数据量很大时，查找数据会变得很慢 优化方案：索引

  • 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
  • 更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

语法

查看索引

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

删除索引

DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

实例

创建测试表testindex

create table test_index(title varchar(10));

使用python程序（ipython也可以）通过pymsql模块 向表中加入十万条数据

from pymysql import connect

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='test_data',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(10000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()

if __name__ == "__main__":
    main()

开启运行时间监测

set profiling=1;

查找第1万条数据ha-99999

select * from test_index where title='ha-99999';

查看执行的时间

show profiles;

为表title_index的title列创建索引

create index title_index on test_index(title);

执行查询语句

select * from test_index where title='ha-99999';

再次查看执行的时间

show profiles;

要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。

  • 对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了
  • 对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
  • 建立索引会占用磁盘空间

用户

创建用户

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

参数说明

user:
    (username@host )

auth_option: {
    IDENTIFIED BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [initial_auth_option]
}

2fa_auth_option: {
    IDENTIFIED BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 3fa_auth_option]
}

3fa_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

initial_auth_option: {
    INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'}
  | INITIAL AUTHENTICATION IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

修改用户信息

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

ALTER USER [IF EXISTS]
    USER() user_func_auth_option

ALTER USER [IF EXISTS]
    user [registration_option]

ALTER USER [IF EXISTS]
    USER() [registration_option]

ALTER USER [IF EXISTS]
    user DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}

参数

auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | DISCARD OLD PASSWORD
  | ADD factor factor_auth_option [ADD factor factor_auth_option]
  | MODIFY factor factor_auth_option [MODIFY factor factor_auth_option]
  | DROP factor [DROP factor]
}

user_func_auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | DISCARD OLD PASSWORD
}

factor_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

registration_option: {
    factor INITIATE REGISTRATION
  | factor FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'auth_string'
  | factor UNREGISTER
}

factor: {2 | 3} FACTOR

tls_option: 参数同上

resource_option: 参数同上

password_option: 参数同上
lock_option: 参数同上

删除用户

DROP USER [IF EXISTS] user [, user] ...

创建角色

本质上角色和用户是相同的，都是mysql.user表中的数据；不同之处在于无法通过角色连接数据库

CREATE ROLE [IF NOT EXISTS] role [, role ] ...

删除角色

DROP ROLE [IF EXISTS] role [, role ] ...

授予权限

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

GRANT PROXY ON user_or_role
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_or_role: {
    user
  | role
}

移除权限

REVOKE [IF EXISTS]
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

REVOKE [IF EXISTS] PROXY ON user_or_role
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]

REVOKE [IF EXISTS] role [, role ] ...
    FROM user_or_role [, user_or_role ] ...
    [IGNORE UNKNOWN USER]

user_or_role: {
    user
  | role
}

查看用户信息

/* 查询用户信息及全局权限 */
select * from mysql.user where Host='host' and User='username';
/* 查询用户的数据库权限 */
select * from mysql.db where Host='host' and User='username';
/* 查询用户的表权限 */
select * from mysql.tables_priv where Host='host' and User='username';
/* 查询用户的字段权限 */
select * from mysql.columns_priv where Host='host' and User='username';
/* 查询用户的角色权限 */
select * from mysql.role_edges where TO_HOST='host' and TO_USER='username';
/* 查询用户的默认角色 */
select * from mysql.default_roles where HOST='host' and DEFAULT_ROLE_USER='username';

实例

创建用户

# 创建一个admin角色
CREATE ROLE 'admin'@'localhost';
# 创建一个worker角色
create role worker;

# 创建一个只能本地登录的test1用户
CREATE USER 'test1'@'localhost' identified by '!QAZ2wsx';

# 创建一个可以从任意远程主机登陆的用户test2
CREATE USER 'test2'@'%' identified by '!QAZ2wsx';

# 修改用户test2的密码
alter user 'test2'@'%' identified BY '@WSX3edc';

管理权限

/* grant 作用在整个 MySQL 服务器上 */
grant select on *.* to 'test2'@localhost; -- test2 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to 'test2'@localhost; -- 如果要授予所的权限则使用ALL, test2 可以管理 MySQL 中的所有数据库

/* grant 作用在单个数据库上 */
grant select on testdb.* to 'test2'@localhost; -- test2 可以查询 testdb 中的表。

/* grant 作用在单个数据表上 */
grant select, insert, update, delete on testdb.orders to 'test2'@localhost; 
-- 在给一个用户授权多张表时，可以多次执行以上语句 

/* grant 作用在表中的列上 */
grant select(id, se, rank) on testdb.apache_log to test2@localhost;

/* grant 作用在存储过程、函数上 */
grant execute on procedure testdb.pr_add to 'test2'@'localhost'
grant execute on function testdb.fn_add to 'test2'@'localhost'

/* grant 将用户权限授权给其他角色 */
grant 'root'@'localhost' to 'admin'@'localhost' WITH ADMIN OPTION; -- WITH ADMIN OPTION:允许admin将权限授权给其他用户
grant 'root'@'localhost' to worker  WITH ADMIN OPTION;

/* grant 将角色权限授权给其他用户 */
GRANT 'admin'@'localhost' TO 'test2'@'%';
GRANT 'worker' to 'test1'@'localhost' WITH ADMIN OPTION;

/* 将 角色设为默认角色，否则权限不生效 */
set default role 'admin'@'localhost' to 'test2'@'%';
set default role 'worker' to 'test1'@'localhost';

/* revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可 */
revoke all on *.* from test2@localhost;

# 刷新配置，立即启用修改
flush privileges;

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

创建临时表

使用temporary关键字创建临时表

mysql> create temporary table class_room(room_id int unsigned primary key, class varchar(20) ) default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into class_room values(205,"九年级一班");
Query OK, 1 row affected (0.00 sec)

mysql> select * from class_room;
+---------+-----------------+
| room_id | class           |
+---------+-----------------+
|     205 | 九年级一班      |
+---------+-----------------+
1 row in set (0.00 sec)

mysql> desc class_room;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| room_id | int(10) unsigned | NO   | PRI | NULL    |       |
| class   | varchar(20)      | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_my_data |
+-------------------+
| students          |
+-------------------+
1 row in set (0.00 sec)

当你使用 show tables命令显示数据表列表时，你将无法看到 class_room表。

如果你退出当前mysql会话，再使用 select命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。

删除临时表

默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。

以下是手动删除临时表的实例：

mysql> drop table class_room;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from class_room;
ERROR 1146 (42S02): Table 'my_data.class_room' doesn't exist

复制表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用create table ... select 命令，是无法实现的。

实例
尝试以下实例来复制表 runoob_tbl 。

步骤一：

获取数据表的完整结构。

mysql> show create table students\G;
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `class_room` varchar(20) DEFAULT '205',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

步骤二：

修改SQL语句的数据表名，并执行SQL语句。

mysql> create table `student` (`id` int(10) unsigned not null auto_increment,`name` varchar(10) not null,`class_room` varchar(20) default '205', primary key (`id`)) engine=innodb auto_increment=9 default charset=utf8;
Query OK, 0 rows affected (0.59 sec)

步骤三：

执行完第二步骤后，你将在数据库中创建新的克隆表 student。 如果你想拷贝数据表的数据你可以使用 insert into... select 语句来实现。

mysql> insert into student(id,name,class_room) select * from students;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+------------+
| id | name   | class_room |
+----+--------+------------+
|  1 | 李华   | 205        |
|  2 | 敏柔   | 205        |
|  3 | 赵强   | 205        |
|  4 | 罗晴   | 205        |
|  5 | 李磊   | 205        |
|  6 | 寒梅   | 205        |
|  7 | 张雷   | 205        |
|  8 | 莉莉   | 205        |
+----+--------+------------+
8 rows in set (0.00 sec)

处理重复数据

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

防止表中出现重复数据

你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

如果你想设置表中字段 id，name 数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：

mysql> create table teachers(id int unsigned auto_increment, name varchar(10), gender varchar(5), primary key(id,name));
Query OK, 0 rows affected (0.26 sec)

mysql> desc teachers;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)      | NO   | PRI | NULL    |                |
| gender | varchar(5)       | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。

insert ignore into 与 insert into 的区别就是 insert ignore 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

mysql> insert into teachers values(1, "李磊", "男");
Query OK, 1 row affected (0.00 sec)

mysql> insert into teachers values(1, "李磊", "男");
ERROR 1062 (23000): Duplicate entry '1-李磊' for key 'PRIMARY'

mysql> insert ignore into teachers values(1, "李磊", "男");
Query OK, 0 rows affected, 1 warning (0.00 sec)

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

mysql> replace into teachers values(1, "李磊", "女");
Query OK, 2 rows affected (0.00 sec)

mysql> select * from teachers;
+----+--------+--------+
| id | name   | gender |
+----+--------+--------+
|  1 | 李磊   | 女     |
+----+--------+--------+
1 row in set (0.00 sec)

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：

mysql> create table person(first_name char(20) not null, last_name char(20) not null, gender char(10),unique (last_name, first_name)) charset=utf8;
Query OK, 0 rows affected (0.25 sec)

mysql> insert ignore into person values("李", "雷", "男");
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into person values("李", "雷", "男");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert ignore into person values("李", "丽", "女");
Query OK, 1 row affected (0.00 sec)

mysql> select * from person;
+------------+-----------+--------+
| first_name | last_name | gender |
+------------+-----------+--------+
| 李         | 丽        | 女     |
| 李         | 雷        | 男     |
+------------+-----------+--------+
2 rows in set (0.00 sec)

统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

  • 创建person_info表，并插入3条重复数据
mysql> create table person_info(id int unsigned primary key auto_increment,first_name char(20) not null, last_name char(20) not null, gender char(10)) charset=utf8;
Query OK, 0 rows affected (0.29 sec)

mysql> insert ignore into person_info values(0,"李", "丽", "女");
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into person_info values(0,"李", "丽", "女");
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into person_info values(0,"李", "丽", "女");
Query OK, 1 row affected (0.00 sec)

mysql> select * from person_info;
+----+------------+-----------+--------+
| id | first_name | last_name | gender |
+----+------------+-----------+--------+
|  1 | 李         | 丽        | 女     |
|  2 | 李         | 丽        | 女     |
|  3 | 李         | 丽        | 女     |
+----+------------+-----------+--------+
3 rows in set (0.00 sec)
  • 查询重复数据
mysql> select count(*) as repetitions,last_name,first_name from person_info group by last_name, first_name having repetitions > 1;
+-------------+-----------+------------+
| repetitions | last_name | first_name |
+-------------+-----------+------------+
|           3 | 丽        | 李         |
+-------------+-----------+------------+
1 row in set (0.00 sec)

以上查询将返回 person_info 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

  • 确定哪一列包含的值可能会重复。
  • 在列选择列表使用count(*)列出的那些列。
  • 在group by子句中列出的列。
  • having子句设置重复数大于1。

过滤重复数据

如果你需要读取不重复的数据可以在 select 语句中使用 distinct 关键字来过滤重复数据。

mysql> select distinct first_name,last_name from person_info;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| 李         | 丽        |
+------------+-----------+
1 row in set (0.00 sec)

你也可以使用 group by 来读取数据表中不重复的数据：

mysql> select first_name, last_name  from person_info group by last_name, first_name;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| 李         | 丽        |
+------------+-----------+
1 row in set (0.01 sec)

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

先创建临时表tab,新表tab中的数据时从person_info表中分组查询出来的

mysql> create table tmp select last_name, first_name, gender from person_info  group by last_name, first_name, gender;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

在表的第一位添加主键

alter table tmp add column `id` bigint(20) primary key not null auto_increment comment 'id' first;

删除原表

mysql> drop table person_info;
Query OK, 0 rows affected (0.01 sec)

重命名为person_info

mysql> alter table tmp rename to person_info;
Query OK, 0 rows affected (0.01 sec)