PowerDesigner表设计&Oracle数据库的使用

PowerDesigner表设计

1安装

语言选择中文RPC，同意协议，其他一路next即可

 

2特点

1）数据库建模的最佳工具

2）可视化全览整个系统所有的表和表之间的关联关系

3）可以轻松的针对不同数据库形成不同的建表SQL语句

3配置工具栏

 

 

4去掉Name to Code mirroring

5 PD配置直线

新版本默认拐角线，老版使用直线。先创建对应的模型，然后改其所有线类型。

 

 

 6表设计

1）外键：由子表出发向主表拖动鼠标，到达主表后松手，PD会自动添加外键字段

2）老师表和课程表：一对多，两张表。关联关系体现：子表存储主表的主键，称外键

3）课程表和学生表：多对多，三张表。关联关系体现：子表无法存储主表的多条关联信息，只能再创建一张表来存储其信息

4）中间表：存储两张表各自的主键，某一张表的主键无法标识记录的唯一性，两个一起才可以标识唯一，称为复合主键

7生成建表SQL

 

 

Oracle数据库的使用

1 PD创建Oracle模型

1）构建物理模型，数据库选择Oracle

 

2）表设计

2安装

1 安装步骤

1）一定要“以管理员身份运行”，Oracle是系统级别的，要安装服务，要求权限大。

2）安装时把防火墙关闭，否则很容易安装失败。

 

3）修改安装路径

 

4）管理员名和密码。统一设成root/root

 

5）端口会和tomcat冲突

 

 

2 Oracle10g EX

1）安装OracleXEUniv简版.exe，会自动创建EX服务。如果安装安装版本，需要手动创建本地服务。

2）安装完成后，侦听Listener和ServiceXE自动启动

 

 

3安装注意事项

1）安装时，最好不要安装到c盘，选择d盘。

2）记住system的密码。安装时需要输入system的密码，oracle的密码是无法破解的，忘记了只能重新安装。

3）oracle默认会开启8080，跟tomcat默认8080端口冲突。可以关闭oracle的8080，或者换tomcat的默认端口。

4）oracle安装完成后，不能使用sql-plus测试是否安装成功，因为它权限非常大。可以直接和oracle通讯。可以使用plsql工具，如果可以连接，则写的程序才能正常访问。

5）如果安装失败，先停掉侦听服务，再停掉Service服务，然后运行安装包，进行卸载。

4配置本地服务-访问远程Oracle服务端。

1）远程访问前，在服务器上安装Oracle服务器端，客户端安装Oracle客户端，使用PL/SQL通过Oracle客户端访问Oracle服务端。

 

2）修改tnsnames.ora文件配置远程访问的地址：

D:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora

XEremote =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

5链接错误

长时间没有访问，oracle会自动断开连接，遇到这样的情况，重新登录即可。

 

3创建数据库

1注意事项

1）oracle的数据库概念和mysql不同，它是创建一个账号，将所有表等都放在这个账号下。所以在oracle中的账户等同于mysql中的数据库。

2）不要将system给用户直接使用，权限太大，造成安全隐患，数据泄露，甚至删除别人的账号（数据库）。

3）所以通常给每个业务单独创建账号，单独分配权限。

2密码安全

System账号不能修改，它是超级管理员，密码必须安全，否则非常危险。要设置复杂密码

14yHl9t-hjCMT 疑似银河落九天-回家吃馒头

3创建用户

 

4设置权限

一般只赋给链接connect和资源resource访问权限，需要更大权限则设置dba。

 

使用新账号ht重新登录：

 

4常见错误

1监听失败

 

1）安装时，必须以管理员身份安装，关闭杀毒软件

2）下面两个服务必须启动，有时异常关机会造成服务停止，或者被杀毒软件停止

 

2 PL/SQL自动断掉链接

如果太久未访问oracle，oracle会自动关闭链接。关闭，重新打开即可。

 

5 PL/SQL客户端操作

1选择自己的内容

2常用对象

 

3 SQL窗口

输入SQL语句执行，可以输入多条SQL，通过选中的内容进行执行，不选中执行所有。

 

6 SQL差异

1 日期

Oracle默认日期格式为：dd-mon-yy 日月年。09-6月-99日期为1999年6月9日

insert into student values(2,'张琪','女','01-12月-19',10);

--修改格式为我们国人习惯的格式：yyyy-mm-dd

alter session set nls_date_format='yyyy-mm-dd';

insert into student values(3,'李欢','女','1919-10-11',100);

2 性别

使用的oracle 10g简版中汉字为unicode码，占3个字节。sex char(2)是无法存储的，修改为char(3)就可以。

 

3 Oracle使用decode()函数做条件选择

DECODE（value，if1，then1，if2，then2，if3，then3，......，else），

表示若value等于if1，则返回then1，

否则若等于if2则返回then2,…若均不满足则返回else

 

要求：显示性别，0代表女，1代表男

select xm,sex,decode(sex,1,'男',0,'女') sex from student

4 MySql使用case when做条件选择

select xm,sex,

  case when sex='0' then '女' else '男' end

from student

5第一条数据查询方式

select top 1 * from students #sqlServer数据库

select * from students limit 1 #mysql数据库

select * from dept_p where rownum<2 #oracle数据库

7 过气技术

下面的技术在历史长河中已经被淘汰，但老项目，小项目依然再用，很多在阿里的开发手册中已经禁止使用。

1 虚表 dual

1）dual是一个虚拟表，mysql没有

2）用来构成select的语法规则，oracle保证dual里面永远只有一条记录，用它可以做很多事情。

将结果存储在dual表中：

select 1 from dual #虚拟出一个数字列

select 'abc' from dual #虚拟出一个字符串列

select sysdate from dual #获取系统当前日期

select sys_guid() from dual #获取uuid

2 序列 sequence

Oracle自身提供的自增主键支持，和mysql的实现思路完全不同

 

 

-- 创建序列，没有替换replace语法

create sequence DEPT_ID_SEQ

minvalue 1

maxvalue 999999999999999999999999999

start with 1

increment by 1

cache 20;

--访问当前值：

SELECT DEPT_ID_SEQ.currval FROM dual

--访问下一个值：

SELECT DEPT_ID_SEQ.nextval FROM dual

--插入记录是调用：

insert into user_p (user_id) values(DEPT_ID_SEQ.nextval)

1）必须执行一次nextval后，才可以访问当前值，否则报错：尚未在此会话中定义。执行2）一次nextval值就会按步长增加。说明oracle内部维护这个信息，每次调用就修改这个值。

3授权视图

 

使用system登录，修改ht的权限为dba，否则无法创建视图。

 

或者：使用system账户执行，分配视图权限。

grant create any view to ht;

4视图 view

1）视图本质就是一个查询

2）和我们自己查询的区别是，它执行完会有缓存，下次查询就直接使用。

3）因为事先缓存，无法做优化，大型项目中禁止使用。

4）视图只需创建一次，后面就可以类似表来使用，只是用来查询不能更新和删除

再用ht登录，执行下面SQL语句

create or replace view DEPT_V as

  select *

    from DEPT

 

select * from dept_p #查询表

select * from dept_v #查询视图

可以看到两者查询结果没有分别

5触发器 trigger

记录生效点：BEFORE/AFTER

记录的操作：INSERT/UPDATE/DELETE

例子：不让减薪

当薪资少于10000时，就发10000。

update user_info_p set salary = 300; #更新操作将触发器生效

commit; #提交事务

在更新表

CREATE OR REPLACE TRIGGER UPDATE_USER_INFO_TRI

  BEFORE UPDATE

  ON USER_INFO_P

  FOR EACH ROW

  

DECLARE

    -- local variables here

  BEGIN

    IF :NEW.SALARY<10000 THEN

       :NEW.SALARY:=10000;

    END IF;

  END;

 

6存储过程 procedure

概念：

1）存储过程（Stored Procedure）其实就是数据库端的编程

2）在数据库为王的时代，虽然已经过去，当时数据库大集中，部署在超级好的服务器，所以执行的性能超群，运行在上面的程序性能极佳。

3）但当分布式架构兴起后，数据库在整个架构中的作用一再衰落，逐步边缘化。

4）存储过程中的SQL是依赖数据库厂商，每个厂商都有其个性的SQL，导致程序迁移数据库时，如从oracle迁移到mysql时基本重写，工作量巨大，造成修改的风险。

5）于是主流方式已经将其从数据库端前置到java程序端实现，这样迁移数据库变得轻松。

案例：

非常难维护和调试，这就是被禁止的原因。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE  [dbo].[SP_TMS_AUTO_BILLING] (
@V_TMS_ORDER_ID              VARCHAR (32),              --订单主ID
@V_RETUREN_VALUE             VARCHAR (20)  output       --状态返回)
BEGIN
	DECLARE @V_OPERATION_ITEM VARCHAR (30)     ----订单上的费用协议操作项目
END
--插入表结束	
			FETCH NEXT FROM V_CHARGE_CURSOR 
			INTO @V_CHARGE_CODE, @V_CHARGE_ITEM_NAME, @V_FRT_NAME_EN, @V_RATES_NUMBER, 
				 @V_CUST_OPERATION_ITEM_ID, @V_CUST_CONTRACT_ID, @V_CUST_CODE, @V_CRM_CUST_ID,
				 @V_CUST_INNER_OUTER
		END
		CLOSE V_CHARGE_CURSOR
		DEALLOCATE V_CHARGE_CURSOR

END

8分页

1 rownum伪列

rownum它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

利用这个伪列和两次子查询实现分页查询，如果有排序必须在线排序在分页

2 分页SQL

SELECT * FROM

(

SELECT t.*,ROWNUM r FROM TABLE t

WHERE ROWNUM <= pageNumber*pageSize

)

 WHERE r > (pageNumber-1)*pageSize

--每页3条记录，第1页

select * from

(select t.*,rownum r from dept_p t where rownum <=3)

where r>0

--每页3条记录，第2页

select * from

(select t.*,rownum r from dept_p t where rownum <=6)

where r>3

Oracle实现分页非常变态，需要嵌套查询才能筛选出来，合理吗？而MySQL多么的轻盈。Oracle 8时语法这么写，现在都Oracle 20c了，还没有变化。Oracle为什么会被击倒，可见一斑。