mysql基础

约束: 表级约束,列级约束( 列名 数据类型 约束类型)
主键约束 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的值。

2,case结构
语法:
case 变量|表达式|字段
when 要判断的值 then 返回的值1|执行语句;
when 要判断的值 then 返回的值2|执行语句;

else 要返回的值n|执行语句;
end case;

#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 $

a代表循环体

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页