OracleDBA之表管理

  下面是Oracle表管理的部分,用到的测试表是oracle数据库中scott用户下的表做的测试,有的实验也用到了hr用户的数据,以下这些东西是我的麦库上存的当时学Oracle的学习笔记今天拿出来和大家分享一下,转载请注明出处,下面用的Oracle的版本是10g,用的时WinServer2003的操作系统,可能有些命令和Oracle11g的有所不同,但大部分是一样的,接下来还会陆续的分享一下Oracle中对数据库的管理,对表的管理,还有Oracle中的存储过程和PL/SQL编程。用到的Oracle的管理工具是PL/SQL Developerl和SQL PLUS,欢迎大家批评指正。

1.表名和列名的命名规则:   1.必须以字母开头   2.长度不能超过30个字符   3.不能使用oracle的保留字命名   4.只能使用字母数字下划线,$或#; 2.oracle的数据类型   1.字符型:    char 定长 最长2000字符(因为是定长的,在做查询时是多位同时比较,其好处是查询速度特快) demo: char(10) 存放‘ab’占俩个字符后面空着的8个字符用空格补全,所以存了2个字符也占10个字符的空间;    varchar2 变长 最大是4000字符(查询速度较慢,因为是变长,查询比较是是一位一位的比较) demo:varchar2(10) 存放‘ab’,就占2个字符;    clob(character large object) 字符型大对象,最大是4G   2.数字类型:     number 的范围 10的-38次方---10的38次方可以表示小数也可以表示整数     number(5,2)表示有5位有效数字,两位小数;范围 -999.99 -- 999.99     number(5) 表示有5位整数,范围:-99999-99999;   3.日期类型:     date 包括年月日和时分秒     timestamp 时间戳(毫秒级)     在oracle中默认的日期格式是“DD-MON-YY” 如“01-5月-1992”,如果没有月则添加不成功;     修改date的格式:

alter session set nls_date_fomat = "yyyy-mm-dd";

  4.大数据(存放媒体)     blob 二进制数据 可以存放图片/声音/视频 最大是4G普通的存放媒体数据一般在数据库中存放的是所放的文件夹路径当为了安全性时才会把媒体文件放在数据库中; 3.oracle中创建表

1 sql>create table student( --创建名为student的数据库表
2   name varchar2(20),    --名字10个变长
3   idcar char(18),     --身份证18个定长字符
4   sex char(2),     --性别2个定长字符
5   grade number(5,2)    --成绩为浮点数,有效5位小数位为2位;
6 )

4.oracle中往已有的表中新增列;

sql>alter table student add(classid number(2));

5.修改已有字段的长度

sql>alter table student modify(name varchar2(10));

6.删除表中的已有字段

sql>alter table student modify(name varchar2(10));

7.表的重命名;

sql>rename student to std;

9.往表中插入数据:   1.省略字段名

sql>insert into student values('name','231','男',234.89);

  2.给部分字段赋值

sql>insert into student(name,idcar) values('TOM','123');

  3.查询idcard字段为空的学生

sql>select * from student where idcard is null;

10.修改表中的数据:

sql>update student set name='cat' where id=1;

11.oracle中的回滚:(要养成创建保存点的习惯)--commit后所有的保存点都没有了

  1.回滚之前先创建保存点
    sql>savepoint pointName;
  2.删除表中的记录
    sql>delete from student;;
  3.回滚
    sql>rollback to pointName;
    truncate table student; --删除表中的所有的数据,不写日志,无法回滚,删除速度极快;

Oracle中的select语句的练习,这也是难点

  1.emp表中的内关联查询:给出每个雇员的名字以及他们经理的名字, 使用表的别名;

sql>select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

  2.去除重复的行,重复的行的意思是行的每个字段都相同; distinct

sql>select distinct emp.job,emp.mgr from emp;

  3.查询SMITH的薪水,职位和部门:

SQL> select emp.ename as 姓名,emp.sal as 薪水, emp.job as 工作,dept.dname as 部门
2 from emp,dept where emp.ename='SMITH' and emp.deptno=dept.deptno;

姓名 薪水 工作 部门
---------- --------- --------- --------------
SMITH 800.00 CLERK RESEARCH

  4.打开显示sql语句运行时间  

sql>set timing on;

  5.查询SMITH的年工资;--nvl 处理为null的字段,在表达式里如果有一个值为null则结果就为null用nvl()函数处理为空的字段,例如nvl(comm,0):如果为null则用0替换;

select emp.ename "名字", emp.sal*12+nvl(emp.comm,0)*12 "年薪" from emp where name='SMITH';

  6.模糊查询like %代替多个字符,_代替一个字符;

select * from emp where emp.ename like 'S%';

  7.or的升级in查询

select * from emp where emp.empno in(7369,7788);

8.查询工资高于500或者是岗位是manager同时名字以J开头的雇员

SQL> select * from emp where (emp.sal>500 or emp.job='MANAGER') and emp.ename like 'J%';

  9.按照工资从低到高排序 order by语句; desc是降序(从高到低),asc是升序(从低到高 默认)

SQL>select * from emp order by emp.sal asc;

  10.按照部门号升序(asc),员工号降序(desc)

SQL>select * from emp order by emp.deptno ,emp.empno desc;

  11.使用列的别名排序:按年薪降序(desc)

SQL>select emp.sal*12 "年薪" from emp order by "年薪" desc;

数据的分组————min,max,avg,sum,count;   1.查询员工的最高工资和最低工资; min()和max() 的使用

select max(sal) "最高工资", min(sal) "最低工资" from emp;

  2.查询所有员工的工资总和和平均工资 sun() 和 avg() 的使用;

SQL> select sum(sal) "工资总和", avg(sal) "平均工资" from emp;

  3.查询员工的总人数:

SQL> select count(*) from emp;

  4.把最高工资的员工的信息输出(用到了子查询)

SQL>select * from emp where sal=(select max(sal) from emp);
//ERROR 不能使用分组函数
error: select * from emp where sal = max(sal);--error;
error: select ename,max(sal) from emp; -error;

    select 后面若有分组函数子可以跟分组函数

  5.显示工资高于平均工资的员工信息:

SQL>select * from emp where sal=(select max(sal) from emp);
//ERROR 不能使用分组函数
error: select * from emp where sal = max(sal);--error;
error: select ename,max(sal) from emp; -error;

group by 和 having子句   group by 用于对查询的结果进行分组统计   having子句用于限制分组显示结果   1.显示每个部门的平均工资和最高工资;

 select avg(sal),max(sal),deptno from emp group by deptno;

  2.显示每个部门的每种岗位的平均工资和最高工资

SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,emp.job order by deptno;

  3.显示平均工资小于2000的部门号和他们的平均工资:

SQL> select emp.deptno,avg(sal) from emp group by emp.deptno having avg(sal)<2000;

DEPTNO AVG(SAL)
------ ----------
30 1566.66666

分组函数只能出现在选择列表,having,order by子句中 如果select中同时有group by ,having ,order by 则三者的顺序为group by ,having, order by;

多表查询:   1.显示雇员名,雇员工资,所在部门名称;

SQL> select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

  2.显示部门号为10的雇员名,雇员工资,所在部门名称

SQL> select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=10 and emp.deptno=dept.deptno;

  3.显示雇员名,雇员工资,工资的级别;

SQL> select emp.ename,emp.sal,salgrade.grade
          from emp,salgrade
        where emp.sal between salgrade.losal and salgrade.hisal;    

子查询: SQL中执行顺序是从右到左执行   1.查询与SMITH在同一部门的所有员工;

SQL> select * from emp where emp.deptno=(
         select emp.deptno from emp where emp.ename='SMITH');

  2.查询和部门10的工作相同的员工的信息

SQL> select * from emp where emp.job in( 
         select emp.job from emp where emp.deptno=10);      

 3.显示工资比部门号为30的所有员工的工资都高的员工信息;(用 all() 或 max()实现)

SQL> select * from emp where sal>all(
         select sal from emp where emp.deptno=30);
或者(下面的效率要高的多)
SQL> select * from emp where sal>(
         select max(sal) from emp where emp.deptno=30);

  4.显示工资比部门号为30的一个员工的工资都高的员工信息;

SQL> select * from emp where sal>any(
         select sal from emp where emp.deptno=30);
或者(下面的效率要高的多)
SQL> select * from emp where sal>(
         select min(sal) from emp where emp.deptno=30);

返回多字段的子查询:    1.查询与SMITH在同一部门并且职位也相同的员工信息;

SQL> select * from emp where (deptno,job)=(
         select deptno,job from emp where ename='SMITH');

    2.查询员工比自己部门的平均工资高的员工信息;(把查询出的信息当作一张表起一个别名)

SQL> select * from emp a,(
         select deptno,avg(sal) mysal from emp group by deptno) a2
         where a.deptno = a2.deptno and a.sal>a2.mysal;

在from中使用子查询时查询的结果会当作一个视图来对待,因此也叫做内嵌视图 必须给内嵌视图命一个别名

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

你真的会玩SQL吗?冷落的Top和Apply

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2278
来自专栏WindCoder

MySQL数据库对象与应用-MySQL进阶SQL应用单元测验

1、(单选)有表 song(id,song_name,album,play_count,fav_count)分别表示歌曲的主键id、歌曲名、专辑名、播放次数和收...

1121
来自专栏james大数据架构

你真的会玩SQL吗?实用函数方法汇总

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2079
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4416
来自专栏james大数据架构

你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

2097
来自专栏技术翻译

编写SQL查询的最佳方法

毫无疑问,编写代码更像是一门艺术,而不是一门科学。即使有经验,每个编码人员也无法编写既可读又可维护的优美代码。一般来说,当您学习编码艺术时,编码会随着经验而提高...

670
来自专栏文渊之博

介绍一种非常好用汇总数据的方式GROUPING SETS

介绍   对于任何人而言,用T-SQL语句来写聚会查询都是工作中重要的一环。我们大家也都很熟悉GROUP BY子句来实现聚合表达式,但是如果打算在一个结果集中包...

38211
来自专栏杨建荣的学习笔记

简单对比MySQL和Oracle中的一个sql解析细节 (r5笔记第40天)

SQL的语法解析器是一个很强大的内置工具集,里面会涉及到很多的编译原理的相关知识,语法分析,词法分析。。一大堆看起来很理论的东东,不过看起来枯燥之余,它们的价值...

3508
来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

1918
来自专栏崔庆才的专栏

MySQL 枚举类型的“八宗罪”

2504

扫码关注云+社区