MySql—约束、TCL、视图
- 常见约束
- 标识列
- 事务控制语言(TCL)
- 视图
-delete VS truncate(事务使用时)
温故知新
#联合查询
一、含义
union :合并、联合,将多次查询结果合并成一个结果
二、语法
查询语句1
union [all]
查询语句2
union [all]
....
三、意义
1.将一条比较复杂的查询语句拆分成多条语句
2.适用于查询多个表的时候,查询的列基本是一致
四、特点
1.要求多条查询语句的查询列数必须一致
2.要求多条查询语句的各列类型、顺序最好一致
3.union去重,union all 包含重复项
##############
查询总结:
语法:
select 查询列表
from 表1 别名
连接类型 jion 表2
on 连接条件
where 筛选
group by 分组列表
having 筛选
order by 排序列表
limit 其实条目索引,条目数;
######################
DML语言:
一、插入
方式一:
语法:
insert into 表名(字段名,....) values(值,...);
特点:
1.要求值得类型和字段的类型要一致或兼容
2.字段的个数和顺序不一定与原来表中的字段个数和顺序一致,但必须保证值和字段一一对应
3.加入表中有可以为null的字段,注意可以通过以下两种方式插入null值
》字段和值都省略
》字段写上,值使用null
4.字段和值的个数必须一致
5.字段名可以省略,默认所有列
二、方式二
语法:
insert into 表名 set 字段=值,字段=值,....;
两种方式的区别:
1.方式一支持一次插入多行,语法如下:
insert into 表名【(字段名,...)] values(值,...),(值,...),...;
2.方式一支持子查询,语法如下:
insert into 表名
查询语句;
#################
一、修改单表的记录
语法:update 表名 set 字段=值,字段=值【where 筛选条件】;
二、修改多表的记录【补充】
语法:
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
###############
删除
方式一:使用delete
一、删除单表的记录
语法:delete from 表名 【where 筛选条件】【limit 条目数】
二、级联删除【补充】
语法:
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
方式二、使用truncate
语法:truncate table 表名
两种方式的区别【面试题】
1.truncate删除后,如果再插入,标识列从1开始
delete删除后,如果再插入,标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete 可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚
############
DDL语言:
》库的管理
一、创建库
create database [if not exists] 库名 【 character set 字符集名】
二、修改库
alter database 库名 character set 字符集名
三、删除库
drop database [if exists] 库名;
》表的管理
一、创建表
create table [if exists] 表名(
字段名 字段类型【约束】,
字段名 字段类型【约束】,
....
字段名 字段类型【约束】
);
二、修改表
1.添加列
alter table 表名 add column 列名 类型【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4.删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename [to] 新表名;
三、删除表
drop table [if exists] 表名;
四、复制表
1.复制表的结构
create table 表名 like 旧表;
2.复制表的结构+数据
create table 表名
select 查询列表 from 旧表 【where 筛选】;
》数据类型
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
特点:
1)M代表整数部位 +小数部位的个数,D代表小数部位
2)如果超出范围,则包out or range异常,并且插入临界值
3)M和D都可以省略,但对于定点数,M默认为10,D默认为0
4)如果精度要求较高,则优先考虑使用定点数
二、字符型
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:非空,用于保证该字段的值不能为空
比如姓名、学号等
default:默认,用于保证该字段有默认值
比如:性别
primary key:主键,用于保证该字段的值具有唯一性,并且非空
比如学号、员工编号等
unique:唯一,用于保证该字段的值具有唯一性,可以为空
比如:座位号
check:检查约束【mysql中不支持】
比如年龄、性别
foreign key:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的的关联列的值,在从表添加外键约束,用于引用主表中某列的值
比如:学生表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空、默认,其他都支持
主键和唯一的大对比:
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 yes no 至多有1个 yes,但不推荐
唯一 yes yes 可以有多个 yes,但不推荐
外键:
1.要求在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3.主表的关联列必须是一个key(一边是主键或唯一)
4.插入数据时,先插入主表,再插入从表,删除数据时,先删除从表,在删除主表。
insert into major values(1,'java');
insert into major values(2,'h5');
delete from stuinfo;
insert into stuinfo values(1,'john','男',null,19,1,1);
insert into stuinfo values(1,'john','男',null,19,2,2);
*/
create table 表名(
字段名 字段类型 列级约束,
字段名 字段类型
表级约束
);
#一、创建表时添加约束:
#1.添加列级约束
/*
语法:
直接在字段名和类型后面追加约束类型即可。
只支持:默认、非空、主键、唯一
*/
use students;
create table stuinfo(
id int primary,#主键
stuName varchar(20) not null,#非空
gender char(1) check(gender = '男' or gender = '女'),#检查
seat int unique 18,#默认约束
majorId int references major(id)#外键
);
create table major(
id int primary key,
majorName varchar(20)
);
desc stuinfo;
show index from stuinfo;
#2.添加表级约束
/*
语法:在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
*/
drop table if exists stuinfo;
create table stuinfo(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int,
constraint pk primary key(id),#主键
constraint uq unique(seat),#唯一键
constraint ck check(gender = '男' or gender = '女'),#检查
constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键
);#【这个的主键名没改掉】
drop table if exists stuinfo;
create table stuinfo(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int,
primary key(id),#主键
unique(seat),#唯一键
check(gender ='男' or gender ='女'),#检查
foreign key(majorid) references major(id)#外键
);
show index from stuinfo;
#什么时候用列级约束?什么时候用表级约束?
#通用写法:
create table if not exists stuinfo(
id int primary key,
stuname varchar(20) not null,
sex char(1),
age int default 18,
seat int unique,
majorid int,
constraint fx_stuinfo_major foreign key(majorid) references major(id)
);
二、修改表时添加约束
/*
1.添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2.添加表级约束
alter table 表名 add 【constraint】 约束类型(字段名) 外键;
*/
drop table if exists stuinfo;
create table stuinfo(
id int,
stuname varchar(20),
gender char(1),
seat int,
age int,
majorid int
);
desc stuinfo;
#1.添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
#2.添加默认约束
alter table stuinfo modify column age int default 18;
#3.添加主键
#1)列级约束
alter table stuinfo modify column id int primary key;
#2)表级约束
alter table stuinfo add primary key(id);
#4.添加唯一
#1)列级约束
alter table stuinfo modify column seat int unique;
#2)表级约束
alter table stuinfo add unique(seat);
#5.添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);
#三、修改表时删除约束
#1.删除非空约束
alter table stuinfo modify column stuname varchar(20) null;
#2.删除默认约束
alter table stuinfo modify column age int;
#3.删除主键
alter table stuinfo drop primary key;
#4.删除唯一
alter table stuinfo drop index seat;
#5.删除外键
alter table stuinfo drop foreign key fk_stuinfo_major;
show index from stuinfo;
大练兵1
######测试###########
#1.向表emp2的id列中添加primary key约束(my_emp_id_pk)
#列级约束
alter table emp2 modify column id int primary key;
#表级约束
alter table emp2
add constraint my_emp_id_pk primary key(id);#【自己】注意:表级约束改名字这个操作在mysql中只是支持语法,但是改不了
#2.向表dept2的id列中添加primary key约束(my_dept_id_pk)
[自己]
alter table dept2 modify column id int primary key;
#3.向表emp2中添加列dept_id,并在其中定义foreign key约束,与之相关联的列是dept2表中id列
alter table emp2 add column dept_id int;
alter table emp2 add constraint fk_emp2_dept2 foreign key(dept_id) references dept2(id);
位置 支持的约束类型 是否可以起约束名
列级约束: 列的后面 语法都支持,但外键没有效果 不可以
表级约束: 所有列的下面 默认和非空不支持,其他支持 可以(主键没有效果)
标识列
#标识列
/*
又称为自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1.标识列必须和逐渐搭配吗?不一定,但要求是一个key
2.一个表可以有几个标识列?至多一个!
3.标识列的类型只能是数值型
4.标识列可以通过set auto_increment_increment = 3;设置步长
*/
#一、创建表时设置标识列
drop table if exists tab_identity;
create table tab_identity(
id int primary key auto_increment,
name varchar(20)
);
truncate table tab_identity;
insert into tab_identity values(null,'john');
#insert into tab_identity values(10,'john');#设置初始值为10
insert into tab_identity(name) values('lucy');
select * from tab_identity;
show variables like '%auto_increment%';
set auto_increment_increment=3;#设置变长为3
#二、修改表是设置标识列
alter table tab_identity modify column id int primary key auto_increment;
#三、修改表时删除标识列
alter table tab_identity modify column id int;#alter table tab_identity modify column id int primary key ;[直接这样去掉auto_increment就可以,但是主键是唯一的,要删除主键]
TCL
#TCL
/*
Transaction Control Language 事务控制语言
事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
案例:转账
张三丰 1000
郭襄 1000
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name = '郭襄'
》show engines;#查看mysql支持的存储引擎
》在mysql中用的最多的存储引擎有:innodb,myisam,memory等。其中innodb支持事务,而myisam、memory等不支持事务
事务的特点:
事务的ACID(acid)属性
1)原子性(atomicity):一个事务不可再分割,要么都执行要么都不执行
2)一致性(consistency):一个事务执行会使数据从一个一致状态切换到另一个一致状态
3)隔离性(isolation):一个事务的执行不受其他事务的干扰
4)持久性(durability):一个事务一旦提交,则会永久的改变数据库的数据
事务的创建
隐式事务:事务没有明显的开启和结束的标记
比如:insert、update、delete语句
delete from 表 where id =1;
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
set autocommit=0;
步骤1:开启事务
set autocommit =0;
start transaction;#可选的
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
....
步骤3:结束事务
commit;#提交事务
rollback;#回滚事务
savepoint 节点名;#设置保存点
*/
show variables like 'autocommit';#查看autocommit变量
show engines;#查看引擎
#演示事务的使用步骤
#开启事务
set autocommit = 0;
start transaction;
#编写一组事务的语句
update account set balance=500 where username ='张无忌';
update account set balance = 1500 where username ='赵敏';
#回滚
#rollback;
#结束事务
commit;
select * from account;
############################
#事务并发问题介绍【数据库隔离就会解决这种问题】
》脏读
》不可重复读
》幻读
事务的隔离级别:
脏读 不可重复读 幻读
read uncommitted: yes yes yes
read committed: no yes yes
repeatable read: no no yes
serializable: no no no
mysql中默认第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
#数据库提供的4种事务隔离级别:
read uncommitted
read committed
repeatable read
serializable
[注:]mysql中默认的事务隔离级别为repeatable read
【sql命令】:
select @@tx_isolation;#查看当前的隔离级别
set [session] transaction isolation level read committed;#设置当前的mysql连接的隔离级别[session可以去掉,但是去掉之后发现不会立即生效 session:表示设置当前连接有效]
set global tansaction isolation level read committed;
#3.演示savepoint的使用
set autocommit=0;
start transaction;
delete from account where id =25;
savepoint a;#设置保存点
delete from account where id =28;
rollback to a;#回滚到保存点a
select * from account;
视图
#视图:
/*
含义:虚拟表,和普通表一样使用
mysql5.1版本出现的新特性,是通过表动态生成的数据
比如:舞蹈班和普通班级的对比
创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查,一般不能增删改
表 create table 保存了数据 增删改查
*/
#案例:查询姓张的学生名和专业名
方法一:【老办法】
select stuname,majorname
from stuinfo as s
inner join major m on s.`majorid` = m.`id`
where s.`stuname` like '张%';
方法二:【视图】
create view v1
as
select stuname,majorname
from stuinfo s
inner join major m on s.`majorid` = m.`id`;
select * from v1 where like '张%';
#一、创建视图
/*【通过视图我查看到我最想看到的那部分】
语法:
create view 视图名
as
查询语句;
视图的好处:
》重用sql语句
》简化复杂的sql操作,不必知道他的查询细节
》保护数据,提高安全性
*/
use myemployees;
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#1)创建
create view myv1
as
select last_name,department_name,job_title
from employees e
join department d on e.department_id = d.department_id
join jobs j on j.job_id = e.job__id;
#2)使用
select * from myv1 where last_name like '%a%';
#2.查询各部门的平均工资级别
#创建视图查看每个部门的平均工资
create view myv2
as
select avg(salary) ag,department_id
from employees
group by department_id;
#使用
select myv2.`ga`,g.grade_level
from myv2
join job_grades g
on myv2.`ag` between g.`lowest_sal` and g.`highest_sal`;
#3.查询平均工资最低的部门信息
#创建视图查看每个部门的平均工资
create view myv2
as
select avg(salary) ag,department_id
from employees
group by department_id;
#使用
select * from myv2 order by ag limit 1;
#4.查询平均工资最低的部门名和工资
create view myv3
as
select * from myv2 order by ag limit 1;
select d.*,m.ag
from myv3 m
join departments d
on m.`department_id` = d.`department_id`;
#二、视图的修改
#方式一:
/*
create or replace view 视图名 #该视图名存在就会修改,不存在就会创建
as
查询语句;
*/
select * from myv3;
create or replace view myv3
as
select avg(salary),job_id
from employees
group by job_id;
#方式二:
/*
语法:
alter view 视图名
as
查询语句
*/
alter view myv3
as
select * from myemployees;
#三、删除视图
/*
语法: drop view 视图名,视图名,...;
*/
drop view myv1,myv2,myv3;
#四、查看视图
desc myv3;#显示出来的太全了
show create view myv3;
#五、视图的更新【指的是视图中数据的更新】
create or replace view myv1
as
select last_name,email
from employees;
select * from myv1;
select * from employees;
#1.插入
insert into myv1 values('张飞','zf@qq.com');
#2修改
update myv1 set last_name = '张无忌' where last_name = '张飞';
#3.删除
delete from myv1 where last_name ='张无忌';
#具备以下特点的视图不允许更新
#1)包含以下关键字的sql语句:分组函数、distinct、group by 、having、union或者union all
create or replace view myv1
as
select max(salary),department_id
from employees
group by department_id;#这个整段代码是创建了一个视图
select * from myv1;
#更新
update myv1 set m=9000 where department_id = 10;#失败,不允许更新
#2)常量视图
create or replace view myv2
as
select 'john' name;
select * from myv2;
#更新
update myv2 set name ='lucy';
#3)select中包含子查询
create or replace view myv3
as
select (select max(salary) from employees) 最高工资;
#更新
select * from myv3;
update myv3 set 最高工资=2000;#报错,不允许更新
#4)join
create or replace view myv4
as
select last_name,department_name
from employees e
join departments d
on e.department_id = d.department_id;
#更新
select * from myv4;
update myv4 set last_name = '张飞' where last_name ='Whalen';#执行成功
insert into myv4 values('陈真','xxxxx');#报错,不允许插入
#5)from一个不能更新的视图
create or replace view myv5
as
select * from myv3;
#更新
select * from myv5;
update myv5 set 最高工资=10000 where department_id=60;#报错
#6)where子句的子查询引用了from子句中的表
create or replace view myv6
as
select last_name,email,salary
from employees
where employee_id in(
select manager_id
from employees
where manager_id is not null
);
#更新
select * from myv6;
update myv6 set salary =10000 where last_name ='k_ing';#报错
大练兵2
[案例讲解]视图的创建
#一、创建视图emp_v1,要求查询电话号码以'011'开头的员工姓名和工资、邮箱
create or replace view emp_v1
as
select last_name,salary,email
from employees
where phone_number like '011%';
select * from emp_v1;
select * from emp_v1 where phone_number like '011%';#不行,因为emp_v1这张表中没有phone_number 该字段
#二、创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
方法一、【老办法】
select max(salary) mx_dep,departmet_id
from employees
group by department_id
having max(salary)>12000;
select d.*,m.mx_dep
from department d
join (
select max(salary) mx_dep,departmet_id
from employees
group by department_id
having max(salary)>12000;
) m
on m.department_id = d.`department_id`;
方法二:
create or replace view emp_v2
as #+方法一的代码
select max(salary) mx_dep,departmet_id
from employees
group by department_id
having max(salary)>12000;
select d.*,m.mx_dep
from department d
join (
select max(salary) mx_dep,departmet_id
from employees
group by department_id
having max(salary)>12000;
) m
on m.department_id = d.`department_id`;
方法三、
create or replace view emp_v2
as
select max(salary) mx_dep,departmet_id
from employees
group by department_id
having max(salary)>12000;
select d.*,m.mx_dep
from departments d
join emp_v2 m
on m.department_id = d.`department_id`;
delete和truncate在事务使用时的区别
#2.delete和truncate在事务使用时的区别
#演示delete
set autocommit = 0;
start transaction;
delete from account;
rollback;#回滚之后表中的数据仍然还在
#演示truncate
set autocommit = 0;
start transaction;
truncate table account;
rollback;#回滚之后表中的数据没有了,truncate是真正的将表中的数据从磁盘上给删除了
大练兵3
【测试题讲解】
1.创建表book,字段如下:
bid 整型,要求主键
bname 字符型,要求设置唯一键,并非空
price 浮点型,要求有默认值10
btypeId 类型编号,要求引用bookType表的id字段
已知bookType表(不用创建),字段如下:
id
name
create table book(
bid int primary key,
bname varchar(20) unique not null,
price float default 10,
btypeId int,
foreign key(btypeid) references bookType(id)
);
2.开启事务
向表中插入1行数据,并结束
set autocommit =0;
insert into book(bid,bname,price,btypeId)
values(1,'小李飞刀',100,1);
3.创建视图,实现查询价格大于100的书名和类型名
crete view myv1
as
select bname,name
from book b
join bookType t on b.btypeid=t.id
where price>100;
4.修改驶入,实现查询价格在90-120之间的书名和价格
create or replace view myv1
as
select bname,price
from book
where price between 90 and 120;
5.删除刚才建的视图
drop view myv1;