第四章 SQL语言基础

1、SQL语言指的是什么？其特点是什么？分为几类？分别是？编写规则又是什么？

答：SQL语言全称是结构化查询语言，英文译作Structured Query Language,它是一种在关系型数据库中定义和操作数据的标准语言。其特点是：集合性、统一性和易于移植性。

分类：数据查询语言（DQL）、数据操纵语言（DML）、事务控制语言（TCL）、数据定义语言（DDL）、数据控制语言（DCL）

编写规则：SQL关键字不区分大小写，既可以使用大写格式，也可以使用小写格式，或者大小写格式混用。

2、什么叫用户模式？什么叫模式对象？

答：模式是一个数据库对象的集合。模式为一个数据库用户所有，并且具有与该用户相同的名称，比如，SYSTEM模式、SCOTT模式等。在Oralce中，为了便于管理用户所创建的数据库对象（比如数据表、索引、视图等），引入了模式的概念，这样某个用户所创建的数据库对象就都属于该用户模式。

模式对象是由用户创建的逻辑结构，用以存储或引用数据。

3、什么是检索语句，并举例解释

答：检索数据是通过select语句实现的，该语句由多个子句组成，通过这些语句可以完成筛选、投影和连接等各种数据操作。

a)简单查询：

select e.empno as "员工编号",e.enameas "员工名字",e.salas "员工工资",(e.sal*1.2)as "员工工资*1.2"from emp e where e.empno=7369;

-----该语句包括检索指定的列，含有表达式（加+、减—、乘 *、除 /），为列指定别名（注意要使用双引号，单引号会报错）以及筛选查询

selectdistinct jobfrom emp;          ------显示不重复记录

b)筛选查询

select * from emp ewhere e.sal>1000orderby sal;

aa)筛选条件：A=B; A!B or A>B; A<B; A>=B; A<=B;

通配符下划线“_”，它代表任意一个字符；

例如：“_MRKJ”表示5个字符长度且后面4个字符是MRKJ的字符串。

百分号“%”，它代表任意数量的字符

例如：“K%”表示以字母K开头的任意长度的字符串；“%K%”表示包含字母K的任意长度的字符串；

select * from emp ewhere e.job in ('CLERK','SALESMAN','MANAGER')orderby e.job;

bb) IN 目标值的项目之间必须使用逗号分隔，并且括在括号中；

     NOTIN表示查询指定的值不再某一组目标值中。

    BETWEEN:返回某一个数值是否位于两个给定的值中间，可以使用范围条件进行检索。

     ISNULL:空值从技术上来说是未知的，不确定的值，但是空值和空字符串不同，因为空值是不存在的值，而空字符串是长度为0的值。

     BETWEEN….AND:逻辑筛选

c)分组筛选

select e.deptno, e.job,avg(e.sal) from scott.emp e groupby e.deptno,e.joborderby e.deptno;

--------先根据部门编号进行分组，然后再根据职位进行分组，算平均工资

select e.deptno as "部门编号",avg(e.sal) as "平均工资"from emp e groupby e.deptnohavingavg(e.sal)>2000orderby e.deptno;   -------同上原理

d) 排序查询

 关键字ASC表示按升序进行排序，这个也是默认的排序方式，DESC表示按降序进行排序。

e) 多表关联查询

表别名：使用表明来限制列

select * from emp e, dept dwhere e.deptno = d.deptnoand e.job ='MANAGER'

内连接：内连接是一种常见的多表关联查询方式，一般使用关键字INNER JOIN来实现。其中，INNER 关键字可以省略，当只有使用JOIN关键字时，语句表示内连接。  

select e.empno, e.ename, e.job,e.sal,d.locfrom emp e, dept dwhere e.deptno =d.deptno;

总结：整体感觉内连接和where很类似。返回的结果集中只包含符合查询条件和连接条件的行。

insertintoemp(empno,ename,job)values(9527,'EAST','SALESMAN');

insertintodept(deptno,dname,loc)values(90,'CFO','MOON');

COMMIT;

select e.empno,e.ename, e.job,e.sal, d.locfrom emp e LEFTjoin dept don e.deptno= d.deptno;

select e.empno,e.ename, e.job,e.sal, d.locfrom emp e RIGHTjoin dept don e.deptno= d.deptno;

select e.empno,e.ename, e.job,e.sal, d.locfrom emp e FULLjoin dept don e.deptno= d.deptno;

总结：与内连接不同，外连接不只列出连接条件相匹配的行，同时也列出左表（左连接）、右表（右连接）、两个表（完全外连接）中所有符合搜索条件的数据行。

类似于数学中的并集和交集：内连接（A交B）,左连接（A和（A交B）），右连接（B和（A交B）））、全连接（A和B）

f) 自然连接            -------很少用，略过！自然连接要求表之间必须有相同的列名称

对比下面两个语句就明白了：

select e.empno,e.ename, e.job,e.sal,d.locfrom emp e join dept d one.sal>2000;

select e.empno,e.ename, e.job,e.sal,d.locfrom emp e naturaljoin dept dwheree.sal>2000;

g) 自连接（字节和自己连接）

selecte2.ename,e1.ename from emp e1,emp e2 where e1.mgr = e2.empno order by e1.mgr;

4、Oralce数据库的常用系统函数有哪些？如何使用？

答：字符类函数：

1）  ASCII(c)函数和CHR(i)函数       -----返回一个字符的ASCII码

SELECTASCII('Z')FROMDUAL;     SELECTCHR(90)FROM DUAL;

2）  CONCAT（s1,s2）函数     -------连接字符串S2在S1的后面

SELECTCONCAT('Hello ','World')from dual;

3）  INITCAP（s）函数    -------该函数将字符串S的每个单词的第一个字母大写，其余小写，单词之间用空格、控制字符、标点符号来区分

selectinitcap('it is abad world,at the same time,it is a good word')from dual;

4）  INSTR（s1,s2[,i][,j]）函数       ---返回字符串S2在S1第j次出现的位置，搜索词从S1的第i个字母开始。

比较下面这两个语句，查看其区别，后面是返回值

selectinstr('it is abad world,at the same world,it is a good world','world',2,2)from dual;   --->31

selectinstr('it is abad world,at the same world,it is a good world','world',25,2)from dual;  --->50

5）  LENGTH（s）函数        -------返回字符串S的长度

6）  LOWER（s）函数和UPPER函数（s）    -------返回字符串S的小写形式和大写形式

7）  LTRIM（s1,s2）函数、RTRIM（s1,s2）函数和TRIM（s1,s2）函数

-------删除字符串S1左边的字符串S2, 删除字符串S1右边的字符串S2，删除字符串S1左右边的字符串S2，如果不指定字符串，则表示去除相应方位的空格。

8）  REPLACE（s1,s2[,s3]）函数     -------将S3字符串代替S1字符串中出现的所有S2字符串；

selectREPLACE('it is abad world,at the same world,it is a good world','world','MOON')from dual;

9）  SUBSTR（s,i,[j]）函数      -----从字符串S的i位置开始截取长度为j的字符串。

   selectsubstr('1234567890',7,2)from dual;

数字类函数：

a)        CEIL（n）函数                      -------返回大于或者等于n的最小整数；

b)        ROUND(n1,n2)函数              -------返回舍入小数点右边n2位的n1的值

c)         POWER(n1,n2)函数              -------n1的n2次方

selectceil(7.8)from dual;

selectround(3.1415926,2)from dual;

selectpower(2,3)from dual;

日期和时间类函数：

select sysdate fromdual;

selectadd_months(sysdate,2) from dual;

转化类函数

selectsysdate as "转化前格式",to_char(sysdate,'YYYY-MM-DD')AS "转化后格式"from dual;

selectto_number('18f','xxx') from dual;

5、子查询是什么意思？并举例说明。

答：子查询是在SQL语句内的另外一条SELECT语句，也被称为内查询或是内SELECT语句。在SELECT、INSERT、UPDATE或DELETE命令中允许是一个表达式的地方都可以包含子查询，子查询甚至可以包含在另外一个子查询中。

简单子查询：

select * from emp ewheree.deptno=(select d.deptnofrom dept dwhere d.loc='NEWYORK');

单行子查询：

select e.empno, e.ename,e.sal,e.deptno fromemp e where e.sal>(select avg(sal) from emp) and e.sal<(select max(sal)from emp);

多行子查询：

多行子查询是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须使用多行运算符（IN、ANY、ALL）

IN：外查询会尝试与子查询中的任何一个结果进行匹配，如果有一个匹配成功，则返回当前检索的记录。

select * from emp e where e.deptno in(select d.deptno from dept d where d.loc='NEW YORK' or d.loc='CHICAGO');

ANY:any必须和单行比较运算符进行结合使用，并且返回行只要匹配子查询的任何一个结果即可；

select e.empno, e.ename,e.sal,e.deptno fromemp e where e.sal>any(select e1.sal from emp e1 where e1.deptno=10) ande.deptno<>10;

select e.empno, e.ename, e.sal, e.deptno fromemp e where e.sal > all (select e1.sal from emp e1 where e1.deptno = 20) ande.deptno <> 20 order by e.sal;

关联子查询：

在当行子查询和多行子查询中，内查询和外查询是分开执行的，也就是说内查询的执行与外查询的执行是没有关系的，外查询仅仅是使用内查询的最终结果。在一些特殊需求的子查询中，内查询的执行需要借助于外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询是相互关联的，这种子查询就被称为关联子查询。

select * from emp e1where e1.sal> (selectavg(e2.sal)from emp e2 wheree2.job=e1.job) orderby e1.sal;

这个语句是下面三个语句的集合

select * from emp e1where e1.sal> (selectavg(e2.sal)from emp e2 where e2.job ='CLERK')and e1.job ='CLERK'orderby e1.job;

select * from emp e1where e1.sal> (selectavg(e2.sal)from emp e2 where e2.job ='SALESMAN')and e1.job ='SALESMAN'orderby e1.job;

select * from emp e1where e1.sal> (selectavg(e2.sal)from emp e2 where e2.job ='MANAGER')and e1.job ='MANAGER'orderby e1.job;

6、操作数据库的操作有哪些?请举例说明。

答：

a) 插入语句：

单条插入：insertintoemp(empno,ename,job)values(9527,'EAST','SALESMAN');

批量插入：create tableemp_temp as (select * from emp e where e.sal>2000);

b) 更改语句

update emp_temp et set et.sal=et.sal*1.2where et.job='ANALYST';

c) 删除语句

delete：该语句用于删除数据库中的虽有记录和指定范围的记录（使用where语句）

truncate：该语句删除数据，不会产生回滚日志

insertintoemp_temp(empno,ename,job)values(110,'XXX','LLLLLL');   commit;

delete emp_tempe where e.empno=110;    commit;

1、  事务处理是什么意思？怎么理解？

答：事务是由一系列语句构成的逻辑工作单元。事务和存储过程等批处理有一定程度的相似之处，通常都是为了完成一定业务逻辑而将一条或者多条语句“封装”起来，使他们与其他语句之间出现一个逻辑上的边界。

其特点为：被统称为ACID（原子性、一致性、隔离性和持久性）

（1）原子性（Atomic）：事务是一个整体的工作单元，事务对数据库所做的操作要么全部执行，要么全部取消。如果某条语句执行失败，则所有语句全部回滚。

（2）一致性（ConDemoltent）：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。如果事务成功，则所有数据将变为一个新的状态；如果事务失败，则所有数据将处于开始之前的状态。

（3）隔离性（Isolated）：由事务所作的修改必须与其他事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

（4）持久性（Durability）：当事务提交后，对数据库所做的修改就会永久保存下来。

Commit：提交事务；

Rollback：回滚事务

insertintoemp_temp(empno,ename,job)values(110,'XXX','LLLLLL');------插入第一行

savepoint sp;                    -------使用保存点

insertintoemp_temp(empno,ename,job)values(220,'yyy','pppppp'); ------插入第二行

rollbacktosavepoint sp;          -------回滚保存点

commit;                                         -------提交事务

select * fromemp_temp;           -------新增一条数据而不是两条

参考文献:

1、《Oracle从入门到精通》 明日科技著。

2、源码资料：http://download.csdn.net/download/m0_37824308/9959545

3、PPT资料：http://download.csdn.net/download/m0_37824308/9959539