Oracle学习04——SQL增删查改语句以及常用函数

1-列举
数据定义命令：数据定义语言create drop alter
数据操作命令：数据操纵语句select update insert delete

2-SQL语言分类并列举每个分类语句的命令。6大分类…
系统控制语句alter system更改正在运行的数据库实例的特性；会话控制语句 alter session设置会话过程的数据格式，set roll设置用户角色；嵌入的SQL语句open，close，fetch，execute、数据操纵语句select 从表或视图中进行检索数据的查询语句，update insert delete对表中列进行插入删除更改；事务控制语句commit、rollback savepoint保存设置点，以便回滚撤销事务；数据定义语言create drop alter创建删除更改表，视图，索引，存储过程

3-DML和DDL如何定义和区分？
DML(Data Manipulation Language)数据操纵语言：对数据库中的数据进行一些简单操作，如insert、delete、update、select等。
DDL(Data Definition Language)数据定义语言：对数据库中的某些对象(如表、视图、索引等等)进行管理，如Create、Alter和Drop。
区别：DML操作是可以手动控制事务的开启（需要commit来提交）、提交和回滚的，DDL操作是隐性提交的，不能回滚。

4-select、from、where、group by、having、order by分别代表什么意思？以及用法？
select用于指定的条件在数据库中查询结果
from指定要查询的数据表
where用于定义搜索的指定条件
group by函数可以实现对查询结果中每一组数据进行分类统计
having通常与group by子句一起使用，在完成对分组结果统计后，可以使用having子句对分组的结果做进一步的筛选
order by查询结果以升序/降序出现，在SELECT语句中， 可以对检索的结果集进行排序，该子句位于from子句之后

5-select、from、where、group by、having、order by 执行顺序？
执行顺序如下图所示：

基本工作原理：from子句先被执行，通过from子句获得一个虚拟表，然后通过where子句从虚拟表中获得满足条件的记录，生成新的虚拟表。将新虚拟表的记录通过group by子句分组后得到更新的虚拟表，而后having子句在最新的虚拟表中筛选出满足条件的记录组成另外一个虚拟表中，select子句会将指定的列提取出来组成更新的虛拟表，最后orderby子句对齐进行排序得出最终的虚拟表。这个最终的虛拟表被称为查询结果集。

6-总结SQL语言的编写规则 4类
（1）SQL语句不区分大小写，关键字大写小写都可以识别
（2）对象名和列名不区分大小写
（3）字符值和日期值区分大小写，字符值和日期值是存储在表中的数据
（4）SQL语句可以分行显示，以分号结束

7-查询SCOTT模式所拥有的数据表命令？
select table_name from dba_tables where owner = ‘SCOTT’;
SCOTT要大写，小写无法查询结果

8-总结SCOTT模式下几张数据表字段的类型有哪几类？分别代表什么意思？
scott用户模式下的几个表是：salgrade、bonus、emp、dept，字段分别是以下几个：
empno number(4) 员工编码，四位数字组成
ename varchar(10) 员工姓名，十个字符组成
job varchar(9) 职位，九个字符组成
mgr number(4) 员工对应的领导的员工编号
hiredate date 雇佣日期，date是日期
sal number(7,2) 工资，格式为七位数字和五位整数两位小数
com number(7,2) 奖金，销售人员可以领取
deptno number(2) 部门编号，与dept表对应

9-如何在SYSTEM账户下查询SCOTT的部门表呢？写出语句
conn system/Abc123 //连接system用户
select * from scott.dept; //system用户下查询SCOTT的部门表

10-在SCOTT模式下查询用AS别名方式查询结果
设置别名，as也可以直接省略

11-算术运算符的优先级并写出使用语句
在使用select语句时，对于数字数据和日期数据都可以使用算术表达式。包括(+)、减(-)、乘(*)除(/)和括号()，运算顺序还是和数学一样：先乘除后加减。带在表达式中同一优先级的运算符计算次序是从左到右。如果使用了括号，括号中的运算优先。如果有多重括号嵌套，内存括号中的运算优先。如下图

12-去重语句除了distinct还有别的命令么？
select distinct deptno from emp;和select deptno from dept;效果一样，是因为deptno在dept表中本身就是唯一的。
select 字段1,字段2,count( ) from 表名 group by 字段1,字段2 having count( ) > 1
将上面的>号改为=号就可以查询出没有重复的数据。

13-like运算符用法？
like是模式匹配运算符，有两个通配符%和_，%百分号表示任意个数的字符，_下划线表示1个且仅一个字符。
select empno,ename from emp where ename like ‘S%’;查询出来的结果就是员工姓名里以S开头的任意姓名的相关信息。

14-IN 和 BETWEEN的用法区别？分别还要注意什么？
in和between…and都是查询范围所用的关键字，in(‘value1’,‘value2’,‘value3’)表示在给定的value1,value2,value3里的，可以加not来用；between A and B表示在给定的A和B之间（包含A和B）的范围里的，也可以加not来用，需要注意的是A要小于B，否则查询不出结果。
IN关键字包含的最大范围是1000条数据，超过这个范围将无法使用in进行查找，in关键字一般只用于参数个数较少的情况
数据库有 exists 关键字的话，可以用exists来代替 in 关键字。
超过1000个的解决方法：可以拆分sql用 where id in (1, 2, …, 999) or id in (1000, …)这种方法解决。
好的解决方法：
1.用表关联代替IN；2.在where条件中使用子查询，如“select * from b where c in (select d from e …)”
这样的形式。
顺便在这里扩充一下in和exists。
在sql优化中，in与exists的区别就是，当括号里面值少的话，用in效率会比较高，值多的话用exists效率高。
与not in和not exists不同，not exists效率永远会比not in要高。

select ename,empno,job from emp where job in(‘PRESIDENT’,‘MANAGER’,‘ANALYST’); //查询出职位是PRESIDENT、MANAGER、ANALYST的员工姓名和员工编号以及职位

select ename,empno,sal,job from emp where sal BETWEEN 2000 AND 3000;
//查询出2000<=sal<=3000的员工姓名和员工编号,工资以及职位

15-GROUP BY 5种 常用聚合函数并分别举例说明效果…用as取别名解释
常用的5种聚合函数如下表所示：

select job,avg(sal),sum(sal),sum(sal),max(sal),count(job) from emp group by job;
//每个职位的平均工资和最低、最高工资，以及每个职位的人数了。

select job as “职位”,count(job) as “岗位人数” from emp group by job;//用as取别名解释

16-HAVING与GROUP BY 组合用法并解释命令作用
select deptno,avg(sal) from emp group by deptno having avg(sal) >2000;表示查找平均工资大于2000的部门编号，having是进一步缩小由group by确定的范围。

17-ORDER BY 排序默认升序还是降序?
order by asc/desc默认是asc，即升序排列，如图所示

*18-内连接和外连接的区别？并用查询结果证明
inner join可以只用join来表示，只包含符合查询条件和连接条件的行，消除了不匹配的行；和outer join返回匹配的行和部分不匹配的行，包括左外连接右外连接和完全外连接
select e.empno,e.ename,d.deptno,d.dname from emp e join dept d on e.deptno = d.deptno;
查询出来符合连接要求的数据，即e.deptno = d.deptno中对应的信息

select e.empno,e.ename,d.deptno,d.dname from emp e right join dept d on e.deptno = d.deptno;
右外连接的查询结果中将右边的部门编号不管符不符合连接要求，都显示出来

select e.empno,e.ename,d.deptno,d.dname from emp e left join dept d on e.deptno = d.deptno;
左外连接的查询结果中不仅包含了满足连接条件的数据行，而且还包含左表员工编号中不满足连接条件的数据行。

select e.empno,e.ename,d.deptno,d.dname from emp e full join dept d on e.deptno = d.deptno;
full join包括了左外连接和右外连接,将结果合并并消除重复的数据

19-INSERT 语句插入一条数据
insert into emp(empno,ename,job)values(9555,‘EAST’,‘SALESMAN’);

20-dual表的作用？
dual是一个虚表，为了保证select语句的完整性，是Oracle为了验证函数功能的查询而给出的一个虚表

21-concat(a1,a2)函数用法？连接字符串还可以用什么？
concat这个函数，将a2的值连接到a1的后面，如果a2是空值则返回a1,如果a1是空值则返回a2。连接字符串还可以使用 || 符号

22-initcap(S)函数用法？
将字符串S的每个开头给大写

23-instr(s1[],s2[],i,j)函数用法？从0开始么？
字符串s2从s1第i个字符开始第j次出现的位置

24-演示以下函数用途: length、lower、upper、ltrim、rtrim、trim、replace、substr、abs、ceil、floor、mod、round、trunc、add_months、last_day、months_between、sysdate、to_char、to_date、to_number、avg、count、max、min、sum。
length(s)：返回字符串s的长度。

lower(s)将字符串变成小写形式、upper(s)将字符串变成大写形式。

ltrim(s1,s2)是去掉字符串s1左边开始的s2字符串，s2为空则表示去掉字符串左边的空格、 rtrim(s1,s2)是去掉字符串s1右边开始的s2字符串，s2为空则表示去掉字符串左边的空格、 trim(s1,s2)是去掉字符串s1两边开始的s2字符串，s2为空则表示去掉字符串两边的空格。

replace(s1,s2,s3)用s3字符串替换s1中包含的s2字符串

substr(s1,a,b)表示从字符串s1中截取第a个位置的b个字符。

abs(n)表示取n的绝对值。

ceil(n)表示返回大于或等于n的最小整数，floor(n)表示返回小于或等于n的最大整数 。
mod(a,b)是区域运算，结果返回a/b的余数，整除则返回0。

round(a,b)返回舍入小数点右边b位的a的值，b的默认值为0,这会返回小数点最接近的整数（四舍五入）。如果b为负数，就舍入到小数点左边相应的位上，b必须是整数。
trunc(a,b)返回舍入小数点右边b位的a的值，b的默认值为0,这会返回小数点最接近的整数（不四舍五入）。如果b为负数，就舍入到小数点左边相应的位上，b必须是整数。

add_months(times,months)函数用于计算在时间X之上机上Y个月后的时间值，要是Y的值为负数的话就是在这个时间点-Y个月。

last_day返回指定日期所在月份的最后一天。
months_between用于计算date1和date2之间有几个月，可以返回负数和0。

sysdate返回当前系统时间。

to_char转换为字符串、to_date转换为日期、 to_number转换为数字。

avg计算平均值、count计数、max计算最大值、min计算最小值、sum求和

25-trunc与round的区别？举例说明
round(a,b)的结果会四舍五入，trunc(a,b)不四舍五入

26-平均工资保留2位小数,SQL代码如何写?
select round(avg(sal),2) from emp;

27-写出insert、update、delete、truncate 的执行sql例子
insert into emp(empno,ename,job) values(9888,‘SMART’,‘SALESMAN’);

update emp set sal=sal*(1+0.2) where job=‘SALESMAN’;

delete from emp where empno=9888;

truncate table emp1;

28-delete和truncate的区别?
delete删除所有记录或者指定范围记录，truncate语句可以很快删除所有记录，不会产生回滚记录，无法回滚
delete之后需要commit，而truncate不需要