MySQL 与 SQL 复习

轻量化复盘一下 sql 操作，以及 MySQL

1、SELECT 语句

​ 数据查询为关系运算理论在 SQL 语言中的主要体现。SQL 的数据查询只一条 SELECT 语句，其使用最广泛。一个完整的 SELECT 语句包含了六个字句，前两个字句是必备的，其他字句可以省略

SELECT 完整格式：

SELECT [DISTINCT] 目标列名序列 ------ 查看的列 [distinct] 为去重操作

FROM 表名或视图名 ------------------- 数据来源

[WHERE 条件表达式] ------------------ 查询条件

[GROUP BY + 列名] ------------------ 分组依据

[HAVING + 组条件表达式] ------------- 分组条件

[ORDER BY + 列名 + [ASC|DESC]序列]--- 排序依据

​ 其中使用 distinct 时要注意列名要在 distinct 的后面，而使用聚合函数时，则是在 count[distinct<列名>] 语法。

SELECT 
	DISTINCT device_id 
FROM usr_profile

​ 查询列后，将列取别名操作，要使用 as ，但是也能省略

列名 | 表达式 [ AS ] 新列名 或 新列名＝列名 | 表达式

SELECT 
	device_id as user_infos_example 
FROM user_profile

# 等同于
SELECT 
	device_id user_infos_example 
FROM user_profile

2、LIMIT 语句

​ 在大表中，一般很少一次性查出所有数据，这样对数据库的压力太大，如果只是抽查一些数据，就可以使用LIMIT关键字来查询。 LIMIT 子句的使用，用于强制 SELECT 语句返回指定的记录数。其接受一个或两个数字参数，且参数必须为整型常量。

​ 如果只给定一个参数，返回最大的记录行数目

​ 给的两个参数，第一个参数是行数序号（从0开始表示第一行），第二个参数是数量。

SELECT 
	* 
FROM table 
LIMIT 5,5 # 检索记录行6-10

SELECT 
	* 
FROM table 
LIMIT 10,-1

​ 也能结合offset 一起使用：

select 
	device_id 
from user_profile 
limit 2 offset 0 # 跳过0条，从第一条数据开始取，取两条数据

3、BETWEEN 语句

​ 当需要查询某个范围值的时候，一般使用两种语句，between and， 以及 and 语句

SELECT 
	device_id, gender, age 
FROM user_profile 
where age 
between 20 and 23
# 等价于
SELECT 
	device_id, gender, age 
FROM user_profile 
where age >= 20 and age <= 23

4、NOT IN 语句

​ 当需要查询集合中指定属性值时，使用 IN ，除此属性值之外的所有值使用 NOT IN

列名 [NOT] IN '属性值'

​ IN：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录。
​ NOT IN：当列中的值与某个常量值相等时，结果为False，表明此记录为不符合查询条件的记录。

SELECT 
	* 
FROM user_profile 
where university NOT IN '复旦大学'

​ 当需要过滤空值时

SELECT 
	device_id, gender, age, university 
FROM user_profile 
WHERE age !=''; 
# 或者
SELECT 
	device_id, gender, age, university 
FROM user_profile 
WHERE age is NOT NULL

5、AND OR 语句

​ bool 值运算关系

SELECT 
	device_id, gender, age, university, gpa 
FROM user_profile 
WHERE gpa > 3.5 AND gender = 'male'

​ 混合使用，AND 的优先级大于 OR

SELECT 
	device_id, gender, age, university, gpa 
FROM user_profile 
WHERE (gpa > 3.5 and university = '山东大学') OR (gpa > 3.8 and university = '复旦大学')

6、模糊匹配

​ 数据库中要进行模糊查询，主要使用以下操作

1. _ ：下划线，代表匹配任意一个字符；
2. % ：百分号，代表匹配0个或多个字符；
3. []: 中括号，代表匹配其中的任意一个字符；
4. [^]: ^尖冒号 代表 非，取反的意思；不匹配中的任意一个字符。

like 模糊查询用法：
  '%北京'   北京开头的
  '_北京%'  第二三个字为北京 
  '%北京%'  含有北京的

tips: 
 你了解哪些数据库优化技术？ 
 答：SQL语句优化也属于数据库优化一部分，而我们的 like 模糊查询会引起全表扫描，速度比较慢，应该尽量避免使用like关键字进行模糊查询。
 
SELECT device_id,age,university FROM user_profile
WHERE university LIKE '%北京%'

7、SQL 中相应的一些函数

​ 1、max

​ 查找某个条件限制下的最小最大值时候，一般有两种方法：第一种为使用 最高分-降序排序，取第一一位。第二种为使用 max 函数也行

SELECT 
    max(gpa) 
FROM user_profile 
WHERE university = '复旦大学'

# 上为第二种，下为第一种方法

SELECT 
    gpa 
FROM user_profile 
WHERE university = '复旦大学' 
ORDER BY gpa 
DESC limit 1

# order by 为升序排列，order by desc 为降序排列

​ 2、count、round、avg 函数

​ count 用于统计某个值的数量，round 用于保留几位小数，avg 用于求某一列的平均值

select 
    COUNT(gender) as male_num,
    round(avg(gpa), 1) as avg_gpa
from user_profile where gender = 'male'

​ 3、聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；

SELECT 
    university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
FROM user_profile 
GROUP BY university
HAVING avg_question_cnt < 5 OR avg_answer_cnt < 20

8、多表查询

​ 若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询。

​ 连接查询包括：内连接、外连接、交叉连接等

​ 连接查询用于连接两个表的条件称为连接条件或者连接谓词。

​ 相应的语法如下：

SELECT …
FROM 表名
[INNER] JOIN 被连接表
ON 连接条件

#以上结果中会包含重复的列
SELECT * FROM 学生表
INNER JOIN 班级表 ON 学生表.班号=班级表.班号

#去除重复列！
SELECT 学号, 姓名, 班级表.班号, 班名 FROM 学生表 JOIN 班级表 ON 学生表.班号=班级表.班号

# 举几个多表连接的例子，多体会
select
    u.university,
    count(q.question_id) / count(DISTINCT (q.device_id)) as avg_answer_cnt 
from user_profile as u
right join question_practice_detail as q 
on u.device_id = q.device_id
group by u.university

# right join 联结结果保留右表的全部数据
# left join  联结结果保留左表的全部数据
# inner join 保留两表的公共数据

9、组合查询

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

select 
    device_id, gender, age, gpa 
from user_profile 
where university = '山东大学'
union all
select 
    device_id, gender, age, gpa 
from user_profile 
where gender = 'male'
 
使用以下语句，会去重
where university = '山东大学' or gender = 'male'
 
union 会去重， union all 不会去重

---

参考网站

https://www.nowcoder.com/exam/oj?tab=SQL%E7%AF%87&topicId=199