Oracle、plsql各种条件查询和特点介绍！不看你就亏了！！！

–查看表结构

desc t_student

–断开连接

disc

–连接

Conn system/orcl@orcl

–查看用户

Show user

–创建用户密码

creat user lilei identified by 1234;

–授权用户

grant connect to lilei

–撤销授权

revoke connect from lilei;

–删除用户

drop user lilei;

–创建一个表结构

create table t_student (id number(3),name varchar(50),sex char(1),birthday date,fellowship number(5,2),resume clob);

–添加一列

alter table t_student add address varchar(100);

–修改adress

alter table t_student modify address char(200);

–删除adress

alter table t_student drop column address;

–删除resume

alter table t_student drop column resume;

–把t_student改成t_stu

rename t_student to t_stu;

create table t_class(id number(3),name varchar(10));

*–*把所有人的奖学金金额设置成99.99

update t_student set fellowship = 99.99;

*–*把所有人的奖学金金额设置为66.66并且所有人生日设置为2000-01-01

update t_student set fellowship = 66.66,birthday = to_date(‘2000-01-01’,‘yyyy-MM-dd’);

*–*把所有人的奖学金取消

update t_student set fellowship = null;

*–*把id为1的同学的奖学金改成66.66

update t_student set fellowship = 66.66 where id = 1;

*–*把账号是2的同学或者性别是女的同学，奖学金设置为77.77

update t_student set fellowship = 77.77 where id = 2 or sex=‘女’;

*–*把账号是2的同学，并且性别是男的同学，奖金设置为88.88

update t_student set fellowship = 88.88 where id = 2 or sex = ‘男’;

*–*删除账号是5的学生

delete from t_student where id = 5;

---

找出各月最后一天受雇的所有员工

select * from emp where hiredate = last_day(hiredate);

–显示正好为6个字符的雇员名字

select ename from emp where length(ename) = 6;

–显示名字中不带‘R’的雇员

select ename from emp where instr(ename,‘R’) = 0;

select * from emp where ename not like ‘%R%’;

–显示所有雇员的姓名的前三个字符

select substr (ename,1,3)from emp;

–把雇员名字中的A 替换成a

select replace (ename,‘A’,‘a’)from emp;

—找出在（任何年份的）2月受聘的所有雇员

select replace (ename,‘A’,‘a’)from emp;

—对于每一个雇员，显示其加入公司的天数；

select ename,sysdate-hiredate from emp;

–去重复

select distinct job from emp;

–列进行运算（在数据库中null不能进行运算）在Oracle中提供一个nvl函数可以把null当成0进行运算，在mysql中函数叫ifnull

select ename, sal * 12 from emp;

select ename as 姓名, sal*12 +nvl（comm，0） as 年工资 from emp;

–运算之后显示的表起别名

select ename as 姓名, sal*12 as 年工资 from emp;

–查询表中工资在2000和5000之间的员工全部信息

select * from emp where sal >2000 and sal <5000;

select * from emp where sal between 2000 and 5000;

（between …and）必须连用；

–模糊匹配（like）；

1.like可以和%连用；%代表匹配所有字符

2.like还可以和_连用；like’_'_代表匹配任意字符

–查询表中以S开头的名字的工资；

select ename,sal from emp where ename like ‘S%’;

–查询表中第三个字母是O的；

select ename,sal from emp where ename like ‘__O%’;

–查询表中数据是否等于其中一个

select * from emp where empno = 7369 or empno =7499 or empno = 7521;

select * from emp where empno in (7369,7499,7521);

–null不可以用=来判断，需要用is

select * from emp where mgr is null;

–查询工资>500或者工作是manger的员工，并且名字要求以J开头；

select * from emp where( SAL > 500 or JOB = ‘MANAGER’) and ename like ‘J%’;

–排序（使用order by 来排序）放在where后面，默认升序

–按照工资排序

（升序）select * from emp order by sal asc;

（降序）select * from emp order by sal desc;

select * from emp order by deptno asc , hiredate desc;

在第一个条件满足的情况下，在相同的部门号中降序排列入职日期；

---

聚合函数：作为select后的条件，不能和普通列一起使用除非和group by连用；

—最大值或最小值

select max(sal) ,min(sal)from emp;

—count ，空格不算

count（*）的计算速度比较慢，基本写唯一值；

select count(empno) from emp;

查询结果可以当成一个新表；

–查询工资最高的员工，输出他的名字和工作

select ename,job from emp where sal = (select max(sal) from emp);-子查询，在查询中包含查询

–显示工资高于平均工资的员工信息

select * from emp where sal > (select avg(sal) from emp);

group by----算出各部门的平均工资

select deptno ,avg(sal),max (sal) from emp group by deptno;

—算出每个部门每个岗位的平均工资和最低工资；

select deptno，job,avg(sal),min(sal) from emp group by deptno,job order deptno,job;

—算出每个部门平均工资小于2000的；

对 group by算出的分组函数进行操作用having；

select deptno,avg(sal),avg(sal) from emp group by deptno having avg(sal) <2000;

顺序：group by，having，order by

多表查询：

会产生笛卡尔积，当有n个表时，要消灭笛卡尔积，必须需要n-1个约束条件；

select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;

select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

自连接（把一个表当做两张表）

–查询每个部门比本部门平均工资高的人（把子查询看做一个临时表）

select e.* from emp e ,(select deptno ,avg(sal) as ping from emp group by deptno) d where e.deptno = d.deptno and sal >d.ping;

---

—每个部门最高工资的人

select * from emp where sal in (select max(sal) from emp group by deptno);

—求出每个部门的人数

select deptno ,count(*) from emp group by deptno;

—分页查询：

select d.num ,e.* from emp e,(select rownum as num,e.*from emp e) d where d.num between 6 and 10;

—创建一个临时表，表中内容复制emp表

create table eee(id,name) as select empno,enmae from emp;

—自我复制数据（蠕虫复制）

—合并查询

union，可以把两个查询结构合并，并去重；

select * from emp where deptno = 10 union select * from emp where deptno = 20 ;

union all 就不会消除重复的行；

内连接和外连接

内连接：多表进行查询，使用where条件来消除笛卡尔积

特点：消除笛卡尔积的条件，如果有数据不成立，则有

外连接：

左外连接：左表数据完全保留left（outer）join

右外连接：右表数据完全保留right（outer）join

---

---

---

oracle默认端口1521

mysql默认端口3306

---

面向接口编程： 接口类 对象名 = new 接口实现类（）

多态：子类调用父类方法

---

数据库锁：

加锁的记录只能查不能修改和删除（for update）

select *from t_emp where enmae = ‘lilei’ for update

事务的提交（commit）

手动事务：不提交事务，数据就不会写入数据库

自定事务：只要sql语句执行完毕就自动提交，写进数据（java代码就是自动事务）

事务的隔离性：持有相同账号和密码的不同用户，他们之间的事务相互不可见，每个用户的事务是独立的。

事物的回滚：就是把没有提交的记录所做的操作撤回，（rollback）

事务的保存点：可以回滚到保存点，之前的内容还在，保存点之后的操作回滚之后就没有了。

在一个方法内，添加两条SQL语句，并设置手动提交，当两条数据没有全部提交到数据库中，启动回滚操作。

  public static void  insert(){

        Connection conn = null;
        PreparedStatement stm1 = null;
        PreparedStatement stm2= null;
        try{
            conn = OracleUtil.getConnection();
            //设置事物为手动提交，
            conn.setAutoCommit(false);//不自动提交
            stm1= conn.prepareStatement("insert into t_admin values(?,?)");
            stm1.setString(1, "admin");
            stm1.setString(2,"bgsn");
            stm1.executeUpdate();
            stm2.setString(1,"qq");
            stm2.setString(2,"34");
            stm2.executeUpdate();
            //如果SQL正常运行，则提交事务
            conn.commit();
           
        }catch (Exception e){
            try {
                //SQL没有正确执行，回滚事务
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            OracleUtil.closePreparedStatement(stm1);
            OracleUtil.closePreparedStatement(stm2);
            OracleUtil.closeConnection(conn);
        }

---

---

事务的隔离性：

脏读：当一个事物读取另一个事物 尚未提交的修改时，产生脏读；

不可重复度：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回都不同的结果集，此时发生非重复度

幻读：统一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

---

—查询字符M在名字中出现的位置

select ename ,instr(ename,‘M’)from emp;

数字函数

select empno,mod (empno,10) from emp;（取模）

日期函数

sysdate 获取数据库当前时间；

—把日期向后加三个月；

select ename,add_months(hiredate,3) from emp;

---

---

---

—把日期向后推一天

select ename , hiradate + 1 from emp;

----to_cahr 日期格式化

select ename ,to_char(hiredate,‘yyyy-MM-dd HH:mm:ss’) from emp;

---

数据的完整性

约束作用在表的列上：

not null ：插入和修改数据是，此列不能为null值，但是可以是‘ ’；

unique：唯一值，在插入和修改时可以为null值，但是非null值不可以重复

primary key主建：not null+ unique

foreign key 外建：两个或两个以上的表，某一个表的主建列，是另外一个表的普通列，我们就把另外一个表的普通列叫做那个表的外键；

check ：限制某一列的数据符合某些规范，如可以限制sex列为男或女，

---

主建的类型：

1.业务主建 ：根据实际业务确定主建列，比如订单表，订单编号具有实际意义，可以为主建。

2.自然主建 ：没有实际业务逻辑意义的主建，只为有主键而生。id（自增长）id（随机数）UUID

3.主键可以使多个列，我们把这种情况叫做联合主键；

---

设计表约束的意义？

防呆：防止呆子操作数据库

---

在数据库中，SQL语句不区分大小写；

但是，在Oracle中，表名和列名是区分大小写的（在建表的时候，表名，列名使用双引号）

---

序列：可以认为是Oracle的一个函数，函数名自己定义，序列可以被大家共用；

create sequence my_seq ----创建序列名
start with 1 -----从1开始
increment by 1------每次增长1
maxvalue 999999999 ----最大值
minvalue 1 ----最小值
cycle -----一直累加，不循环
nocache–不缓存（每次增完之后都放到数据库中）

---

---

---

E-R图

一般来说，在设计数据库之前，需要设计E-R 图，表示实体之间的关联关系；

两个实体之间的三种关系：一对一，一对多，多对多

---

---

---

数据库的索引：

优点：提高查询速度

缺点：占空间，费内存，当修改或删除数据时需要同时维护索引数据，导致效率变低

适合建立索引的表：

1.数据量越大越有意义

2.经常被查询的表

3.离散值越大，并且经常被用作查询条件的列

4.like 不会触发索引，除非最左原则

name like ‘b%’

5.外连接一般不会走索引

---

---

---

plsql

优点：提高应用程序的运行性能2.模块化的设计思想3.减少网络的传输量4.提高安全性

缺点：不能移植

–在控制台输出helloword

declare
begin
dbms_output.put_line(‘hello word !’);
end;

—根据雇员编号查询雇员姓名

declare
v_ename varchar(20);
begin
select ename into v_ename from emp where empno = &no;
dbms_output.put_line(‘姓名：’||v_ename);
end;

–编写一个存储过程，实现根据雇员编号查询雇员名称
create or replace procedure p1(v_no in number,v_name out varchar)
as
begin
select ename into v_name from emp where empno = v_no;
dbms_output.put_line(‘姓名：’||v_name);
exception
when no_data_found then
dbms_output.put_line(‘输入有误，傻子’);
end;

触发器

create or replace trigger trigger_dept
after
insert
on dept
for each row
begin
dbms_output.put_line(‘数据被新增了’);
end;

---