数据库知识点
--oracle创建用户并授权
--创建用户表空间
CREATE TABLESPACE JPM_DATA
LOGGING
DATAFILE 'D:\oracle\11g\app\oracle\oradata\XE\JPM_DATA.DBF'
SIZE 1024M
AUTOEXTEND ON
NEXT 1024M MAXSIZE 5120M
EXTENT MANAGEMENT LOCAL;
--创建用户
--授予权限
grant unlimited tablespace to jpm;--授予jpm用户使用表空间的权限
grant create session to jpm;--授予jpm用户创建session的权限,即登陆权限
grant create table to jpm;--授予创建表的权限
grant drop table to jpm;--授予删除表的权限
grant insert table to jpm;--插入表的权限
grant update table to jpm;--修改表的权限
grant ALL privileges to jpm;--这条比较重要,授予所有权限(all)给用户(jpm)
GRANT DBA TO jpm;
--查看权限
select * from user_sys_privs;--查看当前用户所有权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN ('jpm');
新建表考虑的几点要素:
1. 命名要规范
- 用带有明确含义的英文单词命名,多个单词间以下划线“_”分割。
- 表名一般以 T(table的首字母)开头,而且每张表要有注释。
- 主键名一般以 PK(primary key的首字母)开头,
PK_表名称
- 索引命名以 IDX(index的缩写)开头,
IDX_表名_序列值
- 序列命名以 SEQ(sequence的缩写)开头,
SEQ_表名
2. 合理设计表字段
- 每张表必须有主键,重要的字段放在前面。
- 枚举类型要说明每个枚举值对应的含义,可以新建表维护。
- 每个字段的类型,要根据该字段存储内容设置对应的类型,即varchar2、number 和 date 类型选取要注意。
- 每个字段的最大长度,定义时要参考它所代表的实体对象的取值范围,VARCHAR2 的长度设计要以 8 的倍数递增。
- 每张表保留 4 个字段:创建时间(
CREATE_TIME
)、修改时间(UPDATE_TIME
)、操作人员(OPERATOR
)、备注(REMARK
)。 - 建表时,每个字段有有备注,即数据库字段要有注释。
3. 合理设计表的索引
- 为那些频繁作为查询条件的字段建立索引,提高查询效率。
- 建立索引不是越多越好,要合理考虑应用程序使用情况。
- 在业务上具有唯一特性的字段需建立索引,避免对具有较少值的字段进行索引。
4. 关联表之间的字段设计
- 关联表之间的字段,命名相同。
- 关联表之间的字段,字段类型与大小也相同。
开发时的几条建议:
(1)任何 SQL 语句只要在 where 语句后面添加 is null 或者 is not null,那么 Oracle 优化器将不再使用索引,因此可以为这些列设置默认值,比如用 0 表示空,这样把是否为空转为判断是否为 0,就可以利用上索引。
(2)SQL 在执行带通配符的语句时,如果 %
在首位,那么在字段上建立的主键或者索引将会失效,应该避免类似语句的出现:
SELECT * FROM T_USER T WHERE T.USER_ID = '%19900606%';
但当通配符不在模糊匹配词首出现,又能使用索引,例如:
SELECT * FROM T_USER T WHERE T.USER_ID = '1101011990%';
(3)用 in 代替 or
低效:
SELECT *
FROM T_USER T
WHERE T.USER_NAME = '张三丰'
OR T.USER_NAME = '孙悟空'
OR T.USER_NAME = '刘备';
高效:
SELECT * FROM T_USER T WHERE T.USER_NAME IN ('张三丰', '孙悟空', '刘备');
(4)避免在索引列上使用函数,以及避免在索引列上使用计算
(5)避免出现索引列自动转换,当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换,因此如果是字符串类型,一定要加引号,如果是数字类型,一定不要加引号。
(6)用 >=
替代 >