Oracle's table operations, curd, constraints

Review MySQL CREATE TABLE statement users (ID integer/name string/birthday date type, default today)
Drop table if exists users;
CREATE table if not EXISTS users (
ID int (5) Auto_increment primary KEY,
Name varchar (4) is not NULL,
Birthday date default ' 2015-4-27 '
);

Using Oraclesql, create user table users (ID integer/name string/birthday date/sal integer, default today)
CREATE TABLE Users (
ID Number (5) primary key,
Name VARCHAR2 (8) is not null unique,
Sal Number (6,2) is not NULL,
Birthday Date Default Sysdate
);

Go to Recycle Bin
drop table users;

Querying objects in the Recycle Bin
Show RecycleBin;

Flash back, restore the Recycle Bin
Flashback table name to before drop;
Flashback table name to before drop rename to new table name;

Remove the Users table completely
drop table users purge;

Emptying the Recycle Bin
Purge RecycleBin;

Test the following types
(1) Number (5):
Insert into users (id,name,sal) VALUES (1, ' A ', 6666.66);
Insert into users (id,name,sal) values (one, ' AA ', 6666.66);
Insert into users (Id,name,sal) VALUES (111, ' AAA ', 6666.66);
Insert into users (id,name,sal) VALUES (1111, ' AAAA ', 6666.66);
Insert into users (id,name,sal) values (99999, ' AAAAA ', 6666.66);
Insert into users (id,name,sal) values (100000, ' aaaaaa ', 6666.66); Wrong
5 indicates a maximum deposit of 99999

(2) Number (6,2):
Col sal for 9999.99
INSERT into users (id,name,sal) VALUES (1, ' A ', 6.66);
INSERT into users (id,name,sal) values (one, ' AA ', 66.666);
INSERT into users (Id,name,sal) VALUES (111, ' AAA ', 666.6666);
INSERT into users (id,name,sal) VALUES (1111, ' AAAA ', 6666.66666);
INSERT into users (id,name,sal) values (11111, ' AAAAA ', 66666.666666), error
Number (6,2)
where 2 indicates a maximum of 2 decimal places, rounded, 0 for insufficient digits, and to set col ... for ...
where 6 means that there are no more than 6 bits of decimal + integers
where the integer digits are not more than 4 bits and can be equal to 4 bits

(3) VARCHAR2 (8):
Insert into users (id,name,sal) VALUES (1, ' A ', 7777.77);
Insert into users (id,name,sal) VALUES (2, ' AA ', 7777.77);
Insert into users (Id,name,sal) VALUES (3, ' AAA ', 7777.77);
Insert into users (id,name,sal) VALUES (4, ' AAAA ', 7777.77);
Insert into users (id,name,sal) VALUES (5, ' AAAAA ', 7777.77);
Insert into users (Id,name,sal) VALUES (6, ' aaaaaa ', 7777.77);
Insert into users (id,name,sal) VALUES (7, ' aaaaaaa ', 7777.77);
Insert into users (id,name,sal) VALUES (8, ' aaaaaaaa ', 7777.77);
Insert into users (id,name,sal) VALUES (9, ' aaaaaaaaa ', 7777.77);

Insert into users (id,name,sal) VALUES (1, ' ha ', 7777.77);
Insert into users (id,name,sal) VALUES (2, ' haha ', 7777.77);
Insert into users (Id,name,sal) VALUES (3, ' hahaha ', 7777.77);
Insert into users (id,name,sal) VALUES (4, ' haha-ha ', 7777.77);
Insert into users (id,name,sal) VALUES (5, ' ha ha ha ', 7777.77);

8 = Byte
GBK Zhao 2 bytes

(4) Date: The default format is: ' 2 July-April-15 '
(5) CLOB "Character Large Object": Large text object, which is more than 65565 bytes of data object, storing up to 4G
(6) BLOB "Binary Large Object": binary objects, i.e. pictures, audio, video, up to 4G storage

Add an image column to the EMP table, ALTER TABLE table name add column name type (width)
ALTER TABLE EMP
Add image blob;

Modify the ename column length to 20 bytes, ALTER TABLE table name modify column name type (width)
ALTER TABLE EMP
Modify Ename varchar2 (20);

Delete image column, ALTER TABLE name drop column name
ALTER TABLE EMP
Drop column image;

Rename ename to username,alter table table name rename column name to new column name
ALTER TABLE EMP
Rename column ename to username;

Rename the EMP table emps,rename the original table name to the new table name
Rename EMP to Emps;

Note: When you modify a table, it does not affect the data in the table

Written question: There are "100 billion" member records, how to use the most efficient way to pay word Chingqing 0, the other field content unchanged?

First: Remove the Sal field from the EMP table
ALTER TABLE EMP
Drop column Sal;

Second: Add the Sal field to the EMP table, and the content defaults to 0
ALTER TABLE EMP
Add Sal Number (6) default 0;

Modify table is not rolled back

CREATE TABLE Customers (single) and orders (multi), use primary key/not null/unique/default/foreign key constraint
To reflect "on delete cascade/on delete set NULL"
Requirement: Delete customer, cascade Delete all of his orders
Delete from customers where id = 1;
Requirement: Delete customer, cascade Delete all of his orders, just set the external health to NULL
Delete from customers where id = 1;

CREATE TABLE Customers (
ID Number (3) primary key,
Name VARCHAR2 (4) NOT null unique
);
INSERT into Customers (Id,name) VALUES (1, ' A ');
INSERT into Customers (Id,name) VALUES (2, ' B ');

Create TABLE orders (
ID number (3) primary key,
ISBN varchar2 (6) Not null unique,
The price number (3) is not null,< br> CID Number (3),
Constraint CID_FK foreign key (CID) references customers (ID) on DELETE cascade
--constraint CID _FK foreign KEY (CID) references customers (ID) on delete set null
);
INSERT into orders (ID,ISBN,PRICE,CID) VALUES (1, ' isbn10 ', 10, 1);
INSERT into orders (ID,ISBN,PRICE,CID) VALUES (2, ' Isbn20 ', 20, 1);
INSERT into orders (ID,ISBN,PRICE,CID) VALUES (3, ' Isbn30 ', 30,2);
INSERT into orders (ID,ISBN,PRICE,CID) VALUES (4, ' isbn40 ', 40,2);

CREATE table students, including id,name,gender,salary fields, using CHECK constraints "gender can only be male or female, salary is between 6000 and 8000"
CREATE TABLE Students (
ID Number (3) primary key,
Name VARCHAR2 (4) is not null unique,
Gender VARCHAR2 (2) NOT NULL check (gender in (' Male ', ' female ')),
Salary number (6) NOT NULL check (salary between 6000 and 8000)
);
INSERT into students (Id,name,gender,salary) VALUES (1, ' haha ', ' middle ', 6000);
INSERT into students (Id,name,gender,salary) VALUES (2, ' hehe ', ' Male ', 5000);
INSERT into students (Id,name,gender,salary) VALUES (3, ' hehe ', ' female ', 7000);

Modifying FOREIGN KEY constraints
1. Delete foreign keys
sql> ALTER TABLE orders drop constraint CID_FK;
2. Re-add foreign KEY constraints
sql> ALTER TABLE orders DROP constraint CID_FK foreign key (CID) reference customers (ID) on delete set null;
Test:
Sql> Delete from customers where id = 2;
Sql> select * from Orders;

ID ISBN Price CID
---------- ------------ ---------- ----------
3 Isbn30 30
4 ISBN40 40

sql> Update orders Set cid = 2;
* Error on line 1th:
ORA-02291: Violation of the full constraint (SCOTT. CID_FK)-parent keyword not found
1. Insert the parent table data before you can
Sql> INSERT into Customers (Id,name) VALUES (2, ' B ');
2. Modify the Sub-table
sql> Update orders Set cid = 2;
Sql> select * from Orders;
ID ISBN Price CID
---------- ------------ ---------- ----------
3 Isbn30 30 2
4 ISBN40 40 2

Oracle's table operations, curd, constraints