sql基础

最新推荐文章于 2022-01-04 16:06:38 发布
qinglangee
最新推荐文章于 2022-01-04 16:06:38 发布
阅读量185
点赞数
分类专栏： DataBase 文章标签： SQL Oracle 数据结构 UP
DataBase 专栏收录该内容
2 篇文章 0 订阅
订阅专栏

－－Oracle数据导出导入 data export import
c:>exp 用户名/密码@IP/数据库名 file=导出文件名
c:> exp test/123@192.168.0.95/hmtdb file=d:/20071114.dmp

--设置当前会话日期格式
alter session set nls_date_format='DD-MM-YYYY';
--得到系统日期
select sysdate from dual;

--今天出现了ORA-01843的错误.这个错误代表无效的月份一般在日期转化的时候会提示。
--解决方法
--在中文系统里要输入'05-6月-1998'的格式，英文是'05-JUN-1998'
alter session set NLS_DATE_FORMAT='DD-MON-YY';

chapter developer
--用PL/SQL   Developer   登录同一数据库时，有的用户能正常登录，
--有的用户不能登录，提示"You are not allowed to use  
--PL/SQL Developer on this database"；
--用SQL*PLUS,DEVELOPER2000都能正常登录，
--用PL/SQL Developer 登录其他数据库都正常

--打开pl/sql developer 的错误如下：
--you are not allowed to use PL/SQL Developer on this database.
--you do not have ths system.logon privilege in the PL/SQL Developer authorization.
--原因：你执行了File菜单下的authorization...,
--pl/sql developer在你的数据库建立了sys.plsqldev_authorization 表，
--而你又没有赋予你的用户权限。
--用以下解决办法 
drop table sys.PLSQLDEV_AUTHORIZATION;

--关于pl/sql developer的登录密码问题 
--能否保存密码?否则每次都需要重新输入
--ps. pl/sql developer 的版本是 5.0
选择“Configure”，然后把“Definition”下的“Store with password”打勾就行了。

在快捷方式的目标后添加 userid=uid/pwd@dbname 或 nologon 即可
辛辛苦苦到plsql develop的官方网站down了个使用手册，查了查才发现命令行参数可以解决问题,送给使用plsql develop的懒人兄弟们
--为什么要这样啊?
--难道你们就一个INSTANCE吗?
--我觉得还是登陆的好~~~~~~~~~
--我倒是有个问题,我不能使用sys/****@WW as sysdba 登陆,在PL/SQL里面有什么办法可以
--用SYS登陆?
--谢谢!
登录对话框最后不是有个connect as 吗？ 里边选择 sysdba 就可以


chapter8
--事务隔离级别
READ UNCOMMITTED  --1)幻像读，不可重复读，脏读都允许
READ COMMITTED    --2)允许幻像读和不可重复读，不允许脏读
REPEATABLE READ   --3)允许幻像读，不允许不可重复读和脏读
SERIALIZABLE      --4)幻像读，不可重复读，脏读都不允许
--oracle支持2,4不支持1,3
--在SERIALIZABLE级别的事务中，
--更新及修改别的事务正更新及修改的数据,会一直等待事务结束
--别的事务更新SERIZLIZABLE级别事务使用中的数据也是如此
--4)的事务中不可以更新2)在4)事务开始后已经更新commit的数据，
--2)的可以更新4)的（因为2)能看到）

--设置事务级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

--sys用户可以授权包的EXECUTE权限，比如授权给store用户
CONNECT sys/change_on_install AS sysdba
GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO store

--查询闪回
--开，参数SYSDATE-10/1440表示10分钟之前，24*60=1440
EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - 10/1440);
--禁用，再次启用闪回操作之前，必须先禁用
execute dbms_flashback.disable();

--根据系统变更号(SCN)进行查询闪回
--获得当前SCN,例如
VARIABLE current_scn NUMBER
EXECUTE :cuuuent_scn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
PRINT current_scn
--然后执行一个Insert语句，Insert...
--然后用SCN做查询闪回的，参数，那再执行查询的结果就是Insert之前的状态
EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn);


chapter9
--查看表空间，用system用户身份登录
SELECT * FROM dba_tablespaces;

--创建用户,后面两行是默认表空间和默认临时表空间
CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE def_tabspace]
[TEMPORARY TABLESPACE temp_tabspace];
--例如
create user henry identified by hooray
default tablespace users
temporary tablespace temp;

--授权用户，用户特权=用户权限
--1）链接数据库的权限，假设有两个用户steve,gail
GRANT CREATE SESSION TO steve,gail;

--修改用户密码,jason的密码改成marcus
--修改自己的密码，输入PASSWORD回车
ALTER USER jason IDENTIFIED BY marcus;

--删除用户
--如果用户的模式中包含对象（例如表等），就要在用户名后加关键字CASCADE
DROP USER jason;
DROP USER jason CASCADE;

--常用的系统特权
CREATE SESSION
CREATE SEQUENCE         创建序列
CREATE SYNONYM          创建同名对象
CREATE TABLE
CREATE ANY TABLE
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
CREATE USER
DROP USER
CREATE VIEW
--授予多个系统特权
GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO steve;
--WITH ADMIN OPTION 可也让用户将得到的权限授予别的用户
GRANT EXECUTE ANY PROCEDURE TO steve WITH ADMIN OPTION;
--将权限授予public就是授予全体用户
GRANT EXECUTE ANY PROCEDURE TO PUBLIC;
--user_sys_privs标准可以查看用户权限
SELECT * FROM user_sys_privs;

--撤销用户的系统特权
--撤销steve的特权时，steve如果已经把特权授予了gall，gall仍然拥有特权
REVOKE CREATE TABLE FROM steve;

--常用对象特权
SELECT
INSERT
UPDATE
DELETE
EXECUTE

--对象特权例子
GRANT SELECT, INSERT, PUDATE ON store.products TO steve;

GRANT UPDATE(LAST_NAME, SALARY) ON store.employees TO steve;

GRANT SELECT ON store.customers TO steve WITH GRANT OPTIION;
--查询对象特权，表user_tab_privs_made
--user_tab_privs_recd查询被授予了哪些表的对象特权
--user_col_privs_recd查询被授予了哪些列的对象特权
SELECT * FROM user_tab_privs_made;

--同名对象
CREATE SYNONYM customers FOR store.customers;
--创建公共同名对象，关键是最后一句
CONNECT system/manager
GRANT CREATE PUBLIC SYNONYM TO store;
CONNECT store/store_password
CREATE PUBLIC SYNONYM products FOR store.products;
--撤销对象特权
--撤销时，如果steve已经授予了gall，gall也同时失去特权
REVOKE SELECT ON store.customers FROM steve;

--创建角色，带密码，不带密码
CREATE ROLE product_manager;
CREATE ROLE hr_manager;
CREATE ROLE overall_manager IDENTIFIED BY manager_password;
--为角色授权
GRANT SELECT, INSERT ON products TO product_manager;
--将角色授予角色
GRANT product_manager TO overall_manager;
--将角色授予用户
GRANT overall_manager TO steve;
--检查授予用户的角色
--用哪个用户登录就可以看到自己被授予的角色
SELECT *  FROM user_role_privs;
--检查授予角色的系统特权
SELECT * FROM role_sys_privs;
--检查授予角色的对象特权
SELECT * FROM role_tab_privs
[WHERE role='HR_MANAGER']
--使overall_manager不是默认角色
CONNECT system/manager
ALTER USER steve DEFAULT ROLE ALL EXCEPT overall_manager;
--用steve登录，启用overall_manager角色
CONNECT steve/button
SET ROLE overall_manager IDENTIFIED BY manager_password;
--清空角色的设置
SET ROLE NONE;
--设置为出overall_manager以外的任何角色
SET ROLE ALL EXCEPT overall_manager;
--撤销角色
CONNECT store/store_password
REVOKE overall_manager FROM steve;
--从角色中撤销特权
REVOKE ALL ON products FROM product_manager;
--删除角色
DROP ROLE overall_manager;
chapter10
--create table 创建表的简化语法
CREATE [GLOBAL TEMPORARY] TABLE table_name (
    column_name type [CONSTRAINT constraint_def DEFAULT default_exp]
	[,column_name type...]
)
[ON COMMIT {DELETE | PRESERVE} ROWS]
[TABLESPACE tab_space];
--例一
CREATE TABLE order_status2(
  id INTEGER CONSTRAINT order_status2_pk PRIMARY KEY,
  STATUS varchar2(10),
  last_modified DATE DEFAULT SYSDATE
);
--例二
CREATE GLOBAL TEMPORARY TABLE order_status_temp (
  id INTEGER,
  status VARCHAR2(10),
  last_modified DATE DEFAULT SYSDATE
)
ON COMMIT PRESERVE ROWS;

--获得有关表的结构信息
DESCRIBE table_name;
--获得表的更详细的信息user_tables,例
SELECT table_name, tablespace_name, temporary
FROM user_tables
WHERE table_name IN ('ORDER_STATUS2', 'ORDER_STATUS_TEMP');
--获得表的列的信息
SELECT * 
FROM user_tab_columns
WHERE table_name='PRODUCTS';

--修改表
  --添加，修改，删除列
  --添加，删除约束
  --启用，禁用约束
--添加列
--例一
ALTER TABLE order_status2
ADD modified_by INTEGER;
--例二
ALTER TABLE order_status2
ADD initially_created DATE DEFAULT SYSDATE NOT NULL;
--修改列
--修改列的长度，条件是列的类型长度可以修改，如VARCHAR2
--只有在表中没有任何行或所有列都为控制是才可以减少列的长度
--此例原来长度为10，该为15
ALTER TABLE order_status2
MODIFY status VARCHAR2(15);
--修改数字列的精度
--同上只有，才能减少
ALTER TABLE order_status2
MODIFY id NUMBER(5);
--修改列的数据类型
--没有值可以任意修改，有值了就必须是兼容类型
ALTER TABLE order_status2
MODIFY status CHAR(15);
--修改列的默认值
--只对以后新插入的值适用
ALTER TABLE order_status2
MODIFY last_modified DEFAULT SYSDATE-1;
--删除列
ALTER TABLE order_status2
DROP COLUMN initially_created;

--约束      约束类型  意义
  CHECK         C     指定一列或一组列必须满足的约束
  NOT NULL      C     指定一列不允许存储空值
  PRIMARY KEY   P     指定表的主键。
  FOREIGN KEY   R     指定表的外键。外键可以自引用
  UNIQUE        U     指定一列或一组列只能存储唯一的值
  CHECK OPTION  V     指定对视图执行的DML操作必须满足子查询的条件
  READ ONLY     O     指定视图是只读的
--添加CHECK约束
--例一
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_status_ck
CHECK (status IN ('PLACED', 'PENDING', 'SHIPPED'));
--例二
ALTER TABLE ORDER_STATUS2
ADD CONSTRAINT order_status2_id_ck
CHECK (id > 0);
--添加NOT NULL约束，使用MODIFY而不是ADD CONSTRAINT
--例一
ALTER TABLE order_status2
MODIFY status CONSTRAINT order_status2_status_nn NOT NULL;
--例二
ALTER TABLE order_status2
MODIFY last_modified NOT NULL;
--添加FOREIGN KEY约束
--先删除列，再添加一个引用employees.employee_id列的FOREIGN KEY
ALTER TABLE order_status2
DROP COLUMN modified_by;

ALTER TABLE order_status2
ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id);
--使用一个带有FOREIGN KEY约束的ON DELETE CASCADE子句，可以指定在父表中
--删除一行记录时，子表的匹配行也都将被删除
ALTER TABLE order_status2
DROP COLUMN modified_by

ALTER TABLE order_status2
ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id) ON DELETE CASCADE;
--使用一个带有FOREIGN KEY约束的ON DELETE SET NULL子句，
--可以指定在父表中删除一行记录时，子表中匹配行的外键被设置为空值
ALTER TABLE order_status2
DROP COLUMN modified_by

ALTER TABLE order_status2
ADD CONSTRAINT order_status2_modified_by_fk
modified_by REFERENCES employees(employee_id) ON DELETE SET NULL;

--添加UNIQUE约束
ALTER TABLE order_status2
ADD CONSTRAINT order_status2_status_up UNIQUE (status);

--删除约束
ALTER TABLE order_status2
DROP CONSTRAINT order_status2_status_uq;

--禁用约束(daixu)
DISABLE CONSTRAINT
--启用约束
ENABLE CONSTRAINT
--延迟约束
DEFERRABLE

--获得有关约束的信息，在
--查看表的约束条件，在dba_constraints表中,用户是user_constraints
--表名要全部大写
--例一
SELECT table_name, constraint_name, constraint_type 
FROM user_constraints 
WHERE table_name='PRODUCTS';
--查询user_cons_columns可以获得有关列的约束的信息
COLUMN column_name FORMAT a15
SELECT constraint_name, colunm_name
FROM user_cons_columns
WHERE table_name='ORDER_STATUS2'
--20070612
--others001
--Oracle中创建自增长列和一些PL/SQL语句修改
--创建表格
create table student_score
(
id varchar2(12) primary key,
name varchar2(12),
score number
)
--创建序列
create sequence score_sqe

--这是一个触发器
create or replace trigger trig_score
--必需使用才有before才有行级触发器
before insert     

on student_score
for each row
begin
    --lpad这是一个字符函数,第一个参数是字符串，第二个长度，第三个是
    --所代替的字符，从左边开始代替，rpad是从右边开始
      select lpad(score_sqe.nextval,5,'0') into :new.id from dual;

end;

--这是修改oracle中的查询语句
insert into      student_score(name,score) values('yhh',98);

select * from emp where ename='mith';

--修改把字符替换
c/'mith'/'MITH'/

--追加
input group by score;

--查询一列中不重复的值
select    distinct(deptno) from    emp;
--设置字符串长度
SQL> alter session set nls_length_semantics = CHAR ;
SQL> alter system set nls_length_semantics = CHAR ;

    Session altered.

SQL> show parameter nls_length_semantics 
--判断是否为空函数 null
nvl(e1,e2)  e2类型要与e1一致
例子：
select nvl(max(lineitemno),0) from tblafspartmasteritem;
--错误  ORA-01745
用了ORACLE 的关键字，或者语句中少了逗号
--错误  ORA-00936 missing expression
1.用select作值的没用括号包起来
2.插入语句的话，去掉values试试
3.列column数量跟values不对应,看是否少了逗号。

--select 语句 列名中有关键字的，用[] 括起来
--欄位名為關鍵字的請用中括號框起來再試看看。

SELECT ID,[POSITION],[DATE] FROM HTADDR.POSTED
--优化简介
1.使用表连接而不是多个查询
2.执行连接时使用完全限定的列引用
3.使用CASE表达式而不是多个查询
4.添加表索引
5.使用WHERE而不是HAVING
6.使用UNION ALL而不是UNION
7.使用EXISTS而不是IN
8.使用EXISTS而不是DISTINCT
9.是用绑定变量