1、blackberry, ,(bs1, 102, orange, ,bs2, 105, melont1bananat2grapeo2, 103, coconutc0cherrya2apricotl2, 104, lemonb2berrym1, 106, mangom2xbabayt4, 107, xbababam3xxttb5xxxx, ;【1】从fruits表中检索所有字段的数据Select * from fruits;【2】查询fruits表中f_name列所有水果名称Select f_name from fruits;【3】从fruits表中获取f_name和f_price两列Select
2、 f_name,f_price from fruits;【4】查询价格为元的水果的名称Select f_name from fruits where f_price=;【5】查找名称为“apple”的水果的价格Select f_price from fruits where f_name=apple;【6】查询价格小于10的水果的名称Select f_name from fruits where f_peice and f_price;【10】查询价格在元到元之外的水果名称和价格Select f_name,f_price from fruits where f_price【11】查找所有以b字
3、母开头的水果Select f_name 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】在fruits表中,查询以字母y结尾,且y前面只有4个字母的记录Select * from fruits where f_name like “_y”;第二部分:#创建数据表customersCREA
4、TE 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_contac
5、t, c_email) VALUES(10001, RedHook, 200 Street Tianjin300000LiMing),(10002, Stars333 Fromage Lane,Dalian116000Zhangbo(10003, Netbhood1 Sunny PlaceQingdao266000LuoCong, NULL),(10004, JOTO829 Riverside DriveHaikou570000YangShan 查询表中的记录数目: SELECT COUNT(*) AS cust_num FROM customers;【15】查询customers表中c_em
6、ail为空的记录的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_name,f_price from fruits wh
7、ere s_id=101 and f_price=5;【18】在fruits表中查询s_id = 101或者102,且f_price大于5,并且f_name=apple的水果价格和名称Select f_name,f_price from fruits where s_id in (101,102) and f_price5 and f_name=apple;【19】查询s_id=101或者s_id=102的水果供应商的f_price和f_nameSelect f_price,f_name from fruits where s_id=101 or s_id=102;【20】查询s_id=101
8、或者s_id=102的水果供应商的f_price和f_name(用不同的方法)Select f_price,f_name from fruits where s_id in (101,102);【21】查询fruits表中s_id字段的值,返回s_id字段值且不得重复(使用distinct去重复)SELECT DISTINCT s_id FROM fruits;【22】查询fruits表的f_name字段值,并对其进行排序Select f_name from fruits order by f_name;【23】查询fruits表中的f_name和f_price字段,先按f_name排序,再按
9、f_price排序Select f_name,f_price from fruits order by f_name,f_price;【24】查询fruits表中的f_name和f_price字段,对结果按f_price降序方式排序Select f_name,f_price from fruits order by f_price desc;【25】查询fruits表,先按f_price降序排序,再按f_name字段升序排序Select * from fruits order by f_price desc,f_name;【26】根据s_id对fruits表中的数据进行分组,并显示每个分组的总
10、记录数。Select s_id,count(*) from fruits group by s_id;【27】根据s_id对fruits表中的数据进行分组,将每个供应商的水果名称显示出来(使用GROUP_CONCAT拼接字段内容)SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id;【28】根据s_id对fruits表中的数据进行分组,并显示水果种类大于1的分组信息 Select s_id as nemes from fruits group by s_id having count(f_name)1;【29】
11、根据s_id对fruits表中的数据进行分组,并显示记录数量SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;WITH ROLLUP对所有记录进行统计【30】根据s_id和f_name字段对fruits表中的数据进行分组Select * from fruits group by s_id,f_name;第三部分:创建数据表,SQL语句如下:CREATE TABLE orderitems( o_num INT NOT NULL, o_item INT NOT NULL, quantity INT NOT NU
12、LL, item_price DECIMAL(8,2) NOT NULL, PRIMARY KEY (o_num,o_item)插入数据,SQL语句如下:INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)VALUES(30001, 1, , 10, ,(30001, 2, , 3, 6),(30001, 3, , 5, ,(30001, 4, , 15, ,(30002, 1, b3, 2, ,(30003, 1, , 100, 10),(30004, 1, , 50, ,(30005, 1, , 5, 10),(30005, 2, , 10, ,(30005, 3, , 10, ,(30005, 4, , 5, ;【31】查询总订单价格大于100的订单号和总订单价格【32】显示fruits表查询结果的前4行【33】在fruits表中,使用LIMIT子句,返回从第5个记录开始的,行数长度为3的记录【34】查询customers表中总的行数【35】查询customers表中有电子邮箱的顾客的总数【36】在orderitems表中,使用COUNT()函数统计不同订单号中订购的水果种类【37】在orderitems表中查询30005号订单一共购买的水果总量【38】在or
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1