mysql常用数据库对象

复习:
(1)DDL :数据定义语言  create ,alter ,drop

(2)DQL :数据查询语言  select 
           单表查询  :select .. from 表名[where] ..[group by]..[having]..[order by asc/desc]
           多表查询  :92 : select ..from 表名1，表名2 where 连接条件
                            99:select ..from 表名1 inner join 表名2 on 连接条件 [where...]
                            left join , right join 
                            select ... from 表名1 left /right join 表名2 on连接条件 
           子查询
(3)DML :数据操作语言 insert ,update,delete 
  

数据库常用对象
(1)索引  ：用于提高“检索”/查询效率
              索引本身需要占用磁盘空间，所以不是说越多越好，而是要在合适的列上建立索引
              经常用于“条件筛选”或者排序的列比较合适建立索引
   语法:创建索引  create index 索引名称 on 表名( 具体的列);
   
        删除删除 msyql: alter table 表名drop index 索引名称
                      oracle: drop index 索引名称

-- 创建索引
create index ind_ename on emp (ename);
-- 索引怎么用啊？^_^，查询时自动起作用，无需程序操心
select * from emp where ename='SCOTT';
alter table emp drop index ind_ename; -- mysql支持，删除索引
drop index ind_ename; --oracle 支持,mysql不支持

create index ind_ename on emp (ename);
alter table emp drop index ind_ename;--不支持
drop index ind_ename; --支持

(2)事务 
mysql
--mysql需要使用start transaction开启事务，需要使用commit提交事务，使用rollback回滚事务
create table account(
  id int(4) primary key auto_increment,
  accname varchar(20),
  balance int(4)
);
--添加测试数据
insert into account(accname,balance) values ('zhangsan',500);
insert into account(accname,balance) values ('lisi',200);
select * from account;
--手动开启事务
start transaction;
update account set balance=balance-200 where accname='zhangsan';
update account set balance=balance+200 where accname='lisi';
ROLLBACK; --手动回滚事务
commit; --手动提交事务
-- 如果没有start transaction;SQL语句执行完毕自动提交
oracle中的事务是通过insert ,update,delete开启事务，使用commit提交事务或DDL可以以自动提交事务，使用rollback回滚事务，

oralce:
 create table account(
  id number(4) primary key,
  accname varchar2(20),
  balance number(7,2) 
);
--insert是DMl数据操作语言，自动开启事务
insert into account values (1,'zhangsan',500);
insert into account values (2,'lisi',200);
create user bbt identified by bbt; --DDL数据定义语言，遇到DDL自动提交事务
select * from account;
rollback; --手动回滚
commit;--手动提交
事务的隔离级别  参与mysql授课笔记

(3)视图

定义：
视图是从若干基本表和（或）其他视图构造出来的表。
在创建一个视图时，只是存放的视图的定义，也即是动态检索数据的查询语句，而并不存放视图对应的数据
在用户使用视图时才去求相对应的数据。所以视图被称作“虚表”
 作用：
可以限制对数据的访问，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
可以使复杂的查询变的简单。在编写查询后，可以方便地重用它而不必知道他的基本查询细节。
提供了对相同数据的不同显示

oralce

  分页查询和统计查询都写成了视图，作用，将复杂的SQL语句进行简化
select * from emp order by sal desc;
--分页查询
select rownum as rid, t.* from (select * from emp order by sal desc)t;
create view vi_empsal
as 
select * from (select rownum as rid, t.* from 
(select * from emp order by sal desc)t)  temp 
where rid>4 and rid<=8;
select * from vi_empsal;
 --统计查询
create view vi_emp2
as 
 select dname,numcount,maxsal,avgsal from dept  d, 
    (select deptno,count(*) as numcount,max(sal) as maxsal,avg(sal) as avgsal from emp 
  where deptno is not null  group by deptno)t
 where d.deptno=t.deptno;
 ;
select * from vi_emp2;
mysql:中的视图


--视图，视图是一张虚表，在数据库中根本不存在，操作视图实际上就是在操作基本表
--创建视图
create view vi_emp
as 
  select * from emp;
--如何使用视图呢？（基本表怎么使用，视图就怎么使用）
--查
select  * from vi_emp;
--增，删，改   (对视图就是对视图所对应的基本表的操作)
update vi_emp set sal=900 where ename='SMITH'; --修改了视图中smith的薪水 
select * from emp;
--但是，视图最主要的工作就是查询
create view vi_emp2  --使用视图限定表中列的查询权限
as 
select empno,ename,dname from emp e,dept d where e.deptno=d.deptno;
select * from vi_emp2;  

删除视图
 drop view 视图名称;

(4)存储过程 (防止SQL注入)


delimiter //
 create PROCEDURE mypro(out s int)
 BEGIN
   select count(*) into s from emp;
 end //
  
delimiter ;
--call 存储过程的名称  调用存储过程
set @s=0;
call mypro(@s);
select @s;
--删除的存储过程
delimiter //
create PROCEDURE deletebyno( in nid int)
        begin 
      delete from emp where empno=nid;
        end //
delimiter ;
--使用存储过程
call deletebyno(7369);
 --删除存储过程
drop PROCEDURE deletebyno;
select * from emp;
--新增的存储过程
delimiter //
create PROCEDURE insertemp( in deptno1 int, in  dname1 varchar(20), in loc1 varchar(20))
        begin 
      insert into dept values (deptno1,dname1,loc1);
        end //
delimiter ;
call insertemp(50,'财务部','北京');
select * from dept;
oracle中的存储过程


--根据员工编号查询
SELECT * FROM EMP WHERE EMPNO=7902
----------------增加的存储过程-------------------------------
CREATE OR REPLACE PROCEDURE up_save(
        EMPNO IN NUMBER,ENAME VARCHAR2,JOB VARCHAR2,MGR IN NUMBER,
        HIREDATE DATE,SAL NUMBER,COMM NUMBER,DEPTNO NUMBER
)
AS 
BEGIN
        INSERT INTO EMP VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
END;
EXECUTE up_save(1111,'xhna','CLERK',7902,SYSDATE,9000,890,20);
---------------------修改的存储过程-----------------------------------
CREATE OR REPLACE PROCEDURE up_update(
        mempno IN NUMBER,mname VARCHAR2,mjob VARCHAR2,mmgr IN NUMBER,
        mhiredate DATE,msal NUMBER,mcomm NUMBER,mdeptno NUMBER
)
AS 
BEGIN
        UPDATE EMP SET ENAME=mname,JOB=mjob,MGR=mmgr,HIREDATE=mhiredate,SAL=msal,COMM=mcomm,
        DEPTNO=mdeptno WHERE EMPNO=mempno;
END;
EXECUTE up_update(1111,'xhna','CLERK',7902,SYSDATE,1000,890,20);
--------------------删除的存储过程--------------------------
CREATE OR REPLACE PROCEDURE up_delete(eno IN NUMBER)
AS 
BEGIN
        DELETE FROM EMP WHERE EMPNO=eno;
END;
--执行
EXECUTE up_delete(1113);
--------------------根据部门编号删除员工的存储过程-------------------------------
CREATE OR REPLACE PROCEDURE up_deleteEmpByDeptno(tno IN NUMBER)
AS 
BEGIN
        DELETE FROM EMP WHERE DEPTNO=tno;
END;
--------------------根据部门编号删除一个部门-------------------------------------
CREATE OR REPLACE PROCEDURE up_deleteDeptno(tno IN NUMBER)
AS 
BEGIN
        DELETE FROM DEPT WHERE DEPTNO=tno;
END;
------------------------增加部门的存储过程---------------------------------------------------------
CREATE OR REPLACE PROCEDURE up_saveDept(tno IN NUMBER,tname VARCHAR2,tloc VARCHAR2)
AS 
BEGIN
        INSERT INTO DEPT VALUES(tno,tname,tloc);
END;
-------------------------修改的存储过程--------------------------------------------------------
CREATE OR REPLACE PROCEDURE up_updateDept(tno IN NUMBER,tname VARCHAR2,tloc VARCHAR2)
AS 
BEGIN
        UPDATE DEPT SET DNAME=tname,LOC=tloc WHERE DEPTNO=tno;
END;

---------------查看当前数据库实例名------------------
SELECT name FROM v$database;