摘自 《SQL 必知必会》
1. 了解 SQL
1.1 数据库基础
- 数据库
database：保存有组织的数据的容器。数据库软件通常称为数据库管理系统DBMS
数据库是通过
DBMS创建和操纵的容器
- 表
table：某种特定类型数据的结构化清单 - 模式
schema：用来描述数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息
模式可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系）
- 列
column：表中的一个字段。表由列组成，列存储表中的某部分信息 - 数据类型
datatype：定义了列可以存储那些数据种类 - 行
row：表中的一个记录。表中的数据是按行存储的，每条记录存储在自己的行内
也可称为数据库记录
record
- 主键
primary key：一列（或一组列），其值能够唯一标识表中每一行
表中的任何列只要满足以下条件，都可以作为主键：
- 任意一行的主键值唯一
- 主键列不允许
NULL值- 主键列中的值不允许修改或更新
- 主键值不能重用，即被删除行的主键值不能赋给以后的新行
1.2 什么是 SQL
SQL 即结构化查询语言（Structured Query Language）。与常见的过程式编程语言相比，SQL 更加类似于一种声明式语言。
标准 SQL 由 ANSI 标准委员会管理，从而称为ANSI SQL。所有主要的 DBMS ，即使有自己的扩展，也都支持ANSI SQL。
各个 DBMS 关于 SQL 的实现有自己的名称，例如 Oracle 的
PL/SQL、微软的Transact-SQL等
1.3 注释风格
SQL 支持以下三种注释风格：
# 注释
SELECT *
FROM mytable; -- 注释
/* 注释 1
注释 2 */
1.4 样例数据库
摘自 Sams Teach Yourself SQL in 10 Minutes (Fourth Edition) | Ben Forta
创建数据库
首先创建数据库：
CREATE DATABASE sql_10mins;
USE sql_10mins;
之后创建以下五张样例表：
Vendors 表
Vendors表存储供应商信息，每个供应商有唯一的vendor_id作为主键，用于匹配产品与供应商：
-- --------------------
-- Create Vendors table
-- --------------------
CREATE TABLE Vendors
(
vend_id char(10) NOT NULL, -- 供应商 ID，主键
vend_name char(50) NOT NULL, -- 供应商名
vend_address char(50) NULL, -- 供应商地址
vend_city char(50) NULL, -- 供应商所在城市
vend_state char(5) NULL, -- 供应商所在州
vend_zip char(10) NULL, -- 供应商邮编
vend_country char(50) NULL -- 供应商所在国家
);
Products 表
Products表包含产品目录，每行一个产品。每个产品有唯一的prod_id作为主键，并借助vend_id作为外键与Vendors表相关联：
-- ---------------------
-- Create Products table
-- ---------------------
CREATE TABLE Products
(
prod_id char(10) NOT NULL, -- 产品 ID，主键
vend_id char(10) NOT NULL, -- 供应商 ID 外键
prod_name char(255) NOT NULL, -- 产品名
prod_price decimal(8, 2) NOT NULL, -- 产品价格
prod_desc text NULL -- 产品描述
);
Customers 表
Customers表存储所有顾客信息。每个顾客有唯一的cust_id作为主键：
-- ----------------------
-- Create Customers table
-- ----------------------
CREATE TABLE Customers
(
cust_id char(10) NOT NULL, -- 顾客 ID，主键
cust_name char(50) NOT NULL, -- 顾客名
cust_address char(50) NULL, -- 顾客地址
cust_city char(50) NULL, -- 顾客所在城市
cust_state char(5) NULL, -- 顾客所在州
cust_zip char(10) NULL, -- 顾客邮编
cust_country char(50) NULL, -- 顾客所在国家
cust_contact char(50) NULL, -- 顾客联系名
cust_email char(255) NULL -- 顾客邮件地址
);
Orders 表
Orders表存储顾客订单，每个订单有唯一的编号order_num作为主键，按cust_id作为外键关联到Customers表的相应顾客：
-- -------------------
-- Create Orders table
-- -------------------
CREATE TABLE Orders
(
order_num int NOT NULL, -- 订单号，主键
order_date datetime NOT NULL, -- 订单日期
cust_id char(10) NOT NULL -- 订单顾客 ID，外键
);
OrderItems 表
OrderItems表存储每个订单中的实际物品，每个订单的每个物品一行。对于Orders表的每一行，在OrderItems表中有一行或多行。
每个OrderItem由订单号order_num加订单物品号order_item作为唯一标识的主键，并用order_num作为外键关联到Orders表。另外，prod_id列也作为外键关联到Products表：
-- -----------------------
-- Create OrderItems table
-- -----------------------
CREATE TABLE OrderItems
(
order_num int NOT NULL, -- 订单号，主键，外键
order_item int NOT NULL, -- 订单物品号
prod_id char(10) NOT NULL, -- 产品 ID，外键
quantity int NOT NULL, -- 物品数量
item_price decimal(8, 2) NOT NULL -- 物品价格
);
添加主键和外键
刚才只是创建了五张表及其各个字段，现在添加主键：
-- -------------------
-- Define primary keys
-- -------------------
ALTER TABLE Customers
ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems
ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders
ADD PRIMARY KEY (order_num);
ALTER TABLE Products
ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors
ADD PRIMARY KEY (vend_id);
之后添加外键：
-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products
ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);
ER 图
样例数据库sql_10mins的ER 图如下所示：
插入样例数据
Customers表：
-- ------------------------
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');
Vendors表：
-- ----------------------
-- Populate Vendors table
-- ----------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
Products表：
-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES ('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');
Orders表：
-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders (order_num, order_date, cust_id)
VALUES (20005, '2012-05-01', '1000000001');
INSERT INTO Orders (order_num, order_date, cust_id)
VALUES (20006, '2012-01-12', '1000000003');
INSERT INTO Orders (order_num, order_date, cust_id)
VALUES (20007, '2012-01-30', '1000000004');
INSERT INTO Orders (order_num, order_date, cust_id)
VALUES (20008, '2012-02-03', '1000000005');
INSERT INTO Orders (order_num, order_date, cust_id)
VALUES (20009, '2012-02-08', '1000000001');
OrderItems表：
-- -------------------------
-- Populate OrderItems table
-- -------------------------
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems (order_num, order_item, prod_id, quantity, item_price)
VALUES (20009, 3, 'BNBG03', 250, 2.49);
检查数据
Vendors表：
mysql> describe Vendors;
+--------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| vend_id | char(10) | NO | PRI | NULL | |
| vend_name | char(50) | NO | | NULL | |
| vend_address | char(50) | YES | | NULL | |
| vend_city | char(50) | YES | | NULL | |
| vend_state | char(5) | YES | | NULL | |
| vend_zip | char(10) | YES | | NULL | |
| vend_country | char(50) | YES | | NULL | |
+--------------+----------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> select * from Vendors;
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| BRE02 | Bear Emporium | 500 Park Street | Anytown | OH | 44333 | USA |
| BRS01 | Bears R Us | 123 Main Street | Bear Town | MI | 44444 | USA |
| DLL01 | Doll House Inc. | 555 High Street | Dollsville | CA | 99999 | USA |
| FNG01 | Fun and Games | 42 Galaxy Road | London | NULL | N16 6PS | England |
| FRB01 | Furball Inc. | 1000 5th Avenue | New York | NY | 11111 | USA |
| JTS01 | Jouets et ours | 1 Rue Amusement | Paris | NULL | 45678 | France |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
6 rows in set (0.00 sec)
Products表：
mysql> describe Products;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| prod_id | char(10) | NO | PRI | NULL | |
| vend_id | char(10) | NO | MUL | NULL | |
| prod_name | char(255) | NO | | NULL | |
| prod_price | decimal(8,2) | NO | | NULL | |
| prod_desc | text | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)
Customers表：
mysql> describe Customers;
+--------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+-------+
| cust_id | char(10) | NO | PRI | NULL | |
| cust_name | char(50) | NO | | NULL | |
| cust_address | char(50) | YES | | NULL | |
| cust_city | char(50) | YES | | NULL | |
| cust_state | char(5) | YES | | NULL | |
| cust_zip | char(10) | YES | | NULL | |
| cust_country | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
| cust_email | char(255) | YES | | NULL | |
+--------------+-----------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql> select * from Customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
Orders表：
mysql> describe Orders;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| order_num | int(11) | NO | PRI | NULL | |
| order_date | datetime | NO | | NULL | |
| cust_id | char(10) | NO | MUL | NULL | |
+------------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from Orders;
+-----------+---------------------+------------+
| order_num | order_date | cust_id |
+-----------+---------------------+------------+
| 20005 | 2012-05-01 00:00:00 | 1000000001 |
| 20006 | 2012-01-12 00:00:00 | 1000000003 |
| 20007 | 2012-01-30 00:00:00 | 1000000004 |
| 20008 | 2012-02-03 00:00:00 | 1000000005 |
| 20009 | 2012-02-08 00:00:00 | 1000000001 |
+-----------+---------------------+------------+
5 rows in set (0.00 sec)
OrderItems表：
mysql> describe OrderItems;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| order_num | int(11) | NO | PRI | NULL | |
| order_item | int(11) | NO | PRI | NULL | |
| prod_id | char(10) | NO | MUL | NULL | |
| quantity | int(11) | NO | | NULL | |
| item_price | decimal(8,2) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from OrderItems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
| 20006 | 1 | BR01 | 20 | 5.99 |
| 20006 | 2 | BR02 | 10 | 8.99 |
| 20006 | 3 | BR03 | 10 | 11.99 |
| 20007 | 1 | BR03 | 50 | 11.49 |
| 20007 | 2 | BNBG01 | 100 | 2.99 |
| 20007 | 3 | BNBG02 | 100 | 2.99 |
| 20007 | 4 | BNBG03 | 100 | 2.99 |
| 20007 | 5 | RGAN01 | 50 | 4.49 |
| 20008 | 1 | RGAN01 | 5 | 4.99 |
| 20008 | 2 | BR03 | 5 | 11.99 |
| 20008 | 3 | BNBG01 | 10 | 3.49 |
| 20008 | 4 | BNBG02 | 10 | 3.49 |
| 20008 | 5 | BNBG03 | 10 | 3.49 |
| 20009 | 1 | BNBG01 | 250 | 2.49 |
| 20009 | 2 | BNBG02 | 250 | 2.49 |
| 20009 | 3 | BNBG03 | 250 | 2.49 |
+-----------+------------+---------+----------+------------+
18 rows in set (0.00 sec)
2. 检索数据
2.1 SELECT
# 检索单个列
SELECT prod_name
FROM Products;
# 检索多个列
SELECT prod_id, prod_name, prod_price
FROM Products;
# 检索所有列
SELECT *
FROM Products;
2.2 DISTINCT
mysql> SELECT vend_id
-> FROM Products;
+---------+
| vend_id |
+---------+
| BRS01 |
| BRS01 |
| BRS01 |
| DLL01 |
| DLL01 |
| DLL01 |
| DLL01 |
| FNG01 |
| FNG01 |
+---------+
9 rows in set (0.00 sec)
mysql> SELECT DISTINCT vend_id
-> FROM Products;
+---------+
| vend_id |
+---------+
| BRS01 |
| DLL01 |
| FNG01 |
+---------+
3 rows in set (0.00 sec)
2.3 LIMIT/OFFSET
mysql> SELECT prod_name
-> FROM Products;
+---------------------+
| 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 doll |
+---------------------+
# 返回前 5 行
mysql> SELECT prod_name
-> FROM Products
-> LIMIT 5;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
+---------------------+
# 返回从第 3 行 (1+2) 开始，不超过 4 行的数据
mysql> SELECT prod_name
-> FROM Products
-> LIMIT 4 OFFSET 2;
+---------------------+
| prod_name |
+---------------------+
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
+---------------------+
# 返回第 3~6 行
mysql> SELECT prod_name
-> FROM Products
-> LIMIT 2, 4;
+---------------------+
| prod_name |
+---------------------+
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
+---------------------+
4 rows in set (0.00 sec)
3. 排序检索数据
3.1 ORDER BY
注意：确保
ORDER BY是SELECT语句中最后一条子句，否则会报错
# 通过选择的列进行排序
SELECT prod_name
FROM Products
ORDER BY prod_name;
# 通过非选择列进行排序
SELECT prod_name
FROM Products
ORDER BY prod_id;
# 按多个列排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
-- 或者
ORDER BY 2, 3;
3.2 DESC/ASC
默认升序
ASC，使用DESC关键字降序排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
4. 过滤数据
4.1 WHERE
mysql> SELECT prod_name, prod_price
-> FROM Products
-> WHERE prod_price = 3.49
-> ORDER BY prod_name;
+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| Bird bean bag toy | 3.49 |
| Fish bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
+---------------------+------------+
4.2 WHERE 子句操作符
| 操作符 | 说明 |
|---|---|
= |
等于 |
<> |
不等于 |
!= |
不等于 |
< |
小于 |
<= |
小于等于 |
!< |
不小于 |
> |
大于 |
>= |
大于等于 |
!> |
不大于 |
BETWEEN |
在两个值之间 |
IS NULL |
为 NULL 值 |
# 范围值检查
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
# 空值检查
SELECT cust_name
FROM Customers
WHERE cust_email IS NULL;
5. 高级数据过滤
5.1 AND
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01'
AND prod_price <= 4;
5.2 OR
事实上，许多 DBMS 在OR WHERE子句的第一个条件得到满足的情况下，就不再计算第二个条件了：
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01'
OR vend_id = 'BRS01';
5.3 AND 和 OR 的优先级
在 SQL 中，AND的优先级比OR高，因此尽量使用圆括号明确分组操作符：
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;
5.4 IN
IN操作符用来指定条件范围：
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name;
5.5 NOT
NOT关键字用来在WHERE子句中否定其后条件：
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
这与使用<>操作符效果相同：
SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;
MariaDB 支持使用
NOT否定IN、BETWEEN、EXISTS子句。大多数 DBMS 允许使用NOT否定任何条件
6. 使用通配符进行过滤
关于
MySQL中通配符和正则表达式的使用，参见 MySQL-通配符与正则表达式的使用 | CSDN
6.1 百分号（%）通配符
最常用的通配符是百分号%。在搜索串中，%表示任意字符出现任意次数：
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';
注意：通配符
%不会匹配NULL
6.2 下划线（_）通配符
下划线_只匹配单个字符，而不是多个字符：
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
------
+---------+--------------------+
| prod_id | prod_name |
+---------+--------------------+
| BR02 | 12 inch teddy bear |
| BR03 | 18 inch teddy bear |
+---------+--------------------+
6.3 方括号（[]）通配符
注意：仅在
Access及SQL Server中有效
方括号通配符[]用来指定一个字符集，它会匹配指定位置上的一个字符：
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
使用前缀字符^表示否定：
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;
更新中…
