时间:2021-07-01 10:21:17 帮助过:24人阅读
MySQL/Oracle数据库 Oracle数据库管理系统是管理数据库访问的计算机软件,由Oracle数据库与Oracle实例构成 Oracle数据库:一个相关的操作系统文件集合,这些文件组织在一起,成为一个逻辑整体,即为Oracle数据库。Oracle数据库必须要与内存实例合作,才能对外提供
使用双引号”“,如:将别名原封不动的显示(尤其是别名由多个单词构成)
select employee_id "id",last_name "Name",12*salary "annual_sal"
from employees;
连接符:
如:
select first_name || ' ' || last_name from employees;
使用distinct能够过滤掉重复的数据,如:
select distinct dept_id from empt;
数据库操作函数:
单行函数
单行函数分类:字符、数值、日期、转换、通用
单行函数:①操作数据对象;
②接收参数返回一个结果
③只对一行进行变换
④每行返回一个结果
⑤可以嵌套与转换数据类型
⑥参数可以是一列或一个值
字符函数:
字符大小控制函数,如:
LOWER('HELLOWORLD') //helloworld
UPPER('hello') //HELLO
initcap('helloWorld you') //Helloworld You,首个字母大写
字符控制函数,如:
CONCAT('Hello', 'World') //HelloWorld
SUBSTR('HelloWorld',1,5) //Hello
LENGTH(('HelloWorld') //10
INSTR('HelloWorld','W') //6
LPAD(24000,10,'*') //*****24000
RPAD(24000,10,'*') //24000*****
TRIM('H' FROM 'HelloHWorldH') //elloHWorld
REPLACE('abcd','b','m') //amcd
数值函数:
round:四射五入
round(45.926,2) //45.93
round(45.926,-1) //50
trunc:截断
trunc(45.926,2) //45.92
求余:
MOD(1600,300) //100
转换函数:
隐性(Oracle自动完成下列转换)
VARCHAR2 or CHAR ---------------------> NUMBER
VARCHAR2 or CHAR ---------------------> DATE
NUMBER ------------------------------> VARCHAR2
DATE ------------------------------> VARCHAR2
显性
DATA→to_char()→CHARACTER→to_date()→DATE
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_date('1995-05-23', 'yyyy-mm-dd') from dual;
//在用到字符时使用双引号,如:
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
NUMBER→to_char()→CHARACTER→to_number()→NUMBER
//1,234,567.890,'$999,999,999,999.999'表示美元,
//'L999,999,999,999.999',表示当地货币符(L表示local)
select to_char(1234567.89,'999,999,999,999.999') from dual;
select to_number('Y001,234,567.89','L000,000,999.99') from dual;
在数值的转化中,to_char()函数经常使用的集中格式:
- 9:数字
- 0:零
- $:美元符
- L:本地货币符号
- .:表示小数点
- ,:表示千位符
通用函数:
NVL(expr1,expr2):
函数的形式:
NVL(commission_pct, 0) //表示为当commission_pct为null时其值为0,否则等于commission_pct
NVL(hire_date,'01-JAN-97')
条件表达式:
使用的两种方法:
case表达式:
格式:
case expr when comparison_expr1 then return_expr1
[when comparison_expr2 then return_expr2
...]
else else_expr
end
如:
select department_id,
case department_id when 10 then salary*1.1
when 20 then salary*1.2
else salary*1.3
end
from employees
where department_id in(10,20,30);
decode函数:
如:
select department_id,
decode(department_id, 10, 1.1*salary,
20,1.2*salary,
30,1.3*salary)
from employees;
嵌套组函数:
如:显示各部门平均工资中的最大值
select max(avg(salary))
from employees
group by department_id;
Oracle数据库子查询:
子查询语法:
select select_list
from table
where expr operator(
select select_list2
from table2; //table与table2可以是同一个表
);
注意:
1.子查询要包括在括号内
2.将子查询放在比较条件的右侧
3.子查询中可以使用组函数
4.子查询中要留意空值
单行子查询使用的单行比较操作符:
=、>、>=、<、<=、<>(不等于)
如:
select salary,last_name
from employees
where salary > (select salary
from employees
where last_name='Abel');
多行子查询使用的多行操作符:
如:in、any(和子查询返回的某一个值比较)、all(和子查询返回的所有值比较)
综合子查询嵌套
//查询平均工资最低的部门信息
select *
from departments
where department_id = (select department_id
from employees
group by department_id
having avg(salary) = (select min(avg(salary))
from employees
group by department_id
)
)
创建和管理表(DDL)(create、alter、drop、rename、truncate)→操作皆不可回滚
Oracle数据库中的表
用户定义的表
常用的查询字典命令:
select * from user_tables; //查看用户创建的表有哪些
select table_name from user_tables; //查看用户创建的表的表名
select distinct object_type from user_objects; //查看用户定义的各种数据库对象
select * from user_catalog; //查看用户定义的表,视图,同义词和序列
创建表的方式:
方式一:直接创建空表
create table myemploy(
id number(5),
name varchar2(20), //注意:定义字符串使用的是varchar2
salary number(10,2), //表示10位数据,其中两位是小数
hire_date date
);
方式二 :使用子查询创建表
通过列名和默认值定义列
//方式二:从其他的表中抽取部分列生成新表
create table empt
as
select employee_id,last_name,salary,hire_date
from employees;
//注意,上表生成的信息会把原来的表中的数据也复制过来,若想不生成
数据,则可加上不等的过滤条件,如:
create table employee
as
select employee_id,last_name,salary,hire_date
from employees
where 1=3; //因为2不可能等于3,故创建的表为有相应结构而无数据的空表
数据处理(DML)(insert into、delete from、upddate、select)→操作可回滚
插入数据的特殊方式:从其他表中拷贝数据,如:
insert into emp(employee_id,last_name,salary,hire_date)
select employee_id,last_ame,salary,hire_date
from employees
where employee_id < 20; //加上限制条件表示仅复制部分数据
创建脚本:在SQL语句中使用&变量指定列值,&变量放在values子句中,如:
//该插入方法是在执行语句后来添加数据的
insert into emp(employee_id,last_name,salary,hire_date)
values(&employee_id,'&last_name',&salary,&hire_date);
-update:update不加限制条件更新的是整个列的数据
在update中,可以使用子查询时更新基于另一个表中的数据,如:
upadate emp_copy
set departmen_id = (select departmen_id
from employees
where employee_id = 20
)
where job_id = (select job_id
from employees
where employee_id = 10
);
数据库事务:
以下面的其中之一作为结束:
commit或rollback语句
commit与rollback语句的优点:
1.确保数据完整性
2.数据改变被提交之前预览
3.将逻辑上相关的操作分组
在进行增删改操作时可以设置保存点,如:
savepoint A;
在未commit之前,进行多步(增删改)操作后,可以直接通过保存点回滚到保存点处,如:
rollback to savepoint A;
注意:回滚只能在commit之前进行,在commit之后是无效的
Oracle数据库创建约束:
非空约束(not null),如:
create table emp(
id number(10) constraint emp_id_nn not null, //emp_id_nn表示约束名
name varchar(20),
salary number(10,2)
)
唯一约束(unique),如:
create table emp(
id number(10) constraint emp_id_uq unique, //列级约束,emp_id_uq约束名
name varchar(20) constraint,
salary number(20),
phone number(11),
//表级约束
constraint emp_phone_uq unique(phone) //括号内指明约束列
)
主键约束(primary key),如:
create table emp(
id number(5) constraint emp_id_pk primary key,
name varchar(20),
salary number(9,2)
//或者使用表级约束
//constraint emp_id_pk primary key(id)
)
外键约束(foreign key):连接两个表,将两个表进行关联
如:
create table empt(
id number(5),
name varchar(20),
salary number(7,2),
dept_id number(5),
constraint emp_dept_fk foreign key(dept_id) references depts(dept_id)
)
create table depts(
dept_id number(5),
dept_name varchar(10),
job_id number(3)
)
添加约束的语法:
使用alter table语句:
1.添加或删除约束,但是不能修改约束
2.有效化或无效化约束
3.添加not null约束要使用modify语句
格式:
alter table table_name
add(drop...) [constraint constraint_name] type (column);
如:
//添加不为空
alter table emp
modify salary number(8,2) not null;
//添加其他的约束
alter table emp
add constraint emp_uq unique(name);
//删除约束
alter table emp
drop constraint emp_uq;
//无效化约束
alter table emp
disable constraint emp_uq;
//有效化(注意,enable的前提是约束所在的列不存在对应的约束问题)
alter table emp
enable constraint emp_uq;
查询约束:
如:
//查询约束名、约束类型、约束条件
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name='employees'; //''其内的表示表名
查询定义约束的列:
如:
select constraint_name,column_name
from user_cons_columns
where table_name='employees';
视图:
视图中使用DML的规定
如:
create or replace view emp_view
as select
avg(salary) avg_sal from employees
group by department_id;
序列:可供多个用户用来产生唯一数值的数据库对象
创建格式:
create sequence seq_name
[increment by n] //每次增长的数值
[start wiht n] //从哪个值开始
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}] //是否需要循环
[{cache n | nocache}] //是否缓存登录
如:
create sequence emp_seq
increment by 5
start with 2
maxvalue 50
cycle
nochche;
序列的执行方法:
1.select emp_sql.nextval from dual;
2.select emp_sql.curval from dual;
序列的修改(不能修改初始值)
alter sequence seq_name
increment by newval
nomaxvalue
...;