掌握SQL基础需要了解的小知识
先从贯穿整体的数据字典开始说起
一、Oracle数据字典
数据字典是Oracle存放有关数据库信息的地方,是用来描述数据的,比如一个表的创建信息,访问权限等
命名惯例:
前缀 |
目的 |
静态数据字典 |
|
user |
用户所拥有的信息 |
all |
包含用户信息,以及当前用户可以访问其他表的信息 |
dba |
数据库管理员视图,可以查看所有信息 |
1、静态数据字典
主要由表和视图组成,表不可直接访问,视图可以。
(1)user_users视图:存放当前用户信息,例如用户名,账户id,账户状态,表空间名,创建时间等。
(2)user_tables视图描述当前用户拥有的所有表信息,包括表名,表空间名,簇名,通过这个视图可以清晰的查到当前用户可以操作的表有哪些
(3)user_objects视图包含当前用户拥有的对象的信息,对象包括表,视图,索引,序列等。所能反馈的信息比user_tables更全。
(4)usesr_tab_privs视图存储当前用户下所有表的权限信息,例如要查看当前用户对表employees的权限
(5)user_tab_columns:中包含了当前用户表,视图和Clusters中列信息。
USER_CONSTRAINTS describes the constraint definitions on your tables.
USER_CONS_COLUMNS describes columns that are owned by you and that are specified in constraints.
(6)user_constraints:主要包含约束信息
2、动态数据字典:Oracle包含了一些潜在的由系统管理员如SYS维护的表和视图,由于数据库运行他们会不断更新,所以称他们为动态数据字典,提供了关于内存和磁盘的运行情况,只可读不可写
desc dictionary;汇总了所有信息
select * from dictionary;
select * from dictionary where table_name=’USER_OBJECTS’;
二、分组函数
多个输入对应一个输出
1、avg,count,max,min,sum
2、group by 子句
三、多表查询
1、自然连接两表要有相同的列以及数据类型,选择两表中相同的列作为匹配的列
当有多行可以作为连接列时使用using确定连接列
natural join 以及join 连接时不可以对列取别名
四、子查询
子查询的结果作为主查询的条件
1、单行子查询
2、多行子查询
in,any,all
<all小于子查询的最小值
>all大于子查询的最大值
<any 小于子查询最大值
>any大于子查询最小值
五、集合操作
union求并集
/union all
intersect
minus
6、DML语句
1、insert可以使用单&操作符来简化操作
2、update
(1)更新单行
update employees
set department_id=50
where employee_id=113;
(2)更新多行
update copy_emp
set department_id=110;
(3)更新指定行
3、delete,truncate
drop:DDL语句、表结构与行数据全部删除,不可回退,可以闪回
truncate:DDL语句,只删除行数据,表结构还在,不可回退
delete:DML语句,可以删除指定行和所有行,可以回退,会产生大量的重做日志。
七、TCL
commit,rollback,savepoint
数据库事务的开始与结束:
执行DML语句时产生事务,commit或者rollback结束,DDL或DCL执行时结束,用户正常退出时结束,系统崩溃时结束。
回滚点savepoint
for update将指定列锁定,只可读不可写,只有在commit或者rollback时才会解锁
SELECT employee_id, salary, commission_pct, job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE
ORDER BY employee_id;
八、DDL
数据库对象有:表,视图,索引,同义词,序列
表和列的命名规则:
以字母开头,1-30个字符长度,仅仅由A-Z,a-z,_,$,和#组成
数据类型 |
描述 |
varchar2(size) |
可变长字符 |
char(size) |
定长字符 |
number |
数字类型 |
date |
日期类型 |
timestamp |
以更详细的方式显示时间 |
表与表之间的约束有:
非空,唯一,主键(非空且唯一),外键,check
1、外键约束
方法一:
CREATE TABLE employees(employee_id NUMBER(6)CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name VARCHAR2(20));
方法二:
CREATE TABLE employees(employee_id NUMBER(6),irst_name VARCHAR2(20),CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
2、添加非空约束
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
CONSTRAINT emp_email_uk UNIQUE(email));
3、外键约束
test1为子表,test2为母表
此时向test1添加外键约束会报错,原因是
所以向test2中添加数据
就可以顺利的添加外键约束
4、添加检查约束
5、通过子查询创建表
CREATE TABLE dept80
AS
SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM employees
WHERE department_id = 80;
6、alter table
可以向指定表中添加指定列
alter table dept80
add (job_id varchar2(20));
alter table dept80
modify (last_name varchar2(20));
alter table dept80
drop (job_id);