MySQL 学习笔记 Part1

SQL(Structed Query Language)

视频链接：https://www.bilibili.com/video/BV1Xb41177na?p=1


一、准备

1. 下载安装MySQL

MySQL Community Downloads

在Accounts and Roles界面设置root用户的密码，Connect To Server 界面再次输入密码。

其余全部默认


2. 配置MySQL

  • 点击主界面 + ，新建连接


  • 分别设置连接名称，端口密码，并测试保存。


3. 创建一个数据库

下载链接下SQL Course Materials：https://github.com/JiaZhengJingXianSheng/Study-File

运行create-databases.sql


二、单一表格中检索数据

MySQL语句在执行时关键字是忽视大小写的，建议关键字使用大写。

同时也会忽略回车 空格，所以下方两个指令是一样的，但建议使用第二种。

1
SELECT * FROM customers	# 读取customers表
1
2
SELECT * 
FROM customers	# 读取customers表

1. SELECT 语句

  • USE 用来指定数据库
  • SELECT 用来查询具体的信息
  • FROM 指定了从某个表中查询
  • ORDER BY 可以用来排序
1
2
3
4
5
6
7
8
USE sql_store;  # 选中数据库  

# SELECT customer_id, fisrt_name 选择指定列

# 下面3行是一起的
-- SELECT * FROM customers	# 读取customers表
-- WHERE customer_id = 1	# WHERE 语句可以查询某一条记录
-- ORDER BY first_name 	-- 根据first_name排序

  • 读取指定列，如果需要查询列过多，可以换行。也可以直接在SELECT 语句中进行数学运算。
  • AS 可以指定名称。如果指定的名称包含空格，可以用引号包含起来。
1
2
3
4
5
6
SELECT 
	first_name, 
    last_name, 
    points, 
    (points + 10) * 100 AS 'discount factor'
FROM customers	# 读取指定列

  • 如果我们想检索出不重复的结果可以使用 DISTINCT 关键字
1
2
SELECT DISTINCT state
FROM customers

2. WHERE 子句

  • 下面命令实在customers表下查找points大于300的值
  • “>，>=，<，<=，=(等于)，!=(不等于)，<>(不等于)”
  • MySQL中日期的标准写法是 ‘1990-01-01’
1
2
3
SELECT *
FROM customers	
WHERE points > 300
  • 查询2019年以后订单
1
2
3
4
-- Get the orders placed 2019-01-01
SELECT *
FROM orders
WHERE order_date >= '2019-01-01'

3. AND OR NOT 操作符

  • 与或非，例如：
1
2
3
4
-- Get the orders placed 2019-01-01
SELECT *
FROM orders
WHERE order_date >= '2019-01-01' OR (points > 1000 AND state = 'VA')

4. IN 操作符

  • 有多个匹配项时使用，原理是OR
1
2
3
4
SELECT *
FROM customers
-- WHERE state = 'VA' OR state = 'GA' OR state = 'FL'
WHERE state IN ('VA', 'GA', 'FL')

5. BETWEEN

  • 比如找出 1000 <= points <= 3000 的列
  • BETWEEN用来查找范围值
1
2
3
4
SELECT *
FROM customers
-- WHERE points >= 1000 AND points <= 3000
WHERE points BETWEEN 1000 AND 3000

6. LIKE 筛选

  • LIKE 可以用来筛选，例如下面可以筛选出last_name 以b开头的数据。
1
2
3
SELECT *
FROM customers
WHERE last_name LIKE 'b%'

  • '%b%' 代表包含 b。%为忽视字符，且无数量限制

  • '_y' 表示不管第一个字符是什么，第二个字符为y。下划线为忽视一个字符


7. REGEXP 正则表达式

  • REGEXP 是正则表达式的简称
  • 比如我们想在last_name 里找出包含 field 的条目，可以使用 REGEXP ，如下
  • ^ 幂符号代表以此开始
  • $ 符号代表结尾
  • | 可以搜索多个字符
  • [] 代表多个字符，比如 '[gim]e' 代表 ge 或 ie 或 me
  • **- 代表范围， 例如 a-f 代表从 a 到 f **
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT *
FROM customers
-- WHERE last_name LIKE '%field%'
WHERE last_name REGEXP 'field'

-- 代表以field开头
-- WHERE last_name REGEXP '^field'

-- 代表以field结尾
-- WHERE last_name REGEXP 'field$'

-- 代表包含field或mac
-- WHERE last_name REGEXP 'field|mac'

-- 代表包含 ge 或 ie 或 me
-- WHERE last_name REGEXP '[gim]e'

8. IS NULL 检索缺失项

  • 可以搭配 WHERE 查询缺失项 IS NULL; 未缺失项 IS NOT NULL

  • 例如查询电话号码为零的信息

1
2
3
4
5
6
SELECT *
FROM customers
-- 为空
WHERE phone IS NULL
-- 不为空
-- WHERE phone IS NOT NULL

9. ORDER BY 排序

  • 默认为升序排列，如果需要逆序，在后面增加 DESC 字符
1
2
3
4
5
6
SELECT *
FROM customers
-- 升序
ORDER BY first_name
-- 降序
-- ORDER BY first_name DESC
  • 也可以混合排序，比如下方是先按照 state 排序，在同一个 state 内再按 first_name 排序
1
2
3
SELECT *
FROM customers
ORDER BY state, first_name

10. LIMIT

  • LIMIT 只加一个参数代表查找前多少个
  • LIMIT 加两个参数，其中第一个参数代表偏移量
  • LIMIT 子句永远写在最后
1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM customers
-- 只查询前3个
-- LIMIT 3

-- page 1: 1-3
-- page 2: 4-6
-- page 3: 7-9
-- 第一个参数为偏移量， 表示忽视前六个
-- 表示7-9
LIMIT 6, 3

三、 多个表中检索数据

1. 库内合并数据

  • SQL 有两种连接方式，inner 和 outer， 默认inner
  • JOIN 和 ON 合起来使用可以将表拼接起来
1
2
3
4
5
6
7
8
9
10
11
12
-- 把customer 和 orders按id相同拼接起来
SELECT *
FROM orders
JOIN customers 
	ON orders.customer_id = customers.customer_id
	
-- 只查看order_id, customer_id, first_name, last_name 匹配项
-- 如果要查看customer_id 应该加上前置表名，比如 orders.customer_id
SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
JOIN customers 
	ON orders.customer_id = customers.customer_id
  • 简化名称
1
2
3
4
5
SELECT order_id, o.customer_id, first_name, last_name
-- order 命名为 o， customer 命名为 c
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
  • 注意：简化名称为，就不能使用原名称了

2. 跨库合并数据

  • 跨库合并时要再表名前加上库名，例如：
1
2
3
4
5
6
USE sql_store;

SELECT *
FROM order_items oi
JOIN sql_inventory.products p 
	ON oi.product_id = p.product_id

3. 合并表

  • 下面 hr 数据库存放的是人员工作信息， 其中reports_to 表示为谁打工， employee_id 为每个人id号

  • 下面实现了，找到所有的老板，并将老板信息拼接在后面

1
2
3
4
5
6
7
8
9
USE sql_hr;

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

4. 跨表匹配合并数据

  • 比如我们一个表中记录着状态位，而对应的状态却在另外的表里，这时候就需要跨表匹配
  • 下面实现了，找到orders里对应的客户（客户信息存放在customers），再匹配状态（状态信息存放在order_statues）
1
2
3
4
5
6
7
8
9
10
11
12
13
USE sql_store;

SELECT 
	o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c 
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.status = os.order_status_id

5. 隐式合并

下面第二种写法是隐式合并

1
2
3
4
5
6
7
8
9
10
11
USE sql_store;

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