数据类型和约束(ddl)
数据类型
一、数值型
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 主键:不可重复且非控股
foreign key 外键: 该字段的值引用别的表的字段
主键和唯一:
主键非空且唯一,一个表只能由一个主键
唯一 可以空,一个表可以有多个唯一
(都支持组合键)
外键:
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 xx(
名称1 类型1 primary key[主键] ,
名称2 类型2 default(18),
名称3 类型3 not null【非空】,
名称4 类型4 unique【唯一】,
constraint 约束名 foregin key(名称5) references 主表(主字段名称)
);
注意:
约束 | 支持类型 | 可以起约束名 |
---|---|---|
列级约束 | 除了外键 | 不可以 |
表级约束 | 除了非空和默认 | 可以,但对主键无效 |
修改约束名:
添加修改约束时:
alter table 表名 modify column 字段名 字段类型 约束;
删除约束时:
alter table 表名 modify column 字段名 字段类型;
(别写就行啦)
(主键这么写也行)
alter table 表名 add primary key(字段名);
删除:
alter table 表名 drop primary key;
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;
唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
自增长列
特点:
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 字段名 字段类型 约束
TCL语言:
事务
含义: 一条或多条数据库代码组成的,一起执行或者一起都不执行
特点(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:串行化 | √ | √ | √ |
视图 存储结构 和 函数
视图
含义:一张虚拟的表,数据来自于表,通过执行时动态生成
创建:
create view 视图
as
查询语句
修改:
create or replace view 视图名(alter view 视图名)
as
查询语句
六、使用
1.插入
insert
2.修改
update
3.删除
delete
4.查看
select
注意:视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新
①包含分组函数、group by、distinct、having、union、
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图
七、视图和表的对比
关键字 | 是否占用物理空间 | 使用 |
---|---|---|
视图 view | 占用较小,只保存sql逻辑 | 一般用于查询 |
表 table | 保存实际的数据 | 增删改查 |
变量:
系统变量
说明:系统提供 不许自定义的变量
(全局变量:服务器层面 具有super 权限才可以更改
会话变量:服务器为每一个链接的客户端都提供了系统变量 )
语法:
①查看系统变量
show 【global|session 】variables like ‘’; 如果没有显式声明global还是session,则默认是session
②查看指定的系统变量的值
select @@【global|session】.变量名; 如果没有显式声明global还是session,则默认是session
③为系统变量赋值
方式一:
set 【global|session 】 变量名=值; 如果没有显式声明global还是session,则默认是session
方式二:
set @@global.变量名=值;
set @@变量名=值;
自定义变量:
用户变量(链接)
局部变量(作用域: 仅仅在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中的方法,将一组完成特定功能的逻辑语句包装起来,对外暴露名字
好处:
1、提高重用性
2、sql语句简单
3、减少了和数据库服务器连接的次数,提高了效率
存储过程
创建:
create procedure 存储过程名(in/out/nout(参数模式) 参数名 参数类型)
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 函数名;
流程控制结构
说明:
顺序结构:程序从上往下依次执行
分支结构:程序按条件进行选择执行,从两条或多条路径中选择一条执行
循环结构:程序满足一定条件下,重复执行一组语句
分支结构
if函数 case when then… if流程
特点:
1、if函数
功能:实现简单双分支
语法:
if(条件,值1,值2)
位置:
可以作为表达式放在任何位置
2、case结构
功能:实现多分支
语法1:
case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2;
…
else 语句n;
end [case];
语法2:
case
when 条件1 then 语句1;
when 条件2 then 语句2;
…
else 语句n;
end [case];
位置:
可以放在任何位置,
如果放在begin end 外面,作为表达式结合着其他语句使用
如果放在begin end 里面,一般作为独立的语句使用
3、if结构
功能:实现多分支
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;
位置:
只能放在begin end中
循环结构
位置:
只能放在begin end中
特点:都能实现循环结构
对比:
①这三种循环都可以省略名称,但如果循环中添加了循环控制语句(leave或iterate)则必须添加名称
②
loop 一般用于实现简单的死循环
while 先判断后执行
repeat 先执行后判断,无条件至少执行一次
1、while
语法:
【名称:】while 循环条件 do
循环体
end while 【名称】;
2、loop
语法:
【名称:】loop
循环体
end loop 【名称】;
3、repeat
语法:
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;
二、循环控制语句
leave:类似于break,用于跳出所在的循环
iterate:类似于continue,用于结束本次循环,继续下一次