安装mySQL

可以安装到任意的盘符，安装包括连接到mySQL服务器，在工作台创建项目

安装教程

mySQL

笔记是按照下面的教程学习后整理的，接下来就是整理部分了。

学习教程

选择语句

USE：用来选择数据库

select：用来在数据库中查找

from：搭配在select后，选择数据库中的table

where：指定table中的某些元素，例如customers_id = 1，搜索id为1的

order by：根据某列元素排序。

select

在select中可以输出运算后的数据，例如：

1
SELECT points, points + 10;

同时输出points这列和每个数据运算后的新的列

AS：列后面接as能给新列命名：

1
SELECT points + 10 AS 'new column'

distinct： select后面接distinct可以删除列显示数据的重复性，例如address中存在两个shenzhen，运行后address列中只会存在一个shenzhen。

where

where语句筛选数据，后面接筛选条件，例如points > 4000等。

逻辑运算

where语句后的条件可以用与或非运算（AND，OR，NOT），类似python语法。

注意：AND优先级大于OR。

IN运算符

获取与某个列表内相匹配的数据，例如我想获得shenzhen，guangzhou和shanwei的用户数据：

1
WHERE address IN ('shenzhen', 'guangzhou', 'shanwei');

IN前面可以加NOT运算符

between运算符

获取某个范围的数据，比如我想获得points值介于1000到3000之间，包括临界值：

1
WHERE points BETWEEN 1000 AND 3000

字符串也适用，比如获取1990-01-01和2000-01-01之间的数据：

1
WHERE points BETWEEN '1990-01-01' AND '2000-01-01'

LIKE运算符

like运算符用来匹配字符串，有点像正则，例如我想查找B开头的用户（不区分大小写）：

1
WHERE name LIKE 'B%'

%可以用来代替任意长度的任意字符，_相当于一个占位符，匹配任意一个字符，比如我想匹配Bob，则有：

1
WHERE name LIKE 'B__'

同时，这个式子也能匹配Buy之类的单词。

regexp运算符

REGEXP运算符也是用来匹配字符串的运算符，更接近正则表达式，例如想要搜索名字里有some的用户时，我们用LIKE运算符，需要：

1
WHERE name LIKE '%some%'

而用REGEXP运算符：

1
WHERE name REGEXP 'some'

同时，类似于正则，^代表匹配开头，$代表匹配结尾，|是或，[]内可以写一个匹配范围，[abc]代表匹配abc中的任意一个字符，也可以写成a-c，这里与正则表达式相同。

IS NULL运算符

查找某个某列数据缺失的用户（一行数据），例如，id为5号的数据缺少phone，我们只需：

1
WHERE phone IS NULL

同样的：

1
WHERE phone IS NOT NULL

则可以查找所有有电话号码的用户

order by

在关系型数据库中，每个表格都有一个主键列，这一列中的值要能够唯一识别表里的记录（唯一性）。

表格会默认按照主键列进行排序。

用order by加上列表头改变排序的列，默认升序排序，列后加上desc代表降序排序

可以同时排序多个列，且有先后顺序，先出现的先排，后出现的后排，例如：

1
2
3
4
ORDER BY address, name
-- 1 shenzhen li
-- 2 shenzhen Wang
-- 3 guangzhou liu

将会排序成：3 1 2，先排shenzhen和guangzhou，guangzhou在前，后排名字。

limit

限制输出前n位数据，例如我想输出前三位数据：

1
LIMIT 3

还可以设置偏移量（offset），例如我想输出7到9位的数据：

1
LIMIT 6, 9

连接

INNER JOIN

当我们需要合并两张表的数据时：

1
2
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id

同时，确保两张表中的顾客id能够对的上。

隐式转化

隐式给表头添加别名：

1
order o

相当于：

1
order as o

跨数据库连接

只需要给不在当前数据库的表添加前缀即可，例如，我选中数据库sql_store，然后连接其他数据库sql_inventory

1
2
3
4
5
USE sql_store;
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
	ON oi.product_id = p.product_id

自连接

假如有这么一张学生数据的表格，每栏新生数据中都有自己的导生的学号，要如何提取出新生的名字和对应导生的名字呢？

1
2
3
4
SELECT n.student_id, n.name, o.name as manager
FROM students n
JOIN students o
	ON n.manager_id = o.student_id

分别给新生和老生取别名，这是就是自连接。

多表连接

可以多次使用JOIN方法。

练习：整合sql_invoicing中的付款数据，付款人和付款方式，

1
2
3
4
5
6
7
8
use sql_invoicing;

select p.date ,p.invoice_id, c.name, p.amount, pm.name as payment_method
FROM payments p
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
JOIN clients c
	ON c.client_id = p.client_id

复合连接条件

用多个条件共同识别唯一数据。用于有复合主键的表格。

例如，order_items是一个有复合主键的表格，存在order_id 和 product_id两个主键。

1
2
3
4
5
6
7
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

隐式连接

用where子句可以实现隐式连接。

1
where 条件语句

相当于join…on

不建议使用隐式连接。

外连接

外连接有left join和right join。

当我们使用左连接时，所有左表的记录会被返回，不管条件正确与否，例如：

1
2
3
from customers c
left join orders o
	on c.customer_id = o.customer_id

其中customer是左表，不管有无对应id，customer中的所有用户都会出现在表中，没有数据的部分（即对应不上），用NULL填充。

right join右连接同理。

多表外连接

同理多表内连接。

注意：当存在多表连接时，尽量避免使用右连接，统一左连接，提高代码可读性。

练习答案：

1
2
3
4
5
6
7
8
9
10
use sql_store;

select o.order_date, o.order_id, c.first_name as customer,  sh.name as shippr, os.name as status
from orders o
left join customers c
	on o.customer_id = c.customer_id
left join shippers sh
	on o.shipper_id = sh.shipper_id
join order_statuses as os
	on os.order_status_id = o.status

三表连接，其中发货人这部分实用外连接，因为可以还没发货，不存在发货人，但要保留订单信息。

自外连接

略。

using子句

假设在join…on的语句中，on后存在两个表中的列名称是完全一样的，例如

1
2
3
from orders o
JOIN customers c
	on o.customer_id = c.customer_id

其中存在相同名称customer_id，我们可以用using子句替换，如下：

1
2
3
from orders o
join customers c
	using (customer_id)

内外连接都能使用者这个子句。

在复合连接条件这一小节中，对于有复合主键的表格，我们也可以使用这个using子句简化：

1
2
3
4
5
6
7
8
From order_items oi
Join order_item_notes oin
	ON oi.order_id = oin.order_id
	AND oi.product_id = oin.product_id
-- 替换成：
From order_items oi
Join order_item_notes oin
	using (order_id, product_id)

自然连接

natural join，让数据库引擎直接猜测如何连接，无法外部控制。

交叉连接

交叉连接得到的结果是一个表中的每行数据和另一个表中的每行数据的所有组合，例如123和abc交叉得到9种结果。

显式语法：

1
2
from ...
cross join ...

隐式语法，直接在from后面写两个表格的名称

1
from first_table, second_table

联合

通过union，我们可以合并多段查询的记录（行合并）。合并的查询之间的列的数量一定要一样不然会报错。

多段查询union中，第一段查询写了什么会被用来决定列名：

1
2
3
4
5
select name
from shippers
union
select first_name
from customers

如上，列名为name。

练习答案，根据points给用户分类型：

低于2000是青铜，2000到3000是白银，大于3000是黄金

1
2
3
4
5
6
7
8
9
10
11
12
13
14
use sql_store;

select customer_id, first_name, points, 'Bronze' as type
from customers c
where c.points < 2000
union
select customer_id, first_name, points, 'Silver' as type
from customers c
where c.points between 2000 and 3000
union
select customer_id, first_name, points, 'Gold' as type
from customers c
where c.points > 3000
order by first_name

修改

打开表格的工具栏，分析每个选项的意思：

PK：主键，唯一识别

NN：非空，代表该列不能出现NULL

AI：自动递增排序，通常被用在主键列，在最后一行递增添加一个id，例如现有9个数据，添加第10个数据时该数据的id默认为10。

Default：若你没有给某行添加该列数据，默认填充default中指定的数据，可以是NULL，也可以是0，等等。

插入单行

如果你要给某个数据插入新的一行，例如给customers插入新行：

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO customers
VALUES (
	DEFAULT,
	'John',
	'Smith'
    '1990-01-01',
    NULL,
    'address',
    'city',
   	'CA',
    DEFAULT
	)

其中，DEFAULT代表添加默认值，该值参考表格工具栏中该列的Default中的值。

这些数据的顺序是按照列的默认顺序排列下来，一一对应的。

我们也可以改变他们的顺序，同时只提供Default以外的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO customers (
	last_name,
    first_name,
    birth_day,
    address,
    city,
    state
	)
VALUES (
	'John',
	'Smith'
    '1990-01-01',
    'address',
    'city',
   	'CA',
	)

如此，表格的列名和填写到该列的数据一一对应，你可以以任何的顺序相照应地排列它们。

插入多行

非常简单，和插入单行一样，在values后面添加多个括号，一个括号代表一行：

1
2
3
4
INSERT INTO shippers (name)
VALUES ('shipper1'),
	   ('shipper2'),
	   ('shipper3')

插入分层行

假设我们有两张表格，一张表格存储订单信息，有着发货人收货人等信息，另一张表格存储订单明细，例如订购了什么产品，价格多少。两张表格是相互关联的，但我们要在订单信息表格插入一行新的数据，比如会在订单明细表格更新相对应的新数据，这时我们就要用插入多行了：

1
2
3
4
5
6
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);

INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(), 1, 1, 2.95)

这里使用LAST_INSERT_ID()这个内置函数，返回最后一次插入时MySQL生成的那个id，我们就能用这个订单id来更新order_items里的订单明细，对应order里的新行。

创建表复制

复制一张表的数据到另外一张创建的新表。

1
2
CREATE TABLE order_archived AS
SELECT *FROM orders

用这个方法创建表格，不会复制主键和自动递增列的选项，单纯只是数据的copy。其中这个SELECT是CREATE语句下的一段子查询，也可以用在INSERT语句下（这里假设我们已经创建了一张新表orders_archived）：

1
2
3
4
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

该例子中，复制所有order_date中日期早于2019年的数据到表orders_archived。

更新单行

假设要更新某个发票的支付时间和支付价格

1
2
3
UPDATE invoices -- 选择表格
SET payment_total = 10, payment_date = 'date'
where invoice_id = 1

update…set语句默认更新整列的数据，我们这里用where加个限制条件，只更新invoice_id = 1的这一行。

更新多行

想要一次更新多行在MySQL workbench是不允许的（会有安全保护），可以打开设置 -> MySQL Editor，拉到最下面取消勾选 safe updates。

然后就能和更新单行一样一次更新多行了，更新多少行，哪些行，取决于where条件语句的设置。

在更新中使用子查询

select查询产生的值可以用在update中的条件语句里，例如：

1
2
3
4
5
6
UPDATE invoices 
SET payment_total = 10, payment_date = 'date'
where client_id = 
			(select client_id
             FROM clients
             WHERE name = 'Myworks')

如此，就能找到所有名字为Myworks的用户的id，然后根据这个id来更新。

如果select语句中产生了多条查询记录，client_id后要用 IN 运算符而不是 =。

删除行

1
DELECT FROM table_name

删除名字为table_name的表格的所有数据，当然你也可以在下面使用where条件语句选择特定的行进行删除，这一切都是自由的。

恢复数据库

在这一节中修改了很多数据，可以重新执行数据库脚本来重建数据库，恢复数据库到最开始的版本。

聚合函数

使用这些函数可以查找或者计算某列的数据：

1
2
3
4
5
MAX()
MIN()
AVG() -- 平均
SUM() -- 求和
COUNT() -- 计算记录数

只运行非空值，如果列中有空值NULL，则不会参与计算。

如果你想count表格有多少行的记录，可以用：

1
COUNT(*)

同样的，这些函数后面也可以添加where选择语句， 函数后面一般用AS取个列名。

1
2
3
4
SELECT
	MAX(invoice_total) AS highest
FROM invoices
WHERE invoice_data > '2019-07-01'

如果在COUNT记录时不想获取到重复记录，例如有多个重复的用户id，但只想记录一次，则可以用distinct关键字：

1
COUNT(DISTINCT client_id) AS total_records

GROUP BY子句

单列分组：

GROUP BY语句可以帮助我们给数据分组，只需要在groupby后面加上分组的依据列的名字，比如加上client_id，MySQL就会自动根据client_id分类。

假如我们不同的client_id对应多个订单，我们想要计算每个用户的总订单金额：

1
2
3
4
5
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 后面加上多个列名：

1
2
3
4
5
6
7
select
	state,
	city,
	sum(invoice_total) as total_sales
from invoices i
join clients using (client_id)
group by state, city

先连接两表，再用州和城市来给订单总金额分组；

练习答案（用日期和付款方式给总付款分组）：

1
2
3
4
5
6
7
8
use sql_invoicing;

select p.date, pm.name as payment_method, sum(amount) as total_payments
from payments p
join payment_methods pm 
	ON pm.payment_method_id = p.payment_method
group by p.date, payment_method
order by date;

HAVING子句

用having子句，我们得以在分组后进行数据筛选，比如在分组求总金额后筛选出金额大于500的组：

1
2
3
4
select client_id, sum(invoice_total) as total_sales
from invoices
group by client_id
having total_sales > 500

having后面也可以加复合搜索条件，用and连接即可。

注意：having后面用到的列名必须是select里出现过的。

with rollup

计算分组后，每一列所有数据的总和（计算聚合值），字符串等的总和会输出NULL，不是一个标准的SQL语句，仅能用于MySQL。

一般添加在group by语句后：

1
2
3
select payment_method, sum(amount) as total
-- ...(省略from等语句)
group by payment_method with rollup

根据支付方式分组后，rollup求total的总和作为新的一行。

编写复杂查询

子查询

之前介绍过，用小括号括起来的，子查询（或者叫内查询）：

1
2
3
4
5
6
UPDATE invoices 
SET payment_total = 10, payment_date = 'date'
where client_id = 
			(select client_id
             FROM clients
             WHERE name = 'Myworks')

这段代码来自在更新中使用子查询这一节，其中client_id后面的语句就是一个子查询，MySQL执行时会先执行子查询。

练习答案（外部有IN的子查询，找到没有发票的用户）：

1
2
3
4
5
6
7
use sql_invoicing;

select *
from clients
where client_id NOT IN 
	(select distinct client_id
		from invoices)

ALL关键字

子查询返回一列值时，用ALL关键字就会逐一取子查询中的值出来操作：

1
invoice_total > ALL(111, 222, 333);

只有当左值大于右边的每个值时，才会时true。

ANY关键字

ANY代表任意一个，跟在子查询前代表任意一个。例如 = ANY 等价于 IN，意思是等于（子查询）里面的任意一个。

相关子查询

子查询内存在和外查询有关联的语句。

1
2
3
4
5
6
7
select *
from invoices i
where invoice_total > (
	select avg(invoice_total)
    from invoices
    where client_id = i.client_id
)

i.client_id是外查询的值，只有当内查询的id符合外查询的id时，才会求其平均值，实际上就是根据id分组求平均值。

不难发现，这个相关子查询每次执行内查询都要访问一次外查询，因此运行较慢。

EXISTS关键字

该关键字能直接检查某个子查询中，是否有符合某个条件的行，而不会返回查询到的具体结果，对于某些返回结果较多的数据库能提高查询速度。

select子句中的子查询

1
2
3
4
select 
	...
	(子查询)
from ...

from子句中的子查询

1
2
3
4
select *
from(
	...
) AS origin

注意：必须要取一个别名

其他函数

数值函数

round函数，四舍五入函数：

1
round(5.712, 2)

例如把5.712四舍五入，保留2位。

truncate函数，截断函数：

1
truncate(5.212, 2)

直接截断成2位

ceiling函数，返回大于等于这个数的最小整数。

floor函数，返回小于或等于这个数的最大整数。

abs函数，返回绝对值。

rand函数，返回0-1的随机数。

字符串函数

length函数，返回字符串长度，

upper函数，返回全大写字符串，

lower函数，返回全小写字符串，

ltrim函数，左修整，移除左边空格等预定字符，rtrim同理，trim则是前后都修整。

left函数，返回从左边开始前n个字符，right函数同理：

1
left('string', n)

substring，截断函数，传入起始下标n和截断长度m，不传入长度默认到字符串结尾：

1
substring('string', n, m)

locate函数，返回查找字符的小标，不区分大小写，搜索不到返回0：

1
locate('s', 'string') -- 返回1

replace函数传入一个字符串并传入被替换的字符串和替换的字符串：

1
replace('abc', 'ab', 'ba') -- 返回bac

concat函数，合并两个字符串；

时间函数

now函数，返回当前日期和时间，curdate只返回日期，curtime只返回时间；

extract函数，能从now函数中提取时间数据，例如：

1
extract(year from now()) -- 返回当前年数

mysql语句中还有简化的语句，直接用对应的数据英语，传入now函数就好了，例如：

1
year(now()) -- 返回当前年数

格式化日期函数

date_format函数，能格式化返回日期的数据：

1
DATE_FORMAT(now(), '%y %m %d') -- 返回当前年月日

除了这些格式化字母，还有其他的格式化字母，查询网址

还有time_format函数，返回格式化的时间。

1
TIME_FORMAT(now(), '%H:%i %p') -- 12:00 PM

计算日期和时间

date_add函数，假如我们要增加一年：

1
2
date_sub(now(), interval -1 year)
date_add(now(), interval 1 year)

datediff函数，返回两日期之间的差值：

1
datediff('', '')

time_to_sec函数，返回从0点开始流逝的秒数：

1
time_to_sec('9:00') -- 32400

用两个time_to_sec函数的表达式相减，就能得到两时间之差了。

IFNULL COALESCE

ifnull函数，如果检测到某个数据是null，就用传入的字符串替换，

例如，顾客id是空值，则全用”未注册“替换；

1
IFNULL(customer_id, 'Not assigned')

coalesce函数，可以传入一堆值，返回众多值中的第一个非空值：

1
coalesce(customer_id, comments, 'Not assigned') as cs_id

解释：如果customer_id是null，则返回comments，若comments也是null，则返回最后的字符串（因为不是null）。

IF表达式

传入三个参数，第一个是测试表达式，第二个是表达式为真时返回的值，第三个是表达式为假时返回的值。

case运算符

case运算符一般接若干个when子句，当when子句的条件成立时，执行该when子句：

1
2
3
4
5
6
case
	when 表达式1 THEN '返回的值'
	when ...
	when ...
	else ' '
END

最后用end闭合case语句。

视图

视图是把查询单独封装起来的操作，就像创建了一张虚拟的表（依赖基础表的数据），我们可以随时调用这个查询（查询的结果是一张表）语法如下：

1
2
3
4
CREATE VIEW view_name as
select 
	...
	...

创建后的视图能直接调用，有点像封装好的函数：

1
2
select *
from view_name

删除修改视图

删除视图：

1
drop view view_name

更改视图

可以重新更改或者创建视图（如果该视图不存在）

1
create or replace view view_name

可以把视图放在sql文件夹中，创建git仓库方便管理。

可更新视图

如果一个视图没有以下成分，那么它是一个可更新视图：

1、distinct关键字

2、聚合函数（sum，min，max等）

3、group by 和 having 等排序语句

4、union联合关键字；

如果是一个可更新视图，我们可以insert, update, delete。

如果需要在视图中插入新行，需要当前视图返回的表中有原来的表的全部的列，例如基础表中有日期列，视图里没有select这个列，那就没法插入新行。

with check option

我们在操作可更新视图时可能会把视图中的某些行删除掉（例如某些在操作后不符合创建视图中where语句的行）。

为了防止这些行被意外删除，只要在创建视图的语句末尾加上with check option语句，当我们意外删除掉视图中的行时，会出现报错提示。

1
WITH CHECK OPTION

存储

存储是一个打包SQL语句的过程，能够打包后直接调用。

创建一个存储过程

创建一个从begin开始到end结束的完整存储过程。

其中有几个要注意的点，先在一开始用delimiter语句修改分隔符（任意的，习惯用两个美元符号），这样就能把begin到end之间的存储看成一个整体，最后记得把默认分隔符改回分号；（后面的代码都忽略这个转变分隔符的操作，自己知道就好）

1
2
3
4
5
6
7
8
delimiter $$
create procedure get_client()
begin
	select * from clients;
	...
end$$

delimiter ;

存储不同于视图，有点像封装起来的快捷指令，封装完后可以直接调用；

1
CALL get_client()

在mysql workbench直接创建存储

在每个数据库中有个叫Stored Procedures的文件夹，右键创建新存储，这时候我们只需要填入mysql语句，然后会自动生成存储。

删除存储

1
drop procedure if exists procedure_name

有传参的存储

只需要在create pro语句后的存储名字的括号内添加传入的参数名字：

1
2
3
4
5
6
7
delimiter $$
create procedure get_clients_by_state(state CHAR(2))
begin
	...
end$$

delimiter ;

这里的state类型是cha(2)，代表两个字符。

带默认值的传参

在存储中添加一个传参判断语句来实现默认传参，例如：

1
2
3
4
5
6
begin
	if state is null then
		set state = 'cA'
	end if;
	...
end

以上小节的例子为基础，如果没有传参state，则这里默认set为CA，记得if语句要用end if的结束标志。

如果我们默认值想设置为数据库里的所有的state，巧用IFNULL可以简洁实现：

1
2
3
4
begin
	select *from clients c
	where c.state = IFNULL(state, c.state);
end

之前我们知道IFNULL语句时如果第一个参数为NULL就返回第二个参数，如果传参state为NULL，这个语句就变成c.state = c.state，自然对于clients中所有的state值都成立。

参数验证

当一个参数传入是非法时，我们可以通过if语句和报错设置来提醒用户：

1
2
3
4
if ... then
	signal sqlstate ''
		set MESSAGE_TEXT = 'error'
end if;

其中signal sqlstate是设置错误码，每个错误的详细的错误码可以在官网查找：传送门

输出参数（返回值）

在传入参数前面加上OUT关键字就是输出参数，例如下面例子中的out_name：

1
2
3
4
5
6
7
delimiter $$
create procedure get_clients_by_state(state CHAR(2), OUT out_name INT)
begin
	...
end$$

delimiter ;

可以在存储中用select…into…语句给输出参数赋值。

变量

user变量，会在整个客户会话过程中被保存：

1
set @variables_name = 0

本地变量，在存储或者函数内定义的，存储或者函数完成就会被清空：

使用declare关键字声明本地变量，例如，声明一个int变量默认值为0：

1
declare variables_name INT default 0

也可以不给本地变量设置默认值，用select … into …给变量赋值，用set语句给变量赋值。

函数

函数不同于存储只能返回单一值，没有多行和多列的结果集。

函数还需要声明返回值类型和函数类型：

1
2
3
4
5
6
7
8
9
10
11
create function func_name(client_id INT)
returns integer -- 返回值类型声明
-- 下面是函数类型声明
deterministic -- 确定性的，传入值和返回值一一对应
reads sql data -- 函数中读取了数据
modifies sql data -- 函数中有更新插入删除等

begin
	
	return 1;
end

函数类型按实际情况选择。

同理，你最后可以用drop删除函数。

触发器

触发器是在插入，更新和删除语句前后自动执行的一堆sql代码，通常用来增强数据的一致性。

新建触发器

触发器能修改别的表，但不能修改触发器所在的表。

新建的一个触发器在payments表，当插入新收款时，自动更新发票内某人的付款总值。

1
2
3
4
5
6
7
8
create trigger payments_after_insert
	AFTER INSERT ON payments
	FOR EACH ROW
BEGIN
	UPDATE invoices
	SET payment_toal = payment_total + NEW.amount
	WHERE invoice_id = NEW.invoice_id;
END $$

查看触发器

1
SHOW TRIGGERS

用LIKE关键字能筛选存在于某个表的触发器。

删除触发器

1
DROP TRIGGER IF EXISTS trigger_name

用触发器进行审计

新建一张审计表，触发器内编写一段代码来更新审计表，在插入删除等操作后，记录发生的操作；

在这个例子里，在插入触发器最后补充这段代码，能自动在审计表里更新客户id，日期，支付金额，操作类型和操作时间：

1
2
3
4
INSERT INTO payment_audit
VALUES (
	NEW.client_id, NEW.date, NEW.amount, 'Insert', NOW()
)

事件event

大部分上，事件是按一定规律执行的sql代码。

执行事件，得先打开事件调度器：

1
2
SHOW VARIABLES LIKE 'event%'; -- 先查看是否开启
SET GLOBAL event_scheduler = ON -- 如果查看后为OFF就执行这个

打开事件调度器后就能开始编写事件了：

1
2
3
4
5
6
7
create event event_name
ON SCHEDULE 
	-- AT '2024-10-01' -- 仅在国庆节这天执行
	EVERY 1 YEARS START '2024-01-01' ENDS '2029-01-01' -- 一年执行一次
DO begin
	...
end $$

查看事件

1
SHOW EVENTS;

语法和触发器的基本一致；

修改事件

可以暂时启用事件或者禁用事件：

1
2
ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;

事务（搁置）

事务是代表单个工作单元的一组sql语句；事务是一个整体，密不可分。

创建事务

事务具有一致性，事务内的每个语句不会单独执行，只要有一个错误存在，整个事务都会被退回，不会出现执行了一半的情况。

1
2
3
START TRANSACTION;
	...
COMMIT;

当我们想调试这个事务时，可以把commit改成rollback，这样就会在最后退回事务并撤销所有更改；

1
2
3
START TRANSACTION;
	...
ROLLBACK;

补充一下，mysql先把事务里执行正确的语句自动提交（commit），由autocommit控制；查看该系统变量：

1
SHOW VARIABLES LIKE 'autocommit';

锁定和并发

并发操作可能出现的情况：

当一个事务试图修改某个数据库的一行或多行时，mysql会给数据库上锁，防止其他事务同时修改这些行，出现冲突。只有当第一个事务执行完毕后第二个事务才能继续执行。

并发问题

脏读：读取到其他事务还未提交更改的数据；

重复读：重复读取的数据被其他事务修改导致不一致

幻读： 某个事务在执行时，其他事务修改了相关该事务的数据，导致该事务结果有误。

数据类型

字符串类型

char: 定长字符串，char(10) 相当于30个字节。

varchar: 65535 characters

mediumtext：16mb

longtext: 4GB

tinytext ： 255 bytes

整数类型

tinyint 一个字节

unsigned tinyint 无符号一个字节

smallint 两个字节

mediumint 3个字节

int 4个字节

bigint 8个字节

定点数类型

decimal(p , s)： 最多有p位，小数点后有s位，dec，numeric，fixed都是别名。

浮点数

float 4个字节

double 8个字节

布尔类型

bool，boolean

枚举类型

语法结构：enum（），括号里填入枚举的字符串，之后这一栏填写的数据只能从这里面挑选。枚举类型也许会导致各种问题，不建议使用。

日期和时间类型

date 日期（无时间）

time 时间（纯时间值）

datetime 日期和时间（8个字节）

timestamp 时间戳 （4个字节）

year

Blob类型

二进制长对象，用来存储各种数据。

不太建议把大型二进制数据存储在数据库中，会导致读取速度变慢等问题。

JSON类型

读取json类型，用一个简短的箭头符号：

假如当前列proper存储的是json类型，json类型下有weight: 10键值对

1
select proper -> '$.weight'

假如键值对的值是字符串，读取出来会带有引号，修改一下箭头符号能去掉引号：

1
select proper ->> '$.string'

修改json，修改weight和string的值：

1
2
3
4
5
6
UPDATE database
SET proper = JSON_SET(
	proper,
    '$.weight', 20,
    '$.string', 'sony'
)

删除json，删除其中的weight和string：

1
2
3
4
5
6
UPDATE database
SET proper = JSON_REMOVE(
	proper,
    '$.weight'
    '$.string'
)

设计数据库

第一范式

每个单元格都应该有单一值，且不能出现重复列。

第二范式

一张表仅能代表一种实体，这张表中的每一列都应该用来描述那个实体。

如果表中有一列描述了其他的实体，我们应该将其单独成表。

第三范式

同一表中的列不应派生自其他列。

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create database if not exists test_db;

USE test_db;

DROP TABLE IF EXISTS EMP;-- EMP表存在DEPT表的外键，因此EMP表要先删除。
DROP TABLE IF EXISTS DEPT;-- 受到外键约束，不能随意删除。

create table DEPT(
	DEPTNO INT primary key,
    DNAME VARCHAR(50) NOT NULL,
    LOC VARCHAR(50) NOT NULL
);


create table EMP(
	EMPNO INT primary key,
	ENAME VARCHAR(50) NOT NULL,
    JOB VARCHAR(50) NOT NULL,
    MGR INT,
    HIREDATE DATE NOT NULL,
    SAL DECIMAL(7,2),
    COMM INT,
    DEPTNO INT NOT NULL,
    FOREIGN KEY fk_EMP_DEPT (DEPTNO)
		REFERENCES DEPT(DEPTNO)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

更改数据库

把create换成alter。

添加具体数据

用INSERT语句，详见修改章节。

索引

创建能帮助我们更快找到目标；

创建索引

例如，根据state来创建索引；

1
create index idx_state ON customers (state);

查看索引

1
SHOW INDEXES IN table_name;

前缀索引

指定字符串列的前几个字符，用其作为区分来设定索引，例如这里取姓氏的前5个字符：

1
create index idx_state ON customers (last_name(5));

全文索引

全文索引能根据查找的字符串，计算其相关性然后返回相关结果，十分灵活：

1
2
3
4
5
create fulltext index idx_title_body on posts(title, body);

select *
from posts
where match(title, body) against('react redux')

例子中，在每篇文章索引包含的是标题和主体；match函数中写入所有索引列，例如这里的title和body，不然mysql会报错；

关键是against里面的内容，react redux这两个单词用空格分割，是查找的必要条件，这两个单词可以在title和body中以任意顺序、任意形式出现，出现越多相关性越强。

1
2
...
where match(title, body) against('-from +react' in boolean mode)

使用boolean mode，我们可以硬性查找某些字符串，例如上述例子中，查找所有包含react但没有from的行。

1
2
...
where match(title, body) against('"hello world"' in boolean mode)

使用双引号包含的字符串，执行语句后会返回准确包含这个字符串的行。

复合索引

mysql只会选择一个索引，我们设定多个Btree索引只会选择其中一个；

因此需要复合索引，复合索引可以在多列上建立索引，例如，根据顾客所在州和积分建立复合索引：

1
create index idx_state_points on customers(state, points)

复合索引中的列顺序

1、把更常用的列排前面；

2、把基数更大的列排前面；

3、根据实际情况更新索引；

实验笔记

数据库系统实验一构建数据库代码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
create database if not exists test_db;

USE test_db;

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;

create table DEPT(
	DEPTNO INT primary key,
    DNAME VARCHAR(50) NOT NULL,
    LOC VARCHAR(50) NOT NULL
);


create table EMP(
	EMPNO INT primary key,
	ENAME VARCHAR(50) NOT NULL,
    JOB VARCHAR(50) NOT NULL,
    MGR INT,
    HIREDATE DATE NOT NULL,
    SAL DECIMAL(7,2),
    COMM INT,
    DEPTNO INT NOT NULL,
    FOREIGN KEY fk_EMP_DEPT (DEPTNO)
		REFERENCES DEPT(DEPTNO)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

INSERT INTO DEPT(
	DEPTNO, DNAME, LOC
)
VALUES
	('10', 'ACCOUNTING', 'LONDON'),
	('20', 'RESEARCH', 'PRESTON'),
    ('30', 'SALES', 'LIVERPOOL'),
    ('40', 'OPERATIONS', 'STAFFORD'),
    ('50', 'MARKETING ', 'LUTON');
	
INSERT INTO EMP(
	EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
)
VALUES
	(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 13750, NULL, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1989-02-20', 19000, 6400, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1993-02-22', 18500, 4250, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1889-04-02', 26850, NULL, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1997-09-28', 15675, 3500, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1990-05-01', 24000, NULL, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1988-06-09', 27500, NULL, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 19500, NULL, 20),
    (7839, 'KING', 'PRESIDENT', NULL, '1983-11-17', 82500, NULL, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1992-09-08', 18500, 6250, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1996-5-23', 11900, NULL, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1995-12-03', 12500, NULL, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1991-12-03', 21500, NULL, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1995-06-23', 13250, NULL, 10),
    (3258, 'GREEN', 'SALESMAN', 4422, '1995-07-24', 18500, 2750, 50),
    (4422, 'STEVENS', 'MANAGER', 7839, '1994-06-14', 24750, NULL, 50),
    (6548, 'BARNES', 'CLERK', 4422, '1995-06-16', 11950, NULL, 50);