课程:Mosh_完全掌握SQL【笔记】

目录

第一部分:基础——增删查改

【第一章】做好准备 Getting Started (时长25分钟)

【第二章】在单一表格中检索数据 Retrieving Data From a Single Table (时长53分钟)

【第三章】在多张表格中检索数据 Retrieving Data From Multiple Tables (时长1小时2分)

【第四章】插入、更新和删除数据 Inserting, Updating, and Deleting Data (时长42分钟)

第二部分:基础进阶——汇总、复杂查询、内置函数

【第五章】汇总数据 Summarizing Data (时长33分钟)

【第六章】编写复杂查询 Writing Complex Query (时长45分钟)

【第七章】MySQL的基本函数 Essential MySQL Functions (时长33分钟)

第三部分:提高效率——视图、存储过程、函数

【第八章】视图 Views (时长18分钟)

【第九章】存储过程 Stored Procedures (时长48分钟)

第四部分:高阶主题——触发器、事件、事务、并发

【第十章】触发器和事件 Triggers and Events (时长22分钟)

【十一章】事务和并发 Transactions and Concurrency (时长49分钟)

第五部分:脱颖而出——数据类型、设计数据库、索引、保护

【十二章】数据类型 Data Types (时长35分钟)

【十三章】设计数据库 Designing Databases (时长1时30分)

【十四章】高效的索引 Indexing for High Performance (时长58分钟)

【十五章】保护数据库 Securing Databases (时长20分钟)

终:学习总结

 

第一部分:基础——增删查改

【第一章】做好准备 Getting Started (时长25分钟)

1. 介绍

Introduction (0:18)

……

2. 什么是SQL

What is SQL (3:24)

  • A DATABASE is a collection of data stored in a format that can easily be accessed

    数据库是一个以易访问格式存储的数据集合

  • 为了管理我们的数据库 我们使用一种叫做数据库管理系统(DBMS, Database Management System)的软件。我们连接到一个DBMS然后下达查询或者修改数据的指令,DBMS就会执行我们的指令并返回结果

DBMS

 

  • 有关系型和非关系型两类数据库,在更流行的关系型数据库中,我们把数据存储在通过某些关系相互关联的数据表中,每张表储存特定的一类数据,这正是关系型数据库名称的由来。(如:顾客表通过顾客id与订单表相联系,订单表又通过商品id与商品表相联系)

  • SQL(Structured Query Language,结构化查询语言)是专门用来处理(包括查询和修改)关系型数据库的标准语言

  • 不同关系型数据库管理系统语法略有不同,但都是基于标准SQL,本课使用最流行的开源关系型数据库管理系统,MySQL

3. MySQL Mac版本安装

Installing MySQL on Mac (4:58)

……

4. MySQL Windows版本安装

Installing MySQL on Windows (5:20)

注意

Mosh是用Installer一次性安装了MySQL和workbench,若已安装MySQL而没有workbench的可 单独安装workbench

5. 创建数据库

Creating the Databases (8:32)

注意

如果MySQL版本较低导致导入 create-databases.sql 时出现collate排序规则问题而报错,可以用记事本打开 create-databases.sql 将 utf8mb4_0990_ai_ci 全部替换(Ctrl+H)为 utf8mb4_general_ci 并保存,再次导入就能顺利运行了。

关于MySQL的版本,可参考文章:我TM究竟应该选哪个版本的MySQL?!

背景

查看 数据概要,大致了解一下课程所用到数据的含义及其相互关系,这对理解课程有极大帮助

6. 你会学到什么

What You'll Learn (2:31)

【第二章】在单一表格中检索数据 Retrieving Data From a Single Table (时长53分钟)

 

1. 选择语句

 

The SELECT Statement (5:50)

 

导航

 

第1节先看一下选择语句整体是什么样子,本章后面的小节会分别讲解其中各子句的具体写法

 

实例

 

复制代码
 1 USE sql_store;
 2 
 3 SELECT * / 1, 2  -- 纵向筛选列,甚至可以是常数
 4 FROM customers  -- 选择表
 5 WHERE customer_id < 4  -- 横向筛选行
 6 ORDER BY first_name  -- 排序
 7 
 8 -- 单行注释
 9 
10 /*
11 多行注释
12 */
复制代码

 

 

2. 选择子句

 

The SELECT Clause (8:48)

 

小结

 

SELECT 是列/字段选择语句,可选择列,列间数学表达式,特定值或文本,可用AS关键字设置列别名(AS可省略),注意 DISTINCT 关键字的使用。

 

注意

 

SQL会完全无视大小写(绝大数情况下的大小写)、多余的空格(超过一个的空格)、缩进和换行,SQL语句间完全由分号 ; 分割,用缩进、换行等只是为了代码看着更美观结构更清晰,这些与Python很不同,要注意。

 

实例

 

复制代码
 1 USE sql_store;
 2 
 3 SELECT
 4     DISTINCT last_name, 
 5     -- 这种选择的字段中部分取 DISTINCT 的是如何筛选的?
 6     first_name,
 7     points,
 8     (points + 70) % 100 AS discount_factor/'discount factor'
 9     -- % 取余(取模)
10 FROM customers
复制代码

 

 

练习

 

单价涨价10%作为新单价

 

SELECT 
    name, 
    unit_price, 
    unit_price * 1.1 'new price'  
FROM products

 

 

如上面这个例子所示,取别名时,AS 可省,空格后跟别名就行,可看作是SQL会将将列变量及其数学运算之后的第一个空格识别为AS

 

3. WHERE子句

 

The WHERE Clause (5:17)

 

小结

 

WHERE 是行筛选条件,实际是一行一行/一条条记录依次验证是否符合条件,进行筛选

 

导航

 

3~9 节讲的都是写 WHERE 子句中表达筛选条件的不同方法,这一节(第3节)主要讲比较运算,第4节讲逻辑运算 AND、OR、NOT,5~9可看作都是在讲特殊的比较运算(是否符合某种条件):IN、BETWEEN、LIKE、REGEXP、IS NULL。

 

所以总的来说WHERE条件就是数学→比较→逻辑运算,逻辑层次和执行优先级也是按照这三个的顺序来的。

 

实例

 

1 USE sql_store;
2 
3 SELECT *
4 FROM customers
5 WHERE points > 3000  
6 /WHERE state != 'va'  -- 'VA'/'va'一样

 

 

比较运算符 > < = >= <= !=/<> ,注意等于是一个等号而不是两个等号

 

也可对日期或文本进行比较运算,注意SQL里日期的标准写法及其需要用引号包裹这一点

 

WHERE birth_date > '1990-01-01'

 

练习

 

今年(2019) 的订单

 

1 USE sql_store;
2 
3 select *
4 from orders
5 where order_date > '2019-01-01'
6 -- 有更一般的方法,不用每年改代码,之后教

 

 

4. AND, OR, NOT运算符

 

The AND, OR and NOT Operators (6:52)

 

小结

 

用逻辑运算符AND、OR、NOT对(数学和)比较运算进行组合实现多重条件筛选
执行优先级:数学→比较→逻辑

 

实例

 

1 USE sql_store;
2 
3 SELECT *
4 FROM customers
5 WHERE birth_date > '1990-01-01' AND points > 1000
6 /WHERE birth_date > '1990-01-01' OR 
7       points > 1000 AND state = 'VA'

 

 

AND优先级高于OR,但最好加括号,更清晰

 

1 WHERE birth_date > '1990-01-01' OR 
2       (points > 1000 AND state = 'VA')

 

 

NOT的用法

 

WHERE NOT (birth_date > '1990-01-01' OR points > 1000)

 

 

去括号等效转化为

 

WHERE birth_date <= '1990-01-01' AND points <= 1000

 

 

练习

 

订单6中总价大于30的商品

 

1 USE sql_store;
2 
3 SELECT * FROM order_items
4 WHERE order_id = 6 AND quantity * unit_price > 30

 

 

注意优先级:数学→比较→逻辑

 

SELECT 子句,WHERE 子句以及后面的 ORDER BY 子句等都能用列间数学表达式

 

5. IN运算符

 

The IN Operator (3:03)

 

小结

 

用IN运算符将某一属性与多个值(一系列值)进行比较
实质是多重相等比较运算条件的简化

 

案例

 

选出'va'、'fl'、'ga'三个州的顾客

 

1 USE sql_store;
2 
3 SELECT * FROM customers
4 WHERE state = 'va' OR state = 'fl' OR state = 'ga'

 

 

不能 state = 'va' OR 'fl' OR 'ga' 因为数学和比较运算优先于逻辑运算,加括号 state = ('va' OR 'fl' OR 'ga') 也不行,逻辑运算符只能链接布林值。

 

用 IN 操作符简化该条件

 

WHERE state IN ('va', 'fl', 'ga')

 

 

可加NOT

 

WHERE state NOT IN ('va', 'fl', 'ga')

 

 

这里可用NOT的原因:可以这么看,IN语句 IN ('va', 'fl', 'ga') 是在进行一种是否符合条件的判断,可看作是一种特殊的比较运算,得到的是一个逻辑值,故可用NOT进行取反

 

练习

 

库存量刚好为49、38或72的产品

 

1 USE sql_store;
2 
3 select * from products
4 where quantity_in_stock in (49, 38, 72)

 

 

6. BETWEEN运算符

 

The BETWEEN Operator (2:12)

 

小结

 

用于表达范围型条件

 

注意

 

  • 用AND而非括号
  • 闭区间,包含两端点
  • 也可用于日期,毕竟日期本质也是数值,日期也有大小(早晚),可比较运算
  • 同 IN 一样,BETWEEN 本质也是一种特定的 多重比较运算条件 的简化

 

案例

 

选出积分在1k到3k的顾客

 

1 USE sql_store;
2 
3 select * from customers
4 where points >= 1000 and points <= 3000

 

 

等效简化为:

 

WHERE points BETWEEN 1000 AND 3000

 

 

注意两端都是包含的 不能写作BETWEEN (1000, 3000)!别和IN的写法搞混

 

练习

 

选出90后的顾客

 

1 SELECT * FROM customers
2 WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

 

 

7. LIKE运算符

 

The LIKE Operator (5:37)

 

小结

 

模糊查找,查找具有某种模式的字符串的记录/行

 

注意

 

  • 过时用法(但有时还是比较好用,之后发现好像用的还是比较多的),下节课的正则表达式更灵活更强大
  • 注意和正则表达式一样都是用引号包裹表示字符串

 

1 USE sql_store;
2 SELECT * FROM customers
3 WHERE last_name like 'brush%' / 'b____y'

 

 

引号内描述想要的字符串模式,注意SQL(几乎)任何情况都是不区分大小写的

 

两种通配符:

 

  • % 任何个数(包括0个)的字符(用的更多)
  • _ 单个字符

 

练习

 

分别选择满足如下条件的顾客:

 

1. 地址包含 'TRAIL' 或 'AVENUE'

 

2. 电话号码以 9 结束

 

1 USE sql_store;
2 
3 select * 
4 from customers
5 where address like '%Trail%' or 
6       address like '%avenue%'

 

 

LIKE 执行优先级在逻辑运算符之后,毕竟 IN BETWEEN LIKE 本质可看作是比较运算符的简化,应该和比较运算同级,数学→比较→逻辑,始终记住这个顺序,上面这个如果用正则表达式会简单得多

 

1 where phone like '%9'
2 /where phone not like '%9'

 

 

LIKE的判断结果也是个TRUE/FASLE的问题,任何逻辑值/布林值都可前置NOT来取反

 

8. REGEXP运算符

 

The REGEXP Operator (9:21)

 

小结

 

正则表达式,在搜索字符串方面更为强大,可搜索更复杂的模板

 

实例

 

1 USE sql_store;
2 
3 select * from customers
4 where last_name like '%field%'

 

 

等效于:

 

where last_name regexp 'field'

 

 

regexp 是 regular expression(正则表达式) 的缩写

 

正则表达式可以组合来表达更复杂的字符串模式

 

1 where last_name regexp '^mac|field$|rose' 
2 where last_name regexp '[gi]e|e[fmq]' -- 查找含ge/ie或ef/em/eq的
3 where last_name regexp '[a-h]e|e[c-j]'

 

 

一直不太明白位运算和逻辑运算的用法区别?比如 | 和 OR 的区别?感觉意思和用法差不多啊,只是用在的地方不一样,具体各自用在哪里还要学习……

 

正则表达式总结:

 

符号意义
^ 开头
$ 结尾
[abc] 含abc
[a-c] 含a到c
| logical or

 

(正则表达式用法还有更多,自己去查)

 

练习

 

分别选择满足如下条件的顾客:

 

1. first names 是 ELKA 或 AMBUR

 

2. last names 以 EY 或 ON 结束

 

3. last names 以 MY 开头 或包含 SE

 

4. last names 包含 BR 或 BU

 

1 select * 
2 from customers
3 where first_name regexp 'elka|ambur'
4 /where last_name regexp 'ey$|on$'
5 /where last_name regexp '^my|se'
6 /where last_name regexp 'b[ru]'/'br|bu'

 

 

9. IS NULL运算符

 

The IS NULL Operator (2:26)

 

小结

 

找出空值,找出有某些属性缺失的记录

 

案例

 

找出电话号码缺失的顾客,也许发个邮件提醒他们之类

 

1 USE sql_store;
2 
3 select * from customers
4 where phone is null/is not null

 

 

注意是 IS NULL 和 IS NOT NULL 这里 NOT 不是前置于布林值,而是更符合英语语法地放在了be动词后

 

练习

 

找出还没发货的订单(在线商城管理员的常见查询需求)

 

1 USE sql_store;
2 
3 select * from orders
4 where shipper_id is null

 

 

回顾

 

3~9 节全在讲WHERE子句中条件的具体写法 :

 

  • 第3节:比较运算 > < = >= <= !=
  • 第4节:逻辑运算 ANDORNOT
  • 5~9节:特殊的比较运算(是否符合某种条件):IN 和 BETWEENLIKE 和 REGEXPIS NULL

 

所以总的来说WHERE条件就是

 

数学运算 → 比较运算(包括特殊的比较运算)→ 逻辑运算

 

逻辑层次和执行优先级也是按照这三个的顺序来的。

 

10. ORDER BY子句

 

The ORDER BY Clause (7:06)

 

小结

 

排序语句,和 SELECT …… 很像:

 

  1. 可多列
  2. 可以是列间的数学表达式
  3. 可包括任何列,包括没选择的列(MySQL特性,其它DBMS可能报错),
  4. 可以是之前定义好的别名列(MySQL特性,甚至可以是用一个常数设置的列别名)
  5. 任何一个排序依据列后面都可选加 DESC

 

注意

 

最好别用 ORDER BY 1, 2(表示以 SELECT …… 选中列中的第1、2列为排序依据) 这种隐性依据,因为SELECT选择的列一变就容易出错,还是显性地写出列名作为排序依据比较好

 

注:workbench 中扳手图标可打开表格的设计模式,查看或修改表中各列(属性),可以看到谁是主键。省略排序语句的话会默认按主键排序

 

实例

 

1 USE sql_store;
2 
3 select name, unit_price * 1.1 + 10 as new_price 
4 from products
5 order by new_price desc, product_id
6 -- 这两个分别是 别名列 和 未选择列,都用到了 MySQL 特性

 

 

练习

 

订单2的商品按总价降序排列:

 

法1. 可以以总价的数学表达式为排序依据

 

1 select * from order_items 
2 where order_id = 2
3 order by quantity * unit_price desc
4 -- 列间数学表达式

 

 

法2. 或先定义总价别名,在以别名为排序依据

 

1 select *, quantity * unit_price as total_price 
2 from order_items 
3 where order_id = 2
4 order by total_price desc
5 -- 列别名

 

 

11. LIMIT子句

 

The LIMIT Clause (3:26)

 

小结

 

限制返回结果的记录数量,“前N个” 或 “跳过M个后的前N个”

 

实例

 

1 USE sql_store;
2 
3 select * from customers
4 limit 3 / 300 / 6, 3

 

 

6, 3 表示跳过前6个,取第7~9个,6是偏移量,
如:网页分页 每3条记录显示一页 第3页应该显示的记录就是 limit 6, 3

 

练习

 

找出积分排名前三的死忠粉

 

1 USE sql_store;
2 
3 select *
4 from customers
5 order by points desc 
6 limit 3

 

 

回顾

 

SELECT 语句完结了,里面的子句顺序固定要记牢,顺序乱会报错
select from where + order by limit
纵选列,确定表,横选行(各种条件写法和组合要清楚熟悉),最后再进行排序和限制

 

【第三章】在多张表格中检索数据 Retrieving Data From Multiple Tables (时长1小时2分)

【第四章】插入、更新和删除数据 Inserting, Updating, and Deleting Data (时长42分钟)

第二部分:基础进阶——汇总、复杂查询、内置函数

【第五章】汇总数据 Summarizing Data (时长33分钟)

【第六章】编写复杂查询 Writing Complex Query (时长45分钟)

【第七章】MySQL的基本函数 Essential MySQL Functions (时长33分钟)

第三部分:提高效率——视图、存储过程、函数

【第八章】视图 Views (时长18分钟)

【第九章】存储过程 Stored Procedures (时长48分钟)

第四部分:高阶主题——触发器、事件、事务、并发

【第十章】触发器和事件 Triggers and Events (时长22分钟)

【十一章】事务和并发 Transactions and Concurrency (时长49分钟)

第五部分:脱颖而出——数据类型、设计数据库、索引、保护

【十二章】数据类型 Data Types (时长35分钟)

【十三章】设计数据库 Designing Databases (时长1时30分)

【十四章】高效的索引 Indexing for High Performance (时长58分钟)

【十五章】保护数据库 Securing Databases (时长20分钟)

终:学习总结

 

posted @   天域话诗人  阅读(521)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
点击右上角即可分享
微信分享提示