Mysql学习笔记

已于 2022-02-09 21:13:10 修改
阅读量570 收藏
点赞数
分类专栏： MySQL学习笔记 文章标签： mysql 数据库 sql
于 2022-02-09 21:10:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/L1ghtn1nggggg/article/details/122849832
版权
本文详细介绍了MySQL数据库的基础知识，包括SQL语法、数据查询、条件查询、排序、分组函数、存储引擎、事务处理、存储过程、索引和视图等内容。此外，还探讨了数据库设计的三大范式，强调了如何优化查询性能和确保数据完整性。
摘要由CSDN通过智能技术生成

Mysql

学习笔记，小小记录一下，之后进阶了再更新
笔记来源：老杜带你学_mysql入门基础 -> 点击跳转

目录

sql基础

基本介绍

  • 数据库DB：
    存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

  • 数据库管理系统DBMS：
    对数据库当中的数据进行增删改查。

    常见的数据库管理系统：
    MySQL、Oracle、MS SqlServer、DB2、sybase等…

  • 结构化查询语言SQL：
    通过编写SQL语句来完成数据库中数据的增删改查操作。

三者关系：DBMS–执行–> SQL --操作–> DB

常用命令

数据库：

CREATE DATABASE xxxx;#创建数据库
Show DATABASES;#查看数据库
DROP DATABASE xxxx;#删除数据库
USE DATABASE xxxx;#使用数据库
select database();#查看使用中的数据库
select version();#查看mysql数据库版本号
\c #用于终止一条命令的输入
exit;#退出mysql服务

表的基本介绍：

  • 表是数据库的基本组成单元

  • 行 — 数据/记录 data

    列 — 字段 column

  • 字段包括 字段名、数据类型、相关的约束

    • 特点：

      （1）字段名最长可达64个字符。

      （2）字段名可包含中文、英文字母、数字、下划线_、井字#、货币$及at符号@。

      （3）同一个表中，各个字段的名称绝对不能重复。

  • SQL语句分类：

    • DQL：数据查询语言-select
    • DML：数据操作语言，对数据进行增删改-insert delete update
    • DDL：数据定义语言,操作表的结构-create drop alter
    • TCL：事务控制语言-commit rollback
    • DCL：数据控制语言-grant revoke
#查看表结构*
DESCRIBE EMP;
DESC EMP;#简写
#查看详细表结构*
SHOW CREATE TABLE EMP;
#查看表列表
SHOW TABLES;
#删除表
DROP TABLE EMP;
#导入数据
source 文件路径;#路径中不能含有中文

简单查询

#查询单个字段
select ename from emp;
#查询多个字段
select ename,empno from emp;
#查询所有字段
select * from emp;#效率低、可读性差
select a,b,c,d.... from emp;
#起别名
select ename as name from emp;#as 可省略；别名含有空格需使用单引号
#使用数学表达式
select sal * 12 '年薪' from emp;
#去除重复记录(distinct只能出现在所有字段的最前方;distinct出现在两个字段之前，表示两个字段联合起来去重)
select distinct job from emp;

条件查询

select
	字段...
from
	表名
where
	条件;

条件列表：

  • =
  • <> 或 !=
  • < 和 > 、<= 和 >=
  • between…and… 注意遵顼左小右大
  • is null、is not null
  • and、or：and优先级比or高，括号可以提高优先级
  • in、not in
  • like （%、_）

排序

指定降序：
	select 
		ename,sal
	from
		emp
	order by
		sal desc;
指定升序（默认）:
	select 
		ename,sal
	from
		emp
	order by
		sal asc;
  • 多字段排序：字段在前起主导，主导字段均相等，按次字段排序

数据处理函数(单行处理函数)和分组函数（多行处理函数）

单行处理函数的特点：一个输入对应一个输出。

多行处理函数特点：多个输入，对应1个输出！

常见单行处理函数：

  • lower 转换小写
  • upper 转换大写
  • substr( 被截取的字符串, 起始下标,截取的长度) 取子串
  • concat 字符串的拼接
  • length 取长度
  • trim 去空格
  • str_to_date 将字符串转换成日期
  • date_format 格式化日期
  • format 设置千分位
  • case…when…then…when…then…else…end
  • round 四舍五入
  • rand() 生成随机数
  • ifnull 空处理函数：将 null 转换成一个具体值（在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。）

分组函数：

  • count 计数
  • sum 求和
  • avg 平均值
  • max 最大值
  • min 最小值

注意：
分组函数在使用的时候必须先进行分组，然后才能用，如果你没有对数据进行分组，整张表默认为一组。

分组函数自动忽略NULL，你不需要提前对NULL进行处理；分组函数不能够直接使用在where子句中；所有的分组函数可以组合起来一起用。

分组查询

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。这个时候我们需要使用分组查询

select 
	job,sum(sal)
from
	emp
group by
		job;
#可以联合分组
select 
	deptno, job, max(sal)
from
	emp
group by
	deptno, job;

使用having可以对分完组之后的数据进一步过滤。having不能单独使用，having不能代替where，having必须和group by联合使用。

单表查询执行顺序

select 
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...

执行顺序：

	1. from
	2. where
	3. group by
	4. having
	5. select
	6. order by

连接查询/多表查询

从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。
这种跨表查询，多张表联合起来查询数据，被称为连接查询。

分类：

  • 内连接：表之间没有主次关系
    • 等值连接：连接条件为两表等值的字段
    • 非等值连接：连接条件不为等量关系
    • 自连接：将自身看成另外一张表进行连接
  • 外连接：有主次关系，会查询主表所有的数据
    • 左外连接
    • 右外连接

内外连接可以混合使用。。。

注意：为避免笛卡尔积现象的展现（表的连接次数越多效率越低，尽量避免表的连接次数），需在连接时添加筛选条件，但连接次数没有减少

子查询

含义：select语句中嵌套select语句，被嵌套的select语句称为子查询。子查询可以出现select、from、where后

合并查询结果集和分页查询

**union：**可以减少匹配次数，并拼接两个结果集，但要求两个结果集列数相同

**limit：**将查询结果集的一部分取出来。通常使用在分页查询当中。

limit startIndex, length;#startIndex是起始下标;length是长度;起始下标从0开始。
#分页
#每页显示pageSize条记录
limit (pageNo-1)*pageSize , pageSizee;

DQL语句执行顺序

执行顺序！
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit…

数据类型

  • varchar：可变长度的字符串，根据实际长度动态分配空间
  • char：定长字符串，分配固定长度的空间存储数据，最长255字符
  • int：整型，最长11
  • bigint：长整型
  • float：单精度浮点
  • double：双精度浮点
  • date：短日期类型，年月日
  • datetime：长日期类型，年月日时分秒
  • clob：字符大对象，最多可存储4G的字符串
  • blob：二进制大对象，用于存储图片、声音、视频等流媒体数据，需要使用IO流

表的创建和删除（DDL）数据插入、数据修改、数据删除（DML）

#创建表
create table xxx(
	id int
);
#删除表
drop table xxx;
#插入
insert into xxx() values ();#注意一一对应
#insert 可以多次插入，在values后添加多条对应记录
#数字格式化
format(字段,'格式')；
#日期相关
/*mysql的日期格式：
	%Y	年
	%m  月
	%d  日
	%h	时
	%i	分
	%s	秒 */
str_to_date('字符串','日期格式')#将varchar转换为date
date_format(date,'%m/%d/%Y') #将date类型转换成具有一定格式的varchar
'1990-01-01'#日期标准格式
now() #获取系统当前时间，datetime类型
#修改
update xxx set col = ... where ...;#没有条件会更新所有
#删除
delete from xxx where;#没有条件会删除整张表！
  • 快速创建表：将一个查询结果当做一张表新建
create table xxx as select * from emp;
  • 快速删除表：
truncate table xxx;#属于DDL操作，效率较高，但不支持回滚
delete from xxx;#属于DML，速度慢，效率低，但支持回滚

约束constraint

创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性；约束分为列级约束和表级约束（多字段联合添加约束时使用）

  • 非空约束not null：字段不能为空
  • 唯一约束 unique：字段唯一，但可以为空
  • 主键约束 primary key：每张表均有，主键不能为空且唯一（同时添加非空约束和唯一约束，字段会自动成为主键）；分为单一主键和复合主键（多个字段）；一张表只能有一个主键，主键常使用int、bigint、char等定长类型
  • 外键约束 foreign key：子表的外键引用父表的字段即存在父子表关系，删除表/数据时先删子表，创建/插入数据时先建/插入父表；引用的外键不一定是主键，但至少有唯一约束，可以为空

auto_increment：字段值自增，可用于维护主键值

存储引擎

存储引擎是一个表存储/组织数据的方式，不同的存储引擎，表存储数据的方式不同。（mysql中存在）

给表添加/指定“存储引擎”:

在建表的时候可以在最后小括号的")"的右边使用：
ENGINE来指定存储引擎。
CHARSET来指定这张表的字符编码方式。

CREATE TABLE `t_student` (
 `no` int(11) ....
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#mysql默认的存储引擎是：InnoDB
#mysql默认的字符编码方式是：utf8
#查看mysql当前版本支持的存储引擎 版本不同支持情况不同
show engines \G

mysql常用存储引擎：

  • MyISAM存储引擎：

特点：

  1. 使用三个文件表示每个表：
    1. 格式文件 — 存储表结构的定义（mytable.frm）
    2. 数据文件 — 存储表行的内容（mytable.MYD）
    3. 索引文件 — 存储表上索引（mytable.MYI）
  2. 可被转换为压缩表、只读表来节省空间
  3. 不支持事务机制，安全性低
  • InnoDB存储引擎：默认存储引擎、重量级

特点：

  1. 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
  2. 提供一组用来记录事务性活动的日志文件
  3. 支持事务
  4. 支持数据库崩溃后自动恢复机制
  5. 安全！
  6. 效率不高、不能压缩和转换为只读、不能节省存储空间
  • MEMORY存储引擎：

特点：

  1. 数据存储在内存中，且行的长度固定
  2. 速度快！查询效率高！
  3. 表均以.frm 格式的文件表示
  4. 表数据及索引被存储在内存中
  5. 表级锁机制
  6. 不能包含 TEXT 或 BLOB 字段
  7. 不安全，关机之后数据消失

事务transaction

定义：
用户定义的一系列数据库更新操作，由单独单元的一个或多个sql语句（DML语句）组成

#启动事务-同时关闭自动提交
start transaction;
#提交事务-执行完毕后提交事务才能使操作生效
commit;
#回滚事务-返回事务执行前状态
rollback;

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以

提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中，事务成功的结束

也可以回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件，事务失败的结束

mysql默认情况下是支持自动提交事务的，每执行一条DML语句，则提交一次。

特性：

  • 原子性A：原子性是指事务的一个不可分割的逻辑单元，事务处理的操作要么全部执行，要么全部不执行
  • 一致性C：如果事务全部正确执行，数据库变化生效，处于有效状态；如果事务执行失败，系统会回滚，数据库恢复到事务执行前的有效状态。
  • 隔离性I：多个事务并发执行时，各个事务间不能相互干扰
  • 持久性D：事务完成后，事务对数据库中的修改将永久保存。

事务的隔离性：

两个事务之间的隔离级别，

  • 读未提交 read uncommitted：

事务A可以读取到事务B未提交的数据，存在脏读现象！

  • 读已提交 read committed

事务A只能读取到事务B提交之后的数据，解决了脏读的现象，但不可重复读取数据，事务未提交时不同次数重复读取数据得到数据不一样，但数据绝对真实，是oracle数据库默认的隔离级别。

  • 可重复读 repeatable read

事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变。解决了不可重复读取数据，但会出现幻影读即每一次读取到的数据都是幻象，不真实！（早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据仍然一样即读到的是假象）是mysql的默认事务隔离级别。

  • 序列化/串行化 serializable

最高隔离级别，效率最低，解决了所有的问题，但不允许并发。

#查看隔离级别
SELECT @@tx_isolation;
#设置隔离级别
set global transaction isolation level read uncommitted;

存储过程和存储函数×

#创建存储过程
CREATE PROCEDURE p_test()
BEGIN
SELECT xxx
END
#调用存储过程
CALL p_test();

索引

定义：
对数据库表中的一列或多列的值进行排序的一种结构，提高查询速度（根据索引检索而不是全表扫描），改善数据库性能

原理：
任何数据库当中主键上都会自动添加索引对象，在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象；任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号；在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。

在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree，采用中序遍历方式遍历取数据）

在mysql当中，主键上，以及unique字段上都会自动添加索引的

索引的创建和删除：

#创建索引:
create index emp_ename_index on emp(ename);
#删除索引
drop index emp_ename_index on emp;
#查看一个SQL语句是否使用了索引进行检索
explain select * from emp where ename = xxx;

索引失效的情况：

  1. 模糊匹配中以’%'开头
  2. 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效，因此不建议使用or
  3. 使用复合索引的时候，没有使用左侧的列查找，索引失效
  4. 在where当中索引列参加了运算，索引失效
  5. 在where当中索引列使用了函数
  6. …

索引是各种数据库进行优化的重要手段，有以下分类：

  • 普通索引：由KEY或INDEX定义的索引，允许空值和重复值，可以创建在任何数据类型中
  • 唯一索引：由UNIQUE定义的索引，索引字段值必须唯一，允许空值
  • 全文索引：由FULLTEXT定义的索引，指定义索引的字段支持值的全文查找（TEXT、CHAR、VARCHAR）
  • 空间索引：由SPATIAL定义的索引，只能在空间数据类型的字段上建立（GEOMETRY、POINT、LINESTRING、POLYGON），字段必须声明为 NOT NULL
  • 单列索引：在表中单个字段上创建的索引，可以为普通索引、唯一索引或全文索引
  • 多列索引：在表中多个字段上创建的索引

注意：唯一性比较弱的字段上添加索引用处不大

#创建索引
UNIQUE|FULLTEXT|SPATIAL INDEX|KEY  name(长度) (ASC默认|DESC)
CREATE INDEX t_name ON emp(ename);
ALTER TABLE emp ADD INDEX xxx;
#删除索引
DROP INDEX t_name ON emp;
ALTER TABLE emp DROP INDEX t_name;

视图

定义：
视图是从一个或者几个基本表中导出的虚拟表；数据库中只存放了视图的定义，而并没有存放视图中的数据

作用：

方便，简化开发，利于维护。假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用，每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦。可以把这条复杂的SQL语句以视图对象的形式新建。

优点：

  • 可以面向视图对象进行增删改查，简化用户的操作，但会影响原表数据
  • 视图能使用户从多种角度看待同一数据
  • 视图使重构数据库具备逻辑独立性
  • 视图能够对机密数据提供安全保护。
#创建视图 仅允许DQL语句
CREATE VIEW emp_view
as
select...
#查看视图
select * from emp_view;
DESCRIBE emp_view;
SHOW TABLE STATUS LIKE 'emp_view';
#修改视图
ALTER VIEW emp_view
AS
select...
#删除视图
DROP VIEW emp_view;
#面向视图查询、插入、删除、更新
select * from emp_view;

insert into emp_view(...) values (...);

delete from emp_view;

update emp_view set xxx = where xxx = ;

DBA命令

#导入
mysqldump 数据库 表名（可指定） > D:\xxxx.sql -uroot -pxxxx
#导出
#1.创建数据库
source D:\xxxx.sql #初始化数据库

数据库设计三大范式

定义：
数据库表的设计依据

  1. 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分
  2. 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不产生部分依赖-解决多对多关系（拆分成多个表，并建立关系表，关系表设置多个外键）
  3. 建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不产生传递依赖-解决一对多关系（拆分成两张表，多的表加外键）

设计总结：

数据库设计三范式是理论上的，为了满足客户的需求，有的时候会拿冗余换执行速度，因为在sql当中，表和表之间连接次数越多，效率越低。有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。

ber困
关注
专栏目录
mysql查询包含某两个标签_如何查询同时包含多个指定标签的文章
weixin_28785657的博客
01-27 2789
文章和标签是典型的多对多的关系，也就是说每一篇文章都可以包含多个标签，如图：每一篇文章都可以包含多个标签下面问题来了：如何查询 tag_id 同时包含 1、2、3 的 article_id？此问题看似简单，实际上也非常简单，本来是一道送分题，但是很多人却做不出来！方法一：SELECT article_id FROM (SELECT article_id, GROUP_CONCAT(tag_id O...
MySQL学习笔记
12-19
### MySQL学习笔记知识点详解 #### 一、数据库概述 数据库是指按照特定的数据结构来组织、存储和管理数据的集合体。随着信息技术的进步和社会需求的增长，数据库已经从最初的简单存储发展成为用户所需的各种复杂...
Mysql 判断字段是否包含字母（大小写）
【欢迎关注公众号：冬瓜白】
04-12 3306
select * from 表名 where 字段名 REGEXP BINARY '[A-Z]';//只查包含大写字母的字段select * from 表名 where 字段名 REGEXP BINARY '[a-z]';//只查包含小写字母的字段。1、使用正则表达式来判断字段是否包含字母，不区分大小写 'a-z’或者’A-Z’都满足。select * from 表名 where 字段名 REGEXP '[A-Z]';
MySQL查询包含汉字的记录
hfsu0419的专栏
03-25 4599
<br />SELECT * <br />FROM `table_test` <br />WHERE textname REGEXP ‘[^ -~]‘ =1<br />列出英文字段记录<br />SELECT * <br />FROM `table_test` <br />WHERE textname REGEXP ‘[^ -~]‘ !=1<br />列出中文字段记录<br />select username from cdb_members where username regexp ‘[/u4e00-/
MYSQL查询同时包含两行数据的数据（用户既买了商品A又买了商品B）
Simon的博客
11-27 8892
现在有个场景： 要查询订单中，哪些订单是顾客既买了A又买了B的订单 表user_pro结构数据如下： SQL查询： SELECT * FROM user_pro T WHERE EXISTS(SELECT 1 FROM user_pro WHERE user=T.user AND product='A') AND EXISTS(SELECT 1 FROM user_pro WHERE u...
MySQL学习笔记.zip
最新发布
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
mysql学习笔记
12-16
这份“mysql学习笔记”涵盖了MySQL的基础知识，包括但不限于数据库的概念、SQL语言的使用、以及更深入的多表操作。 在“day08入门笔记”中，你可能会学到以下内容： 1. **数据库基本概念**：了解什么是数据库，...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
mysql查询某一个字段是否包含中文汉字
m0_60721514的博客
04-21 5941
在使用mysql时候，某些字段会存储中文字符，或是包含中文字符的串，查询出来的方法是： 　　SELECT column FROM table WHERE length(column)!=char_length(column) 原理其实很简单，当字符集为UTF-8，并且字符为中文时，length()和char_length()两个方法返回的结果是不相同的。 ...
mysql数据库查询包含大写字母的字段
hyb1234hi的专栏
05-14 9862
SELECT * FROM `v9_category_attribute` WHERE title_cn REGEXP BINARY  '[A-Z]';BINARY  强制区分大小写REGEXP 正则表达式
MYSQL中查询怎么判断一个字段包含英文?
Architect_CSDN的博客
10-10 7129
SELECT* from 表名 where `字段名`REGEXP'[a-z]+' 查询出的就是这个字段里包含字母的数据了
mysql 模糊查询（包含/存在）方法
风一样的小青~
07-13 8032
IN/NOT IN  可以与子查询一起使用,也可以直接in (a,b.....) EXISTS/NOT EXIST  一般都是与子查询一起使用 LIKE '%str%'    %决定匹配位置  like是广泛的模糊匹配，字符串中没有分隔符 FIND_IN_SET(str,strlist)   SELECT * from test where FIND_IN_SET('
sql where条件语句中同时包含and,or注意加括号
热门推荐
李玉峰的博客
03-17 5万+
在测试程序的时候发现sql查询语句超过范围检查sql语句发现原语句：select xx from xx where a=b and c=d or e=f;预期的结果是满足a=b的同时c=d或者e=f,忘记加括号导致范围超限加括号后select xx from xx where a=b and （c=d or e=f）;问题随小，还是值得注意...
mysql 分组取最大值的同时获取包含最大值的行的其他字段
JJmaiz-麦嘉俊-Concentrate on Web
03-25 1万+
假设有表如下： +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.6
MySQL开发-判断一个字符串里面包含几个其他的字符，逐个获取以分隔符间隔的字符串
lylyf99的专栏
10-16 1万+
一、获取一个字符串中包含几个指定的匹配字符串 在存储过程中要对一个字符中进行分割，获取里面以','分隔的各个字符串，如： '0102_001956B_1,0102_001955A_1,0102_001954C_1,0102_001953D_1,0102_001952E_1'   要获取该字符串中有几个',' ，写法如下： select length('0102_001956B_1,01...
MySQL学习笔记：DQL查询语句详解
"本篇是关于MySQL学习笔记的第六部分，主要关注DQL（Data Query Language），即查询语句的使用。通过创建一个名为`student02`的表，包含了id、NAME、age、sex、address、math和english等字段，并插入一些学生数据，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值