MySQL 深挖基础 —— 增删改查

基础——我觉得对开发来说是很重要的，下面是MySQL的基础，没事看看，总是好的。

# 导入 mysqlsampledatabase.sql 到本地文件

# ----------------------------------- Chapter 1 -----------------------------------
# SELECT 语句
# SELECT 语句允许从 table(表) 和 view(视图) 中获取数据；
#
# 语法结构：
SELECT
column_1, column_2, ...
FROM
table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;
# SELECT 实战
# 1. 获取 employees 表中 lastName,firstName,jobTitle 三个字段的结果集。
select
lastName,firstName,jobTitle
from
employees;

# 2. 获取 employees 表中 所有字段 的结果集
select
*
from
employees;

# ----------------------------------- Chapter 2 -----------------------------------
# ORDER BY 语句
# 对结果集进行排序
#
# 升序(ASC)和降序(DESC)
# 语句结构
SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

# ORDER BY 实战
# 1. 查询 customers 表获取 contactFirstName,contactLastName 的结果集，并对 lastName 升序排列
# 降序请使用 desc;
select
contactFirstName,contactLastName
from
customers
order by
contactLastName asc;
# 2. 查询 customers 表获取 contactFirstName,contactLastName 的结果集，并对 lastName 降序，firstName 升序
select
contactLastName,contactFirstName
from
customers
order by contactLastName desc,contactFirstName asc;
# **** 3. 通过一个表达式对结果集进行排序
# 查询表orderdetails, 并且获取字段ordernumber,orderlinenumber,quantityOrdered * priceEach
select
ordernumber, orderlinenumber, FORMAT(quantityOrdered * priceEach, 2)
from
orderdetails
order by ordernumber , orderLineNumber, quantityOrdered * priceEach;
# 内置函数 format(number, decimal_place); 返回一个 string
# 如果要求是数值，推荐使用ROUND(number,decimal_palce); 返回一个number
select
orderNumber, orderLineNumber, ROUND(quantityOrdered * priceEach, 2) as subtotal
from
orderdetails
order by orderNumber , orderLineNumber, quantityOrdered * priceEach;
# 对结果集使用FIELD()函数进行排序，指定值进行排序
# **** 4. 在表中 orders 中的status字段，有如下固定值：
# 'On Hold'，'In Process'，'Cancelled'，'Shipped' ，'Disputed'，'Resolved'
# 对结果集按status中的值的如下顺序排序：
# 'In Process','On Hold','Cancelled','Resolved','Disputed','Shipped'
select
orderNumber,status
from
orders
order by
FIELD(
status,
'In Process','On Hold','Cancelled','Resolved','Disputed','Shipped'
);

# ----------------------------------- Chapter 2 -----------------------------------
# DISTINCT 语句
# 作用是去除重复语句，所谓的重复设置 row1 和 row2 完全是样的，使用distinct只获取唯一数据组成的结果集
# DISTINCT 实战
# 1. employees 表中有重复的lastName字段值，用distinct获取employees中的lastName的结果集
SELECT DISTINCT
lastName
FROM
employees;
# 2. distinct 和 null -- customers 表的 state 字段包含有 null 值
# 获取到唯一的state同时包含一套NULL数据，如何取出null呢？
SELECT DISTINCT
state
from customers;
# 3. distinct 和 null -- 除去null值并获取结果集中唯一数据
# -- 法一
SELECT DISTINCT
state
FROM customers
WHERE state is not null;
# -- 法二
SELECT DISTINCT
IFNULL(state,'Unknow State')
FROM customers;
# 4.
# SQL1
SELECT state FROM customers GROUP BY state;
# SQL2
SELECT DISTINCT state FROM customers;
# 的区别是，order by 对结果集进行排序，而distinct不是。
# 5. distinct 和 聚合函数
# 查询在美国的用户的唯一状态(state)数据
SELECT
COUNT(DISTINCT state)
FROM
customers
WHERE
country = 'USA';
# 6. distinct 和 limit 语句
# 查询表customers中用户的唯一(不重复)状态，且这个状态不为NULL，获取头5条记录。
SELECT DISTINCT
state
FROM
customers
WHERE
state IS NOT NULL
LIMIT 5;

# ----------------------------------- Chapter 3 -----------------------------------
# WHERE 语句，我认为比较简单，故列举几个简单的例子如下，
# 运算符：= > < >= <= <>(不等于) !=(不等于，和<>一个意思)
# cluase 1.
SELECT
e.lastName, e.firstName, e.jobTitle
FROM
employees as e
WHERE
jobTitle = 'Sales Rep' AND officeCode = 1;

# ----------------------------------- Chapter 4 -----------------------------------
# LIMIT 语句
# 语句结构
SELECT
column1,column2,...
FROM
table
LIMIT offset , count;
# offset 表示 第一行数据，默认是0，并且可以省略，省略表示取出头 count 条数据
# count 表示 取出多少条数据。
# 1. 取出最高，或者最低的数据的前5条, 配合 ORDER BY 对需求字段进行排序后取出对应条数的数据
# case: highest
SELECT
c.customerNumber,c.customerName,c.creditLimit
FROM
customers AS c
ORDER BY c.creditLimit DESC
LIMIT 0, 5;
# case: lowest
SELECT
customernumber, customername, creditlimit
FROM
customers
ORDER BY creditlimit ASC
LIMIT 5;

# ----------------------------------- Chapter 4 -----------------------------------
# IN 、NOT IN 运算符
SELECT
column1,column2,...
FROM
table_name
WHERE
(expr|column_1) IN [or NOT IN] ('value1','value2',...);
# IN 语句的子查询
# 查找 orders 表中 合计（quantityOrdered*priceEach）> 60000 的订单。
# if you want to find orders whose total amounts are greater than 60000, you use the IN operator as the following query
# ****** IMPORTANT CLAUSE
select
o.orderNumber,o.customerNumber,o.status,o.shippedDate
from
orders as o
where
o.orderNumber in
(# 下面是子查询语句, 下面语句应用了 group by, having 和 聚合函数 sum
select
d.orderNumber
from
orderdetails as d
group by
d.orderNumber
having sum(d.quantityOrdered * d.priceEach) > 60000
);

# ----------------------------------- Chapter 4 -----------------------------------
# [NOT] BETWEEN ... AND ...
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice >= 90 AND buyPrice <= 100;
# 等价于
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice BETWEEN 90 AND 100;
# NOT BETWEEN
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice NOT BETWEEN 20 AND 100;

# BETWEEN 和 日期格式
SELECT
orderNumber, requiredDate, status
FROM
orders
WHERE
requireddate BETWEEN CAST('2003-01-01' AS DATE) AND CAST('2003-01-31' AS DATE);
# 关于 CAST() 内置函数
# 用法 CAST(value as dataType)
# -> dataType 可以是 BINARY, CHAR, DATE, DATETIME, TIME,DECIMAL, SIGNED, UNSIGNED
# e.g.
SELECT (1 + CAST('1' AS UNSIGNED))/2; => 1.0000
SELECT CONCAT('MySQL CAST example #',CAST(2 AS CHAR));

# ----------------------------------- Chapter 5 -----------------------------------
# LIKE 模糊查询
# 通配符
# % 表示一串未知字符，用长度表示 0-n
# _ 表示一个未知字符，用长度表示 1
# 1.
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
firstName LIKE 'a%';
# 2.
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
lastName LIKE '%on';
# 3.
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
lastname LIKE '%on%';
# 4.
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
firstname LIKE 'T_m';
# 5. NOT LIKE
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
lastName NOT LIKE 'B%';
# 6. 转义 \_ , 表示 _ 字符串
SELECT
productCode, productName
FROM
products
WHERE
productCode LIKE '%\_20%';
# 等价于 ESCAPE
SELECT
productCode, productName
FROM
products
WHERE
productCode LIKE '%$_20%' ESCAPE '$';

# ----------------------------------- Chapter 6 -----------------------------------
# UNION 语句
SELECT column1, column2
UNION [DISTINCT | ALL]
SELECT column1, column2
UNION [DISTINCT | ALL]
# 关键点：
# 被 UNION 的 每个查询语句中的字段数相等；
# 字段类型相同或者至少能转换为相同；
# 如果union1 和unionN 的字段名不同，我们么有取别名，那么取第一个union中的查询字段为显示字段
# 默认查询的结果中是去重的，要显示所有条数使用 UNION ALL
# 1. e.g: 145 rows
# 取别名，使列等同
SELECT customerNumber id, contactLastname name
FROM customers
UNION ALL
SELECT employeeNumber id,firstname name
FROM employees;
# 2. e.g:
# 不取别名，使用第一条select中的字段
SELECT customerNumber, contactLastname
FROM customers
UNION ALL
SELECT employeeNumber,firstname
FROM employees;
# UNION 和 ORDER BY
(SELECT customerNumber id,contactLastname name
FROM customers)
UNION
(SELECT employeeNumber id,firstname name
FROM employees)
ORDER BY name,id;# ORDER BY 对结果集(别名在结果集中)进行排序
# 等价于
SELECT customerNumber, contactLastname
FROM customers
UNION ALL
SELECT employeeNumber,firstname
FROM employees;
# UNION 和 ORDER BY
(SELECT customerNumber id,contactLastname name
FROM customers)
UNION
(SELECT employeeNumber id,firstname name
FROM employees)
ORDER BY 2,1;# ORDER BY 对结果集(别名在结果集中)进行排序，替代方案。

# 如果对每一条 select 进行排序，不会影响到 UNION 运算符的结果。
(SELECT
customerNumber id,contactLastname name
FROM
customers
ORDER BY
name, id)
UNION
(SELECT
employeeNumber id,firstname name
FROM
employees
ORDER BY
name,id
);

# ----------------------------------- Chapter 7 -----------------------------------
# 关联查询 INNER JOIN
# 查询出所有表中可以提取的公共部分
# 1.
SELECT
productCode, productName, textDescription
FROM
products T1
INNER JOIN
productlines T2 ON T1.productline = T2.productline;
# INNER JOIN 和 GROUP BY
SELECT
T1.orderNumber,
status,
ROUND(SUM(quantityOrdered * priceEach), 2) total
FROM
orders AS T1
INNER JOIN
orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber;

# ----------------------------------- Chapter 7 -----------------------------------
# LEFT JOIN
SELECT
T1.c1, T1.c2, T2.c1, T2.c2
FROM
T1
LEFT JOIN
T2 ON T1.c1 = T2.c1;
# 1. 实例 - 查询以左表为准的数据
SELECT
c.customerNumber, c.customerName, orderNumber, o.status
FROM
customers c
LEFT JOIN
orders o ON c.customerNumber = o.customerNumber;
# 2. 实例 - 查询不对应的数据
SELECT
c.customerNumber, c.customerName, orderNumber, o.status
FROM
customers c
LEFT JOIN
orders o ON c.customerNumber = o.customerNumber
WHERE
orderNumber IS NULL;

# ----------------------------------- Chapter 8 -----------------------------------
# SELF JOIN
# 一个表和自己作连接查询
# 1. 查找雇员以及对应的上级名称
select
concat(e.firstname,' ', e.lastname) as employee_name,
concat(m.firstname,' ', m.lastname) as direct_report
from
employees as e
inner join
employees as m
on e.reportsto = m.employeeNumber;
# 2. 上面的查询会出现null
# 1)
select
concat(e.firstname,' ', e.lastname) as employee_name,
ifnull(concat(m.firstname,' ', m.lastname), 'Top Manager') as direct_report
from
employees as e
left join
employees as m
on e.reportsto = m.employeeNumber;
# 2) 和 1) 是异曲同工，但需要使用排序
SELECT
IFNULL(CONCAT(m.lastname, ', ', m.firstname),
'Top Manager') AS 'Manager',
CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
employees e
LEFT JOIN
employees m ON m.employeeNumber = e.reportsto
ORDER BY manager DESC;
# 3.
SELECT
c1.city, c1.customerName, c2.customerName
FROM
customers c1
INNER JOIN
customers c2 ON c1.city = c2.city
AND c1.customername <> c2.customerName
ORDER BY c1.city;

# ----------------------------------- Chapter 9 -----------------------------------
# GROUP BY 语句 ----- 经常配合聚合函数一起使用
# 结构如下
SELECT
c1, c2,..., cn, aggregate_function(ci)
FROM
table
WHERE
where_conditions
GROUP BY c1 , c2,...,cn;
# 1. 仅仅是分组获取某个列的值
SELECT
status
FROM
orders
GROUP BY status;
# 等价于
SELECT DISTINCT
status
FROM
orders;
# 2. 配合聚合函数进行 - 获取了status的值，计算得出了各个status的行数
SELECT
status, COUNT(*)
FROM
orders
GROUP BY status;
# 3. 求出多表中某一状态值下和（合计）的总值
SELECT
status, TRUNCATE(SUM(quantityOrdered * priceEach),2) AS amount
FROM
orders
INNER JOIN
orderdetails USING (orderNumber) # using(orderNumber) 等价于 on orders.orderNumber = orderdetails.orderNumber
GROUP BY status;
# 4. 获取每笔订单的总和
SELECT
orderNumber,
TRUNCATE(SUM(quantityOrdered * priceEach), 2) AS total
FROM
orderdetails
GROUP BY orderNumber;
# 4. GROUP BY 和 表达式
# 获取每一年的总和，
SELECT
YEAR(orderDate) AS year,
TRUNCATE(SUM(quantityOrdered * priceEach), 2) AS total
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
WHERE
status = 'Shipped'
GROUP BY YEAR(orderDate);
# 5. GROUP BY 和 HAVING
SELECT
YEAR(orderDate) AS year,
TRUNCATE(SUM(quantityOrdered * priceEach),
2) AS total
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
WHERE
status = 'Shipped'
GROUP BY year
HAVING year > 2003;
# 6. 标准SQL和MySQL中的SQL
# 以下可以运行于 MySQL, 但不是标准SQL
# 标准SQL中不允许使用别名进行分组，MySQL可以
SELECT
YEAR(orderDate) AS year, COUNT(orderNumber)
FROM
orders
GROUP BY year;
# 以下可以运行于 MySQL, 但标准SQL不能
SELECT
status, COUNT(*)
FROM
orders
GROUP BY status DESC;
# 请使用
SELECT
status, COUNT(*)
FROM
orders
GROUP BY status
ORDER BY status DESC;
# 标准SQL中不允许使用别名进行分组，不允许和ORDER BY简写 GROUP BY status DESC

# ----------------------------------- Chapter 10 -----------------------------------
# mysql HAVING 语句
# HAVING 经常和 GROUP BY 一起使用
# 进行分组，
select
orderNumber,
sum(quantityOrdered) as itemsCount,
sum(priceEach) as total
from
orderdetails
group by orderNumber;
# 进行过滤
SELECT
ordernumber,
SUM(quantityOrdered) AS itemsCount,
SUM(priceeach) AS total
FROM
orderdetails
GROUP BY ordernumber
HAVING total > 1000;
# 1. 复杂的过滤条件 - 复杂度参考 where 条件
SELECT
a.ordernumber, SUM(priceeach) total, status
FROM
orderdetails a
INNER JOIN
orders b ON b.ordernumber = a.ordernumber
GROUP BY ordernumber
HAVING b.status = 'Shipped' AND total > 1500;

# ----------------------------------- Chapter 11 -----------------------------------
# 子查询
# 1.
SELECT customerNumber,
checkNumber,
amount
FROM payments
WHERE amount = (
SELECT MAX(amount)
FROM payments
);
# 2.
SELECT customerNumber,
checkNumber,
amount
FROM payments
WHERE amount > (
SELECT AVG(amount)
FROM payments
);
# 3. not in
SELECT customername
FROM customers
WHERE customerNumber NOT IN(
SELECT DISTINCT customernumber
FROM orders
);
# 4.
SELECT priceEach * quantityOrdered
FROM orderdetails
WHERE priceEach * quantityOrdered > 10000
GROUP BY orderNumber;
# 5. exists 或者 not exists 存在就取，不存在就 pass
SELECT customerName
FROM customers
WHERE EXISTS (
SELECT priceEach * quantityOrdered
FROM orderdetails
WHERE priceEach * quantityOrdered > 10000
GROUP BY orderNumber
);
# 6. from 子查询
SELECT max(items),
min(items),
floor(avg(items))
FROM
(SELECT orderNumber,
count(orderNumber) AS items
FROM orderdetails
GROUP BY orderNumber) AS lineitems;# 这里把from table 中的 table 使用 子查询结果集来代替
# 上面的各种子查询都是独立的，但是也可以不独立
# 7. 相关联的子查询
# 外部查询的变化会影响到内部查询，内部子查询不能单独调用。
SELECT
productname, buyprice
FROM products AS p1
WHERE buyprice > (
SELECT AVG(buyprice)
FROM products
WHERE productline = p1.productline)
# 上例中，avg(buyprice) 收到 productline 的影响，而productline是每一行数据都有的，会影响到avg(buyprice)

# ----------------------------------- Chapter 12 -----------------------------------
# INSERT 语句
# 1. 写入一条数据
INSERT INTO table(column1,column2...) VALUES (value1,value2,...);
# 2. 插入多条记录
INSERT INTO
table(column1,column2...)
VALUES
(value1,value2,...),
(value1,value2,...),
...;
# 注意：table(columns) 中的columns 与 values(values) 中的values 个数，且数据类型一一对应。

# ----------------------------------- Chapter 13 -----------------------------------
# Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE statement
# 结构如下
INSERT INTO table(column_list)
VALUES(value_list)
ON DUPLICATE KEY UPDATE column_1 = new_value_1, column_2 = new_value_2, …;

INSERT INTO sample_table(column_1)
VALUES(column_1)
ON DUPLICATE KEY UPDATE column_1 = VALUES(column_1) + 1;# VALUES() 内置函数
# 实战
# 建表
create table if not exists devices(
id int auto_increment primary key,
name varchar(100)
);
# 插入数据
insert into devices(name)
values('Router F1'),
('Switch 1'),
('Switch 2');
# 插入新数据 - 没有duplicate，返回受影响行数 1
insert into devices(name) values('Printer')
on duplicate key update name='Printer';
# 存在id则更新，否则插入，如果成功返回 2
insert into devices(id,name) values(4, 'Printer')
on duplicate key update name='Server';
--** 解读上面的：如果不存在id为4的记录，那么就插入新数据（会返回1）;
--** 否则存在id = 4那么发生 duplicate 问题，这时会更新id = 4 的数据的指定的字段值
--** 如果返回0，表示新插入数据和原来数据同

# ----------------------------------- Chapter 14 -----------------------------------
# UPDATE EXAMPLES
UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET
column_name1 = expr1,
column_name2 = expr2, --** 这里指定多个字段
...
WHERE
condition;
--** 指定 where 条件很重要
# MySQL UPDATE from SELECT statement example
UPDATE customers
SET
salesRepEmployeeNumber = (SELECT
employeeNumber
FROM
employees
WHERE
jobtitle = 'Sales Rep'
LIMIT 1)
WHERE
salesRepEmployeeNumber IS NULL;
--** 根据内查询的结果修改

# ----------------------------------- Chapter 15 -----------------------------------
# 句法：
UPDATE T1, T2
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,
T2.C3 = expr
WHERE condition
--** 等价于
UPDATE T1, T2
SET T1.c2 = T2.c2,
T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition
# 1. INNER JOIN
UPDATE employees # 只修改employees表中数据
INNER JOIN # INNER JOIN 紧跟 UPDATE 语句之后
merits ON employees.performance = merits.performance # 这是2张表的关联语句 ON
SET # 开始修改
employees.salary = employees.salary + employees.salary * merits.percentage;
-- How the query works.
-- We specify only the employees table after UPDATE clause
-- because we want to update data in the employees table only.
-- For each row in the employees table,
-- the query checks the value in the performance column against the value
-- in the performance column in the merits table.
-- If it finds a match, it gets the percentage in the merits table and updates
-- the salary column in the employees table.
-- Because we omit the WHERE clause in the UPDATE statement,
-- all the records in the employees table get updated.
# 2. LEFT JOIN
UPDATE employees
LEFT JOIN
merits ON employees.performance = merits.performance
SET
salary = salary + salary * 0.015
WHERE
merits.percentage IS NULL;

# ----------------------------------- Chapter 16 -----------------------------------
# 删除单表数据
DELETE FROM table
[WHERE conditions] [ORDER BY ...] [LIMIT rows]
# 删除多表数据
DELETE table_1, table_2,...
FROM table-refs
[WHERE conditions]
--** 等价于
DELETE FROM table_1, table_2,...
USING table-refs
[WHERE conditions]
# 同时删除相关联的数据的方法
set foreign_key_checks = 0;# 默认情况下开启外键检查，设置为0
DELETE employees,
offices
FROM employees,
offices
WHERE employees.officeCode = offices.officeCode AND
offices.officeCode = 1;
set foreign_key_checks = 1;# 重新开启外键检查。

# ----------------------------------- Chapter 17 -----------------------------------
# DELETE JOIN - CLAUSE
# INNER JOIN
DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition
# LEFT JOIN
DELETE T1
FROM T1
LEFT JOIN T2 ON T1.key = T2.key
WHERE T2.key IS NULL
# 1. e.g:
set foreign_key_checks=0;
delete offices,employees from offices
inner join employees on employees.officecode = offices.officecode
where offices.officecode=5;
# 2. e.g:
set foreign_key_checks=0;
delete customers from customers
left join orders on customers.customerNumber = orders.customerNumber
where orderNumber IS NULL;

# ----------------------------------- Chapter 18 -----------------------------------
# MySQL ON DELETE CASCADE
CREATE TABLE buildings (
building_no int(11) NOT NULL AUTO_INCREMENT,
building_name varchar(255) NOT NULL,
address varchar(355) NOT NULL,
PRIMARY KEY (building_no)
) ENGINE=InnoDB;

create table if not exists rooms(
room_no int not null auto_increment,
room_name varchar(255) not null,
building_no int not null,
primary key (room_no),
key building_no (building_no),
constraint rooms_ibfk_1
foreign key (building_no)
references buildings(building_no) # 写法：constraint key_name foreign key (这张表的字段 a) references buildings (关联表的字段a)
on delete cascade # 就是这句
)engine=innodb;

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
('ACME Sales','5000 North 1st Street CA 95134');

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Showroom',2);

SELECT * FROM rooms;

# We have 3 rooms that belong to building 1 and 2 rooms that belong to the building 2.
# rooms表中的有3个room 属于 building 1; 2 个房间属于building 2;
# 需求当删除父表（buildings）中的数据时，属于它的房间也会跟着删除。
# -- 只需要
delete from buildings where building_no = 2;

# 查询收到影响的表 使用数据库中 information_shema 表
use information_schema;

SELECT table_name
FROM referential_constraints
WHERE constraint_schema = 'classicmodels' AND
referenced_table_name = 'buildings' AND
delete_rule = 'CASCADE';
-- 返回受到删除规则为cascade的数据库为classicmodels下受到影响的表