题目：按要求创建一个数据库dblx1，然后在该数据库下创建几个表。
1.新建数据表fruitsCREATE TABLE fruits(f_id char(8) NOT NULL,s_id INT
2.创建数据表suppliersCREATE 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.创建表ordersCREATE 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.新建数据表orderitemsCREATE 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.创建数据表customersCREATE 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', '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');
一、单表查询 基础练习
1.在SELECT语句中指定所有字段
以fruits 表为例：select* from fruits;select {*|col_list} from table_name; 或者是直接查询指定表中的
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’的水果价格和名称。
select f_price,f_name from fruits where s_id=81 or s_id=82 and f_price>5 and f_name='apple';
