oracle数据库的一些操作

1.启动oracle服务，使服务器处于启动状态。dos命令下启动这一个就可以了。
  dos命令下输入sqlplus ，如果提示输入账号、密码，说明安装成功
  --如果忘记密码：使用免密登陆
  1.1 dos窗口输入：conn/as sysdba 
  1.2 创建用户：create user 用户名 identified by 密码;
     --给用户授权：grant 权力/角色 to 用户名；-->dba
     --dos命令下切换用户：conn 用户名/密码
  1.3 dos命令显示当前用户：show user   
2.使用第三方（如PL/SQL）操作数据库，则需要启动两个服务，一个数据库服务，一个监听服务（服务器监听第三方的命令操作）
  OracleServiceXE 和 OracleXETNSListener
3.学习网路编程时，如何让别人登陆自己的数据库，或登陆别人的数据库
  1.1 要知道服务器的ip地址和端口号。
  1.2 找到oracle安装路径：D:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
  1.3 服务器端-->配置监听器 listener.ora
  以本人客户端为例，要登陆别人的服务器：

  
4. 查询
   select * from emp;--查询全部
   select * from emp where mgr is not null;--查询非空值
   select * from emp where mgr is null;
   --限定行数的查询
   select * from emp where rownum <=5;--rownum 是伪列，只允许使用<或<=
   --去掉重复行
   select distinct deptno from emp;--员工都被分配到了哪些部门
   select distinct deptno,job from emp;--查询每个部门的职位信息
   --查询结果排序
   select empno ,ename,sal from emp order by sal desc;--降序  asc升序
   --模糊查询 like 、between   to  、  %  、 _ 、in 、or 、not 、and 、
          --查询经理是7902、7839的员工编号，姓名，经理编号，部门编号，并排序
   select empno,ename,mgr,deptno from emp where mgr in(7902,7839) order by deptno desc;
   
5. Oracle数据库中的常用函数：字符函数
   1. 连接 concat(x,y)    
      select concat('hello','world') from dual;
      select 'hello'||'world' from dual;
   2. 首字母转大写 initcap(x)--可用户java的set/get方法
      select initcap('hello') from dual;
   3. 大小写转换 lower(x),upper(x)
      select lower('HELLO') ,upper('world') from dual;     
   4. 计算字符串的长度 length(x) --可用于账户/密码验证
      select length('helloworld') from dual;
   5. 删除左右字符串。第二个参数不写，默认为空格。ltrim(x)\rtrim(x)
      select ltrim('   hello') from dual;
      select ltrim('helloworld','he')from dual;--输出：lloworld
      select ltrim('helloworld','w') from dual;--输出：helloworld 没变
      select ltrim('helloworld','el') from dual;--输出：helloworld 没变
      select ltrim('helloworld','hle') from dual;--输出：oworld 只要第二个参数是前几个字母
      select rtrim('  hello   ') from dual;--输出：     hello。
      select rtrim('helloworld','dl') from dual;--输出：hellowor
   6. 替换 replace(x,y,z)
      select replace('helloworld','o','*') from dual;--输出：hell*w*rld
      select replace('helloworld','o') from dual;--输出：hellwrld 第三个参数不写，则直接删除要替换的字母    
   7. 截取子字符串 substr(x,y,z)
      select substr('helloworld',1,3) from dual;--输出：hel 从第y个开始截取，一共截取z个
      select substr('helloworld',2) from dual;--输出：elloworld 截取到最后
   8. 删除左右字符串 trim(x)
      select trim('   hello   world    ')from dual;--输出：‘hello   world’  
      
6. 数值函数：
   1.绝对值 abs(x)
   select abs(-2.3) from dual;
   2.向上取整 ceil(x)
   select ceil(20.35) from dual;
   3.向下取整 floor(x) 
   select floor(-30.25) from dual;
   4.round(x,y)
   select round(1234.5678,2.9)from dual;--输出：1234.56. 2.9 取整为2，保留两位小数。
   select round(1234.5678,-2.9) from dual;-- 输出：1200.-2.9 取整为-2，小数点左侧四舍五入
   5.求余数mod(x,y) x除以y的余数
   select mod(10,3) from dual;
   6.power(x,y) x的y次方
   select power(2,3) from dual;
   7.开平方 sqrt(x)
   select sqrt(10) from dual;
   8.随机数 dbms_random.value 
   select dbms_random.value from dual;--获得一个[0,1)的随机数
   9.获得当前日期
   select sysdate from dual;
7. 日期型函数
   1.获得当前系统时间:sysdate 
   select sysdate from dual;
   2.返回指定月后的某一天：add_months(x,y)
   select add_months(sysdate,1) from dual;--一个月后的今天
   3.返回两个日期之间的间隔月数：months_between(x.y)
   select months_between(sysdate,'14-1月-90') from dual ;
   4.返回指定月的最后一天 ：last_day(x)
   select last_day(sysdate) from dual;--输出：2018/10/31 22:16:25
   5.返回下周某一天的日期（返回下一个周几）：next_day(x,y)
   select next_day(sysdate,'星期一') from dual; --输出：2018/11/5 22:18:17
   --查询每个月倒数第三天入职的员工信息
   select * from emp where last_day(hiredate )-2 = hiredate;
   
8.类型转换：
   1.数值型转字符型：to_char(x,y)
   select to_char(1234.567,'9999.99') from dual; --输出：  1234.57.前面的不足用空格补齐。最大值为99999.99 超过这个数字会出错
   select to_char(1234.56, '00000.000') from dual; --输出： 01234.560。强制转为8位数，不足的补0.
   select to_char(1234.56, '$9999.99' ) from dual; -- 添加货币符号。
   select to_char(1234.56, 'L9999.99' ) from dual; -- 添加货币符号。
   2.日期转字符型
   select to_char(sysdate,'yyyy-mm-dd') from dual;
   select to_char(sysdate,'dd') from dual;
   3.字符转数值型
   select to_number('123.45') + 10 from dual;
   4.字符转日期
   select to_date('8-8月-08') from dual;--输出：2008/8/8
   5.日期之间的运算
   select sysdate - to_date('8-8月-08') from dual;--返回两个日期之间的月数
9. 其他函数 nvl(s,x) 当s为空置的时候，默认为x。
   select empno,ename,sal+nvl(comm,0) 总工资 from emp ;--没津贴的时候默认为0；
   --求津贴的平均值
   select avg(comm) from emp;--不计算空置
   select avg(nvl(comm,0)) from emp;--计算空置
10.多行函数（分组函数，聚合函数）max(x),min(x),sum(x),avg(x),count(x)
   --针对于多行得出一个结果
   --查询职位是‘CLERK’的平均服务年限
   select floor(avg(floor(months_between(sysdate,hiredate)/12))) from emp where job = 'CLERK';
   --查询最早入职的员工信息？？（需要子查询）
   select min(hiredate) from emp    
   --查询部门编号为30的薪资最低的员工的信息。??
   select min(sal) from emp where deptno = 30;
   ...........
   
11.分组查询 group by 分组之后的条件使用 having
   select ..from 表名 [where..] [group by..][having..][order by asc/desc]
   --查询每个部门的平均工资
   select deptno,avg(sal) from emp group by deptno;
   注意：select 后只允许跟  分组项 和 分组函数，deptno是分组项，avg()是分组函数
   --查询每个职位的总人数
   select job,count(*) from emp group by job;
   --查询每个职位的总人数大于3的职位：分组之后的筛选用having（对分的组筛选，选出符号条件的组）
   select job,count(*) from emp group by job having count(*)>3;
/*where和having的区别*/
   where 是分组之前的条件筛选，先筛选，后分组
   having 是分组之后的条件筛选，先分组，在对分的组进行筛选，选出满足条件的组
   --查询工资大于1000的平均工资的部门平均工资信息
   select deptno,avg(sal) from emp where sal>1000 group by deptno;
   --查询平均工资大于1000的部门平均工资信息
   select deptno,avg(sal) from emp group by deptno having avg(sal)>1000;
   --查询个人工资大于1000，且平均工资大于1000的部门平均工资信息
   select deptno,avg(sal) from emp where sal>1000 group by deptno having avg(sal)>1000;
   --统计人数少于4的部门的平均工资
   select deptno,avg(sal),count(* ) from emp group by deptno having count(*)<4;
   --统计各部门的最高工资，排除最高工资小于3000的部门
   select deptno,max(sal) from emp group by deptno having not max(sal)<3000;
12.DML操作语言：数据操作语言（Data Manipulation Language）。   
   insert/update/delete 需要手动提交。
   1.insert: insert into 表名 values (列值);插入所有的列
             insert into 表名(列名1，列名2) values(列值1，列值2);插入部分列
   insert into emp (empno,ename) values (1001,'gueryang');
   --一次性向表中插入多条数据
   create table temptable--将工资大于2000的员工选出，放入新建的表中
   as
   select * from emp where sal>2000;
   --只复制表结构，不添加数据
   create table emptable
   as
   select * from emp where 1<>1;
   --将工号是1001的员工信息加入到temptable表中。
   insert into temptable select * from emp where empno = 1001;
   --查询每个部门的最高薪水，最低薪水，平均薪水，总人数，将查询的结果放到一张新建的表中
   create table newemp
   as
   select deptno 编号, max(sal),min(sal),avg(sal),count(*) from emp group by deptno
   
   2.update 修改
   update 表名 set 列名 = 新的值;
   update 表名 set 列名 = 新的值 where empno = 1001;
   update 表名 set 列名1 = 新的值，列名2 = 新的值 where empno = 1001;
   update emp set sal = 9000 where empno = 1001;
   3.delete 删除
   delete [from] 表名;--删除表中的数据，需要提交
   truncate table 表名;--删除表中的数据，不需要提交
   delete emp where empno = 1001;--条件删除
   
13.高级查询
   1.表连接查询 等值连接 (主外键关系)
   92标准
   --select .. from 表1，表2 where 主表主键 = 外键表外键
   select * from emp e,dept d where e.deptno = d.deptno and e.deptno = 10;
   99标准
   --select ..from 表1 join 表2 on 主表主键  = 外键表外键
   select * from emp e join dept d  on  e.deptno = d.deptno where e.deptno = 10;
   2.非等值连接查询 两表之间没有直接的主外键关系，而是通过运算得到（找两表的关系）
   --查询各个员工的工号，姓名，工资，工资等级
   select empno ,ename,sal,grade from emp e,salgrade s 
   where sal between s.losal and hisal;
   --查询员工编号，姓名，薪水，薪水等级，部门名称
   92:
   select empno,ename,sal,grade,dname from emp e,dept d,salgrade s
   where e.deptno = d.deptno and (sal between s.losal and s.hisal);
   99
   select empno,ename ,sal,grade ,dname from emp e
   join dept d on e.deptno = d.deptno
   join salgrade s on e.sal between s.losal and s.hisal; 
   
14.外连接查询 
   --查询所有员工的编号，姓名，薪水，部门名称（包括没有部门的编号）
   92：
   select empno,ename,sal,dname from emp e,dept d
   where d.deptno = e.deptno;   --没有查询出全部员工，漏了没有部门的员工
   --修改后
   select empno,ename,sal,dname from emp e,dept d
   where d.deptno(+) = e.deptno; --右外连接
   99：
   select empno,ename,sal,dname from emp e
   left join dept d on e.deptno = d.deptno;--左外连接
   全外连接查询
   --查询所有员工的信息和所有部门的信息，包括没有部门的员工，没有员工的部门
   select * from emp e
   full join dept d on e.deptno = d.deptno;
   
15.自连接查询  一张表当作两张表用
   --查询所有员工的编号，姓名，职位，及上级领导姓名（包含没有经理的员工）
   select e.empno,e.ename,e.job,m.ename  from emp e
   left join emp m on e.mgr = m.empno; 
   
16.单行子查询：子查询的结果必须是单行单列 <、 >、 <=、 >=、 <>
   --查询所有比员工'CLARK'工资高的员工
   select * from emp where sal>(select sal from emp where ename = 'CLARK')   
   子查询的作用：子查询作为修改条件、子查询作为删除条件、子查询作为查询条件
   --查询和'SCOTT'同一部门且比它工资高的员工信息
   --删除低于平均工资的员工
   --将工资低于平均工资的员工工资增加1000
   子查询作用：作为修改的值来用、作为新增的值来使用
   --将JACK的工资修改为和SMITH工资相同
   --JACK今天入职到了SCOTT所在的部门
   子查询作为表使用：
   --查询部门编号，部门名称，部门平均工资
   select avg(sal),deptno from emp group by deptno;
   
   select e.deptno,e.dname,d.asal from dept e,
   (select avg(sal) asal,deptno from emp group by deptno) d
   where e.deptno = d.deptno;
     
   --使用子查询作为表来使用，实现分页显示
         --开始条件 rnum>(页数-1)*每页显示的条数
         --结束条件 rnum<页数*每页显示的条数
   select * from emp where rownum <=5;--查询前5条
   --rownum不能使用>,>=
   select * from emp where rownum>5 and rownum<=10;--错误(可以运行，但没有结果)
   --rownum不能和order by 一起使用。
   select rownum,e.* from emp e order by sal;--错误（可以运行，但rownum错乱）
  
   --显示最高工资的前五条信息
   select * from emp order by sal desc;--不能直接使用rownum
   select d.* from (select * from emp order by sal desc) d where rownum<6 and sal is not null;

   --显示第二页的5条信息。
   --(1)获得排序后的信息
   select e.* from emp e where sal is not null order by sal desc;
   --(2)获得加伪列后的信息
   select rownum rnum,d.* from (select e.* from emp e where sal is not null order by sal desc)d
   --(3)通过伪列获取结果
   select * from (select rownum rnum,d.* from 
   (select e.* from emp e where sal is not null order by sal desc)d)
   where rnum>5 and rnum<11;
17.多行子查询  查询结果是多行单列  any、 all、 in
   --查询工资低于任何一个'CLERK'的工资雇员信息
          select sal from emp where job = 'CLERK';--（1）
   --比（1）中任何一个都低
          select * from emp where sal <any (select sal from emp where job = 'CLERK')
     
   将上面语句改为单行子查询：自己完成
   
   --查询工资比所有的“SALESMAN”都高的雇员的编号，名字和工资。
          select sal from emp where job = 'SALESMAN';--获得SALESMAN的所有工资
          select empno,ename,sal from emp where sal >all(select sal from emp where job = 'SALESMAN');--获得结果

   --查询20号部门中的员工职务 同10号部门中的员工职务 一样的员工信息。
      select job from emp where deptno = 10;--10号部门员工职务信息
      select * from emp where job in(select job from emp where deptno = 10) and deptno  = 20;
18.子查询的分类（根据是否可以独立运行分类）
      不相关子查询：由内到外执行，子查询可以独立运行
      --查询各个部门工资最高的员工信息（重点）
      select * from emp e where (e.deptno ,e.sal) in 
      (select deptno,max(sal)from emp group by deptno);--各个部门的最高工资（可以独立运行）
      相关子查询：由外到内执行，子查询不能独立运行
      --查询各个部门工资最高的员工信息（重点）
      select * from emp a where a.sal = 
      (select max(sal) from emp where deptno = a.deptno);

19.常用数据库对象
--用户对象
      1.创建用户
      create user 用户名 identified by 密码;
      2.给用户授权
      grant 权力/角色 to 用户名;
      3.撤销权力
      revoke 权力/角色 from 用户名;
      4.修改用户密码
      alter user 用户名 identified by 新密码;
      5.删除用户
      drop user 用户名;
--表(数据库)对象
      1.创建表
      create table (
             sno varchar2(20) ,
             sname varchar2(20)--
      )      
      2.修改表结构
      alter table 表名 add 新列名 number(5,2);--添加列
      alter table 表名 add drop colume 列名;--删除列
      alter table 表名 modify(sno varchar2(30));--修改列
      3.删除表
      drop table 表名;
      4.给表重命名
      rename 表名 to 新表名;
      5.给列重命名
      --alter table 表名  rename column 旧列名 to 新列名;
      alter table emp  rename column ename to 姓名;

20.约束：constrains
      1.主键约束:primary key()--主键不允许 为空，不能重复
      create table stu (
             sno varchar2(20) primary key ,--系统自命名
             sname varchar2(20) 
             --或
             constraint stu_pk_sno primary key (sno)
      )      
      --或
      alter table stu add constraint stu_pk_sno primary key (sno);
      2.唯一约束：unique
      create table stu (
             sno varchar2(20) primary key,
             sname varchar2(20) not null,
             email varchar2(20) unique --或
             
      )
      --或
      alter table stu add constraint stu_un_email unique (email);
      
      3.非空约束：not null--只允许定义在字段级
      create table stu (
             sno varchar2(20) primary key,
             sname varchar2(20) not null--非空约束 ，只能在此处定义。     
      )
      4.检查约束：check.
      create table stu(
             sno varchar2(20),
             age number(3),
             sname varchar2(20),
             sex varchar2(3),
             email varchar2(30)
      )
      --给年龄添加检查约束
      alter table stu add constraint stu_age check (age between 18 and 40)
      alter table stu add constraint stu_sex check (sex in('男','女'))--或(sex = '男' or sex = '女')
      alter table str add constraint stu_email check (email like '%.%@%')
      5.外键约束
--创建主键表

      create table clazz (
             clazzid  number(2) primary key,
             clazzname varchar2(15) not null
      )
      --插入数据
      insert into clazz values(1,'1001班')
      insert into clazz values(2,'1002班')
      
--再创建外键表
      create table student(
             stuno number(8) primary key,
             stuname varchar2(15) not null,
             clazzid number(2)
      )

--给学生表添加外键
      alter table student add constraint stu_fk_clazzid 
      foreign key(clazzid) --student表中的列名
      references clazz(clazzid);--clazz表中的表名 、列名
      
--添加测试数据
      insert into student values(10001,'张三',3);--未找到父项关键字
      insert into student values(10002,'李四',2);--添加成功
select * from student s,clazz c where s.clazzid = c.clazzid

21.序列：sequence Oracle的特有对象，其他数据库没有，--用的时候注意
      --创建序列
      create sequence seq_clazz
      increment by 1
      start with 1;
      --maxvalue 10 --可选项
      --cycle   --可选项
      --cache 2;   --可选项
           
       --使用序列:在添加数据时使用
       insert into clazz values(seq_clazz.nextval,'1003班');--如果之前没有加序列，以后还能加吗？
       select * from clazz;
       --删除序列
       drop sequence seq_clazz;
       --注意：序列号不会因为添加失败而停止自增。
       
22.索引：index 优点：提高查询效率 缺点：占用磁盘空间，insert update delete时，索引要一起变化
       创建索引：
            1.自动创建
            添加唯一约束时，会自动创建索引
            2.手动创建
            create index index_emp_empno on emp (empno);
            3.删除索引
            drop index index_emp_empno;
       --什么样的列适合索引？
       数据过滤和数据排序（经常做where后的条件的列适合作为索引）
23.视图：view
       作用：给不同的人查询不同的数据。
             使用view存储复杂的sql语句
       1.创建视图：
       create view view_avgsal
       as
       select deptno,floor(avg(sal)) avgsal from emp where deptno is not null group by deptno order by avgsal desc
       with read only;
       2.使用视图
       select * from view_avgsal;
       3.删除视图
       drop view view_avgsal;









MySql











--创建用户， 权限：最低是管理员  ，system系统管理员，sys超级管理员
select  * from DEPT;
select * from emp;

select '我是'|| ename || ',我的年薪是'|| 12*sal from emp;



--创建用户并设置密码
create user scott identified by 123456;
--修改密码
alter user scott identified by 654321;
--对用户进行授权,connect 只有登陆权限，没有操作权限,resource是使用权限
grant connect,resource to scott;--
--对用户的授权，给scott用户管理员的权限
grant dba to scott;
--SQL pluS 
--本节最重要的,导入数据库表，
--scott.sql文件

select * from emp;

delete emp where deptno = 22

update dept set dname = 'it' , loc = '河南' where deptno = 22

select * from emp;