SQL进阶教程

admin管理员组

文章数量:1794759

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
  • 复合连接条件
  • 隐式连接语法
  • :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