MySQL笔记【基础篇】
1|0MySQL笔记
相关术语:
- DB :数据库:在硬盘上以文件的方式存在
- DBMS:数据库管理系统:例如mysql,oracle,sqlserver…
- SQL:一门适用于所有DBMS,对DB进行操作的标准规划语言,
- DQL:数据库查询语句 select,
- DML:数据库管理语句,修改表中数据update,insert,delete,
- DDL:数据库管理语句,修改表中的结构drop,create,alter,
- TCL:commit提交事务,rollback回滚事务,
- DCL:grant授权,revoke撤销权限
1|1一.DOS窗口命令
- DOS窗口进入数据库 mysql -uroot -p密码
- 查看数据库:show databases;
- 创建数据库:create database 数据库名字;
- 使用某个数据库:use 数据库名字;
- 查看使用数据库中的表:show tables;
- 数据库初始化执行sql文件:source +sql文件路径(直接拖就行)
- 删除数据库:drop database 数据库名字;
- 查看表结构:desc+表名;
- 查看表中数据:select * from 表名;(当在DOS界面中显示MYSQL数据库中的表的信息时,可能会出现中文乱码问题,出现是原因是因为DOS窗口默认字符集为GBK格式,如果当前MYSQL设置的默认编码格式非GBK格式,则可能会出现乱码。
解决方法:在输出信息前,执行以下语句:
set names gbk;) - 查询当前使用的哪个数据库:select database();也可直接再use一下需要的数据库;
- 查询mysql的版本号:select version();
- 结束一条语句:\c 命令
- 退出mysql:exit 命令
- 查看创建表的时候使用的sql语句:show create table 表名;
注意:以下操作都是基于如下sql脚本进行的,建议先导入sql文件,一边操作一边看!
1|2二.DQL
单表简单查询:
1.简单的查询语句(DQL)
语法格式:select 字段名1,字段名2 *12,字段名3 as ’表名‘ ,… from 表名;
-
字段二表示字段可以进行数学运算;
-
字段三表示可以重新为该字段命名,字符串用单引号 (as可以省略,用空格替换!);
2.查询所有字段:
select * from 表名 注意:实际开发中不建议*因为效率较低
3.条件查询格式:
select 字段,字段…from 表名 where 条件;条件(> ,>=,<,<=,<>或者!=,between 小数字 and 大数字 ,)
运算符 | 说明 |
---|---|
= | 等于 |
<> 或 != | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between … and … | 两个值之间 |
is null | 为null(is not null不为空) |
and | 并且 |
or | 或者 |
in | 包含,相当于多个or(not in 不在这个范围中) |
not | not可以取非,主要用在is或in中 |
like | like成为模糊查询,支持%或_匹配 |
in后面不是区间而是具体的值in(800,1000)意思是要么800要么1000
4.模糊查询like
(必须掌握两个符号,一个是%,一个是_)
%表示任意多个字符,__代表一个字符。如果_或%需要转化为普通字符就在前面加‘ \ ’ 起到转义的作用。
5.排序(升序和降序):
默认指的是升序
格式:select 字段1,字段2,from表名 order by 字段名 asc(升序) ;
select 字段1,字段2,from表名 order by 字段名 desc(降序) ;
如果按照某个字段排序,而该字段又相等则再在后面的字段加上,第二个字段名 asc/desc;
例如:select 字段1,字段2,from表名 order by 字段名1 desc,字段名2 asc;(越靠前的字段越能起主导作用,order by后面是最后执行的)
6.分组函数:
别名:多行处理函数
所有的分组函数都是对某一组函数进行操作的 注意:分组函数自动忽略null,统计不上并且分组函数不能写在where语句后面,分
组函数即使没有group by也自己成为一组,where的优先级高于group by,所以where不执行完,分组函数执行不了。
- count计数:select count(*) from 表名; //统计的是总记录条数。
select count(字段名)from 表名; //统计的是当前字段不为null的数据条数。
-
sum求和:select sum(字段名) from 表名;
-
avg平均值:select avg(字段名) from 表名;
-
max最大值:select max(字段名) from 表名;
-
min最小值:select min(字段名) from 表名;
7.单行处理函数:
ifnull(可能为null的数据,被当作什么处理);
例如:ifnull(sid,0);如果sid为null,那么将其当作0来处理;
8.group by 和 having
group by:按照某个字段或者某些分段进行分组。
例子:select 分组函数 from 表名 group by 字段名; 执行顺序:from-> group by -> select。
注意:
-
分组函数一般都是和group by联合使用,这也是为什么称之为分组函数的原因,并且任何一个分组函数(count,max,avg,
min,sum)都是在group by执行完成后才执行。
-
如果一个sql语句后面有group by则查询的字段字段只能有分组字段,和分组函数。
having:having是对分组之后的数据进行再次过滤。可以理解作为优先级低的where;
9.distinct去重记录:
select distinct 字段1,字段2 from 表;使得字段1和字段2联合起来去重。
注意:distinct必须出现在所有字段的最前面。不可:select 字段1,distinct 字段2 from 表;
count(diatinct 字段),先为字段去重,再统计数据条数;
10.总结完整DQL语句
select …from…where…group by…having…order by;
以及查询的顺序;
提示:
1.任何一条sql语句都以’’ ; ''结尾;
2.sql语句不区分大小写;
3.字符串用单引号括起来;
4.任何值与null进行运算结果都是null;
多表连接查询:
1.什么是连接查询:
在实际开发中大部分都不是从一张表中查询数据,一般都是多张表联合查询取得结果。
实际开发中,一般一个业务对应多张表。比如:学生和班级,起码两张。(因为一张表可能会出现冗余的情况,即数据大量重复!)
2连接查询的分类:
1.根据出现年代来划分,包括:
-
sql92(一些老的DBA还在使用的语法,已经被淘汰)
-
sql99(比较新的语法)
2.根据表的连接方式划分,包括:
-
内连接: 等值连接
非等值连接
自链接
-
外连接: 左外连接(左连接)
右外连接(右连接)
-
*全连接(很少用,了解)
3.笛卡尔积现象:
在表的连接查询方面的一种现象:当两张表进行查询的的时候,没有任何条件限制,最终的查询结果条数是两张表的乘积。
关于表别名,使用表别名有什么好处吗?
- 执行效率高
- 可读性好
4.避免笛卡尔积现象
答:当然是加条件了。
思考:避免了笛卡尔积会减少记录的匹配次数吗?
不会,还是匹配一样的次数,但是只显示有效条数。(并不能提高效率)
5.内连接
1、等值连接
语法:(inner)join …on…
inner可省略,带着只为可读性好一些。两张表内连接的意思 (常用)
sql99语法:使得表的连接条件和后来的过滤条件分离了!
2、非等值连接
sql99语法:表连接条件并不是一个确切的值!
3、自连接
最大特点:一张表看作两张表,自己连自己
6.外连接
语法格式:left|right(outer)join … on …
outer可省略,带着只为可读性好一些。两张表外连接的意思
-
左外连接(左连接):表示左边是主表。
-
右外连接(右连接):表示右边是主表。
左连接有右连接的写法,同样右连接也有左连接的写法 :
案例:查询没有员工的部门
7内连接和外连接的区别:
- 内连接:假设有AB两张表,使用内连接,凡是A表B表能够匹配上的记录查询出来,这就是内连接,AB两张表并没有主次之分,两张表是平等的。
- 外连接:假设AB两张表,使用外连接,AB两张表中的一张是主表,一张是副表,主要查询主表中的数据,捎带查询副表,当副表中的数据并没和主表中的数据匹配上,副表自动模拟出NULL与之相匹配(主表数据无条件查询)
8.三个表连接查询
案例一:查询每个员工的部门名称和薪资等级
案例二:查询每个员工的部门名称、薪资等级、上级领导
子查询:
定义:select语句中嵌套select语句,被嵌套的select语句是子查询。
子查询可以出现在:
1.where后面嵌套子查询
2.from后面嵌套子查询*
3.在select 后面嵌套子查询
找出每个员工所在的部门名称
4.union
可将查询结果集相加
而且两张不相干的表中数据也能够因此拼接在一起。(但是列数保持一致)
5.*limit
重点中的重点,分页查询全靠它
1.limit是mysql中特有的,其他数据库中没有,但是Oracle中有一个相同的机制叫rownum。
2.limit取结果集中的部分数据
3.语法机制:limit startIndex,length
startindex表示起始位置,从0开始,0表示第一条数据。
length表示取几个。
4.limit是sql语句中最后一个执行的环节。
(当前页数-1)* 页的大小 = 当前页起始下标
1|3三.DDL和DML
1.创建表:
char 和 varchar 如何选择?
- char 适用于数长度不会发生改变的时候,是定长的,例如:性别,生日
- varchar 当一个字段数据长度不确定,例如:简介、姓名、等都是采用varchar
char是直接开辟一定长度的空间,varchar是根据存储数据的长度动态的开辟空间!
测试案例
2.删除表:
使用drop删除表,就像是我们把表中的数据擦干净,但是表的结构还在,可以通过rollback回滚过来数据!
我们如果需要删除大表的话,就需要truncate 来删除,会清空所有数据且执行速度很快,并且永久删除,数据不可恢复!
删除表常用:
3.修改表结构:
用工具:哈哈!设计表选项
(因为在实际开发中表设计好以后,对表结构进行修改是很少的。并且修改表中结构的语句并不会写在java语句当中。)
4.insert:表中插入数据
语法:insert into 表名(字段1,字段2,字段3…)values(value1 , value2 , value3) ;
需要注意的地方:当一条insert语句执行成功之后,表格必然会多一行记录,及时多的一行记录当中某些字段为null,后期也没办法再通过insert插入数据了,只能通过update更新 !
**一次插入多行数据:**insert into 表名(字段1,字段2,字段3)values(value1,value2,value3),(value1,value2,value3)
5.表的复制
了解一下即可!
将查询出来的结果插入道一张表中
6.update:修改表中数据
语法格式:update 表名 set 字段名1 = 值1 ,字段名2 = 值2 …where 条件 ;
注意:没有条件整张表的数据全部更新 ;
7.delete删除表中数据;
语法格式:delete from 表名 where 条件 ;
注意:没有条件表中数据全部清空!
8.增删改查有一个术语:CRUD操作
-
Create(增加)
-
Retrieve(查询)
-
Update(修改)
-
Delete(删除)
1|4四.约束
创建表的时候可以给字段添加相应的约束,约束的目的:保证表中数据的合法性,唯一性,有效性。
-
非空约束(not null):约束字段不能为NULL
-
唯一约束(unique):约束字段不能重复
-
主键约束(primary key):约束字段既不能为NULL也不能重复
-
外键约束(foreign key):阿里巴巴手册,已经禁止使用外键了
-
检查f约束(check):Oracle数据库有check约束,但是Mysql没有。
注意:我们主要还是记住英文,平时使用的都是英文!
1.非空约束 not null
测试
2.唯一约束(unique)
测试
3.主键约束(primary key) *
必须记住:一张表的主键约束只有一个 ;最好与别的字段不相关,仅仅代表唯一标识 !
测试
【表级约束】
主键值自增:在primary key后面添加auto_increment(从1开始,递增);
了解:Oracle也提供一个自增序列叫做序列(sequence);
4.外键约束(foreign key)
关于外键约束的相关术语
- 外键约束:foreign key
- 外键字段:添加有外键约束的字段
- 外键值:外键字段中的每一个值。
t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表。t_class表叫做父表。
顺序要求:
- 删除数据的时候,先删除子表,再删除父表。
- 添加数据的时候,先添加父表,在添加子表。
- 创建表的时候,先创建父表,再创建子表。
- 删除表的时候,先删除子表,在删除父表。
使用外键【Alibaba规范不推荐使用】
-
reference是引用;
-
外键可以为NULL;
-
此时classno中的数据来自cno;
-
外键引用的字段不一定是主键,但是起码是被unique约束;
1|5五.存储引擎
表在数据库中的存储方式。
存储引擎只存在mysql中,(Oracle中有对应机制,但是不叫存储引擎)。
完整的建表语句:
注意:在MYSQL当中,凡是标识符可以使用飘号括起来,最好别用,不通用!
建表的时候可以指定存储引擎,也可以指定字符集。
mysql默认的存储引擎是InnoDB的方式!默认采用的字符集是UFT-8
1.常见的存储引擎:
-
MyISAM 存储索引
-
InnoDB 存储索引
-
MEMORY 存储索引
2.MyISAM 存储引擎:
MyISAM:这种存储引擎不支持事务。是最常用的但不是默认的
它管理的表具有以下特征:
使用三个文件表示每个表 :
- 格式文件 — 存储表的结构(mytable.frm)
- 数据文件 — 存储表的数据(mytable.MYD)
- 索引文件 — 存储表中索引的文件(mytable.MYI)
优点:可被转换为压缩,来节省空间,并且可以转换为只读表,提高检索效率!
缺点:不支持事务!
3.InnoDB 存储引擎:
支持事务,行级锁,外键等 ;这种存储引擎安全。
- 表的结构存储在xxx.frm文件当中
- 数据存储在tablespace这样的表空间当中(逻辑概念),无法被压缩,无法被转换为只读!
- 这种InnoDB存储引擎在MYSQL数据库崩溃之后提供自动恢复机制!【事务、安全、重量级】
- 支持级联删除和级联更新。
4.MEMORY 存储引擎:
所有数据保存在内存中,断电即失,但是查询速度超级快!
- 在数据库目录当中,每个表均已.frm格式的文件表示
- 表数据以及索引被存储在内存当中
- 表级锁机制
- 不能包含TEXT(CLOB)或BLOB字段
- 查询速度快
1|6六.事务(Transaction)*
1、什么是事务
一个事务是一个完整的业务逻辑单元,不可再分。
比如:银行转账,从A账户向B账务转账10000,需要执行两条update语句
以上两条DML语句必须同时成功,或同时失败,不允许一条成功,一条失败!
想要保证以上的两条DML语句(update、insert、delete)同时成功或者同时失败,那么就需要使用数据库的“事务机制” !
2、事务原理
假设一个事儿,需要先执行一条insert,再执行一条update,最后执行一条delete,这个事儿才算完成。
开启事务机制(开始)
- 执行insert语句–>insert.(这个执行成功之后,把这个执行记录到数据库的操作历史当中,并不会向文件中保存-条数据,不会真正的修改硬盘上的数据。)
- 执行update语句—> update…(这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据)
- 执行delete语句---->delete…(这个执行也是记录一下历史操作【记录到缓存】,不会真正的修改硬盘上的数据)
提交事务或者回滚事务(结束)
怎么提交事务,怎么回滚事务?
提交事务:commit;语句
回滚事务:rollback;语句==(回滚永远都是只能回到上次事务的提交点!)==
MySQL默认的事务行为是怎么样的?
MySQL默认情况下是支持自动提交事务的。(自动提交):就是一条DML提交一次!
怎么样才能将MySQL的自动提交事务关闭呢?
先执行如下命令:start transaction
,开启事务,也是将自动提交事务关闭!
在次命令后的指定的DML语句并不会持久化到数据库,等待commit
| rollback
操作!
3、事务的特性:ACID
A:原子性(atomicity):事务是最小的工作单元,不可再分。
C:一致性(consistency):事务必须保证多条DML语句同时成功或同时失败。
I :隔离性(isolation):事务A与事务B之间具有隔离。
D:持久性(durability):持久性说的时最终的数据必须持久化道硬盘文件中,事务才算成功的结束。
4、事务的隔离性
将事务A与事务B分别比作教室A,B,然而它们之间的墙就是隔离性,墙越厚隔离级别越高!(这种墙分为四个级别)
-
第一级别:读未提交(read uncommitted):我们当前事务可以读取对方未提交的事务,
存在问题:存在脏读(Dirty read)现象,表示读到了脏的数据。
-
第二级别:读已提交(read committed):我们当前事务可读取对方已提交的事务,解决了脏读现象,存在问题:不可重复读。
-
第三级别:可重复读(repeatable read):解决不可重复读,永远读取到的都是开启事务时的数据,==存在问题:读取到的数据是幻像(幻读)==例如:执行一条查询的sql需要从1点3点,那么这条sql读取的一直是1点的数据,即使13点之间存在其他操作,该sql的结果并不会受到影响,类似快照,一直读取的是快照中的数据。
-
第四级别:序列化读/串行化读(serializable):解决了所有问题。 效率低,需要事务排队(类似于synchronized)。
Oracle数据库默认级别:第二级别;读已提交
Mysql数据库默认级别:第三级别,可重复读
5、验证各种隔离级别
对以上各种隔离级别做一个验证
读未提交(read uncommitted):
读已提交(read committed):
测试 读已提交(read committed)
可重复读(repeatable read):
测试:可重复读(repeatable read)
序列化读/串行化读(serializable):
测试 序列化读/串行化读(serializable)
1|7七.索引 *
1、索引的概述
索引相当于一本书的目录,通过目录可以快速找到对应的资源。
作用:提高检索效率,缩小扫面范围。
注意:索引虽然提高效率但是依旧不能随意添加,因为索引也是数据库中的对象,也是需要数据库不断的维护的,有维护成本,表中数据经常修改就不适合,因为一旦修改索引需要重新排序,进行维护。
2、怎么创建索引对象
语法格式:create index 索引名称 on 表名 (字段名)。
3、怎么删除索引对象
语法格式:drop index 索引名称 on 表名;
4、什么时候给字段添加索引
- 数据量庞大。(根据客户需求,根据线上的环境。)
- 该字段很少的DML操作。
- 该字段经常出现在where子句中。
5、sql语句的执行计划
主键和具有unique约束的字段自动添加索引,根据主键查询效率较高,尽量根据主键检索。
==explain+sql语句可以解释该语句。(执行计划)==可以通过此命令判断sql是否走了索引!
6、索引底层采用的数据结构是:b+tree
索引的实现原理
案例分析
7、索引分类
- 单一索引:给单个字段添加索引
- 复合索引:给多个字段联合起来添加一个索引
- 主键索引:主键上会自动添加索引
- 唯一索引:有unique约束的字段上会自动添加索引
8、索引什么时候失效 ?
1、模糊查询,这个时候索引失效,因为以%开始,无法使用索引!
2、使用or的时候会索引失效,因为如果or两端的字段存在一个字段不具备索引,具备索引的字段也会失效!
3、使用复合索引,没有使用左侧的列去查找,索引失效
4、where当中的索引列参加了运算,索引失效。
5、在where当中索引列使用了函数
1|8八.视图(view)
1.视图的概念
站在不同的角度看到数据。(同一张表的数据,通过不同的角度去看待。)
2.创建/删除视图的sql
注意:只有DQL语句才能以视图对象的方式创建出来;
3.对视图进行CRUD操作
对视图进行增删改查,会影响到原表数据。
注意:视图对象时保存在硬盘上的,而非保存在内存当中,所以不会消失!
4.视图的作用
思考:假设有一条复杂的SQL语句,而且这条SQL语句需要在不同的地方反复使用,每一次使用都写一遍,很长很麻烦,怎么办?
答:可以把复杂的SQL以视图的形式展示出来,我们需要执行复杂的SQL时,只需查询该SQL所映射的视图即可!并且这种方式很容易后 期的 维护,我们只需改动原SQL,重新创建视图即可!
视图可以隐藏表的实现细节,保密级别较高的系统,数据库只对你提供相关的视图java程序员只能对视图进行CRUD。(并不会提高视图的检索效率)
1|9九.DBA命令(了解)
掌握以下即可,其他需要再查!
1.将数据库导出
2.向数据库导入数据
1|10十.数据库设计三范式(重点)
设计表的依据,按照这个三范式设计的表不会出现数据冗余。
1、三范式都是那些
- 第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分。【核心】
- 第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖。
- 第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖。
声明:三范式是面试官经常问的,所以一定要熟记在心!
设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费。
2、必备口诀
多对多,三张表,关系表两个外键。
一对多,两张表,多的表加外键。
一对一两种方案:主键共享,外键唯一。
提醒:实际的开发中,以满足客户需求为主,有时候会拿冗余换执行速度。
1|11三十四道练习题
1、取得每个部门最高薪水的人员名称;
2、哪些人的薪水在部门的平均薪水之上;
3、取得部门中(所有人的)平均的薪水等级;
4、不准用组函数(Max)取得最高薪水
5、取得平均薪水最高的部门的部门编号
6、取得平均薪水最高的部门的部门名称
7、求平均薪水等级最低的部门的部门名称
8、取得比普通员工(员工代码没在mgr字段出现的)的最高薪水还要高的领导人姓名;
注意(比“普通员工最高薪水”还要高的一定是领导)
9、取得薪水最高的前五名
10、取得薪水最高的第6名刀到第10名员工
11、最后入职的五名员工
12、取得每个薪水等级有多少个员工
13.列出所有员工以及领导的名字
14、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
15、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
16、列出至少有五个员工的所有部门
17、列出薪金比 SMITH多的所有员工信息
18、列出所有CLERK(办事员)的姓名,部门名称,部门的人数。
注意:对两张临时表,进行连接查询!(目前最复杂)
19、列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
20、列出在部门sales(销售部)工作的员工的姓名,假定不知道销售部的部门编号
21、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
22、列出与SCOTT从事相同工作的所有员工以及部门名称
23、列出薪金等于30部门中员工的薪金的其他员工的姓名和工资
24、列出薪金高于部门30工作的所有员工的薪金的员工姓名,薪金,部门名称
25、列出在每个部门工作的员工数量,平均工资和平均服务期限
27、列出所有员工的姓名,部门名称和工资
28、列出所有部门的详细信息和人数
29、列出各种工作的最低工资以及从事此工作的雇员姓名
30、列出各个部门的MANGER(领导)的最低薪资
31、列出所有员工的年工资、按照年薪从低到高排序
32、求出员工领导的薪水超过3000的员工名称与领导名称
33、求出部门名称当中,带’S’字符的部门员工的工资合计、部门人数
34、给任职超过30年的员工加薪10%
关于存储过程、游标、索引原理、存储引擎、事务的文章接着就来!
__EOF__

本文链接:https://www.cnblogs.com/qxsong/p/15837238.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库