摸到 SQL 的门把手【下篇】
这里继续接上篇，学习如何利用 SQL 进行增删查改。那这篇文章就简单整理下关于 SQL 的一些笔记，课程是网易微专业的数据分析师（SQL 和 Excel）
如何在 SQL中进行查询？
首先先看 select 最基本的操作语句
- 算术操作符号
+ - * / - 比较操作符
= > < <= >= != <> !> !<
聚合类函数
-
avg按列计算平均值 -
sum按计算值的总和 -
max求一列中的最大值 -
min求一列中的最小值 -
count按列值计个数
select 语句的用法
select <目标列组>
from <数据源>
[where <元组选择条件>]
[group by <分列组> [having <组选择条件>]]
[order by <排序列1> <排序要求1> [, ...n]]
-- 对大气质量表进行有选择的查询
select city_name,avg(pm25),avg(pm10)
from Monthly_Indicator
where pm25>50
group by city_name,month_key having city_name <> '北京'
order by avg(pm25) desc;
-- 查询大气质量表的全部内容
select *
from monthly_indicator;
-- 查询北京的大气质量数据，相当于筛选北京的数据
select *
from monthly_indicator
where city_name = '北京';
-- 查询不同月份 PM2.5 的最大值，相当于数据透视表，pm2.5 找最大值
select month_key,max(pm25)
from monthly_indicator
group by month_key;
-- 降序查询不同城市 PM10 的平均值，同上，多了个排序
select city_name,avg(pm10)
from monthly_indicator
group by city_name
order by avg(pm10) desc;
注意：having 是接 group by 的，而 where 的权限需要 select 才能运行
多表查询
select <select_list> from <表1> xx join <表2> on 表1.key = 表2.key
/*
1.xx代表连接的方向，可以是 inner left right 等关键字
2.在连接语句前边的表是“左表”，在连接语句后边的表是“右表”
*/
内连接查询
-- 按照连接条件合并两个表，返回满足条件的行
select <select_list> from A
inner join B on A.key = B.key
左连接查询
-- 结果中除了包括满足连接条件的行外，还包括左表的所有行
select <select_list> from A
left join B on A.key = B.key
右链接查询
-- 结果中除了包括满足连接条件的行外，还包括右表的所有行
select <select_list> from A
right join B on A.key = B.key
举个例子
use test
-- 创建学员信息表
create table 学员信息表(
学号 varchar(5),
学员姓名 varchar(10),
年龄 int
);
-- 创建学员成绩表
create table 学员成绩表(
学号 varchar(5),
成绩 int
);
-- 为学员信息表导入数据
load data local infile 'C:/Users/35055/Desktop/xyxx.csv'
into table 学员信息表
fields terminated by ','
ignore 1 lines;
-- 为学员成绩表导入数据
load data local infile 'C:/Users/35055/Desktop/xycj.csv'
into table 学员成绩表
fields terminated by ','
ignore 1 lines;
-- 内连接
select 学员信息表.*, 学员成绩表.*
from 学员信息表 inner join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;
-- 左连接
select 学员信息表.*, 学员成绩表.*
from 学员信息表 left join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;
-- 右连接
select 学员信息表.*, 学员成绩表.*
from 学员信息表 right join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;
联合查询
union 用于合并两个或者多个 select 语句的结果集，并消去表中的任何重复行
-- 例：用 union 合并 t1 与 t2 表
select t1.* from t1
union
select t2.* from t2;
union all 用于合并两个或者多个 select 语句的结果集，保留重复行
-- 例：用 union all 合并 t1 与 t2 表
select t1.* from t1
union all
select t2.* from t2
举个例子
-- 纵向合并练习
create table t1(
key1 varchar(20),
v1 int(4)
);
load data local infile 'C:/Users/35055/Desktop/t1.csv'
into table t1
fields terminated by ','
ignore 1 lines;
create table t2(
key2 varchar(20),
v2 int(4)
);
load data local infile 'C:/Users/35055/Desktop/t2.csv'
into table t2
fields terminated by ','
ignore 1 lines;
-- 用 union 合并 t1 与 t2 表
select t1.* from t1
union
select t2.* from t2;
-- 用 union all 合并 t1 与 t2 表
select t1.* from t1
union all
select t2.* from t2;
union
union all
查询操作符与子查询是个什么东东？
首先这里会有要的例子是
-- 创建 fruits 数据表
create table fruits(
f_id char(10) not null,
s_id int not null,
f_name varchar(255) not null,
f_price decimal(8.2) not null,
primary key(f_id)
);
/*
字段说明：
f_id：水果ID
s_id：品类ID
f_name：水果名称
f_price：水果价格
*/
操作符
操作符与子查询的组合应用
子查询：在写()中，把内层查询结果当做外层查询参照的数据表来用
as 重命名与 limit 限制查询结果行数
接下来举个例子
-- 使用数据库
use test;
-- 创建fruits数据表
create table fruits(
f_id char(10) not null,
s_id int not null,
f_name varchar(255) not null,
f_price decimal(8,2) not null,
primary key(f_id)
);
-- 插入数据
insert into fruits(f_id,s_id,f_name,f_price)
values('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',25.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('b5',107,'xxxx',3.6);
-- 用 and 操作符查询 s_id 为 101 并且 f_id 为 a1 的水果记录
select * from fruits
where s_id = 101 and f_id = 'a1';
-- 用 or 操作符查询苹果或者橙子的相关记录
select * from fruits
where f_name = 'apple' or f_name = 'orange';
-- 用 in 操作符查询苹果和橙子的相关记录
select * from fruits
where f_name in('apple','orange');
-- 用 not in 操作符查询苹果和橙子之外的水果的相关记录
select * from fruits
where f_name not in('apple','orange');
-- 用 between...and 操作符查询 f_price 在 10 元到 20 元之间的水果记录
select * from fruits
where f_price between 10 and 20;
-- 用 like 操作符查询所有 f_name 由 a 开始的水果记录
select * from fruits
where f_name like 'a%';
-- 用 like 操作符查询所有 f_id 由 b 开始且字符长度为两位的水果记录
select * from fruits
where f_id like 'b_';
-- 用 is null 操作符查询所有 f_name 为空的水果记录
select * from fruits
where f_name is null;
-- 查询 fruits 表中所有不重复的 s_id
select distinct s_id from fruits;
-- 用 any 操作符与子查询语句来查询所有 f_id 对应的 f_price 在 10 元到 20 元之间的水果记录
select * from fruits where f_id = any
(select f_id from fruits where f_price between 10 and 20);
-- 用 all 操作符与子查询语句来查询所有 f_price 大于 20 元的水果记录
select * from fruits where f_price > all
(select f_price from fruits where f_price <= 20);
-- 用 exists 操作符与子查询语句来查询是否存在 f_price 大于 30 元的水果记录
select * from fruits where exists
(select * from fruits where f_price > 30);
-- 用 as 将 fruits 表名重命名为f后使用
select f.* from fruits as f;
-- 显示 f_price 金额最大的前三名水果记录
select * from fruits
order by f_price desc
limit 3;
如何在 SQL 中使用函数？
（不用会，但要有印象，而且会查）
常用的数学函数
常用的字符串函数
常用的日期使用函数
其他函数
使用字段赋值
update...set 为字段赋值，语法是 update 表名 set 字段名 = 值
-- 例：使用 concat 函数在 f_name 字段值前添加 'fruit_' 信息
update fruits set f_name = concat('fruit_',f_name);
删除数据
delete 删除数据表中的数据，语法为 delete from 表名 [where clause]，如果省略 where 的话则删除表中所有数据记录。
-- 例：删除 f_id 为 'b5' 的数据记录
delete from fruits where f_id = 'b5';
例子
-- 使用数据库
use test;
-- 使用 abs 函数求所有水果平均值与最大值差值的绝对值
select abs(avg(f_price)-max(f_price)) from fruits;
-- 使用 length 函数求每个 f_name 的名字与他们的字符长度
select f_name, length(f_name) from fruits group by f_name;
-- 使用 now 函数求当前的日期和时间
select now();
-- 使用 group_concat 函数查询不同 s_id 下对应的所有 f_name 信息
select s_id, group_concat(f_name) from fruits
group_by s_id;
-- 使用 concat 函数在 f_name 字段值前添加 'fruit_' 信息
update fruits set f_name = concat('fruit_',f_name);
select * from fruits;
-- 删除 f_id 为 'b5' 的数据记录
delete from fruits where f_id = 'b5';
select * from fruits;
