sql基础知识实例学习

Oracle 重点
重点：与SELECT 相关的所有
如果你不做DBA，其他知识点暂时理解就够了
/******************************************************/

Day01

/******************************************************/

1. s_emp、s_dept 表的字段含义

 
 first_name 名
 last_name 姓
 title 职位
 dept_id 部门号
 commission_pct 提成（有空值）

2. 列出所有人的年薪

 select first_name,salary*12 from s_emp;

3. 给列起别名

 select first_name,salary*12 Ann_Sal   from s_emp;
 select first_name,salary*12 "Ann Sal"   from s_emp;
 select first_name,salary*12 as "Ann Sal" from s_emp;

4. 处理空值的函数nvl(p1,p2)

 null Oracle 当做无穷大来处理
 空值不等于0
 空值不等于空格
 算数表达式中为空值，返回空值
 select first_name , salary*12*(1+nvl(commission_pct,0)/100) from s_emp;

5. SQLPLUS 命令：

 a) L
 列出上一次敲入的命令
 b) clear scr 或者！clear
 清屏

 
6. 字段（列名）拼接

 

 || 字符串拼接
 '' Oracle 中字符和字符串用单引号表示
 "" 双引号用于表示别名
 select first_name||' '||last_name employee from s_emp;
 select first_name||' is int department '||dept_id||'.' from s_emp;
 

7. 去除重复值distinct

 

 ##该公司有哪些职位？
 select distinct title from s_emp;
 ##各个部门有哪些不同的职位？
 ##distinct 的功能：部门号单独重复，职位单独重复，部门号和职位联合不重复
 select distinct dept_id,title from s_emp;
 ##会报错，因为distinct 只能出现在select 后面，否则会造成逻辑不通
 select dept_id,distinct title from s_emp; (X)

 

8. 列出表中所有字段

 

 ##注意：写*会降低效率，公司中一般会禁止写*;
 select * from s_emp;

 
9. Oracle 中写SQL 大小写区别在功能上无影响，性能上有影响

 

 ##注意：写SQL，一般公司都有规范
 

10. where 控制子句

 

 ##年薪大于1.2w 的员工的年薪?
 ##如果salary 字段上建了索引，第一种写法，索引用不上，所以慢
 select first_name,salary*12 a_sal
 from s_emp
 where salary*12 > 12000;
 ##如果salary 字段上建了索引，第二种写法，效率高些
 select first_name,salary*12 a_sal
 from s_emp
 where salary>1000;
 ##会报错，where 子句后面不可以跟“列别名”,where 子句执行在select 语句
 之前
 select first_name,salary*12 a_sal
 from s_emp
 where a_sal > 12000;
 ##EX.不会报错，order by 子句可以使用“别名”
 select first_name,salary*12 a_sal
 from s_emp
 order by a_sal;
 

11. 注意：单引号中大小写敏感

 

 ##列出Carmen 的年薪是多少？
 Select first_name,salary*12 a_sal
 From s_emp
 Where first_name = 'Carmen';
 

12. 大小写转换函数lower() upper()

 

 ##列出Carmen 的年薪是多少？
 select first_name , salary*12 a_sal
 from s_emp
 where lower(first_name) = 'carmen';
 

13. AND 和between and 连接符

 

 ##找出员工工资在1000 与1500 之间
 select first_name , salary
 from s_emp
 where salary>=1000 and salary<=1500;
 ##between and 就表示了如上含义
 select first_name , salary
 from s_emp
 where salary between 1000 and 1500;
 

14. OR 连接符IN（）表述形式=ANY()

 

 ##找出31、41、43 部门员工的姓名和部门号？
 select first_name , dept_id
 from s_emp
 where dept_id=31 or dept_id=41 or dept_id=43;
 ##简单的表述形式in（）
 select first_name , dept_id
 from s_emp
 where dept_id in (31 , 41 , 43);
 ##另一种表述形式in()相当于=any()
 select first_name , dept_id
 from s_emp
 where dept_id = any(31, 41, 43);
 ##从连续区间中取值使用Between-And，从离散数值中取值用IN（）
 

15. LIKE 运算符SUBSTR（）函数Length（）函数

 

 （通配符：%表示0 或多个字符；_表示任意单个字符）
 ##效率高些
 where last_name like 'M%'
 ##结果等同如上
 where substr(last_name , 1 , 1) = 'M';
 ##列出名字的最后两个字母
 select first_name , substr(first_name , -2 ,2) from s_emp;
 ##列出名字的最后两个字母length(）函数
 select substr(first_name , length(first_name)-1 ,2) from s_emp;
 

16. escape 关键字（表示\后边的符号不是通配符）

 

 select talble_name from user_tables
 where talbe_name like 'S\_%' escape '\';

 
17. IS NULL 判断字段是否为空IS NOT NULL

 

 select first_name , commission_pct from s_emp where commission_pct is null;

 
18. NOT BETWEEN AND

 

 NOT IN()
 NOT LIKE
 IS NOT NULL
 ##除了31、41、43 部门的部门员工的情况
 select first_name , dept_id from s_emp
 where dept_id not in(31, 41 , 43);
 ##等价写法
 select first_name , dept_id from s_emp
 where dept_id != 31 and dept_id!=41 and dept_id!=43;
 ##等价写法<>all(31,41,43)
 select first_name , dept_id from s_emp
 where dept_id <>all (31,41,43);
 ##任何数据与NULL 比较，都返回false，
 ##使用not in()时，如果集合中有null 值，则查不出任何记录，对in()没影响
 select first_name , dept_id from s_emp
 where dept_id not in(31, 41 , 43,null);

 

19. 注意下两句SQL 的区别，理解OR 和AND

 

 ## 找出部门号为44，工资大于1000 的员工或者部门号为42 的所有员工?
 select last_name , salary , dept_id
 from s_emp
 where salary >=1000 and dept_id=44 or dept_id=42;
 ##找出部门号为44 或者42 的，并且工资大于1000 的员工
 select last_name , salary , dept_id
 from s_emp
 where salary >=1000 and (dept_id=44 or dept_id=42);

 
20. 隐式数据类型转换

 

 ##如下式相同的结果，系统做了隐式数据类型转换，均为：字符转数值
 select first_name , salary from s_emp where salary = 1450;
 select first_name , salary from s_emp where salary = '1450';
 ##相当于
 select first_name , salary from s_emp where to_number(salary) = 1450;
 ##做严格的数据类型匹配相当重要
 select first_name , salary from s_emp where salary = 1450;

 
21. 显式数据类型转换to_char()函数

 

 ##输出所有员工的manager_id，如果没有manager_id，则用BOSS 填充
 select first_name , nvl(to_char(manager_id) , 'Boss') from s_emp;
/******************************************************/ 

Day02

/******************************************************/

1. 表和表之间的关系

 

 s_emp 员工表
 s_dept 部门表
 s_region 部门所在地区表
 salgrade 工资等级表
 emp 员工表
 dept 部门表

 

2. 等值连接

 

 ##查询'Carmen'所在部门的地区？（Canmen 在哪个地区上班？）
 ##中间表“部门表”
 ##用几张表就JOIN 几次
 ##等值连接（内连接的一种）：父表的主键==子表的外键
 select e.first_name , r.name
 from s_emp e
 join s_dept d
 on e.dept_id = d.id
 and e.first_name = 'Carmen'
 join s_region r
 on d.region_id = r.id;
 ##亚洲地区有哪些员工？
 select e.first_name , r.name
 from s_emp e
 join s_dept d
 on e.dept_id = d.id
 join s_region r
 on d.region_id = r.id
 and r.name = 'Asia';

 
3. 非等值连接

 

 ##列出员工的工资以及对应的工资级别？
 select e.ename , e.sal , s.grade
 from emp e
 join salgrade s
 on e.sal between s.losal and s.hisal;
 ##SMITH 的工资级别？
 select e.ename , e.sal , s.grade
 from emp e
 join salgrade s
 on e.sal between s.losal and s.hisal
 and e.ename = 'SMITH';
 ##3, 5 级有哪些员工（哪些员工属于3,5 级）？
 select e.ename , e.sal , s.grade
 from emp e
 join salgrade s
 on e.sal between s.losal and s.hisal
 and s.grade in(3,5);
 

4. 自连接

 

 ##列出员工名和领导名的对应关系
 ##结果为24 个，少一个manager_id 为空的人（BOSS 丢了）
 select e.first_name emplayee , m.first_name manager
 from s_emp e
 join s_emp m
 on e.manager_id = m.id;
 ##列出哪些人是领导？
 select distinct m.first_name
 from s_emp e join s_emp m
 on m.id = e.manager_id;

 
5. outer join 外连接

 

 ## 内连接from t1 join t2 on t1.id = t2.id
 ##from t1 left outer join t2 on t1.id = t2.id 左边的表做驱动表
 ##from t1 right outer join t2 on t1.id = t2.id 右边的表做驱动表
 ##外连接解决的问题：驱动表中的记录在结果集中“一个都不少”
 ##列出员工名和领导名的对应关系？
 select e.first_name employee , nvl(m.first_name,'Boss') manager
 from s_emp e
 left outer join s_emp m
 on e.manager_id = m.id;
 ##如何写外连接：
 ##先写出内连接，再确定哪张表当驱动表就可以
 ##哪个部门没有员工？14 条记录，少1 条
 select e.ename , e.deptno
 from emp e
 join dept d
 on e.deptno = d.deptno;
 ##哪个部门没有员工？15 条记录
 select e.ename , e.deptno , d.deptno ,d.dname
 from emp e
 right join dept d
 on e.deptno = d.deptno;
 ##哪个部门没有员工？15 条记录
 select d.deptno ,d.dname , e.ename , e.deptno
 from emp e
 right join dept d
 on e.deptno = d.deptno
 where e.empno is null;

 ##使用外连接解决了两类问题：

 1.把所有结果列出到结果集
 2.解决否定问题（不是，没有，不包含）
 ##那些人是员工？(即：那些人不是领导？)
 ##思路：
 ##先解决那些人是领导
 ##能匹配的是领导
 ##把匹配不上的挑出来
 ##
 select e.first_name , m.first_name
 from s_emp e
 right join s_emp m
 on e.manager_id = m.id;
 ## 加条件
 select e.first_name , m.first_name
 from s_emp e
 right join s_emp m
 on e.manager_id = m.id
 where e.id is null;
 ## 最后列出m.first_name 即可
 select m.first_name
 from s_emp e
 right join s_emp m
 on e.manager_id = m.id
 where e.id is null;

 

6. And 在外连接之前做过滤，where 在外连接之后做过滤

 

 ##
 select e.ename , d.dname
 from emp e
 right join dept d
 on e.deptno = d.deptno
 and e.ename = 'SMITH';
 ##驱动表的过滤全部写在where 之后
 select e.ename eename , d.dname dename
 from emp e
 right join dept d
 on e.deptno = d.deptno
 and e.ename = 'SMITH';
 where e.empno is null;
 ##选择left jon 或者right join 不重要，重要的是选择哪张表做驱动表
 

7. full out join 用的比较少

 

8. 组函数

 

 ##组函数：一堆数据返回的结果
 ##max()
 ##avg()
 ##min()
 ##avg()
 ##求所有人的平均工资？
 ##求所有人的平均提成？
 select avg(nvl(commission_pct,0)) from s_emp;
 ##count()处理的结果如果全为空值，结果返回0
 select count(commission_pct) from s_emp where commission_pct is null;
 ##计算有多少条记录
 select count(id) from s_emp;
 ##求按提成分组，计算人数？
 select commission_pct , count(id)
 from s_emp
 group by commission_pct;
 ##count()函数中可以加入关键字
 select count(title) from s_emp;
 ##等同于
 select count(all title) from s_emp;
 ##把重复值去掉，再做统计
 select count(distinct title) from s_emp;
 ##列出42 号部门的平均工资
 ##若有groupby 子句，select 后面可跟group by 后面跟的表达式以及组函
 数，其他会报错。
 select dept_id , avg(salary)
 from s_emp
 where dept_id=42
 group by dept_id;
 ##若没有group by 子句，select 后面有一个组函数，其他都必须是组函数
 select max(dept_id) , avg(salary)
 from s_emp
 where dept_id=42;
 作业：
 insert into salgrade values (6,10000,15000);
 ##列出每个工资级别有多少员工？
 ##列出3,5 级有多少员工
 ##列出每个工资级别有多少员工（若该级别没有员工，也要列出）
/******************************************************/

Day03

/******************************************************/

 

1. 子查询

 

 ##先执行子查询；子查询只执行一遍
 ##若子查询返回值为多个，Oracle 会去掉重复值之后，将结果返回主查询
 ##谁是受老板剥削工资最低的人？
 select first_name , salary
 from s_emp
 where salary = (select min(salary) from s_emp);
 ##谁跟SMITH 的职位是一样的？
 select last_name , title
 from s_emp where
 title = (select title from s_emp where last_name='Smith')
 and last_name != 'Smith';
 ##如果表中有重复值，如两个'Smith'，会报错：
 ##single-row subquery returns more than one row 单行子查询返回多行
 ##修改为：
 select last_name , title
 from s_emp where
 title = any (select title from s_emp where last_name='Smith')
 and last_name != 'Smith';
 ##哪些部门的平均工资比32 部门的工资高？
 select dept_id , avg(salary)
 from s_emp
 group by dept_id
 having avg(salary) >
 (select avg(salary) from s_emp where dept_id = 32);
 ##那些人是领导？子查询
 select first_name
 from s_emp
 where id in (select manager_id from s_emp);
 ##那些人是领导？表连接
 select distinct m.first_name
 from s_emp m
 join s_emp e
 on e.manager_id = m.id;
 ##Ben 的领导是谁？子查询
 select first_name
 from s_emp
 where id =
 (select manager_id from s_emp where first_name='Ben');
 ##Ben 领导谁？子查询
 select first_name
 from s_emp
 where manager_id =
 (select id from s_emp where first_name='Ben');
 ##Ben 的领导是谁？ 表连接
 select m.first_name
 from s_emp m
 Join s_emp e
 on e.first_name = 'Ben' and e.manager_id = m.id;
 ##Ben 领导谁？表连接
 select e.first_name
 from s_emp e
 join s_emp m
 on m.first_name='Ben' and e.manager_id = m.id;
 ##
 select first_name
 from s_emp
 where id in (select manager_id from s_emp);
 ##演示代码
 ##对not in 来说，结果集中如果有null，则整个结果集为null
 ##结论：对not in 来说，子查询结果集中是不能有null 的
 select first_name
 from s_emp
 where id not in (select manager_id from s_emp);
 ##查询那些人是员工？
 select first_name
 from s_emp
 where id not in (select manager_id from s_emp where manager_id is not null);
 ##not in 尽量不用
 

2. 子查询与空值

 

 ##哪些部门的员工工资等于本部门员工平均工资？
 ## 多列
 select first_name , dept_id , salary
 from s_emp
 where (dept_id , salary) in (select dept_id , avg(salary) from s_emp group by dept_id);
 

3. 关联子查询

 
 ##哪些员工的工资比本部门的平均工资高？
 select first_name , dept_id , salary
 from s_emp outer
 where salary > (select avg(salary) from s_emp inner
 where outer.dept_id = inner.dept_id);
 

4. 常用的关联子查询：EXISTS NOT EXISTS

 

 ##找到即返回
 ##哪些部门有员工？
 select dname from dept o
 where exists
 (select 1 from emp i
 where o.deptno = i.deptno);
 ##那些人是员工？
 select first_name from s_emp a
 where not exists
 (select 1 from s_emp b where a.id =b.manager_id);
 总结：
 子查询：
 非关联in / not in(不建议)
 关联exists (比inner join 优势)/ not exist(即outer join + is null)
 

5. IN 和EXISTS 的比较（非关联和关联子查询的比较）

 

6. 标量子查询

 
 ##列出员工名和领导名？
 select first_name employee ,
 (select first_name from s_emp i where o.manager_id = i.id) Manager
 from s_emp o;
 

7. CASE WHEN 表达式

 

 ##实现31 部门，32 部门工资分别涨1.1 倍和1.2 倍？
 ##如果没有else 返回空值
 select first_name , salary,
 case when dept_id = 31 then salary*1.1
 when dept_id = 32 then salary*1.2
 else
 salary
 end ala_sal
 from s_emp;
 ##工资<1000 涨300 块，1000<工资<1500 涨500，其他人不动
 select first_name , salary,
 case when salary<1000 then salary+300
 when salary>1000 and salary<1500 then salary+500
 else
 salary
 end ala_sal
 from s_emp;

 
8. DECODE 函数(等同于Case when)

 

 ##decode(参数1，参数2，参数3，参数11，参数12，参数13...)
 ##表示如果参数1 的值为参数2 则参数3；参数11 的值为参数12 则参数13...
 select first_name, salary,
 decode(dept_id , 31 , salary*1.1,
 32,salary*1.2,
 33,salary*1.3,
 salary) aft_sal
 from s_emp;
 select first_name
 from s_emp outer
 where not exists
 (select 1 from s_emp inner where inner.id=outer.manager_id);
 ##列出所有员工
 select first_name
 from s_emp outer
 where not exists
 (select 1 from s_emp inner where outer.id =inner.manager_id);
/******************************************************/

Day04

/******************************************************/

 

1. 约束
 

 not null（非空约束）这是一个列级约束。在建表时,在数据类型的后面加上not
 null ，也就是在插入时不允许插入空值。
 create table student(id number primary key,name varchar2(32) not null,address
 varchar2(32));
 primary key （主键约束PK）保证记录的主键唯一且非空，并且每一个表中只能有
 一个主键。
 foreign key （外建约束FK）被引用的表，叫做parent table（父表），引用方的表叫
 做child table（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入
 也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修
 改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，
 后删除父表。
 unique key（唯一键），值为唯一的, 如果创建一个uk，系统自动建一个唯一索引
 唯一约束，是会忽略空值的，唯一约束，要求插入的记录中的值是为一的。
 create table student(id number，name varchar2(32),address varchar2(32),primary key
 (id),unique (address));
 check 约束
 检查约束，可以按照指定条件，检查记录的插入。check 中不能使用尾列，不能使用
 函数，不能引用其他字段。
 create table sal (a1 number , check(a1>1000));
 

2. primary key 约束(主键约束)

 

 第一种形式：
 create table test(c number primary key );
 第二种形式：
 create table test(c number , primary key(c) ) ; 表级约束
 create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
 

3. foregin key (fk) 外键约束:(先定义父表，再定义子表）

 

 carete table parent(c1 number primary key );
 create table child (c number primary key , c2 number references parent(c1));
 或表级约束定义:
 create table child( c number primary key , c2 number , foreign key(c2) references
 parent(c1));
 create table test(c1 number primary key); 设置主键
 create table test(c1 number constraints test_c1 primary key); 定义约束名,默认约束名为
 SYS_ 在列后面定义约束称为列级约束
 create table test(c1 number primary key(c1)); 所有列定义完后再定义约束称为表级约
 束(能定义联合主键)
 cretae table test(c1 number,c2 number,priary key(c1,c2)); 定义联合主键
 create table child(c1 number primary key); 先要定义父表
 create table child(c1 number primary key, c2 number references parent(c1)); 然后定义子
 表references parent 定义外键
 create table child(c1 number primary key, c2 number references parent(c1) on delete
 cascate); on delete cascate 为级联删除
 create table child(c1 number primary key, c2 number references parent(c1) on delete set
 null); on delete set null 删除后将外键置空
 create table child (c1 number primary key, c2 number,foreignkey(c2) references parent(c1));

 

4. 两表没有任何关联时会产生迪卡尔乘积：

 

 select first_name , name from s_emp , s_dept;
 

5. insert 操作，插入记录(DML 操作)

 
 insert into student value(1,'xxx','xxx');
 insert into student(id,name,address) value(1,'xxx','xxx');
 注意：有空值的话:
 隐式插入
 INSERT INTO s_dept (id, name) VALUES (12, 'MIS');
 不往想为空的字段中插数据，系统默认为NULL
 显示插入
 INSERT INTO s_dept VALUES (13, 'Administration', NULL);
 select * from s_emp where 1=2; 这样选不出纪录，方便察看表结构
 

6. update 修改操作

 
 update table 表名set 字段名1=数据1 或表达式1， 字段名2=数据2 或表达式2
 [where ....=....];
 update shenfenzhen set num=99 where sid=2;
 

7. delete 删除操作

 

 delete from 表名[where ...=...];
 用delete 操作删除的记录可以通过rollback 命令回滚操作，会恢复delete 操作删除的
 数据。
 delete 操作不会释放表所占用的空间，delete 不适合删除记录多的大表。
 delete 操作会占用大量的系统资源。
 

8. alter table 命令

 

 alter table 命令用于修改表的结构(这些命令不会经常用)：
 增加字段：
 alter table 表名add(字段字，字段类型)
 删除字段：
 alter tbale 表名drop column 字段; (8i 以后才支持)
 给列改名:9.2.0 才支持
 alter table 表名rename column 旧字段名to 新字段名;
 修改字段
 alter table 表名modify( 字段，类型)
 (此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必
 须要为空)
 not null 约束是使用alter table .. modify (..,not null)，来加上的。
 增加约束：
 alter table 表名add constraint [约束名] 约束(字段);
 只能够增加表级约束。
 解除约束：(删除约束)
 alter table 表名drop 约束;
 （对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主
 键此时还有其它表引用时删除主键时会出错）
 alter table father drop primary key cascade;
 (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键
 约束被及联删除了）
 alter table 表名drop constraint 约束名;
 (怎样取一个约束名：
 a、人为的违反约束规定根据错误信息获取!
 b、查询视图获取约束名!)
 使约束失效或者生效
 alter table 表名disable from primary key; (相当于把一个表的主键禁用)
 alter table 表名enable primary key;
 （enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏
 数据才可以enable）
 更改表名
 rename 旧表名to 新表名;
 删除表：
 trucate table 表名;
 (表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表)
/******************************************************/

Day05

/******************************************************/
今天的note 不全，请自己补充

 

1. 哪些列更适合做索引

 

##表大，结果集小
经常出现在where 子句的列
经常用于表连接的列
主键列PK、唯一键列UK
create table test(
create unique index test_c1_idx on test(c1);

 

2. 新的建表语句

 

create table test_01
as
select * from test;

 

3. 一些概念

 

建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，
相对应的索引也会删除。索引是会进行排序。

 

4. 查看表的rowid

 

##每条记录都有自己的rowid
select rowid,first_name from s_emp;

 

5. 创建视图

 

creating views 视图名;
视图就相当于一条select 语句,定义了一个视图就是定义了一个sql 语句,视图不占空
间,使用view 不会提高性能，但是能简单化sql 语句
（扩展知识： oracle 8i 以后的新视图）
MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时
性，但是存在刷新问题，物化视图中的数据存在延迟问题，主要应用在数据仓库中
用要用于聚合表)
使用视图的好处：控制数据访问权限。
如何创建一个视图的例子：
create or replace views test_vi as select * from test1 where c1=1;
or replace 的意义，如果view 存在就覆盖，不存在才创建。
force|no force ，基表存在是使用，不存在是则创建该表。
此时往表test1（base table 基表）中插入数据时：表中没能变化，视图中的数据发生
改变
从视图中插数据时相对应的表会发生改变:
往视图中插数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创
建时的select 语句。
限制对数据库的访问，简化查询。
简单视图：来自于单表，且select 语句中不能包括函数，能进行DML 操作。
复杂视图：来源于多张表，不能执行DML 操作。

 

6. 删除视图drop views 视图名

 

7. 行列转置问题

 

8. 子查询还可以跟在from 后面

 

select e.first_name ,e.salary , a.asal
from s_emp e join
(select dept_id , avg(salary) asal from s_emp group by dept_id) a
on e.dept_id = a.dept_id
and e.salary > a.asal;

 

9. Rownum

 

##出现两条记录
select first_name , rownum from s_emp where rownum <=2;
##无记录，因为rownum 的特点是必须从1 条记录开始找
select first_name , rownum from s_emp where rownum between 4 and 9;
##排名问题：找出前N 条记录
##找出工资最高的前10 个人？
select rownum , first_name , salary
from (select first_name , salary
from s_emp
order by salary desc)
where rownum<=10;
##分页问题
##找出第11 条到第20 条的记录（先找出前20 条记录，再过滤掉前10 条）
##关键点：给rownum 起别名
##越往后走越慢，这种做法效率较低
select rn , first_name , salary
from (select rownum rn , first_name , salary
from s_emp
where rownum <=20)
where rn between 11 and 20;

 

10. (集合运算)Union 和Union ALL 拼结果集

 

##去重
select deptno from dept
union
select deptno from emp;
##不去重
select deptno from dept
union all
select deptno from emp;

 

11. (集合运算)intersect 去重后的交集

 

select deptno from dept
intersect
select deptno from emp;

 

12. (集合运算)minus 找出不包含的

 

select deptno from dept
minus
select deptno from emp;

 

13. Rename 改名

 

##改表名
rename abc to testabc;
##给列改名
alter table testabc rename column c1 to c10;
/******************************************************/

外延与复习

/******************************************************/

 

1. 查询系统表

 

##查询本用户下所拥有的所有表的表名
select table_name from user_tables;

 

2. SQLPLUS

 

a) SQLPLUS 的buffer 中会缓存最后一条sql 语句
i. 可以使用"/"来执行这最后一条命令
ii. 可以使用edit 命令来编辑最后一条sql 语句
iii. L 命令（list）（sqlplus 命令）可以显示buffer 中最后一条命令
b) desc [表名]
##sqlplus 命令，注意他不是sql 语句，用于查看表的结构。descript 的缩写
desc s_emp;

 

3. 将SQL 语句写入指定的文件

 

spool a.lst

 

4. sql 脚本

 

a) 也就是在文件中写有sql 语句的文件，可以在sqlplus 中运行
b) 引入sql 脚本
i. sqlplus 用户名/密码@sql 脚本
ii. 注意：在用户名密码输入结束后一定要加空格然后再写@sql 脚本
iii. 在脚本中最后一行写上“exit”，则运行完脚本以后，回到shell 上

 

5. Oracle 数据库中的空表

 

dual 测试表
select sysdate from dual;

 

6. 单行函数

 

1)字符函数
字符是大小写敏感的
转小写lower(字段名) --- 其中的参数可以是一个字符串常量或是一个字段名
转大写upper(字段名)
首字母大写initcap(字段名)
字符串拼接concat(字段1, 字段2)
截取子串substr(字段名, 起始位置，取字符个数)
dual 表，是专门用于函数测试和运算的，他只有一条记录
字符串拼接concat(...,....)
求指定子串substr(...,起始位置，取字符个数)
可以使用"-"表示从右向左取，取的时候可以从左往友取。
例：select substr(first_name,-2,2) sub from s_emp;（取后两个）
select substr(first_name,2,2) sub from s_emp;（取前两个）
2)数值函数
四舍五入round(数据,保留小数点后几位)
可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1 表示个
位（保留到十位）。
例：select round(15.36,1) from dual;
截取数字函数trunc(数据，保留的位数（小数点后位数）) 截取个位之后补0
例：select trunc(123.456,1) from dual;
3)日期函数
日期格式，
全日期格式世纪信息，年月日，时分秒。
缺省日期格式，日-月-年dd-mon-rr
修改当前会话的日期格式，会按照指定的格式输出日期
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
返回当前日期sysdate
例：select sysdate from dual;
select sysdate+1 from dual; 获得明天的日期，加1，单位是天
日期是格式敏感的
求两个日期间相隔了多少个月months_between(date1,date2)
加减指定数量的月份add_months(date,月数)，月数可以为负，负值就是减去相应的
月数。
从date 日期开始的第一个星期五next_day(date,FriDay)
返回月末的日期last_day(date)
截取日期trunc(date,'年或月或日或时分秒')
例：select next_day(sysdate,2) from dual;
例：select trunc(add_months(sysdate,1),'month') from dual;
ROUND('25-MAY-95','MONTH') 01-JUN-95
ROUND('25-MAY-95 ','YEAR') 01-JAN-95
TRUNC('25-MAY-95 ','MONTH') 01-MAY-95
TRUNC('25-MAY-95 ','YEAR') 01-JAN-95
练习：
返回下个月的第一天的日期
select round(last_day(sysdate),'MONTH') from dual;
select add_months(trunc(sysdate,'MONTH'),1);
4)不同数据类型间转换函数
将日期转成字符tochar(date,'日期格式')
日期格式要用有效格式，格式大小写敏感'yyyy mm dd hh24:mi:ss',
'year'(全拼的年),'mm'(数字表示的月) 'month'(全拼的月)，'day'(星期的全拼)，'ddspth'
(日期的全拼) 'yy mm dd'
例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;
将字符转换成数字to_number('...')
将数字转字符to_char(number，'fmt') fmt 是数字格式
将字符串转成日期to_date('...','日期格式')
例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；

 

7. Oracle 中的主键自动增长

 

使用序列sequence
drop table xxxbyx_1;
create table xxxbyx_1(
id number primary key,
name varchar2(20)
);
drop sequence xxx_1;
create sequence xxx_1;
insert into xxxbyx_1 values (xxx_1.nextVal , 'aaa');
insert into xxxbyx_1 values (xxx_1.nextVal , 'bbb');
insert into xxxbyx_1 values (xxx_1.nextVal , 'ccc');
select xxx_1.currval from dual;