【SQL基础学习

【SQL基础学习

SQL学习基础与语法-01

1.between and

两种方式
1.between 在列值得某与某之间
select
device_id,
gender,
age,
university
from user_profile
WHERE
age between 20 and 232.用and的来连接条件范围
SELECT
device_id,
gender,
age
from user_profile
WHERE
age >= 20 and age<=23

SELECTdevice_id,gender,age,university
FROMuser_profile
WHEREuniversity NOT IN ('复旦大学')

3.空值过滤

解法一：
select device_id,gender,age,university
from user_profile
where age is not NULL;
解法二：
select device_id,gender,age,university
from user_profile
where age !='';

4.where in，not in

4.1not in用法

SELECTdevice_id,gender,age,university,gpa
FROMuser_profile
WHEREuniversity NOT IN ('浙江大学')

4.2where in用法

SELECTdevice_id,gender,age,university,gpa
FROMuser_profile
WHEREuniversity IN ('北京大学', '复旦大学', '山东大学')

5.通配符

一般形式为：
列名 [NOT ] LIKE
匹配串中可包含如下四种通配符：
_：匹配任意一个字符；
%：匹配0个或多个字符；
[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；
[^ ]：不匹配[ ]中的任意一个字符。

# 1.查询姓张的学生
SELECT * FROM 学生表 WHERE 姓名 LIKE '张%'
# 2.查询姓张并且名字为3个字的
SELECT * FROM 学生表 WHERE 姓名 LIKE '张__'
# 3.如果把姓名列的类型改为nchar(20)，在SQL Server 2012中执行没有结果。
# 原因是姓名列的类型是char(20)，当姓名少于20个汉字时，系统在存储这些数据时自动在后边补空格，
# 空格作为一个字符，也参加LIKE的比较。可以用rtrim()去掉右空格。
# 4.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况
SELECT * FROM 学生表 WHERE rtrim(姓名) LIKE '张__'
# 5.查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。
SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%'
# 6.查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号。
SELECT 姓名,学号 FROM 学生表 WHERE 姓名 LIKE '_[小大]%'
# 7.查询学生表中所有不姓“刘”的学生
SELECT 姓名 FROM 学生 WHERE 姓名 NOT LIKE '刘%'
# 8.从学生表表中查询学号的最后一位不是2、3、5的学生信息。
SELECT * FROM 学生表 WHERE 学号 LIKE '%[^235]'
SELECT device_id,age,university FROM user_profile
WHERE university LIKE '%北京%'

6.limit用法

# LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
# LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。
# 如果只给定一个参数，它表示返回最大的记录行数目。
# 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
# 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。
# 初始记录行的偏移量是 0(而不是 1)。
# 1.检索记录行 [6-7]
SELECT * FROM table LIMIT 5,2
# 2.检索前 5 个记录行[3-7]5条数据
SELECT * FROM user_profile  LIMIT 5 OFFSET 2
# 3.检索记录行 11-last
SELECT * FROM table LIMIT 10,-1
# 4.检索前 5 个记录行
SELECT * FROM table LIMIT 5

7.去重distinct,group by

# 去重两种思路
# 第一种：使用distinct(关键词distinct用于返回唯一不同的值)
select distinct university from user_profile;
# 第二种：使用group by按照university进行分组
select university from user_profile group by university;

8.max()，min()

# 方法1
selectmax(gpa) as gpa
fromuser_profile
whereuniversity = '复旦大学';# 方法2
selectgpa
fromuser_profile
whereuniversity = '复旦大学'
order bygpa desc
limit1

9.avg(),round(),count()

# 表头重命名，用as语法
# 浮点数的平均值可能小数点位数很多，按照示例保存一位小数，用round函数
select count(gender) as male_num,round(avg(gpa), 1) as avg_gpa
from user_profile where gender="male";

10.聚合函数having过滤

selectuniversity,avg(question_cnt) as avg_question_cnt,avg(answer_cnt) as avg_answer_cnt
fromuser_profile
group byuniversity
havingavg_question_cnt < 5 or avg_answer_cnt < 20

11.分组排序

selectuniversity,avg(question_cnt) as avg_question_cnt
fromuser_profile
group byuniversity
order byavg_question_cnt

12.left join,right join,inner join

left join(左联接) ——返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接)——返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接)—— 只返回两个表中联结字段相等的行

# 1.子查询
selectdevice_id,question_id,result
fromquestion_practice_detail
wheredevice_id IN (selectdevice_idfromuser_profilewhereuniversity = '浙江大学')
#  2.多表连接 
selectq.device_id,question_id,result
fromquestion_practice_detail qinner join user_profile u on q.device_id = u.device_id
whereuniversity = '浙江大学'selectuniversity,count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt
fromquestion_practice_detail as qpdinner join user_profile as up on qpd.device_id = up.device_id
group byuniversity

13.三张表关联（不推荐，性能不佳）

selectuniversity,difficult_level,round(count(qpd.question_id) / count(distinct qpd.device_id),4) as avg_answer_cnt
fromquestion_practice_detail as qpdleft join user_profile as up on up.device_id = qpd.device_idleft join question_detail as qd on qd.question_id = qpd.question_id
group byuniversity,difficult_level# 第一段：select选择字段，含原生字段及构造字段，其中构造的过程中常用sum count round +-*/ 等函数及运算表达式
selectuniversity,difficult_level,round(count(qpd.question_id) / count(distinct qpd.device_id),4) as avg_answer_cnt# 第二段：from选择表格，含合并表格操作
from question_practice_detail as qpdjoin user_profile as upon up.device_id=qpd.device_idjoin question_detail as qdon qd.question_id=qpd.question_id# 第三段：数据操作段，含 分组/排序/筛选/取指定行数等操作
group by university,difficult_level;

14.union，union all(去重，不去重)

分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

# 结果不去重就用union all ， 去重就用union
select device_id,gender,age,gpa
fromuser_profile
whereuniversity = '山东大学'
union all
selectdevice_id,gender,age,gpa
fromuser_profile
wheregender = 'male'

15.case函数、if（）函数

CASE函数
是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：
简单CASE函数
搜索CASE函数
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

  #case函数SELECT学号,课程号,CASEWHEN 成绩 >= 90 THEN '优'WHEN 成绩 BETWEEN 80 AND 89 THEN '良'WHEN 成绩 BETWEEN 70 AND 79 THEN '中'WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'WHEN 成绩 < 60 THEN '不及格'END 成绩FROM成绩表WHERE课程号 = 'M01F011'SELECTCASEWHEN age < 25 OR age IS NULL THEN '25岁以下'WHEN age >= 25 THEN '25岁及以上'END age_cut,COUNT(*) numberFROMuser_profileGROUP BYage_cut
#if()函数
SELECTIF(age >= 25, "25岁及以上", "25岁以下") AS age_cut,count(*) AS number
FROMuser_profile
GROUP BYage_cut;

16.日期函数

DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。select DAYOFWEEK('1998-02-03')
-> 3WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5DAYOFMONTH(date)
返回date的月份中日期，在1到31范围内。mysql> select DAYOFMONTH('1998-02-03');
-> 3DAYOFYEAR(date)
返回date在一年中的日数, 在1到366范围内。mysql> select DAYOFYEAR('1998-02-03');
-> 34MONTH(date)
返回date的月份，范围1到12。mysql> select MONTH('1998-02-03');
-> 2DAYNAME(date)
返回date的星期名字。mysql> select DAYNAME("1998-02-05");
-> 'Thursday'MONTHNAME(date)
返回date的月份名字。mysql> select MONTHNAME("1998-02-05");
-> 'February'QUARTER(date)
返回date一年中的季度，范围1到4。mysql> select QUARTER('98-04-01');
-> 2WEEK(date)
对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。mysql> select WEEK('1998-02-20');
-> 7WEEK(date,first)
2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。mysql> select WEEK('1998-02-20',0);
-> 7mysql> select WEEK('1998-02-20',1);
-> 8YEAR(date)
返回date的年份，范围在1000到9999。mysql> select YEAR('98-02-03');
-> 1998HOUR(time)
返回time的小时，范围是0到23。mysql> select HOUR('10:05:03');
-> 10MINUTE(time)
返回time的分钟，范围是0到59。mysql> select MINUTE('98-02-03 10:05:03');
-> 5SECOND(time)
回来time的秒数，范围是0到59。mysql> select SECOND('10:05:03');
-> 3PERIOD_ADD(P,N)
增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。mysql> select PERIOD_ADD(9801,2);
-> 199803PERIOD_DIFF(P1,P2)
返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。mysql> select PERIOD_DIFF(9802,199703);
-> 11

selectday(date) as day,count(question_id) as question_cnt
fromquestion_practice_detail
wheremonth(date) = 8and year(date) = 2021
group bydate

16.lag()和lead()函数用法

<窗口函数> OVER ([PARTITION BY <用于分组的列>] ORDER BY <用于排序的列>)
lag和lead分别是向前向后的意思
参数有三个。expression：列名；offset：偏移量；default_value：超出记录窗口的默认值(默认为null，可以设置为0)

#lag和lead分别是向前向后的意思
#参数有三个。
#expression：列名；offset：偏移量；default_value：超出记录窗口的默认值(默认为null，可以设置为0)
select * from weather
#将温度向后退一行
SELECTid,date,temperature,LAG( temperature, 1, 0 ) OVER ( ORDER BY date ) AS temp 
FROMweather;
#查询出当天比前天温度高的id
SELECTid 
FROM(SELECT id, date, temperature, LAG( temperature, 1, 0) OVER ( ORDER BY date ) AS temp FROM weather) as tmp 
WHEREtemperature > temp AND temp != 0;
#将温度向前移一行
SELECTid,date,temperature,lead( temperature, 1, 0 ) OVER ( ORDER BY date ) AS temp 
FROMweather;
#查询出当天比后一天温度高的id
SELECTid 
FROM(SELECT id, date, temperature, lead( temperature, 1, 0) OVER ( ORDER BY date ) AS temp FROM weather) as tmp 
WHEREtemperature > temp AND temp != 0;select count(date2) / count(date1) as avg_ret
from (selectdistinct qpd.device_id,qpd.date as date1,uniq_id_date.date as date2from question_practice_detail as qpdleft join(select distinct device_id, datefrom question_practice_detail) as uniq_id_dateon qpd.device_id=uniq_id_date.device_idand date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_dateselect avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (selectdistinct device_id,date as date1,lead(date) over (partition by device_id order by date) as date2from (select distinct device_id, datefrom question_practice_detail) as uniq_id_date
) as id_last_next_date

#group by去重
SELECTid,date,sum( temperature ) 
FROMweather 
GROUP BYtemperature #partition by不去重，组内排序PARTITION BY temperature ORDER BY date
SELECTid,date,temperature,sum( temperature ) over ( PARTITION BY temperature ORDER BY date ) 
FROMweatherselect count(date2) / count(date1) as avg_ret
from (selectdistinct qpd.device_id,qpd.date as date1,uniq_id_date.date as date2from question_practice_detail as qpdleft join(select distinct device_id, datefrom question_practice_detail) as uniq_id_dateon qpd.device_id=uniq_id_date.device_idand date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_dateselect avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (selectdistinct device_id,date as date1,lead(date) over (partition by device_id order by date) as date2from (select distinct device_id, datefrom question_practice_detail) as uniq_id_date
) as id_last_next_date

// 得到当前时间增加1个小时的结果
select date_add(now(),interval 1 hour)// 得到当前时间增加1天的结果
select date_add(now(),interval 1 day)// 得到当前时间减少72个小时的结果
select date_add(now(),interval -72 hour)// 还有一些更加高级的用法，比如增加一天一小时一分一秒
// 但是如果你不太喜欢这种用法，你仍然可以使用等同于该数值的second来进行加减
date_add(now(),interval '1 1:1:1' day_second)

select DATEDIFF(year,'2010-12-31','2019-01-01')
select DATEDIFF(Month,'2018-01-01','2019-01-01')
select DATEDIFF(Week,'2018-12-01','2018-12-31')
select DATEDIFF(day,'2018-12-01','2018-12-31')

select count(date2) / count(date1) as avg_ret
from (selectdistinct qpd.device_id,qpd.date as date1,uniq_id_date.date as date2from question_practice_detail as qpdleft join(select distinct device_id, datefrom question_practice_detail) as uniq_id_dateon qpd.device_id=uniq_id_date.device_idand date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_dateselect avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (selectdistinct device_id,date as date1,lead(date) over (partition by device_id order by date) as date2from (select distinct device_id, datefrom question_practice_detail) as uniq_id_date
) as id_last_next_date