MySQL/Oracle数据库的基础(二)

程序员文章站 2024-01-06 22:12:34

...

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自动完成下列转换)

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是无法回滚序列的

相关标签： MySQL Oracle 数据库基础 MySQL Orac

MySQL/Oracle数据库的基础(二)

MySQL/Oracle数据库

关于oracle与mysql官方jdbc的一些区别

MySQL/Oracle数据库的基础(二)

Mysql入门系列：获取MYSQL数据库和表的有关信息[组图]_MySQL

在原有3306端口mysqld服务的情况再搭建第二个3308端口的mysql实_MySQL

mysql 两个数据库中表的结构不同,如何把一个表中的数据导入到另一个表中

Oracle数据库在Linux中dbstart没有反应的解决

巧用mysql提示符prompt清晰管理数据库的方法_MySQL

Oracle系列：安装Oracle RAC数据库（二）

mysql 怎样随机抽取数据库中的10条内容？

关于mysql 字段的那个点为是定界符_php基础