大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

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