mysql基础

最新推荐文章于 2024-05-20 08:59:13 发布

qq_21562243

最新推荐文章于 2024-05-20 08:59:13 发布

阅读量174

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_21562243/article/details/108026855

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

约束：表级约束，列级约束( 列名数据类型约束类型)
主键约束 PRIMARY KEY(primary key)
唯一约束 UNIQUE (unique)
外键约束 FOREIGN KEY REFERENCES(references)，设置表级约束才生效
非空约束 NOT NULL
检查约束 CHECK(mysql不支持，但语法不报错)
默认约束 default

主键保证唯一性不允许为空一个表中至多有1个允许组合列（不推荐）
唯一保证唯一性允许为空一个表中允许多个允许组合列（不推荐）

外键：
1 要求在从表设置外键关系
2 先删从表数据，才能删除主表数据
3 外键列的类型和主表的关联列的类型要求一致
4 要求主表的列必须是主键或者唯一键
5 添加数据时，先添加主表，再添加从表

stuinfo 学生表
major 专业表

create table if not exists stuinfo(
id int primary key,
stuname varchar(20),
sex char(1),
age int default 18,
seat int unique,
majorid int,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) – 从表stuinfo的列majorid 与主表major的列id,设置外键约束。（表级约束）约束名:fk_stuinfo_major（自定义）
);

查看表约束结构：
show index from 表名;

修改表中列的约束：
alter table emp2 modify column id int primary key; – 列级约束：修改id为primary key（主键约束）
alter table emp2 add constraint my_emp_id_pk primary key(id); --表级约束（作用一样,表现形式不一样: 修改id为primary key（主键约束）my_emp_id_pk 自定义约束名

列级约束：位置,列的后面支持的约束类型，语法都支持，但外键没有效果不可以起约束名
表级约束：位置,所有列的下面支持的约束类型，默认和非空不支持，其他支持可以起约束名(主键没有效果)

标识列：自增长列。auto_increment
1 一个表至多只有一个标识列
2 标识列类型: 只能是数值型。
3 可以设置步长，设置初始值。

TCL :Transaction Control Language 事务控制语言
事务：一个或一组sql语言组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

存储引擎：在MySQL中的数据用各种不同的技术存储在文件(或内存)中。
show engines; – 查看MySQL支持的存储引擎。
innodb 支持事务，默认存储引擎。

事务的ACID属性：
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。(数据操作总体保持一致, 例如：2+8=10 操作后 1+9=10)

隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据，对并发的其他事务是隔离的，
并发执行的各个事务之间不能互相干扰。

持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的创建：
隐式事务：事务没有明显的开始和结束的标记。mysql 默认开启自动事务提交
例如： insert ，update , delete语句

显示事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁止。
set autocommit=0; #只针对当前事务有效。（一次性的）
show variables like ‘autocommit’; #查看当前事务是否自动提交状态。on 开启自动提交，off 关闭自动提交

开启事务：
set autocommit=0;
start transaction;
编写事务中的sql语句(select insert update delete)
语句1；
语句2；
…
结束事务：
commit; 提交事务
rollback; 回滚事务

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
1 脏读：对于两个事务T1,T2。T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。针对数据更新操作
2 不可重复读：对于两个事务T1,T2。T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。
3 幻读：对于两个事务T1,T2。T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行。针对数据插入，删除操作

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。

一个事务与其他事务隔离的程度称为隔离级别：数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就
越好，但是并发性越弱。

select @@tx_isolation; – 查看当前数据库事务隔离级别
set transaction isolation level read committed; --设置当前会话隔离级别
MySQL隔离级别四种：
1 READ UNCOMMITTED(读未提交）允许事务读取未被其他事务提交的数据。脏读，不可重复读，幻读问题都会出现
2 READ COMMITTED(读已提交) 只允许事务读取已经被其他事务提交的数据。可以避免脏读，但不可重复读，和幻读问题仍会出现。
3 REPEATABLE READ（可重复读) 确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，紧张其他事务对这个字段进行更新操作。
可以避免脏读和不可重复读，但幻读的问题仍然存在。（MYSQL默认使用事务隔离级别）

 4 SERIALIZABLE（串行化)   确保事务可以从一个表中读取相同的行，在这个事务持续期间，紧张其他事务对该表执行插入，更新，和删除操作。所以并发问题都可以避免，但性能低下。

oracle隔离级别： read committed

savepoint 节点名；#设置保存点,搭配rollback to 节点名使用。,指定回滚节点。

#开启事务
set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a; #设置保存点
delete from account where id-28;
rollback to a; #回滚至保存点 a

视图：虚拟表，和普通表一样使用。是通过表动态生成的数据。
创建视图：
create view 视图名
as
查询语句；

#创建视图，查询员工名,部门名，工作名
create view myv1
as
select last_name,department_name,job_title
from employees e
join departments d ON e.department_id = d.department_id
join jobs j ON j.job_id=e.job_id

#使用视图,查询员工姓名带 a 字符串相关信息。
select * from myv1 where last_name=’%a%’;

视图修改：
create or replace view
as
查询语句；

或者：
alter view 视图名
as
查询语句；

删除视图
drop view 视图名,视图名,…;#允许删除多个

查看视图结构：
desc myv3;

视图数据更新操作：update ,insert ,delete操作：
具备以下特点的视图不允许数据更新操作：
1 sql语句，具有：聚合函数(max(),avg()等等)，distinct,group by,having,union 或者 union all
2 常量视图
3 select中包含子查询
4 join
5 from一个不能更新的视图

视图 create view 保存sql逻辑，增删改查（一般情况下，视图用于查询。增删改需要符合特定条件）
表 create table 保存数据，增删改查

delete 和 truncate在事务使用时的区别：

#delete使用，支持事务回滚，可以数据恢复。
set autocommit=0;
start transaction;
delete from account;
rollback;

#truncate使用，不支持事务回滚，已删除就不能恢复。
set autocommit=0;
start transaction;
truncate table account;
rollback;

设置外键级联删除（主表语句删除数据，会把相关联的从表语句的数据也一并删除，on delete cascade）
alter table stuinfo ADD constraint fk_stu_major foreign key(majorid) references major(id) on delete cascade;

设置外键级联置空：（主表语句删除数据，会把相关联的从表语句的数据置空,on delete set null）
alter table stuinfo ADD constraint fk_stu_major foreign key(majorid) references major(id) on delete set null;

变量：
系统变量：变量由系统提供,不是用户定义，属于服务器层面。
使用语法：
1 查看所有的系统变量:
show variables; #默认等于会话变量 show session variables;
全局变量: 作用域GLOBAL的，系统变量。
服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话(连接)有效，但不能跨服务重启。
show global variables;

会话变量：作用域为SESSION的，系统变量。
	仅仅针对于当前会话（连接）有效。
	show session variables;

2 查看满足条件的部分系统变量
show global | 【session】variables like ‘%char%’;
3 查看指定的某个系统变量的值
select @@global | 【session】.系统变量名;
4 为某个系统变量赋值
set global | 【session】系统变量名=值；
set @@global | 【session】.系统变量名=值；

注意：
如果是全局级别，则需要加GLOBAL,如果是会话级别,则需要加SESSION,如果不写，则默认SESSION

自定义变量：变量是用户自定义，不是由系统提供。
使用步骤
1 声明
2 赋值
3 使用（查看，比较，运算等）

用户变量: 作用域针对于当前会话（连接）有效。同于系统会话变量作用域。
1 声明并初始化3种方式，（= 或 := 赋值操作符）
set @用户变量名=值;
set @用户变量名 :=值；
select @用户变量名 :=值；

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

3 使用
  select @变量名； #查看

局部变量: 作用域仅仅在定义它的begin end中有效。应用在begin end中的第一句话
1 声明
declare 变量名类型;
declare 变量名类型 default 值;
2 赋值
set 局部变量名=值;
set 局部变量名 :=值；
select @局部变量名 :=值；
select 字段 into 局部变量名 from 表；
3 使用
select 局部命量名; #查看

用户变量：作用域，当前会话。定义和使用的位置:会话中的任何地方语法,必须加@符号，不用限定类型
局部变量：作用域，begin end中定义和使用的位置:只能在begin end中，且为第一句话语法，一般不用加@符号，需要限定类型

存储过程和函数：类似于java中的方法。

存储过程：
含义：一组预先编译好的SQL语句的集合，理解成，批处理语句。
作用：
1 提高代码的重用性
2 简化操作
3 减少了编译次数与数据库服务器连接次数，提高了效率。

创建语法：
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的SQL语句)
end

注意：
1 参数列表包含三部分
参数模式参数名参数类型
例如：
IN stuname varchar(20)

参数模式：
IN : 该参数可以作为输入,也就是该参数需要调用方传入值（不写参数模式,默认IN）
OUT : 该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以输入又可以作为输出。也就是该参数既需要传入值，又可以返回值。

2 如果存储过程体仅仅只有一句话，begin end可以省略。
存储过程体中的每条sql语句结尾要求必须加分号
存储过程的结尾可以使用 DELIMITER 重新设置
DELIMITER 结束标记
例如：DELIMITER $

调用存储过程：
语法：
call 存储过程名(实参列表);

实例：插入5条数据
delimiter $
create procedure myp1()
begin
insert into admin(username,password)
values
(‘john1’,‘0000’),
(‘john2’,‘0001’),
(‘john3’,‘0002’),
(‘john4’,‘0003’),
(‘john5’,‘0004’);
end $

call myp1();

#删除存储过程
drop procedure 存储过程名称;

#查看存储过程结构
show create procedure myp2;

#存储过程：可以有0个或者多个返回。适合做批量插入，更新操作。
#函数：只能有1个返回，适合处理数据后返回一个结果。
函数创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end

注意：
1 参数列表：包含两部分：
参数名参数类型

2 函数体：肯定有return语句，如果没有会报错。如果return语句没有放在函数体的最好也不报错
但不建议。

3 函数体中仅有一句话，则可以省略begin end
4 使用delimiter语句设置结束标记

函数调用语法
select 函数名(参数列表)

示例：
delimiter $
create function myf1() returns INT
begin
declare c int default 0;
select count(*) into c
from employees;
return c;
end $

select myf1();

注意：SET GLOBAL log_bin_trust_function_creators=TRUE; – mysql默认是不允许创建函数的,设置此选项，才能创建函数。

#查看函数结构
show create function myf1;
#删除函数
drop function myf1;

#流程控制结构
顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码

一，分支结构
1, if 函数
语法：
if (表达式1,表达式2，表达式3）
执行顺序：
如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值。

#case 区间判断使用
case
when 条件1 then 语句1；
when 条件2 then 语句2；
else 语句n;
end case;

case 特点：
可以作为表达式，嵌套在其他语句中使用，可以放在任何地方,BEGIN END中或 BEGIN END的外面
可以作为独立的语句去使用，只能放在BEGIN END中

delimiter $
create procedure test_case(IN score INT)
begin
case
when score>=90 and score<=100 then select ‘A’;
when score>=80 then select ‘B’;
when score >=60 then select ‘C’;
else select ‘D’;
end case;
end $

call test_case(95);

if 结构：
语法:
if 条件1 then 语句1；
elseif 条件2 then 语句2；
…
else 语句n;
end if;

应用在begin end中。
delimiter $
create function test_if(score INT) returns char
begin
if score >=90 and score<=100 then return ‘A’;
elseif score>=80 then return ‘B’;
elseif score>=60 then return ‘C’;
else return ‘D’;
end if;
end $

select test_if(86);

循环结构：都必须要在begin end中执行
1 while
2 loop
3 repeat

循环控制：
iterate 类似于continue,结束本次循环，继续下一次
leave 类似于break,结束当前所在循环

while语法：
标签名:while 循环条件 do
循环体
end while 标签名;

loop语法:
标签名:loop
循环体
end loop 标签名;

repeat语法：
标签名:repeat
循环体;
until 结束循环的条件
end repeat 标签名;

示例：
delimiter $
create procedure pro_while(IN insertCount INT)
begin
declare i INT DEFAULT 0;
while i<=insertCount Do
insert into admin(username,‘password’)values(CONCAT(‘Rose’,i),‘666’);
set i=i+1;
end while;
end $;

call pro_while(100);

示例2
delimiter $
create procedure test_while2(IN insertCount int)
begin
declare i int default 1;
a:while i<=insertCount do
insert into admin(username,‘password’) values(concat(‘xiaohua’,i),‘0000’);
if i>20 then leave a; #跳出循环体a
end if;
set i=i+1;
end while a;
end $