1.创建表
--创建表
CREATE TABLE if not EXISTS `t_student` (
`sno` int DEFAULT NULL,
`sname` varchar(10) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`age` int DEFAULT NULL,
`enterdate` date DEFAULT NULL,
`classname` varchar(15) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--删除表
drop table t_student;
--查询表结构
desc t_student;
--查看建表语句
show create table t_student;
-- 查询
select * from t_student;
--插入
insert into t_student values (101,"张三",'男',18,'2021-11-11','高三(1)','wangjl@126.com');
insert into t_student values (102,"张三",'男',18,'2021-11-11','高三(1)','wangjl@126.com');
--修改表中数据
update t_student set sex='女' where sno='102';
update t_student set email='WANGJL@126.com' where sno='102';
update t_student set sex='男' where email='wangjl@126.com'; --注意:影响两行
#注意
1.关键字,表名、字段名不区分大小写
2.默认情况下,内容不区分大小写
3.删除操作from关键字不可缺少
4.修改、删除数据别忘记加限制条件
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:
1、数据库名与表名是严格区分大小写的;
2、表的别名是严格区分大小写的;
3、列名与列的别名在所有的情况下均是忽略大小写的;
4、字段内容默认情况下是大小写不敏感的。
--删除操作
delete from t_student where sno='102';
--修改表结构
alter table t_student add score double(5,2); --总数,小数
--删除列
alter table t_student drop score;
--添加一列放在前面
alter table t_student add score double(5,2) first ;
alter table t_student add score double(5,2) after age ; --没起作用
update t_student set score=99.5 where sno='101';
2.表的完整性约束
#表的完整性约束
-- check 检查约束mysql8之后
drop table if exists t_student;
-- 创建表
CREATE TABLE if not EXISTS `t_student` (
`sno` int(6) primary key auto_increment,
`sname` varchar(10) not null,
`sex` char(1) default '男' check(sex='男'||'女'),
`age` int(3) check(age>=8 and age<=50) ,
`enterdate` date ,
`classname` varchar(15) ,
`email` varchar(20) unique
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 添加数据
insert into t_student values (null,'张三','男',18,'2003-09-01','高三(1)','wangjl@126.com');
insert into t_student values (null,'刘邦','男',18,'2003-09-01','高三(1)','liubang@126.com');
insert into t_student values (null,'关羽','男',18,'2003-09-01','高三(1)','guanyu@126.com');
-- 一条没插入成功 主键会自增一次 浪费一次主键?
insert into t_student values (default,'毛爷爷','男',18,'2003-09-01','高三(1)','mao@126.com');
-- 主键 null 或default
select * from t_student;
3.修改或添加表的完整性约束
-- 3.表的完整性约束
drop table t_student;
CREATE TABLE if not EXISTS `t_student` (
`sno` int(6) ,
`sname` varchar(10) ,
`sex` char(1) ,
`age` int ,
`enterdate` date ,
`classname` varchar(15) ,
`email` varchar(20) ,
constraint pk_stu primary key(sno), -- pk_stu 主键约束
constraint ck_stu_sex check(sex='男'|| sex='女'),
constraint ck_stu_age check(age>=8 and age <=50),
constraint ck_stu_email unique(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建表之后添加约束
CREATE TABLE if not EXISTS `t_student` (
`sno` int(6) ,
`sname` varchar(10) ,
`sex` char(1) ,
`age` int ,
`enterdate` date ,
`classname` varchar(15) ,
`email` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table t_student add constraint pk_stu primary key (sno);
alter table t_student modify sno int(6) auto_increment;
alter table t_student add constraint ck_stu_sex check(sex='男'|| sex='女');
alter table t_student add constraint ck_stu_age check(age>=8 and age <=50);
alter table t_student add constraint ck_stu_email unique(email);
-- 查看表结构
desc t_student;
4.外键约束
-- 4.外键约束
-- 父表:班级表
create table if not exists t_class(
cno int(4) primary key auto_increment,
cname varchar(20) not null,
room char(10)
);
-- 添加班级表数据
insert into t_class values(null,'高三(1)','r001'),(null,'高三(2)','r002'),(null,'高三(3)','r003');
drop table t_student;
-- 学生表
CREATE TABLE if not EXISTS `t_student` (
`sno` int(6) primary key auto_increment,
`sname` varchar(10) not null,
`sex` char(1) default '男' check(sex='男'||'女'),
`age` int(3) check(age>=8 and age<=50) ,
`enterdate` date ,
`classno` int(4) ,
`email` varchar(20) unique ,
constraint fk_stu_classno foreign key (classno) references t_class(cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 添加学生信息
insert into t_student(sno,sname,classno) values (null,'刘邦',1),(null,'项羽',2),(null,'嬴政',3);
-- 添加外键约束
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno);
-- 外键删除
-- 策略1: no action 不允许操作
-- 把班级2对应学生的班级 改为null
update t_student set classno =null where classno=2;
-- 删除班级2
delete from t_class where cno =2 ;
select * from t_class;
-- 策略2 cascade 级联操作:操作主表的时候影响从表的外键信息
-- 删除外键
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键
alter table t_student add constraint fk_stu_classno foreign key(classno) references t_class(cno) on update cascade on delete cascade;
-- 更新
update t_class set cno=5 where cno=3;
-- 策略3 set null 置空操作
-- 删除之前的外键约束
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键 都可以设置null 或者随更新而更新
alter table t_student add constraint fk_stu_classno foreign key(classno) references t_class(cno) on update cascade on delete set null;
-- 试试删除
delete from t_class where cno=1;
-- 2应用场合
-- 1).朋友圈删除 点赞 留言 都删除 -- 级联删除
-- 2).解散班级 对应的学生 职位班级null 就可以 set null
5.DDL和DML的一些其他操作
create table t_student_bak as select * from t_student;
-- 只要表结构
create table t_student_bak2 as select * from t_student where 1=2;
-- 指定条件
create table t_student_bak3 as select * from t_student where sno=4;
-- 删除数据操作:清空数据 保留表结构
delete from t_student;
truncate table t_student;
delete 和 truncate 区别
- 1.delete 为数据操作语言DML truancate 为数据定于语言DDL
- 2.delete 操作是将表中所有记录一条一条删除直到删除完,truncate操作则是保留了表的结构,重新创建了这个表,所有的状态都相当于新表。因此,truncate操作的效率更高。
- 3.delete 操作可以回滚,truncate操作会导致隐式提交,因此不能回滚
- 4.delete 操作执行成功后返回已删除的行数;截断操作不会返回已删除的行量,结果通常是“Affected rows:0”。delete 操作删除表中记录后,再次向表中添加新记录时,对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增;truncate操作则会重新从1开始自增。
6.DQL查询
6.1数据准备
仿oracle 自带的表
- 部门表
create table DEPT(
DEPTNO int(2) not null,
DNAME varchar(14),
LOC varchar(13)
);
-- 员工表
alter table DEPT add constraint pk_dept primary key (DEPTNO);
create table EMP(
EMPNO int(4) primary key,
ENAME varchar(10),
JOB varchar(9),
MGR int(4),
HIREDATE DATE,
SAL DOUBLE(7,2),
COMM double(7,2),
DEPTNO int(2)
);
alter table EMP add constraint fk_deptno foreign key (DEPTNO) references DEPT(DEPTNO);
-- 薪资等级
create table SALGRADE(
GRADE int primary key,
LOSAL double(7,2),
HISAL double(7,2)
);
-- 奖金
create table BONUS(
ENAME varchar(10),
JOB varchar(9),
SAL double(7,2),
COMM double(7,2)
);
-- 部门表
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
-- 工资等级
INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,DATE_FORMAT('1980-12-17', '%Y-%m-%d'), 800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,DATE_FORMAT('1981-02-20', '%Y-%m-%d'), 1600, 300, 30);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,DATE_FORMAT('1981-02-22', '%Y-%m-%d'), 1250, 500, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,DATE_FORMAT('1981-04-2', '%Y-%m-%d'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,DATE_FORMAT('1981-09-28', '%Y-%m-%d'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,DATE_FORMAT('1981-05-1', '%Y-%m-%d'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,DATE_FORMAT('1981-06-9', '%Y-%m-%d'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,DATE_FORMAT('1982-12-09', '%Y-%m-%d'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,DATE_FORMAT('1981-11-17', '%Y-%m-%d'), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,DATE_FORMAT('1981-09-8', '%Y-%m-%d'), 1500, 0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,DATE_FORMAT('1983-05-12', '%Y-%m-%d'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,DATE_FORMAT('1981-12-3', '%Y-%m-%d'), 950, NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,DATE_FORMAT('1981-12-3', '%Y-%m-%d'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,DATE_FORMAT('1982-01-23', '%Y-%m-%d'), 1300, NULL, 10);
6.2 查询语句
-- 对emp表查询
select * from EMP;
-- 显示部分列
select empno,ename,sal from EMP ;
-- where 子句
select * from EMP where sal > 2000;
select empno,ename,sal from EMP where sal > 2000;
-- 起别名
select empno 员工编号,ename as 姓名 ,sal as "工 资" from EMP where sal > 2000;
-- 算术运算符
select empno ,ename,sal ,sal+1000 as '涨薪后' from EMP where sal > 2000;
select empno ,ename,sal ,comm ,sal+comm from EMP ;
-- 去重操作
select job from EMP;
select distinct jobfrom EMP;
select job ,deptno from EMP;
-- 组合去重
select distinct job ,deptno from EMP;
-- 排序
select * from EMP order by sal asc ; -- 默认升序
select * from EMP order by sal desc; -- 降序
select * from EMP order by sal desc,deptno desc; -- 组合排序
-- where 子句
select * from EMP where deptno=10;
select * from EMP where deptno>10;
select * from EMP where deptno>=10;
select * from EMP where deptno<10;
select * from EMP where deptno<>10;
select * from EMP where deptno!=10;
select * from EMP where job='CLERK';
select * from EMP where job='clerk';
select * from EMP where binary job='clerk'; -- binary 区分大小写
select * from EMP where HIREDATE < '1981-12-25';
select * from EMP where sal > 1500 and sal < 3000;
select * from EMP where sal between 1500 and 3000 order by sal;
select * from EMP where deptno =10 or deptno=20;
select * from EMP where deptno =10 || deptno=20;
select * from EMP where deptno in(10,20);
-- 模糊查询
select * from EMP where ename like "%A%";
select * from EMP where ename like '__A%';
-- 关于null判断
select * from EMP where comm is null ;
select * from EMP where comm is not null ;
-- 小括号使用
select * from EMP where job='SALESMAN' OR job ='CLERK' AND sal >=1500;
select * from EMP where job='SALESMAN' OR (job ='CLERK' AND sal >=1500);
select * from EMP where (job='SALESMAN' OR job ='CLERK') AND sal >=1500;
7.函数
-- 7.函数
-- 单行函数
select empno,ename,lower(ename),upper(ename),sal from EMP;
-- 多行函数 (分组函数)
select max(sal),min(sal) ,count(sal),sum(sal),avg(sal) from EMP;
-- 除了多行函数(仅5个),都是单行函数
-- 单行函数
-- 1.字符串函数
-- subst
select ename ,length(ename),substring(ename,2,6) from EMP;
-- 2.数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) ,mod(10,3) from dual; -- dual 实际是一个伪表
-- 没有where条件 from dual 可以省略
-- 3.日期与时间函数
select * from EMP;
SELECT CURDATE(),CURTIME();
SELECT NOW(),sysdate(),sleep(3),now() ,sysdate() from dual;
-- 4.流程函数
-- if相关
select empno,ename,sal ,if(sal>=2500,'高薪','底薪') as '薪资等级' from EMP;
SELect empno ,ename ,sal,comm,sal + ifnull(comm,0) '实际工资' from EMP;
select nullif(1,1),nullif(1,2) from dual; -- 如果value1等于value2,则返回null,否则返回value1
-- case相关
-- case 等值判断
select empno,ename ,job ,
case job
when 'CLERK' THEN '店员'
when 'SALESMAN' THEN '销售'
when 'MANAGER' THEN '经理'
else '其他'
end '岗位'
,sal from EMP;
-- case 区间判断
select empno ,ename ,sal ,
case
when sal<=1000 then 'A'
-- 5.json函数
-- 6.其他函数
select database(),user(),version() from dual;
select PASSWORD('123456') from dual ;
-- 多行函数自动忽略null值
select max(comm),min(comm) ,count(comm),sum(comm),avg(comm) from EMP;
-- group by 分组
select deptno,avg(sal) from EMP ; -- 字段和多行函数不可以同时使用
select deptno,avg(sal) from EMP group by deptno; -- 字段和多行函数使用,除非这个字段属于分组
-- 统计每个岗位的平均工资
select job,avg(sal) from EMP group by job;
-- having
-- 分组之后 筛选
select deptno, avg(sal) 平均工资 from EMP group by deptno having avg(sal) > 2000;
select deptno, avg(sal) 平均工资 from EMP group by deptno having avg(sal) > 2000 order by deptno desc;
select job,avg(sal) from EMP where job !='MANAGER' GROUp by job;
select job,avg(sal) from EMP GROUp by job having job!='manager';
-- where 在分组前过滤 having在分组后过滤
-- 单表查询练习
-- 1.列出工资最小值小于2000的职位
select job,avg(sal) from EMP group by job;
select job,avg(sal) from EMP group by job having (avg(sal) < 2000);
-- 2.列出平均工资大于1200元的部门和工作搭配组合
select deptno ,job ,avg(sal) from EMP group by deptno,job ;
select deptno ,job ,avg(sal) from EMP group by deptno,job having avg(sal) >1200 order by avg(sal);
-- 3.统计人数小于4的部门的平均工资
select deptno ,count(1) from EMP group by deptno ;
select deptno ,count(1),avg(sal) from EMP group by deptno;
select deptno ,count(1),avg(sal) from EMP group by deptno having count(1) <4;
-- 4.统计各部门的最高工资,排除最高工资小于3000的部门
select deptno ,avg(sal) , max(sal) from EMP group by deptno having max(sal) <3000;
8.多表查询
-- 查询员工编号 姓名 部门编号
select empno ,ename, deptno from EMP ;
-- 查询员工编号 姓名 部门编号 部门名称
select t1.empno ,t1.ename, t1.deptno, t2.DNAME from EMP t1 , DEPT t2 where t1.DEPTNO=t2.DEPTNO ;
-- 交叉连接 cross 可以省略 oracle中不可以省略
select * from EMP cross join DEPT ;-- 笛卡尔乘积 没有实际意义 有理论意义
-- 自然连接
-- 优点 自动匹配所有的同名词,同名列只展示一次,简单
select * from EMP natural join DEPT ;
-- 查询字段的时候,没有指定字段所属的数据库表,效率低
select empno ,ename ,sal,dname , loc from EMP natural join DEPT ;
-- 解决
select EMP.empno ,EMP.ename ,EMP.sal,DEPT.dname , DEPT.loc from EMP natural join DEPT ;
-- 表起别名
-- 自然连接 缺点 natural join 缺点:自动匹配表中所有的同名列,但是有时候我们希望只匹配部分同名列。
-- 解决 内连接 using 子句
-- inner join inner 可以不写
select * from EMP e join DEPT d USING(DEPTNO); -- 这里不能写natural join,实际是内连接
-- using 关联的字段必须是同名的
-- 解决: 内连接 on 子句
select * from EMP e inner join DEPT d on (e.DEPTNO =d.DEPTNO);
-- 多表连接查询的类型 1 交叉连接 cross join 2 .自然连接 natural join
-- 3.内连接 using 4.内连接 on子句
-- 条件
-- 1.筛选提交: where having
-- 2.连接条件 on,using natural
-- 3.SQLL99语法 筛选条件和连接条件是分开的
-- inner join -- on 子句
select *
from EMP e
inner join DEPT d
on e.DEPTNO = d.DEPTNO;
select * from EMP ;
SELECT * FROM DEPT ;
-- 问题
-- 1.40号部门没有员工 但是没有显示在查询结果中
-- 2.员工scott没有部门,没有显示在结果中
-- 外连接 除了显示匹配的数据之外 还可以显示不匹配的数据
-- 左外连接 left outer join 左表
select *
from EMP e
left outer join DEPT d
on e.DEPTNO = d.DEPTNO;
-- 右外连接
select *
from EMP e
right outer join DEPT d
on e.DEPTNO = d.DEPTNO;
-- 全外连接 MySQL 不支持 oracle支持
select *
from EMP e
full outer join DEPT d
on e.DEPTNO = d.DEPTNO;
-- 解决MySQL不支持全外连接 并集
select *
from EMP e
left outer join DEPT d
on e.DEPTNO = d.DEPTNO
union -- 并集
select *
from EMP e
right outer join DEPT d
on e.DEPTNO = d.DEPTNO;
-- union all 不去重 效率高
select *
from EMP e
left outer join DEPT d
on e.DEPTNO = d.DEPTNO
union all-- 并集
select *
from EMP e
right outer join DEPT d
on e.DEPTNO = d.DEPTNO;
-- mysql中对接好操作支持比较弱,只支持并集操作 差集不支持
-- 查询员工的编号 姓名 薪水 部门编号 部门名称 薪水等级
select * from EMP;
SELECT * FROM DEPT ;
SELECT * FROM SALGRADE;
SELECT e.ename ,e.ename,e.SAL,e.DEPTNO,d.DNAME,e.SAL FROM EMP e
right outer join DEPT d
on e.DEPTNO = d.DEPTNO
inner join SALGRADE s
on e.SAL BETWEEN s.LOSAL and s.HISAL ;
-- 自连接
select * from EMP;
-- 左外连接
select e1.EMPNO 员工编号 ,e1.ENAME 员工姓名 ,e1.MGR 经理编号 ,e2.ename 经理名称 from EMP e1
left outer join EMP e2
on e1.MGR=e2.EMPNO;
-- 子查询
-- 查询所有比“CLARK” 工资高的员工的信息
select sal from EMP where ename ="CLARK" ;
select * from EMP where sal> 2450.00;
select * from EMP where sal >(select sal from EMP where ename ="CLARK");
-- 单行子查询
-- 查询工资高于平均工资的雇员名字和工资
select ename,sal
from EMP
where sal > (select avg(sal) from EMP );
-- 查询和clark同一部门且比他工资低的雇员名字和工资
select ename ,sal from EMP
where deptno =(select deptno from EMP where ename='CLARK')
AND sal <(select sal from EMP where ename ="CLARK" );
-- 查询职务和scott相同 比scott雇佣的时间早的雇员信息。
select * from EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT')
and hiredate <(select hiredate from EMP where ename="SCOTT");
-- 多行子查询
-- 1.查询【部门20中职务同部门10的雇员一样的】雇员信息
select JOB from EMP where DEPTNO=10;
select * from EMP where DEPTNO=20;
SELECT * FROM EMP WHERE DEPTNO=20
AND JOB IN (SELECT JOB FROM EMP WHERE DEPTNO=10);
-- 2.查询工资比所有的“SALESMANE”都高的雇员的编号,姓名和工资
select ename ,EMPNO ,sal from EMP
WHERE SAL >(SELECT max(SAL) FROM EMP where job='SALESMAN' );
-- 3.查询工资低于任意一个clerk的工资的雇员信息
select * from EMP
WHERE sal <(SELECT MAX(sal) FROM EMP WHERE job ='CLERK')
and job != 'CLERK';
-- 相关子查询:子查询不可以独立运行,并且先运行外查询 在运行子查询
-- 不相关子查询:子查询可以独立运行,先运行子查询,再运行外查询。
-- 查询最高工资的员工
select * from EMP WHERE SAL =(SELECT MAX(SAL) FROM EMP);
-- 查询本部门最高工资的员工 (相关子查询)
select * from EMP WHERE DEPTNO =10 AND SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10)
UNION
select * from EMP WHERE DEPTNO =20 AND SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20)
UNION
select * from EMP WHERE DEPTNO =30 AND SAL=(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);
SELECT * from EMP e WHERE SAL=(select max(sal) from EMP WHERE deptno =e.deptno) ORDER BY e.deptno;
-- 查询工资高于其所在岗位的平均工资的那些员工
SELECT * FROM EMP WHERE JOB ='CLERK' AND SAL>=(SELECT AVG(SAL) WHERE JOB ='CLERK');
SELECT * FROM EMP e WHERE SAL>=(SELECT AVG(SAL) from EMP WHERE JOB=e.JOB);
9.事物
-- 事物的特性
原子性 要么都成功 要么都失败
一致性 从一个一致性状态,变成另一个一致性状态
隔离性 各个事物互不干扰
持久性 事物一旦提交,对数据所作的改变,都要记录存储器中
-- 创建账户表
create table account(
id int primary key auto_increment,
uname varchar(10) not null,
balance double
);
select * from account;
insert into account values( null,'a',2000),(null,'b',2000);
update account set balance =balance -200 where id=2;
update account set balance =balance +200 where id=2;
-- 手动开启事物
start TRANSACTION ;
update account set balance =balance -200 where id=1;
update account set balance =balance +200 where id=2;
-- 手动回滚
ROLLBACK ;
-- 手动提交
commit ;
-- 事物并发问题
脏读 一个事物读到了另一个事物还未提交的数据。
不可重读读 一个事物多次读到的数据不一致。数据修改
幻读 插入一条记录 或删除一条记录 读到数据条数不同
-- 事物隔离级别 脏读 不可重读读 幻读
read uncommitted 读未提交 √ √ √
read committed 读已提交 X √ √
repeatable read 可重复读 X X √
serializable 序列化 X X X
-- 查看事物隔离级别
select @@transaction_isolation;
-- 设置事物的隔离级别 (设置当前会话的隔离级别)
set session transaction isolation level read uncommitted ;
set session transaction isolation level read committed ;
set session transaction isolation level repeatable read ;
set session transaction isolation level serializable ;
10.视图
select * from EMP;
-- 创建单表的视图
create view myview1
as
select EMPNO,ENAME ,JOB,DEPTNO from EMP
WHERE DEPTNO =20;
-- 查看视图
SELECT * FROM myview1;
-- 插入数据 实际表中也增加一行数据
insert into myview1 values(9999,'Lucy','CLERK',20);
-- 视图中没有 但是实际表中插入一条数据
insert into myview1 values(8888,'James','CLERK',30);
-- 删除视图
drop view myview1;
-- 创建视图-校验
create view myview1
as
select EMPNO ,ENAME,JOB,DEPTNO
FROM EMP
where deptno=20
with check option;
-- 插入一条数据
insert into myview1 values(9999,'Lucy','CLERK',20);
-- CHECK OPTION failed 'mysqldb.myview1' 校验不通过
insert into myview1 values(8888,'James','CLERK',30);
-- 创建多表视图
create or replace view myview02
as
select e.empno ,e.ename,e.sal,d.deptno,d.dname
from EMP e join DEPT d
on e.deptno = d.deptno
where e.sal>2000;
select * from myview02;
-- 创建统计视图
create or replace view myview03
as
select e.deptno,d.dname ,avg(e.sal),min(e.sal),count(1)
from EMP e
join DEPT d
using(deptno)
group by e.deptno;
select * from myview03;
-- 创建基于视图的视图
create or replace view myview04
as
select * from myview03
where deptno =20;
select * from myview04;
11.存储过程
select * from EMP where ename like "%A%";
create procedure mypro01( name varchar(10))
begin
if name is null or name ="" then
select * from EMP ;
else
select * from EMP where ename like concat('%',name,'%');
end if;
end ;
-- 删除 存储过程
drop procedure mypro01;
-- 调用存储过程
call mypro01(null);
call mypro01('A');
-- 定义一个 有返回值的存储过程
create procedure mypro2(in name varchar(10),out num int(3))
begin
if name is null or name ="" then
select * from EMP;
else
select * from emp where ename like concat('%',name,'%');
end if ;
select found_rows() into num;
end;
call mypro2(null,@num);
select @num;