一、高级SQL语句(进阶查询)
先准备2个表
一个location表:
use market;
create table location(Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');
复制代码
一个store_info表:
create table store_info (Store_Name char (20),Sales int (10) ,Date char(10));
insert into store_info values('Los Angeles', '1500', '2020-12-05') ;
insert into store_info values ('Houston', '250', '2020-12-07') ;
insert into store_info values('Los Angeles', '300', '2020-12-08') ;
insert into store_info values('Boston', '700', '2020-12-08') ;
复制代码
1.1 select
select,显示表格中一个或数个字段的所有数据记录。
语法: SELECT "字段" FROM "表名";
示例:
select store_name from store_info;
select store_name,sales from store_info;
复制代码
1.2 distinct
distinct,不显示重复的数据记录。
注意:关系型数据库,单次只能对一个字段去重。
SELECT DISTINCT "字段" FROM "表名";
示例:
select distinct store_name from store_info; #对商店名称进行去重
select distinct date from store_info; #对日期字段进行去重
复制代码
1.3 where
where,有条件查询。
SELECT "字段" FROM 表名" WHERE "条件";
示例:
select * from store_info where sales > 1000; #查找销售额大于1000的数据记录
select store_name from store_info where sales > 1000; #查找销售额大于1000的商店名称
复制代码
1.4 and 和 or
and:且。
or:或。
SELECT "字段" FROM "表名" WHERE "条件1" AND "条件2";
SELECT "字段" FROM "表名" WHERE "条件1" OR "条件2";
复制代码
示例:
#查询销售额大于500且小于1000的数据记录。
select * from store_info where sales > 500 and sales < 1000 ;
#查询销售额大于200且小于500的记录,或者销售额大于1000的记录。
select * from store_info where sales > 1000 or (Sales < 500 and Sales > 200);
#查询销售额大于200且小于500的商店名,或者销售额大于1000的商店名。
select store_name from store_info where sales > 1000 or (Sales < 500 and Sales > 200);
复制代码
1.5 in
in,显示已知的值的数据记录。
SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...); #in,遍历一个取值列表
SELECT "字段" FROM "表名" WHERE "字段" NOT IN ('值1', '值2', ...); #not in取反,查询不在这个范围内的值
复制代码
示例:
#查询商店名为“Los Angeles”,以及商店名为“Houston”的记录。
select * from store_info where store_name in ('Los Angeles','Houston');
#查询商店名称不是“Los Angeles”、不是“Houston”的记录。
select * from store_info WHERE store_name not in ('Los Angeles','Houston');
复制代码
1.6 between
between,显示两个值范围内的数据记录。
SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';
复制代码
示例:
#查询销售额在500~1