SQL语句复习【专题一】
--创建用户 scott 并设置密码为 tiger
create user scott identified by tiger
--用户刚刚创建没有任何的权限,连登录的权限都没有
--给用户授予权限。
--角色:一个角色是一个权限的集合。
--常用的角色:connect Resource。
grant connect, resource to scott
--给scott 导入4张表。
--复制scott.sql中的内容,粘贴到一个命令窗口。
--BONUS:奖金表: ename job sal comm
select * from bonus
--DEPT:部门表 deptno dname loc
select * from dept
--EMP:员工表 empno ename job mgr hriedate sal comm deptno
select * from emp
--SALGRADE :工资登记表 grade losal hisal
select * from salgrade
测试数据库:Oracle-XE
可视化工具:PLSQL Developer
建议:复制到notepad++进行查看效果更加
测试数据表SQL:scott.sql
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 prompt PL/SQL Developer import file 2 prompt Created on 2017Äê12ÔÂ24ÈÕ by Administrator 3 set feedback off 4 set define off 5 prompt Creating BONUS... 6 create table BONUS 7 ( 8 ENAME VARCHAR2(10), 9 JOB VARCHAR2(9), 10 SAL NUMBER, 11 COMM NUMBER 12 ) 13 tablespace USERS 14 pctfree 10 15 initrans 1 16 maxtrans 255 17 storage 18 ( 19 initial 64K 20 next 1M 21 minextents 1 22 maxextents unlimited 23 ); 24 25 prompt Creating DEPT... 26 create table DEPT 27 ( 28 DEPTNO NUMBER(2) not null, 29 DNAME VARCHAR2(14), 30 LOC VARCHAR2(13) 31 ) 32 tablespace USERS 33 pctfree 10 34 initrans 1 35 maxtrans 255 36 storage 37 ( 38 initial 64K 39 next 1M 40 minextents 1 41 maxextents unlimited 42 ); 43 alter table DEPT 44 add constraint PK_DEPT primary key (DEPTNO) 45 using index 46 tablespace USERS 47 pctfree 10 48 initrans 2 49 maxtrans 255 50 storage 51 ( 52 initial 64K 53 next 1M 54 minextents 1 55 maxextents unlimited 56 ); 57 58 prompt Creating EMP... 59 create table EMP 60 ( 61 EMPNO NUMBER(4) not null, 62 ENAME VARCHAR2(10), 63 JOB VARCHAR2(9), 64 MGR NUMBER(4), 65 HIREDATE DATE, 66 SAL NUMBER(7,2), 67 COMM NUMBER(7,2), 68 DEPTNO NUMBER(2) 69 ) 70 tablespace USERS 71 pctfree 10 72 initrans 1 73 maxtrans 255 74 storage 75 ( 76 initial 64K 77 next 1M 78 minextents 1 79 maxextents unlimited 80 ); 81 alter table EMP 82 add constraint PK_EMP primary key (EMPNO) 83 using index 84 tablespace USERS 85 pctfree 10 86 initrans 2 87 maxtrans 255 88 storage 89 ( 90 initial 64K 91 next 1M 92 minextents 1 93 maxextents unlimited 94 ); 95 alter table EMP 96 add constraint FK_DEPTNO foreign key (DEPTNO) 97 references DEPT (DEPTNO); 98 create bitmap index INDEX_EMP_JOB on EMP (JOB) 99 tablespace USERS 100 pctfree 10 101 initrans 2 102 maxtrans 255 103 storage 104 ( 105 initial 64K 106 next 1M 107 minextents 1 108 maxextents unlimited 109 ); 110 create index INDEX_EMP_SAL on EMP (SAL) 111 tablespace USERS 112 pctfree 10 113 initrans 2 114 maxtrans 255 115 storage 116 ( 117 initial 64K 118 next 1M 119 minextents 1 120 maxextents unlimited 121 ); 122 create index INDEX_EMP_SAL_JOB on EMP (SAL DESC, JOB) 123 tablespace USERS 124 pctfree 10 125 initrans 2 126 maxtrans 255 127 storage 128 ( 129 initial 64K 130 next 1M 131 minextents 1 132 maxextents unlimited 133 ); 134 135 prompt Creating SALGRADE... 136 create table SALGRADE 137 ( 138 GRADE NUMBER, 139 LOSAL NUMBER, 140 HISAL NUMBER 141 ) 142 tablespace USERS 143 pctfree 10 144 initrans 1 145 maxtrans 255 146 storage 147 ( 148 initial 64K 149 next 1M 150 minextents 1 151 maxextents unlimited 152 ); 153 154 prompt Disabling triggers for BONUS... 155 alter table BONUS disable all triggers; 156 prompt Disabling triggers for DEPT... 157 alter table DEPT disable all triggers; 158 prompt Disabling triggers for EMP... 159 alter table EMP disable all triggers; 160 prompt Disabling triggers for SALGRADE... 161 alter table SALGRADE disable all triggers; 162 prompt Disabling foreign key constraints for EMP... 163 alter table EMP disable constraint FK_DEPTNO; 164 prompt Deleting SALGRADE... 165 delete from SALGRADE; 166 commit; 167 prompt Deleting EMP... 168 delete from EMP; 169 commit; 170 prompt Deleting DEPT... 171 delete from DEPT; 172 commit; 173 prompt Deleting BONUS... 174 delete from BONUS; 175 commit; 176 prompt Loading BONUS... 177 prompt Table is empty 178 prompt Loading DEPT... 179 insert into DEPT (DEPTNO, DNAME, LOC) 180 values (10, 'ACCOUNTING', 'NEW YORK'); 181 insert into DEPT (DEPTNO, DNAME, LOC) 182 values (20, 'RESEARCH', 'DALLAS'); 183 insert into DEPT (DEPTNO, DNAME, LOC) 184 values (30, 'SALES', 'CHICAGO'); 185 insert into DEPT (DEPTNO, DNAME, LOC) 186 values (40, 'OPERATIONS', 'BOSTON'); 187 commit; 188 prompt 4 records loaded 189 prompt Loading EMP... 190 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 191 values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20); 192 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 193 values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30); 194 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 195 values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30); 196 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 197 values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20); 198 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 199 values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30); 200 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 201 values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30); 202 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 203 values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10); 204 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 205 values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20); 206 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 207 values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10); 208 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 209 values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30); 210 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 211 values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20); 212 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 213 values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30); 214 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 215 values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20); 216 insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 217 values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10); 218 commit; 219 prompt 14 records loaded 220 prompt Loading SALGRADE... 221 insert into SALGRADE (GRADE, LOSAL, HISAL) 222 values (1, 700, 1200); 223 insert into SALGRADE (GRADE, LOSAL, HISAL) 224 values (2, 1201, 1400); 225 insert into SALGRADE (GRADE, LOSAL, HISAL) 226 values (3, 1401, 2000); 227 insert into SALGRADE (GRADE, LOSAL, HISAL) 228 values (4, 2001, 3000); 229 insert into SALGRADE (GRADE, LOSAL, HISAL) 230 values (5, 3001, 9999); 231 commit; 232 prompt 5 records loaded 233 prompt Enabling foreign key constraints for EMP... 234 alter table EMP enable constraint FK_DEPTNO; 235 prompt Enabling triggers for BONUS... 236 alter table BONUS enable all triggers; 237 prompt Enabling triggers for DEPT... 238 alter table DEPT enable all triggers; 239 prompt Enabling triggers for EMP... 240 alter table EMP enable all triggers; 241 prompt Enabling triggers for SALGRADE... 242 alter table SALGRADE enable all triggers; 243 set feedback on 244 set define on 245 prompt Done.
------------------------------------------------- DQL 最基本的查询语句 --------------------------------------------
--最简单的sql 语句【查询所有员工的信息】
--* 通配符 代表所有的。 select 后跟的是要查询的内容。from 后跟表的名称。
select * from emp;
--部分表字段内容查询【查询员工的编号,名称,工资,部门编号】
select empno, ename, sal, deptno from emp;
使用算术表达式【查询员工的姓名,工作,年薪】
select ename, job, sal*12 from emp;
--把奖金加上, 任何数据和 null 运算,结果还是 null。
select ename, job, sal*12+comm from emp;
查询结果中的字段使用别名:在字段名后使用关键字 字段名 as "别名",作用[方便查看查询结果]
--注意:as关键字可以缺省不写,别名中没有特殊的字符双引号也可以缺省
--方式-1
select empno 员工编号 from emp;
--方式-2
select empno "员工编号",ename "员工姓名" from emp;
--方式-3
select empno as "员工编号",ename as "员工姓名",job as "工作职位" from emp;
使用连接符 || 相当于 java 中的 + 连接符
--编号是XXX的员工的名字为XXXX,入职日期为XXX
select '编号是:' || empno || '的员工的名字为:' || ename ||',入职日期为:'|| hiredate from emp;
去除重复行 distinct
--查询所有的部门编号
select deptno from emp;
--去除重复行的编号
select distinct deptno from emp;
--去除多个字段组合的重复行
select distinct deptno, job from emp;
排序 order by 默认是升序排列 asc 降序 desc
--按照部门编号进行升序排序
select * from emp order by deptno asc;
--按照部门编号进行降序排序并去除部门重复
select distinct deptno from emp order by deptno desc;
--查询员工的所有的信息,员工的部门编号升序排列,部门编号相同的,工资降序排列
select * from emp order by deptno asc ,sal desc;
--字符串排序(姓名)
select ename from emp order by ename;
排序的时候,使用字段的别名
--排序时使用算术表达式
select ename, job, sal*12 as 年薪 from emp order by sal*12;
--使用别名进行排序
select ename, job, sal*12 as 年薪 from emp order by 年薪;
sql 中那些内容是大小写敏感的?哪些是不敏感的。
关键字 大小写不敏感
SELECT * FROM emp
表名 大小写不敏感
select * from EMP
字段名 大小写不敏感
select ENAME, job, MGR from emp
元组的内容,字段的内容,大小写是敏感的。
select * from emp where ename='SMITH'
select * from emp where ename='smith'--查询不到数据
where 子句 后跟筛选数据的条件(进行 行数据的过滤)
--查询姓名 为 scott的员工的信息
select * from emp where ename='SCOTT'
--查询入职日期为1981/4/2 的员工的信息
--1 :使用默认的日期的字符串形式 'DD-MON-RR‘
select * from emp where hiredate='2-4月-1981'
使用运算符进行筛选 =,>,>=,<,<=,<>或者!= 单个条件中
select * from emp where sal>1600 order by sal
select * from emp where sal<1600 order by sal
select * from emp where sal>=1600 order by sal
select * from emp where sal<=1600 order by sal
select * from emp where sal=1600 order by sal
select * from emp where sal!=1600 order by sal
select * from emp where sal<>1600 order by sal
--查询工资在1000-2000之间的所有的员工的信息
--and 相当于 并且 java 中的 &&
select * from emp where sal >=1000 and sal <=2000
--between xx and xx 闭区间的
select * from emp where sal between 1000 and 2000
--查询 员工信息 工资是 1100 或者是 1600
--or 代表或者的意思
select * from emp where sal=1100 or sal=1600
--查询所有员工中工种为 clerk manager analyst 的员工的信息 ename ,job deptno
select ename, job,deptno from emp where job='CLERK' or job='MANAGER' or job='ANALYST'
-- 在集合中的某一个值就可以 in ()
select ename, job, deptno from emp where job in ('CLERK','MANAGER','ANALYST')
模糊查询 like【% 代表 任意个字符 通配符,_ 代表一个字符】
select * from emp
--查询名称的第一个字符为 A 的员工的信息
select * from emp where ename like 'A%'
--查询名字中包含A 字符的
select * from emp where ename like '%A%'
--查询第二个字符为A 的员工的信息
select * from emp where ename like '_A%'
--查询不包含A字符的员工信息
select * from emp where ename not like '%A%'
--特殊情况 名字中包含 _ 员工的信息
select * from emp where ename like '%\_%' escape '\'
空判断【is null is not null】
--所有奖金为空的员工的信息
select * from emp where comm is null
--不为空的员工的信息
select * from emp where comm is not null
--查询 工资在 1000--2000 之间 或者是职位是 职员 的员工的信息
--查询职员
select * from emp where job='CLERK'
select * from emp where job='CLERK' or sal between 1000 and 2000
--工作是 clerk 或者 manager 并且 sal 大于 1500的
--连接条件的关键字的连接的优先级,配合小括号使用
select * from emp where (job='CLERK' or job='MANAGER') and sal > 1000
伪表 dual 也称为虚表
-- 存在的意义:不依赖于任何表的查询或者是计算的工作。
-- 查询系统日期,和当前用户
select sysdate from dual
select user from dual
select * from dual
select 1+1 from dual
select ceil(1.5) from dual
函数分类【函数名大小写不敏感】
1:单行函数:对于一个查询的结果计算之后会得到一个对应的结果。
2:多行函数:对于多个结果处理之后得到一个结果。
单行函数:日期处理函数、字符串处理函数、数序运算的函数,转换函数,通用函数
--将emp表中所有的员工的姓名全部小写输出
select ename, lower(ename) from emp
-- 查询所有员工的名字 和入职天数
select ename,sysdate-hiredate 入职天数 from emp
select ename ,floor(sysdate- hiredate) 入职天数 from emp
-- 查询所有员工的名字 和入职月数,要求整月输出。 函数的嵌套使用。
select ename,floor(months_between(sysdate, hiredate)) 入职的月数 from emp
-- 查询下周三的日期
select next_day(sysdate,'星期三') from dual
-- 查询本月最后一天的日期
select last_day(sysdate) from dual
-- 查询所有员工的入职的星期数,年数,使用别名显示 按照入职时间长短 升序排列
select ename, round((sysdate-hiredate)/7) as "星期数" , round((sysdate-hiredate)/365) as 年数 from emp order by 星期数
转换函数【to_number to_char to_date】
to_number:字符串 -->数值的转换
to_char: 数值--->字符串 的转换 日期 --->字符串的转换
to_date:字符串--->日期的转换
--数值和字符串之间的相互转换
--java Integer.toString(int) Integer.parseInt(String)
--日期对象和字符串之间的相互转换
--sdf String format(Date) Date parse(String)
自动转换
--字符串自动转换为数值形式
select '2' -'2' from dual
--数值向字符串的自动转换
select 1 || 1 from dual
函数转换
数值--->字符串 的转换 (to_char(number, format) )
--9:代表一位数字,整数部分:如果该位没有数字则不进行显示,但对于小数点后面的部分仍会强制显示
select to_char(123123.123 ,'L999,999.9999') from dual
--0:代表一位数字,如果该位没有数字则强制显示0 整数和小数部分
select to_char(123.123 ,'L000,000.00000') from dual
日期-->字符串(to_char(date,format))
--将所有员工的受雇日期,按照指定的格式显式 2018-09-26 16:19:33
select hiredate, to_char(hiredate, 'YYYY-MON-DD HH24:MI:SS') from emp
--员工入职的年份
select round((sysdate-hiredate)/365) 年数 from emp
select to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY') 年数 from emp
to_date : 字符串到日期对象
将日期字符串转换为 日期对象 和 hiredate 比较
将字符串解析为 日期对象 解析的格式需要和 日期字符串 一致。
--查询 XXXX时间之后入职的员工信息 1981/4/2
select * from emp where hiredate > to_date('1981/4/2','YYYY/MM/DD') order by hiredate
--查询 指定 日期之间的入职的员工的信息 1981/4/2 1982/1/23
select * from emp where hiredate > to_date('1981/4/2','YYYY/MM/DD') and hiredate < to_date('1982/1/23','YYYY/MM/DD')
select * from emp where hiredate between to_date('1981/4/2','YYYY/MM/DD') and to_date('1982/1/23','YYYY/MM/DD')
to_number:将字符串转换为 数值
select to_number('$123.123','$000.000') + 1 from dual
select to_number('¥123.123','L999.999') + 1 from dual
--select '¥123.123' + 1 from dual
通用函数:(nvl (exp1,exp2) : 如果exp1 是null 那么返回 exp2 如果不是null 就返回自身)
--所有员工的年薪
select ename, sal*12 +comm 年薪 from emp
select ename, sal *12 + nvl(comm, 0) 年薪 from emp
--nvl2(exp1,exp2,exp3) : 参数的意义 :如果exp1 是null 就返回 exp3 ,否则返回 exp2
select ename, sal * 12 + nvl2(comm, comm, 0) 年薪 from emp
decode (value, key0,value0,key1,value 1,..... ,valuen)
参数的意思,如果 value 的值 是 key0? 整个函数返回 value0 ,如果值是key1就返回value1,以此类推,如果都没有找到合适,最后返回 valuen。
--查询工种并去除重复
select distinct job from emp
--将emp 表中所有的员工的名字 工作 以及工作中文显示
select ename, job, decode(job,'CLERK','职员','SALESMAN','销售','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师') 中文职业 from emp
select ename, job, decode(job,'SALESMAN','销售','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师','职员') 中文职业 from emp
------------------------------------------------- 一波小练习 -------------------------------------------
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 --1:查询每个月倒数第三天入职的员工的信息 2 select * from emp where hiredate=last_day(hiredate)-2 3 4 --2:找出早于35年前入职的员工的信息 5 select * from emp where (sysdate-hiredate)/365 > 35 6 select * from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY') > 35 7 8 --3:将所有的员工的名称全部小写输出 9 select ename, lower(ename) from emp 10 11 --4:显示不带有字符E的员工的姓名 12 select ename from emp where ename not like '%E%' 13 14 --5:显示名字长度为5的员工的名字 15 select ename from emp where length(ename)=5 16 17 --6:显示所有员工的名字的前3个字符 18 select ename, substr(ename,1,3) from emp 19 20 --7:显示所有员工的姓名把 A 换成 a 显示 21 select ename, replace(ename,'A','a') from emp 22 23 --8:显示所有的员工的信息,按照姓名排序 24 select * from emp order by ename 25 26 --9:显示员工的姓名,加入公司的月份,年份,按照月份排序,如果月份相同,则按照年份排序 27 select ename, to_char(hiredate,'MM') 月份, to_char(hiredate,'YYYY') 年份 from emp order by 月份,年份 28 29 --10:显示所有员工的姓名,受雇日期,按照受雇日期的长短,将服务最长时间的人排在前面。升序 30 select ename, hiredate from emp order by hiredate 31 32 --11:显示所有员工的姓名,工作,薪金,按照工作的降序排序,工作相同,按照工资升序排。 33 select ename,job,sal from emp order by job desc, sal asc 34 35 --12:找出所有在二月份入职的员工信息 36 select * from emp where to_char(hiredate,'MM')=2 37 38 --13:将所有的员工加入公司的天数显示。 39 select ename, floor(sysdate-hiredate) 入职天数 from emp 40 41 --14:将“¥123”显示为数值 42 select to_number('¥123','L000') from dual