MySQL基础练习题（一）

分别创建了数据表fruits、数据表suppliers、表orders、数据表orderitems、数据表orderitems和数据表customers。

1.新建数据表fruits

CREATE TABLE fruits(f_id char(8) NOT NULL,
s_id INT NOT NULL,
f_name char(255) NOT NULL,
f_price decimal(10,2) NOT NULL,PRIMARY KEY(f_id) ); INSERT INTO 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);

2.创建数据表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(8) 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(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');

3.创建表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, '2008-09-01', 8001),
(30002, '2008-09-12', 8003),
(30003, '2008-09-30', 8004),
(30004, '2008-8-03', 8005),
(30005, '2008-8-08', 8001);

4.新建数据表orderitems

CREATE TABLE 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)) ; INSERT INTO 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);

5.创建数据表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(8) 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(8001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', **********'),
(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');

一、单表查询 基础练习

1.在SELECT语句中指定所有字段

以fruits 表为例：select* from fruits;

select {

|col_list} from table_name;

或者是直接查询指定表中的所有字段，|col_list}表示数据表中的字段列表。select f_id, s_id,f_name,f_price from fruits;

select

from fruits;

2.查询fruits表中f_name列所有水果名称，SQL语句如下：

select f_name from fruits;

3.从fruits表中获取f_name和f_price两列，SQL语句如下：

select f_name,f_price from fruits;

4.查询价格为8.2元的水果的名称

select f_name from fruits where f_price=8.2;

5.查找名称为“apple”的水果的价格，SQL语句如下：

select f_price from fruits where f_name='apple';  一定要注意要用单引号扩上！

6.查询价格小于8的水果的名称

select f_name from fruits where f_price<8;

7.查询s_id为81和82的记录，并按照f_name升序排序，SQL语句如下：

用到inSELECT s_id FROM fruits WHERE s_id IN (81,82) ORDER BY f_name;

8.查询所有s_id不等于81也不等于82的记录，并按照f_name升序排序

select * from fruits where s_id<>81 and s_id<>82 order by f_name;

9.查询价格在2.00元到8.20元之间的水果名称和价格。

select f_name,f_price from fruits where f_price between 2.00 and 8.20;

10.查询价格在2.00元到8.20元之外的水果名称和价格。

select f_name,f_price from fruits where f_price not between 2.00 and 8.20;

11.查找所有以’b’字母开头的水果。

select * from fruits where f_name like'b%';

12.在fruits表中，查询f_name中包含字母’g’的记录。

select * from fruits where f_name like '%g%';

13.查询以’b’开头，并以’y’结尾的水果的名称。

select * from fruits where f_name like 'b%y';

14.查询customers1表中c_email不为空的记录的c_id、c_name和c_email字段值。

select c_id,c_name,c_email from customers where c_email is not null;

15.在fruits表中查询s_id = 81，并且f_price大于等于5的水果价格和名称。

select f_price,f_name from fruits where s_id=81 and f_price>=5;

16.在fruits表中查询s_id = 81或者82，且f_price大于5，并且f_name=‘apple’的水果价格和名称。

方式一：用 in(81,82)指定多个值，然后利用and组合多个条件，查询相关记录
select s_id,f_price,f_name from fruits where s_id in(81,82) and f_price > 5 and f_name = 'apple';

方式二：先从fruits表中查询s_id='81'或'82'的s_id,f_name ,f_price，并将查询结果作为一个新的表，再从这个新表中查询f_price >'5' and f_name ='apple'的记录
select * from (select s_id,f_name ,f_price from fruits where s_id ='81' or s_id ='82' )as a where f_price >'5' and f_name ='apple';

这里感谢一位牛友在评论区指出我犯下的错误，希望其他伙伴不要和我一样犯粗心大意的错误（如下）

select f_price,f_name from fruits where s_id=81 or s_id=82 and f_price>5 and f_name='apple';
如果是对SQL语句基础掌握的不够牢固的话，乍一看可能会觉得这条语句没错，当然，命令是可执行的，但是返回结果却大错特错：如果是用or做连接的话，执行时会返回符合or前后的条件的任意语句，也就是出现如下返回结果！

17.查询s_id=81或者s_id=82的水果供应商的f_price和f_name。

select f_price,f_name from fruits where s_id=81 or s_id=82;

18.查询s_id=81或者s_id=82的水果供应商的f_price和f_name

select f_price,f_name from fruits where s_id=81 or s_id=82;

19.查询fruits表中s_id字段的值，返回s_id字段值且不得重复。

select distinct s_id from fruits;//使用关键字 distinct，使返回数据不重复

20.查询fruits表的f_name字段值，并对其进行排序。

select f_name from fruits order by f_name;