摸到 SQL 的門把手【下篇】
這里繼續(xù)接上篇，學(xué)習(xí)如何利用 SQL 進(jìn)行增刪查改。那這篇文章就簡單整理下關(guān)于 SQL 的一些筆記，課程是網(wǎng)易微專業(yè)的數(shù)據(jù)分析師（SQL 和 Excel）
如何在 SQL中進(jìn)行查詢？
首先先看 select 最基本的操作語句
- 算術(shù)操作符號(hào)
+ - * / - 比較操作符
= > < <= >= != <> !> !<
聚合類函數(shù)
-
avg按列計(jì)算平均值 -
sum按計(jì)算值的總和 -
max求一列中的最大值 -
min求一列中的最小值 -
count按列值計(jì)個(gè)數(shù)
select 語句的用法
select <目標(biāo)列組>
from <數(shù)據(jù)源>
[where <元組選擇條件>]
[group by <分列組> [having <組選擇條件>]]
[order by <排序列1> <排序要求1> [, ...n]]
-- 對(duì)大氣質(zhì)量表進(jì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;
-- 查詢大氣質(zhì)量表的全部內(nèi)容
select *
from monthly_indicator;
-- 查詢北京的大氣質(zhì)量數(shù)據(jù)，相當(dāng)于篩選北京的數(shù)據(jù)
select *
from monthly_indicator
where city_name = '北京';
-- 查詢不同月份 PM2.5 的最大值，相當(dāng)于數(shù)據(jù)透視表，pm2.5 找最大值
select month_key,max(pm25)
from monthly_indicator
group by month_key;
-- 降序查詢不同城市 PM10 的平均值，同上，多了個(gè)排序
select city_name,avg(pm10)
from monthly_indicator
group by city_name
order by avg(pm10) desc;
注意：having 是接 group by 的，而 where 的權(quán)限需要 select 才能運(yùn)行
多表查詢
select <select_list> from <表1> xx join <表2> on 表1.key = 表2.key
/*
1.xx代表連接的方向，可以是 inner left right 等關(guān)鍵字
2.在連接語句前邊的表是“左表”，在連接語句后邊的表是“右表”
*/
內(nèi)連接查詢
-- 按照連接條件合并兩個(gè)表，返回滿足條件的行
select <select_list> from A
inner join B on A.key = B.key
左連接查詢
-- 結(jié)果中除了包括滿足連接條件的行外，還包括左表的所有行
select <select_list> from A
left join B on A.key = B.key
右鏈接查詢
-- 結(jié)果中除了包括滿足連接條件的行外，還包括右表的所有行
select <select_list> from A
right join B on A.key = B.key
舉個(gè)例子
use test
-- 創(chuàng)建學(xué)員信息表
create table 學(xué)員信息表(
學(xué)號(hào) varchar(5),
學(xué)員姓名 varchar(10),
年齡 int
);
-- 創(chuàng)建學(xué)員成績表
create table 學(xué)員成績表(
學(xué)號(hào) varchar(5),
成績 int
);
-- 為學(xué)員信息表導(dǎo)入數(shù)據(jù)
load data local infile 'C:/Users/35055/Desktop/xyxx.csv'
into table 學(xué)員信息表
fields terminated by ','
ignore 1 lines;
-- 為學(xué)員成績表導(dǎo)入數(shù)據(jù)
load data local infile 'C:/Users/35055/Desktop/xycj.csv'
into table 學(xué)員成績表
fields terminated by ','
ignore 1 lines;
-- 內(nèi)連接
select 學(xué)員信息表.*, 學(xué)員成績表.*
from 學(xué)員信息表 inner join 學(xué)員成績表 on 學(xué)員信息表.學(xué)號(hào) = 學(xué)員成績表.學(xué)號(hào);
-- 左連接
select 學(xué)員信息表.*, 學(xué)員成績表.*
from 學(xué)員信息表 left join 學(xué)員成績表 on 學(xué)員信息表.學(xué)號(hào) = 學(xué)員成績表.學(xué)號(hào);
-- 右連接
select 學(xué)員信息表.*, 學(xué)員成績表.*
from 學(xué)員信息表 right join 學(xué)員成績表 on 學(xué)員信息表.學(xué)號(hào) = 學(xué)員成績表.學(xué)號(hào);
聯(lián)合查詢
union 用于合并兩個(gè)或者多個(gè) select 語句的結(jié)果集，并消去表中的任何重復(fù)行
-- 例：用 union 合并 t1 與 t2 表
select t1.* from t1
union
select t2.* from t2;
union all 用于合并兩個(gè)或者多個(gè) select 語句的結(jié)果集，保留重復(fù)行
-- 例：用 union all 合并 t1 與 t2 表
select t1.* from t1
union all
select t2.* from t2
舉個(gè)例子
-- 縱向合并練習(xí)
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
查詢操作符與子查詢是個(gè)什么東東？
首先這里會(huì)有要的例子是
-- 創(chuàng)建 fruits 數(shù)據(jù)表
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：水果價(jià)格
*/
操作符
操作符與子查詢的組合應(yīng)用
子查詢：在寫()中，把內(nèi)層查詢結(jié)果當(dāng)做外層查詢參照的數(shù)據(jù)表來用
as 重命名與 limit 限制查詢結(jié)果行數(shù)
接下來舉個(gè)例子
-- 使用數(shù)據(jù)庫
use test;
-- 創(chuàng)建fruits數(shù)據(jù)表
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)
);
-- 插入數(shù)據(jù)
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 操作符查詢蘋果或者橙子的相關(guān)記錄
select * from fruits
where f_name = 'apple' or f_name = 'orange';
-- 用 in 操作符查詢蘋果和橙子的相關(guān)記錄
select * from fruits
where f_name in('apple','orange');
-- 用 not in 操作符查詢蘋果和橙子之外的水果的相關(guān)記錄
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 表中所有不重復(fù)的 s_id
select distinct s_id from fruits;
-- 用 any 操作符與子查詢語句來查詢所有 f_id 對(duì)應(yīng)的 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 中使用函數(shù)？
（不用會(huì)，但要有印象，而且會(huì)查）
常用的數(shù)學(xué)函數(shù)
常用的字符串函數(shù)
常用的日期使用函數(shù)
其他函數(shù)
使用字段賦值
update...set 為字段賦值，語法是 update 表名 set 字段名 = 值
-- 例：使用 concat 函數(shù)在 f_name 字段值前添加 'fruit_' 信息
update fruits set f_name = concat('fruit_',f_name);
刪除數(shù)據(jù)
delete 刪除數(shù)據(jù)表中的數(shù)據(jù)，語法為 delete from 表名 [where clause]，如果省略 where 的話則刪除表中所有數(shù)據(jù)記錄。
-- 例：刪除 f_id 為 'b5' 的數(shù)據(jù)記錄
delete from fruits where f_id = 'b5';
例子
-- 使用數(shù)據(jù)庫
use test;
-- 使用 abs 函數(shù)求所有水果平均值與最大值差值的絕對(duì)值
select abs(avg(f_price)-max(f_price)) from fruits;
-- 使用 length 函數(shù)求每個(gè) f_name 的名字與他們的字符長度
select f_name, length(f_name) from fruits group by f_name;
-- 使用 now 函數(shù)求當(dāng)前的日期和時(shí)間
select now();
-- 使用 group_concat 函數(shù)查詢不同 s_id 下對(duì)應(yīng)的所有 f_name 信息
select s_id, group_concat(f_name) from fruits
group_by s_id;
-- 使用 concat 函數(shù)在 f_name 字段值前添加 'fruit_' 信息
update fruits set f_name = concat('fruit_',f_name);
select * from fruits;
-- 刪除 f_id 為 'b5' 的數(shù)據(jù)記錄
delete from fruits where f_id = 'b5';
select * from fruits;
