Oracle数据库 SQL函数

--1.SQL语句
/* Oracle SQL 语句由如下命令组成：
   （1）数据定义语言（DDL），包括 CREATE（创建）命令、 ALTER（修改）命令、 DROP（删除）命令等。
   （2）数据操纵语言（DML），包括 INSERT（插入）命令、 UPDATE（更新）命令、 DELETE（删除）命令、 SELECT … FOR UPDATE（查询）等。
   （3）数据查询语言（DQL），包括基本查询语句、 Order By 子句、 Group By 子句等[selects]。
   （4）事务控制语言（TCL），包括 COMMIT（提交）命令、 SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
   （5）数据控制语言（DCL）， GRANT（授权）命令、 REVOKE（撤销）命令。
*/


--2.数据类型
/*
   char(length)：固定长度的字符串
   varchar2(length)：可变长度的字符串
   number(p,s)：既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点,p默认是38位）s是指小数位数 
   date：存储日期和时间
   timestamp：除了存储日期的年月日、时分秒，还存储秒后6位及时区
   clob：存储大的文本
   blob：存储二进制的对象，如图像、视频、声音等。
   
*/
---日期类型的函数
/*
   sysdate 内置函数返回日期和时间，也就是DATE类型
   systimestamp函数可以返回当前的日期时间和时区
*/
select sysdate,systimestamp from dual;
----dual：在需要的场景使用，用于语法的完整性。没有固定的值。在系统中只有一行一列


------------- 3.DDL语法 -------------
---（1）创建表
语法：
create table 表名(
  列名 列类型 [约束],
  列名 列类型 [约束],
  列名 列类型 [约束],
  列名 列类型 [约束] --最后一个位置不需要逗号
)

----示例：
create table student(
  s_id number,
  s_no varchar2(8),
  s_name varchar2(20),
  s_sex char(3),
  s_birth date,
  s_phone varchar2(20),
  s_address varchar2(100)
)
select * from student;

---（2）删除表
----drop table 表名
-----删除student表
drop table student;

---（3）修改表结构
----<1>修改列类型 alter table 表名 modify 列名 列类型;
-----修改 s_sex的类型为varchar2类型
alter table student modify s_sex varchar2(10);

/*----查询表的数据结构的方法：
      （1）在Command命令行中查询：desc 表名;
      （2）在SQL命令行中查询：select * from user_tab_columns where table_name='表名大写';
      （3）在菜单中，找到表名，右键选择 view 或 edit
*/
select * from user_tab_columns where table_name='STUDENT';

----<2>增加列  ALTER TABLE 表名 ADD 列名 列类型;
-----在student表中，添加名为s_add的一列 数据类型为char类型
alter table student add s_add char;
----<3>删除列  ALTER TABLE 表名 DROP COLUMN 列名;
-----在student表中，删除名为s_add的一列
ALTER TABLE student DROP COLUMN s_add;
----<4>修改列名  ALTER TABLE 表名 rename column 旧名 to 新名字;
-----修改student表中的s_add为s_addd
ALTER TABLE student rename column s_add to s_addd;
----<5>修改表名 1>ALTER TABLE 表名 RENAME TO 新表名;  
----            2>Rename 旧名字 to 新名字;
-----修改student表名为student1
1>
	ALTER TABLE student RENAME TO student1;
-----修改student1表名为student
2>
	Rename student1 to student;


--4.数据完整性
实体完整性：唯一约束、主键约束、标识列
域完完整性：限制数据类型、外键约束、默认值、非空约束
引用完整性：外键
自定义完整性：过程，触发器等
---五类完整性约束：
NOT NULL         非空
UNIQUE           唯一
PRIMARY KEY      主键
FOREIGN KEY      外键
CHECKED          检查(mysql不支持的)
---创建约束的语法：
（1）所有的约束都可以在建表的时候直接添加约束
（2）所有的约束都可以在建表之后添加约束
     alter table 表名 modify 列名 列类型 null
     alter table 表名 add constraint 约束名 约束类型(要约束的列)

create table student01(
  id number not null,
  name varchar2(10)
)
alter table student01 modify id not null;

----4.1 非空约束：not null ----
语法：
1>创建表时
 create table 表名(
     列名  列类型  not null,
  )
2>创建表之后
alter table 表名 modify 列名 列类型 not null;
-----删除非空约束：
语法：alter table 表名 modify 列名 列类型 null;

----4.2 unique UK 唯一 ----
语法：
（1）创建表时
create table 表名(
      列名 列类型 primary key,
  )
-------示例：
create table student02(
      id number primary key,
      name varchar2(10)
  )
（2）创建表之后
语法：alter table 表名 add  constraint UK_表名_列名 unique(列名);
-------示例：
alter table student02 add  constraint UK_student02_id unique(列名);
------删除约束：
语法：alter table 表名 drop constraint UK_表名_列名;

----4.3 主键约束：primary key  PK ----
主键:功能相当于非空且唯一，但是一个表只能创建一个主键，这个主键不一定只有一列
     主键要是由多列组成，称之为复合主键。主键选择的原则:最少列，最少更改。
语法：
（1）创建表时
1>方法1：
create table 表名(
      列名 列类型 primary key,
  )
2>方法2：
create table 表名(
     列名 列类型，
     constraint PK_表名_列 primary key(列名)
  )
3>方法3：
 create table 表名(
     列名 列类型，
     primary key(列名)
  )
（2）创建表之后
语法：alter table 表名 add constraint  PK_表名_列 primary key(列名);
-----删除表主键
语法：alter table 表名 drop primary key;

----4.4 默认值 default
（1）创建表时
create table 表名(
     列名 列类型 default '男'
  ) 
（2）创建表之后
语法：alter table 表名 modify 列名 列类型 default '默认值';

----4.5 check约束 CK 检查约束  ----
（1）创建表时
create table 表名(
     score number check (score>=0 and score<=100)
   )
（2）创建表之后
语法：alter table 表名 add constraint CK_约束名_列 check(score>=0 and score<=100);
-----删除表约束
alter table 表名 drop constraint CK_约束名_列;

----4.6 外键 ----
外键一定是另一个表的主键，而且与另一个表的主键的类型保持一致。
外键一定是建立在多方的表中的。
-----表与表之间的关系
1>一对多：部门与员工
2>一对一：身份证与人
3>多对多：账号与角色
------示例：
创建student表加以上所学的约束
create table student(
     id  number primary key not null ,
     sno varchar2(8) unique ,
     sname varchar2(20),
     s_sex char(3) check(s_sex='男' or s_sex='女'),
     s_birth date,
     s_phone varchar2(20),
     s_address varchar2(100)default '张家界'
)
select * from user_tab_columns where table_name='STUDENT';



------------- 5.数据库操纵语言（DML） -------------
--5.1 简单查询
语法：SELECT  * （列名|表达式） FROM  表名  WHERE 条件 ORDER BY 列名

---查询表中所有的行和列
select * from 表名;
----查询emp表中中所有的行和列
select * from emp;
---查询表中部分行
select * from 表名 where 行条件;
----查询emp表中empno=7788的这一行的数据
select * from emp where empno=7788;
---查询表中部分列
select 列名 from 表名;
----查询emp表中的empno列
select empno from emp;
---查询起别名
select 列名 as 别名 from 表名;
select 列名 别名 from 表名;
----查询emp表中的sal列并起别名

---Oracle 中可以把查询的结果根据结果集中的表结构和数据形成一张新表。
----是对数据的复制和备份
----如果在复制的时候只需要表结构，只需使查询的条件不成立（比如 where 1=2、1!=1），就不会查询出任何数据，从而复制一个表结构
语法：
create table 新表名 as select 查询语句
---示例：查询emp表中的所有内容，形成一张名为emp001的新表
create table emp001 as select * from emp;--复制表中数据及表结构
select * from emp001;
---示例：只复制emp表的结构
create table emp002 as select * from emp where 1=2;--只复制表结构
select * from emp002;
-----查询所有的表信息
------查询系统用户下所有的表信息
select * from all_all_tables;
------查询当前用户的表
select table_name from user_tables;
------查询所有用户的表
select table_name from all_tables;
------查询所有表包括系统表
select * from dba_tables;

--5.2 数据插入
语法：INSERT INTO 表名(列名 1，列名 2……) VALUES (值 1，值 2……);
INSERT INTO 表名 VALUES (值 1，值 2……);--知道列名且列较少
----时间格式化字符：
1. yyyy 表示四位年份 
2. mm 表示两位月份，比如 3 月表示为 03 
3. dd 表示两位日期 
4. hh24 表示小时从 0-23，hh12 也表示小时从 0-11。 
5. mi 表示分钟 
6. ss 表示秒
-----向表中所有列插入数据
create table test01(
  id number,
  name varchar2(10),
  sex varchar2(10)
)
insert into test01(id,name,sex)values(1,'小曹','女');
select * from test01;
-------将查询的结果插入到表中
insert into test01 select * from test01;
-----向表中的所有的行和列插入数据
语法：insert into 表名 values(值列表)
insert into test01 values(2,'小王八蛋'，'女');
-------给test01表中添加birth列
alter table test01 add birth date;
-------给test01表中的birth列插入
insert into test01 values(2,'小王八蛋'，'女',to_date('2000-12-30','YYYY-MM-DD'));
-------插入空值
insert into test01 values(2,'王八蛋'，null,to_date('2000-12-30','YYYY-MM-DD'));
-------查询空值
select * from test01 where sex=null;

--5.3 更新数据
语法：UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件;
更新数据：对原来的记录做修改
----将test01表中的小曹改成小璃
update test01 set name='小璃' where name='小曹';

--5.4 删除数据
语法：delete from 表名 where 条件

--5.5 TRUNCATE
语法：TRUNCATE TABLE 表名
TRUNCATE 和 DELETE 都能把表中的数据全部删除，他们的区别是：
1. TRUNCATE 是 DDL 命令，删除的数据不能恢复； DELETE 命令是 DML 命令，删除后的数据可以通过日志文件恢复。
2. 如果一个表中数据记录很多， TRUNCATE 相对 DELETE 速度快。
   由于 TRUNCATE 命令比较危险，因此在实际开发中， TRUNCATE 命令慎用。

-- 5.6 操作符
赋值运算符：:=
特殊字符：|| （字符串连接操作符）
           --   单行注释
         /* */ 多行注释
         <<,>> 标签分隔符
           ..  范围操作符
算术运算符：+ - * /
             ** 求幂
关系运算符：>  <  >=  <=  =（=表示相等关系，不是赋值）
             <> !=不等关系
逻辑运算符：and  or  not


------------- 6.数据记录的高级查询与常用函数 -------------
-- 6.1 数据记录的高级查询
---- 6.1.1 消除重复行
去重关键字：distinct
---- 6.1.2 null操作
在查询条件中NULL值用IS NULL做条件，非NULL值用IS NOT NULL
select * from emp where ename is not null;
SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000 AND COMM IS NULL;
---- 6.1.3 IN操作
-----在 Where 子句中可以使用 IN 操作符来查询其列值在指定的列表中的行.
比如：查询出工作职责是 SALESMAN、 PRESIDENT 或者 ANALYST 的员工。
SELECT ENAME,JOB,SAL FROM EMP WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST');
-----如果要查询出不在某个值中，可以使用NOT IN.
-----in后面还可以是select查询语句
---- 6.1.4 between..and...
在 WHERE 子句中，可以使用 BETWEEN 操作符来查询列值包含在指定区间内的行。
------查询工资在1000~2000间的员工
select ename from emp where sal between 1000 and 2000;
---- 6.1.5 LIKE模糊查询
模糊查询使用 LIKE 关键字通过字符匹配检索出所
需要的数据行。字符匹配操作可以使用通配符“%”和“_” :
%：表示零个或者多个任意字符。
_：代表一个任意字符
以...开头：字符%
以...结尾：%字符
包含某一个字符：%字符%
语法是：LIKE '字符串'[ESCAPE '字符']。
-------显示员工名字以J开头,S结尾的员工的名字，工资及工作
select ename,job,sal from emp where ename like 'J%S';
-------显示员工名字以J开头的员工的名字，工资及工作
select ename,job,sal from emp where ename like 'J%';
-------显示员工名字以S结尾的员工的名字，工资及工作
select ename,job,sal from emp where ename like '%S';
-------显示员工名称包含AM的员工
select ename from emp where ename like '%AM%';
-------显示员工名称第二个字母为A的员工
select ename from emp where ename like '_A%';
---- 6.1.6 集合查询
INTERSECT(交集)， 返回两个查询共有的记录。
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
A： 1 2 3 4 5
B： 1 2 3 4 5 6 7 8
UNION ALL：1 2 3 4 5 1 2 3 4 5 6 7 8
UNION：1 2 3 4 5 6 7 8
-------查询出 dept 表中哪个部门下没有员工。只需求出 dept 表中的部门号和 emp表中的部门号的补集即可。
-------补集查询：
select deptno from dept minus select distinct deptno from emp;
-------交集查询：
select deptno from dept intersect select distinct deptno from emp;
---- 6.1.7 连接查询
内连接（inner join）与 外连接(outer join) 其中外连接又分为左外链接(left join)与右外连接(right join)。
-------请查询出工资大于 2000 元的，员工姓名，部门，工作，工资。 
select e.ename,dname,e.job,e.sal from emp e,dept d 
where e.deptno=d.deptno and e.sal > 2000;

-------inner join
select e.ename,dname,e.job,e.sal from emp e
inner join dept d on e.deptno=d.deptno where e.sal > 2000;

---- 6.1.8 排序
语法：
Select  …from 表名  [where 条件]  order by 排序列  asc|desc
asc:升序
desc:降序
排序 order by 字段 默认会从小到大排序
-------emp表中工资从小到大排序
select * from emp order by sal;
select * from emp order by sal dec;
-------多列排序 按照部门从小到大排序,然后同部门下再按照工资从大到小排序
select * from emp order by deptno,sal desc;

---- 6.1.9 子查询
-----单行子查询,多行子查询
-----单行子查询:不向外部返回结果，或者只返回一行结果。
-----多行子查询:向外部返回零行、一行或者多行结果
------查询出销售部(SALES)下面的员工姓名，工作，工资。
select ename,job,sal from emp 
where deptno=(select deptno from dept
where dname='SALES');
-------查询出Emp表中比任意一个销售员("SALESMAN")工资低的员工姓名、工作、工资
select ename,job,sal from emp where sal < any(select sal from emp where job='SALESMAN');
-------查询出比所有销售员的工资都高的员工姓名，工作，工资
select ename,job,sal from emp where sal > all(select sal from emp where job='SALESMAN');

---- 6.1.10 聚合函数与分组查询
avg:平均值
sum:求和
min:最小值
max:最大值
count:数据统计
函数名(表达式)
------例子：
select avg(sal) from emp;
select sum(sal) from emp;
select min(sal) from emp;
select max(sal) from emp;
select count(sal) from emp;
----- 分组查询
select 列 from 表 where 条件 group by ... having ... order by ....
先筛选出满足条件的记录，按照 某个列进行分组，然后再对分组后的结果进行筛选，最后在排序
  WHERE子句： 用来筛选 FROM 子句中指定的操作所产生的行 
  GROUP BY子句： 用来分组 WHERE 子句的输出 
  HAVING子句： 用来从分组的结果中筛选行 
  --> where --> group --> by-having
-------查询各岗位的最高薪水，最低薪水。要求只统计薪水>1000的
SELECT "MAX"(SAL),"MIN"(SAL) FROM EMP WHERE SAL > 1000 GROUP BY "JOB";
-------查询各部门的平均薪水及部门编号，要求只列出平均薪水>2000
SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;