内容简述:数据库简介、SQL基本操作、SQL分类、DML、DDL、DQL、筛选、排序、分组、聚合、关联、笛卡儿积、表之间的对应关系、权限管理、视图、索引、约束、事务。
写在前面:文章内容为个人整理,分享给大家学习使用,请勿商用哟~
- 文中查询、操作的数据库结构,在 六、附录 中可查看哟~
查看表结构的SQL:
1 | -- 查看当前用户下的所有表及表注释 |
一、SQL语句、DDL语句、非空约束、修改表名、修改表结构、DML语句
Hello World
1 | -- 打印 |
SQL语句
SQL:结构化查询语句,是用来操作数据库的语言;
所有的数据库都支持标准的SQL语句。
SQL语句包含:
- DDL,DML,TCL,DQL,DCL这几类语句
DDL语句:
- DDL语句是用于增删改数据库对象的
数据库对象:
- 表,视图,索引,序列
创建表:
1 | CREATE TABLE employee( |
查看表结构:
1 | DESC employee; |
删除表:
1 | DROP TABLE employee; |
大小写:
- SQL语句是不区分大小写的,但是字符串的值(直接量)是区分大小写的,字符串的直接量是使用单引号括起来的。
默认值:
数据库中所有数据类型的默认值都是NULL,在创建表时,可以使用DEFAULT为字段单独指定默认值。
1
2
3
4
5
6
7
8
9
10
11CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1) DEFAULT 'M',
birth DATE,
salary NUMBER(6,2) DEFAULT 5000,
job VARCHAR2(30) DEFAULT 'CLERK',
deptno NUMBER(2)
);
DESC employee;
非空约束
当一个字段被NOT NULL修饰后,该字段在任何情况下值不能为NULL。
1
2
3
4
5
6
7
8
9
10
11
12CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT 'M',
birth DATE,
salary NUMBER(6,2) DEFAULT 5000,
job VARCHAR2(30) DEFAULT 'CLERK',
deptno NUMBER(2)
);
-- 非空约束可以在查看表结构中体现出来
DESC employee;
修改表 - 修改表名:
1 | -- 方式一: |
实践
1 | -- 将employee表改名为myemp |
修改表结构:
添加新字段
- 向表myemp中添加字段hiredate。
1
ALTER TABLE myemp ADD( hiredate DATE );
删除表中现有字段
- 将myemp表中的hiredate字段删除。
1
2-- 将myemp表中的hiredate字段删除
ALTER TABLE myemp DROP(hiredate);修改表中现有字段
- 修改字段可以修改字段的类型,长度,默认值
1
ALTER TABLE myemp MODIFY( job VARCHAR2(40) DEFAULT 'CLERK');
注意:若表中已经存在数据,那么修改字段的时候尽量不修改类型,修改长度尽量不要缩小,否则可能导致修改失败。
DML语句
- DML语句用来对表中数据进行相关操作,包括:增,删,改。
插入数据
1
INSERT INTO myemp(id,name,salary,deptno) VALUES (1,'JACK',3000,10);
- 插入数据时,忽略字段名则是全列插入;
1
INSERT INTO myemp VALUES (2,'ROSE','F',SYSDATE,5000,'MANAGER',20);
- 插入日期时,使用TO_DATE函数
1
INSERT INTO myemp (id,name,birth) VALUES(3,'JACKSON',TO_DATE('1992-08-02','YYYY-MM-DD'));
修改数据
- UPDATE语句用于修改表中数据,需要使用WHERE添加条件以修改满足条件的记录;
- 若不添加WHERE则是全表所有数据修改。
1
2-- 将ROSE的工资改为6000,部门号改为30
UPDATE myemp SET salary=6000,deptno=30 WHERE name='ROSE'删除表中现有字段
- DELETE语句用于删除表中记录,通常需要使用WHERE添加条件来删除满足条件的记录;
- 若不添加WHERE是清空表操作。
1
DELETE FROM myemp WHERE name='ROSE';
二、DQL语句、字符串函数、数字函数、日期类型、日期函数、空值操作、空值函数
DQL语句
- DQL语句用于查询数据库中的数据
DQL必须包含两个子句:
SELECT:SELECT子句用来指定要查询的字段,可以是表中的字段,函数和表达式;
FROM:FROM子句用来指定数据来源的表;
1
2
3
4
5-- 查看emp表中的数据
SELECT * FROM emp;
-- 查看ename,job,sal,deptno
SELECT ename,job,sal,deptno FROM emp;DQL中也可以使用WHERE子句来添加过滤条件,这样只会将满足条件的记录查询出来;
1
SELECT ename,job,sal,deptno FROM emp WHERE deptno=20
SELECT子句中也可使用函数或表达式。
1
2-- 查看公司每个员工的年薪是多少?
SELECT ename,sal,sal*12 FROM emp;
字符串函数
CONCAT(char1,char2)
- 将两个参数字符串连接在一起返回;
1
2
3
4-- 示例1
SELECT CONCAT(ename,sal) FROM emp;
-- 示例2
SELECT CONCAT(CONCAT(ename,','),sal) FROM emp;- “||”可以连接字符串。
1
SELECT ename||','||sal FROM emp;
LENGTH(char)
- 返回指定字符串的长度
1
SELECT ename, LENGTH(ename) FROM emp;
补充:dual(伪表)
伪表不是一张真是存在的表,当查询的内容与任何表数据无关时,可以使用伪表。
1 SELECT SYSDATE FROM dual;
UPPER、LOWER、INITCAP
- 将字符串转换为大写,小写,首字母大写其余小写;
1
2
3
4SELECT UPPER('helloworld'), LOWER('HELLOWORLD'), INITCAP('HELLO WORLD') FROM dual;
-- 打印信息
'HELLOWORLD','helloworld','Hello World'- 可以作为查询条件。
1
SELECT ename,sal,job,deptno FROM emp WHERE ename=UPPER('scott');
TRIM、LTRIM、RTRIM
- 去除字符串两端的指定字符
1
2
3
4SELECT TRIM('e' FROM 'eeeliteee'), LTRIM('eddsdsesliteee','esd'), RTRIM('eeeliteddsdses','esd') FROM dual;
-- 打印信息
'lit','liteee','eeelit'LPAD、RPAD 补位函数
- 将指定字符串显示指定长度,当不足时补充若干个指定字符以达到该长度。
1
SELECT ename,RPAD(sal,5,'$') FROM emp;
SUBSTR(char, m[, n])
- 截取指定字符串,从m处开始连续截取n个字符;
- n若不指定或超过实际可截取的长度,则都是截取到字符串末尾;
- m若为负数,则是从倒数位置开始截取,数据库中下标都从1开始。
1
SELECT SUBSTR('thinking in java',-7,2) FROM dual;
INSTR(char1, char2[, m[, n]])
- 查看char2在char1中的位置;
- m为从哪里开始查找,不写默认为1;
- n为第几次出现,不写默认为1。
1
SELECT INSTR('thinking in java','in',4,2) FROM dual;
数字函数
ROUND(n,m)
- 四舍五入保留n小数点后m位;
- 若m不写或0则表示保留到个位;
- 若是负数则是保留到十位以上的数字。
1
2
3
4
5
6-- 保留两位小数
SELECT ROUND(45.678, 2) FROM DUAL; -- 45.68
-- 保留整数
SELECT ROUND(45.678, 0) FROM DUAL; -- 46
-- 保留到百位
SELECT ROUND(55.678, -2) FROM DUAL; -- 100TRUNC()函数
- 与ROUND参数意义一致,作用是截取数字。
1
2
3
4
5
6-- 截取两位小数
SELECT TRUNC(45.678, 2) FROM DUAL; -- 45.67
-- 截取整数
SELECT TRUNC(45.678, 0) FROM DUAL; -- 45
-- 截取到十分位
SELECT TRUNC(55.678, -1) FROM DUAL; -- 50MOD(m,n)函数
- 求余数,n为0则直接返回m。
1
SELECT MOD(4321.123,1000) FROM DUAL;
CEIL、FLOOR
- 向上取整、向下取整。
1
2SELECT CEIL(45.678) FROM DUAL; -- 46
SELECT FLOOR(45.678) FROM DUAL; -- 45
日期类型
两个常用关键字:
- SYSDATE:对应数据库一个内置函数,返回一个DATE类型数据,表示当前系统时间;
- SYSTIMESTAMP:返回一个时间戳类型的当前系统时间。
1
2
3
4
5
6-- 获取系统日期 2020/12/1 9:22:59
SELECT SYSDATE FROM DUAL;
-- 插入系统日期
INSERT INTO emp(empno,ename,hiredate) VALUES(1,'jack',SYSDATE);
-- 获取系统时间戳 01-DEC-20 09.23.15.157000 AM +08:00
SELECT SYSTIMESTAMP FROM DUAL;
日期转换函数
TO_DATE()
- 可以将给定字符串按照指定的日期格式转换为DATE类型值。
1
2-- 1992/8/3 15:22:33
SELECT TO_DATE('1992-08-03 15:22:33', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;- 在日期格式字符串中凡不是英文,符号的其他字符都需要使用双引号括起来。
1
2
3
4-- 1992/8/3 15:22:33
SELECT
TO_DATE('1992年08月03日 15时22分33秒', 'YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"')
FROM DUAL;TO_CHAR() 函数
- 可以将DATE按照给定的日期格式转换为字符串。
1
2
3
4
5-- 2020-12-01 09:33:51
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- 1958-08-03
SELECT TO_CHAR(TO_DATE('58-08-03','RR-MM-DD'), 'YYYY-MM-DD') FROM DUAL;
日期类型是可以计算的
对一个日期加减一个数字等同于加减天数;
两个日期做减法,差为相差的天数。
查看明天的日期?
1 | SELECT SYSDATE+1 FROM DUAL; |
查看每个员工入职至今多少天了?
1 | SELECT ename,SYSDATE-hiredate FROM emp; |
日期函数:
LAST_DAY(date)
返回给定日期所在月的月底日期。
案例:查看当月月底的日期:
1
SELECT LAST_DAY(SYSDATE) FROM DUAL;
ADD_MONTHS(date,n)
对指定日期加上n个月;
若n为负数,则是减去指定的月数。
案例:查看每个员工的转正日期:
1
SELECT ename,ADD_MONTHS(hiredate,3) FROM emp;
MONTHS_BETWEEN(date1,date2)
计算两个指定日期之间相差的月;
案例:查看每个员工入职至今多少个月:
1
SELECT ename, MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;
NEXT_DAY(date,i)
返回给定日期第二天开始一周内指定周几的日期;
i可以是1-7,分别表示周日,周一..周六。
1
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
LEAST、GREATEST
求最小值与最大值;
对于日期而言,最大值为最晚的日期,最小值为最早的日期。
1
2
3
4
5-- 1998/8/6
SELECT LEAST(SYSDATE, TO_DATE('1998-08-06', 'YYYY-MM-DD')) FROM DUAL;
-- 2020/12/1 10:06:51
SELECT GREATEST(SYSDATE, TO_DATE('1998-08-06', 'YYYY-MM-DD')) FROM DUAL;
EXTRACT函数
获取一个日期中指定时间分量的值。
案例:查看今年是哪年:
1
2-- 2020
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;案例:查看1980年入职的员工:
1
2
3SELECT ename, hiredate
FROM emp
WHERE EXTRACT(YEAR FROM hiredate)=1980
空值操作
1 | CREATE TABLE student(id NUMBER(4), name CHAR(20), gender CHAR(1) NOT NULL); |
更新NULL
1 | UPDATE student SET gender = NULL WHERE id=1000; |
判断是否为NULL
判断要用 IS NULL 或 IS NOT NULL
1
DELETE FROM student WHERE gender IS NULL;
NULL的运算
NULL与字符串连接等于什么都没做;
注意:区分于MySQL,MySQL中,NULL与字符串连接结果为NULL;
NULL与数字运算结果还是NULL。
1
SELECT '123' || NULL FROM DUAL;
空值函数
NVL(arg1, arg2)
当arg1为NULL时,函数返回arg2的值,否则返回arg1自身。
相当于MySQL 中的IFNULL()
该函数意义:将NULL值替换为非NULL值
查看每个员工的收入(工资+绩效):
1
SELECT ename, sal, comm, NVL(sal, 0) + NVL(comm, 0) FROM emp;
NVL2(arg1, arg2, arg3)
当arg1不为NULL时,函数返回arg2,若为NULL,则函数返回arg3。
案例:查看每个员工是否有绩效,即:有绩效的显示”有绩效”,为NULL的则显示为”没有绩效”。
1
SELECT ename,comm,NVL2(comm,'有绩效','没有绩效') FROM emp;
练习:
1:查询emp表中数据,列出一列,内容为名字与职位, 显示格式:ename:job
2:查看每个员工职位的字符个数
3:将所有员工的名字以小写形式,与首字母大写形式查询出来,第一列为小写形式,第二列为首字母大写
4:将字符串’aaaaaabaaaaa’中左右两边的a去除
5:显示每个员工的名字,要求显示10位,第一列左对齐效果,第二列 右对齐效果
6:截取字符串’DOCTOR WHO’中的’WHO’
7:查看’DOCTOR WHO’中’WHO’的位置
8:分别查看55.789四舍五入保留小数点后2位,整数位,十位后的数字,显示成三列。
9:分别查看55.789截取后保留小数点后2位,整数位,十位后的数字,显示成三列。
10:查看每名员工工资百位以下的数字?
11:查看每名员工从入职到今天一共多少天,若有小数则向上取整。
12:查看从2008-08-08号到今天为止一共经历了多少天?
13:将每名员工入职时间以例如:”1981年12月3日”的形式显示
14:查看每个员工入职所在月的月底是哪天?
15:查看每名员工转正日期(入职后3个月)
16:查看每名员工入职至今共多少个月?
17:查看从明天开始一周内的周日是哪天?
18:查看82年以后入职的员工的入职日期,82年以前的按照”1982年01月01号”显示。格式都是DD-MON-RR(默认格式)
19:查看每名员工的入职年份?
20:显示每个员工的总收入(工资加奖金),奖金为NULL的只看工资
21:使用NVL2实现20题的需求
22:查看到今天为止自己活了多少天?
三、列别名、LIKE关键字、聚合函数、分组、关联查询、内连接、外链接、自连接
列别名
当一个SELECT子句中包含函数或者表达式时,查询的结果集对应的该字段,就是使用这个函数或者表达式作为字段名,可读性差。为此可以为这样的字段添加别名。
若别名使用双引号,那么别名就可以区分大小写并且包含空格。
1
SELECT ename,sal*12 sal FROM emp;
AND的优先级高于OR
1 | SELECT ename,job,sal FROM emp WHERE sal>1000 AND (job='SALESMAN' OR job='CLERK'); |
LIKE关键字
LIKE用于模糊匹配字符串,它支持两个通配符比较:
- _:表示单一的一个字符;
- %:表示任意个字符(0-多个)。
案例:查看名字第二个字母是A的第四个字母是T的员工。
1
SELECT ename FROM emp WHERE ename LIKE '_A_T%';
IN(list)与NOT IN(list)
判断在列表中或不在列表中,IN和NOT IN常用在子查询的判断中。
案例:查看职位是CLERK或SALESMAN的员工。
1
SELECT ename,job,deptno FROM emp WHERE job IN ('CLERK','SALESMAN');
BETWEEN…AND…
案例:判断在一个范围内,查看工资在1500到3000之间的员工?
1
SELECT ename,job,sal FROM emp WHERE sal BETWEEN 1500 AND 3000;
ANY(list)和ALL(list)
- ANY与ALL是配合>,>=,<,<=使用的
- >ANY(list):大于列表之一;
- >ALL(list):大于列表所有;
- <ANY(list):小于列表之一;
- <ALL(list):小于列表所有。
- 常用在子查询中。
DISTINCT 关键字
去除重复行;
注意distinct效率不高,尽量使用exists关键字代替或者不使用。
案例:查看公司有哪些职位?
1
SELECT DISTINCT job FROM emp;
多字段去重,这几个字段值的组合没有重复行。
1
SELECT DISTINCT job,deptno FROM emp;
排序结果集
ORDER BY子句用来对结果集按照指定的字段排序。
排序有两种方式:
- 升序(ASC):不写默认就是升序;
- 降序(DESC):从大到小,需要单独指定。
ORDER BY子句必须写在DQL的最后一个子句上。
案例:查看公司中工资的排名?
1
2
3SELECT ename,sal,deptno
FROM emp
ORDER BY sal DESC;多字段排序,有优先级,首先按照第一个字段排序,当第一个字段有重复值时才按照第二个字段排序,依次类推。
1
2
3SELECT ename,deptno,sal
FROM emp
ORDER BY deptno DESC,sal DESC;排序时NULL被认作为最大值。
1
2
3SELECT ename,comm
FROM emp
ORDER BY comm DESC;
聚合函数
聚合函数又称为:多行函数,分组函数。
作用是对结果集的指定字段进行统计然后得出一个结果。
案例:查看公司的最高工资与最低工资?
1
SELECT MAX(sal),MIN(sal) FROM emp;
案例:查看公司的平均工资与工资总和?
1
SELECT AVG(sal),SUM(sal) FROM emp;
COUNT函数
COUNT函数是对记录数的统计。
案例:查看公司共多少人?
1
SELECT COUNT(ename) FROM emp;
聚合函数忽略NULL值
1 | SELECT SUM(comm),AVG(comm) FROM emp; |
注意:COUNT()在统计某列的时候会忽略NULL值。
分组
GROUP BY 子句
GROUP BY可以将结果集按照给定字段值一样的记录进行分组;
配合聚合函数,可以对不同的分组分别统计结果。
1
2
3
4
5
6
7SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno;
SELECT MAX(sal),job
FROM emp
GROUP BY job;
多字段分组
这些字段值都一样的记录看做一组。
案例1:同部门,同职位的员工的平均工资?
1
2
3SELECT AVG(sal),deptno,job
FROM emp
GROUP BY deptno,job;案例2:查看每个部门的最低工资是多少?(前提是该部门的最低工资要高于1000)
1
2
3
4
5-- SQL语句会报错:此处不允许使用分组函数
SELECT MIN(sal),deptno
FROM emp
WHERE MIN(sal)>1000
GROUP BY deptno;
HAVING 子句
HAVING子句必须跟在GROUP BY 子句之后;
作用是添加过滤条件来过滤GROUP BY的分组,它可以将不满足条件的分组去除;
HAVING子句可以使用聚合函数作为过滤条件。
案例1:查看平均工资高于2000的部门的最低工资?
1
2
3
4SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;案例2:查看最低工资高于1000的那些职位的平均工资?
1
2
3
4SELECT AVG(sal),job
FROM emp
GROUP BY job
HAVING MIN(sal)>1000;
关联查询
查询数据是从多张表中关联查询一个结果集,关联查询的重点是添加连接条件。
关联条件的作用是告知数据库表与表之间的数据是怎样对应的。
关联查询通常都要添加连接条件,否则会产生笛卡尔积,通常是一个无意义的结果集。
案例:查看每个员工的名字以及其所在部门的名字?
1
2
3SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;当关联查询的表中有同名字段,需要通过表名或表别名来指定该字段所属表。
在关联查询中过滤条件必须与连接条件同时成立。
案例:查看RESEARCH部门的员工信息?
1
2
3
4SELECT e.ename,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.dname='RESEARCH';不加关联条件会产生笛卡尔积。
1
SELECT e.ename,d.dname FROM emp e,dept d;
内连接
内连接也是关联查询的一种。
1
2
3SELECT e.ename,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;关联查询忽略不满足连接条件的记录。
1
2
3
4-- 筛选 dept 的同时会筛选掉有对应关系的 emp 数据。
SELECT e.ename,d.dname
FROM emp e JOIN dept d ON e.deptno=d.deptno
WHERE d.dname='RESEARCH';
外链接
- 外链接在关联查询时还可以将不满足连接条件的记录也查询出来。
外链接分为:
左外连接,右外连接,全外连接。
1
2
3SELECT e.ename,d.dname
FROM emp e LEFT|RIGHT|FULL OUTER JOIN dept d
ON e.deptno=d.deptno;
左外连接
以JOIN左侧表作为驱动表。
驱动表中所有数据都要列出来,那么当该表某条记录不满足连接条件时,那么来自右侧表的字段值全部为NULL。
自连接
当表中的一条记录可以对应当前表的其他记录时,这种设计称为自连接。
案例:查看每个员工以及其上司的名字?
1
2
3
4
5
6
7
8
9-- 写法一:
SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno;
-- 写法二:
SELECT e.ename,m.ename
FROM emp e JOIN emp m
ON e.mgr=m.empno;
作业:
1:查看工资高于2000的员工
2:查看不是”CLERK”职位的员工
3:查看工资在1000-2500之间的员工
4:查看名字是以K结尾的员工
5:查看20,30号部门的员工
6:查看奖金为NULL的员工
7:查看年薪高于20000的员工
8:查看公司共有多少种职位
9:按部门号从小到大排列查看员工
10:查看每个部门的最高,最低,平均工资,和工资总和
11:查看平均工资高于2000的部门的最低薪水
12:查看在NEWYORK工作的员工
13:查看所有员工及所在部门信息,若该员工没有部门,则
部门信息以NULL显示
14:查看ALLEN的上司是谁
15:查看SMITH上司在哪个城市工作?
16:查看平均工资高于2000的那些部门名字以及所在城市?
17:在NEW YORK工作的员工有多少人?
18:在DALLAS工作的员工的平均工资是多少?
19:查看SMITH的上司是谁?他在哪个城市工作?
四、子查询、EXISTS关键字、分页查询、DECODE函数、排序函数、高级分组函数
子查询
子查询是一条查询语句,它是嵌套在其他SQL语句当中的,目的是为了外层查询提供数据的。
案例1:查看谁的工资高于CLARK?
1
2
3SELECT ename,sal
FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK');案例2:查看谁的工资高于公司平均工资?
1
2
3SELECT ename,sal
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);
在DDL中使用子查询
根据一个查询结果集快速构建一张表:
1
2
3
4
5CREATE TABLE employees
AS
SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+); -- 左连接
DML中使用子查询
将SMITH所在部门的员工工资上浮10%
1
2
3UPDATE emp
SET sal=sal*1.1
WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');当子查询为多行单列时,那么在用作判断条件中时要搭配IN、ANY、ALL使用。
案例1:查看与职位是SALESMAN同部门的其他职位员工?
1
2
3SELECT ename,sal,deptno,job
FROM emp
WHERE deptno IN(SELECT deptno FROM emp WHERE job='SALESMAN') AND job <> 'SALESMAN';案例2:查看比职位是SALESMAN和CLERK工资都高的员工信息?
1
2
3SELECT ename,sal
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE job IN('SALESMAN','CLERK'));
EXISTS关键字
EXISTS关键字后面跟一个子查询,当该子查询可以查询出至少一条记录时,EXISTS条件成立。
案例1:查看有员工的部门有哪些?
1
2
3SELECT d.deptno,d.dname,d.loc
FROM dept d
WHERE EXISTS(SELECT * FROM emp e WHERE e.deptno=d.deptno);案例2:查看哪些人是别人的领导?
1
2
3SELECT empno,ename,job,deptno
FROM emp m
WHERE EXISTS(SELECT * FROM emp e WHERE e.mgr=m.empno);案例3:查看部门的最低薪水?(前提是该部门的最低薪水要高于30号部门的最低薪水)
1
2
3
4SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30);
补充:
在FROM子句中使用子查询,通常是将子查询的结果当做一张表看待,基于该查询结果进行二次查询使用。
案例1:查看谁的工资高于其所在部门的平均工资?
1
2
3
4
5
6SELECT e.ename,e.sal,e.deptno
FROM emp e,
(SELECT AVG(sal) avg_sal, deptno
FROM emp
GROUP BY deptno) t
WHERE e.deptno=t.deptno AND e.sal>t.avg_sal
分页查询
分页查询需要两个参数:
- pageSize:每页显示的条目数;
- page:页数。
开始行号 和 结束行号:
- start:(page - 1) * pageSize + 1;
- end:pageSize * page。
分页查询就是将数据分段查询出来,一次只查询数据的一部分。
这样做可以减少系统资源开销,减少数据量可以提高网络传输速度。
分页在不同的数据库中的SQL语句是不同的。
ORACLE中提供了一个伪列:ROWNUM
ROWNUM字段不存在于任何一张表中,但是每张表都可以查询该字段;
该字段的值是结果集中每条记录的行号;
ROWNUM字段的值是动态生成的,伴随查询过程;
只要可以查询出一条记录,ROWNUM就会为该条记录生成行号,从1开始每次递增1。
由于ROWNUM是在查询表的过程中进行编号的,所以在使用ROWNUM对结果集编行号的查询过程中不要使用ROWNUM做大于1以上数字的判断,否则结果集没有任何数据。
1
2
3
4SELECT *
FROM (SELECT ROWNUM rn,empno,ename,sal,deptno FROM emp)
WHERE rn BETWEEN 6 AND 10;
-- 注意:oracle中的子查询结果集可以没有别名,Mysql中必须有别名。输出结果
案例:查看公司工资排名的第6-10名
1
2
3
4
5
6
7
8
9
10
11
12-- 写法一:
SELECT *
FROM(SELECT ROWNUM rn,t.*
FROM(SELECT empno,ename,sal,deptno FROM emp ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10;
-- 写法二:
SELECT *
FROM(SELECT ROWNUM rn,t.*
FROM(SELECT empno,ename,sal,deptno FROM emp ORDER BY sal DESC) t
WHERE ROWNUM <=10)
WHERE rn >=6输出结果
DECODE函数
可以实现分支效果。
1
2
3
4
5
6
7SELECT ename, job, sal,
DECODE(job,
'MANAGER', sal * 1.2,
'ANALYST', sal * 1.1,
'SALESMAN', sal * 1.05,
sal) bonus
FROM emp;输出结果
补充:相当于MySql中的 CASE-WHEN 语句。
案例:将MANAGER与ANALYST看做一组,其他职位看做另一组,分别统计两组人数?
1
2
3
4
5
6
7
8
9
10SELECT COUNT(*),
DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER') TYPE
FROM emp
GROUP BY DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER');输出结果
排序函数
排序函数允许将结果集,按照指定字段分组,在组内按照指定字段排序,然后该函数为每组生成一个行号。
ROW_NUMBER():生成组内连续且唯一的数字。
案例:查看每个部门的工资排名?
1
2
3
4
5
6SELECT ename,deptno,sal,
ROW_NUMBER() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp;输出结果
RANK函数
生成组内不连续也不唯一的数字。
1
2
3
4
5
6SELECT ename,deptno,sal,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp;输出结果
DENSE_RANK函数
生成组内连续但不唯一的数字
1
2
3
4
5
6
7SELECT
ename,deptno,sal,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp;输出结果
高级分组函数
- ROLLUP(a[,b,c…])
GROUP BY ROLLUP(a,b,c)
等同于
1
2
3GROUP BY a,b,c UNION ALL
GROUP BY a,b UNION ALL
GROUP BY a
- CUBE()
CUBU的分组策略为每个参数的组合进行一次分组;
GROUP BY CUBE(a,b,c)
等同于
1
2
3
4
5
6
7GROUP BY a,b,c UNION ALL
GROUP BY a,b UNION ALL
GROUP BY b,c UNION ALL
GROUP BY a,c UNION ALL
GROUP BY a UNION ALL
GROUP BY b UNION ALL
GROUP BY c
- GROUPING SETS()
该函数允许自行指定分组策略,然后将这些分组统计的结果并在一起。函数的每个参数为一种分组方式。
GROUP BY GROUPING SETS (A, B, C)
等同于
1
2
3GROUP BY A UNION ALL
GROUP BY B UNION ALL
GROUP BY C案例1:查看每天与每月的营业额?
1
2
3
4
5
6
7SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY
GROUPING SETS(
(year_id,month_id,day_id),
(year_id,month_id))
ORDER BY year_id,month_id,day_id案例2:统计一下每个国家的平均工资和每个部门的平均工资
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18CREATE TABLE employee
(
name NVARCHAR2(10),
gender NCHAR(1),
country NVARCHAR2(10),
department NVARCHAR2(10),
salary NUMBER(10)
);
INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);
INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);
INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);
INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);
INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);
INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);
SELECT NVL(country, department) TYPE, round(avg(salary), 2)
FROM employee
GROUP BY GROUPING SETS (country, department);输出结果
作业:
1:查看与CLARK相同职位的员工
2:查看低于公司平均工资的员工
3:查看与ALLEN同部门的员工
4:查看平均工资低于20号部门平均工资的部门平均工资
5:查看低于自己所在部门平均工资的员工
6:查看公司工资排名的第1-5名
7:查看CLERK职位的人数和其他职位的总人数各多少?
8:查看每个职位的工资排名
9:查看每个职位的工资排名,若工资一致,排名一致
10:查看每个职位的工资排名,若工资一致,排名一致,不跳名次。
11:分别查看:同部门同职位,同职位,以及所有员工的工资总和
12:分别查看:同部门同职位,同职位,同部门以及所有员工的工资总和
13:分别查看同部门同职位和同职位的员工的工资总和
14:查看公司最高工资的员工的名字以及所在部门名称
15:查看每个部门的最高工资的员工名字
16:查看有下属的员工信息
五、视图、序列、约束
视图
- 视图也是数据库对象之一;
- 在SQL语句中体现的角色与表一致,但视图只是对应一个查询语句的结果集。
创建视图:
1 | CREATE VIEW v_emp_10 |
视图也可以查看结构
1 | DESC v_emp_10; |
视图分类:
- 视图根据对应的SQL语句不通,分为:简单视图,复杂视图,连接视图;
- 简单视图:当对应的子查询不含有函数,表达式,分组,去重,关联查询的视图称为简单视图;
- 复杂视图:简单视图相反就是复杂视图;
- 连接视图:连接视图算作复杂视图的一种,连接视图指子查询使用了关联查询。
视图别名
视图对应的子查询的查询字段可以使用别名,那么该字段的名字就是这个别名。
若字段含有函数或表达式,那么该字段必须指定别名。
举个栗子:
1
2
3
4
5CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal*12 sal,deptno
FROM emp
WHERE deptno=10
对视图进行DML操作
- 对视图进行DML就是对视图数据来源的基础表进行的操作;
- 只能对简单视图进行DML操作,复杂视图不可以;
- 对视图中不包含的数据进行DML操作会污染基础表数据。
视图检查
可以为视图添加检查选项,来保证对视图进行DML操作时不会对基表数据污染。
WITH CHECK OPTION
当视图添加了检查选项后,视图要求对视图中数据进行DML操作后,视图必须对该记录可见,否则不允许操作。
举个栗子:
1
2
3
4
5
6CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10
WITH CHECK OPTION;
视图只读
为视图添加只读选项,当一个视图添加了只读选项后,该视图不能进行DML操作。
WITH READ ONLY
举个栗子:
1
2
3
4
5
6CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10
WITH READ ONLY;
补充:查看数据字典,有助于了解曾经创建过的数据库对象。
1
2
3 SELECT * FROM user_objects WHERE object_name LIKE '%_FANCQ';
SELECT * FROM user_views;
SELECT * FROM user_tables;
复杂视图
复杂视图不能进行DML操作。
案例1:创建一个部门工资信息的视图:
1
2
3
4
5
6
7
8
9
10CREATE VIEW v_dept_sal
AS
SELECT MIN(e.sal) min_sal,
MAX(e.sal) max_sal,
AVG(e.sal) avg_sal,
SUM(e.sal) sum_Sal,
d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname;案例2:查看谁的工资高于自己所在部门平均工资?
1
2
3
4SELECT e.ename,e.sal,e.deptno
FROM emp e,v_dept_sal v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal;
删除视图
- 删除视图不会影响基表数据。
1 | DROP VIEW v_emp_10; |
序列
- 序列也是数据库对象之一;
- 作用是根据指定的规则生成一系列数字;
- 一般用于为表的每一条记录的主键字段提供值。
创建序列
1 | CREATE SEQUENCE seq_emp_id |
序列支持两个伪列:
NEXTVAL:获取序列下一个数字,序列会根据序列最后生成的数字加上步进来得到。
NEXTVAL会导致序列发生步进,序列是不能回退的。
CURRVAL:获取序列最后一次生成的数字。需要注意的是,新创建的序列必须在使用NEXTVAL生成一个数字后才可以使用CURRVAL。
1
2SELECT seq_emp_id.NEXTVAL FROM DUAL;
SELECT seq_emp_id.CURRVAL FROM DUAL;
删除序列
1 | DROP SEQUENCE seq_emp_id; |
索引
- 索引是数据库对象之一;
- 索引是加快查询效率的机制;
- 索引的建立以及应用是数据库自行完成的。
约束
- 唯一性约束
唯一性约束要求该字段每条记录的值不能重复,NULL除外。
举个栗子:
1
2
3
4
5
6
7
8CREATE TABLE employees1 (
eid NUMBER(6) UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE,
CONSTRAINT employees1_email_uk UNIQUE(email)
);
- 主键约束
主键约束要求该字段的值为空且唯一;
主键约束只能在一张表的一个字段上建立。
主键:使用该字段的值可以唯一定位表中的一条记录。
1
2
3
4
5
6
7CREATE TABLE employees2 (
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE
);
作业:
1:创建一个视图,包含20号部门的员工信息,
字段:empno,ename,sal,job,deptno
2:创建一个序列seq_emp_no,从10开始,步进为10
3:编写SQL语句查看seq_emp_no序列的下一个数字
4:编写SQL语句查看seq_emp_no序列的当前数字
5:为emp表的ename字段添加索引:idx_emp_ename
6:为emp表的LOWER(ename)字段添加索引:
idx_emp_lower_ename
7:为emp表的sal,comm添加多列索引
8:创建myemployee表,字段:
id NUMBER(4) , id作为主键
nameVARCHAR2(20), name要求不能为空
birthday DATE,
telephone VARCHAR2(11) telephone需要唯一
scoreNUMBER(9,2) score值必须>=0
六、附录:
本文中的sql基础库如下:
1 | create table EMP( |
- 本文作者: ACG kaka
- 本文链接: http://acgkaka.github.io/2020/11/10/7Java入门(七)Oracle 数据库/
- 版权声明: 文章均为个人整理,如有侵权,请联系删除。