oracle-sql基本操作1

1.
数据查询（SELECT）语句可以从一个或多个表、视图、快照中检索数据
2.
完整的SELECT语句由6个子句构成，分别为：SELECT子句、FROM子句、WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句。其中
SELECT子句：指定要获取表中哪些列数据
WHERE子句：指定要获取哪些行数据
HAVING子句：在对表中数据进行分组统计时，指定分组统计条件
3.
日期格式的默认格式为“DD-MON-YY”
故这种写法是正确的：birthdate > '06-7月-1995' （不需要进行数据类型转换）
4.
SELECT子句中指定列或表达式的别名时，别名要用""（英文双引号）
SELECT子句中，列或表达式可以使用字符串连接，以显示更有意义的查询结果。使用“||”实现字符串连接，连接的字符串要用''（英文单引号）：
select name || '生日是：' || birthdate as "学生生日清单" from students;

SELECT子句中，列或表达式可以使用含有加、减、乘、除运算符的算术表达式：
select name as "姓名", bonus+wage as "月总收入（奖金+工资）" from teachers;

5.空值处理
空值（NULL）用来在数据库中表示未知或未确定的值，没有数据类型。
任何数据类型的列，只要没有使用非空（NOT NULL）或主键（PRIMARY KEY）完整性约束，都可能出现空值。
注意：当空值参与运算时，如果出现在算术表达式中，其运算结果为NULL；如果出现在字符串连接表达式中，Oracle将NULL作为空串（零个字符）处理

oracle提供了3个函数对NULL进行处理：
NVL(expr1,expr2)：expr1为NULL，则返回expr2；否则返回expr1（expr1和expr2的数据类型必须一致）
select name as "姓名", NVL(bonus,0)+wage as "月总收入" from teachers;

 

NVL2(expr1,expr2,expr3)：expr1为NULL，则返回expr3；否则返回expr2（参数的数据类型必须一致；该函数从oracle9i开始增加）
select name as "姓名", NVL2(bonus, bonus+wage, wage) as "月总收入" from teachers;

 

COALESCE(expr1 [, expr2]...)：返回参数列表中的第一个非空值；如果所有的表达式都是空值，最终返回一个空值
select name as "姓名", COALESCE(bonus+wage, wage) as "月总收入" from teachers;

6.
where子句中使用的比较条件：
算术比较：<>、!= 都表示不等于
包含：IN、NOT IN
范围：BETWEEN AND、NOT BETWEEN AND
匹配：LIKE、NOT LIKE  （%表示0个或多个字符；_表示单个字符）
NULL测试：IS NULL、IS NOT NULL   （注意：IS NULL 不能写成 =NULL）
逻辑：
（逻辑运算符的优先级低于比较运算符）
7.
当按ASC排序时，如果指定的排序列包含有NULL值的记录行，那么这些记录行会排列在最后面；DESC则相反
8.
ORDER BY子句中的表达式可以指定一个以上列或表达式，即多列排序，其中每一列可以指定不同的排序规则
GROUP BY子句中的表达式可以指定一个以上列或表达式，即多列分组*
HAVING必须与GROUP BY一起使用，而GROUP BY可以单独使用
9.
列函数，GROUP BY子句，HAVING子句共同完成分组查询
没有使用GROUP BY子句时，原表所有行被分为一个组
COUNT(*)：表中行的总数
COUNT(column)：列不为NULL的行数
COUNT(distinct column)：column指定列中相异值的数量

10.

在HAVING子句中使用子查询时，该子查询的结果作为主查询分组条件
例子：在teachers表中，查询部门平均工资高于最低部门平均工资的部门和平均工资
select department_id, AVG(wage) as 平均工资 from teachers
group by department_id
having AVG(wage) > (select MIN(AVG(wage)) from teachers group by department_id);

11.

多行比较符any，some
where score > any (30,90)    等效于   where score > some (30,90)   大于最小即可（反之，小于最大即可）
12.
查询与张三专业相同，生日相同的同学（也可以这样写：应该是记录结构吧）
select * from students where (specialty,birthdate) = (select specialty,birthdate from students where name='张三');

13.

相关子查询：子查询引用了外部查询中包含的一列或多列
查询在teachers表中工资高于所在部门平均工资的教师
select * from teachers t1
where wage > (select AVG(wage) from teachers t2 where t2.department_id = t1.department_id);
14.
集合操作有并、交、差，分别为
UNION：并集中自动去掉重复行
UNION ALL：并集中保留重复行
INTERSECT：交集，结果以第一列进行排序
MINUS：差集，结果以第一列进行排序
（这些集合操作符具有相同的优先级）
集合操作符只能有一个ORDER BY子句，并且必须将它放在语句的末尾，用于对集合操作的结果集进行排序
集合操作中，文本列、数字列、日期列数据类型相同，但宽度不同时，oracle会进行自动类型转换；反之，可以通过数据类型转换函数进行显示转换

15.

oracle9i以前版本采用的非标准SQL外连接查询语句格式同内连接查询语句格式，
只是在左或右连接查询时，需要在where子句的表达式的适当位置给出(+)操作符，指明外连接类型，并且不能实现完全外连接查询
（标准SQL在SQL-92时实现了外连接查询，oracle9i及以后版本支持这两种格式，尽量使用标准SQL格式喽，呵呵）
例如：左（外）连接
select * from teacher t, department d where t.department_id = d.department_id(+);
select * from teacher t LEFT OUTER JOIN department d ON t.department_id = d.department_id;   （右连接类似喽）
全外连接
select * from teacher t FULL OUTER JOIN department d ON t.department_id = d.department_id;

连接查询在一个表或一个视图上进行，这样的连接查询被称为自连接
（如果在同一时间需要同一个表中两个不同行中的信息，就需要自连接了）
例如：显示学生与班长的对应信息
select s1.student_id, s1.name as 学生名, s1.monitor_id, s2.name as 班长名
from students s1, students s2 where s1.monitor_id = s2.student_id(+);
等同于
select s1.student_id, s1.name as 学生名, s1.monitor_id, s2.name as 班长名
from students s1 LEFT OUTER JOIN students s2 ON s1.monitor_id = s2.student_id;

16.

oracle处理DML语句时，以事务为单位进行，一个事务为一个工作的逻辑单位，在执行每条DML语句时，所有的操作先在内存中完成，之后决定是否写回磁盘
数据库事务开始后，oracle会为DML操作涉及的表加上表锁，涉及的行加上行锁，事务结束后释放

非只读视图不仅能执行查询操作，还能执行DML操作

 

17.

假设students表中的主键是id，（只要主键修改后仍然是唯一的，则下面语句可以执行）
update students set id='100' where id='001'

 

创建oracle数据库时，要填写“全局数据库名”和“SID”
全局数据库名：用于识别不同的oracle数据库，由“数据库名.域名”构成；数据库名最大长度为8个字符，域名最大长度为128个字符；
                    （在只有一个oracle数据库服务器的环境中，可以不设置域名）
SID：用于识别同一台计算机上的同一个oracle数据库的不同实例

SQL*Plus对英文字母大小写不敏感

参考书籍：《Oracle 10g SQL和PL/SQL编程指南》