以下内容为本人上课笔记,如有不对请指出回复。谢谢!!!
1.查找所有以‘b’字母开头的水果,输入如下语句:
Select f_name from fruit where f_name like ‘b%’;
2.在fruits表中,查询f_name中包含字母‘g’的记录
Select * from ruits where f_name like ‘%g%’;
3.查询以‘b’开头,并以‘y’结尾的水果的名称
Select * from fruit where f_name like ‘b%y’;
4.在fruits表中,查询名称以字母‘y’结尾,且‘y’前面只有4个字母的记录
Select * from fruit where f_name like ‘____y’;
5.查询customers表中c_email为空的记录的c_id、c_name和c_email字段值:
Select c_id,c_name,c_email from customers where c_email is null;
6.查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值
在fruits表中查询s_id = ‘101’,并且f_price大于5的记录价格和名称
Select f_price,f_name from fruits where s_id=’101’ and f_price >5;
7.在fruits表中查询s_id = ‘101’或者’102’,并且f_price大于5,并且f_name=’apple’的记录价格和名称
Select f_price,f_name from fruits where (s_id=’101’ or s_id =’102’) and f_price>5 and f_name=’apple’
8.查询fruits表中s_id字段的值,并返回s_id字段值不得重复
Select distinct s_id from fruits;
9.查询fruits表的f_name字段值,并对其进行排序(asc /desc)
select f_name from fruits ORDER BY f_name;
10.查询fruits表中的f_name和f_price字段,先按f_name排序,再按f_price排序
Select f_name from fruits group by f_name desc;
Select * from fruits order by f_name asc,f_price desc;
11.查询fruits表中的f_name和f_price字段,对结果按f_price降序方式排序
Select f_price,f_name from fruits group by f_price desc;
Select f_name,f_price from fruits order by f_price desc;
12.查询fruits表,先按f_price降序排序,再按f_name字段升序排序,SQL语句如下:
Select * from fruit order by f_price desc;
Select * from fruit order by f_name asc;
13.通过orderitems表提供的f_id查询fruit表中水果名称含有a的水果记录;
select f_name from fruits where f_id in(select f_id from orderitems ) and f_name like ‘%a%’;
----------------------------分组查询(GROUP BY )---------------------------------------------------
14.根据s_id对fruits表中的数据进行分组,将每个供应商的水果名称显示出来
Select s_id,group_concat(f_name) as names from fruits group by s_id;
15.根据s_id对fruits表中的数据进行分组,并显示水果种类大于1的分组信息
Select s_id, group_concat(f_name)as names from fruits group by s_id h
16.根据s_id对fruits表中的数据进行分组,并显示记录数量
select s_id,count(*) as total from fruits group by s_id with rollup;
17.根据s_id和f_name字段对fruits表中的数据进行分组, SQL语句如下,
Select * from fruits group by s_id f_name;
18.查询订单价格(订单数量*每单价格)大于100的订单号(o_num)和总订单价格
Select o_num,sum(quantity * item_price) as order total
From ordertotal group by o_num having sum(quantity * item_price)>100 order by order tot
Select o_num,sum(quantity * item_price) as order total from orderitems group by o_num huaing sum(quantity * item_price)>100 order by order total;
19.显示fruits表查询结果的前4行,输入如下语句:
Select * from fruits limint 4;
Select * from fruits limint 0,4;
20.在fruits 表中,使用LIMIT子句,返回从第5个记录开始的,行数长度为3的记录
select * from fruits limit 4,3;
21.查询customers表中总的行数
Select count(*) as cust_num from customers;
22.查询customers表中有电子邮箱的顾客的总数,输入如下语句:
Select count(c_email) as email_num from customers;
23.在orderitems表中,使用COUNT()函数统计不同订单号中订购的水果种类
Select o_num,count(f_id) from orderitems group by o_num;
24.在orderitems表中查询30005号订单一共购买的水果总量,输入如下语句:
select sum(quantity) as items_total from orderitems where o_num=30005;
25.在orderitems表中,使用SUM()函数统计不同订单号中订购的水果总量
select o_num ,sum(quantity) as items_total from orderitems group by o_num;
26.在fruits表中,查询s_id=103的供应商的水果价格的平均值,SQL语句如下:
select avg(f_price) as avg_price from fruits where s_id=103
27.在fruits表中,查询每一个供应商的水果价格的平均值,SQL语句下:
select s_id,avg(f_price) as avg_price from fruits group by s_id;
28.在fruits表中查找市场上价格最高的水果,SQL语句如下:
select max(f_price)as max_price from fruits;
29.在fruits表中查找不同供应商提供的价格最高的水果
select s_id,max(f_price) as max_price from fruits group by s_id;
30.在fruits表中查找f_name的最大值,SQL语句如下
select max(f_name) from fruits;
31.在fruits表中查找市场上价格最低的水果,SQL语句如下:
select min(f_price) as min_price from fruits;
32.在fruits表中查找不同供应商提供的价格最低的水果
select s_id ,min(f_price) as min_price from fruits group by s_id;
33.在fruits表和suppliers表之间使用内连接查询,查询供应商供应的水果信息。查询之前,查看两个表的结构。
查看表的结构:desc fruits;
select suppliers.s_id,s_name,f_name,f_price from fruits,suppliers where fruits.s_id=suppliers.s_id;
34.在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询
Select suppliers . s_id s_name,f_name,f-price form fruits inner
select suppliers.s_id,s_name,f_name,f_price from fruits inner join suppliers on fruits.s_id=suppliers.s_id;
35.在customers表和orders表中,查询所有客户,包括没有订单的客户,SQL语法
select customers.c_id,orders.o_num from customers left outer join orders on customers.c_id=orders.c_id;
36.在customers表和orders表中,查询所有订单,包括没有客户的订单
select customers.c_id,orders.o_num from customers right outer join orders on customers.c_id=orders.c_id;
37.在customers表和orders表中,使用INNER JOIN语法查询customers表中ID为10001、的客户的订单信息
Select customers.c-id,order.o_num from customers
select customers inner join orders on customers.c_id =orders.c_id and customers.c_id=10001;
38.在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询,并对查询结果排序
select suppliers.s_id,s_name,f_name,f_price from fruits join suppliers on fruits.s_id=suppliers.s_id order by fruits.s_id;
39.定义两个表tb1和tb2
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);
40.向两个表中插入数据,
INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);
41.返回tbl2表的所有 num2 列,然后将 tbl1 中的 num1 的值与之进行比较,只要大于 num2的某一个值为符合查询条件的结果。
select num1 from tab|1 where num1>any (select num2 from tab|2);
42.返回tbl1表的中比tbl2表num2 列所有值都大的值
select num1 from tab|1 where num1>all(select num2 from tab|2);
43.查询表suppliers表中是否存在s_id=107的供应商,如果存在则查询fruits表中的记录
select * from fruits where exists(select s_name from suppliers where s_id=107);
44.查询表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 );
45.查询表suppliers表中是否存在s_id=107的供应商,如果不存在则查询fruits表中的记录
select * from fruits where not exists(select s_name from suppliers where s_id=107);
46.在orderitems表中查询订购f_id为c0的订单号(o_num),并根据订单号(o_num)在订单表(orders表)查询具有订单号的客户c_id
select c_id from orders where o_num in (select o_num from orderitems where f_id ='c0');
47.与前一个例子语句类似,但是在SELECT语句中使用NOT IN操作符
select c_id from orders where o_num not in(select o_num from orderitems where f_id='c0');
48.在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');
49.在suppliers表中查询s_city等于Tianjin的供应商s_id,然后在fruits表中查询所有非该供应商提供的水果的种类,SQL语句如下,
select s_id,f_name from fruits where s_id ! =(select s1.s_id from suppliers as s1 where s1.s_city='Tianjin');
50.查询所有价格小于9的水果的列表,查询s_id等于101和103所有的水果种类,使用UNION连接查询结果
select s_id,f_name,f_price from fruits where f_price < 9 ;
51.查询所有价格小于9的水果的列表,查询s_id等于101和103所有的水果种类,使用UNION ALL连接查询结果,SQL语句如下
select s_id,f_name,f_price from fruits where f_price < 9 ;
52.为orders表取别名o,查询订30001订单的下单日期
select * from orders as where o.o_num=30001;
53.为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;
54.查询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;
55.查询suppliers表中字段s_name和s_city,使用CONCAT函数连接这个两个字段值,并取列别名为suppliers_title。
如果没有对连接后的值取别名,其显示列名称将会不够直观,输入如下SQL,
select concat(rtrim(s_name),'(',rtrim(s_city), ')') from suppliers order by s_name;
56.在fruits表中,查询f_name字段以字母‘b’开头的记录
select * from fruits where f_name regexp '^b';
57.在fruits表中,查询f_name字段以“be”开头的记录
select * from fruits where f_name regexp '^be';
58.在fruits表中,查询f_name字段以字母‘t’结尾的记录
select * from fruits where f_name regexp 't$';
59.在fruits表中,查询f_name字段以字符串“rry”结尾的记录
select * from fruits where f_name regexp 'rry$';
60.在fruits表中,查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录
select * from fruits where f_name regexp 'a.g';
select * from fruits where f_name like '%a_g%';
61.在fruits表中,查询f_name字段值以字母‘b’开头,且‘b’后面出现字母‘a’的记录
select * from fruits where f_name regexp '^ba*';
62.在fruits表中,查询f_name字段值以字母‘b’开头,且‘b’后面出现字母‘a’至少一次的记录
select * from fruits where f_name regexp '^ba+';
63.在fruits表中,查询f_name字段值包含字符串“on”的记录
select * from fruits where f_name regexp 'on ';
64.在fruits表中,查询f_name字段值包含字符串“on”或者“ap”的记录
select * from fruits where f_name regexp 'on | ap';
65.在fruits表中,使用LIKE运算符查询f_name字段值为“on”的记录
select * from fruits where f_name like 'on';
66.在fruits表中,查找f_name字段中包含字母o或者t的记录
select * from fruits where f_name like '[ot]';
67.在fruits表,查询s_id字段中数值中包含4、5或者6的记录
select * from fruits where s_id regexp'[456]';
68.在fruits表中,查询f_id字段包含字母a到e和数字1到2以外的字符的记录
select * from fruits where f_id regexp '[^a-e 1-2]';
69.在fruits表中,查询f_name字段值出现字符串‘x’至少2次的记录
select * from fruits where f_name regexp'x{2,}';
70.在fruits表中,查询f_name字段值出现字符串“ba”最少1次,最多3次的记录
select * from fruits where f_name regexp'ba{1,3}';