MySQL基础完整内容

最新推荐文章于 2024-07-21 19:06:05 发布
最新推荐文章于 2024-07-21 19:06:05 发布
阅读量381 收藏
点赞数 1
分类专栏： Mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yyuggjggg/article/details/119580285
版权

补充内容

1.数据完整性

2.DQL补充：关键字与查询详叙

3.有主外键关联表，先删外键表数据，再删除主键表数据

数据库

数据库、数据表、数据之间的关系

image-20210806075315871

image-20210806075832043

DDL 操作数据库和数据表

/*
查询所有数据库
-- 标准语法：
	SHOW DATABASES;
*/
SHOW DATABASES;
/*
查询某个数据库的创建语句
标准语法：
	SHOW CREATE DATABASE 数据库名称
*/
SHOW CREATE DATABASE mysql;
/*
	创建数据库
	标准语法：
		CREATE DATABASE 数据库名称;
*/
-- 创建0809开始自学的数据库内容
CREATE DATABASE case0806;
/*
	为了避免出现上面数据库创建语句，如果重复时，创建报错，
	故使用下面的数据库创建语句来避免出现这种错误出现，
	下面语句执行后，若存在同样的数据库，便只是会出现警告
	并不会报错
	
	创建数据库，判断、如果不存在则创建
	标准语法：
		CREATE DATABASE IF NOT EXISTS 数据库名称;
*/
CREATE DATABASE IF NOT EXISTS case0806;
/*
	创建数据库，并指定字符集
	标准语法：
		CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称；
*/
CREATE DATABASE case0806 CHARACTER SET utf8;

-- 查看数据库的字符集
SHOW CREATE DATABASE case0806;

CREATE DATABASE IF NOT EXISTS case0806 CHARACTER SET utf8;
SHOW CREATE DATABASE case0806;

/*
	修改数据库的字符集
		ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
*/
ALTER DATABASE case0806 CHARACTER SET gbk;
SHOW CREATE DATABASE case0806;
ALTER DATABASE case0806 CHARACTER SET utf8;

/*
	删除数据库：
		DROP DATABASE 数据库名称；
*/
DROP DATABASE case0806;

/*
	删除数据库，判断、如果存在则删除：（与前面的创建数据库类似）
		DROP DATABASE IF EXISTS 数据库名称;
*/
DROP DATABASE IF EXISTS db2;

/*
	使用数据库
		USE 数据库名称;
*/
USE case0806;

/*
	查询当前使用的数据库
		SELECT DATABASE();
*/
SELECT DATABASE();

-- 操作数据表
-- 使用mysql数据库
USE mysql;

/*
	查询所有的数据表
		SHOW TABLES;
*/
SHOW TABLES;

/*
	查询表结构：
		DESC 表名;
*/
-- 查询库中所有的表
DESC USER;

/*
	查询数据表的字符集
		SHOW TABLE STATUS FROM 数据库名称 LIKE '表名';
*/
SHOW TABLE STATUS FROM mysql LIKE 'user';

/*
	创建数据表
		CREATE TABLE 表名(
			列名 数据类型 约束,
			列名 数据类型 约束,
			...
			列名 数据类型 约束
		);
*/
CREATE TABLE product(
	id INT,
	NAME VARCHAR(20),
	price DOUBLE,
	stock INT,
	insert_time DATE
);
-- 查询库中所有的表
DESC product;

-- 修改数据表
/*
	修改表名
		ALTER TABLE 旧表名 RENAME TO 新表名;
*/
ALTER TABLE product RENAME TO product2;

/*
	修改表的字符集
		ALTER TABLE 表名 CHARACTER SET 字符集名称;
*/
SHOW TABLE STATUS FROM case0806 LIKE 'product';
ALTER TABLE product CHARACTER SET gbk;

/*
	单独添加一列
		ALTER TABLE 表名 ADD 列名 数据类型;
*/
ALTER TABLE product ADD color VARCHAR(10);

/*
	修改某列的数据类型
		ALTER TABLE 表名 MODIFY 列名 新数据类型;
*/
ALTER TABLE product MODIFY color INT;
DESC product;

/*
	修改列名和数据类型
		ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
*/
ALTER TABLE product CHANGE color address VARCHAR(200);


/*
	删除某一列
		ALTER TABLE 表名 DROP 列名;
*/
ALTER TABLE product DROP address;

-- 数据表删除
/*
	DROP TABLE 表名;
*/
DROP TABLE product;

/*
	删除数据表（判断，如果存在则删除）
		DROP TABLE IF EXISTE 表名;
*/
DROP TABLE IF EXISTS product;

DML 表数据的增删改

-- 新增表数据
-- 列名和值的数量以及数据类型要对应,除了数字类型，其他数据类型的数据都需要加引号(单弓双引都行，推荐单引)。
/*
	给指定列添加数据
		INSERT INTO 表名(列名1,列名2,...） VALUES(值1,值2,...);
*/
INSERT INTO product(id,NAME,price,stock,insert_time) VALUES (1,'qq',1999.99,25,'2020-02-02');
INSERT INTO product (id,NAME,price) VALUES (2,'但是',3999.99);

/*
	给全部列添加数据
		INSERT INTO 表名 VALUES(值1,值2,...);
*/
INSERT INTO product VALUES (3,'冰袖',1500,35,'2030-02-03');

/*
	批量添加数据
		INSERT INTO 表名(列名1，列名2，...） VALUES(值1,值2,...),(值1,值2,...),...;
		
		INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),...;
*/

-- 修改和删除表数据
/*
	修改表中的数据
		UPDATE 表名 SET 列名1=值1，列名2=值2,...[WHERE 条件];
		中括号表示可选，可以有where也可以没有，在这里需要有，因为没有的话会删除全部的数据
	修改语句中必须加条件,如果不加条件,则会将所有数据都修改。
*/
UPDATE product SET price=3500 WHERE NAME='qq';

UPDATE product SET price=1800,stock=36 WHERE NAME='但是';

/*
	删除表中的数据
		DELETE FROME 表名 [WHERE 条件];
	删除语句中必须加条件，如果不加条件,则会将所有数据都删除。
*/
DELETE FROM product WHERE NAME='但是';

DELETE FROM product WHERE stock=25;

DQL表数据的查询

条件查询

image-20210809082558797

-- 条件查询
SELECT * FROM product WHERE stock > 20;

SELECT * FROM product WHERE NAME='冰袖';

SELECT * FROM product WHERE price >= 400 AND price <= 6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;

SELECT * FROM product WHERE stock = 14 OR stock=30 OR stock=23;
SELECT * FROM product WHERE stock IN(14,30,23);

SELECT * FROM product WHERE stock IS NULL;

SELECT * FROM product WHERE stock IS NOT NULL;
-- 查询名称以冰开头的商品信息
SELECT * FROM product WHERE NAME LIKE '冰%';
-- 查询名称第二个字是袖的商品信息
SELECT * FROM product WHERE NAME LIKE '_袖%';
-- 查询名称为4个字符的商品信息
SELECT * FROM product WHERE NAME LIKE '____';
-- 查询名称中包含电脑的商品信息
SELECT * FROM product WHERE NAME LIKE '%电脑%';

聚合函数查询

image-20210809083810396

-- 聚合函数查询
-- 计算product表中总记录条数
SELECT COUNT(*) FROM product;
-- 获取最大值
SELECT MAX(price) FROM product;
SELECT MIN(stock) FROM product;
-- 获取总库存数量
SELECT SUM(stock) FROM product;
-- 获取name为冰袖的总库存数量
SELECT SUM(stock) FROM product WHERE NAME='冰袖';
SELECT AVG(price) FROM product WHERE NAME='冰袖';

排序查询

-- 排序查询
/*
	排序查询语法
		SELECT 
			列名列表
		FROM
			表名
		[WHERE 条件]
		ORDER BY
			列名 排序方式,
			列名 排序方式,...
	排序方式: ASC升序，DESC-降序
	如果有多个排序条件，只有当前边的条件值一样时,才会判断第二条件。
*/
-- 按照库存升序排序
SELECT * FROM product ORDER BY stock ASC;
-- 按照金额降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
-- 按照金额升序排序，如果金额相同，再按照库存降序排序
SELECT * FROM product ORDER BY price ASC,stock DESC;

分组查询

-- 分组查询
/*
如果有条件，一定是先进行条件判断
	分组查询语法
		SELECT 列名列表 FROM 表名 [WHERE 条件] GROUP BY 分组列名
		[HAVING 分组后的条件过滤]
		[ORDER BY 排序列名 排序方式]
*/
-- 按照品牌分组，获取每组商品的总金额
SELECT brand,SUM(price) FROM product GROUP BY brand;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额
seelct brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
SELECT brand,SUM(price) getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;
-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总
SELECT brand,SUM(price) getSUm FROM product 
WHERE price > 4000 
GROUP BY brand 
HAVING getSum > 7000 
ORDER BY getSum DESC;

分页查询

-- 分页查询
/*
	分页查询语法
		SELECT 列名列表 FROM 表名
		[WHERE 条件]
		[GROUP 分组列名]
		[HAVING 分组后的条件过滤]
		[ORDER BY 排序列名 排序方式]
		LIMIT 当前页数,每页显示的条数;
	
	LIMIT 当前页数，每页显示的条数;
	公式:当前页数=(当前页数-1) * 每页显示的条数
*/
-- 每页显示3条数据  如果一共7条数据
-- 第一页 当前页数=(1-1) * 3
SELECT * FROM product LIMIT 0,3;
-- 第二页 当前页数=(2-1) * 3
SELECT * FROM product LIMIT 3,3;
-- 第三页 当前页数=(3-1) * 3
SELECT * FROM product LIMIT 6,3;
-- ......

约束

image-20210809092515826

唯一约束：

非空约束：

image-20210809094217953

主键约束：

主键一般用于表中数据的唯一标识。不能为null值

image-20210809092640323

主键自增约束：

image-20210809093154817

外键约束：

让表与表之间产生关联关系,从而保证数据的准确性!

image-20210809091256577

外键级联更新/删除

了解

image-20210809092033147

多表操作

多表概念：说白了就是多张数据表,而表与表之间是可以有一定的关联关系 ,这种关联关系通过外键约束实现。

一对一

适用场景：
人和身份证。一个人只有一个身份证,一个身份证只能对应一个人。
建表原则：
在任意一个表建 立外键，去关联另外-一个表的主键。

image-20210809095507097

image-20210809102117688

ALTER TABLE orderlist ADD
CONSTRAINT ou_fk2 FOREIGN KEY orderlist(uid) REFERENCES USER(id)
ON UPDATE CASCADE;

一对多

适用场景
用户和订单。一个用户可以多个订单。
商品分类和商品。一个分类下可以有多个商品。
建表原则
在多的一方，建立外键约束，来关联一的一方主键。

image-20210809095425144

CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	NAME VARCHAR(20)			-- 姓名
);
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
-- 创建orderlist表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 主键id
	number VARCHAR(20),			-- 订单编号
	uid INT,				-- 外键列
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);

多对多

适用场景
学生和课程。一个学生可以选择多个课程,一个课程也可以被多个学生选择。
建表原则
需要借助第三张中间表,中间表至包含两个列。这两个列作为中间表的外键,分别关联两张表的主键。

image-20210809122504718

image-20210809122533249

image-20210809122635335

多表查询

内连接查询
-- 内连接
-- 查询的是相关的数据表之间有交集的那部分的数据，也就是有外键关系的数据
/*
	显示内连接
		SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON orderlist.uid = user.id;

-- 查询用户信息和对应的订单信息，起别名
SELECT * FROM USER u INNER JOIN orderlist o ON o.uid = u.id;

-- 查询用户姓名，年龄。和订单编号
SELECT 
	u.name,
	u.age,
	o.number
FROM
	USER u
INNER JOIN
	orderlist o
ON
	o.uid = u.id;

/*
	隐式内连接
	标准语法:
		SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;
*/
-- 查询用户姓名，年龄。和订单编号
SELECT 
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	o.uid = u.id;
外连接查询

image-20210809132752692

-- 左外连接
-- 查询所有用户信息，以及用户对应的订单信息
SELECT
	u.*,
	o.number
FROM
	USER u
LEFT OUTER JOIN
	orderlist o
ON
	o.uid=u.id;
-- 右外连接
-- 查询所有订单信息，以及订单所属的用户信息
SELECT
	o.*,
	u.name
FROM 
	USER u
RIGHT OUTER JOIN
	orderlist o
ON
	o.uid = u.id;
子查询

image-20210809134252373

image-20210809173507584

-- 结果是单行单列的
-- 查询年龄最高的用户姓名
SELECT NAME,age FROM USER WHERE age=(SELECT MAX(age) FROM USER);

-- 结果是多行多列的
-- 查询张三1和张三2的订单信息
SELECT * FROM orderlist WHERE uid IN (1,2);
SELECT id FROM USER WHERE NAME IN ('张三1','张三2');
SELECT * FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME IN ('张三1','张三2'));

-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM orderlist WHERE id > 4;
SELECT 
	u.name,
	o.number
FROM
	USER u,
	(SELECT * FROM orderlist WHERE id > 4) o
WHERE
	u.id = o.uid;
自关联查询

在同一张表中数据有关联性,我们可以把这张表当成多个表来查询。

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	mgr INT,
	salary DOUBLE
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.0),
(1002,'沙和尚',1005,8000.00),
(1003,'猪八戒',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,18000.00),
(1006,'李逵',1009,8500.00),
(1007,'林冲',1009,7900.00),
(1008,'武松',1009,8100.00),
(1009,'宋江',NULL,16000.00);
-- mgr上级编号，mgr=1005，表示他们的上级都是唐僧，唐僧的mgr为null，没有上级
-- 1009类似
-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
	员工信息：employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
SELECT
	e1.id,
	e1.name,
	e1.mgr,
	e2.id,
	e2.name
FROM
	employee e1
LEFT OUTER JOIN
	employee e2
ON
	e1.mgr = e2.id;
练习
-- 1.查询用尸的编号、姓名、年龄。订单编号
/*
	user表、orderlist表
	条件：user.id=orderlist.uid
*/
SELECT 
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.id = o.uid;
-- 2.查询所有的用户。用户的编号、姓名、年龄。订单编号
/*
	外连接
	
*/
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	USER u
LEFT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;
-- 3.查询所有的订单。用户的编号、姓名、年龄。订单编号
/*
	右外连接
*/
SELECT
	u.id,
	u.age,
	u.name,
	o.number
FROM
	USER u
RIGHT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;
-- 4.查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*
	user orderlist
	条件：user.id=o.uid and u.age>23
*/
SELECT 
	u.id,
	u.age,
	u.name,
	o.number
FROM
	USER u,
	orderlist o
WHERE
	u.age>23 AND u.id=o.uid;

-- 5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
/*
	user orderlist
	条件：user.id=orderlist.uid and user.name in ('张三','李四');
*/
SELECT 
	u.id,
	u.age,
	u.name,
	o.number
FROM
	USER u,
	orderlist o
WHERE 
	u.id=o.uid
	AND
	u.name IN ('张三1','张三2');
-- 6.查询商品分类的编号、分类名称。分类下的商品名称
/*
	category product
	条件：category.id = product.cid
*/
SELECT
	c.id,
	c.name,
	p.name
FROM
	category c,
	product p
WHERE
	c.id=p.cid;

-- 7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*
	category product
	条件：category.id=product.cid
	左外连接
*/
SELECT
	c.id,
	c.name,
	p.name

FROM
	category c
LEFT OUTER JOIN
	product p
ON
	c.id=p.cid;
-- 8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
SELECT
	c.id,
	c.name,
	p.name

FROM
	category c
RIGHT OUTER JOIN
	product p
ON
	c.id=p.cid;
-- 9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
/*
	user	product	多对多关系，所以还需要查询 us_pro
	条件：us_pro.uid=user.id and us_pro.pid=peoduct.id
*/
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id;
-- 10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*
	user	product	us_pro
	条件：us_pro.uid=user.id and us_pro.pid=product.id and user.name in ('张三1','张三2');
*/
SELECT
	u.id,
	u.name,
	u.age,
	p.name
FROM
	USER u,
	product p,
	us_pro up
WHERE
	up.uid=u.id
	AND
	up.pid=p.id
	AND
	u.name IN ('张三1','张三2');

视图

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

image-20210809201439797

视图的创建和查询

image-20210809201638897

-- 视图
CREATE DATABASE db0809pm;
USER db0809pm;
CREATE TABLE country(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30)
);
INSERT INTO country VALUES (NULL,'中国'),(NULL,'美国'),(NULL,'俄罗斯');
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	cid INT,`country`
	CONSTRAINT cc_fk1 FOREIGN KEY (cid) REFERENCES country(id)
);
INSERT INTO city VALUES (NULL,'beijing',1),(NULL,'shanghai',1),(NULL,'niuy',2),(NULL,'莫斯科',3);
-- 创建city_ country视图，保存城市和国家的信息(使用指定列名)
CREATE VIEW city_country (city_id,city_name,country_name) AS
SELECT 
	c1.id,
	c1.name,
	c2.name
FROM
	city c1,
	country c2
WHERE
	c1.id=c2.id;
-- 查询视图
SELECT * FROM city_country;
视图的修改和删除

image-20210809203123616

-- 修改视图数据,将北京修改为深圳。(注意:修改视图数据后，源表中的数据也会随之修改)
UPDATE city_country SET city_name='深圳' WHERE city_name='beijing';

-- 将视图中的country_ name修 改为name
ALTER VIEW city_country (city_id,city_name,NAME) AS 
SELECT 
	c1.id,
	c1.name,
	c2.name
FROM
	city c1,
	country c2
WHERE
	c1.id=c2.id;
-- 删除视图
DROP VIEW IF EXISTS city_country;

数据库备份和恢复

image-20210809203700182

MySQL存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
存储过程和函数的好处
提高代码的复用性。
减少数据在数据库和应用服务器之间的传输,提高效率。
减少代码层面的业务处理。

类似与java中的方法

存储过程和函数的区别
存储函数必须有返回值。
存储过程可以没有返回值。

存储过程的创建和调用

image-20210809204721780

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	gender VARCHAR(5),
	score INT
);
INSERT INTO student VALUES(NULL,'张三',23,'男',95),(NULL,'lisi',24,'男',98),
(NULL,'wangwu',25,'女',100),(NULL,'zhaoliu',26,'女',90);

-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
-- 创建stu_ group()存储过程，封装分组查询总成绩，并按照总成绩升序排序的功能
DELIMITER $

CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$
DELIMITER ;
-- 调用stu_ group()存储过程
CALL stu_group();
储存过程的查看和删除

image-20210809210004161

-- 杳看db6数掘库中所有的存储讨程
SELECT * FROM mysql.proc WHERE db='db0809pm';
-- 删除储存过程
DROP PROCEDURE IF EXISTS stu_group;
存储过程语法
变量

image-20210809210331626

-- 定义一个int类型变量，并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	-- 定义变量
	DECLARE num INT DEFAULT 10;
	-- 使用变量
	SELECT num;
END$

DELIMITER ;

-- 调用pro_ test1存 储过程
CALL pro_test1();

-- 定义一个varchar类型变量并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	-- 定义变量
	DECLARE NAME VARCHAR(10);
	-- 为变量赋值
	SET NAME = '存储过程';
	-- 使用变量
	SELECT NAME;
END$

DELIMITER ;
-- 调用pro_ test2存 储过程
CALL pro_test2();

-- 定义两个Int变量，用于存储男女同学的总分数
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	-- 定义两个变量
	DECLARE men,women INT;
	-- 查询男同学的总分数，为men赋值
	SELECT SUM(score) INTO men FROM student WHERE gender='男';
	-- 查询女同学的总分数，为women赋值
	SELECT SUM(score) INTO women FROM student WHERE gender='女';
	-- 使用变量
	SELECT men,women;
END$

DELIMITER ;

-- 调用pro_ test3存 储过程
CALL pro_test3();
if语句

image-20210809211909930

/*
定义一个int变量，用于存储班级总成绩
定义一个varchar变量，用于存储分数描述
根据总成绩判断:
	380分及以上	学习优秀
	320 ~ 380	学习不错
	320以下		学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test4()
BEGIN
	-- 定义变量
	DECLARE total INT;
	DECLARE info VARCHAR(10);
	-- 查询总成绩，为total赋值
	SELECT SUM(score) INTO total FROM student;
	-- 对总成绩判断
	IF total > 380 THEN
		SET info = '学习优秀';
	ELSEIF total >= 320 AND total <= 380 THEN
		SET info = '学习不错';
	ELSE
		SET info = '学习一般';
	END IF;
	-- 查询总成绩和描述信息
	SELECT total,info;
END$

DELIMITER ;

-- 调用pro_test4储存过程
CALL pro_test4();
参数传递

image-20210809212800843

/*
输入总成绩变量，代表学生总成绩
输出分数描述变量，代表学生总成绩的描述信息
根据总成绩判断:
	380分及以上	学习优秀
	320 ~ 380	学习不错
	320以下		学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test5(IN total INT,OUT info VARCHAR(10))
BEGIN
	-- 对总成绩判断
	IF total > 380 THEN
		SET info = '学习优秀';
	ELSEIF total >= 320 AND total <= 380 THEN
		SET info = '学习不错';
	ELSE
		SET info = '学习一般';
	END IF;
END$

DELIMITER ;

-- 调用pro test5存 储过程
CALL pro_test5(383,@info);

CALL pro_test5((SELECT SUM(score) FROM student),@info);

SELECT @info;
while循环

image-20210809213707210

-- 计算1~100之间的偶数和
DELIMITER $

CREATE PROCEDURE pro_test6()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <= 100 DO
		IF num % 2 = 0 THEN
			SET result = result + num;
		END IF;
		
		SET num = num+1;
	END WHILE;
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;
-- 调用pro_ test6存 储过程
CALL pro_test6();
存储函数

image-20210809214311949

-- 定义存储函数，获取学生表中成绩大于95分的学生数量
DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- 定义变量
	DECLARE s_count INT;
	-- 查询成绩大于95分的数量，为s_count赋值
	SELECT COUNT(*) INTO s_count FROM student WHERE score>95;
	-- 返回统计结果
	RETURN s_count;
END$

DELIMITER ;

-- 调用函数
SELECT fun_test1();
-- 删除函数
DROP FUNCTION fun_test1;

触发器

image-20210810081111412

操作触发器

image-20210810090822464

insert触发器
-- 触发器
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	money DOUBLE
);
-- insert into account values (null,'zhangsan',1000),(null,'lisi',1000);
-- 创建日志表account_log
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 日志id
	operation VARCHAR(20),		-- 操作类型
	operation_time DATETIME,
	operation_id INT,		-- 操作表的id
	operation_params VARCHAR(200)	-- 操作参数
);
-- 创建INSERT型触发器。用于对account表新增数据进行日志的记录’
DELIMITER $

CREATE TRIGGER acccount_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;

-- 向account表添加一条记录
INSERT INTO account VALUES (NULL,'wangwu',2000);

-- 查询account表
SELECT * FROM account;
-- 查询account_ log表
SELECT * FROM account_log;
update触发器
-- 创建UPDATE型触发器。用于对account表修改数据进行日志的记录
DELIMITER $

CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('更新前{id=',old.id,',name=',old.name,',money=',old.money,'}','更新后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;

UPDATE account SET money=2000 WHERE account.name='lisi';
SELECT * FROM account;
SELECT * FROM account_log;
delete触发器
-- 创建DELETE型触发器。用于对account表删除数据进行日志的记录
DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$

DELIMITER ;

DELETE FROM account WHERE account.name='wangwu';
SELECT * FROM account;
SELECT * FROM account_log;
查看/删除触发器

image-20210810094155722

-- 查看触发器
SHOW TRIGGERS;
-- 删除acccount_insert触发器
DROP TRIGGER acccount_insert;

事务

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

事务的操作

开启事务：

​ START TRANSACTION;

回滚事务：

​ ROLLBACK;

提交事务：

​ COMMIT;

-- 事务
-- 张三个李四转账500
-- 开启事务
START TRANSACTION;

-- zhangsan-500
UPDATE account SET money=money-500 WHERE NAME='zhangsan';

-- chucuo
-- 暂时储存，如果不提交，关闭mysql连接过后再重新打开，数据库里面的数据不会发生变化
-- lisi+500
UPDATE account SET money=money+500 WHERE NAME='lisi';

-- 回滚事务
ROLLBACK;
-- 提交事务
COMMIT;
事务的提交方式

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

/*
	查询事务提交方式: SELECT @@AUTOCOMMIT;		
			1代表自动提交	0代表手动提交
	修改事务提交方式:SET @QAUTOCOMMIT=数字;
*/

-- 查询事务的提交方式
SELECT @@autocommit;
-- 修改事务的提交方式
SET @autocommit = 0;
事务的四大特征

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

事务的隔离级别

image-20210810215103830

-- 查询事务隔离级别
SELECT @@tx_isolation;
-- 修改事务隔离级别(修改后需要重新连接)
SET clobal TRANSACTION ISOLATION LEVEL READ COMMITTED;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yhjyXnBr-1628603928108)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20210810121655957.png)]

存储引擎

image-20210810122027390

image-20210810122038771

​ MySQL数据库使用不同的机制存取表文件,包括存储方式、引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储弓|擎。

​ Oracle、SqIServer 等数据库只有一种存储引擎。而MySQL针对不同的需求，配置不同的存储引擎就会让数据库采取不同处理数据的方式和扩展功能。
​ MySQL支持的存储引擎有很多, 常用的有三种: InnoDB、MyISAM、 MEMORY。

​ 特性对比
​ MylSAM存储引擎:访问快,不支持事务和外键操作。
​ InnoDB存储引擎:支持事务和外键操作,支持并发控制，占用磁盘空间大。(MySQL 5.5版本后默认)
​ MEMORY存储引擎:内存存储,速度快，不安全。适给小量快速访问的数据。

image-20210810122411987

存储引擎的操作

image-20210810122540164

SHOW ENGINES;
SHOW TABLE STATUS FROM db0809pm;
SHOW TABLE STATUS FROM db0809pm  WHERE NAME = 'account';
CREATE TABLE engine_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
)ENGINE = INNODB;
ALTER TABLE engine_test ENGINE = MYISAM;
存储引擎的选择

MyISAM
特点:不支持事务和外键操作。读取速度快,节约资源。
使用场景:以查询操作为主，只有很少的更新和删除操作，姐对事务的完整性、并发性要求不是很高!
InnoDB
特点: MySQL的默认存储引擎,支持事务和外键操作。
使用场景:对事务的完整性有比较高的要求,在并发条件下要求数据的一致性，读写频繁的操作!
MEMORY
特点:将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下,可以提供更快的访问。
使用场景:通常用于更新不太频繁的小表,用来快速得到访问的结果!

​ 总结:针对不同的需求场景，来选择最适合的存储|擎即呵!如果不确定、则使用数据库默认的存储引擎!

索引

​ MySQL索引:是帮助MySQL高效获取数据的一种数据结构。所以,索引的本质就是数据结构!
​ 在表数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据，这样就可以**在这些数据结构上实现高级查找算法,**这种数据结构就是索引。

按照功能分类
普通索引:最基本的索引，没有任何限制。
唯一索引:索引列的值必须唯- , 但允许有空值。如果是组合索引,则列值组合必须唯一。
主键索引:一种特殊的唯一引, 不允许有空值。在建表时有主键列同时创建主键索弓l。
联合索列:顾名思义,就是将单列索弓l进行组合。
外键索引:只贿InnoDB引擎支持外键索引,用来保证数据的一致性、完整性和实现级联操作。
全文索引:快速匹配全部文档的方式。InnoDB 引擎5.6版本后才支持全文索引。MEMORY 引擎不支持。

按照结构分类
BTree索引: MySQL使用最频繁的一个索|数据结构,是InnoDB和MylSAM存储弓|擎默认的索|类型，底层基于B+Tree数据结构。
Hash索引: MySQL中Memory存储引擎默认支持的索引类型。

索引的操作
创建查询索引

image-20210810134945016

-- 为student表中的name列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);
-- 为student表中的age列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);
-- 查询student表中的索引 (主键列目带主键索引)
SHOW INDEX FROM student;
-- 查询db0809数据库中的product表(外键列自带外键索引)
SHOW INDEX FROM product;
添加删除索引

image-20210810135206561

索引的原理

​ 索引是在存储引擎中实现的,不同的存储弓擎所支持的索引也不一样,这里我们主要介绍InnoDB引擎的BTree索引。
​ BTree索引类型是基于B+Tree数据结构的,而B+Tree数据结构又是BTree数据结构的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。
需要理解的B+Tree和BTree的区别

磁盘存储

​ 系统从磁盘读取数据到内存时是以磁盘块( block )为基本单位的。
​ 位于同一个磁盘块中的数据会被一次性读取出来 ,而不是需要什么取什么。
​ InnoDB存储引擎中有页( Page )的概念,页是其磁盘管理的最小单位。InnoDB 存储弓|擎中默认每个页的大小为16KB。
​ InnoDB弓|擎将若干个地址连接磁盘块，以此来达到页的大小16KB ,在查询数据时如果一个页中的每条数据都能有 助于定位
​ 数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。

BTree

image-20210810141146579

在每一个结点上除了去保存键值以外，还会去保存真实的数据，在进行查询数据时，只要涉及到相应的磁盘块，那这些数据也会被全部读取出来，所以效率不是很高。

B+Tree

B+Tree特点：在分支结点上只保存键值，并没有真实的数据，真实的数据只会保存在叶子结点上

image-20210810141743010

在磁盘块上并没有涉及到IO操作，提高了查询的特点

叶子结点之间进行连接，方便进行范围的查询

BTree数据结构
每个节点中不仅包含key值,还有数据。会增加查询数据时磁盘的I0次数。
B+Tree数据结构
非叶子节点只存储key值。
所有数据存储在叶子节点。
所有叶子节点之间都有连接指针。
B+Tree好处
提高查询速度。
减少磁盘的I0次数。
树型结构较小。

索引的设计原则

创建索引遵循的原则
1.对查询频次较高，且数据量比较大的表建立索弓|。
2.使用唯一索引，区分度越高，使用索引|的效率越高。
3.索引字段的选择，最佳候选列应当从where子句的条件中提取。

​ 4.虽然可以有效的提升查询数据的效率 ,但并不是多 多益善。

image-20210810142541843

锁

​ 锁机制:数据库为了保证数据的一致性,在共享的资源被并发访问时变得安全所设计的一种规则。
​ 锁机制类似多线程中的同步,作用就是可以保证数据的一致性和安全性。
按操作分类
​ 共享锁:也叫读锁。针对同一份数据,多个事务读取操作可以同时加锁而不互相影响, 但是不能修改数据。
​ 排他锁:也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入。
按粒度分类
​ 表级锁:会锁定整个表。开销小，加锁快。锁定力度大,发生锁冲突概率高,并发度低。不会出现死锁情况。
​ 行级锁:会锁定当前行。开销大，加锁慢。锁定粒度小,发生锁冲突概率低,并发度高。会出现死锁情况。
按使用方式分类
​ 悲观锁:每次查询数据时都认为别人会修改,很悲观，所以查询时加锁。
​ 乐观锁:每次查询数据时都认为别人不会修改,很乐观,但是更新时会判断-下在此期间别人有没有去更新这个数据。

image-20210810142948317

InnoDB共享锁

image-20210810190521927

采用带有索引的列进行加锁，加的是行锁，如果是不带索引的列，就会提升为一个表锁

共享锁和共享锁是兼容的

数据可以被多个事务查询，但是不能进行修改数据

InnoDB排他锁

image-20210810190629308

普通查询没问题，只要是加锁查询就有问题了

排他锁和共享锁不兼容

排他锁和排他锁不兼容

MyISAM读锁

image-20210810191212689

要想让修改操作成功，必须解锁，而不是上面两种只要commit就可以了

MyISAM读锁

image-20210810191630159

要想其他连接操作成功，也是必须解锁

悲观锁和乐观锁

了解

?abc!
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL基础PDF版
05-16
2. **MySQL基础** - **DBMS**：数据库管理系统（DataBase Management System），如MySQL，用于科学地组织和存储数据，高效地获取和维护数据。 - **SQL语句分类**：主要包括DDL（数据定义语言）、DML（数据操作语言...
MySQL基础.docx
03-16
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其适合于中小型企业及个人网站的数据存储。...通过学习这些内容，读者可以熟练掌握MySQL的基本操作，为后续的数据库管理和开发打下坚实的基础。
MySQL数据库-DQL（单表查询）
金鳞踏雨
02-15 1951
单表查询、数据准备、查询语法、条件查询 （where）、聚合函数查询、排序查询 （order by）、排序语法、分组查询 （group by）、分页查询 （limit）
mysql主要内容_mysql基本内容学习过程
weixin_39650756的博客
01-28 304
mysql数据库的基本操作：1， 数据库的登录：mysql -u 用户名(root) -p密码 -P (端口) -h服务器名(本地表示:127.0.0.1)2. 更改数据库显示：mysql -u root -p --prompt \u@\h \d>--prompt: 使用--prompt命令进行修改；\h：表示本机(localhost)\u:当前登录用户(用户名)\d:当前操作数据库(dat...
康师傅mysql基础pdf
01-28
康师傅mysql基础pdf应该详细介绍了这些内容，并可能还包括了实践案例和常见问题解答。通过学习这份资料，初学者能够建立起对MySQL的基本理解和操作技能，为进一步深入学习和应用MySQL打下坚实基础。
关于MySQL的基础篇章
05-10
【MySQL基础知识详解】 MySQL是一种广泛使用的开源...以上是MySQL基础学习的主要内容，通过这些知识，开发者可以进行基本的数据库管理和数据操作。深入了解和熟练掌握这些概念将有助于在实际项目中高效地运用MySQL。
MySQL基础PDF文件
12-15
MySQL基础PDF文件涵盖了关于MySQL数据库的入门知识，包括数据库的概念、安装配置、基本语法以及SQL语句的操作。MySQL是一种广泛使用的开源关系型数据库管理系统（RDBMS），它以其免费、功能强大和高效的特点，成为了...
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 455
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL：SELECT 语句
Zachyy的博客
07-20 317
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL（事务、索引）&&MyBatis
hycccccch的博客
07-19 669
事务指的是一组操作的集合，是一个不可分割的工作单位。事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败默认MySQL的事务是自动提交，即当执行一条DML语句时，MySQL会立即隐式的提交事务开启事务后运行事务不会对数据改变，commit后才会改变数据如果有命令发生错误，需要进行rollback进行回滚、
mysql中ONLY_FULL_GROUP_BY报错
最新发布
zzzzzucc的博客
07-21 103
禁用会话级别的 ONLY_FULL_GROUP_BY SET SESSION sql_mode = ‘’;– 或者禁用全局级别的 ONLY_FULL_GROUP_BY SET GLOBAL sql_mode = ‘’;在 MySQL 中，sql_mode是一个服务器变量，用于定义 SQL 语句的运行模式。是sql_mode中的一个选项，它影响 SQL 查询中GROUP BY子句的行为。
使用 Vue3、Node.js、MySQL、Electron 和 Express 实现用户登录、文章管理和截屏功能
liyananweb的博客
07-15 935
通过本文，我们详细介绍了如何使用 Vue3、Node.js、MySQL、Electron 和 Express 实现一个完整的用户登录、文章管理和截屏功能的应用。希望这篇文章能为你提供有价值的参考，帮助你更好地理解和实现前后端分离的应用开发。如果你有任何问题或建议，欢迎在评论区留言讨论。
Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
xzzteach的博客
07-21 163
【代码】Windows版MySQL5.7解压直用（如何卸载更换位置重新安装）
Mysql的insert批量插入性能问题
m0_54123999的博客
07-17 228
同时分批插入也有数据一致性风险，如果在批次中途出现失败，部分数据可能已经插入，而部分数据可能未插入，需要额外处理数据一致性；当需要往数据库插入大量数据是，一条一条插入会导致插入时间长，性能不佳，所以我们一般会进行insert批量插入，一次性插入较大批次的数据，而不是每条记录单独插入，这通常会提高性能，因为批量操作减少了上下文切换和 SQL 解析的开销。事务期间，所有涉及的表或行会被锁定（根据具体的存储引擎），可能会导致更长时间的锁定，从而影响系统的并发性能，且资源占用高，大事务可能会占用较多的数据库资源。
使用Docker 实现 MySQL 循环复制（一）
m0_60511809的博客
07-18 1019
复制是 MySQL 的一项非常重要的技术，它使服务器能够将更改从一个实例复制到另一个实例，复制技术被广泛应用在水平横向扩展、商业智能和分析、地理数据分布、高可用性与容灾等关键性场景中。在某些地区或网络环境下，直接从官方 Docker Hub 下载镜像可能会出现速度慢、不稳定甚至无法连接等问题。典型的复制拓扑是主/从属服务器的一对多关系，高级复杂复制拓扑有双向复制、循环复制以及多源复制等。本次实验将在一台虚拟机中使用 Docker 容器构建三个 MySQL 循环复制拓扑。
【Mysql关于读已提交和可重复读（Read Committed）隔离级别下解决幻读的方案】
vbgesab的博客
07-19 928
在“读已提交”隔离级别下，虽然默认的MVCC机制不能直接解决幻读问题，但可以通过上述方法来解决。每种方法都有其适用场景和优缺点，选择合适的方法需要根据具体的应用需求和系统环境来决定。在高并发系统中，通常需要综合考虑性能和一致性的需求，选择最合适的解决方案。
MySQL第七次作业
taolichao30的博客
07-16 343
触发器名称分别为product_bf_insert、product_af_update和product_af_del。执行语句部分是向operate表插入操作方法和操作时间。在product表上分别创建BEFORE INSERT、AFTER UPDATE和AFTER DELETE 3个触发器，3.删除 product_bf_insert 和 product_af_update 这两个触发器。对 product 表执行 INSERT、UPDATE 和 DELETE 操作。2.1 INSERT 操作。
MySQL-显示所有错误信息
深度安全实验室
07-21 647
MySQL-显示所有错误信息
mysql的索引、事务和存储引擎
Hai990218的博客
07-17 1779
索引是一个排序的列表，在列表当中存储索引的值以及索引值对应数据所在的物理行。索引值和数据是一个映射关系。
MySQL基础知识详解
作者Ben Forta通过本书，从基础的数据库概念出发，逐步引导读者深入学习MySQL的高级特性。 书中首先介绍了基础的数据检索操作，这是所有数据库系统的核心功能。读者将学习如何使用SQL（结构化查询语言）来创建、...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

?abc!

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值