MySQL常用基础知识

愤怒的阿斗

已于 2023-09-25 09:44:09 修改

阅读量93

点赞数 3

分类专栏： MySQL语法 MySQL存储过程 MySQL触发器文章标签： mysql 数据库学习

于 2023-09-13 16:43:03 首次发布

本文链接：https://blog.csdn.net/qq_35676686/article/details/132763540

版权

MySQL语法同时被 3 个专栏收录

2 篇文章 0 订阅

订阅专栏

MySQL存储过程

1 篇文章 0 订阅

订阅专栏

MySQL触发器

1 篇文章 0 订阅

订阅专栏

MySQL常用基础知识

一、库操作

创建数据库：CREATE DATABASE database_name;
连接到数据库：USE database_name;
创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, …);
删除表：DROP TABLE table_name;

二、关键字

1. 新增(INSERT)

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);

2. 修改(UPDATE)

UPDATE 表名 SET 列1=新值1, 列2=新值2, ... WHERE 条件;

3. 删除(DELETE)

DELETE FROM 表名 WHERE 条件;

4. 查询(SELECT)

SELECT 列1, 列2, ... FROM 表名 WHERE 条件;

5. 连表查询

左连接（LEFT JOIN）
左连接返回左表中的所有行，以及右表中满足连接条件的匹配行。如果右表中没有匹配的行，则返回 null值。

SELECT * FROM table1 
LEFT JOIN table2 ON table1.column = table2.column;

右连接（RIGHT JOIN）
右连接返回右表中的所有行，以及左表中满足连接条件的匹配行。如果左表中没有匹配的行，则返回 null值。

SELECT * FROM table1 
RIGHT JOIN table2 ON table1.column = table2.column;

内连接（INNER JOIN）
返回两个表中满足连接条件的匹配行，表table1和表table2的交集内容。

SELECT * FROM table1 
INNER JOIN table2 ON table1.column = table2.column;

全连接（Full Join）
全连接返回左表和右表中的所有行，如果某个表中没有匹配的行，则返回null值。

SELECT * FROM table1 
FULL JOIN table2 ON table1.column = table2.column;

6. 去重（DISTINCT）

对表中的单个字段或多个字段去重操作。

SELECT DISTINCT column1,column2 FROM table1;

7. 合并（UNION/UNION ALL）

用于组合两个或更多SELECT语句的结果集。
UNION前后表的列数量相同。
以UNION前的查询结果字段为字段名，UNION后的字段按顺序排列，而不是字段名称。
不考虑对应字段的数据类型。

UNION

对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。
union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

//UNION之后的查询结果与UNION之前的相同，会将重复的去重处理。
SELECT column FROM table
UNION
SELECT column FROM table;

UNION ALL

对两个结果集进行并集操作，包括重复行，不进行排序。

//查询的结果是table表column字段会出现两条相同数据。
SELECT column FROM table where id = 1
UNION ALL
SELECT column FROM table where id = 1;

8. 分组（GROUP BY）

对数据进行分组，即按照指定的字段或者表达式进行分组。
group by作用于 where 之后，order by 之前。需要配合函（sum、count、min、max、avg等）数进行使用。
先通过where过滤数据，再对过滤后的数据中进行分组，分组的依据就是字段 column的值，即不同的column字段值，分成不同的组。然后再根据column字段值进行排列。

//根据名字进行分组，取相同名字中的最大年龄
select name,max(age) 
from user 
group by name
order by create_time desc

9. 过滤（HAVING）

用于对聚合函数之后的数据进行过滤。一般配合分组函数group by一起使用。
WHERE在分组前执行，HAVING在分组后执行。

//根据name进行分组，取每组年龄最大的那条，然后再过滤选择10岁以上的。
SELECT name, max(age) as age
FROM table
where 1=1
GROUP BY name 
HAVING age > 10;

10. 条件转换(case when then)

对查询的结果根据条件进行处理。

//基础
CASE SCORE WHEN '1' THEN '优' ELSE '不及格' END
CASE SCORE WHEN '2' THEN '良' ELSE '不及格' END
CASE SCORE WHEN '3' THEN '中' ELSE '不及格' END

// 简写
CASE WHEN SCORE = '1' THEN '优'
     WHEN SCORE = '2' THEN '良'
     WHEN SCORE = '3' THEN '中' 
     ELSE '不及格' END

// 简写
CASE SCORE  
	WHEN '1' THEN '优'
    WHEN '2' THEN '良'
    WHEN '3' THEN '中' 
    ELSE '不及格' END

三、函数

1. 求和（sum()）

返回某列之和。

select sum(id) from user

2. 最小值（min()）

返回某列的最小。

select min(id) from user

3. 最大值（max()）

返回某列的最大值。

select max(id) from user

4. 统计（count()）

返回某列的行数。

select count(*) from user

5. 取余（mod()）

返回某列除以指定值的余。

select mod(id,3) from user

6. 平均值（avg()）

返回某列的平均值。

select avg(id) from user

7. null补值（IFNULL()）

字段中存在的null值，替换成指定内容
参数1：不为null，则返回该值；参数2：第一个参数如果为null,则返回改参数值。

select IFNULL(type,0) from user;

8. 截取（substring()）

截取字符串的指定内容。
参数1：需要截取的字符串；参数2：截取的起始位置；参数3：截取的长度，如果没有第三个参数则默认为截取到字符串的末尾，如果参数的值大于待截取的字符串长度，返回起始位置之后的内容。

select substring(name,2,3) from user

9. 左边截取（LEFT()）

从左往右截取指定内容的指定长度。参数1：需要截取的内容或字段；参数2：从左往右截取的长度。.
参数2大于字符串的总长度时，则返回全部字符串；参数2小于等于零时，返回空字符串。

//返回user表name字段从左往右3个长度的内容
SELECT LEFT(name,3) FROM user

10. 右边截取（RIGHT()）

从右往左截取指定内容的指定长度。参数1：需要截取的内容或字段；参数2：从右往左截取的长度。
参数2大于字符串的总长度时，则返回全部字符串；参数2小于等于零时，返回空字符串。

//返回user表name字段从右往左3个长度的内容
SELECT RIGHT(name,3) FROM user

11. 替换（REPLACE()）

三个参数。第一位：需要替换的内容或字段；第二位：指定需要替换的内容；第三位：替换成指定内容。

//将查询结果user表中所有name字段的‘张’替换为‘李’
select id,REPLACE(name,'张','李') as name 
from user

12. 连接条件（USING()）

连接查询时如果是字段名相同则可以作为连接条件，可以代替on出现。

select * 
from user 
left join students 
USING(user_id)
-- 上下两段SQL效果一样 
select * 
from user u
left join students s
on u.user_id = s.user_id

13. 是否返回值（EXISTS()）

用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false。

//查询与门店有关联的用户
SELECT u.id,u.name 
FROM user u
WHERE EXISTS(
	SELECT s.shop_id FROM shop s WHERE s.user_id = u.id
)

四、存储过程

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

1.优缺点

1.1 优点

SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改，保证安全性。
存储过程经过编译创建并保存在数据库中的，执行过程无需进行重复的编译操作，对SQL指令的执行过程进行了性能上的提升。
存储过程中多个指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务。

1.2 缺点

存储过程是根据不同的数据库执行编译、创建并存储在数据库中，当我们需要切换到其他的数据库产品时，需要重新编写对于新数据库的存储过程。
存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题。
在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间（因为我们将复杂的过程交给数据库进行处理）。

2. 实例

1.存储过程的创建与使用

存储过程创建

在MySQL中使用CREATE PROCEDURE语句创建存储过程。在这个语句中，需要指定存储过程的名称，并且还需要指定存储过程的参数列表。

//查询用户的数量
CREATE PROCEDURE manage_base.select_user()
BEGIN
select count(*) as number from manage_base.sys_user;
END;

mybatis调用存储过程

mybatis中，mapper.xml执行命令的标签中需要加上statementType="CALLABLE"进行申明。

 <select id="getCCGC" resultType="Integer" statementType="CALLABLE">
     {call manage_base.select_user()}
 </select>

2. 存储过程的参数

存储过程的参数有三种类型：in/out/inout

1. in（输入参数）

在调用存储过程中传递数据给存储过程的参数。
参数的类型以及类型长度需要和创建表字段的类型和类型长度一致。

CREATE PROCEDURE manage_base.select_user(in code int,in createBy VARCHAR(64))
BEGIN
select count(*) as number from manage_base.sys_user where sex = code and create_by = createBy;
END;

 <select id="getCCGC" resultType="String" statementType="CALLABLE">
     {call manage_base.select_user(#{sex},#{createBy})}
 </select>

2. out（输出参数）

将存储过程中产生的数据返回给调用者，相当于Java方法的返回值，但不同的是一个存储过程可以有多个输出参数。

CREATE PROCEDURE manage_base.get_user(in id int,out nickName VARCHAR(30))
BEGIN
select nick_name into nickName from manage_base.sys_user where user_id = id;
select count(*) from manage_base.sys_user where nick_name = nickName;
END;

<select id="getCCGCStr" statementType="CALLABLE" parameterType="Map" resultType="String">
	{CALL manage_base.get_user(#{id,jdbcType=INTEGER,mode=IN},#{nickName,jdbcType=VARCHAR,mode=OUT})}
</select>

inout（输入输出参数）

参数即作为输入参数，也作为输出参数。
该类型可读性差，不建议使用。

五、索引

索引是数据库管理系统中用于提高查询速度的一种数据结构。
在MySQL中，索引可以看作是一种特殊的表，其中包含了对数据表中特定列的值及其在数据表中的位置信息。
通过使用索引，MySQL可以在不需要扫描整个表的情况下快速找到与查询条件匹配的记录。

1. 优缺点

优点

提高查询速度： 索引可以显著提高数据库查询速度。
确保数据唯一性和引用完整性： 通过使用主键和外键索引，可以确保数据的唯一性和引用完整性。

缺点

增加存储空间和维护成本： 索引会占用额外的存储空间，并增加数据更新和维护的成本。
影响写操作性能： 由于在插入、更新和删除操作时需要维护索引，索引可能会降低写操作的性能。

使用注意事项

选择合适的索引类型： 根据查询需求选择合适的索引类型，如B-Tree索引、哈希索引、空间索引或全文索引。
为常用查询列创建索引： 根据实际查询需求，为常用查询条件和排序列创建索引。
避免过度索引： 过多的索引可能会降低写操作性能并占用额外的存储空间。在创建索引时，要权衡查询优化和空间、性能成本。

2. 创建索引

建表时创建索引
在创建表时，可以使用INDEX、UNIQUE INDEX、PRIMARY KEY或FOREIGN KEY关键字创建索引。

CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(255) UNIQUE,
	email VARCHAR(255) UNIQUE,
	age INT,
	INDEX (age)
);

在已有表上创建索引
可以使用CREATE INDEX或CREATE UNIQUE INDEX语句创建索引。

CREATE INDEX idx_age ON users (age);
CREATE UNIQUE INDEX idx_email ON users (email);

使用ALTER TABLE创建索引
可以使用ALTER TABLE语句添加或删除索引。

ALTER TABLE users ADD INDEX (age);
ALTER TABLE users DROP INDEX idx_age

3. 索引类型

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。

B-树索引 使表中的每一行都会在索引上有一个对应值。
哈希索引 可根据索引列对应的哈希值的方法获取表的记录行。
普通索引 允许在定义索引的列中插入重复值和空值。
唯一索引 可以避免数据出现重复。
主键索引 是为主键字段创建的索引。
空间索引 是对空间数据类型的字段建立的索引。
全文索引 用来查找文本中的关键字。
单列索引 即索引只包含原表的一个列。

3.1 存储方式区分

MySQL中常用的索引在物理上分为 B-树索引和 HASH 索引两类。
B-树索引

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。
B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。
B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

查询必须从索引的最左边的列开始。
查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。

HASH 索引

根据索引列对应的哈希值的方法获取表的记录行。
最大特点是访问速度快。
MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
不能使用 HASH 索引排序。
HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

3.2 逻辑区分

普通索引

MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。
创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

CREATE INDEX index_id ON users (id);

唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字

CREATE UNIQUE INDEX index_id ON users (id);

主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。

CREATE TABLE users (
     `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
     `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
     PRIMARY KEY (`id`)
);

空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。
空间索引主要用于地理空间数据类型geometry。

//school表的存储引擎必须是 MyISAM，coordinate字段必须为空间数据类型，而且是非空的
CREATE SPATIAL INDEX index_coordinate ON school(coordinate);

全文索引

创建全文索引使用 FULLTEXT 关键字。
全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

//index_info的存储引擎必须是MyISAM，info字段必须是CHAR、VARCHAR和TEXT等类型。
CREATE FULLTEXT INDEX index_info ON school(info);

3.3 实际使用区分

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

//查询时可以只查询name字段的第一个字符，而不查询全部（即根据姓名的姓氏进行索引）。
CREATE INDEX index_name ON users (name(1));

多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。
多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

//在表中的 id、name和sex字段上建立一个多列索引。那么，只有查询条件使用了id字段时，该索引才会被使用。
CREATE INDEX index_name ON users (id,name,sex);

4. 使用索引

在确定要使用的索引时，需要考虑到查询条件的顺序和索引的顺序是否一致。如果查询条件中的列顺序与索引的顺序不一致，可能会导致MySQL无法使用索引，从而降低查询效率。

//创建了一个age字段的名为index_age索引,将会按照gender列和age_index索引来查询数据
SELECT name, class FROM students WHERE gender = '女' AND age > 20;

mysql索引在不使用索引列进行查询、数据类型不匹配、前缀索引的使用不当、使用函数或表达式进行查询、索引列的顺序不正确、数据更新频繁和索引过多或过少情况下会失效。

1. 不使用索引列进行查询：如果查询中没有使用到任何索引列，MySQL将无法使用索引进行优化查询，这样查询的效率可能会较低。为了避免这种情况，应该在查询中使用适当的索引列。
2. 数据类型不匹配：如果查询中使用了索引列但数据类型不匹配，MySQL将无法有效使用索引。例如，将字符串类型的列与数值类型进行比较，或将日期类型的列与文本进行比较。在设计表结构时，应该确保索引列和查询的数据类型匹配。
3. 前缀索引的使用不当：在某些情况下，为了提高索引的效率和节省存储空间，可以使用前缀索引。然而，如果使用前缀索引的长度过短，那么查询的结果可能会不准确。此外，如果使用前缀索引的列进行排序或分组操作，也会导致索引失效。
4. 使用函数或表达式进行查询：如果在查询中使用了函数或表达式，MySQL将无法使用索引进行优化查询。例如，select * from table where year(date_column) = 2021; 在这种情况下，将无法使用date_column上的索引。
5. 索引列的顺序不正确：复合索引是指包含多个列的索引。如果查询的条件中的列的顺序与复合索引的列的顺序不一致，MySQL将无法使用索引进行优化查询。因此，在建立复合索引时应该根据查询条件中最常用的列进行排序。
6. 数据更新频繁：索引是为了提高查询性能而创建的，但在数据更新频繁的情况下，索引会导致插入、更新和删除操作的性能下降。因此，在设计表结构时需要权衡查询和更新的频率，并根据实际情况来确定是否创建索引。
7. 索引过多或过少：如果表中的索引过多，可能会导致查询性能下降。每个索引都需要额外的存储空间，并且在数据更新时需要维护索引的一致性。另一方面，如果表中没有足够的索引，某些查询可能会变得很慢。

六、触发器

触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。
触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行。

1. 作用

可在写入数据前，强制检验或者转换数据(保证护数据安全)。
触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

2. 创建触发器

基础语法

delimiter：自定义结束符号
create： trigger 触发器名字触发时间触发事件 on 表 for each row
begin：触发器内容主体，每行用分号结尾
end：自定义的结束符合delimiter

//trigger_name:触发器的名称
//trigger_time:触发器的执行时间，可以是BEFORE或AFTER
//trigger_event:触发器的事件，可以是INSERT、UPDATE或DELETE
//table_name:触发器所在的表名
//trigger_body:触发器的SQL语句
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_body;

创建实例
employee表上创建了一个BEFORE INSERT的触发器，当有新的员工加入时，自动将入职时间设置为当前时间

CREATE TRIGGER set_join_date BEFORE INSERT ON employee FOR EACH ROW
BEGIN
	SET NEW.join_date = NOW();
END;

BEFORE触发器

BEFORE触发器在执行INSERT、UPDATE或DELETE操作之前执行，可以在数据库中实现一些数据的预处理和验证。

//在staff表上创建了一个BEFORE UPDATE的触发器，当员工的工资大于10000时，将其工资设置为10000
CREATE TRIGGER limit_salary BEFORE UPDATE ON staff FOR EACH ROW
BEGIN
	IF NEW.salary > 10000 THEN
	SET NEW.salary = 10000;
	END IF;
END;

AFTER触发器

AFTER触发器在执行INSERT、UPDATE或DELETE操作之后执行，可以在数据库中实现一些数据的后处理和计算。

//在order表上创建了一个AFTER INSERT的触发器，当有新的订单加入时，自动更新产品的库存量
CREATE TRIGGER update_inventory AFTER INSERT ON order FOR EACH ROW
BEGIN
	UPDATE product SET inventory = inventory - NEW.number WHERE id = NEW.product_id;
END;

触发器的事件

在MySQL中，触发器可以在INSERT、UPDATE或DELETE事件上执行。INSERT事件在插入新的记录时触发，UPDATE事件在更新记录时触发，DELETE事件在删除记录时触发。触发器可以在多个事件上执行。

//staff表上创建了一个BEFORE INSERT或UPDATE的触发器，当员工的工资大于10000时，将其工资设置为10000
//可以使用IF语句来控制触发器的执行条件
CREATE TRIGGER limit_salary BEFORE INSERT OR UPDATE ON staff FOR EACH ROW
BEGIN
	IF NEW.salary > 10000 THEN
	SET NEW.salary = 10000;
	END IF;
END;

触发器的流程控制

在MySQL中，触发器可以使用流程控制语句来控制程序的执行流程。例如IF语句、CASE语句、WHILE语句、LOOP语句等。

//在staff表上创建了一个BEFORE INSERT的触发器，当员工的工资小于10000时，将其入职时间设置为当前时间，否则将其入职时间设置为一个月前的时间
CREATE TRIGGER set_join_date BEFORE INSERT ON staff FOR EACH ROW
BEGIN
	IF NEW.salary < 10000 THEN
		SET NEW.join_date = NOW();
	ELSE
		SET NEW.join_date = DATE_SUB(NOW(), INTERVAL 1 MONTH);
	END IF;
END;