MySQL之DQL语句（一）

GrowthDBA 2021-08-24

449

今天开始正式学习使用率最高的DQL语句，知识的梳理顺序按照Ben Forta的《MySQL必知必会》进行，同时，书中还为我们提供了做练习的测试数据，下载地址：https://forta.com/books/0672327120/。

测试数据准备

为了方便大家直接粘贴复制，我已经将脚本下载下来，脚本共有两个文件，分别是create.sql和populate.sql。

create.sql：

/* 创建一个dql数据库 */
CREATE DATABASE `dql` DEFAULT CHARACTER SET utf8mb4;
USE `dql`;

/* create.sql */
########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################


########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id int       NOT NULL AUTO_INCREMENT,
  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 ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id char(10) NOT NULL ,
  quantity int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id char(10) NOT NULL,
  vend_id int           NOT NULL ,
  prod_name char(255) NOT NULL ,
  prod_price decimal(8,2) NOT NULL ,
  prod_desc text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id int      NOT NULL AUTO_INCREMENT,
  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 ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id int           NOT NULL AUTO_INCREMENT,
  prod_id char(10) NOT NULL,
  note_date datetime NOT NULL,
  note_text text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# 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);
复制

populate.sql：

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# 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(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','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(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'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(1005,'Jet Set','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(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);
复制

创建好表，插入好测试数据，下面正式开始。

SELECT检索数据

数据库是表的集合，表又是数据的集合，表是一个二维结构，分为行和列。行即为一条完整的记录，比如商品表中，商品编号是1、商品名称是苹果、商品单价是5、产地是山东烟台，这就是表中的一行数据；一列就是我们有很多商品，并且每种商品都有各自的编号且不重复，那么这些编号就是表中的一列数据。多个列和多个行共同组成了一张表。

检索单个列：

SELECT prod_name FROM products;
复制

其实列就是我们之前所说的字段，上述语句就是从products表中检索出一个名为prod_name的列。

检索多个列：

如果想要检索多个列，使用相同的SELECT语句，唯一不同的是必须在SELECT关键字后面给出多个列名，列名之间必须以逗号隔开。

SELECT prod_id,prod_name,prod_price FROM products;
复制

这个语句可以检索出三个列的信息。

检索所有列：

除了指定所需的列外，SELECT语句还可以检索所有的列而不必逐一列出它们（当然全部列出来也是可以的）。可以通过在实际列名的位置使用星号“*”通配符来达到目的。

SELECT * FROM products;
复制

注意：一般情况下，除非你确定需要使用表中的每个列，否则最好不要使用“*”通配符，虽然用起来省事，但是检索不需要的列会降低检索和应用程序的性能。SELECT * 和 SELECT [所有列] 的区别在于：程序端API传参上会报错，比如DBA新增列之后，写成*也会传入新增的列，可能会报错。

检索不同的行：

SELECT返回所有匹配的行。但是，如果你不想要每个值每次都出现，怎么办？

SELECT语句返回14行数据（即使表中只有4个供应商），因为我们products表中有14个产品，那么，如何检索出有不同值的列表呢？解决办法就是使用DISTINCT关键字，即去重。

SELECT DISTINCT vend_id FROM products;
复制

可以看到输出的结果只剩下去重后的4行了。

注意：

1、DISTINCT需要放到所有列名的前面，如果写成SELECT vend_id, DISTINCT prod_name FROM products;会报错。

[root@localhost][dql]> SELECT vend_id, DISTINCT prod_name FROM products;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT prod_name FROM products' at line 1
复制

2、DISTINCT的作用域是对后面所有列名的组合进行去重，并不只生效于跟在DISTINCT关键字后面单独的一列。（因为商品名没有重复的，所以下面的结果又返回了14行数据）

起别名：

我们在使用 SELECT 查询的时候，还有一些技巧可以使用，比如你可以给列名起别名。

SELECT vend_id AS '供应商ID',prod_name AS '商品名称' FROM products;
复制

运行结果和上面多列检索的运行结果是一样的，只是将列名改成了'供应商ID'和'商品名称'。当然这里的列别名只是举例，一般来说起别名的作用是对原有名称进行简化，从而让SQL语句看起来更精简。同样我们也可以对表名称起别名，这个在多表连接查询的时候会用到。

查询常数：

SELECT查询还可以对常数进行查询。就是在SELECT查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。SQL中的SELECT语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

SELECT vend_id AS '供应商ID',prod_name AS '商品名称','物美价廉' AS remark FROM products;
复制

我们虚构了一个remark字段，并且把它设置为固定值“物美价廉”。需要说明的是，如果常数是个字符串，那么使用单引号''就非常重要了，比如'物美价廉'。单引号说明引号中的字符串是个常数，否则SQL会把物美价廉当成列名进行查询，但实际上数据表里没有这个列名，就会引起错误。如果常数是英文字母，比如'WZRY'也需要加引号。如果常数是个数字，就可以直接写数字，不需要单引号。常数列的数据量是由真实查得的数据行数决定的。

限制结果：

SELECT语句返回所有匹配的行，它们可能是指定表中的每个行。但是为了返回第一行或是前几行，可以使用LIMIT语句。

第一种：LIMIT [数字]

SELECT prod_name FROM products LIMIT 5;
复制

第二种：LIMIT [数字],[数字]

SELECT prod_name FROM products LIMIT 5,5;
复制

这种写法表示MySQL返回从第5行开始往后显示5行数据。第一个数字为开始位置，第二个数字为要检索的行数。注意：当行数不够时，MySQL只能返回真实数据的行数。（比如，我们的表是14行记录，使用LIMIT 10,5，那么最终结果只能返回4行）

第三种：LIMIT [数字] OFFSET [数字]

SELECT prod_name FROM products LIMIT 5 OFFSET 5;
复制

这种写法同LIMIT [数字],[数字]，只不过两个数字的含义正好是相反的。第一个数字表示取几行数据，第二个数字表示从第几行开始取。LIMIT 3 OFFSET 4等同于LIMIT4,3。

使用完全限定的表名：

直接看例子

SELECT products.prod_name FROM dql.products;
复制

其实完全限定的表名就是将表名完整的关系体现出来，即[库名].[表名]、[表名].[列名]。

排序检索数据

其实，检索出的数据并不是以纯粹的随机顺序显示的，如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或者删除，则此顺序将会受到MySQL重用回收存储空间的影响。因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。

SQL语句由子句构成，有些子句是必须的，而有的是可选的。为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

按单个列排序：

SELECT prod_name FROM products ORDER BY prod_name;
复制

通常，ORDER BY子句中使用的列将是为显示所选择的列，但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

按多个列排序：

为了按多个列排序，只要指定列名，列名之间用逗号分开即可。

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
复制

重要的是理解在按多个列排序时，排序完全按所规定的的顺序进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值是唯一的，则不会按prod_name排序。

指定排序方向：

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;
复制

上面这个例子是按价格以降序排序产品（最贵的排在最前面）。

下面一例以降序排序产品（最贵的在最前面），然后再对产品名排序：

DESC关键字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。与DESC相反的关键字为ASC（ASCENDING），在升序排序时可以指定它。实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则默认为ASC）。

ORDER BY和LIMIT的组合：

SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
复制

这个例子演示的是如何找出最昂贵物品的单价。prod_price DESC保证行是按照由最昂贵到最便宜检索的，而LIMIT 1只返回一行数据。

注意：在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生报错。

[root@localhost][dql]> SELECT prod_price FROM products LIMIT 1 ORDER BY prod_price DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY prod_price DESC' at line 1
复制

小结

本章学习了如何使用SQL的SELECT语句来检索单个表列、多个表列以及所有表列，同时，还学习了起别名、查询常数。最后学习了如何用SELECT语句的ORDER BY子句对检索出的数据进行排序。这个子句必须是SELECT语句中的最后一条子句。可根据需要，利用它在一个或多个列上对数据进行排序。本章内容较为简单，大家可以下载下来脚本自己测试练习。每天进步一点点。

end

mysql

文章转载自GrowthDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL之DQL语句（一）

评论

相关阅读