MySQL数据库基本操作和数据库主键，外键，视图，触发器，事务等详细介绍

最新推荐文章于 2023-07-08 20:15:30 发布

青青草原我最穷

最新推荐文章于 2023-07-08 20:15:30 发布

阅读量681

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库 sql java

本文链接：https://blog.csdn.net/a1120467800/article/details/108475363

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、SQL通用语法

1.SQL 语句可以单行或多行书写，以分号结尾

2.可使用空格和缩进来增强语句的可读性

3.MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写

数据库的注释：
	单行注释：-- 注释内容       #注释内容(mysql特有)
	多行注释：/* 注释内容 */

二、SQL分类

1.DDL(Data Definition Language)数据定义语言
	用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
	
2.DML(Data Manipulation Language)数据操作语言
	用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
	
3.DQL(Data Query Language)数据查询语言
	用来查询数据库中表的记录(数据)。关键字：select, where 等
	
4.DCL(Data Control Language)数据控制语言(了解)
	用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

三、数据库的四种操作语言（DDL、DML、DQL、DCL）

1.DDL-数据库的查询，创建，修改，删除，使用

①R(Retrieve)：查询
	查询所有数据库
		-- 查询所有数据库 
		SHOW DATABASES;

	查询某个数据库的创建语句
		-- 查看某个数据库的创建格式
		SHOW CREATE DATABASE 数据库名称;

②C(Create)：创建
	创建数据库
		-- 标准语法
		CREATE DATABASE 数据库名称;
		-- 创建一个已存在的数据库会报错
		-- 错误代码：1007  Can't create database '数据库名称'; database exists

		创建数据库(判断，如果不存在则创建)  ***
			-- 标准语法
			CREATE DATABASE IF NOT EXISTS 数据库名称;

		创建数据库、并指定字符集
			-- 标准语法
			CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;

③U(Update)：修改
	修改数据库的字符集
		-- 标准语法
		ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

④D(Delete)：删除
	删除数据库
		-- 标准语法
		DROP DATABASE 数据库名称;
		-- 删除一个不存在的数据库会报错
		-- 错误代码：1008  Can't drop database '数据库名称'; database doesn't exist

	删除数据库(判断，如果存在则删除)
		-- 标准语法
		DROP DATABASE IF EXISTS 数据库名称;

⑤使用数据库
	查询当前正在使用的数据库名称
		-- 查询当前正在使用的数据库
		SELECT DATABASE();

	使用数据库
		-- 标准语法
		USE 数据库名称；

2.DDL-操作数据表-查询，创建，修改，删除

①R(Retrieve)：查询
	查询数据库中所有的数据表
		-- 查询库中所有的表 
		SHOW TABLES;

	查询表结构	
		-- 标准语法
		DESC 表名;
			
	查询表字符集
		-- 标准语法
		SHOW TABLE STATUS FROM 库名 LIKE '表名';

②C(Create)：创建
	创建数据表
		--标准语法
		CREATE TABLE 表名(
			列名1 数据类型1,
			列名2 数据类型2,
			....
			列名n 数据类型n
		);
		-- 注意：最后一列，不需要加逗号

	数据类型
		1. int：整数类型	
		2. double:小数类型	
		3. date:日期，只包含年月日     yyyy-MM-dd
		4. datetime:日期，包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
		5. timestamp:时间戳类型	包含年月日时分秒	 yyyy-MM-dd HH:mm:ss	
			-如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
		6. varchar：字符串

	复制表
		-- 标准语法
		CREATE TABLE 表名 LIKE 被复制的表名;

③U(Update)：修改
	修改表名 
		-- 标准语法
		ALTER TABLE 表名 RENAME TO 新的表名;

	修改表的字符集
		-- 标准语法
		ALTER TABLE 表名 CHARACTER SET 字符集名称;
		  
	添加一列
		-- 标准语法
		ALTER TABLE 表名 ADD 列名 数据类型;

	修改列名称和数据类型	
		-- 修改数据类型 标准语法
		ALTER TABLE 表名 MODIFY 列名 新数据类型;
		  
		-- 修改列名和数据类型 标准语法
		ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
		 
	删除列	
		-- 标准语法
		ALTER TABLE 表名 DROP 列名;

④D(Delete)：删除
	删除数据表	
		-- 标准语法
		DROP TABLE 表名;
		-- 删除不存在的表，会报错
		-- 错误代码：1051  Unknown table 'product3'

	删除数据表(判断，如果存在则删除)	
		-- 标准语法
		DROP TABLE IF EXISTS 表名;

3.DML-INSERT语句

新增表数据语法 
	格式1：给指定列添加数据
		-- 标准语法
		INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...);

	格式2：默认给全部列添加数据
		-- 标准语法
		INSERT INTO 表名 VALUES (值1,值2,值3,...);

	格式3：批量添加数据
		-- 默认添加所有列数据 标准语法
		INSERT INTO 表名 VALUES (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);

		-- 给指定列添加数据 标准语法
		INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

	注意事项
		列名和值的数量以及数据类型要对应
		除了数字类型，其他数据类型的数据都需要加引号(单引双引都可以，推荐单引)

4.DML-UPDATE语句

修改表数据语法
	-- 标准语法
	UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... where 条件;
注意事项	
	修改语句中必须加条件
	如果不加条件，则将所有数据都修改

5.DML-DELETE语句

删除表数据语法
	-- 标准语法
	DELETE FROM 表名 WHERE 条件;

注意事项
	删除语句中必须加条件
	如果不加条件，则将所有数据删除

6.DQL-单表查询

①查询语法
	select
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定

②查询全部
	-- 标准语法
	SELECT * FROM 表名;

③查询部分 
	指定字段查询
		-- 标准语法
		SELECT 列名1,列名2,... FROM 表名;

	去除重复查询
		-- 标准语法
		SELECT DISTINCT 列名1,列名2,... FROM 表名;

	计算列的值(四则运算)
		-- 标准语法
		SELECT 列名1 运算符(+ - * /) FROM 表名;

	起别名(AS可省略)
		-- 标准语法
		SELECT 列名1 AS 别名 FROM 表名;

④条件查询
		
	-- 查询金额(price)在4000 ~ 6000之间的商品信息
	SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
	SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;

	-- 查询数量(count)为14、30、23的商品信息
	SELECT * FROM product WHERE count=14 OR count=30 OR count=23;
	SELECT * FROM product WHERE count IN(14,30,23);

	-- 查询库存(stock)为null的商品信息
	SELECT * FROM product WHERE stock IS NULL;
	-- 查询库存不为null的商品信息
	SELECT * FROM product WHERE stock IS NOT NULL;

	-- 查询名称中包含电脑的商品信息
	SELECT * FROM product WHERE NAME LIKE '%电脑%';

查询条件分类
在这里插入图片描述

⑤聚合函数
		
	聚合函数语法
		-- 计算product表中总记录条数
		SELECT COUNT(*) FROM product;

		-- 获取最高价格
		SELECT MAX(price) FROM product;
		-- 获取最高价格的商品名称
		SELECT NAME,price FROM product WHERE price = (SELECT MAX(price) FROM product);

		-- 获取最低库存
		SELECT MIN(stock) FROM product;
		-- 获取最低库存的商品名称
		SELECT NAME,stock FROM product WHERE stock = (SELECT MIN(stock) FROM product);

		-- 获取总库存数量
		SELECT SUM(stock) FROM product;
		-- 获取品牌为苹果的总库存数量
		SELECT SUM(stock) FROM product WHERE brand='苹果';

		-- 获取品牌为小米的平均商品价格
		SELECT AVG(price) FROM product WHERE brand='小米';

聚合函数分类
在这里插入图片描述

⑥排序查询
	排序分类
		注意：多个排序条件，当前边的条件值一样时，才会判断第二条件
		ORDER BY 列名1 排序方式1,列名2 排序方式2	对指定列排序，ASC升序(默认的)  DESC降序

	排序语法
		-- 标准语法
		SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;

⑦分组查询
	-- 标准语法
	SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];

⑧分页查询
	-- 标准语法
	SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式] LIMIT 开始索引,查询条数;
	-- 公式：开始索引 = (当前页码-1) * 每页显示的条数

四、主键约束

1.约束的概念和分类

	约束的概念
		对表中的数据进行限定，保证数据的正确性、有效性、完整性！

约束的分类
在这里插入图片描述

2.主键约束
	
	主键约束特点
		主键约束包含：非空和唯一两个功能
		一张表只能有一个列作为主键
		主键一般用于表中数据的唯一标识

	建表时添加主键约束
		-- 标准语法
		CREATE TABLE 表名(
			列名 数据类型 PRIMARY KEY,
			列名 数据类型,
			...
		);

	删除主键
		-- 标准语法
		ALTER TABLE 表名 DROP PRIMARY KEY;

	建表后单独添加主键
		-- 标准语法
		ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

3.主键自动增长约束
	
	建表时添加主键自增约束
		-- 标准语法
		CREATE TABLE 表名(
			列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
			列名 数据类型,
			...
		);

	删除自动增长
		-- 标准语法
		ALTER TABLE 表名 MODIFY 列名 数据类型;

	建表后单独添加自动增长
		-- 标准语法
		ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

4.唯一约束
	
	建表时添加唯一约束
		-- 标准语法
		CREATE TABLE 表名(
			列名 数据类型 UNIQUE,
			列名 数据类型,
			...
		);

	删除唯一约束 （其实就是删除唯一约束产生的索引，唯一约束就是唯一索引）
		-- 标准语法
		ALTER TABLE 表名 DROP INDEX 列名;

	建表后单独添加唯一约束	
		-- 标准语法
		ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

5.非空约束
	
	建表时添加非空约束
		-- 标准语法
		CREATE TABLE 表名(
			列名 数据类型 NOT NULL,
			列名 数据类型,
			...
		);

	删除非空约束
		-- 标准语法
		ALTER TABLE 表名 MODIFY 列名 数据类型;

	建表后单独添加非空约束
		-- 标准语法
		ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

五、外键约束

1.外键约束概念

	当表与表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的准确性！
	让表和表之间产生关系，从而保证数据的准确性！

2.外键约束
		
	建表时添加外键约束
		CREATE TABLE 表名(
			列名 数据类型 约束,
			...
			CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
		);

	删除外键约束
		ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

	建表后单独添加外键约束
		ALTER TABLE 表名 ADD 
		CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

3.级联删除
	
	当我想把主表中的数据进行删除时，我期望从表中有关联的数据也会随之删除！
		
	添加级联更新
		ALTER TABLE 表名 ADD 
		CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
		ON UPDATE CASCADE;

	添加级联删除
		ALTER TABLE 表名 ADD 
		CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
		ON DELETE CASCADE;

	同时添加级联更新和级联删除
		ALTER TABLE 表名 ADD 
		CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
		ON UPDATE CASCADE ON DELETE CASCADE;

4.多表操作
	
	一对一
		在任意一个表建立外键，去关联另外一个表的主键。
			
	一对多
		在多的一方，建立外键约束，来关联一的一方主键。
			
	多对多
		需要借助第三张中间表，中间表至少包含两个列。这两个列作为中间表的外键，分别关联两张表的主键。

5.多表查询

	内连接查询
		查询原理
			内连接查询的是两张表有交集的部分数据(有主外键关联的数据)。
		
		查询语法
			显式内连接
				SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
			隐式内连接
				SELECT 列名 FROM 表名1,表名2 WHERE 条件;

	外连接查询
		左外连接
			查询原理
				查询左表的全部数据，和左右两张表有交集部分的数据。
			查询语法
				SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

		右外连接
			查询原理
				查询右表的全部数据，和左右两张表有交集部分的数据。
			查询语法
				SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

	子查询
		子查询概念
			查询语句中嵌套了查询语句，我们就将嵌套的查询称为子查询。
		
		结果是单行单列的
			查询作用
				可以将查询的结果作为另一条语句的查询条件，使用运算符判断。 = > >= < <= 等。
			查询语法
				SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);
		
		结果是多行单列的
			查询作用
				可以作为条件，使用运算符 IN 或 NOT IN 进行判断。
			查询语法
				SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 
		
		结果是多行多列的
			查询作用
				查询的结果可以作为一张虚拟表参与查询。
			查询语法
				SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];

六、视图

1.视图介绍

	视图：是一种虚拟存在的数据表，这个虚拟表并不在数据库中实际存在。
	作用：将一些较为复杂的查询语句的结果，封装到一个虚拟表中，后期再有相同需求时，直接查询该虚拟表即可。

2.视图的创建、查询、修改和删除

	创建视图语法
		CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
		
	查询视图语法
		SELECT * FROM 视图名称;

	修改视图数据语法
		UPDATE 视图名称 SET 列名=值 WHERE 条件;

	修改视图结构语法
		ALTER VIEW 视图名称 (列名列表) AS 查询语句;

	删除视图语法
		DROP VIEW [IF EXISTS] 视图名称;

七、数据库的备份和恢复

命令行方式
	备份
		登录到 MySQL 服务器，输入： mysqldump –u root –p 数据库名称 > 文件保存路径
	恢复
		1. 登录 MySQL 数据库。
		2. 删除已备份的数据库。
		3. 重新创建名称相同的数据库。
		4. 使用该数据库。
		5. 导入文件执行：source 备份文件全路径。

八、MySQL 存储过程和函数

1.存储过程和函数介绍
	
	存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合。
	存储过程和函数的好处
		提高代码的复用性。
		减少数据在数据库和应用服务器之间的传输，提高效率。
		减少代码层面的业务处理。
	存储过程和函数的区别
		存储函数必须有返回值。
		存储过程可以没有返回值。

2.存储过程的创建和调用
	
	创建存储过程
		-- 修改结束分隔符
		DELIMITER $

		-- 创建存储过程
		CREATE PROCEDURE 存储过程名称(参数列表)
		BEGIN
			SQL 语句列表;
		END$

		-- 修改结束分隔符
		DELIMITER ;
			
	调用存储过程
		CALL 存储过程名称(实际参数);

3.存储过程的查看和删除
		
	查看数据库中所有的存储过程
		SELECT * FROM mysql.proc WHERE db='数据库名称';
				
	删除存储过程
		DROP PROCEDURE [IF EXISTS] 存储过程名称;

4.存储过程语法-变量
	
	定义变量
		DECLARE 变量名 数据类型 [DEFAULT 默认值];

	变量赋值方式一
		SET 变量名 = 变量值;

	变量赋值方式二
		SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

5.存储过程语法-if语句
		
	if语句标准语法
		IF 判断条件1 THEN 执行的sql语句1;
		[ELSEIF 判断条件2 THEN 执行的sql语句2;]
		...
		[ELSE 执行的sql语句n;]
		END IF;

6.存储过程语法-参数传递
		
	CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
	BEGIN
		SQL 语句列表;
	END$
	
	IN：代表输入参数，需要由调用者传递实际数据(默认)
	OUT：代表输出参数，该参数可以作为返回值
	INOUT：代表既可以作为输入参数，也可以作为输出参数

7.存储过程语法-while
		
	while循环语法
		初始化语句;
		WHILE 条件判断语句 DO
			循环体语句;
			条件控制语句;
		END WHILE;

8.存储函数
		
	存储函数和存储过程是非常相似的，区别在于存储函数必须有返回值。
			
	创建存储函数
		CREATE FUNCTION 函数名称(参数列表)
		RETURNS 返回值类型
		BEGIN
			SQL 语句列表;
			RETURN 结果;
		END$

	调用存储函数
		SELECT 函数名称(实际参数);

	删除存储函数
		DROP FUNCTION 函数名称;

九、MySQL 触发器

1.触发器介绍

触发器是与表有关的数据库对象，可以在 insert、update、delete 之前或之后触发并执行触发器中定义的 SQL 语句。
这种特性可以协助应用系统在数据库端确保数据的完整性、日志记录、数据校验等操作。
使用别名 NEW 和 OLD 来引用触发器中发生变化的内容记录。

触发器分类
在这里插入图片描述

2.触发器的操作

	创建触发器
		DELIMITER $

		CREATE TRIGGER 触发器名称
		BEFORE|AFTER INSERT|UPDATE|DELETE
		ON 表名
		FOR EACH ROW
		BEGIN
			触发器要执行的功能;
		END$

		DELIMITER ;

	查看触发器
		SHOW TRIGGERS;

	删除触发器
		DROP TRIGGER 触发器名称;

十、MySQL 事务

1.事务介绍

	事务：一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。
	单元中的每条 SQL 语句都相互依赖，形成一个整体。
	如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会撤回到事务最初的状态。
	如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。

2.事务的操作

	开启事务
		START TRANSACTION;
	
	回滚事务
		ROLLBACK;
	
	提交事务
		COMMIT;

3.事务的提交方式

	事务提交方式的分类
		自动提交(MySQL默认)。
		手动提交。
		
	查看事务提交方式
		SELECT @@AUTOCOMMIT;

	修改事务提交方式
		SET @@AUTOCOMMIT=数字;

4.事务的四大特征(ACID)

	原子性(Atomicity)
		原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
		因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
	
	一致性(Consistency)
		一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。
		也就是说一个事务执行之前和执行之后都必须处于一致性状态。
	
	隔离性(isolcation)
		隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务。
		不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
	
	持久性(durability)
		持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。
		即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

5.事务的隔离级别

	事务的隔离级别
		多个客户端操作时，各个客户端的事务之间应该是隔离的，相互独立的，不受影响的。
		而如果多个事务操作同一批数据时，就会产生不同的问题，我们需要设置不同的隔离级别来解决这些问题。
	
	隔离级别分类

	引发的问题
	
	查询数据库隔离级别
		SELECT @@TX_ISOLATION;

	修改数据库隔离级别
		SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
		
		注意：隔离级别从小到大安全性越来越高，但是效率越来越低，所以不建议修改数据库默认的隔离级别。

隔离级别总结在这里插入图片描述

青青草原我最穷

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
1
评论
MySQL数据库基本操作和数据库主键，外键，视图，触发器，事务等详细介绍

一、SQL通用语法1.SQL 语句可以单行或多行书写，以分号结尾2.可使用空格和缩进来增强语句的可读性3.MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写数据库的注释：单行注释：-- 注释内容 #注释内容(mysql特有) 多行注释：/* 注释内容 */二、SQL分类1.DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等2.DML(Data Ma
复制链接

扫一扫