MySQL学习笔记

Dの

已于 2022-08-04 23:11:35 修改

阅读量413

点赞数 2

文章标签： mysql

于 2022-03-26 12:11:46 首次发布

本文链接：https://blog.csdn.net/weixin_53788591/article/details/122840154

版权

常用的命令

退出MySQL： exit
查看有哪些数据库： show databases;
使用某一个数据库： use + 数据库名称 + ;
创建一个数据库： create database + 名称 + ;
查看某个数据库下有哪些表： show tables;
查看表中的数据： select * form + 表名 + ;
查看表的结构： desc + 表名 + ;
查看当前使用的数据库： select database();
终止语句输入： \c

导入现有的数据表、表的数据

方式一：source 文件的全路径名

方式二：基于具体的图形化界面的工具可以导入数据

关键字的顺序

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

执行顺序：

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

MySQL语句

DQL（数据查询语言）

简单查询

查询一个字段：select 字段名 from 表名;
查询多个字段：使用逗号隔开
查询所有字段：使用 “ * ”（效率很低，不建议书写，建议把所有字段打出来）
给查询的列起别名：使用as关键字（只修改显示内容，不修改表中内容）as也可以省略。若起的别名中包含空格，编译报错，可以将别名用单引号或双引号括起来。
字段可以使用数学表达式进行运算。

注意：在所有的数据库中，最好使用单引号，单引号为标准语句。双引号在oracle数据库中用不了，但是在MySQL中可以使用。

条件查询

语法： select 字段1，字段2，字段3... from 表名 where 条件;

where查询中不可以使用别名进行查询，因为where的执行顺序在select之前

数据库中‘ <> ’ 符号可以代表不等号，也可以直接用 ' != ' 符号。
between .... and ...查询两个值中间的数值（遵循左小右大）
is null 查询一个值是否为null，不能用 ' =null ' 来查询，或者使用<=>NULL的方式
and 并且，or 或者（and的优先级大于or）
in 包含，相当于多个or
like 模糊查询，支持%或下划线匹配（%匹配任意个字符，一个下划线只匹配一个字符）

排序

语法：select 字段1,2,3..... from 表名 order by 字段1,2,3......；

order by 中可以使用别名进行排序

默认为升序，指定降序需要在最后加上 desc (descend)，指定升序加 asc (ascend)。

order by 后面的字段1作为主导，只有字段1相等的情况下才进行字段2的排序，字段3同理。
select 字段1,2,3..... from 表名 order by 数字；表示根据前面第几个字段进行排序。00

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

MySQL中起始下标为1，而不是0。
round(1236.567,0) == 1236//保留到整数为，round(1236.567,1) == 1236.6//保留到小数位1位，round(1236.567,-1) == 1240//保留到十位
在所有的数据库当中，只要有null参与的运算结果都为null。
ifnull(数据，被当做哪个值)

lower	转换小写
upper	转换大写
substr	取子串（substr(被截取的字符串，起始下标，截取的长度)）
length	区长度
trim	去空格
str_to_date	将字符串varchar类型转换为data类型
data_format	将data类型转换成具有一定格式的varchar字符串类型
format	格式化数字，如format(int,'$999,999')是设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换为一个具体值
concat	进行字符串拼接

分组函数/多行处理函数/聚合函数

分组函数使用的时候必须先进行分组，如果没有分组则默认整张表为一组。
分组函数自动忽略null的值，不需要对null预处理。
分组函数中count(*)和count(某个字段)
- count(某个字段)表示统计该字段下所有不为null的元素的总数。
- count(*)统计总行数，数据库中不可能存在一行记录所有的元素都为null。
分组函数不能直接使用在where子句中。(因为where函数执行的时候分组函数还没有执行)
所有的分组函数可以组合起来一起用。

count	取得记录数
sum	求和
avg	取平均
max	取最大
min	取最小

分组查询

语法：select...form...group by...

注意：在一条select语句当中，如果有group by语句的话，select后面只能跟参加分组的字段以及分组函数。

having语句可以对分完组后的数据进行进一步过滤。但having不能单独使用代替where，当过滤条件中出现聚合函数时，用having替代where。having必须与group by 联合使用，并放在group by 后面。

去除查询结果的重复记录

distinct关键字

连接查询

内连接：等值连接，非等值连接，自连接
外连接：左外连接，右外连接
全连接（很少用到）

两张表没有任何限制进行连接时会出现笛卡尔积现象，得出结果为两张表条数的乘积。

增加条件： ag：emp.deptno = dept.deptno 。但总的匹配次数并没有减少。

内连接

语法：SQL99，select e.ename,d.dname from emp e (inner) join dept d on e.deptno = d.deptno;
优点：表连接的条件是独立的，连接之后如果需要进一步的筛选，再往后添加where条件

外连接

语法：select e.ename ,d.dname from emp e right/left (outer) join dept d on e.deptno = d.deptno;
right/left表示将join关键字右/左边的表作为主表，主要是为了将这张表的数据全部查询出来，捎带关联查询了左边的表。

子查询

select语句中嵌套select语句，被嵌套的叫做子查询。可以出现在select，from，where中。
from后面的子查询可以将查询结果充当一张临时表。

union合并查询结果集

union连接的效率更高一点，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积的翻倍，但union可以减少匹配次数同时也能完成对表的连接。

注意事项：union在进行结果集合并的时候要求两个结果集的列数相同，同时若列之间的数据类型不一致在MySQL中可以运行，但在oracle中会报错。

limit提取部分查询结果

可以将查询结果集的一部分提取出来，通常使用在分页查询当中。可以提高用户体验。

语法：limited startIndex（起始偏移量），length(起始下标，长度)。也可以缺省startIndex，startIndex默认是0。

MySQL8.0新特性，limit (length) offset (startIndex)

注意事项：limited在order by 之后执行！！！

DML（数据操作语言）

插入数据

语法：insert into 表名(字段名1，字段名2.... )values(值1，值2....);

insert语句中的字段名可以省略，但值要写全
insert语句可以插入多条值，加逗号即可，values(),(),()......;

修改数据 updata

语法格式：updata 表名 set 字段名1=值1，字段名2=值2....where 条件

删除数据

语法：delete from 表名 where 条件

原理：表中的数据被清空了，但是数据在硬盘上的空间不会释放，缺点是删除慢，优点是可以回滚，后悔了可以恢复数据
快速删除(物理删除)：表被一次截断，但删除很快。语法：truncate table ....（属于DDL操作）

修改表结构

较少用到，真的要用到时建议使用工具，因为要更改大量代码，成本很高。

DDL（数据定义语言）

表的创建

语法： create table 表名(字段名1 数据类型，字段名2 数据类型....);

关于MySQL中的数据类型：很多数据类型，我们只需要掌握常见的

varchar，可变长度字符串，根据实际的数据长度动态分配空间，最长255个字节
bigint，长整型，相当于long
datatime，长日期类型，默认格式：%Y-%m-%d %h:%i:%s
date，短日期类型，只包括年月日的信息
clob，字符大对象，最多可以存储4个G的字符串，比如存储一篇文章
blob，二进制大对象，专门用来存储图片，声音，视频等流媒体数据；向其插入数据时，需要使用IO流插入。
char，float，double，int(最长11)

MySQL的日期格式：

%Y(大写) 年；%m 月；%d 日；%h 时；%i 分；%s 秒

如果日期格式是年-月-日标准格式，则不用使用str-to-data函数了

在MySQL中获取系统当前时间：new()函数，datatime类型

快速创建表：create table ... as select * from ... where....，将一个查询结果当做一张表新建，可以完成对一张表的快速复制

约束

非空约束(not null)
唯一性约束(unique)，约束的字段不能重复但可以为null，若想多个字段联合起来唯一则用unique将这些字段括起来
在MySQL当中，如果一个字段同时被非空和唯一约束，则该字段自动变为主键字段，但在oracle中不一样。
主键约束(primary key)：主键值是每一行记录的唯一标识，任何一张表都应该有主键，没有主键表无效。主键的特征是not null+unique
- 单一主键：字段名类型 primary key
- 复合主键：primary key(字段1，字段2...)
- 一张表主键约束只能添加1个
- 主键值可以用 auto_increment来维护（从1开始自增）
外键约束(foreign key)：foreign key(字段) references t_class(字段)

事务

一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分，要求语句同时成功或者同时失败。
只有DML语句才有事务这一说法，因为要修改数据，同时保证数据安全性
完成需求通常需要多条DML语句操作，所以需要事务机制来同步失败或者成功，保证数据安全
InnoDB存储引擎（默认引擎）：提供一组用来记录事务性活动的日志文件
执行start transaction语句开启事务，同时可以关闭MySQL的自动提交事务服务

索引

索引是在数据库表的字段上添加的，是为了提高查询效率的一种机制
MySQL中索引也需要排序，而且这个排序与TreeSet(TreeMap)的数据结构相同。其底层是一个自平衡二叉树，相当于一个B-Tree结构(B+树)。
在任何数据库当中的主键上都会自动添加索引。另外，在MySQL中，用unique约束的字段也会自动创建索引对象。
在MySQL中，索引是一个单独的对象，不同存储引擎以不同的方式存储。在MyISAM中，索引存储在一个.MYI的文件中。在InnoDB中，索引存储在一个逻辑名称叫tablespace当中。在MEMORY中，索引被存储在内存里。
创建索引：create index 别名 on 表名(字段名)
删除索引：drop index 别名 on 表名
查看索引：explain命令
索引失效：
- 模糊查找
- 使用复合索引时没有使用左侧的列查找，索引失效
- 在where当中索引列参加了运算或者是使用了函数

视图

view：站在不同的角度看待同一份数据

创建视图：create view 别名 as select * from 表名;(这里的语句必须是DQL语句)
删除视图：drop view 别名
对视图对象的操作，会导致原表的数据被修改
复杂的多条SQL语句重复用时，可以创建一个视图对象包含这些语句，可以大大简化开发
视图是以文件存在的，存储在硬盘上，不会消失

TCL（事务控制语言）transaction

提交事务(commit)

回滚事务(rollback)

事务之间的隔离级别（4级）

读未提交（read uncommitted）：最低级别
- 事务A可以读取到事务B未提交的数据，存在脏读现象(Dirty Read)
- 大多数数据库的隔离级别是级别而起步
读已提交（read committed）
- 事务A只能读取到事务B已提交的数据，但存在不可重复读取数据问题
可重复读（repeatable read）
- 事务A开启之后，不管是多久，每次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交，事务A读取的内容不会改变
- 问题：幻影读问题，每次读到的数据都不够真实
- MySQL中默认的隔离级别是三级
序列化读/串行化（serializable）：最高级别
- 效率很低，解决了所有问题
- 这种隔离级别是事务排队，不能并发
- 相当于线程同步，每一次读取到的数据都是最真实的