MySQL/Oracle数据库的基础(二)
程序员文章站
2022-06-16 17:53:49
...
MySQL/Oracle数据库 Oracle数据库管理系统是管理数据库访问的计算机软件,由Oracle数据库与Oracle实例构成 Oracle数据库：一个相关的操作系统文件集合,这些文件组织在一起,成为一个逻辑整体,即为Oracle数据库。Oracle数据库必须要与内存实例合作,才能对外提供
MySQL/Oracle数据库
- Oracle数据库管理系统是管理数据库访问的计算机软件,由Oracle数据库与Oracle实例构成
- Oracle数据库：一个相关的操作系统文件集合,这些文件组织在一起,成为一个逻辑整体,即为Oracle数据库。Oracle数据库必须要与内存实例合作,才能对外提供数据管理服务。
- Oracle实例:位于物理内存里的数据结构,它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被进程锁访问。
- Oracle用它们来管理数据库访问
- Oracle实例就是平常所说的数据库服务(service)
- 实例可以操作数据库;在任何时刻一个实例只能与一个数据库关联,访问一个数据库；同一个数据库可由多个实例访问(RAC)
- Oracle区别于MySQL的：
- 在Oracle数据库中,使用NUMBER表示数字类型、DATE表示日期类型、VARCHAR2表示字符类型
- 空值不同于0,凡是空值参与的运算,结果都为空(null),空值是无效的,未指定的
- 别名的创建：(方式三)
-
使用双引号”“,如:将别名原封不动的显示(尤其是别名由多个单词构成)
select employee_id "id",last_name "Name",12*salary "annual_sal" from employees; -
连接符：
- 把列与列，列与字符连接在一起
- 用”||”来连接
- 可以用来“合成”列
-
如：
select first_name || ' ' || last_name from employees;
- 字符串：
- 字符串可以是select列表中的一个字符、数字、日期
- 日期和字符只能在【单引号】中出现
- 每当返回一行时，字符串被输出一次
-
使用distinct能够过滤掉重复的数据，如：
select distinct dept_id from empt; - SQL语句与SQL*Plus命令：
- SQL：一种语言、ANSI标准、关键字不能缩写、使用语句控制数据库中的表的定义和表中的数据。
- SQL*Plus:一种环境、Oracle的特性之一、关键字可以缩写(如:edit(ed)、describe(desc))、命令不能改变数据库中的数据的值、集中运行
-
数据库操作函数：
-
单行函数
-
单行函数分类：字符、数值、日期、转换、通用
单行函数：①操作数据对象； ②接收参数返回一个结果 ③只对一行进行变换 ④每行返回一个结果 ⑤可以嵌套与转换数据类型 ⑥参数可以是一列或一个值 -
字符函数：
-
字符大小控制函数，如：
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中的日期型数据实际含有两个值：日期和时间
- 日期：函数sysdate返回
- 两个日期相减返回日期之间相差的天数(但是不允许做加法运算)
- MONTHS_BETWEEN：两个日期相差的月数
- ADD_MONTHS：向指定日期中加上若干月数
- NEXT_DAY：指定日期的下一个星期*对应的日期
- LAST_DAY：本月的最后一天
- ROUND：日期四舍五入
- TRUNC：日期截断
- Oracle中的日期型数据实际含有两个值：日期和时间
-
转换函数：
-
隐性(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')
- NVL2(expr1,expr2,expr3)：expr1不为NULL,返回expr2;为NULL,返回expr3
- NULLIF(expr1,expr2)：相等返回NULL,不等返回expr1
- COALESCE(expr1,expr2,…,exprn)：如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE.
- COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值
-
条件表达式：
- 在SQL语句中使用IF-THEN-ELSE逻辑
-
使用的两种方法：
-
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; //查看用户定义的表,视图,同义词和序列
- 数据字典
- 由Oracle Server自动创建的一组表
- 包含数据库信息
-
-
创建表的方式：
-
方式一：直接创建空表
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 -
创建脚本:在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(回滚):当操作数据不当时,可以通过回滚回到修改前的数据
-
数据库事务：
- 以第一个DML语句的执行作为开始
-
以下面的其中之一作为结束:
-
commit或rollback语句
commit与rollback语句的优点: 1.确保数据完整性 2.数据改变被提交之前预览 3.将逻辑上相关的操作分组 在进行增删改操作时可以设置保存点,如: savepoint A; 在未commit之前,进行多步(增删改)操作后,可以直接通过保存点回滚到保存点处,如: rollback to savepoint A; 注意：回滚只能在commit之前进行,在commit之后是无效的 - DDL语句(自动提交)
- 用户会话正常结束
- 系统异常终止
-
- 提交或回滚前的数据状态
- 改变前的数据状态是可以恢复的
- 执行DML操作的用户可以通过delect语句查询之前的修正
- 其他用户不能看到当前用户所做的改变,直到当前用户结束事物
- DML语句所涉及到的行被锁定,其他用户不能操作
- 数据库操作(增删改查)完成后最好执行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):连接两个表,将两个表进行关联
- foreign key:在表级指定子表中的列
- references:标示在父表中的列
-
如：
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;
-
-
查询约束：
- user_constraints
-
如：
//查询约束名、约束类型、约束条件 select constraint_name,constraint_type,search_condition from user_constraints where table_name='employees'; //''其内的表示表名 -
查询定义约束的列:
- user_cons_columns
-
如:
select constraint_name,column_name from user_cons_columns where table_name='employees';
-
-
视图：
- 视图是一种虚表,视图建立在已有表的基础上
- 向视图提供数据内容的语句为SELECT语句
- 视图的优点:
- 控制数据访问
- 简化查询
- 避免重复访问相同的数据
-
视图中使用DML的规定
- 可以在简单视图中执行DML操作
- 当视图定义中包含以下元素之一时不能使用delete:
- 组函数
- group by子句
- distinct关联字
- rownum伪列(表示原来表中不存在的列)
-
如:
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; - 注意：首次调用方法时，需要闲滴啊用nextval.
-
序列的修改(不能修改初始值)
alter sequence seq_name increment by newval nomaxvalue ...; - 修改序列注意:
- 1.必须是序列的拥有者或对序列有alter权限
- 2.只有将来的序列值会被改变
- 3.改变序列的’初始值’只能通过删除序列之后重新建序列的方法实现
- 4.rollback是无法回滚序列的