commit: 提交
rollback: 回滚
单表查询
---单表的查询学习 --查询表的所有数据 select * from 表名;*代表所有 select * from emp; --查询表中指定字段的值 select 字段名1,字段名2,...from表名 select empno from emp; select empno,ename from emp; --给查询结果中的字段使用别名 --在字段名后使用关键字 字段名 as "别名" --作用:方便查看查询结果 --注意:as关键字可以省略不写,别名中没有特殊字符双引号也可以省略不写。 select empno 员工编号,ename"员工 姓名",job as 工作,mgr as "领导编号" from emp; --连接符:select 字段名||'字符'||字段名||..... from 表名 --||为sql语句的字符链接符,使用在select和from之间 --字符链接格式为 字段名||'字符'||字段名 --注意:一个拼接好的连接在结果集中是作为一个新的字段显示,可以使用别名优化字段显示。 select empno||'的姓名是'||ename as"信息",job||'哈哈'||mgr from emp; --去除重复 select distinct 字段名,字段名,...fromn 表名 ---注意:去除重复的规则是按照行进行去除的,多行数据完全相同取其一 select distinct job ,mgr from emp; --排序 --单字段排序 --select * from 表名 order by 字段名 asc 升序排序 asc可以省略不写 --select * from 表名 order by 字段名 desc 降序序排序 --多字段排序 --select * from emp order by 字段名1,字段名2... --先按照字段1排序,如果字段1的值相同,则按照字段2排序,.... select * from emp order by empno desc--单字段排序 降序 select empno,ename,job from emp order by ename asc--单字段排序 升序 select * from emp order by empno,ename--多字段排序 --字段的逻辑运算 --select关键字和from关键字之间的字段可以直接进行四则运算 --字段与字段之间也可以直接进行运算 --注意:字段值为数值类型 select * from emp select empno,ename,job,sal*2+1000,sal+comm from emp ----------------------------------------------------------------- --使用where子句查询筛选 --select 字段名,字段名,...from表名 where 筛选条件 --单筛选条件 --使用运算符进行筛选 =,>,>=,<,<=,<> 单个条件中 --注意:如果条件中的值为字符,必须使用单引号括起来 --查询所有的员工的工资信息 select empno,ename,sal+comm as 薪资 from emp --查询SMITH的个人信息 select * from emp where ename='SMITH' --查询SMITH的薪资信息,逻辑运算符= select empno,ename,sal,sal+comm from emp where ename='SMITH' --查询工资大于1000的员工信息,逻辑符> select * from emp where sal>'2000' --查询工资不等于3000的员工信息 select * from emp where sal<>3000 order by sal --练习: --查看工资等于1250的员工信息 select *from emp where sal='1250' --查看工作等于CLERK的员工信息 select * from emp where job='CLERK' --查看工资大于1250的员工姓名和工作 select ename,job from emp where sal>1250 --查看工资大于等于2000的员工信息 select * from emp where sal>=2000; --查看工资小于等于2000的员工信息; select * from emp where sal<=2000; --查看工资不等于1500的员工信息 select * from emp where sal<>1500; --查看入职日期在81年后的员工信息 --注意:oracle默认的日期格式为 日-月-年,示例'03-1月-1981' select * from emp order by hiredate select * from emp where hiredate>='01-1月-1981' order by hiredate --多条件筛选(where子句关键字:and,or,like,is null,is not null, in ,between and) --查询工资在2000-3000之间的员工信息 --使用and关键字,多条件同时成立的筛选使用and关键字进行条件连接 select * from emp where sal>=2000 and sal<3000 --使用between and 关键字进行条件连接,包含两头的数据 select * from emp where sal between 2000 and 3000 --查询工作为SALESMAN,ANALYST,MANAGER的员工信息 --使用or关键字,进行或条件的筛选。 select * from emp where job='SALESMAN' or job='ANALYST' or job='MANAGER' order by job --使用in关键字,也可以进行或筛选,但是in中的内容只能为一个字段的值。 select * from emp where job in('SALESMAN','ANALYST','MANAGER') --查询姓名中包含s的,以s开头的,以s结尾的,第二个字符为A的。(模糊查询) --%号表任意多个的任意字符 --select * from 表名 where 字段名 like '%字符%' 查询包含指定字符的数据 select * from emp where ename like '%S%' --包含s的 --select * from 表名 where 字段名 like '字符%' 查询以指定字符开头的数据 select * from emp where ename like 'S%'--以S开头 --select * from 表名 where 字段名 like '%字符' 查询以指定字符结尾的数据 select * from emp where ename like '%S'--以S结尾的 --select * from 表名 where 字段名 like '_字符%' 查询指定位置为指定字符的数据 --_表示一个任意字符 select * from emp where ename like '_A%'--第二个字符为A的 --select * from 表名 where 字段名 like '%字符2字符1%' escape'字符2' --escape将指定的字符变为转义字符 --转义字符可以将特殊字符转为普通字符 select * from emp where ename like '%/_%' escape '/' select * from emp for update --查询有津贴的员工信息 -- select * from 表名 where 字段名 is null 字段值为null -- select * from 表名 where 字段名 is not null 字段值不为null --多个条件使用and关键进行连接,筛选的是符合所有条件的数据 --select * from 表名 where 筛选条件1 and 条件2 and .... select * from emp where comm is not null and comm>0
--Oracle函数学习(单行函数,多行函数,转换函数,其他函数)
--查询工作为SALESMAN,MANAGER并且工资大于2500的员工信息
--and关键字的执行级别高于or
--可以使用小括号提升条件的执行级别,使用了小括号的级别是最高的
select * from emp where (job='SALESMAN' or job='MANAGER') and sal>2500
单行函数
--单行函数学习(字符函数,数值函数,日期函数) --特点1:不改变真实数据,只是对数据做了进一步修饰或者处理显示。 --特点2:可以和字段混合使用 --使用:select 字段名, 函数名(字段名),字段名....from 表名 --字符函数: --查询所有的员工信息,员工姓名小写显示。 select empno,ename,lower(ename),job,mgr,sal,lower('HH') from emp --查询所有的员工信息,员工姓名首字母大写。 select empno,INITCAP(ename) 首字母大写的姓名,lower(ename),job from emp --数值函数:对数值类型的数据进行运算 --伪表:真实存在的表,是为了方便进行数据的验证而临时存在的表。表名为:dual select abs(-1),ceil(2.2),floor(3.3),power(2,3),mod(5,2),round(4.55),trunc(10/3,2) from dual --日期函数: select months_between('01-1月-2018','24-6月-2017') from dual --返回两个日期间的月份数 select add_months('01-4月-2018',-4) from dual --返回指定月数后的日期 select next_day('16-4月-2018','星期二') from dual--查询最近的星期的日期 select last_day('16-4月-2018') from dual--返回当月的最后一天的日期 select round(to_date('19-4月-2018'),'DAY') from dual--按照星期(月,年)进行四舍五入
多行函数
1 --多行函数(max,min,avg,sum,count)很重要 2 --作用:对查询的数据进行统计 3 --使用:select 多行函数名(字段名),多行函数名(字段名)..from 表名 4 --注意:多行函数不能和普通字段以及单行函数混用,除非分组 5 --max(字段名) 返回该字段的最大值 6 --min(字段名) 返回该字段的最小值 7 --sum(字段名) 返回该字段的和 8 --avg(字段名) 返回该字段的平均值 9 --count 10 --count(*) 返回表的记录数 11 --count(字段名) 返回非空值的数量 12 --count(distinct 字段名) 去除重复后的字段值的数量 13 --查看员工的最高工资 14 select max(sal) from emp--多行函数不能和字段直接混用,除非分组。 15 select lower(ename),max(sal) from emp--多行函数 不能和单行函数混用,除非分组 16 --查看员工的最低工资 17 select min(sal) from emp 18 --查看员工的平均工资 19 select avg(sal) from emp 20 --查看所有的员工工资之和 21 select sum(sal) from emp 22 --查询公司有多少员工 23 select count(*) from emp--查询表的记录数 24 --查询有津贴的员工人数 25 select count(comm) from emp--查询字段的值的数量,null会自动过滤 26 --查询公司有多少工作种类 27 select distinct job from emp 28 select count(distinct job) from emp 29 select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp 30 select * from emp
分组查询&分组筛选
----分组查询&筛选学习: --关键字:group by 分组字段名,分组字段名.... --注意1:使用了分组后,在select语句中只允许出现分组字段和多行函数。 --注意2:如果是多字段分组,则先按照第一字段分组,然后每个小组继续按照第二个字段继续分组,以此类推。 --注意3:在where子句中不允许出现多行函数。 --分组筛选 --关键字:having --作用:针对分组进行分组后的数据筛选,允许使用多行函数。 --注意:having关键必须和分组结合使用。不允许单独使用。 --where和having的比较: --where子句不允许出现多行函数,having允许出现多行函数 --where子句和having都可以使用普通字段直接进行筛选,但是where的效率高于having --where执行顺序: from--->where--->group by-->select-->order by --having执行顺序:from--->group by-->select--->having--->order by --结论:在分组语句中,使用where进行字段级别的筛选,使用having进行多行函数的筛选。 --查询最高工资和员工数 select max(sal),count(*) from emp --查询不同部门的最高工资 select deptno,max(sal) from emp group by deptno select * from emp --查询不同工作岗位的员工数 select job, count(*) from emp group by job --查询不同部门的不同工作岗位的人数 select deptno ,lower(job),count(*) from emp group by deptno,job order by deptno --查询不同部门的不同工作岗位的并且人数大于1的信息 select deptno ,lower(job),count(*) from emp group by deptno,job having count(*)>1 order by deptno --查询部门号大于10的不同部门的不同工作岗位的人数 --使用having关键字 select deptno ,lower(job),count(*) from emp group by deptno,job having deptno>10 order by deptno --使用where关键字 select deptno,job,count(*) from emp where deptno>10 group by deptno,job order by deptno ---SQL查询语句的结构 --select 子句 要查询的数据(oracle函数,别名,连接符,去除重复,逻辑运算) --from语句 决定要查询的表(表名) --where子句 筛选数据(筛选条件,关键字) --group by子句 分组 (分组字段) --having子句 分组筛选 (多行函数筛选条件) --order by子句 排序 (排序) --from-->where--->group by-->select--->having--->order by
数据库的增删改
--数据库的增删改 --注意:增加删除修改的数据SQL语句执行完毕后,不会立马进行数据的写入。 --还需要手动对数据进行提交,如果数据有问题还可以回滚 --主键:非空唯一的字段可以设置为主键。 --在一张表中,某个字段的值是非空唯一的,将此字段设置为主键。 --主键的作用:唯一的标识一条数据。 --增加数据 --insert into 表名(字段名,字段名,...)values(值1,值2,值3....); --注意1:主键必须给值,允许为空的字段可以不给值。 --注意2:插入语句表名后跟的字段名为要赋值的字段,值和字段数量和顺序必须是一一对应的。 --注意3:如果是全字段插入,可以省略字段名部分 insert into 表名 values(值1,值2,.....) --在部门中新增一个新的部门信息,信息内容为 编号:50,名称:LOL学院,地址:北京 insert into dept(deptno,dname,loc)values(50,'LOL学院','北京'); insert into dept(deptno,dname,loc)values(60,'LOL学院','北京'); insert into dept values(60,'LOL学院','北京'); select * from dept --在部门中新增一条数据,只有部门编号和名称,没有地址。 insert into dept(deptno,dname)values('吃鸡学院',70) --删除数据 --delete from 表名 删除表中的所有记录 --truncate table 表名 删除表中的所有记录,但是效率高于delete --delete from 表名 where 条件 删除指定的数据,只要符合条件就会删除 delete from dept where deptno=50 --删除指定的数据 delete from dept --清空表数据 truncate table dept--清空表中数据 --更新数据 --update 表名 set 字段名=新的值,字段名=新的值...(会将字段的值全部改为新的值) --update 表名 set 字段名=新的值,字段名=新的值... where 条件(将符合条件的数据的字段改为新的值) update dept set dname='java学院',loc='上海' update dept set dname='java学院',loc='上海' where deptno=50 select * from dept
数据的备份
create table deptBak as select * from dept--全部备份
--注意:只会备份表结构和表的数据,约束不会备份。 --表级别备份 --全部备份:create table 新的表名 as select * from 备份表名 --部分备份: create table 新的表名 as select 字段名,字段名,...from 备份表名 --数据整体插入 --insert into 插入表名 select * from 表名 --注意:查询语句结果的字段数据必须和插入表名的字段数量一致,类型要一致。 create table deptBak as select * from dept--全部备份 create table deptBak2 as select deptno,dname from dept-- 部分备份 select * from deptBak2 insert into deptBak2 select deptno,dname from dept
转换函数
to_number, to_char, to_date
--转换函数: --to_number(数值类型的字符):将字符转换为数值 --to_char(数值或者是日期):将数值或者日期转换为字符 --to_date(日期格式的字符):将字符转换为日期 ----------------数值和字符的互转----------------------- --字符转换为数字char---->number select to_number('123')+2 from dual --数字转换字符number--->char --指定显示格式: --9表示位置占位,例如999,999,999会将数字按照三个一组使用逗号隔开。 --L表示人民币符号,$表示美元符号 --0可以进行占位分组,但是如果真实数据位数不足,会使用0进行补位。 select to_char(12345,'$999,999,999') from dual select to_char(12345,'L999,999,999') from dual select to_char(12345678,'000,000,000,000.000') from dual --查询工资大于2000的员工信息 --数值和字符之间的转换可以隐式转换。to_number可以省略不写. select * from emp where sal>'2000'; select * from emp where sal>to_number('2000'); ---------------日期和字符的互转--------------------------- --一般使用方式:新增数据使用to_date(),查询数据使用to_char() --字符转换为日期 char--->date --使用to_date('要转换的字符',日期格式)函数将字符转换为日期 --注意1:字符必须符合日期格式 --注意2:oralce默认的转换格式为日月年,例如'01-1月-2018' oracle认为是一个日期 --常用日期格式: -- yyyy-mm-dd -- yyyy/mm/dd --查询员工入职日期在82年后的信息 select * from emp where hiredate >to_date('1982-01-01','yyyy-mm-dd') select * from emp where hiredate >to_date('1982/01/01','yyyy/mm/dd') select * from emp where to_char(hiredate,'yyyy-mm-dd') >'1982-01-01' --日期转换为字符 date--->char --使用to_char('要转换的日期',转换格式) --注意1:如果不指名转换格式,则使用默认格式,日月年例如:'01-1月-81' --常用转换格式: -- yyyy-mm-dd -- yyyy/mm/dd --'yyyy"年"mm"月"dd"日"' select to_char(hiredate) from emp--使用默认格式将日期转换为字符 select to_char(hiredate,'yyyy-mm-dd') from emp--使用指定格式 yyyy-mm-dd select to_char(hiredate,'yyyy/mm/dd') from emp--使用指定格式 yyyy/mmm/dd select to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp--使用指定格式 'yyyy"年"mm"月"dd"日"'
其他函数
nvl, nvl2, decode
--nvl():nvl(字段名,新的值) --如果字段值不为null,则返回该字段的值。如果为null则返回新的值 --nvl2():nvl2(字段名,处理1,处理2) --如果字段值不为null,则执行处理1,为null执行处理2 --decode():decode(字段名,值1,处理1,值2,处理2,值3,处理3,...,公共处理) --如果字段的值和decode中的条件值相同则执行对象的处理。如果都没有则执行公共处理 ---查询员工的工资信息 select ename,job,sal from emp --查询员工的薪水信息 select ename,job,sal+nvl(comm,0),sal+comm,sal from emp select ename,job,nvl2(comm,sal+comm,sal) from emp --显示员工的职称 select ename,job,decode(job,'MANAGER','经理','PRESIDENT','董事长','SALESMAN','销售','普通员工') from emp
练习1
View Code
练习2
View Code
练习3
1 --1.选择部门30中的所有员工. 2 select * from emp where deptno=30 3 --2.列出所有办事员(CLERK)的姓名,编号和部门编号. 4 select ename,empno,deptno from emp where job='CLERK' 5 --3.找出佣金高于薪金的员工. 6 select * from emp where sal<comm 7 --4.找出佣金高于薪金的60%的员工. 8 select * from emp where sal*0.6 <comm 9 --5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料. 10 select * from emp where (job='MANAGER' and deptno=10) or (deptno=20 and job='CLERK') 11 --6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. 12 select * from emp where (job='MANAGER' and deptno=10) or (deptno=20 and job='CLERK')or (job<>'MANAGER' and job<>'CLERK' and sal>=2000) 13 --7.找出收取佣金的员工的不同工作. 14 select distinct job from emp where comm is not null 15 --8.找出不收取佣金或收取的佣金低于100的员工. 16 select * from emp where comm is null or comm <100 17 --9.找出各月倒数第3天受雇的所有员工. 18 select * from emp where hiredate=last_day(hiredate)-2 19 --10.找出早于12年前受雇的员工. 20 --11.以首字母大写的方式显示所有员工的姓名 21 select INITCAP(ename) from emp 22 --12.显示正好为5个字符的员工的姓名. 23 select ename from emp where length(ename)=5 24 --13.显示不带有"R"的员工的姓名. 25 select * from emp where ename not like '%R%' 26 --14.显示所有员工姓名的前三个字符. 27 select substr(ename,1,3) from emp 28 --15.显示所有员工的姓名,用a替换所有"A" 29 select replace(ename,'A','a') from emp 30 --16.显示满35年服务年限的员工的姓名和受雇日期. 31 select ename,hiredate, floor((sysdate-hiredate)/365) from emp where (sysdate-hiredate)/365>35 32 --17.显示员工的详细资料,按姓名排序. 33 select * from emp order by ename 34 --18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面. 35 select ename ,hiredate,floor((sysdate-hiredate)/365) years from emp order by hiredate asc 36 --19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. 37 select ename,job,sal from emp order by job desc,sal 38 --20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面. 39 select ename,hiredate,to_char(hiredate,'yyyy') years,to_char(hiredate,'mm') ms from emp order by ms,hiredate 40 --21.显示在一个月为30天的情况所有员工的日薪金,忽略余数. 41 select floor((sal+nvl(comm,0))/30) from emp 42 --22.找出在(任何年份的)2月受聘的所有员工。 43 select * from emp where to_char(hiredate,'mm')=2 44 --23.对于每个员工,显示其加入公司的天数. 45 select ename,sysdate-hiredate from emp 46 --24.显示姓名字段的任何位置包含"A"的所有员工的姓名. 47 select * from emp where ename like '%A%' 48 --25.以年月日的方式显示所有员工的服务年限. 49 select ename,floor(floor(months_between(sysdate,hiredate))/12),mod(floor(months_between(sysdate,hiredate)),12),floor(mod(sysdate-hiredate,30)) from emp