数据库
选择语句
USE sql_store;
SELECT *
FROM customers
– WHERE customer_id = 1
ORDER BY first_name
选择子句
SELECT first_name,last_name
FROM customers
DISTINCT:选择不重复
SELECT DISTINCT first_name
FROM customers；
AS:命名一个列进行显示
SELECT price price*10 AS ‘new price’
FROM customers;
等于小于大于不等于
SELECT *
FROM customers
WHERE birthdate > ‘1990-01-01’（日期的表达形式）
AND 优先级较高
OR
SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR ( points > 1000 AND state = 'VA')
NOT 运算符
SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000)
IN 运算符
SELECT *
FROM customers
WHERE state IN('VA','FL','GA')
SELECT *
FROM customers
WHERE state NOT IN('VA','FL','GA')
BETWEEN 运算符
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
SELECT *
FROM customers
WHERE birthdate BETWEEN '1990-01-01' AND '2000-01-01'
-- 选择大于等于1000 小于等于3000的所有记录，等同于下面的语句
SELECT *
FROM customers
WHERE points >= 1000 AND points <= 3000
LIKE 运算符（选择遵循某种特定字符串）
SELECT *
FROM customers
WHERE last_name LIKE 'B%'
-- %表示任意字符，可以放在任意位置，B不区分大小写，上述表示选择last_name是以B或者b开头的字符串
SELECT *
FROM customers
WHERE last_name LIKE 'Brush%'
-- 上述表示是以Brush开头的字符串
SELECT *
FROM customers
WHERE last_name LIKE '%y'
-- 上述表示是以y结尾的字符串
SELECT *
FROM customers
WHERE last_name LIKE '%B%'
-- 上述表示只要字符串中有B即可，不管B在开头，中间还是结尾
SELECT *
FROM customers
WHERE phone NOT LIKE '%9'
-- 找到电话号码的结尾不是9的
_运算符，表示一个单字符，_可表示任意一个字符
SELECT *
FROM customers
WHERE last_name LIKE 'b_____y'
-- 找到开头为b，中间有5个字符，结尾为y的字符串
REGEXP 正则表达式
SELECT *
FROM customers
WHERE last_name REGEXP 'field'
-- 使用正则表达式同样可以查找到last_name 中具有field字符串，同LIKE一样的查询结果
SELECT *
FROM customers
WHERE last_name LIKE '%field%'
-- 同上面使用正则表达式一样的结果，但是正则表达式要简单一些
SELECT *
FROM customers
WHERE last_name REGEXP '^field'
-- 正则表达式使用^,找到last_name是以field开头的字符串
SELECT *
FROM customers
WHERE last_name REGEXP 'field$'
-- 正则表达式使用$,找到last_name是以field结尾的字符串
SELECT *
FROM customers
WHERE last_name REGEXP 'field|mac'
-- 正则表达式使用|,| 表示多个搜寻模式，找到last_name含有field或者mac
SELECT *
FROM customers
WHERE last_name REGEXP '^field|mac|rose'
-- 正则表达式使用|,| 表示多个搜寻模式，找到last_name含有field开头或者含有mac或者含有rose
SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e'
-- 正则表达式使用[]，找到last_name中含有字符e,并且e前面含有g或者i或者m
SELECT *
FROM customers
WHERE last_name REGEXP 'e[gim]'
-- 正则表达式使用[]，找到last_name中含有字符e,并且e后面含有g或者i或者m
SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e'
-- 正则表达式使用[]，找到last_name中含有字符e,并且e前面含有a-h之间的任意字母
-- 总结
-- ^ beginning
-- $ end
-- | 逻辑或
-- [abcd]
-- [a-f]
NULL 运算符
SELECT *
FROM customers
WHERE phone IS NULL
-- IS NULL的使用
SELECT *
FROM customers
WHERE phone IS NOT NULL
ORDER BY子句 排序
SELECT *
FROM customers
ORDER BY first_name
-- 以first_name进行排序，则会以first_name列英文字母的顺序开始从前到后排序，即升序
SELECT *
FROM customers
ORDER BY first_name DESC
-- 以first_name进行排序，则会以first_name列英文字母的顺序开始从后到前排序，即降序
SELECT *
FROM customers
ORDER BY state,first_name
-- 以first_name进行排序，先以state值进行升序排序，然后state值相同的以first_name升序排序
SELECT *
FROM customers
ORDER BY state DESC,first_name DESC
-- 以first_name进行排序，先以state值进行降序排序，然后state值相同的以first_name降序排序
-- mysql与其他数据库的一个区别是排序的条件不需要一定是SELECT 选中的列,可以是别名或者算术表达式，如下面在mysql是合法的，但是在其他的数据库中是不合法的
SELECT first_name,last_name
FROM customers
ORDER BY birthdate
-- 选择first_name,last_name列，但是排序条件是birthdate
SELECT *,quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY quantity * unit_price DESC
-- 使用quantity * unit_price进行计算并排序
LIMIT子句，控制选择的行数，LIMIT子句永远放在最后
SELECT *
FROM customers
LIMIT 5
-- 只显示前五行
SELECT *
FROM customers
LIMIT 6,3
-- 从第6行开始计算，显示前3行
内连接
内连接，在多个表中检索数据
SELECT *
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id
-- 将会将order_items表和products表中product_id列相同的行连接在连接起来合并成一个表
-- order_items oi，products p 这里oi和p跟在后面起到重命名的作用
跨数据库连接，下面的方式将不同数据库的表进行连接
USE sql_inventory
SELECT *
FROM sql_store.order_items oi
JOIN products p
ON oi.product_id = p.product_id
-- 声明了sql_inventory后，相当于当前环境在sql_inventory数据库，则products就不需要加前缀了，但是order_items在sql_store数据库中，则就需要添加前缀
连接多个表
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
列属性
插入单行数据
INSERT INTO shippers () VALUES()
插入多行数据
INSERT INTO sql_store.shippers (name) VALUES ('shippers1'),('shippers2'),('shippers3')
INSERT INTO sql_store.shippers (name,quantity_in_stock,unit_price)
VALUES ('products1',10,1.95),
('products2',11,1.95),
('products3',12,1.95)
插入分层行
INSERT INTO sql_store.orders (customer_id,order_date,status)
VALUES (1,'2019-01-02',1);
INSERT INTO sql_store.order_items
VALUES (LAST_INSERT_ID(),1,1,2.95),
(LAST_INSERT_ID(),2,1,2.95)
-- LAST_INSERT_ID() 表示上一次插入得到的ID,这里就是第一个INSERT INTO插入得到的ID
创建表复制
USE sql_store;
CREATE TABLE orders_archived AS
SELECT * FROM orders
-- 创建的新的表为orders_archived，复制orders到内容到orders_archived
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'
-- 将orders表中满足一定条件的记录数据插入到orders_archived表中
更新单行
UPDATE sql_invoicing.invoices
SET payment_total = 10.0,payment_date = '2019-03-01'
WHERE invoice_id = 1
-- 调用UPDATE关键字更新invoices表中的payment_total字段，payment_date字段，其中invoice_id要是1
UPDATE sql_invoicing.invoices
SET payment_total = invoice_total*0.5,payment_date = due_date
WHERE invoice_id = 3
-- 同样调用UPDATE更新字段，也可以使用对应记录的其他字段值进行更新
更新多行
UPDATE sql_invoicing.invoices
SET payment_total = invoice_total*0.5,payment_date = due_date
WHERE client_id = 3
-- 当where中的判定条件得到多行满足时，可以更新多行数据
UPDATE sql_invoicing.invoices
SET payment_total = invoice_total*0.5,payment_date = due_date
WHERE client_id IN (3,4)
-- 当where中的判定条件得到多行满足时，可以更新多行数据
在updates中用子查询
USE sql_invoicing;
UPDATE sql_invoicing.invoices
SET payment_total = invoice_total*0.5,payment_date = due_date
WHERE client_id =
(SELECT client_id
FROM sql_invoicing.clients
where name = 'Myworks')
-- 子句中使用SELECT 语句先查询到对应的ID,然后作为WHERE语句中的值
USE sql_invoicing;
UPDATE sql_invoicing.invoices
SET payment_total = invoice_total*0.5,payment_date = due_date
WHERE client_id IN
(SELECT client_id
FROM sql_invoicing.clients
where state IN ('CA','NY'))
-- 当子句中多个条件是满足时，WHERE语句中需要用IN，就像上面子句根据state是'CA'或者'NY'，本身就会得到多个结果，所以WHERE语句也需要用IN
删除行
DELETE FROM invoices
WHERE client_id =
(SELECT client_id
FROM clients
WHERE name = 'Myworks')
聚合函数
汇总数据
MAX：最大值SUM：最小值MIN：平均值COUNT：总行数AVG：累加和
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(*) AS total_records
FROM invoices
-- 运行上述语句得到整个表中invoice_total字段的最大值，最小值，平均值，累加和，总行数，AS用于定义输出后重命名的列
-- 上述的聚合函数用于计算非空值，如果有字段值为空，则不进行参与到计算中，如：如果有一行的invoice_total为空，则该行就不会计算入COUNT总行数中，当然如果需要计算所有表中所有行，则可以使用COUNT(*)
SELECT MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total*1.1) AS total,
COUNT(invoice_total) AS number_of_invoices,
COUNT(*)
FROM invoices
WHERE invoice_date > '2019-07-01'
-- 上述语句中只不过添加了WHERE限定条件，仅仅将表中invoice_date> '2019-07-01'的所有行进行计算
-- 当然上述聚合函数中的参数也可以使用表达式，如上述SUM(invoice_total*1.1)进行计算，会使用每行的invoice_total*1.1计算得到的结果相加得到总和
SELECT MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total*1.1) AS total,
COUNT(DISTINCT client_id) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01'
-- 上述语句中在计算总数的时候，使用了client_id，并且前面添加了DISTINCT，表示只计算不重复的client_id值，即如果有多行该字段值相同，则也仅仅为1个
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_get
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_get
FROM invoices
WHERE invoice_date
BETWEEN '2019-07-01' AND '2019-12-31'
-- 上述语句用于计算上半年和下半年的销售金额，成本，利润，并连接起来
GROUP BY子句
会根据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 client_id，将会根据client是相同的进行分组计算SUM，并根据total_sales从大到小的顺序进行排序
-- 还有一点需要注意的是：上述语句的顺序是标准的SQL语句，不能写错，GROUP BY永远放在FROM和WHERE子句之后，并在ORDER BY之前
HAVING子句
HAVING子句可以在结果数据中进行筛选数据。
SELECT
client_id,
SUM(invoice_total) AS total_sales,
COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 800 AND number_of_invoices > 5
-- HAVING用于对HAVING之前的语句执行的结果进行筛选，这里筛选total_sales > 800 并且number_of_invoices > 5的所有行，此外，还需要注意的是，这里HAVING后跟的判定条件一定是前面SELECT后选择的列，如这里HAVING后只能跟client_id、total_sales、number_of_invoices。
-- 相反，WHERE后面跟的条件可以是表中的任意字段，这一点和HAVING不同
rollup运算符
SELECT
client_id,
SUM(invoice_total) AS total_sales,
COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id WITH ROLLUP
-- WITH ROLLUP 用于计算SELECT列的每个可计算列的总和,结果如下所示，会在最后一行输出总和
| client | total_sales | number_of_invoices |
|---|---|---|
| 1 | 802.89 | 5 |
| 3 | 705.90 | 5 |
| 5 | 980.02 | 6 |
| null | 2488.81 | 16 |
编写复杂查询
子查询
WHERE unit_price > (
SELECT unit_price
FROM sql_store.products
WHERE product_id = 3);
-- 寻找所有大于product_id = 3价格的产品
| product_id | name | quantity_in_stock | unit_price |
|---|---|---|---|
| 2 | james | 49 | 4.65 |
| 4 | kobe | 90 | 4.53 |
IN运算符
USE sql_store;
SELECT *
FROM products
WHERE product_id NOT IN
(SELECT DISTINCT product_id
FROM order_items)
-- 在products表中找到没有被订阅过的产品，order_items表中一定是已经订阅过的产品，products表中是所有的产品，当子句返回的记录有多个时，需要用到IN
子查询VS连接
USE sql_store;
SELECT *
FROM customers
JOIN orders USING(customer_id)
JOIN order_items USING(order_id)
WHERE order_items.product_id = 3
ALL关键字
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
-- 上述两种写法效果是一样的，都是找到大于client_id = 3情况下的所有invoice_total
-- 第一种情况下，先找到client_id = 3时，invoice_total的最大值，然后运用到主查询中
-- 第二种直接提供了这样一个关键字，相当于直接大于ALL后面的所有值
ANY关键字
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
)
-- 这里从clients表中选出至少有两个客户
-- 两个实现效果相同，IN 和 = ANY的效果相同
相关子查询
USE sql_hr;
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id
)
-- 找到employees表中，所有薪水大于部门平均值的员工
EXISTS运算符
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
)
-- 使用EXISTS关键字，查找所有没被订购过的商品，等价于下面的子句，但是当子句返回的规模很大时，下面的这种方法效率就会很低
USE sql_store;
SELECT *
FROM products p
WHERE product_id NOT IN (
SELECT product_id
FROM order_items
)
SELECT子句中的查询
USE sql_invoicing;
SELECT
invoice_id,
invoice_total,
(SELECT AVG(invoice_total)
FROM invoices) AS invoice_avg,
invoice_total-(SELECT invoice_avg) AS difference
FROM invoices
-- 除了前面在WHERE语句中使用子句，在SELECT语句中也同样可以使用子句，如上述，计算所有行的invoice_total的平均值得到invoice_avg字段值，并利用invoice_avg字段值计算平均值与每个invoice_total的差异
FROM子句中的查询
USE sql_invoicing;
SELECT *
FROM(
SELECT
invoice_id,
invoice_total,
(SELECT AVG(invoice_total)
FROM invoices) AS invoice_avg,
invoice_total-(SELECT invoice_avg) AS difference
FROM invoices
) AS summary_sales
WHERE difference > 0
-- 上述在FROM子句中使用可SELECT子句进行查询，并在主查询中使用判断difference>0进行筛选
基本函数
数值函数
-
ROUND：四舍五入，用于小数位SELECT ROUND(5.23) -- 输出5，取出小数位 SELECT ROUND(5.37,1) -- 输出5.4，输出小数位1位，四舍五入 -
TRUNCAT：保留小数位几位SELECT TRUNCAT(5.23，0) -- 输出5，取出小数位 SELECT TRUNCAT(5.375,1) -- 输出5.3，输出小数位1位 -
CEILING：上限函数，大于等于该数字的最小整数SELECT CEILING(5.7) -- 返回6 -
FLOOR：返回小于等于该数字的最大整数SELECT FLOOR(6.2) -- 返回6 -
ABS：取绝对值SELECT FLOOR(-6.2) -- 返回6.2 -
RAND：随机数，输出0~1之间的随机数SELECT RAND()
字符串函数
LENGTH:用于计算字符串的长度
SELECT LENGTH('FJDSK')
-- 输出5
UPPER:全部转换成大写
SELECT LOWER('FhDSK')
-- 输出FHDSK
LOWER：全部转换成小写
SELECT LOWER('FhDSK')
-- 输出fhdsk
rtrim或者ltrim和trim：删掉字符串的右边空格，删掉字符串的左侧空格，删掉字符串左侧和右侧的空格
SELECT rtrim('FhDSK ')
-- 输出FhDSK
SELECT ltrim(' FhDSK')
-- 输出FhDSK
SELECT trim(' FhDSK ')
-- 输出FhDSK
LEFT和RIGHT：获取字符串的前几个字符，获取字符串的后几个字符
SELECT LEFT('kingdergarden',4)
-- 输出king
SELECT RIGHT('kingdergarden',6)
-- 输出garden
SUBSTRING：从字符串中指定位置截取一定长度的字符串
SELECT substring('kingdergarden',6,2)
-- 输出er,从第六个字符开始计算，截取2个字符构成字符串，此外，第三个长度信息是可选的，如果不填，则默认一直到字符串结束
LOCATE：找到字符或者字符串在字符串中第一次出现的位置
SELECT LOCATE('d','kingdergarden')
-- 输出5
SELECT LOCATE('er','kingdergarden')
-- 输出6
REPLACE：替换字符串中的某一段字符串
SELECT REPLACE('kingdergarden','den','demd')
-- 输出kingdergardemd,使用demd替换kingdergarden中的den
CONCAT：串联字符串
SELECT CONCAT('FIRST','SECOND')
-- 输出FIRSTSECOND，将两个字符串连接起来，构成一个字符串
MYSQL中的日期函数
SELECT NOW(),CURDATE(),CURTIME()
-- 输出当前年月日时钟，年月日，当前时钟，如下所示
'2021-01-24 21:16:00', '2021-01-24', '21:16:00'
SELECT YEAR(NOW()),MONTH(NOW())
-- 输出2021,1,如下所示
'2021', '1'
SELECT NOW(),YEAR(NOW()),MONTH(NOW()),DAY(NOW()),
HOUR(NOW()),
MINUTE(NOW()),
SECOND(NOW()),
DAYNAME(NOW()),
MONTHNAME(NOW())
-- 输出下面结果
'2021-01-24 21:24:03', '2021', '1', '24', '21', '24', '3', 'Sunday', 'January'
格式化日期和时间
SELECT DATE_FORMAT(NOW(),'%M %D %Y')
-- 输出下面结果
'January 24th 2021'
SELECT TIME_FORMAT(NOW(),'%H:%I %p')
-- 输出下面结果
'21:09 PM'
计算日期和时间
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY)
-- 输出结果,明天的同一时间,递增一天
'2021-02-01 10:16:23'
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR)
-- 输出结果,明年的同一时间,递增一年
'2022-01-31 10:18:50'
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR)
-- 输出结果,去年的同一时间,递减一年
'2020-01-31 10:19:12'
SELECT DATEDIFF('2020-01-08','2020-01-15')
-- 输出结果,比较两个时间的时间差,第一个参数减去第二个参数,也可能是正数
'-7'
SELECT TIME_TO_SEC('09:00')-TIME_TO_SEC('10:00')
-- 输出结果,比较两个具体时间点的差,并转换成具体秒
'-3600'
IFNULL和COALESCE
USE sql_store;
SELECT
order_id,
IFNULL(shipper_id,'...'),
COALESCE(shipper_id,comments,'Not assigned') AS shipper
FROM orders
-- 这里IFNULL用于判断,如果shipper_id为null,则用'...'代替,否则返回shipper_id
-- COALESCE是用于多个参数,如果shipper_id为null,则用comments代替,如果comments还为null,则用'Not assigned'代替
order_id | IFNULL(shipper_id,'...') | shipper
'1', '...', 'Not assigned'
'2', '4', '4'
'3', '...', 'Not assigned'
'4', '...', 'Not assigned'
'5', '3', '3'
'6', '...', 'Aliquam erat volutpat. In congue.'
'7', '4', '4'
'8', '...', 'Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.'
'9', '1', '1'
'10', '2', '2'
IF函数
USE sql_store;
SELECT
order_id,
order_date ,
IF(YEAR(order_date) = YEAR(NOW()),
'active',
'archived') AS category
FROM orders
-- 使用IF函数进行计算,第一个参数为表达式,第二项为表达式为真的情况下返回的值,第三项为表达式为假的情况下返回的值.这里判断当条件表达式YEAR(order_date) = YEAR(NOW())为真的情况下，则返回第一项active，当表达式为假的情况下，则返回结果为第二项archived
CASE运算符
USE sql_store;
SELECT
order_id,
order_date,
CASE
WHEN YEAR(order_date) = YEAR(NOW()-2) THEN 'active'
WHEN YEAR(order_date) = YEAR(NOW()) -3 THEN 'last year'
WHEN YEAR(order_date) < YEAR(NOW()) -3 THEN 'archived'
ELSE 'future'
END AS category
FROM orders
-- 使用case表达式进行判断,case表达式内部使用WHEN... THEN...进行判断和返回,WHEN后面跟的是判断表达式,THEN后面跟的是返回值.ELSE是除了上面情况之外的情形,直接返回future
'1', '2019-01-30', 'future'
'2', '2018-08-02', 'last year'
'3', '2017-12-01', 'archived'
'4', '2017-01-22', 'archived'
'5', '2017-08-25', 'archived'
'6', '2018-11-18', 'last year'
'7', '2018-09-22', 'last year'
'8', '2018-06-08', 'last year'
'9', '2017-07-05', 'archived'
'10', '2018-04-22', 'last year'
视图
创建视图
视图不存储数据,视图的数据存储在表中,视图相当于一张虚拟表
USE sql_invoicing;
CREATE VIEW sales_by_client AS
SELECT
c.client_id,
c.name,
SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name
-- 创建视图,CREATE VIEW sales_by_client AS是创建视图语句,其中sales_by_client是视图名称
-- 下面的select语句,是从表中选择对应的数据
-- 这两步结合相当于从表中获取数据,然后通过视图的形式进行显示
-- 如下所示同样是构建视图的例子
USE sql_invoicing;
CREATE VIEW clients_balance AS
SELECT
c.client_id,
c.name,
SUM(invoice_total-payment_total) AS balance
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id,name
更改或者删除视图
删除视图
DROP VIEW sales_by_client
USE sql_invoicing;
CREATE OR REPLACE VIEW clients_balance AS
SELECT
c.client_id,
c.name,
SUM(invoice_total-pasales_by_clientyment_total) AS balance
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id,name
-- 创建视图一般使用CREATE OR REPLACE VIEW clients_balance AS语句,创建或者代替的意思,如果已经创建过了,则代替,如果没有创建过则直接创建
可更新视图
在构建视图的查询语句中如果没有用到DISTINCT,GROUP BY,聚合函数,UNION运算符,则该视图是一个可更新视图,可以使用该视图进行修改数据,否则的话视图的数据是不能进行更新的
CREATE OR REPLACE VIEW invoice_with_balance AS
SELECT
invoice_id,
number,
client_id,
invoice_total,
payment_total,
invoice_total-payment_total AS balance,
invoice_date,
due_date,
payment_date
FROM invoices
WHERE (invoice_total-payment_total) > 0
-- 可更新视图,通过上面的过程可以创建可更新的视图,因为没有使用DISTINCT,GROUP BY,聚合函数,UNION运算符
-- 所以可以对该视图进行更新,如下面所示对该视图进行更新
UPDATE invoice_with_balance
SET due_date = DATA_ADD(due_date,INTERVAL 2 DAY)
WHERE invoice = 2
WITH CHECK OPTION子句
在一些情况下,通过对视图进行更新或者删除操作,可能会导致视图中的行会消失,这时候如果不想让行从视图中消失,则在创建视图时的选择语句最后添加WITH CHECK OPTION
CREATE OR REPLACE VIEW invoice_with_balance AS
SELECT
invoice_id,
number,
client_id,
invoice_total,
payment_total,
invoice_total-payment_total AS balance,
invoice_date,
due_date,
payment_date
FROM invoices
WHERE (invoice_total-payment_total) > 0
WITH CHECK OPTION
-- 通过上面添加WITH CHECK OPTION字句后,就会防止用于用户在更新或者删除使得行被消失,因为这样会进行检测,检测到行会被删除会报错
视图的其它优点
- 数据库可以减少数据库设计改动的影响,视图为我们的数据库表提供了一种抽象化,这种抽象化减少了变动带来的影响
- 我们可以使用视图限制基础表访问
- 总体上来说,视图的最大好处就是可以简化查询
存储过程
什么是存储过程
存储过程是一个包含一堆SQL代码的数据库抽象,在我们的代码里,我们调用这些过程来获取或者保存数据
- 使用存储过程来存储和管理SQL代码
- 在存储过程里的SQL代码有时候执行速度更快
- 此外,和视图一样,存储过程能加强数据安全性
创建存储过程
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
SELECT * FROM clients;
END$$
DELIMITER ;
-- 创建存储过程,通过上面语句可以创建存储过程
CALL get_clients()
-- 调用存储过程,通过上面的执行可以调用存储过程
使用工作台创建存储过程
直接在工作台创建数据库
删除存储过程
DROP PROCEDURE IF EXISTS get_clients
-- 删除存储过程
参数
DROP PROCEDURE IF EXISTS get_clients_by_state;
DELIMITER $$
-- get_clients_by_state(state CHAR(2)),就是有参存储的表示方式,state是输入的列,CHAR(2)是2个字符的意思
CREATE PROCEDURE get_clients_by_state
(
state CHAR(2)
)
BEGIN
SELECT * FROM clients c
WHERE c.state = state;
END $$
DELIMITER ;
-- 创建带有参数的存储过程,相当于创建有参的函数
CALL get_clients_by_state('CA')
-- 调用存储过程,得到下述结果
'3', 'Yadel', '096 Pawling Parkway', 'San Francisco', 'CA', '415-144-6037'
带默认值的参数
DROP PROCEDURE IF EXISTS get_clients_by_state;
DELIMITER $$
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 ;
-- 当state为NULL时,则默认设置state = 'CA',相当于给state一个默认值
CALL get_clients_by_state('NULL')
-- 当输入参数值是NULL时,会得到所有的数据,当传入的不是NULL时,会得到所选state的数据
DROP PROCEDURE IF EXISTS get_clients_by_state;
DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
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 ;
-- 等价于,上下两个的意思是等价的,下面更加简洁
DROP PROCEDURE IF EXISTS get_clients_by_state;
DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
state CHAR(2)
)
BEGIN
SELECT * FROM clients c
WHERE c.state = IFNULL(state,c.state);
END IF;
END $$
DELIMITER ;
-- IFNULL判断如果第一个参数state为NULL,则返回c.state,如果不为null,则直接用state赋值给c.state
参数验证
DROP PROCEDURE IF EXISTS make_payments
DELIMITER $$
CREATE PROCEDURE make_payments
(
invoice_id INT,
payment_total DECIMAL(9,2),
payment_date DATE
)
BEGIN
IF payment_total <= 0 THEN
SIGNAL SQLSTATE '22003'
SET MESSAGE_TEXT = 'Invalid payment total';
END IF;
UPDATE invoices i
SET i.payment_total = payment_total,
i.payment_date = payment_date
WHERE i.invoice_id = invoice_id;
END $$
DELIMITER ;
-- 如上面所示,当输入参数payment_total <=0 时,通过抛出错误信息检测出输入参数的不正确性
输出参数
变量
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_total 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
-- 声明变量risk_factor,invoices_total,invoices_total,使用DECLARE符号进行声明
-- 后面INTO invoices_count,invoices_total语句将COUNT(*)结果赋给invoices_count,将SUM(invoice_total)结果赋给invoices_total
-- 然后下面SET语句就可以直接使用invoices_total和invoices_count计算得到risk_factor
函数
CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_for_client`(
client_id INT
)
RETURNS int(11)
READS SQL DATA
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 i
WHERE i.client_id = client_id;
SET risk_factor = invoices_total / invoices_count*5;
-- 如果为NULL,则直接返回0,不为NULL,则直接返回risk_factor
RETURN IFNULL(risk_factor,0);
END
-- 通过上面的语句创建函数get_risk_for_client,并且函数的输入参数为client_id,类型为INT类型
-- 此外还返回值,返回值为int(11),通过RETURNS int(11)进行了声明
-- 下面的例子是对创建的函数进行调用,通过下面的语句可以直接进行调用get_risk_for_client函数
SELECT
client_id,
name,
get_risk_for_client(client_id)
FROM clients
-- 通过上面的语句,返回下面的结果
'1', 'Vinte', '803'
'2', 'Myworks', '509'
'3', 'Yadel', '706'
'4', 'Kwideo', '0'
'5', 'Topiclounge', '817'
触发器
触发器和事件
触发器是在插入,更新和删除语句前后自动执行的一堆代码,通常使用触发器增强数据一致性.
USE sql_invoicing;
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 ;
-- 首先创建一个触发器
-- 然后设置AFTER INSERT ON,意思是当往payments表中进行数据插入后
-- 下面的BEGIN...END是,当上面异步进行完后,会触发BEGIN...END中的语句执行.
-- BEGIN...END,意思其实就是更新payment_total,每增加一行记录,则将该行的payment_total重新进行计算
-- NEW.amount和NEW.invoice_id表示新增行的字段值
-- 创建好后,通过执行下面的语句,会发现invoices表中invoice_id为3的payment_total变大了15
INSERT INTO payments
VALUES (DEFAULT,5,3,'2019-01-01',15,1
查看触发器
SHOW TRIGGERS
-- 可以显示所有创建好的触发器
删除触发器
DROP TRIGGER IF EXISTS payments_after_insert;
-- 删除触发器
使用触发器进行审计
USE sql_invoicing;
DELIMITER $$
DROP TRIGGER IF EXISTS payments_after_insert;
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;
INSERT INTO payments_audit
VALUES(NEW.client_id,NEW.date,NEW.amount,'insert',NOW());
-- 每次往payments表中插入数据后,不仅仅更新invoices表的payment_total字段值
-- 此外,还将新插入的数据记录到payments_audit表中,相当于一种审计过程
END $$
DELIMITER ;
-- 这里payments_audit表的创建语句如下:
USE sql_invoicing;
CREATE TABLE payments_audit
(
client_id INT NOT NULL,
date DATE NOT NULL,
amount DECIMAL(9,2) NOT NULL,
action_type VARCHAR(50) NOT NULL,
action_date DATETIME NOT NULL
)
-- 对payments表进行如下数据插入
INSERT INTO payments
VALUES (DEFAULT,5,3,'2019-01-01',25,1)
-- 则payments_audit表中新增如下一条记录:
'5', '2019-01-01', '25.00', 'insert', '2021-02-06 17:11:50'
事件
事件是根据计划执行的任务或者一堆SQL代码 ,通过事件,我们可以自动化数据库维护任务,比如删除已经过期数据或者将一张表复制到存档表或者汇总数据生成报告,所以事件非常有用.
DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
-- 事件的执行时间,每隔一年执行一次,并且开始时间为2019-01-01,结束时间为2029-01-01
DO BEGIN
DELETE FROM payment_audit
WHERE action_date < NOW() - INTERVAL 1 YEAR;
-- 内部为事件具体执行的内容,删除payment_audit一年前的记录
END $$
DELIMITER ;
查看,删除和更改事件
SHOW EVENTS LIKE 'yearly%'
-- 查看所有事件
DROP EVENTS IF EXISTS yearly_delete_stale_audit_rows;
-- 删除事件
DELIMITER $$
ALTER EVENT yearly_delete_stale_audit_rows
-- 调用ALERT EVENT.....更改事件
ON SCHEDULE
EVERY 2 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
-- 事件的执行时间,每隔2年执行一次,并且开始时间为2019-01-01,结束时间为2029-01-01
DO BEGIN
DELETE FROM payment_audit
WHERE action_date < NOW() - INTERVAL 1 YEAR;
-- 内部为事件具体执行的内容,删除payment_audit一年前的记录
END $$
DELIMITER ;
事务
事务是代表单个工作单元的一组SQL语句,所有这些语句都应成功,否则事务会运行失败.事务的属性:
- 原子性:事务就像原子一样,不可分割,每个事务都是一个工作单元,不管其包含多少语句,要么所有语句均被执行,且事务被提交,要么事务被退回去所有更改被撤销
- 一致性:通过使用事务,数据库将始终保持一致的状态
- 隔离性质:事务相互隔离,当有同样的数据被更改时各自受到保护,所以互相不会受到干扰.当有多个事务要更改一个行时,其中一个事务在更改时,其它事务需要在等待状态.
- 持久性:一旦事务被提交,事务产生的更改是永久的,当停电或者其它异常时,也不会丢失更改内容
创建事务
USE sql_store;
-- 开始事务
start transaction;
insert into orders (customer_id,order_date,status)
values(2,'2020-01-02',3);
insert into order_items
values(last_insert_id(),2,2,2);
-- 结束事务
commit;
并发和锁定
在现实情况下存在很多用户同时访问相同数据的情况,这就是并发
在多个事务针对同一个数据进行更改时,会加锁对数据进行保护,直到前一个事务执行完成,后一个事务才能对同样的数据进行修改.
-- 事务1
USE sql_store;
start transaction;
update customers
set points = points + 10
where customer_id = 1;
commit;
-- 事务2
USE sql_store;
start transaction;
update customers
set points = points + 10
where customer_id = 1;
commit;
并发问题
- 丢失更新
当两个事务尝试更新相同的数据并且没有上锁时,就会发生这样的情况,在这种情况下,较晚提交的事务会覆盖较早的事务的更改
-
脏数据:
-
不可重复读:
-
幻读:
事务隔离级别:
-
READ COMMITTED(读已提交):当对事务使用这个隔离级别时,那个事务只能读取已提交的数据,这样就避免了藏脏读
-
REPEATABLE READ(可重复读):这个级别上我们读取的数据是可重复和一致的,就算有其它事务更改了数据,我们会看到首次读取就创建的快照
-
SERIALIZABLE(序列化):它能保证当有别的事务在更新数据时,我们的事务能够知晓变动,如果有其它事务修改了可能影响查询结果的数据,我们的事务必须等他们完成,这样的事务就会按序列化执行,这是我们应用于事务的最高隔离级别
事务隔离级别
MySQL中默认的事务隔离级别是第三种,可重复读取隔离级别.,可通过下述语句查询:
SHOW variables LIKE 'transaction_isolation'
-- 输出结果
'transaction_isolation', 'REPEATABLE-READ'
设置隔离级别:
set session transaction isolation level serializable;
-- 只在当前窗口生效
set global transaction isolation level serializable;
-- 在所有窗口生效,是全局的
-- 上述语句用来设置隔离级别为序列化
读未提交隔离级别
-- 读取语句
USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customers_id = 1;
-- 更改语句
USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customers_id = 1;
COMMIT;
-- 当更改语句中在最后由于异常原因未提交时,第一条读取语句最终同样会读取到为20
读已提交隔离级别
-- 读取语句
USE sql_store;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customers_id = 1;
SELECT points FROM customers WHERE customers_id = 1;
COMMIT;
-- 更改语句
USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customers_id = 1;
COMMIT;
-- 当第一次更新数据为20并提交后,读取语句的第一条select读取的值为20,当第二次又更新数据为30并提交后,读取语句的第二条select读取的值为30,导致了在同一个事务内部读取到了不同的points值.
可重复读隔离级别(MySQL的默认隔离级别)
可重复读是指,事务不会读到其它事务对已有数据的修改,即使事务已提交.但是,对于其它事务新插入的数据是可以读到的,这就引发了幻读问题.
-- A读取语句
USE sql_store;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ REPEATABLE;
START TRANSACTION;
SELECT points FROM customers WHERE customers_id = 1;
SELECT points FROM customers WHERE customers_id = 1;
COMMIT;
-- B更改语句
USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customers_id = 1;
COMMIT;
-- 在这种情况下,当读物事务A读取语句中第一条select语句读取到的值是原值比如10,则此时执行更改语句, 并且提交, 则读取事务A中第二条select语句读取到的值仍然是10.
-- 读取语句
USE sql_store;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ REPEATABLE;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
COMMIT;
-- 更改语句
USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customers_id = 1;
COMMIT;
-- 在
序列化隔离级别
事务按照顺序依次执行的,可以真正消除并发问题
-- 事务A读取语句
USE sql_store;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
COMMIT;
-- 事务B更改语句
USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customers_id = 3;
COMMIT;
-- 当事务B在执行更新语句但还没提交时,此时如果执行事务A,则会被中止,因为事务B还没执行完,直到事务B执行完,事务A才能执行,并读取到了所有满足state = 'VA'的记录
死锁
死锁就是当不同事务均因抓住了别的事务需要的锁而无法完成的情况,所以两个事务一直在等待对方,并永远无法释放锁
-- 事务A更改语句
USE sql_store;
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customers_id = 3;
UPDATE orders SET status = 1 WHERE order_id = 3;
COMMIT;
-- 事务A更改语句
USE sql_store;
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 3;
UPDATE customers SET state = 'VA' WHERE customers_id = 3;
COMMIT;
-- 当事务A执行第一条update语句时,事务B也开始,并执行第一条update语句,紧接着,事务B如果继续执行第二条update语句由于customers_id = 3该条记录被事务A正在修改所以会被中止,然后此时,如果事务A要执行第二条update语句也会由于order_id = 3该条记录被事务B修改所以会被中止.这时候两个事务就产生了死锁现象
数据类型
数据类型的种类:
- 字符串类型
- 数值类型
- 日期时间类型
- 存储二进制数据的BLOB类型
- 存储几何或者地区值的空间类型
字符串类型
- CHAR(X):存储固定长度字符串
- VARCHAR(X):存储可变长度的字符串,max:65535 characters,
- MEDIUMTEXT:max 16MB,允许最多存储1600万个字符
- LONGTEXT:max:4GB
- TINYTEXT:max:255个字符
- TEXT:max:65000字符,和VARCHAR一样
所有字符占的字节数:英文1个字节,中欧2个字节,亚洲语言如中文和日文占3个字节
整数类型
- TINYINT:微整形,只占用一个字节,可以存储-128~127的数值
- UNSIGNED TINYINT:无符号微整形,可以存储0~255的数值
- SMALLINT:小整形,占用两个字节 [-32k,32k]
- MEDIUMLINT:中整形,占用3个字节 [-8M,8M]
- INT :整形,4个字节 [-2B,2B]
- BIGINT:大整数,占用8个字节,[-4B,4B]
定点数类型和浮点数类型
- DECIMAL(p,s): 参数p用于定义最多可以存储多少位数字,s用于定义小数点后最多可以定义多少位数字
- FLOAT:占4个字节
- DOUBLE:占8个字节
布尔型
- BOOL
- BOOLEAN
枚举类型和集合类型
- Enum类型
- SET集合类型
日期和时间类型
- DATE:存储一个没有时间成分的日期
- TIME:存储一个时间值
- DATETIME:日期时间型 8b
- TIMESTAMP:时间戳 4b(最大2038),只能存储2038年以前的日期
- YEAR:用来存储四位数年份
BLOB类型
用来存储大型二进制数据,如图像,视频,pdf,word文档,几乎包含了所有二进制数据
- TINYBLOB:最大能存储255b的二进制数据
- Blob用于存储最高达65kb的二进制数据
- MEDIUMBLOB:最高存储达16MB的二进制数据
- LONGBLOB:最高能存储达4GB的二进制数据
一般情况下,不建议将文件放到数据库中,如果将文件放入数据库中,则数据库大小会迅速增大
JSON类型
JSON类型基本上是一种通过网络存储和传输数据的轻量级格式,其在网络和移动应用中被大量应用,通常移动应用程序通过JSON将数据发送到后端
//JSON
{
"key1":value1,
"key2":value2,
}
//可以使用大括号定义JSON对象,key是字符串,后面的value可以是字符串,数字,布尔值,数组或者其它对象都行
UPDATE products
SET properties = '
{
"dimensions":[1,2,3],
"weight":10,
"manufacturer":{"name":"sony"}
}
'
WHERE product_id = 1;
-- 另一种方式,使用JSON函数定义JSON对象
UPDATE products
SET properties = JSON_OBJECT(
'weight',10,
'dimensions',JSON_ARRAY(1,2,3),
'manufacturer',JSON_OBJECT('name','sony')
)
WHERE product_id = 1;
-- 从JSON对象中获取属性值
SELECT product_id,properties-> '$.dimensions'
FROM products
WHERE product_id = 1;
-- 获取到[1,2,3],properties-> '$.dimensions[0]',获取到第一个:1
-- properties-> '$.dimensions':获取到10
-- 获取嵌套的JSON对象中的内容:
SELECT product_id,properties->> '$.dimensions.name'
FROM products
WHERE product_id = 1;
-- 获取到 sony
此外,还有很多的JSON函数,如JSON_SET,JSON_REMOVE
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
