MySQL学习笔记（简洁版）

锅中锅灶台鱼

已于 2022-04-22 16:27:22 修改

阅读量525

点赞数 1

分类专栏：学习笔记文章标签： sql java mysql

于 2022-04-20 19:46:55 首次发布

本文链接：https://blog.csdn.net/O_C_E_A_N_/article/details/124303562

版权

学习笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

前言

对韩顺平MySQL教学视频的个人笔记

MySQL结构

请添加图片描述

在这里插入图片描述

DATABASE

CREATE DATABASE t; # 创建

DROP DATABASE [IF EXISTS] t; # 删除

SHOW DATABASES; # 打印所有数据库

SHOW CREATE DATABASE dxh_db01; # 打印已创建的某一个

备份数据库（DOS）

外部环境> mysqldump -u root -p -B dxh_db01 … … > e:\bak.sql

还原（DOS）

mysql> source e:\bak.sql;

TABLE

在这里插入图片描述

修改

USE dxh_db01; # 选数据库

ALTER TABLE t1 ADD hobby VARCHAR(255) ; # 增列

ALTER TABLE t1 MODIFY hobby VARCHAR(64)  NOT NULL DEFAULT '' ; # 修改字段属性

ALTER TABLE t1 DROP del_1;  # 删列

RENAME TABLE t1 TO t;  # 重命表名

ALTER TABLE t CHARACTER SET utf8; # 修改字符集/核对
ALTER TABLE t COLLATE utf8_bin;

ALTER TABLE t CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT '' ; # 取代列

DROP TABLE t;
DESC t;  # 查表

多表 ( 写好 WHERE )

在这里插入图片描述

SELECT *
	FROM a, c
	WHERE a.id = c.id
	;

表与表自由组合时用 WHERE 找表间字段的关系以去重

自连接
在这里插入图片描述

SELECT worker.ename AS 'cleck', boss.ename AS 'guide'
	FROM emp AS worker, emp AS boss
	WHERE worker.mgr = boss.empno;

子查询

-- （返回结果）单行子查询
SELECT * FROM emp
	WHERE deptno =(
		SELECT deptno 
		FROM emp
		WHERE ename = 'SMITH'
	);
	
	
-- （返回结果）多行子查询
--  和部门十号工作同的雇员但是不包括十号自己的雇员
SELECT ename, job, sal, deptno
	FROM emp
	WHERE  job IN ( -- 集合
		SELECT DISTINCT job 
			FROM emp
			WHERE deptno = 10
	) AND deptno != 10;
	
-- 多列子查询
SELECT * FROM emp
	WHERE (deptno, job) = (
		SELECT deptno, job
		FROM emp
		WHERE ename = 'SMITH'
	);

对于多行子查询的返回结果可以用 ALL, ANY 限制

在这里插入图片描述

临时表

在这里插入图片描述

表的复制与去重

-- 表的结构复制
CREATE TABLE my_tab02 LIKE emp;

-- 表的(数据)复制
INSERT INTO my_tab01
	(id, `name`, sal, job, deptno)
	SELECT empno, ename, sal, job, deptno FROM emp;

-- 表的自我复制 ( data * 2^n )
INSERT INTO my_tab01
	SELECT *FROM my_tab01;
	
-- 表的去重 ( tmp )
CREATE TABLE tmp LIKE my_tab01;
INSERT INTO tmp 
	SELECT DISTINCT * FROM my_tab01;
DELETE FROM my_tab01;
INSERT INTO my_tab01 
	SELECT * FROM tmp;
DROP TABLE tmp;

外连接

左/右外连

在这里插入图片描述

ON 后字段补充，没有则为NULL

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

CRUD

对表内数据：

# 建表
CREATE TABLE `goods`(
id INT,
goods_name VARCHAR(10),
price DOUBLE
);

INSERT

# 插入数据
INSERT INTO `goods` (id,goods_name,price)
  VALUES(1,'HuaWei',2000),(2,'Apple',2500); 

INSERT INTO `goods` VALUES(3,'Nokia',2000);

全插入可省略列名，可插入NULL(如果允许)

字符和日期放在 ’ ’ 中，字段 `

选择的列有默认值则 (欲赋值字段集合 ) 可以不添加

UPDATE

# 更新数据
UPDATE goods 
	SET price = 1000, goods_name = 'Apple' 
	WHERE id = 2 AND price = 2000

set 后的修改值用，并列 ( 可NULL )

where 后的修改条件用运算符连接

DELETE

# 删除中的记录
DELETE FROM goods 
	WHERE id = 2;

-- 表中所有记录
DELETE FROM goods;

SELECT

# 查询
SELECT [DISTINCT] * FROM goods;

SELECT `id`, `goods_name` FROM goods;

SELECT (a+b+c) AS total_score FROM goods;

DISTINCT 去重

全列 *

还可以对列运算 , 用AS取列的别名

where子句 ( 过滤 ) 中常用运算符：

在这里插入图片描述

… id LIKE ‘1%’ 以1开头的不限制长度的id

… id LIKE ‘1_’ 以1开头的两位字符

在这里插入图片描述

BETWEEN … AND … 是双闭区间

IN (a,b,c) 是集合

ORDER BY 子句排序查询结果

SELECT (id+price) AS 'a' FROM goods
	WHERE id IN (1,2,3)
	ORDER BY a DESC;

默认升序，放于句尾

ASC, DESC

GROUP BY 子句分组统计

SELECT id, price, COUNT(id), COUNT(price) FROM goods 
	GROUP BY id, price;

SELECT 中的各列名一定要作为 GROUP BY 的标准

SELECT id, COUNT(price) FROM goods 
	GROUP BY id 
	HAVING COUNT(id) > 1;

HAVING 过滤

可使用别名

分页查询（展示符合条件的记录的一部分）

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

SELECT * FROM `one` 
	ORDER BY id
	LIMIT 0, 2;

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

分组，排序，分页显示

合并查询

UNION ALL 不去重

函数

数字

COUNT

SELECT COUNT(*) FROM goods
	WHERE id = 1;

满足条件的记录的行数（*）

满足条件的某列有多少个，不计NULL（列名）

SUM （数字型列值和）

SELECT SUM(id) AS total_id,SUM(price) FROM goods;

SELECT SUM(id)/COUNT(*) AS aver FROM goods;

AVG ( 数字型列均和 )

SELECT AVG(id) FROM goods;

MAX/MIN ( 数字型列最值 )

SELECT MAX(id) FROM goods;

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

字符串

在这里插入图片描述

SELECT CONCAT(goods_name, ' is sold for ', price) AS Info FROM goods;

SELECT LENGTH(goods_name) FROM goods;

SELECT REPLACE(goods_name, 'HuaWei', 'HW') FROM goods; 

SELECT STRCMP('1', '1') FROM DUAL;
# DUAL 是 测试表

SELECT SUBSTRING(goods_name, 1, 2) FROM goods;

SELECT CONCAT(LCASE(SUBSTRING(goods_name,1,1)), 
			SUBSTRING(goods_name,2)) AS new_name FROM goods;

时间日期

在这里插入图片描述

CREATE TABLE msg(
	id INT,
	content TEXT,
	send_time DATETIME);
	
INSERT INTO msg VALUES(1, '123',CURRENT_TIMESTAMP());

-- 日期格式化处理
SELECT id, content, DATE(send_time)
	FROM msg;
	
-- 十分钟之内发布的新闻
SELECT * FROM msg
	WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
	
SELECT * FROM msg
	WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);
	
	
-- 日期 -> 天数差
SELECT DATEDIFF(NOW(),'2003-10-10') FROM DUAL;

SELECT DATEDIFF(DATE_ADD('2003-10-10', INTERVAL 80 YEAR), NOW())
	FROM DUAL;

在这里插入图片描述

加密和系统

在这里插入图片描述

流程控制

在这里插入图片描述

SELECT IF( 1+1 = 2, TRUE, FALSE) FROM DUAL;

SELECT  IFNULL (NULL, 'default') FROM DUAL;

-- 返回一个值
SELECT CASE
	WHEN TRUE THEN 'jack'
	WHEN FALSE THEN 'tom'
	ELSE 'mary' END;

在这里插入图片描述

约束

PRIMARY KEY

主键列值不可重不可NULL，每表最多一个（可复合）

字段末 / 表末（列名）
在这里插入图片描述

复合主键仍然是一个整体

唯一标识

NOT NULL

非空

UNIQUE

列值不可重，如果没有NOT NULL 约束，字段可多NULL

一表可多 UNIQUE

id INT PRIMARY KEY,
id INT UNIQUE NOT NULL, -- equal

FOREIGN KEY

定义在从表，主表有主键/UNIQUE，外键列数据在主键列存在/NULL

对应主外键至少数据类型一致

外键表型 INNODB

在这里插入图片描述

CREATE TABLE m_c(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '');
	
CREATE TABLE m_s(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT,
	-- 
	FOREIGN KEY (class_id) REFERENCES m_c(id));
	
INSERT INTO m_c 
	VALUES (100, 'java'), (200, 'web');
	
INSERT INTO m_s
	VALUES (1, 'tom', 100),
	(2, 'jack', 200);

INSERT INTO m_s	-- fail
	(3, 'hhh', 300);
	
DELETE FROM m_c -- fail
	WHERE id =100;
	
SELECT * FROM m_c, m_s;

CHECK

在这里插入图片描述

自增长

CREATE TABLE k(
	id INT PRIMARY KEY AUTO_INCREMENT,
	n INT);

-- 修改默认值
ALTER TABLE k
	AUTO_INCREMENT = 2;
	
INSERT INTO k 
	VALUES (NULL, 1);
	
-- 指定值为准
INSERT INTO k -- 
	VALUES(9,2);

-- 从上一个值开始 +1 
INSERT INTO k
	(n) VALUES(3);
	
SELECT * FROM k;

索引

索引只对创建的列有效

没有索引进行全表扫描，索引会形成索引数据结构

代价：磁盘占用；dml语句效率影响 (维护) 。

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

主键自带索引

创建

CREATE TABLE a(
	id INT,
	`name` VARCHAR(32));
	
SHOW INDEXES FROM a;

-- UNIQUE
CREATE UNIQUE INDEX id_index ON a(id);
ALTER TABLE a ADD UNIQUE INDEX id_index(id); 

-- 普通(字段可重)
CREATE INDEX id_index ON a(id);
ALTER TABLE a ADD INDEX id_index(id); 

-- PRIMARY KEY
CREATE TABLE b(
	id INT,
	`name` VARCHAR(32));
	
ALTER TABLE b ADD PRIMARY KEY (id);

SHOW INDEXES FROM b;

建立索引后，进行 SELECT 会大大提速

删除

-- drop
DROP INDEX n ON b;

ALTER TABLE b DROP PRIMARY KEY;

修改

-- modify = delete + create

查询

-- select
SHOW INDEX FROM b;
SHOW INDEXES FROM b;

SHOW KEYS FROM b;

DESC b;

建议

频繁使用（WHERE）建立 id

不适：

唯一性差（频繁作为查询条件） sex

经常更新 logincount

事务

类似于原语，将多个dml语句当作一个整体，以保证数据的一致性

InnoDB存储引擎默认且唯一支持事务

在这里插入图片描述

CREATE TABLE c(
	id INT,
	`name` VARCHAR(32));
	
SELECT * FROM c;

--  默认保存点（初始状态）
START TRANSACTION;

SAVEPOINT a;

INSERT INTO c VALUES (1, 'l');

SAVEPOINT b;

INSERT INTO c VALUES (2, 'i');

ROLLBACK TO b;

ROLLBACK TO a;

-- 回退到初始状态
ROLLBACK;

--
COMMIT;

回退

保存点是事务中的点，commit 后会自动删除该事务定义的所有保存点，回退途经保存点会被删除
提交

隔离级别

==事务间（不同客户端的操作）==的隔离程度

默认 REPEATABLE READ

在这里插入图片描述

隔离级别自上而下逐渐增强

加锁：如果某事务正在操作未提交，则加锁端事务会等待其提交后再进行。

在这里插入图片描述

指令

-- 查看当前会话/系统隔离级别
-- mysql>
SELECT @@tx_isolation;
SELECT @@global.tx_isolation;

-- 设置
-- mysql>
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL [ ];

ACID

Atomicity

事务是一个不可分割的工作单位
Consistency

一致状态
Isolation

多个并发事务之间要相互隔离
Durability

事务一旦提交，对数据库的数据改变是永久的

存储引擎

表类型由存储引擎决定

在这里插入图片描述

重点

在这里插入图片描述

MyISAM, InnoDB, Memory

视图

虚拟表，由查询范围定义，数据来自对应的真实表（基表）

视图/基表修改后相互影响，视图最终都映射到基表

可多个基表

在这里插入图片描述

-- 视图中可以再使用视图
CREATE VIEW myView
	AS 
	SELECT id, `name` FROM account;

CREATE VIEW my
	AS 
	SELECT id FROM myView;

应用

安全（保密字段）
性能（避免使用 JOIN ）
灵活（）

在这里插入图片描述

MySQL管理

在这里插入图片描述

建/删用户

-- 建
create user 'hhh' @'localhost' identified by 'dxh';

-- 删
drop user 'hhh' @'localhost';

在这里插入图片描述

不同数据库用户登录DBMS后，根据相应的权限，可操作的数据库和数据对象（表，视图，触发器）不同

修改用户密码

root 中：

-- 修改他人
SET PASSWORD FOR 'hhh'@'localhost' = PASSWORD('1314');

-- 自己
SET PASSWORD = PASSWORD('dxh');

-- 查看用户
select `host`, `user` from mysql.user;

在这里插入图片描述

mysql.user

用户权限管理

授予

在这里插入图片描述

-- 几乎root权限
GRANT ALL ON *.* TO 'hhh'@'localhost' ;

-- 对应用户获得权限
GRANT SELECT, INSERT 
	ON dxh_db01.a
	TO 'hhh'@'localhost';

回收

在这里插入图片描述

-- 回收对应用户的权限
REVOKE SELECT, INSERT  -- ALL
	ON dxh_db01.a
	FROM 'hhh'@'localhost';

权限生效

FLUSH PRIVILEGES;

锅中锅灶台鱼

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记（简洁版）

DATABASECREATE DATABASE t; # 创建DROP DATABASE [IF EXISTS] t; # 删除SHOW DATABASES; # 打印所有数据库SHOW CREATE DATABASE dxh_db01; # 打印已创建的某一个备份数据库（DOS）外部环境> mysqldump -u root -p -B dxh_db01 … … > e:\bak.sql还原（DOS）mysql> source e:\ba
复制链接

扫一扫