文章目录
- 介绍
- 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大师!!
发布评论