返回值是1;如果数值是负数,返回值是-1;如果数值是0,返回值是0–select sign(100) from dual————— 1–select sign(-100) from dual———— -1–select sign(0) from dual—————— 06)round():四舍五入round(参数1,参数2):参数1:要进行四舍五入的数据参数2:如果是正数,表示保留几位小数;如果是0,表示只有整数;如果是负数,表示对小数点前第几位进行四舍五入。
–select round(45.926,2) from dual——- 45.93–select round(45.926,1) from dual——- 45.9–select round(45.926,0) from dual——- 46— select round(45.926,-1) from dual—– 50–:注释,只起到解释说明的作用,不参与程序的执行–select round(45.926,-2) from dual—— 0–select round(55.926,-2) from dual—— 1007)trunc():截取函数trunc(参数1,参数2)参数1:要截取数值。
参数2:如果是正数,表示保留几位小数;如果是0,表示舍弃所有的小数:如果是负数,表示舍弃小数点前第几位–select trunc(45.926,2) from dual — 45.92–select trunc(45.926,1) from dual– 45.9— select trunc(45.926,0) from dual– 45–select trunc(45.926,-1)from dual– 40–select trunc(45.926,-2)from dual– 05.约束:设计表的时候提前对表中的数据设置一些规则,只有满足这些规则,才可以把数据插入到表中,这些规则就是约束。
约束的类型:主键约束唯一约束检查约束外键约束默认值约束非空约束6.主键约束:主键约束的作用用来保持数据的唯一性,避免出现冗余的数据(防止表中出现完全相同的数据)。
1)一张表中只能有一个主键(主键约束),被主键约束修饰的列,该列的列值必须要非空而且唯一。
2)可以使用主键约束修饰一个列或者多个列的组值。
create table worker1(id number(4) primary key,name varchar2(50),age number(3))–insert into worker1 values(1,’王勃’,16)反例:–insert into worker1(name,age)volues(‘李密’,21)3)在创建表的时候指定主键约束的名字create table worker2(id number(4) constraint pk_id_w2primary key,name varchar2(50))insert into worker2 values(1,’张三’)–反例insert into worker2 values(1,’李四’)4)如果使用主键约束修饰多个列的组合值,称为联合主键(复合主键)5)创建表的时候,设置联合主键create table worker3(in number(3),name varchar2(50)age number(3),constraint pk_id_name_w3primary key(id,name))6)修改表的时候设置主键约束格式:alter table 表名add constraint 约束名primary key(列名1,列名2,列名3…)create table worker4(id number(4),name varchar2(50))alter table worker4add constraint pk_id_w4primary key(id)create table worker5(id number(4),name varchar2(50),job varchar2(50),age number(3))alter table worker5add constraint pk_id_name_w5primary key(id,name)a案例:创建一张表customer2,IDnumber(4),namevarchar2(50),password varchar2(50),age number(3),address varchar2(50),修改customer2的时候设置主键约束pk_id_name_cus2修饰id和name的组合值— create table customer2(id number(4),name varchar2(50),password varchar2(50),age number(3),address varchar2(50))alter table customer2add constraint pk_id_name_cus2primary key(id,name)7)删除主键:a)格式1:只能删除主键alter table 表名 drop primary keyalter table worker1 drop primary ket案例:删除worker2,worker3中的主键— alter table worker2 drop primary keyalter table worker3 drop primary keyb)格式2:alter table 表名 drop constraint 约束名alter table worker4 drop constraint pk_id_w4案例:删除worker5中的主键约束7日期处理函数:date1)常用的日期格式:YYYY-MM-dd:年月日y:年份 m:月份 d:几号eg:1999-12-11yyyy-mm-dd hh24:mi:ss 年月日 时分秒h:小时 mi:分钟 ss:秒钟eg:2011-11-11 11:11:11默认的日期格式:dd-mon月-yyeg:2012-12-22 22-12月-122)sysdate;当前的系统时间select sysdate from dualsysdate以天为单位案例:查询今天,昨天,明天select sysdate,sysdate-1,sysdate+1 from dual3)to_char(日期数据,’日期格式’):把日期类型数据(date)按照指定的格式转换为char类型字符串显示。
select sysdate,to_char(sysdate,’yyyy-mm-dd’)from dual案例:查询当前的系统时间,按照yyyy-mm-dd hh24:mi:ss的格式显示— select sysdate,to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)from dual案例:查询emp表中员工的姓名,职位,工资,入职时间,入职时间按照yyyy-mm-dd hh:mi:ss格式显示select ename,job,sal,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp4)使用to_char()函数获得日期的某一个部分select sysdate,to_char(sysdate,’yyyy’) from dual案例:查询emp表中员工的编号,姓名,入职时间以及入职的月份— select empno,ename,hiredate,to_char(hiredate,’mm’)from emp案例:查询emp表中12月份入职的员工的编号,姓名,职位入职时间— select empno,ename,job,hiredate from empwhere to_char(hiredate,’mm’)=’12’5)to_date(‘日期字符串’,’日期的格式’):把满足日期格式的char类型数据转换为对应的date类型的数据,经常用于插入 *** 作。
create table worker6(id number(4) primary key,name varchar2(50),hiredate date)insert into worker6values(1,’李世民’,to_date(‘2000-10-20 13:11:15′,’yyyy-mm-dd hh24:mi:ss’))往worker6插入3条数据6使用默认的日期格式插入数据insert into worker6values(5,’曹 *** ’,’12-12月-09′)不能插入时分秒往worker6表中插入3条数据values(6,’夏侯惇’,’15-10月-16′)insert into worker6values(7,’典韦’,’31-12月-17′)insert into worker6values(8,’夏侯渊’,’30-11月-15′)修改:update worker6 set hiredate=to_date(‘2013′,’yyyy’)7)months_between(d1,d2):求出日期d1和d2之间间隔了多少个月案例:查询emp表中员工的姓名,职位,工资,入职时间,以及工作了多少个月select ename,job,sal,hiredate,sysdate,months_between(sysdate,hiredate)from emp8)add_months(d1,数字):在日期d1之后数字个月select add_months(sysdate,6) from dual知识点:1.唯一约束:唯一约束用于指定某一个或者多个列的组合值具有唯一性,防止在该列中输入重复的列值。
1)使用唯一约束修饰的列,该列的列值必须唯一,但是可以输入空值。
2)一张表中可以出现多个唯一约束。
3)不要使用唯一约束修饰主键所在的列。
create table worker7(id number(4) primary key,name varchar2(50) unique)insert into worker7 values(1,’张三’)insert into worker7 values(2,null)–反例insert into worker7 values(3,’张三’)(违反唯一约束条件)4)使用一个唯一约束修饰多个列的组合值(唯一值)5)修改表的时候,设置唯一约束alter table 表名add constreint 约束名unique(列名1,列名2,列名3…)create table worker8(in number(4) primary key,name varchar2(50))alter table worker8add constraint uq_name_w8unique(name)insert into worker8 values(1,’李白’)反例:insert into worker8 values(2,’李白’)create table worker9(id number(4) primary key,name varchar2(50),password varchar2(50),email varchar2(50))alter table worker9add constraint uq_name_password_w9unique(name,password)案例:创建一张表BOOK3,id number(4)name varchar2(50) 书名, author carchar2(50) 作者,pub varchar2(50) 出版社, numinput namber(10)进货量, 修改book3,设置主键约束pk_id_name_b3修饰ID和name的列,设置唯一约束uq_author_pub_b3修饰author和pub的列— create table book3(id nu mber(4),name varchar2(50),author varchar2(50),pub varchar2(50),numinput number(10))alter table book3add constraint pk_id_name_b3primary key(id,name)alter table book3add constraint pk_author_pub_b3unique(author,pub)6.删除唯一约束:格式:alter table 表名drop constraint 约束名alter table worker8drop constraint uq_name_w8案例:删除worker9中的唯一约束alter table worker9arop constraint uq_name_password_w97)主键约束和唯一约束之间的区别a)一张表中只能定义一个主键约束,但可以定义多个唯一约束。
b)对于指定为主键修饰一个列或者多个列的组合值,其中任何一个列都不能出现空值,而对于唯一约束的列,该列的列值可以为空。
2索引:索引是建立在表中列上的数据库对象,用于提高数据的查询速度。
1)索引是提高查询效率的机制。
2)索引一旦创建以后就由Oracle系统自动进行维护,编写sql语句的时候不需要知道使用的是哪一个索引补充:having 子句:对分组以后的数据再次进行过滤,经常跟聚合函数结合使用格式select 列名/聚合函数from 表名where 条件group by 列名having 子句order by 列名/聚合函数/别名 asc/descwhere条件对整张表中所有的数据进行过滤having子句对分组以后的数据进行过滤–案例查询emp表中每一个部门最低工资高于900的部门编号,人数,工资总和以及最低工资,最后根据部门编号进行升序排列select deptno,count(*),sum(sal),min(sal)from empgroup by deptnohaving min(sal)>900order by deptno语法规则:首先执行where条件,对表中所有的数据过滤,然后使用group by进行分组,之后通过having子句对分组以后的数据再次进行过滤,最后执行order by进行排序注意:having子句一定要跟group by集合使用,而且having子句经常跟聚合函数结合使用练习:查询emp表中名字中没有字母A,或者所在部门编号是30号部门,查询每个部门最高工资低于5000的部门编号,人数,平均工资,最高工资,最后根据人数进行升序排列,如果人数一致,根据最高工资进行降序排列用到group by 分组,select只能有一个列名,后面均为聚合函数,此列名作为分组列名select deptno,count(*),max(sal),avg(sal)from empwhere deptno=30 or ename not like ‘%A%’group by deptnohaving max(sal)<5000order by count(*) asc, max(sal) desc练习2:查询emp表中含有上级领导,并且岗位不是SALESMAN,每个职位人数小于3个人的职位名称,人数,平均工资,工资总和,根据人数进行升序排列,如果人数一致根据平均工资再进行升序排列select job,count(*),avg(sal),sum(sal)from empwhere mgr is not null and job<>’SALESMAN’group by jobhaving count(*)<3order by count(*),avg(sal)关联查询(表的连接查询)所需要查询的数据来源多张表,使用关联查询,把多张表连接起来进行查询案例:查询emp表中员工的编号,姓名以及所在部门的编号,部门名称格式:select 别名1.*/列名,列名2.*/列名from 表名 别名1,,表2别名2where 关联条件select e.empno,e.ename,e.deptno,d.dnamefrom emp e,dept d笛卡尔积现象:是数学中的一个概念,表示两个表中的每一行数据任意组合,在表的关联查询中,如果没有关联条件,则表中的数据会出现乘积现象,称为笛卡尔积关联条件:用来描述两张表之间的关联关系,通过添加关联条件,有效的避免出现笛卡尔积现象emp表和dept表之间的关联关系:emp表中的deptno等于dept表中的deptno关联条件:emp.deptno=dept.deptno练习:查询emp表中的员工编号,姓名,职位,以及所在部门的编号,地址,最后根据部门编号进行升序排列,如果部门编号一致根据员工的编号进行降序排列select e.empno,e.ename,e.job,e.sal,d.deptno,d.locfrom emp e, dept dwhere e.deptno=d.deptnoorder by deptno,empno desc非等值连接:关联条件不是使用“=”进行连接案例:查询emp表中员工的姓名,职位,工资以及该工资的等级select e.ename,e.job,e.sal,s.gradefrom emp e, salgrade swhere e.sal between s.losal and s.hisal分析:emp表中员工的工资sal在salgrade(工资等级表)中的最低工资losal和最高工资hisal之间emp表和salgrade表之间的关联关系:e.sal between s.losal and s.hisal练习:查询工资的等级在1,3,4,5等级下员工的编号,姓名,职位,工资以及当前的工资等级,最后根据工资的等级进行降序排序,如果等级一致,根据员工的编号进行升序排列select e.empno,e.ename,e.job,e.sal,s.gradefrom emp e, salgrade swhere e.sal between s.losal and s.hisal and s.grade in(1,3,4,5)order by s.grade desc,e.empno练习2:查询emp表中员工的编号,姓名,职位,工资,该工资的等级以及该员工所在部门的编号,名称select e.ename,e.job,e.sal,s.grade,d.deptno,d.dnamefrom emp e, salgrade s,dept dwhere e.sal between s.losal and s.hisal and e.deptno=d.deptno练习3:查询emp表中职位是SALESMAN,MANAGER,ANALYST,PRESIDENT下员工的编号,姓名,职位,工资,奖金,入职时间,工资的等级,以及该等级最低工资所在部门的名称,最后根据工资的等级进行升序排列,如果工资等级一致,根据员工的编号降序排列select e.empno,e.ename,e.job,e.sal,e.comm,e.hiredate,s.grade,s.losal,d.dnamefrom emp e, salgrade s,dept dwhere e.sal between s.losal and s.hisal and e.deptno=d.deptno ande.job in(‘SALESMAN’,’MANAGER’,’ANALYST’,’PRESIDENT’)order by s.grade,e.empno desc
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)