MySql可以用show databases;而Oracle需要使用
select table_name from all_tables;
show tables.-------------------------------------1----------------------------------------
1.select sysdate from dual; 查询日期 用户名 system 密码 orcl 数据库ORCL
2. create table tb_1 (id number,name varchar2(18)) 注意oracle 不用int 用 number
3. 忘记管理员密码
命令行下输入 sqlplus /nolog 进入SQL*PLUS
输入 conn / as sysdba
修改密码 alter user system identified by pwd
然后可以使用system用户 和修改的pwd 登录了 密码第一个字符不能是数字
4. 主机字符串 是一个写在配置文件(内有IP地址 端口号)的一个标示 一般是orcl
网络配置文件 tnsnames.ora 是Oracle主机配置文件 服务名向导配置文件
监听器配置文件 listener.ora 重要的两个配置文件
Oracle默认端口 1521
5.测试数据库 是否联通 tnsping 主机字符串
①②③④⑤⑥⑦⑧⑨⑩
6.Oracle 所使用的操作系统物理文件 分为
①数据文件 DBF文件
②控制文件 CTL控制文件
③日志文件 LOG日志文件
7.ORACLE的默认用户
①SYS 系统 系统最高权限管理员 orcl
②SYSTEM 默认的系统管理员 orcl
③SCOTT 示范账户 测试账号 默认锁住 alter user scott account unlock 解锁 默认密码tiger
8.数据字典主要有三种静态视图组成 每种应用在不同范围
DBA 所有方案的对象 DBA_XXX
ALL 用户所能访问的对象 ALL_XXX
USER 在用户方案的对象 USER_XXX
9.用系统管理员登陆 查询 系统表空间
select * from DBA_TABLESPACES;查看所有表看空间 健在数据库的所有信息
select * from DBA_DATA_FILES; 查看所有数据文件
select * from DBA_TABLES; 查看所有在数据库的表
select distinct tablespace_name from dba_free_space 查看的表空间 即 dbf文件
10 创建表空间即DBF文件
CREATE TABLESPACE ZUKGIT DATAFILE 'E:\ZUKGIT\zukgit.dbf'
SIZE 20M
AUTOEXTEND on;
11 删除表空间 同时删除表空间所在的物理文件
DROP TABLESPACE ZUKGIT INCLUDE CONTENT AND DATAFILE;
12 要连接数据库 必须要一个用户账户 (ORACLE 通过用户管理数据) 每个用户都有自己默认的
表空间 和一个临时表空间
Create User 用于用户 //用户有对应的权限
CREATE USER zukgit //刚创建的用户没有任何权限
IDENTIFIED BY zukgit //它的账号密码登陆不了ORACLE 也
DEFAULT TABLESPACE tablespace //做不了任何操作 所以需要给用户授权
TEMPORARY TABLESPACE tablespace(TEMP这个表空间专门为临时表空间)
13 select * from all_users;//查看所有用户
select * from dba_users; //查看所有用户
14权限有两种 系统权限和对象权限
System系统权限允许用户执行数据库操作 如 创建表
Object对象权限允许用户对对象(表,视图)进行操作
角色是一组相关权限的集合 为了方便管理
我们可以权限赋予角色 角色赋予用户,以达到简化的目的
Oracle有100多种系统权限
通过Grant 给用户赋予权限或者角色
给12创的用户赋予登陆角色 grant connect to zukgit with admin option;
with admin option 表示zukgit又可以给别的用户赋予权限
其实 with admin option 可以不写。
grant connect to zukgit with admin option; 连接登陆的角色 connect
revoke connect from zukgit;收回登陆的角色
grant resource to zukgit with admin option; 创建表的角色 resource
grant connect,resource to zukgit;一次赋予多个角色
grant dba to zukgit; 赋予管理员权限
drop user zukgit;删除用户
-------------------------------------2----------------------------------------
1 数据类型、 ORACLE 没有双引号 select id ,case name when 'sun280' then '??' end as DD from host;
①②③④⑤⑥⑦⑧⑨⑩ ⑴⑵⑶⑷⑸⑹⑺⑻⑼⑽
①数值型
number(<p>,<s>)
Integer/int number(38)
在定义整型时候 一般情况都是number很少用到int 定义浮点型 就要用到number(12,1)
-float 浮点型
② 日期型
date 默认格式 DD-MON-YY 日 月 年
timestamp date的扩展
③LOB类型 大对象 Large Object
BLOB 二进制大对象 包括图片 MP3 电影
CLOB 字符大对象 包括文章
④ 字符型 char(<size>) 最多2000字节 占用空间固定就是size个字节 没占用的char就填充空字符
char的效率比varchar2高
varchar(<size>) 最多4000 字节 占用空间变化
size只是指定最大的你不能大过的值 它实际存储空间看他存储的值
varchar2(<size>) 最多4000字节 占用空间变化
目前 varchar 和varchar2 是同义词
工业标准的varchar类型可以存储空字符串 但Oracle不这样做,不允许这样做~(varchar不能存储空字符串)
保留了这样做的权利。Oracle自己开发了一个数据类型varchar2,它将工业标准的varchar可以存储空字符串的特性
改为存储NULL值 ~ORACLE建议使用VARCHAR2 而不是VARCHAR
何时用Char 何时用VARCHAR2 ?
Char和VARCHAR2 是矛盾的统一体~VARCHAR2比Char节省空间 但效率上比Char会弱 牺牲空间 获得效率 Char
如果VARCHAR2烈经常被修改 每次修改的数据不一样 那么会造成行偏移现象
所以经常修改的话那么数据类型选择CHAR会好一点
⑤ ROWID 表示在行中的唯一地址
2,.SQL分类
DDL 数据定义语言 (基本上与数据无关与结构有关)
包含: Create , Alter, Drop, Truncate,Rename 等
DML 数据操纵语言(与数据有决定关系)
包含:Select,delete,update,insert 最重要
DCL 数据控制语言(就是赋予权限 收回权限的语言)
包含:grant ~~to,revoke~~ from
TCL 事物控制语言(表示的是数据提交控制语言~要么一起完成~要么都不完成)
包含 Commit,ROLLBACK,SAVEPOINT
3.DDL 对表结构进行操作
建表 create table tab_1(id number,sex char(5),age number,address varchar2(25),iphone varchar2(15));
create table tab_2 as select id from tb_1;
另一种方式 建表~~从其他的表结构创建数据 create table tab_2 as select id from tb_1;
增加字段 alter table tab_1 add email varchar2(20);
修改字段(把Address增长)
alter table tab_1 modify address varchar(50);
删除iphone字段
alter table tab_1 drop column iphone;
重命名字段名
alter table tab_1 rename column iphone to iphone4s;
修改表的名字
rename tab_1 to tab_zukgit;
为表的字段加注释 便于理解 即注释
Comment on table tb_1 is '祝正杰学Oracle';
Comment on column tb_1.iphone4s is '苹果';
查看 表有注释的(数据字典)
select * from user_tab_comments where comments is not null;
查看 字段有注释的(数据字典)
select * from user_col_comments where comments is not null;
删除一张表 drop table tab_1;
在执行drop table 语句时候 Oracle会把删除表放在数据库回收站里10g以后可以恢复
把表从垃圾站恢复回来
FlashBack table tab_1 to before Drop;
不能恢复的删除
彻底删除一张表 drop table tab_1 purge[cascade constraints];
4.DML
插入数据
insert into tab_1(id,sex,age) values(1,'m','22');
不提交事物的话一个Session更改了数据 能在该session得到结果
但另一个session得不到这个结果 只有session提交事务commit;才能让数据
写到数据库 让所有session访问到
对Oracle来说DML语言需要手动提交
我们执行一条DML语句 ,sql会先放入缓存(回滚段) 需要我们手动提交commit;
commit; 提交
rollback; 把上条语句撤销掉;
插入数据
insert into tab_1(id,sex,age) values(1,'m','22');
插入数据 另一种方式
insert into tab_2(id,sex,age) select id,sex,age from tab_1;
修改数据
update tab_1 set age=23 where id=1;
update tab_1 set age=23 , id=1 where name='zukgit'; 修改两个数据
update tab_1 set age=23;如果不加where语句 那么就对所有字段修改
删除数据 delete 不能用*
delete from tab_1 where id=3;
delete from tab_1; 删除所有数据(表存在 可以回滚)
truncate table tab_1;DDL语句删除表数据(表存在 不可以回滚)
不可以回滚那么效率就快
drop table tab_1 连表都删除了
5.数据完整性
存储在数据库的数据都是正确状态,否则数据库就丧失数据完整性
数据库采用多种办法 保证数据的完整性
外键 约束 规则 触发器
约束:是在表中强制执行的数据,校验规则 保护数据完整性
大部分数据库支持五类数据完整性
not null 非空约束
unique 唯一约束
foreign key 外键约束
primary key 主键约束
check 检查约束
6 约束 列级约束
列级约束 直接跟在列后面定义,不指定列名,与下一个字段定义逗号隔开
create table student(int number ,
name varchar2(15) not null,//非空约束
sex char(4) not null check(sex='男' or sex='女') , //检查约束
age number not null check(age>18 and age<60), //检查约束
address varchar2(25) default 'gz' //假如 不插入这个数据 默认为广州
)
唯一性字段可以是空 但不能相同
主键和唯一键 Oracle自动创建索引
外键与主键 REFERENCE 就是个坑 REFERENCES才对
create table class(
id number primary key,
code varchar(16) not null)
create table student(int number ,
name varchar2(15) not null,//非空约束
sex char(4) not null check(sex='男' or sex='女') ,
age number not null check(age>18 and age<60),
classId number not null references class(id) // 行级外键约束 非空约束 列级约束
)
7.表级约束 在所有字段定义好之后添加约束 逗号隔开
create class(id number, code varchar2(15),constraints class_pk primary key (id))//要定义约束名?
删除这个约束 就要操作这个约束名
create table student(int number , 表级约束 是不能添加非空约束的
name varchar2(15) not null,//非空约束
sex char(4) not null,
age number not null,
classId number not null,
constraints student_pk primary key (id),// 表级外键约束 非空约束 列级约束
constraints student_unique unique(id),
constraints student_check_sex check (sex='男' or sex='女'),
constraints student_check_age check (age>18 and age<60),
constraints student_fk foreign key (classId) REFERENCES class(id)// 表级外键约束
)
8.约束的维护
create table class(id number,code varchar2(10) not null);
alter table class add constraints class_pk primary key (id);增加主键约束
create table student(int number ,
name varchar2(15) not null,//非空约束
sex char(4) not null , //检查约束
age number not null , //检查约束
address varchar2(25) default 'gz' , //假如 不插入这个数据 默认为广州
classId number
)
alter table student add constraints student_pk primary key (id);//增加主键约束
alter table student add constraints student_fk foreign key (classId)REFERENCES class(id);//增加外键
alter table student add constraints student_unique_email unique(Email)//增加唯一约束
alter table student add constraints student_check_sex check(sex='男' or sex='女)//增加检查约束
alter table student add constraints student_check_age check(age>18 and age<60)//增加检查约束
//删除外键约束约束 约束如果不自定义那么系统会给定义
alter table student drop constrains student_fk; 通过约束名删除约束 删除外键约束
//删除唯一约束约束
alter table student drop constrains student_unique_email;
//禁止约束 或激活约束
alter table student disable constrains student_fk;禁止约束
alter table student enable constrains student_fk; 激活约束
9 复合约束 联合主键
create table person(lastname varchar2(20),
firstname varchar2(20),age number,
constrains person_pk primary key(lastname,firstname));
对于外国人来说联合主键 两个和在一起不重复
10 查询语句
select * from tab_1;
查询的时候可以使用 +-*/
select empno,sal,sal*12 from emp; 查询员工编号, 月薪, 年薪
连接操作
select dname||'_'||loc from emp;查询的数据连在一起
---where 条件
select * from emp where empno=111;
select * from emp where job='CLERK';
select * from emp where job='CLERK' and empno=111;
select * from emp where job='CLERK' or empno=111;
--查询NULL值
select * from emp where job is null; 不能 用 =null
select * from emp where job is not null;
select * from emp where job is null; 不等于 select * from emp where job="";
--所有与NULL 进行运算 结果都为空
select empno,(sal+comm)*12 from emp; 因为有的comm奖金是空 所以返回结果好多是空
只有comm不为空的才不返回null 返回数值
--定义别名
select empno,sal*12 年薪 from emp;
--查询重不复记录 distinct
select distanct deptno from emp;
select distanct deptno,job from emp;//以 deptno,job组合不重复来判断输出
输出更多的行了、
--限制 <>不等于
查询 部门号是20 ,job是 clerk 薪水大于 1000的
select * from emp where deptno=20 and job ='CLERK' and sal >1000;
---between and
查询薪水大于1000 并且 小于 3000的
select * from emp where sal >1000 and sal<3000;(不包含)
select * from emp where sal between 1000 and 3000; 相当于
大于等于1000 小于等于3000 (包含)
---in(list) in执行的时候会拆分为一堆的or
--查询部门在20,30的
select * from emp where deptno in(20,30);
select * from emp where deptno not in(20,30);
select * from emp where deptno=20 or deptno=30;
---like 模糊查询 %表示任意多个(包括0个)的字符 _下划线代表一个字符
select * from emp like ename like '%A%'; 所有名字包含A的员工
--- is not null
select * from emp where comm is nou null
---优先级
select * from emp where job="CLERK" or job='MANAGER' AND sal>2000;
---排序 order 默认升序
order by ~~desc order~~by asc
---单行函数 进来多少行 出去多少行
字符函数:
一:大小写转换函数 lower upper initcap(首字母大写 每一个一个单词)
二:字符串处理函数 concat(连接两字符)
substr(获取子字符串)
Length(返回长度)
Instr(返回字符串位置)
LPAD|RPAD (空格补齐宽度)
TRIM|LTRIM|RTRIM(去掉空格)
REPLACE(替换字符串)()()
select * from emp where lower(ename)='smith';
select upper('hello') from dual;-->HELLO
select upper('HELLO') from dual;-->hello
select INITCAP('sql course') from dual; -->Sql Course
select concat('HELLO','world') from dual;;-->HELLOworld
select substr('HelloWorld',1,5) from dual;-->Hello 索引从1开始到5包含
select substr('HelloWorld',4) from dual; -->oWorld直接给一个参数就是从4开始取到末尾
select length('HelloWorld') from dual;--> 10
select INSTR('HelloWorld','H') from dual;-->1
RPAD在列的右边黏贴字符串
select RPAD(sal,8,'*') from temp;--> 显示8位 不足用*填充 12345*** 123*****
select LPAD(sal,8,'*') from temp;*****123 *1234567
select trim(' HelloWorld ') from dual;-->删除首尾空字符 HelloWorld
select length(trim(' HelloWorld ')) from dual;;-->10 嵌套函数
select trim(‘H’from 'HelloH WorldH') from dual;-->elloH World 删除首尾的H
select trim(‘H’from 'HelloH WorldH ') from dual;-->elloH WorldH 删除首尾的H 因为最后的是""空字符
select trim(leading‘H’from 'HelloH WorldH ') from dual;-->elloH WorldH 只删除前面的H
select trim(trailing‘H’from 'HelloH WorldH') from dual;-->HelloH World 只删除后面的H
select replace('helloworld','ll','FF') from dual;-->heFFoworld
数值函数:
select Round(45.926,2) from dual;--> 45.93 取两位 四舍五入
select TRUNC(45.926,2) from dual;--> 45.92 取两位 截断
select MOD(1600,300) from dual;--> 100 取余
日期函数: 日期上加上或减去一个数字仍未日期 两日期相减返回之间隔得天数
Oracle内部使用数字存储日期 : 世纪,年,月,日,小时,分,秒,
Oracle默认日期格式 DD-MON-RR YYY-MM-DD
select sysdate from dual; ;-->当前时间
select trunc(sysdate+1/24) from dual; -->得到下一小时0分0秒 2014/3/29 16:00:00
select trunc(sysdate+1) from dual;得到下一天0时0分0秒
select months_between(d1,d2) from dual; -->返回d1 d2 之间的月数 d1d2可以是字符
select add_months(d,I) from dual; -->返回d 加上I月后的日期
select last_day(d) from dual; -->返回d 月最后一天的日期
select last_day(sysdate) from dual; 2014/3/31 16:05:11 最后一天
下个月的第一天
select trunc(last_day(sysdate)+1) from dual; 2014/3/31 00:00:00 最后一天凌晨
得到下一年的 1月 1号 0时 0分 0秒
select months_add(trunc(sysdate,'yyyy'),12)
select round(sysdate) from dual 超过中午12点 就进入下一天
select round(sysdate,'mm') from dual 超过15号 就进入下一月
--截断日期
select trunc(sysdate,'mm') from dual
select trunc(sysdate,'yyyy') from dual
转换函数:
TO_CHAR(date,'fmt') 函数对日期的转换
select to_char(123) from dual 变为字符‘123’
select to_char(sysdate) from dual --> 13-6月-12 12年6月13号
select to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) from dual --> 2013-06-13 14:13:22
TO_DATE
select to_date('2012-06-12 13:42:21','yyyy-mm-dd hh:mi:ss') from dual;2012-06-12 13:42:21 日期
TO_NUMBER
select to_number('123') from dual; 字符变数字123
select to_number('aa') from dual; 报错
通用函数: nvl nvl2 case decode 许多数据库提供空 从数据库拿到的数据是空 有时候需要把null转为0;
case实现逻辑if else
select ename,sal,nvl(comm,0) from emp;//把奖金为空的用0代替
select ename,sal,nvl2(comm,comm,0) from emp;//三目运算 如果comm为空则返回第三个 如果comm不为空则返回它自己
case语句
select ename,job,sal ,case job
when 'SALESMAN' THEN sal*0.9
when 'ALERK' THEN sal*0.8
when 'SALESMAN' THEN sal+100
else sal end
as t_sal
from emp; 根据部门发不同的钱 ename,job,sal ,t_sal
decode语句 同样实现if else语句
select ename,job,sal ,decode( job
'SALESMAN' , sal*0.9
'ALERK' , sal*0.8
'SALESMAN' ,sal+100
)
as t_sal
from emp; 根据部门发不同的钱 ename,job,sal ,t_sal
---多行函数 组函数 进来多行 出去一行
mysql> select name,sum(case course when 'hibernate' then grade end) as hibernate
,sum(case course when 'spring' then grade end) as spring ,sum(case course when
'JDBC' then grade end) as JDBC from tb_course group by name;
+-------+-----------+--------+------+
| name | hibernate | spring | JDBC |
+-------+-----------+--------+------+
| marry | 60 | 70 | 50 |
| tom | 50 | 80 | 20 |
+-------+-----------+--------+------+
select * from tb_course;
mysql> select * from tb_course
+-----------+-------+-------+
| course | grade | name |
+-----------+-------+-------+
| JDBC | 20 | tom |
| Hibernate | 50 | tom |
| Spring | 80 | tom |
| Spring | 70 | marry |
| Hibernate | 60 | marry |
| JDBC | 50 | marry |
+-----------+-------+-------+
mysql> select name,case course when 'hibernate' then grade end as hibernate ,cas
e course when 'spring' then grade end as spring ,case course when 'JDBC' then gr
ade end as JDBC from tb_course ;
+-------+-----------+--------+------+
| name | hibernate | spring | JDBC |
+-------+-----------+--------+------+
| tom | NULL | NULL | 20 |
| tom | 50 | NULL | NULL |
| tom | NULL | 80 | NULL |
| marry | NULL | 70 | NULL |
| marry | 60 | NULL | NULL |
| marry | NULL | NULL | 50 |
+-------+-----------+--------+------+
-------------------------------------3----------------------------------------
我有外键 箭头就指向我 1______>n 的关系 foreign 是一对多的信息
我就是多的一方
foreign 加 unique 是一对一的信息
中间表 |||--->|||<---||| 中间表(有两个外键 并且这两个外键联合主键) 那么就是多对多关系
1 . 把关系型数据库中的表中删除冗余数据的过程 称为规范化 重点在冗余
并避免非规范的数据更新 数据删除异常
第一范式: ER E为实体 R为关系
在一张表中 必须要有主键(无论多少个主键必须要有主键)
并且表的每个字段是不可再分的字段
(不要象电话这样 还可以分为手机电话 家庭电话 既不能当主键 也不能是字段 需要细分电话)
create table person(phone number primary key,name varchar2(10),card varchar2(18))
create table person(phone number ,name varchar2(10),card varchar2(18) primary key )
不满足第一范式的例子(电话这样 还可以分为手机电话 家庭电话 需要细分电话 为两个字段)
第二范式:(主要针对联合主键) (没有部分依赖的问题(例如单个关键字) 只要满足第一方式 就是第二范式?)
如果表中的非主属性都完全依赖于
主属性(1个主键,2个联合主键,3个联合主键都可能) 则是第二范式
例如: create table sci(学号 number ,学生姓名 varchar2(10),课程号 number,
成绩 number,学分 number,constarins cci_pf primary key(学号,课程号));
这个表有联合主键 (学号,课程号) 不满足第二范式的例子(部分依赖了 第二范式需要完全依赖) 但满足第一范式的例子
会出现问题:一:数据冗余
假如同一门课由40个学生选修,学分(每门课程学分都一样)那就重复40次
二:更新异常
若调整了某课程的学分,表中的相关元组(一行)都要更新,当没有完全更改
完时,有可能出现同一门课程学分不同
三:插入异常
如果计划开新课,由于没人选择这个课程,没有学号关键字,所以不能插入
只能等有人选择才能把课程和学分插入(其实是先有课程才让学生选择)
四:删除异常
如果在表中的学生李四 结业 需要删除他的数据,从当前数据库删除
李四选修记录 假设:李四选修课程只有他这么一条记录
(即数据库显示只有李四一人选修该课程(如Oracle)) 那么删除李四的信息时,
那么Oracle这个课程也会被删除 在数据库就再也找不到Oracle课程
产生这些异常的原因: 非关键字属性 课程名 学分 仅依赖于课程号 ,
也就是 课程名 学分 部分依赖关键字(学生号码,课程号码)
而不是完全依赖于关键字(学生号码,课程号码)
解决模式:分成两个关系模式
create table 学生(学号 number primary key,课程号 number references 课程(课程号) ,姓名 varchar2(10),分数 number);
create table 课程(课程号 number,课程名 number,学分 number);
第三范式:
表中的所有非主属性字段都依赖于一个主键(第二范式是允许完全依赖联合主键的但第三范式不行)
非主属性字段只能依赖主键 非主属性字段之间也不有函数联系
依赖指的是 我可以根据这个字段 推出那个字段
例子: Create table 学生(学号 number primary key,姓名 varchar2(10),
学院编号 number,学院名 varchar2(10), 学院地址 varchar2(10) );
不满足第三范式的例子( 第三范式需要完全依赖依赖于主键 而学员名不依赖于主键) 但满足第一和第二范式的例子
分析:关键字 学号决定各个属性,由于是单属性,没有部分依赖问题 所以肯定是第二范式。
但这个关系肯定有大量冗余 学院名 学院地址
数据冗余 更新异常 插入异常 删除异常 都会出现
解决: Create table 学生(学号 number primary key,姓名 varchar2(10),
学院编号 number foreign key 学院(学院编号));
Create table 学院(学院编号number primary key,学院名 varchar2(10),地址 varchar2(10));
2. 表的关系 一对一 一对多 多对多
课程和学生 多对多 中间表
学生(多)和班级(一) 多对一 多对一 外键 多的一方有外键
人和身份证 一对一
create table student(int number ,
name varchar2(15) not null,//非空约束
sex char(4) not null check(sex='男' or sex='女') , //检查约束
age number not null check(age>18 and age<60), //检查约束
address varchar2(25) default 'gz' , //假如 不插入这个数据 默认为广州
classid number references class(id)
)
create table course(
id number primary key,
name varchar2(8),
credit number)
课程和学生 多对多 中间表
--学生课程中间表-- 1 建立主键 学生表外键 课程表外键
2学生id 课程id 联合主键 同时还要是外键
create table student_course(
student_id number foreign key student(id),
course_id number foreign key course(id),
grade number,
constrains pk primary key(student_id,course_id)
)
--一对一 人 和 身份证 1 唯一外键关联 2主键关联
1 唯一外键关联
create table person(id number primary key,name varchar2(10),
card_id number unique,
constrains person_fk foreign key (card_id) references card(code))
create table card(
id number primary key,
code varchar2(18) ---身份证
)
2主键关联
create table person1(id number primary key,name varchar2(10),
constrains person_fk foreign key (id) references card1(id))
create table card1(
id number primary key,
code varchar2(18) ---身份证
)
一对多 就是foreign key 有外键自己就是多