数据库mysql学习笔记

Hands_UP_

已于 2022-09-23 17:54:00 修改

阅读量1.7k

点赞数 1

分类专栏：基础知识文章标签：数据库 mysql 学习

于 2022-09-18 22:32:44 首次发布

本文链接：https://blog.csdn.net/m0_53853047/article/details/126919601

版权

基础知识专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、绪论

数据库database，按照一定格式存储数据的一些文件的组合
数据库管理系统databaseManagment，对数据库中的数据进行增删改查
常见的数据库管理系统：Mysql、Oracle、Ms Sqlserver、DB2等
SQL：结构化查询语言，程序员通过编写SQL语句，数据库管理系统dbms负责执行SQL语句，最终完成数据库中数据的增删改查
下载安装mySql后查看mySql服务：右键此电脑--管理--服务和应用程序--服务--找mysql服务

二、mysql基础服务和mysql基础常用命令

ps：命令不区分大小写

在windows端启用和关闭某服务：net start 服务名； net stop 服务名
在命令解释器（win+r输入cmd）登录mysql：mysql -u root -p ；exit退出
查看mysql中有哪些数据库：show databases；（要以英文分号结尾）
数据库最基本的单元是表，以表的形式表示数据，行被称为数据/记录，列被称为字段
使用某个数据库：use 数据库名；
创建一个数据库：create database 数据库名；
查看某个数据库下有哪些表：use某个数据库名后，输入show tables；
sql语句分类：

DQL：数据查询语言（带select）

DML：数据操作语言（对表中的数据增删改）insert；delete；updata

DDL：数据定义语言（修改表的结构）create新建；drop（删除）；alter（修改）

TCL：事务控制语言 commit事务提交；rollback（事务回滚）

DCL：数据控制语言 grant（授权）；revoke（撤销权限）

ps：mysql输入语句输入分号才执行，若无分号，则会等待继续输入，或\c结束
查看mysql数据库的版本号 select version（）；
查看当前使用的是哪个数据库 select database（）；
导入sql文件：新建一个数据库或使用已有的数据库use，输入 source：文件路径

dept是部门表，emp是员工表，salgrade是工资等级表

查看表中的数据 select * from 表名； *表示所有
不看表的数据，只看表的结构 desc 表名；

三、DQL语句

1.简单查询

简单查询，select 字段名 from 表名；查询多个字段时，字段名用逗号隔开即可

给查询的列起别名，查询段名后加as 别名；ps: as可以省略，别名可以用单引号括起来
字段名可以参与数学运算

2.条件查询

条件查询格式：select 字段名 from 表名 where 条件；数据库中null要用is衡量不能用=
条件逻辑：and并且、or或者，and的优先级比or高，用（）解决优先级问题；in包含，相当于多个or
like模糊查询，支持%或下划线匹配，%匹配任意多个字符，下划线任意一个字符

3.数据排序

order by 字段名 asc/desc
多个字段排序，第一个字段相等时按第二个字段排：order by 第一个字段 asc/desc，第二个字段 asc/desc；

4.数据处理函数/单行处理函数

lower、upper转大小写
substr（被截取的字符串，起始下标/1开始，截取长度）
length取长度
trim去空格
round（数字，保留小数位位数）四舍五入
rand生成随机数
ifnull（数据，数据若为null则替换的值），数据库中null参与与运算的结果总是null
case...when...then...when...then...else...end

5.分组函数/多行处理函数

分组函数在使用时必须先进行分组，若没有分组，整张表默认为一组，分组函数会自动忽略null；count(*)表示统计总行数；分组函数不能直接在where子句中
count、sum、avg、max、min
关键字输入顺序和执行顺序：select ...from...where...group by...order by...;执行顺序：from-where - group by -s elect - order by
对分组查询后的数据进行进一步过滤使用having（where可以解决的优先用where）：
去除重复distinct：

6.连接查询

6.1 内连接

概念：多个表联合起来查询，显示匹配的结果
笛卡尔积现象：当两张表进行连接查询，没有任何限制的时候，最终查询的结果条数是两张表条数的乘积
带条件查询后结果条数减少了，但匹配次数没有减少
表起别名：上条命令可以写为：sql92：select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
sql99语法：上条命令写为：select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
自连接：

6.2 外连接

right/left表示将join关键字右边/左边的这张表看成主表，将这张表的数据全部查询出来，若有未查询到的，用null
多张表连接：select ... from a join b on a和b的连接条件 left join c on a和c的外连接条件；

7.子查询

select语句中嵌套select语句，被嵌套的select语句成为子查询

8. union合并查询结果集

union特点：效率高，可以减少连接的匹配次数，union在连接时要求两个结果的列数和列的数据类型相同

9.limit

limit是将查询结果集的一部分取出来，通常使用在分页查询中
用法：limit satrtindex,length; / limit length ; 默认起始下标为0
分页公式：limit (pageno-1)*pagesize,pagesize

四、DDL

1.建表

语法：create table 表名（字段名数据类型，字段名数据类型）；
数据类型：varchar（可变字符串）、char[]（定长字符串）、int、bigint（long）、float、double、data（短日期类型）、datatime（长日期类型）、clob（字符大对象，存储大文章等）、blob（二进制大对象，存储图片、视频等）
删除表：drop table 表名； / drop table if exists 表名 ;
删除表内容truncate（不同于delete）： truncate table 表名；
delete删除原理是一条条的删除记录，但不会删除物理存储空间，支持回滚rollback，truncate为直接删除物理存储空间，不支持回滚
alter对表结构修改，或用工具如navicat

五、DML

1.insert插入数据

insert语法：insert into 表名（字段名1，字段名2...） values（值1，值2...）;
insert插入多条记录
str_to_date函数进行类型转换：将字符串转换为日期类型，格式：str_to_date（‘字符串日期’，‘日期格式’） &Y、&m、%d、%i、%s
date_format（日期类型，‘日期字符串格式’）：将日期转换为指定格式字符串
date和datetime：date短日期只包括年月日，datetime长日期包括年月日时分秒，查询当前时间：now（）获得时间为datetime类型

2.update修改

update修改：update 表名 set 字段名1=值1，字段名2=值2... where 条件；

3.delete删除

delete删除记录：detele from 表名 where 条件；

4.快速创建和插入

快速创建表/复制表：create table new-table as select * from old-table；
将查询结果插入另一个表中：insert into table1 as select 字段名1... from table2;

5.约束

创建表时给表的字段后加一些约束（字段名类型约束），保证表中数据的完整性和有效性，约束类型：非空约束（not null）、唯一性约束（unique）、主键约束（primary key）、外键约束（foreign key）、检查约束（check）
not null：非空约束约束的字段不能为null
unique：唯一性约束约束的字段内容不能重复，但可以有多个为null的内容，要想两个字段联合起来唯一，需在创建字段后，再加unique（字段名1 类型，字段名2 类型，unique（字段名1，字段名2））此约束成为表级约束；
not null和unique可以联合约束一个字段，在mysql中该字段自动变为主键
primary key：被主键约束约束的字段成为主键字段，主键字段中的每一个值都叫做主键值，主键值是每一行记录的唯一标识，任意一个表都要有主键，没有主键该表无效，特征：not null+unique，可以使用表级约束，但一张表主键约束只能有一个
自然主键和业务主键：自然主键为一个自然数和业务没关系
主键可以用auto_increment自动进行自增（id int primary key auto_increment,）
外键：被外键约束的字段可以与另一个表的某个字段关联，此时外键约束的这个字段值只能是另一个表中的指定字段全部值的其中的值：语法 create table student（foreign key（sno） references 另一个表名（指定关联字段名），），指定关联字段至少要有unique约束，外键值可以为null

六、存储引擎

1.存储引擎概述

存储引擎是一张表存储/组织数据的方式
show create table 表名；可以查看表创建的详细细节
在创建表时可以指定存储引擎engine和字符串默认编码方式default charset
查看当前数据库有哪些引擎：show engines \G；

2.MyISAM存储引擎

使用三个文件表示每个表：格式文件.frm存储表结构的定义、数据文件.MYD存储表行的内容、索引文件.MYI存储表上索引用于提高查询效率
可被转换为压缩、只读表节省空间
不支持事务，安全性低
对于一张表，只要有主键或有unique约束就会自动创建索引

3.InnoDB存储引擎

支持事务，支持数据库崩溃后自动恢复机制
每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace用于存储表的内容（数据+索引）
提供一组用于记录事务性活动的日志文件
用commit、savapoint、rollback支持事务处理

4.MEMORY存储引擎

使用该引擎的表，数据存储在内存中，且行的长度固定，因此查询效率高
每个表均以.frm格式的文件表示
不能含有text和blob字段

七、事务

1.概述

一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分
只有insert、delete、update三个语句和事务有关
事务的执行过程中，每一条DML操作都会记录到事务性活动的日志文件中，在事务的执行过程中，可以提交事务或回滚事务
提交事务：commit；清空事务性的日志文件，并将数据全部持久化到数据库中
回滚事务：rollback；清空事务性的日志文件，并撤销之前所有的DML语句，rollback只能回滚到上一次commit的位置
在mysql中默认自动提交，即一条命令执行后自动commit，可以用start transaction关闭自动提交
事务的特性：原子性、一致性（事务中所有的操作成功或失败是一致的）、隔离性、持久性

2.事务的隔离性

事务的隔离级别（从低到高）：

读未提交（read uncommitted，事务a可以读取到事务b未提交的数据），存在脏读现象
读已提交（read committed，事务a只能读到事务b提交过的数据），存在不可重复读取数据，每次读取的数据不一定相同，oracle默认使用此级别
可重复读（repeatable read），事务a开启后，只要事务a不结束，不论过多久，每次a读取到的数据都是一致的，即使事务b修改了数据，a读取到的数据依然不变，存在幻影读
序列化/串行化（serializable），事务排队，不可并发，效率最低
查看当前隔离级别：select @@transaction_isolation;
设置隔离级别：set global transaction isolation level 级别；