MySQL练习05

#创建数据库db_practices07
CREATE DATABASE IF NOT EXISTS `db_practices07`;

#使用数据库db_practices07
USE `db_practices07`;

#创建tb_fruits表
CREATE TABLE IF NOT EXISTS `tb_fruits`(
`f_id` CHAR(8) NOT NULL COMMENT '水果编号',
`s_id` INT NOT NULL,
`f_name` CHAR(255) NOT NULL COMMENT '水果名称',
`f_price` DECIMAL(10,2) NOT NULL COMMENT '水果价格',
PRIMARY KEY(`f_id`)
);

#向tb_fruits表中插入数据
INSERT INTO `tb_fruits`(`f_id`, `s_id`, `f_name`, `f_price`)
VALUES('a1', 81,'apple',5.2),
('b1',81,'blackberry', 8.2),
('bs1',82,'orange', 11.2),
('bs2',85,'melon',8.2),
('t1',82,'banana', 8.3),
('t2',82,'grape', 5.3),
('o2',83,'coconut', 9.2),
('c0',81,'cherry', 3.2),
('a2',83, 'apricot',2.2),
('l2',84,'lemon', 6.4),
('b2',84,'berry', 7.6),
('m1',86,'mango', 15.6),
('m2',85,'xbabay', 2.6),
('t4',87,'xbababa', 3.6),
('m3',85,'xxtt', 11.6),
('b5',87,'xxxx', 3.6);

#创建数据表tb_suppliers
CREATE TABLE IF NOT EXISTS `tb_suppliers`(  
`s_id` INT  NOT NULL AUTO_INCREMENT COMMENT '供应商编号',  
`s_name` CHAR(50) NOT NULL COMMENT '供应商名称',  
`s_city` CHAR(50) NULL COMMENT '供应商地址',  
`s_zip`  CHAR(8) NULL COMMENT '邮政编码',  
`s_call` CHAR(50) NOT NULL COMMENT '供应商电话',  
PRIMARY KEY (`s_id`)
);

#向表tb_suppliers中插入数据
INSERT INTO `tb_suppliers`(`s_id`,`s_name`,`s_city`,`s_zip`,`s_call`)
VALUES(81,'FastFruit Inc.','Tianjin','300000','48075'),
(82,'LT Supplies','Chongqing','400000','44333'),
(83,'ACME','Shanghai','200000','90046'),
(84,'FNK Inc.','Zhongshan','528437','11111'),
(85,'Good Set','Taiyuang','030000', '22222'),
(86,'Just Eat Ours','Beijing','08', '45678'),
(87,'DK Inc.','Zhengzhou','450000', '33332');

#创建表tb_orders
CREATE TABLE IF NOT EXISTS `tb_orders`(  
`o_num` INT NOT NULL AUTO_INCREMENT COMMENT '订单编号',  
`o_date` DATETIME NOT NULL COMMENT '订单日期',  
`c_id` INT NOT NULL,  
PRIMARY KEY (`o_num`)
) ;

#向表tb_orders中插入数据
INSERT INTO `tb_orders`(`o_num`,`o_date`,`c_id`)
VALUES(30001, '2008-09-01', 8001),
(30002, '2008-09-12', 8003),
(30003, '2008-09-30', 8004),
(30004, '2008-8-03', 8005),
(30005, '2008-8-08', 8001);

#新建数据表tb_orderitems
CREATE TABLE IF NOT EXISTS `tb_orderitems`(  
`o_num` INT NOT NULL,  
`o_item` INT NOT NULL,  
`f_id` CHAR(8) NOT NULL,  
`quantity` INT NOT NULL,  
`item_price` DECIMAL(8,2) NOT NULL,  
PRIMARY KEY (o_num,o_item)
);

#向表tb_orderitems中插入数据
INSERT INTO `tb_orderitems`(`o_num`, `o_item`, `f_id`, `quantity`, `item_price`)
VALUES(30001, 1, 'a1', 8, 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', 80, 8),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 8),
(30005, 2, 'b1', 8, 8.99),
(30005, 3, 'a2', 8, 2.2),
(30005, 4, 'm1', 5, 14.99);

#创建数据表tb_customers
CREATE TABLE IF NOT EXISTS `tb_customers`(  
`c_id` INT NOT NULL AUTO_INCREMENT COMMENT '顾客编号',  
`c_name` CHAR(50) NOT NULL COMMENT '顾客姓名',  
`c_address` CHAR(50) NULL COMMENT '顾客地址',  
`c_city` CHAR(50) NULL COMMENT '顾客城市',  
`c_zip` CHAR(8) NULL COMMENT '邮政编码',  
`c_contact` CHAR(50) NULL COMMENT '联系人',  
`c_email` CHAR(255) NULL COMMENT '顾客邮箱',  
PRIMARY KEY (c_id)
);

#向tb_customers表中插入数据
INSERT INTO `tb_customers`(`c_id`,`c_name`,`c_address`,`c_city`,`c_zip`,`c_contact`,`c_email`) 
VALUES(8001, 'RedHook', '200 Street ', 'Tianjin',  '300000',  'LiMing', 'LMing@163.com'),
(8002, 'Stars', '333 Fromage Lane', 'Dalian', '116000',  'Zhangbo','Jerry@hotmail.com'),
(8003, 'Netbhood', '1 Sunny Place', 'Qingdao',  '266000', 'LuoCong', NULL),
(8004, 'JOTO', '829 Riverside Drive', 'Haikou',  '570000',  'YangShan', 'sam@hotmail.com');

#01、查询tb_fruits表中的所有字段。
SELECT * FROM tb_fruits;
#02、查询tb_fruits表中f_name列所有水果名称。
SELECT f_name '水果名称' FROM tb_fruits;
#03、从tb_fruits表中获取f_name和f_price两列。
SELECT f_name, f_price FROM tb_fruits;
#04、查询价格为8.2元的水果的名称。
SELECT f_name '水果名称' FROM tb_fruits WHERE f_price='8.2';
#05、查找名称为“apple”的水果的价格。
SELECT f_price FROM tb_fruits WHERE f_name='apple';
#06、查询价格小于8的水果的名称。
SELECT f_name FROM tb_fruits WHERE f_price<8;
#07、查询s_id为81和82的记录，并按照f_name升序排序。DESC
SELECT * FROM tb_fruits WHERE s_id IN (81,82) ORDER BY f_name;
#08、查询所有s_id不等于81也不等于82的记录，并按照f_name升序排序。
SELECT * FROM tb_fruits WHERE s_id NOT IN (81,82) ORDER BY f_name;
#09、查询价格在2.00元到8.20元之间的水果名称和价格。
SELECT f_name,f_price FROM tb_fruits WHERE f_price BETWEEN 2.00 AND 8.20;
#10、查询价格在2.00元到8.20元之外的水果名称和价格。
SELECT f_name,f_price FROM tb_fruits WHERE f_price NOT BETWEEN 2.00 AND 8.20;
#11、查找所有以’b’字母开头的水果。
SELECT * FROM tb_fruits WHERE f_name LIKE 'b%';
#12、在tb_fruits表中，查询f_name中包含字母’g’的记录。
SELECT * FROM tb_fruits WHERE f_name LIKE '%g%';
#13、查询以’b’开头，并以’y’结尾的水果的名称。
SELECT * FROM tb_fruits WHERE f_name LIKE 'b%y';
#14、查询tb_customers1表中c_email不为空的记录的c_id、c_name和c_email字段值。
SELECT c_id,c_name,c_email FROM tb_customers WHERE NOT c_email  IS NULL;
#15、在tb_fruits表中查询s_id = 81，并且f_price大于等于5的水果价格和名称。
SELECT f_price,f_name FROM tb_fruits WHERE s_id = 81 AND f_price>=5;
#16、在tb_fruits表中查询s_id = 81或者82，且f_price大于5，并且f_name=‘apple’的水果价格和名称。
SELECT f_price,f_name FROM tb_fruits WHERE s_id IN (81,82) AND f_price>5 AND f_name='apple';
#17、查询s_id=81或者s_id=82的水果供应商的f_price和f_name。
SELECT f_price,f_name FROM tb_fruits WHERE s_id IN (81,82);
#18、查询s_id=81或者s_id=82的水果供应商的f_price和f_name
SELECT f_price,f_name FROM tb_fruits WHERE s_id IN (81,82)
#19、查询tb_fruits表中s_id字段的值，返回s_id字段值且不得重复。
SELECT s_id FROM tb_fruits GROUP BY s_id;
#20、查询tb_fruits表的f_name字段值，并对其进行排序。
SELECT f_name FROM tb_fruits ORDER BY f_name;
#21、查询tb_fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序。
SELECT f_name,f_price FROM tb_fruits ORDER BY f_name,f_price;
#22、查询tb_fruits表中的f_name和f_price字段，对结果按f_price降序方式排序。
SELECT * FROM tb_fruits ORDER BY f_price DESC;
#23、查询tb_fruits表，先按f_price降序排序，再按f_name字段升序排序。
SELECT * FROM tb_fruits ORDER BY f_price DESC,f_name;
#24、根据s_id对tb_fruits表中的数据进行分组。
SELECT * FROM tb_fruits GROUP BY s_id;
#25、根据s_id对tb_fruits表中的数据进行分组，将每个供应商的水果名称显示出来。
SELECT s_id,group_concat(f_name) FROM tb_fruits GROUP BY s_id;
#26、根据s_id对tb_fruits表中的数据进行分组，并显示水果种类大于1的分组信息。
SELECT s_id,group_concat(f_name) FROM tb_fruits GROUP BY s_id HAVING COUNT(f_name)>1;
#group_concat（）函数的用法
#group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
#27、以id分组，把f_name字段的值打印在一行，逗号分隔(默认)。
SELECT group_concat(f_name) FROM tb_fruits GROUP BY s_id;
#27、以id分组，把f_name字段的值打印在一行，分号分隔。
SELECT group_concat(f_name SEPARATOR ';') FROM tb_fruits GROUP BY s_id;
#27、以id分组，把去冗余的f_name字段的值打印在一行，逗号分隔。
SELECT group_concat(DISTINCT f_name) FROM tb_fruits GROUP BY s_id;
#27、以id分组，把f_name字段的值打印在一行，逗号分隔，以name排倒序。
SELECT group_concat(f_name ORDER BY f_name DESC) FROM tb_fruits GROUP BY s_id ;
#28、根据s_id对tb_fruits表中的数据进行分组，并显示记录数量。
SELECT s_id,COUNT(*) FROM tb_fruits GROUP BY s_id;
#29、根据s_id和f_name字段对tb_fruits表中的数据进行分组。
SELECT s_id,f_name FROM tb_fruits GROUP BY s_id,f_name;
#30、查询订单价格大于80的订单号和总订单价格。
SELECT o.o_num '订单号',SUM(item_price*quantity) '总订单价格' 
FROM tb_orders o 
JOIN tb_orderitems os ON o.o_num=os.o_num
GROUP BY o.o_num
HAVING 总订单价格 >80;
#31、显示tb_fruits表查询结果的前4行。
SELECT * FROM tb_fruits LIMIT 0,4;
#32、在tb_fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录。
SELECT * FROM tb_fruits LIMIT 4,3;
#33、查询tb_customers表中总的行数。
SELECT COUNT(*) '行数' FROM tb_customers;
#34、查询tb_customers表中有电子邮箱的顾客的总数。
SELECT COUNT(*) FROM tb_customers WHERE c_email IS NOT NULL;
#35、在tb_orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类。
SELECT o_num,COUNT(f_id) '水果种类' FROM tb_orderitems GROUP BY o_num;
#36、在tb_orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量。
SELECT o_num,COUNT(quantity) '水果总量' FROM tb_orderitems GROUP BY o_num;
#37、在tb_fruits表中，查询s_id=83的供应商的水果价格的平均值。
SELECT s_id,AVG(f_price) FROM tb_fruits GROUP BY s_id HAVING s_id=83;
#38、在tb_fruits表中，查询每一个供应商的水果价格的平均值。
SELECT s_id,AVG(f_price) FROM tb_fruits GROUP BY s_id;
#39、在tb_fruits表中查找市场上价格最高的水果。
SELECT f_name '价格最高的水果' FROM tb_fruits ORDER BY f_price limit 1;
#40、在tb_fruits表中查找不同供应商提供的价格最高的水果。 
SELECT s_id,f_name '价格最高的水果',MAX(f_price) FROM tb_fruits GROUP BY s_id,f_name;
#41、在tb_fruits表中查找f_name的最大值。
SELECT MAX(f_name) '最大值' from tb_fruits;
#42、在tb_fruits表中查找市场上价格最低的水果。
SELECT f_name FROM tb_fruits WHERE f_price=(SELECT MIN(f_price) FROM tb_fruits);
#43、在tb_fruits表中查找不同供应商提供的价格最低的水果。
SELECT s_id,f_name '价格最低的水果', MIN(f_price) FROM tb_fruits GROUP BY s_id,f_name;