SQL编程和高级查询

一、使用SQL操作表

1、SQL语言简介

2、数据库操纵语言

（1）INSERT命令

INSERT INTO grade VALUES (5,'五年级');--插入每一列
INSERT INTO grade VALUES (6,'六年级');
INSERT INTO grade VALUES (7,'七年级');

INSERT INTO student(stuNo,stuName,sex) VALUES (20145451,'大帅哥','男');--插入指定列

（2）UPDATE命令

UPDATE student SET address='中国广东' WHERE stuNo='20145451';--改变单个列的值

UPDATE student SET gradeId=6,phone='110' WHERE stuNo='20145451';--改变多个列的值，逗号隔开

（3）SELECT命令

简单查询数据操作：

SELECT * | DISTINCT column | expression alias --distinct表示不重复，expression表示列与常数组成的表达式，alias表示列的别名
FROM grade
WHERE condition
GROUP BY columns HAVING condition
ORDER BY columns ASC/DESC

CREATE TABLE stuInfo AS SELECT * FROM student;--利用现有的表创建新表，创建学生备份表

SELECT stuName "学  生" FROM student;--使用别名时，如果带有特殊字符（如空格），需要双引号

SELECT 'S:'||stuName "学  生" FROM student;
--|| 连接两个或多个字符串，或者连接字符串与数值

查询语句中运用算数运算符：（+ - * / ），算数表达式由number数据类型的列名、数值常量和连接它们的算数操作符组成。

查询语句中运用比较运算符：（=， !=， >，<， >=，<=， between...and...，in，like ，is null）

查询语句中运用逻辑运算符：and， or， not。

（4）DELETE命令

DELETE FROM student WHERE stuNo='20145451';

DELETE FROM stuInfo;   --删除记录（可选择删除，可通过ROLLBACK回滚撤销删除）
TRUNCATE TABLE stuInfo;--删除记录（快速删除记录并释放空间，不使用事务处理，无法回滚）
DROP TABLE stuInfo;    --删除表（包括表的所有记录和表的结构）

二、使用SQL函数对表进行操作

1、SQL函数

Oracle SQL提供了用于执行特定操作的专用函数。函数接受一个或者多个参数并返回一个值。Oracle值的函数如下：

单行函数>>多行函数>>分析函数

2、单行函数

单行函数也称标量函数，对于从表中查询的每一行，该函数都返回一个值。单行函数可以出现在SELECT子句中，也可以用在WHERE子句中。单行函数大致可以划分为：字符函数、日期函数、数字函数、转换函数、其他函数等。

（1）字符函数：对字符数据类型进行操作，操作结果可能是字符数据类型，也可能是数字类型。

函数	说明
lower（char）	转换为小写
upper（char）	转换为大写
initcap（char）	首字母大写
replace (char，old_char，new_char)	字符串替换
instr（char，substr）	查找子串位置，从1开始
substr（char，begin_index，end_inex）	截取字符串
concat（char1，char2）	连接字符串
ltrim('123456'，'123')	左裁剪 >>456
rtrim（'123456'，'456'）	右裁剪 >>123

（2）数字函数：接受数字类型的数据并返回数字类型结果，可以精确到小数点后38位

（3）日期函数

（4）转换函数

（5）其他函数

3、聚合函数

聚合函数也称分组函数，基于数据库表的多行进行计算，返回一个结果，如对多行记录进行求和、求最大值运算。

sum（）、avg（）仅适用于数值型，而count（）、max（）、min（）适用于任何数据类型。

在包含group by 子句的查询中，select子句中列表的项可以是分组函数，也可以是group by 子句中出现的列或表达式、常数。Oracle为每组行使用分组函数，并为每组返回一个结果。

如果分组后还要按一定的条件对这些组进行筛选，只需要输出满足条件的组，则可以使用having子句来制定筛选条件。该子句位于group by 子句后面。

where子句和having子句的区别在于作用对象不同。where子句作用于基本表或者视图，从中选择满足条件的记录。having子句作用于组，从中选择满足条件的组。

4、分析函数

Oracle从8.1.6开始提供分析函数。分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数，区别在于每个组返回多个行，聚合函数每组返回一行。

聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。

分析函数是对一个查询结果中的每个分组进行运算，每个分组对应的结果可以有多个。

三、查询

1、查询

2、子查询

（1）子查询概念

外部查询为父查询，内部查询为子查询。

（2）子查询实例

使用IN关键字的子查询

通过IN（或NOT IN）引入的子查询结果是一列零值或更多值。子查询返回结果之后，外部查询将利用这些结果。

使用比较运算符的子查询

当用户能确切知道内层查询返回的是单值时，可以用（=，!=，<>，>，>=，<，<=，!>，!<）等比较运算符。

使用ANY，ALL的子查询

子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL修饰符，具体语义如下表：

其中，

=ANY 等价于IN，

<>ALL等价于NOT IN，

>ANY 等价于>MAX，

<ANY 等价于<MAX。

SELECT * FROM emp WHERE sal>ALL (SELECT sal FROM emp WHERE deptNo=20)AND deptNO<>20;--比20部门所有人工资都高的其他部门职员信息

使用EXISTS或NOT EXISTS的子查询

（3）子查询分类

3、连接

若一个查询同时涉及到两个或两个以上表，则成为连接查询。连接查询为关系型数据库的最主要查询，包括内连接，外连接，自连接，交叉连接查询等。

（1）内连接

内连接分为等值连接，不等值连接，自然连接。

等值连接：关联条件的运算符是用等号连接的。

SELECT ename,dname FROM emp e
INNER JOIN dept d
ON e.deptNo=d.deptNo;

不等值连接：在两个表中没有列的值直接对应相等，连接条件是除等号之外的其他操作符

SELECT e.ename,e.job,e.sal,s.grade FROM emp e
INNER JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

自然连接：在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将它们连接起来，并返回所有符合条件的结果。

SELECT * FROM emp NATURAL JOIN dept;

关于自然连接，我们并没有指定连接的条件，实际上Oracle自作主张的将emp表中的deptNo字段和dept表中的deptNo字段进行了连接。

（2）外连接

左外连接是指在关联过程中，已左边表为准，即使右边表没有满足连接条件的对应记录，左边也会出现在查询结果中，右边以空值出现，而右外连接则是以右边为准。

（3）自连接

自连接也叫自身连接。自连接是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当做另一个表来对待，从而能够得到一些特殊的数据，即自连接的本意就是将一张表看作多张表来做连接。

SELECT e1.ename 雇员,e2.ename 经理
FROM emp e1 INNER JOIN emp e2
ON e1.mgr=e2.empNo;

（4）交叉连接

交叉连接返回到结果集合中的数据行数等于第一张表中符合程序条件的数据行数乘以第二张表中符合条件的数据行数。交叉连接也叫做笛卡儿积。

4、集合查询

集合查询包含UNION（并集），INTERSECT（交集），MINUS（差集）。

5、分页查询

（1）ROWNUM

ROWID与ROWNUM虽都被称为伪列，但它们的存在方式是不一样的，ROWID可以说是物理存在的，表示记录在表空间中的唯一位置id，在数据库中唯一。

对于一个查询返回的每一行，ROWNUM伪列返回一个数值代表行的次序。返回的第一行的ROWNUM值为1，第二行的ROWNUM的值为2，依次类推。通过使用ROWNUM伪列，用户可以限制查询返回的行数。如下表：

（2）ROWNUM和排序

SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC ) WHERE ROWNUM<6 ;--工资排序1~5条记录SELECT e2.* FROM (SELECT e1.*,rownum rn FROM (SELECT * FROM emp e1 ORDER BY sal DESC )e1)e2  WHERE rn<6;--工资排序1~5条记录SELECT e2.* FROM (SELECT e1.*,rownum rn FROM (SELECT * FROM emp e1 ORDER BY sal DESC )e1)e2  WHERE rn BETWEEN 6 AND 10 ;工资排序6~9条记录