MySQL 基础练习

数据库

说明

最近看了 Sams Teach Yourself 系列的《SQL必知必会》。里面的内容很基础，使用SQL上手简单。我将其中典型的题目及答案总结如下，包含数据库所有的基础操作，其中所使用的数据库样例表见文章末尾。

练习

（Products）检索列vend_id的所有值和「不同值」

SELECT DISTINCT vend_id FROM Products;

检索列prod_name的前5行数据、第6到10行数据

SELECT prod_name FROM Products LIMIT 5;
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

检索列prod_id, prod_price, prod_name，并按后两个升序排列（位置）

SELECT  prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;

检索列prod_price，并按prod_price降序排列

SELECT  prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;

检索列prod_name, prod_price，同时prod_price为3.49、小于10、在5至10之间、空值

SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;

检索列prod_name, prod_price，其中vend_id为DLL01或BRS01（两种方式）

 SELECT  prod_name , prod_price  FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

检索列prod_id, prod_name，其中prod_name以词Fish起头、包含"bean bag"、以F开头y结尾

 SELECT  prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';
 SELECT  prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';
 SELECT  prod_id, prod_name FROM Products WHERE prod_name LIKE 'F%y';

Vendors表包含列vend_name和vend_country，用括号将vend_country括起来，组合两列返回（使用别名vend_title）

SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title FROM Vendors ORDER BY vend_name;

在OrderItems中，检索order_num为20008的所有物品（+ 汇总物品的价格为expanded_price = quantity * item_price）

SELECT *, quantity * item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

在Vendors中，将vend_name显示为小写和大写（vend_name_upcase）

SELECT vend_name, UPPER(vend_name) AS vend_upcase FROM Vendors ORDER BY vend_name;

在Orders中，输出order_num，条件是order_date在2012年

SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;

在Products中，将prod_price的平均值作为avg_price输出（6.8233）（+ vend_id为DLL01的平均值）

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

在Customers中，计算顾客的总数作为num_cust输出（+ cust_email列）

SELECT COUNT(*) AS num_cast FROM Customers;

在Products中，返回prod_price的最大值，作为max_price输出（11.99）

SELECT MAX(prod_price) AS max_price FROM Products;

在OrderItems中，计算order_num为20005的quantity的总和，作为items_ordered输出

SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;

在Products中，列出vend_id及每个出现的次数（num_prods）

SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;

在Orders中，按照cust_id分组，列出cust_id及其行数（orders），打印出行数>=2的

SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;

在Products中，对vend_id分组，找出prod_price>=4并且行数>=2的vend_id，并打印对应行数（num_prods）

SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

在OrderItems中，选取prod_id为RGAN01的列order_num，并将结果作为order_num的范围选取条件，在Orders中选取cust_id

SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');

在Vendors和Products中，选取列vend_name, prod_name, prod_price（vend_id相同，等值联结）（+使用内联结）

SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;
SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

在Customers中，插入一行数据，cust_id=10006, cust_name=yano(+并删除)

INSERT INTO Customers (cust_id, cust_name) VALUES(10006, 'yano');
 DELETE from Customers where cust_name = 'yano';

将表Customers的内容全部复制到CustCopy表中

CREATE TABLE CustCopy AS  SELECT * FROM Customers;

在Customers中，更新cust_id为1000000005的用户，将cust_email改为'yano_nankai'

UPDATE Customers SET cust_email = 'yano_nankai' WHERE cust_id = '1000000005';

创建表yano，id为整数非空，name为10个字符非空（默认为hello），并更新表结构（增加和删除phone列，20个字符非空），将id增加约束为主键，删除表yano

CREATE TABLE YANO( id INTEGER NOT NULL, name CHAR(10) NOT NULL DEFAULT 'HELLO');
ALTER TABLE YANO ADD phone CHAR(20);
ALTER TABLE YANO DROP COLUMN phone;

查看Orders的索引

SHOW INDEX FROM Orders;

样例表

http://www.forta.com/books/0672327120/mysql_scripts.zip