在运行栏键入sqlplus命令,SET LINESIZE 300 设置格式;SET PAGESIZE 30 每页显示的数据条数,COL ename FOR A10 设置字段列宽。
切换用户 CONN system/manager 切换到system用户下
CONN sys/change_on_install as sysdba 切换到sys用户下
CONN scott/tiger 切换到Scott用户下
查看当前的用户身份
SHOW user
在sys身份下查询表
SELECT * FROM table
查询指定表
SELECT * FROM scott.emp;
在sqlplus命令里面,除了使用Oracle自己的命令还可以使用HOST调用本机操作系统的命令
调用echo命令 HOST echo HELLO
调用copy命令 HOST copy D:?.jpg(原路径) E:?.jpg(目标路径)
简单查询
简单查询就是在一张表中进行数据查询,而且没有条件的过滤
·查询全部的列
SELECT * FROM 表明【别名】
·查询指定列的内容
SELECT 列名【别名】,列名【别名】 FROM 表名【别名】
·解决重复的数据(只有查询的所有列的内容相同才算重复的信息)
SELECT DISTINCT 重复数据 FROM 表名
·要查询的结果可以使用四则运算
SELECT 结果(四则运算)【别名】FROM 表名
限定查询
要使用限定查询需要用到一个WHERE子句作为条件的过滤
SELECT * FROM 表名 WHERE 条件
·关系运算符 >,<,=,!=,<>
·逻辑运算符 AND,OR,NOT
·范围查询 BETWEEN ? AND ?
·空判断 IS NOT NULL;
·in操作符 IN(?,?,?);
·模糊查询 :
"_":表示任意[一位]字符
"%":表示任意位数的任意字符
LIKE "_?%" ;
排序查询
要使用排序查询则要使用到另一个子句:ORDER BY
SELECT */字段名【别名】
FROM 表名
WHERE 过滤条件
ORDER BY 字段名 [ASC/DESC];
多表查询
内连接查询:(等值查询)满足条件的数据会被显示出来
外连接查询:(左外连接,右外连接查询)控制左表或者右表不满足条件的信息是否显示
SELECT */字段名 【别名】
FROM 表名1,表名2...
WHERE 1字段=2字段...(消除笛卡尔积)
左外连接 :字段=字段(+)控制左表不满足条件也显示
右外连接 :(+)字段=字段 控制右表不满足条件也显示
五种统计常用函数
统计查询的数据量:COUNT(*/字段/DISTINCT字段)
统计最大值:MAX(日期或者数字类型字段)
统计最小值:MIN(日期或者数字类型字段)
统计平均值:AVG(数字类型字段)
计算某字段的数据之和:SUM(数字类型字段)
分组查询
要实现分组查询,就上GROUP BY子句
③SELECT */字段名 【别名】
①FROM 数据来源
②WHERE 条件过滤
④GROUP BY 分组字段
⑤HAVING 条件过滤
⑥ORDER BY 字段 排序
*统计函数是为分组查询服务的,如果没有使用GROUP BY子句而使用了统计函数,那么SELECT子句中不能再有其他字段
如果SELECT子句中使用了嵌套的统计函数,那么在SELECT子句中不能再有其他字段(包括分组字段)
WHERE和HAVING的区别
·WHERE是在GROUP BY之前执行,GROUP BY只能对WHERE筛选出来的结果 进行分组,WHERE子句中不能出现统计函数
·HAVING是在GROUP BY之后执行的,可以对分组后的数据进行筛选,可以使用统计函数。
子查询
WHERE子句中的子查询
SELECT * FROM 表名
WHERE 字段 关系运算符[?](SELECT ...) WHERE子句返回的是一条数据
切换用户 CONN system/manager 切换到system用户下
CONN sys/change_on_install as sysdba 切换到sys用户下
CONN scott/tiger 切换到Scott用户下
查看当前的用户身份
SHOW user
在sys身份下查询表
SELECT * FROM table
查询指定表
SELECT * FROM scott.emp;
在sqlplus命令里面,除了使用Oracle自己的命令还可以使用HOST调用本机操作系统的命令
调用echo命令 HOST echo HELLO
调用copy命令 HOST copy D:?.jpg(原路径) E:?.jpg(目标路径)
简单查询
简单查询就是在一张表中进行数据查询,而且没有条件的过滤
·查询全部的列
SELECT * FROM 表明【别名】
·查询指定列的内容
SELECT 列名【别名】,列名【别名】 FROM 表名【别名】
·解决重复的数据(只有查询的所有列的内容相同才算重复的信息)
SELECT DISTINCT 重复数据 FROM 表名
·要查询的结果可以使用四则运算
SELECT 结果(四则运算)【别名】FROM 表名
限定查询
要使用限定查询需要用到一个WHERE子句作为条件的过滤
SELECT * FROM 表名 WHERE 条件
·关系运算符 >,<,=,!=,<>
·逻辑运算符 AND,OR,NOT
·范围查询 BETWEEN ? AND ?
·空判断 IS NOT NULL;
·in操作符 IN(?,?,?);
·模糊查询 :
"_":表示任意[一位]字符
"%":表示任意位数的任意字符
LIKE "_?%" ;
排序查询
要使用排序查询则要使用到另一个子句:ORDER BY
SELECT */字段名【别名】
FROM 表名
WHERE 过滤条件
ORDER BY 字段名 [ASC/DESC];
多表查询
内连接查询:(等值查询)满足条件的数据会被显示出来
外连接查询:(左外连接,右外连接查询)控制左表或者右表不满足条件的信息是否显示
SELECT */字段名 【别名】
FROM 表名1,表名2...
WHERE 1字段=2字段...(消除笛卡尔积)
左外连接 :字段=字段(+)控制左表不满足条件也显示
右外连接 :(+)字段=字段 控制右表不满足条件也显示
五种统计常用函数
统计查询的数据量:COUNT(*/字段/DISTINCT字段)
统计最大值:MAX(日期或者数字类型字段)
统计最小值:MIN(日期或者数字类型字段)
统计平均值:AVG(数字类型字段)
计算某字段的数据之和:SUM(数字类型字段)
分组查询
要实现分组查询,就上GROUP BY子句
③SELECT */字段名 【别名】
①FROM 数据来源
②WHERE 条件过滤
④GROUP BY 分组字段
⑤HAVING 条件过滤
⑥ORDER BY 字段 排序
*统计函数是为分组查询服务的,如果没有使用GROUP BY子句而使用了统计函数,那么SELECT子句中不能再有其他字段
如果SELECT子句中使用了嵌套的统计函数,那么在SELECT子句中不能再有其他字段(包括分组字段)
WHERE和HAVING的区别
·WHERE是在GROUP BY之前执行,GROUP BY只能对WHERE筛选出来的结果 进行分组,WHERE子句中不能出现统计函数
·HAVING是在GROUP BY之后执行的,可以对分组后的数据进行筛选,可以使用统计函数。
子查询
WHERE子句中的子查询
SELECT * FROM 表名
WHERE 字段 关系运算符[?](SELECT ...) WHERE子句返回的是一条数据
ANY其中的一个 ALL所有的
HAVING子句中的子查询
SELECT 字段 统计函数..FROM 表名 GROUP BY 字段
HAVING 统计函数 关系运算符(
SELECT 统计函数 FROM 表名
)
HAVING子句中的子查询返回的是单列数据,需对统计后的数据进行删选才会使用HAVING子查询
FROM子查询
SELECT 字段名【别名】FROM 表名 别名,(
SELECT 字段名 FROM 表名)别名
SELECT 字段 统计函数..FROM 表名 GROUP BY 字段
HAVING 统计函数 关系运算符(
SELECT 统计函数 FROM 表名
)
HAVING子句中的子查询返回的是单列数据,需对统计后的数据进行删选才会使用HAVING子查询
FROM子查询
SELECT 字段名【别名】FROM 表名 别名,(
SELECT 字段名 FROM 表名)别名
WHERE 1字段名=2字段名;
FROM子句返回的结果是多行多列的数据,相当于一张临时表的形式
数据的增,删,改
数据的增加
复制一张表:CREATE TABLE 要建的表名 AS SELECT * FROM 来源表名
数据的增加:INSERT INTO 表名称 (字段1,字段2..)VALUE(值1,值2..)
[自定义的字段要对应指定的值,顺序要对应]
数据的修改
UPDATE 表名称 SET 字段值=新的值,字段值=新的值 WHERE 修改条件
删除数据
DELETE FROM 表名称 WHERE 删除条件
删除全部数据 DELETE FROM 表名称
事物处理
commit 事务提交
rollback 事务回滚
五种约束
表的创建:CREATE TABLE 表名称(
字段名 类型,字段名 类型)
表的删除 DROP TABLE 表名称
常用的数据类型
·数字类型
NUMBER表示小数整数都可以
NUMBER(n)表示只保存整数,而且不能超过n位
NUMBER(n,m)表示只能保存小数,整数位不能超过n-m位 ,小数位m位
·字符串类型
VARCHAR2(n)表示保存小量的字符串类容,不能超过n个字符
CLOB 保存大量的字符串数据【4G】
·日期类型
DATE 保存日期数据(SYSDATE[字符串格式的日期],TO_DATE[转换格式])
非空约束:规定某一字段的内容不能为空,使用"NOT NULL"来完成
唯一约束:规定某一字段的内容不能重复,使用"UNIQUE"(UK)
主键约束:规定某一字段的内容即不重复又不为空,使用"PRIMARY KEY" (PK)
检查约束:在更新数据之前进行一些条件判断,若满足则更新否则不更新
使用"CHECK" (CK) ,CONSTRAINT 自定名 CHECK(字段 条件)
*检查约束一般不用因为会降低效率,所有的数据的检查都在程序中完成
外键约束:如果有两张表,一张表中的字段来自另一张表那么提供字段的表叫父表
另一张叫子表。使用"FOREIGN KEY"(FK)
CONSTRAINT 自定名 FOREIGN KEY (字段) REFERENCES 父表(字段)
*如果表中存在外键关系,那么在删除子表之前要先删除父表
强制删除:DROP TABLE 表名 CASCADE CONSTRAINT;
DROP TABLE 表名 PURGE;
*父表中被作为子表的关联外键字段,必须设置为主键或者唯一约束
*如果父表中的某一条数据的某一字段被子表关联,那么父表中的这条记录无法先被删除
级联删除 (ON DELETE CASCADE)
级联更新 (ON DELETE SET NULL)
清空回收站 (PURGE RECYCLEBIN)
增加列:ALTER TABLE 表名 ADD (新列 列类型,..)
修改列的数据类型:ALTER TABLE 表名 MODIFY(列名 列类型,..)
删除列:ALTER TABLE 表名 DROP COLUMN 列名;
修改列名:ALTER TABLE 表名 RENAME COLUMN 就列名 TO 新列名
伪列ROWNUM
要完成数据的分页显示需要使用伪列ROWNUM
SELECT *FROM(
SELECT ROWNUM rn 字段名.. FROM 表名
WHERE ROW <= currentPage*lineSize
)temp
WHERE temp.rn >=(currentPage-1)*lineSize
currentPage:表示当前页
lineSize:表示每页显示多少数据量
*ROWNUM必须要从1开始,使用子查询将前面的所有数据查询出来之后ROWNUM就成了一个真实的列
序列SEQUENCE
创建一个序列:CREATE SEQUENCE 序列名
删除一个序列:DROP SEQUENCE 序列名
使用序列:INSERT INTO 表名(pid,name)VALUES(序列名.nextval,'?');
创建步长序列:CREATE SEQUENCE 序列名 INCREMENT BY 定义的步长
规定序列的开始值:CREATE SEQUENCE 序列名 START WITH 定义的开始值
单表的设计
数据库的第一设计范式
[数据表中的字段不可以再分,以学生表为例]
CREATE TABLE student1(
sid NUMBER(4),name VARCHAR2(50),age NUMBER(3),
address VARCHAR2(50),tel VARCHAR2(11),email VARCHAR2(50),
CONSTRAINT pk_sid PRIMARY KEY(sid)
);
多对多的关系数据表设计
[例如:一门课程可以被多个学生选择,一个学生可以选择多门课程]
我们需要设计三张表
·用一张表记录学生的信息
·用一张表记录课程的信息
·用一张表记录学生和课程关系信息
DROP TABLE student PURGE;
CREATE TABLE student(
sid NUMBER(4),name VARCHAR2(50),age NUMBER(3),
address VARCHAR2(50),tel VARCHAR2(11),email VARCHAR2(50),
CONSTRAINT pk_sid PRIMARY KEY(sid)
);
CREATE TABLE course(
cid NUMBER(4),name VARCHAR2(50),
CONSTRAINT pk_cid PRIMARY KEY(cid)
);
CREATE TABLE select_course(
cid NUMBER(4),sid NUMBER(4),
CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES course(cid),
CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES student(sid)
);
一对多的关系数据表设计
[例如:一所学校可以对应多个学生,一个学生只能对应一所学校]
CREATE TABLE school(
scid NUMBER(4),name VARCHAR2(50),
CONSTRAINT pk_scid PRIMARY KEY(scid)
);
CREATE TABLE student2 (
sid NUMBER(4),name VARCHAR2(50),
age NUMBER(3),address VARCHAR2(50),
tel VARCHAR2(11),email VARCHAR2(50),
scid NUMBER(4),
CONSTRAINT fk_scid FOREIGN KEY(scid) REFERENCES school(scid),
CONSTRAINT pk_stuid PRIMARY KEY(sid)
);
FROM子句返回的结果是多行多列的数据,相当于一张临时表的形式
数据的增,删,改
数据的增加
复制一张表:CREATE TABLE 要建的表名 AS SELECT * FROM 来源表名
数据的增加:INSERT INTO 表名称 (字段1,字段2..)VALUE(值1,值2..)
[自定义的字段要对应指定的值,顺序要对应]
数据的修改
UPDATE 表名称 SET 字段值=新的值,字段值=新的值 WHERE 修改条件
删除数据
DELETE FROM 表名称 WHERE 删除条件
删除全部数据 DELETE FROM 表名称
事物处理
commit 事务提交
rollback 事务回滚
五种约束
表的创建:CREATE TABLE 表名称(
字段名 类型,字段名 类型)
表的删除 DROP TABLE 表名称
常用的数据类型
·数字类型
NUMBER表示小数整数都可以
NUMBER(n)表示只保存整数,而且不能超过n位
NUMBER(n,m)表示只能保存小数,整数位不能超过n-m位 ,小数位m位
·字符串类型
VARCHAR2(n)表示保存小量的字符串类容,不能超过n个字符
CLOB 保存大量的字符串数据【4G】
·日期类型
DATE 保存日期数据(SYSDATE[字符串格式的日期],TO_DATE[转换格式])
非空约束:规定某一字段的内容不能为空,使用"NOT NULL"来完成
唯一约束:规定某一字段的内容不能重复,使用"UNIQUE"(UK)
主键约束:规定某一字段的内容即不重复又不为空,使用"PRIMARY KEY" (PK)
检查约束:在更新数据之前进行一些条件判断,若满足则更新否则不更新
使用"CHECK" (CK) ,CONSTRAINT 自定名 CHECK(字段 条件)
*检查约束一般不用因为会降低效率,所有的数据的检查都在程序中完成
外键约束:如果有两张表,一张表中的字段来自另一张表那么提供字段的表叫父表
另一张叫子表。使用"FOREIGN KEY"(FK)
CONSTRAINT 自定名 FOREIGN KEY (字段) REFERENCES 父表(字段)
*如果表中存在外键关系,那么在删除子表之前要先删除父表
强制删除:DROP TABLE 表名 CASCADE CONSTRAINT;
DROP TABLE 表名 PURGE;
*父表中被作为子表的关联外键字段,必须设置为主键或者唯一约束
*如果父表中的某一条数据的某一字段被子表关联,那么父表中的这条记录无法先被删除
级联删除 (ON DELETE CASCADE)
级联更新 (ON DELETE SET NULL)
清空回收站 (PURGE RECYCLEBIN)
增加列:ALTER TABLE 表名 ADD (新列 列类型,..)
修改列的数据类型:ALTER TABLE 表名 MODIFY(列名 列类型,..)
删除列:ALTER TABLE 表名 DROP COLUMN 列名;
修改列名:ALTER TABLE 表名 RENAME COLUMN 就列名 TO 新列名
伪列ROWNUM
要完成数据的分页显示需要使用伪列ROWNUM
SELECT *FROM(
SELECT ROWNUM rn 字段名.. FROM 表名
WHERE ROW <= currentPage*lineSize
)temp
WHERE temp.rn >=(currentPage-1)*lineSize
currentPage:表示当前页
lineSize:表示每页显示多少数据量
*ROWNUM必须要从1开始,使用子查询将前面的所有数据查询出来之后ROWNUM就成了一个真实的列
序列SEQUENCE
创建一个序列:CREATE SEQUENCE 序列名
删除一个序列:DROP SEQUENCE 序列名
使用序列:INSERT INTO 表名(pid,name)VALUES(序列名.nextval,'?');
创建步长序列:CREATE SEQUENCE 序列名 INCREMENT BY 定义的步长
规定序列的开始值:CREATE SEQUENCE 序列名 START WITH 定义的开始值
单表的设计
数据库的第一设计范式
[数据表中的字段不可以再分,以学生表为例]
CREATE TABLE student1(
sid NUMBER(4),name VARCHAR2(50),age NUMBER(3),
address VARCHAR2(50),tel VARCHAR2(11),email VARCHAR2(50),
CONSTRAINT pk_sid PRIMARY KEY(sid)
);
多对多的关系数据表设计
[例如:一门课程可以被多个学生选择,一个学生可以选择多门课程]
我们需要设计三张表
·用一张表记录学生的信息
·用一张表记录课程的信息
·用一张表记录学生和课程关系信息
DROP TABLE student PURGE;
CREATE TABLE student(
sid NUMBER(4),name VARCHAR2(50),age NUMBER(3),
address VARCHAR2(50),tel VARCHAR2(11),email VARCHAR2(50),
CONSTRAINT pk_sid PRIMARY KEY(sid)
);
CREATE TABLE course(
cid NUMBER(4),name VARCHAR2(50),
CONSTRAINT pk_cid PRIMARY KEY(cid)
);
CREATE TABLE select_course(
cid NUMBER(4),sid NUMBER(4),
CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES course(cid),
CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES student(sid)
);
一对多的关系数据表设计
[例如:一所学校可以对应多个学生,一个学生只能对应一所学校]
CREATE TABLE school(
scid NUMBER(4),name VARCHAR2(50),
CONSTRAINT pk_scid PRIMARY KEY(scid)
);
CREATE TABLE student2 (
sid NUMBER(4),name VARCHAR2(50),
age NUMBER(3),address VARCHAR2(50),
tel VARCHAR2(11),email VARCHAR2(50),
scid NUMBER(4),
CONSTRAINT fk_scid FOREIGN KEY(scid) REFERENCES school(scid),
CONSTRAINT pk_stuid PRIMARY KEY(sid)
);