MySQL高级SQL语句
围绕两张表
Location表
Store_Info表
#select选择
SELECT Store_Name FROM Store_Info;
#distinct去重
select distinct 列名 from 表名
#where条件查询
select distinct 列名 from 表名 where
#and且 or或
语法：SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
SELECT Store_Name FROM Store_Info WHERE Sales > 1000 OR (Sales < 500 AND Sales > 200);
#in显示已知的
not in ≈取反
语法：SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);
SELECT * FROM Store_Info WHERE Store_Name IN ('Los Angeles', 'Houston');
#between两个值范围内的数据记录
select * from store_info where between 300 and 1000;
300≤结果≤1000
#通配符——配合like使用
%：百分号表示零个、一个或多个字符
_：下划线表示单个字符
#like
语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};
SELECT * FROM Store_Info WHERE Store_Name like '%os%';
#order by
asc升序
desc降序
语法：SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序。
SELECT Store_Name,Sales,Date FROM Store_Info ORDER BY Sales DESC; 函数
数学函数：
abs(x) 返回 x 的绝对值
rand() 返回 0 到 1 的随机数
mod(x,y) 返回 x 除以 y 以后的余数
power(x,y) 返回 x 的 y 次方
round(x) 返回离 x 最近的整数
round(x,y) 保留 x 的 y 位小数四舍五入后的值
sqrt(x) 返回 x 的平方根
truncate(x,y) 返回数字 x 截断为 y 位小数的值
ceil(x) 返回大于或等于 x 的最小整数
floor(x) 返回小于或等于 x 的最大整数
greatest(x1,x2...) 返回集合中最大的值，也可以返回多个字段的最大的值
least(x1,x2...) 返回集合中最小的值，也可以返回多个字段的最小的值
SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
聚合函数：
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum(x) 返回指定列的所有值之和
SELECT avg(Sales) FROM Store_Info;
SELECT count(Store_Name) FROM Store_Info;
SELECT count(DISTINCT Store_Name) FROM Store_Info;
SELECT max(Sales) FROM Store_Info;
SELECT min(Sales) FROM Store_Info;
SELECT sum(Sales) FROM Store_Info; 字符串函数
trim() 返回去除指定格式的值
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x) 返回字符串 x 的长度
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
upper(x) 将字符串 x 的所有字母变成大写字母
lower(x) 将字符串 x 的所有字母变成小写字母
left(x,y) 返回字符串 x 的前 y 个字符
right(x,y) 返回字符串 x 的后 y 个字符
repeat(x,y) 将字符串 x 重复 y 次
space(x) 返回 x 个空格
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
reverse(x) 将字符串 x 反转
a=12345678
echo $(a:6:3)
echo $(#a) GROUP BY配合“聚合函数”一起使用
HAVING 对group by汇总后的结果做筛选select store_name ,sum(store_name) from store_info group by store_name having sum(store_name) >=2
连接查询
select * from location as A inner join store_info as B on A.store_name = B.store_name;
表连接
inner join 内连接，只返回两个表的字段相等的行记录
left join 左连接，返回左表所有的行记录和右表字段相等的行记录，不相等的行返回null
right join 右连接，返回右表所有的行记录和左表字段相等的行记录，不相等的行返回null
union 联集，将两个select查询语句的结果合并，并去重
union all 联集，将两个select查询语句的结果合并，不去重
求交集
select A.字段 from 左表 A inner join 右表 B
数据库模式：
create database kgc;
show databases;
use kgc;
create table location (Region char(20),Store_Name char(20));
desc location;
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');
select * from location;
create table store_info (Store_Name char(20),Sales int(10),Date char(10));
desc store_info;
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');
select * from store_info;
select * from store_info where store_name='Los Angeles';
select * from store_info where sales <= 1000;
select * from store_info where sales >= 1000;
select * from store_info where sales != 1500;
select * from store_info where sales > 1000 or (sales < 500 and sales > 200);
select * from store_info where Store_Name in ('Los Angeles', 'Houston');
select * from store_info where date between '2020-12-06' and '2020-12-10';
select * from store_info where store_name like '%os%';
select store_name,sales,date from store_info order by sales desc;
select sum(sales) from store_info;
select min(sales) from store_info;
select max(sales) from store_info;
select avg(sales) from store_info;
select count(sales) from store_info;
select concat('abc','123');
select concat('abc',' ','123');
select * from location;
select * from store_info;
select * from location where store_name='New York';
select concat(Region,store_name) from location where store_name='New York';
select concat(Region,'+',store_name) from location where store_name='New York';
select region || store_name from location;
select region || ' ' || store_name from location;
select substr(store_name,5) from location where store_name='Los Angeles';
select substr(store_name,5,6) from location where store_name='Los Angeles';
select store_name, sum(sales) from store_info group by store_name order by sales desc;
select store_name, sum(sales) from store_info group by store_name having sum(sales) > 1500;
select a.store_name store, sum(a.sales) "total sales" from store_info a group by a.store_name;
select sum(sales) from store_info where store_name in (select store_name from location where region = 'west');
select sum(a.sales) from store_info a where a.store_name in (select store_name from location b where b.store_name = a.store_name);
select sum(sales) from store_info where exists (select * from location where region = 'West');
update store_info set store_name='Washington' WHERE sales=300;
select * from store_info;
select * from location a right join store_info b on a.store_name = b.store_name ;
select * from location a left join store_info b on a.store_name = b.store_name ;
select * from location a inner join store_info b on a.store_name = b.store_name ;
select * from location a, store_info b where a.store_name = b.store_name;
select a.region region, sum(b.sales) sales from location a, store_info b where a.store_name = b.store_name group by region;
select store_name from location union select store_name from store_info;
select store_name from location union all select store_name from store_info;
select A.store_name from location A inner join store_info B ON A.store_name = B.store_ore_name;
select A.store_name from location A inner join store_info B using(store_name);