第2讲 数据查询

第2讲数据查询SQL：1）DML(data manipulation language)：select, insert, update, delete, merge2）DDL(data definition language): create, alter, drop, rename, truncate3）DCL(data control language): grant, revoke●熟悉EMP、DEPT、SALGRADE结构，讲解数据类型。

（书P10）使用DESC显示表结构●简单查询1）查询emp表的所有数据2）查询emp表中的姓名、雇员号，工资。

3）改进第二小题，增加一列显示行号。

（rownum列）注意：每个表都有一个虚拟列rownum，代表查询结果中记录的行号，实际在数据库中记录是没有行号的。

4）显示emp表的雇员名称、工资及工资与津贴之和（使用计算列）。

练习：查询显示每个雇员的姓名，工资，及全年工资（即工资*12）。

5) 显示雇员工资上浮20%的结果。

6) 使用列别名显示字段名和列别名之间用as或空格隔开，如果列别名含空格、特殊字符等，as 后列别名用“”。

7）使用列别名显示雇员姓名和年薪加8000元奖金8）连接运算符为“||”select ename||job as “name&job” from emp;10）在雇员和职务中间加空格select ename||‟…||job as “name&job” from emp;注意：字符串和日期常量用单引号‘’引起，而列别名用“”，注意分别。

练习：显示的DEPT表的内容，按以下的形式显示:Department ACCOUNTING City is NEW YORKDepartment SALES City is CHICAGO练习：按以下形式显示emp表的相关信息SMITH Job Date：17-12月-80，Manager No：7902ALLEN Job Date：20-2月-81，Manager No：769812）消除重复行Select job from emp; 结果含重复行Select distinct job from emp; 消除重复练习：显示emp表中不同的部门号。

对结果进行排序1）显示雇员姓名，工资和雇用日期，并按工资升序排序2）显示雇员姓名，工资和雇用日期，并按工资降序排序3）显示雇员姓名，工资和雇用日期，按雇用日期降序排序4）显示雇员姓名，工资和雇用日期，先按雇用日期升序排序，再按工资降序排序5）查询雇员信息，先按部门号由大到小排序，再按入职时间先后排序6）查询每个员工的年薪，按年薪进行降序排序（使用别名排序）7）将部门名称按字母顺序显示2.3条件查询1. 显示职务是CLERK的员工的姓名、职务和工资。

2．查询入职日期在’01-APR-82’后的员工信息提示：字符或日期型常量需用’’引起。

默认日期格式为dd-mm月-yy，总长度是9位。

如04-2月-053．查询不在部门20的员工工资情况，并按工资升序排序。

6、复合条件查询，使用逻辑运算符：not,and,or显示工资在2500与3000之间的雇员姓名、工资7．显示职务是SALESMAN或CLERK的员工。

8、显示雇员姓名、工资、职位、部门号，只显示在10和20号部门的且工资超过2000的员工.9．显示职务是CLERK而且工资800以上或者职务是MANAGER而且工资超过2500的员工。

10、between… and显示工资在1000~2000之间的雇员信息11．显示入职日期在1-1月-80到31-12月-81之间的雇员。

12、in的用法1）显示工资是800，1600和3000的雇员2）显示职务是CLERK，SALESMAN和PRESIDENT的雇员2）显示不在10，20部门的雇员信息13、like用法%代表任意个字符，_代表一个字符1）显示姓名中以’S’开头的雇员信息。

2）显示姓名中含’S’的雇员。

3）显示姓名中不含’S’的雇员。

4）查询姓名中第三个字母是‘R’的雇员信息。

练习:查询姓名中包含’A’且在部门20或30的雇员信息5）显示81年入职的雇员select * from emp where hiredate like '%81%'14、NULL的用法显示没有提成的雇员信息，包括姓名，工资，提成，并按工资进行升序排序。

不能使用comm=null（有提成的呢？is not null）练习：显示有经理的雇员信息。

小结：1、所有的字符型与日期型数据必须用’’引起2、在like条件中，%代表任意个字符，_代表一个字符。

3、适用条件：对于数值型数据，表示范围可以用BETWEEN… AND的特殊运算表示方法。

使用IN运算符，可以显示满足特定值的集合的结果。

使用LIKE 操作符完成按通配符查找字符串，即查找条件中除了可以包含字符或数字之外，也可以包含通配符，判空IS NULL。

判断非空用IS NOT NULL。

作业：作业12.4 函数dual表：是一个所有用户都可以使用的一行一列的虚拟表。

1、常用数值型函数的用法P31（侧重mod, floor, round, trunc）练习：P34 练习12、常用字符型函数的用法。

P34（lower,concat,substr,length,rpad）例：select concat(…Shenzhen‟,… Polytechnic‟) from dual;select lpad(…SQL‟,8,‟*‟) from dual; --在字符串左侧填充字符select substr(…Shenzhen‟,2,3) from dual;select instr(…Shenzhen‟,‟e‟,1,2) from dual;select substr(…Shenzhen‟,‟en‟,1,1) from dual;例：查询81年入职的员工信息。

（substr()）select * from emp where substr(hiredate,8,2)=‟81‟例：显示名字中包含m（不管大小写）的员工信息。

Select * from emp where lower(ename) like …%m%‟或Select * from emp where instr(ename,‟m‟,1,1)>0 or instr(ename,‟M‟,1,1)>0 练习：显示姓名长度为5的雇员，并将姓名转换成以大写字母开头显示Select empno,initcap(ename) from emp where length(ename)=5例：显示雇员名称和职务列表，中间用.....隔开。

SELECT concat (rpad (ename,10,'.'),job) as 职务列表FROM emp;练习：书P36 练习13、常用日期型函数的用法，P36（侧重当前日期及日期型数据的加减算法）取当前日期：sysdateSELECT sysdate FROM dual;months_between函数：返回两个日期相差的月份：select months_between('8-6月-04','8-6月-02') from dual;last_day函数：返回指定日期所在月的最后一天SELECT last_day('08-2月-03') FROM dual;next_day：返回指定日期的下周日期select next_day(sysdate,‟星期二‟) from dual;日期round函数：对YEAR四舍五入，看月份，六月以前（含六月），要舍去，退回到本年的1月1日，7月及以后，则进到下一年的1月1日；MONTH四舍五入，看天，15日及以前，退回到本月的第一天，否则进到下月的第一天；DAY四舍五入，看星期，星期三及以前，退回到本周第一天，否则进到下周的第一天。

日期trunc函数：日期计算：例：求当前日期再过1000日后的日期。

Select sysdate+1000 as “New Date” from dual;例：求每个雇员工作的天数，星期数。

Select ename,round(sysdate-hiredate) as DAYS,Round((sysdate-hiredate)/7) as WEEKS from emp;注意：日期型包含时间在内，相减后有小数，所以使用四舍五入round。

6、转换函数（1）To_char函数：转换成字符串的函数日期转换成字符（P39日期转换格式符）例：将日期转换成带星期的字符串显示SELECT to_char(sysdate,'YYYY-MM-DD DY') FROM dual;例：SELECT to_char(sysdate,'YY-MM-DD DY AM') FROM dual;例：将雇用日期转换成字符串格式显示select ename, to_char(hiredate, 'YYYY-MONTH-DD') HIREDATE FROM emp;例：将日期显示转换成中文的年月日SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;数字转换成字符(p41数值转换格式符)例：select to_char(345.89,‟$999.99‟) from dual;例：select to_char(345.89,‟L999.99‟) from dual;例：将工资转换成字符串格式，保留两位小数，并前面添加本地货币（人民币）符号显示SELECT ename,to_char(sal,'L99,999.99') SALARY FROM emp;（2）to_date函数：把字符串转换成日期的函数select to_date('2002-12-6','yyyy-mm-dd') from dual;练习：显示从本年1月1日开始到现在经过的天数。

select round(sysdate-to_date('2009-1-1','yyyy-mm-dd')) from dual;练习：P41 练习1（3）to_number：把字符串转换成数字的函数select to_number(…1234.5‟) from dual;其它常用函数：7、nvl函数nvl（参数1，参数2）：把NULL转换为实际值。