4、高级查询和函数

it2022-05-09 31

一、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>sqlplusEnter user-name: systemEnter password:accpConnected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith 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 empwhere comm is not null;

select empno,ename,job,sal,comm from empwhere 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 empwhere empno=7369 or empno=7566 or empno=7788；

select empno,ename,job,sal,comm from empwhere empno in(7369 ,7566 ,7788 );

--7.模糊查询like--7.1查询员工姓名中以字母A开头的全部信息select empno,ename,job,sal,comm from empwhere ename like 'A%';

--7.2查询员工姓名中第二个字母为A的全部信息select empno,ename,job,sal,comm from empwhere ename like '_A%';

--7.3查询员工姓名中包含字母为A的全部信息select empno,ename,job,sal,comm from empwhere ename like '%A%';

--7.4查询员工姓名中不包含字母为A的全部信息select empno,ename,job,sal,comm from empwhere ename not like '%A%';

--8.数据排序：默认升序asc，降序desc--8.1 查询所有员工信息，要求工资升序排列select empno,ename,job,sal,comm from emporder by sal;

select empno,ename,job,sal,comm from emporder by sal asc;

--8.2 查询所有员工信息，要求工资降序排列select empno,ename,job,sal,comm from emporder by sal desc;

--8.2 查询所有员工信息，要求工资sal降序排列，雇佣日期hiredate升序select empno,ename,job,sal,comm from emporder 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: SMITHold 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: smithold 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----------SmithAllenWardJones

-(5)查询每个员工姓名的长度SQL> select ename,length(ename) from emp

ENAME LENGTH(ENAME)---------- -------------SMITH 5ALLEN 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 ITHALLEN LEN

----substr(字段,开始点，长度);开始点是从1开始，SQL> select ename,substr(ename,2,3) from emp;

ENAME SUBSTR---------- ------SMITH MITALLEN 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--获取字符串的年月日时分秒 ,单个值有0SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY-------------------2015-12-03 11:55:35

--获取字符串的年月日时分秒，单个值没有0SQL> 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

--将字符串转换为dateSQL> 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

转载于:https://www.cnblogs.com/holly8/p/5668452.html

专利

最新回复(0)