MySQL3

MySQL1-常用配置和常用指令
 MySQL2-DQL和DML
MySQL3-DDL和TCL
MySQL4-查询练习

DDL学习

DDL(Data Definition Languages)即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等

DDL生产通用写法

数据定义语言，对于库和表的操作

drop database if exists 库名;
create database 库名;

drop database if exists 表名;
create table 表名();

创建create

库

create database if not exists books;

表

create table 表名(
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    column_name1 date_type() [null|not null] [constant_expression],
    ...
    列名 列的类型[(长度) 约束]
);

修改alter

库

-- 不能修改
rename database books to books;

-- 修改库字符集
alter database books character set gbk;

表

-- 修改表名
alter table book_author rename to author;

-- 修改列名
alter table book change column publishdata pubData datetime;

-- 修改列类型,约束
alter table book modify column pubdata timestamp;

-- 添加列
alter table author add column annual double;

-- 删除列
alter table author drop column annual;

删除drop

库

drop database if exists books;

表

drop database if exists author;

表的复制

只复制表的结构

create table `copy` like author;

复制表的整体

create table copy2 select * from author;
-- 添加筛选
create table copy3 select id,au_name from author 
where nation=`中国`;

约束

一般通用写法

create table if not exists stuinfo(
    id int primary key,
    stuNume varchar(20) not null,
    gender char(1),
    seat  int unique,
    age int default 18,
    majorID int,
    constraint fk_stuinfo_major foreign key (majorid) references major(id)
)

常见约束

一种限制，用于限制表中的数据，为了保证数据的一致性。

一般添加约束的时机：创建表时，修改表时。

约束添加的分类：列级约束，表级约束。

六大约束:
    not null    非空约束    保证该字段的值非空
    default     默认约束    保证该字段有默认值
    primary key 主键约束    保证唯一性，且非空
    unique      唯一约束    保证唯一性，可非空
    check       检查约束    mysql不支持，
    foreign key 外键约束    保证通主表值一致

主键和唯一

主键    保证唯一性  不允许为空      至多有一个  允许进行组合主键  
唯一    保证唯一性  允许为空只能一个可以有多个  允许进行组合唯一
外键：
    1.从表上设置外键关系
    2.从表外键列的类型和主表的关联列类型一样或兼容
    3.主表中的关联列必须是个key，一般是主键或唯一
    4.插入数据时，要先插入主表，再插入从表
      删除数据时，要先删除从表，再删除主表

添加列级约束

create table stuinfo(
    id int primary key,
    stuNume varchar(20) not null,
    gender char(1) check(gender in ('男','女')),
    seat  int unique,
    age int default 18,
    majorID int references major(id)
);

create table major(
    id int primary key,
    majorName varchar(20)
);

添加表级约束

create table stuinfo(
    id int,
    stuname varchar(20),
    gender char(1),
    seat int,
    age int,
    majorid int,

    [constraint pk] primary key(id,stuname),
    [constraint uq] unique(seat),
    [constraint ck] check(gender in ('男','女'))
    [constraint fk_stuinfo_major] foreign key (majorid) references major(id)

);

修改表时添加约束

-- 非空
alter table stuinfo modify column stuname varchar(20) not null;

-- 默认
alter table stuinfo modify column age int default 18;

-- 主键
alter table stuinfo modify column id int primary key;
alter table stuinfo add primary key(id,stuname);

-- 唯一
alter table stuinfo modify column seat int unique;
alter table stuinfo add unique(seat);

-- 外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

修改表时删除约束

-- 非空
alter table stuinfo modify column stuname varchar(20);

-- 默认
alter table stuinfo modify column age int;

-- 主键
alter table stuinfo modify column id int;
alter table stuinfo drop primary key;

-- 唯一
alter table stuinfo modify column seat int;
alter table stuinfo drop index seat;

-- 外键
alter table stuinfo drop  foreign key fk_stuinfo_major;

标识列

自增长值，可以自动插入系统默认提供的序列值，一个表只能有一个标识列，且要和key搭配，只能设置到数值型。

添加标识列

creat table tab_identity(
    id int primary key auto_increment,
    name varchar(20)
);

-- 步长修改为3
set auto_increment_increment=3;

修改表时设置标识列

alter table tab_identity modify column id int primary key auto_increment;

修改表时删除标识列

alter table tab_identity modify column id int primary key;

级联

一般不用

设置级联删除

可直接删除从表中的内容，主表与之关联的内容会被删除

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;

视图

5.1后出现
可以理解为虚拟的表,有行有列和普通的表一样使用
虚拟表内的数据都为动态的,只保存sql逻辑,不保存结果

创建视图

create view myv1 as 
select last_name,department_name,job_title from employees e
inner join departments d on e.`department_id`= d.`department_id`
inner join jobs j on j.`job_id` = e.`job_id`;

内连接   inner join
左外连接 left outer join
右外连接 right outer join

使用视图

select * from myv1 where last_name like '%a%';

修改视图

方式一

create or replace view myv1 as
select last_name,department_name,job_title from employees e
inner join departments d on e.`department_id`= d.`department_id`
inner join jobs j on j.`job_id` = e.`job_id`;

方式二

alter view myv1 as
select last_name,department_name,job_title from employees e
inner join departments d on e.`department_id`= d.`department_id`
inner join jobs j on j.`job_id` = e.`job_id`;

删除视图

drop view myv1,myv2;

查看视图

-- 简单句
desc myv1;

-- 复杂句,通用可以查看表
show create view myv1;

更新视图

插入数据

插入数据会对原表进行插入

insert into myv1 values('张飞','zhangfei@qq.com');

修改数据

更改数据会对原表进行更改

update myv1 set last_name = '张无忌' where last_name = '张飞';

删除数据

删除数据会对原表进行删除

delete from myv1 where last_name = '张无忌';

变量

系统变量

系统提供的变量

全局变量

每次启动服务器都会赋新值，所有会话都有效。

-- 查看所有系统变量
show global variables;

-- 查看指定的某个系统变量的值
select @@global.系统变量名;

-- 给变量赋值
set global 系统变量名 = 值;
set @@global.系统变量名 = 值;

绘画变量

针对当前会话有效。

-- 查看所有系统变量
show session variables;

-- 查看指定的某个系统变量的值
select @@session.系统变量名;

-- 给变量赋值
set session 系统变量名 = 值;
set @@session.系统变量名 = 值;

自定义变量

使用步骤：1.声明2.赋值3.使用(查看、比较、运算)

用户变量

针对于当前会话有效

-- 声明并初始化
set @用户变量名:=值;
select @用户变量名:=值;

-- 赋值
set @用户变量名:=值;
select @用户变量名:=值;
select 字段 into 变量名 from 表;

-- 使用
select @用户变量名;

局部变量

在begin end块中第一句话有效

-- 声明
declare 变量名 类型;
declare 变量名 类型 default 值;

-- 赋值
set 局部变量名:=值;
select @局部变量名:=值;
select 字段 into 变量名 from 表;

--使用
select 局部变量名;

存储过程和函数

类似于方法

好处:1.提高代码的重要性2.简化操作

储存过程

一组预先编译好的sql语句集合

好处1.提高代码的重用性2.简化操作3.减少编译次数，提高效率

创建语法

参数列表包含:参数模式,参数名,参数类型 in stuname varchar(20)

参数模式:in输入,out返回值,inout即传入又返回

mysql> delimiter 结束标记
mysql> create procedure 储存过程名(参数列表1,参数列表2)
->begin
->    储存过程体1;
->    储存过程体2;
->end 结束标记
mysql> delimiter;

调用语法

-- in
mysql> call 储存过程名(参数);

-- out
mysql> call 储存过程名(@变量);

--inout
-- 变量需要提前赋值，使用后返回值覆盖原值
mysql> call 储存过程名(@变量);

删除语法

drop procedure 存储过程名;

查看结构语法

show create procedure 存储过程名;

函数

一组预先编译好的sql语句集合

好处1.提高代码的重用性2.简化操作3.减少编译次数，提高效率

同存储过程区别在返回值，函数必须有一个返回值

创建语法

参数列表包含:参数名,参数类型 stuname varchar(20)

函数体必须有return语句

mysql> delimiter 结束标记 --修改结束标记防止正在写入时出现直接执行的情况 一般使用$$ 
mysql> create function 函数名(参数列表1,参数列表2) returns 返回类型 
->begin
->    函数体1;
->    函数体2;
->    return ;
->end 结束标记
mysql> delimiter;

调用语法

select 函数名(参数列表);

删除语法

drop function 函数名;

查看结构语法

show create function 函数名;

触发器

用途：触发器用于在特定的数据库事件（如 INSERT、UPDATE 或 DELETE）发生时自动执行一段代码。
触发时机：可以在事件发生之前（BEFORE）或之后（AFTER）触发。
作用范围：触发器是针对表的，每次对表的每一行进行操作时都会触发。

DELIMITER $$ --修改结束标记防止正在写入时出现直接执行的情况 一般使用$$ 
CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- 触发器代码
END$$
DELIMITER ;
INSERT：当向表中插入新记录时触发。
UPDATE：当更新表中的现有记录时触发。
DELETE：当从表中删除记录时触发。

查看当前数据库中所有的触发器

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

TRIGGER_NAME：触发器的名称。
EVENT_MANIPULATION：触发事件（INSERT、UPDATE 或 DELETE）。
EVENT_OBJECT_TABLE：触发器关联的表。
ACTION_TIMING：触发时机（BEFORE 或 AFTER）。
information_schema.TRIGGERS：包含所有触发器信息的系统表。
TRIGGER_SCHEMA = DATABASE()：过滤条件，确保只查询当前数据库中的触发器。

删除触发器

DROP TRIGGER [IF EXISTS] trigger_name;

流程控制结构

顺序结构

程序从上到下依次执行

分支结构

程序从两条或者多条路径中选择一条去执行

if结构

简单实现双分支

select if(表达式1,表达式2,表达式3);
执行顺序：表达式1成立返回表达式2，不成立返回表达式3

实现多重分支

只应用在begin end块中

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
[else 语句n;]
end if;

case结构

可以作为表达式，嵌套在其他语句中使用

还可以作为独立的语句进行使用，需要放在begin end块中

case结构：多项选择

实现等判断

-- 作为表达式
case 表达式
when 值1 then 值1
when 值2 then 值2
...
else 值n
end;

-- 作为独立语句
case 表达式
where 值1 then 语句1;
where 值2 then 语句2;
···
else 语句n;
end case;

case结构：多重if

实现区间判断

-- 作为表达式
case 表达式
when 值1 then 值1
when 值2 then 值2
...
else 值n
end;

-- 作为独立语句
case 表达式
where 值1 then 语句1;
where 值2 then 语句2;
···
else 语句n;
end case;

循环结构

程序满足一定条件基础上,重复执行一段代码

while

标签(名字):while 循环条件 do
    循环体;
end while 标签(名字);

loop

简单的死循环

标签(名字):loop
    循环体;
end loop 标签(名字);

repeat

标签(名字):repeat
    循环体;
until 结束循环的条件
end repeat 标签(名字);

循环控制

iterate

结束本次循环，继续执行循环

leave

结束本次循环，并且跳出循环

索引

按照一定规律，和算法进行查找。

优点：

加快数据的查询速度

缺点：

创建索引会耗费时间和占用空间，数据量越多创建时间也会增加
经常更新的表避免对其进行过多的索引，只对经常查询的字段进行索引
数据量小最好不用索引
同一字段相同值比较多，最好不建立索引

查看表中已有的索引

show index from 索引;

创建索引

在persons表的LastName列上创建一个名为PIndex的索引：

create index idx_PIndex ON Persons (LastName)

索引名一般为uni_字段名或idx_字段名

alter table 表名 add index 索引名(字段);

联合索引

对多个字段进行联合查询

联合索引最左原则从左向右排列，不带最后一个玩

alter table 表名 add index 索引名(字段1,字段2);

TCL学习

TCL(Transaction Control Language)事务控制语句，用于控制事务

存储引擎

InnoDB  默认的数据库存储引擎            支持事务
MyISAM  5.5之前默认的数据库存储引擎     不支持事务
MEMORY  使用较多的数据存储引擎          不支持事务

-- 查看数据库的数据引擎
show engines;

事务

一个或一组sql语句组成一个执行单元，这个执行单元要么全执行成功，要么全不执行。
原子性A，不可分割的工作单位
一致性C，从一个一致性状态转换为另一个一致性状态
隔离性I，一个事务的执行不受其他的事务的干扰
持久性D，一个事务提交后，对数据库进行永久性的改变

查看事务的开启状态

show variables like 'autocommit';

设置紧张自动提交，只针对单次事务有效

set autocommit=0;

事务的创建

隐式事务

事务没有明显的开启和结束标志

比如insert、update、delete语句，事务默认自动提交

显示事务

事务具有明显的开启和结束标志

前提必须设置自动提交功能为禁止

事务编写结构

开启事务
set autocommit=0;
start transaction; # 可选择
编写事务中的sql语句
语句一般是(select,insert,updata,delete) 语句1 语句2
结束事务
commit; # 提交事务
rollback; # 回滚事务
savepoint 节点; # 保存节点,搭配'rollback to 节点',使用

演示

单个事务

-- 开启事务
set autocommit=0;  
start transaction;

-- 事务语句
update account set balance=500 where username='张无忌';
# savepoint a; 
update account set balance=1500 where username='赵敏';

-- 结束事务
commit;
# rollback;
# rollback to a;

多个事务并发

脏读:读取到没被提交的数据
不可重复读:读取后字段更新,再次读取值不同
幻读:读取后插入数据,再次读取数据行变多

数据库事务隔离

在Oracle中设置两种级别read committed(默认)和serializable
在MySQL中设置四种级别read uncommitted/read committed/repeatable read(默认)/serializable

-- 查看当前的默认隔离级别,5.0
select @@tx_isolation;

-- 查看当前的默认隔离级别,8.0
select @@transaction_isolation;

-- 修改当前默认隔离级别
set session transaction isolation level read uncommitted;

-- 设置全局默认隔离级别
set global transaction isolation level read uncommitted;

posted @ 2025-04-01 13:31 *--_- 阅读(48) 评论(0) 收藏举报

刷新页面返回顶部

ELM

MySQL3

DDL学习

DDL生产通用写法

创建create

库

表

修改alter

库

表

删除drop

库

表

表的复制

只复制表的结构

复制表的整体

约束

一般通用写法

常见约束

添加列级约束

添加表级约束

修改表时添加约束

修改表时删除约束

标识列

添加标识列

修改表时设置标识列

修改表时删除标识列

级联

设置级联删除

设置级联置空

视图

创建视图

使用视图

修改视图

方式一

方式二

删除视图

查看视图

更新视图

插入数据

修改数据

删除数据

变量

系统变量

全局变量

绘画变量

自定义变量

用户变量

局部变量

存储过程和函数

储存过程

创建语法

调用语法

删除语法

查看结构语法

函数

创建语法

调用语法

删除语法

查看结构语法

触发器

查看当前数据库中所有的触发器

删除触发器

流程控制结构

顺序结构

分支结构

if结构

case结构

case结构：多项选择

case结构：多重if

循环结构

while

loop

repeat

循环控制

iterate

leave

索引

查看表中已有的索引

创建索引