数据库个人总结

--新建用户
create user aaa identified by "123";


--授予权限
grant connect,resource to aaa;


--创建表
create table t_food(


       t_id number(3),
       t_name varchar2(20),
       t_Company varchar2(30),
       t_Price number(5,2),
       t_Produce_time date,
       t_Validity_time number(3),
       t_Address varchar2(50)


);




--1) 用第一题创建的用户登录，用PLsql工具客户端创建如上表。


--2) 用命令create table 创建如上表t_food
--3) 往t_food表增加一个字段联系人contract,类型是varchar2

      --在表中添加字段：alter table 表名 add 字段名 字段类型

      alter table t_food add contract varchar2(20);


--4) 将Validity_time字段的类型改成varchar2
      --修改表中的字段类型：alter table 表名modify (字段名 修改后的类型);
      alter table t_food modify(t_Validity_time varchar2(20));


--5) 将Validity_time字段的名字改成validity
      --修改表中字段名：alter table 表名 rename column 原字段名 to 新字段名;
      alter table t_food rename column t_Validity_time to t_validity;


--6) 将Produce_time字段删除
      --删除表中的字段：alter table 表名 drop column 字段名;

      alter table t_food drop column t_Produce_time;

--玩家表
create table player(


       p_id number(3) primary key,
       p_name varchar2(20)


);


--英雄表
create table hero(
       h_id number(3) primary key,
       h_name varchar2(50)
);

--添加外键
--关联玩家表和中间表
alter table m_main add constraint main_foreign_p_id foreign key(m_p_id) references player(p_id);


--关联英雄表和中间表
alter table m_main add constraint main_foreign_h_id foreign key(m_h_id) references hero(h_id);

   --1、选择部门30中的雇员 
    select * from emp where deptno=30;
　　
　--2、列出所有办事员的姓名、编号和部门编号
    select ename,empno,deptno from emp where job='办事员';
　　
　--3、找出佣金高于薪金的雇员
　select * from emp where comm>sal;


　--4、找出佣金高于薪金60%的雇员
        select * from emp where comm>sal*0.6;
　
　--5、找出部门10中所有程序员和部门20中的所有办事员的详细资料
        select * from emp where deptno=10 and job='程序员';
        select * from emp where deptno=20 and job='办事员';
        
        select * from emp where (deptno=10 and job='程序员') or (deptno=20 and job='办事员');
　
　--6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
　     select * from emp where deptno=10 and job='程序员';
         select * from emp where deptno=20 and job='办事员';
         select * from emp where job!='经理' and job!='办事员' and sal>=2000;


         select * from emp where (deptno=10 and job='程序员') or (deptno=20 and job='办事员')
          or(job!='经理' and job!='办事员' and sal>=2000);
          
　--7、找出收取佣金的雇员的不同工作
          --distinct 去重复
          select distinct job from emp where comm>0;
　　
　--8、找出不收取佣金或收取的佣金低于100的雇员
　       select * from emp where comm<100 or comm is null;
　
　--9、找出早于25年之前受雇的雇员
        --(当前时间-入职日期)/365>25
        select * from emp where (sysdate-hiredate)/365>25;


   --10、显示在一个月为30天的情况下所有雇员的日薪金
        select ename,sal/30 from emp,


　--11、显示雇员的详细资料，按编号排序
        select * from emp order by empno asc;


  --12、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
        select ename,job,sal from emp order by job desc,sal asc;

--创建用户：create user 用户名 identified by "密码";
create  user java identified by "123";


--授予权限(角色) ：grant 角色|权限 to 用户名;
grant connect to java;


grant resource to java;

--修改密码：alter user  用户名 identified by "新密码";
alter user java identified by "qwe";


--收回权限|角色：revoke 权限|角色 from 用户名;
revoke resource from java;


--锁定用户(解锁)：alter user 用户名 account lock|unlock;
alter user java account lock;


alter user java account unlock;

--在表中添加一个字段：alter table 表名 add 字段名 字段类型;
alter table student add s_pone number(11);


--修改表中的字段类型：alter table 表名 modify (字段名 字段类型);
alter table student modify (s_sex char(6));

--修改表中的字段：alter table 表名 rename column 原字段名 to 新字段名;
alter table student rename column s_pone to s_address;


--删除表中的字段：alter table 表名 drop column 字段名;
alter table student drop column s_phone;

--分组查询、聚合函数：  
    count(*);
    max();
    min();
    avg();
    sum();
    
--多表连接：
      --交叉连接、左连接、右连接、内连接、外链接(全连接)
      
      a>交叉连接：得到的总数=两个表的数据的积
      select * from emp,dept;
      
      b>左连接：以左表为主，显示是左表所有的数据，只有右表有的数据不显示(数据查询不完整)
      select * from 表1 left join 表2 on 连接条件;
      select * from emp left join dept on emp.deptno=dept.deptno;
      select * from dept left join emp on dept.deptno=emp.deptno;
      
      c>右连接：以右表为主，显示的是右表所有的数据，只有左表有的数据不显示(数据查询不完整)
      select * from 表1 right join 表2 on 连接条件;
      select * from emp right join dept on emp.deptno=dept.deptno;
      select * from dept right join emp on dept.deptno=emp.deptno;
      
      --内连接：查询两张表交集
      select * from 表1 inner join 表2 on 连接条件;
      select * from emp inner join dept on emp.deptno=dept.deptno;
      
      --外连接：
      select * from 表1 full join 表2 on 连接条件;
      select * from emp full join dept on emp.deptno=dept.deptno; 
 
      
      


 
      --1、列出至少有一个雇员的所有部门
      select deptno,count(*) from emp group by deptno having count(*)>=1;
　
      --2、列出薪金比"张三"多的所有雇员
             --1.查询张三的薪金
             select sal from emp where ename='张三';
             --查询薪金大于张三
             select * from emp where sal>(select sal from emp where ename='张三');
　　
　   --3、列出所有雇员的姓名及其工作地点
             select a.ename,dept.loc from dept,
             (select ename,deptno from emp) a
             where dept.deptno=a.deptno
　　
　　
　　--5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
             select dept.dname,emp.* from dept left join emp on dept.deptno=emp.deptno;
　
　　--6、列出所有“CLERK”（办事员）的姓名及其部门名称
             select ename,dname from emp left join dept on emp.deptno=dept.deptno where job='办事员';
　
　　--7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
             select job,min(sal) from emp group by job having min(sal)>1500;


　　--8、列出从事“SALES”（销售）工作的雇员的姓名，及部的部门编号
             select ename,deptno from emp where job='销售';
　
　　--9、列出薪金高于公司平均水平的所有雇员
             --1.查询公司平均薪资水平
             select avg(sal) from emp;
             --2.查询高于公司平均薪资的
             select * from emp where sal>(select avg(sal) from emp);
　
　　--10、列出与“张三”从事相同工作的所有雇员
             --查询张三的工作
             select job from emp where ename='张三';
             --查询工作与张三相同的
             select * from emp where job=(select job from emp where ename='张三');


　　--11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
             --1.查询部门30中的雇员薪资
             select sal from emp where deptno=30;
             --2.查询薪资等于30号部门中任意一个的
             select ename,sal from emp where sal in (select sal from emp where deptno=30);
　
　　--12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金
             --1.查询30号部门的最高薪资
             select max(sal) from emp where deptno=30;
             --2.查询工资大于30号部门的最高工资的人
             select * from emp where sal>(select max(sal) from emp where deptno=30);
　　
　　--13、列出每个部门的信息以及该部门中雇员的数量
              select dept.*,a.mun from dept left join
              (select deptno,count(1) mun from emp group by deptno) a
              on dept.deptno=a.deptno;


　　--14、列出所有雇员的雇员名称、部门名称和薪金
　             select ename,dname,sal from emp left join dept on emp.deptno=dept.deptno
　　


　　--16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员
　           select dept.*,a.mun from dept left join
              (select deptno,count(1) mun from emp group by deptno) a
              on dept.deptno=a.deptno;


　　--17、列出各种类别工作的最低工资
              select job,min(sal) from emp group by job;
　
　　--18、列出各个部门的MANAGER（经理）的最低薪金
              --1.查询所有经理的信息
              select * from emp where job='经理';
              --2.分组  查询最低工资
              select deptno,min(sal) from emp where job='经理' group by deptno;
　
　　--19、列出按年薪排序的所有雇员的年薪
              select sal*12 yearsal from emp order by yearsal;
　　
　　--20、列出薪金水平处于第三位的雇员
              select * from 
              (select ename,sal,rank() over(order by sal desc) pm from emp) a where a.pm=3

--1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
       select * from
       (select * from sc where cno='c001') a,
       (select * from sc where cno='c002') b
       where a.sno=b.sno and a.score>b.score;


--2、查询平均成绩大于60 分的同学的学号和平均成绩；
       select sno,avg(score) from sc group by sno having avg(score)>60;


--3、查询所有同学的学号、姓名、选课数、总成绩；
       select student.sno,student.sname,a.选课数,a.总成绩 from student full join 
       (select sno,count(1) as 选课数, sum(score) as 总成绩 from sc group by sno) a
       on student.sno=a.sno;




--4、查询姓“刘”的老师的个数；
       select count(1) from teacher where tname like '刘%';


--5、查询没学过“谌燕”老师课的同学的学号、姓名；
       
       --1.查询谌燕老师的编号
       --2.根据老师编号查询她所教的课程
       --3.根据课程编号查询所学这些课程的学生
       --4.查询没学过该老师课程的学生
       select * from student where sno not in(
       select distinct sno from sc where cno in
       (select cno from course where tno=
       (select tno from teacher where tname='谌燕')));


6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
       --1.查询学过C001课程的学号
       select sno from sc where cno='c001';
       --2.查询学过c002课程的学生
       select sno from sc where cno='c002';
       --3.查询既学过c001又学过c002课程的学生
       select a.sno,student.sname from 
       (select sno from sc where cno='c001') a,
       (select sno from sc where cno='c002') b,student
       where a.sno=b.sno and b.sno=student.sno;


7、查询学过“谌燕”老师所教的课的同学的学号、姓名；
       --1.查询谌燕老师的编号
       select tno from teacher where tname='谌燕';
       --2.根据老师的编号查询所教课程
       select cno from course where tno=
       (select tno from teacher where tname='谌燕');
       --3.根据课程编号查询所教学生编号
       select distinct sno from sc where cno in
       (select cno from course where tno=
       (select tno from teacher where tname='谌燕'));
       --4.根据学号查询学生姓名
       select * from student where sno in
       (select distinct sno from sc where cno in
       (select cno from course where tno=
       (select tno from teacher where tname='谌燕')));






8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
       --1.查询学过c002课程的学号，成绩
       select * from sc where cno='c002'
       --2.查询学过c001课程的学号，成绩
       select * from sc where cno='c001';
       --3.查询同时学c001和C002课程的学生，并且课程c002成绩小于课程c001
       select a.sno,student.sname,b.score,a.score from
       (select * from sc where cno='c002') a,
       (select * from sc where cno='c001') b,student
       where a.sno=b.sno and a.score<b.score and a.sno=student.sno


--9、查询所有课程成绩小于60 分的同学的学号、姓名；
         select sno,sname from student where sno in
         (select sno from sc group by sno having min(score)<60);




--10、查询没有学全所有课的同学的学号、姓名；
         --1.查询有多少门课程
         --2.查询选课数小于所有课程数
         select student.sno,student.sname,a.选课数 from student full join
         (select sno,count(*) 选课数 from sc group by sno having count(*)<
         (select count(*) from course)) a on student.sno=a.sno;



--聚合函数：
          count(1);--记录数
          avg();--平均数
          max();--最大值
          min();--最小值
          sum();--求和
  
-- 查询员工表有多少员工
          select count(*) from emp;
--查询整个公司的平均工资水平
          select avg(sal) from emp;
--查询最高工资是多少
          select max(sal) from emp;
--查询最低工资是多少         
          select min(sal) from emp;
--查询所有工资的和
          select sum(sal) from emp;
          
          
--分组查询：分组查询一般与聚合函数一起使用
          select 分组依据或聚合函数 from 表名 where 条件 group by 分组依据  having 条件;
          
          
          --查询男生女生各有多少学生
          select s_sex,count(1) from s_student group by  s_sex;  
          
          --查询每个部门各有多少人
          select deptno,count(*) from emp group by deptno;
          
          --查询做每种工作的有几个人
          select job,count(*),avg(sal) from emp group by job;
          
          --多字段分组：查询每个班级男生女生各有多少人
          select s_class,s_sex,count(1) from s_student group by s_class,s_sex;
          
          --查询至少有2名学生的班级
          select s_class,count(*) from s_student group by s_class having count(*)>=2;
          
          --根据不同年龄段分组：统计【16-22】之间有多少人，【23-30】之间有多少人，大于30有多少人
          语法：case  when ...then... else...end
          select
                   sum(case when s_age between 16 and 22 then 1 else 0 end) as "【16-22】",
                   sum(case when s_age between 23 and 30 then 1 else 0 end) as "【23-30】",
                   sum(case when s_age >30 then 1 else 0 end) as"【大于30】"
          from s_student;
          
          --请根据不同性别统计：统计【15-22】之间有多少人，【23-30】之间有多少人，大于30有多少人
          select s_sex,
                   sum(case when s_age between 15 and 22 then 1 else 0 end) as "【15-22】",
                   sum(case when s_age between 23 and 30 then 1 else 0 end) as "【23-30】",
                   sum(case when s_age >30 then 1 else 0 end) as"【大于30】"
          from s_student group by s_sex;
          
--常用的函数：
          --查询员工的姓名和入职日期（只显示年月日）
          select ename, to_char(hiredate ,'yyyy-MM-dd') as 入职日期 from emp;
          
          select ename,hiredate as 入职日期 from emp;
          --decode用法：
          select s_name,decode(s_sex,'男','帅哥','女','靓妹','人妖') as 性别 from s_student;
          --nvl用法：判断空值
          select s_name,nvl(s_sex,'不详') as 性别 from s_student;
          
          --where 和 having:
          where是判断表数据，直接剔除不符合要求的数据
          having是判断表中表，剔除表中表中不符合要求数据
          
--1. 显示平均工资为>2000的职位
          select job,avg(sal) from emp group by job having avg(sal)>2000;




--2. 计算工资在2000以上，各种职位的平均工资大于3000的职位及平均工资
          select job,avg(sal) from emp where sal>2000 group by job having avg(sal)>3000 ;




--3. 找每个部门的最高和最低的工资
          select deptno,max(sal),min(sal) from emp group by deptno;




--4. 找每个部门中每种职位的最高和最低的工资
          select deptno,job,max(sal),min(sal) from emp group by deptno,job;


--5. 显示出工作名称(job)中包含"办事"的员工平均工资，最高工资，最低工资及工资的和
          select avg(sal),max(sal),min(sal),sum(sal) from emp where job like '%办事%';


--6. 显示出20号部门的员工人数
          select count(*) from emp where deptno=20;


--7. 显示出平均工资大于2000的部门名称及平均工资
          select dept.dname,a.pjsal from dept,
          (select deptno ,avg(sal) pjsal from emp group by deptno having avg(sal)>2000) a
          where dept.deptno=a.deptno;






          select dept.dname,a.pjsal from dept,
          (select deptno,avg(sal) as pjsal from emp group by deptno having avg(sal)>2000) a
          where dept.deptno=a.deptno;


--8. 显示每个部门每种工作平均工资大于2500的部门名称及工作
          select dept.dname,a.job,a.pjsal from dept,
          (select deptno,job,avg(sal) pjsal from emp group by deptno,job having avg(sal)>2500) a
          where dept.deptno=a.deptno;


--9. 列出最低工资大于1500的各种工作
          select job,min(sal) from emp group by job having min(sal)>1500;


--10. 列出各部门的员工数量及平均工作年限
          --入职时间  avg((sysdate-入职时间)/365)      sum(sysdate-入职时间)/count(1)
          
          select deptno,count(*),avg((sysdate-hiredate)/365) from emp group by deptno;
          


          
         --约束：
    好处：保证了数据的准确性和完整性
    缺点：降低了操作效率
    
--创建表：create table 表名( 字段名  数据类型, 字段名 数据类型);    
    --数据类型： 数字型  number(3)   number(6,2)
                      --字符串  char(3)存放固定长度的字符串   varchar2(20) 存放可变长度的字符串
                      --时间类型   date  年月日时分秒  timestamp  年月日十分秒 时区 秒后6位
                      --clob  存放大文本   blob 存放二进制 图像 视频 音频
                      
create table users(
       u_id number(3),
       u_name varchar2(20),
       u_sex char(3),
       u_age number(3)


);
    


    
--1.非空约束
    --1.创建表时，设置非空
     create table aaa(
            a_id number(3) not null,
            a_name varchar2(20) not null
     );
     
     insert into aaa(a_id,a_name) values(1,'XXX');
     insert into aaa(a_id) values(2);
     
     create table ccc(
            c_id number(3) not null,
            c_name varchar2(20) not null,
            c_sex char(3) default '男'
     );
     insert into ccc(c_id,c_name) values(1,'XXX');
     
     --2.建表之后，设置非空：alter table 表名 modify(字段名 not null);
     alter table student modify (s_age not null);


--2.唯一约束：1.唯一，不能重复,可以为空 2. 自动创建索引，快速查找 3.可以设置多个列为唯一
     --1.创建表时，设置唯一约束
     create table aaa(
            a_id number(3) unique,
            a_name varchar2(20)
     
     );


     insert into aaa(a_name) values('XXX');
     
     --2.表创建后，设置唯一约束：alter table 表名 add constraint 约束标识名 unique(字段名);
     alter table aaa add constraint aaa_unique_name unique(a_name);
     insert into aaa(a_id,a_name) values(2,'XXX');


--3.主键约束：1.唯一，不能重复，不能为空 2.自动创建索引，快速查询
                   --3.一张表只能有一个主键（不止有一个列，也可以由多个列组成，复合主键）
     --1.创建表时，设置主键约束
     create table bbb(
            b_id number(3) primary key,
            b_name varchar2(20)
     );
     insert into bbb values (1,'XX');
     
     --2.在创建表之后，设置主键：alter table 表名 add constraint 约束标示名 primary key(字段名);
     alter table student add constraint student_primary_id primary key(s_id);
     
     alter table bbb add constraint bbb_primary_id_name primary key(b_id,b_name);


--4.check约束：某一个字段的值按着一定的格式，在一定的范围中
     --创建表的时候，设置check约束
     create  table ccc(
             c_id number(3) primary key,
             c_name varchar2(20),
             c_sex char(3),
             c_age number(3) check(c_age >=0 and c_age<=130)
     );
     
     create  table ccc(
             c_id number(3) primary key,
             c_name varchar2(20),
             c_sex char(3),
             c_age number(3) check(c_age between 0 and 130)
     );
     --添加数据：insert into 表名(字段名) values(' 对应的值');
     insert  into  ccc(c_id,c_name,c_age) values(2,'XXX',30);
     
     --创建表之后，设置check约束：alter table表名 add constraint 约束标识名 check(字段条件);
     alter table ccc add constraint ccc_check_sex check(c_sex ='男' or c_sex='女' );
     alter table ccc add constraint ccc_check_sex check(c_sex in('男','女'));


     insert into ccc(c_id,c_name,c_sex) values(4,'XXX','男');




--5.外键约束
     --1.创建表时，设置外键
     create table c_class(
            c_id number(3) primary key,--设置主键
            c_name varchar2(20)
     );
     
     create table s_student(
            s_id number(3) primary key,
            s_name varchar2(20),
            s_class number(3),
            constraint student_foreign_class foreign key(s_class) references c_class(c_id)
     );
     insert into c_class values(1,'java1703');
     insert into s_student values(1,'XX',1);
     
     --创建表之后，设置外键：alter table 从表 add constraint 约束标识名 foreign key(外键) references 主表(主键);
      alter table s_student add constraint student_foreign_class foreign key(s_class) references c_class(c_id);


--序列：用来生成连续整数数据类型的对象（通常用来作为主键中增长列）
-- Create sequence 创建
create sequence USERS_SEQUENCES
minvalue 1 --最小值
maxvalue 999999999999999999999999999--最大值
start with 1 --初始值
increment by 1 --增量
cache 20;--缓存


--1>在select中使用
      select users_sequences.nextval from dual;--dual伪表
      select users_sequences.currval from dual;--当前序列值  注意：currval和nextval要同一个会话中
--2>在insert中使用
      insert into users values(users_sequences.nextval,'XX','男',21);
      insert into s_student values(users_sequences.nextval,'XX',1);
      
--不同的表可以使用同一个序列，但是一般情况一张表对应一个序列


--插入
      --1>单行插入
             --insert into 表名(字段名,字段名) values (对应的值);
             --注意：字符串要用单引号，如果值与字段一一对应，可以省略字段名
             insert into s_student(s_id,s_name) values(2,'XXX');
             insert into s_student(s_id,s_class,s_name) values (4,1,'XX');
             
      --2>多行插入
             --a>将其他表中的数据插入到本表中  注意：不需要values
              insert into s_student(s_id,s_name) select u_id,u_name from users;
              
             --b>复制表（不复制约束）
                     -->复制表结构和数据
                     create table student_beifen as select *from s_student;
                     -->复制表结构
                     create table student_beifen1 as select * from s_student where 1=6;
                     


--删除
           --delete from 表名 where 条件  
           delete from student_beifen where s_name='XX';
           delete from s_student where s_name='XX';
           
           --删除表所有记录
           delete from student_beifen;--执行效率低，可以回滚
           truncate table student_beifen;--执行效率高，不可以回滚
           
           
           commit;--提交


--修改
           --update 表名 set 字段名=修改后的值,字段名=修改后的值 where 条件;
           --把id为31的学生姓名改为曾勇
           update s_student set s_name='XX' where s_id=31;
           
           --把id为21的姓名改为曾勇 班级编号改为1
           update s_student set s_name='XX', s_class=1 where s_id=21;


           --把所有的学生班级编号都改为1
           update s_student set s_class=1;
           
           
           --在表中添加一个字段：alter table 表名 add 字段名 数据类型;
           alter table s_student add s_birth date;
           
           --把所有的学生生日都改为今天的日期
           update s_student  set s_birth=sysdate;


           select sysdate from dual;
           
           --把其他表中的值设置到本表中( 把学生表里id为21的姓名改成 英雄表里id为1的姓名)
           update s_student set s_name=(select h_name from hero where h_id=1) where s_id=21;


--查询
           --select * from 表名 where 查询条件  order by 排序的列名  ASC或DESC
           
           --查询班级编号为1班的所有学生信息
           select * from s_student where s_class=1;
           --查询班级编号为1或者为2的所有学生信息
           select * from s_student where s_class=1 or s_class=2;
           --查询班级编号为1 id也为1的学生信息
           select * from s_student where s_class=1 and s_id=1;
           
           --没有班级的学生信息(不能用=)
           select * from s_student where s_class is null;




           --like  模糊查询  _：一个字符   %：任意个字符
           --查询姓张的所有学生信息
           select * from s_student where s_name like '张%';


           --查询名字有两个字并且姓张的所有学生信息
           select * from s_student where s_name like'张__';
           
           --名字中有袁字的学生信息
           select * from s_student where s_name like '%袁%';
           
           --查询特定的列：查询所有学生的姓名和班级编号
           select s_name,s_class from s_student;
           
           --查询所有学生的姓名和班级编号 并且列名用中文显示
           --列名：as可以省略 ，双引号也可以省略,但是有特殊字符的时候不能省略双引号
           select s_name "【姓名】",s_class "【班级编号】" from s_student;
           
           --常量：字符型要用单引号，数值型可以省略单引号
           select s_name "【姓名】",s_class "【班级编号】",10086 as "【地址】" from s_student;
           
           --查询年龄在18到30岁之间的学生信息
           select * from s_student where s_age>=18 and s_age<=30;
           select * from s_student where s_age between 18 and 30;
           
           --查询所有女学生的学生信息，并且按照年龄的升序排序,如果年龄相同，按照学号的降序排序
           select * from s_student where s_sex='女' order by s_age asc, s_id desc;
           
           --子查询：查询java1703班的所有学生信息
           select c_id from c_class where c_name='java1703';
           
           select * from s_student where s_id=(select c_id from c_class where c_name='java1703');
           
       --聚合函数：
          count(1);--记录数
          avg();--平均数
          max();--最大值
          min();--最小值
          sum();--求和
  
-- 查询员工表有多少员工
          select count(*) from emp;
--查询整个公司的平均工资水平
          select avg(sal) from emp;
--查询最高工资是多少
          select max(sal) from emp;
--查询最低工资是多少         
          select min(sal) from emp;
--查询所有工资的和
          select sum(sal) from emp;
          
          
--分组查询：分组查询一般与聚合函数一起使用
          select 分组依据或聚合函数 from 表名 where 条件 group by 分组依据  having 条件;
          
          
          --查询男生女生各有多少学生
          select s_sex,count(1) from s_student group by  s_sex;  
          
          --查询每个部门各有多少人
          select deptno,count(*) from emp group by deptno;
          
          --查询做每种工作的有几个人
          select job,count(*),avg(sal) from emp group by job;
          
          --多字段分组：查询每个班级男生女生各有多少人
          select s_class,s_sex,count(1) from s_student group by s_class,s_sex;
          
          --查询至少有2名学生的班级
          select s_class,count(*) from s_student group by s_class having count(*)>=2;
          
          --根据不同年龄段分组：统计【16-22】之间有多少人，【23-30】之间有多少人，大于30有多少人
          语法：case  when ...then... else...end
          select
                   sum(case when s_age between 16 and 22 then 1 else 0 end) as "【16-22】",
                   sum(case when s_age between 23 and 30 then 1 else 0 end) as "【23-30】",
                   sum(case when s_age >30 then 1 else 0 end) as"【大于30】"
          from s_student;
          
          --请根据不同性别统计：统计【15-22】之间有多少人，【23-30】之间有多少人，大于30有多少人
          select s_sex,
                   sum(case when s_age between 15 and 22 then 1 else 0 end) as "【15-22】",
                   sum(case when s_age between 23 and 30 then 1 else 0 end) as "【23-30】",
                   sum(case when s_age >30 then 1 else 0 end) as"【大于30】"
          from s_student group by s_sex;
          
--常用的函数：
          --查询员工的姓名和入职日期（只显示年月日）
          select ename, to_char(hiredate ,'yyyy-MM-dd') as 入职日期 from emp;
          
          select ename,hiredate as 入职日期 from emp;
          --decode用法：
          select s_name,decode(s_sex,'男','帅哥','女','靓妹','人妖') as 性别 from s_student;
          --nvl用法：判断空值
          select s_name,nvl(s_sex,'不详') as 性别 from s_student;
          
          --where 和 having:
          where是判断表数据，直接剔除不符合要求的数据
          having是判断表中表，剔除表中表中不符合要求数据
          
--1. 显示平均工资为>2000的职位
          select job,avg(sal) from emp group by job having avg(sal)>2000;




--2. 计算工资在2000以上，各种职位的平均工资大于3000的职位及平均工资
          select job,avg(sal) from emp where sal>2000 group by job having avg(sal)>3000 ;




--3. 找每个部门的最高和最低的工资
          select deptno,max(sal),min(sal) from emp group by deptno;




--4. 找每个部门中每种职位的最高和最低的工资
          select deptno,job,max(sal),min(sal) from emp group by deptno,job;


--5. 显示出工作名称(job)中包含"办事"的员工平均工资，最高工资，最低工资及工资的和
          select avg(sal),max(sal),min(sal),sum(sal) from emp where job like '%办事%';


--6. 显示出20号部门的员工人数
          select count(*) from emp where deptno=20;


--7. 显示出平均工资大于2000的部门名称及平均工资
          select dept.dname,a.pjsal from dept,
          (select deptno ,avg(sal) pjsal from emp group by deptno having avg(sal)>2000) a
          where dept.deptno=a.deptno;






          select dept.dname,a.pjsal from dept,
          (select deptno,avg(sal) as pjsal from emp group by deptno having avg(sal)>2000) a
          where dept.deptno=a.deptno;


--8. 显示每个部门每种工作平均工资大于2500的部门名称及工作
          select dept.dname,a.job,a.pjsal from dept,
          (select deptno,job,avg(sal) pjsal from emp group by deptno,job having avg(sal)>2500) a
          where dept.deptno=a.deptno;


--9. 列出最低工资大于1500的各种工作
          select job,min(sal) from emp group by job having min(sal)>1500;


--10. 列出各部门的员工数量及平均工作年限
          --入职时间  avg((sysdate-入职时间)/365)      sum(sysdate-入职时间)/count(1)
          
          select deptno,count(*),avg((sysdate-hiredate)/365) from emp group by deptno;
          

//登陆数据库
cmd ->sqlplus /nolog->不登录数据库->进行sqlplus管理工具 
conn / as sysdba  管理员登陆数据库 本机可以省略账号和密码
//意思是：任意(空的也行)密码和账号都可以登录 即使不存在的账号和密码


单行注释：--
多行注释：/*......*/


conn / 用户名/密码 as sysdba 登陆其他主机上的数据库
//conn /用户名/密码 以普通身份登陆数据库


select * from dual; select sysdate from dual; 查看日期


--------------------------------------------------------------
//管理员->创建用户 create user 用户名 identified by 密码;
密码数字开头  用双引号


//;结束一条命令


grant 权限 to 用户; grant create session to 用户名 ->将会话权限授权给用户


grant 角色|权限 to 用户名;     不同角色->不同权限   多个角色或权限 用逗号


//常用角色  dba管理员-> 具备数据库的所有权限
 connect  -> 只具备连接数据库的权限     一般授权给临时用户
 resource -> 具备数据库的基本操作
 grant connect,resource to user; 具备连接权限和操作权限;


//一个窗口->一个用户使用


查看所有用户：
select * from dba_users;   
select * from all_users;   
select * from user_users;


-------------------------------
//删除用户
drop user 用户名 cascade; cascade 级联删除->删除用户所有数据
若该用户连接数据库时不允许删除
-------------------------------


//收回权限|角色
revoke 权限|角色 from 用户名;
例：revoke create session from 用户名;


//修改密码：alter user 用户名 identified by "新密码";


//锁定用户(解锁)：alter user 用户名 account lock|unlock;




//普通身份->normal->connect resource  管理员身份->sysdba 数据库操作员->sysoper->create view


//配置连接其它主机上的数据库：
1.查看ip地址
2.找配置文件 D:\oracle\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
  XE =                                                       
  //数据库连接字符串，可以随便命名，不能出现中文字符
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Hasee-PC)(PORT = 1521))
     //HOST=本地主机名称或本地主机localhost或者环回地址127.0.0.1
    //也可以是主机地址
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)//XE不能修改
    )
  )
  3.修改HOST
  4.关闭防火墙


  //数据库中哪些用户拥有DBA权限
  select * from dba_role_privs where granted_role='DBA'  
  //收回某用户的DBA权限则执行
  revoke dba from user_Name  


  //as sysdba方式登录的数据库？如果是这样，并且sqlnet中设置了操作系统认证，那么即使用户已经被删除了，
  还是可以登录到数据库，因为这时as sysdba实际上就是说我以sys这个用户登录！
--------------------------------------------------------------------
  //二维表：关系数据库
  姓名     性别     年龄
  张三   男 18        




User user=new User(); 
  user.setName("张三");




  列：java成员属性
  行：一个实例化对象


  -------------------------------------------------------
  1.创建表结构
  create table 表名 ( 字段名  数据类型(长度), 字段名 数据类型(长度));


  2.表的命名  t_student


  3.Oracle不区分大小写  系统会默认转化为大写


 ----------------------------------------------------------
结构化查询语言      SQL语言的分类：
DDL 数据定义语言        create alter drop truncate
DDM 数据操作语言        insert update delete
DQL 数据查询语言    select
DCL 数据控制语言        grant revoke
TCL 事务控制语言    commit rollback savepoint


----------------------------------------------------------------
1.对表添加注释
comment on table 表名 is '注释内容'


2.对字段添加注释
comment on column 表名.属性名 is '注释内容'


----------------------------------------------------------------
1.数据类型
数值类型：number 可以存储整数和小数
     number(p,s): p->整个长度     s->小数长度 默认0;
                 小数可以超过最大长度，小数部分自动45
     number 可以不指定长度 默认最大长度是38位




字符类型：
varchar2 可以存储4000个字节以内的数据    必须指定长度
    根据数据大小分配相应的内存空间
     数据大小不固定 使用varchar2类型


char     可以存储2000个字节以内的数据不指定时默认为1
     根据数据定义大小分配内存空间，不足空格填充
    数据大小固定 使用char类型


   char类型执行效率比varchar2高




日期类型：


date       年月日时分秒
timestamp  时间戳         年月日时分秒毫秒(秒的后6位)






-----------------------------表结构操作-------------------------------------
1.添加字段
alter table 表名 add 字段名 类型(长度);


2.修改字段类型
alter table 表名 modify 字段名 新类型(长度);


3.修改字段的名称
alter table 表名 rename column 原名称 to 新名称


4.删除字段
alter table 表名 drop column 字段名称


5.删除表
drop table 表名 cascade constraints 删除表同时删除表中相关的约束




--------------------------添加数据---------------------------
添加数据：insert into 表名(字段名) values(' 对应的值');
注意：1.字符要用单引号  2.添加的值和字段一一对应的时候可以省略字段名


insert into student(s_id,s_name,s_sex,s_age) values (1,'汪鹏','男',20);
insert into student values(2,'汪鹏','男',20);
insert into student(s_id,s_age,s_name) values(4,30,'邹老板');




-----------------------------约束-------------------------------
1.not null 非空 


2.default  默认  精简版对数字有效  企业版对数字和字符都有效


3.unique   唯一性约束  属性字段唯一性  可为空值并且可以多个
     每个属性都可以设置
     自动创建索引 提升查询效率


            alter table 表名 add constraint 约束的标示名 unique(字段名);


4.primary kye   主键约束 不能为空     表中只能有一个主键


     alter table 表名 add constraint 约束标示名 primary key(字段名);


     自动创建索引 提升查询效率




5.复合主键      alter table 表名 add constraint 约束标示名 primary key(字段名,字段名);








6.外键约束  
子表                 主表


学生表  班级名属性 | 班级表 班级名属性


java01                java01
                     若修改
每个需要改       < -  j1701


1                       1       java01
2    -> 设置外键关联    2       java02        关联班级       
3                       3   java03


外键：主表中的主键


表中进行关联，称之为主键关联


          可以避免数据库字段的冗余->多余的字段






       cascade     删除java01班 对应学生表的数据删除       级联删除：
            no action   子表有数据，不能删除
            set null    删除java01班 对应学生表的相应信息设置为空




---表中添加--
子表中添加外键：constraint 约束标示名 foreign key(字段) references 主表(字段);


--表建立后添加--
alter table 子表 add constraint 约束标示名 foreign key(字段) references 主表(字段);
-----------------------------总结--------------------------------------
当一个字段为空时不能修改
alter table s_studentcopy modify s_sex not null;


当一个字段属性为空时不能设置主键约束
alter table s_studentcopy add constraint pk_s_id  primary key(s_sex);


当一个字段有唯一约束不能同时设置主键约束
alter table s_studentcopy add constraint unique_s_id unique (s_id);
alter table s_student add constraint pk_s_id primary key(s_id);


7.check 设定字段取值范围
check (sex='男' or sex='女')


check (sex in('男','女))     varchar2(3)->一个汉字
check (sex not in('男','女)) 


check(age>=19 and age<=23)
check (age betweent 19 and 23)
check (age not betweent 19 and 23)
!=   <>   ----->   不等于   



commit 提交 
rollback    回滚


--DML 数据操作语言
--插入
insert into 表名(字段名,...字段名n)values(值，...值n);


sysdate 系统时间
to_date() 字符串类型日期数据 转化为 日期类型




--java中MM标示月份


select to_date('2015-09-06 13:30:24' 'yyyy-mm-dd HH:mi:ss')   12小时制
select to_date('2015-09-06 13:30:24' 'yyyy-mm-dd HH24:mi:ss') 24小时制


--dual 伪表 临时创建 客户端查看




--DATE 插入数据 需要转化


--last_day() 最后一天
--trunc()      第一天


--序列->一般用于主键插入数据 保证主键唯一性  seq+表名  
select 序列名.nextval from dual;   下一个序列数值
select 序列名.currval from dual; 当前序列数值




----------------------------------------------------------
多行插入
复制表结构及相关数据
create table  复制的表名  as  select  * from 表名


    --相当于备份了,但是约束没有复制过来，只复制表结构和数据；
 




复制表结构
create table  复制的表名  as  select  * from 表名 where 1<>1;








表数据插入到->另外一张表
insert into 要插入的表名(字段名,...)
                  select 相关字段名 from 表名;
commit;


字段类型相同时：
insert into student_1 select * from student_2;




修改
表数据设置到->另外一张表
update student_1 set (sname,ssex)=(select username,usersex from t_user where userid=3)
where sid in(36,23,24);


删除
delete from 表名 where 条件  需要提交和回滚


truncate table 表名
truncate 不需要提交 快速删除
--只是删除表数据，没有删除表结构
-------------------------------------------------
ascii码值范围
a-z：97-122


A-Z：65-90


0-9：48-57


 substr(char,m,n)：取字符串的子串，m表示起点，n 代表取 n 个字符的意思->注：第一个字符串起点是1


 ascii()函数将字符转换为ASCII码：字符 – > ASCII码；


 查询出  首字母是大写  的  字段名
 WHERE ASCII(SUBSTR(要截取的字段名,1,1)) >= 65 AND ASCII(SUBSTR(要截取的字段名,1,1))<=90;
 -------------------------------------------------------------------------------------------


lengthb(string)    计算string所占的字节长度：返回字符串的长度，单位是字节
length(string)     计算string所占的字符长度：返回字符串的长度，单位是字符


select length('票') from dual;       结果是  1
select lengthb('票') from dual;      结果是  3


--对于单字节字符,LENGTHB和LENGTH是一样的.
--如可以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。


--------------------------------------------------------------------------


upper(字符串)：转换为大写
lower(字符串)：转换为小写
initcap(字符串)：首字母大写
内容替换：replace()
SELECT replace('Hello', 'l', 'x') FROM DUAL
---------------------------------------------


where sex=null   性别值为null  和 sex is null  性别是null；




------------------------------------------------------------
DQL 
-----------------------------------------------------
select 字符名            3
from 表                  1       ->执行顺序
where 条件               2
group by 分组字段
having 过滤分组条件
order by 排序
-------------------------------------------------------------
select sid,sname,1 from student 


->  1表示常量列,一般都有加单引号，


数值类型可以省略  


则该列属性值都为1
------------------------------------------------------------------------
修改 类表名：as 可以省略，双引号也可以省略；有特殊字符时候不可以省略双引号
as  学号    as "[学号]"   
    学号       "[学号]"
    -------------------------------------------------------------
    拼接字符(||)
    select sid||'_'||sname from student;  显示  -> 1_康盼
    '是一个转义字符
    '''' -> '\''
    '\''||snam||'\''  ->  'sname'
 --------------------------------------------------------------


 like '康%'   康||'多个字符'
 --匹配任意个数的任意字符


 like '%康%'    '多个字符'||康||'多个字符'


 like '康_'


 -------------------------------
asc    升序 默认
desc   将序 
----------------聚合函数------------------------


count(*)  select    匹配+统计*(任意字段) 统计  未分组不能出现字段，可以出现常量
count(sex)，1       统计sex字段的个数,为空不统计
-----------------------------
select 1 from student; 
select count(1) from student;           统计
----------------------------------------> 
--效率高  常量列来统计  count(1)比count(*)高
sum()
avg()
max()
min()
-----------------------------------
select sex,count(1)                3------------列出信息->分组字段，不能出现其它字段
from student             1--------查找表     
group by sex     2-------对性别分组




分组单独使用--取消重复行----------使用distinct
select sex                        select distinct sex 
from student                      from student
group by sex


having 分组条件


select sex,count(1) 
from student
group by sex
having count(1)>10


------------------------------执行顺序
select sex,count(1)           4
from student                  1
where age>20                  2          
group by sex                  3
--------------------------------------
多字段分组
--先按城市分组，再按性别分组,统计人数
select add,sex,count(1)
from student
group by add,sex
--------------------------
分组和聚合函数一起使用时，先分组，在统计