[大数据]mysql学习知识点总结

mysql学习知识点总结

B站学习Mosh Hamedani老师的mysql高阶课程, 视频地址

安装就省略了，直接去官网下载就可以了。

mysql一些特点

• 不区分大小写(最好大写语法，小写其他)
• 不同语句之间以分号分隔
• --（两个连字符）表示注释
• 选择语句中语法顺序不能颠倒，必须按照顺序来写。
• 换行符等没有用，但看着方便

运算符

• /除法以及常见的加减乘±*
• %取模（余数）
• 比较运算符：> >= < <= = !=(<>)
• 逻辑运算符：AND OR NOT （AND优于OR）

选择语句

双击或者直接输入代码USE 数据库名表示打开该数据库

SELECT 列名1,列名2表示提取需要的列，或者SELECT *表示提取所有的列

FROM 数据表名表示从该数据表提取数据

WHERE 筛选条件比如ID列为1的顾客数据，可以通过WHERE ID=1

ORDER BY 排序列名表示根据哪一列进行排序

SELECT子句

除了全选*，列名外，还可以直接在选择的时候进行数学计算，比如对于得分列score，每一条数据加10生成新的一列SELECT score + 10 as score10 SELECT DISTINCT score

as表示计算后的新列的名字，可以省略。如果希望生成的名字中间有空格，需要给名字加上引号。DISTINCT表示选择非重复数据。

WHERE子句

用来筛选数据的语句，如果查询内容是文本类型，一般用单引号标识，双引号也可以。如：WHERE state='VA'日期虽然不是文本类型，也需要用引号标识，如：WHERE birthdata>'1999-01-01'

连接多个查询条件：WHERE birthdata>'1999-01-01' AND point > 1000

IN表示在集合中任意一个

如：WHERE price IN (10,20,30)表示价格是10或20或30，也可以用NOT IN表示不在其中的数据

BETWEEN …AND…表示介于

如：WHERE price BETWEEN 10 AND 30表示价格介于10到30之间（包含10和30）的全部数据。

LIKE和正则表达式表示模糊匹配

LIKE比较原始

如：WHERE lastname LIKE 'b%'表示名字以b开头的数据，%表示任意长度的任意字符，%可以放在任何位置，比如%b%就表示包含b的任意数据。_表示单个字符，如：WHERE lastname LIKE b___y表示以b开头，之后跟4个字符，以y结尾的全部数据。也可以在LIKE前面加上NOT表示否定。

可以用正则，效果和LIKE一样，不过更强大

没有符号表示包含此单词a的任意字符，相当于like里的%b%, ^b表示以b开头，b$表示以b结尾，|表示或者，[g,i,m]e表示包含ge 或者 ie 或者me。

如：WHERE lastname REGEXP 'b'和WHERE lastname LIKE '%b%'是一样的。

NULL缺失值

如：WHERE phone IS NULL表示选择电话缺失的记录。

ORDER　BY子句，排序，DESC表示降序

查询默认按照主键排序，按照需要自己设置排序列。

如：ORDER BY name DESC表示按照名字降序排列，也可以根据多列进行排序，用，分隔。

mysql可以按照不在SELECT子句里的列进行排序，甚至按照计算公式进行排序

如：SELECT name FROM customers ORDER BY ID

LIMIT限定查询的结果数量

如：LIMIT 3表示前三条记录

可以设置偏移量，如LIMIT 6，3 前一个数字6表示偏移量，这条命令的意思是跳过前6条，获取剩下的数据的前三条，即原数据的第7，8，9条记录。

注意顺序，LIMIT一定是放在最后的。

内连接

INNER JOIN

• 实现跨表查询，如：表示选择order_id, first_name, last_name这三列。并且将sql_store.orders这一张表和sql_store.customers这一张表按照customer_id这一列连接起来。

select order_id, first_name, last_name

from sql_store.orders

join sql_store.customers
	on orders.customer_id = customers.customer_id;

• 当多次用到某个表名称的时候，可以进行简化，如下：即将sql_store.orders这张表命名为O，同理

select order_id, o.customer_id, first_name
from sql_store.orders o
join sql_store.customers c
	on o.customer_id = c.customer_id;

注意

当有多个表时，选择这多个表都有的列时，必须注明是哪张表。如上例，要选择customer_id这一列，就必须写成select orders.customer_id

跨数据库连接

同上，只不过需要加上数据库名字。

自连接

有时需要同一张表和自己连接，用法同上。比如，一张员工信息表，有该员工的ID以及他管理者的ID，那么，可以将他的ID和他管理者的ID连接起来。

多表连接

连接多个表时，只用重复JOIN语句就可以了。

复合连接条件

当主键是联合主键时处理，即添加AND语句

select *
from order_items oi
join order_item_notes orn
	on oi.order_id=orn.order_Id
    and oi.product_id=orn.product_id

隐式连接语法

虽然mysql支持这种语法，但最好不要用，很容易出错

select *
from orders o, customers c
where o.customer_id=c.customer_id

外连接

分为左连接和右连接，理解的话内连接相当于取交集，而外连接根据左右进行类似并集处理。如果是左连接，就是按照FROM语句后面的表进行连接，即这张表的全部数据被选中，同理。

select*
from customers c
left join orders o
	on o.customer_id=c.customer_id
order by c.customer_id

多表外连接

同内连接，继续加语句就好了。

自连接同理

语句简化

当要连接的两个表的条件列名一致的时候，可以简化语句。如下：(当有多个主键时，用,分隔)

select*
from customers c
left join orders o
	-- on o.customer_id=c.customer_id
	using(customer_id)

自然连接、交叉连接

不常用，容易出错，不过要知道是什么

自然连接:数据库自己根据相同的列名连接

select o.order_id,c.first_name
from orders o
natural join customers c

交叉连接

select p.name,c.first_name
from customers c
cross join products p
order by c.first_name
------隐式语法
select p.name,c.first_name
from customers ，products p
order by c.first_name

联合

连接多张表的行:比如将两个查询结果联起来

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'

列属性

依次为

列名：名称，不用解释

数据类型：INT是整数，VARCHAR指可变字符，最大50个，如果改成CHAR（50）那么，如果名字是4个字，就会填充46个空格。

PK：主键的意思。

NN：非空值，这个属性决定了该列是否可以为空值。

AI：自动递增的意思，通常用在主键列，意思是当插入新行的时候，自动加1

Default/Exception:指的是当出现空值的时候，如何填补。

插入单行

----第一种
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')

通过如上语句插入一行，第一种写法需要按照列的顺序依次写入数据，可以使用Default，就是让系统自动填充（必须设置过填充内容），主键的自动填充也用default。当然也可以写Null，表示空值，如果允许空值的话。

第二种写法更自由。

插入多行

这个也简单，只用在小括号外加逗号，再加小括号就行。

insert into shippers(name)
values('shipper1'),('shipper2'),('shipper3')

插入分层行：子母表

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(),2,1,3.95)

使用last_insert_id()函数获取上一个自动生成的数据。注意，在这里，orders表是一个母表，order_items是一个子表。

复制表

create table order_archived as 
select *
from orders

创建并生成复制表可以用以上代码，但是注意，生成的新表并没有设置主键和自动递增列。下面的select语句称为子查询，是隶属于create语句的。

子查询还可以与insert语句结合。如下

----右击表名，点击truncate table,即截断表，删除所有数据，但属性不变。
insert into order_archived
select *
from orders
where order_date<'2019-01-01'

更新单行

update invoices
set payment_total=10,payment_date='2019-01-01'
where invoice_id=1

where语句指定更新的记录，set设置值，这个值可以是公式。可以随意选择更新的列，不一定是全部列。

更新多行

update invoices
set payment_total=invoice_total*0.5,payment_date=due_date
where client_id=3

where 语句是可选的。

更新多行会报错，这是因为系统默认为安全更新模式，需要在设置里取消勾选。

更新时用子查询

有时候在更新行的时候，查询条件本身不在当前表中，就要用到子查询。注意，此时，因为子查询有多个记录，所以用where client_id in，如果只有一条记录，用=

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'))

删除行

删除语句delete from 表名。其他的子查询或者查询条件和更新一样。

聚合函数

• max()
• min()
• count()
• avg()
• sum()
• 注意：只运行非空值

select max(invoice_total) as highest
from invoices
--------分割线----------
select 
   max(invoice_total) as highest,
   count(payment_date) as number_of_payment,
   count(*) as total_record
   -- count(*) 可以获取全部记录数，包含空值
from invoices
--------分割线------------
select 
   max(invoice_total) as highest,
   count(distinct client_id) as total_record
from invoices
where invoice_date > '2019-07-01'

注意：

• 不一定非要是数值型数据才可以用这些函数，日期、文本等都可。其他常用的函数见上文，用法一样。
• 默认是包含重复值的，如果想去重，需要用到distinct

select
	'first_half_of_2019' as date_range,
	sum(invoice_total) as total_sales,
    sum(payment_total) as total_payment,
    sum(invoice_total - payment_total)  as what_we_except
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_payment,
    sum(invoice_total - payment_total)  as what_we_except
from invoices
where invoice_date between '2019-07-01' and '2019-12-31'

group by 子句

单列分组

select
	client_id,
    sum(invoice_total) as total
from invoices
where invoice_date>='2019-07-01'
group by client_id
order by total desc

多列分组

select
	state,
    city,
    sum(invoice_total) as total
from invoices
join clients using(client_id)
group by state,city

having 子句

由于WHERE子句要在GROUP之前，所以如果要对分组后的数据进行筛选，需要用到HAVING子句。

select
	client_id,
    sum(invoice_total) as total
from invoices
group by client_id
having total > 500

如果有多个筛选条件，可以使用AND连接多个条件

where　和　having　的两点区别

• where 是分组前使用，having是分组后使用
• where筛选时所用的筛选条件可以是select里没有的，而having必须是select里包含的。

rollup运算符

统计每个分组及整个结果的汇总值。注意，用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

复杂查询

子查询

小括号括起来，比如查询价格比产品3价格贵的产品。

select *
from products
where unit_price > (
		select unit_price
        from products
        where product_id=3)

IN运算符

select *
from clients
where client_id not in (
    select distinct client_id
	from invoices
)

子查询和连接

有时候这两种方法都可以达到目的，但是往往从可读性角度来选择。比如查询订购过产品3的顾客信息。

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 c.customer_id,c.first_name,c.last_name
from customers c
join orders o using(customer_id)
join order_items oi using(order_id)
where oi.product_id=3

ALL关键字

通常可以和MAX函数互换使用。比如要查询票价大于顾客3的所有票价的票价。

select *
from invoices
where invoice_total>(
	select max(invoice_total)
    from invoices
    where client_id=3
);
--方法2--
select *
from invoices
where invoice_total>all (
	select invoice_total
    from invoices
    where client_id=3
)

ANY 和SOME意思一样，可以和IN互换

比如要查询至少有两次小票记录的顾客信息

select*
from clients
where client_id =any (
	select client_id
    from invoices
    group by client_id
    having count(*)>=2
)

相关子查询

也就是说，子查询相当于一个循环，会在主查询的每一条记录运行一次。

比如要查询工资超过部门平均的员工信息

select *
from employees e
where salary > (
	select avg(salary)
    from employees
    where office_id=e.office_id
    )

EXISTS语句

某些情况下可以和IN互换，区别是，IN语句子查询返回的是列表，而EXISTS返回的是指令，如果列表非常的长，也就是说，子查询结果很多，那么用EXISTS效率更高。

比如要查询有票价信息的顾客

select *
from clients c
where exists(
	select client_id
    from invoices
    where client_id=c.client_id
    )

SELECT子句的子查询

select 
	invoice_id,
    invoice_total,
    (select avg(invoice_total)
    from invoices)as invoice_average,
    invoice_total-(select invoice_average) as difference
from invoices

FROM子句的子查询

往往会使查询变得复杂，因此通常仅限于简单的查询，可以使用视图来解决。

select*
from (
	select 
		client_id,
		name,
		(select sum(invoice_total)
		from invoices i
		where client_id=c.client_id) as total_sale,
		(select avg(invoice_total) from invoices) as average,
		(select total_sale- average) as difference
	from clients c
) as sale_summary
where total_sale is not null

函数

数值函数

一些常用的数值函数

ROUND函数

用于四舍五入，第二个参数表示保留的小数位数。

如下1，结果为5.74

TRUNCATE函数

用于截断数据，第二个参数表示小数位数。

如下2，结果为5.73

CEILING函数

返回大于等于这个数字的最小整数

如下3，返回值为6

FLOOR函数

返回小于等于这个数字的最大整数

如下4，返回为5

ABS函数

计算绝对值

RAND函数

生成0-1之间的随机浮点数

select round(5.7353,2)；
select truncate(5.7353,2)；
select ceiling(5.7353)
select floor(5.7353)

字符串函数

LENGTH函数

返回字符个数

如下1，结果为3

UPPER函数和LOWER函数

将字符串转换为大写或者小写

LTRIM和RTRIM和TRIM函数

移除字符串左侧或右侧的空格或其他预定义字符，TRIM表示移除左侧和右侧的空格或其他预定义字符。

LEFT和RIGHT函数，SUBSTRING函数

返回字符串左侧或右侧的若干字符，SUBSTRING函数获取字符串任意位置的若干字符。

如下2，返回Sk

如下3，返回lue，如果省略第三个参数，则会返回从所选择位置后的所有字符。

LOCATE函数

返回第一个字符或者一串字符的匹配位置。不区分大小写。

如下4，返回字母u第一次在字符串中出现的位置，即3

REPLACE函数

替换一段字符

如下5，返回redSky

CONCAT函数

连接两个字符串

如下6，返回blueSkyis blue

如下7，8

select length('sky')；
select left('Sky',2)；
select substring('blueSky',2,3)；
select locate('u','blueSky')；
select replace('blueSky','blue','red')；
select concat('blueSky','is blue')；
select concat(first_name,' ',last_name) as fullname
from customers

日期函数

NOW，CURDATE，CURTIME函数

返回当前日期

如下1，结果为

当然，这几个函数可以嵌套使用，如下2，3，用来分别获取当前的年份，月份等信息，这些获取的信息都是整数，也可以获取字符串信息，使用DAYNAME，MONTHNAME函数，也可以用EXTRACT函数，如下4，返回2021。用法是参数year或者day或者month等 from now().

select now(),curdate(),curtime()；
select 		year(now()),month(now()),day(now()),
			hour(now()),minute(now()),second(now())；
select extract(year from now())；
--获取今年的订单信息--
select *
from orders
where year(order_date)=year(now())

格式化日期和时间

DATE_FORMAT函数，两个参数，第一个参数是日期值，第二个参数是一个格式字符串。其中，%y表示两位数的年份，%Y表示四位数的年份，%m两位的月份，%M文本格式的月份，%d表示日期（天）

用法如下

select date_format(now(),'%y');
--返回：21
select date_format(now(),'%Y')；
--返回：2021
select date_format(now(),'%m %Y')；
--返回：08 2021
select date_format(now(),'%M %Y')；
--返回：August 2021
select date_format(now(),'%M %d %Y')；
--返回：August 19 2021

同样，有TIME_FORMAT函数

%i表示分钟，%p表示PM或者AM

select time_format(now(),'%H:%i %p')
--返回值：19:57 PM

计算日期和时间

date_add函数，第一个参数指要修改的日期，第二个指修改值。如果增加一年可以是interval 1 year。注意，所加的修改值可以是负值，相当于减。

date_sub函数，用法和date_add一样，只是用来减时间的，效果和上述负值一样。

select date_add(now(),interval 1 day)；

DATEDIFF 函数返回两个日期天数的间隔。

select datediff('2021-05-01','2021-04-29')
--2

TIME_TO_SEC函数计算从零点开始的秒数

select time_to_sec('9:00')
--   time_to_sec('9:00')-time_to_sec('9:03')
--   -180

IFNULL函数和coalesce函数

IFNULL表示如果为空值如何赋值

select order_id,
	ifnull(shipper_id,'not assigned') as shipper
from orders

coalesce函数和IFNULL类似，但是有三个参数，如果为空值，会先返回第二个设定列的值，如果还为空值，再返回其他设定值。这在有备注列的时候常用。

select order_id,
	coalesce(shipper_id,comments,'not assigned') as shipper
from orders

IF函数

例如，查询订单时间，如果是今年的订单，就返回活跃，否则返回归档。

select order_id,
	order_date,
    if(year(order_date) = year(now()),'active','archived')
from orders

CASE运算符

IF函数的升级版，可以设定多个限定条件。

select order_id,
	order_date,
    case
	    when year(order_date) = year(now()) then 'active'
        when year(order_date) = year(now())-1 then 'lastyear'
        when year(order_date) < year(now())-1 then 'archived'
        else 'future'
	end as category
from orders
--------
select concat(first_name,' ',last_name) as customer,
	points,
    case
		when points>3000 then 'gold'
        when points >=2000 then 'silver'
        else 'bronze'
	end as category
from customers

视图

即将查询或者子查询保存便于调用。保存的视图可以相当于一张虚拟表，连接、筛选、排序等都可以。但是视图并不存储数据。视图可以使用更新语句（INSERT、DELEAT等），但如果出现DISTINCT或者聚合函数或者GROUP　BY或者HAVING或者UNION语句则不可以更新。

---创建一个client_sale的视图---
create view client_sale as
select 
	c.client_id,
    c.name,
    sum(invoice_total) as total_sale
from clients c
join invoices i using(client_id)
group by client_id,name
--查看
select *
from client_sale
--删除视图
drop view client_sale
--也可以在创建视图的时候写成 create or replace view client_sale

注意，在更新视图的时候可能出现行消失的情况，为防止这种情况出现，在创建视图的时候在最后加上WITH　CHECK OPTION就可以了。

存储过程

是一个包含一堆SQL代码的数据库对象。总的来说，就是用来存储和管理SQL，并且执行更快，数据更安全。

--delimiter语句用来修改默认的分隔符，国际惯例是改为$$,其实改成什么都行，主要是为了和默认的;区别开。
--可以通过右击的方式创建存储过程，此时就不需要修改默认分隔符了
--get_clients()括号里可以设定参数
delimiter $$
create procedure get_clients()
begin
	select * from clients;
end$$
delimiter ;
--调用存储过程
call get_clients()
--删除存储过程
drop procedure get_clients
--注意，此时，存储过程名字后面没有加括号。
--不过，一般这样写
drop procedure if exists get_clients

参数

存储过程可以设置参数，多个参数之间用逗号分隔。

如果不设置默认值，要想调用这个存储过程，就必须给定参数值，在实际中中，可以设定默认参数值，即当没有给定参数值时，返回的内容。

delimiter $$
-- char指字符，括号里的2指两个字符，一般会设置为VARCHAR指可变字符。
create procedure get_clients_bystate(state char(2))
begin
	select * from clients c
    where c.state=state;
end$$
delimiter ;
-- 加入设定默认参数值，州为CA，即当没有给定州名称时，返回CA的数据。
delimiter $$
create procedure get_clients_bystate(state char(2))
begin
	if state is null then 
		set state='CA';
    end if;
	select * from clients c
    where c.state=state;
end$$
delimiter ;
-- 调用，注意即便是空值，也必须写NULL，不能空着
call get_clients_bystate(null)
-- 如果空值就返回全部信息，方法一
delimiter $$
create procedure get_clients_bystate(state char(2))
begin
	if state is null then 
		select * from clients;
	else
		select * from clients c
		where c.state=state;
    end if;	
end$$
delimiter ;
-- 方法二（方法二更专业）
delimiter $$
create procedure get_clients_bystate(state char(2))
begin
	select * from clients c
	where c.state=ifnull(state,c.state);
end$$
delimiter ;
-- 两个参数的例子，如果支付编号为空，就返回该顾客的全部信息，如果都为空，就返回全部信息。
delimiter $$
create procedure get_payments(client_id int,payment_method_id tinyint)
begin
	select * from payments p
	where p.client_id=ifnull(client_id,p.client_id) and payment_method=ifnull(payment_method_id,payment_method);
end$$
delimiter ;

参数验证

如上创建存储过程，但面临一个问题，如果金额为负值，也可以输入更新表，这显然不符合逻辑。为此，设置限制条件，加入if语句，其中，signal表示发出错误信号，sqlstate指sql错误代码，信息可以搜索sqlstate error查询。这样写可以帮助调用者发现错误原因，可以注明错误信息。

因为可能存在多个验证条件，因此，sqlstate和message并不矛盾，只有当错误码是对应的错误码时才会返回对应的信息，错误码可能重复。

输出参数

即使用参数来给调用程序返回值，上述的参数都是输入参数。

相当于给获取到的数据赋值。存储在变量中。调用如下：

变量

使用SET语句加上@符号前缀，通常在调用有输出参数的存储过程时使用。

用户或会话变量：用户使用时创建，用户断线后清空。

本地变量：存储过程或函数内定义的。只在存储过程中生效。

在存储过程中声明并使用本地变量如下：

declare语句声明变量。

decimal(9,2)表示最多9位数，小数点后两位数

invoices_count和 invoices_total这两个变量没有设置默认值，这是因为他们两个是选择语句选择出来的。

函数

创建函数和创建存储过程的语法相似，事实上，函数和存储过程也很像，区别是函数只能返回单一值，存储过程可以返回结果集。

mysql左侧右击创建。

RETURNS 声明返回值的类型，可以是INTEGER，INT 或其他允许的类型。

属性：可以有多个属性

deterministic：意思是特定的计算方式，即同样的输入永远会有同样的输出。当不想根据数据库中的数据返回值的时候比较有用。
reads sql data：意思是会有查询语句用以选择数据。
modifies sql data：意思是会有插入更新或删除语句。

调用

select client_id,name,risk_factor_client(client_id)
from clients

发现有缺失值，完善函数。

触发器

创建触发器语句为create trigger +名字，例如： payments_after_insert。

delimiter $$
create trigger payments_after_insert
	after insert on payments
    for each row
begin
	update invoices
    set payment_total=payment_total+ new.amount
    where invoice_id=new.invoice_id;
end $$
delimiter ;

第三行分别为触发位置，触发情况 on 加表名。after表示在之后，也可以用before,表示之前。insert表示插入数据这种情况触发，也可以用update,delete等。

for each row表示对于每一行，即如果插入多条数据，那么每一行数据都应用这个触发器。

new表示新的数据，也可以用old表示原有的记录。

查看触发器

show triggers

可以加入正则，查找满足条件的触发器。比如show triggers like’payments%’ 表示所有名称以payments开头的触发器

事务

将多个更改作为一个单元一起成功或失败。虽然没有使用事务语句，但一般的查询、更新等都被系统自动打包成为事务。

属性：A

原子性（Atomicity）：每个事务都是一个独立的单元，要么所有的语句都成功执行且事务提交，要么执行失败事务撤销。

一致性（Consistency）：不会出现有订单没有项目的情况。

独立性（Isolation）：事务之间相互独立，不会相互干扰。

持久性（Durability）：事务的更改是持久的。

start transaction;
insert into orders(customer_id,order_date,status)
values(1,'2019-01-01',1);
insert into order_items
values(last_insert_id(),1,1,1);
commit;
-- 如果是手动撤销事务，第6行换成rollback，这在逐行操作的时候常见。

并发

当多个用户同时访问和更改同一个数据时，出现的显示和更改问题。

mysql一般会对正在更改的数据进行锁定来解决并发问题。

常见的并发问题：

**脏读：**指第二个事务读取了第一个事务没有提交的数据。例如：事务A更新了数据，但是没有提交，这时候事务B选择了数据，并基于此做出了决策，但是，事务A撤回了修改，那么事务B就是基于脏数据做出的决策。

解决办法：事务隔离级别。

幻读:当事务A选择数据后，事务B更新了数据，那么A基于得到的数据做出的决策可能就不是准确的了。

丢失更新:AB同时提交更新，A先，B后，那么B就会覆盖A的更新。

不可重复读:B读取数据，A修改数据，B再次读取数据，发现数据和第一次读取的不一致。

事务隔离级别

由上到下，隔离级别逐渐增强。级别越强，并发数越少，但是需要更高的性能和锁定以及扩展性。默认的是可重复读级别。

show variables like 'transaction_isolation';
set transaction isolation level serializable;-- 设置下一个事务的隔离级别为序列
set session transaction isolation level serializable;-- 设置当前会话或连接以后的所有事务设置隔离级别。
set global transaction isolation level serializable;-- 设置所有会话的所有新事务的隔离级别。

读未提交事务隔离级别：

最低级的事务隔离级别，会产生大量的并发问题。

读已提交事务隔离级别：

不会产生脏读。但会有其他问题。

可重复读事务隔离级别：

读取的都是第一次的快照，即便在这期间数据发生了更改，也不影响。

序列化隔离级别：

相当于独立系统，最高级别的隔离。

死锁

不管是什么事务隔离级别，当某行数据更新时，这行会被锁定。如果两个事务同时等对方完成，就会形成死锁。

如果两个事务以相反的顺序更新数据，往往出现死锁。比如A：更新记录1；更新记录2 ；提交；B：更新记录2；更新记录1；提交。

数据类型

字符串类型

char:固定长度

varchar:可变长度

mediumtext:一般长的中文文本使用

longtext

以上这4种比较常见，且可以设置索引，为查询提速。如图，虚线下的不常见，且不能设置索引。

根据国际标准，英语占1个字节，欧洲语言占2个字节，亚洲语言占3个字节。所以，如果设置char(10)那么，mysql会留出30个字节。

整数

可以设置显示

最好使用尽量小且满足需求的类型。

小数

P表示精度，也就是位数，S表示小数位数。虚线上面后三个是第一个的同义词。虚线下面的两个主要是用于科学计算的，非常大或非常小的数时使用。

布尔型

枚举和集合类型

尽量避免使用。修改麻烦。可以设置专门的表来存储。

日期时间

二进制数据

比如图片、文件等，但一般来说，不要使用数据库存储二进制数据。

JSON类型

大括号括起来的键值对。

使用：用单引号和大括号引起来，键都要双引号。

也可以用函数创建

JSON_OBJECT函数，JSON_ARRAY函数

JSON_EXTRACT函数用于提取

也可以不用这个函数，使用指针

[0]是因为提取的数据在元组的第一个数据。

对于嵌套类型

对于字符串，要想去掉双引号

增加或者修改键值对

删除某个或某些键值对

设计数据库

数据建模

• 理解和分析业务需求
• 概念模型：业务中的实体、事务或概念以及它们之间的关系。可视化：实体关系图（ER）或UML（标准建模语言图）

• 逻辑模型：注意各个实体之间的关系以及属性类型。相比概念模型，更操作化。

• 实体模型：实体模型是逻辑模型通过特定数据库技术的实现。

注意，最好不要使用中文。如图，方框是因为中文显示问题。

主键：唯一标识

虽然在学生表中，邮件可以作为主键，但是主键最好不能更改，因此增加学生id列作为主键

外键：在一张表中引用了另一张表主键的那列。

在为两个表添加关系时，一个为父表或主键表，一个为子表或外键表。在这里，学生表是父表。

先在左侧选择关系类型，然后先点击外键表（子表），再点击父表。

可以用这两列设置复合主键，也可以另外选择一个注册id作为主键。复合主键的优点是可以防止不良数据，缺点是未来如果添加新的表且和这张表有父子关系时较为麻烦。

外键约束

如上，最右侧选择当主键表更新或删除时外键表如何反应。多数情况下，更新设置为同步更新，删除为不反应。只要有外键，就要设置这两个选项。

• RESTRICT指不反应
• CASCADE指同步更新
• SET NULL指设置为空，一般不选这种。
• NO ACTION也是不反应

最终建立实体模型如下：

标准化

确保设计遵循防止数据重复的预定义规则。一共7条规则，也成为7范式。范式之间是递增的。一般只需要检查前三范式。

第一范式

每行中每个单元格都有单一值，不会出现重复列。

因此，上面的tag列就不满足第一范式。解决方法是建立一张新表记录二者的关系。注意，课程表和标签表是多对多关系，在关系型数据库中，没有直接的多对多，只能通过中间表设置两次1对多，如下

第二范式

在第一范式基础上，每一张表都应该代表且仅代表一个实体。并且该表每一列都是该实体的属性。

如上，讲师就不属于课程的属性。因为一个讲师可能教授多门课程。这样的重复是无意义的。需要建立讲师表。

第三范式

在第二范式的基础上，简单点说，各列之间应该相互独立，也就是说，不存在任何一列可以由其他列计算得出。

不用太纠结于第几范式，只要看到重复值，且重复值不是像1，2，3，这样的外键时就需要注意标准化问题，究竟违背哪个范式，没那么重要。

正向工程：

没有数据的时候使用正向工程，也就是一个模型生成一个新的数据库

模型建好后就可以生成数据库了

同步模型

当已经有数据库了，需要数据库和模型同步。

逆向工程

没有模型的时候使用。

创建数据库

-- 创建和删除数据库
create database if not exists sql_store2;
drop database if exists sql_store2;
-- 创建表
use sql_store2;
create table customers
(	customer_id int primary key auto_increment,
	first_name varchar(50) not null,
    points int not null default 0,
    email varchar(255) not null unique
);
-- 都是列名加上属性
-- 更改表
alter table customers
	-- 添加列
	add last_name varchar(50) not null after first_name,
	-- 修改列
    modify first_name varchar(55) not null;
    -- 可以用drop points 删除points列
 -- 创建关系
 create table if not exists orders
(
	order_id int primary key,
    customer_id int not null,
    -- 外键设置，外键名（通常用fk+子表名加父表名） 括号里是外键列名
    -- 接着设置外键引用的表和列
    foreign key fk_orders_customers(customer_id)
    	references customers(customer_id)
    	on update cascade
    	on delete no action
);
-- 修改关系
alter table orders
	drop primary key,
    add primary key(order_id),
    drop foreign key fk_orders_customers,
    add foreign key fk_orders_customers(customer_id)
		references customers(customer_id)
		on update cascade
		on delete no action;

字符集和排序规则

如图，计算机存储数字，字符会被转换为数字表示。可以看出，mysql支持的字符集有许多种。第一列是字符集名字，第二列是支持的语言。第三列是默认排序规则，ci指不区分大小写。最后一列是最大长度列，以utf8为例，这也是默认的，因为它几乎支持所有国际通用的语言，可以看出，最大长度列为3，也就是说，会为每个字符预留3个字节。那么，如果设置某列为char(10)那么，mysql 会为每条记录分配10*3=30字节的空间，但是如果只是支持英文，那么只需要10字节（英文字符为1字节），空间大幅缩小，特别是如果数据量庞大。

查看排序规则（数据库级别）

修改（表级别），右侧的engine是存储引擎，一般不动，因为修改这个需要重建表。也可以用，engine=InnoDb来修改。

-- 数据库级别，创建时
create database db_name
	character set latin1;
-- 数据库级别，修改
alter database db_name
	character set latin1;
-- 表级别，创建时  
create table table1()
character set latin1;
-- 表级别，修改
alter table table1
character set latin1;
-- 列级别
create table customers
(	customer_id int primary key auto_increment,
	first_name varchar(50) character set latin1 not null,
    points int not null default 0,
    email varchar(255) not null unique
);

索引

本质上是搜索引擎用来快速查找数据的数据结构。不要基于表创建索引，因为这会增大性能负担，拖慢运行。而要基于查询进行索引。是基于二进制树的。

如图，可以看出，在做这个查询的时候，系统进行了逐行扫描（type:ALL），如果数据量庞大，这是很耗时的。

创建索引

可以看出，此时，已经不是逐行扫描。从行上来看，只扫描了37行而不是1010行。possible_keys指可能使用的索引，然后系统从中选择最佳的索引，在key中。

查看索引

查看表中的索引，如下，可以看出，顾客表中一共有3个索引，其中，第一个是主键，也成为聚集索引，系统会自动为主键添加索引。collation指数据在索引中的排列顺序，A指升序，D指降序。Cardinality指唯一值的估量，

要想查看准确的信息，需要如下

前缀索引

对于字符串文本等类型的数据，要想兼顾简洁和高效，使用前缀索引。

create index idx_lastname on customers(last_name(20));
-- 20指20个字符作为前缀，这个是自己设定的。

寻找合适的字符数：通常使用如下方法。可以看出，将字符数增加到10，并没有太大的增益，因此，设定为5

全文索引

比如数据库存储的是博客，有标题，有正文，如果用户希望搜索关键词，能又快又准确获得所需内容，解决方法：

create fulltext index idx_title_body on posts(title,body);
select *
from posts
where match(title,body) against('react redux');
-- 如上，匹配在列标题或正文中出现一个或者两个以及任意顺序排列的关键词（react redux）的记录。

结果是根据相关性得分排序的，可以查看相关性得分

有两种模式：自然语言模式（默认的）和布尔模式（可以包含或排除某些词），布尔模式如下：包含form排除redux

包含特定的短语可以用双引号

复合索引

可以为多列创建复合索引，尽量少创建单列索引，耗内存。

复合索引中列顺序规则：

• 更频繁使用的列排在前面（比如5个查询都基于state列，1个基于points列，那就将State放在前面）
• 唯一值数量更多的列排在前面
• 以上两个原则虽然重要，但一定要从查询本身出发看问题。

可以设置使用特定的索引。

注意：

使用索引时，必须把列单独列出来，不能放表达式。

当条件中使用or关键词时，最好使用两个查询然后连接起来。

最好不要用外部排序，因为非常耗费成本。

如上，可以查看上个操作耗费的成本。

如下，如果有索引（a,b），基于此索引进行排序，有如下三种，其他的会使用外部排序，耗费成本高。

设计

创建用户

如上语法创建新用户，john指用户名，可以限制访问位置，用@符号后跟域名或者IP或者主机名，域名如果要包含子域名，需要加%和.，另外加引号

完整的创建语句，1234指密码。

查看用户

删除用户，如上

修改密码

更改自己的密码

权限

1：对于用户，只有读写权限

*可以换为具体的表。

2：对于管理员

如上，意为给该管理员全部权限。第一个星号表示为全部数据库。

查看权限

撤销特权

如图，移除创建视图特权。