数据查询语言

1、数据查询语言相关的操作：1.sqlplus访问数据库命令，和数据库建立连接的命令： sqlplus 用户名/密码2.显示当前用户的用户名： show user3.改变身份： conn（ect） 用户名/密码特殊：sys用户，要指明以什么身份登录AS SYSDBA/SYSOPR。说明：一旦使用了超级管理员登录的话，可以通过命令手工的控制数据库实例的打开和关闭：shutdown immediate;一旦关闭后，用户无法直接连接sqlplusw命令，此时可以：A.先使用“/nolog”登录；B.使用管理员登录：conn sys/SMITHe_on_install as sysdba;C.然后启动数据

2、库：startup,就正常连接了。4.sqlpus命令窗口也可以调用本机的操作系统命令，使用“host”作为前缀。例如：host copy c:test1.sql c:test2.txt;一 单表查询语句 (1).查询系统表：（查询本用户下所拥有的所有表的表明）select table_name from user_tables;scott用户下有四张表，使用：select * from tab; 查询所有的数据表的名称。(2).查看表的结构：desc 表明1查询某张表的所有数据：select distinct /可无 *col_name1,col_name2, from tb_name;一般

3、不要写*，有多少就具体写出来。2.对指定的列的所有值进行算术运算：select col_name 运算符 数字 from tb_name;例：select id,last_name,(salary+100)*12 from s_emp;3.给查询的列取别名： select old_colunm as new_column_name from tb_name; 例：select id,last_name,salary*12 annual from s_emp;4.变换查询显示：（尽量别使用中文）select job 工作,sal 工资 from scott.emp;5.使用|可以使得多列的值或者

4、列和特殊的字符串合并到一个列进行显示： select col_name|spe_char|col_name from tb_name;例：select id,first_name| |last_name|,|title name from s_emp;6.对null值进行替换运算（nvl是通用函数的一种，见单行函数）： select nvl(col_name,SMITHe_value) from tb_name; 例：select id,last_name,nvl(commission_pct,0) commission_pct from s_emp;7.用distinct关键字，可以将显示中

5、重复的记录只显示一条：select distinct col_name,col_name from tb_name;例：select distinct title,dept_idfrom s_emp; 注意1：distinct 关键字只能放在select关键词后面。 如：select id,distinct title from s_emp;错误！注意2：distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录。1 2 1 21 3 结果：1 32 4 2 42 47，sqlplus命令 a:在当前操作的命令行追加内容。 c:在当前操作的命令行修改内

6、容，只能修改一次，修改第一个。 clear buffer：清空当前缓存del:删除当前操作行。del line_num,删除指定行。i:当前操作命令行的下一行插入内容。l:查看缓存命令 l line_num:查看指定的命令行。n text:替换第n行的整行内容。！：后面接终端命令。 ！clear：清屏8.sqlplus设置：set pause on回车响应，分屏显示，只在本会话中有效。 ctrl+c 停止显示。set pause off 关闭分屏显示。set pause “please enter key:”设置分屏显示的提示信息。set head off 提头输出关闭。set feed of

7、f 结尾输出关闭。set echo off 回写关闭。 set linesize num; 设置每行显示的字节数。 set pagesize 30; 设置每页显示的行数。9.文件命令：没有“；”号save file_name:将缓存命令（即就上一条）保存到file_name中。get file_name:将文件中的内容提取到sqlplus。start file_name:执行文件中的sqlp命令。filename:同上。edit file_name:使用终端设置好的vi编辑器编辑文件。spool file_name:将接下来的sql语句以及sql的运行结果保存到文件中。直到遇到 spool o

8、ff 关闭spool功能。例：sqll result1exit:退出。10.column命令： column命令 列格式的定义 ·column 目标列名 /查看这个列是否定义了格式 ·column 目标列名 format a. /设置列宽 ·column last_name heading Employee|Name format A15 /设置题头 |是换行符。 ·column salary justify left format $99,999.00 /定义数字显示格式 注意：如果不满足显示的格式，就会把数据显示为“#”。 ·column

9、salary justify left format $00,000.00会出现$00,928.00,用0补。 ·column 列名 clear （清除列格式定义） ·$99,999.00中的“$”: 加$；“L”: 使用本地货币符号； “.”: 意思是小数计数显示； “，”: 表示千分位显示。查看列是否定义了格式：设置列宽：取消列的格式定义：设置题目：column last_name heading Employee|Name format A15。|为换行。设置后用select empno from scott.emp;查看，如果起了个别名查看，select empno

10、 Em from scott.emp;此时看到的是没有格式的原来的内容。注意：这个很像前面的起别名：select empno Em from scott.emp;但是起别名没有引号的效果，也不能换行，仅仅是起了别名。定义数字显示格式：-左对齐（left）设置格式中不够的补0：column salary justify left format $00,000.00二选择操作1. order by排序查询升序：ASC 降序：DESC语法：order by 目标列名（别名）排序顺序（不写则默认升序） 注意：有where时，order by在where后面。其实order by是在语句的最后的。2wh

11、ere子句（条件查询） (1)单条件查询： select job,sal from scott.emp where job=MANAGER;select job,sal from scott.emp where sal<=1600;单条件查询使用的比较运算符表：说明：1. like和not like适合于字符型字段的查询，%代表任意长度的字符串，_下划线代表一个任意的字符。like M%代表M开头的任意长度的字符串，like M_代表M开头的长度为3的字符串。 在开发之中，数据可的模糊查询肯定使用LIKE子句，但是在使用LIKE子句的时候有一个最大的注意点：如果在模糊查询上不设置任何的查

12、询关键字的话（%），则表示查询全部记录。这个特点可以帮助用户节约很多的代码，一定记住！·下面的例子可以实现：N出现在第三个位置的长度不小于5的ename；·下面的可以实现：ename中含有“_”（下划线）的名字。用转义字符和escape 。select deptno,job,ename,LENGTH(ename) from scott.emp where job like '%_%' escape ;2.between and不仅能对数值型操作，还能对日期型操作。是闭区间.3.is (not)null的用法： 4.in（在列表）指定一个范围的操作。 如：查询

13、empno=7369，empno=7566，empno=7799的员工。7799没有就不显示了。注意： 如果现在使用了in操作符，查询的范围之中存在了null，不影响查询；如果现在使用的是NOT IN ，查询的范围之中存在null（表示查询全部数据），则不会有任何的数据返回。 (2)组合条件的查询： select job,sal from scott.emp where job<=MANAGER and sal<=2000; select job,sal from scott.emp where job<=MANAGER or sal<=2000;select job,

14、sal from scott.emp where not job=TOM ; /not job即job<>TOM。3.分组查询：分组查询是指将查询结果按照某列或者某些列相同的值进行分组，然后对改组的数据进行组函数运用。语法： selectfromwheregroup by col_name,col_namehaving order by 顺序不能改变。group by col_name:即将数据按照col_name相同值进行分组组函数常见的5个：avg:求平均数count：求总数max:最大值min:最小值sum:求和1【avg】函数用法：avg(字段名) 求平均值。要求字段为数值

15、型。 select dept_id,avg(salary) dept_salary from s_emp group by dept_id order by dept_salary desc;工作原理：先将相同的id分组，然后对其中的salary分别求和，随后按avg(salary)降序排列。 2【count】函数用法：count(字段名) 或 count(*) 统计总数。 注意： COUNT()函数的主要功能是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有统计记录，COUNT()也会返回数据，只是这个数据是“0”。 如果使用的是其他函数，则可能返回null，但是COUNT()永远

16、都会返回一个具体的数字，这一点以后在开发之中都会使用到。 3【max】函数 用法：max(字段名) 计算数值型字段最大数。4【min】函数 用法：min(字段名) 计算数值型字段最小数。 5【sum】函数 用法：sum(字段名) 计算数值型字段的总和。select job,sal from scott.emp group by sal,job having sal<=2000;select job,sal from scott.emp where sal<=2000 group by job,sal;注意： 1.没有出现在group by子句中和组函数中的列，不能出现在select

17、子句中：如： select dept_id,last_name,max(salary) from s_empgroup by dept_id;（错误）并不能找到工资最大的员工名字。last_name不能存在。 2.当group by子句中出现多列的时候，表示按照从左到右的顺序进行分组，即 按照第一列分组，然后在第一列分号的组里面，按照第二列进行分组，以此类推。 3.如果限制条件中出现了组函数，该条件必须放到having子句中，不能放在where子句中，此时其实where已经管不着了。如：avg(salary)>1000select dept_id ,avg(salary) from s_

18、empgroup by dept_id;having avg(salary)>1000;4.where在执行group by之前的检测每条记录是否符合条件，having是执行group by之后，再次检查分组后的各组是否满足条件，having语句只能配合group by 语句使用，没有group by 时不能使用having，但可以使用where。 二 多表查询：尽可能不要用多表查询，用单表性能很差，因为数据量大。多表查询，又称表联合查询，是相对于单表而言的，指从多个数据表中查询数据，下面主要是从两个表查询数据。多表查询连接方式：1 等值连接2 不等值连接3 外连接4 自连接：5 集合连

19、接注意：一定要指明查的内容是哪张表的，不能直接写ename,deptno等。除非这个内容在一张唯一的表中。1. 无条件多表查询：是将各表的记录以“笛卡尔”积的方式组合起来。例如：scott.emp有14条记录，scott.dept有4条，那么它们的“笛卡尔”积就是4*14=56条记录。即使只查一个字段，也会显示56条记录。2. 等值多表查询：等值多表查询将按照等值的条件查询多个数据表中关联的数据。要求关联的多个数据表的某些字段具有相同的属性，即具有相同的数据类型、宽度和取值，要不没办法比较是否等值。3. 非等值多表查询：where，运用上面的比较运算符表来组合查询条件。 注意：如果表的名称很长

20、，例如：”yinheng_yazhou_zhongguo_shanghai_ren”,所以在进行多表查询的时候往往都会先为表起个别名，然后通过“别名.字段”的方式进行查询。4. 外连接-能显示的很全 当一个表的记录在另外一张表中不存在的时候，我们依旧需要显示，使用外连接需求：查看s_customer中所有客户的id和名字以及该客户所对应的负责员工的id，last_name表之间的连接列 s_customer 表中的sales_rep_id s_emp 表中的id但是发现一张表中多出的内容也不显示了，如果要显示，则使用左、右连接。左、右连接 关于左、右连接指的是查询判断条件的参考方向，例如： s

21、elect * from emp e,dept d where e.deptno=d.deptno; 查询结果少了40这个部门，因为在雇员表之中没有一条记录是属于40部门的，所以现在不会显示40部门的信息，即：现在的查询以emp表为参考，那么如果说现在非要显示40部门，就必须改变这种参考饿方向，就需要使用左、右连接。select * from emp e,dept d where e.deptno（+）=d.deptno; 就会出现40部门。“（+）”就用于左、右连接的更改，这种符号有以下两种使用情况： ·（+）=：放在等号左边，表示右连接； ·=（+）：放在等号右边，表示

22、左连接。意义：右（左）外连接表示右（左）边表（=号右（左）边的表）中的记录在左（右）边表中不存在的时候，右（左）边表的记录依旧显示。不用刻意区分左还是右，只是根据查询结果而定，如果发现有些需要的数据没有显示出来，就使用此符号更改连接方向。这个符号是Oracle数据库独有的，其他数据库不能使用。如果想使用，就看下面的5. 自连接：实质就是一张表当多张表用，即一张表中的某列的值取决于自己的某一列。语法：给同一个表取不同的别名就行。需求：查看每一个员工的经理的名字。 6. 集合连接 union：将上下结果取并集，去除掉重复的记录（重复的只显示一次）。union all:将上下结果全部显示，包括重复的

23、。minus：取差集intersect：取交集 union： select e.last_name,from s_emp e,s_dept dwhere e.dept_id(+)=d.idunionselect e.last_name,from s_emp e,s_dept dwhere e.dept_id=d.id(+) minus： select last_name,salaryfrom s_empwhere rownum<=10minusselect last_name,salaryfrom s_empwhere rownum<5rownum是记录行号

24、。 intersect: select last_name,salaryfrom s_enpwhere rownum<=10intersectselect last_name,salaryfrom s_emp where rownum<57.SQL:1999语法除了以上的表连接操作之外，在SQL语法中，也提供了另外一套用于表连接的操作SQL，格式如下： (1).交叉连接（CROSS JOIN table）:用于产生笛卡尔积。 select * from emp CROSS JOIN dept; 笛卡尔积本身并不是属于无用大的内容，在某些情况下还是需要使用的。(2).自然连接（NAT

25、URAL JOIN）：自动找到匹配的关联字段，消除掉笛卡尔积。 select * from emp NATURAL JOIN dept; 但是，并不是所有的字段都是关键字段，设置关联字段需要通过约束指定。(3).JOINUSING子句：用户自己指定一个消除笛卡尔积的关联字段。 select * from emp JOIN dept USING(deptno);(4).JOINON子句：用户自己指定一个可以消除笛卡尔积的关联条件。 select * from emp JOIN dept ON(emp.deptno=dept.emptno);(5).连接方向的改变：select * from em

26、p RIGHT OUTER JOIN dept ON(emp.deptno=dept.deptno);在Oracle之外的数据库都使用以上的SQL：1999语法操作，所以这个语法必须会一些。再次强调：多表查询的性能肯定不高，而且性能一定要在大数据量的情况下才能够发现。三 用SQL进行嵌套查询在select查询语句里可以嵌入select查询语句，称为嵌套查询。也称为“子查询”，子查询形成的结果又成为父查询的条件。子查询可以嵌套多层，子查询操作的数据表可以使父查询不操作的数据表。子查询中不能有order by分组语句。例如：查看工资大于SMITH员工工资的所有员工的empno和ename。分析步骤

27、：第一步：确定需求第二步：写父句，写子句 父句：select empno,ename,sal from scott.emp where sal>?; 子句：select sal from emp where ename='WARD'第三步：嵌套 select empno,ename,salfrom scott.empwhere sal>(select sal from emp where ename='WARD');1.简单嵌套查询等价于： select sal from scott.emp where ename=WARD，得出sal=1250,

28、select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal>=1250;2.带【in】的嵌套查询查询薪水和WARD相等的员工。3.带【any】的嵌套查询 等价于： select sal from scott.emp where job=MANAGER; 4.带【some】的嵌套查询 等价于： select sal from sal from scott.emp where job=MANAGER； any和some的嵌套查询功能是一样的，any是早期的。5.带【all】的嵌套查询等价于：select sal fr

29、om scott.emp where job=MANAGER; 6.带【exists】的嵌套查询 7.并操作的嵌套查询-union即并集，A或B元素的总和（相同的元素只显示一次）。 8.交操作的嵌套查询-intersect交操作就是集合中交集的概念。属于A且属于B元素的总和。 9.差操作的嵌套查询-minus 就是集合中差集的概念，属于A且不属于B的元素总和。 注意：并、交和差操作的嵌套查询要求属性具有相同的定义，包括类型和取值范围。练习：练习1：查询工资大于smith所在部门平均工资的员工的select sal,enamefrom scott.empwhere sal>(select

30、avg(sal) from scott.emp where deptno=(select deptno from emp where ename='SMITH') group by deptno);练习2：查看部门平均工资大于30号部门平均工资的部门deptno.select deptno,avg(sal)from scott.empgroup by deptnohaving avg(sal)> (select avg(sal) from scott.empwhere deptno=30);练习3：查看部门和名字为SMITH的部门相同或者区域ID为2的部门相同的部门所有员

31、工id和名字父句：select id,last_name,dept_idfrom s_empwhere dept_id = ?or dept_id in ?;子句1：select dept_idfrom s_empwhere last_name = 'SMITH'子句2：select idfrom s_deptwhere region_id = 2;练习：1.查看薪资大于SMITH员工薪资的员工信息2.查看薪资大于SMITH员工薪资或者所在部门在3号区域下 的员工的信息3.查看薪资大于SMITH所在区域平均工资的员工信息4.查看薪资高于SMITH员工经理薪资的员工信息5.查看薪

32、资大于SMITH员工经理的经理所在区域的最低工资的员工的信息>24506.查看客户负责员工中工资大于SMITH员工的工资的员工信息练习:1.查看SMITH员工所在部门其他员工薪资总和2.统计不由11号和12号员工负责的客户的人数3.查看部门平均工资大于SMITH所在部门平均工资的部门信息4.查看员工的id，last_name，salary，部门名字，区域名字，这些员工有如下条件：薪资大于SMITH所在区域的平均工资或者跟SMITH员工不在同个部门1.select ename,empno,sal from scott.emp where sal>(select sal from sc

33、ott.emp where ename='SMITH');2.select ename,empno,sal,deptno from scott.emp where sal>(select sal from scott.emp where ename='SMITH') or deptno=3;3.select ename,sal from scott.emp where sal>(select avg(sal) from scott.emp where deptno=(select deptno from scott.emp where ename=&

34、#39;SMITH') );4.select ename,sal from scott.emp where sal>(select sal from scott.emp where empno=(select mgr from scott.emp where ename='SMITH');5.select sal,ename from scott.emp where sal>(select min(sal) from scott.emp where job=(select job from scott.emp where empno=(select mgr

35、from scott.emp where empno=(select mgr from scott.emp where ename='SMITH') group by job);练习2：1.select sum(sal) from scott.emp where deptno=(select deptno from scott.emp where ename='SMITH') and ename !='SMITH'2.select sum(num) from scott.emp where empno !=11 and empno !=12 3.select deptno,avg(sal) from scott.emp group by deptno having avg(sal)&