我最近在读全宇宙最牛逼的Oracle知识点总结（II）

12.基本命令：
SQL>Show user;显示当前登录的用户
SQL>clear screen;清空屏幕
SQL>host cls;清空屏幕
SQL> spool c:\some.sql – 将以下执行的sql 语句和执行结果保存到some.sql文件中。
SQL> spool off;关闭，将数据写出到some.sql中。

13.
--创建表的DDL语句
--创建一个用户表 CONSTRAINT 创建表约束
create table users(
userid integer CONSTRAINT users_pk PRIMARY KEY,
username nvarchar2(50),
userpass nvarchar2(20)
);

--查看表结构的命令 desc 表名
desc users

--查询users表
select * from users;

--删除表 users
drop table users;

--更改表名
rename users to myusers;

--插入记录
insert into myusers values(5,'张无忌','123456','1992-12-05',5000);

--查询记录
select * from myusers;

--给myusers新增加一列 出生日期
--sysdate 是系统的当前时间 default 默认值 not null 不能为空
alter table myusers
add birthday date default sysdate not null;

--给myusers表添加性别(sex)字段
alter table myusers
add sex nvarchar2(6);

--查看表的(sex)字符长度和(username)的字节长度
select length(sex),lengthb(username) from myusers;
select length(birthday),lengthb(birthday) from myusers;

--修改字段(修改性别字段的默认值是男)
alter table myusers
modify sex default '男';

select * from myusers;
--添加新的用户
insert into myusers values(1,'张三丰','678988','1982-05-23');
insert into myusers values(2,'刘备','123456',sysdate);
insert into myusers values(4,'曹操','abcd12','1982-04-14');
insert into myusers (userid,username,userpass,birthday) values (3,'刘亦菲','345678','1990-12-24');
insert into myusers values(6,'午马','345671','1972-08-12',2000,'男');
--不允许对虚拟列插入值
insert into myusers (userid,username,userpass,birthday,salary,sex) values(8,'李四','123678','1992-09-12',2000,'女');

--给表添加注释
comment on table myusers is '用户信息表';

--从视图中查看备注信息
select * from SYS.user_tab_comments;

--复制一张新表
create table users as select * from myusers;

--删除表users 被删除的表会在回收站中
drop table users;

--删除表users 不经过回收站直接删除
drop table users purge;

--给myusers表再增加一个工资字段
alter table myusers
add salary number(10,2);

--修改字段(修改工资字段的默认值是1000)
alter table myusers
modify salary number(10,2) default 1000;

--修改(更新)字段值
update myusers set salary=1000;

--新增一个年薪虚拟列
alter table myusers add(year_salary as (salary*12));

--查看表结构
desc myusers;

describe myusers;

--给列重命名
alter table myusers
rename column year_salary to ysalary;

select * from myusers;

--删除虚拟列
alter table myusers
drop column ysalary;

alter table myusers
drop column year_salary;

--在表中复制一行
insert into myusers (userid,username,userpass) select 7,username,userpass from myusers where userid=3;

14.Oracle数据库中dual表：
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情。
1、查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;

　　2、用来调用系统函数
　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间
　　select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名
　　select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale

　　select dbms_random.random from dual;--获得一个随机数

　　3、得到序列的下一个值或当前值，用下面语句
　　select your_sequence.nextval from dual;--获得序列your_sequence的下一个值
　　select your_sequence.currval from dual;--获得序列your_sequence的当前值
　　4、可以用做计算器 select 7*9 from dual;

15.SELECT语句的基本语法
SELECT 子句
FROM 子句
[WHERE 子句]
说明
SELECT 子句：指定查询结果集的列的组成，列表中的列可以来自一个或多个表或视图
FROM 子句：指定要查询的一个或多个表或视图
WHERE 子句：指定查询的条件

关键字
是指单个的SQL元素。
例如，SELECT和FROM都是关键字
子句
是SQL语句的一部分。
例如，SELECT employee_id,last_name,…就是一个子句
语句
是两个或更多个子句的组合。
例如，SELECT * FROM employees就是一个SQL语句

选择所有的列:在SELECT关键字后加星号(*)，也可以列出所有的列
投影:在SELECT语句中指定列名，使用逗号隔开，这样可以指定表显示某些列，也可以调整列的先后显示顺序
如果查询语句中有表达式时，表达式的名称将会显示在列位值，可以为其提供别名；别名可以使用双引号，也可以在别名前加AS关键字
例如: SELECT sal AS "员工工资" FROM emp;
SQL语句是不区分大小写的
SQL语句可以是一行，也可以是多行
关键字不能在两行之间一分为二或缩写
子句通常放在单独的行中，这样可以增强可读性并且易于编辑
使用缩进是为了增强可读性
关键字通常使用大写字母输入；所有其它文字都是使用小写输入的

使用算术运算符，可以创建数字和日期数据表达式。
算术表达式可以包含列名、常数值和算术运算符,+,-,*,/
乘和除的运算优先级高于加和减
优先级相同的运算符是从左到右进行运算的
可以使用小括号来强制语句做出优先运算，并使语句运算顺序更为清晰
例如:SELECT empno,ename, sal,sal/12 + 100 FROM emp;
使用括号
例如:SELECT empno,ename, sal,(sal+10000)/12 FROM emp;

连接运算符具有以下特定和功能
将列或字符串连接到其他列
由两条竖线表示(||) 或 concat(arg1,arg2)
创建一个合成列，它是字符串表达式
例如:SELECT empno||ename AS "Employees" FROM emp;
或者:SELECT concat(empno,ename) AS "Employees" FROM emp;
文字字符串：
文字是指包含在SELECT列表中的字符、数字或日期
日期和字符文字值必须包含在单引号中
在输出结果中，每个返回的行都会将每个字符串显示一次

WHERE子句在FORM后面，将查询对象限制在满足一定条件的行
condition(s)：由列名、表达式、常数和比较运算符组成
WHERE子句可以比较列、文字值、算术表达式或函数的值。它包含三个元素：
列名
比较条件
列名、常数或值列表
Where子句中的and\or\in\not in\between\like关键字的使用

字符串和日期包含在单引号中
WHERE子句中的字符串和日期必须包含在单引号中。但是数字常数不能包含在单引号中
字符值是区分大小写的
默认日期格式为DD-MON-YY
Oracle数据库以内部数字格式存储日期，包括世纪、年、月、日、小时、分钟和秒

dual表常用在没有查询目标的SELECT语句块中，它只包含一行数据的系统表。
可以使用DESCRIBE查看dual表结构
DESCRIBE dual
查询dual表的数据
SELECT * FROM dual;
查询当前日期
SELECT sysdate FROM dual;
可以在日期上使用加法或减法运算符，加减操作的数字表示天数
例如，在日期上加3天：SELECT TO_DATE('14-5月-2012')+3 FROM dual;
在某日期上减3天：SELECT TO_DATE('14-5月-2012')-3 FROM dual;

比较运算符：
运算符 说明
= 等于
<>或!= 不等于
< 小于

  大于

<= 小于等于
= 大于等于
ANY 比较值列表中的任何一个值
ALL 比较列表值中的所有值

<>运算符
查询empno不等于7369的客户信息：SELECT FROM emp WHERE empno <> 7369;
或者：SELECT FROM emp WHERE empno != 7369;

运算符
查询工资大于1000的员工：SELECT * FROM EMP WHERE SAL>1000;

ANY运算符
在WHERE子句中使用ANY运算符与列表中的任意值进行比较
必须在使用ANY运算符之前使用= , <> , < , > , <= ,或 >=运算符
例如:查询emp表，empno值只要大于列表(7369, 7521, 7499)中的任意一个值即满足条件
SELECT FROM emp
WHERE empno > ANY (7369, 7521, 7499);
ALL运算符
在WHERE子句中使用ALL运算符与值列表中的所有值进行比较
例如，查询emp表
SELECT FROM emp
WHERE empno > ALL(7369, 7521, 7499);

SQL运算符可以进行模式匹配，像字符串、列表值、NULL值。
LIKE：匹配字符串
IN：匹配列表值
BETWEEN：匹配范围值
IS NULL：匹配空值
IS NAN：匹配非数字值

可以在WHERE子句中使用LIKE运算符进行字符串的匹配
可以使用普通字符及通配符进行组合，通配符如下：
下划线(_)：表示匹配某个位置的字符
百分号(%)：表示在某个位置的任意个字符
例如，匹配’_o%’，表示第一个字母任意，第二个是o，%表示最后可以是任意个字母
SELECT *FROM emp WHERE ename LIKE '_M%';

转义字符：
如果要查询实际的下划线或百分号就需要使用ESCAPE选项区分通配符
ESCAPE选项告知数据库如何区分通配符和要匹配的字符，可以使用反斜杠()，例如，
SELECT ename FROM emp
WHERE ename LIKE '%\%%' ESCAPE '\';

IN运算符：
在WHERE子句中可以使用IN筛选某列的值是否在列表中
例如：查询empno在(7369, 7521, 7499)中的员工
SELECT FROM EMP
WHERE EMPNO IN (7369, 7521, 7499);
查询empno不在(7369, 7521, 7499)中的员工
SELECT FROM EMP
WHERE EMPNO NOT IN (7369, 7521, 7499);
Exists运算符
select from emp where exists (select from emp);

BETWEEN运算符：
在WHERE子句中可以使用BETWEEN检查某列的值是否在一个指定的范围内。包含区间两端的值。
例如，查询工资在800到1300之间的员工
SELECT * FROM emp
WHERE sal BETWEEN 800 AND 1300;

逻辑运算符：
逻辑运算符允许根据逻辑条件限制行
x AND y：x和y都必须是真时，返回真
x OR y：x或y为真时，返回真
NOT x：x为假时，返回真

AND运算符
例如：查询emp表中雇佣日期列大于’01-1月-1982’年，并且工资大于900的员工信息
SELECT FROM EMP
WHERE HIREDATE > '01-1月-1982'
AND SAL > 900;
OR运算符
例如：查询emp表中雇佣日期列大于’01-1月-1982’年，或者工资大于900的员工信息
SELECT FROM EMP
WHERE HIREDATE > '01-1月-1982'
OR SAL > 900;

ORDER BY字句 进行排序
可以使用ORDER BY对查询的结果进行排序，可以对一列或多列进行排序，ORDER BY必须在FROM子句后或WHERE子句后(如果有WHERE子句)
例如，查询EMP表，并对ename列进行排序
SELECT FROM EMP ORDER BY ename;
默认情况下，ORDER BY是按照升序(ASC)排序，也可以使用DESC指定为降序排序
例如，查询EMP表，先按empno升序排列，再按ename降序排列
SELECT FROM EMP
ORDER BY empno ASC, ename DESC;
同样可以是列的位置编号，指定哪列需要进行排序
例如，查询emp表，要对查询的第2列ename列进行降序排列
SELECT *FROM emp ORDER BY 2 DESC;

NULL值：
NULL值表示未知的值。它是一个特殊的值，但并不是空字符串，NULL值表示该列是未知的。
当某些查询语句在输出结果列上看不到值的时候，可能就是NULL值
SELECT * FROM EMP WHERE COMM IS NULL;
处理NULL值
NULL值被查询出来的时候没有显示信息，如何告知用户这是空字符串还是NULL，这可以通过NVL()函数来进行处理
SELECT empno,ename ,NVL(comm, 0) FROM EMP;

在SELECT语句中，可以使用DISTINCT关键字删除重复的行
例如，查询EMP表中，删除重复的job值
SELECT DISTINCT job FROM EMP;

SELECT中使用参数：
语法：使用&+参数名
例如：查询员工编号为某数值的员工
SELECT * FROM EMP WHERE EMPNO=&arg;