MySQL常见语句汇总

2,379 阅读9分钟

数据库操作

连接数据库

C:\Users\21952>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

创建数据库

CREATE DATABASE 数据库名;

删除数据库

DROP DATABASE 数据库名;

选择数据库

use 数据库名;

查看所有数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| blog               |
| information_schema |
| mydb               |
| mysql              |
| notes              |
| notesblog          |
| performance_schema |
| sys                |
| test               |
+--------------------+
9 rows in set (0.00 sec)

数据表操作

创建数据表

CREATE TABLE table_name(
    column_name column_type,
    ...
);

以下例子中将创建user表

CREATE TABLE IF NOT EXISTS `user`(
    `uid` INT UNSIGNED AUTO_INCREMENT,
    `uname` VARCHAR(20) NOT NULL,
    `age` INT,
    `sex` VARCHAR(2),
    PRIMARY KEY(`uid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

解析:

  • IF NOT EXISTS表示只有当user表不存在时才创建
  • UNSIGNED存储的时无符号数
  • NOT NULL表示该字段不能为空,否则会报错
  • AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
  • PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
  • ENGINE 设置存储引擎,CHARSET 设置编码。

删除数据表

DROP TABLE [IF EXISTS] table_name;

修改表结构

修改表名

修改表名有两种方式:

ALTER TABLE old_table_name RENAME new_table_name;
RENAME TABLE old_table_name TO new_table_name;

示例如下:

mysql> ALTER TABLE user RENAME user1;
Query OK, 0 rows affected (0.28 sec)
mysql> RENAME TABLE user1 TO user;
Query OK, 0 rows affected (0.51 sec)

修改表中某一列的列名和列结构

ALTER TABLE table_name CHANGE old_column_name new_column_name new_ype;

将user表的uname更改为name,且将VARCHAR(20)修改为VARCHAR(21)

ALTER TABLE user CHANGE uname name VARCHAR(21) NOT NULL;

修改列结构

ALTER TABLE table_name MODIFY column_name new_type;

实例:

mysql> ALTER TABLE user MODIFY uname VARCHAR(20);
Query OK, 3 rows affected (1.28 sec)
Records: 3  Duplicates: 0  Warnings: 0

增加列

mysql> ALTER TABLE user ADD address VARCHAR(20);
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除列

mysql> ALTER TABLE user DROP address;
Query OK, 0 rows affected (0.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表结构

DESC table_name;
mysql> DESC user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| uid   | int unsigned | NO   | PRI | NULL    | auto_increment |
| uname | varchar(21)  | NO   |     | NULL    |                |
| age   | int          | YES  |     | NULL    |                |
| sex   | varchar(2)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

查看当前数据库的所有表

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| stu            |
| user           |
+----------------+
2 rows in set (0.00 sec)

数据操作

插入数据

INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN);

我们可以在VALUES前面指定部分要插入的字段

当我们要插入全部字段可以省略(field1, field2,...fieldN)

插入全部字段:(要指定主键)

mysql> INSERT INTO user VALUES (1, 'xgc', 21, '男');
Query OK, 1 row affected (0.08 sec)

插入部分字段(当主键设置了AUTO_INCREMENT,主键可以省略,主键会自动加1)

mysql> INSERT INTO user (uname, age) VALUES ('zhangsan', 22);
Query OK, 1 row affected (0.08 sec)

查询数据

SELECT column_name [AS result_name],column_name FROM table_name [WHRER Clause] [LIMIT N] [OFFSET M]

[]内的为可选项

  • AS result_name表示为查询结果对应的列名取个别名

  • WHERE子句后面跟着选择条件

  • LIMIT来设置返回的记录数

  • OFFSET指定查询数据的偏移量,默认为0

当我们要查询全部字段的时候,可以用*来代替列名

mysql> SELECT * FROM user;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   1 | xgc      |   21 | 男   |
|   6 | zhangsan |   22 | NULL |
+-----+----------+------+------+
2 rows in set (0.00 sec)

查询部分字段

mysql> SELECT uname AS name,age FROM user;
+----------+------+
| name     | age  |
+----------+------+
| xgc      |   21 |
| zhangsan |   22 |
+----------+------+
2 rows in set (0.00 sec)

WHERE子句

mysql> SELECT * FROM user WHERE uid=6;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   6 | zhangsan |   22 | NULL |
+-----+----------+------+------+
1 row in set (0.00 sec)

LIMIT子句

LIMIT后面可以加一个或两个参数

只有一个参数,则表示要查询的记录数

mysql> SELECT * FROM  user LIMIT 1;
+-----+-------+------+------+
| uid | uname | age  | sex  |
+-----+-------+------+------+
|   1 | xgc   |   21 | 男   |
+-----+-------+------+------+
1 row in set (0.00 sec)

有两个参数,第一个表示要跳过的数量,第二个表示要查询的记录数

mysql> SELECT * FROM  user LIMIT 1,2;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   6 | zhangsan |   22 | NULL |
+-----+----------+------+------+
1 row in set (0.00 sec)
OFFSET子句

OFFSET子句不能单独使用,只有在LIMIT子句存在的时候才能使用

为了方便测试,再增加一条数据

mysql> INSERT INTO user (uname, age, sex) VALUES ('xiaoli', 22, '男');
Query OK, 1 row affected (0.35 sec)

此时全部数据如下:

mysql> SELECT * FROM user;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   1 | xgc      |   21 | 男   |
|   6 | zhangsan |   22 | NULL |
|   7 | xiaoli   |   22 | 男   |
+-----+----------+------+------+
3 rows in set (0.00 sec)

测试OFFSET子句

mysql> SELECT * FROM user WHERE age=22 LIMIT 1 OFFSET 1;
+-----+--------+------+------+
| uid | uname  | age  | sex  |
+-----+--------+------+------+
|   7 | xiaoli |   22 | 男   |
+-----+--------+------+------+
1 row in set (0.00 sec)

UNION操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

SELECT column_name, column_name FROM table_name [WHERE Clause]
UNION [ALL | DISTINCT]
SELECT column_name, column_name FROM table_name [WHERE Clause];

参数:

  • DISTINCT: 可选,删除结果集中重复的数据。默认。
  • ALL: 可选,返回所有结果集,包含重复数据。

注:

  • 查询结果的列数要相同,不同会报错。

  • UNION操作符不会检查列名是否相同,直接将第二个SELECT语句的组合到第一个SELECT语句的结果集中。即结果集的列名为第一个SELECT语句的列名。

实例:

SELECT * FROM user WHERE uname = 'xgc'
UNION
SELECT * FROM user WHERE age = 22;

ORDER BY 排序

我们可以使用ORDER BY子句来对数据集排序,再返回结果

SELECT column_name, column_name FROM table_name ORDER BY column_name [ASC | DESC], [column_name [ASC | DESC]]
  • ORDER BY后面的列名表示要根据这个列名来排序
  • ASC表示升序排序,DESC表示降序排序。默认按升序排序
  • 我们可以使用多个列名排序,先按照靠前的列名排序,当列名相同时就按照下一个列名来排序。

示例:

mysql> SELECT * FROM user ORDER BY age DESC;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   6 | zhangsan |   22 | NULL |
|   7 | xiaoli   |   22 | 男   |
|   1 | xgc      |   21 | 男   |
+-----+----------+------+------+
3 rows in set (0.00 sec)

GROUP BY 分组

GROUP BY 语句根据一个或多个列对结果集进行分组。将列值相同的分为一组。

在分组的列上我们可以使用 COUNT, SUM, AVG等函数。

SELECT column_name function(column_name) FROM table_name [WITH ROLLUP]
GROUP BY column_name;
  • WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计

示例:将统计user表中年龄为22的分为一组,并统计对应人数

mysql> select age, count(age) AS num from user group by age;
+------+------------+
| age  | num        |
+------+------------+
|   21 |          1 |
|   22 |          2 |
+------+------------+
2 rows in set (0.00 sec)

这里,我们可以使用WITH ROLLUP再统计查询出的总人数

mysql> select age, count(age) AS num from user group by age WITH ROLLUP;
+------+-----+
| age  | num |
+------+-----+
|   21 |   1 |
|   22 |   2 |
| NULL |   3 |
+------+-----+
3 rows in set (0.00 sec)

我们看到使用WITH ROLLUP统计出的数据对应age列的值为NULL。我们可以使用coalesce来设置取代NULL的值。

我们来看看语法

coalesce(a,b,c)

说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null。

我们将其设置为总数

mysql> select coalesce(age, '总数') AS age, count(age) AS num from user group by age WITH ROLLUP;
+--------+-----+
| age    | num |
+--------+-----+
| 21     |   1 |
| 22     |   2 |
| 总数   |   3 |
+--------+-----+
3 rows in set, 1 warning (0.00 sec)

修改数据

UPDATE table_name SET column_name = new_value, column_name = new_value [WHERE Clause]

删除数据

DELETE FROM table_name [WHERE Clause]

WHERE子句

SELECT column_name, column_name FROM table_name [WHERE condition1 [AND|OR] condition2 ..]
  • WHERE后面跟的是查询条件

  • 可以使用AND和OR连接多个查询条件

  • WHERE 子句可以运用于 SQL 的 SELECT、DELETE 或者 UPDATE 命令。

  • MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

    SELECT * FROM user WHERE BINARY name=`xgc`;
    

操作符如下表:

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

LIKE子句

我们可以在WHERE子句中使用LIKE子句

LIKE子句可以代替等号=,区别在于LIKE子句支持模糊匹配。

MySQL提供了两种匹配方式

  • %:匹配任意0个或多个字符。
  • _:匹配单个任意字符
mysql> SELECT * FROM user WHERE uname LIKE 'zh%';
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   6 | zhangsan |   22 | NULL |
+-----+----------+------+------+
1 row in set (0.00 sec)

REGEXP

我们知道MySQL可以通过LIKE进行模糊匹配。

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

SELECT * FROM user WHERE uname REGEXP '^xiao'

上面就表示查询列uname的值以xiao开头的数据。

模式 描述
^ 匹配输入字符串的开始位置。
$ 匹配输入字符串的结束位置。
. 匹配除 \n 之外的任何单个字符。要匹配包括 \n 在内的任何字符,使用 [.\n]
[...] 字符集合。匹配所包含的任意一个字符。例如, [abc] 可以匹配 plain 中的 a
[^...] 负值字符集合。匹配未包含的任意字符。例如, [^abc]可以匹配 plain 中的p
p1|p2|p3 匹配 p1 或 p2 或 p3。例如,z|flood能匹配 zfood(z|f)ood则匹配 zoodfood
* 匹配前面的子表达式零次或多次。例如,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 次。

NULL值处理

当我们需要查询列值为空或不为空是,=!=是不起作用的。

我们应该使用的应该是IS NULLIS NOT NULL

SELECT * FROM user WHERE sex IS NULL;
SELECT * FROM user WHERE sex IS NOT NULL;

JOIN 连接

在真正的应用中经常需要从多个数据表中读取数据。

我们可以使用MySQL 的 JOIN 在两个或多个表中查询数据。

我们可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来进行联合多表查询。

JOIN 按照功能大致分为如下三类:

  • INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
  • **LEFT JOIN(左连接):**获取左表所有记录,即使右表没有对应匹配的记录。
  • RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

在学习JOIN前,我们先了解什么叫做笛卡尔积:

在我们进行多表联合查询的时候会出现的一种情况——笛卡尔积现象

现在,我们有两个集合A和B

A = {0,1} B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式:

A×B = {(0,2),(1,2),(0,3),(1,3),(0,4),(1,4)};

B×A = {(2,0),(2,1),(3,0),(3,1),(4,0),(4,1)}

以上A×B和B×A的结果就可以叫做两个集合相乘的笛卡尔积

从上面我们得出两个结论:

  • 两个集合相乘,不满足交换率,既 A×B ≠ B×A
  • A集合和B集合相乘,包含了集合A中元素和集合B中元素相结合的所有的可能性。即两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数

数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积

在使用前,我们先看看联合多表查询的两个表

mysql> SELECT * FROM stu;
+-----+----------+------+------+---------+
| sid | sname    | age  | sex  | address |
+-----+----------+------+------+---------+
|   1 | zhangsan |   21 | 男   | NULL    |
|   2 | lisi     |   21 | NULL | NULL    |
+-----+----------+------+------+---------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM user;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   1 | xgc      |   21 | 男   |
|   6 | zhangsan |   22 | NULL |
|   7 | xiaoli   |   22 | 男   |
+-----+----------+------+------+
3 rows in set (0.00 sec)

INNER JOIN:

我们把stu表和user表中age相等的记录查询出来(当然,查询出来的数据是没应用意义的,但只是为了查看一下效果)

mysql> SELECT * FROM user u INNER JOIN stu s ON u.age = s.age;
+-----+-------+------+------+-----+----------+------+------+---------+
| uid | uname | age  | sex  | sid | sname    | age  | sex  | address |
+-----+-------+------+------+-----+----------+------+------+---------+
|   1 | xgc   |   21 | 男   |   1 | zhangsan |   21 | 男   | NULL    |
|   1 | xgc   |   21 | 男   |   2 | lisi     |   21 | NULL | NULL    |
+-----+-------+------+------+-----+----------+------+------+---------+
2 rows in set (0.00 sec)

我来先解释一下上面SQL语句的意义

  • INNER JOIN,就是内连接
  • 表名user、stu后面的u、s分别是它们的别名,从u.age = s.age可以看到,它有区分两个表相同列名的作用
  • ON的作用类似于WHERE,表示的是查询后符合条件的记录

我们看到内连接的匹配就是:

  • 先用笛卡尔积来进行组合成 A表记录数 x B表记录数 个记录
  • 然后使用ON后面条件筛选出符合的记录

LEFT JOIN:

mysql> SELECT * FROM user u LEFT JOIN stu s ON u.age = s.age;
+-----+----------+------+------+------+----------+------+------+---------+
| uid | uname    | age  | sex  | sid  | sname    | age  | sex  | address |
+-----+----------+------+------+------+----------+------+------+---------+
|   1 | xgc      |   21 | 男   |    1 | zhangsan |   21 | 男   | NULL    |
|   1 | xgc      |   21 | 男   |    2 | lisi     |   21 | NULL | NULL    |
|   6 | zhangsan |   22 | NULL | NULL | NULL     | NULL | NULL | NULL    |
|   7 | xiaoli   |   22 | 男   | NULL | NULL     | NULL | NULL | NULL    |
+-----+----------+------+------+------+----------+------+------+---------+
4 rows in set (0.00 sec)

RIGHT JOIN:

mysql> SELECT * FROM user u RIGHT JOIN stu s ON u.age = s.age;
+------+-------+------+------+-----+----------+------+------+---------+
| uid  | uname | age  | sex  | sid | sname    | age  | sex  | address |
+------+-------+------+------+-----+----------+------+------+---------+
|    1 | xgc   |   21 | 男   |   1 | zhangsan |   21 | 男   | NULL    |
|    1 | xgc   |   21 | 男   |   2 | lisi     |   21 | NULL | NULL    |
+------+-------+------+------+-----+----------+------+------+---------+
2 rows in set (0.00 sec)

MySQL事务

事务指的是一组SQL语句,要么全部执行,要么全部执行。

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务用来管理 insert,update,delete 语句

在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。

因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

修改MySQL自动提交模式

MySQL中事务默认是自动提交的,我们可以手动设置关闭或开启自动提交事务的模式

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

开启事务

我们可以手动开启一个事务,从而使对数据库的操作在提交后才永久生效

  • BEGIN 或 START TRANSACTION 显式地开启一个事务;

事务回滚

当我们在执行事务的时候,发现其中一条语句出现错误,并且希望撤销正在进行的所有未提交的修改。那么我们可以使用事务回滚。

  • ROLLBACK 或 ROLLBACK WORK 进行事务的回滚

事务提交

当事务执行之后,而且没有一条语句出现错误。我们打算提交所有的修改,从而让对数据库的修改持久化。我们就要进行事务的提交

  • COMMIT 或 COMMIT WORK 会提交事务,并使已对数据库进行的所有修改成为永久性的;

事务处理示例(开启、回滚、提交)

mysql> SELECT * FROM user;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   1 | xgc      |   21 | 男   |
|   6 | zhangsan |   22 | NULL |
|   7 | xiaoli   |   22 | 男   |
+-----+----------+------+------+
3 rows in set (0.00 sec)

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

mysql> INSERT INTO user (uname, age) VALUES('lisi', 18);
Query OK, 1 row affected (0.00 sec)

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

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

mysql> INSERT INTO user (uname, age) VALUES('xiaohong', 18);
Query OK, 1 row affected (0.00 sec)

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

# 查看回滚后的数据
mysql> SELECT * FROM user;
+-----+----------+------+------+
| uid | uname    | age  | sex  |
+-----+----------+------+------+
|   1 | xgc      |   21 | 男   |
|   6 | zhangsan |   22 | NULL |
|   7 | xiaoli   |   22 | 男   |
|   8 | lisi     |   18 | NULL |
+-----+----------+------+------+
4 rows in set (0.00 sec)

保存点

SAVEPOINT是在数据库事务处理中实现子事务(subtransaction),也称为嵌套事务的方法。事务可以回滚到SAVEPOINT 而不影响到SAVEPOINT创建前的变化,不需要放弃整个事务。

允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT

  • 声明一个保存点

    SAVEPOINT savapoint_name;
    
  • 回滚到保存点

    ROLLBACK TO savepoint_name;
    
  • 删除保存点,一般在事务处理完成后(执行一条 ROLLBACK 或 COMMIT)自动释放

    MySQL5以来,可以用:

    RELEASE SAVEPOINT savepoint_name; #删除指定保存点
    

设置事务隔离级别

事务的隔离级别是用来解决并发时的问题,如:脏读、幻读、不可重复读

InnoDB 存储引擎提供事务的隔离级别有

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

设置事务隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 
READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE

查看事务隔离级别

查看全局和当前会话事务隔离级别:

mysql> select @@global.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的,索引能够大大提高MySQL的检索速度。

创建索引,你需要确保该索引是应用在SQL查询条件的条件(一般是作为WHERE子句的条件)

实际上,索引也是一张表。该表保存了主键与索引字段,并指向实体表的记录。

虽然索引能够加快查询速度,但我们不应该滥用索引。因此更新(UPDATE、DELETE、INSERT)表的时候,MySQL不仅会保存数据,还要保存一下索引文件,这会降低更新表的速度。

索引分类

在MySQL中,索引分为普通索引,唯一索引,主键索引和全文索引。其中每种索引根据包含列的不同又可以分为单列索引和组合索引。

因此,我们又可以说索引分为单列索引和组合索引。

  • 单列索引,即一个索引只包含单个列。一个表中可以有多个单列索引。

  • 组合索引,即一个索引包含多个列。

    组合索引指的是在多个字段上创建的索引。只有在查询条件中使用了创建索引的第一个字段,索引才用被使用。使用组合索引时遵循最左前缀匹配原则。

    最左前缀匹配原则:

    mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

    比如a = 1 and b = 2 and c > 3 and d = 4,如果建立(a,b,c,d)顺序的索引,d是用不到索引的,

    如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

这里,我们来看看普通索引,唯一索引,主键索引和全文索引的不同

  • 普通索引:这是最基本的索引,它没有任何限制。

  • 唯一索引:它与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。

    如果是组合索引,则列值的组合必须唯一。

  • 主键索引:一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表时同时创建主键索引。

  • 全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较。

    FULLTEXT索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。

    FULLTEXT索引配合match against操作使用,而不是一般的where语句加like。

创建索引

下面这三种方法是创建普通索引、唯一索引和全文索引的方法:

建表后,直接创建

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name(column_name[, column_name]);

修改表结构(添加索引)

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT] INDEX index_name(column_name[, column_name]);

创建表时同时指定

CREATA TABLE table_name(
	column_name column_type,
    column_name column_type,
    [INDEX | FULLTEXT | UNIQUE] [index_name] (column_name[, column_name])
);

创建主键索引的两种方式如下:

修改表结构(主键索引)

ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column_name[, column_name]);

建表时同时创建

CREATE TABLE table_name(
	column_name column_type,
    column_name column_type,
    PRIMARY KEY(column_name[, column_name])
);

删除索引

上面所有的索引都可以使用下面语句删除

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

如果是主键,我们还可以不用指定索引名

ALTER TABLE table_name DROP PRIMARY KEY;

显示索引信息

SHOW INDEX FROM table_name;

示例:

mysql> SHOW INDEX FROM user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user  |          0 | PRIMARY  |            1 | uid         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

临时表

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

临时表在MySQL 3.23版本中添加,如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。

创建临时表

CREATE TEMPORARY TABLE table_name(
	column_name column_type,
    column_name column_type
);

使用SHOW TABLES命令显示数据表列表时,我们是无法看到临时表。

删除临时表

默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。

当然我们也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。

DROP TABLE table_name;

复制表

有时候,我们需要完成的复制MySQL的数据表,包括表结构、索引等。如果仅仅使用CREATE TABLE new_table_name SELECT命令是无法实现的。

可能我们也会需要复制MySQL表的数据到另一个表。

复制表结构

  1. 使用 SHOW CREATE TABLE 命令获取创建数据表语句,该语句包含了原数据表的结构,索引等。

    SHOW CREATE TABLE table_name;
    
  2. 复制该数据库表语句修改表名并执行

示例:

mysql> SHOW CREATE TABLE user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `uid` int unsigned NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

复制表数据

INSERT INTO table_name SELECT column_name FROM table_name;

元数据

命令 描述
SELECT VERSION() 服务器版本信息
SELECT DATABASE() 当前数据库名 (或者返回空)
SELECT USER() 当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES 服务器配置变量

MySQL 序列

一张数据表只能有一个字段是自增主键。如果我们想实现其他字段也自动增加,可以使用MySQL序列。

使用AUTO_INCREMENT

MySQL 中最简单使用序列的方法就是使用 AUTO_INCREMENT 来定义列。

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

获取AUTO_INCREMENT值

在MySQL的客户端中我们可以使用 SQL中的1686434 函数来获取最后的插入表中的自增列的值。

SELECT LAST_INSERT_ID();

重置序列

如果我们删除了数据表中的多条记录,并希望对剩下数据的AUTO_INCREMENT列进行重新排列。那么我们可以通过删除自增的列,然后重新添加来实现。

不过该操作要非常小心,如果在删除的同时又有新记录添加,有可能会出现数据混乱。

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

设置序列的开始值

一般情况下序列的开始值为1,但如果你需要指定一个开始值100,那我们可以通过以下语句来实现:

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者你也可以在表创建成功后,通过以下语句来实现:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

处理重复数据

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

防止表中出现重复数据

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

让我们尝试一个实例:下表中无索引及主键,所以该表允许出现多条重复记录。

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

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

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

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

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

INSERT IGNORE INTOINSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO,执行后不会出错,也不会向数据表中插入重复数据:

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

如果我们设置了唯一索引,那么在使用INSERT INTO插入重复数据时,SQL 语句将无法执行成功,并抛出错。假设我们使用INSERT IGNORE INTO,如果插入重复数据,将不返回错误,只以警告形式返回。

统计重复数据

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

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

过滤重复数据

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

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

你也可以使用 GROUP BY 来读取数据表中不重复的数据:

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

删除重复数据

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

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然你也可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录。方法如下:

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

导出数据

MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。

使用 SELECT ... INTO OUTFILE 语句导出数据

以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

导出表

mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本,其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录,该目标必须是可写的。

以下实例将数据表 runoob_tbl 导出到 /tmp 目录中:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl
password ******

导出SQL格式数据

导出 SQL 格式的数据到指定文件,如下所示:

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

以上命令创建的文件内容如下:

-- MySQL dump 8.23
--
-- Host: localhost    Database: RUNOOB
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `runoob_tbl`
--

CREATE TABLE runoob_tbl (
  runoob_id int(11) NOT NULL auto_increment,
  runoob_title varchar(100) NOT NULL default '',
  runoob_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (runoob_id),
  UNIQUE KEY AUTHOR_INDEX (runoob_author)
) TYPE=MyISAM;

--
-- Dumping data for table `runoob_tbl`
--

INSERT INTO runoob_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

如果你需要导出整个数据库的数据,可以使用以下命令:

$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******

如果需要备份所有数据库,可以使用以下命令:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

--all-databases 选项在 MySQL 3.23.12 及以后版本加入。

该方法可用于实现数据库的备份策略。

将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

源主机上执行以下命令,将数据备份至 dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

如果完整备份数据库,则无需使用特定的表名称。

如果你需要将备份的数据库导入到MySQL服务器中,可以使用以下命令,使用以下命令你需要确认数据库已经创建:

$ mysql -u root -p database_name < dump.txt
password *****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

导入数据

mysql 命令导入

mysql -u user_name -p < 要导入的数据库数据(runoob.sql)

source命令导入

source sql文件

LOAD DATA 导入数据

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE table_name;

如果指定LOCAL关键词,则表明从客户主机上按路径读取文件。如果没有指定,则文件在服务器上按路径读取文件。

使用mysqlimport导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令:

$ mysqlimport -u root -p --local mytbl dump.txt
password *****