修改表SQL语句

修改表名字

关键字:alterrename

alter table 表名称 rename 新表名;

添加字段

关键字:alteradd

alter table 表名称 add 列名称  数据类型;  -- 默认是尾部追加字段
alter table 表名称 add 列名称  数据类型 after 列名称;  -- 指定追加位置
alter table 表名称 add 列名称  数据类型 first;  -- 指定头部添加字段

修改字段

关键字:alterchangechange名字类型都可以修改modify只能改类型不能改名字)

alter table 表名称 change 列名称 新列名称 新数据类型;

删除字段

关键字:alterdrop

alter table 表名称 drop 列名称;

MySQL视图

​ 视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。通过视图,可以展现基表的部分数据;视图数据来自定义视图的查询中使用的表,使用视图动态生成。

​ 当频繁的使用一张虚拟表,可以考虑制作成视图,降低操作难度。但是会造成表的混乱 毕竟视图不是真正的数据源,而且视图只能用于数据的查询 不能做增、删、改的操作 可能会影响原始数据(视图里面的数据是直接来源于原始表 而不是拷贝一份)

视图的优点:

  1. 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。
  2. 安全:使用视图的用户只能访问他们被允许查询的结果,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。
  3. 数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响,源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。
    总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率。

视图的缺点:

  1. 性能:从数据库视图查询数据可能会很慢,特别是如果视图是基于其他视图创建的。
  2. 表依赖关系:将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时,都必须更改视图。

语法

create view 视图名 as select 查询语句;

MySQL触发器

什么是触发器

​ 触发器(trigger)是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete, update)时就会激活它执行。简单理解为:你执行一条sql语句,这条sql语句的执行会自动去触发执行其他的sql语句。

触发器的作用

  • 可在写入数据表前,强制检验或转换数据。
  • 触发器发生错误时,异动的结果会被撤销。
  • 部分数据库管理系统可以针对数据定义语言(DDL)使用触发器,称为DDL触发器。
  • 可依照特定的情况,替换异动的指令 (INSTEAD OF)。

触发器创建的四要素

  • 监视地点(table)
  • 监视事件(insert、update、delete)
  • 触发时间(after、before)
  • 触发事件(insert、update、delete)

触发器的使用语法

create trigger 触发器名
before|after delete|insert|update
on 表名 for each row
begin
触发SQL代码块;
end;
  • before/after: 触发器是在增删改之前执行,还是之后执行
  • delete/insert/update: 触发器由哪些行为触发(增、删、改)
  • on 表名: 触发器监视哪张表的(增、删、改)操作
  • 触发SQL代码块: 执行触发器包含的SQL语句

案例

数据准备

CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime,  -- 提交时间
    success enum ('yes', 'no')  -- 0代表执行失败
);
CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

需求:cmd表插入数据的success如果值为no 则去errlog表中插入一条记录

delimiter $$  # 将mysql默认的结束符由;换成$$
    create trigger tri_after_insert_cmd after insert on cmd for each row
    begin
        if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
            insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
        end if;
    end $$
    delimiter ;  # 结束之后记得再改回来,不然后面结束符就都是$$了
INSERT INTO cmd (
      USER,
      priv,
      cmd,
      sub_time,
      success
)VALUES
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');

补充:

  1. 临时修改SQL语句的结束符delimiter 结束符 ,临时修改的原因是因为触发器存储过程等技术点,代码中也需要使用分号,如果不修改则会冲突
  2. 查看当前库下所有的触发器信息:show triggers\G;
  3. 删除当前库下指定的触发器信息:drop trigger 触发器名称;

更多文章:https://zhuanlan.zhihu.com/p/147736116

MySQL事务

​ MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你既需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

事务的四大特性(ACID)

  • 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
  • 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
  • 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

使用

数据准备

create table user(
  id int primary key auto_increment,
  name char(32),
  balance int
  );
insert into user(name,balance)
  values
  ('kevin',1000),
  ('tom',1000),
  ('jerry',1000);

事务操作

start transaction; -- 开启一个事务的操作 or begin

-- 编写SQL语句(同属于一个事务)
update user set balance=900 where name='kevin';
update user set balance=1010 where name='tom';
update user set balance=1090 where name='jerry';

-- 事务回滚(返回执行事务操作之前的数据库状态)
rollback;  -- 执行完回滚之后,事务自动结束

-- 事务确认(执行完事务的主动操作之后,确认无误之后,需要执行确认命令)
commit;  -- 执行完确认提交之后,无法回滚事,务自动结束
  • begin :开始一个事务
  • rollback :事务回滚
  • commit :事务确认

更多文章:https://zhuanlan.zhihu.com/p/29166694

MySQL存储过程

什么是存储过程

​ 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。

存储过程的特点

  • 能完成较复杂的判断和运算
  • 可编程行强,灵活
  • SQL编程的代码可重复使用
  • 执行的速度相对快一些
  • 减少网络之间的数据传输,节省开销

存储过程的使用语法

无参

delimiter $$
create procedure  存储过程名()
begin
    SQL语句;
end $$
delimiter ;

-- 调用存储过程
call 存储过程名();

-- 查看存储过程具体信息
show create procedure 存储过程名;

-- 查看所有存储过程
show procedure status;

-- 删除存储过程
show procedure 存储过程名;  

有参

delimiter $$
create procedure 存储过程名([[in |out |inout ] 参数名 数据类形...])
begin
    SQL语句;
end $$
delimiter ;
  • in 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
  • out 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
  • inout 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

有参使用

create table userinfo(
    id int primary key auto_increment,
  name varchar(32),
  password varchar(32)
  
);

insert into userinfo (name,password) values ('kevin','123'),('tom','312'),('jason','312'),('jerry','132'),('emma','213');
delimiter $$
create procedure p(
    in m int,  -- in表示这个参数必须只能是传入不能被返回出去
  in n int,
  out res int  -- out表示这个参数可以被返回出去,还有一个inout表示即可以传入也可以被返回出去
)
begin
    select name from userinfo where id >m and id < n;
    set res=0;  -- 用来标志存储过程是否执行
end $$
delimiter ;

# 针对res需要先提前定义
set @res=10; -- 定义
select @res; -- 查看
call p(1,5,@res) -- 调用
select @res; -- 再次查看

MySQL函数

与存储对象区别在于,mysql内置的函数只能在SQL语句中使用

准备数据

drop table if exists userinfo;
create table userinfo(
  id int primary key auto_increment,
  sex tinyint,
  name varchar(32)
);

insert into userinfo (sex,name) values(1,'kevin'),(1,'tom'),(2,'jerry'),(1,'jason'),(2,'emma');

if函数

语法

if(条件表达式,值1,值2);
  • 当条件表达式为true返回值1,否则返回值2
select name as 名字, if(sex=1,'男','女') as 性别 from userinfo;

更多:https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

MySQL流程控制

if结构

语法

if 条件语句1 then 
    语句1;
elseif 条件语句2 then 
    语句2;
else 
    语句3;
end if;

while循环

语法

while 条件 DO
      循环体;
   end while;

更多:https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html

MySQL索引

类似于书的目录。意味着以后再查数据应该先找目录再找数据,而不是用翻页的方式查询数据

在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构

  • primary key:主键
  • unique key:唯一键
  • index key:索引键

上面三种“键”前两种除了有加速查询的效果之外,还有额外的约束条件(primary key:非空且唯一,unique key:唯一),而index key没有任何约束功能只会帮你加速查询

本质都是:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。

索引的影响:

  • 在表中有大量数据的前提下,创建索引速度会很慢
  • 在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低

更多文章:https://juejin.cn/post/6931901822231642125

Last modification:May 14, 2022
如果觉得我的文章对你有用,请随意赞赏