mysql数据库完整案例-“水果销售”_b2b2c水果批发零售商城的数据库-CSDN博客

CREATE TABLE fruits
(
f_id    char(10)      NOT NULL,
s_id    INT           NOT NULL,
f_name  char(255)   NOT NULL,
f_price decimal(8,2)   NOT NULL,
PRIMARY KEY(f_id)
);

输入表记录：

INSERT INTO fruits (f_id, s_id, f_name, f_price)
      VALUES ('a1', 101,'苹果',5.2),
     ('b1',101,'黑莓', 10.2),
     ('bs1',102,'桔子', 11.2),
     ('bs2',105,'西瓜',3),
     ('t1',102,'香蕉', 5.3),
     ('t2',102,'葡萄', 10.5),
     ('o2',103,'椰子', 9.2),
     ('c0',101,'樱桃', 18),
     ('a2',103, '杏子',2.2),
     ('l2',104,'柠檬', 6.4),
     ('b2',104,'草莓', 12),
     ('m1',106,'芒果', 15.6);

(2)数据库表customers：

创建表结构：

CREATE TABLE customers
(
  c_id      int       NOT NULL AUTO_INCREMENT,
  c_name    char(50)  NOT NULL,
  c_address char(50)  NULL,
  c_city    char(50)  NULL,
  c_zip     char(10)  NULL,
  c_contact char(50)  NULL,
  c_email   char(255) NULL,
  PRIMARY KEY (c_id)
);

输入表记录：

INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip,  c_contact, c_email)
VALUES
 (10001, '红房子水果店', '长安路200号 ', '天津', '300000',  '黎明', 'LMing@163.com'),
(10002, '星星水果店', '淮海路112号', '大连', '116000',  '张博','Jerry@hotmail.com'),
(10003, '绿叶水果店', '五一路01号', '青岛',  '266000', '罗聪', NULL),
(10004, '四季水果店', '河滨路829号', '海口',  '570000',  '杨姗','sam@hotmail.com');

(3)数据库表orderitems：

创建表结构：

CREATE TABLE orderitems
(
  o_num      int          NOT NULL,
  o_item     int           NOT NULL,
  f_id       char(10)       NOT NULL,
  quantity   int           NOT NULL,
  item_price decimal(8,2)   NOT NULL,
  PRIMARY KEY (o_num,o_item)
) ;

输入表记录：

INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);

(4)数据库表suppliers：

创建表结构：

CREATE TABLE suppliers
(
  s_id      int      NOT NULL AUTO_INCREMENT,
  s_name    char(50)   NOT NULL,
  s_city    char(50)     NULL,
  s_zip     char(10)    NULL,
  s_call    CHAR(50)   NOT NULL,
  PRIMARY KEY (s_id)
) ;

输入表记录：

INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
VALUES (101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');

(5)创建表orders：

创建表结构:

CREATE TABLE orders
(
  o_num  int      NOT NULL AUTO_INCREMENT,
  o_date datetime NOT NULL,
  c_id   int      NOT NULL,
  PRIMARY KEY (o_num)
) ;

输入表记录：

INSERT INTO orders(o_num, o_date, c_id)
VALUES (30001, '2022-09-01', 10001),
(30002, '2022-09-12', 10003),
(30003, '2022-09-30', 10004),
(30004, '2022-10-03', 10005),
(30005, '2022-10-08', 10001);

3.基本操作

(1)从fruits表中检索所有字段的数据：

 SELECT * FROM fruits;

(2)查询fruits表中f_name列所有水果名称：

SELECT f_name FROM fruits;

(3)从fruits表中获取f_name和f_price两列：

SELECT f_name, f_price FROM fruits;

(4)在fruits表中，查询价格为10.2元的水果的名称：

SELECT f_name, f_price  FROM fruits  WHERE f_price = 10.2;

(5)在fruits表中，查找名称为“apple”的水果的价格：

SELECT f_name, f_price  FROM fruits  WHERE f_name = 'apple';

(6)在fruits表中，查询价格小于10的水果的名称：

SELECT f_name, f_price FROM fruits WHERE f_price < 10;

(7)在fruits表中，查询s_id为101和102的记录：

SELECT s_id,f_name, f_price  FROM fruits  
WHERE s_id IN (101,102) ORDER BY f_name;

(8)在fruits表中，查询所有s_id不等于101也不等于102的记录：

SELECT s_id,f_name, f_price  FROM fruits
WHERE s_id NOT IN (101,102) ORDER BY f_name;

(9)在fruits表中，查询价格在2.00元到10.20元之间的水果名称和价格：

SELECT f_name, f_price FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;

(10)在fruits表中，查询价格在2.00元到10.20元之外的水果名称和价格：

SELECT f_name, f_price FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;

(11)在fruits表中，查找所有以’b’字母开头的水果：

SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%';

(12)在fruits表中，查询f_name中包含字母’g’的记录：

SELECT f_id, f_name  FROM fruits WHERE f_name LIKE '%g%';

(13)在fruits表中，查询以’b’开头，并以’y’结尾的水果的名称：

SELECT f_name  FROM fruits  WHERE f_name LIKE 'b%y';

(14)在fruits表中，查询以字母’y’结尾，且’y’前面只有4个字母的记录：

SELECT f_id, f_name FROM fruits WHERE f_name LIKE '----y';

(15)查询customers表中c_email为空的记录的c_id、c_name和c_email字段值：

SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;

(16)查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值：

SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;

(17)在fruits表中查询s_id = 101，并且f_price大于等于5的水果价格和名称：

SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;

(18)在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称：

SELECT f_id, f_price, f_name FROM fruits
WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';

(19)查询s_id=101或者s_id=102的水果供应商的f_price和f_name：

SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;

(20)查询fruits表中s_id字段的值，返回s_id字段值且不得重复：

SELECT DISTINCT s_id FROM fruits;

(21)查询fruits表的f_name字段值，并对其进行排序：

 SELECT f_name FROM fruits ORDER BY f_name;

(22)查询fruits表中f_name和f_price字段，先按f_name排序，再按f_price排序：

SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;

(23)查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序：

SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;

(24)查询fruits表，先按f_price降序排序，再按f_name字段升序排序：

SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;

(25)根据s_id对fruits表中的数据进行分组：

SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;

(26)根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来：

SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits
GROUP BY s_id;

(27)根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息：

SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits
GROUP BY s_id HAVING COUNT(f_name) > 1;

(28)根据s_id对fruits表中的数据进行分组，并显示记录数量：

SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;

(29)在表orderitems中，查询订单价格大于100的订单号和总订单价格：

SELECT o_num,  SUM(quantity * item_price) AS orderTotal
FROM orderitems GROUP BY o_num
HAVING SUM(quantity*item_price) >= 100;

可以看到，返回的结果中orderTotal列的总订单价格并没有按照一定顺序显示，接下来，使用ORDER BY关键字按总订单价格排序显示结果，SQL语句如下：

SELECT o_num,  SUM(quantity * item_price) AS orderTotal
FROM orderitems  GROUP BY o_num
HAVING SUM(quantity*item_price) >= 100
ORDER BY orderTotal;

(30)显示fruits表查询结果的前4行：

SELECT * From fruits LIMIT 4;

(31)在fruits表中使用LIMIT子句，返回从第5个记录开始的连续3条记录：

SELECT * From fruits LIMIT 4, 3;

(32)查询customers表中总的行数：

SELECT COUNT(*) AS cust_num  FROM customers;

(33)查询customers表中有电子邮箱的顾客的总数：

 SELECT COUNT(c_email) AS email_num FROM customers;

(34)在orderitems表中，使用COUNT( )函数统计不同订单号中订购的水果种类：

 SELECT o_num, COUNT(f_id)
     FROM orderitems  GROUP BY o_num;

(35)在orderitems表中查询30005号订单一共购买的水果总量：

SELECT SUM(quantity) AS items_total
 FROM orderitems WHERE o_num = 30005;

(36)在orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量：

SELECT o_num, SUM(quantity) AS items_total
    FROM orderitems  GROUP BY o_num;

(37)在fruits表中，查询s_id=103的供应商的水果价格的平均值：

SELECT AVG(f_price) AS avg_price
   FROM fruits  WHERE s_id = 103;

(38)在fruits表中，查询每一个供应商的水果价格的平均值：

SELECT s_id,AVG(f_price) AS avg_price
   FROM fruits  GROUP BY s_id;

(39)在fruits表中查找市场上价格最高的水果：

SELECT MAX(f_price) AS max_price FROM fruits;

(40)在fruits表中查找不同供应商提供的价格最高的水果：

SELECT s_id, MAX(f_price) AS max_price
   FROM fruits  GROUP BY s_id;

(41)在fruits表中查找不同供应商提供的价格最低的水果：

SELECT s_id, MIN(f_price) AS min_price
     FROM fruits  GROUP BY s_id;

(42)由结果可以看到，fruits表和suppliers表中都有相同数据类型的字段s_id，两个表通过s_id字段建立联系。接下来从fruits表中查询f_name、f_price字段，从suppliers表中查询s_id、s_name，SQL语句如下：

SELECT suppliers.s_id, s_name,f_name, f_price
     FROM fruits ,suppliers   WHERE fruits.s_id = suppliers.s_id;

(43)在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询：

 SELECT suppliers.s_id, s_name,f_name, f_price
     FROM fruits INNER JOIN suppliers  ON fruits.s_id = suppliers.s_id;

(44)查询供应f_id= ‘a1’的水果供应商提供的其他水果种类：

 SELECT f1.f_id, f1.f_name  FROM fruits AS f1, fruits AS f2
      WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

(45) LEFT JOIN左连接：左连接的结果包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。

在customers表和orders表中，查询所有客户，包括没有订单的客户：

SELECT customers.c_id, orders.o_num
     FROM customers LEFT OUTER JOIN orders
     ON customers.c_id = orders.c_id;

(46)RIGHT JOIN右连接：右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。

在customers表和orders表中，查询所有订单，包括没有客户的订单：

 SELECT customers.c_id, orders.o_num
     FROM customers RIGHT OUTER JOIN orders
     ON customers.c_id = orders.c_id;

(47)在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息：

SELECT customers.c_id, orders.o_num
     FROM customers INNER JOIN orders
     ON customers.c_id = orders.c_id AND customers.c_id = 10001;

(48)在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果排序：

SELECT suppliers.s_id, s_name,f_name, f_price
     FROM fruits INNER JOIN suppliers
     ON fruits.s_id = suppliers.s_id  ORDER BY fruits.s_id;

(49)查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录：

 SELECT * FROM fruits
     WHERE EXISTS  (SELECT s_name FROM suppliers WHERE s_id = 107);

(50)查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的f_price大于10.20的记录：

 SELECT * FROM fruits
     WHERE f_price>10.20 AND EXISTS
     (SELECT s_name FROM suppliers WHERE s_id = 107);

(51)查询suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录：

 SELECT * FROM fruits
     WHERE NOT EXISTS
     (SELECT s_name FROM suppliers WHERE s_id = 107);

(52)在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id：

SELECT c_id FROM orders WHERE o_num IN
     (SELECT o_num  FROM orderitems WHERE f_id = 'c0');

(53)在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类：

SELECT s_id, f_name FROM fruits
WHERE s_id =(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

(54)在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类：

 SELECT s_id, f_name FROM fruits
 WHERE s_id <> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

(55)查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果的信息，使用UNION连接查询结果：

SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);

(56)如前所述，UNION将多个SELECT语句的结果组合成一个结果集合。可以分开查看每个SELECT语句的结果：

 SELECT s_id, f_name, f_price
     FROM fruits
     WHERE f_price < 9.0;

 SELECT s_id, f_name, f_price
     FROM fruits
     WHERE s_id IN(101,103);

(57)查询所有价格小于9的水果的信息，查询s_id等于101和103的所有水果的信息，使用UNION ALL连接查询结果：

SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);

(58)为orders表取别名o，查询30001订单的下单日期：

SELECT * FROM orders AS o
WHERE o.o_num = 30001;

(59) 为customers和orders表分别取别名，并进行连接查询：

 SELECT c.c_id, o.o_num
    FROM customers AS c LEFT OUTER JOIN orders AS o
     ON c.c_id = o.c_id;

 SELECT f1.f_id, f1.f_name
    FROM fruits AS f1, fruits AS f2
      WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

(60)查询fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别名f1，查询表中f_price < 8的水果的名称：

 SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price
     FROM fruits AS f1
     WHERE f1.f_price < 8;

(61)查询suppliers表中字段s_name和s_city，使用CONCAT函数连接这两个字段值，并取列别名为suppliers_title。

如果没有对连接后的值取别名，其显示列名称将会不够直观：

 SELECT CONCAT(TRIM(s_name) , ' (',  TRIM(s_city), ')')
      FROM suppliers
      ORDER BY s_name;

SELECT CONCAT(TRIM(s_name) , ' (', TRIM(s_city), ')')
     AS suppliers_title
     FROM suppliers
     ORDER BY s_name;

(62)在fruits表中，查询f_name字段以字母’b’开头的记录：

SELECT * FROM fruits WHERE f_name REGEXP '^b';

(63)在fruits表中，查询f_name字段以“be”开头的记录：

SELECT * FROM fruits WHERE f_name REGEXP '^be';

(64)在fruits表中，查询f_name字段以字母’y’结尾的记录：

SELECT * FROM fruits WHERE f_name REGEXP 'y$';

(65)在fruits表中，查询f_name字段以字符串“rry”结尾的记录：

SELECT * FROM fruits WHERE f_name REGEXP 'rry$';

(66)在fruits表中，查询f_name字段值包含字母’a’与’g’且两个字母之间只有一个字母的记录：

SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

(67)在fruits表中，查询f_name字段值以字母’b’开头，且’b’后面出现字母’a’的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP '^ba*';

(68)在fruits表中，查询f_name字段值以字母’b’开头，且’b’后面出现字母’a’至少一次的记录，SQL语句如下：

 SELECT * FROM fruits WHERE f_name REGEXP '^ba+';

(69)在fruits表中，查询f_name字段值包含字符串“on”的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'on';

(70)在fruits表中，查询f_name字段值包含字符串“on”或者“ap”的记录：

 SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

(71)在fruits表中，使用LIKE运算符查询f_name字段值为“on”的记录：

SELECT * FROM fruits WHERE f_name LIKE 'on';

(72)在fruits表中，查找f_name字段中包含字母’o’或者’t’的记录：

SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

(73)在fruits表，查询s_id字段中数值中包含4、5或者6的记录：

SELECT * FROM fruits WHERE s_id REGEXP '[456]';

(74)在fruits表中，查询f_id字段包含字母a~e和数字1~2以外的字符的记录：

SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

(75)在fruits表中，查询f_name字段值出现字母’x’至少2次的记录：

SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';