SQL|关系数据库标准语言SQL01 sql|数据库|mysql

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
关系数据库标准语言SQL02

文章目录

SQL语言介绍
- SQL的特点
- SQL语言的组成
- - 1.数据定义语言(DDL)
  - 2.数据操纵语言(DML)
  - 3.数据控制语言(DCL)
数据定义
- 数据库的定义和删除
- - 1.创建数据库
  - 2.选择数据库
  - 3.删除数据库
- 数据类型
- - 1.字符串类型
  - 2.数值类型
  - 3.日期和时间类型
  - 4.布尔类型
- 基本表的定义、删除和修改
- - 1.创建表
  - 2.利用子查询来创建表
  - 3.修改表的结构
  - 4.截断表和删除表
数据查询
- 基本查询
- - 查询所有列
  - 查询指定的列
  - 去掉重复行
  - 为列起别名
  - 比较判断
  - 之间判断
  - 字符串的模糊查询
  - 空值判断
  - 之内判断
  - 排序
- 分组查询
- - 聚合函数
  - 按单列分组
  - 按多列分组
  - 使用HAVING子句
- 连接查询
- - 相等连接
  - 自身连接
  - 左外连接
  - 右外连接
- 子查询
- - 返回单值的子查询
  - 返回多值的子查询
- 合并查询结果

SQL语言介绍 SQL的特点 1.综合统一
2.高度非过程化
3.面向集合的操作方式
4.同一种语法结构提供两种使用方式
5.语言简洁、易学易用
SQL语言的组成 1.数据定义语言(DDL)
DDL用来定义、修改、删除数据库中的各种对象，包括创建、修改、删除或重命名模式对象(CREATE、ALTER、DROP、RENAME)的语句，以及删除表中所有行但不删除表(TRUNCATE)的语句等
2.数据操纵语言(DML)
DML的命令用来查询、插入、修改、删除数据库中的数据，包含用于查询数据(SELECT)、添加新行数据(INSERT)、修改现有行数据(UPDATE)、删除现有行数据(DELETE)的语句等
3.数据控制语言(DCL)
DCL用于事务控制、并发控制、完整性和安全性控制等。事务控制用于把一组DML语句组合起来形成一个事务并进行事务控制。通过事务语句可以把对数据所做的修改保存起来(COMMIT)或者回滚这些修改(ROLLBACK)。在事务中设置一个保存点(SAVEPOINT)，以便用于可能出现的回溯操作；通过管理权限(GRANT、REVOKE)等语句完成安全性控制以及通过锁定一个数据库表(LOCKTABLE)限制用户对数据访问等操作，实现并发控制。
数据定义数据库的定义和删除 1.创建数据库

mysql> CREATE DATABASE LearnMySQL; Query OK, 1 row affected (0.18 sec)

2.选择数据库

mysql> USE LearnMySQL; Database changed

3.删除数据库

mysql> DROP DATABASE LearnMySQL; Query OK, 0 rows affected (0.19 sec)

数据类型 1.字符串类型
CHAR:描述定长的字符串，说明格式为CHAR(L)，其中L为字符串长度，取值范围为1~255。比L大的值将被截断，比L小的值将用空格填补。
VARCHAR:描述变长的字符串，说明格式为VARCHAR(L)，其中L为字符串长度，取值范围为1~255。比L大的值将被截断，比L小的值不会用空格填补，按实际长度存储。
2.数值类型
INT:用于表示整数，存储长度默认为4个字节。
DECIMAL:可以用来表示所有的数值数据，说明格式为DECIMAL(p,s)，其中p表示数值数据的最大长度，s表示数值数据中小数点后的数字位数，p,s在定义时可以省略，例如DECIMAL(5)、DECIMAL
3.日期和时间类型
DATE:用来保存固定长度的日期数据
TIME:用来保存固定长度的时间数据
DATETIME:用来保存固定长度的日期时间数据。日期格式为’YYYY-MM-DD’；时间格式为’HH:MM:SS’；日期时间格式为’YYYY-MM-DD HH:MM:SS’。
4.布尔类型
BOOLEAN:TRUE、FALSE
基本表的定义、删除和修改 1.创建表

mysql> CREATE TABLE product -> ( -> p_code DECIMAL(6), -> p_name VARCHAR(30), -> p_price DECIMAL(5,2) -> ); Query OK, 0 rows affected (0.41 sec)mysql> DESC product; +---------+--------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | p_code| decimal(6,0) | YES|| NULL|| | p_name| varchar(30)| YES|| NULL|| | p_price | decimal(5,2) | YES|| NULL|| +---------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)

mysql> CREATE TABLE ord -> ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> ordno DECIMAL(8), -> p_code DECIMAL(6), -> s_code DECIMAL(6), -> ordate DATETIME DEFAULT CURRENT_TIMESTAMP, -> price DECIMAL(8,2) -> ); Query OK, 0 rows affected (0.34 sec)将id列设置为自增类型字段在默认情况下，自增类型字段的值从1开始，步长为1. 设置自增类型的字段，需将其设置为主键，否则数据表将创建失败。同时，为ordate字段设置默认值为当前系统时间。mysql> DESC ord; +--------+--------------+------+-----+-------------------+-------------------+ | Field| Type| Null | Key | Default| Extra| +--------+--------------+------+-----+-------------------+-------------------+ | id| int| NO| PRI | NULL| auto_increment| | ordno| decimal(8,0) | YES|| NULL|| | p_code | decimal(6,0) | YES|| NULL|| | s_code | decimal(6,0) | YES|| NULL|| | ordate | datetime| YES|| CURRENT_TIMESTAMP | DEFAULT_GENERATED | | price| decimal(8,2) | YES|| NULL|| +--------+--------------+------+-----+-------------------+-------------------+ 6 rows in set (0.00 sec)

2.利用子查询来创建表

mysql> CREATE TABLE ord_c -> SELECT * FROM ord; Query OK, 0 rows affected (0.54 sec) Records: 0Duplicates: 0Warnings: 0

3.修改表的结构
为表增加一个新列

mysql> ALTER TABLE ord_c -> ADD telephone VARCHAR(11); Query OK, 0 rows affected (0.27 sec) Records: 0Duplicates: 0Warnings: 0mysql> DESC ord_c; +-----------+--------------+------+-----+-------------------+-------------------+ | Field| Type| Null | Key | Default| Extra| +-----------+--------------+------+-----+-------------------+-------------------+ | id| int| NO|| 0|| | ordno| decimal(8,0) | YES|| NULL|| | p_code| decimal(6,0) | YES|| NULL|| | s_code| decimal(6,0) | YES|| NULL|| | ordate| datetime| YES|| CURRENT_TIMESTAMP | DEFAULT_GENERATED | | price| decimal(8,2) | YES|| NULL|| | telephone | varchar(11)| YES|| NULL|| +-----------+--------------+------+-----+-------------------+-------------------+ 7 rows in set (0.00 sec)

修改一个表中已有的列
一个ALTER TABLE …MODIFY语句只能为表修改一列。

mysql> ALTER TABLE ord_c -> MODIFY telephone VARCHAR(13) DEFAULT '0431-8657130'; Query OK, 0 rows affected (0.15 sec) Records: 0Duplicates: 0Warnings: 0mysql> DESC ord_c; +-----------+--------------+------+-----+-------------------+-------------------+ | Field| Type| Null | Key | Default| Extra| +-----------+--------------+------+-----+-------------------+-------------------+ | id| int| NO|| 0|| | ordno| decimal(8,0) | YES|| NULL|| | p_code| decimal(6,0) | YES|| NULL|| | s_code| decimal(6,0) | YES|| NULL|| | ordate| datetime| YES|| CURRENT_TIMESTAMP | DEFAULT_GENERATED | | price| decimal(8,2) | YES|| NULL|| | telephone | varchar(13)| YES|| 0431-8657130|| +-----------+--------------+------+-----+-------------------+-------------------+ 7 rows in set (0.00 sec)

从一个表中删除一列

mysql> ALTER TABLE ord_c -> DROP telephone; Query OK, 0 rows affected (0.53 sec) Records: 0Duplicates: 0Warnings: 0mysql> DESC ord_c; +--------+--------------+------+-----+-------------------+-------------------+ | Field| Type| Null | Key | Default| Extra| +--------+--------------+------+-----+-------------------+-------------------+ | id| int| NO|| 0|| | ordno| decimal(8,0) | YES|| NULL|| | p_code | decimal(6,0) | YES|| NULL|| | s_code | decimal(6,0) | YES|| NULL|| | ordate | datetime| YES|| CURRENT_TIMESTAMP | DEFAULT_GENERATED | | price| decimal(8,2) | YES|| NULL|| +--------+--------------+------+-----+-------------------+-------------------+ 6 rows in set (0.00 sec)

4.截断表和删除表
截断表
删除了表中所有数据行，但表的结构依然保留

mysql> TRUNCATE TABLE ord_c; Query OK, 0 rows affected (0.54 sec)

删除表
不仅删除表中的数据而且删除表的结构。

mysql> DROP TABLE ord_c; Query OK, 0 rows affected (0.22 sec)mysql> DESC ord_c; ERROR 1146 (42S02): Table 'learnmysql.ord_c' doesn't exist

数据查询基本查询查询所有列

mysql> select * fromproducts; +---------+---------------------+------------+ | prod_id | prod_name| prod_price | +---------+---------------------+------------+ | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BNBG03| Rabbit bean bag toy |3.49 | | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear|8.99 | | BR03| 18 inch teddy bear|11.99 | | RGAN01| Raggedy Ann|4.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | +---------+---------------------+------------+ 9 rows in set (0.04 sec)

查询指定的列

mysql> select prod_name fromproducts; +---------------------+ | prod_name| +---------------------+ | Fish bean bag toy| | Bird bean bag toy| | Rabbit bean bag toy | | 8 inch teddy bear| | 12 inch teddy bear| | 18 inch teddy bear| | Raggedy Ann| | King doll| | Queen dool| +---------------------+ 9 rows in set (0.00 sec)

去掉重复行
复制了一张products表，并加入了一行重复数据

mysql> SELECT * FROM products_c; +---------+---------------------+------------+ | prod_id | prod_name| prod_price | +---------+---------------------+------------+ | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BNBG03| Rabbit bean bag toy |3.49 | | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear|8.99 | | BR03| 18 inch teddy bear|11.99 | | RGAN01| Raggedy Ann|4.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | | RYL02| Queen dool|9.49 | +---------+---------------------+------------+ 10 rows in set (0.00 sec)

mysql> SELECT DISTINCT * FROM products_c; +---------+---------------------+------------+ | prod_id | prod_name| prod_price | +---------+---------------------+------------+ | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BNBG03| Rabbit bean bag toy |3.49 | | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear|8.99 | | BR03| 18 inch teddy bear|11.99 | | RGAN01| Raggedy Ann|4.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | +---------+---------------------+------------+ 9 rows in set (0.00 sec)

为列起别名

mysql> SELECT prod_id AS 产品编号,prod_name AS 产品名称,prod_price AS 产品价格 FROM products; +--------------+---------------------+--------------+ | 产品编号| 产品名称| 产品价格| +--------------+---------------------+--------------+ | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BNBG03| Rabbit bean bag toy |3.49 | | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear|8.99 | | BR03| 18 inch teddy bear|11.99 | | RGAN01| Raggedy Ann|4.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | +--------------+---------------------+--------------+ 9 rows in set (0.00 sec)

比较判断

mysql> SELECT * FROM products WHERE prod_id='BR01'; +---------+-------------------+------------+ | prod_id | prod_name| prod_price | +---------+-------------------+------------+ | BR01| 8 inch teddy bear |5.99 | +---------+-------------------+------------+ 1 row in set (0.00 sec)mysql> SELECT * FROM products WHERE prod_price >5; +---------+--------------------+------------+ | prod_id | prod_name| prod_price | +---------+--------------------+------------+ | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear |8.99 | | BR03| 18 inch teddy bear |11.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | +---------+--------------------+------------+

之间判断

mysql> SELECT * FROM products WHERE prod_price BETWEEN 5 AND 9; +---------+--------------------+------------+ | prod_id | prod_name| prod_price | +---------+--------------------+------------+ | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear |8.99 | +---------+--------------------+------------+ 2 rows in set (0.00 sec)

字符串的模糊查询
%用于表示0个或任意多个字符；_表示任意一个字符

mysql> SELECT * FROM products WHERE prod_id LIKE 'R%'; +---------+-------------+------------+ | prod_id | prod_name| prod_price | +---------+-------------+------------+ | RGAN01| Raggedy Ann |4.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | +---------+-------------+------------+ 3 rows in set (0.00 sec)mysql> SELECT * FROM products WHERE prod_id LIKE '_G%'; +---------+-------------+------------+ | prod_id | prod_name| prod_price | +---------+-------------+------------+ | RGAN01| Raggedy Ann |4.99 | +---------+-------------+------------+ 1 row in set (0.00 sec)

mysql> SELECT * FROM products WHERE prod_id NOT LIKE 'B%'; +---------+-------------+------------+ | prod_id | prod_name| prod_price | +---------+-------------+------------+ | RGAN01| Raggedy Ann |4.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | +---------+-------------+------------+ 3 rows in set (0.00 sec)

空值判断

mysql> select * from websites; +----+----------+---------------------------+-------+---------+ | id | name| url| alexa | country | +----+----------+---------------------------+-------+---------+ |1 | Google| https://www.google.cm/|1 | USA| |2 | Taobao| https://www.taobao.com/|13 | CN| |3 | Xuexi| http://www.xxx.cn|888 | xxx| |4 | Weibo| http://weibo.com/|20 | CN| |5 | Facebook | https://www.facebook.com/ |1 | USA| |6 | NULL| NULL|50 | NULL| +----+----------+---------------------------+-------+---------+ 6 rows in set (0.00 sec)mysql> SELECT * FROM websites WHERE url IS NULL; +----+------+------+-------+---------+ | id | name | url| alexa | country | +----+------+------+-------+---------+ |6 | NULL | NULL |50 | NULL| +----+------+------+-------+---------+ 1 row in set (0.00 sec)mysql> SELECT * FROM websites WHERE url IS NOT NULL; +----+----------+---------------------------+-------+---------+ | id | name| url| alexa | country | +----+----------+---------------------------+-------+---------+ |1 | Google| https://www.google.cm/|1 | USA| |2 | Taobao| https://www.taobao.com/|13 | CN| |3 | Xuexi| http://www.xxx.cn|888 | xxx| |4 | Weibo| http://weibo.com/|20 | CN| |5 | Facebook | https://www.facebook.com/ |1 | USA| +----+----------+---------------------------+-------+---------+ 5 rows in set (0.00 sec)

之内判断

mysql> SELECT prod_name,prod_price FROM products WHERE prod_id IN ('BR01','BR02') and prod_price <8; +-------------------+------------+ | prod_name| prod_price | +-------------------+------------+ | 8 inch teddy bear |5.99 | +-------------------+------------+ 1 row in set (0.00 sec)

排序
默认ASC升序，加DESC降序

mysql> SELECT * FROM products ORDER BY prod_price; +---------+---------------------+------------+ | prod_id | prod_name| prod_price | +---------+---------------------+------------+ | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BNBG03| Rabbit bean bag toy |3.49 | | RGAN01| Raggedy Ann|4.99 | | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear|8.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | | BR03| 18 inch teddy bear|11.99 | +---------+---------------------+------------+ 9 rows in set (0.00 sec)mysql> SELECT * FROM products ORDER BY prod_price DESC; +---------+---------------------+------------+ | prod_id | prod_name| prod_price | +---------+---------------------+------------+ | BR03| 18 inch teddy bear|11.99 | | RYL01| King doll|9.49 | | RYL02| Queen dool|9.49 | | BR02| 12 inch teddy bear|8.99 | | BR01| 8 inch teddy bear|5.99 | | RGAN01| Raggedy Ann|4.99 | | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BNBG03| Rabbit bean bag toy |3.49 | +---------+---------------------+------------+ 9 rows in set (0.00 sec)

SELECT语句中包含多个子句时，ORDER BY必须是最后一个子句
可以使用列的别名、位置进行排序

mysql> SELECT prod_price AS 产品价格 FROM products ORDER BY 产品价格 DESC; +--------------+ | 产品价格| +--------------+ |11.99 | |9.49 | |9.49 | |8.99 | |5.99 | |4.99 | |3.49 | |3.49 | |3.49 | +--------------+ 9 rows in set (0.00 sec)

对产品名称降序，对产品价格升序

mysql> SELECT * FROM products ORDER BY prod_name DESC,prod_price; +---------+---------------------+------------+ | prod_id | prod_name| prod_price | +---------+---------------------+------------+ | RGAN01| Raggedy Ann|4.99 | | BNBG03| Rabbit bean bag toy |3.49 | | RYL02| Queen dool|9.49 | | RYL01| King doll|9.49 | | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BR01| 8 inch teddy bear|5.99 | | BR03| 18 inch teddy bear|11.99 | | BR02| 12 inch teddy bear|8.99 | +---------+---------------------+------------+ 9 rows in set (0.00 sec)

分组查询聚合函数

mysql> SELECT COUNT(*) AS 产品总数,SUM(prod_price) AS 产品价格总和,AVG(prod_price) AS 平均价格,MAX(prod_price) AS 最高价,MIN(prod_price) AS 最低价 FROM products; +--------------+--------------------+--------------+-----------+-----------+ | 产品总数| 产品价格总和| 平均价格| 最高价| 最低价| +--------------+--------------------+--------------+-----------+-----------+ |9 |61.41 |6.823333 |11.99 |3.49 | +--------------+--------------------+--------------+-----------+-----------+ 1 row in set (0.00 sec)

聚合函数只能出现在所查询的列、ORDER BY、HAVING子句中，不能出现在WHERE、GROUP BY子句中。
按单列分组
我对products表如下修改

mysql> SELECT * FROM products; +----+---------+---------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+---------------------+------------+ |1 | BNBG01| Fish bean bag toy|3.49 | |2 | BNBG01| Bird bean bag toy|3.49 | |3 | BNBG01| Rabbit bean bag toy |3.49 | |4 | BR01| 8 inch teddy bear|5.99 | |5 | BR01| 12 inch teddy bear|8.99 | |6 | BR01| 18 inch teddy bear|11.99 | |7 | RGAN01| Raggedy Ann|4.99 | |8 | RYL01| King doll|9.49 | |9 | RYL01| Queen dool|9.49 | +----+---------+---------------------+------------+ 9 rows in set (0.00 sec)

按产品编号分组，按产品编号升序输出每组产品的平均价格，最高价格和最低价格

mysql> SELECT prod_id AS 产品编号,AVG(prod_price) AS 平均价格,MAX(prod_price) AS 最高价格,MIN(prod_price) AS 最低价格 FROM products GROUP BY prod_id ORDER BY prod_id; +--------------+--------------+--------------+--------------+ | 产品编号| 平均价格| 最高价格| 最低价格| +--------------+--------------+--------------+--------------+ | BNBG01|3.490000 |3.49 |3.49 | | BR01|8.990000 |11.99 |5.99 | | RGAN01|4.990000 |4.99 |4.99 | | RYL01|9.490000 |9.49 |9.49 | +--------------+--------------+--------------+--------------+ 4 rows in set (0.00 sec)

按多列分组
按产品编号和产品价格进行分组

mysql> SELECT * FROM products GROUP BY prod_id,prod_price ORDER BY prod_id; +----+---------+--------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+--------------------+------------+ |1 | BNBG01| Fish bean bag toy|3.49 | |4 | BR01| 8 inch teddy bear|5.99 | |5 | BR01| 12 inch teddy bear |8.99 | |6 | BR01| 18 inch teddy bear |11.99 | |7 | RGAN01| Raggedy Ann|4.99 | |8 | RYL01| King doll|9.49 | +----+---------+--------------------+------------+ 6 rows in set (0.00 sec)

使用HAVING子句
按产品编号分组，按产品编号升序输出产品平均价格大于8的组和其平均价格

mysql> SELECT prod_id AS 产品编号,AVG(prod_price) AS 平均价格 FROM products WHERE prod_id IS NOT NULL GROUP BY prod_id HAVING AVG(prod_price)>8; +--------------+--------------+ | 产品编号| 平均价格| +--------------+--------------+ | BR01|8.990000 | | RYL01|9.490000 | +--------------+--------------+ 2 rows in set (0.00 sec)

上述语句也可写成

mysql> SELECT prod_id AS 产品编号,AVG(prod_price) AS 平均价格 FROM products WHERE prod_id IS NOT NULL GROUP BY prod_id HAVING 平均价格>8;

连接查询现在需要再创建一个表
产品生产者的信息表

mysql> SELECT * FROM producers; +----+------+---------+------+ | id | name | prod_id | age| +----+------+---------+------+ |1 | jack | BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy | RYL01|24 | +----+------+---------+------+ 3 rows in set (0.00 sec)

相等连接

mysql> SELECT p1.prod_id AS 产品编号,p1.prod_name AS 产品名称,p2.name AS 生产者,p1.prod_price AS 产品价格 FROM products p1,producers p2 WHERE p1.prod_id=p2.prod_id ORDER BY 产品编号; +--------------+---------------------+-----------+--------------+ | 产品编号| 产品名称| 生产者| 产品价格| +--------------+---------------------+-----------+--------------+ | BNBG01| Fish bean bag toy| jack|3.49 | | BNBG01| Bird bean bag toy| jack|3.49 | | BNBG01| Rabbit bean bag toy | jack|3.49 | | BR01| 8 inch teddy bear| tom|5.99 | | BR01| 12 inch teddy bear| tom|8.99 | | BR01| 18 inch teddy bear| tom|11.99 | | RYL01| King doll| lucy|9.49 | | RYL01| Queen dool| lucy|9.49 | +--------------+---------------------+-----------+--------------+ 8 rows in set (0.00 sec)

可以发现产品编号为RGAN01的产品信息没有输出，因为producers表里没有与其对应的生产者。
上述语句也可写成

mysql> SELECT p1.prod_id AS 产品编号,p1.prod_name AS 产品名称,p2.name AS 生产者,p1.prod_price AS 产品价格 FROM products p1 INNER JOIN producers p2 ON p1.prod_id=p2.prod_id ORDER BY 产品编号;

自身连接
再建个表，empno(雇员编号)，mgr(管理员编号)

mysql> SELECT * FROM emp; +-------+-------+------+ | empno | ename | mgr| +-------+-------+------+ |7369 | SMITH | 7902 | |7566 | JONES | 7839 | |7788 | SCOTT | 7566 | |7839 | KING| NULL | |7876 | ADAMS | 7788 | |7902 | FORD| 7566 | +-------+-------+------+ 6 rows in set (0.00 sec)

把一个表映射成两个表，一个做雇员表，一个做管理员表，再把两个表连接起来

mysql> SELECT e.ename 雇员,m.ename 管理员 FROM emp e,emp m WHERE m.empno=e.mgr; +--------+-----------+ | 雇员| 管理员| +--------+-----------+ | SMITH| FORD| | JONES| KING| | SCOTT| JONES| | ADAMS| SCOTT| | FORD| JONES| +--------+-----------+ 5 rows in set (0.00 sec)

他们之间的关系是这样的

文章图片

左外连接
products p1 LEFT JOIN producers p2
显示products表中所有记录

mysql> SELECT p1.prod_id AS 产品编号,p1.prod_name AS 产品名称,p2.name AS 生产者,p1.prod_price AS 产品价格 FROM products p1 LEFT JOIN producers p2 ON p1.prod_id=p2.prod_id ORDER BY 产品编号; +--------------+---------------------+-----------+--------------+ | 产品编号| 产品名称| 生产者| 产品价格| +--------------+---------------------+-----------+--------------+ | BNBG01| Fish bean bag toy| jack|3.49 | | BNBG01| Bird bean bag toy| jack|3.49 | | BNBG01| Rabbit bean bag toy | jack|3.49 | | BR01| 8 inch teddy bear| tom|5.99 | | BR01| 12 inch teddy bear| tom|8.99 | | BR01| 18 inch teddy bear| tom|11.99 | | RGAN01| Raggedy Ann| NULL|4.99 | | RYL01| King doll| lucy|9.49 | | RYL01| Queen dool| lucy|9.49 | +--------------+---------------------+-----------+--------------+ 9 rows in set (0.00 sec)

右外连接
products p1 RIGHT JOIN producers p2
显示producers表中所有记录

mysql> SELECT p1.prod_id AS 产品编号,p1.prod_name AS 产品名称,p2.name AS 生产者,p1.prod_price AS 产品价格 FROM products p1 RIGHT JOIN producers p2 ON p1.prod_id=p2.prod_id ORDER BY 产品编号; +--------------+---------------------+-----------+--------------+ | 产品编号| 产品名称| 生产者| 产品价格| +--------------+---------------------+-----------+--------------+ | BNBG01| Fish bean bag toy| jack|3.49 | | BNBG01| Bird bean bag toy| jack|3.49 | | BNBG01| Rabbit bean bag toy | jack|3.49 | | BR01| 8 inch teddy bear| tom|5.99 | | BR01| 12 inch teddy bear| tom|8.99 | | BR01| 18 inch teddy bear| tom|11.99 | | RYL01| King doll| lucy|9.49 | | RYL01| Queen dool| lucy|9.49 | +--------------+---------------------+-----------+--------------+ 8 rows in set (0.00 sec)

子查询返回单值的子查询

mysql> SELECT * FROM producers WHERE name=(SELECT name FROM producers WHERE age=28); +----+------+---------+------+ | id | name | prod_id | age| +----+------+---------+------+ |1 | jack | BNBG01|28 | +----+------+---------+------+ 1 row in set (0.00 sec)

返回多值的子查询
查询价格最低且产品名称不包含（‘Fish bean bag toy’ 和’Rabbit bean bag toy’）的产品信息

mysql> SELECT * from products WHERE prod_price IN (SELECT MIN(prod_price) FROM products) AND prod_name<>'Fish bean bag toy' AND prod_name<>'Rabbit bean bag toy'; +----+---------+-------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+-------------------+------------+ |2 | BNBG01| Bird bean bag toy |3.49 | +----+---------+-------------------+------------+ 1 row in set (0.00 sec)

改变表信息为

mysql> SELECT * FROM products; +----+---------+---------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+---------------------+------------+ |1 | BNBG01| Fish bean bag toy|3.49 | |2 | BNBG01| Bird bean bag toy|3.49 | |3 | BNBG02| Rabbit bean bag toy |3.49 | |4 | BR01| 8 inch teddy bear|5.99 | |5 | BR02| 12 inch teddy bear|8.99 | |6 | BR03| 18 inch teddy bear|11.99 | |7 | RGAN01| Raggedy Ann|4.99 | |8 | RYL01| King doll|9.49 | |9 | RYL02| Queen dool|9.49 | +----+---------+---------------------+------------+ 9 rows in set (0.00 sec)

查询产品价格大于‘BR02’的所有产品信息

mysql> SELECT * FROM products WHERE prod_price>ALL(SELECT prod_price FROM products WHERE prod_id='BR02'); +----+---------+--------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+--------------------+------------+ |6 | BR03| 18 inch teddy bear |11.99 | |8 | RYL01| King doll|9.49 | |9 | RYL02| Queen dool|9.49 | +----+---------+--------------------+------------+ 3 rows in set (0.00 sec)mysql> SELECT * FROM products WHERE prod_price



 
mysql> SELECT * FROM products WHERE prod_price>ANY(SELECT prod_price FROM products WHERE prod_id='BR02');

+----+---------+--------------------+------------+
| id | prod_id | prod_name| prod_price |
+----+---------+--------------------+------------+
|6 | BR03| 18 inch teddy bear |11.99 |
|8 | RYL01| King doll|9.49 |
|9 | RYL02| Queen dool|9.49 |
+----+---------+--------------------+------------+
3 rows in set (0.00 sec)mysql> SELECT * FROM products WHERE prod_price


 查询生产者为lucy的产品信息

 
mysql> SELECT * FROM products WHERE EXISTS (SELECT * FROM producers WHERE products.prod_id=producers.prod_id AND name='lucy');

+----+---------+-----------+------------+
| id | prod_id | prod_name | prod_price |
+----+---------+-----------+------------+
|8 | RYL01| King doll |9.49 |
+----+---------+-----------+------------+
1 row in set (0.00 sec)


 相当于

 
mysql> SELECT * FROM products WHERE prod_id IN (SELECT prod_id FROM producers WHERE products.prod_id=producers.prod_id AND name='lucy');



 
合并查询结果 
mysql> SELECT * FROM products WHERE prod_id='BR01'
-> UNION
-> SELECT * FROM products WHERE prod_price=9.49;

+----+---------+-------------------+------------+
| id | prod_id | prod_name| prod_price |
+----+---------+-------------------+------------+
|4 | BR01| 8 inch teddy bear |5.99 |
|8 | RYL01| King doll|9.49 |
|9 | RYL02| Queen dool|9.49 |
+----+---------+-------------------+------------+
3 rows in set (0.00 sec)


 对合并后的查询结果排序

 
mysql> SELECT * FROM products WHERE prod_id='BR01'
-> UNION
-> SELECT * FROM products WHERE prod_price=9.49
-> ORDER BY prod_id DESC;

+----+---------+-------------------+------------+
| id | prod_id | prod_name| prod_price |
+----+---------+-------------------+------------+
|9 | RYL02| Queen dool|9.49 |
|8 | RYL01| King doll|9.49 |
|4 | BR01| 8 inch teddy bear |5.99 |
+----+---------+-------------------+------------+
3 rows in set (0.00 sec)


 ORDER BY之后排序的列名一定是来自第一个表中的列名，第一个表中的列名如果设置了别名，在ORDER BY后面也要写成别名。

 
mysql> SELECT prod_id AS 产品编号,prod_name,prod_price FROM products WHERE prod_id='BR01'
-> UNION
-> SELECT prod_id,prod_name,prod_price FROM products WHERE prod_price=9.49
-> ORDER BY 产品编号 DESC;

+--------------+-------------------+------------+
| 产品编号| prod_name| prod_price |
+--------------+-------------------+------------+
| RYL02| Queen dool|9.49 |
| RYL01| King doll|9.49 |
| BR01| 8 inch teddy bear |5.99 |
+--------------+-------------------+------------+
3 rows in set (0.00 sec)


 【SQL|关系数据库标准语言SQL01】这篇文章就先写到这里吧，下篇文章继续。
 关系数据库标准语言SQL02


		  	

    
    




    
    
    


推荐阅读

           
                  
              
                  5类商标分类 商标30类什么意思，商标三十五类 
                
                   
                
              
            

                  
              
                  柠檬泡酒能祛斑吗 
                
                   
                
              
            

                  
              
                  水仙土培好还是水培好 水仙可以土培么 
                
                   
                
              
            

                  
              
                  如何绘制简单的中国地图？ 
                
                   
                
              
            

                  
              
                  如何安装手机服务器系统？ 手机服务器系统怎么安装 
                
                   
                
              
            

                  
              
                  分析恐怖事件,美国911恐怖事件 
                
                   
                
              
            

                  
              
                  年画产地有哪三个 
                
                   
                
              
            

                  
              
                  广东惠州最好的大学 
                
                   
                
              
            

                  
              
                  《暗黑破坏神3：夺魂之镰》野蛮人废土套玩法攻略 
                
                   
                
              
            

                  
              
                  win10关机选项无睡眠的解决技巧在哪 WIN10关机选项无睡眠的解决技巧 
                
                   
                
              
            

                  
              
                  诌一套《哀江南》，放悲声唱到老 
                
                   
                
              
            

                  
              
                  2023年成都居民大病医疗互助报销比例是多少 
                
                   
                
              
            

                  
              
                  华为p40的返回键在哪里（华为p40怎么设置返回键） 
                
                   
                
              
            

                  
              
                  肾阳虚喝什么茶叶好？四款茶饮值得一试 
                
                   
                
              
            

                  
              
                  防溺水六不一会内容 防溺水六不一会分别是什么 
                
                   
                
              
            

                  
              
                  云电脑哪个好用?玩玩dnf或者lol？ 
                
                   
                
              
            

                  
              
                  一杯酸奶的热量是多少大卡 
                
                   
                
              
            

                  
              
                  iPhone改语言有什么坏处，苹果6s把语言改成英文有什么影响 
                
                   
                
              
            

                  
              
                  两高贷款诈骗罪司法解释重要规定都有哪些 
                
                   
                
              
            

                  
              
                  豆粕是什么东西 
                
                   
                
              
            

          

正则表达式教程（30分钟让你精通正则表达式语法） 
 云栖号技术分享|专访李飞飞 （从清华附中高材生到阿里飞刀，一口井钻出「云原生」） 
 java|主题（JAVA 桥接模式） 
 SQL|如何实现高性能的数据处理\数据库性能优化 
 架构|Java大牛带你4小时开发一个SpringBoot+vue前后端分离博客项目 
 一文读懂 MySQL Explain 执行计划 
 MySQL索引（一） 
 MySQL索引（二）索引优化方案你都了解吗 
 服务器|分布式ID问题及解决方案