深入浅出MySQL之SQL基础

Author：带翅膀的猫
发布时间：December 22, 2020
3382views
No comments
27450 words
Categories： MySql

SQL简介

SQL是Structure Query Language(结构化查询语言)的缩写，它是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统SystemR上实现。由于它具有功能丰富、使用方便灵活、语言简洁易学等突出的优点，深受计算机工业界和计算机用户的欢迎。1980年10月，经美国国家标准局(ANSI)的数据库委员会X3H2批准，将SQL作为关系数据库语言的美国标准，同年公布了标准SQL，此后不久，国际标准化组织(ISO)也作出了同样的决定。

SQL使用入门

SQL分类

SQL语句主要可以划分为以下3个类别：

DDL(Data Definition Languages)语句：数据定义语言。这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括CREATE、DROP、ALTER等。
DML(DATA Manipulation Languages)语句：数据操纵语言。用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括INSERT、DELETE、UPDATE、SELECT等。
DCL(Data Control Languages)语句：数据控制语言。用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括GRANT、REVOKE等。

DDL语句

DDL是数据库定义语言的缩写，是对数据库内部的对象进行创建、删除、修改等操作的语言。

DDL与DML最大的区别是DML只是对表内部数据的操作，而不涉及表的定义、结构的修改，更不会涉及其他对象。

输入mysql -uroot -p连接数据库。-u后接用户名，-p表示需输入密码。
请输入图片描述

连接成功后可以知道以下信息：

命令的结束符为";"或者"\g";
客户端连接ID，这个数字记录了MySQL服务到目前为止的连接次数；每个新连接都会自增1，本例中为13030;
MySQL服务器的版本为"5.6.49-log"
通过help;或者\\h命令来显示帮助内容，通过\\c来清除命令行buffer。

创建数据库

使用如下语法创建数据库。

CREATE DATABASE dbname;

例如创建数据库test1，命令如下所示：

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.00 sec)

所有的DDL和DML(不包括SELECT)操作成功后都显示"Query OK"

使用SHOW DATABASES;命令来查看系统中的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test1              |
+--------------------+
4 rows in set (0.00 sec)

可以发现除了刚刚创建的test1数据库外，还有另外3个数据库，它们都是MySQL自动创建的：

information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等;
mysql：存储了系统的用户权限信息;
performance_schema：用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
使用USE tablename;选择要操作的数据库，再用SHOW TABLES;命令查看数据库中的表。

mysql> USE test1;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

由于test1是刚刚创建的数据库，所以显示为空(Empty set)。

创建表

使用如下语法创建表。

CREATE TABLE tablename (
    column_name1 column_type1 constraints,
    column_name2 column_type2 constraints,
    ...
    column_namen column_typen constraints
);

column_name是列的名字; column_type是列的数据类型; constraints是这个列的约束条件。以下创建了emp表。

mysql> CREATE TABLE emp(
    ->     ename varchar(10),
    ->     hiredate date,
    ->     sal decimal(10,2),
    ->     deptno int(2)
    -> );
Query OK, 0 rows affected (0.01 sec)

使用DESC tablename;查看表的定义。

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

使用SHOW CREATE TABLE tablename \G查看详细的表定义信息：

mysql> SHOW CREATE TABLE emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR: 
No query specified

删除表

使用DROP TABLE tablename;命令删除数据库表。

修改表

我们无法避免对表结构的修改，使用alter table语句对表结构进行修改。

修改表字段

语法如下：

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];

将emp表的ename字段约束由varchar(10)改为varchar(20):

mysql> ALTER TABLE emp MODIFY ename varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

添加表字段

语法如下：

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];

为emp表添加字段age，类型为int(3):

mysql> ALTER TABLE emp ADD COLUMN  age int(3);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表字段

语法如下：

ALTER TABLE tablename DROP [COLUMN] column_name;

删除age字段：

mysql> ALTER TABLE emp DROP age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

字段重命名

语法如下：

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name];

将ename改为ename1：

mysql> ALTER TABLE emp CHANGE ename ename1 varchar(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename1   | varchar(25)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

注意：CHANGE 和 MODIFY 都可以修改表的定义，不同的是CHANGE后面需要写两次列名，不方便。但是CHANGE的优点是可以修改列名称，MODIFY则不能。

修改字段顺序

在上文所述的语法中都有一个可选项FIRST | AFTER column_name，此选项可以用来修改字段在表中的位置，默认新增字段都是在末尾添加。
在emp表ename字段后添加birth，类型为date：

mysql> ALTER TABLE emp ADD COLUMN birth date AFTER ename;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(25)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改age顺序，将其放在最前面：

mysql> ALTER TABLE emp MODIFY age int(3) FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age      | int(3)        | YES  |     | NULL    |       |
| ename    | varchar(25)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

注意：CHANGE/FIRST | AFTER COLUME这些关键字都属于MySQL在标准SQL上的扩展，在其他数据库上不一定适用。

修改表明

我可以对数据库表进行重命名：

ALTER TABLE tablename RENAME [TO] new_tablename;

DML语句

DML 操作是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询。

插入操作

表插入记录语法如下：

INSERT INTO tablename (field1,field2,...,fieldn) VALUES (value1,value2,...,valuen);

例如向emp表中插入一条记录：

mysql> INSERT INTO emp (ename,birth,hiredate,sal,deptno,age) VALUES ('HCP','1995-10-01','2020-12-23','20000',,1,25);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 20000.00 |      1 |
+------+-------+------------+------------+----------+--------+
1 row in set (0.00 sec)

在MySQL中，INSERT语句可以一次性插入多条数据，语法如下:

INSERT INTO tablename (field1,field2,...,fieldn)
VALUES
(record1_value1,record1_value2,...,record1_valuen),
(record2_value1,record2_value2,...,record2_valuen),
...
(recordn_value1,recordn_value2,...,recordn_valuen);

如下所示插入两条记录：

mysql> INSERT INTO emp (ename,birth,sal) VALUES ('LISI','1990-10-20',10000),('JAKE','1995-12-01',25000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 20000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |   NULL |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |   NULL |
+------+-------+------------+------------+----------+--------+
3 rows in set (0.00 sec)

更新操作

修改表记录的语法如下：

UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen [WHERE CONDITION]

例如向emp表中ename为"HCP"的sal从20000改为30000：

mysql> UPDATE emp SET sal = 30000 WHERE ename = 'HCP';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |   NULL |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |   NULL |
+------+-------+------------+------------+----------+--------+
3 rows in set (0.00 sec)

UPDATE语句也可以同时修改多个表记录：

UPDATE t1,t2,...,tn SET t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]

删除操作

如果记录不再需要可以用DELETE命令进行删除，语法如下：

DELETE FROM tablename [WHERE CONDITION];

与UPDATE一样，DELETE同样可以一次删除多个表的数据：

DELETE FROM t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION];

切记：不加WHERE条件会将所有记录删除，谨慎!谨慎!再谨慎!

查询操作

目前只介绍最基本的SELECT操作语法：

SELECT * FROM tablename [WHERE CONDITION];

数据去重

使用distinct关键字来实现数据的去重：

mysql> SELECT deptno FROM emp;
+--------+
| deptno |
+--------+
|      1 |
|      1 |
|      2 |
+--------+
3 rows in set (0.01 sec)

mysql> SELECT distinct deptno FROM emp;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
+--------+
2 rows in set (0.00 sec)

条件查询

使用WHERE进行条件查询，比较运算符可以为=,>=,<=,!=等等；多个条件之间可以使用or、and 等逻辑运算符进行多条件联合查询。

mysql> SELECT * FROM emp WHERE sal > 10000 and sal < 26000;
+------+-------+------------+----------+----------+--------+
| age  | ename | birth      | hiredate | sal      | deptno |
+------+-------+------------+----------+----------+--------+
| NULL | JAKE  | 1995-12-01 | NULL     | 25000.00 |      2 |
+------+-------+------------+----------+----------+--------+
1 row in set (0.00 sec)

排序和限制

我们可以使用 ORDER BY 关键字对查询出来的数据进行排序操作，语法如下：

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC | ASC],field2 [DESC | ASC]]

其中，DESC 表示降序，ASC表示升序。如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，以此类推。

mysql> SELECT * FROM emp ORDER BY deptno ASC;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |      2 |
+------+-------+------------+------------+----------+--------+
3 rows in set (0.00 sec)

如果需要限制查询记录数可以使用LIMIT关键字来实现：

SELECT ... [LIMIT offset_start,row_count];

其中offset_start表示记录的起始偏移量，row_count表示显示的行数。默认情况下，起始偏移量为0，只需要写记录行数n就行。此时显示的就是前n条记录。

mysql> SELECT * FROM emp ORDER BY deptno ASC LIMIT 2;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
+------+-------+------------+------------+----------+--------+
2 rows in set (0.00 sec)

聚合

聚合操作的语法如下：

SELECT [field1,field2,...,fieldn] func_name
FROM tablename
[WHERE CONDITION]
[GROUP BY field1,field2,...,fieldn]
[WITH ROLLUP]
[HAVING CONDITION]

参数说明如下：

func_name 表示要做的聚合操作(函数)，常用的有sum、count(*)、max、min。
CROUP BY 关键字表示要进行分类聚合的字段。
WITH ROLLUP 表示是否队分类聚合后的结果进行再汇总。
HAVING关键字表示对分类后的结果再进行条件的过滤。

HAVING 和 WHERE 的区别在于 HAVING 是对聚合后的结果进行条件过滤，而 WHERE 是在聚合前就对记录进行过滤，如果逻辑运行，我们尽可能用 WHERE 先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 HAVING 进行再过滤。

各部门人数和总人数：

mysql> SELECT deptno,COUNT(1) FROM emp GROUP BY deptno WITH ROLLUP;
+--------+----------+
| deptno | COUNT(1) |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
|   NULL |        3 |
+--------+----------+
3 rows in set (0.00 sec)

在上一步基础上统计人数大于1的部门：

mysql> SELECT deptno,COUNT(1) FROM emp GROUP BY deptno HAVING COUNT(1) > 1;
+--------+----------+
| deptno | COUNT(1) |
+--------+----------+
|      1 |        2 |
+--------+----------+
1 row in set (0.00 sec)

统计所有员工的薪水总和、最高和最低薪水：

mysql> SELECT SUM(sal),MAX(sal),MIN(sal) FROM emp;
+----------+----------+----------+
| SUM(sal) | MAX(sal) | MIN(sal) |
+----------+----------+----------+
| 65000.00 | 30000.00 | 10000.00 |
+----------+----------+----------+
1 row in set (0.00 sec)

表连接

当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。从大类上分，表连接分为内连接和外连接。它们的最主要区别是，内连接仅仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。我们最常用的是内连接。

mysql> SELECT ename,emp.deptno,deptname FROM emp,dept WHERE emp.deptno = dept.deptno;
+-------+--------+-----------+
| ename | deptno | deptname  |
+-------+--------+-----------+
| HCP   |      1 | 测试部    |
| LISI  |      1 | 测试部    |
| JAKE  |      2 | 开发部    |
+-------+--------+-----------+
3 rows in set (0.00 sec)

外连接又分为左连接和右连接。

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。

mysql> SELECT ename,deptname FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno;
+-------+-----------+
| ename | deptname  |
+-------+-----------+
| HCP   | 测试部    |
| LISI  | 测试部    |
| JAKE  | 开发部    |
| TOM   | NULL      |
+-------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT ename,deptname FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno;
+-------+-----------+
| ename | deptname  |
+-------+-----------+
| HCP   | 测试部    |
| LISI  | 测试部    |
| JAKE  | 开发部    |
| NULL  | 运维部    |
+-------+-----------+
4 rows in set (0.00 sec)

子查询

某些情况下，当进行查询的时候需要的条件是另一个SELECT语句的结果，此时就需要使用子查询。用于子查询的关键字主要包括IN、NOT IN、=、!=、EXISTS、NOT EXISTS等。
例如查询出所有在部门在dept表中的记录：

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |      2 |
|   25 | TOM   | 1995-10-01 | 2020-12-23 | 20000.00 |      3 |
+------+-------+------------+------------+----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |      2 |
+------+-------+------------+------------+----------+--------+

如果子查询的记录数是唯一的，可以使用 = 代替 IN。
子查询在某些情况下可以使用表连接进行优化。

记录联合

我们经常需要将两个表的数据查询出来以后将结果一并显示出来，使用UNION和UNION ALL关键字来实现。

SELECT * FROM t1
UNION | UNION ALL
SELECT * FROM t2
...
UNION | UNION ALL
SELECT * FROM tn;

UNION 和 UNION ALL 的主要区别是UNION ALL是把结果集直接合并到一起，而 UNION 是将 UNION ALL 后的结果进行一次 DISTINCT，去除重复记录后的结果。

mysql> SELECT deptno FROM emp
    -> UNION ALL
    -> SELECT deptno FROM dept;
+--------+
| deptno |
+--------+
|      1 |
|      1 |
|      2 |
|      3 |
|      1 |
|      2 |
|      4 |
+--------+
7 rows in set (0.00 sec)

mysql> SELECT deptno FROM emp 
    -> UNION 
    -> SELECT deptno FROM dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
+--------+
4 rows in set (0.00 sec)

多个表查询的结果集维度要一样哦！

mysql> SELECT * FROM emp
    -> UNION ALL
    -> SELECT * FROM dept;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

DCL语句

DCL语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人很少使用。
创建一个数据库用户zhangsan，具有对test1数据库中所有表的SELECT权限：

mysql> GRANT SELECT ON test1.* to 'zhangsan'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

################################## 切换用户 ##################################

mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|      1 | 测试部    |
|      2 | 开发部    |
|      4 | 运维部    |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> INSERT INTO dept (deptno,deptname) VALUES (3,'市场部');
ERROR 1142 (42000): INSERT command denied to user 'zhangsan'@'localhost' for table 'dept'

现在我需要收回SELECT权限，使用REVOKE语句：

mysql> REVOKE SELECT ON test1.* FROM 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)

################################## 切换用户 ##################################

mysql> USE test1;
ERROR 1044 (42000): Access denied for user 'zhangsan'@'localhost' to database 'test1'

查询元数据信息

在日常编码中我们可能会遇到如下应用场景：

删除数据库test_com下的所有前缀为blog_的表；
将数据库test_com下所有存储引擎为MyISAM的表改为InnoDB

MySQL5.0之后，提供了information_schema，用来记录MySQL中的元数据信息。这个库很特殊，它是个虚拟数据库，物理上不存在相关的目录和文件；库里SHOW TABLES;显示的表都是视图。

元数据指的是数据的数据，比如表名、列名、列类型、索引名等表的各种属性名称。

对于上面提及的两个需求，SQL如下：

ysql> SELECT table_name  FROM tables WHERE table_schema='test_com' and table_name LIKE 'blog%';
+--------------------+
| table_name         |
+--------------------+
| blog_PageCache     |
| blog_access_log    |
| blog_comments      |
| blog_contents      |
| blog_fields        |
| blog_links         |
| blog_metas         |
| blog_options       |
| blog_relationships |
| blog_users         |
+--------------------+
10 rows in set (0.00 sec)

mysql> SELECT CONCAT('ALTER TABLE test_com.',table_name,' engine=innodb;') FROM tables WHERE table_schema = 'test_com' and engine = 'MyISAM';
+--------------------------------------------------------------+
| CONCAT('ALTER TABLE test_com.',table_name,' engine=innodb;') |
+--------------------------------------------------------------+
| ALTER TABLE test_com.blog_comments engine=innodb;            |
| ALTER TABLE test_com.blog_contents engine=innodb;            |
| ALTER TABLE test_com.blog_fields engine=innodb;              |
| ALTER TABLE test_com.blog_links engine=innodb;               |
| ALTER TABLE test_com.blog_metas engine=innodb;               |
| ALTER TABLE test_com.blog_options engine=innodb;             |
| ALTER TABLE test_com.blog_relationships engine=innodb;       |
| ALTER TABLE test_com.blog_users engine=innodb;               |
+--------------------------------------------------------------+
8 rows in set (0.00 sec)

常用视图介绍

查看information_schema数据库中可以发现内部有许多视图：

mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_LOCK_WAITS                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_CMP                            |
| INNODB_METRICS                        |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_INDEXES                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_CMPMEM                         |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_BEING_DELETED               |
| INNODB_SYS_TABLESPACES                |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_TABLES                     |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_FT_CONFIG                      |
+---------------------------------------+
59 rows in set (0.00 sec)

简要说明一下几个常用视图：

SCHEMATA：该表提供了当前MySQL实例中的所有数据库信息，SHOW DATABASES; 的结果就取之此表；
TABLES：该表提供了关于数据库中的表的信息(包括视图)，详细表述了某个表属于哪个SCHEMA、表类型、表引擎、创建时间等信息，SHOW tables FROM schemaname;的结果取之此表；
COLUMNS：该表提供了表中的列信息，详细表述了某张表的所有列以及每个列的信息。SHOW columns FROM schemaname.tablename的结果取之此表；
STATISTICS：该表提供了关于表索引的信息。SHOW index FROM schemaname.tablename的结果取之此表。
这里编辑标签内容

版权属于：带翅膀的猫
本文链接：https://www.chengpengper.cn/archives/139/
转载时须注明出处及本声明

Last modification：December 23rd, 2020 at 01:52 am

如果觉得我的文章对你有用，请随意赞赏

深入浅出MySQL之SQL基础

带翅膀的猫 • 2020 年 12 月 22 日

SQL简介

SQL使用入门

SQL分类

SQL语句主要可以划分为以下3个类别：

DDL(Data Definition Languages)语句：数据定义语言。这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括CREATE、DROP、ALTER等。
DML(DATA Manipulation Languages)语句：数据操纵语言。用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括INSERT、DELETE、UPDATE、SELECT等。
DCL(Data Control Languages)语句：数据控制语言。用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括GRANT、REVOKE等。

DDL语句

DDL是数据库定义语言的缩写，是对数据库内部的对象进行创建、删除、修改等操作的语言。

DDL与DML最大的区别是DML只是对表内部数据的操作，而不涉及表的定义、结构的修改，更不会涉及其他对象。

输入mysql -uroot -p连接数据库。-u后接用户名，-p表示需输入密码。
请输入图片描述

连接成功后可以知道以下信息：

命令的结束符为";"或者"\g";
客户端连接ID，这个数字记录了MySQL服务到目前为止的连接次数；每个新连接都会自增1，本例中为13030;
MySQL服务器的版本为"5.6.49-log"
通过help;或者\\h命令来显示帮助内容，通过\\c来清除命令行buffer。

创建数据库

使用如下语法创建数据库。

CREATE DATABASE dbname;

例如创建数据库test1，命令如下所示：

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.00 sec)

所有的DDL和DML(不包括SELECT)操作成功后都显示"Query OK"

使用SHOW DATABASES;命令来查看系统中的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test1              |
+--------------------+
4 rows in set (0.00 sec)

可以发现除了刚刚创建的test1数据库外，还有另外3个数据库，它们都是MySQL自动创建的：

information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等;
mysql：存储了系统的用户权限信息;
performance_schema：用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
使用USE tablename;选择要操作的数据库，再用SHOW TABLES;命令查看数据库中的表。

mysql> USE test1;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

由于test1是刚刚创建的数据库，所以显示为空(Empty set)。

创建表

使用如下语法创建表。

CREATE TABLE tablename (
    column_name1 column_type1 constraints,
    column_name2 column_type2 constraints,
    ...
    column_namen column_typen constraints
);

column_name是列的名字; column_type是列的数据类型; constraints是这个列的约束条件。以下创建了emp表。

mysql> CREATE TABLE emp(
    ->     ename varchar(10),
    ->     hiredate date,
    ->     sal decimal(10,2),
    ->     deptno int(2)
    -> );
Query OK, 0 rows affected (0.01 sec)

使用DESC tablename;查看表的定义。

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

使用SHOW CREATE TABLE tablename \G查看详细的表定义信息：

mysql> SHOW CREATE TABLE emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR: 
No query specified

删除表

使用DROP TABLE tablename;命令删除数据库表。

修改表

我们无法避免对表结构的修改，使用alter table语句对表结构进行修改。

修改表字段

语法如下：

ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];

将emp表的ename字段约束由varchar(10)改为varchar(20):

mysql> ALTER TABLE emp MODIFY ename varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

添加表字段

语法如下：

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];

为emp表添加字段age，类型为int(3):

mysql> ALTER TABLE emp ADD COLUMN  age int(3);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表字段

语法如下：

ALTER TABLE tablename DROP [COLUMN] column_name;

删除age字段：

mysql> ALTER TABLE emp DROP age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

字段重命名

语法如下：

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name];

将ename改为ename1：

mysql> ALTER TABLE emp CHANGE ename ename1 varchar(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename1   | varchar(25)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

注意：CHANGE 和 MODIFY 都可以修改表的定义，不同的是CHANGE后面需要写两次列名，不方便。但是CHANGE的优点是可以修改列名称，MODIFY则不能。

修改字段顺序

mysql> ALTER TABLE emp ADD COLUMN birth date AFTER ename;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(25)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改age顺序，将其放在最前面：

mysql> ALTER TABLE emp MODIFY age int(3) FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age      | int(3)        | YES  |     | NULL    |       |
| ename    | varchar(25)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

注意：CHANGE/FIRST | AFTER COLUME这些关键字都属于MySQL在标准SQL上的扩展，在其他数据库上不一定适用。

修改表明

我可以对数据库表进行重命名：

ALTER TABLE tablename RENAME [TO] new_tablename;

DML语句

DML 操作是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询。

插入操作

表插入记录语法如下：

INSERT INTO tablename (field1,field2,...,fieldn) VALUES (value1,value2,...,valuen);

例如向emp表中插入一条记录：

mysql> INSERT INTO emp (ename,birth,hiredate,sal,deptno,age) VALUES ('HCP','1995-10-01','2020-12-23','20000',,1,25);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 20000.00 |      1 |
+------+-------+------------+------------+----------+--------+
1 row in set (0.00 sec)

在MySQL中，INSERT语句可以一次性插入多条数据，语法如下:

INSERT INTO tablename (field1,field2,...,fieldn)
VALUES
(record1_value1,record1_value2,...,record1_valuen),
(record2_value1,record2_value2,...,record2_valuen),
...
(recordn_value1,recordn_value2,...,recordn_valuen);

如下所示插入两条记录：

mysql> INSERT INTO emp (ename,birth,sal) VALUES ('LISI','1990-10-20',10000),('JAKE','1995-12-01',25000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 20000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |   NULL |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |   NULL |
+------+-------+------------+------------+----------+--------+
3 rows in set (0.00 sec)

更新操作

修改表记录的语法如下：

UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen [WHERE CONDITION]

例如向emp表中ename为"HCP"的sal从20000改为30000：

mysql> UPDATE emp SET sal = 30000 WHERE ename = 'HCP';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |   NULL |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |   NULL |
+------+-------+------------+------------+----------+--------+
3 rows in set (0.00 sec)

UPDATE语句也可以同时修改多个表记录：

UPDATE t1,t2,...,tn SET t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]

删除操作

如果记录不再需要可以用DELETE命令进行删除，语法如下：

DELETE FROM tablename [WHERE CONDITION];

与UPDATE一样，DELETE同样可以一次删除多个表的数据：

DELETE FROM t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION];

切记：不加WHERE条件会将所有记录删除，谨慎!谨慎!再谨慎!

查询操作

目前只介绍最基本的SELECT操作语法：

SELECT * FROM tablename [WHERE CONDITION];

数据去重

使用distinct关键字来实现数据的去重：

mysql> SELECT deptno FROM emp;
+--------+
| deptno |
+--------+
|      1 |
|      1 |
|      2 |
+--------+
3 rows in set (0.01 sec)

mysql> SELECT distinct deptno FROM emp;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
+--------+
2 rows in set (0.00 sec)

条件查询

使用WHERE进行条件查询，比较运算符可以为=,>=,<=,!=等等；多个条件之间可以使用or、and 等逻辑运算符进行多条件联合查询。

mysql> SELECT * FROM emp WHERE sal > 10000 and sal < 26000;
+------+-------+------------+----------+----------+--------+
| age  | ename | birth      | hiredate | sal      | deptno |
+------+-------+------------+----------+----------+--------+
| NULL | JAKE  | 1995-12-01 | NULL     | 25000.00 |      2 |
+------+-------+------------+----------+----------+--------+
1 row in set (0.00 sec)

排序和限制

我们可以使用 ORDER BY 关键字对查询出来的数据进行排序操作，语法如下：

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC | ASC],field2 [DESC | ASC]]

其中，DESC 表示降序，ASC表示升序。如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，以此类推。

mysql> SELECT * FROM emp ORDER BY deptno ASC;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |      2 |
+------+-------+------------+------------+----------+--------+
3 rows in set (0.00 sec)

如果需要限制查询记录数可以使用LIMIT关键字来实现：

SELECT ... [LIMIT offset_start,row_count];

其中offset_start表示记录的起始偏移量，row_count表示显示的行数。默认情况下，起始偏移量为0，只需要写记录行数n就行。此时显示的就是前n条记录。

mysql> SELECT * FROM emp ORDER BY deptno ASC LIMIT 2;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
+------+-------+------------+------------+----------+--------+
2 rows in set (0.00 sec)

聚合

聚合操作的语法如下：

SELECT [field1,field2,...,fieldn] func_name
FROM tablename
[WHERE CONDITION]
[GROUP BY field1,field2,...,fieldn]
[WITH ROLLUP]
[HAVING CONDITION]

参数说明如下：

func_name 表示要做的聚合操作(函数)，常用的有sum、count(*)、max、min。
CROUP BY 关键字表示要进行分类聚合的字段。
WITH ROLLUP 表示是否队分类聚合后的结果进行再汇总。
HAVING关键字表示对分类后的结果再进行条件的过滤。

各部门人数和总人数：

mysql> SELECT deptno,COUNT(1) FROM emp GROUP BY deptno WITH ROLLUP;
+--------+----------+
| deptno | COUNT(1) |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
|   NULL |        3 |
+--------+----------+
3 rows in set (0.00 sec)

在上一步基础上统计人数大于1的部门：

mysql> SELECT deptno,COUNT(1) FROM emp GROUP BY deptno HAVING COUNT(1) > 1;
+--------+----------+
| deptno | COUNT(1) |
+--------+----------+
|      1 |        2 |
+--------+----------+
1 row in set (0.00 sec)

统计所有员工的薪水总和、最高和最低薪水：

mysql> SELECT SUM(sal),MAX(sal),MIN(sal) FROM emp;
+----------+----------+----------+
| SUM(sal) | MAX(sal) | MIN(sal) |
+----------+----------+----------+
| 65000.00 | 30000.00 | 10000.00 |
+----------+----------+----------+
1 row in set (0.00 sec)

表连接

mysql> SELECT ename,emp.deptno,deptname FROM emp,dept WHERE emp.deptno = dept.deptno;
+-------+--------+-----------+
| ename | deptno | deptname  |
+-------+--------+-----------+
| HCP   |      1 | 测试部    |
| LISI  |      1 | 测试部    |
| JAKE  |      2 | 开发部    |
+-------+--------+-----------+
3 rows in set (0.00 sec)

外连接又分为左连接和右连接。

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。

mysql> SELECT ename,deptname FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno;
+-------+-----------+
| ename | deptname  |
+-------+-----------+
| HCP   | 测试部    |
| LISI  | 测试部    |
| JAKE  | 开发部    |
| TOM   | NULL      |
+-------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT ename,deptname FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno;
+-------+-----------+
| ename | deptname  |
+-------+-----------+
| HCP   | 测试部    |
| LISI  | 测试部    |
| JAKE  | 开发部    |
| NULL  | 运维部    |
+-------+-----------+
4 rows in set (0.00 sec)

子查询

mysql> SELECT * FROM emp;
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |      2 |
|   25 | TOM   | 1995-10-01 | 2020-12-23 | 20000.00 |      3 |
+------+-------+------------+------------+----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);
+------+-------+------------+------------+----------+--------+
| age  | ename | birth      | hiredate   | sal      | deptno |
+------+-------+------------+------------+----------+--------+
|   25 | HCP   | 1995-10-01 | 2020-12-23 | 30000.00 |      1 |
| NULL | LISI  | 1990-10-20 | NULL       | 10000.00 |      1 |
| NULL | JAKE  | 1995-12-01 | NULL       | 25000.00 |      2 |
+------+-------+------------+------------+----------+--------+

如果子查询的记录数是唯一的，可以使用 = 代替 IN。
子查询在某些情况下可以使用表连接进行优化。

记录联合

我们经常需要将两个表的数据查询出来以后将结果一并显示出来，使用UNION和UNION ALL关键字来实现。

SELECT * FROM t1
UNION | UNION ALL
SELECT * FROM t2
...
UNION | UNION ALL
SELECT * FROM tn;

UNION 和 UNION ALL 的主要区别是UNION ALL是把结果集直接合并到一起，而 UNION 是将 UNION ALL 后的结果进行一次 DISTINCT，去除重复记录后的结果。

mysql> SELECT deptno FROM emp
    -> UNION ALL
    -> SELECT deptno FROM dept;
+--------+
| deptno |
+--------+
|      1 |
|      1 |
|      2 |
|      3 |
|      1 |
|      2 |
|      4 |
+--------+
7 rows in set (0.00 sec)

mysql> SELECT deptno FROM emp 
    -> UNION 
    -> SELECT deptno FROM dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
+--------+
4 rows in set (0.00 sec)

多个表查询的结果集维度要一样哦！

mysql> SELECT * FROM emp
    -> UNION ALL
    -> SELECT * FROM dept;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

DCL语句

DCL语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人很少使用。
创建一个数据库用户zhangsan，具有对test1数据库中所有表的SELECT权限：

mysql> GRANT SELECT ON test1.* to 'zhangsan'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

################################## 切换用户 ##################################

mysql> SELECT * FROM dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|      1 | 测试部    |
|      2 | 开发部    |
|      4 | 运维部    |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> INSERT INTO dept (deptno,deptname) VALUES (3,'市场部');
ERROR 1142 (42000): INSERT command denied to user 'zhangsan'@'localhost' for table 'dept'

现在我需要收回SELECT权限，使用REVOKE语句：

mysql> REVOKE SELECT ON test1.* FROM 'zhangsan'@'localhost';
Query OK, 0 rows affected (0.00 sec)

################################## 切换用户 ##################################

mysql> USE test1;
ERROR 1044 (42000): Access denied for user 'zhangsan'@'localhost' to database 'test1'

查询元数据信息

在日常编码中我们可能会遇到如下应用场景：

删除数据库test_com下的所有前缀为blog_的表；
将数据库test_com下所有存储引擎为MyISAM的表改为InnoDB

元数据指的是数据的数据，比如表名、列名、列类型、索引名等表的各种属性名称。

对于上面提及的两个需求，SQL如下：

ysql> SELECT table_name  FROM tables WHERE table_schema='test_com' and table_name LIKE 'blog%';
+--------------------+
| table_name         |
+--------------------+
| blog_PageCache     |
| blog_access_log    |
| blog_comments      |
| blog_contents      |
| blog_fields        |
| blog_links         |
| blog_metas         |
| blog_options       |
| blog_relationships |
| blog_users         |
+--------------------+
10 rows in set (0.00 sec)

mysql> SELECT CONCAT('ALTER TABLE test_com.',table_name,' engine=innodb;') FROM tables WHERE table_schema = 'test_com' and engine = 'MyISAM';
+--------------------------------------------------------------+
| CONCAT('ALTER TABLE test_com.',table_name,' engine=innodb;') |
+--------------------------------------------------------------+
| ALTER TABLE test_com.blog_comments engine=innodb;            |
| ALTER TABLE test_com.blog_contents engine=innodb;            |
| ALTER TABLE test_com.blog_fields engine=innodb;              |
| ALTER TABLE test_com.blog_links engine=innodb;               |
| ALTER TABLE test_com.blog_metas engine=innodb;               |
| ALTER TABLE test_com.blog_options engine=innodb;             |
| ALTER TABLE test_com.blog_relationships engine=innodb;       |
| ALTER TABLE test_com.blog_users engine=innodb;               |
+--------------------------------------------------------------+
8 rows in set (0.00 sec)

常用视图介绍

查看information_schema数据库中可以发现内部有许多视图：

mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_LOCK_WAITS                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_CMP                            |
| INNODB_METRICS                        |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_INDEXES                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_CMPMEM                         |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_BEING_DELETED               |
| INNODB_SYS_TABLESPACES                |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_TABLES                     |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_FT_CONFIG                      |
+---------------------------------------+
59 rows in set (0.00 sec)

简要说明一下几个常用视图：

SCHEMATA：该表提供了当前MySQL实例中的所有数据库信息，SHOW DATABASES; 的结果就取之此表；
TABLES：该表提供了关于数据库中的表的信息(包括视图)，详细表述了某个表属于哪个SCHEMA、表类型、表引擎、创建时间等信息，SHOW tables FROM schemaname;的结果取之此表；
COLUMNS：该表提供了表中的列信息，详细表述了某张表的所有列以及每个列的信息。SHOW columns FROM schemaname.tablename的结果取之此表；
STATISTICS：该表提供了关于表索引的信息。SHOW index FROM schemaname.tablename的结果取之此表。
这里编辑标签内容

版权属于：带翅膀的猫
本文链接：https://www.chengpengper.cn/archives/139/
转载时须注明出处及本声明

SQL简介

SQL使用入门

SQL分类

DDL语句

创建数据库

创建表

删除表

修改表

修改表字段

添加表字段

删除表字段

字段重命名

修改字段顺序

修改表明

DML语句

插入操作

更新操作

删除操作

查询操作

数据去重

条件查询

排序和限制

聚合

表连接

子查询

记录联合

DCL语句

查询元数据信息

常用视图介绍