版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_43965708/article/details/120037528
文章目录1. 查询1.1 查询多列1.2 检索唯一值1.3 限制输出1.4 注释2. ORDER BY 排序2.1 单列排序3.2 多列排序3.3 指定排序方向4. WHERE 过滤数据4.1 WHERE 子句操作符4.2 范围值检查4.3 空值检查5. 高级数据过滤5.1 WHERE 组合过滤5.2 IN 操作符5.3 NOT 操作符6. 通配符过滤6.1 % 通配符6.2 _ 通配符7. 创建计算字段7.1 计算字段7.2 拼接字段7.3 执行算数计算8. 函数8.1 常用文本处理函数8.2 日期和时间
mysql> SHOW DATABASES;
CREATE DATABASE 数据库名 CHARSET='编码格式'
mysql> CREATE DATABASE create_test CHARSET = ‘utf8’;
mysql> USE create_test;
使用 SELECT DATABASE()
查看当前使用的数据库。
mysql> SELECT DATABASE();
±------------+
| DATABASE() |
±------------+
| create_test |
±------------+
mysql> DROP DATABASE create_test;
格式:
CREATE TABLE [IF NOT EXISTS] `表名` (
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
…
`字段名` 列类型 [属性] [索引] [注释]
) [表类型] [字符集设置] [注释]
使用下面的语句创建示例中的 one_piece
表。
mysql> CREATE TABLE one_piece
-> (
-> id CHAR(10) NOT NULL COMMENT ‘海贼团id’,
-> pirates CHAR(10) NOT NULL COMMENT ‘海贼团名称’,
-> name CHAR(10) NOT NULL COMMENT ‘海贼名’,
-> age INT(11) NOT NULL COMMENT ‘海贼年龄’,
-> post VARCHAR(10) NULL COMMENT ‘海贼团职位’
-> );
注意:创建表时,指定的表名必须不存在,否则会出错。
在刚才创建的 one_piece
表中添加一列 bounty
(赏金)。
mysql> ALTER TABLE one_piece
-> ADD bounty INT(15);
删除 bounty
列。
mysql> ALTER TABLE one_piece
-> DROP COLUMN bounty;
mysql> DESC one_piece;
±--------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------±------------±-----±----±--------±------+
| id | char(10) | NO | | NULL | |
| pirates | char(10) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| post | varchar(10) | YES | | NULL | |
±--------±------------±-----±----±--------±------+
\G
后面不能加“ ; ”
。
mysql> SHOW TABLE STATUS LIKE ‘one_piece’ \G
*************************** 1. row ***************************
Name: one_piece
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-09-03 17:53:58
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.39 sec)
两种方法:
ALTER TABLE 表名 RENAME [TO | AS] 新表名;
RENAME TABLE 表名 TO 新表名;
用方法一将 Products
表更名为 new_Products
,再用方法二改回来。
– 方法一
mysql> ALTER TABLE one_piece RENAME TO new_one_piece;
– 方法二
mysql> RENAME TABLE new_one_piece TO one_piece;
DROP TABLE 表名
mysql> DROP TABLE one_piece;
注意:在该表与其他表有关联时,Mysql
会阻止该表的删除。
同时输出 name
, age
列。
mysql> SELECT name, age
-> FROM one_piece;
使用 DISTINCT
关键字,查询字段 age
的唯一值。
mysql> SELECT DISTINCT age
-> FROM one_piece;
在 Mysql
中使用 LIMIT
关键字限制输出的数据。LIMIT
有两种常见用法:
SELECT * FROM table LIMIT [offset], rows – LIMIT 单独使用
SELECT * FROM table LIMIT rows OFFSET [offset] – 配合 OFFSET 使用
offset:行开始的行的索引。0 表示从第 1 行 开始显示(包括第 1 行),以此类推。
rows:数据显示的条数。
示例:
SELECT * FROM one_piece LIMIT 5; – 检索前5条数据
–相当于
SELECT * from one_piece LIMIT 0,5; – 从第0行开始检索5条数据
–相当于
SELECT * FROM one_piece LIMIT 5 OFFSET 0; – 从第0行开始检索5条数据,注意这里的LIMIT的5指代的是数量
注:如果表中数据不足,即 LIMIT 设定的数过大,则只会检索到最后一行。
三种注释方式
– 单行注释
/* 多行注释 */
使用 ORDER BY
子句。 ORDER BY
子句取一个或多个列的名字,据此对输出进行排序(默认升序)。
mysql> SELECT name, age
-> FROM one_piece
-> ORDER BY age;
** 注意:** 在指定一条 ORDER BY
子句时,应该保证它是 SELECT
语句中最后一条子句。
mysql> SELECT A, B
-> FROM test
-> ORDER BY A, B;
在按多列排序时,仅在多个行具有相同的 A
值时 才按 B
进行排序。如果 A
列中所有的值都是 唯一的,则不会按 B
排序。
ORDER
默认升序(从 A 到 Z)排序。指定 DESC
关键字进行降序(从 Z 到 A)排序。
mysql> SELECT age
-> FROM one_piece
-> ORDER BY age DESC;
多列指定排序方向时,要使用逗号分隔。
mysql> SELECT name, age
-> FROM one_piece
-> ORDER BY name DESC, age;
操 作 符 | 说明 | 操作符 | 说明 |
---|---|---|---|
= | 等于 | > | 大于 |
<>、!= | 不等于 | >= | 大于等于 |
< | 小于 | !> | 不大于 |
<= | 小于等于 | BETWEEN | 在两值之间 (包含边界) |
!< | 不小于 | IS NULL | 是 NULL 值 |
使用 WHERE
关键字和 BETWEEN AND
进行范围值检查(前闭后闭)。
mysql> SELECT age
-> FROM one_piece
-> WHERE A BETWEEN 5 AND 10;
查询 字段 age 中 >=5
并且 <= 10
的数据。
使用 WHERE
关键字和 IS NULL
进行范围值检查。如果没有 NULL
值就不返回数据。
mysql> SELECT name
-> FROM one_piece
-> WHERE name IS NULL;
使用 AND
、OR
操作符给 WHERE
子句添加附加条件。 AND
的优先级比 OR
要高,优先级高低 ()
、 AND
、 OR
。在使用的过程中要注意各个优先级的影响。
mysql> SELECT name, age
-> FROM one_piece
-> WHERE(name = ‘索隆’ OR name = ‘路飞’)
-> AND age >= 18;
IN
操作符用来指定条件范围,范围中的每个条件都可以进行匹配。(与 OR
的功能相同,但速度比 IN
慢)
mysql> SELECT name, age
-> FROM one_piece
-> WHERE name IN (‘索隆’, ‘路飞’)
WHERE
子句中的 NOT
操作符有且只有一个功能,那就是否定其后所跟的任何条件。
mysql> SELECT name
-> FROM one_piece
-> WHERE name NOT IN (‘索隆’, ‘路飞’)
通配符搜索只能用于文本字段(字符串),非文本数据类型字段不能使用 通配符搜索。
在使用通配符过滤之前要先了解 LIKE
, LIKE
操作符用于在 WHERE
子句中搜索列中的指定模式或取值。
%
表示任何字符出现任意次数。例如,为了找出所有以 路
开始的 name
。
mysql> SELECT name, age
-> FROM one_piece
-> WHERE name LIKE ‘路’;
通配符 _
的用途与 %
一样也是匹配任意字符,但它只匹配单个字符,而不是多个字符。
mysql> SELECT name, age
-> FROM one_piece
-> WHERE name LIKE ‘乌_普’;
下面将 name
, sex
两列进行合并。并通过 AS
关键字进行给新列赋予别名。
mysql> SELECT Concat(name, ‘(’, sex, ‘)’) AS new_column
-> FROM one_piece;
通过 quantity
(数量)、 price
(价格)来计算 total_price
(总价)
mysql> SELECT quantity, price,
-> quantity * price AS total_price
-> FROM test
函数 | 说明 |
---|---|
LEFT(str, length) | 返回指定长度的字符串的左边部分 |
RIGHT(str, length) | 返回指定长度的字符串右边部分 |
LTRIM(str) | 去掉字符串左边的空格 |
RTRIM(str) | 去掉字符串右边的空格 |
LOWER(str) | 将字符串转换为小写 |
UPPER(str) | 将字符串转换为大写 |
LENGTH(str) | 返回字符串的长度 |
使用 LENGTH(str)
获取字符串的长度。
mysql> SELECT name, LENGTH(name) AS length
-> FROM one_piece;
查询在 2000 年 出生的人员信息。
mysql> SELECT *
-> FROM test
-> WHERE YEAR(brithday)=2000;
函数 | 说明 |
---|---|
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
SIN() | 返回一个角度的正弦 |
TAN() | 返回一个角度的正切 |
PI() | 返回圆周率 |
EXP() | 返回一个数的指数值 |
SQRT() | 返回一个数的平方根 |
以 ABS() 函数为例
sql> SELECT ABS(-1);
±--------+
| ABS(-1) |
±--------+
| 1 |
±--------+
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
查询平均 age 。
mysql> SELECT AVG(age) AS avg_age
-> FROM one_piece
两种使用方式:
COUNT(*)
对表中行的数目进行计数,包括空值。mysql> SELECT COUNT(*) AS num_person
-> FROM one_piece;
COUNT(column)
对特定列中非 NULL
行进行计数。mysql> SELECT COUNT(name) AS num_name
-> FROM one_piece;
当 column
列为数值列, MAX(column) / MIN(column)
返回 column
列中的最大值 / 最小值。
当 column
列为文本数据, MAX(column) / MIN(column)
返回 column
列数据排序后的最后一行 / 最前面的行。
SUM()
用来返回指定列值的和(总计)(忽略列值为 NULL
的行)。
mysql> SELECT SUM(price * quantity) AS total_price
-> FROM test
计算 one_piece
表中数据的条数,年龄的最小值、最大值和平均值。
mysql> SELECT COUNT(*) AS num_person,
-> MIN(age) AS age_min,
-> MAX(age) AS age_max,
-> AVG(age) AS age_avg
-> FROM one_piece;
使用分组将数据分为多个逻辑组, 对每个组进行聚集计算。
例:统计各个海贼团 ( pirates
) 的人数。
mysql> SELECT pirates, COUNT(*) AS num_person
-> FROM one_piece
-> GROUP BY pirates;
group by
注意事项:
GROUP BY
可以嵌套使用。GROUP BY
子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT
中使用表达式,则必须在 GROUP BY
子句中指定相同的表达式。不能使用别名。SELECT
语句中的每一列都必须在 GROUP BY
子句 中给出。NULL
值的行,则 NULL
将作为一个分组返回。 如果列中有多行 NULL
值,它们将分为一组。GROUP BY
子句必须出现在 WHERE
子句之后,ORDER BY
子句之前。使用 HAVING
子句在数据分组后进行过滤。
查询海贼团人数在 500 人以上的 海贼团名称 及 人数。
mysql> SELECT pirates, COUNT(*) AS num_person
-> FROM one_piece
-> GROUP BY pirates
-> HAVING COUNT(*) >= 500;
WHERE
与 HAVING
的主要区别:
SELECT 子句顺序:
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
现在查询 草帽海贼团 的排名信息。
mysql> SELECT rank
-> FROM rank_info
-> WHERE id IN (SELECT id
-> FROM one_piece
-> WHERE pirates = ‘草帽海贼团’);
注意:
SELECT
语句中,子查询总是从内向外处理。SELECT
语句只能查询单个列。检索多个列会报错。查询海贼团排名和任务信息,首先从 one_piece
表中根据 id
检索出排名信息,再统计每个冒险团的人数。
mysql> SELECT rank,
-> (SELECT COUNT(*)
-> FROM one_piece AS oe
-> WHERE oe.id = ro.id) AS num_person
-> FROM rank_info AS ro
-> ORDER BY rank;
注意:上面的例子中使用的是 oe.id
和 ro.id
,而不是直接使用 id
,因为在两个表中都有 id
列,在有可能混淆列名时必须使用这种语法。
假如现在有人不知道 乔巴 所属的海贼团, 想要知道 乔巴 所属海贼团的所有成员名称与赏金。
先看一下子查询的方式:
mysql> SELECT name, bounty
-> FROM one_piece
-> WHERE pirates = (SELECT pirates
-> FROM one_piece
-> WHERE name = ‘乔巴’);
接下来使用自联结的方式:
mysql> SELECT c1.name, c1.bounty
-> FROM Customers AS c1, Customers AS c2
-> WHERE c1.pirates = c2.pirates
-> AND c2.name = ‘乔巴’;
通常情况下,自联结的方式比子查询的方式要快很多。
联结是一种机制,用来在一条 SELECT
语句 中关联表,因此称为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。联结不是物理实体。换句话说,它在实际的数据库表 中并不存在。它只在查询执行期间存在。
两表 table1
, table2
中数据如下:
table1 table2
±-----±-----±-----+ ±-----±-----±-----+
| A | B | C | | C | D | E |
±-----±-----±-----+ ±-----±-----±-----+
| 1 | 2 | 3 | | 2 | 3 | 4 |
| 4 | 5 | 6 | | 6 | 7 | 8 |
±-----±-----±-----+ ±-----±-----±-----+
现在通过表联结,获取两个表中的数据。
mysql> SELECT *
-> FROM table1 AS t1, table2 AS t2
-> WHERE t1.C = t2.C;
±-----±-----±-----±-----±-----±-----+
| A | B | C | C | D | E |
±-----±-----±-----±-----±-----±-----+
| 4 | 5 | 6 | 6 | 7 | 8 |
±-----±-----±-----±-----±-----±-----+
注意:上例中WHERE
中限制了联结条件,如果没有条件的话,返回的结果就是两表的笛卡尔积,返回 6 × 9
共 54 条数据
上面的联结准确来说是等值联结,也可以称为内联结,它还有另一种语法。返回的结果以上面相同。
mysql> SELECT *
-> FROM table1 AS t1 INNER JOIN table2 AS t2
-> ON t1.C = t2.C;
±-----±-----±-----±-----±-----±-----+
| A | B | C | C | D | E |
±-----±-----±-----±-----±-----±-----+
| 4 | 5 | 6 | 6 | 7 | 8 |
±-----±-----±-----±-----±-----±-----+
一般内联结可以用如下图进行表示,取两个表关联字段相同的部分。
自然连接是一种特殊的等值连接,它在两个关系表中自动比较相同的属性列,无须添加连接条件,并且在结果中消除重复的属性列。
mysql> SELECT *
-> FROM table1 AS t1 NATURAL JOIN table2 t2;
±-----±-----±-----±-----±-----+
| C | A | B | D | E |
±-----±-----±-----±-----±-----+
| 6 | 4 | 5 | 7 | 8 |
±-----±-----±-----±-----±-----+
左外联结,左表 ( table1
) 的记录将会全部表示出来,而右表 ( table2
) 只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL
。
mysql> SELECT *
-> FROM table1 AS t1 LEFT JOIN table2 AS t2
-> ON t1.C = t2.C;
±-----±-----±-----±-----±-----±-----+
| A | B | C | C | D | E |
±-----±-----±-----±-----±-----±-----+
| 4 | 5 | 6 | 6 | 7 | 8 |
| 1 | 2 | 3 | NULL | NULL | NULL |
±-----±-----±-----±-----±-----±-----+
右外联结,右表 ( table2
) 的记录将会全部表示出来,而右左表 ( table1
) 只会显示符合搜索条件的记录。左表记录不足的地方均为 NULL
。
mysql> SELECT *
-> FROM table1 AS t1 RIGHT JOIN table2 AS t2
-> ON t1.C = t2.C;
±-----±-----±-----±-----±-----±-----+
| A | B | C | C | D | E |
±-----±-----±-----±-----±-----±-----+
| 4 | 5 | 6 | 6 | 7 | 8 |
| NULL | NULL | NULL | 2 | 3 | 4 |
±-----±-----±-----±-----±-----±-----+
使用 INSERT
插入完整行它要求指定 表名和插入到新行中的值。
mysql> INSERT INTO one_piece
-> VALUES(‘1’,
-> ‘草帽海贼团’,
-> ‘路飞’,
-> ‘age’,
-> ‘团长’,
-> ‘1500000000’);
注意:
INSERT
推荐的插入方法是明确给出表的列名。这样还可以省略列,即只给某些列提供值,给其他列不提供值。
省略的列必须满足以下某个条件:
该列定义为允许 NULL
值(无值或空值)。
在表定义中给出默认值(如果不给出值,将使用默认值)。
如果表中不允许有 NULL
值或者默认值,这时却省略了表中的值, DBMS
就会产生错误消息,相应的行不能成功插入。
现在同样在 one_piece
表中插入一行。
mysql> INSERT INTO one_piece(id,
-> pirates,
-> name)
-> VALUES(‘1’,
-> ‘草帽海贼团’,
-> ‘路飞’);
不管使用哪种 INSERT 语法,VALUES 的数目都必须正确。如果不提供列 名,则必须给每个表列提供一个值;如果提供列名,则必须给列出的每个列一个值。否则,就会产生一条错误消息,相应的行不能成功插入。
有一种数据插入不使用 INSERT
语句。要将一个表的内容复制到一个全新的表(运行中创建的表)。
mysql> CREATE TABLE one_pieceCopy AS
-> SELECT * FROM one_piece;
任何 SELECT
选项和子句都可以使用,包括 WHERE
和 GROUP BY
。
可利用联结从多个表插入数据。
不管从多少个表中检索数据,数据都只能插入到一个表中。
主要用途:它是试验新 SQL
语句前进行表复制的很好工具。先进行复制,可在复制的数据上测试 SQL
代码,而不会影响实际的数据。
使用 UPDATE
语句,更新(修改)表中的数据。
有两种使用 UPDATE
的方式:
使用时要明确是 更新特定行 还是 更新所有行。
UPDATE
语句中可以使用子查询,使得能用 SELECT
语句检索出的数据 更新列数据。
将 路飞
的赏金更新为 10000000000
mysql> UPDATE one_piece
-> SET bounty = 10000000000
-> WHERE name = ‘路飞’;
在更新多个列时,只需要使用一条 SET
命令,每个 “列 = 值” 对之间用逗号分隔(最后一列之后不用逗号)。
mysql> UPDATE one_piece
-> SET bounty = 10000000000,
-> age = ‘19’
-> WHERE name = ‘路飞’;
不使用 WHERE
限制条件,即更新表中所有行。
mysql> UPDATE one_piece
-> SET bounty = 10000000000,
-> age = ‘19’
假如表定义允许 NULL 值,要删除某个列的值,可设置它为 NULL。(要注意删除列值(保留列结构)和删除列(完全删除)的区别)
mysql> UPDATE one_piece
-> SET bounty = NULL
-> WHERE name = ‘路飞’;
使用 DELETE
语句,删除表中的数据。
有两种使用 DELETE
的方式:
使用时要明确是 删除特定行 还是 删除所有行。
删除 one_piece
表中 name
为 路飞
的行。
mysql> DELETE FROM one_piece
-> WHERE name = ‘路飞’;
删除 Customers
中的所有行。不删除表本身。
mysql> DELETE FROM one_piece;
如果想从表中删除所有行,推荐使用 TRUNCATE TABLE
语句,它完成相同的工作,而速度更快(因为不记录数据的变动)。
但要注意: TRUNCATE
属于数据定义语言( DDL
),且 TRUNCATE
命令执行后无法回滚,使用 TRUNCATE
命令之前最好对当前表中的数据做备份。
mysql> TRUNCATE TABLE one_piece;
优点:
简单来说,使用存储过程有三个主要的好处:简单、安全、高性能。
我们来看一个简单的存储过程例子,对 草帽海贼团 人数进行计数。
mysql> DELIMITER KaTeX parse error: Undefined control sequence: \* at position 77: … SELECT COUNT(\̲*̲) AS num\_perso…
在定义过程时,使用 DELIMITER $$
命令将语句的结束符号从分号 ;
临时改为 $$
,使得过程体中使用的分号被直接传递到服务器,而不会被 Mysql
解释。
使用 CALL 存储过程名(参数)
来调用存储过程。
mysql> DELIMITER ;
mysql> CALL personCount();
存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl 语句,if-then-else 和 while-do 语句、声明变量的 declare 语句等。
过程体格式:以 begin 开始,以 end 结束 (可嵌套)
BEGIN
BEGIN
BEGIN
statements;
END
END
END
注意:每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的 begin-end
块 (又叫做复合语句 compound statement
),则不需要分号。
存储过程可以有 0 个或多个参数,用于存储过程的定义。
3 种参数类型:
IN
(输入参数):表示调用者向过程传入值(传入值可以是字面量或变量)OUT
(输出参数):表示过程向调用者传出值 (可以返回多个值)(传出值只能是变量)INOUT
(输入输出参数):既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)mysql> DELIMITER m y s q l > C R E A T E P R O C E D U R E i n _ t e s t ( I N p _ i n I N T ) − > B E G I N − > S E L E C T p _ i n ; − − 第 一 次 查 询 − > S E T p _ i n = 2 ; − − 修 改 p _ i n 的 值 − > S E L E C T p _ i n ; − − 第 二 次 查 询 − > E N D mysql> CREATE PROCEDURE in\_test(IN p\_in INT) -> BEGIN -> SELECT p\_in; -- 第一次查询 -> SET p\_in = 2; -- 修改p\_in的值 -> SELECT p\_in; -- 第二次查询 -> END mysql>CREATEPROCEDUREin_test(INp_inINT)−>BEGIN−>SELECTp_in;−−第一次查询−>SETp_in=2;−−修改p_in的值−>SELECTp_in;−−第二次查询−>END
mysql> DELIMITER ;
mysql> SET @p_in = 1;
mysql> CALL in_test(@p_in);
±-----+
| p_in |
±-----+
| 1 |
±-----+
±-----+
| p_in |
±-----+
| 2 |
±-----+
mysql> SELECT @p_in;
±------+
| @p_in |
±------+
| 1 |
±------+
虽然 p_in
在存储过程中被修改,但并不影响 @p_in
的值,因为 in_test
只接受输入参数,并不输出参数,所以相当于在函数内改变,但并未将这个值输出给 @p_in
。
mysql> DELIMITER m y s q l > C R E A T E P R O C E D U R E o u t _ t e s t ( O U T p _ o u t I N T ) − > B E G I N − > S E L E C T p _ o u t ; − − 第 一 次 查 询 − > S E T p _ o u t = 2 ; − − 修 改 p _ o u t 的 值 − > S E L E C T p _ o u t ; − − 第 二 次 查 询 − > E N D mysql> CREATE PROCEDURE out\_test(OUT p\_out INT) -> BEGIN -> SELECT p\_out; -- 第一次查询 -> SET p\_out = 2; -- 修改p\_out的值 -> SELECT p\_out; -- 第二次查询 -> END mysql>CREATEPROCEDUREout_test(OUTp_outINT)−>BEGIN−>SELECTp_out;−−第一次查询−>SETp_out=2;−−修改p_out的值−>SELECTp_out;−−第二次查询−>END
mysql> DELIMITER ;
mysql> SET @p_out = 1;
mysql> CALL out_test(@p_out);
±------+
| p_out |
±------+
| NULL |
±------+
±------+
| p_out |
±------+
| 2 |
±------+
mysql> SELECT @p_out;
±-------+
| @p_out |
±-------+
| 2 |
±-------+
第一个返回结果为 NULL
是因为 OUT
是向调用者输出参数,不接收输入的参数,所以第一次查询时 p_out
还未赋值,所以是 NULL
。最后 @p_out
变量的值变为 2 是因为调用了 out_test
存储过程,输出参数,改变了 p_out
变量的值。
mysql> DELIMITER m y s q l > C R E A T E P R O C E D U R E i n o u t _ t e s t ( I N O U T p _ i n o u t I N T ) − > B E G I N − > S E L E C T p _ i n o u t ; − − 第 一 次 查 询 − > S E T p _ i n o u t = 2 ; − − 修 改 p _ i n o u t 的 值 − > S E L E C T p _ i n o u t ; − − 第 一 次 查 询 − > E N D mysql> CREATE PROCEDURE inout\_test(INOUT p\_inout INT) -> BEGIN -> SELECT p\_inout; -- 第一次查询 -> SET p\_inout = 2; -- 修改p\_inout的值 -> SELECT p\_inout; -- 第一次查询 -> END mysql>CREATEPROCEDUREinout_test(INOUTp_inoutINT)−>BEGIN−>SELECTp_inout;−−第一次查询−>SETp_inout=2;−−修改p_inout的值−>SELECTp_inout;−−第一次查询−>END
mysql> DELIMITER ;
mysql> SET @p_inout = 1;
mysql> CALL inout_test(@p_inout);
±--------+
| p_inout |
±--------+
| 1 |
±--------+
±--------+
| p_inout |
±--------+
| 2 |
±--------+
mysql> SELECT @p_inout;
±---------+
| @p_inout |
±---------+
| 2 |
±---------+
调用 inout_test
存储过程,既接受了输入的参数,也输出参数, @p_inout
的值被改变。
使用 DROP PROCEDURE
来删除存储过程。
mysql> DROP PROCEDURE in_test;
事务处理(transaction processing)是一种机制, 用来管理必须成批执行的
SQL` 操作,保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们要么完全执行,要么完全不执行(除非明确指示)。如果没有错误发生,整组语句提交给(写到)数据库表;如果发生错误,则进行回退(撤销),将数据库恢复到某个已知且安全的状态,以此来维护数据库的完整性。
事务处理术语:
SQL
语句;SQL
语句的过程;SQL
语句结果写入数据库表;事务处理用来管理 INSERT
、UPDATE
和 DELETE
语句。不能回退 SELECT
语句(回退 SELECT
语句也没有必要),也不能回退 CREATE
或 DROP
操 作。事务处理中可以使用这些语句,但进行回退时,这些操作也不撤销。
一般来说,事务是必须满足 4 个条件( ACID
)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。
transaction
)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚( Rollback
)到事务开始前的状态,就像这个事务从来没有执行过一样。Read uncommitted
)、读提交( read committed
)、可重复读( repeatable read
)和串行化( Serializable
)。在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
BEGIN / START TRANSACTION
:显式地开启一个事务;COMMIT / COMMIT WORK
:提交事务,使已对数据库进行的所有修改成为永久性的;ROLLBACK / ROLLBACK WORK
:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;SAVEPOINT
:SAVEPOINT
允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT
;RELEASE SAVEPOINT
:删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;ROLLBACK TO
:把事务回滚到标记点;SET TRANSACTION
:用来设置事务的隔离级别。InnoDB
存储引擎提供事务的隔离级别有 READ UNCOMMITTED
(读未提交)、READ COMMITTED
(读已提交,项目中常用)、REPEATABLE READ
(可重复读,Mysql
默认隔离级别) 和 SERIALIZABLE
(串行化)。简单示例:
mysql> use test;
mysql> CREATE TABLE transaction_test(id int(5)) ENGINE = INNODB; # 创建数据表
mysql> SELECT * FROM transaction_test;
Empty set (0.01 sec)
mysql> BEGIN; # 开始事务
mysql> INSERT INTO runoob_transaction_test VALUE(1);
mysql> INSERT INTO runoob_transaction_test VALUE(2);
mysql> COMMIT; # 提交事务
mysql> SELECT * FROM transaction_test;
±-----+
| id |
±-----+
| 1 |
| 2 |
±-----+
mysql> BEGIN; # 开始事务
mysql> INSERT INTO transaction_test VALUES(3);
mysql> SAVEPOINT first_insert; # 声明一个保存点
mysql> INSERT INTO transaction_test VALUES(4);
mysql> SAVEPOINT second_insert; # 声明一个保存点
mysql> INSERT INTO transaction_test VALUES(5);
mysql> ROLLBACK TO second_insert; # 回滚到 second_insert 保存点
mysql> SELECT * FROM transaction_test; # 因为回滚所以数据没有插入
±-----+
| id |
±-----+
| 1 |
| 2 |
| 3 |
| 4 |
±-----+
mysql> ROLLBACK TO first_insert;
mysql> SELECT * FROM transaction_test;
±-----+
| id |
±-----+
| 1 |
| 2 |
| 3 |
±-----+
mysql> COMMIT; # 执行 COMMIT 或 ROLLBACK 后保存点自动释放
先看一下如何查看事务隔离级别。
# 查看默认事务隔离级别(session)
select @@transaction_isolation;
select @@session.transaction_isolation;
select @@global.transaction_isolation;
在 Mysql
下事务的隔离级别有四种,由低到高依次为 Read uncommitted
、Read committed
、Repeatable read
(默认)、Serializable
,这四个级别中的后三个级别可以逐个解决脏读 、不可重复读 、幻读的问题。
对于两个事务 T1 与 T2,T1 读取了已经被 T2 更新但是还没有提交的字段之后,若此时 T2 回滚,T1 读取的内容就是临时并且无效的。
示例:
打开两个 Mysql 客户端,分别执行下面操作,查询当前会话的隔离级别(默认 REPEATABLE READ
)。修改当前会话隔离级别为( READ UNCOMMITTED
)。全局事务隔离级别仍然为 REPEATABLE READ
。
mysql> SELECT @@session.transaction_isolation;
±------------------------+
| @@transaction_isolation |
±------------------------+
| REPEATABLE-READ |
±------------------------+
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; # 修改会话隔离级别
mysql> SELECT @@session.transaction_isolation; # 当前会话隔离级别已修改
±--------------------------------+
| @@session.transaction_isolation |
±--------------------------------+
| READ-UNCOMMITTED |
±--------------------------------+
mysql> SELECT @@global.transaction_isolation; # 全局事务隔离级别未修改
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| REPEATABLE-READ |
±-------------------------------+
之后黑框用来做更新,白框用来查询。
由于黑框的 ④ 回滚,白色背景的客户端中 ③ 读取的数据就是临时并且无效的。即脏读。
对于两个事务 T1 和 T2,T1 读取了一个字段,然后 T2 更新了该字段并提交之后,当 T1 再次读取的时候,结果不一致的情况发生。
由于黑框的更新操作,白框出现两次读取的结果不一致。
对于两个事务 T1、T2,T1 从表中读取数据,然后 T2 进行了 INSERT 操作并提交,当 T1 再次读取的时候,结果不一致的情况发生。
由于黑框的插入操作,白框出现两次读取的结果不一致。
几种现象在各隔离级别中出现的可能性:
隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
未提交读(Read uncommitted) | 可能 | 可能 | 可能 |
已提交读(Read committed) | 不可能 | 可能 | 可能 |
可重复读(Repeatable read) | 不可能 | 不可能 | 可能 |
可串行化(Serializable ) | 不可能 | 不可能 | 不可能 |
SQL
检索操作返回结果集,简单地使用 SELECT
语句,没有办法得到第一行、下一行或前 10 行。有时,需要在检索出来的行中前进或后退一行或多行,这就是游标的用途所在。游标(cursor
)是一个存储在 DBMS
服务器上的数据库查询, 它不是一条 SELECT
语句,而是被该语句检索出来的结果集。在存储了 游标之后,应用程序可以根据需要滚动或浏览其中的数据。
游标使用的步骤:
SELECT
语句和游标选项。SELECT
语句把数据实际检索出来。声明游标后,可以根据需要频繁地 打开或关闭 游标。在游标打开时,可根据需要频繁地执行 取 操作。
注意:不像多数 DBMS,MySQL 游标只能用于存储过程(和函数)。
使用 DECLEAR
来创建游标,DECLARE
命名游标,并定义相应的 SELECT
语句,根据需要带 WHERE
和 其他子句。
下面的语句定义了名为 ordernumbers
的游标,使用了可以检索所有订单的 SELECT
语句。
Order 表中的信息:
DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
– 定义游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
这个存储过程中,使用 DECLARE
语句用来定义和命名游标。存储过程处理完成后,游标就消失(因为它局限于存储过程)。
使用 OPEN
语句来打开游标,CLOSE
语句关闭游标,在一个游标被打开后,可以使用 FETCH
语句分别访问它的每一行。FETCH
指定检索的数据(所需的列),数据存储的位置(定义的变量)。 它还向前移动游标中的内部行指针,使下一条 FETCH
语句检索下一行(不重复读取同一行)。
DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
– 定义局部变量
DECLARE num INT;
– 定义游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
– 打开游标
OPEN ordernumbers;
– 获取第一行数据
FETCH ordernumbers INTO num;
– 查询结果
SELECT num;
– 关闭游标
CLOSE ordernumbers;
END;
CALL processorder();
其中 FETCH
用来检索当前行的 order_num
列(将自动从第一行开始)到一个名为 num
的局部变量中,并将查询 num
的结果。由于只检索到第一行,所以 num
的值为 ‘20005’。
下面,循环检索数据,从第一行到最后一行。
DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
– 定义局部变量
DECLARE done BOOLEAN DEFAULT false;
DECLARE num INT;
– 定义游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
– 定义CONTINUE HANDLER
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=true;
– 打开游标
OPEN ordernumbers;
– 循环所有行
REPEAT
– 获取第一行数据
FETCH ordernumbers INTO num;
– 结束循环
UNTIL done END REPEAT;
– 查询结果
SELECT num;
– 关闭游标
CLOSE ordernumbers;
END;
CALL processorder();
循环了结果集的所有行,所以 num
的值是最后一行的数据。
与上一个例子不同之处是,这个例子的 FETCH
是在 REPEAT
内,因此它反复执行直到 done
为真。
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=true;
这条语句定义了一个 CONTINUE HANDLER
,它是在条件出现时被执行的代码。这里,它指出当 SQLSTATE '02000'
出现时,SET done=true
。SQLSTATE '02000'
是一个未找到条件,当 REPEAT
由于没有更多的行供循环而不能继续时,出现这个条件。
优点:游标是面向集合与面向行的设计思想之间的一种桥梁,因为游标是针对行操作的,所以对从数据库中 SELECT
查询得到的每一行可以进行分开的独立的相同或不同的操作,是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。
缺点:
DBMS
通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的,用 CREATE TABLE
或是 ALTER TABLE
语句。
主键是一种特殊的约束,用来保证一列(或 一组列)中的值是唯一的,而且永不改动。没有主键,要安全地 UPDATE
或 DELETE
特定行而不影响其他行会 非常困难。
主键的条件:
NULL
值)。创建表时定义主键。
CREATE TABLE teacher
(
id INT(11) PRIMARY KEY,
teacher_name VARCHAR(10)
);
使用 ALTER TABLE
添加主键。
ALTER TABLE teacher
ADD CONSTRAINT PRIMARY KEY(id);
删除主键约束。
ALTER TABLE teacher DROP PRIMARY KEY;
外键是表中的一列,其值必须列在另一表的主键中。外键是保证引用完 整性的极其重要部分。
下面新建 student
表并添加外键 teacher_id
与 teacher
表中的主键 id
进行关联。
在创建表的时定义外键。
CREATE TABLE student
(
stu_id INT(11) PRIMARY KEY,
teacher_id INT(11) REFERENCES teacher(id),
stu_name VARCHAR(10)
);
使用 ALTER TABLE
添加外键。
ALTER TABLE student
ADD CONSTRAINT teacher_id_id
FOREIGN KEY (teacher_id) REFERENCES teacher(id);
使用外键可以有效地防止意外删除,比如在上面两表中如果删除 teacher
表中的信息,如果该 id
在 student
表中也有出现,那么 Mysql
会防止删除操作。当然也可以启用级联删除的特性,那么在删除时就会删除所有相关信息。
删除外键。
ALTER TABLE student DROP FOREIGN KEY teacher_id_id;
唯一约束用来保证一列(或一组列)中的数据是唯一的。它们类似于主 键,但存在以下重要区别。
NULL
值。在创建表的时定义唯一约束。
CREATE TABLE student
(
stu_id INT(11) PRIMARY KEY,
teacher_id INT(11) REFERENCES teacher(id),
stu_name VARCHAR(10)
);
使用 ALTER TABLE
添加唯一约束。
ALTER TABLE student
ADD CONSTRAINT unique_id UNIQUE(stu_id);
删除唯一性约束。
ALTER TABLE student DROP INDEX unique_id;
检查约束用来保证一列(或一组列)中的数据满足一组指定的条件。
常见用途:
下面创建一个检查约束来限制性别列只能输入男、女。
在创建表的时定义检查约束。
CREATE TABLE student
(
stu_id INT(11) PRIMARY KEY,
gender VARCHAR(1) CHECK(gender IN(‘男’, ‘女’))
);
使用 ALTER TABLE
添加检查约束。
ALTER TABLE student ADD CONSTRAINT check_gender CHECK(gender in (‘男’, ‘女’));
删除检查约束。
ALTER TABLE student DROP CHECK check_gender;
索引用来排序数据以加快搜索和排序操作的速度。主键数据总是排序的, 因此,按主键检索特定行总是一种快速有效的操作。但是,搜索其他列中的值通常效率不高。这时候我们可以使用索引,在一个或多个列上定义索引,使 DBMS
保存其内容的一个排过序的列表。在定义了索引后,DBMS
以使用书的索引类似的方法使用它。DBMS
搜索排过序的索引,找出匹配的位置,然后检索这些行。
索引特点:
DBMS
必须动态地更新索引。在创建表的时创建普通索引。
DROP TABLE IF EXISTS student;
CREATE TABLE student
(
id INT(11),
stu_name VARCHAR(10)
)
直接创建。
CREATE INDEX stu_id ON student(id);
修改表时创建。
ALTER TABLE student ADD INDEX stu_id(id);
删除索引。
DROP INDEX stu_id ON student;
唯一索引列值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一。事实上,在许多场合,创建唯一索引的目的往往不是提高访问速度,而是为了避免数据出现重复。
CREATE UNIQUE INDEX stu_id ON student(id);
全文索引只能作用在 CHAR
、VARCHAR
、TEXT
、类型的字段上。创建全文索引需要使用 FULLTEXT
参数进行约束。
CREATE FULLTEXT INDEX s_name ON student(stu_name);
多列索引,即在数据表的多个字段上创建索引。
CREATE TABLE student
(
id INT(11),
stu_name VARCHAR(10),
email VARCHAR(20),
INDEX info(stu_name, email)
);
在多列索引中,只有查询条件中使用了这些字段中的第一个字段 (即上面示例中的 stu_name
字段),索引才会被使用(最左前缀’原则)。如果没有用到第一字段,则索引不起任何作用。
– 使用索引
SELECT * FROM student WHERE stu_name = ‘张三’;
SELECT * FROM student WHERE stu_name = ‘李四’ AND email = ‘11111@qq.com’;
– 未使用索引
SELECT * FROM student WHERE email = ‘11111@qq.com’;
**这就是本文所有的内容了,如果感觉还不错的话。**❤ 点个赞再走吧!!!❤
后续会继续分享各种干货,如果感兴趣的话可以点个关注不迷路哦~。
Source