Oracle 学习笔记4 SQL语言基础
一、示例模式 scott
1、解锁SCOTT模式
SQL> alter user scott account unlock;
2、从system模式切换至scott模式，scott默认密码为tiger
SQL> connect scott/tiger
3、通过show命令，确认当前模式
SQL> show user
4、通过select 命令，查询表信息
SQL> select * from emp;
二、检索数据
1、语法的基本格式
SELECT {[DISTINCT | ALL] columns | *}
[INTO table_name]
FROM {tables | views | other select}
[WHERE conditions]
[GROUP BY columns]
[HAVING conditions]
[ORDER BY columns]
上面的语法中，共使用了7个子句，他们的功能如下表
| 子句 | 说明 |
| SELECT子句 | 用于选择数据表、视图的列 |
| INTO子句 | 用于将原表的结构和数据插入新表中 |
| FROM子句 | 用于指定数据来源，包括表、视图和其他SELECT语句 |
| WHERE子句 | 用于对检索的数据进行筛选 |
| GROUP BY | 用于对检索的结果进行分组显示 |
| HAVING | 用于从使用GROUP BY 子句分组后的查询结果中筛选数据行 |
| ORDER BY | 用于对结果集进行排序（包括升序和降序） |
2、简单查询
简单查询就是只包含SELECT子句和FROM子句的SQL语句；其中SELECT子句用于选择想要在查询结果中显示的列，对于这些要显示的列，可以使用列名来表示，也可以使用*来表示，在检索数据时，数据将按照列明的顺序来排列，如果使用 * 则表示检索所有的列，并且数据将按照表结构的自然顺序进行排列；
示例1：检索所有的列
SQL> select * from dept;
示例2 ：检索部分列
SQL> select dname from dept;
在Oracle数据库中，有一个表示行中唯一特性的行标识符，该行标识符的名称为ROWID。行标识符ROWID是Oracle数据库内部使用的隐藏列，由于该列实际上并不是定义在表中，因此也称为伪列。伪列ROWID长度为18位字符，包含改行数据在Oracle数据库中的物理地址。用户使用DESCRIBE命令是无法超导ROWID列的，但是可以在SELECT语句中检索到该列。
SQL> select rowid,dname from dept;
示例2：带有表达式的SQL语句
在工资sal基础上加上50%，并同时显示原有的字段的数值与加上50%后的数值；
SQL> select sal*(1+0.5),sal from emp;
示例3：使用别名查询，别名必须要用双引号
方法1，加上as
SQL> select empno as "雇员的编号",ename as "雇员的名称" from emp;
方法2：不加as
SQL> select empno "雇员的编号",ename "雇员的名称" from emp;
示例4：将job列去重
SQL> select distinct job from emp;
3、筛选查询
示例1：查询工资大于3000的员工信息
SQL> select empno,ename,sal from emp where sal>3000;
示例2：查询员工姓名以S开头的，任意长度的员工信息，匹配任意长度，使用通配符%。
SQL> select empno,ename,job from emp where ename like 'S%';
示例3：查询员工表中，工种为PRESODNT、MANAGER、ANALYST的员工信息
SQL> select empno,ename,job from emp where job in ('PRESODNT','MANAGER','ANALYST');
示例4：查询员工表中，工资大于等于2000小于等于3000的员工信息
SQL> SELECT empno,ename,sal from emp where sal between 2000 and 3000;
示例4：登录到hr表中
首先以管理员登录system
SQL> connect system/system as sysdba;
然后解锁hr表
SQL> alter user hr account unlock;
解锁HR表后，进行密码更改
SQL> alter user hr identified by hr;
最后登录hr表
SQL> conn hr/hr;
示例5：用 IS NULL 来检索控制数据
SQL> select street_address from locations where state_province is null;
4、分组查询
分组查询的目的是用来汇总数据或者为整个分组显示单行的汇总信息，通常使用GROUP BY 子句对记录进行分组，格式如下
SELECT columns_list
FROM table_name
[WHERE conditional_expression]
GROUP BY columns_list
columns_list 字段列表，在RGOUP BY 子句中也可以指定多个列分组
table_name 表名
conditional_expression 筛选条件表达式
常用的数据统计函数
| 函数 | 说明 |
| AVG | 返回一个数字列或是计算列的平均值 |
| COUNT | 返回查询结果中的记录数 |
| MAX | 返回一个数字列或是计算列的最大值 |
| MIN | 返回一个数字列或是计算列的最小值 |
| SUM | 返回一个数字列或是计算列的总和 |
示例1：通过deptno,job 对emp表中的信息进行分组，然后根据deptno进行顺序排列
SQL> select deptno,job from emp group by deptno,job order by deptno;
5、排序查询
在SELECT 语句中，可以用ORDER BY 子句对检索的结果进行排序，用ASC进行升序排列，用DESC进行降序排列；
SELECT columns_list
FROM table_name
[WHERE conditional_expression]
[GROUP BY columns_list]
ORDER BY {order_by_expression[ASC | DESC]} [,......n]
示例1：根据deptno,empno进行升序排序
SQL> select deptno,empno,ename from emp order by deptno,empno;
6、多表关联查询
在实际的应用系统开发中会涉及多个数据表，需要通过多表查询进行实现，在进行多表查询时，会涉及到表别名，内连接、外连接、自然连接和交叉连接等概念；
别名：在SQL语句中，为每个表取的简要名称
示例1：查询管理层的员工编号，员工姓名及部门名称
SQL> select e.empno as 员工编号，e.ename as 雇员姓名，d.dname as 部门
2 from emp e,dept d
3 where e.deptno=d.deptno
4 and e.job='MANAGER';
员工编号 雇员姓名 部门
---------- -------------------- ----------------------------
7782 CLARK ACCOUNTING
7566 JONES RESEARCH
7698 BLAKE SALES
内连接
示例2：查询emp表中的部门编号存在于dept表中的记录
SQL> select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
外连接包括左连接、右连接、完全连接
左连接：查询结果会包含左表中不满足条件的查询结果；
右连接：查询结果会包含右表中不满足条件的查询结果；
示例3：查询emp中所有部门名称；
SELECT E.EMPNO,E.ENAME,E.JOB,D.DEPTNO,D.DNAME FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
自然连接：
自然连接是指在检索多个表时，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行自动连接。在自然连接中，用户不需要明确指定进行连接的列，这个任务右Oracle系统自动完成，自然连接使用NATURAL JION 关键字；
自连接
自连接主要用在自参照表上显示上下级关系或者层次关系。自参照表是指同一张表的不同列之间具有参照关系或者主从关系的表。
示例：查询每个人员的管理者
SELECT E.EMPNO,E.ENAME,E.JOB,D.DEPTNO,D.DNAME FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;
交叉连接
交叉连接实际上就是不需要任何连接条件的连接，它使用CROSS JOIN 关键字来实现，其语法格式如下：
SELECT colums_list
FROM table_name1 CROSS JOIN table_name2
colums_list:字段列表
table_name1 和table_name2:两个实现交叉连接的表名。
交叉连接的执行结果时一个笛卡尔积，这种查询结果时非常冗余的，但是可以通过WHERE子句来过滤出有用的记录信息。
三、Oracle常用的系统函数
1、字符类函数
（1）ASCII(c)函数和CHR(i)函数
示例1：
ASCII(c)函数用于返回一个字符的ASSCII码，其中参数c表示一个字符；
SQL> select ascii('A'),ascii('K') from dual;
ASCII('A') ASCII('K')
---------- ----------
65 75
示例2：
CHR(i)函数用于返回给出ASCII码所对应的字符
SQL> select CHR(80) from dual;
CH
--
P
（2）CONCAT(s1,s2)函数
该函数将字符串s2连接到字符串s1的后面，如果s1为NULL，则返回s2;如果s2为NULL，则返回s1,如果s1,s2都为NULL，则返回NULL；
示例：
SQL> select concat('Hello','World') from dual;
CONCAT('HELLO','WORL
--------------------
HelloWorld
（3）INITCAP(s)函数
该函数将字符串s的每个单词的第一个字母大写，其他字母小写，单词之间用空格、控制字符、标点符号区分；
（4）INSTR(s1,s2[,i][,j])函数
该函数用于返回字符串s2在字符s1中第j次出现的位置，搜索从字符串s1的第i个字符开始，当没有发现要查找的字符时，该函数返回0；如果i为负数，那么搜索将从右往左进行，但函数的返回位置还是从左往右来计算。其中s1和s2均为字符串，i和i均为整数，默认值为1。
示例：
在字符串oracle 11g中，从第三个字符开始查询字符串1第二次出现的位置；
SQL> select INSTR('oracle 11g','1',3,2) from dual;
INSTR('ORACLE11G','1',3,2)
--------------------------
9
（5）LENGTH（s）函数
该函数用于返回字符串s的偿付，如果s为NULL，则返回值为NULL。
SQL> select length('xxxxx') from dual;
示例：
LENGTH('XXXXX')
---------------
5
（6）LOWER(s)函数和UPPER(s)函数
LOWER(s)函数用于返回字符串s的小写形式，UPPER（s）用于返回字符串s的大写形式；
（7）LTRIM(s1，s2)函数、RTRIM(s1，s2)函数和TRIM(s1，s2)函数
示例1：
LTRIM(s1,s2)用于删除字符串s1左边的字符串s2;
SQL> select LTRIM('#XXX#','#') from dual;
LTRIM('#
--------
XXX#
示例2：
RTRIM(s1,s2)用于删除字符串s1右边的字符串s2;
SQL> select RTRIM('#XXX#','#') from dual;
RTRIM('#
--------
#XXX
示例3
TRIM(s1,s2)用于删除字符串s1左右两端的字符串s2;
SQL> select TRIM('#'from'#XXX#') from dual;
TRIM('
------
XXX
如果这三个函数不指定字符串s2，则表示去除相应方位的空格；
（8）REPLACE(s1,s2[,s3])函数
该函数使用s3字符串替换出s1字符串中的所有s2字符串，并返回替换后的新字符串，其中s3的默认值为空字符串；
示例：将字符串Bad luck Bad gril中的Bad改为Good
SQL> select replace('Bad luck Bad gril','Bad','Good') from dual;
REPLACE('BADLUCKBADGRIL','BAD','GOOD')
--------------------------------------
Good luck Good gril
（9）SUBSTR(s,i,[j])函数
该函数表示从字符串s的第i个位置开始截取长度为j的子字符串，如果省略参数j，则直接截取到尾部；
示例：从字符串messageBox的第八位开始截取三位的字符串；
SQL> select substr('messageBox',8,3) from dual;
SUBSTR
------
Box
2、数字类函数
（1）常用的数字类函数及说明
| 函数 | 说明 |
| ABS（n） | 返回n的绝对数值 |
| CEIL（n） | 返回大于或等于数值n的最小整数 |
| COS（n） | 返回n的余弦值，n为弧度 |
| EXP（n） | 返回e的n次幂，e=2.71828183 |
| FLORR(n) | 返回小于或等于n的最大整数 |
|
LOG(n1，n2) |
返回n1为底n2的对数 |
| MOD（n1，n2） | 返回n1除以n2的余数 |
|
POWER(n1，n2) |
返回n1的n2次方 |
|
ROUND(n1，n2) |
返回舍入小数点n2位的n1的值，n2的默认值为0，这会返回小数最接近的整数。如果n2是负数，就舍入到小数点左边相应的位上，n2必须是整数 |
| SIGN(n) |
若n为负数，则返回-1，若n为正数，则返回1；若n=0，则返回0 |
| SIN（n） | 返回n的正弦值，n为弧度 |
|
SQRT(n) |
返回n的平方根 |
|
TRUNC(n1，n2) |
返回位数到n2位的小数的n1值，n2默认设置为0，当n2为默认设置时，会将n1截尾为整数，如果n2为负数，就截尾在小数点左边相应的位上； |
（2）CEIL(n)函数
返回大于或等于数值n的最小整数，适合于比较运算
示例：返回大于或僧与7.3的最小整数；
SQL> select ceil(7.3) from dual;
CEIL(7.3)
----------
8
（3）ROUND(n1，n2) 函数
返回舍入小数点n2位的n1的值，n2的默认值为0，这会返回小数最接近的整数。如果n2是负数，就舍入到小数点左边相应的位上，n2必须是整数
示例：将数值3.1415926四舍五入取2位小数；
SQL> select round(3.1415926,2) from dual;
ROUND(3.1415926,2)
------------------
3.14
（3）PROWER(n1，n2)函数
返回n1的n2次方
示例：计算3的3次方的结果
SQL> select power(3,3) from dual;
POWER(3,3)
----------
27
3、日期和时间类函数
（1）常用的日期和时间类函数
| 函数 | 说明 |
| ADD_MONTHS(d,i) | 返回日期d加上i个月之后的结果，其中，i为整数 |
| LAST_DAY(d) |
返回包含日期d月份的最后一天 |
| MONTHS_BETWEEN(d1,d2) |
返回d1和d2之间的数目，若d1和d2的日期相同，或者都是该月的最后一天，则返回一个整数，否则返回的结果将包括小数 |
| NWE_TIME(d1,t1,t2) | 其中，d1是日期数据类型，当时区t1中的日期和时间是d1时，返回时区d2的日期和时间，t1和t2是字符串 |
| SYSDATE() | 返回系统当前时间 |
（2）SYSDATE()函数
该函数用于返回系统当前时间
示例：显示系统日期
SQL> select sysdate as s系统日期 from dual;
S系统日期
--------------
05-1月 -21
（3）ADD_MONTHS(d,i)函数
该函数用于返回日期d加上i个月之后的结果，其中，i为整数
示例：求当前日期加上1个月的日期
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYS
--------------
05-2月 -21
4、转换类型函数
（1）常用的转换类函数
| 函数 | 说明 |
| CHARTORWIDA(s) | 该函数将字符串s转换为RWID数据类型 |
| CONVERT(s,aset[,best]) | 该函数将字符串s右best字符集转换为aset字符集 |
| ROWIDTOCHAR() | 该函数将ROWID数据类型转换为CHAR类型 |
| TO_CHAR(x[,format]) | 该函数实现将表达式转换为字符串，format表示字符串格式 |
| TO_DATE(s[,format[lan]]) | 该函数将字符串s转换成DATE=类型，format表示字符串格式，lan表示所使用的语言 |
|
TO_NUMBER(s[,format[lan]]) |
该函数将返回字符串s代表的数字，返回值按照format格式进行显示，format表示字符串格式，lan表示所使用的语言 |
（2）TO_CHAR（）函数
示例：将系统时间按照年月日格式进行显示
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;
TO_CHAR(SYSDATE,'YYY
--------------------
2021-01-05
（3）TO_NUMBER(s[,format[lan]]) 函数
该函数将返回字符串s代表的数字，返回值按照format格式进行显示，format表示字符串格式，lan表示所使用的语言
示例：将16进制的18f转换为十进制数；
SQL> select to_number('18f','xxx') from dual;
TO_NUMBER('18F','XXX')
----------------------
399
5、聚合函数
（1）常用的聚合函数
| 函数 | 说明 |
| AVG(x[DISTINCT|ALL]) | 计算所选择列表项的平均值，列表项目可以是一个列或多个列的表达式 |
| COUNT(x[DISTINCT|ALL]) | 返回查询结果中的记录数 |
| MAX(x[DISTINCT|ALL]) | 返回选择列表项目中的最大数，列表项目可以是一个列或多个列的表达式 |
| MIN(x[DISTINCT|ALL]) | 返回选择列表项目中的最小数，列表项目可以是一个列或多个列的表达式 |
| SUM(x[DISTINCT|ALL]) | 返回选择列表项目中的数值总和，列表项目可以是一个列或多个列的表达式 |
| VARIANCE(x[DISTINCT|ALL]) | 返回选择列表项目中的统计方差，列表项目可以是一个列或多个列的表达式 |
| STDDEV(x[DISTINCT|ALL]) | 返回选择列表项目中的标准偏差，列表项目可以是一个列或多个列的表达式 |
（2）AVG(x[DISTINCT|ALL])
计算所选择列表项的平均值，列表项目可以是一个列或多个列的表达式
示例：求emp表中右多少职工，职工的平均工资是多少
SQL> select count(empno),round(avg(sal),2) from scott.emp;
COUNT(EMPNO) ROUND(AVG(SAL),2)
------------ -----------------
15 2073.21
四、子查询的用法
1、单行子查询
单行子查询是指返回一行数据的子查询语，当在WHERE子句中引用单行子查询时，可以使用单行比较运算符（=、<、>、>=、<=、和<>）;
示例：查询EMP表中工资大于最低工资并且小于最高工资的人员信息；
SQL> select empno,ename,sal from emp where sal>(select min(sal) from emp)and sal<(select max(sal) from emp);
2、多行子查询
多行子查询是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须使用多行比较符（IN、ANY、ALL）
示例：查询EMP表中不属于销售部门的人员信息；
SQL> select empno,ename,job from emp where deptno in (select deptno from dept where dname<>'SALES');
3、关联子查询
在单行子查询和多行子查询中，内查询和外查询时分开执行的，也就是说，内查询的执行与外查询的执行时没有关系的，外查询仅仅是使用内查询的最终结果。在一些特殊需求的子查询中，内查询的执行需要借助于外查询，而外查询的执行又离不开内查询的执行，这时，内查询和外查询时相互关联的，这种子查询就被成为关联子查询。
示例：查询EMP表中，工资大于同职位平均工资的人员信息
SQL> select empno,ename,sal from emp f where f.sal>(select avg(sal) from emp where job=f.job) order by job;
五、操作数据库
1、插入操作（INSERT语句）
使用INSERT语句有以下注意事项：
当为数字列增加数字时，可以直接提供数字值，或者用单引号引住；
当为字符列或日期列增加数据时，必须用单引号引住；
当增加数据时，数据必须满足约束规则，并且必须为主键列和NOT NULL列提供数据。
当增加数据时，数据必须于列的个数和顺序保持一致；
(1)单条数据插入格式
INSERT INTO table_name [(column_name1[,column_name2]......)] VALUES(express1[,express2]......)
table_name：表示要插入的表名；
colmun_name1和column_name2:指定表的完全或部分列名称。如果指定多个列，那么列之间用逗号隔开；
express1和express2:表示要插入的值列表。
示例：往DEPT表中插入设计部门信息
SQL> insert into dept(deptno,dname,loc) values(88,'design','beijing');
已创建 1 行。
SQL> commit;
提交完成。
（2）批量插入数据格式
INSERT INTO table_name[(column_name1[,column_name2]......)] selectSubquery
table_name:表示要插入的表名称；
column_name1和column_name2:表示指定的列名；
selectSubquery:任何合法的SELECT语句，器所选列的个数和类型要与语句中的column对应；
示例：新建JOB_EMP表，并将JOBS表中MAX_SALARY大于10000的数据插入到JOB_EMP表中
SQL> insert into dept(deptno,dname,loc) values(88,'design','beijing');
已创建 1 行。
SQL> commit
2
SQL> commit;
提交完成。
SQL> insert into job_emp
2 select * from jobs where jobs.max_salary >10000;
2、更新数据（UPDATE语句）
使用UPDATE语句有以下注意事项：
当更新数字列时，可以直接提供数字值，或者用单引号引住；
当更新字符列或日期列时，必须用单引号引住；
当更新数据时，数据必须要满足约束规则；
当更新数据时，数据必须与列的数据类型匹配；
（1）更新数据格式：
UPDATE table_name
SET{column_name1=express1[,colum_name2=express2......]
| (column_name1[,column_name2......])=(selectSubquery)}
[WHERE condition]
table_name：表示要修改的表名；
column_name1和column_name2;表示指定要更新的列名；
selectSubquery:任何合法的SELECT语句，其所选列的个数和类型要与语句中的column对应；
condition:筛选条件表达式，只要符合筛选条件的记录才被更新；
示例：更新EMP表中销售员的工资，销售元工资上涨20%。
SQL> update emp set sal =sal*1.2 where job='SALESMAN';
已更新5行。
3、删除数据（DELETE语句和TRUNCATE语句）
（1）DELETE语句
DELETE语句格式：
DELETE FROM table_name [WHERE condition]
table_name：表示要删除记录的表名；
condition：筛选条件表达式，是个可选项，当筛选条件存在时，只有符合筛选条件的记录才被删除；
删除满足条件的数据：当使用DELETE语句删除数据时，通过指定WHERE子句可以删除满足条件的数据；
（2) TRUNCATE语句
相比于DELETE语句，TRUNCATE语句的删除速度更快，但是他不会产生回滚记录，所以无法同构ROLLBACK进行语句撤销；
格式：TRUNCATE TABLE table_name
示例：清空job_emp表中的数据
SQL> truncate table job_emp;
表被截断。
SQL> select * from job_emp;
未选定行
