MySQL进阶笔记

最新推荐文章于 2022-08-30 17:16:31 发布

剑༒来

最新推荐文章于 2022-08-30 17:16:31 发布

阅读量185

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_33638124/article/details/116045802

版权

DDL(数据库)语言

库/表的管理

一、创建库：create database 【if not exists】库名【 character set 字符集名】;
二、修改库：alter database 库名 character set 字符集名;
三、删除库：drop database 【if exists】库名;
四、创建表 ★
create table 【if not exists】表名(
字段名字段类型【约束】,
字段名字段类型【约束】,
…
)
五、修改表
1.添加列：alter table 表名 add column 列名类型【first|after 字段名】;
2.修改列的类型或约束：alter table 表名 modify column 列名新类型【新约束】;
3.修改列名：alter table 表名 change column 旧列名新列名类型;
4 .删除列：alter table 表名 drop column 列名;
5.修改表名：alter table 表名 rename 【to】新表名;
六、删除表：drop table【if exists】表名;
七、复制表
1、复制表的结构：create table 表名 like 旧表;
2、复制表的结构+数据：
create table 表名
select 查询列表 from 旧表【where 筛选】;

数据类型

一、数值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
②如果超出了范围，会报out or range异常，插入临界值
③长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

2、浮点型
定点数：decimal(M,D)
浮点数:
float(M,D) 4
double(M,D) 8
特点：
①M代表整数部位+小数部位的个数，D代表小数部位
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数

二、字符型
char、varchar、binary、varbinary、enum、set、text、blob
char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略

三、日期型
year年
date日期
time时间
datetime 日期+时间 8
timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

约束

一、常见的约束
NOT NULL：非空，该字段的值必填
UNIQUE：唯一，该字段的值不可重复
DEFAULT：默认，该字段的值不用手动插入有默认值
CHECK：检查，mysql不支持
PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
FOREIGN KEY：外键，该字段的值引用了另外的表的字段

主键和唯一
1、区别：
①、一个表至多有一个主键，但可以有多个唯一
②、主键不允许为空，唯一可以为空
2、相同点
都具有唯一性
都支持组合键，但不推荐
外键：
1、用于限制两个表的关系，从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3、主表的被引用列要求是一个key（一般就是主键）
4、插入数据，先插入主表
删除数据，先删除从表
可以通过以下两种方式来删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

二、创建表时添加约束
create table 表名(
字段名字段类型 not null,#非空
字段名字段类型 primary key,#主键
字段名字段类型 unique,#唯一
字段名字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表（被引用列）

)
注意：
支持类型可以起约束名
列级约束除了外键不可以
表级约束除了非空和默认可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

三、修改表时添加或删除约束
1、非空
添加非空：alter table 表名 modify column 字段名字段类型 not null;
删除非空：alter table 表名 modify column 字段名字段类型 ;

2、默认
添加默认：alter table 表名 modify column 字段名字段类型 default 值;
删除默认：alter table 表名 modify column 字段名字段类型 ;
3、主键
添加主键：alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键：alter table 表名 drop primary key;

4、唯一
添加唯一：alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一：alter table 表名 drop index 索引名;
5、外键
添加外键：alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键：alter table 表名 drop foreign key 约束名;

四、自增长列
特点：
1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
auto_increment_increment
如果要更改起始值：手动插入值；如果要更改步长：更改系统变量
set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key

一、创建表时设置自增长列
create table 表(
字段名字段类型约束 auto_increment
)
二、修改表时设置自增长列：alter table 表 modify column 字段名字段类型约束 auto_increment
三、删除自增长列：alter table 表 modify column 字段名字段类型约束

事务

一、含义
事务：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
二、特点（ACID）
A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的
D 持久性：一个事务一旦提交了，则永久的持久化到本地

三、事务的使用步骤 ★
了解：隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete
显式事务：具有明显的开启和结束
使用显式事务：
①开启事务
set autocommit=0;
start transaction;#可以省略
②编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete
设置回滚点：savepoint 回滚点名;
③结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方：rollback to 回滚点名;
四、并发事务
1、事务的并发问题是如何发生的？
多个事务同时操作同一个数据库的相同数据时
2、并发问题都有哪些？
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
不可重复读：一个事务多次读取，结果不一样
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“插入”的数据
3、如何解决并发问题
通过设置隔离级别来解决并发问题
4、隔离级别
脏读不可重复读幻读
read uncommitted:读未提交 × × ×
read committed：读已提交 √ × ×
repeatable read：可重复读 √ √ ×
serializable：串行化 √ √ √

视图

一、含义
mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。
好处：
简化sql语句；提高了sql的重用性；保护基表的数据，提高了安全性
二、创建
create view 视图名
as
查询语句;
三、修改
方式一：
create or replace view 视图名
as
查询语句;
方式二：
alter view 视图名
as
查询语句
四、删除：drop view 视图1，视图2,…;
五、查看：desc 视图名；show create view 视图名;
六、使用
插入insert；修改update；删除delete；查看select
注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新
①包含分组函数、group by、distinct、having、union、
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图
七、视图和表的对比
关键字是否占用物理空间使用
视图 view 占用较小，只保存sql逻辑一般用于查询
表 table 保存实际的数据增删改查

变量

分类
一、系统变量：由系统提供的，不用自定义
语法：
①查看系统变量：show 【global|session 】variables like ‘’; 如果没有显式声明global还是session，则默认是session
②查看指定的系统变量的值：select @@【global|session】.变量名; 如果没有显式声明global还是session，则默认是session
③为系统变量赋值
方式一：set 【global|session 】变量名=值; 如果没有显式声明global还是session，则默认是session
方式二：set @@global.变量名=值；set @@变量名=值；

1、全局变量：服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效
2、会话变量：服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）

二、自定义变量：用户变量
作用域：针对于当前连接（会话）生效
位置：begin end里面，也可以放在外面
使用：
①声明并赋值：
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;

②更新值
方式一：
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;
方式二：
select xx into @变量名 from 表;

③使用
select @变量名;

2、局部变量
作用域：仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
使用：
①声明
declare 变量名类型【default 值】;
②赋值或更新
方式一：
set 变量名=值;或
set 变量名:=值;或
select @变量名:=值;
方式二：
select xx into 变量名 from 表;
③使用
select 变量名;

存储过程和函数

都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字

存储过程

一、创建 ★
create procedure 存储过程名(参数模式参数名参数类型)
begin
存储过程体
end
注意：
1.参数模式：in、out、inout，其中in可以省略
2.存储过程体的每一条sql语句都需要用分号结尾

二、调用
call 存储过程名(实参列表)
举例：
调用in模式的参数：call sp1（‘值’）;
调用out模式的参数：set @name; call sp1(@name);select @name;
调用inout模式的参数：set @name=值; call sp1(@name); select @name;
三、查看
show create procedure 存储过程名;
四、删除
drop procedure 存储过程名;

函数

一、创建
create function 函数名(参数名参数类型) returns 返回类型
begin
函数体
end
注意：函数体中肯定需要有return语句
二、调用：select 函数名(实参列表);
三、查看：show create function 函数名;
四、删除：drop function 函数名；