摸到 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;