DataBase学习笔记.doc

1 Oracle1.1 用户登录和修改密码SYS身份登录：sqlplus sys/sys as sysdba普通身份登录：sqlplus username/password普通账户解锁：1、 SYS身份登录2、 alter user scott account unlock修改账户密码：格式：alter user username identified by password1、 SYS身份登录2、 alter user scott identified by tiger1.2 SQL语言分类SQL语言总共有四类：数据控制语言DCL：grant、deny、revoke，用来控制数据库组件的存取许可、权限等；数据定义语言DDL：create、alter、drop，用来建立数据库、数据库对象和定义列；数据操纵语言DML：select、insert、update、delete，用来操纵数据库中的数据；其他语言元素：如流程控制语言、内嵌函数、批处理语句等。1.3 数据控制语言DDLgrantdenyrevoke1.4 数据定义语言DDLcreatealter修改字段的大小或类型alter table t_admin_yh modify yhxm varchar2(20);drop删除用户drop user U_EBCMTZ cascade；删除表空间drop tablespace tablespacename including contents and datafiles;1.5 数据操纵语言DML显示表结构：desc 表名;例：desc emp；表dual：dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下：1、查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;2、用来调用系统函数select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual;-获得当前系统时间select SYS_CONTEXT(USERENV,TERMINAL) from dual;-获得主机名select SYS_CONTEXT(USERENV,language) from dual;-获得当前 localeselect dbms_random.random from dual;-获得一个随机数3、得到序列的下一个值或当前值，用下面语句select your_sequence.nextval from dual;-获得序列your_sequence的下一个值select your_sequence.currval from dual;-获得序列your_sequence的当前值4、可以用做计算器 select 7*9 from dual;Oracle字符串连接：和其他数据库系统类似，Oracle字符串连接使用“|”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样。除了“|”，Oracle还支持使用CONCAT()函数进行字符串拼接。Oracle中的单双引号：distinct：去掉重复的值。select distinct deptno, job from emp;null查询：select ename, sal, comm from emp where comm is null;select ename, sal, comm from emp where comm is not null;Oracle中的in和exists：Oracle中的转义字符：Oracle中的通配符：常用数值函数、转换函数、字符串函数：lower(字段)uper(字段)substring(字段)char(字段)to_char();select to_char(sysdate, YYYY-MM-DD HH:MI:SS) from dual;select to_char(sysdate, YYYY-MM-DD HH24:MI:SS) from dual;to_date()to_number()nvl()round()聚合函数：count()：select count(*) from emp;统计记录数。max()min()sum()avg()约束：create table student( id number(6), name varchar2(20) constraint stu_name not null, sex number(1), age number(3), state date, grade number(2) default 1, class number(4), email varchar2(50), constraint stu_class_fk forign key (class) references class(id), constraint stu_id_pk primary key (id), constraint stu_name_email_unique unique(email,name)create table class( id number(4) primary key, name varchar2(20) not null)非空(not null)constraint stu_name not nullstu_name是非空约束的名字，名字可以自定义。或者直接not null表示唯一(unique)constraint stu_name_email_unique unique(email,name)stu_name_email_unique是唯一约束的名字，名字可以自定义。unique(email,name)表示email,name组合唯一约束。主键(primary key)constraint stu_id_pk primary key (id)stu_id_pk是主键约束取的名字，名字可以自定义。primary key (id)表示约束id为主键。或者直接在id后面用primary key表示外键(foreign key) constraint stu_class_fk forign key (class) references class(id)stu_class_fk forign是外键约束取得名字，名字可以自定义。forign key (class) references class(id)表示约束的是class字段受class表中的id约束。或者直接在class后面用references class(id)表示。外键约束被参考的字段必须是主键。即class表中的id必须是主键。CHECK 1.6 创建、授权用户/* 创建表空间和用户 */sysdba身份登录cmd-sqlplus sys/sys as sysdba- 1、创建临时表空间tmp_tbs_ebcmtz，maxsize unlimited：指定无限制大小carete temporary tablespace tmp_tbs_ebcmtz tempfile d:oracletablespacetmp_tbs_ebcmtz.dbf size 50m autoextend on next 50m maxsize 2048m extent management local;- 2、创建表空间tbs_ebcmtzcarete temporary tablespace tbs_ebcmtz tempfile d:oracletablespacetbs_ebcmtz.dbf size 50m autoextend on next 50m maxsize 2048m extent management local;-maxsize unlimited- 3、创建用户u_ebcmtzcreate user u_ebcmtz identified by u_ebcmtz default tablespace tbs_ebcmtz temporary tablespace tmp_tbs_ebcmtz;- 4、授权用户- grant/revoke role privileges 授权、撤权角色权限grant connect,resource to u_ebcmtz; -撤权，将to改为from，即grant connect,resource from u_ebcmtz;grant exp_full_database to u_ebcmtz; grant imp_full_database to u_ebcmtz; - grant/revoke system privileges 授权、撤权系统权限grant create procedure to u_ebcmtz; grant create trigger to u_ebcmtz; grant execute any procedure to u_ebcmtz; grant grant any privilege to u_ebcmtz; grant restricted session to u_ebcmtz; grant select any table to u_ebcmtz; grant unlimited tablespace to u_ebcmtz; grant create any view to u_ebcmtz; -如果出现oracle错误6550 6510 ：标识符sys.dbms_defer_import_internal必须声明，则需要授权此两项。用户解决用户数据导入导出是碰到的此类问题grant execute on sys.dbms_defer_import_internal to u_ebcmtz;grant execute on sys.dbms_export_extension to u_ebcmtz;- 5、删除用户drop user u_ebcmtz cascade;- 6、删除表空间drop tablespace tablespacename including contents and datafiles ;1.7 数据备份- exp导出-exp导出备份时，有时一些空表无法备份出来，所以要先执行下列语句，用于批量查找空表，执行查找后的alter语句，就手动给空表分配内存了，这样就可以导出空表。select alter table |table_name| allocate extent; from user_tables where num_rows=0; -执行后返回alter语句，执行这些语句- 四种主要方式（完全、用户、表）- 1、安全，完全导出整个数据库 full=y exp system/managerorcl buffer=64000 file=d:full.dmp full=y -以管理员身份完全导出整个数据库。full,完全导出。 exp u_ebcmtz/u_ebcmtzorcl buffer=64000 file=d:full.dmp full=y -如果要执行完全导出，必须具有特殊的权限，如用户u_ebcmtz已被授权exp_full_database等权限。- 2、用户，导出指定用户数据库对象 owner= exp u_ebcmtz/u_ebcmtzorcl buffer=64000 file=d:u_ebcmtz0605.dmp owner=u_ebcmtz -owner:指定导出用户 - 3、表，导出表或者指定表。tables参数，用()括起要导入的表 exp u_ebcmtz/u_ebcmtzorcl buffer=64000 file=d:u_ebcmtz0605.dmp owner=u_ebcmtz tables=(t_admin_yh) -导出u_ebcmtz的t_admin_yh单个表 exp u_ebcmtz/u_ebcmtzorcl buffer=64000 file=d:u_ebcmtz0605.dmp owner=u_ebcmtz tables=(t_admin_yh,t_admin_js,t_admin_jg) -导出u_ebcmtz的多个表 -4、表中数据，指定表的指定字段的数据，姓名中以li开头的数据导出 -exp u_ebcmtz/u_ebcmtzorcl file=d:u_ebcmtz0605.dmp tables=(t_admin_yh) query= where name like li%; -如果导出出现：ora-12154: tns: 无法解析指定的连接标识符，删除orcl即可 - exp参数- buffer： 下载数据缓冲区，以字节为单位，缺省依赖操作系统- consistent：下载期间所涉及的数据保持read only，缺省为n;自export启动后，consistent=y冻结来自其它会话的对export操作的数据对象的更新， -这样可以保证dump结果的一致性。但这个过程不能太长，以免回滚段和联机日志消耗完。- direct： 使用直通方式 ，缺省为n;direct=y，取代buffer选项，query选项不可用有利于提高速度- feeback： 显示处理记录条数，缺省为0，即不显示- file： 输出文件，缺省为expdat.dmp- filesize： 输出文件大小，缺省为操作系统最大值- indexes： 是否下载索引，缺省为n，这是指索引的定义而非数据，exp不下载索引数据- log： log文件，缺省为无，在标准输出显示- owner： 指明下载的用户名- query： 选择记录的一个子集- rows： 是否下载表记录- tables： 输出的表名列表- imp导入- 二种主要方式（完全、用户、表）- 1、导入数据库对象imp u_ebcmtz/u_ebcmtzorcl file=d:u_ebcmtz0605.dmp fromuser=u_ebcmtz touser=u_ebcmtz -fromuser就是把当前的dmp文件中的某一个用户下的数据取出。-touser就是把现在dmp文件中的数据导入到目标库的指定user下。imp u_ebcmtz/u_ebcmtzorcl file=d:u_ebcmtz0605.dmp full=y -ignore=y fromuser=u_ebcmtz touser=u_ebcmtz，full不能与fromuser/touser同时使用。-对于部分已经导入的表，使用ignore=y可以忽略导入过程报错- 2、导入部分表imp u_ebcmtz/u_ebcmtzorcl file=d:u_ebcmtz0605.dmp tables=(t_admin_yh) -导入u_ebcmtz0605.dmp中指定表t_admin_yh- imp参数- buffer： 上载数据缓冲区，以字节为单位，缺省依赖操作系统- commit： 上载数据缓冲区中的记录上载后是否执行提交- feeback： 显示处理记录条数，缺省为0，即不显示- file： 输入文件，缺省为expdat.dmp- filesize： 输入文件大小，缺省为操作系统最大值- fromuser： 指明来源用户方- ignore： 是否忽略对象创建错误，缺省为n，在上载前对象已被建立往往是一个正常现象，所以此选项建议设为y- ind