一、sqlplus命令在bin目录下
有一个环境变量Oralce_sid，决定跟哪个数据库连
严格做数据类型转换
where子句中尽量不写表达式
select to_char(sysdate,'fmmm') from dual;
'fm'是去掉前导0
二、多表查询
表连接（关联查寻）
如果多表查询时不加where子句，也就是过滤条件或者是使用了无效的条件，就会产生两表之间记录的相互逐条匹配（组合），产生很多无效的结果（笛卡尔积）。
注意：在使用表连接时，要注意查询的表间的关系信息，表之间的字段所表示的信息的关系
1、等值连接
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表2 表别名2
where 表别名1.字段名3=表别名2.字段名4;
表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用
表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。
注意：当为表起了别名，就不能再使用表名.字段名。
例：select a.first_name,a.last_name,b.name
from s_emp a,s_dept b
where a.dept_id=b.id;
2、非等值连接
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表2 表别名2
where 表别名1.字段名3 ..... 表别名2.字段名4
....可以使比较运算符，也可以使其他的除了'='的运算符
例：select e.ename, d.grade,e.sal
from emp e,salgrade d
where e.sal between d.losal and d.hisal;
3、自连接
用别名把一张表中的数据分成两部分，然后在使用条件过滤。
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表1 表别名2
where 表别名1.字段名3=表别名2.字段名4;
例：select a.first_name ename,b.first_name cname
from s_emp a,s_emp b
where a.manager_id=b.id;
以上所提到的表连接，都叫做内连接，严格匹配两表的记录。
4、外连接
会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。
例：select a.first_name enamei,a.id,b.first_name cname,b.id
from s_emp a,s_emp b
where a.manager_id=b.id(+);
即用a表中的数据去匹配b表的，若b表中有null，系统模拟纪录与其匹配
注意：要把那一方的记录全部都显示出来，还有注意条件(+)跟在要全部选出的对端。
外连接的应用：
列出哪个部门没有员工
select e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno
and e.deptno is null;
三、组函数
group 组
group by 分组子句，按指定的分组规则分组 ，这个group by 子句可以跟在 select 语句后或是 having后面。
group by子句也会出发排序操作，会按分组字段排序。
select [组函数或分组的字段名] ，... from 表名 group by [字段名1],[字段名2],.....；
例：select avg(salary) from s_emp group by dept_id;
注意：组函数可以处理一组数据，返回一个值。
组函数会忽略空值。
avg(..),求平均值，sum(..),求和 这两个函数的参数只能是number型的。
以下所提到的函数可以使用任意类型做参数。
count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。
max(..),min(..)求最大值和最小值，
count(*),统计表中记录数。
例：select max(b.name),avg(a.salary), max(c.name)
from s_emp a,s_dept b,s_region c
where a.dept_id=b.id and b.region_id=c.id
group by b.dept_id;
注意：只要写了group by子句，
*** select后就只能用group by后的字段或者是组函数。 ***
where子句只能够过滤记录，放单行函数。
having子句可以过滤组函数结果或是分组的信息，且写在group by子句后。
例:
select max(b.name),avg(a.salary), max(c.name)
from s_emp a,s_dept b,s_region c
where a.dept_id=b.id and b.region_id=c.id
group by b.id
having sum(a.salary)>4000;
column 也可以定义有别名的列的格式。
column "别名" 格式定义
注意：要先过滤掉不需要的记录，然后再进行分组操作，提高效率。
四、子查询
子查询，就是可以嵌在任何的sql语句中的select语句。
在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。
注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值，in）。
配合使用子查询返回的结果必须符合运算符的用法。
例:
select first_name,title
from s_emp
where title=any(select title from s_emp
where last_name='Smith')
and upper(last_name)!='SMITH';
select first_name,title
from s_emp
where title in (select title from s_emp
where last_name='Smith')
and upper(last_name)!='SMITH';
五、将业务需求转换成可操作的表
一: 需求分析
二: 画E-R图
三: 转换成表关系
四: 割接（新老系统交接）
五:
E-R图属性:
* 为强制且非空属性
o 可选属性(可以有值也可以没有)
#* 表示此属性唯一且非空
实体关系:
mastbean maybean
数量关系: 多对一关系
一对多关系
一对一关系
多对多关系
第一范式，所有的属性都必须是单值，也就是属性只表示单一的意义。（记录可以重复，没有任何限制）
第二范式，属性要求唯一且非空，（记录不可重复，但是数据可能会出现冗余）。
第三范式，非主属性只能依赖于主属性，不能依赖于其他非主属性。（解决数据冗余问题）
六、约束
约束是针对表中的字段进行定义的。
primary key （主键约束 PK）保证实体的完整性，保证记录的唯一
主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键。
foreign key （外建约束 FK）保证引用的完整性，
外键约束，外键的取值是受另外一张表中的主键或唯一值得约束，不能够取其他值，只能够引用主键会唯一键的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。
unuque key（唯一键），值为唯一
index（索引）是数据库特有的一类对象
view（视图）
典型的一对多 class 对应多个学生。
student table class table
______________________________ _________________________
| id | name | address| class_id| | id |class_desc|class_num|
|(PK)|______|________|___(FK)__| |(pk)|__________|_________|
| | | | | | | | |
一对一
student tabel shenfenzheng table
____________________ _________________________________
| id | name | address| | s_id |shenfen_desc|shenfen_num|
|(PK)|______|________| |(PK，FK)|____________|___________|
| | | | | | | |
多对多
student tabel zhongjian table kecheng table
____________________ _________________________________ __________________
| id | name | address| | s_id |shenfen_desc|shenfen_num| | kid | kechengname|
|(PK)|______|________| |(FK，FK)|____________|___________| | (PK)|____________|
| | | | |联合主键| | | | | |
引用对方表的主键,当作本身的主键,所以这个表的主键,既是主键又是外建
建表和其他相关操作
DDL语句
创建表：
create table 表名 ( 字段名1 类型(数据长度)(default ...) 约束条件， 字段名2 类型(数据长度) 约束条件 );
Oracle数据库中的数据类型
varchar(长度)，可变长字符串，char(长度) 定长
number(..,..),number 表示浮点数，或者是整数
long 大对象，clog 字符的大对象，相当于文本文件在表中只存放一个相当于只针对值
blog 二进制的大对象，也是以相当于指针的形式存放的。
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
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
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));
如果两个字段都为唯一且非空,这时可以定义成UK+NOT NULL
(PK或UK)一对多(FK)
(PK+UK)一对一(FK) 或 (PK)一对一(PK)
多对对多关系,一般都通过一张中间表来分解成两个一对多的表
建立表
create table[schema]table
schema: 一个用户对应一个schema 不同用户下的表不能互相查看
select count(*) from s_dept; <===> select count(*) from sd0611.s_dept;
一个表中只能存储一个LONG类型
CLOB 存储大的文本对象
BLOB 存储大的二进制对象
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));
课堂练习
两表没有任何关联时会产生迪卡尔机：
select first_name , name from s_emp , s_dept;
等值连接：
练习一:查看员工的姓名和员工部门号要考虑到表中实际数据中空值的影响）
select first_name , name from s_emp, s_dept where s_emp.dept_id=s_dept.id;
练习二：每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
非等值连接：
练习三:查出每个员工和每个员工的工资级别
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
自连接：
select first_name , manager_id from s_emp;
练习四:查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id;
外连接：(防止空值时，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少；
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id(+);
练习五：查看员工分部的部门：
select distinct(deptno) from emp ;
找出没有员工的部门：(很经典的一个例子,用外连接来解决的标准做法,这是一种方式，用子查询也可以实现）
第一步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno;
第二步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno and e.deptno is null;
练习六：查询员工有多少人有提成：
select count( commission_pct ) from s_emp ;
select sum(commission_pct) count(*) from s_emp;
练习七：员工分部在多少个不同的部门：
select count(dept_id) from s_emp;
select count(distinct dept_id) from s_emp;
练习八：求各个部门的平均工资：
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
//体会下句sql
select region_id , count(*) from s_dept ****此句会有错,请多体会********
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确）
练习九：求各个部门不同工种的平均工资：
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;
练习十：查询哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
练习十一：除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ; (此种sql效率要高，先过滤再计算）
练习十二：求各个部门的平均工资:
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name) ***
select max(d.name) , avg(e.salary) , max(r.name)
from s_emp e, s_dept d , s_region r
where e.dept_id = d.id and d.region_id=r.id
group by d.id ;
关于子查询: Subqueries
练习十三：找出所有员工中，工资最低的那个员工：( 利用子查询 ）>
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp) ;
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;
练习十四：查询谁跟Smith的工种一样：
select last_name from s_emp where last_name='Smith';
//下种写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
使用子查询时应注意： 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value
练习十五：查出哪些员工的工资比平均工资低：
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from s_emp group by dept_id ) ;
练习十六：哪个部门里没有员工(用子查询的方式来实现）：
select deptno from dept where deptno not in ( select deptno from emp );
如果给你带来帮助，欢迎微信或支付宝扫一扫，赞一下。
