Oracle基础学习笔记(一)-基于马士兵Oracle视频

004. sqlplus sys/bjsxt as sysdba; --更改登录用户

005. alter user scott account unlock; --解锁用户

006. desc <表名>; --查看表字段结构

007. select ename,sal*12 annual_sal from emp;

   select ename,sal*12 "annual sal" from emp;

   区别: 加双引号保持原大小写,不加全变大写.

008. select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号.

009. select distinct deptno, job from emp; --去掉重复

010. select ename from emp where ename like '_A%'; --_代表一个字母,%代表0个或多个字母.

   --如果查询%本身,可用转义字符\%. 还可以用 escape 指定任意转义字符.

   --比如:select ename from emp where ename like '%$a%' escape '$'; --使用 '$' 做转义字符

012. select lower(ename) from emp;

013. select substr(ename,2,3) from emp; --从第二字符截,一共截三个字符.

014. select round(23.652,1) from dual; --23.7

015. select round(23.652,-1) from dual; --20

016. select to_char(sal,'$99_999_999') from emp;

017. select to_char(sal,'L99_999_999') from emp; --L代表本地符号

018. select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;

019. select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; --也可以改为:HH12

020. select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');

021. select sal from emp where sal>to_number('$1.250.00','$9,999,99');

022. select to_char(avg(sal),'99999999,99') from emp;

023. select round(avg(sal),2) from emp;

024. select count(*) from emp where deptno=10;

025. select count(ename) from emp where deptno=10; --count某个字段,如果这个字段不为空就算一个.

026. select count(distinct deptno) from emp;

027. select sum(sal) from emp;

028. --把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)

   --分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.

   select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.

--SQL1999 表连接 join on

--left (outer) join:左(外)连接,结果集既包括连接表的匹配行,也包括左连接表的所有行;

--right (outer) join:右(外)连接,结果集既包括连接表的匹配连接行,也包括右连接表的所有行;

--full (outer) join:全(外)连接,不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录;

029. select ename,dname,grade from emp e,dept d, sqlgrade s where e.deptno = d.deptno and e.sql between s.losal and s.hisal and job<>'CLERK';

     select ename,dname from emp,dept; --(旧标准).

     select ename,dname from emp cross join dept; --(1999标准)

     select ename,dname from emp,dept where emp.deptno=dept.deptno; --(旧) 

     select ename,dname from emp join dept on(emp.deptno = dept.deptno); --1999标准.没有Where语句.

     select ename,dname from emp join dept using(deptno); --等同上句,但不推荐使用.

   select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); --join 连接语句, on过滤条件。

     select ename, dname, grade from emp e join dept d on(e.deptno=d.deptno) join salgrade s on(e.sal between s.losal and s.hisal) where ename not like '_A%'; --三张表连接

     select e1.ename,e2,ename from emp e1 left (outer) join emp e2 on(e1.mgr =e2.empno); --左(外)连接:会把左边这张表多余数据显示出来

     select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); --右(外)连接

   select ename,dname from emp e full join dept d on(e.deptno =d.deptno); --全(外)连接:即把左边多余数据，也把右边多余数据拿出来

030. 面试题:不准用组函数，求薪水的最高值

   select distinct sal from emp where sal not in( select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));

032. 事务(transaction) rollback; commit;

   --事务(transaction)起始于一条DML语句;

   --出现 DDL语句,DCL语句 时,事务自动提交;

   --用户正常断开连接,事务自动提交(exit);

   --非正常断开连接,事务回滚;

033. 建表语句 约束

create table table_name

( id number(10) primary key,

  name varchar2(20) constraint table_name_name_not_null not null,

  gender char(1),

  teacher_id number(10),

  s_class number(2),

  birth date,

  grade number(1) default 1,

  email varchar2(20) unique

);

create table table_name

( id number(10),

  name varchar2(20) constraint table_name_name_not_null not null,

  gender char(1),

  teacher_id number(10),

  s_class number(2),

  birth date,

  grade number(1) default 1,

  email varchar2(20),

  constraint t_id_pk primary key (id),

  constraint t_t_id_fk foreign key (teacher_id) references teacher(id) ,

  constraint t_email_name_u unique (name,email)

);

-- constraint 指定约束名称; 外键约束,被参考的字段必须是主键！

034. 修改表语句

alter table t_name add(addr varchar2(20));

alter table t_name drop(addr);

alter table t_name add(addr varchar2(50));

alter table t_name modify(addr varchar2(100));

alter table t_name drop constraint cons_name;

alter table t_name add constraint cons_name foreign key(teacher_id) references teacher (id);

035. Oracle 数据字典表(user_tables;user_views;user_constraints;user_indexes;等等)

select table_name from user_tables;

select view_name from user_views;

select constaint_name,table_name from user_constaints;

036. 数据字典表的存储表(dictionary,存储了所有的用户字典表)

select * from dictionary where table_name like 'USER%'; --查询所有的用户数据字典表;

037. 索引(index)

create index idx_stu_email on stu(email);

create index idx_stu_name_email on stu(name,email);

drop index index_name;

--索引的作用:提高读的效率,降低了修改的效率;

--索引也会占用大量的空间;

038. 视图(view)

create view v$_dept_avg-sal_info as select deptno, deptname from dept;

--作用:便于权限控制,保护数据,简化查询;增加了维护的难度;一般不使用视图来更新数据;

039. 序列(sequence)<Oracle特有>

--获取不重复的值,一般用于生成表的主键;

create sequence seq_name;

drop sequence seq_name;

select seq_name.nextval from dual;

select seq_name.currval from dual;

040. 范式

第一范式：

1.要有主键(primary key);

2.列不可分;列不能重复;

第二范式：

1.满足第一范式;

2.不能存在部分依赖(当表存在联合主键时,非主键字段不能仅依赖于部分主键);

第三范式：

1.满足第二范式;

2.表中不包含在其他表中已包含的非主键字段信息;