数据库学习
一、
SELECT *
FROM customers
WHERE last_name LIKE 'b____y'
第二章
1.正则表达式
SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e'
^ 代表字符串开头
$ 代表字符串结尾
| 代表一个逻辑或 OR， 这样就可以给出多个搜索模式
[] 匹配任意在括号里列举的单字符
[a-h] 表示从a-h的任意字母
exercise
SELECT *
FROM customers
2.排序
ORDER BY
SELECT *, quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC
3.限定查询返回的记录
SELECT *
FROM customers
LIMIT 6, 3
6被称为偏移量，跳过前6条记录然后获取三条记录
Exercise
获取积分最多的前三名顾客
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3
注：LIMIT 语句要放到最后，语句顺序很重要
第三章 表之间的连接
1.内连接
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
ON o .customer_id = c.customer_id
别名AS 可以省略
练习
SELECT order_id, o.product_id, quantity, o.unit_price
FROM order_items o
JOIN products p
ON o.product_id = p.product_id
☆多张表有一样的表名是，调用的时候要添加表名称前缀，让他生效。
SELECT 返回结果
2.跨数据库连接
USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id
只需要给不在当前数据库的表加前缀
3.自连接
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.多表连接
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
表连接尽量不要超过3张，所有的表链接都会一定的影响查询速度
练习
USE sql_invoicing;
SELECT
p.date,
p.invoice_id,
p.amount,
c.name,
pm.name AS payment_method
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
5.复合连接条件
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
复合主键，包含超过了一列。
6.隐式连接语法
显式连接语法（正常下的操作）
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
★建议不要使用，如果忘记写WHERE o.customer_id = c.customer_id ，则会得到交叉连接的结果！（笛卡儿积）
即：订单表里的每条记录都和顾客表里的每条记录连接了。
7.外连接
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
LEFT JOIN 做连接：左边这个表里的所有记录都会返回，不管正确与否。
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM orders o
RIGHT JOIN customers c
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
练习
SELECT
p.product_id,
name,
quantity
FROM products p
LEFT JOIN order_items oi
ON p.product_id = oi.product_id
8.多表外连接（尽量使用左连接）
SELECT
c.customer_id,
c.first_name,
o.order_id,
sh.name AS shipper
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
练习
SELECT
o.order_date,
o.order_id,
c.first_name,
sh.name AS shipper,
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
LEFT JOIN order_statuses os
ON o.status = os.order_status_id
9.自外连接
USE sql_hr;
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
10. USING子句
使用前提是一样的字段名称
注：USING 只能在不同表中的列名字完全一样的情况下使用。
SELECT
o.order_id,
c.first_name
FROM orders o
JOIN customers c
USING(customer_id)
SELECT *
FROM order_items oi
JOIN order_items_notes oin
USING (order_id, product_id)
练习
USE sql_invoicing;
SELECT
p.date,
c.name AS client,
p.amount,
pm.name AS payment_methods
FROM payments p
JOIN clients c
USING (client_id)
JOIN payment_methods pm
ON pm.payment_method_id = payment_method
11.自然连接
数据库引擎自己选择 相同名称的列连接
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customers c
注：不建议使用
12.交叉连接
顾客表里的每条记录都会和产品表里的每条记录结合。
显式交叉连接
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
**隐式交叉连接 **（不推荐）
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c,products p
ORDER BY c.first_name
练习
SELECT
sh.name AS shipper,
p.name AS product
FROM shippers sh， products p
ORDER BY sh.name
SELECT
sh.name AS shipper,
p.name AS product
FROM shippers sh
CROSS JOIN products p
ORDER BY sh.name
13.联合UNION
合并多段查询的记录,可以是同一个表也可以是不同表。
注：查询返回的结果一定要一样，否则会出错。
第一段查询决定返回结果的列名
SELECT
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT
order_id,
order_date,
'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'
练习
SELECT
customer_id,
first_name,
points,
'Bronze' AS type
FROM customers
WHERE points < 2000
UNION
SELECT
customer_id,
first_name,
points,
'Sliver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
customer_id,
first_name,
points,
'Gold' AS type
FROM customers
WHERE points > 3000
ORDER BY first_name
第四章 数据库的设计
1.列属性
INT 整型
*VARCHAR（50）*可变字符的缩写，这列最多可以有50个字符，不会因不够而浪费空间。一般用来存储字符串和文本值。
CHAR(50) 顾客名只有5个字符，MySQL会在插入45个空格符填满这列，会浪费空间。
PK—主键列的缩写
NN—非空值，决定该列是否可以写空值
AI— 自动递增的意思，通常被用来主键列
2.插入单行
字符串和日期值要带上引号，带引号双引号都行。
INSERT INTO customers
VALUES (DEFAULT,
'John',
'Smith',
'1990-01-01',
NULL,
'address',
'city',
'CA',
DEFAULT)
等同
INSERT INTO customers(
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (
'John',
'Smith',
'1990-01-01',
'address',
'city',
'CA')
3.插入多行
INSERT INTO shippers(name)
VALUES ('shipper1'),
('shipper2'),
('shipper3')
一对括号一行
练习
INSERT INTO products(name, quantity_in_stock, unit_price)
VALUES ('product1',1,2),
('product2',2,3),
('product3',3,4)
4.插入分层行
INSERT INTO orders(customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);
INSERT INTO order_items
VALUES (last_insert_id(),2, 1, 3.95),
(last_insert_id(),1, 1, 2.95)
LAST_INSERT_ID （获取最近插入的id，即新生成的id）
5.创建表复制
从一张表复制数据到另一张表
CREATE TABLE orders_archived AS
SELECT * FROM orders
用这个技巧创建表时，MySQL会忽略主键等属性
子查询是部分属于另外一段SQL语句的选择语句
Teuncate Table 截断表（删除表中所有行）
使用选择语句作为插入语句中的子查询的一个例子：
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'
练习
CREATE TABLE invoices_archived AS
SELECT
i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_total,
i.invoice_date,
i.payment_date,
i.due_date
FROM invoices i
JOIN clients c
ON i.client_id = c.client_id
WHERE payment_date IS NOT NULL
DROP table 删除表
6.更新单行
UPDATE invoices
SET payment_total = 10, payment_date = '2019-03-01'
WHERE invoice_id = 1
如果更新错行，则使用如下，（默认的值）
UPDATE invoices
SET payment_total = DEFAULT, payment_date = NULL
WHERE invoice_id = 1
客户在截止日期付了百分之50的支票
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE invoice_id = 3
7.更新多行
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id = 3
练习：1990年前出生的人加50积分
USE sql_store;
UPDATE customers
SET points = points + 50
WHERE birth_date <'1990-01-01'
8.在UPDATE中用子查询
用子查询
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id IN
(SELECT client_id
FROM clients
WHERE state IN ('CA','NY'))
不用子查询
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE payment_date IS NULL
注：以上两种使用前最好先确定一下查询的内容是否正确！
练习
积分大于3000的顾客，在注释中标注“Gold customer”
USE sql_store;
UPDATE orders
SET comments = 'Gold customer'
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
9.删除行
DELETE FROM invoices
WHERE client_id = (
SELECT client_id
FROM clients
WHERE name = 'Myworks')
10.恢复数据库
打开数据库脚本，重新执行
第五章
1.聚合函数（汇总数据）(只运行非空值)
为数据汇总编写查询
USE sql_invoicing;
SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total) AS total,
COUNT(invoice_total) AS number_of_invoices,
COUNT(payment_date) AS count_of_payments,
COUNT(*) AS total_records
FROM invoices
去掉重复值↓
COUNT(DISTINCT client_id) AS total_records
练习
SELECT
'First half of 2019' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT
'Second half of 2019' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT
'total' AS date_range,
SUM(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31'
2. GROUP BY 子句
注意顺序
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC
group by语句永远在from和where子句之后
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY client_id
练习
SELECT
date,
pm.name AS payment_method,
SUM(p.amount) AS total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY date, payment_method
ORDER BY date
3. HAVING子句
HAVING 在分组行后筛选数据
WHERE在分组行之后筛选数据
SELECT
client_id,
SUM(invoice_total) AS total_sales,
COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5
HAVING 中用到的列必须是SELECT中提到的列！
WHERE中不存在这个问题。
练习
USE sql_store;
SELECT
c.customer_id,
c.first_name,
c.last_name,
state,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING(order_id)
WHERE state = 'VA'
GROUP BY
c.customer_id,
c.first_name,
c.last_name
HAVING total_sales > 100
4. ROLLUP运算符（仅MySQL中有）
只应用于聚合值得列
USE sql_invoicing;
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP
每个组及每个结果得汇总值 ↓
USE sql_invoicing;
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY state,city WITH ROLLUP
练习
SELECT
pm.name AS payment_method,
SUM(amount) AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP
注：用rollup运算符得时候，不能在group by子句中使用列别名
第六章
1.恢复数据库
2.子查询
先执行内查询，再执行外查询。
SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)
练习
找到高于平均雇员工资的雇员。
USE sql_hr;
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
3. IN运算符
SELECT *
FROM products
WHERE product_id NOT IN(
SELECT DISTINCT product_id
FROM order_items
)
练习
SELECT *
FROM clients
WHERE client_id NOT IN (
SELECT DISTINCT client_id
FROM invoices
)
4.子查询VS连接
与3中的练习一样的功能，根据情况适当选择
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
whERE invoice_id IS NULL
练习 用两种方法找出购买生菜的顾客
USE sql_store;
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_id IN (
SELECT order_id
FROM order_items
WHERE product_id = 3
)
)
SELECT DISTINCT customer_id, first_name, last_name
FROM customers
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
SELECT o.customer_id
FROM order_items oi
JOIN orders o USING (order_id)
WHERE product_id = 3
)
5. ALL关键字
ALL关键字会将所有满足条件的记录返回出来
且可以有好几个需求值时，也可以返回。>ALL(120，150，20，…)
大于括号里的所有项
每当用了ALL关键字，都可以用MAX聚合函数改写 两者都好用
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
SELECT *
FROM invoices
WHERE invoice_total > ALL(
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
6. ANY关键字
ANY和SOME关键字一样，得到高于子查询返回的任意值。
-- 使用IN运算符
SELECT *
FROM clients
WHERE client_id IN(
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) > 2
)
-- 不使用IN运算符使用‘= ANY（）’
SELECT *
FROM clients
WHERE client_id = ANY (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) > 2
)
-- ANY 和 SOME 一样
SELECT *
FROM clients
WHERE client_id = SOME (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) > 2
)
=ANY 与 IN等效
7.相关子查询
使用相关子查询的时候在，这段查询会在主查询的每一行的层面执行
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id
)
练习
USE sql_invoicing;
SELECT *
FROM invoices i
WHERE invoice_total > (
SELECT AVG(invoice_total)
FROM invoices
WHERE client_id = i.client_id
)
8. EXISTS运算符
与IN 运算符相比，在记录多时使用EXISTS运算符提高效率。
IN运算符的子查询返回多个值给外查询，而EXIESTS返回的是一个指令（TRUE || FALSE），如果在相关子查询中找到满足条件的记录，则返回给EXISTS一个true，记录下这个记录。
SELECT *
FROM clients
WHERE client_id IN (
SELECT DISTINCT client_id
FROM invoices
)
SELECT *
FROM clients
JOIN invoices USING (client_id)
SELECT *
FROM clients c
WHERE EXISTS (
SELECT client_id
FROM invoices
WHERE client_id = c.client_id
)
练习
USE sql_store;
SELECT *
FROM products p
WHERE NOT EXISTS (
SELECT product_id
FROM order_items
WHERE product_id = p.product_id
)
SELECT *
FROM products
WHERE product_id NOT IN(
SELECT DISTINCT product_id
FROM order_items
)
9. SELECT子句中的子查询
表达式中不能使用列的别名
第六行：利用SELECT来使用别名用于表达式
SELECT
invoice_id,
invoice_total,
(SELECT AVG(invoice_total)
FROM invoices) AS invoice_average,
invoice_total - (SELECT invoice_average) AS difference
FROM invoices
练习
SELECT
client_id,
c.name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total)
FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM invoices
RIGHT JOIN clients c USING(client_id)
GROUP BY client_id
MOSH答案
SELECT
client_id,
name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total)
FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c
10. FROM子句中的子查询
每当我们在FROM子句中使用子查询，我们需要给子查询一个别名（这是必选项），不管我们会不会使用到别名
SELECT *
FROM (
SELECT
client_id,
name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total)
FROM invoices) AS average,
-- (SELECT total_sales) - (SELECT average) AS difference
(SELECT total_sales - average) AS difference
FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL
作为视图存储在数据库中
可以用视图来存储上面FROM子句中的内容，会大大简便查询。
第七章 MySQL内置函数
1.数值函数
SELECT ROUND(5.73)
SELECT ROUND(5.73,1)
SELECT ROUND(5.7345,2)
SELECT TRUNCATE(5.7365,2)
SELECT CEILING(5.7)
SELECT FLOOR(5.7)
SELECT ABS(-5.2)
SELECT RAND()
2.字符串函数
SELECT LENGTH('sky')
SELECT UPPER('sky')
SELECT LOWER('Sky')
SELECT LTRIM(' Sky')
SELECT RTRIM('Sky ')
SELECT TRIM(' Sky ')
SELECT LEFT('Kindergarten',4)
SELECT RIGHT('Kindergarten',6)
SELECT SUBSTR('Kindergarten', 3, 5)
SELECT SUBSTR('Kindergarten', 3)
SELECT LOCATE('n','Kindergarten')
SELECT LOCATE('garten','Kindergarten')
SELECT REPLACE('Kindergarten','garten','garden')
SELECT CONCAT('first','last')
USE sql_store;
SELECT CONCAT(first_name,' ', last_name)
FROM customers
3.日期函数
SELECT NOW(),
CURDATE(),
CURTIME(),
YEAR(NOW()),
MONTH(NOW()),
DAY(NOW()),
HOUR(NOW()),
MINUTE(NOW()),
SECOND(NOW()),
DAYNAME(NOW()),
MONTHNAME(NOW()),
EXTRACT(YEAR FROM NOW())
练习
返回当前年份的订单
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())
4.格式化日期和时间
SELECT DATE_FORMAT(NOW(),'%m %d %y')
SELECT TIME_FORMAT(NOW(),'%H:%i %p')
5.计算日期和时间
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY),
DATE_ADD(NOW(), INTERVAL 1 YEAR),
DATE_ADD(NOW(), INTERVAL -1 YEAR),
DATE_SUB(NOW(), INTERVAL 1 YEAR),
DATEDIFF('2019-01-05', '2019-01-01'),
DATEDIFF('2019-01-01', '2019-01-05'),
TIME_TO_SEC('9:00'),
TIME_TO_SEC('9:00') - TIME_TO_SEC('9:02')
6. IFNULL和COALESCE函数
IFNULL函数：我们用其他内容替换空值
COALESCE函数：我们提供一堆值，返回这堆值中的第一个非空值
USE sql_store;
SELECT
order_id,
IFNULL(shipper_id,'Not assigned') AS shipper
FROM orders
USE sql_store;
SELECT
order_id,
COALESCE(shipper_id, comments, 'Not assigned') AS shipper
FROM orders
练习
SELECT
CONCAT(first_name, ' ', last_name) AS customer,
IFnull(phone, 'Unkown') AS phone
FROM customers
7.IF函数
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
