oracle数据库行转列egg,Oracle数据库笔记

1. 关系型数据库与非关系型数据库

基于关系模型的数据库

关系模型:以二维表形式(行和列)存储数据的模型。

Oracle  mysql  sqlServer..    db2

非关系型数据库： nosql    not only sql

Redis(key-value)     mongdb  …

2.数据库认证

OCA,OCP,OCM

3.数据库数据类型

number  数值类型

number   范围-10的38次方 到 10的38次方

number(3)  能存3位整数，如果添加了小数时，自动四舍五入成整数插入

number(3,2)  表示三位有效数字，其中有两位小数，一位整数

char  字符类型    最大能保存2000字节

char(3)  能保存长度为3字节的字符(一个字母或数字占一个长度)中文占3个字节(utf-8编码时)

char(10)   如果插入的字符小于10字节，剩下的会用空格填满

缺点：浪费空间

优点:查询速度更快

varchar2(10)    字符类型  最大能保存4000字节

如果插入的字符长度为2字节，实际上占用的空间也为2

优点：节省空间

date  时间类型

to_date(‘2018-08-20 11:11:20‘,‘yyyy-mm-dd HH:mi:ss‘)

--把字符串日期转化为特定格式的date日期

yyyy   --年

mm 月

dd  日

HH 时(12小时制)      HH24(24小时制)

mi  分

ss秒

timestamp这是oracle9i对date数据类型的扩展。可以精确到毫秒。

clob(character large object)字符型大对象 最大4G

blob二进制数据 可以存放图片/声音4G一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。

4.增删改查+创建表格

Create table 表名(

字段1数据类型,

字段2数据类型,

。。。。。

字段2数据类型

);

注意：最后一个字段不要，

查询

Select  字段1，字段2…. from  表名;

Select  字段1，字段2…. from  表名where条件;

Select * from 表名；(*代表所有字段)

插入

Insert  into  表名values(值1,值2…);

注意：插入数据时，数据与表的字段顺序对应，数据类型也要对应

--指定字段插入

insert into test3(birth ,a1) values(sysdate,‘aaa‘);

insert into 表名(字段x ,字段y…) values(值x,值y);

修改

update  test3  set a1=‘pig2‘  where  a1=‘egg‘ ;

update  表名  set 字段1=值1 ,字段2=值2 where  字段x=值x ;

删除

delete from  test3 where a1=‘pig2‘;

delete from 表名 where  条件;

drop

Drop table 表名；

删除表结构和数据

注意：只有当字母作为字符内容时，才区分大小写

表Student

Sno(学号)

sname

sage

sgender

number

Varchar2(20)

number

char(3)

插入数据

1001

egg

12

男

1002

狗蛋

15

男

1003

翠花

16

女

1004

花花

12

女

查询表中所有数据

Select * from student;

查询性别为男的学生信息(学号，名字，性别)

Select  sno,sname,sgender from student where sgender=’男’;

查询学号为1002 的学生信息(学号，名字，性别)

Select sno,sname,sgender from student where  sno=1002;

修改名字为“花花”的年龄，改为20岁

Update  student set sage = 20 where sname=’花花’;

修改学号为1003的学生 性别为男

Update  student set sgender=’男’ where sno=1003;

删除学号为1001的学生

Delete from student where sno=1001;

5.命名规范

必须以字母开头

·长度不能超过30个字符

·不能使用oracle的保留字

·只能使用如下字符 A-Z，a-z，0-9，$,#,_等

6.字段操作

--给表添加一个字段username  varchar2(20)

alter table  sc1 add(username varchar2(20));

--修改字段的数据类型

alter table sc1 modify(username varchar2(50));

--删除某个字段

alter table sc1 drop column username;

--修改字段名字(重命名)

alter table sc1 rename column age to scAge;

7. 操作符

算术运算符

+  -  *  /   mod(x,y)

比较运算符

<   >  =       !=   <>  (不等于)

逻辑运算符

and(与)   or   (或)    not

8. Null

Null和空格和0是不一样的null表示空，没有赋值

--7.查询奖金为null的员工信息

select * from emp where comm  is null;

--8.查询奖金不为null的员工信息

select * from emp where comm  is not null;

当插入数据时，不插入值,用null

insert into test3 values(‘111‘,null);

9. Nvl()

--10.查询所有员工的奖金(如果奖金没有的用0表示)

select empno, nvl(comm,0)  from emp;

--nvl(a，b)如果a的值为null  ,取b  ,否则取a

10．别名

select * from emp  A where A.ENAME=‘SCOTT‘;

select empno  "员工编号" from emp;

select empno   as "员工编号" from emp;

select empno noo from emp;

select empno as noo from emp;

select empno as "Noo" from emp;

注意:别名不要用单引号

11.in

用in  查询指定散点集合,可用于子查询

--查询上级编号为7369,7698,7839的员工信息

select * from  emp A where A.Mgr in(7369,7698,7839);

--查询和SCOTT同一部门的员工信息(子查询)

select * from emp where deptno

in(select deptno from emp where ename=‘SCOTT‘ );

12．模糊查询like

-- %  代表0个或多个字符

--  _代表一个字符

--查询emp中名字带有A的员工信息

select * from emp where ename like ‘%A%‘;

--查询emp中名字第二个字母为M的员工信息

select * from emp where ename like ‘_A%‘;

--查询emp中名字首字母为S的员工信息

select * from emp where ename like ‘S%‘;

--查询emp中名字以R结尾的员工信息

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

--查询名字中包含%的学生信息(转义)

select * from sc2 where scname like‘%\%%‘ escape ‘\‘;

--把\作为转义的符号  要把% 转义  —>  \%

13.连接符||

select 6||‘asd‘ as "连接后的结果" from dual;

14. 排序  order by  + 要排序的字段

desc    降序

asc    升序(不写默认)

select * from  emp order by  empno asc;

select * from  emp order by  hiredate,sal;

--按多个字段排序，先排前面的字段，再后面的字段

15. 聚合函数

count()   max()   min()  sum()  avg()

16. 子查询

内部查询的结果作为外部查询的条件

17. 分组 group by

按照一定的规则进行分组，分组后的数据会聚合，需要使用聚合函数，使用聚合函数不一定要分组。

Select 的字段只能是分组的字段或聚合函数。

where和having的区别：

-- where是一次筛选，使用在分组之前，筛选的字段可以是任意的(不能是聚合函数)

-- having是二次筛选，使用在分组查询之后，紧跟着group by ,

有having,一定有group by ,有group by 不一定有having

--筛选的字段只能是分组字段或聚合函数

18. 去重distinct

去除指定字段的重复值

select distinct scname from sc2 ;

--去除scname重复的数据(如果有多个重复，只会留一条)

select distinct * from sc2 ;

--查询能作为上级的员工信息

select * from emp where empno

in (select distinct mgr from emp where mgr  is not null );

19. nvl2()

nvl2(a,b,c)

如果a是null，取c,否则取b

20. 表连接

内连接，左外连接，右外连接，全外连接，交叉连接

内连接:  inner   join ….   on

两张表互相匹配到的数据显示出来

select * from t_stu A inner join t_sco B on A.TNO=B.TNO;

select * from t_stu A , t_sco B where A.TNO=B.TNO;(自然连接)

左外连接

以左表为主表，主表的数据全部显示出来，从表的数据匹配到的显示出来

select * from t_stu A left join t_sco B on A.TNO=B.TNO;

select * from t_stu A , t_sco B where A.TNO=B.TNO(+);

右外连接

以右表为主表，同上

select * from t_stu A right join t_sco B on A.TNO=B.TNO;

select * from t_stu A , t_sco B where A.TNO(+)=B.TNO;

全外连接

两张表的数据全部显示出来

select * from t_stu A full join t_sco B on A.TNO=B.TNO;

交叉连接(笛卡尔积)

两张表的乘积

select * from t_stu A cross join t_sco B ;

select * from t_stu A ，t_sco B ;

自连接

自己连自己(当作是内连接的一种)

21. 常用函数

to_date

to_char()

集合运算(交集并集差集)，序列，主键，外键，约束，索引， 分页，行转列，列转行，视图，三范式(递归查询)

22. 集合运算

--集合运算

--交集，并集{2,3,4}    {2,5,9}，差集

select * from t_student for update;

select * from t_teacher;

--1.查询所有学生和老师的姓名、性别、生日

--union【all】并集

select tname ,tsex,tbirthday from t_teacher

union

select sname ,ssex,sbirthday from t_student;

--union公共部分只会显示一次(会去重)

--union all公共部分会显示多次(不会去掉重复的)

--交集：求公共部分

--查询emp工资大于2000，并且在20部门的员工姓名，工种

--intersect

select ename,job from emp where sal > 2000

intersect

select ename,job from emp where deptno=20;

select ename,job from emp where sal > 2000

and deptno=20;

--差集

--minus

--查询工资大于2000，但是部门不在20

select * from emp where sal > 2000

minus

select * from emp where deptno=20;

select * from emp where sal>2000 and deptno !=20;

注意:做集合运算的两个集合的字段个数，字段类型要一致(兼容)，顺序也要一致，比如:ename ,job,hiredate  和ename hiredate job  是不一样的。

23. 约束

概念：加在表上的规则或条件

约束分类：非空约束，主键约束，外键约束，唯一约束，检查约束

(1)非空约束

create table tt(

t_id number,

t_name varchar2(50) not null,

t_sex char(3)

);

t_name添加了非空约束，该字段不能插入空的数据(null)

(2)唯一约束unique

create table tt(

t_id number unique,

t_name varchar2(50) not null ,

t_sex char(3) check(t_sex in(‘男‘,‘女‘))

);

alter table tt add constraint t1 unique(t_name);

--表名约束名字段

(3)检查约束

t_sex char(3) check(t_sex in(‘男‘,‘女‘))

Alter table tt add constraint  my  check(length(t_name)<6);

(4)主键约束(主键)primarykey

唯一的，不为空的(唯一标识)，每张表都会有主键

alter table tt add constraint my_key  primary key (t_id);

主键和唯一键的区别

主键是唯一不为空的，唯一键 可以为空，但是只有一个空

(5)外键约束(外键)foreignkey

进行两张表的强制关联，外键一般是另外一张表的主键或者是唯一键

物理外键：

逻辑外键：

3. 序列  (掌握)

用来提供主键值

--怎么创建序列？

create sequence seq_tt;--简单的创建方式

select seq_tt.nextval from dual;--得到序列的下一个值

select seq_tt.currval from dual;--得到序列的当前值

注意:创建的新序列，第一次不能执行currval(获取不到当前值)

--自定义序列

create sequence seq_my

start with 100  --起始值

maxvalue  120 --最大值

increment by 5  --增量

nocycle  --是否循环cycle循环；nocycle不循环

;  ·

drop sequence seq_my;--删除序列

4. 索引  (sql优化之一)

是一种的特殊的查询表

提高查询的速度

当做是书的目录

索引本身会占空间，索引不是越多越好

会减慢数据录入和删除的速度，做修改也要更新索引表

创建

create index myIn on t_student (sno);

删除

drop index myIn;

24.视图

是一种虚拟的表 ，是一种逻辑的概念，视图并不保存数据

是从表中抽取出来逻辑上相关的数据集合。

视图是建立在已有的表的基础上。视图赖以建立的这些表叫做基表。

可以理解为存储起来的sql 语句

优点：简化复杂查询

视图不能提高性能

--创建视图

create view myview as

select A.empno,B.dname from emp A ,dept B where  A.Deptno=B.DEPTNO

with read only ;

drop view myview;--删除视图

select * from myview;--查询视图

25.三大范式

是设计数据库表应该遵循的规则

1nf    2nf   3nf  bcnf    4nf    5nf    dknf   …

第一范式(1nf):原子性，将列(字段)分到不可再分为止

个人信息表: 姓名，年龄，地址

江西省南昌市南昌县..

要查看某个人的所在省份或所在市？

优化成：省，市，县。。详细地址

第二范式(2nf):满足第一范式的前提，非主键完全依赖于主键。(不存在部分依赖)

学生表：学号(主键)，姓名，课程号，成绩

优化成:

学生表：学号 ，姓名

课程表:课程号  。。

成绩表：学号，课程号，成绩(中间关系表)(学号和课程号作为联合主键)

第三范式(3nf):满足第二范式的基础上，非主键列必须直接依赖于主键，不能存在传递依赖，减少冗余

举个例子：

学生表：学号，性别，系号，系主任

传递依赖：学号—系号—系主任

优化成：

学生表：学号，性别，系号

院系表：系号，系主任

注意:第三范式不一定要满足，反而有时特意添加冗余字段，减少表连接，提高查询效率

原文：https://www.cnblogs.com/lin02/p/11104159.html