4、高级查询和函数
一、DML、函数、高级查询
--为scott用户解锁
SQL> alter user scott account unlock;
用户已更改。
--为scott设置密码
SQL> alter user scott identified by tiger;
用户已更改。
--切换用户连接
SQL> conn scott;
输入口令: tiger
已连接。
--删除学生成绩表中成绩列
alter table stuinfo drop column score;
--添加班级列
alter table stuinfo add(classno varchar2(12));
--查看表结构
desc stuinfo;
--查询序列
select sequence_name from user_sequences;
--插入语句
SQL> insert into stuinfo values(seq_stuinfo.nextval,'holly','TB07');
已创建 1 行。
SQL> insert into stuinfo values(seq_stuinfo.nextval,'汤帅','TB07');
已创建 1 行。
SQL> insert into stuinfo values(seq_stuinfo.nextval,'陆毅文','TB07');
已创建 1 行。
SQL> insert into stuinfo values(seq_stuinfo.nextval,'张宗奇','TB07');
已创建 1 行。
SQL> insert into stuinfo values(seq_stuinfo.nextval,'周强','TB07');
已创建 1 行。
SQL> insert into stuinfo values(seq_stuinfo.nextval,'董演','TB07');
已创建 1 行。
SQL> insert into stuinfo values(seq_stuinfo.nextval,'薛东','TB07');
已创建 1 行。
SQL> insert into stuinfo values(seq_stuinfo.nextval,'钱涛','TB07');
已创建 1 行。
--修改学生成绩表结构
SQL> alter table stuscore modify(english number(3,1),chain number(3,1),history number(3,1));
SQL> desc stuscore;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
SID NOT NULL NUMBER
ENGLISH NUMBER(3,1)
CHAIN NUMBER(3,1)
HISTORY NUMBER(3,1)
EXAMTIME DATE
--插入学生成绩信息
SQL> insert into stuscore values(seq_stuscore.nextval,1,50.5,34.5,35.5,to_date('2014-4-4','yyyy-mm-dd'));
已创建 1 行。
SQL> insert into stuscore values(seq_stuscore.nextval,2,70.5,64.5,85.5,to_date('2014-4-8','yyyy-mm-dd'));
已创建 1 行。
SQL> insert into stuscore values(seq_stuscore.nextval,3,70.5,64.5,85.5,to_date('2014-4-8','yyyy-mm-dd'));
已创建 1 行。
SQL> insert into stuscore values(seq_stuscore.nextval,4,70.5,64.5,85.5,to_date('2014-4-8','yyyy-mm-dd'));
已创建 1 行。
SQL> insert into stuscore values(seq_stuscore.nextval,5,70.5,64.5,85.5,to_date('2014-4-8','yyyy-mm-dd'));
已创建 1 行。
SQL> insert into stuscore values(seq_stuscore.nextval,6,70.5,64.5,85.5,to_date('2014-4-8','yyyy-mm-dd'));
已创建 1 行。
SQL> insert into stuscore values(seq_stuscore.nextval,7,70.5,64.5,85.5,to_date('2014-4-8','yyyy-mm-dd'));
已创建 1 行。
SQL> insert into stuscore values(seq_stuscore.nextval,8,70.5,64.5,85.5,to_date('2014-4-8','yyyy-mm-dd'));
已创建 1 行。
二、简单查询、模糊查询、条件查询、单行函数
--切换sqlplus
>sqlplus
Enter user-name: system
Enter password:accp
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
--解锁scott测试用户
SQL> alter user scott account unlock;
User altered.
--为scott用户设置密码
SQL> alter user scott identified by tiger;
User altered.
--切换scott用户登录
SQL> conn scott;
Enter password:tiger;
Connected.
--1.基本查询
--1.1查询员工职位
SQL> select distinct job from emp;
--1.2查询每个员工的职位（员工是唯一的，职位是可以重复的）
SQL> select ename,job from emp;
--2.算术操作符
--2.1查询每一个员工的姓名、职位、年薪
SQL> select ename,job,sal*12 from emp;
--2.2查询每一个员工的姓名、职位、年薪(每个员工奖金300)
SQL> select ename,job,(sal+300)*12 as income from emp;
--2.3公司每年年给大家奖金300同时,年底都会多发一个月基本工资
SQL> select ename,job,(sal+300)*12+sal as income from emp;
--2.4.数据类表显示中文字段
SQL> select '员工编号：'|| empno||';员工姓名：'||ename from emp;
--3..限定查询
--3.1查询员工编号为7839员工
SQL> select empno,ename,job ,sal from emp where empno=7839;
--4.关系运算
--4.1查询工资高于1500的员工信息
select empno,ename,job ,sal from emp where sal>1500;
--4.2查询工资在1500到300之间的员工信息
select empno,ename,job ,sal from emp where sal>=1500
and sal<=3000;
--4.3查询职位是办事员CLERK 或销售人员SALESMAN的全部信息
SQL> select empno,ename,job,sal from
emp where job='CLERK' or job='SALESMAN';
--4.4查询职位是办事员CLERK或销售人员SALESMAN的信息，并且要求这些员工的工资大于1500
select empno,ename,job,sal from emp
where (job='CLERK' or job='SALESMAN') AND sal>1500;
--4.5查询所有不是办事员CLERK的员工信息
select empno,ename,job,sal from emp where job !='CLERK';
select empno,ename,job,sal from emp where job <>'CLERK';
select empno,ename,job,sal from emp where not job='CLERK';
--5.判断是否为空：is (not） null
--5.1查询所有领取奖金的员工信息
select empno,ename,job,sal,comm from emp
where comm is not null;
select empno,ename,job,sal,comm from emp
where not comm is null;
--6.指定范围的判断：in操作符
--6.1查询所有不领取奖金的员工信息
SQL> select empno,ename,job,sal,comm from emp
where comm is null;
--6.2查询员工编号是7369,7566,7788的员工信息
select empno,ename,job,sal,comm from emp
where empno=7369 or empno=7566 or empno=7788；
select empno,ename,job,sal,comm from emp
where empno in(7369 ,7566 ,7788 );
--7.模糊查询like
--7.1查询员工姓名中以字母A开头的全部信息
select empno,ename,job,sal,comm from emp
where ename like 'A%';
--7.2查询员工姓名中第二个字母为A的全部信息
select empno,ename,job,sal,comm from emp
where ename like '_A%';
--7.3查询员工姓名中包含字母为A的全部信息
select empno,ename,job,sal,comm from emp
where ename like '%A%';
--7.4查询员工姓名中不包含字母为A的全部信息
select empno,ename,job,sal,comm from emp
where ename not like '%A%';
--8.数据排序：默认升序asc，降序desc
--8.1 查询所有员工信息，要求工资升序排列
select empno,ename,job,sal,comm from emp
order by sal;
select empno,ename,job,sal,comm from emp
order by sal asc;
--8.2 查询所有员工信息，要求工资降序排列
select empno,ename,job,sal,comm from emp
order by sal desc;
--8.2 查询所有员工信息，要求工资sal降序排列，雇佣日期hiredate升序
select empno,ename,job,sal,comm from emp
order by sal desc，hiredate asc;
--9.单行函数
--9.1 字符函数
--(1)转换为大写：
select upper('hello') from dual;
--(2)转换为小写：
select lower(ename),sal from emp;
--（3)动态输入
SQL> select empno,ename,sal from emp where ename='&str';
Enter value for str: SMITH
old 1: select empno,ename,sal from emp where ename='&str'
new 1: select empno,ename,sal from emp where ename='SMITH'
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
--输入时通过upper转换成大写
SQL> select empno,ename,sal from emp where ename=upper('&str');
Enter value for str: smith
old 1: select empno,ename,sal from emp where ename=upper('&str')
new 1: select empno,ename,sal from emp where ename=upper('smith')
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
--(4)将每个员工的首字母大写,其余全部小写
select initcap(ename) from emp;
INITCAP(EN
----------
Smith
Allen
Ward
Jones
-(5)查询每个员工姓名的长度
SQL> select ename,length(ename) from emp
ENAME LENGTH(ENAME)
---------- -------------
SMITH 5
ALLEN 5
--（6）将姓名中所有的A替换为_
SQL> select replace(ename,'A','_') from emp;
REPLACE(EN
----------
SMITH
_LLEN;
--(7)截取字符串
--substr(字段,开始点); 开始点是从1开始，表示从开始点开始截取到最后
SQL> select ename,substr(ename,3) from emp;
ENAME SUBSTR(ENAME,3)
---------- ----------------
SMITH ITH
ALLEN LEN
----substr(字段,开始点，长度);开始点是从1开始，
SQL> select ename,substr(ename,2,3) from emp;
ENAME SUBSTR
---------- ------
SMITH MIT
ALLEN LLE
--要求截取雇员姓名的后三个字母
select ename,substr(ename,-3) from emp;
select ename,substr(ename,length(ename)-2) from emp;
SQL> select ename,substr(ename,-3,3) from emp;
ENAME SUBSTR
---------- ------
SMITH ITH
--(8)数字函数
--round(数字|列[，保留的小数的位数] ：四舍五入
SQL> select round(903.53) from dual;
ROUND(903.53)
-------------
904
--取模（取余数）
SQL> select mod(10,3) from dual;
MOD(10,3)
----------
1
SQL> select mod(9,3) from dual;
MOD(9,3)
----------
0
--舍去小数部分
SQL> select trunc(93.6) from dual;
TRUNC(93.6)
-----------
93
--(9)日期函数
--获取每个雇员到今天为止的雇佣天数
select ename,hiredate,TRUNC(sysdate-hiredate) from emp;
--获取本月最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
31-12月-15
--获取下一个指定星期的日期
SQL> select next_day(sysdate,'星期一') from dual;
NEXT_DAY(SYSDA
--------------
07-12月-15
--求出四个月后日期
select add_months(sysdate,4) from dual;
--求出每个雇员到今天为止的雇佣月份
--select ename, trunc(months_between(sysdate,hiredate)) from emp;
（10）转换函数
--to_char()转成字符串
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;
TO_CHAR(SY
----------
2015-12-03
--分别截取年月日
SQL> select to_char(sysdate,'yyyy-mm-dd') as 日期,to_char(sysdate,'yyyy') as 年
2 to_char(sysdate,'mm') as 月,to_char(sysdate,'dd') as 日
3 from dual;
日期 年 月 日
---------- ---- -- --
2015-12-03 2015 12 03
SQL> select to_char(sysdate,'yyyy-mm-dd') 日期,to_char(sysdate,'yyyy') 年
2 to_char(sysdate,'mm') 月,to_char(sysdate,'dd') 日
3 from dual;
日期 年 月 日
---------- ---- -- --
2015-12-03 2015 12 03
--获取字符串的年月日 时分秒 ,单个值有0
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-12-03 11:55:35
--获取字符串的年月日 时分秒，单个值没有0
SQL> select to_char(sysdate,'fmyyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-12-03 11:55:35
--查询的带金钱符号￥
SQL> select to_char(9999999999,'L999,999,999,999') from dual;
TO_CHAR(9999999999,'L999,9
--------------------------
￥9,999,999,999
--将字符串转换为date
SQL> select to_date('1999-12-2','yyyy-mm-dd') from dual;
TO_DATE('1999-
--------------
02-12月-99
--将字符串转为数字
SQL> select to_number('1')+to_number('2') from dual;
TO_NUMBER('1')+TO_NUMBER('2')
-----------------------------
3
