目录
📢:哈喽~☀️欢迎加入程序🐒大家庭,快点开始✒️自己的黑客帝国吧 ~🌊🌊
内容简述:数据库简介、SQL基本操作、SQL分类、DML、DDL、DQL、筛选、排序、分组、聚合、关联、笛卡儿积、表之间的对应关系、权限管理、视图、索引、约束、事务。
写在前面:文章内容为个人整理,分享给大家学习使用,请勿商用哟~
- 文中查询、操作的数据库结构,在 六、附录 中可查看哟~
查看表结构的SQL:
-- 查看当前用户下的所有表及表注释
SELECT * FROM USER_TAB_COMMENTS;
-- 查看某张表的所有字段注释
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='';
-- 查看某张表的某个字段的注释
SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='' AND COLUMNE_NAME='';
-- 查看某张表的所有字段的详细信息(字段类型,字段长度,字段精度,默认值等,除注释外)
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME='';
-- 查看某张表的所有字段类型
DESC XXX;
一、SQL语句、DDL语句、非空约束、修改表名、修改表结构、DML语句
Hello World
-- 打印
SELECT "Hello World" FROM dual;
SQL语句
-
SQL:结构化查询语句,是用来操作数据库的语言;
-
所有的数据库都支持标准的SQL语句。
SQL语句包含:
- DDL,DML,TCL,DQL,DCL这几类语句
DDL语句:
- DDL语句是用于增删改数据库对象的
数据库对象:
- 表,视图,索引,序列
创建表:
CREATE TABLE employee(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1),
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptno NUMBER(2)
)
查看表结构:
DESC employee;
删除表:
DROP TABLE employee;
大小写:
- SQL语句是不区分大小写的,但是字符串的值(直接量)是区分大小写的,字符串的直接量是使用单引号括起来的。
默认值:
-
数据库中所有数据类型的默认值都是NULL,在创建表时,可以使用DEFAULT为字段单独指定默认值。
CREATE 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。
CREATE 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;
修改表 - 修改表名:
-- 方式一:
RENAME old_name TO new_name;
-- 方式二:
ALTER TABLE old_name TO new_name;
实践
-- 将employee表改名为myemp
RENAME employee TO myemp;
DESC myemp;
修改表结构:
-
添加新字段
- 向表myemp中添加字段hiredate。
ALTER TABLE myemp ADD( hiredate DATE );
-
删除表中现有字段
- 将myemp表中的hiredate字段删除。
-- 将myemp表中的hiredate字段删除 ALTER TABLE myemp DROP(hiredate);
-
修改表中现有字段
- 修改字段可以修改字段的类型,长度,默认值
ALTER TABLE myemp MODIFY( job VARCHAR2(40) DEFAULT 'CLERK');
注意:若表中已经存在数据,那么修改字段的时候尽量不修改类型,修改长度尽量不要缩小,否则可能导致修改失败。
DML语句
- DML语句用来对表中数据进行相关操作,包括:增,删,改。
-
插入数据
INSERT INTO myemp(id,name,salary,deptno) VALUES (1,'JACK',3000,10);
- 插入数据时,忽略字段名则是全列插入;
INSERT INTO myemp VALUES (2,'ROSE','F',SYSDATE,5000,'MANAGER',20);
- 插入日期时,使用TO_DATE函数
INSERT INTO myemp (id,name,birth) VALUES(3,'JACKSON',TO_DATE('1992-08-02','YYYY-MM-DD'));
-
修改数据
- UPDATE语句用于修改表中数据,需要使用WHERE添加条件以修改满足条件的记录;
- 若不添加WHERE则是全表所有数据修改。
-- 将ROSE的工资改为6000,部门号改为30 UPDATE myemp SET salary=6000,deptno=30 WHERE name='ROSE'
-
删除表中现有字段
- DELETE语句用于删除表中记录,通常需要使用WHERE添加条件来删除满足条件的记录;
- 若不添加WHERE是清空表操作。
DELETE FROM myemp WHERE name='ROSE';
二、DQL语句、字符串函数、数字函数、日期类型、日期函数、空值操作、空值函数
DQL语句
- DQL语句用于查询数据库中的数据
DQL必须包含两个子句:
-
SELECT:SELECT子句用来指定要查询的字段,可以是表中的字段,函数和表达式;
-
FROM:FROM子句用来指定数据来源的表;
-- 查看emp表中的数据 SELECT * FROM emp; -- 查看ename,job,sal,deptno SELECT ename,job,sal,deptno FROM emp;
-
DQL中也可以使用WHERE子句来添加过滤条件,这样只会将满足条件的记录查询出来;
SELECT ename,job,sal,deptno FROM emp WHERE deptno=20
-
SELECT子句中也可使用函数或表达式。
-- 查看公司每个员工的年薪是多少? SELECT ename,sal,sal*12 FROM emp;
字符串函数
-
CONCAT(char1,char2)
- 将两个参数字符串连接在一起返回;
-- 示例1 SELECT CONCAT(ename,sal) FROM emp; -- 示例2 SELECT CONCAT(CONCAT(ename,','),sal) FROM emp;
- "||"可以连接字符串。
SELECT ename||','||sal FROM emp;
-
LENGTH(char)
- 返回指定字符串的长度
SELECT ename, LENGTH(ename) FROM emp;
补充:dual(伪表)
伪表不是一张真是存在的表,当查询的内容与任何表数据无关时,可以使用伪表。
SELECT SYSDATE FROM dual;
-
UPPER、LOWER、INITCAP
- 将字符串转换为大写,小写,首字母大写其余小写;
SELECT UPPER('helloworld'), LOWER('HELLOWORLD'), INITCAP('HELLO WORLD') FROM dual; -- 打印信息 'HELLOWORLD','helloworld','Hello World'
- 可以作为查询条件。
SELECT ename,sal,job,deptno FROM emp WHERE ename=UPPER('scott');
-
TRIM、LTRIM、RTRIM
- 去除字符串两端的指定字符
SELECT TRIM('e' FROM 'eeeliteee'), LTRIM('eddsdsesliteee','esd'), RTRIM('eeeliteddsdses','esd') FROM dual; -- 打印信息 'lit','liteee','eeelit'
-
LPAD、RPAD 补位函数
- 将指定字符串显示指定长度,当不足时补充若干个指定字符以达到该长度。
SELECT ename,RPAD(sal,5,'$') FROM emp;
-
SUBSTR(char, m[, n])
- 截取指定字符串,从m处开始连续截取n个字符;
- n若不指定或超过实际可截取的长度,则都是截取到字符串末尾;
- m若为负数,则是从倒数位置开始截取,数据库中下标都从1开始。
SELECT SUBSTR('thinking in java',-7,2) FROM dual;
-
INSTR(char1, char2[, m[, n]])
- 查看char2在char1中的位置;
- m为从哪里开始查找,不写默认为1;
- n为第几次出现,不写默认为1。
SELECT INSTR('thinking in java','in',4,2) FROM dual;
数字函数
-
ROUND(n,m)
- 四舍五入保留n小数点后m位;
- 若m不写或0则表示保留到个位;
- 若是负数则是保留到十位以上的数字。
-- 保留两位小数 SELECT ROUND(45.678, 2) FROM DUAL; -- 45.68 -- 保留整数 SELECT ROUND(45.678, 0) FROM DUAL; -- 46 -- 保留到百位 SELECT ROUND(55.678, -2) FROM DUAL; -- 100
-
**TRUNC()**函数
- 与ROUND参数意义一致,作用是截取数字。
-- 截取两位小数 SELECT TRUNC(45.678, 2) FROM DUAL; -- 45.67 -- 截取整数 SELECT TRUNC(45.678, 0) FROM DUAL; -- 45 -- 截取到十分位 SELECT TRUNC(55.678, -1) FROM DUAL; -- 50
-
**MOD(m,n)**函数
- 求余数,n为0则直接返回m。
SELECT MOD(4321.123,1000) FROM DUAL;
-
CEIL、FLOOR
- 向上取整、向下取整。
SELECT CEIL(45.678) FROM DUAL; -- 46 SELECT FLOOR(45.678) FROM DUAL; -- 45
日期类型
-
两个常用关键字:
- SYSDATE:对应数据库一个内置函数,返回一个DATE类型数据,表示当前系统时间;
- SYSTIMESTAMP:返回一个时间戳类型的当前系统时间。
-- 获取系统日期 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类型值。
-- 1992/8/3 15:22:33 SELECT TO_DATE('1992-08-03 15:22:33', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
- 在日期格式字符串中凡不是英文,符号的其他字符都需要使用双引号括起来。
-- 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按照给定的日期格式转换为字符串。
-- 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;
日期类型是可以计算的
-
对一个日期加减一个数字等同于加减天数;
-
两个日期做减法,差为相差的天数。
查看明天的日期?
SELECT SYSDATE+1 FROM DUAL;
查看每个员工入职至今多少天了?
SELECT ename,SYSDATE-hiredate FROM emp;
日期函数:
-
LAST_DAY(date)
-
返回给定日期所在月的月底日期。
-
案例:查看当月月底的日期:
SELECT LAST_DAY(SYSDATE) FROM DUAL;
-
-
ADD_MONTHS(date,n)
-
对指定日期加上n个月;
-
若n为负数,则是减去指定的月数。
-
案例:查看每个员工的转正日期:
SELECT ename,ADD_MONTHS(hiredate,3) FROM emp;
-
-
MONTHS_BETWEEN(date1,date2)
-
计算两个指定日期之间相差的月;
-
案例:查看每个员工入职至今多少个月:
SELECT ename, MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;
-
-
NEXT_DAY(date,i)
-
返回给定日期第二天开始一周内指定周几的日期;
-
i可以是1-7,分别表示周日,周一…周六。
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
-
-
LEAST、GREATEST
-
求最小值与最大值;
-
对于日期而言,最大值为最晚的日期,最小值为最早的日期。
-- 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函数
-
获取一个日期中指定时间分量的值。
-
案例:查看今年是哪年:
-- 2020 SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
-
案例:查看1980年入职的员工:
SELECT ename, hiredate FROM emp WHERE EXTRACT(YEAR FROM hiredate)=1980
-
空值操作
CREATE TABLE student(id NUMBER(4), name CHAR(20), gender CHAR(1) NOT NULL);
INSERT INTO student VALUES(1000, '李莫愁', 'F');
INSERT INTO student VALUES(1001, '林平之', NULL);
INSERT INTO student(id, name) VALUES(1002, '张无忌');
更新NULL
UPDATE student SET gender = NULL WHERE id=1000;
判断是否为NULL
-
判断要用 IS NULL 或 IS NOT NULL
DELETE FROM student WHERE gender IS NULL;
NULL的运算
-
NULL与字符串连接等于什么都没做;
注意:区分于MySQL,MySQL中,NULL与字符串连接结果为NULL;
-
NULL与数字运算结果还是NULL。
SELECT '123' || NULL FROM DUAL;
空值函数
-
NVL(arg1, arg2)
-
当arg1为NULL时,函数返回arg2的值,否则返回arg1自身。
相当于MySQL 中的IFNULL()
-
该函数意义:将NULL值替换为非NULL值
-
查看每个员工的收入(工资+绩效):
SELECT ename, sal, comm, NVL(sal, 0) + NVL(comm, 0) FROM emp;
-
-
NVL2(arg1, arg2, arg3)
-
当arg1不为NULL时,函数返回arg2,若为NULL,则函数返回arg3。
-
案例:查看每个员工是否有绩效,即:有绩效的显示"有绩效",为NULL的则显示为"没有绩效"。
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子句中包含函数或者表达式时,查询的结果集对应的该字段,就是使用这个函数或者表达式作为字段名,可读性差。为此可以为这样的字段添加别名。
-
若别名使用双引号,那么别名就可以区分大小写并且包含空格。
SELECT ename,sal*12 sal FROM emp;
AND的优先级高于OR
SELECT ename,job,sal FROM emp WHERE sal>1000 AND (job='SALESMAN' OR job='CLERK');
LIKE关键字
-
LIKE用于模糊匹配字符串,它支持两个通配符比较:
- _:表示单一的一个字符;
- %:表示任意个字符(0-多个)。
-
案例:查看名字第二个字母是A的第四个字母是T的员工。
SELECT ename FROM emp WHERE ename LIKE '_A_T%';
IN(list)与NOT IN(list)
-
判断在列表中或不在列表中,IN和NOT IN常用在子查询的判断中。
-
案例:查看职位是CLERK或SALESMAN的员工。
SELECT ename,job,deptno FROM emp WHERE job IN ('CLERK','SALESMAN');
BETWEEN…AND…
-
案例:判断在一个范围内,查看工资在1500到3000之间的员工?
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关键字代替或者不使用。
-
案例:查看公司有哪些职位?
SELECT DISTINCT job FROM emp;
-
多字段去重,这几个字段值的组合没有重复行。
SELECT DISTINCT job,deptno FROM emp;
排序结果集
-
ORDER BY子句用来对结果集按照指定的字段排序。
-
排序有两种方式:
- 升序(ASC):不写默认就是升序;
- 降序(DESC):从大到小,需要单独指定。
-
ORDER BY子句必须写在DQL的最后一个子句上。
-
案例:查看公司中工资的排名?
SELECT ename,sal,deptno FROM emp ORDER BY sal DESC;
-
多字段排序,有优先级,首先按照第一个字段排序,当第一个字段有重复值时才按照第二个字段排序,依次类推。
SELECT ename,deptno,sal FROM emp ORDER BY deptno DESC,sal DESC;
-
排序时NULL被认作为最大值。
SELECT ename,comm FROM emp ORDER BY comm DESC;
聚合函数
-
聚合函数又称为:多行函数,分组函数。
-
作用是对结果集的指定字段进行统计然后得出一个结果。
-
案例:查看公司的最高工资与最低工资?
SELECT MAX(sal),MIN(sal) FROM emp;
-
案例:查看公司的平均工资与工资总和?
SELECT AVG(sal),SUM(sal) FROM emp;
COUNT函数
-
COUNT函数是对记录数的统计。
-
案例:查看公司共多少人?
SELECT COUNT(ename) FROM emp;
聚合函数忽略NULL值
SELECT SUM(comm),AVG(comm) FROM emp;
SELECT AVG(NVL(comm,0)) FROM emp;
注意:COUNT()在统计某列的时候会忽略NULL值。
分组
GROUP BY 子句
-
GROUP BY可以将结果集按照给定字段值一样的记录进行分组;
-
配合聚合函数,可以对不同的分组分别统计结果。
SELECT AVG(sal),deptno FROM emp GROUP BY deptno; SELECT MAX(sal),job FROM emp GROUP BY job;
多字段分组
-
这些字段值都一样的记录看做一组。
-
案例1:同部门,同职位的员工的平均工资?
SELECT AVG(sal),deptno,job FROM emp GROUP BY deptno,job;
-
案例2:查看每个部门的最低工资是多少?(前提是该部门的最低工资要高于1000)
-- SQL语句会报错:此处不允许使用分组函数 SELECT MIN(sal),deptno FROM emp WHERE MIN(sal)>1000 GROUP BY deptno;
HAVING 子句
-
HAVING子句必须跟在GROUP BY 子句之后;
-
作用是添加过滤条件来过滤GROUP BY的分组,它可以将不满足条件的分组去除;
-
HAVING子句可以使用聚合函数作为过滤条件。
-
案例1:查看平均工资高于2000的部门的最低工资?
SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000;
-
案例2:查看最低工资高于1000的那些职位的平均工资?
SELECT AVG(sal),job FROM emp GROUP BY job HAVING MIN(sal)>1000;
关联查询
-
查询数据是从多张表中关联查询一个结果集,关联查询的重点是添加连接条件。
-
关联条件的作用是告知数据库表与表之间的数据是怎样对应的。
-
关联查询通常都要添加连接条件,否则会产生笛卡尔积,通常是一个无意义的结果集。
-
案例:查看每个员工的名字以及其所在部门的名字?
SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
-
当关联查询的表中有同名字段,需要通过表名或表别名来指定该字段所属表。
-
在关联查询中过滤条件必须与连接条件同时成立。
-
案例:查看RESEARCH部门的员工信息?
SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname='RESEARCH';
-
不加关联条件会产生笛卡尔积。
SELECT e.ename,d.dname FROM emp e,dept d;
内连接
-
内连接也是关联查询的一种。
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
-
关联查询忽略不满足连接条件的记录。
-- 筛选 dept 的同时会筛选掉有对应关系的 emp 数据。 SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE d.dname='RESEARCH';
外链接
- 外链接在关联查询时还可以将不满足连接条件的记录也查询出来。
外链接分为:
-
左外连接,右外连接,全外连接。
SELECT e.ename,d.dname FROM emp e LEFT|RIGHT|FULL OUTER JOIN dept d ON e.deptno=d.deptno;
左外连接
-
以JOIN左侧表作为驱动表。
-
驱动表中所有数据都要列出来,那么当该表某条记录不满足连接条件时,那么来自右侧表的字段值全部为NULL。
自连接
-
当表中的一条记录可以对应当前表的其他记录时,这种设计称为自连接。
-
案例:查看每个员工以及其上司的名字?
-- 写法一: 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?
SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK');
-
案例2:查看谁的工资高于公司平均工资?
SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);
在DDL中使用子查询
-
根据一个查询结果集快速构建一张表:
CREATE 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%
UPDATE emp SET sal=sal*1.1 WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');
-
当子查询为多行单列时,那么在用作判断条件中时要搭配IN、ANY、ALL使用。
-
案例1:查看与职位是SALESMAN同部门的其他职位员工?
SELECT ename,sal,deptno,job FROM emp WHERE deptno IN(SELECT deptno FROM emp WHERE job='SALESMAN') AND job <> 'SALESMAN';
-
案例2:查看比职位是SALESMAN和CLERK工资都高的员工信息?
SELECT ename,sal FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE job IN('SALESMAN','CLERK'));
EXISTS关键字
-
EXISTS关键字后面跟一个子查询,当该子查询可以查询出至少一条记录时,EXISTS条件成立。
-
案例1:查看有员工的部门有哪些?
SELECT d.deptno,d.dname,d.loc FROM dept d WHERE EXISTS(SELECT * FROM emp e WHERE e.deptno=d.deptno);
-
案例2:查看哪些人是别人的领导?
SELECT empno,ename,job,deptno FROM emp m WHERE EXISTS(SELECT * FROM emp e WHERE e.mgr=m.empno);
-
案例3:查看部门的最低薪水?(前提是该部门的最低薪水要高于30号部门的最低薪水)
SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30);
补充:
-
在FROM子句中使用子查询,通常是将子查询的结果当做一张表看待,基于该查询结果进行二次查询使用。
-
案例1:查看谁的工资高于其所在部门的平均工资?
SELECT 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以上数字的判断,否则结果集没有任何数据。
SELECT * FROM (SELECT ROWNUM rn,empno,ename,sal,deptno FROM emp) WHERE rn BETWEEN 6 AND 10; -- 注意:oracle中的子查询结果集可以没有别名,Mysql中必须有别名。
输出结果
-
案例:查看公司工资排名的第6-10名
-- 写法一: 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函数
-
可以实现分支效果。
SELECT 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看做一组,其他职位看做另一组,分别统计两组人数?
SELECT COUNT(*), DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER') TYPE FROM emp GROUP BY DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER');
输出结果
排序函数
-
排序函数允许将结果集,按照指定字段分组,在组内按照指定字段排序,然后该函数为每组生成一个行号。
-
ROW_NUMBER():生成组内连续且唯一的数字。
-
案例:查看每个部门的工资排名?
SELECT ename,deptno,sal, ROW_NUMBER() OVER( PARTITION BY deptno ORDER BY sal DESC ) rank FROM emp;
输出结果
RANK函数
-
生成组内不连续也不唯一的数字。
SELECT ename,deptno,sal, RANK() OVER( PARTITION BY deptno ORDER BY sal DESC ) rank FROM emp;
输出结果
DENSE_RANK函数
-
生成组内连续但不唯一的数字
SELECT 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)
等同于
GROUP BY a,b,c UNION ALL GROUP BY a,b UNION ALL GROUP BY a
- CUBE()
-
CUBU的分组策略为每个参数的组合进行一次分组;
-
GROUP BY CUBE(a,b,c)
等同于
GROUP 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)
等同于
GROUP BY A UNION ALL GROUP BY B UNION ALL GROUP BY C
-
案例1:查看每天与每月的营业额?
SELECT 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:统计一下每个国家的平均工资和每个部门的平均工资
CREATE 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语句中体现的角色与表一致,但视图只是对应一个查询语句的结果集。
创建视图:
CREATE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = 10;
视图也可以查看结构
DESC v_emp_10;
视图分类:
- 视图根据对应的SQL语句不通,分为:简单视图,复杂视图,连接视图;
- 简单视图:当对应的子查询不含有函数,表达式,分组,去重,关联查询的视图称为简单视图;
- 复杂视图:简单视图相反就是复杂视图;
- 连接视图:连接视图算作复杂视图的一种,连接视图指子查询使用了关联查询。
视图别名
-
视图对应的子查询的查询字段可以使用别名,那么该字段的名字就是这个别名。
-
若字段含有函数或表达式,那么该字段必须指定别名。
-
举个栗子:
CREATE 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操作后,视图必须对该记录可见,否则不允许操作。
-
举个栗子:
CREATE OR REPLACE VIEW v_emp_10 AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10 WITH CHECK OPTION;
视图只读
-
为视图添加只读选项,当一个视图添加了只读选项后,该视图不能进行DML操作。
-
WITH READ ONLY
-
举个栗子:
CREATE OR REPLACE VIEW v_emp_10 AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10 WITH READ ONLY;
补充:查看数据字典,有助于了解曾经创建过的数据库对象。
SELECT * FROM user_objects WHERE object_name LIKE '%_FANCQ'; SELECT * FROM user_views; SELECT * FROM user_tables;
复杂视图
-
复杂视图不能进行DML操作。
-
案例1:创建一个部门工资信息的视图:
CREATE 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:查看谁的工资高于自己所在部门平均工资?
SELECT e.ename,e.sal,e.deptno FROM emp e,v_dept_sal v WHERE e.deptno=v.deptno AND e.sal>v.avg_sal;
删除视图
- 删除视图不会影响基表数据。
DROP VIEW v_emp_10;
序列
- 序列也是数据库对象之一;
- 作用是根据指定的规则生成一系列数字;
- 一般用于为表的每一条记录的主键字段提供值。
创建序列
CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1;
序列支持两个伪列:
-
NEXTVAL:获取序列下一个数字,序列会根据序列最后生成的数字加上步进来得到。
NEXTVAL会导致序列发生步进,序列是不能回退的。
-
CURRVAL:获取序列最后一次生成的数字。需要注意的是,新创建的序列必须在使用NEXTVAL生成一个数字后才可以使用CURRVAL。
SELECT seq_emp_id.NEXTVAL FROM DUAL; SELECT seq_emp_id.CURRVAL FROM DUAL;
删除序列
DROP SEQUENCE seq_emp_id;
索引
- 索引是数据库对象之一;
- 索引是加快查询效率的机制;
- 索引的建立以及应用是数据库自行完成的。
约束
- 唯一性约束
-
唯一性约束要求该字段每条记录的值不能重复,NULL除外。
-
举个栗子:
CREATE TABLE employees1 ( eid NUMBER(6) UNIQUE, name VARCHAR2(30), email VARCHAR2(50), salary NUMBER(7, 2), hiredate DATE, CONSTRAINT employees1_email_uk UNIQUE(email) );
- 主键约束
-
主键约束要求该字段的值为空且唯一;
-
主键约束只能在一张表的一个字段上建立。
-
主键:使用该字段的值可以唯一定位表中的一条记录。
CREATE 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基础库如下:
create table EMP(
EMPNO NUMBER(4) primary key,
ENAME varchar(10) not null,
JOB varchar(9),
MGR NUMBER(4),
HIREdate date,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(4)
);
create table Dept(
DEPTNO NUMBER(4) primary key,
DNAME varchar(14) not null unique,
LOC varchar(13)
);
create table SALGRADE(
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER
);
insert into SALGRADE VALUES (1,700,1200);
insert into SALGRADE VALUES (2,1201,1400);
insert into SALGRADE VALUES (3,1401,2000);
insert into SALGRADE VALUES (4,2001,3000);
insert into SALGRADE VALUES (5,3001,9999);
insert into Dept VALUES (10,'ACCOUNTING','NEW YORK');
insert into Dept VALUES (20,'RESEARCH','DALLAS');
insert into Dept VALUES (30,'SALES','CHICAGO');
insert into Dept VALUES (40,'OPERATIONS','BOSTON');
SELECT * FROM DEPT;
insert into EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-YYYY'),800,null,20);
insert into EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-YYYY'),1600,300,30);
insert into EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-YYYY'),1250,500,30);
insert into EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-YYYY'),2975,NULL,20);
insert into EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-YYYY'),1250,1400,30);
insert into EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-YYYY'),2850,NULL,30);
insert into EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-YYYY'),2450,NULL,10);
insert into EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-YYYY'),5000,NULL,10);
insert into EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-YYYY'),1500,0,30);
insert into EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-YYYY'),950,NULL,30);
insert into EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-YYYY'),3000,NULL,20);
insert into EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-YYYY'),1300,NULL,10);
commit;