Oracle数据库学习

Oracle数据库学习第二章

本章学习要点：

• SQL简介

• 如何创建表的同时加约束

• 如何对表里的数据进行增删改查

• SQL的三大函数

SQL简介

• 1）SQL语言概述结构化查询语言(Structured Query Language,简称SQL）；
• 2）SQL是数据库语言。Oracle用该语言检索和存储信息；
• 3）表示主要的数据库对象，用于存储数据；
• 4）SQL 支持下列类别的命令：
  1.数据定义语言（DDL,Data Definition Language） 如 CREATE ,DROP,ALTER
  2.数据操纵语言（DML,Data Manipulation Language） 如 INSERT ,DELETE,UPDATE,SELECT
  3.事务控制语言（TCL,Transaction Control Language）如 COMMIT,SAVEPOINT,ROLLBACK
  4.数据控制语言（DCL,Data Control Language）如 GRANT,REVOKE

如何创建表的同时加约束

–创建数据表

–主表(父亲)
create table grade(
gid number primary key, –pk主键约束（每张表必须有主键）
gname varchar2(20) not null
)

–子表(儿子)
create table student(
sno number not null primary key, –pk主键约束（每张表必须有主键）
sname varchar2(20) not null, –notnull
sage number(3,0) check(sage between 18 and 25), –Ck检查约束（检查符不符合条件）
ssex char(2) check(ssex=’女’ or ssex=’男’),同上
saddress nvarchar2(50) default(‘杭州市西湖区’), –DF（默认约束）
stel number(11,0) unique, –UQ唯一约束
gid number references grade(gid) –Fk 外键约束(数据类型一致)
)

如何对表里的数据进行增删改查

添加数据语法
1 )添加所有数据
insert into 表名 values（对应值）；
如： insert into grade values(1,’java140’);
insert into grade values(2,’java146’);
2）添加部分数据
insert into 表名（字段1，字段2） values（对应值1，对应值2）；
如： insert into student(sno,sname,ssex,gid) values(1002, ‘张三’,’男’,1);
insert into student(sno,sname,gid) values(1003, ‘李四’,1);
3 )批量添加数据 insert into 新表名 select * from 源表 (新表名必须存在!)
如： insert into newStudent1 select sno,sname,gid from student;

–4)批量添加数据 create table 新表名 as select * from 源表 (where 1!=1 )复制表的结构
– (新表名不存在!)
create table newStudent as select sno,sname,gid from student; –复制表的所有数据
create table newStudent1 as select sno,sname,gid from student where 1!=1; –复制表的结构

修改数据语法
根据条件修改数据 – update 表名 set 字段名=修改值, 字段名=修改值 where 条件
如：update student set sage=18,ssex=’女’ where sno=1002
update student set stel=199929323,gid=2 where sno=1002
update student set gid=1 where sno=1001

删除数据语法
1)根据条件删除 – delete [from] 表名 [where 条件]
delete from student where sno=1003

2)删除表所有数据 – delete [from] 表名
select *from newStudent1
delete newStudent1;

3）删除结构
drop table newStudent1;

查询数据语法
1） 查询所有的数据 select * from 表名 (*:这个表中所有的字段名)
select *from student;
2）根据条件查询 select * from 表名 [where 条件]（条件可以多个，条件之间用逗号）
select * from student where sno=1001
3 )查询学生的姓名,年龄 ,所在年级–起别名 姓名
select sname 姓名,sage 年龄,gid 年级 from student;
4 )过滤重复的数据 distinct 写在字段名前面
select distinct gid from student;
5）根据条件降序或者升序排列select * from 表名 [where 条件] [order by 字段名 desc/asc (默认可以不写)]
select * from emp where deptno=20 order by sal;
6）如何使用列别名？别名中有空格如何解决？
select ‘T’||empno, ename “姓 名” from emp; 其中||相当于JAVA中的+，添加的别名如果中间带空格必须使用“”;

SQL的三大函数

Oracle 提供一系列用于执行特定操作的函数

SQL 函数带有一个或多个参数并返回一个值

1)单行函数 :单行函数对于从表中查询的每一行只返回一个值

日期函数

–返回系统时间
select sysdate from dual;
– add_months(d1,n1) 【功能】：返回在日期d1基础上再加n1个月后新的日期。
select sysdate, add_months(sysdate,4) from dual;

–last_day(d1)【功能】：返回日期d1所在月份最后一天的日期。
–oracle默认的日期格式 日-月-年
select sysdate, last_day(sysdate),last_day(‘27-10月-2016’),last_day(‘27-7月-2016’) from dual;

–extract(c1 from d1) 【功能】：日期/时间d1中，参数(c1)的值
select sysdate, extract(year from sysdate),extract(month from sysdate),extract(day from sysdate) from dual;

数字函数

select ceil(-18.8),ceil(18.8) from dual; –ceil对一个数进行上舍入

select floor(-18.8),floor(18.8) from dual; –floor对一个数进行下舍入

select mod(4,3), mod(6,2) from dual; –mod是求余

字符函数

–LENGTH(c1)【功能】返回字符串的长度;
select ‘zhang’,length(‘zhang长大’),lengthb(‘zhang长大’) from dual;

–LTRIM(c1,[,c2])【功能】删除左边出现的字符串
select LTRIM(’ gao qian jing’) text from dual;

–SUBSTR(c1,n1[,n2]) 【功能】取子字符串(下标是从1开始,n1包含起始位置,n2字符的个数)
select substr(‘zhangsan’,3,2),substr(‘zhangsan’,3,3),substr(‘zhangsan’,3) from dual;

–REPLACE(c1,c2[,c3])
–【功能】将字符表达式值中，部分相同字符串，替换成新的字符串
–c2 要替换的字符串
–c3 替换的值
select replace(‘this is a hello’,’is’,’A’) from dual;

–INSTR(C1,C2[,I[,J]]) –类似于 –charAt
–【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
select instr(‘this is a hello’,’is’),instr(‘重庆某软件公司’,’某’,5,1) from dual;

转换函数
常用的转换函数有：
TO_CHAR
–TO_CHAR(x[[,c2],C3]) 【功能】将日期或数据转换为char数据类型
– x是一个date或number数据类型。c2为格式参数 c3为NLS设置参数

select sysdate, to_char(sysdate,’yyyy-MM-dd hh:mi:ss’),to_char(sysdate,’yyyy-MM-dd hh24:mi:ss’)
,to_char(120)||1, to_char(15,’x’),to_char(10,’x’) from dual;

TO_DATE
–TO_DATE(X[,c2[,c3]]) 【功能】将字符串X转化为日期型
select to_date(‘2017-10-12’,’yyyy-MM-dd’) from dual;

select hiredate,to_char(hiredate,’yyyy-MM-dd’) from emp;

TO_NUMBER
–TO_NUMBER(X[[,c2],c3])【功能】将字符串X转化为数字型
select to_number(‘1002’),TO_CHAR(11,’XX’) from dual;

其他函数
【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
注意两者的类型要一致 */
select nvl(null,’aa’),nvl(‘name’,’aa’) from dual;
– select * from emp; if…else
select comm, nvl(comm,0) from emp;
【语法】NVL2 (expr1, expr2, expr3)
【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。
expr2和expr3类型不同的话，expr3会转换为expr2的类型
select comm, nvl2(comm,comm,0) from emp;
decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
【功能】根据条件返回相应值
select * from emp;
select job,decode(job,’CLERK’,’职员’,’SALESMAN’,’销售员’,’MANAGER’,’经理’) from emp;
–** rownum【功能】返回当前行号
select e.*,rownum from emp e;

2)聚合函数：聚合函数基于一组行来返回结果为每一组行返回一个值–max,min,avg,sum,count

如：select max(sal) 最高工资, min(sal)最低工资 , sum(sal) 总工资, avg(sal) 平均工资, count(1) 人数 from emp;
1 )GROUP BY子句:用于将信息划分为更小的组每一组行返回针对该组的单个结果
如： –根据不同部门进行分组
–select distinct deptno from emp;
select deptno from emp group by deptno;

–统计每个部门人数
select deptno, count(1) from emp group by deptno;

–ename发生错误: 是多个数据
select deptno, count(1), ename from emp group by deptno;

–将ename分组
select deptno, count(1), ename from emp group by deptno,ename;

2 )HAVING子句 用于指定 GROUP BY 子句检索行的条件 (分组之后)
–统计每个部门的平均工资大于>2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000

–总结查询语法
select * from 表名 [where 条件] [group by …][having 条件]
–[where 条件] 分组之前
–group by … 分组
–[having 条件] 分组之后

–根据job=’CLERK’
select * from emp where job=’CLERK’

–查询职位是CLERK的员工的每个部门的最高工资
select deptno, max(sal) from emp where job=’CLERK’ group by deptno;

–查询职位是CLERK的员工的每个部门的最高工资大于>1000的部门
select deptno, max(sal) from emp where job=’CLERK’ group by deptno having max(sal)>1000;

3)分析函数:根据一组行来计算聚合值用于计算完成聚集的累计排名等分析函数为每组记录返回多个行

–(1)ROW_NUMBER 返回连续的排位，不论值是否相等
ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
select * from emp;

–根据工资降序排序
select e.*, row_number() over (order by sal desc) from emp e

–根据部门编号分组,按工资降序排序
select e.*,row_number() over (partition by deptno order by sal desc) numbers from emp e;

–(2)RANK 具有相等值的行排位相同，序数随后跳跃
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

select e.*,rank() over (order by sal desc) from emp e

select e.*,rank() over (partition by deptno order by sal desc) numbers from emp e;

–(3)DENSE_RANK 具有相等值的行排位相同，序号是连续的
【语法】dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

select e.*,dense_rank() over (order by sal desc) from emp e

select e.*,dense_rank() over (partition by deptno order by sal desc) numbers from emp e;

–补充:
–通配符: like %
select * from emp;
–查询S开头的姓名 %:0-n
select * from emp where ename like ‘S%’

–查询包含S的姓名
select * from emp where ename like ‘%S%’

–查询S结尾的姓名 %:0-n
select * from emp where ename like ‘%S’

– _ :任意1个字符
select * from emp;
–查询姓名
select * from emp where ename like ‘KIN_’
select * from emp where ename like ‘SM___’