MySQL学习笔记（个人笔记不喜勿喷）

1.MySql基础使用

数据库的创建以及Mysql的基础使用

可以管理窗口的显示

以上是最常用的一些功能按键

2.选择语句

选择数据库

可以双击数据库或者输入指令USE（可以大写也可以小写，但是最好是大写sql中的关键字，小写其他内容）

USE sql_store

选中后对应的数据库名称会加粗显示

查询语句SELECT

select 后面写要获取的列项或者使用*表示获取所有列项

然后下面使用from子句表明要查询的表，这里查询customers表

USE sql_store;
SELECT customer_id,first_name
FROM customers

当你有几个语句时，要用；隔开

select可以写在一行，但是如果数据库太过庞大，会使得语句看起来复杂所以我们写成两行

SELECT后面写列的名称，如果这个列存放的是数字，那么你也可以进行计算再显示1出来

当我们想要自定义列的名字可以在后面加关键字AS+自定义名字

如果你的名字想要有空格或者_以外的字符可以把名字用''或""框起来

选择子句

当我们查询时想要得到一份数据的唯一列表（没有重复项）时，我们可以在select后面加上distinct关键字

可以看到有两个vm

WHERE子句

就是筛选语句，后面写出要满足的条件，对数据进行筛选

比如如下例子在所有顾客中找出积分大于3000的顾客

可以用的符号：

(> >= < <= = !=不等于也可以用<>)

当我们要找的日期在某日期之前或者之后时也可以用> <但是要注意我们用引号表述日期值 即使日期不算是字符串

AND OR NOT运算符

AND一般用于多条件

WHERE birth_data >'1990-01-01' AND points　＞　１０００

这种要同时满足两个条件的

而OR就是满足其中一个就可以的意思（当条语句既有OR和AND时AND的优先级更高但是最好还是将AND两边的语句括起来这样方便使用者查看）

WHERE birth_data >'1990-01-01' OR (points　＞　１０００ AND state=VA)

NOT用于否定一个条件

当WHERE后面使用了NOT后条件中的比较运算符都要反过来，且and和or互换

IN 运算符

当我们的选择条件是同属性的不同值时我们用and就显得很麻烦，

所以我们要用IN

WHERE quantity_in_stock IN (49,38,72)

当然如果值是字符串要加''

BETWEEN运算符

用于简写我们取值的范围

比如我们要筛选积分在1000到3000

WHERE points BETWEEN 1000 AND 3000
//或者
WHERE points >=1000 AND points<=3000 

(注意日期要用''括起来)

LIKE运算符

这里先举一个例子

WHERE last_name LIKE 'b%'

这是找姓以b开头的人

%表示省略多个字符

找名字中含有b的可以用%b%

_ 表示省略一个字符

REGEXP运算符

正则表达式的缩写用于搜索字符串

比如我们要搜索姓含有'field'的人我们用like是可以完成的

WHERE last_name LIKE '%field%'

这样就可以找到姓里面无论哪里含有filed的人但是我们用REGEXP就可以不用百分号了

WHERE last_name REGEXP 'field'

效果是完全一样的

当然用法也有不同

WHERE last_name REGEXP '^field'

表示last name必须以field开头

WHERE last_name REGEXP 'field$'

表示必须以field结尾

WHERE last_name REGEXP 'field|mac|rose'

可以同时找多个关键词关键词之间用竖线隔开

WHERE last_name REGEXP '^field|mac|rose'

当然也可以和上面的一起结合使用

假如你想搜索姓氏里面有e且e的前面有一些规定字母的人

WHERE last_name REGEXP '[gim]e'

就比如这样可以找到姓氏里有e且e前面是这三个其中一个的符合条件的所有数据

同样可以把括号写在后面找后面字母

当要求是一个范围时可以再[]里写a-h类似

IS NULL运算符

用于查找缺失某信息的数据

SELECT *
FROM orders
WHERE shipped_date IS NULL

比如查找那些没有发货的订单数据

如果要找所有发货了的我们可以加上NOT

SELECT *
FROM orders
WHERE shipped_date IS NOT NULL

ORDER BY子句

使数据进行排序显示

一般一个表都有属于它自己的主键，主键是唯一的，排序会默认按照主键排序，当我们要自定义某方式排序显示时就可以用到这个子句

点可击这个可以对表进行设置

当我们指定要以姓的首字母进行排序时

当然默认的就是升序排序，如果你想改为降序在后面加上DESC就行了

LIMIT子句

限定查询返回的记录

SELECT *
FROM customers
LIMIT 3

这样就限制只显示前三条，如果限制的数大于了所有的数据，则就显示所有数据

SELECT *
FROM customers
LIMIT 6,3

这里第一个数表示跳过前几个数据，后一个数表示显示几条数据，执行结果如下

这里要注意下各子句的顺序

3.多表连接

内连接

连接的关键字是JOIN而连接又分成内连接和外连接，内连接的关键字是INNER JOIN

但是INNER可写可不写

关键字后用ON 写两个表联合的数据

SELECT *
FROM orders
JOIN customers
	ON orders.customer_id=customers.customer_id

比如这个就是说连接orders和customers两张表要确保两个表中顾客id列一样

（注意要显示两个链接的表都有的列要指明位置如表名.列名）

这里也是可以简化代码的

SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id=c.customer_id

在表名后面可以自定义代称这样就可以简化下代码

跨数据库连接

​ 其实用法和内连接很相像，只不过这是两个数据库所以在写表的时候要在前面写上数据库的名字（只需要给不在当前数据库的表加前缀）

USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products p
 ON oi.product_id=p.product_id

这是在数据库sql_inventory中将库中的products表和sql_store数据库中的order_items相连接

自连接

一张表和他自己连接 ，这种有什么用呢，比如这里有个存放员工管理工作的表也就是上下级关系

我们可以通过自连接同时显示员工的详细信息以及其对应管理者的详细信息

USE sql_hr;
SELECT *
FROM employees e
JOIN employees m
	ON e.reports_to=m.employee_id

这里其实理解为复制了一张一样的表只是代号不一样，我们从e表中获取员工信息，从m表中获取对应的管理者信息，将两者合并

所以我们在筛选列的时候一定要注明是那张表

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

多表连接

其实和内连接差不多只是同一个数据库中一个表连接两个或两个以上的其他表

我们就依次像内连接一样依次连接就好了

比如我要链接如下三张表的信息（他们在同一个数据库中）

USE sql_store;

SELECT *
FROM orders o
JOIN  customers c
	ON o.customer_id=c.customer_id
JOIN order_statuses os
	ON o.status=os.order_status_id

这样三张表的信息都会显示到一张表上，但是列太多了，看起来信息又多有难找，所以我们接着筛选下我们需要的列

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

复合连接条件、

我们一般的表都有单一的列能够准确识别表中的一行比如

customers表中的customer_id这一列每个数字都是不一样的能识别单独一行，但是有些表没有这种列

这张表就需要用两列的信息唯一识别这一行

总的来说就是要满足两个列的值一一对应来完成连接我们叫做复合连接，写条件时直接加上AND就ok了

隐式连接语法

我们主要通过一个简单的例子来说明

USE sql_store;

SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id=c.customer_id

USE sql_store;

SELECT *
FROM orders o,customers c
WHERE o.customer_id=c.customer_id

这两个查询结果是一样的

但是隐式的连接会让人有时候忘了写条件，所以为了严谨我们一般还是写JOIN，只是需要知道这样的代码是啥意思就行了

外连接

OUTER JOIN

USE sql_store;

SELECT 
c.customer_id,
c.first_name,
o.order_id
FROM customers c
JOIN orders o
	ON c.customer_id=o.customer_id
    ORDER BY c.customer_id

当我们执行的时候，我们只会看到有订单的人的信息而不是所有人，因为有的人在order里面没有信息满足不了条件

所以这里有两种外连接

LEFT JOIN

USE sql_store;

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

当使用做链接时就是join左边那张表不管是否满足后面的条件都会显示所有

RIGHT JOIN

同理就是右边的表可以不管条件返回列中所有的信息

多表外连接

和多表的内连接类似

现在我们要在上节课的基础上显示出发货人的id其信息在shippers表中

USE sql_store;

SELECT 
c.customer_id,
c.first_name,
o.order_id,
s.shipper_id
FROM orders o
LEFT JOIN customers c
	ON c.customer_id=o.customer_id
JOIN shippers s
	ON o.shipper_id=s.shipper_id
    ORDER BY c.customer_id

发现只有这一点信息这是因为一些商品没有发货人信息，所以不满足条件没有显示，我们要让他全部显示无视条件就要用外连接

USE sql_store;

SELECT 
c.customer_id,
c.first_name,
o.order_id,
s.name
FROM customers c
LEFT JOIN orders o
	ON c.customer_id=o.customer_id
LEFT JOIN shippers s
	ON o.shipper_id=s.shipper_id
    ORDER BY c.customer_id

一般使用外连接就使用左连接，你左右连接混合会使人思维混乱

自外连接

回顾一下我们之前学的自连接来获取每个员工的管理者

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

但是这样会有一个问题就是有的员工没有管理者就不会显示出来，我们就可以将连接改成外连接这样就显示了

USING子句

用来简化连接

当我们要连接两个列的名字一模一样时就可以用USING来取代ON

USE sql_store;

SELECT *
FROM orders o
JOIN customers c
ON o.customer_id=c.customer_id

可以改成

USE sql_store;

SELECT *
FROM orders o
JOIN customers c
USING (customer_id)

这两个是一模一样的

这个关键字只能在不同的表中关联列名字完全一样的情况下使用

可能上面的例子不能体现其简化

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

这里我们的条件有两个且都是不同表的同名列所以我们用USING改写

USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin 
USING (order_id,product_id)

是不是简短多了

自然连接

关键字NATURAL JOIN

一般不建议使用，有时候结果会有出入，只是要知道这是干什么的

使用后就不用写条件了，系统自定义同名的列进行连接

交叉连接

我们用交叉连接结合或者连接第一个表的每条记录和第二个表的每条记录

关键字CROSS JOIN

两个表的每条记录都会互相结合，所以不用写条件

改进一下就可以将每个顾客与相对应的产品一一对应了

USE sql_store;
SELECT 
sh.name AS shipper,
p.name AS product
FROM shippers sh
CROSS JOIN products P
ORDER BY sh.name

练习

联合|Unions

关键字UNION

我们学的连接都是链接多张表的列，SQL里也可以连接多张表的行

比如我们筛选出一些有用信息，但是筛选条件不一样又想要一起显示结果时就i可以用联合

SELECT 
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date>='2019-01-01'

筛选出19年1月后的订单，状态显示Active

SELECT 
order_id,
order_date,
'Archived' AS status
FROM orders
WHERE order_date<'2019-01-01'

筛选出19年1月之前的订单状态显示Archived

随后将两个结果合并

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'

这里是同一张表但是我们也可以是不同的表

只是要记住查询返回的列的数量一定要一样，否者会得到错误提示

USE sql_store;
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

练习

4.增删改

1.列属性

当我们点击扳手图案就会跳出相应表的信息

首先是列名，以及定义的主键

对应列的数据类型（VARCHAR-可变字符类型--就是根据实际使用的字符数给空间，不会有空间的浪费如果用的是CHAR(50)那么就算你只存入五个字符，系统都会填入45个空格将其余空间占满）后面的数据表示可以存储的字符长度

PK-primary key主键

NN-NOT NULL 非空

AI-自动递增（通常被用在主键列，插入新数据时，将其排在最后且序号自加1）

Default/Expression-列的默认值

2.插入单行

关键字INSERT INTO VALUES（每一列的值）

如果是主键列且他有AI就可以输入不确定值DEFAULT避免数据重复

例：

INSERT INTO customers
VALUES(
DEFAULT,
'John',
'Smith',
'1990-01-01',
NULL,
'address',
'city',
'CA',
DEFAULT)

这列略过了电话号码 顾客id和积分因为他们有默认值或者有AI

还有一种写法就是指定要赋值的列名，这样不确定值和空值就可以不用写了，而且这种方法顺序随你写只是后面value里面的顺序要和你写的一样才行

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

就直接在values后面打逗号就可以了

4.插入分层行

往多表插入数据

这里我们当往一个表插入数据后想得到新数据所对应的id可以调用系统自带的方法

SELECT LAST_INSERT_ID()

这里我们显示出来只是为了展示一下，这个方法在赋值时直接用就好了

INSERT INTO orders (customer_id,order_date,status)
VALUES(1,'2019-01-02',1);

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

这些代码每执行一次，就会生成在上面我们显示方法时就执行了上半截一次所以orders里面就新增了两个

切实多表插入变化不多，只是需要调用方法而已

5.创建表复制

有时候我们有一张新的表要将旧表的数据全部记录进去这时候就不能用INSERT INTO了应为太麻烦了

1.创建新表

格式

**CREATE TABLE 新表名 AS **

SELECT * FROM 旧表名

CREATE TABLE orders_archiver AS 
SELECT * FROM orders

但是用这个方法复制表时，系统并不会继承其主键所以还是要自己去设置下主键

其中SELECT * FROM orders被称作子查询，如果你只想复制一些数据到一张表中那么就可以用到子查询

首先我们用查询语句筛选出我们需要的数据看看是不是正确的

然后将这些语句直接当作子查询

INSERT INTO orders_archiver
SELECT *
FROM orders
WHERE order_date<'2019-01-01'

就完成了复制

练习：

CREATE TABLE invoices_archive AS 
SELECT
i.invoice_id,
i.number,
c.name,
i.invoice_total,
i.payment_total
FROM invoices i
JOIN clients c
ON i.client_id=c.client_id
WHERE payment_date IS NOT NULL

6.更新单行

关键字UPDATE更新表中的一条或多条记录

SET用来指定一列或者多列的新值

WHERE选定到指定的行

UPDATE invoices
SET payment_total=invoice_total*0.5,payment_date=due_date
WHERE invoice_id=3

这里更改了第三行，当然同行的数据是可以直接访问计算后赋值的

你条件能选到几条就能同时更改几条所以单条和多条是一样改这里就不多弄一章了

同时选两个条件

WHERE client_id IN  (3,4)

7.在UPDATE中用子查询

我们接着上面的，比如我们只知道我们要改的用户名字叫做Myworkes我们要找他的clients_id就要到表clients里去找然后再去修改invoices

SELECT client_id
	FROM clients
	WHERE name='Myworks'

这本来是查询语句，现在我们将他作为子查询直接放到条件语句中

UPDATE invoices
SET payment_total=invoice_total*0.5,payment_date=due_date
WHERE client_id=
	(SELECT client_id
	FROM clients
	WHERE name='Myworks')

这样就可以一步修改了

如果条件语句中返回的是多个值那么就不能用等号连接了，改成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'))

8.删除行

一个简单的就是选中要删除的行右键删除就好了、

关键字 DELETE FROM 所在表名

WHERE 条件

同样可以用子查询

9.恢复数据库初始状态

点击file

选择open SQL script

选择用来创建数据库的文件如

点击运行就行了

5.函数

1.聚合函数

SQL中有一些自带的函数，其中一些叫做聚合函数

常见函数：MAX(),MIN(),AVG(),SUM()，COUNT()--这些函数我们在学算法的时候经常看到，看到名字就知道啥意思

SELECT MAX(invoice_total)
FROM invoices

比如用max（）函数筛选总价最高的，执行后你会发现列名变成了

这些函数只运算非空数据

当用COUNT()函数获取数量的时候，如果想去除重复的项可以在里面加上DISTINCT

COUNT(DISTINCT client_id)--这样就去除了相同client——id的数据，只记录一次

SELECT 'First half 0f 2019' AS date_range,
		SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payment,
        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 0f 2019' AS date_range,
		SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payment,
        SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date 	BETWEEN'2019-07-01'AND'2020-01-01'
UNION
SELECT 'Total 0f 2019' AS date_range,
		SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payment,
        SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date >'2019-01-01'

练习

GROUP BY子句

上面我们用一个简单的SUM（）就算出了某时间段的销售额总数，可是如果你想知道每个客户的总销售额的话就要用到子句GROUP BY

GROUP BY 列名会根据后面的列名来进行分组比如

GROUP BY client_id

就是client_id一样的为一个组将其信息整合

默认是我们定义的那个列进行排序的，也可以用ORDER BY来调整顺序

SELECT 
		client_id,
		SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC

注意：如果要加条件语句WHERE请放在GROUP BY 前面

SELECT 
		p.date,
        pm.name AS payment_method,
        SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
GROUP BY date,name

例子

HAVING子句

用于分组过后对数据进行筛选，因为where不能在GROUP BY 后面

SELECT 
		client_id,
		SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales>500

其也是一个条件语

注意：HAVING里面的列名必须在SELECT里面，SELECT下面没有但是表里面有的列名是不能用的

练习：

SELECT 
		c.customer_id,
        c.first_name,
        c.last_name,
        c.state,
        sum(oi.quantity*oi.unit_price) AS total_spent
FROM orders o
JOIN customers c
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_spent>100

ROLLUP运算符

sql的WITH ROLLUP用于汇总数据

我们在GROUP BY 后面加上这个运算符系统就会计算所显示的数据的总和在后面

当然只会计算数值

练习：

SELECT 
		pm.name AS payment_method,
        SUM(p.amount) AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
GROUP BY pm.name WITH ROLLUP

6.编写复杂查询

子查询

这个也是常用于我们筛选信息，当我们知道一个产品的序号要找到比他单价更贵的其他产品，我们首先要找到其对应的价格再写查询语句，这种要查询两次的就可以用一次子查询来实现、

SELECT *
FROM products
WHERE unit_price>(
SELECT unit_price
FROM products
WHERE product_id=3
)

IN运算符

z这个和前面讲的基本一样，用于筛选范围的

SELECT DISTINCT client_id
FROM clients
WHERE client_id NOT IN (
SELECT DISTINCT client_id
FROM invoices
)

子查询和连接

上面一节我们学的是子查询的方式，当然我们也可以选择连接的方式来进行信息的筛选，只是要视情况来选择是选择连接好还是子查询

SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

这种连接的方法就很聪明，两个表连接之后，行数补齐之后没有发票的信息对应的invoice_id就会是空的

练习：找到谁买了生菜（shipper_id=3）

法一：

SELECT 
	c.customer_id,
	c.first_name,
    c.last_name
FROM customers c
WHERE customer_id=
(SELECT customer_id
FROM orders O
WHERE shipper_id=3)

法二：

SELECT 
	c.customer_id,
	c.first_name,
    c.last_name
FROM customers c
JOIN orders USING(customer_id)
WHERE shipper_id=3

ALL关键字

在我们进行比较的时候如果使目标值大于或小于一个数组中的所有数，我们一般会先找出数组中的最大值或者最小值，但是如果你在数组前面加上ALL就不用找了

比如
......
WHERE O>MAX(156,127.168,369)
和
WHERE O>ALL(156,127.168,369)
是一样的

ANY/SOME关键字

举个例子，我们要找到有两张发票以上的客户

SELECT *
FROM clients
WHERE client_id IN
(SELECT 
	client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*)>=2
)

或者

SELECT *
FROM clients
WHERE client_id = ANY
(SELECT 
	client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*)>=2
)

意思就是ANY和IN一样的

相关子查询

当我们有多组数据要分开进行相同的处理时就相当于算法中的FOR

我们有个例题是筛选出这张表中大于相同部门平均工资的员工，我们就用的是相关子查询的方法

SELECT *
FROM employees e
WHERE salary>
(SELECT 
	AVG(salary)
FROM employees
WHERE office_id=e.office_id
)

这个方法有点难理解，首先程序执行到外层查询时因为每个数都要比较所以会从第一个数开始然后再执行子查询（有点像是双重for）此时子查询会遍历office_id等于第一个数的所有数的平均值再同第一个数比较。依次类推每一个数都会重复上面的操作。这段代码子查询有外查询的引用（相关性）所以子查询会执行多次，所以相关子查询一般会比较慢且数据越多，查询也就跟费力，也会占用更多存储。

EXISTS运算符

这个运算符解决的是当我们使用子查询且子查询返回了一堆符合条件的数据时，我们就可以用这种方法。

下面是简单的双重查询：

SELECT *
FROM clients
WHERE client_id IN(
	SELECT DISTINCT client_id
    FROM invoices
)

这种方法是不会返回一个数组给外查询的where，而是子查询每一行都会判断是否满足条件如果满足条件的行保存到EXISTS中这样就能节省时间

SELECT *
FROM clients c
WHERE EXISTS(
	SELECT client_id
    FROM invoices
    WHERE client_id=c.client_id
)

练习：找到重来没有卖出去过的产品信息

SELECT *
FROM products p
WHERE NOT EXISTS(
	SELECT product_id
    FROM order_items
    WHERE  product_id=p.product_id
)

SELECT子句中的子查询

我们前面也看到了WHERE语句中最常用子查询，其实我们的SELECT语句也是可以使用子查询的

当我们有计算比如说是计算平均值的时候，我们在查询里直接使用AVG（）函数查询会只出现一个数据，因为这个函数只会返回一个数据所以使结果只有一行

SELECT 
	invoice_id,
    invoice_total,
    AVG(invoice_total)
FROM invoices

我们在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,
    name,
    (SELECT SUM(payment_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 diffience
    FROM clients c
    

现在子查询多了就显得格式比叫混乱，所以写的时候要注意子查询都要用括号括起来，方法也是一个独立的括号而AS语句就不用括号括起来了

FROM子句中的子查询

接着使用我们上节课的例子，我们可以将上节课的练习整体当成是一个子查询写到FROM 后面

SELECT *
FROM （SELECT 
	client_id,
    name,
    (SELECT SUM(payment_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 diffience
    FROM clients c
    ） AS sales_summary

每当我们使用FROM的子查询的时候我们需要给子查询一个别名,这种方法是为了再次筛选数据我们可以直接在后面添加WHERE语句进行筛选

7.内置函数

数值函数

1.ROUND函数又叫轮函数用来四舍五入数字

SELECT ROUND(5.73)

这样我们会得到一个整数

SELECT ROUND(5.73,1)

这样是保留一位小数

2.CEILING(上限函数)

用来返回大于或等于某值的最小整数

SELECT CEILING(5.7)

比如这个就会返回6

3.FLOOR（下限函数）

用来返回小于或等于某数的最大整数

4.ABS

用来计算绝对值的函数

5.RAND（）

用来生成0-1区间的随机浮点数。

这些是最常用的函数，如果还想看看其他的数值函数可以去网上查

字符串函数

1.LENGTH得到字符串的字符数

2.UPPER/LOWER将字符串转化成大写和小写字母

3.LTRIM(left trim)左修整/RTRIM--移除字符串左侧或右侧的空白字符或者其他预定义字符

4.TRIM删除所有左右两侧的空格

5.LEFT/RIGHT（'String',number）返回字符串左侧或右侧的几个字符

6.SUBSTRING（'String',起始位置，长度）获取一个字符串中任意位置的字符，如果不定义长度就会返回起始位置之后的全部字符

7.LOCATE返回第一个字符或者一处阿姊夫匹配的位置

SELECT LOCATE('n','Kindergarten')

第一个参数是你要查找的字符（不分大小写）没有符合条件的也会返回0

我们也可以搜索一个字符串

8.REPLACE(原字符串，要替换的字符串，新的字符串)替换字符串

SELECT REPLACE('Kindergarten','garten','garden')

9.CONCAT（‘String1','String2）串联两个字符串

日期函数

1.NOW()调用当前的日期及时间

2.CURDATE()当前的日期

3.CURTIME（）当前时间

4YEAR/MOUNTH/DAY（）提取日期的年份,月份，号数

5.HOUR/MINUTE/SECOND()返回时间的时分秒。

6.DAYNAME()返回星期几

7.MONTHNAME()返回字符串式的月份

8.EXTRACT（）

SELECT EXTRACT（YEAR/MONTH/DAY FROM NOW()）

格式化日期和时间

1.DATE_FROMAT()要用到两个参数一个是日期值一个是格式字符串

SELECT DATA_FORMAT(NOW(),'%M/m %d %Y/y')

当是M时会返回字符串式的月份，m则会返回数字

Y会返回4位数的年份，y会返回两位数

计算日期和时间

1.DATE_ADD()给时间日期值添加日期成分

SELECT DATA_ADD(NOW(),INTERVAL 1 DAY)

这是给日期加上一天的时间，也可以是增加一年，一个小时，一个月等等

如果要减去就直接传入负数就可以了

2.DATEDIFF（）返回两个日期的间隔

3.TIME_TO_SEC()返回某个时间与零点的间隔（秒数），要计算两个时间的间隔就分别调用函数后减去就行了

IFNULL/COALESCE函数

1.IFNULL用于如果返回值是空值时，更改返回为自定义

SELECT
order_id,
IFNULL(shipper_id,'Not assigned')
FROM orders

如上当其shipper_id为空的时候就会返回我们自定义的字符串而不是NULL

2.COALESCE它与IFNULL的区别是它可以提供一堆值，如果一个为空就去看下一个是否为空，当提供的都为空的时候才返回自定义的值

IF函数

格式：

IF（条件，符合返回的值，其他返回的值）

如果我们不写if将两段数据分开就要写两个查询再用union连接起来而我们使用if就只用写一段查询就够了

.例子

SELECT 
	p.product_id,
    p.name,
    COUNT(*) AS orders,
    IF(COUNT(*                                                                                                          )>1,'Many times','Once')
    FROM products p
    JOIN order_items oi USING(product_id)
    GROUP BY product_id,name

case运算符

上面我们学了IF函数，但是IF只有一个判别式所以当我们有多个判别式的时候就可以用case

格式：

SELECT
	CASE
		WHEN 表达式 THEN 为真返回值
		WHEN 表达式2 THEN 为真返回值
		......
		ELSE 返回值
	END AS category
FROM orders

8.视图

1.创建视图

我们可以将查询语句保存为视图这样就不用重复的写查询了

结构：

CREATE VIEW 视图名 AS
查询语句

执行过后视图会保存在view里面

注意：视图不存储数据，我们的数据存储在表中。

2.删除视图或更改视图

删除：

DROP VIEW 视图名

更改视图

CREATE OR REP;ACE VIEW 视图名

3.可更新视图

当一个视图没有用DISTINCT关键字，没有用到任何的聚合函数或者GROUPBY,也没有用UNION运算符那么我们就称他为可更新函数

DELETE FROM invoices_with_balance
WHERE invoice_id=1

这是删除，当然我们也可以更新

UPDATE  invoices_with_balance
SET due_date=DATE_ADD(due_date,INTERVAL 2 DAY)
WHERE invoice_id=2

4.WITH OPTION CHECK子句

有一些表单会有自己的行为比如当余额为零时会自己删除所在行，当你不想让这种现象发生时可以在创建或修改视图时查询语句的末尾加上这句

9.存储过程

创建一个存储过程

关键字

CREATE PROCEDURE 名字(参数)

BEGING

​ **查询语句 **

END

更改默认分隔符

DELIMITER 自定义分隔符（默认的是$$）

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
END $$

执行过后我们可以看到生成了新的存储过程

我们可以点击来执行存储过程也可以通过CALL 来执行·

CALL get_clients()

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
	SELECT 
    invoice_id
    FROM invoices
    WHERE invoice_total-payment_total>0;
END $$

2.使用工具台创建存储空间

右键工具台

点击创建

你只用跟改下名字并且补全查询语句并且用分号结束就可以了

然后点击apply

这样我们就不用每次都要重新更改分隔符了

3、删除存储过程

关键字 DROP PROCEDURE +名字

一般更安全的删除存储过程可以用

DROP PROCEDURE IF EXISTS+名字

或者是点击右键来删除

4.参数

调用时给定的参数用于筛选信息

CREATE PROCEDURE get_clients_by_state
(
	state CHAR (2)--一般我们会用VARCHAR可变长度的字符类型
)
BEGING
	SELECT * FROM CLIENTS c
	WHERE c.state=state;
	END

创建完毕之后我们

CALL get_clients_by_state（‘CA’）

就只会调用出州是在CA的信息了

5.带默认参数

就是当我们调用时不输入参数的情况让他给定一个默认参数，这里就在begin里面用上IF就行了，只是这样要加END IF

CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
	IF state IS NULL THEN
	SET state='CA';
	END IF;
	SELECT *
    FROM clients c
    WHERE c.state=state;
END

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
    p.payment_method=IFNULL(payment_method_id,p.payment_method);
END $$

参数验证

我们先写一个存储过程来更新我们的数据

CREATE PROCEDURE make_payment(
	invoice_id INT,
    payment_amount DECIMAL(9,2),
    payment_date DATE
)
BEGIN
	UPDATE invoices i
    SET 
    i.payment_total=payment_amount,
    i.payment_date=payment_date
    WHERE i.invoice_id=invoice_id;
END

这样当我们想更新数据的时候就不用再写查询了可以直接调用我们的存储过程，但是有的时候我们的参数类型是有要求的，所以我们通过参数的验证来避免存入错误的数据到数据库中

这个有点像java中的抛出异常

我们在UPDATE之前写一个判断语句，同时要用上关键字SIGNAL SQLSTATE 后面是输出的错误类型，具体的要到网上去查，我们这里用的是数据超出规定范围，查到的是22003(注意这是字符串)，随后我们一般还要写出提示

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
	invoice_id INT,
    payment_amount DECIMAL(9,2),
    payment_date DATE
)
BEGIN
	IF payment_amopunt<=0 THEN
	  SIGNAL SQLSTATE '22003'
	  SET MESSAGE_TEXT ='错误提示语句';
	  END IF;
	UPDATE invoices i
    SET 
    i.payment_total=payment_amount,
    i.payment_date=payment_date
    WHERE i.invoice_id=invoice_id;
END

输出参数

我们一般通过调用这个来获取要查询的值，我们也可以通过参数获取这些值

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
	client_id INT
 )
BEGIN
SELECT COUNT(*),
SUM(invoice_total)
FROM invoices i
WHERE i.client_id=client_id AND payment_total=0;
END

就是定义两个参数用于输出得到的数据，代码如下

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
	client_id INT,
	OUT invoices_count INT,
    OUT invoices_total DECIMAL(9,2)
 )
BEGIN
SELECT COUNT(*),
SUM(invoice_total)
INTO invoices_count,invoices_total
FROM invoices i
WHERE i.client_id=client_id AND payment_total=0;
END

定义了两个用于·OUT的参数，我们在调用时不需要赋值

我们会发现输出的数据是一样的只是列名变成了我们自定义的参数名

变量

上节课我们就使用到了用户变量，这些变量我们使用SET语句定义，并加上@前缀，通常我们会在调用有输出参数的存储过程使用这些变量

但是这种变量会在客户断开sql时就被清空

MYSQL中还有一种变量叫做‘本地变量’这种变量不会在客户使用的过程中一直保存，一番我们存储过程完成执行任务这些变量就会被清空

这里本地便用要用到关键字DECLARE 再用DEFAULT赋初始值

CREATE PROCEDURE get_risk_factor
 (
 )
BEGIN
DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
DECLARE invoices_total DECIMAL(9,2);
DECLARE invoices_count INT;
SELECT COUNT(*),SUM(invoice_total)
INTO invoices_count,invoices_total
FROM invoices;
SET risk_factor=invoices_total/invoices_count*5;
SELECT risk_factor;
END

这里就很像我们java中的局部变量，一旦存储完成就会释放其存储空间

函数

创建自己的函数，函数其实和存储过程很像但是函数只会返回单一值

在数据库中你可以看到FUNCTION的一行我们同样右键点击创建函数，当然你也可以完全用代码创建，不过这种可以减少很多工作量

它和存储过程的的语法基本上一模一样，只是多了一个关键字来说明返回值类型

RETURNS 数据类型

后面紧接着跟上属性，每个·mysql都至少要有一个属性

1.DETERMINISTIC(确定性的)，给这个函数同一组数值这个函数永远都会返回同一个数值

2.READS SQL DATA（读取sql数据，函数中会配置选择语句，用以读取一些数据）

3.MODIFIES SQLDATA(修改sql数据)函数中插入，更新或者删除函数

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_risk_factor`(
 )
BEGIN
DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
DECLARE invoices_total DECIMAL(9,2);
DECLARE invoices_count INT;
SELECT COUNT(*),SUM(invoice_total)
INTO invoices_count,invoices_total
FROM invoices;
SET risk_factor=invoices_total/invoices_count*5;
SELECT risk_factor;
END

随后我们就可以在查寻中直接调用该函数了

10.触发器

创建触发器

有的表中一跳信息可以对应好几个其他信息，当我们录入一个新的记录时，要确定表中的某个信息会随之改变，比如加上或者减去新的值，这种情况就可以用触发器了

步骤：

1.修改默认分隔符

DELIMITER $$
BEGIN

END $$ 
DELIMITER ;

CREATE TRIGGER payment_after_insert
AFTER INSERT ON payments

这里名字最好是见名知意

下面呢表明AFTER/BEFORE表明是更新之前还是之后启动，UPDATE/DELETE/INSERT更新删除创建具体根据我们的需求来写

下一行我们输入

FOR EACH ROW

意思是这个触发器会作用与每一个受影响的行

以下是创建一个完整的触发器

DELIMITER $$

CREATE TRIGGER payment_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 ;

INSERT INTO payments
VALUES(DEFAULT,5,3,'2019-01-01',10,1)

随后我们插入行，如上会发现invoices里面的3对应的payment_total发生了改变

我们再写一个删除触发器

DELIMITER $$

CREATE TRIGGER payment_after_delete
AFTER DELETE ON payments
FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total=payment_total-OLD.amount
	WHERE invoice_id=OLD.invoice_id;
END $$ 
DELIMITER ;

DELETE
FROM payments
WHERE payment_id=9

将刚刚的删除掉

查看触发器

在mysql上我们不能直观的看到我们的触发器

但是我们可以用命令SHOW TRIGGERS

删除触发器

关键字 DROP TRIGGER IF EXISTS +名字

使用触发器进行审计

触发器的宁一个常见用途是为了之后的审计的目的而记录对数据库的修改

简单来说就是用来记录一些操作的

一般就是在触发器的begin里面加入

INSERT INTO payments_audit
VALUES(NEW.client_id,NEW.date,NEW.amount,'Insert',NOW());

这样在我们删除一个记录之后就可以记录下时间以及对谁操作了什么

事件

时间是根据计划执行的任务或一堆SQL代码

首先要打开Mysql的事件调度器

关键字

SHOW VARIABLES LIKE 'event

如果你发现你要找的调度器关闭了你可以用set将其打开或者关闭

SET GLOBAL event_scheduler=OFF/ON

接下来我们就可以写事件让其在固定的时间执行一次

DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
	AT '2022-03-01'
DO BEGIN
	DELETE FROM payments_audit
    WHERE action_date<NOW()-INTERVAL 1 YEAR;
END $$
DELIMITER ;

如果是要循环时间执行我们可以将AT改成

EVERY 1 YEAR/MOUTH/DAY STARTS '2019-01-01' ENDS '2023-01-01'

查看，删除和更改事件

1.查看

SHOW EVENTS;

当我们想要查看以年循环的事件时可以在后面加上 LIKE 'yealy%'

2.删除

DROPP EVENT IF EXISTS +名字

3.修改

ALTER EVENT

其语法和创建是一样的只需要将CREATE 改成ALTER

11.事务

创建事务

事务就是确定多个事件都能完成，比如一个订单要求多个输入语句输入信息，为了避免突发情况使得有的输入语句中断，我们得到不完整的订单信息我们可以使用事务来避免

关键字 START TRANSACTION

随后直接写要执行的语句就行了，只是一个语句写完了要打分号

最后再用关键字 COMMIT

来关闭事务

USE sql_store;

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;

并发和锁定

现实中一般会有两个甚至更多的用户同时访问数据，这就是并发问题

一些语句的锁定，是其默认行为，可以防止一些并发问题，但是有的时候默认行为不能满足就需要我们改写默认行为

并发问题

1.丢失更新：两个事务尝试更新相同的数据并且没有上锁时，就会发生这种情况较晚的事务就会覆盖较早的事务的更改

这种情况用锁定机制，防止两个事务同时更新同样的数据，他们会一个一个按顺序运行

2.脏读：当一个事务读取了尚未被提交的数据，意思是这个数据是错误的

我们需要为事务建立隔离级别，这样事务修改的数据不会马上被其他事务读取

3.不可重复读取： 在事务过程中，读取了某个数据两次，并得到了不同的结果怎么办

这也是通过隔离级别来解决，只是方式不一样

4.幻取： 查询数据时，没有看到同时更新了的符合条件的数据，造成了数据的遗漏

隔离级别：序列化 确保当有数据正在更新时，我们的查询能够等待其更新完成在查询

事务隔离级别

隔离级别也分为四个级别，下面这张表就反映了对应的级别可以解决什么问题（问题上面说了）

隔离级别由低到高分别是：

• 读未提交 可以读彼此未提交的更改

• 读已提交 只能读取已经提交的数据，防止脏读

• 可重复读取 确保不同的读取会返回同样的结果

• 可序化 基本可以解决所有的问题但是会加重程序的负载

  所以隔离级别越高，会存在越重的性能和可拓展性问题，因为他需要更多的锁和资源

  要想更改隔离等级我们先查看下隔离级别

  SHOW VARIABLES LIKE 'transaction_isolation';

可以看到系统默认的是可重复读取

更改语句

SET TRANSACTION ISOLATION LEVEL +级别（SERIALIZABLE）;

这是为下一个事务设置隔离级别

我们也可以为当前事务或连接所有以后事务设定隔离级别只需要在SET后面加 SESSION

对所有会话中的所有新事务设置全局隔离级别是在SET后加 GLOBAL

应用：

首先我们将下一个事务的隔离级别改成-读未提交

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customer_id=1;

在第一个用户中只执行前两行代码

随后打开第二个用户

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points=20
WHERE customer_id=1;
COMMIT;

创建一个事务，执行前三行

再回到第一用户执行第三行

你会发现只读取到了20积分

这是第二用户修改后应该改有的值，那如果第二用户写该数据时服务器崩了数据没提交成功呢

我们模拟这种情况将第二用户最后一行代码改成 ROLLBACK并只执行最后一行

现在呢第一用户的查询就是错的了，因为这数据没有修改成功

这就是脏读

第二隔离级别-读已提交

第一用户更改下一级别

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT points
FROM customers
WHERE customer_id=1;

只执行第二行后转到第二用户

START TRANSACTION;
UPDATE customers
SET points=20

执行这三行代码

再回到第一用户执行查询

我们可以看到查到的仍然是原来的，这是因为我们修改的数据还没有提交应为我们是手动逐条执行，所以我们没有选的代码他是不会执行的，当我们回到第二用户执行COMMIT后表示提交成功了，再到第一用户执行查询你会发现只有20积分了

所以在这个隔离情况下我们不可能存在脏读但是会存在其他问题

其他的隔离级别使用方式基本是一样的只是名字的不同以及使用的条件不同

死锁

死锁就是当不同事务均因握住了别的事务需要的锁而无法完成的情况，这个死锁和我学语言的时候学的死锁类似，比如A B两个程序，A执行的条件是B先完成，而B执行的条件是A完成这两个程序就会无限等待，这就是死锁
死锁是不可避免的，但是可以想办法减少频率，比如减少程序运行时间，或者更改程序执行时间段，易冲突的程序分开执行等等