数据库(5) | MySQL SQL 语言查询

SQL语言基础

本章，我们将会重点探讨SQL语言基础，学习用SQL进行数据库的基本数据查询操作。另外请注意本章的SQL语法基于MySQL数据库的SQL语法。

PL/SQL: Procedural Language SQL 过程化语言 SQL

SQL定义

SQL，Structured Query Language，结构化查询语言。SQL 是用于访问和处理数据库的标准的计算机语言。

读音：/ˈɛs kjuː ˈɛl/ 或者 /ˈsiːkwəl/

SQL语言，和C、C++、C#、Java、Python、PHP一样，是一种编程语言，每个月都可以看到Tiobe编程排行榜上SQL上榜。同时SQL又是一种标准，每个数据库的厂商都提供了对标准SQL的支持。此外每个厂商也基本上扩展了标准SQL的使用。

SQL编程是指用SQL语言来完成对数据库的逻辑性操作。这个操作可以比较简单，只有一段SQL来完成最基本的数据库操作；也可以比较复杂，需要多段SQL在一起建立起存储过程来完成复杂的数据库的操作。

注释

SQL的注释有两种形式：单行注释和多行注释。

单行注释：

单行以--开头，该行之后的内容以及SQL代码将会被注释掉。

MySQL 中，--后面需要加一个空格才能代表是注释，对于其他数据库，不需要空格也可以。

例如

-- 该行是注释，无论是文本还是SQL代码都会被注释掉
-- SELECT * FROM Country;

多行注释：

多行注释是以/*开头，以*/结尾的，中间的部分将会被全部注释掉。

例如

/*
该行是注释
该行还是注释
SELECT * FROM Country;
*/

SQL语言分类

SQL语言大体上可以分为六大类别：

一：数据查询语言（DQL ：Data Query Language）：Select
二：数据操作语言（DML ：Data Manipulation Language）：Insert、Update、Delete
三：事务处理语言（TPL）：BEGIN TRANSACTION、Commit、Rollback
四：数据控制语言（DCL）：Grant、Revoke
五：数据定义语言（DDL）：Create、Drop、Alter（table,index etc.）
六：指针控制语言（CCL）：DECLARE CURSOR，FETCH INTO

DQL语言

DQL，Data Query Language，数据查询语言。

SELECT 查询

基本语法结构

SELECT <字段名1, 字段名2...>
FROM <表名1, 表名2...>
WHERE <条件>;

DDL语言

DDL，Data Definition Language，数据定义语言。

注意：DDL中，必须加入被定义的对象

  • DDL 数据定义子语言 DBMS：数据定义
  1. CREATE [object] 创建
  2. ALTER [object] 修改
  3. DROP [object] 删除

[object]:

  1. DATABASE data：数据 base 基地 数据库
  2. TABLE* table：桌子， 表格
  3. VIEW view： 视图，用眼睛看数据
  4. INDEX index： 索引，目录（优化查询）

CREATE创建

创建数据库的对象。

CREATE DATABASE
CREATE TABLESPACE （Oracle 特有的结构，类似于货架）
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE （存储过程）
CREATE USER

ALTER修改

修改数据库的对象

ALTER TABLE
ALTER USER

DROP删除

删除数据库的对象，对象中的内容也一定一并删除。

  • 删除数据库：数据库中所有的东西（表、数据……）全部都没有了
DROP TABLE
DROP VIEW
DROP PROCEDURE
DROP USER

RENAME重命名

重命名数据库的对象

RENAME TABLE
RENAME COLUMN

DML语言

DML，Data Manipulation Language，数据操纵子语言。

DML的对象一般指的是表。DML不会对数据库对象（比如：表）的结构进行进行任何更改，只会对数据库对象（比如：表）的数据记录进行增加、更新、删除等操作。此外，DML的操作，需要事务提交才能真正完成。

  • DML 数据操纵子语言 DBMS：数据操纵
  1. SELECT 查询 【查】(有些时候，会出来一个 DQL，单独只查询)
  2. INSERT 增加，插入 【增】
  3. UPDATE 更新【改】
  4. DELETE 移除【删】
  • 对象：记录，数据

INSERT插入

在表中插入记录。增加记录。

UPDATE更新

更新表中的记录，可以更新精确到字段。

DELETE删除

删除表中的记录，对表结构没有任何影响。

DCL/TPL语言

DCL，Data Control Language，数据控制子语言。
TPL, Transaction Process Language,事务处理语言

DCL应用的场景，一般是授权、回收等权限操作
TPL一般是事务处理，包括事务的提交、回滚。

GRANT

授权，给用户授权。授权后的用户才可以操作数据库。

REVOKE

取消授权

COMMIT

提交，事务提交

ROLLBACK

回滚，事务回滚

MySQL数据类型

MySQL的数据类型见如下表格

  • 数值型
整数类型 字节 范围（有符号） 范围（无符号） 用途
TINYINT 1字节 (-128，127) (0，255) 小整数值
SMALLINT 2字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值,如：float（7，3）表示总长度7位，小数点3位
DOUBLE 8字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值 ，double（7，3）表示总长度7位，小数点3位
DECIMAL 依赖于M和D的值， 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 DECIMAL(4, 1) 取值范围：-999.9 到 9999.9
  • 字符串型
字符串类型 字节大小 描述及存储需求
CHAR 0-255字节 定长字符串，如CHAR(10)，定长10占位，不足补空格
VARCHAR 0-255字节 变长字符串 ，如VARCHAR（10），最长10个字节，存储长度按照实际输入长度为准
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65535字节 二进制形式的长文本数据
TEXT 0-65535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
VARBINARY(M) M 允许长度0-M个字节的定长字节符串，值的长度+1个字节
BINARY(M) M 允许长度0-M个字节的定长字节符串
  • 日期时间型
类型 大小(字节) 范围 格式 用途
DATE 4 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2037 YYYYMMDD HHMMSS 混合日期和时间值，用于记录INSERT或UPDATE操作时记录日期和时间。 如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。 也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。
  • 混合型
ENUM SET
ENUM 类型 　　ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值， 除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。 ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息， 这个错误值用索引 0 或者一个空字符串表示。 MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。 SET 类型 　SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。 　一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。 并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。 希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

表的基本操作

表的基本操作主要包括对表的增删查改，以及创建、修改和移除表结构。

命令行操作MySQL

  • 登录数据库：mysql-u root-p
  • 列出所有的数据库: show databases;
  • 选择某个数据库(test)：use test;
  • 列出选定数据库的表：show tables;
  • 描述(desc, describe) 某个表结构: desc salesorder; 
Setting environment for using XAMPP for Windows.
we@TEACHER-3 d:\xampp
# cd mysql\bin

we@TEACHER-3 D:\xampp\mysql\bin
# mysql-u root-p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
7 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| salesorder     |
| user           |
+----------------+
2 rows in set (0.00 sec)

mysql> desc salesorder;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | char(10)      | YES  |     | NULL    |       |
| tx_date | datetime      | YES  |     | NULL    |       |
| amount  | decimal(9,2)  | YES  |     | NULL    |       |
| remark  | varchar(1000) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

SQLyog 创建表

  1. 打开SQLyog，并连接数据库

  2. 选择 test数据库：用鼠标左键点击 左侧列表的 test

  3. 右键 test | 表，选择 创建表

  4. 填写具体的输入

    Snap17.jpg
  • 表名称(Table Name)：英文（或者英文+数字），不需要空格
    • 数据库(Database)：查看是不是 test
    • 引擎(Engine)：选择 InnoDB
    • 字符集(character set)：utf8
    • 核对(collection)：utf8_general_ci

表约束

约束，constraint，用于实施数据库中的某些数据完整性。当给某一列增加一个约束，MySQL自动确保不满足此约束的数据是绝对不能被接受的。如果用户试图写一个不满足约束的数据记录，那么MySQL就会对这个非法的SQL语句产生一个错误。

约束是表级的强制规定

约束放置在表中删除有关联关系的数据

约束可以再创建或者增加包含某列的表时，与该列进行关联，也可以在表创建以后通过SQL命令ALTER TABLE来实现与该列的关联。

主要的几种约束类型:

  • NOT NULL(非空)

    设定 NOT NULL 非空的列，必须有值，尽管值可以重复。

    场景：【名字】，姓名、性别、出生日期、课程名、客户名称、商品名称

任何列都可以设置为NOT NULL。如果在SQL操作中将一个NULL值赋给某个有NOT NULL约束的列，那么MySQL会为这个语句返回一个错误。

  • **唯一键 **(UNIQUE KEY)

    设定 UNIQUE， 唯一。该列不可以重复，可以不填（NULL），如果填了就不能重复

    场景：【可有可无的唯一标识】，手机号、QQ号，Email，银行账户账号，支付宝。。。

如果将某个列设置为唯一，那么就不能在表中插入和这个列中已有值重复的行，也不能修改已有的列值使之与其他列值重复。

  1. 字段数据允许为空
  2. 如果有数据那么必须保证每行都不相同，否则无法存储
  3. 系统也会为唯一键默认创建一个索引（高级，了解就行）
  4. 可以是一个或多个字段组成
  5. 一个表的唯一（唯一键）可以有多个
    1. 比如：第5列（手机号）本身唯一，第2列（股票代码）+第3列（交易时间）也唯一
    2. 如上：就有两个唯一键

  • 主键(Primary Key, 简称PK)

    PK = NOT NULL + UNIQUE

    一个表的主键，只能有一个。

    这个主键可能是1列，也可能是多列一起组成。

    • 比如 身份证号 做主键
    • 比如 银行卡号 + 社保卡号 做主键

每个表最多可以有一个主键约束。主键约束可以由表中的多个列组成。

主键：相当于身份证号码，是唯一的，通过身份证号码只能找到一个人，即通过主键列只能找到一行数据，在创建表时，通常要有主键列

主键属于表对象，所以主键有一个名字，若没给主键指定名字，MySQL会自动分配一个唯一的名字，在一个表中只能有一个主键对象

1.主键一定是唯一的行标识，即每行的主键都不会重复
2.主键是不允许为空
3.系统会为主键默认的创建一个索引
4.主键可以是一个或多个字段
5.通常情况下，关系型数据库中每张表都会有主键

外键（Foreign Key,简称FK）

外键是约束，约束了该列的内容

外键对应了另外表的主键

外键的值，可以为空，如果不为空，就必须是对应主键所在表的主键列表的值

  • 股票基本信息表
股票代码1（PK, PRIMARY KEY） 股票名称 价格
000281 XXX1
000295 XXX3
100254 XXX2
  • 股票购买表
购买日期 股票代码2（FK, FOREIGN KEY） 数量
2017-3-10 000295 1000
2017-3-9 100254 2000
2017-3-8 100254 5000
  • 股票代码1在股票基本信息表是主键
  • 股票代码2在股票购买表是外键
  • 在 股票购买表 中，股票代码2的值，必须是 股票基本信息表 中 股票代码1的值。

外键约束是为数据库中某个与其他表(称作父表)有关系的表(称作子表)而定义的。外键的值必须事先出现在某个特定表的唯一键或者主键中。外键可包含一列或者多列，但是其所参考的键也必须包含相同的列。外键也可以和同一个表的主键相关联。如果没有其他约束限制，外键可以包含NULL值。

  1. 一张表的外键关联字段通常情况下关联的是另外一张表的主键
  2. 一张表的外键关联字段必须填写外键表中存在的数据
  3. 外间关联表的数据被引用了之后，通常不允许删除，如果一定要删除，可以级联删除引用数据
  4. 外键字段允许为空
  5. 外键字段可以是一个或多个

创建表

表(TABLE)是关系型数据库的核心，所有的数据都存储在表中。

创建表的前提

登录数据库的用户必须拥有

  • CREATE TABLE权限
  • 可用的存储空间

创建表的基本语法

CREATE TABLE [schema.]table
        (column datatype [DEFAULT expr][, ...]);

在创建表的时候，必须制定：

  • 表名
  • 字段名（又叫做列名）
  • column：栏位
  • field：字段，列
  • point：点，数据点
  • 字段类型
  • 字段长度等

表名和字段名（列名）

表名和列名:

  • 必须以字母开头（可以为中文，但是不推荐用）
  • 必须在 1–30 个字符之间
  • 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
  • 必须不能和用户定义的其他对象重名
  • 必须不能是 MySQL 的保留字

DEFAULT选项

插入时为一个列指定默认值

... hire_date DATE DEFAULT '2017-03-08', ...

具体要求：

  • 字符串, 表达式, 或SQL 函数都是合法的
  • 其它列的列名和伪列是非法的
  • 默认值必须满足列的数据类型定义

表的确认与查看

SQL> DESC <表名>

或者

SQL> DESCRIBE <表名>

示例：

MariaDB [mysql]> desc db;
+-----------------------+---------------+------ +-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------ +-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(80)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------ +-----+---------+-------+
22 rows in set (0.01 sec)

MariaDB [mysql]>

创建表脚本

-- 推荐的写法
CREATE TABLE 名字(
  字段名 字段类型 是否为空 PRIMARY KEY,
  字段名 字段类型 是否为空 DEFAULT 默认值,
  字段名 字段类型 是否为空 UNIQUE,
  ....
  字段名1 字段类型  /* 外键 */,
  FOREIGN KEY(字段名1) REFERENCES 另一个表表名 (另一个表的主键字段名)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

如果想看最标准（MySQL 官方建议写法），可以使用 SQLyog 导出表的脚本。

创建表示例

CREATE TABLE person 
(
  id CHAR(18),
  name VARCHAR(100),
  sex CHAR(1),
  birthday DATETIME,
  height INT,
  weight INT,
  age INT,
  hometown CHAR(6)
);
-- Table created.

上面的SQL代码执行后便新建了一个数据库的表，表的名字是person，一共有七个字段。

接下来我们可以使用之前的DESC来查看所创建的表的结构。请注意务必在PLSQL Developer的命令窗口执行，或者在操作系统的命令行窗口执行。

SQL> desc person
Name     Type          Nullable Default Comments 
--------------------------------------------
ID       CHAR(18)      Y                         
NAME     VARCHAR(100) Y                         
SEX      CHAR(1)       Y                         
BIRTHDAY DATETIME          Y                         
HEIGHT   INT        Y               （）          
WEIGHT   INT        Y                         
AGE      INT        Y                         
hometown    CHAR(6)       Y

添加表的约束

还记的上一章我们曾经学习过的表的约束么？接下来我们创建表的时候，来添加表的约束，实现数据的完整性。

约束：就是限制

约束 描述
非空约束 值不能为空，一个列默认是可以为空
唯一约束 值不能重复，属于表对象(跟列一样)，必须要有名字，若没有指定名字，则MySQL随即分配一个唯一的名字
主键 相当于身份证号码，包含非空约束和唯一约束，也是属于表对象，在设计一张表示，需要有主键列
校验约束(MySQL 失效) 检查值是否满足某个条件，属于表对象，必须要有名字
外键 也属于表对象，必须要有名字

首先创建一个表：hometown

CREATE TABLE hometown
(
  id CHAR(6) PRIMARY KEY, 
  city VARCHAR2(100) DEFAULT 'shenzhen' NOT NULL, 
  province VARCHAR2(100) DEFAULT 'guangdong' 
);
-- Table created.

可以看到上述表hometown使用了主键约束和非空约束。我们在接下来的修改表结构小节里面来继续讨论约束。

修改表结构

刚刚我们接连创建了两个表，是person和hometown后者在创建的时候我们考虑了约束并且添加了，但是前者在创建的是时候，我们没有增加约束，接下来我们通过修改表结构，使前者也添加约束。

修改表person的字段，使的表满足以下约束条件。

字段名 约束 详细描述
id 主键 定长18位字符 主键约束
name 非空 可变长度100位字符 姓名不允许为空
sex 检查约束和默认值 定长1位字符 检查输入是否为'M'或'F'，默认值是'F' 修改列名为gender
birthday 非空和默认值 日期 生日不允许为空，默认值为系统时间
height 精度为999.9
weight 精度为999.9
hometown 外键 定长6位字符 参考hometown这个表的id字段
age 删除字段，已经与birthday重复了
phone 唯一 定长11位字符 增加字段，保证唯一约束

修改字段

修改表person的字段，使的表满足以下约束条件。

修改字段的语法

-- 修改字段
ALTER TABLE table
MODIFY     (column datatype [DEFAULT expr]
           [, column datatype]...);

-- 添加约束
ALTER TABLE  table
  ADD [CONSTRAINT constraint] type (column);

具体代码如下：

-- id添加主键约束
ALTER TABLE person
ADD constraint person_id_pk primary key (ID);

-- 姓名不允许为空
ALTER TABLE person
ADD constraint person_name_nn check (name IS NOT NULL);
-- 或者这样
alter table PERSON modify name not null;

-- 性别检查约束
ALTER TABLE person
ADD constraint person_sex_chk check (sex = 'M' OR sex = 'F');

-- 性别默认值
ALTER TABLE person
MODIFY sex CHAR(1) DEFAULT 'F';

-- 性别字段名由sex修改为gender
ALTER TABLE person RENAME COLUMN sex TO gender;

-- 生日不允许为空
ALTER TABLE person
 ADD constraint person_birthday_nn check (birthday IS NOT NULL);

-- 生日默认值
ALTER TABLE person
MODIFY birthday DATE DEFAULT NOW();

-- 修改身高和体重的格式，使其满足999.9
ALTER TABLE person
MODIFY(
  height DECIMAL(3,1),
  weight DECIMAL(3,1)
);

接下来是外键约束

-- 添加外键，使得person的hometown字段参考hometown这个表的id字段
ALTER TABLE person
  ADD CONSTRAINT person_hometown_fk FOREIGN KEY (hometown)
  REFERENCES hometown (id);

删除字段

ALTER TABLE table
DROP (column);

具体代码

-- 删除年龄字段
ALTER TABLE person
DROP (age);

-- 或者
ALTER TABLE person
DROP COLUMN age;

添加字段

ALTER TABLE table
ADD        (column datatype [DEFAULT expr]
           [, column datatype]...);

具体代码

-- 添加字段，手机。并设置为唯一约束
ALTER TABLE person
ADD (phone CHAR(11) UNIQUE);

至此，整个person的表就已经按照要求完全实现了。

整个表的一次性创建脚本如下

-- 完整的一次性创建表的脚本
CREATE TABLE person2
(
  ID CHAR(18) PRIMARY KEY,
  NAME VARCHAR(100) NOT NULL,
  SEX CHAR(1)  DEFAULT 'F' CHECK(sex='M' OR sex='F'),
  birthday DATETIME DEFAULT NOW() NOT NULL,
  height DECIMAL(4,1) ,
  weight DECIMAL(4,1),
  hometown CHAR(6) REFERENCES hometown(ID),
  phone CHAR(11) UNIQUE
);

插入记录

-- 插入记录到 Hometown
INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('518000', 'Shenzhen', 'Guangdong');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('100000', 'Beijing', 'Beijing');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('517000', 'Guangzhou', 'Guangdong');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('516000', 'Shanghai', 'Shanghai');

-- 插入表 Person 记录
INSERT INTO PERSON
  (ID, NAME, SEX, BIRTHDAY, HEIGHT, WEIGHT, HOMETOWN, PHONE)
VALUES
  ('123456789012345678',
   'Juliet',
   'F',
   TO_DATE('19900712', 'yyyymmdd'),
   170,
   62,
   '518000',
   '13866666666');

-- 插入表 Person 记录
INSERT INTO PERSON
  (ID, NAME, SEX, BIRTHDAY, HEIGHT, WEIGHT, HOMETOWN, PHONE)
VALUES
  ('123456789012345679',
   'Remeo',
   'M',
   TO_DATE('19920316', 'yyyymmdd'),
   162,
   45,
   '100000',
   '13866666669');

由上述脚本可以看出，由于Person.hometown字段是外键，参考了Hometown.id字段，那么在插入Person记录的时候，必须插入在Hometown.id中已经存在的记录。

另外，务必先插入Hometown记录，再插入Person记录。

移除表结构

DROP TABLE table;

具体代码

-- 删除表person
DROP TABLE person;
-- Table dropped.

表的查询

表的查询是整个数据库的基础，也是我们学习的重点

讲义中使用 HRDB的六个表

初始化数据库

  • 创建数据库

    CREATE DATABASE HRDB;
USE HRDB;

  • 创建数据库的表

  • 区域表 Regions

    字段名 字段类型 注释说明
    region_id smallint(5) 无符号、主键、自增长
    region_name varchar(25) 可以为空 
    DROP TABLE IF EXISTS regions;

CREATE TABLE regions (
  region_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  region_name varchar(25) DEFAULT NULL,
  PRIMARY KEY (region_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 国家表 Countries

    字段名 字段类型 注释说明
    country_id char(2) 主键、非空
    country_name varchar(40)
    region_id smallint(5) 无符号、外键：参考 Regions(region_id) 
    DROP TABLE IF EXISTS countries;

CREATE TABLE countries (
  country_id char(2) NOT NULL DEFAULT '',
  country_name varchar(40) DEFAULT NULL,
  region_id smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (country_id),
  FOREIGN KEY (region_id) REFERENCES regions (region_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 位置表 Locations

    字段名 字段类型 注释说明
    location_id smallint(5) 主键
    street_address varchar(40) 街道
    postal_code varchar(12) 邮编
    city varchar(30) 城市
    state_province varchar(25) 省份
    country_id char(2) 外键，关联 Countries(country_id) 
    DROP TABLE IF EXISTS locations;

CREATE TABLE locations (
  location_id smallint(5) unsigned NOT NULL,
  street_address varchar(40) DEFAULT NULL,
  postal_code varchar(12) DEFAULT NULL,
  city varchar(30) NOT NULL,
  state_province varchar(25) DEFAULT NULL,
  country_id char(2) DEFAULT NULL,
  PRIMARY KEY (location_id),
  FOREIGN KEY (country_id) REFERENCES countries (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 职位表 Jobs

    字段名 字段类型 注释说明
    job_id varchar(10) 主键
    job_title varchar(35) 职位名称
    min_salary int(11) 职位最低工资
    max_salary int(11) 职位最高工资 
    DROP TABLE IF EXISTS jobs;

CREATE TABLE jobs (
  job_id varchar(10) NOT NULL,
  job_title varchar(35) NOT NULL,
  min_salary int(11) DEFAULT NULL,
  max_salary int(11) DEFAULT NULL,
  PRIMARY KEY (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ​

  • 员工表 Employees

    字段名 字段类型 注释说明
    employee_id mediumint(8) 主键
    first_name varchar(20) 名字
    last_name varchar(25) 姓
    email varchar(25)
    phone_number varchar(20)
    hire_date date 入职日期
    salary float(8,2) 工资
    commission_pct float(2,2) 销售提成比
    job_id varchar(10) 外键 jobs (job_id)
    manager_id mediumint(8) 外键 employees (employee_id)
    department_id smallint(5) 外键 departments (department_id) 
    DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id mediumint(8) unsigned NOT NULL,
  first_name varchar(20) DEFAULT NULL,
  last_name varchar(25) DEFAULT NULL,
  email varchar(25) DEFAULT NULL,
  phone_number varchar(20) DEFAULT NULL,
  hire_date date DEFAULT NULL,
  job_id varchar(10) DEFAULT NULL,
  salary float(8,2) DEFAULT '10000.00',
  commission_pct float(2,2) DEFAULT NULL,
  manager_id mediumint(8) unsigned DEFAULT NULL,
  department_id smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (employee_id),
  UNIQUE KEY email (email),
  FOREIGN KEY (manager_id) REFERENCES employees (employee_id),
  FOREIGN KEY (department_id) REFERENCES departments (department_id),
  FOREIGN KEY (job_id) REFERENCES jobs (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ​

  • 部门表 Departments

    字段名 字段类型 注释说明
    department_id smallint(5) 主键
    department_name varchar(30) 部门名称，非空
    manager_id mediumint(8) 外键，参考 employees (employee_id)
    location_id smallint(5) 外键，参考 locations (location_id) 
    DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
  department_id smallint(5) unsigned NOT NULL,
  department_name varchar(30) NOT NULL,
  manager_id mediumint(8) unsigned DEFAULT NULL,
  location_id smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (department_id),
  FOREIGN KEY (manager_id) REFERENCES employees (employee_id),
  FOREIGN KEY (location_id) REFERENCES locations (location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ​

    ​

基本查询

查询的语法

SELECT  *|{[DISTINCT] column|expression [alias],...}
FROM    table;

SQL的查询有以下特点：

  • SQL 语言大小写不敏感。
  • SQL 可以写在一行或者多行
  • 关键字不能被缩写也不能分行
  • 各子句一般要分行写。
  • 使用缩进提高语句的可读性。
-- 简单基础查询
SELECT * FROM HRDB.EMPLOYEES;
SELECT * FROM HRDB.DEPARTMENTS D; /*表的别名 D*/
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL FROM SCOTT.EMP E;-- 表的别名E和查询指定字段
SELECT
    SG.GRADE AS jibie,-- 字段的别名
    SG.LOSAL AS "Low Salary",-- 字段的别名
    SG.HISAL "High Salary"-- 字段的别名
FROM
    SCOTT.SALGRADE SG; 
SELECT ROWNUM,t.*,ROWID FROM SCOTT.EMP t;
  • *代表所有列
  • HRDB是数据库名
  • BONUS、DEPARTMENTS、EMPLOYEES、SALGRADE是表名，代表查询哪个表
  • D、E、SG是表的别名
  • 对每个字段都可以进行重命名，可以使用AS，也可以不用AS。
  • PS：请注意，字段（列）的重命名，是在查询结果产生以后进行的。
  • SELECT后面跟的是字段（列）
  • FROM表名关注的是表，后面只能是表名或者是查询出来的子表
  • ROWNUM ROWID:伪列，系统自动生成的列，只能查询，不能进行增删改操作，ROWNUM表示行号、ROWID表示当前记录数据物理存放位置

条件查询

SELECT  *|{[DISTINCT] column|expression [alias],...}
FROM    table
[WHERE  condition(s)];
  • 条件查询是用WHERE语句进行过滤的，在执行完过滤条件后，查询的结果才会展现出来。
  • WHERE条件关注的是字段（列）
  • 使用WHERE子句，将不符合条件的记录过滤掉。

具体的例子

基本条件查询

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HR.EMPLOYEES E
 WHERE E.EMPLOYEE_ID = 200
-- 查询员工编号为200的员工的 员工编号、姓名、入职日期和工作职位

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID = 200
    OR E.EMPLOYEE_ID = 201;
-- 查询员工编号为200或者201的员工
-- 还可以用：E.EMPLOYEE_ID >= 201   "<>  <= >= > <"

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID IN (200, 201, 202, 205, 208);
-- 查询员工编号为(200, 201, 202, 205, 208)范围内的员工

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID BETWEEN 200 AND 208;
-- 查询员工编号为200到208范围内的员工

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID > 200
   AND E.EMPLOYEE_ID <= 209;
-- 查询员工编号为大于200并且小于等于209范围内的员工

-- 基本条件查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID, E.Salary
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME = 'Jennifer'
-- 查询员工名字为“Jennifer”的员工

NULL空值查询

NULL，读音/nʌl/，表示未知的，空值。

NULL是数据库中特有的数据类型，当一条记录的某个列为NULL，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此，NULL并不是一个确定的值。

这是NULL的由来、也是NULL的基础，所有和NULL相关的操作的结果都可以从NULL的概念推导出来。

判断一个字段是否为NULL，应该用IS NULL或IS NOT NULL，而不能用=，对NULL的任何操作的结果还是NULL。

查询示例

-- 空值查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.MANAGER_ID IS NULL;
-- 查询所有的没有确定经理的员工；

-- 空值查询
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.MANAGER_ID IS NOT NULL;
-- 查询所有的有确定经理的员工；

模糊条件查询

模糊条件查询一般是指字符类型模糊查询，使用LIKE

  • LIKE 后面一定用单引号 ''
  • 查询用到了通配符，选择相似值
  • %代表任意字符（零个或者一个或者多个字符）
  • _代表一个字符
  • 查询字符或者数字
  • %和_可以同时使用
-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like 'J%'
-- 查询员工名字以“J”开头的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like '%on%'
-- 查询员工名字包含“on”的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like '_a%'
-- 查询员工名字以“任意一个字符+a”开头的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID LIKE '_2%'
-- 查询员工编号以“任意一个字符+2”开头的员工

-- 模糊条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID LIKE '__2'
-- 查询员工编号为“XX2”的员工

函数条件查询

字符大小写转换函数
函数 描述 示例
UPPER 将字符串变成全部大写 UPPER('SmitH')='SMITH'
LOWER 将字符串变成全部小写 LOWER('SmitH')='smith'
CONCAT 连接函数 CONCAT('Smith','.', 'LEE')='Smith.LEE' 
-- 函数查询
SELECT UPPER(E.FIRST_NAME) "Upper Name",
       LOWER(E.FIRST_NAME) "Lower Name",
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME) "Full Name"
  FROM HRDB.EMPLOYEES E;
-- 查询所有员工的名字和姓名，并直接相连。

查询示例

-- 函数条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE UPPER(E.First_Name) = 'PETER'; 
-- 查询员工名字大写为“PETER”的员工（peter, Peter, pEter...）

-- 函数条件查询
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE LOWER(E.FIRST_NAME) = 'peter';
-- 查询员工名字大写为“PETER”的员工（peter, Peter, pEter...）
字符控制函数
函数 描述 示例
CONCAT 连接两个字符成为一个新的字符 CONCAT('Hello','World')='HelloWorld'
SUBSTRING 截取字符串中指定的子字符串 SUBSTR('HelloWorld',1,5)='Hello'
LENGTH 获取字符串的长度 LENGTH('HelloWorld')=10
INSTR 查询字符串中指定字符的位置 INSTR('HelloWorld', 'Wor')=6
LPAD 输出指定位数的字符串，将左侧全部补指定字符 LPAD(salary,10,'*')='*****24000'
RPAD 输出指定位数的字符串，将右侧全部补指定字符 RPAD(salary, 10, '*')='24000*****'
TRIM 除去字符串中前后的空格, 也可以去除前后的某个字符 trim(' john ')='john'

查询示例

-- 函数查询
SELECT CONCAT(E.FIRST_NAME, E.LAST_NAME) "Full Name",
       E.JOB_ID,
       TRIM(E.first_name), 
       LENGTH(E.LAST_NAME),
       LPAD(E.SALARY, 11, '*'),
       RPAD(E.SALARY, 12, '$')
  FROM HRDB.EMPLOYEES E
 WHERE SUBSTRING(JOB_ID, 4) = 'REP';
数字处理函数
函数 描述 示例
ROUND 四舍五入并保留指定小数位数 ROUND(45.926, 2)=45.93
TRUNCATE 直接截断舍去并保留指定小数位数 TRUNC(45.926, 2)=45.92
MOD 求余数 MOD(1600, 300)=100

函数示例

SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923,-1) , ROUND(45.923,-2)


SELECT  TRUNCATE(45.923,2), TRUNCATE(45.923,0), TRUNCATE(45.923,-1), TRUNCATE(45.923,-2)

SELECT LAST_NAME, SALARY, MOD(SALARY, 5000)
  FROM HRDB.EMPLOYEES WHERE JOB_ID = 'SA_REP';
-- 查询职位ID是“SA_REP”的员工的工资对5000取余
日期处理函数
函数 描述 示例
NOW() 当前日期 SELECT NOW();
CURTIME() 当前时间 select curtime();
WEEKDAY(date) 返回日期date是星期几(0=星期一,1=星期二,……6= 星期天) select WEEKDAY(NOW());
MONTHNAME(date) 返回日期的月份名 select MONTHNAME(NOW());
DATE_FORMAT(date,format) 输出指定格式的日期 select DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
DATE_ADD 日期增加函数 SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND);')
DATE_ADD 增加一天 SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY);
DATE_ADD 后退一天10小时 SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR);

查询优先级

优先级 查询方式
1 算术运算符 /,*,+,-
2 连接符 ||
3 比较符 >,>=,=,<,<=
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 NOT
7 AND
8 OR

可以用括号()来改变查询的优先级

SELECT LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID = 'SA_REP'
    OR JOB_ID = 'AD_PRES'
   AND SALARY > 15000;
-- 查询所有工作ID是“SA_REP”或者工作ID是“AD_PRES”并且工资高于15000

SELECT LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE (JOB_ID = 'SA_REP' OR JOB_ID = 'AD_PRES')
   AND SALARY > 15000;
-- 查询所有工作ID是“SA_REP”或“AD_PRES”并且工资高于15000

排序

使用ORDER BY字句进行排序操作。

  • ASC是升序， ascend，如果省略ASC，一般数据库也是默认升序。
  • DESC是降序, descend
  • ORDER BY字句用在SELECT语句的结尾

排序语法

SELECT     *|{[DISTINCT] column|expression [alias],...}
FROM       table
[WHERE     condition(s)]
[ORDER BY  {column, expr, alias} [ASC|DESC]];

默认排序

一般的关系型数据库中，默认排序是升序排序。就是在排序的ORDER BY子句中不添加ASC或者DESC。

查询示例

-- 排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, E.HIRE_DATE
  FROM HRDB.EMPLOYEES E
 ORDER BY E.HIRE_DATE;
-- 默认排序，查询所有的员工，并按照入职时间从小到大升序排序
-- ORDER BY E.HIRE_DATE 等同于 ORDER BY E.HIRE_DATE ASC

-- 排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, E.HIRE_DATE
  FROM HRDB.EMPLOYEES E
 ORDER BY E.HIRE_DATE DESC;
-- 默认排序，查询所有的员工，并按照入职时间从大到小降序排序

字段别名排序

字段的别名排序，是将字段重新命名以后，按照新的名称进行排序。

重点理解：排序的动作，是在查询表已经呈现以后，并且若有字段重命名，那么排序也是在重命名之后。

-- 按照别名排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY * 12 ANNUAL_SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.SALARY > 5000
 ORDER BY ANNUAL_SALARY ASC;
-- 查询月薪高于5000的员工，并按照年薪从小到大升序排序
-- ANNUAL_SALARY 不在 HRDB.EMPLOYEES
-- 先筛选where
-- 检查 ANNUAL_SALARY 在不在 HRDB.EMPLOYEES
    -- 如果在， 就直接排序，然后筛选结果
    -- 如果不在，就先展示结果，看结果中有无 ANNUAL_SALARY， 如果有，就再排序

多字段排序

多个字段的排序是支持的。排序的效果是按照字段的优先级进行，若当前字段无法分出高低，则依靠后续的字段进行排序确认。

-- 多个字段排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY
  FROM HRDB.EMPLOYEES E
 ORDER BY E.LAST_NAME ASC, E.JOB_ID DESC, E.SALARY DESC;
-- 查询员工的信息，按照员工名字升序，工作职位降序，工资降序依次进行排序

最后确认一点，排序是可以使用不在SELECT 列表中的列进行排序的。

-- 多个字段排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY
  FROM HRDB.EMPLOYEES E
 ORDER BY E.LAST_NAME ASC, E.JOB_ID DESC, E.SALARY DESC;
-- 查询员工的信息，按照员工名字升序，工作职位降序，工资降序依次进行排序
-- JOB_ID 不在 SELECT 后面

排序总结

  • order by 字段/表达式 asc/desc

  • where vs order by

    1. 先执行 where，筛选出合适的数据
    2. 再执行 order by，按照指定的列进行排序

  • from vs order by

    1. 只要是排序的列在 from 后面的表中，就可以按该列排序
    2. 排序和 select 后面的列无关

  • select vs order by

    1. select 只能在 order by 出来结果后，选择显示指定的select 后面的列
    2. order by 后面的列不在 from 的表里面，那么必须在 select 的别名中。

  • order by 可以跟 多个列

    1. 先按前面的列进行排序，无法决定结果的时候，再考虑后面的列
    2. 每个列之间，用逗号","隔开
    3. 每个列后面，必须加上 asc/desc，不要省略
    4. 排序的依据还可以是列的表达式（算术）

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

查询语法

SELECT  [column,] group_function(column), ...
FROM        table
[WHERE  condition]
[GROUP BY   column]
[ORDER BY   column];
-- group_function()是分组函数方法
函数 描述 示例
AVG 求平均 AVG(SALARY)
COUNT 求数量 COUNT(SALARY), COUNT(*)
MAX 求最大值 MAX(SALARY)
MIN 求最小值 MIN(SALARY)
SUM 求和 AVG(SUM)

其中：COUNT(SALARY)是求SALARY不为NULL的记录数，COUNT(*)是求记录总数

分组函数忽略空值NULL

查询示例

-- 分组函数查询
SELECT AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID LIKE '%REP%';
-- 可以对数值型数据记录使用 AVG 和 SUM 函数进行求平均值和汇总等操作
-- 可以对任意数据类型的数据使用 MIN 和 MAX 函数。

GROUP BY子句的使用

-- 分组查询
SELECT E.DEPARTMENT_ID, MIN(E.HIRE_DATE), MAX(E.HIRE_DATE)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.DEPARTMENT_ID;
-- 对日期类型进行 MAX 和 MIN 函数操作

-- 分组函数查询
SELECT E.JOB_ID, AVG(E.SALARY), MAX(E.SALARY), MIN(E.SALARY), SUM(E.SALARY)
  FROM HRDB.EMPLOYEES E
 WHERE E.JOB_ID LIKE '%REP%'
 GROUP BY E.JOB_ID;
-- 按照职位包含“REP”的员工的职位进行统计，统计每个组的平均工资、最高工资、最低工资和工资总和。

-- 分组函数查询
SELECT E.DEPARTMENT_ID, COUNT(*), COUNT(E.EMPLOYEE_ID), COUNT(E.MANAGER_ID)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.DEPARTMENT_ID
-- COUNT是计数函数。COUNT(*) 返回表中记录总数，COUNT(字段)返回的是当前组里面不是NULL的记录数

综合示例

--  请查询(工资高于8000)员工信息，EMPLOYEES
--  按照部门、职位输出：每个部门+职位，有几个员工
--  职位，部门，员工数，最低工资，最高工资，平均工资，工资总和
SELECT 
  e.department_id,
  e.job_id,
  COUNT(*) total_count,
  MIN(e.salary) min_salary,
  MAX(e.salary) max_salary,
  AVG(e.salary) avg_salary,
  SUM(e.salary) sum_salary 
FROM
  hrdb.employees e 
WHERE e.salary > 8000 
GROUP BY e.department_id,
  e.job_id 
ORDER BY avg_salary DESC ;

-- 1. 先筛选工资高于8000的 员工
-- 2. 对筛选出来的员工进行分组
-- 3. 同时按照 department_id 和 job_id 进行分组
-- 4. 分组后，对分组的结果进行统计
-- 5. 重命名分组的结果（列）
-- 6. 按照分组后的 avg_salary 列进行排序

查询结果：

department_id  job_id      total_count  min_salary  max_salary    avg_salary  sum_salary  
-------------  ----------  -----------  ----------  ----------  ------------  ------------
           90  AD_PRES               1    24000.00    24000.00  24000.000000      24000.00
           90  AD_VP                 2    17000.00    17000.00  17000.000000      34000.00
           20  MK_MAN                1    13000.00    13000.00  13000.000000      13000.00
           80  SA_MAN                5    10500.00    14000.00  12200.000000      61000.00
          110  AC_MGR                1    12000.00    12000.00  12000.000000      12000.00
          100  FI_MGR                1    12000.00    12000.00  12000.000000      12000.00
           30  PU_MAN                1    11000.00    11000.00  11000.000000      11000.00
           70  PR_REP                1    10000.00    10000.00  10000.000000      10000.00
           80  SA_REP               15     8400.00    11500.00   9660.000000     144900.00
           60  IT_PROG               1     9000.00     9000.00   9000.000000       9000.00
          100  FI_ACCOUNT            2     8200.00     9000.00   8600.000000      17200.00
          110  AC_ACCOUNT            1     8300.00     8300.00   8300.000000       8300.00
           50  ST_MAN                1     8200.00     8200.00   8200.000000       8200.00

分组总结

  • 分组的目的，作用：统计
  • 分组后，结果还是一个表，表里面有：
    1. 共同的列（分组依据）
    2. 统计的列
  • 每组记录的个数
    1. COUNT(*)：* 代表一条记录
    2. COUNT(列): 列：代表不是null的列
  • 每组记录某些列（数值）的统计
    1. 用统计函数（聚合函数）
    2. 求和：SUM()
    3. 求平均：AVG()
    4. 求最大值: MAX()
    5. 求最小值: MIN()
      GROUP BY 列（多个列，用","分开，表达式）

DISTINCT关键字

DISTINCT是去除重复记录。

-- DISTINCT关键字
SELECT DISTINCT e.job_id
FROM hr.employees e;

DISTINCT后面如果加了多列，那么会剔除多列共同重复的记录

SELECT DISTINCT 
  d.location_id,
  d.manager_id 
FROM
  hrdb.departments d ;
-- 去除 location_id 和 manager_id 一起重复的记录

查询结果

location_id  manager_id  
-----------  ------------
       1700           200
       1800           201
       1700           114
       2400           203
       1500           121
       1400           103
       2700           204
       2500           145
       1700           100
       1700           108
       1700           205
       1700        (NULL)

DISTINCT关键字也可以使用在分组函数中。

COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数

查询示例

-- DISTINCT关键字
SELECT COUNT(DISTINCT E.SALARY), COUNT(E.SALARY)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.JOB_ID;
-- DISTINCT去除了每个组中重复的工资数目计数

多个字段分组

与之前的ORDER BY子句一样，GROUP BY子句也是可以多个字段进行的。

-- 多字段分组
SELECT E.JOB_ID, E.DEPARTMENT_ID, COUNT(DISTINCT E.SALARY), COUNT(E.SALARY)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.JOB_ID, E.DEPARTMENT_ID;
-- 所有的数据将会以(JOB_ID + DEPARTMENT_ID)作为依据分组，统计每一个组的计数
-- 第四行中：E.JOB_ID 和 E.DEPARTMENT_ID 可以互换位置，不影响结果

过滤分组 HAVING

HAVING 是在 GROUP BY 之后进行过滤，对分组的结果进行过滤

使用 HAVING 过滤分组:

  1. 行已经被分组。
  2. 使用了组函数。
  3. 满足 HAVING 子句中条件的分组将被显示。
  4. 不满足 HAVING 子句中条件的分组，将不会显示

查询语法

SELECT  column, group_function
FROM        table
[WHERE  condition]
[GROUP BY   group_by_expression]
[HAVING group_condition]
[ORDER BY   column];

查询示例

-- 分组后进行过滤
 SELECT DEPARTMENT_ID, MAX(SALARY)
   FROM HRDB.EMPLOYEES
  GROUP BY DEPARTMENT_ID
 HAVING MAX(SALARY) > 10000;
-- 分组后，过滤最大工资。筛选最大工资高于10000的数据进行查询

与WHERE子句一起使用

-- 分组后进行过滤
SELECT JOB_ID, SUM(SALARY) PAYROLL
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID NOT LIKE '%REP%'
 GROUP BY JOB_ID
HAVING SUM(SALARY) > 13000
 ORDER BY SUM(SALARY);
-- 先筛选出合适的记录进行分组统计，统计以后再次对分组统计好的数据进行筛选，然后排序

使用之前的例子进行查询

--  请查询(工资高于8000)员工信息，EMPLOYEES
--  按照部门、职位输出：(平均工资不低于10000)每个部门+职位，有几个员工
--  职位，部门，员工数，最低工资，最高工资，平均工资，工资总和
SELECT 
  e.department_id,
  e.job_id,
  COUNT(*) total_count,
  MIN(e.salary) min_salary,
  MAX(e.salary) max_salary,
  AVG(e.salary) avg_salary,
  SUM(e.salary) sum_salary 
FROM
  hrdb.employees e 
WHERE e.salary > 8000 
GROUP BY e.department_id,
  e.job_id 
HAVING avg_salary >= 10000 
ORDER BY avg_salary DESC ;

-- 1. 先筛选工资高于8000的 员工
-- 2. 对筛选出来的员工进行分组
-- 3. 同时按照 department_id 和 job_id 进行分组
-- 4. 分组后，对分组的结果进行统计
-- 5. 用 HAVING 对统计的结果进行过滤
-- 6. 重命名分组的结果（列）
-- 7. 按照分组后的 avg_salary 列进行排序

查询结果

department_id  job_id   total_count  min_salary  max_salary    avg_salary  sum_salary  
-------------  -------  -----------  ----------  ----------  ------------  ------------
           90  AD_PRES            1    24000.00    24000.00  24000.000000      24000.00
           90  AD_VP              2    17000.00    17000.00  17000.000000      34000.00
           20  MK_MAN             1    13000.00    13000.00  13000.000000      13000.00
           80  SA_MAN             5    10500.00    14000.00  12200.000000      61000.00
          110  AC_MGR             1    12000.00    12000.00  12000.000000      12000.00
          100  FI_MGR             1    12000.00    12000.00  12000.000000      12000.00
           30  PU_MAN             1    11000.00    11000.00  11000.000000      11000.00
           70  PR_REP             1    10000.00    10000.00  10000.000000      10000.00

子查询

  • 子查询作为新查询的数据表（常见）

查询语法

SELECT  select_list
FROM    (SELECT select_list
               FROM     table) s
WHERE   expr operator;

查询示例

SELECT 
  s.job_id,
  s.ct,
  s.ay 
FROM
  (
  SELECT 
    e.job_id,
    COUNT(*) ct,
    AVG(e.salary) ay 
  FROM
    hrdb.employees e 
  WHERE e.salary > 10000 
  GROUP BY e.job_id 
  HAVING COUNT(*) > 2
  ) s 
WHERE s.ay > 11000 ;
-- s表，是一个查询语句
-- s表有三个字段，job_id, ct, ay.
--  select xx from s where xxx

在当前的查询结果中，做进一步的查询

  • 子查询作为新查询的条件

查询语法

SELECT  select_list
FROM    table
WHERE   expr operator
            (SELECT select_list
               FROM     table);

  • 子查询要包含在括号内。

  • 将子查询放在比较条件的右侧。

  • 一般情况下不要在子查询中使用ORDER BY 子句。

  • 单行操作符对应单行子查询，多行操作符对应多行子查询。

    查询示例

查询示例

-- 子查询
SELECT LAST_NAME, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY > (SELECT SALARY FROM HRDB.EMPLOYEES WHERE LAST_NAME = 'Abel');
-- 查询工资高于“Abel”的所有员工的名字和工资
-- 这条语句有风险，如果雇员中有多个人的 LAST_NAME 是 Abel，这条语句执行可能失败

  • ALL：全部，一般指最大值

    SELECT 
  LAST_NAME, SALARY 
FROM
  hrdb.employees e 
WHERE e.salary > ALL 
  (SELECT 
    e.salary 
  FROM
    hrdb.employees e 
  WHERE e.last_name = 'Cambrault') ;
-- 查询工资高于（全部Last_name是“Cambrault”雇员工资）的所有员工的名字和工资

    ​

  • ANY：任何一个，一般指最小值

    SELECT 
  LAST_NAME, SALARY 
FROM
  hrdb.employees e 
WHERE e.salary > ANY 
  (SELECT 
    e.salary 
  FROM
    hrdb.employees e 
  WHERE e.last_name = 'Cambrault') ;
-- 查询工资高于（任意Last_name是“Cambrault”雇员工资）的所有员工的名字和工资

  • 精确子查询，需要用主键（PRIMARY KEY）

-- 子查询
 SELECT LAST_NAME, JOB_ID, SALARY
   FROM HRDB.EMPLOYEES
  WHERE JOB_ID = (SELECT JOB_ID FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 141)
    AND SALARY > (SELECT SALARY FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 143);
/*查询职位和员工编号141相同，并且工资高于员工编号143的所有员工的姓，工作编号和工资*/

HAVING子句子查询

-- HAVING子句子查询
 SELECT DEPARTMENT_ID, MIN(SALARY)
   FROM HRDB.EMPLOYEES
  GROUP BY DEPARTMENT_ID
 HAVING MIN(SALARY) > (SELECT MIN(SALARY)
                         FROM HRDB.EMPLOYEES
                        WHERE DEPARTMENT_ID = 50);
-- 统计以后再次对分组的数据进行筛选，筛选条件基于一个子查询的结果

ANY & ALL 子查询

-- 子查询
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY < ANY
 (SELECT SALARY FROM HRDB.EMPLOYEES WHERE JOB_ID = 'IT_PROG')
   AND JOB_ID <> 'IT_PROG';
-- any，任何一个,查出非IT_PROG职位的员工中薪水少于IT_PROG任一员工薪水的信息,小于最高工资

-- 子查询
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY < ALL
 (SELECT SALARY FROM HRDB.EMPLOYEES WHERE JOB_ID = 'IT_PROG')
   AND JOB_ID <> 'IT_PROG';
-- all，全部，查出少于IT_PROG职位最低薪水的员工信息

EXISTS操作符

EXISTS操作符检查在子查询中是否存在满足条件的行

  • 如果在子查询中存在满足条件的行:
  • 不在子查询中继续查找
  • 条件返回TRUE
  • 如果在子查询中不存在满足条件的行:
  • 条件返回FALSE
  • 继续在子查询中查找
-- 子查询
select 'True' from dual WHERE EXISTS (SELECT '44'
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = 101);

-- 下面的思考一下原理
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID
  FROM HRDB.EMPLOYEES  E
  WHERE EXISTS (SELECT NULL
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = E.EMPLOYEE_ID);
-- 找出员工表中职位是经理的员工，实际上只要子查询中有数据返回则Where条件成立
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, (select job_title from HRDB.jobs j where E.job_id=j.job_id) "job_title",DEPARTMENT_ID
  FROM HRDB.EMPLOYEES E 
  WHERE EXISTS (SELECT NULL
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = E.EMPLOYEE_ID);
-- 检验一下看是不是都是经理职位，这样改一改会更容易理解

-- 子查询
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE NOT EXISTS
 (SELECT 'X' FROM HRDB.EMPLOYEES WHERE DEPARTMENT_ID = D.DEPARTMENT_ID);
-- 找出部门表中没有员工的部门，实际上只要子查询中无数据返回则where条件成立

多表查询

笛卡尔积

笛卡尔积是集合的一种，假设A和B都是集合，A和B的笛卡尔积用A X B来表示，是所有有序偶（a,b）的集合，其中a属于A，b属于B。

SELECT COUNT(*) FROM HRDB.EMPLOYEES E;      -- 107行数据
SELECT COUNT(*) FROM HRDB.DEPARTMENTS D;    -- 27行数据
-- 笛卡儿积
SELECT * FROM HRDB.EMPLOYEES E, HR.DEPARTMENTS D;   -- 107 * 27 = 2889行数据
-- 记录数是两个表的记录数相乘
-- 所有表中的所有行互相连接

内连接 INNER JOIN

连接语法

-- 两种语法：第一种比较古老
SELECT  table1.column, table2.column,...
FROM    table1, table2
WHERE   table1.column1 = table2.column2
AND     table.column1 = 'value';

SELECT       table1.column, table2.column,...
FROM         table1 
INNER JOIN   table2
ON           table1.column1 = table2.column2
WHERE        table1.column1 = 'value';

  • 使用连接在多个表中查询数据。

  • 在 WHERE 子句中写入连接条件。

  • 在表中有相同列时，在列名之前加上表名前缀。

    ​

    -- 多表连接查询
SELECT 
    E.EMPLOYEE_ID,  
    CONCAT(E.FIRST_NAME, ' ', E.LAST_NAME) AS "employee_name",
    E.SALARY,
    E.DEPARTMENT_ID DEPARTMENT_ID1,
    D.DEPARTMENT_ID DEPARTMENT_ID2,
    D.DEPARTMENT_NAME
FROM HRDB.EMPLOYEES E, HRDB.DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询每个员工的员工编号、姓名、工资、和部门编号、部门名称

    ​

  • 使用表名前缀在多个表中区分相同的列。

  • 使用表名可以提高效率。

  • 在不同表中具有相同列名的列可以用别名加以区分。

    -- 非等值内连接
SELECT concat(E.FIRST_NAME, E.LAST_NAME), E.SALARY, J.JOB_TITLE
  FROM HRDB.EMPLOYEES E, HRDB.JOBS J
 WHERE E.JOB_ID = J.JOB_ID
   AND E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
-- 查询工资符合职位工资区间的员工的姓名、工资和职位

    ​

查询语法2

SELECT       table1.column, table2.column,...
FROM         table1 
INNER JOIN   table2
ON           table1.column1 = table2.column2
WHERE        table1.column1 = 'value';

查询示例

-- 多表连接查询
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.' , E.LAST_NAME) AS "employee_name",
       E.SALARY,
       E.DEPARTMENT_ID DEPARTMENT_ID1,
       D.DEPARTMENT_ID DEPARTMENT_ID2,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 INNER JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询每个员工的员工编号、姓名、工资、和部门编号、部门名称

-- 非等值内连接
SELECT concat(E.FIRST_NAME, '.', E.LAST_NAME), E.SALARY, J.JOB_TITLE
  FROM HRDB.EMPLOYEES E
INNER JOIN HRDB.JOBS J
  ON E.JOB_ID = J.JOB_ID
   AND E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
-- 查询工资符合职位工资区间的员工的姓名、工资和职位

  • 查询示例1

    -- 查询每个国家的编号、名字和所在的大洲（区域表）
SELECT 
  c.country_id,
  c.country_name,
  r.region_name 
FROM
  hrdb.countries c 
  INNER JOIN hrdb.regions r 
    ON c.region_id = r.region_id ;

  • 查询示例2

    -- 查询每个员工的员工编号、姓名、工资、和部门编号、部门名称
SELECT 
  e.employee_id,
  e.first_name,
  e.salary,
  e.department_id,
  d.department_name 
FROM
  hrdb.employees e 
  INNER JOIN hrdb.departments d 
    ON e.department_id = d.department_id ;
--  从员工表（部门编号）查员工的部门名称（在部门表中）

  • 查询示例3

    -- 查询每个员工的员工编号、姓名、工资、和职位编号、职位名称  
SELECT 
  e.employee_id,
  e.first_name,
  e.salary,
  e.job_id,
  j.job_title 
FROM
  hrdb.employees e 
  INNER JOIN hrdb.jobs j 
    ON e.job_id = j.job_id ;

    ​

  • 查询示例4

    -- 查询每个部门的名字和部门经理的名字
SELECT 
  d.department_name,
  m.first_name 
FROM
  hrdb.departments d 
  INNER JOIN hrdb.employees m 
    ON d.manager_id = m.employee_id ;

    ​

  • 查询示例5

    -- 查询每个员工的名字和员工经理的名字
SELECT 
  e.first_name employee_name,
  m.first_name manager_name
FROM
  hrdb.employees e 
  INNER JOIN hrdb.employees m 
    ON e.manager_id = m.employee_id ;

    ​

    查询示例
    数据库查询示例.png

外连接

外连接包括：左连接，右连接和全连接。

  • 内连接只返回满足连接条件的数据。
  • 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外联接。
  • 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为全 外联接。
左连接

左连接是以左边的表为基础，左边的表符合过滤条件的都筛选出来，右边的表若能匹配的记录，直接匹配，否则补NULL。

查询语法

SELECT  table1.column, table2.column,...
FROM    table1
LEFT JOIN table2
ON  table1.column = table2.column;

查询示例

-- 外连接查询，左连接
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
  LEFT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的员工。

  • ORACLE支持老的语法

    -- ORACLE左连接
SELECT    table1.column, table2.column,...
FROM  table1, table2
WHERE table1.column1 = table2.column2(+)
AND     table.column1 = 'value';

    ​

右连接

查询语法

SELECT  table1.column, table2.column,...
FROM    table1
RIGHT JOIN table2
ON  table1.column = table2.column;

查询示例

-- 外连接查询，右连接
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 RIGHT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的部门。

  • ORACLE支持老的语法

    -- ORACLE左连接
SELECT    table1.column, table2.column,...
FROM  table1, table2
WHERE table1.column1(+) = table2.column2
AND     table.column1 = 'value';

    ​

全连接

查询语法

SELECT  table1.column, table2.column,...
FROM    table1
FULL JOIN table2
ON  table1.column = table2.column;

MySQL 没有全连接的用法，需要用UNION把左右连接联合起来。

UNION 连接了两段完整的 SELECT 语句，会去掉重复记录

  • UNION 查询比 UNION ALL 慢

UNION ALL 连接了两段完整的 SELECT 语句，不会去掉重复记录

查询示例

-- 外连接查询，全连接
SELECT E.EMPLOYEE_ID,
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 LEFT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
UNION
SELECT E.EMPLOYEE_ID,
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 RIGHT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- oracle的写法：
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
  FULL JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的员工和部门。

多表查询总结

  • 内连接

    是否显示 左边表 右边表
    Y 有 有
    N 有 无
    N 无 有

  • 左连接

    是否显示 左边表 右边表
    Y 有 有
    Y 有 无（补NULL）
    N 无 有

  • 右连接

    是否显示 左边表 右边表
    Y 有 有
    N 有 无
    Y 无（补NULL） 有

  • 全连接

    是否显示 左边表 右边表
    Y 有 有
    Y 有 无（补NULL）
    Y 无（补NULL） 有

Top-N分析

Top-N 分析查询一个列中最大或最小的 n 个值:

  • 销售量最高的十种产品是什么?
  • 销售量最差的十种产品是什么?

最大和最小的值的集合是 Top-N 分析所关心的

查询最大的几个值的 Top-N 分析:

查询语法

SELECT [column_list] 
FROM   table
WHERE  column...
order by column
limit M, N; -- 从第M+1行开始，获取N行数据

查询示例

SELECT last_name,salary 
FROM HRDB.employees
ORDER BY salary DESC
limit 0,10;
-- 查询工资最高的十名员工

SELECT last_name,salary 
FROM HRDB.employees
ORDER BY salary DESC
limit 3,1;
-- 查询工资第3+1高的员工

Top-N分析 Oracle

Top-N 分析查询一个列中最大或最小的 n 个值:

  • 销售量最高的十种产品是什么?
  • 销售量最差的十种产品是什么?

最大和最小的值的集合是 Top-N 分析所关心的

查询最大的几个值的 Top-N 分析:

查询语法

SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;

查询示例

SELECT ROWNUM as RANK, last_name, salary 
FROM  (SELECT last_name,salary FROM HR.employees
       ORDER BY salary DESC)
WHERE ROWNUM <= 3;
--查询工资最高的三名员工

ROWNUM的使用必须遵循以下特性：

  • ROWNUM务必从第一个开始，若没有第一个，就没有后续的
  • ROWNUM务必连续使用。若中间断开，则段开以后的部分将不会被选择
  • ROWNUM将于查询记录形成的同时一起产生。ROWNUM的产生在ORDER BY子句以前。
SELECT RANK, LAST_NAME, SALARY
  FROM (SELECT ROWNUM AS RANK, LAST_NAME, SALARY
          FROM (SELECT LAST_NAME, SALARY
                  FROM HR.EMPLOYEES
                 ORDER BY SALARY DESC))
 WHERE RANK = 3;
--查询工资第三高的员工

分组统计及Top分析综合示例：

​ 示例一：某学校系统中的“学分表Records”包含：学号SID、课程CID、分数SCORE三个字段，请查询出总分排名前三的学生的学号及总分

创建表及插入数据

>mysql-u root-p
mysql>source XXX.sql

分析：

1、由于需要按照学号统计总分，因此首先要按照学号分组对分数求和,并按照分数倒序排列，如下：

SELECT r.SID, SUM(r.Score) as SS  FROM HRDB.Records r  GROUP BY r.SID

2、从上面的查询结果中做二次查询，以找到排名前三的学员，最终SQL如下：

SELECT r.SID, SUM(r.Score) as SS  
FROM HRDB.Records r  
GROUP BY r.SID 
order by SS desc
limit 0,3

示例二：同上表，请查询出每门课程得到最高分的学生的学号和课程号、分数

分析：1、由于要按照课程来统计出最高分，因此可以先用分组查询语句查询出每门课程的最高分

SELECT r.CID, MAX(r.Score) MS  FROM Records r  GROUP BY r.CID

2、上述的查询结果可以作为一张临时表

3、上述临时表再关联Records表，从Records表中查询出和最高分数相等的学生的相关信息

-- 方法1,使用inner join
SELECT r.SID, r.CID, r.Score 
FROM Records r
INNER JOIN 
(SELECT r.CID, MAX(r.Score) MS
  FROM HRDB.Records r
  GROUP BY r.CID
) s
ON r.CID = s.CID
AND r.Score = s.MS;

-- 方法2 直接用where联表
select  r.SID, r.CID, r.Score 
FROM HRDB.Records r,
(SELECT r.CID, MAX(r.Score) MS
  FROM HRDB.Records r
  GROUP BY r.CID
) s
where r.CID = s.CID and r.Score = s.MS

实用查询示例

接下来的部分是一些查询练习

-- 同姓的员工计数
SELECT LAST_NAME, COUNT(LAST_NAME)
  FROM HRDB.EMPLOYEES
 GROUP BY LAST_NAME
HAVING COUNT(LAST_NAME) > 1;

-- 同名的员工计数
SELECT FIRST_NAME, COUNT(FIRST_NAME)
  FROM HRDB.EMPLOYEES
 GROUP BY FIRST_NAME
HAVING COUNT(FIRST_NAME) > 1;

-- 列出至少有一个雇员的所有部门名称
-- 方法一
 SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID IN
       (SELECT distinct E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E)
 ORDER BY DEPARTMENT_NAME ASC;
-- 方法二
SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID IN
       (SELECT E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E
         GROUP BY E.DEPARTMENT_ID
        HAVING COUNT(DEPARTMENT_ID) > 0)
 ORDER BY DEPARTMENT_NAME ASC;

-- 列出没有任何雇员的部门名称
-- 方法一
 SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID NOT IN
       (SELECT distinct E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E WHERE E.DEPARTMENT_ID IS NOT NULL)
 ORDER BY DEPARTMENT_ID ASC;
-- 方法二
SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID NOT IN
       (SELECT E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E
          WHERE E.DEPARTMENT_ID IS NOT NULL
         GROUP BY E.DEPARTMENT_ID
        HAVING COUNT(DEPARTMENT_ID) > 0)
 ORDER BY DEPARTMENT_NAME ASC;

-- 列出工资大于5000的所有雇员的员工编号，姓名和 其经理的姓名
-- 方法一
SELECT  E.EMPLOYEE_ID,E.FIRST_NAME,E.LAST_NAME,E.SALARY,      
        M.FIRST_NAME,M.LAST_NAME
FROM HRDB.EMPLOYEES E
LEFT JOIN HRDB.EMPLOYEES M
    ON E.MANAGER_ID = M.EMPLOYEE_ID
 WHERE E.SALARY > 5000
 ORDER BY E.SALARY DESC;

-- 方法二
SELECT E1.EMPLOYEE_ID, E1.FIRST_NAME,
       E1.LAST_NAME, E1.SALARY,
       M.FIRST_NAME, M.LAST_NAME
  FROM (SELECT E.EMPLOYEE_ID,
               E.FIRST_NAME,
               E.LAST_NAME,
               E.SALARY,
               E.MANAGER_ID
          FROM HRDB.EMPLOYEES E
         WHERE E.SALARY > 5000) E1
  LEFT JOIN HRDB.EMPLOYEES M
    ON E1.MANAGER_ID = M.EMPLOYEE_ID
 ORDER BY E1.SALARY DESC;

-- 列出所有雇员的职位名称，要求职位名称不重复(列出所有雇员职位名称的种类)
-- 方法一
 SELECT DISTINCT J.JOB_TITLE
 FROM HRDB.EMPLOYEES E, HRDB.JOBS J
 WHERE E.JOB_ID = J.JOB_ID
-- 方法二
SELECT DISTINCT J.JOB_TITLE
  FROM HRDB.EMPLOYEES E
 INNER JOIN HRDB.JOBS J
    ON J.JOB_ID = E.JOB_ID;

-- 列出平均工资最高的部门的经理的所有信息
SELECT ROWNUM, S.*, D.*, E2.*
  FROM (SELECT E.DEPARTMENT_ID, ROUND(AVG(E.SALARY), 2)
          FROM HRDB.EMPLOYEES E
         GROUP BY E.DEPARTMENT_ID
         ORDER BY AVG(E.SALARY) DESC) S
 INNER JOIN HRDB.DEPARTMENTS D
    ON D.DEPARTMENT_ID = S.DEPARTMENT_ID
 INNER JOIN HRDB.EMPLOYEES E2
    ON E2.EMPLOYEE_ID = D.MANAGER_ID
 WHERE ROWNUM = 1
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,126评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,254评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,445评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,185评论 1赞 278
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,178评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,970评论 1赞 284
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,276评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,927评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,400评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,883评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,997评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,646评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,213评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,204评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,423评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,423评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,722评论 2赞 345

推荐阅读更多精彩内容