SQL基础操作

最新推荐文章于 2024-04-09 09:38:05 发布

今天丶

最新推荐文章于 2024-04-09 09:38:05 发布

阅读量310

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/weixin_45020637/article/details/96915552

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1)SQL查询

--注释
--select *|字段名1,字段名2... from 表名;
--1)
--查询|检索|获取 所有员工的所有信息
--查询的数据: 员工的所有信息
--数据的来源:员工表  emp
--条件:
select * from emp;

--2)
--查询所有的员工名字
select ename from emp;

--3)
--查询所有的员工编号和员工名称,员工上级的编号
--查询的数据: empno,ename,mgr
--数据的来源:员工表  emp
select empno,ename,mgr from emp;

--4)
--查询所有部门部门编号
select deptno from dept;

--5)
--查询出所有员工所在的部门的部门编号
select distinct  deptno from emp;
--对查询数据进行去重(非字段去重) distinct

--6)
--查询出所有存在员工的部门的部门编号,以及员工名称
select deptno,ename from emp;
select distinct sal,deptno from emp;

--7)
--查询表达式,结果是表达式的值,显示的字段名就是表达式,计算值
select 1+1 from emp;
select 'a' from emp;

--8)
--给字段取别名   select 字段1 (as) 别名1,字段2 别名2 from 表名 别名;  表的别名不能加as
--查询所有员工的名称(别名为:名字),员工编号(编号)
--别名默认变大写,别名中的内容原封不动出现 ""->中的内容原封不动出现
select 123+456 "get sum" from emp;
select empno as 员工编号,ename "员工 姓名" from emp;

--9)
--字符串 ''  原封不动显示""
select distinct '哈哈' 笑 from emp e;

--10)
--字符串拼接 java中使用+   这里使用||
--查询 ab--cd  表达式
select distinct 'ab-'||'-cd' from emp;

--查询所有的员工名字,给他们来一个前缀SXT
select 'sxt-'||ename from emp;

--11)
--伪列 : 不存在的列就是伪列  比如:表达式,字符串

--12)
--虚表: 在oracle中不存在的表,也可以说是这个表中没有任何数据,没有任何字段 --oracle中的虚表:dual
--虚表的作用:可以不使用distinct就可以去重的效果,因为里面没有数据,不会出现多行
select * from dual;
select distinct 123*456 from emp;
select 123*456 from dual;
select sysdate from dual;

--比如查询当前时间
--13)
--给每一个员工在原来的基础上+100块钱奖金
--null 空
--null与数字运算,结果还为null
--null与字符串运算,结果原串
--nvl(参数1,参数2) 处理null使用   如果参数1为null,最终结果参数2,如果参数1不为null,最终的结果就是参数1
select comm 原奖金,comm||'100' 新奖金 from emp;
select comm 原奖金,nvl(comm,0)+100 新奖金  from emp


--小练习
--查询所有员工的名字, 工种, 年薪(不带奖金)
select ename,job,sal*12 年薪 from emp;

--查询所有员工的名字,工种,年薪(带12月奖金的)
select ename,job,(sal+nvl(comm,0))*12 年薪 from emp;

--查询所有员工的名字, 工种, 年薪(带一次奖金的)
select ename,job,sal*12+nvl(comm,0) 年薪 from emp;

2)SQL条件查询

--select *|表达式|字符串|伪列|字段1 别名1,字段2 as 别名2... from 表名 别名|结果集 where 行过滤条件;
--执行流程: from-->where-->select确定结果集
-- 查询20部门的员工信息
--数据: *
--来源: emp
--条件: deptno=20
select * from emp where deptno=20;

-- > < >=  <=  = !=   <>
-- 查询工资大于1000的员工的姓名 工作岗位  工资   所属部门编号
--数据: ename,job,sal,deptno
--来源: emp
--条件: sal>1000
select ename,job,sal,deptno from emp where sal=1000;

-- 查询不在20部门工作的员工信息 <>即!=
select * from emp where deptno != 20;
select * from emp where deptno <> 20;

--where 中不能使用字段的别名
-- 查询员工的年薪大于20000的 员工名称、岗位 年薪
select ename 姓名,job 岗位,(sal+nvl(comm,0))*12 sum from emp where ((sal+nvl(comm,0))*12)>20000;

select ename 姓名,job 岗位,(sal+nvl(comm,0))*12 sum from emp;
select 岗位, sum
  from (select ename 姓名, job 岗位, (sal + nvl(comm, 0)) * 12 sum from emp)
 where sum > 20000;

-- 查询  any(任意一个)   some(任意一个)  all(所有)
select * from emp where deptno = any(10,20);
select * from emp where deptno = some(10,20);
--大于最小的
select * from emp where sal> any(1500,2000); --薪资>1500的就可以
--大于最大的
select * from emp where sal> all(1500,2000); --薪资>2000的就可以

-- 查询 工种不为’SALESMAN’的员工信息 （注意 内容区分大小写）
select * from emp where not job ='SALESMAN';

--or或   and并且|都   not取反
-- -检索 工资 1600, 3000员工名称 岗位 工资
select ename,job,sal from emp where sal=1600 or sal=3000;
select ename,job,sal from emp where not (sal=1600 or sal=3000);


-- 工资在2000到3000之间的员工信息
select * from emp where sal>2000 and sal<3000;
--between 小范围值  and 大范围的值  两者之间  <= >=
select * from emp where sal between 1600 and 3000;


---查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
select ename ,deptno ,sal from emp where job='CLERK' and deptno=20;


-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
select ename ,deptno ,sal,job from emp where job='CLERK' or deptno=20;

--查询 岗位 不是 CLERK 员工名称 部门编号，工资
select ename ,deptno ,sal,job from emp where job!='CLERK';
select ename ,deptno ,sal,job from emp where not job='CLERK';
select ename ,deptno ,sal,job from emp where job<>'CLERK';

-- 查询 岗位 不为 CLERK 并且部门编号不为 20的员工名称 部门编号，工资
select ename ,deptno ,sal,job from emp where job!='CLERK' and deptno!=20;
select ename ,deptno ,sal,job from emp where not (job='CLERK' or deptno=20);

--存在佣奖金的员工名称
select ename,comm from emp where not comm is null;
select ename,comm from emp where comm is not null;

--不存在奖金的员工名称
select ename,comm from emp where comm is null;

--集合
--Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
--Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序 ；
--Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
--Minus，差集( 减去重复 ) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序
--查询工资大于1500 或 含有佣金的人员姓名
select ename,sal,comm from emp where sal>1500 or comm is not null;
select ename,sal,comm from emp where sal>1500;
select ename,sal,comm from emp where comm is not null;
--并集
select ename,sal,comm from emp where sal>1500 
Union
select ename,sal,comm from emp where comm is not null;
--全集
select ename,sal,comm from emp where sal>1500 
Union all
select ename,sal,comm from emp where comm is not null;

--查询显示不存在雇员的所有部门号。
--求出所有的部门号
select deptno from dept;
--有员工的部门号
select distinct deptno from emp;

select deptno from dept
Minus
select distinct deptno from emp;


-- 查询显示存在雇员的所有部门号。
select deptno from dept
Intersect
select distinct deptno from emp;


--模糊匹配  like %任意任意字符   _一个任意字符   一起使用
--查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
--完全匹配
select * from emp where ename like 'SMITH';

--查询员工姓名以'A'结尾的员工信息
select * from emp where ename like 'A%';


--查询员工姓名中第二个字母为A的员工信息
select * from emp where ename like '_A%';

insert into emp(empno,ename,sal) values(1000,'t_%test',8989);
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);

--escape('单个字符')指定转义符
--查询员工姓名中包含字符%的员工信息
select * from emp where ename like '%B%%' escape('B');

--当执行插入数据,删除数据,修改的时候,默认开启事务
--可提交  commit  
--可回滚  rollback

--多个人中任意一个值就可以  in任意一个
select * from emp where sal=1600 or sal=3000 or sal=1500;
select * from emp where sal in(1500,1600,3000);

--select 字段.. from 结果集 where 行过滤条件 order by 排序字段 desc降序|asc升序(默认)..;
--执行流程: from--> where-->select-->排序
select empno,ename,sal from emp order by sal desc,empno asc;

--按照奖金升序排序,如果存在null值,所有的奖金null值的数据最先显示
select empno,ename,sal,comm from emp where deptno in (10,30) order by comm asc nulls first;

3)SQL查询

--部门名称为 SALES 或 ACCOUNTING 的雇员信息
--查询的数据: 员工信息*
--来源: emp
--条件: 部门名称=SALES 或 ACCOUNTING
--1)先查询出部门名称=SALES 或 ACCOUNTING对应的部门编号
select deptno from dept where dname in('SALES','ACCOUNTING');

--子查询
select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

-- 查询工资等级为 2的员工信息
select * from emp;
select * from salgrade;
select * from emp where sal between 1201  and 1400 ;
select *
  from emp
 where sal between (select losal from salgrade where grade = 2) and
       (select hisal from salgrade where grade = 2);

-- 查询 销售部（SALES） 中 工资大于1500的员工信息
--查询的数据: 员工信息*
--来源: emp
--条件: 部门名称=SALES and sal>1500
select *
  from emp
 where sal > 1500
   and deptno = (select deptno from dept where dname = 'SALES');

select *
  from (select *
          from emp
         where deptno = (select deptno from dept where dname = 'SALES'))
 where sal > 1500;

-- 查询工资比SMITH高的同一部门的员工信息
--查询的数据: 员工信息*
--来源: emp
--条件: sal>SMITH的薪资  and deptno = SMITH的部门编号
select *
  from emp
 where sal > (select sal from emp where ename = 'SMITH')
   and deptno = (select deptno from emp where ename = 'SMITH');

--exists 存在即保留,存在即合法
select *
  from emp
 where exists (select deptno from dept where dname = 'SALES');

select *
  from emp
 where exists (select deptno
          from dept
         where dname in ('SALES', 'ACCOUNTING')
            and emp.deptno =dept.deptno);
            
--别名

select *
  from emp e
 where exists (select deptno
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
            and e.deptno =d.deptno);
            
--20部门的员工信息
select *
  from emp e
 where not exists (select deptno
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
            and e.deptno = d.deptno);
            
            
--有奖金的员工信息
select empno, ename, sal,comm
  from emp e1
 where exists (select empno, ename, sal, comm
          from emp e2
         where comm is not null
           and e1.empno = e2.empno);
    
--与有奖金的员工统一部门的所有员工的员工信息     
select empno, ename, sal,deptno
  from emp e1
 where exists (select empno, ename, sal, comm,deptno
          from emp e2
         where comm is not null
           and e1.deptno = e2.deptno);

4)单行函数

--函数
--单行函数:一条数据返回一个结果
--多行函数|组函数|聚合函数:多条数据返回一个结果

-- 当前时间
select sysdate from dual;
select current_date from dual;
select ename,sysdate from emp;

-- 加减日期
-- 2天以后是几号
select sysdate+2 from dual;

-- 所有员工入职的3天前是几号
select ename,hiredate,hiredate-3 from emp;


-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select ename,hiredate 入职日期,hiredate+30*3 转正日期 from emp;
--add_months() 月份相加
select ename,hiredate 入职日期,add_months(hiredate,3) 转正日期 from emp;

-- 查询所有员工到目前为止一共工作了几个月
select ename,months_between(sysdate,hiredate) from emp;

-- 查询当前月的最后一天
select last_day(sysdate) from dual;
select last_day(hiredate) from emp;

-- 下一个星期三是几号
select next_day(sysdate,'星期一') from dual;

--日期对象与字符串之间的转问题
-- to_date('字符串','识别日期字符串模板')
-- 设定一个特定的时间（用一个特定的时间字符串转换为日期）
select to_date('2019-07-30 10:11:13','yyyy-mm-dd hh24:mi:ss')+3 from dual;
select to_date('2019年07月30日 10:11:13','yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

-- 将日期转为特定格式的字符串 to_char()
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;


-- decode 判断decode(判定字段，校验字段值1，结果1，校验字段2，结果2。。,默认值)
--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,decode(deptno,10,'十',20,'二十',30,'三十',40,'四十') 中文名字 from dept;

-- 给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水
select ename,sal 原薪水,deptno 部门编号,decode(deptno,20,sal*1.1,sal) 涨薪后的薪水 from emp;
--或
SELECT ENAME,SAL,DEPTNO,(CASE DEPTNO WHEN 20 THEN SAL*1.1 ELSE SAL END ) 涨薪 FROM EMP;

5)组函数

-- 组函数: count() sum() max() min() avg()
--对确定的结果集使用函数得结果
--注意: select后 组函数不能和非组函数或分组字段一起使用
--注意: where 不能使用组函数

-- 统计一下一共有多少个员工
--count(*|1|字段)
select 1 from emp;
select count(1) from emp;

-- 统计一共有几个部门 
select count(*) from dept;

-- 统计有员工存在的部门总数
select distinct deptno from emp;
select count(distinct deptno) from emp;
select count(distinct 1) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno=20;

--sum()
-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno = 20;

--max()  min()
-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;


--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno=30;


-- 请查询出 20部门的平均工资
select avg(sal) from emp where deptno=20;


--null值不参与运算
-- 计算出所有员工的奖金总和
select sum(comm) from emp;
select sum(comm) from emp where comm is not null;

-- 统计有奖金的员工有几个
select count(1) from emp where comm is not null;

--查询 最高薪水的员工姓名，及薪水
select max(sal) from emp;
select ename, sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资
select 

--查看 高于本部门平均薪水员工姓名

6)分组

--分组
--select 数据 from 数据源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段;
--执行顺序: from --where--group by--having--select--order by

--所有有员工存在的部门编号
select distinct deptno from emp;

select deptno from emp group by deptno;

--10,30,部门的每个部门最高薪资
select max(sal)  from emp group by deptno;
--先过滤再分组
select max(sal) from emp where deptno in(10,30) group by deptno ;
--先分组再过滤
select max(sal),deptno  from emp group by deptno having deptno in(10,30);

--如果有分组,select后只能跟分组字段和组函数

7)子查询+行转列

create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;

select *　from tb_student;

--使用一条sql语句,查询出没门课程分数都>80分的学生名字
--查询的数据: 学生名字 name
--数据的来源: tb_student
--条件: 没门分数都要>80 and 每一个人的总课程数=3
select name
  from tb_student
 group by name
having min(score) > 80 and count(1) = (select count(distinct course)
                                         from tb_student);
--分析
--每个人如果最小分数都>80,证明所有成绩都>80
select name from tb_student group by name having min(score)>80;
--一共有几门课程
select count(distinct course) from tb_student;
select name from tb_student group by name having count(1) =3;

--行转列
select name,
       min(decode(course, '语文', score)) "语文",
       max(decode(course, '数学', score)) "数学",
       avg(decode(course, '英语', score)) "英文"
  from tb_student
 group by name;
 -----------
select name,
       min(decode(course, '语文', score)) "语文",
       max(decode(course, '数学', score)) "数学",
       avg((CASE course WHEN '英语' THEN score ELSE 0 END)) "英文"
  from tb_student
 group by name;
---------------
select name,
       min(decode(course, '语文', score)) "语文",
       max(decode(course, '数学', score)) "数学",
       avg(NVL(decode(course, '英语', score),0)) "英文"
  from tb_student
 group by name;

8)rowid和rownum

--rowid和rownum都是伪列
--rowid相当于表中每一个条记录的地址,数据插入到表中的时候就已经存在,后续不会改变
select empno,ename,rowid from emp;

--去重,没有主键,没有唯一的字段,可以存在多条数据重复,想要达到去重,可以使用rowid


insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);

select * from tb_student;

--查到唯一的不重复的每一条数据,重复的数据只显示一条
select name,course,score,min(rowid) from tb_student group by name,course,score;

select * 　from tb_student where rowid in (select min(rowid)
                                            from tb_student
                                           group by name, course, score);

--查询要删除的数据
select * 　from tb_student where not rowid in (select min(rowid)
                                            from tb_student
                                           group by name, course, score);

--删除这些数据
delete from tb_student
 where not rowid in
        (select min(rowid) from tb_student group by name, course, score);
        
--rownum结果集中数据的序号
select empno,ename,sal,rownum from emp order by sal;
select deptno,dname,rownum from dept;


--分页查询
select empno,ename,sal,rownum from emp where rownum<=5;
--rownum不确定,因为结果集不确定
select empno,ename,sal,rownum from emp where rownum>=6 and rownum<=10;
--如果结果集能确定,对以确定的rownum进行判断,肯定能够判断
select empno,ename,sal,rownum,n from (select empno,ename,sal,rownum n from emp) where n>=6 and n<=10;

--一页显示4条数据 num=4,显示也是  i 
-- i=1  起始数据的rownum=1  结束:rownum=4
-- i=2  起始数据的rownum=5  结束:rownum=8
-- i=3  起始数据的rownum=(i-1)*num+1=9  结束:rownum=i*num=12

--根据薪资进行升序排序,达到分页的效果一页显示4条数据 num=4,显示第二页的数据i=2

select empno,ename,sal,rownum n from emp order by sal; 
--对已经确定的数据,按照执行的要求排序后的,rownum已经确定的,有规律的,从1开始,每次+1
select empno,ename,sal,rownum n from (select empno,ename,sal,rownum from emp order by sal); 

--分页
select empno, ename, sal,n
  from (select empno, ename, sal, rownum n
          from (select empno, ename, sal, rownum from emp order by sal))
 where n >= 5
   and n <= 8;

9)表连接 92语法

--查询每一个员工的员工信息,以及所在的部门信息
--数据: 员工信息,部门信息
--来源:emp,dept
--连表查询
--92,99语法

--92语法
--笛卡尔积
select * from emp,dept order by sal;

--等值连接  可以是两个表中的相同字段做连接,可以是不同字段做连接,但是类型要保持一致
select * from emp,dept where emp.deptno=dept.deptno;
select * from emp,dept where emp.ename=dept.dname;

--非等值连接
--查询每个员工的信息以及这个员工的薪资等级
select *　from emp,salgrade where sal between losal and hisal;

--查询2500薪资的等级
select grade from salgrade where 2500 between losal and hisal;

--查询 每一个的员工的员工信息,薪资等级,部门信息
select * from emp,dept,salgrade where emp.deptno=dept.deptno and sal between losal and hisal;
--查询 30部门员工的 每一个的员工的员工信息,薪资等级,部门信息

--92语法中,相同的字段一定要指明字段的出处
select *
  from emp, dept, salgrade
 where dept.deptno = 30
   and emp.deptno = dept.deptno
   and sal between losal and hisal;

--30部门的员工信息及部门信息
--先连接后过滤
select empno,ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=30;
--先过滤后连接,效率较高
select empno, ename, e.deptno, dname
  from (select * from emp where deptno = 30) e,
       (select * from dept where deptno = 30) d;
       
--查询所有有上级的员工信息及其上级经理人信息  自连接
--员工表 e1和经理人表 e2
select * from emp e1,emp e2 where e1.mgr=e2.empno;

--查询所有的员工信息及其上级经理人信息 所有员工都应该显示
--想要无论是否满足连接条件,都要显示其中数据的表->主表

--外链接
--做为主表的表中的数据全部显示
--在连接条件的位置,在主表对面的表的连接条件后添加(+)
-- emp e1,emp e2 主表的位置确定,主表在左边叫做左连接,主表在右边,叫做右连接
select * from emp e1,emp e2 where e1.mgr=e2.empno(+); --左连接
select * from emp e2,emp e1 where e1.mgr=e2.empno(+); --右连接

今天丶

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SQL基础操作

1)SQL查询--注释--select *|字段名1,字段名2... from 表名;--1)--查询|检索|获取所有员工的所有信息--查询的数据: 员工的所有信息--数据的来源:员工表 emp--条件:select * from emp;--2)--查询所有的员工名字select ename from emp;--3)--查询所有的员工编号和员工名称,员工上级的...
复制链接

扫一扫