-
NUMBER表示数字类型,经常被定义成NUMBER(P,S)形式,其中,P表示数字的总位数;S表示小数点后面的位数。
在表student中的id列和fee列的定义如下:
上述定义表示id列中的数据,整数位最大为4位,即最大值为9999;fee列整数位最大为5位,小数位最大位数是2位,即最大取值为99999.99。- CREATE TABLE student(
- id NUMBER(4),
- name VARCHAR2(20),
- gender CHAR(1),
- fee NUMBER(7,2)
- );
- 创建学员信息表student1,使用VARCHAR2数据类型定义student1表的name字段,该字段的长度为最大20个字节变长字符串;使用CHAR数据类型定义gender字段,该字段的长度为1个字节定长字符串;使用VARCHAR2数据类型定义residence字段,该字段的长度为100个字节定长字符串。
- CREATE TABLE student1(
- id NUMBER(4),
- name VARCHAR2(20),
- gender CHAR(1),
- residence CHAR(100)
- );
-
DATE类型用于定义日期时间的数据,长度是7个字节,默认格式是:DD-MON-RR, 例如:“11-APR-71”,表示1971年4月11日;如果是中文环境,则是“11-4月-71”这种形式。
在表student2中的birth列的定义如下:
- CREATE TABLE student2(
- id NUMBER(4),
- name VARCHAR2(20),
- gender CHAR(1),
- birth DATE
- );
- 使用“CREATE TABLE”语句创建表,语法是:
- CREATE TABLE [schema.]table_name(
- column_name datatype[DEFAULT expr][,…]
- );
上述创建表的语法表示:
1)“CREATE TABLE”为固定写法;
2)使用“[]”括起来的部分可以省略,[schema.]表示该表所属的用户,默认是当前登录oracle的用户;
3)table_name表示表名,根据实际情况替换该名称,此案例的表名为employee;
4)column_name表示列名,根据实际情况替换该名称,此案例中有9列,列名如表-1所示;
5)datatype表示对应列的数据类型,此案例中9列对应的数据类型如表-1所示;
6)[DEFAULT expr]表示给该列的默认值,可以省略;
7)[,…]表示多列之间使用“,”逗号做分隔,最后一列不使用逗号。
- CREATE TABLE employee(
- id NUMBER(4),
- name VARCHAR2(20) NOT NULL,
- gender CHAR(1) DEFAULT 'M',
- birth DATE,
- salary NUMBER(6,2),
- comm NUMBER(6,2),
- job VARCHAR2(30),
- manager NUMBER(4),
- deptno NUMBER(2)
- );
- 在建表后如果希望修改表名,可以使用RENAME语句实现,语法如下
RENAME old_name TO new_name;
RENAME employee TO myemp;
- 在建表之后,要给表增加列可以使用ALTER TABLE的ADD子句实现,该语句的语法如下:
- ALTER TABLE table_name ADD
- (column datatype [DEFAULT expr] [, column datatype…]);
ALTER TABLE myempADD (hiredate DATE DEFAULT sysdate);
- 建表之后,可以改变表中列的数据类型、长度和默认值,注意这种修改仅对以后插入的数据有效;另外,如果表中已经有数据的情况下,把长度由大改小,有可能不成功,比如原来的类型是VARCHAR2(100),其中已经存放了100个字节长度的数据,如果要改为VARCHAR2(80),则不会修改成功。该语句的语法如下:
- ALTER TABLE table_name MODIFY
- (column datatype [DEFAULT expr] [, column datatype…]);
- ALTER TABLE myemp
- MODIFY(job VARCHAR2(40) DEFAULT 'CLERK' )
ALTER TABLE table_name DROP (column);
ALTER TABLE myempDROP (hiredate);
- INSERT INTO table_name[(column[, column…])]
- VALUES(value[, value…]);
- INSERT INTO myemp(id, name, job, salary)
- VALUES(1001, 'rose', 'PROGRAMMER', 5500);
- --使用默认日期格式插入记录
- INSERT INTO myemp(id, name, job,birth)
- VALUES(1002, 'martha', 'ANALYST', '01-SEP-89');
- --使用自定义日期格式插入记录
- INSERT INTO myemp(id, name, job, birth)
- VALUES(1003, 'donna', 'MANAGER',
- TO_DATE('1978-09-01', 'YYYY-MM-DD'));
- 需要注意的一点,所有的DML操作,都需要执行事务提交语句commit,才算真正确认了此操作。
UPDATE myempSET salary = 8500 WHERE name = 'rose';
- UPDATE myempSET salary = 6500, job = 'ANALYST'
- WHERE id = 1003;
DELETE FROM myempWHERE job is null;
DELETE FROM myempWHERE name ='ROSE';
SELECT ename, sal, job FROM emp WHERE LENGTH(ename) = 5;
SELECT ename, LPAD(sal, 15, '$') as "SALARY" FROM emp ;
- SELECT ename, hiredate,
- ROUND(SYSDATE-hiredate) as "WORKTIME"
- FROM emp;
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss' ) FROM DUAL;
'fmDD "of" MONTH YYYY'
其中,fm表示格式;DD表示2位数字的天;“of”即为显示字符串“of”;MONTH表示全拼的月份;YYYY表示4位数字的年份。
然后,使用to_char函数,将日期类型的表示的时间,按格式转换为字符类型的时间,SQL语句如下所示:
- SELECT TO_CHAR(hiredate,'fmDD "of" MONTH YYYY') "Date Hired"
- FROM emp;
上述SQL语句中,"Date Hired"是“TO_CHAR(hiredate,'fmDD "of" MONTH YYYY')”的别名,在SQL中将别名加上双引号,可以区分别名中字符的大小写。不加双引号别名默认显示为大写。使用to_char函数配合Oracle日期格式将hiredate列显示的格式为“1980-12-17”,并且将hiredate列以“1980年12月17日”格式显示,并且显示的列名为“REVIEW”,SQL语句如下所示:
- SELECT ename, TO_CHAR(hiredate, 'yyyy-mm-dd') as "HIREDATE",
- TO_CHAR(hiredate, 'yyyy"年"mm"月"dd') as "REVIEW"
- FROM emp;
- SELECT custid, orderdate, shipdate,
- ROUND(MONTHS_BETWEEN(shipdate, orderdate)) as "Time Taken" FROM ord;
- SELECT cust_id, cname, NVL(TO_CHAR(birthday, 'yyyy-mm-dd'), 'not available'),
- NVL(TO_CHAR(account), 'no account')
- FROM customer;
上述SQL语句中,使用TO_CHAR函数将日期类型的数据、数值类型的数据转换为字符类型,这是因为,NVL函数要求两个参数的数据类型必须保持一致。
- SELECT * FROM customer
- WHERE TO_CHAR(birthday, 'yyyy') = '1987';
select hiredate from emp order by hiredate;
select ename, sal from emp where sal > 1600;
select ename, sal from emp where sal not between 4000 and 5000;
select ename from emp where deptno is null;
emp 表中,comm 列表示绩效,该列可能为 null。因此,首先需要使用 nvl 函数对comm 列进行转换:如果comm 列为 null,则转换为数值 0;然后,使用计算表达式 sal + nvl(comm,0) * 0.8计算月收入。
计算完月收入后,使用 order by 子句进行排序。SQL语句如下所示:
select ename, sal + nvl(comm,0) * 0.8 money from emp order by money
- select job, max(sal), min(sal), avg(sal), sum(sal)
- from emp
- group by job;
- select job, count(*)
- from emp
- group by job
select max(sal)-min(sal) "DIFFERENCE"from emp;
emp 表中,有 mgr 列表示员工的管理者,因此,首先需要对 mgr 列进行分组,并统计每组中 sal 列的最小值。
这样,会统计出每个管理者下属员工的最低工资,为了达到案例的要求,还需要判断 mgr 列不为空,以及最低工资不低于 800。
SQL语句如下所示:
- select mgr, min(sal)from emp
- where mgr is not null
- group by mgr
- having min(sal) >= 800;
emp 表中,有 deptno 列表示员工的所属部门,因此,需要对 deptno 列进行分组,并统计各组中记录的条数。
为了只统计工资大于 1500 的记录数,需要使用where子句进行过滤。SQL语句如下所示:
select deptno , count(*) from emp where sal > 1500 group by deptno;
SELECT deptno, AVG(NVL(comm,0)) avg_comm FROM emp group by deptno;
- select ename , dname , loc
- from emp e join dept d
- on e.deptno = d.deptno
- select t1.ename , t2.ename mgr_name
- from emp t1 join emp t2
- on t1.mgr = t2.empno ;
emp表中,有deptno列表示员工所在部门的编码;dept表中,也有deptno列表示部门编码,因此,可以通过此列对两个表进行关联查询。因为要求把没有部门的员工也查出来,则需要将emp表中的所有数据都列出来,需要使用emp表左外连接dept表。
SQL语句如下所示:
- select e.empno , ename , d.deptno , d.dname , d.loc
- from emp e left outer join dept d
- on e.deptno = d.deptno ;
要求把没有员工的部门也查出来,则需要将dept表中的所有数据都列出来,需要使用dept表左外连接emp表,或者使用emp表右外连接dept表。
SQL语句如下所示:
- select e.empno , e.ename , d.deptno , d.dname , d.loc
- from dept d left outer join emp e
- on e.deptno = d.deptno;
实现此案例,只需要在上一个案例的基础上,添加过滤条件:员工编号为null。
SQL语句如下所示:
- select e.empno , e.ename , d.deptno , d.dname , d.loc
- from dept d left outer join emp e
- on e.deptno = d.deptno
- where e.empno is null ;
首先,统计emp表各部门的员工数量以及平均工资:需要对 deptno 列进行分组,并统计sal列的平均值和记录数;
其次,将上一步中的查询结果作为中间表,与dept表,进行关联查询,查询部门的名称、所在地以及上一步中的统计结果。
SQL语句如下所示:
- select d.dname, d.loc, e.EMP_COUNT, e.SAL_AVG
- from dept d
- join(
- select deptno, count(*) as "EMP_COUNT", avg(sal) as "SAL_AVG" from emp
- group by deptno
- ) e
- on d.deptno = e.deptno;
分析第一条查询语句:
- SELECT e1.ename, e2.ename FROM emp e1, emp e2WHERE e1.mgr = e2.empno
此语句中:查询e1表中的员工姓名,以及e1中员工的管理员的姓名。查询的过程如图-15所示(以MGR列中的数据7902和7698为例):
图-15
由图-15可以看出,SMITH的管理者为FORD,而ALLEN和WARD拥有相同的管理者:BLAKE。
因此,第一条语句的作用在于:查询每名员工的姓名及其管理者的姓名,查询结果如图-16所示:
图-16
继续分析第二条查询语句:
- SELECT e1.ename, e2.ename FROM emp e1, emp e2WHERE e1.empno = e2.mgr
此语句中:查询e1表中的员工姓名,以及e1中员工的下属的姓名。查询的过程如图-17所示(以empno值为7566的数据为例):
图-17
由图-17可以看出,JONES有两名下属:SCOTT和FORD。
因此,第二条语句的作用在于:查询每个管理者姓名及其下属员工的姓名,查询结果如图-18所示: