数据库进阶版第二辑

发表时间: 2024-03-12 08:32

查看表结构

/*展示新创建表的结构*/ DESC t_student;

查看建表语句

/*查看建表语句*/ show CREATE TABLE t_student;

列类型

整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4)。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示主键自增:不使用序列，通过auto increment，要求是整数类型

浮点数类型

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充。 score double(4,1)score double(4,1)--小数部分为1位，总宽度4位，并且不会自动扩充。

字符串类型

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

日期类型

子主题 1

创建表

/*查看建表语句*/ show CREATE TABLE t_student;
/*创建学生表*/ CREATE TABLE t_student( sno int(6), sname VARCHAR(10), sex CHAR(1), age int(3), enterdate date, classname VARCHAR(10), email VARCHAR(15) );
修改表名ALTER TABLE your_table_name RENAME TO acount;

数据查询语言

查询所有列select*from t_student
查询部分列 select empo ,ename,sal from emp
查询部分行select *from emp where sal>2000
-- as alias 别名 select empno as 员工编号,ename as 姓名,sal as 工资 from emp as可以省略
--算术运算符: select empno,ename,sal,sal+1000 as 涨后',deptno from emp where sal < 2500;
去重操作: select job from emp; select distinct job from emp; select distinct job,deptno from emp;-- 对后面的所有列组合去重，而不是单独的某一列去重
排序: select * from emp order by sal 默认情况下升序排列 select * from emp order by sal asc -- asc 升序，可以默认不写 select * from emp order by sal -- desc 降序
-- 在工资升序的情况下，deptno按照降序排列 select* from emp order by sal asc ,deptno desc;

表中数据操作语言

插入

/*表中插入数据*/ insert into t_student VALUES (1,'张三','男',18,'2022-5-8','软件1班','123@126.com'); /*插入指定的列*/ insert into t_student(sno,sname,sex,age) VALUES(1,'张三','男',18);
注意事项 1、int 宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节 2、时间的方式多样1256-1223'"1256/12/23""1256.12.23" 3、字符串不区分单引号和双引号 4、如何写入当前的时间 now(),sysdate()，CURRENT DATEO() 5、char() varchar() 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。 6、如果不是全字段插入数据，需要指定字段的名字

修改

注意事项 1.关键字，表名，字段名不区分大小写 2.默认情况下，内容不区分大小写 3.删除操作from关键字不可缺少 4.修改，删除数据别忘记加限制条件
UPDATE t_student SET sex= '女' WHERE sno = 3;

删除

DELETE from t_student where sno =3; delete from t_student删除全表
(2] delete和truncate的区别:从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面: (1)DELETE为数据操作语言DML: TRUNCATE为数据定义语言DDL 2)DELETE操作是将表中所有记录一条一条删除直到删除完:TRUNCATE操作则是保留了表的结构，重新建了这个表，所有的状态都相当干新表因此，TRUNCATE操作的效率更高 (3)DELETE操作可以回滚，TRUNCATE操作会导致隐式提交，因此不能回滚(在第十章中会讲解事务的提交和回滚) 4)DELETE操作执行成功后会返回已删除的行数(如删除4行记录，则会显示“Afected rows:4”);截断操作不会返回已删除的行量，结通常是“Aected rows:0”。DELEE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增;TRUNCATE操作则会重新从1开始自增

函数

单行函数

- 函数举例: select empno,ename,lower(ename),upper(ename),sal from emp;-- 函数的功能:封装了特定的一些功能，我们直接拿过来使用，可以实现对应的功能函数作用:为了提高select的能力注意:函数没有改变数据自身的值，而是在真实数据的上面进行加工处理，展示新的结果而已。 - 单行函节lower(ename),upper(ename) :改变每一条结果，每一条数据对应一条结果--
单行函数是指对每一条记录输入值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函计算得到每条记录的计算结果。常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数
函数练习

字符串函数

select ename,length(ename)substring(ename 2，3) from emp; substring从字符2开始，截取长度3

数值函数

select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表 ,没有where语句from dual可以省略不写 abs(-5)绝对值,ceil(5.3)向上取整,floor(5.9)向下取整,round(3.14)四舍五入 select 10/3，10%3，mod(10,3)

日期与时间函数

select curdate(),curtime() ; curdate()年月日 curtime()时分秒 select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒

流程函数

select empno,ename,sal,if(sal>=2500，高薪"，底薪') as薪资等级’from emp; -- 类似if-else 双分支结构 select empno,ename,sal,comm,sal+ifnull(comm,0) from emp;如果comm是nul1，那么取值为0 select nullif （1,1），nullif （1,2）from dua1;如果value1等于value2，则返回nul1，否则返回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' when sal<=2000 then 'B' when sal<=3000 then 'C' else'D' end,"工资等级" deptno from emp ;

json函数
其他函数

select database(),user(),version() from dual;

多行函数

多行函数是指对一组数据进行运算，针对这一组数据(多行记录)只返回一个结果，也称为分组函数。
函数练习

select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp; -max(sal),min(sal),count(sal),sum(sal),avg(sal);多条数据，最终展示一个结果 -多行函数 - max(),min(),count()针对所有类型sum(),avg() 只针对数值型类型有效 select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
统计表的记录数

--计数:count 方式一 select count(ename) from emp; select count(*) from emp; --一般用*统计，以免其他数据有空
-- 统计表的记录数:方式2 select 1 from dual; --伪表只能查询到一个 select 1 from emp; --显示1的行数 select count(1) from emp; 统计多少个1

PS: 除了多行函数 (max,min,count,sum,avg) ，都是单行函数,多行函数会忽略null值

单表查询

where子句

where+关系运算符

select * from emp where deptno = 10; select * fromemp wheredeptno > 10; select * from emp wheredeptno > 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区分大小写

where+逻辑运算符

select *from emp where sal > 1500 and sal < 3000; -- (1566,3888) select * from emp where sal > 1500 8& sal < 3000; select * from emp where sal > 1500 and sal < 3000 order by sal; select * from emp where sal between 1500 and 3000; -- [1500,3000]
select * from emp where deptno = 10 or deptno = 20; select * from emp where deptno in (10,20); select * from emp where job in(MANAGER','CLERK,'ANALYST');

where +模糊查询

查询名字中带A的员工 -- %代表任意多个字符 0,1,2，select * from emp where ename like "%A%-任意一个字符 --_任意一个字符 select * from emp where ename like '_A%'

关于nul1的判断: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);
where分组前过滤

group by 分组

select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用 select deptno,avg(sal) from emp group by deptno;· 字段和多行函数不可以同时使用,除非这个字段属于分组 select deptno,avg(sal) from emp group by deptno order by deptno desc;
select job,avg(sal) from emp group by job; select job,lower(job),avg(sal) from emp group by job;

having分组

分组之后过滤
--统计各个部门的平均工资 ,只显示平均工资2000以上的 - 分组以后进行二次筛选 having select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000 select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资>2000 select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
统计各个岗位的平均工资,除了MANAGER 方法1: select job,avg(sal) from emp where job != 'MANAGER' group by job; 方法2: select job,avg(sal) from emp group by job having job != 'MANAGER' ;where在分组前进行过滤的，having在分组后进行后滤。

select 语句的执行顺序

from--where -- group by- select - having- order by

多表查询

交叉连接cross join

select*from emp cross join dept;-- 14*4 = 56条笛卡尔乘积 : 没有实际意义，有理论意义,cross可以省略不写

自然连接natural join :

select* from emp natural join dept;
select empno,ename, sal,dname,locfrom empnatural join dept; 缺点:查询字段的时候，没有指定字段所属的数据库表，效率低解决:指定表名: select from emp emp.empno, emp .ename, emp .sal,dept .dname, dept.loc,dept.deptno natural join dept; 缺点:表名太长解决:表起别名 select e.empno,e.ename,e.sal,d.dname,d.loc ,d.deptno from emp enatural join dept

内连接

优点自动匹配所有的同名列，通名列只展示一次缺点自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列 ——解决inner-using子句 select from emp e inner join dept d using(deptno) inner可以不写
- using缺点:关联的字段，必须是同名的解决: 内连接 - on子句:用的多 select *from emp einner join dept don (e.deptno = d.deptno); select *from emp einner join dept don (e.deptno = d.deptno)where sa1 > 3500;

外连接

内连接问题: 1.49号部分没有员工，没有显示在查询结果中 2.员工scott没有部门门，没有显示在查询结果中外连接:除了显示匹配的数据之外，还可以显示不匹配的数据
左外连接 left outer join

-左外连接: left outer join左面的那个表的信息，即使不匹配也可以查看出效果 select *from emp e left outer join dept d on e.deptno = d.deptno;

右外连接

右外连接: right outer join右面的那个表的信息，即使不匹配也可以查看出效果outer可以省略不写 select *from emp e right outer join dept don e.deptno = d.deptno;

全外连接full outer join

mysql不支持，oracle支持

union并集

解决mysql中不支持全外连接的问题: select *from emp e left outer join dept don e.deptno = d.deptno union --并集select *from emp eright outer join dept don e.deptno = d.deptno;
select *from emp e left outer join dept don e.deptno = d.deptno union all-- 并集不去重select *from emp eright outer join dept don e.deptno = d.deptno;

练习

select e.ename,e.sal,e.empno,e.deptno,d.dname, s.*from emp e right outer join dept don e.deptno = d.deptnoinner join salgrade son e.sal between s.losal and s.hisal
select *from emp e1 inner join emp e2on e1 .mgr = e2 .empno ; 自连接

92语法

查询员工的编号，员工姓名，薪水，员工部门编号，部门名称:select e .empno,e .ename ,e .sale.deptno , d .dnamefrom emp e,dept d-- 相当于99语法中的cross join ,出现笛卡尔积，没有意义
select e .empno , e .ename , e . sal, e .deptno , d. dnamefrom emp e,dept dwhere e.deptno = d.deptno; -- 相当于99语法中的natural join
- 查询员工的编号，员工姓名，薪水，员工部门编号，部门名称，查询出工资大于2880的员工 select e.empno,e .ename ,e.sal,e.deptno,d.dnamefrom emp e,dept d where e.deptno = d.deptno and e.sal > 2000:
查询员工的名字，岗位，上级编号，上级名称(自连接):select e1.ename,e1.job,e1.mgr ,e2.enamefrom emp e1,emp e2where e1.mgr = e2.empno;

子查询

相关子查询

执行顺序

子查询不可以独立运行，并且先运行外查询，再运行子查询

查询最高工资的员工 (不相关子查询) select * from emp where sal = (select max(sal) from emp)查询本部门最高工资的员工(相关子查询) 方法1: 通过不相关子查询实现: 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)- 缺点: 语句比较多，具体到底有多少个部分未知 - 方法2:相关子查询 select * from emp e where sal = (select max(sal) from emp where deptnoe.deptncorder by deptno
查询工资高于其所在岗位的平均工资的那些员工 - 相关子查询: select * from emp e where sal >= (select avg(sal) from emp where job = e.job)

不相关子查询

执行顺序

子查询可以独立运行，先执行子查询在执行外查询

单行子查询

-- 查询工资高于平均工资的雇员名字和工资 select ename,sal from emp where sal >(select avg(sal) from emp);
查询和CLARK同部门且比他工资低的雇员名字和工资 select ename,sal from emp where deptno = (select 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!)
查询所有比“CLARK”工资高的员工的信息步骤聚1:“CLARK”工资 select sal from emp where ename = 'CLARK!-- 2450 -- 步骤2:查询所有工资比2450高的员工的信息 select * from emp where sal > 2450;-. --两次命令解决问题 --》效率低，第二个命令依托于第一个命令，第一个命令的结果给第二个命令使用，但是-- 因为第一个命令的结果可能不确定要改，所以第二个命令也会导致修改 -- 将步骤1和步骤2合并 --》子查询: select * from emp where sal > (select sal from emp where ename = 'CLARK');·1个命令解决问题

多行子查询

查询部门20中的雇员信息 select * from emp where deptno = 20;-CLERK,MANAGER,ANALYST 部门10的雇员的职务:- select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK -- 查询部门20中职务同部门10的雇员一样的雇员信息。 select * from empwhere deptno = 20and job in (select job from emp where deptno = 10) 第一种 select * from empwhere deptno = 20 and job = any(select job from emp where deptno = 10) 第二种
查询雇员的编号、名字和工资 select empno,ename,sal from emp “SALESMAN”的工资: select sal from emp where job =SALESMAN 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资多行子查询方式 select empno,ename , sal from emp where sal > all(select sal from emp where job = 'SALESMAN‘) 单行子查询:-- select empno,ename, sal from emp where sal > (select max(sal) from emp where job ='SALESMAN');
查询工资低于任意一个“CLERK”的工资的雇员信息select * from emp where sal < any(select sal from emp where job = 'CLERK')and job !='CLERK! -- 单行子查询: select * from emp where sal < (select max(sal) from emp where job = 'CLERK'and job !='CLERK!

表结构操作（数据定义语言）

增加

ALTER TABLE t_student ADD score DOUBLE(5,2);-- 5是总位数 2是小数位数默认添加在最后一列
ALTER TABLE t_student ADD score DOUBLE(5,2) FIRST; -- 5是总位数 2是小数位数默认添加在第一列
ALTER TABLE t_student ADD score DOUBLE(5,2) AFTER sex; 增加在性别之后

删除

ALTER TABLE t_student DROP score;
drop table t_student 删除表

修改列字段及列类型

列类型

ALTER TABLE t_student MODIFY score FLOAT(4,1); -- MODIFY修改列的类型，但不会改变列的名字

修改列名及列类型

ALTER TABLE t_student CHANGE score score1 DOUBLE(5,1) --列名和列的类型

表的完整性约束

子主题 1
非外键约束

CREATE TABLE t_student( sno int(6) PRIMARY KEY auto_increment, sname VARCHAR(10) NOT NULL, sex CHAR(1) DEFAULT '男' CHECK(sex='男' ||sex='女'), age int(3)CHECK(age>=18 and age<=50), enterdate date, classname VARCHAR(10), email VARCHAR(15) UNIQUE );

约束分类

(1)表级约束:可以约束表中任意一个或多个字段，与列定义相互独立，不包含在列定义中，与定义用，分隔，必须指出要约束的列的名称；

子主题 2
子主题 1

2)列级约束: 包珍在列定义中，直接跟在该列的其它定义之后，用空格分隔;不必指定列名;

CREATE TABLE t_student( sno int(6) PRIMARY KEY auto_increment, sname VARCHAR(10) NOT NULL, sex CHAR(1) DEFAULT '男' CHECK(sex='男' ||sex='女'), age int(3)CHECK(age>=18 and age<=50), enterdate date, classname VARCHAR(10), email VARCHAR(15) UNIQUE );
创建表之后添加约束

子主题 1
验证约束查看成功查看表结构

desc_student

约束总结

子主题 1

外键约束

外键约束(FOREIGN KEY，缩写FK) 是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。外健是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束，被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

子主题 1
-- 先创建父表班级表 create table t_class( cno int(4) primary key auto_increment, cname varchar(10) not null, room char(4) );
-- 创建子表学生表 create table t_student( sno int(6) primary key auto_increment, classno varchar(5) not null -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义尽量要求相同 );

出现问题:- 1.添加一个学生对应的班级编码为4: insert into t student values (null,"丽丽,4); - 2.删除班级 delete from t class where cno = 2; 出现问题的原因: 因为你现在的外键约束，没用语法添加进去，现在只是逻辑上认为班级编号是外键，没有从语法上定义

注意:外键约束只有表级约束，没有列级约束: create table t student( sno int(6) primary key auta increnent, sname varchar(5) mot mul1. classno int(4)，- 取值参考t class表中的no字段，不要求字段名字完全重复，但是类型长度定义尽量要求相同。constraint fk stu classno foreipn key (classmo) references t class (cno) );
在创建表以后添加外键约束: alter table t student add constraint fk stu classno foreien key (classno) references t class (cno)
先删除主表，再删除从表

外键策略

删除班级2:如果直接删除的话肯定不行因为有外键约束:加入外键策略: 策略1: no action 不允许操作通过操作sq来完成: 先把班级2的学生对应的班级改为nu11 update t student set classno = null where classno = 2; - 然后再删除班级2: delete from t class where cno = 2;
策略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; -- 试试删除: delete from t class where cno = 5;
策略3: set null 置空操作:- 先删除之前的外键约束: 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 set nul on delete set null 试试更新: update t class set cno = 8 where cno = 1;

快速添加表

-- 添加一张表:快速添加: 结构和数据跟t_student 都是一致的 create table t student2 as select * from t student; select * from t_student2;
快速添加，结构跟t_student一致，数据没有: create table t student3 as select * from t_student where 1=2
快速添加:只要部分列，部分数据: create table tstudent4 as select sno,snameage from t_student where sno = 2;

DQL查询操作

查看表: select * from dept;部门表: dept:department 部分，loc - location 位置
select * from emp; -员工表: emp:employee 员工 ,mgr :manager上级领导编号，hiredate 入职日期 firedate 解日期，common: 补助 deptno 外键参考 dept - deptno字段 - mgr 外键参考自身表emp - empno 产生了自关联
select * from salgrade;-- losal - lowsal- hisal - highsal

事务

是用来维护数据库完整性的，它能够保证一系列的MySQL操作要么全部执行，要么全不执行。比如转账收款方和付款方同时增加减少

事务特性：事务处理可以确保除非事务性序列内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠。但并不是所有的操作序列都可以称为事务，这是因为一个操作序列要成为事务，必须满足事务的原子性(Atomicity)、一致性(Consistency) 、隔离性 (lsolation) 和持久性 Durability) 。这四个特性简称为ACID特性
- 丽丽给小刚转200元: update account set balance = balance - 200 where id = 1;update account set balance = balance + 200 where id = 2:默认一个DML语句是一个事务，所以上面的操作执行了2个事务

手动开启事务:start transaction;
手动回滚:刚才执行的操作全部取消:rollback;
手动提交commit

在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据
事务并发

脏读

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”依据“脏数据”所做的操作可能是不正确的。

子主题 1

不可重复性

(Unrepeatableread):指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不大一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

子主题 1

幻读

(Phantom read):幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务(T2)插入了一些数据时在随后的查询中，第一个事务(T1)就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读

子主题 1

区别

不可重复度和幻读区别: 不可重复读的重点是修改，幻读的重点在于新增或者删除
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。事务的隔离级从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE，隔离级别越低，越能支持高并发的数据库操作。

子主题 1

视图

视图(view)是一个从单张成多张其础数据表可其他图中均建出来的虚拟表。同基础表一样，视图中也包含了一系列带有名称的列和行数据但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而开不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。

单表视图 create or replace view myview as select empno,ename,job,deptno from emp where deptno = 20 with check option ;
多表视图 create or replace view myview92as select e.empno,e.ename,e.sal,d.deptno,d.dnamefrom emp ejoin dept don e.deptno = d.deptnowhere sal > 2000 ;
-- 创建统计视图 create or replace view myview03 as select e.deptno,d.dname,avg(sal),min(sal),count(1)from emp e join dept d using(deptno)group by e.deptno;
-- 创建基于视图的视图 create or replace view myview4 as select * from myview03 where deptno = 20;

好处

简化用户操作:视图可以使用户将注意力集中在所关心地数据上，而不需要关心数据表的结构、与其他表的关联条件以及查询条件等对机密数据提供安全保护:有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据(如，敏感字段“salary”)出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能

PS:视图本质上就是: 一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

存储过程

存储过程是数据库中保存的一系列sql命令的集合，也可以看作是相互之间有关系的SQL命令组织在一起形成的一个小程序。

--定义一个没有返回值 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 mypr01; -- 调用存储过程 call mypro01(null);
-- 参数前面的in可以不写带返回值FOUND_ROWS()mysql的函数，返回查询结果的条数 create procedure mypro02(in name varchar(10),out num int) 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;

存储过程的优点

1)提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。 2)可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。 3)可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了

创建用户并限制权限

创建一个新的用户 CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
授予该用户对特定数据库的增删改查权限 GRANT SELECT, INSERT, UPDATE, DELETE ON `database_name`.* TO 'user_name'@'localhost';
禁止该用户对其他数据库的任何操作 REVOKE ALL PRIVILEGES ON *.* FROM 'user_name'@'localhost';
. 刷新权限以使更改生效FLUSH PRIVILEGES;
检查你是否具有创建用户的权限SELECT User FROM mysql.user;

数据库进阶版第二辑

热门阅读

推荐阅读