MYSQL基础
第一章 SQL语句介绍
第二章 选择语句
2.1 选择语句 | The SELECT Statement[在单一表格中检索数据]
USE
-- 使用该数据库
USE sql_store;
注意:USE该数据库后该数据库会加粗显示
SELECT
-- 查询
-- 查询哪些数据列
-- SELECT 列名
-- *表示全部列
SELECT *;
-- 查询哪些数据列
SELECT customer_id
FROM
-- 在哪个表中查询
-- FROM 表名
FROM customers
WHERE
-- 筛选当customer_id = 1的数据
-- WHERE 列名+筛选条件
WHERE customer_id = 1
ORDER BY
-- 按照first_name顺序排列
-- ORDER BY 列名
ORDER BY first_name;
案例
-- 使用该数据库
USE sql_store;
SELECT *
FROM customers
ORDER BY first_name;
运行结果
2.2 选择子句 | The SELECT Clause
-- USE sql_store;可以注释是因为上节课中已经使用了这个数据库
-- SELECT 列名1,列名2…… 表中显示数据的顺序和查询的顺序一致,
-- 当所要查询的列名过长时,可以每行只表示一个列名
SELECT
first_name,
last_name,
points,
points*10+100,
-- 通过加小括号可以改变运算顺序,
-- AS 关键字可以用来给列名起别名,
-- 如果你想在别名中加入空格,
-- 可以用单引号或者双引号将别名置入
(points+10)*100 AS 'discount factor'
FROM customers;
运行结果
此外,SELECT还可以用来删除查询中重复出现的数据
第一步:
USE sql_store;
-- 查询表中州数据
SELECT state
FROM customers;
运行结果
此时数据中并没有重复数据,因此我们需要改变某个值来使表中出现重复state
第二步:
第三步:
此时再次运行第一步中的代码,得到
此时只需要在state前面加上DISTINCT即可删除重复项
SELECT DISTINCT state
FROM customers;
运行结果
Exercise
-- Exercise
-- 查询 name
-- unit_price
-- unit_price*1.1 并起别名new price
-- Exercise
USE sql_store;
SELECT
name,
unit_price,
unit_price*1.1 AS 'new price'
FROM products;
运行结果
2.3 WHERE 子句 | The WHERE Clause
WHERE子句筛选数据
-- 比较运算符
SELECT *
FROM customers
-- 注意在用到字符串时要加上'',或者"",
-- 这里VA的大小写无所谓,得到的结果一样
WHERE state <> 'VA';
-- 至于WHERE后的运算符,可以是比较运算符,比如:
-- >
-- >=
-- <
-- <=
-- = 注意SQL中的等于只有一个
-- != 或者 <> 注意这两个都表示不等于
运行结果
日期筛选
SELECT *
FROM customers
-- 虽然日期不是字符型,但是在SQL中必须要加上''或者""
WHERE birth_date > '1990-01-01';
运行结果
Exercise
-- Exercise
-- Get the orders placed this year
-- 得到今年(假设是2018)下的订单
-- Exercise
SELECT *
FROM orders
-- 虽然日期不是字符型,但是在SQL中必须要加上''或者""
-- 通过year函数来提取年份
WHERE year(order_date) = '2018';
运行结果
逻辑运算符(AND,OR,NOT)
-- AND OR
SELECT *
FROM Customers
WHERE birth_date > '1990-01-01' OR
(points > 1000 AND state = 'VA');
运行结果
SELECT *
FROM Customers
-- WHERE NOT(birth_date > '1990-01-01' OR points > 1000 );
-- 上面代码相当于以下:
WHERE birth_date <= '1990-01-01' AND points <= 1000 ;
Exercise
-- Exercise
-- From the order_items table,get the items
-- for order #6
-- where the total price is greater than 30
-- 从order_items表中,
-- 获取订单号为6的项目,
-- 并且项目的总价格大于30
-- Exercise
SELECT *
FROM order_items
WHERE order_id = 6 AND quantity*unit_price > 30;
-- quantity为数量
-- unit_price为单价
运行结果
2.4 IN 运算符 | The IN Operator
IN 运算符可以替代 OR
USE sql_store;
SELECT *
FROM customers
-- IN 运算符可以替代 OR 来进行判断数据是否符合多个条件中的一个
-- WHERE state = 'VA' OR state = 'FL' OR state = 'GA';
-- 以上语句可转化为以下:
WHERE state IN ('VA','FL','GA');
NOT IN
SELECT *
FROM customers
WHERE state NOT IN ('VA','FL','GA');
Exercise
-- Exercise
-- Return products with
-- quantity in stock equal to 49,38,72
-- 查询现货库存数量如下的产品 49,38,72
-- Exercise
SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72);
运行结果
2.5 BETWEEN 运算符 | The BETWEEN Operator
BETWEEN运算符可以部分替代AND
SELECT *
FROM customers
-- BETWEEN 关键字可替代当左右两边都可以取临界值时的条件
-- WHERE points >= 1000 AND points <= 3000;
-- BETWEEN 大于等于左数,小于等于右数,以下语句完全等同于上面
WHERE points BETWEEN 1000 AND 3000;
运行结果
Exercise
-- Exercise
-- Return customers born
-- between 1/1/1990 and 1/1/2000
-- 查询得到在1990年1月1日到2000年1月1日之间出生的顾客
-- Exercise
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2020-01-01';
运行结果
2.6 LIKE 运算符 | The LIKE Operator
SELECT *
FROM customers
-- last_name 中b开头,y结尾,中间有四个字符的数据
WHERE last_name LIKE 'b____y';
-- % any number of characters
-- % 表示任意长度的字符
-- _ single character
-- _ 表示单个字符
-- 有几个_就表示有几个字符
运行结果
Exercise
-- Exercise
-- Get the customers whose
-- 1.addresses contain TRAIL or AVENUE
-- 2.phone numbers end with 9
-- 1.查询得到地址中包含 trail 或者 avenue 的顾客
SELECT *
FROM customers
-- 这里用到 OR 运算符时一定要记得把前面的限制条件写上(即address LIKE)
WHERE address LIKE '%trail%' OR address LIKE '%avenue%';
运行结果
-- 2.查询得到电话号码以9结束的顾客
SELECT *
FROM customers
WHERE phone LIKE '%9';
运行结果
2.7 REGEXP 运算符 | The REGEXP Operator
REGEXP与LIKE有些许类似,但是比LIKE强大得多
SELECT *
FROM customers
-- REGEXP(regular expression 正则表达式) 在搜索字符串时的功能极为强大
-- WHERE last_name LIKE '%field%';
-- 等同于以下 , 但是没有% %
-- WHERE last_name REGEXP 'field';
WHERE last_name REGEXP '[abcdef]ield$|mac|rose';
-- ^ begining 以该字符开头
-- $ end 以该字符结尾
-- | logical OR 代表逻辑上的一个OR ,增加筛选条件
-- [abcd] 相邻字符为abcd之一,匹配单字符
-- [a-h] 相邻字符为a to h(abcdefgh)之一,匹配单字符
运行结果
Exercise
-- Exercise
-- Get the customers whose
-- 1.first names are ELKA or AMBUR
-- 2.last names end with EY or ON
-- 3.last names start with MY or contains SE
-- 4.last names contain B followed by R or U
-- 1.收集获得first_name是ELKA或者AMBUR的顾客
SELECT *
FROM customers
WHERE first_name REGEXP '^elka$|^ambur$';
运行结果
-- 2.收集获得last_name结尾是EY或者是ON的顾客
SELECT *
FROM customers
WHERE last_name REGEXP 'ey$|on$';
运行结果
-- 3.收集获得last_name开头是MY或者包含SE的顾客
SELECT *
FROM customers
WHERE last_name REGEXP '^my|se';
运行结果
-- 4.收集获得last_name中包含B,并且B后是R或者U的顾客
SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]';
运行结果
2.8 IS NULL 运算符 | THE IS NULL Operator
IS NULL 运算符用来搜索数据为空的数据元素
SELECT *
FROM customers
WHERE phone IS NULL;
运行结果
IS NOT NULL 运算符用来搜索数据不为空的数据元素
SELECT *
FROM customers
WHERE phone IS NOT NULL;
运行结果
Exercise
-- Exercise
-- Get the orders that are not shipped
-- 查询得到没有发货的订单
-- Exercise
SELECT *
FROM orders
WHERE shipped_date IS NULL;
运行结果
2.9 ORDER BY 子句 | TheORDER BY Clause
ORDER BY 子句用来对数据进行排序
未使用 ORDER BY子句的数据也会进行默认排序,即按照主键排序
SELECT *
FROM customers
-- [ASC]:升序排列(可省略) DESC:降序排列
ORDER BY state DESC,first_name
运行结果
MYSQL与其他数据库管理系统的一个区别是:
- MYSQL中你可以用任何列排序数据,尽管那个列并没有在 SELECT 子句中出现
举个例子:
SELECT first_name,last_name
FROM customers
ORDER BY birth_date;
运行结果
在MYSQL中会正常显示,但是在其他数据库管理系统中写这样的查询就会报错
–
其次,我们也可以用别名来进行排序
-- 这里的points并不是表中列名,而是一个别名
SELECT first_name,last_name,10 AS points
FROM customers
ORDER BY points,first_name;
运行结果
–
也可以通过列序号进行排序,比如:
-- 这里的points并不是表中列名,而是一个别名
SELECT first_name,last_name,10 AS points
FROM customers
ORDER BY 1,2;
运行结果同上图中的别名排序
但是这种排序方法在新增选择列名后,可能会指向新的列名
因此应尽量避免使用
Exercise
查询得到order_items中
order_id=2,并且总价格降序排列的所有数据
注意:总价格为数量*单价 ,即quantity*unit_price
-- Exercise
SELECT *,quantity*unit_price AS total_price
FROM order_items
WHERE order_id=2
ORDER BY total_price DESC;
运行结果
2.10 LIMIT 子句 | The LIMIT Clause
LIMIT用来返回指定行的数据
1.LIMIT num
若num<=最大行数,则返回前num行
否则返回全部行
比如:
SELECT *
FROM customers
LIMIT 3;
运行结果
2.LIMIT num1,num2
返回num1后的num2行数据
num1行不算在内
比如:
SELECT *
FROM customers
LIMIT 6,3;
运行结果
Exercise
-- Exercise
-- Get the top three loyal customers
-- 返回前三名积分最多的顾客
-- Exercise
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3;
运行结果
–
SELECT 语句
FROM 语句
WHERE 语句
ORDER BY 语句
LIMIT 语句放在最后
查询语句的顺序很重要,写错的话可能会报错
第三章 多表连接
3.1 内连接 | Inner Joins (在多张表中查询数据)
[INNER] JOIN 内连接
INNER可以省略不写
orders [AS] o / customer [AS] s 表名可以起别名用来下列的简便调用
但是一旦对表名起了别名,其他任何地方都不能再使用原来的表名,而必须使用新的别名
当要查询的列名在多张表都存在时,必须加上 前缀.
语法:
JOIN 表名
ON 连接方法,即找到两者中相同的,多数情况为主键
后面可继续接上JOIN ON语句来连接多张表
SELECT order_id,c.customer_id,first_name,last_name
FROM orders o
JOIN customers c
ON c.customer_id=o.customer_id;
运行结果
Exercise
--Exercise
-- 对照order items表
-- 写一段查询,把这张表和products表连接
-- 每笔订单都返回产品id和名字,连同order items表的数量和单价
-- Exercise
-- 这里使用oi.unit_price而不是p.unit_price的原因是,
-- products可能会随时改变,而order_price才是下订单时的price
-- product_id也一样
SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
JOIN products p
ON p.product_id=oi.product_id;
运行结果
3.2 跨数据库连接 | Joining Across Databases
跨数据库连接表,即连接不在同一数据库下的表
语法:
数据库名.表名
只需在 不在该数据库下的表名前加上前缀
USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products s
ON oi.product_id=s.product_id;
运行结果
3.3 自连接 | Self Joins
自连接就是将相同的两张表通过不同的别名,
使用不同的列连接起来,
并通过不同的别名来区分表示列名
USE sql_hr;
-- 可理解为有第一张员工表,第二张领导表
-- 员工表的reports_to与领导表的employee_id想连接形成一张大表
-- 这里因为id为37270的员工没有reports_to所以第一个表中没有了这一行
-- 由于其余所有的员工reports_to都为37270,所以第二张表中信息全为id 37270
SELECT
e.employee_id,
e.first_name,
m.employee_id AS manager_id,
m.first_name AS manager_first_name
FROM employees e
JOIN employees m
ON e.reports_to=m.employee_id;
运行结果
3.4 多表连接 | Joining Multiple Tables
使用JOIN ON进行多表连接
USE sql_store;
SELECT
order_id,
order_date,
first_name,
last_name,
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
1.使用invoicing数据库
2.连接clients,payment_methods,payments三张表
3.查询得到订单日期,订单id,消费金额,顾客名字,支付方式
USE invoicing;
-- client_id 顾客id
-- invoice_id 订单id
-- status 支付方式
SELECT
p.date,
p.invoice_id,
p.amount,
c.client_id,
c.name AS customer_name,
pm.name AS status
FROM payments p
JOIN clients c
ON p.client_id=c.client_id
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
运行结果
3.5 复合连接条件 | Compound Join Conditions
当一个表中不能通过一个列来唯一识别时,即这张表的结构为复合主键时
这时我们可以通过 JOIN ON AND 来连接两张表
USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.product_id=oin.product_id
AND oi.order_id=oin.order_id
运行结果
3.6 隐式连接语法 | Implicit Join Syntax
虽然MYSQL中允许使用隐式连接语法,但是为了避免出错,
最好使用显式连接,因为使用显示连接让你不得不注意语法规范
相反地、使用隐式连接可能会漏写WHERE语句
以下是分别通过显式和隐式来进行内连接
-- 显式连接语句
SELECT *w
FROM customers c
JOIN orders o
ON c.customer_id=o.customer_id;
-- 隐式连接语句
SELECT *
FROM customers c,orders o
-- 可能会漏写WHERE语句,造成交叉查询的结果
WHERE c.customer_id=o.customer_id;
运行结果
3.7 外连接 | Outer Joins
语法:
LEFT/RIGHT [OUTER] JOIN
通过外连接可以实现 ,即使不满足连接条件,也可以实现查询的效果
-- 左连接
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id=o.customer_id
ORDER BY c.customer_id;
运行结果
-- 右连接
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id=o.customer_id
ORDER BY c.customer_id;
运行结果
Exercise
-- 写一段查询,实现外连接products和order_items
-- 以实现查询得到含有quantity列为null的product_id
SELECT
p.product_id,
p.name,
o.quantity
FROM products p
LEFT JOIN order_items o
ON p.product_id=o.product_id
ORDER BY p.product_id;
运行结果
3.8 多表外连接 | Outer Join Between Multiple Tables
多表外连接和多表内连接类似
不同在于返回表中数据的数量不同
最好使用左外连接,避免使用右外连接。
因为当SQL语句数量过多时,如果存在多个连接(包含内外),此时不易辨别表的连接结构
当使用JOIN来连接orders和shippers时
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id=c.customer_id
JOIN shippers s
ON o.shipper_id=s.shipper_id;
运行结果
当使用LEFT JOIN来连接orders和shippers时
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id=c.customer_id
LEFT JOIN shippers s
ON o.shipper_id=s.shipper_id;
运行结果
Exercise
-- 写一段查询实现以下返回结果
USE sql_store;
SELECT
order_date,
o.order_id,
first_name,
s.name AS shipper,
os.name
FROM orders o
JOIN customers c
ON o.customer_id=c.customer_id
LEFT JOIN shippers s
ON o.shipper_id=s.shipper_id
LEFT JOIN order_statuses os
ON o.status=os.order_status_id
ORDER BY os.name
运行结果
3.9 自外连接 | Self Outer Jions
自外查询和自连接类似
不同在于返回表中数据的数量不同
当使用JOIN来自连接时
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
运行结果
当使用LEFT JOIN来自外连接时
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.reports_to=m.employee_id
运行结果
3.10 USING子句 | The USING Clause
USING子句用于替换当连接条件的列名相同时的情况
使用USING子句可以使连接条件更加清楚
USING(列名1...列名n)
SELECT
o.order_id,
c.first_name,
s.shipper_id AS shipper
FROM customers c
JOIN orders o
-- ON o.customer_id=c.customer_id
USING(customer_id)
LEFT JOIN shippers s
-- ON o.shipper_id=s.shipper_id;
USING(shipper_id)
运行结果
Exercise
-- 写一段查询实现以下返回结果
SELECT
date,
c.name AS client,
amount,pm.name
FROM payments p
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
JOIN clients c
USING(client_id)
运行结果
3.11 自然连接 | Natural Joins
自然连接可以通过表中相同的列名进行连接
但是连接的结果不是可控的
因此在写连接语句时,应尽量避免该语句的出现
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customers c
运行结果
3.12 交叉连接 | Cross Joins
交叉连接是指将两张表的中 每张表的每一列进行映射连接
连接后的表即为两张表自由组合后的表
显式写法
USE sql_store;
SELECT
c.first_name AS customer,
p.name AS product_name
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
运行结果
隐式写法
SELECT
c.first_name AS customer,
p.name AS product_name
FROM customers c,products p
ORDER BY c.first_name
Exercise
-- Do a cross join between shippers and products
-- using the implicit syntax
-- and then using the explicit syntax
使用交叉连接实现连接shippers表和products表
使用隐式写法,然后再使用显式写法
隐式
SELECT *
FROM shippers,products
ORDER BY product_id
显式
SELECT *
FROM shippers
CROSS JOIN products
ORDER BY product_id
运行结果
3.13 联合 | Unions
前面的连接都是不同列之间的连接
Union是行的连接,即多个行数据的连接
需要注意的是:
1.进行联合时要注意连接的列数一定要一致
2.联合后返回的列名于第一个查询返回的列名一致
USE sql_store;
SELECT
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date>='2019-01-01'
UNION
SELECT
order_id,
order_date,
'Archive' AS status
FROM orders
WHERE order_date<'2019-01-01';
运行结果
SELECT name
FROM shippers
UNION
SELECT first_name
FROM customers
运行结果
SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers
运行结果
Exercise
-- 执行一段查询,得到以上的返回结果
-- 新增一个列,名为type,规定:
-- 1.积分小于2000为Bronze(青铜)
-- 2.积分小于等同于3000且大于等于2000为Silver(白银)
-- 3..积分大于3000为Gold(黄金)
-- 注意表中对名字进行了排序
SELECT
customer_id,
first_name,
points,
'Bronze' AS type
FROM customers
WHERE points<2000
UNION
SELECT
customer_id,
first_name,
points,
'Silver' AS type
FROM customers
WHERE points>=2000 AND points<=3000
UNION
SELECT
customer_id,
first_name,
points,
'Gold' AS type
FROM customers
WHERE points>3000
ORDER BY first_name;
运行结果