1. 创建表和约束
语法格式:ALTER TABLE 命令
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。 |
案例 1:创建一个学生信息(INFOS)表和约束
代码:
CREATE TABLE INFOS
(
STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号
STUNAME VARCHAR2(10) NOT NULL, --姓名
GENDER VARCHAR2(2) NOT NULL, --性别
AGE NUMBER(2) NOT NULL, --年龄
SEAT NUMBER(2) NOT NULL, --座号
ENROLLDATE DATE, --入学时间
STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址
CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号
)
alter table INFOS add constraint PK_INFOS primary key(STUID)
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女')
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50)
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100)
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO
CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR
(CLASSNO >='2001' AND CLASSNO<='2999'))
ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME)
案例 2:创建一个成绩表(SCORES)表和约束
代码:
CREATE TABLE SCORES
(
ID NUMBER , --ID ①
TERM VARCHAR2(2), --学期 S1或S2
STUID VARCHAR2(7) NOT NULL, --学号
EXAMNO VARCHAR2(7) NOT NULL, --考号 E+班号+序号
WRITTENSCORE NUMBER(4,1) NOT NULL, --笔试成绩
LABSCORE NUMBER(4,1) NOT NULL --机试成绩
)
ALTER TABLE SCORES
ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2')
ALTER TABLE SCORES
ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES
INFOS(STUID)
Oracle 中可以把查询的结果根据结果集中的表结构和数据形成一张新表。
CREATE TABLE 表名 AS SELECT 语句
CREATE TABLE INFOS1 AS SELECT * FROM INFOS;
使用上面命令创建的新表中,不存在任何约束,并且把查询的数据一起插入到新表中。
如果只复制表结构,只需使查询的条件不成立(比如 where 1=2),就不会查询从出任何数
据,从而复制一个表结构。
CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2;
2. 简单查询
SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名
select stuname,gender,age,stuaddress from INFOS where gender = '男' order by age
案例 1:每名员工年终奖是 2000 元,请显示基本工资在 2000 元以上的员工的月工资,年总工资。
SELECT ENAME,SAL,(SAL*12+2000) FROM EMP WHERE SAL>2000;
BETWEEN…AND…
SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000;
字符串连接操作符(||)
SELECT (ENAME || 'is a ' || JOB) AS "Employee Details" FROM EMP WHERE SAL>2000;
--去重查询distinct
select distinct job from emp;
--and or
select ename,sal from emp where sal > 2000 or (sal < 1000 and sal > 500);
--IN
select sal from emp where ename IN('SMITH','ALLEN');
--between
select ename from emp where sal between 1000 and 2000;
--like
select * from emp where ename like '%CO%';
--order by
select * from emp order by sal desc;
--sum
select sum(sal) from emp;
--count
select count(ename) from emp where ename is not null;
--group by 查每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--having
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
--alias 别名
select e.ename from emp e;
--concatenate 将不同栏位获得的资料串在一起
select concat(ename,job) from emp where empno = 7369;--Mysql/Oracle
select ename||' '||job from emp where empno = 7369;--oracle
--substring用来抓出一个栏位资料中的一部分,substr(str,pos,len),由str的第pos位开始选出接下去的len个字符
select substr(job,3) from emp where ename = 'ALLEN';
select substr(job,2,4) from emp where ename = 'ALLEN';
--trim函数是用来移除掉一个字符串中的字头或字尾,ltrim:将所有字符串起头的空白移除,rtrim:将所有字符串结尾的空白移除
select trim(' dd df ') from emp;
select ltrim(' dd df ') from emp;
select rtrim(' dd df ') from emp;
3. 高级查询
消除重复行
SELECT DISTINCT DEPTNO FROM EMP;
NULL 操作
案例 1:查询 EMP 表中没有发奖金的员工。
SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000 AND COMM IS NULL;
IN 操作
SELECT ENAME,JOB,SAL FROM EMP WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST');
BETWEEN…AND…
SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000;
LIKE 模糊查询
案例 2:显示员工名称以 J 开头以 S 结尾的员工的姓名、工资和工资。
SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'J%S';
集合运算
集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括:
INTERSECT(交集),返回两个查询共有的记录。
UNION ALL(并集),返回各个查询的所有记录,包括重复记录。
UNION(并集),返回各个查询的所有记录,不包括重复记录。
MINUS(补集),返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。当使用集合操作的时候,要注意:查询所返回的列数以及列的类型必须匹配,列名可以不同
案例 3: 查询出 dept 表中哪个部门下没有员工。只需求出 dept 表中的部门号和 emp表中的部门号的补集即可。
SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP;
用 union 插入多条数据
INSERT INTO DEPT
SELECT 50,'公关部','台湾' FROM DUAL
UNION
SELECT 60,'研发部','西安' FROM DUAL
UNION
SELECT 70,'培训部','西安' FROM DUAL
连接查询
案例 4:请查询出工资大于 2000 元的,员工姓名,部门,工作,工资。
内连接
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.SAL>2000;
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO
WHERE e.SAL>2000
这里 INNER JOIN 中,关键字 INNER 可以省略
外联接
案例 5:请查询出每个部门下的员工姓名,工资。
案例分析:
Emp 表用外键 deptno 引用 Dept 表中的 deptno,在 Dept 表中如果有某些部门没有员工,那么用内联接,没有员工的部门将无法显示,因此必须以 Dept 表为基准的外联接。
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
FROM EMP e ,DEPT d
WHERE e.DEPTNO(+)=d.DEPTNO
(+): Oracle 专用的联接符,在条件中出现在左边指右外联接,出现在右边指左外联接。
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO
这里 RIGHT OUTER JOIN 中,关键字 OUTER 可以省略。
4. 子查询
案例 1:查询出销售部(SALES)下面的员工姓名,工作,工资。
select ename,job,sal from emp where deptno = (select deptno from dept where dname = 'SALES')
案例 2:查询出 Emp 表中比任意一个销售员(“SALESMAN” )工资低的员工姓名、工作、工资。
select ename,job from emp where sal < any(select sal from emp where job = 'SALESMAN')
<any:比子查询结果中任意的值都小,也就是说,比子查询结果中最大值还小,那么同理>any 表示比子查询结果中最小的还大。
案例 3:查询出比所有销售员的工资都高的员工姓名,工作,工资。
select ename,job,sal from emp where sal > all(select sal from emp where job = 'SALESMAN')
ROWID
select rowid,ename FROM emp where SAL>2000;
ROWNUM
案例 4:查询出员工表中前 5 名员工的姓名,工作,工资
SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5;
案例 5:查询出工资最高的前 5 名员工的姓名、工资和工资。
SELECT ROWNUM,T.* FROM (SELECT ENAME,JOB,SAL FROM EMP ORDER BY SAL DESC) T WHERE ROWNUM<=5
案例 6:查询出表 EMP 中第 5 条到第 10 条之间的记录。
SELECT * FROM (SELECT ROWNUM R,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=10) WHERE R>5
NVL(x,value) 如果 x 为空,返回 value,否则返回 x。
案例 7:对工资是 2000 元以下的员工,如果没有发奖金,每人奖金 100 元。
SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;
NVL2(x,value1,value2) 如果 x 非空,返回 value1,否则返回 value2。
案例 8:对 EMP 表中工资为 2000 元以下的员工,如果没有奖金,则奖金为 200 元,如果有奖金,则在原来的奖金基础上加 100 元
SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) FROM EMP WHERE SAL<2000;
案例 9:求本月所有员工的基本工资总和。
select sum(sal) from emp;
案例 10:求不同部门的平均工资。
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;
5.表格处理
NOT NULL:指定一个栏位不能为空
UNIQUE:保证一个栏位中的所有资料都是有不一样的值
CREATE TABLE Customer(
SID integer Unique,
Last_Name varchar(30),
First_Name varchar(30));
CHECK:保证一个栏位中的所有资料都是符合某些条件
CREATE TABLE Customer(
SID integer CHECK(SID>0),
Last_Name varchar(30),
First_Name varchar(30));
主键(Primary Key):主键中每一笔资料都是表格中的唯一值,它是用来独一无二地确认一个表格中的每一行资料。主键可以是原本资料内的一个栏位,或是一个人造栏位(与原本资料没有关系的栏位)。主键可以包含一或多个栏位。当主键包含多个栏位时,称为组合键(Composite Key)。
Mysql:
CREATE TABLE Customer(
SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY(SID));
Oracle:
CREATE TABLE Customer(
SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
外键:一个(或数个)指向另外一个表格主键的栏位
CREATE TABLE ORDERS(
Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);
CREATE VIEW:视图表(Views)可以被当作是虚拟表格,它是建立在表格之上的一个架构,本身并不实际存储资料
CREATE VIEW V_Customer AS SELECT First_Name,Last_Name FROM Customer;
CREATE INDEX:索引(index)可以帮助我们从表格中快速找到需要的资料
create index idx_customer_First_Name on Customer(First_Name);
DROP TABLE:清除表格
DROP TABLE customer;
TRUNCATE TABLE:只清除表格中的所有资料
6. 创建表空间
进入数据库:
su - oracle
export ORACLE_SID=orcljk (orcljk为数据库的实例)
sqlplus / as sysdba
启动数据库
startup
查询数据库的实例于状态:
select instance_name,status from v$instance;
启动到mount状态,需要先关闭数据库,再启动到mount状态
非归档模式改为归档模式
查看数据库模式
select name, log_mode from v$database;
1. 关闭数据库
shutdown immediate;
2. 启动数据库
startup mount;
3. 修改数据库为归档模式
alter database archivelog;
4. 打开数据库
alter database open;
create bigfile tablespace t2 datafile 'u01/oracle/app/oradata/orcljk/t2.dbf' size 10g;
create table a2(n number,
id char(2000),
id1 char(2000),
id2 char(2000),
id3 char(2000),
id4 char(2000),
id5 char(2000),
id6 char(2000),
id7 char(2000),
id8 char(2000))tablespace t2;
declare
i number;
begin
i := 1;
while i>0 loop
insert into a2 values(i,TO_CHAR('3.3321'+i),TO_CHAR('9.6681'+i),TO_CHAR('3.34326'+i),TO_CHAR('8.2669'+i),TO_CHAR('2.3122'+i),TO_CHAR('3.676'+i),TO_CHAR('7.64497'+i),TO_CHAR('6.5232'+i),TO_CHAR('4.557'+i));
commit;
i := i+1;
end loop;
end;
/
查询真实数据
select sum(bytes)/1024/1024/1024 from dba_segments;