MySQL

最新推荐文章于 2024-07-02 18:27:30 发布

原创最新推荐文章于 2024-07-02 18:27:30 发布 · 599 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #sql

Java 同时被 2 个专栏收录

10 篇文章

订阅专栏

MySQL

3 篇文章

订阅专栏

MySQL

本文根据尚硅谷的教学视频完成，如有不全请见谅

https://www.bilibili.com/video/BV1xW411u7ax

DBMS

数据库的管理软件，方便管理

基本命令

flush privileges   --刷新权限
show databases  --查看所有的数据库
mysql> use   school  --切换数据库
show tables  --查看数据库中所有的表
describe student  --显示数据库中的所有的表的信息
create  database student --创建一个数据库
exit；   --退出连接
-- 单行注释
/*
多行注释
*/

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库

操作数据库>操作数据库的表>操作数据库表的数据

– tab键的上面，如果你的表名或字段名是一个特殊的字符

创建数据库
```
create database [if not exists] westos
```
删除数据库

drop database [if exists ] westos
使用数据库

数据库数据类型

数值

tinyint 十分小的数据一个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据三个字节
int 标准整数 4字节
big 较大数据 8字节
float 浮点数四字节
double 浮点数八字节
decimal 字符串形式的浮点数金融一般使用字节

字符串

char 字符串固定大小 0——255
varchar 可变字符串 0——65535 常用的string
tinytext 微型文本 2 ^8-1
text 文本串 2^16-1 保存大文本
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合

时间日期

date YYYY-MM-DD 日期
time HH:mm:ss 时间格式
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳较为常用
year 年份表示

null

没有值未知

**注意：不要使用null进行运算**

数据库的字段属性

Unsigned：无符号的整数

声明该列不能为负数

zerofill：0填充的

不足的位数，使用0来填充

自增：通常理解为自动在上一条记录的基础上+1

通常用来设计为唯一的主键-----index，必须是整形类型

可以自定义涉及主键自增的起始值和步长

非空 Null not null

假设设置为not null，如果不给它赋值，就会报错
NULL，默认值就是null

默认
- 设置默认值

做项目每一个表都必须要有以下的五种

ID 主键
version 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

CREATE TABLE IF NOT EXISTS ` student` (
  `id` INT (4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR (20)  DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR (2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR (100) DEFAULT NULL COMMENT '家庭住址',
   `email` VARCHAR (50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)` student`
) ENGINE = INNODB DEFAULT CHARSET = utf8

创立一个表记录student的信息

常用命令

show create database school 产看创建数据库的语句

show create table school.student 产看student数据库的定义语句

desc school.student 显示表的结构

数据库的引擎

innodb 默认使用

myisam 早年使用

	myisam	innodb
事务支持	否	是
数据行锁定	否	是
外键约束	否	是
全文索引	是	否
表空间大小	小	大（约2倍myisam）

myisam 节约空间，速度较快
innodb 安全较好，多表多用户操作

所有的数据库文件都是以文件方式存储

MySQL引擎在物理文件上的区别

InnoDB 在数据库中的一个.frm文件，以及上级目录
MYISAM对应文件
.frm 表结构的定义文件
.MYD 数据文件

mysql默认编码是Latin1 ，不支持中文

ENGINE = INNODB DEFAULT CHARSET = utf8

修改删除表

修改

修改表名

Alter table teacher rename as teacher1

形式：Alter table 旧表名 rename as 新表名

增加表的字段

Alter table teacher1 add age int（11）

形式：Alter table 表名 add 字段名数据类型（列属性）

修改表的字段（重命名）

Alter table teacher1 modify age varchar(11)

形式：Alter table 表名 modify 字段名数据类型（列属性）

Alter table teacher1 change age int（1）——字段重命名

删除表的字段

Alter table 表名 drop 字段名

删除

drop table if exists teacher

DQL语言

基础查询

语法：select查询列表 from 表名

查询字段，常量值，表达式，函数

要查询时先打开该表存在的数据库

查询常量值

SELECT 2;
SELECT ‘大四’;

查询表达式

SELECT 5*5;

查询函数：SELECT version（）

起别名：SELECT 100%98 AS 结果;

SELECT 字段1 新名,字段2 新名 from 表名;

查询的字段有重名，使用别名区分

去重：SELECT distinct 字段 from 表名;

mysql中的+是运算符
在这里插入图片描述

select null+10；只要其中一方是null，则结果也为null；

select concat（‘a’,‘b’,‘c’）as 结果;

条件查询

select 查询列表 from 表名 where 筛选条件运算符：< > = !=或<> >= <=

在这里插入图片描述

select * from
student
where name like ‘%姆%’;

SELECT FROM student
WHERE id between 1 and 4;

使用between and提高语句简洁度

包含临界值，不能调换顺序

in：判断某字段的值是否属于in列表的某一项

安全等于 <=> 代表等于 a<=>null 缺点：可读性差约等于是否等于

排序查询

语法：select 查询列表 from 表

【where 筛选条件】

order by 排序列表【asc（小到大）|desc（大到小）】

常见函数

概念：类似Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

单行函数：字符函数，数学函数，日期函数，其他函数，流程控制函数

字符函数

length函数：获取参数的字节个数

concat：凭借字符串
在这里插入图片描述

upper、lower：大小写转换

substr、substring：索引（从1开始）

SELECT SUBSTR(‘毒素好好的双方各户’,1,2);

2 是截取的长度

instr：返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR (‘说不定给出的哦爱词霸’,‘不定’) AS out_put;

trim：去除其他的字节

select
length(trim(’ 长大后 ')) as out_put;

lpad：左填充指定字符 rpad：右填充

SELECT LPAD(‘殷素素’,10,’*’) AS out_put;

replace：替换

select replace（‘字段1&字段2’,‘字段1’,‘字段3’）；

数学函数

round：四舍五入

SELECT ROUND (1.567,2);保留两位小数

ceil：向上取整，返回>=该参数的最小的整数

floor：向下取整

truncate：截断后面的小数

mod：取余

日期函数

now ：返回当前系统的日期和时间

curdate：发挥当前的日期，没有时间

curtime：……

str_to_date:将日期格式的字符转换成指定的日期

date_format:将日期转换成字符

流程控制函数

if函数：SELECT name,birthday,IF(birthday>‘2017-5-4’,‘开心’,‘伤心’) 备注
FROM student;

case-函数:约等于switch case

case要判断的字符或表达式

when 常量1 then 显示的值1或语句2；

when常量2 then 显示

分组函数

类别：sum求和，avg平均值，max最大值，min最小值，count计算个数

sum：select sum（字段） from 表名;

和distinct搭配

select sum（distinct 字段） from 表名;

count函数支持的函数

select count（*）from 表名；

在innodb引擎下count（*）和count（1）的效率差不多

分组查询

select 分组函数，列（要求出现在group by 后面）from 表

【where 筛选条件】

group by 分组的列表

【order by 语句】

查询列表比特殊要求是分组函数和group by 出现

分组函数做条件是放在having字句中

分组查询筛选条件两类

数据源位置关键字

分组前原始表 group by句前 where

分组后分组结果 group by后面 having

分组函数肯定是放在having子句

能分组前筛选的，优先考虑使用分组前筛选

group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求）

添加排序

连接查询

含义：多表查询，查询的字段来自多个表

笛卡尔乘积现象

分类：内连接、外连接、交叉连接

在这里插入图片描述

SQL199语法

左外：left【outer】

右外：right【outer】

全外：full【outer】

内连接语法：select 查询列表

from 表1 别名【连接类型】

inner join 表2 别名 on 连接条件

【 where 筛选条件】【group by 分组】

【having筛选条件】【order by 排序列表】

外连接

1，外连接的查询结果为主表中的所有记录

如果从表中有和它匹配的，则显示匹配的值

	如果从表中没有和他匹配的，则显示的是null

	外连接查询结果=内连接结果+主表中有而从表没有的记录

左（外）连接：left join 左边的是主类

右（外）连接：right join右边的是主类

左外连接和右外交换两个表的顺序，可以实现同样的效果

全外连接=内连接的结果+表1独有的+表2独有的（mysql现已不支持）

在这里插入图片描述

子查询

含义：出现在其他语句中的select语句，成为子查询或内查询内部向前其他select语句的查询，称之为外查询

分类：按子查询出现的位置

select后面

from后面

where或having后面

exists（存在）后面

按功能的划分：

标量子查询（结果只有一行一列）

列子查询（结果为一列多行）

行查询（结果为一行多列）

表子查询（多行多列查询）

一、where或having后面

特点：子查询放在小括号内、子查询一般放在条件右边、标量子查询搭配着单行操作符使用，列子查询搭配多行操作符使用

案例1：查询员工信息，满足salary>拉姆

SELECT *

FROM
student
WHERE salary >
(SELECT
salary
FROM
student
WHERE name = ‘拉姆’) ;

子查询的执行优先于主查询执行

数据管理

外键（了解）

数据库是单纯的表，只能用来存数据，只有行（数据）和列（字段）

我们想要使用多张表的数据，想使用外键（程序实现）

DML语言：数据操作语言

insert插入
update修改
delete删除

添加

语法一：insert into 表名（字段名1，字段2，字段3）values（‘值1’，‘值2’，‘值3’…）

语法二：insert into 表名

set 列名=值，……

由于主键自增我们可以省略（如果不写表的字段，它就会字段一一对应）

一般写插入语句，我们一定要数据和字段一一对应

可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

还可以支持子查询

在这里插入图片描述

insert into grade(gradename) values(‘大四’)

修改

update 表名（条件） set 列=新值，…… where 筛选条件；

修改多表的记录：

语法：update 表1 别名，表2 别名

set 列=值

在这里插入图片描述

删除

单表的删除

delete from 表名 where 筛选条件；

多表的删除【补充】

SQL92语法:

delete 别名

from 表1 别名，表2 别名

where 筛选条件 and 筛选条件

SQL99语法

delete 表1 别名，表2 别名

from 表1 别名，表2 别名

inner|left|right join 表2 别名 on 连接条件

where 筛选条件；

truncate table 表名；（全删）

特点：

delete 可以加where条件，truncate不可以
truncate删除效率高
要删除的表中有自增长列，如果用delete删除后，插入数据，自增长列的值从断点开始，而truncate删除后，在插入数据，自增长列的值从1开始
truncate删除没有返回值，delete删除有返回值
truncate删除不能回滚，delete可以回滚

DDL（数据定义语言）

库和表的管理

一、库的管理

创建，修改，删除

二、表的管理

创建，修改，删除

创建： create

修改：alter

删除：drop

库的创建

语法：

create database 库名；

判断：create database if not exists 库名；

修改库的字符集

alter database 库名 character set utf-8；

库的删除

drop database if exists 库名；

表的管理

表的创建

create table 表名（

列名列的类型（长度），

）；

表的修改

修改列名
- alter table 表名 change column 旧列名
  
  新列名类型；
修改列的类型或约束
- alter table 表名 modify column 旧列名
  
  新列名类型；
添加新列
- alter table 表名 add column 列名 double；
删除列
- alter table 表名 drop column 列名
修改表名
- alter table 表名 rename to 列名

在这里插入图片描述

复制表的结构和数据

create table 表名 select * from 需要复制的表名；

在这里插入图片描述

数据类型特点

如果不设置无符号还是有符号还是有符号的，默认是有符号的，如果想设置无符号，需要添加unsigned
插入数值超出范围，会报错，并且插入临界值
如果不设置长度，会有默认的长度

长度代表了显示的最大宽度，如果不够会用0来在左边填充，但必须使用zerofill搭配

Dec（m，d）

M：整数部分+小数部分

D：小数部分

char如果超出临界值，则插入临界值。

m和d都可以省略：varchar中M不可省略

如果是decimal，则M默认为10，D为0

如果是float和double，则根据插入的数值的精度来决定精度

定点型的精确度较高，擦汗入数值的精度就较高考虑使用

原则：

所选择的类型越简单越好，能保存数值的类型越小越好

常见约束

含义：一种限制，用于限制表中的数据

分类：六大约束

not null ：非空，用于保证该字段的值不能为空

添加非空：alter table 表名 modify column 字段名字段类型 not null；

删除非空：alter table 表名 modify column 字段名字段类型；

default：默认，用于该字段有默认值

添加默认：alter table 表名 modify column 字段名字段类型 default 值；

删除默认：alter table 表名 modify column 字段名字段类型；

primary key：主键，用于保证该字段的值具有唯一性，并且非空

unique：唯一，用于保证该字段的值具有唯一性，可以为空

添加唯一：alter table 表名 add unique（字段）

删除唯一：alter table 表名 drop index；

check：检查约束【MySQL中不支持】

foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表的添加外键约束，用于引用主键中某列的值

添加约束的时机：

  1. 创建表时
     2. 修改表时

约束的添加分类：

列级约束：六大约束支持，但外键约束没有效果

表级约束：除了非空，默认，其他都支持

index 查看表中所有的索引，包括主键，外键，唯一

create table 表名（

字段名字段类型约束

）

列级约束语法：

直接在字段名和类型后面追加约束类型

支持：默认，非空，主键，唯一

主键和唯一的对比

保证唯一性是否允许为空一个表中可有多少个

主键 √ × 一个

唯一 √ √ 可有多个

外键（foreign key）：

语法：alter table 约束表名 add 【constraint 约束名】 foreign key （‘字段名’）reference 主表（被引用列）

要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容
主表的关联列必须是一个key
插入数据时，先是主表，再是从表
删除数据时，先删除从表。再删除主表

添加列级约束

语法：alter table 表名 modify column 字段名字段类型新约束；

在这里插入图片描述

添加表级约束

语法：alter table 表名 add 【constraint 约束名】约束类型（字段名）【外键的引用】；

修改表时删除约束

alter table 表名 drop 约束名

标识列

含义：可以不用手动的插入值，系统提供默认的序列值

特点：
1.标识列不一定和主键搭配，但要求是一个key
2.一个表只可以有一个标识列
3.标识列的类型只能是数值
4.标识列可以通过 SET auto_increment_increment = 3; 设置步长
可以通过 手动插入值，设置起始值

insert into 表名（字段名，……） values（null，‘ ’）

修改表时设置标识列

alter table 表名 modify column 字段名数据类型主键 auto_increment；

TCL语言

事务：

一个或一组SQL语句组成一个执行单元，这个执行单元全部执行或是全部不执行

show engines；展示所有引擎

事务的ACID属性

原子性：食物是一个不可分割的工作单位，发生或不发生
一致性：事务必须使从一个一致性状态变换到另一个一致性状态
隔离性：事务的隔离性是只一个事物的执行不能被其他事务干扰，各个事务互不干扰
持久性：一个事务一旦被提交，它对数据库的改变就是永久性的，以后操作不会对这个干扰

事务的创建

隐式事务：事务没有明显的开启和结束的标记

前提：必须先设置自动提交功能为禁用

步骤一：开启事务

set autocommit=0；

start transaction；

步骤二：编写事务中的SQL语句

步骤三：结束事务

commit；提交

rollback；回滚

例子 student表

SET autocommit=0;
START TRANSACTION;
UPDATE student SET salary=5000 WHERE name=‘时崎狂三’;
UPDATE student SET salary=1500 WHERE name=‘艾米莉亚’;
COMMIT;
ROLLBACK;

隔离级别

在这里插入图片描述

设置隔离级别

set session transaction isolation level read uncommitted;设置隔离级别为read uncommitted

select @@tx_isolation;查看隔离级别

set names gbk;改变编译语言

set session transaction isolation level read committed;设置隔离级别为read committed

结束事务：commit； rollback；

saveport；存档点

例子：saveport a；设置保存点，与rollback to a；搭配

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据**更新（UPDATE）**操作。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据**更新（UPDATE）**操作。

幻读

幻读是针对数据**插入（INSERT）**操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

读未提交（READ UNCOMMITTED）读提交（READ COMMITTED）可重复读（REPEATABLE READ）串行化（SERIALIZABLE）从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

在这里插入图片描述

只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

视图

含义：虚拟的表，和普通的表一样使用，动态生成的数据，只保存SQL逻辑，不保存查询结果，视图名不能重复

语法：

create view v1 as

select 字段名 from 表名1

inner join 表名2 on 表名1.‘字段’=表名2.‘字段’;

一、创建视图

语法：create view 视图名

as 查询语句；

查看视图：select * from 视图名；

视图特点：

重用SQL语句
简化复杂的SQL语句
保护数据，提高安全性

视图修改

方法一、

create or replace view 视图名

as 查询语句；

方式二、

alter view 视图名

as 查询语句；

删除视图

drop view 视图名，视图名，……；

查看视图

desc 视图名；

show create view 视图名；

视图的更新

插入：insert into 视图名 values（‘字段1’，‘字段2’）；

修改：update 视图名 set 字段=‘修改后的’ where 条件

删除：delete from 视图名 where 条件

在这里插入图片描述

创建语法的关键字是否占用物理空间使用

视图 create view 没有（保存逻辑）增删改查（一般不支持增删改）

表 create table 占用增删改查

变量

系统变量：变量是系统提供的，不是用户定义，属于服务器层面

全局变量

会话变量

自定义变量：用户变量

局部变量

查看所有的系统变量

show global | session variables；

查看满足条件的部分系统变量

show global | session variables like ’ % ‘

查看指定的某个系统变量的值

select 】@@global | 【session】.系统变量名；

为某个系统变量赋值

方法一：

set global |【session】系统变量名=值；

方法二：

show @@global | 【session】.系统变量名=值；

注意

如果是全局级别，则需要加global，是会话级别，则加session，如果不写，默认是session

全局变量作用域

服务器每次启动将为所有的全局变量赋予初始值，针对所有的会话（连接）有效，但不能跨重启

会话变量

作用域：仅仅针对于当前会话（连接）有效

自定义变量

变量是用户自定义的，不是系统的。

使用步骤：

声明

赋值

使用（查看，比较，运算等）

用户变量

作用域：针对当前连接（会话）有效，同会话变量的作用域，应用在任何地方 begin end

声明初始化：set @用户变量名=值；

set @用户变量名：=值；

select @用户变量名：=值；

赋值

方法一：通过set或select

				set  @用户变量名=值；

set @用户变量名：=值；

select @用户变量名：=值；

方法二：通过select into

				select 字段 into @变量名

					from 表；

使用（查看用户变量的值）

select @变量名

局部变量

作用域：仅仅定义它的begin end 中有效

声明

declare 变量名类型；

declare 变量名类型 default 值；
赋值

方法一：通过set或select

				set  @用户变量名=值；

set @用户变量名：=值；

select @用户变量名：=值；

方法二：通过select into

				select 字段 into @变量名

					from 表；

在这里插入图片描述

使用

select 局部变量名；

存储过程和函数

存储过程和函数：类似Java中的方法

好处：

提高代码的重用性
简化操作

存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处语句

好处

提高代码的重用性
简化操作
减少编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法

create procedure 存储过程名（参数列表)

begin

存储过程体（一组合法的SQL语句）

end

注意：

参数列表包括三部分

参数模式参数名参数类型

参数模式：

in：该参数可以作为输入，需要调用方传入值

out：该参数可以作为输出，可以作为返回值

inout：前者的in和out的功能和

如果存储过程仅仅只有一句话，begin 和end可以省略

存储过程体中的每条SQL语句的结尾要求加分号

存储过程的结尾可以使用delimiter重新设置

语法：

delimiter 结束标记

call 存储过程名（实参列表）

DELIMITER $
CREATE PROCEDURE myp1 () //myp1 为存储过程
BEGIN
INSERT INTO admin (username, password)
VALUES
(‘john1’, ‘0000’),
(‘Lily’, ‘0000’),
(‘rose’, ‘0000’),
(‘jack’, ‘0000’),
(‘tom’, ‘0000’);
END $

调用

call myp1() $；

创建带有int类型的存储过程

在这里插入图片描述

CREATE PROCEDURE myp4(IN useraname VARCHAR(20),IN password VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.username=username
AND admin.password=PASSWORD;
SELECT IF(result>0,‘成功’,‘失败’);

END $

调用：CALL myp4(‘张飞’,‘8888’)$

创建带out模式的存储过程

在这里插入图片描述