oracle-tablespace-User-roles-permissions-constraints

One, table space 1.1 create TABLE space

--Problem: Create a tablespace named HP, specifying a data file of hp.dbf, and a size of 10m.

' C:\app\hp\hp.dbf ' size 10Mautoextend on next 5mmaxsize unlimited;

1.2 Deleting a table space

Drop tablespace hp;

1.3 Study Questions

How do I delete an associated data file when I delete a tablespace?

Drop tablespace test_data including contents and datafiles;

Two create user 2.1 create user

--Syntax structure: Create user username identified by password "default tablespace tablespace name" "Temporary tablespace temp"

123456123456default123456default tablespace users Temporary tablespace temp;

2.2 Modifying user passwords

--Syntax structure: ALTER USER username identified by password

654321;

2.3 Deleting a user

--drop user username "Cascade"

Drop user Hope;drop user hope Cascade;

Three permissions and Roles

--permission refers to the right to execute a specific type of SQL command or to access other objects

--System permissions: Allow users to perform certain database operations

--Object permissions: Allows a user to perform a specific action on a particular object

--a role is a combination of a set of permissions with a name

3.1 Common system pre-defined roles

--CONNECT: Temporary user

--RESOURCE: A more reliable and formal user

--DBA: Database Administrator role with the highest permissions to administer the database

In general, the role of Connect and resource is sufficient for ordinary users.

3.2 Assigning Permissions and Roles

--Syntax structure: Grant role 1, role 2, ... to user

Grant connect to Hope2grant Connect,resource to Hope2;

--gives hope2 the ability to query Scott's EMP table

SelectSelect, update on scott.emp to Hope2;

--Revoke roles and permissions:

--Grammatical structure: Revoke role 1, role 2, ... From user

from Hope2; 

Four Oracle data Types @@

Type	Grammar		Describe
Text type	CHAR (n)	Fixed length	Maximum 2000 characters, default N=1
	VARCHAR2 (N)	Variable length	Maximum 4000 characters
	NCHAR (n)	Fixed length	Unicode data type, Max 2000
	NVARCHAR2 (N)	Variable length	Unicode data type, max 4000
	CLOB (character large Object)		Character large object, Max 8TB
	Blob (Birnary large Object)		Binary data, can store picture, sound, maximum 8TB
Numerical	Number (P,s)	Variable length	P-effective Bits, S-decimal digits; 1<=p<=38,-84<=s<=127
Date	Date		Default format: dd-mm-yyyy ' 1 June-May-2017 '
	Timestamp (n)

4.1 Text types

--char: can store fixed-length strings char (200)

--varchar: can store variable-length strings

--VARCHAR2: can store variable-length strings VARCHAR2 (200)

-When to use char, when to use varchar (2) @@

When the string length is known to be more appropriate with Char

--When you are unsure of the length of a string, use VARCHAR2 to be appropriate.

--nchar and NVARCHAR2 can store Unicode character sets

4.2 Numeric types

--Numeric data type:numer

--number equivalent to number (38,0)

--number (15) indicates that a 15-bit integer can be stored

--number (22,7) indicates a floating-point number, an integer part with 15 digits, and 7 digits after the decimal point.

-- Note : We do not recommend the use of varchar,integer,float,double

4.2 Date Data type

--Functions related to date types

--sysdate: Date function: Displays the current date

Select Sysdate from dual;

--systimestamp: A time that is more accurate than date

Select Systimestamp from dual;

--to_date (String, format): Converts an Oracle-not-recognized string into a date format recognized by Oracle

Select To_date (' 2018-01-05 ', ' YYYY-MM-DD ') from dual;

--date: Date type, storage date and time

--timestamp: A date type that is more precise than date.

--Default date format:

--Oracle in Chinese: day-month-year, July 1, 1998-1998 ' January-July

--English oracle:day-mon-year such as July 1, 1998 ' 01-jul-1998 '

--lob:

--blob: Storing binary objects, like, audio, and video files

--clob: Large objects that store character formatting

Five table 5.1 Create a table

1. Create with Sql/plus

Name of the CREATE TABLE table (field name 1 data type, field Name 2 data type ...) ）;

Example:

To create a student table, the fields and types are as follows:

Field	Field type
Id	Number
Name	VARCHAR2 (20)
Sex	CHAR (2)
Birthday	Date
Score	Number (4,2)

CREATE TABLE Student (

ID number,

Name VARCHAR2 (20),

Sex char (2),

Birthday date,

Score Number (4,2)

)

2. Create with Plsql developer third-party tools

--Create TABLE: Actually solves the problem of table structure, table has several fields, how long each field is, what data type each field is

--create Table name (field name 1 data type 1 (length), Field name 2 data type 2 (length), ... )；

CREATE TABLE Student (Stuno number  (4), Stuname varchar2 (ten), age number (2), addr varchar ());

5.2 Deleting a table

drop table student;

5.3 Modifying a table

1. Modify the table name

RENAME the old table name to the new table name;

2. Adding columns

ALTER Table name ADD

(Column name data type [default defaults],

Column name data type [default defaults],

...);

3. Modifying columns

ALTER Table name MODIFY

(Column name data type [default defaults],

Column name data type [default defaults],

...);

4. Delete Columns

ALTER TABLE name drop column name;

ALTER TABLE name drop (column name 1, column name 2,...);

5. For example

(1) Add Class number to student table

ALTER TABLE students Add (class_id number);

(2) Student name becomes VARCHAR2 (30)

ALTER TABLE students modify (name VARCHAR2 (30));

(3) Delete the score field of student table

ALTER TABLE students drop column score;

ALTER TABLE students drop (score);

(4) Change the student name students to Stu

Sql>rename students to Stu;

Six constraints

--Problem : When inserting data into a table, there may be some problems, such as: repeated insertion of data, the content is not correct (gender)

-How to ensure the integrity and consistency of data in database tables?

--common constraints: PRIMARY key (primary key), foreign key (foreign key), unique (unique), non-null (NOT NULL), default, user-defined (check)

6.1 PRIMARY KEY constraints

-What is a PRIMARY KEY constraint? The primary KEY constraint is to define a primary key for the table and what is the primary key?

-primary key is used to ensure that the table record is the only non-empty.

--Add a primary key when building a table

CREATE TABLE Student (Stuno number (4) Primary key,stuname varchar2 (ten), age number (2), addr varchar (+); INSERT into student values (1001,'Zhang',18 ,'Luoyang');

--insert same record again, illegal primary key constraint, do not allow insertion

sql> INSERT into student values (1001, ' Zhang ', ' Luoyang ');

INSERT into student values (1001, ' Zhang ', ' Luoyang ')

ORA-00001: Violates the unique constraint condition (SYS. sys_c0010797)

--The PRIMARY key constraint is also violated if the primary key is null and does not allow insertion

sql> INSERT into student values (null, ' Zhang ', ' Luoyang ');

INSERT into student values (null, ' Zhang ', ' Luoyang ')

--Create a curriculum:

CREATE TABLE Course (CNO number (4) Primary key,cname VARCHAR2 (a), Cscore number (2));

--Create a score table: School number and course number together as a primary key, called a federated primary key

--a table can have only one primary key

CREATE TABLE Score (SNO number (4), CNO number (4), score number (5,2), constraint Pk_score primary key (SNO,CNO));

drop table course;

CREATE TABLE Course (CNO number (4) Constraint Pk_course primary key, CNAME varchar2 (+), Cscore number (2));

Homework:

1. How to delete associated data files when you delete a tablespace

Drop tablespace test_data including contents and datafiles;

2. Review all the contents of this week, preview the relevant content of the constraint, and ask questions next week.

oracle-tablespace-User-roles-permissions-constraints