- 鉴于自己最近在做后端开发的工作时,发现自己的SQL能力实在太差,开始学习SQL语句基础,学习过程中在本博客进行笔记记录,课程参考:https://www.bilibili.com/video/BV1UE41147KC?p=2
文章目录
基本概念
DBMS: 数据库管理系统,数据库管理的软件应用,用来管理所有的数据
关系型数据库 : DBMS的一种,每个数据存储对象以表的形式进行分别,表与表之间建立关联关系,因此成为关系型数据库。
非关系型数据库: DBMS的一种,每种数据之间没有表与表之间的关联关系,不能够用SQL语句进行查询。
**SQL全称: ** Structured Query Language ,结构化查询语言
本次学习目标
- 基础的增、删、改、查
- 学习汇总数据、展示报告
- 使用子查询做复杂的查询
- 使用基础内置函数做一些数字、日期、文本数据处理
- 创建视图、存储过程、函数
- 学习使用触发器、时间、事务、并发
-
学习设计数据库
-
学习索引基础知识、如何使用
- 数据库安全问题
SQL语句基本编写原则
基本语句组成示例
USE xxxDB; // 指定使用数据库
SELECT *
FROM customers
WHERE customer_id = 1
ORDER BY first_name;
1、尽管SQL语句不区分大小写,但是关键词最好大写,便于区分,例如 select -> SELECT
,where -> WHERE
2、SQL语句基本逻辑数据: SELETE -> FROM -> 条件查询语句 -> ORDER BY
,先后不按照规定来会导致语句错误
3、每个条件区分可以使用换行符区分
选择语句
SELECT
select用来查询出相关的数据,例如:
SELECT *
FROM customers;
就是选择customers中的所有列。
其中 *
代表的是选择全部,我们也可以指定选择某列数据,例如:
SELECT first_name, last_name
FROM customers;
只选择first_name
跟last_name
列进行展示。
同时,我们还可以对列进行基本的算数运算操作,例如:
除此之外,还可以给查询的列取别名,例如:
SELECT first_name, last_name, points, points + 1 as points_after_caculator
FROM customers;
可以看到我们的列已经展示出来了我们想要的别名。
DISTINCT:
DISTINCT 关键词用来做去重,对我们查询到的数据进行去重。
WHERE:
where语句用来做条件查询,例如:
SELECT *
FROM customers
WHERE points > 3000;
查询所有points 大于 3000的顾客,查询结果:
比较运算符有:
>
>=
<
<=
=
!=
<> // 不等于
AND OR NOT操作符
操作符用来做where的扩展条件补充,例如除了积分外,我们还要筛选出生年月大于1990年的顾客:
AND :
与,与代码中的 &
效果一致
SELECT *
FROM customers
WHERE points > 1000 AND birth_date > '1990-01-01';
结果:
OR :
或,与代码中的 ||
效果一致满足其中一个条件即可,例如:
SELECT *
FROM customers
WHERE points > 1000 OR birth_date > '1990-01-01';
此时选择的便是出生日期为90之前,或者积分大于1000的顾客,结果:
- 对于多个条件都出现时,优先计算AND联合起来的条件,再计算OR的条件,存在一定的计算优先级。
例如:
SELECT *
FROM customers
WHERE points > 1000 OR birth_date > '1990-01-01' AND state = 'VA';
就是计算了 birth_date > '1990-01-01' AND state = 'VA'
后,再计算OR的结果,结果:
其效果与增加了括号的效果一致。
NOT :
非,与代码中的 !=
效果一致,不满足该条件才满足要求,例如:
SELECT *
FROM customers
WHERE NOT (points > 1000 OR birth_date > '1990-01-01');
选择非的条件,NOT一般放在where 后面,NOT后面的条件用括号进行区分,清晰SQL语句。
结果:
IN
用来联合所有的条件,例如查询多个相同的条件:
SELECT *
FROM customers
WHERE state = 'VA' OR state = 'FL' OR state = 'GA';
等同于:
SELECT *
FROM customers
WHERE state IN ('VA', 'FL', 'GA');
结果:
BETWEEN
条件区间,类似于数学上的[x, y]
例如选择分数为 1000到 3000之间的顾客,可以使用where语句:
SELECT *
FROM customers
WHERE points >= 1000 AND points <= 3000;
等同于:
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000;
结果:
LIKE
模糊匹配预算符,按照一定的模式匹配字符串,例如:匹配所有以b开头的名字顾客:
SELECT *
FROM customers
WHERE last_name LIKE 'b%';
结果:
具体的匹配规则可以参考正则匹配符号。
REGEXP(正则表达式)
做正则表达式匹配,具体可以参考正则表达式匹配规则,例如,选择所有名字包含field的顾客,使用LIKE:
SELECT *
FROM customers
WHERE last_name LIKE '%field%';
使用REGEXP
:
SELECT *
FROM customers
WHERE last_name REGEXP 'field';
二者效果相同,结果:
基本:
^
: 忽略开头
$
: 忽略结尾
|
: 逻辑或
具体可以参考正则表达式的规则。
IS NULL
为空判断符,为空才满足该要求。例如:
SELECT *
FROM customers
WHERE phone IS NULL;
查询所有没有phone字段内容的顾客。
结果:
ORDER BY 语句
用来排序返回结果,根据不同的ORDER条件进行排序查询,例如,按照first_name排序:
SELECT *
FROM customers
ORDER BY first_name;
结果:
默认为升序排序,我们也可以改为降序:
SELECT *
FROM customers
ORDER BY first_name DESC;
结果:
同时也可以进行多条件排序,例如:
SELECT *
FROM customers
ORDER BY state, first_name;
排序的规则时优先第一个,再保证后续规则。
排序规则同时可以作用与别名列表。
Limit
Limit主要用来做限制返回的结果,例如返回20个数据,只想要3个数据,便可以使用Limit语句来做限制,例如只显示3个顾客:
SELECT *
FROM customers
LIMIT 3;
结果只返回前3个数据:
对于数据比较多,需要的数据不是前面的数据,可以进行偏移量(offset)的设置来进行设置,例如读取的是3 - 5的数据:
SELECT *
FROM customers
LIMIT 2, 3;
- Limit后的第一个参数就是偏移量,第二个数字就是返回的item数。
结果:
偏移量的一个经典应用就是进行数据库分页,根据分页的偏移量来返回结果。
若LIMIT的数量大于数据库的所有数量,返回全部数据。
内连接
在一个数据库表的数据不能够完全满足查询的要求,需要和别的表一起使用时,我们就用到了连接的操作。
连接便是将两个表的数据连接成一个表进行显示的操作,连接分为两种:
内连接: 满足条件的记录才会进行显示,没有满足的条件不会进行显示.
外连接: 指定规则的表数据会进行显示,其中分为左外、右外连接等。
本次主要先学习内连接的知识。
JOIN
JOIN便是用来做连接操作的符号,在JOIN的时候,默认就是做内连接。
例如我们将订单表和顾客表按照顾客ID进行连接:
USE sql_store;
SELECT *
FROM orders o // 使用o做表别名,使用别名后,其他位置的使用都要用该别名做查询
JOIN customers c
ON o.customer_id = c.customer_id;
- 在使用别名后,其他位置的使用都要用该别名做查询。
ON
后便是指定的条件。结果:
跨数据连接
在需要跨数据库连接时,我们要做的就是在JOIN前增加一个数据库的前缀,例如,我们要连接订单表,和sql_incentory
数据库的产品表:
SELECT *
FROM order_items oi
JOIN sql_inventory.products p // 在表前添加数据库名
ON oi.product_id = p.product_id;
查询结果:
自连接
在某些情况下,想要连接的数据可能来自自身的表,这时候就可以使用自连接。
例如,在雇员表中汇报对象字段,汇报对象也在雇员表中,这时候我们就可以使用自连接显示雇员的汇报对象详细信息,例如:
USE sql_hr;
SELECT *
FROM employees e
JOIN employees m
ON e.employee_id = m.employee_id;
结果:
多表连接
在连接两个表不能够满足需求时,就会使用到更多个表的连接操作。
例如,在订单表中,不但要连接查看顾客表数据,还要连接查看状态表数据:
USE sql_store;
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
JOIN order_statuses os
ON o.status = os.order_status_id;
在2表的基础上,再增加一次JOIN,要注意连接的表的名字。
结果:
复合连接条件查询
对于连接时不单单要使用单一条件的情况,要使用多个条件才能进行连接,我们就要用到复合连接条件查询。
例如在做连接时,我们不单要考虑连接订单id,还要连接产品id,用他们来做唯一匹配:
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_Id
AND oi.product_id = oin.product_id;
隐式条件连接
在使用简单连接时,也可以省略JOIN
,例如以下两个sql的查询结果是一致的:
省略前:
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;
省略后:
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;
但是不建议使用,可能会导致交叉数据的情况出现。
外连接
在使用JOIN关键词时默认的都是内连接,在某些场景下,我们需要使用外连接。
在使用内连接时,只有满足要求的内容
才会显示,使用外连接时,某部分的所有数据
都会显示。
外连接的类型
左外连接 : 连接的主表的所有数据都会被显示,也就是FROM
指定的表的数据。
右外连接 : 连接的副表的所有数据都会被显示,也就是JOIN
指定的表的数据。
对比内连接,内连接的两表中,只有条件满足才会进行显示,而不是像外连接中的显示某一表的所有内容,例如,使用内连接连接顾客表以及订单表:
SELECT c.customer_id,
c.first_name,
o.order_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;c
结果:
可以看到,并不是所有的客户的ID都被显示出来,只有在订单表中有订单存在的顾客,才会在订单表中进行显示,这就是内连接的特性。
若我们要显示所有的顾客的订单情况,就要用到外连接,外连接的关键词就是在JOIN
前面加上LEFT/RIGHT
:
SELECT c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
此时,结果变为:
可以看到所有的顾客的信息都得到了展示,并且对于顾客多个的订单也进行了展示。
若左表对右表存在多个项,会同时进行展示。
假如我们使用右连接:
SELECT c.customer_id,
c.first_name,
o.order_id
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
结果:
与左连接不同,这次是满足右表的所有数据的情况会被返回。
多表外连接
有时候对于一些比较复杂的查询,两表的连接还不能够满足要求,需要多个表的连接才能够完整的展示数据,这时候我们就要用到多表连接,多表连接的使用也很简单,就是在连接的基础上再加上一层JOIN
,也就是套娃,例如在上面左连接的基础上,加上一层连接,显示订单运输人名字:
SELECT c.customer_id,
c.first_name,
o.order_id,
sh.name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
JOIN shippers sh
ON sh.shipper_id = o.shipper_id
ORDER BY c.customer_id;
结果:
同理,若要更多的表,继续在JOIN的基础上继续增加即可。
自外连接
对于一些需要对表字身进行外连接的情况,就需要使用到自外连接来进行实现,例如,在获取到雇员表中的管理者:
SELECT e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
结果:
USING 语句
在遇到两个表进行连接时,两个表的列名一致,可以省略ON
以及后面的列名对应,使用USING
语句,例如:
SELECT e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
ON e.employee_id= m.employee_id
可以将ON
换成USING
:
SELECT e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
USING (employee_id)
以上两者的作用是一致的。
USING预计只有在两个列名完全一致的情况下才能够使用。
Union
UNION用来连接多个查询,将查询结果合并到同个结果集。
例如,我们对订单进行查询,并且针对不同的订单时间,返回不同的结果,进行合并:
我们可以先选择出2019年以上的订单,称之为Active
:
SELECT order_id,
order_date,
'Active' AS status
FROM sql_store.orders
WHERE order_date >= '2019-01-01'
查询2018年的订单称之为Archived
:
SELECT order_id,
order_date,
'Archived' AS status
FROM sql_store.orders
WHERE order_date < '2019-01-01'
使用UNION
将2者结果进行合并:
SELECT order_id,
order_date,
'Active' AS status
FROM sql_store.orders
WHERE order_date >= '2019-01-01'
UNION
SELECT order_id,
order_date,
'Archived' AS status
FROM sql_store.orders
WHERE order_date < '2019-01-01'
结果:
要注意:
- 在查询的时候要确保列数一致,否则会报错。
- 查询的过程中以首列为名字