Oracle Database Basic Operations

Statement of the basic operation:

Dml:select, INSERT, UPDATE, DELETE, MERGE

Ddl:create, ALTER, DROP, RENAME, TRUNCATE, COMMENT

Dcl:grant, REVOKE

Transaction control: COMMIT (Release), ROLLBACK (rollback), savepoint

To create a table:

CREATE TABLE Student
(
ID char (2) primary key,
Thename varchar (TEN) is not NULL,
Password varchar (ten) is not NULL,
Gender char (2),

Email varchar (20)
)

Delete tables: drop table Student

Constraints: The embodiment of data integrity, representing the reliability and accuracy of the data, the types of constraints are: PRIMARY KEY constraints (primary key columns are unique, primary keys do not have any meaning), unique constraints (the column is unique), check constraints (limit data types), default constraints (default), FOREIGN KEY constraints

Constraint recommended naming:

Primary key: Pk_colname unique: uq_colname check: Ck_colname

CREATE TABLE Student
(
ID char (2) primary key,----PRIMARY KEY constraint
Thename varchar (ten) NOT NULL,-----non-null constraint
Password varchar (ten) is not NULL,
Gender char (2),
Email varchar (20)
)
ALTER TABLE student add constraint ck_gender check (gender= ' m ' or gender= ' W ')---checking constraints
ALTER TABLE student add constraint uq_email unique (email)----only constraint
ALTER TABLE student Modify (gender char (2) defaults ' m ')----DEFAULT constraint

Multi-table Relationship: Many-to-one

One-to-many time in more than one side plus foreign keys:

CREATE TABLE Employee (
ID int PRIMARY KEY,
Name Char (5) is not NULL,
DeptID int---The value of DeptID from the Department ID
)
CREATE TABLE Department (
ID int PRIMARY KEY,
Name Char (5) NOT NULL
)
ALTER TABLE employee
Add Constraint Fk_deptid
Foreign KEY (DeptID) references Department (ID),----foreign key deptid from the primary key of the departmental table

To delete a database constraint: ALTER TABLE employee DROP constraint Fk_deptid

Summary: The data must be constrained, the data can be accurate and reliable, but the database development with too many constraints to affect its performance, it is generally in the front-end development of the data constraints, and the development of the database less data constraints

Add Data:

SELECT * FROM Employee
INSERT into employee (Id,name) VALUES (1, ' Linxi ')
Commit----------------commit to a database

Oracle Database Basic Operations