SQL基础（Oracle）

作者: 龍狮虎 | 来源:发表于2021-08-09 16:17 被阅读0次

大纲

SQL概念与分类

数据定义语言（DDL）

DML（Data Manipulation Language，数据操作语言）

数据查询语言(DQL:Data Query Language)

函数

约束与索引

1、概念:

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；

2、 SQL语言基本分类:

2.1 数据定义语言（DDL）

包括：create table、alter table、drop table等；

2.2 DML（Data Manipulation Language，数据操作语言）

用于检索或者修改数据。
DML包括：
INSERT：用于增加数据到数据库；
UPDATE：用于从数据库中修改现存的数据
DELETE：用于从数据库中删除数据。

2.3 数据查询语言(DQL:Data Query Language)

其语句，也称为"数据检索语句"，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL(也是所有SQL)用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

2.4 DCL（Data Control Language，数据控制语言）

用于定义数据库用户的权限。
DCL包括：
ALTER PASSWORD
GRANT
CREATE SYNONYM

2.5 事务处理语言(TPL)

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK

2.6 指针控制语言(CCL)

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作

3、DDL基础

3.1Create

Create table

Create sequence

Create view

Create index

Create table （建表）
语法：
CREATE TABLE table_name(
    column_name1 data_type,
    column_name2 data_type,.......
);

表名和列的命名规则

必须以字母开头

长度不能超过30个字符

不能使用oracle的保留字

只能使用如下字符 A-Z，a-z，0-9，$,#等

Oracle中字段的数据类型

字符型 char 最大2000个字节定长
char(10) '张三'后面添加6个空格把10个字节填满
varchar2 范围最大4000个字节变长

char 查询的速度极快浪费空间，查询比较多的数据用。
varchar2 节省空间（oralce中基本都用这个）
数字 number 范围- 10的38次方到10的38次方，可以是整数也可以是小数
number(4)最大表示四位整数-9999到9999
number(5,2)表示5为有效数字 2为小数的一个小数-999.99到999.99

日期 date 包含年月日和时分秒 7个字节

图片 blob 二进制大对象图像/声音 4G

Create table例子：创建一个员工表
create table liuyl_EMP
(empno    NUMBER(4),--员工号
  ename    VARCHAR2(10),--员工姓名
  job      VARCHAR2(9),--职位
  mgr      NUMBER(4),--上级领导
  hiredate DATE,--入职日期
  sal      NUMBER(7,2),--工资
  comm     NUMBER(7,2),--奖金
  deptno   NUMBER(2)—部门编号);

3.2 Drop

Drop table table_name;

Drop SEQUENCE seq_name;

Drop index index_name;

Drop view view_name;

3.3 Alter

添加字段(学生所在班级classid)（添加add）
alter table student add(classid number(2));
修改字段的长度（修改modify）
alter table student modify(xm varchar2(12));
删除一个字段
alter table student drop column sal;
字段如何改名字
--先删除
a)alter table student drop column sal;
--再添加
b)alter table student add (salary number(7,2));
表名修改
rename student to stu;

4、DML基础

4.1 insert(插入数据)

所有字段都插入

insert into liuyl_EMP 
values
  (‘1’,
   '小红',
   '会计',
   5,
   sysdate,
   2000,
   500,
   20);

部分字段插入

insert into dengxianglei_EMP
  (empno, ename, job, mgr, sal, comm, deptno)
values
  ('2',
   '小明',
   '会计',
     null,--空值
   2000,
     500,
      20);

4.2 UPDATE

将员工号为1的员工名字改为小胡

UPDATE liuyl_EMP T
SET T.ENAME=‘小胡’,
T.sal=‘5000’
WHERE T.EMPNO=1;

4.3 delete

删除员工号为2的员工信息删除

delete from liuyl_EMP T
where t.empno=2;

delete from liuyl_EMP;删除表中所有记录，表结构还在，写日志，可以恢复，速度慢
drop table liuyl_EMP;删除表结构和数据
truncate table liuyl_EMP;删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快

5、DQL(数据查询语言，sql最主要的部分)

Select查询语法

SELECT “栏位名” FROM “表格名” ;
Distinct（去重）
Where（条件）
And or
In\not in
Between and

select * from dengxianglei_EMP t
where t.empno between 4 and 7;

Like
Order by
Count
Union \union all\Intersect\Minus

select * from dengxianglei_EMP t  where t.empno = 5
intersect
select * from dengxianglei_EMP t  where t.empno = 6;

select * from dengxianglei_EMP t where t.empno = 5
union
select * from dengxianglei_EMP t where t.empno = 5;

select * from dengxianglei_EMP t where t.empno = 5
union all
select * from dengxianglei_EMP t where t.empno = 5;

select * from dengxianglei_EMP t where t.empno = 6
minus
select * from dengxianglei_EMP t where t.empno = 5;

Group by、having

select t.sal from dengxianglei_EMP t  group by t.sal having t.sal=2000;

Exists \ not exists

select *
  from dengxianglei_EMP o
 where exists (select 'x' from dengxianglei_dept t where t.deptno = o.deptno);

Decode

Decode (a,b,c,d);\decode(a,b,c,d,e)

Case

select t.ename,case t.ename
         when '小红' then
          sal * 1
         when '小明' then
          sal * 2
         else sal
       end new_sal from liuyl_emp t;

连接查询

表连接(多表查询)
笛卡尔积(a表记录条数*b表记录条数)

select * from dengxianglei_emp,dengxianglei_dept;

规定：多表查询的条件是至少不能少于表的个数-1 才能排除笛卡尔集（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）

SELECT e.ename, e.sal, d.dname FROM dengxianglei_emp e, dengxianglei_dept d WHERE e.deptno = d.deptno;

自连接（内联）
左连接、右连接

select 属性列表 from 表1 lift/right outer join 表2
on 表1.属性=表2.属性;
左连接查询(可以查询出表1所有的记录，表2中匹配的记录）
右连接查询(可以查询出表2所有的记录，表1中匹配的记录)
复合条件连接查询查询
select 属性列表 from 表1 lift/right outer join 表2
on 表1.属性=表2.属性
where 属性名=' ';(on写连接条件when中写其他条件)

习惯用法(oracle)
Select * from A ,B
WHERE A.NO=B.NO(+)(右链接，可以查询出A所有的记录，B中匹配的记录)
Select * from A ,B
WHERE A.NO(+)=B.NO(左链接，可以查询出B所有的记录，A中匹配的记录)

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

select * from liuyl_emp de
where de.deptno in (select dd.deptno from liuyl_dept dd
where dd.dname='业务部');
select *
  from liuyl_EMP o
 where exists (select 'x' from liuyl_dept t where t.deptno = o.deptno);

多列

select * from dengxianglei_emp de
 where (de.empno, de.ename) =
       (select d.empno, d.ename from dengxianglei_emp d where d.empno = 5);

分页查询（重点）

分析函数分页（这个用得多效率稍差）：分析函数可分为四类：等级函数(ranking)，聚合函数(aggregate)，行比较函数(row comparison)，统计函数(statistical)。

select a.*
  from (select de.*, row_number() over(order by de.empno desc) rn
          from liuyl_emp de) a
 where a.rn > 1 and a.rn<4;

rownum分页（效率好）

 select * from (select de.*,rownum rn from liuyl_emp de
order by de.empno) a
where a.rn<6 and a.rn>2;

with子集

With alias_name as (select1), --as和select中的括号都不能省略
alias_name2 as (select2),--后面的没有with，逗号分割，同一个主查询同级别地方，with子
查询只能定义一次

with a as
(select deptno from dept where dname like '%A%')
select * from emp where deptno in (select * from a);
with a as
(select deptno from dept where dname like '%A%'),--a结果集
a2 as(select * from a where deptno>20)--a1结果集直接从a中筛选
select * from emp where deptno in (select * from a2);

6、函数

7、约束

NOT NULL
UNIQUE
CHECK

CHECK 限制是保证一个栏位中的所有资料都是符合某些条件。
CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));
"SID" 拦只能包含大于 0 的整数。

主键 (Primary Key)

简单来说非空且唯一
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。

CREATE TABLE Customer 
(SID number PRIMARY KEY, 
Last_Name varchar(30), 
First_Name varchar(30)); 

alter table liuyl_emp add primary key (empno);

外键 (Foreign Key)

举例来说，假设我们有两个表格：一个 emp（子表）表格，里面记录了所有员工的资料；另一个 dept（父表）表格，里面记录了所有部门的资料。在这里的一个限制，就是所有的员工的部门号，都一定是要跟在 dept表格中存在。在这里，我们就会在 emp表格中设定一个外来键，而这个外来键是指向 dept表格中的主键。

CREATE TABLE ORDERS 
(Order_ID integer primary key, 
Order_Date date, 
Customer_SID integer references CUSTOMER(SID), 
Amount double);

8、索引

普通索引
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
唯一索引
CREATE unique INDEX “INDEX_NAME” ON “TABLE_NAME” (COLUMN_NAME)--唯一索引
联合索引
CREATE INDEX “INDEX_NAME” ON “TABLE_NAME” (COLUMN_NAME1, COLUMN_NAME2…..)--联合索引

其实设置主键是系统默认给该字段添加唯一索引
create index index_emp on liuyl_emp(ename);

删除索引
Drop index index_name;

9、视图

视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。

create or replace view liuyl_emp_v as
select * from liuyl_emp;

10、开窗函数

create table EMP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)
alter table EMP
  add constraint PK_EMP primary key (EMPNO);
 
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
       values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

1、 rank()/dense_rank() over(partition by ...order by ...)

现在客户有这样一个需求，查询每个部门工资最高的雇员的信息

--一般写法
select * from (select ename, job, hiredate, e.sal, e.deptno
          from emp e,
               (select deptno, max(sal) sal from emp group by deptno) t
         where e.deptno = t.deptno
           and e.sal = t.sal)
 order by deptno;
        
select * from (select ename 姓名, job 职业, hiredate 入职日期, e.sal 工资, e.deptno 部门
          from emp e,
               (select deptno, max(sal) sal from emp group by deptno) t
         where e.deptno = t.deptno
           and e.sal = t.sal)
 order by 部门;

image.png

开窗写法
select empno, ename, job, hiredate, sal, deptno
  from (select empno, ename, job, hiredate, sal, deptno, rank() over(partition by deptno order by sal desc) r from emp)
 where r = 1;
 
select empno, ename, job, hiredate, sal, deptno
  from (select empno, ename, job, hiredate, sal, deptno, dense_rank() over(partition by deptno order by sal desc) r from emp)
 where r = 1

为什么会得出跟上面的语句一样的结果呢？这里补充讲解一下rank()/dense_rank() over(partition by e.deptno order by e.sal desc)语法。
over: 在什么条件之上。
partition by e.deptno: 按部门编号划分（分区）。
order by e.sal desc: 按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则非法）
rank()/dense_rank(): 分级
整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示（最小值一定为1）。

那么rank()和dense_rank()有什么区别呢？
rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。

2、min()/max() over(partition by ...)

现在我们已经查询得到了部门最高/最低工资，客户需求又来了，查询雇员信息的同时算出雇员工资与部门最高/最低工资的差额。这个还是比较简单，在第一节的groupby语句的基础上进行修改如下：

-- 查询每位雇员信息的同时算出雇员工资与所属部门最高/最低员工工资的差额

select ename 姓名, job 职业, hiredate 入职日期, e.deptno 部门, e.sal 工资, e.sal-me.min_sal 最低差额, me.max_sal-e.sal 最高差额
  from emp e, (select deptno, min(sal) min_sal, max(sal) max_sal from emp group by deptno) me  
 where e.deptno = me.deptno order by e.deptno, e.sal;

image.png

上面我们用到了min()和max()，前者求最小值，后者求最大值。如果这两个方法配合over(partition by ...)使用会是什么效果呢？

select ename 姓名, job 职业, hiredate 入职日期, deptno 部门, 
     min(sal) over(partition by deptno) 部门最低工资, 
     max(sal) over(partition by deptno) 部门最高工资 
  from emp order by deptno, sal;  
 
select ename 姓名, job 职业, hiredate 入职日期, deptno 部门, 
     nvl(sal - min(sal) over(partition by deptno), 0) 部门最低工资差额, 
     nvl(max(sal) over(partition by deptno) - sal, 0) 部门最高工资差额
  from emp order by deptno, sal;

3、lead()/lag() over(partition by ... order by ...)

在和最高/最低工资比较完之后还觉得不过瘾，这次就提出了一个比较变态的需求，计算个人工资与比自己高一位/低一位工资的差额

select ename 姓名, job 职业, sal 工资, deptno 部门,  
   lead(sal, 1, 0) over(partition by deptno order by sal)  比自己工资高的部门前一个,  
   lag(sal, 1, 0) over(partition by deptno order by sal)  比自己工资低的部门后一个,  
   nvl(lead(sal) over(partition by deptno order by sal) - sal, 0)  比自己工资高的部门前一个差额,   
   nvl(sal - lag(sal) over(partition by deptno order by sal), 0)   比自己工资高的部门后一个差额
 from emp;

image.png

4、其他

select ename 姓名, job 职业, sal 工资, deptno 部门, 
    first_value(sal) over(partition by deptno) first_sal, 
    last_value(sal) over(partition by deptno) last_sal, 
    sum(sal) over(partition by deptno) 部门总工资, 
    avg(sal) over(partition by deptno) 部门平均工资, 
    count(1) over(partition by deptno) 部门总数, 
    row_number() over(partition by deptno order by sal) 序号 
 from emp;

image.png

SQL基础（Oracle）

大纲

1、概念:

2、 SQL语言基本分类:

2.1 数据定义语言（DDL）

2.2 DML（Data Manipulation Language，数据操作语言）

2.3 数据查询语言(DQL:Data Query Language)

2.4 DCL（Data Control Language，数据控制语言）

2.5 事务处理语言(TPL)

2.6 指针控制语言(CCL)

3、DDL基础

3.1Create

3.2 Drop

3.3 Alter

4、DML基础

4.1 insert(插入数据)

4.2 UPDATE

4.3 delete

5、DQL(数据查询语言，sql最主要的部分)

Select查询语法

连接查询

6、函数

7、约束

8、索引

9、视图

10、开窗函数

1、 rank()/dense_rank() over(partition by ...order by ...)

2、min()/max() over(partition by ...)

3、lead()/lag() over(partition by ... order by ...)

4、其他

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读