数据库基本操作命令一

最新推荐文章于 2024-09-21 11:30:16 发布

kusch

最新推荐文章于 2024-09-21 11:30:16 发布

阅读量245

点赞数 1

文章标签：数据库 table 脚本 sql user date

本文链接：https://blog.csdn.net/kusch/article/details/6191611

版权

1	创建用户
	SQL> CREATE USER xiaoxc IDENTIFIED BY kusch
	2 DEFAULT TABLESPACE myDataTS
	3 TEMPORARY TABLESPACE myTempTS;

	User created.

2	创建临时表空间
	SQL> CREATE TEMPORARY TABLESPACE myTempTS
	2 TEMPFILE 'D:/oracle/product/10.1.0/oradata/STUDY/myTempTS.DBF' SIZE 10M
	3 AUTOEXTEND ON NEXT 1M MAXSIZE 20M;

	Tablespace created.

3	创建永久表空间
	SQL> create tablespace myDataTS
	2 DATAFILE 'D:/oracle/product/10.1.0/oradata/STUDY/myDataTS.DBF' SIZE 10M
	3 AUTOEXTEND ON NEXT 1M MAXSIZE 100M;

	Tablespace created.

4	解锁数据库的某一用户
	SQL> conn sys/kusch@study as sysdba
	Connected.
	SQL> alter user scott identified by kusch account unlock;

	User altered.

5	查看某一角设的所拥有的权限
	my.....>select * from dba_sys_privs where grantee='MYROLE';

	GRANTEE PRIVILEGE ADM
	------------------------------ ---------------------------------------- ---
	MYROLE CREATE TABLE NO
	MYROLE CREATE TABLESPACE NO

6	创建一个用户后，如果用该用户创建表时，一定得先给这个用户分配表空间的使用配额，否则无法创建表成功。
	MY...>CONN SYS/KUSCH@STUDY AS SYSDBA
	Connected.
	MY...>ALTER USER XIAOXC QUOTA UNLIMITED ON DATA_INFOR_TS

7	修改数据库表的表名
	MY...>RENAME DEPT_LOCAIONS TO DEPT_LOCATIONS;

	Table renamed.

8	为表添加check约束
	MY...>ALTER TABLE DEPENDENT
	2 ADD CONSTRAINT CK_DEPENDENT_SEX CHECK(SEX IN ('F','M'));

	Table altered.

9	为表添加主键的两种脚本实现方法
	法一:	创建数据表的时候直接添加,脚本如下:
		MY...>create table PROJECT
		2 (PNAME VARCHAR2(20),
		3 PNUMBER NUMBER PRIMARY KEY,
		4 PLOCATION VARCHAR2(20),
		5 DNUM NUMBER REFERENCES DEPARTMENT);

		Table created.
	法二:	数据表创建好后,修改数据表,脚本如下:
		MY...>create table DEPARTMENT(DNAME VARCHAR2(20),
		2 DNUMBER NUMBER,
		3 MGRSSN VARCHAR2(20),
		4 MGRSTARTDATE DATE);

		Table created.

		MY...>alter table DEPARTMENT
		2 ADD CONSTRAINT PK_DEPTARTMENT PRIMARY KEY (DNUMBER);

		Table altered.

10	为表添加外键的两种脚本实现方法
	法一:	创建数据表的时候直接添加,脚本如下 :
		MY...>CREATE TABLE WORKS_ON( ESSN VARCHAR2(20) REFERENCES EMPLOYEE,
		2 PNO NUMBER REFERENCES PROJECT,
		3 HOURS NUMBER(4,1));

		Table created.

	法二:	数据表创建好后,修改数据表实现,脚本如下:
		MY...>create table DEPARTMENT(DNAME VARCHAR2(20),
		2 DNUMBER NUMBER,
		3 MGRSSN VARCHAR2(20),
		4 MGRSTARTDATE DATE);

		Table created.

		MY...>ALTER TABLE DEPARTMENT
		2 ADD CONSTRAINT FK_DEPT_EMP FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN);

		Table altered.