文章目录

介绍
chapter1-选择语句
- 选择子句
- - exercise
- where子句
- - exercise
- and or 和not运算符
- - exercise
- in运算符
- - exercise
- BETWEEN运算符
- - exercise
- like运算符
- - exercise
- regexp运算符
- - exercise
- IS NULL 运算符
- - exercise
- ORDER BY子句
- - exercise
- LIMIT子句
- - exercise
- chapter2-内连接
- Inner Joins 在多张表格中检索数据
- - exercise
- 跨数据库连接
- 自连接 | self joins
- 多表连接 | joining multiple Tables
- - exercise
- 复合连接条件
- 隐式连接语法
- :s 外连接
- - exercise
- 多表外连接
- - exercise

介绍

数据库是一个以可轻易获取形式来存储数据的集合
操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。
DBMS分为关系型（MySQL、微软的SQLServer、Oracle）和非关系型（NoSQL）
使用SQL文件下载
这里演示用Navicat导入运行sql文件

运行后右键刷新可看到

Navicat中ctrl+r快速运行 sql语句

chapter1-选择语句

sql_store为数据库名
*表示返回所有列

USE sql_store; 
SELECT *
FROM customers
-- WHERE customer_id = 1
ORDER BY first_name

选择子句

AS给新的一列起别名

USE sql_store; 
SELECT
	last_name,
	first_name,
	points,
	(points + 10) * 100
	AS 'new points'
FROM customers;

distinct唯一的值删去重复项

SELECT DISTINCT state
FROM sql_store.customers

exercise

写一段SQL查询，返回数据库所有产品
在结果集看到name unit_price和new_price的新一列
new_price = unit_price * 1.1

SELECT 
	name,
	unit_price,
	unit_price * 1.1 
	AS 'new_price'
FROM sql_store.products;

where子句

筛选数据

SELECT *
FROM customers
WHERE points > 3000;

---------------------------------------------------------------------------------------------------

比较运算符
> >= < <= = != <>

exercise

从orders表中得到今年下的订单假设今年是2019年

and or 和not运算符

筛选数据时结合多条搜索条件

SELECT *
FROM customers
WHERE birth_date >= '1990-01-01'
AND points > 1000

SELECT *
FROM customers
WHERE birth_date >= '1990-01-01'
OR points > 1000

or满足两个条件中的至少1个优先级小于and

exercise

从order_items表中，获取订单号为6的项目并且项目总价大于30

SELECT *
FROM order_items
WHERE order_id = 6
AND quantity * unit_price > 30

in运算符

SELECT * 
FROM customers
-- WHERE state = 'VA' OR state = 'GA' OR state = 'FL'
WHERE state IN ('VA', 'FL', 'GA')

exercise

得到现货库数量为49， 38， 72的产品
即从products中找quantity_in_stock中数量为49，38，72

SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72)

BETWEEN运算符

SELECT *
FROM customers
-- WHERE points >= 1000 AND points <= 3000
WHERE points BETWEEN 1000 AND 3000

exercise

return customers born between 1990-1-1 and 2000-1-1

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

like运算符

检索遵循特定字符串模式的行
%表示任意字符（0个、1个、多个）
下划线_代表一个单字符

SELECT *
FROM customers
WHERE last_name LIKE 'b%'

exercise

get the customers whose
	address contain TRAIL or AVENUE
	phone numbers end with 9

SELECT *
FROM customers
WHERE address LIKE '%trail%'
OR address LIKE '%avenue%'

SELECT *
FROM customers
WHERE phone LIKE '%9'

regexp运算符

SELECT *
FROM customers
-- WHERE last_name LIKE '%field%'
WHERE last_name REGEXP 'field'

^表示字符串的开头
$代表字符串的末尾
field|mac|rose  | 表示多个搜寻模式
字母‘e’之前要有‘g’和‘i’两者之一‘[gi]e’
'[a-h]e' 不包含h

exercise

4个问题 4个答案

SELECT *
FROM customers
WHERE first_name REGEXP 'elka|ambur'

SELECT *
FROM customers
WHERE last_name REGEXP 'ey$|on$'

SELECT *
FROM customers
WHERE last_name REGEXP '^my|se'

SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]'

IS NULL 运算符

如何搜索缺失了属性的记录

SELECT *
FROM sql_store.customers
-- WHERE phone IS NOT NULL
WHERE phone IS NULL

exercise

get the orders that are not shipped
即查询获取所有还没有发货的订单

SELECT *
FROM sql_store.orders
WHERE shipped_date IS NULL

ORDER BY子句

用SQL查询为数据排序

USE sql_store;
SELECT *
FROM customers
ORDER BY first_name DESC, points ASC

exercise

USE sql_store;
SELECT *
FROM order_items
WHERE order_id = 2
ORDER BY quantity * unit_price DESC

想在最后一列加一列total_price

LIMIT子句

限制查询返回的记录

添加一个偏移量在给数据分页的时候会很有用

USE sql_store;
SELECT * 
FROM customers
LIMIT 6, 3
-- 6为偏移量

-- page 1: 1-3
-- page 2: 4-6
-- page 3: 7-9
-- 要获取第三页的数据

exercise

get the top three loyal customers
得到前三个最忠实的用户也就是由最多积分的顾客

USE sql_store;
SELECT * 
FROM customers
ORDER BY points DESC
LIMIT 3

chapter2-内连接

Inner Joins 在多张表格中检索数据

多张表格选取列
选取orders表中的order_id 但是要展示每位顾客的全名不在这个表中
(多张列表中有一样的列前面记得要加表格名)

USE sql_store;
SELECT o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id

exercise

order_items和products表连接每笔订单都返回产品id和名字连同order_item表的数量和单价。
同时是要使用别名简化的代码。

USE sql_store;
SELECT order_id,oi.product_id,name,quantity,oi.unit_price
FROM order_items oi
JOIN products p
	ON oi.product_id = p.product_id

跨数据库连接

USE sql_store;
-- 只需要给不在当前数据库的表加上前缀
SELECT *
FROM order_items oi 
JOIN sql_inventory.products p 
	ON oi.product_id = p.product_id

自连接 | self joins

sql_hr.employees
想要找到每个员工和它对应的管理人员的名字。

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

多表连接 | joining multiple Tables

写查询的时候怎么连接超过两张表

USE sql_store;
SELECT 
	o.customer_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

exercise

sql_invoicing payments
把payments表和payment_methods表以及clients表连接
生成一份报告显示付款和更多详细信息：客户姓名付款方式

USE sql_invoicing;
SELECT 
	p.date,
	p.invoice_id,
	p.amount,
	c.`name`,
	pm.`name`
FROM payments p
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
JOIN clients c 
	ON p.client_id = c.client_id

复合连接条件

如果一个表格里面有2个及以上的主键就是有复合主键
可能单列里面有重复数据，无法依靠一列来准确判断
我们要学会如何把存在复合主键的这张表和其他表连接起来

USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin 
	ON oi.order_id = oin.order_id
	AND oi.product_id = oin.product_id

隐式连接语法

-- SELECT *
-- FROM orders o
-- JOIN customers c
-- 	ON o.customer_id = c.customer_id
-- 以上还有一种方式就是用隐式连接语法写上面这条查询

-- Implpicit Join Syntax
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
-- 虽然支持 但是建议不要用 忘记打where子句 会出现交叉连接的结果

😒 外连接

INNER JOIN
OUTER JOIN
分为LEFT JOIN RIGHT JOIN
前面的一个表左连接全部显示
左连接和外连接中间的OUTER不用写

USE sql_store;
SELECT 
	c.customer_id,
	c.first_name,
	o.order_id
FROM orders o
LEFT JOIN customers c
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id

如果直接写JOIN关键词，就是在内连接
如果做左或右连接就是在做外连接。

exercise

写一段查询生成product_id name quantity
需要连接产品表和订单项目表
在内连接中我们只能看到有订单的产品，但是要做外连接也要显示没有订购过的产品即显示所有产品

USE sql_store;
SELECT 
	p.product_id,
	p.`name`,
	oi.quantity 
FROM order_items oi
RIGHT JOIN products p
	ON oi.product_id = p.product_id

多表外连接

USE sql_store;
SELECT *
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

避免使用右连接太乱了如果左右一起用所以尽量使用左连接

exercise

写上一段查询生成以下结果

USE sql_store;
SELECT 
	o.order_date,
	o.order_id,
	c.first_name,
	sh.`name`,
	os.`name` AS status
FROM orders o
JOIN  customers c
	ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
JOIN order_statuses os
	ON os.order_status_id = o.`status`

更多推荐

SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！

SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！

文章目录

介绍

chapter1-选择语句

选择子句

exercise

where子句

exercise

and or 和not运算符

exercise

in运算符

exercise

BETWEEN运算符

exercise

like运算符

exercise

regexp运算符

exercise

IS NULL 运算符

exercise

ORDER BY子句

exercise

LIMIT子句

exercise

chapter2-内连接

Inner Joins 在多张表格中检索数据

exercise

跨数据库连接

自连接 | self joins

多表连接 | joining multiple Tables

exercise

复合连接条件

隐式连接语法

😒 外连接

exercise

多表外连接

exercise

相关文章

发布评论取消回复

最近发表

热门文章

标签列表