Basic Operations for database tables

I. Creating and Managing Tables

1.1 commonly used data types

VARCHAR/VARCHAR2---> represents a String

Number (n)---> represents an integer, the length of an integer is n, you can use int

Number (M,N)---> represents a Decimal, a numeric fractional length of n, an integer length of m-n, and a float can be used

Date---> represents the type of day and is stored in the standard date format

CLOB---> large objects, representing large text data, typically storing 4G text

Blob---> large objects, representing binary data, can hold up to 4G, such as: movies, songs, pictures

establishment of the 1.2 table

syntax: CREATE table table name (

Field name 1 field type [ default defaults ],

...

field name n field type [ default defaults ],

)

CREATE table table name as ( subquery )---> results of subqueries as a single table

if the subquery writes:SELECT * from EMP; indicates that the table structure and table contents are copied together

if the subquery writes:SELECT * from emp where 1 = 2; join a condition that will never be set up,

It means the structure of the table is copied, not the contents of the table.

Copy the structure of the table: Select Table Temp as (select* from EMP where 1=2);

deletion of 1.3 tables

Syntax:drop table name ;

modification of the 1.4 table

You can add new columns by using the ALTER command

Example 1: Add an address column to the EMP table

ALTER TABLE from Add (address varchar2 () default ' temporary no addresses ');

Example 2: Modifying the ename in the EMP table tochange the length to 50

ALTER TABLE from modify (ename VARCHAR2 (50));

1.5 renaming a table

syntax:rename old table name to new table name

1.6 Truncation Table

If you need to empty a table's data but do not need to roll back at the same time, you can release the resource immediately to use the truncated table

syntax:truncate TABLE name

Ii. constraints

use constraints to better ensure the integrity of data in the database

Classification of 2.1 constraints

A, PRIMARY KEY constraint primary key: Represents a unique identity, cannot be duplicated, cannot be empty

typically used on the ID , the PRIMARY KEY constraint can be specified at the time the table is constructed

Example 1: Creating a person table and adding a PRIMARY KEY constraint on the PID

CreateTable person (

PIDVARCHAR2 (primary key),

NAMEVARCHAR2 (200),

Agenumber (3),

Birthdaydate,

SEVVARCHAR2 (3) Default ' male ',// defaults

)

Example 2: Specify the name of the constraint:

CreateTable person (

PIDVARCHAR2 (18),

NAMEVARCHAR2 (200),

Agenumber (3),

Birthdaydate,

SEXVARCHAR2 (3) defult ' man ',

CONNSTRAINTPERSON_PID_PK primary KEY (PID)

)

b, unique constraint unique: A table value allows you to establish a PRIMARY key constraint, while the other columns do not want duplicate

Value, you can use a unique constraint

CREATE TABLE Person (

PID VARCHAR2 (18),

Name VARCHAR2 (200),

Age Number (3),

Birthday date,

Sex VARCHAR2 (3) Default ' male ',

Constraint PERSON_PID_PK primary KEY (PID)

)

C, check the constraint check: Check whether the contents of a column is legitimate

CREATE TABLE Person (

PID VARCHAR2 (18),

Name VARCHAR2 ($) Unique NOT NULL,

Age Number (3) is not a null check (age between 0 and 150),

Birthday date,

Sex VARCHAR2 (3) Default ' male ' Check (Sex in (' male ', ' female ', ' medium ')),

Constraint PERSON_PID_PK primary KEY (PID)

)

D, non-null constraint not nill: The contents of the field cannot be empty

CREATE TABLE Person (

PID VARCHAR2 (18),

Name VARCHAR2 ($) is not NULL,

Age Number (3) is not NULL,

Birthday date,

Sex VARCHAR2 (3) Default ' male ',

Constraint PERSON_PID_PK primary KEY (PID)

)

e, primary - FOREIGN KEY constraint foreign key: constraint in two tables

Example: Complete a program, a book belongs to only one person

CreateTable person (

PIDVARCHAR2 (18),

NAMEVARCHAR2 (+) NOT NULL,

Agenumber (3) NOT NULL,

Birthdaydate,

SEXVARCHAR2 (3) defult ' man ',

CONSTRAINTPERSON_PID_PK primary KEY (PID),

Constraintperson_name_uk Unique (name),

Constraintperson_age_ck check (age between O and 150),

Constraintperson_sex_ck Check (Sex in (' male ', ' female ', ' medium '))

);

CreateTable Book (

Bidnumber primary key NOT NULL,

BNAMEVARCHAR2 (20),

Bpricenumber (5,2),

PIDVARCHAR2 (18),

CONSTRAINTPERSON_BOOK_PID_FK foreign KEY (PID) references person (PID)

);

Insertinto person (pid,name,age,birthday,sex) VALUES (' 010101 ', ' Zhang

three ', 23,to_date (' 1992-2-19 ', ' yyyy-mm-dd '), ' female ');

Insertinto Book (Bid,dname,bprice,pid) VALUES (1, ' Java ', 89.9, ' 010101 ');

Considerations when using the primary - FOREIGN Key Association:

1. Theforeign key set in the child table must be a primary key in the parent table

2. Delete the child table before deleting the parent table.

Cascade Deletions can also be used in a primary-foreign key association, where data from a table can be deleted automatically, and its corresponding

Child table Records

Example:

CreateTable Book (

Bidnumber primary key NOT NULL,

BNAMEVARCHAR2 (20),

Bpricenumber (5,2),

PIDVARCHAR2 (18),

CONSTRAINTPERSON_BOOK_PID_FK foreign KEY (PID) references person PID

On Detele

Cascade

)

F, modify constraints

If a table is already established, you can add constraints to it, and you must unify the name of the constraint type:

--primarykey--> primary key field _PK

--unique--> Field _uk

--check--> Field _ck

--foreignkey--> Parent Field _ child field _FK

Example 1: Adding a constraint to a person

altertable person Add constraint PERSON_PID_PK primary key (PID);

Example 2: Deleting a PRIMARY KEY constraint in person

altertable person drop constraint person_pid_pk;

Third,rownum

RowNum: Represents the line number, which is actually a column name, which is generally referred to as pseudo-column

Example 1: Add rownum to the column name when querying the employee table

Select Rownum,empno,ename,job,sal,hiredate from EMP;

The rownum itself takes the form of automatic numbering.

Example 2: Querying the top 5 employee Information

Select Rownum,empno,ename,job,sal,hiredatefrom emp where rownum <= 5;

Example 3: Query out 6-10 employee Information

SELECT * FROM (select RowNum m,empno,ename,job,sal,hiredate from Empwhere

RowNum <=10) temp where temp.m >5;

Example 4: Query out the following 5 employee Information

SELECT * FROM (select RowNum m,empno,ename,job,sal,hiredate from Empwhere

RowNum <=15) temp where temp.m >10;

Iv. Collection Operations

three sets of operations: and (Union), intersection (intersect), difference (minus)

4.1union

combine the results of multiple queries into one query result with no duplicate content

4.2union All

combines multiple query results into one query, but contains duplicate values

4.3intersect

returns the same portion of multiple query results

4.4 Minus

returns the difference set of two query results

Example: Copy the EMP table and take all of the department's values out

CREATE TABLE EMP20 as SELECT * from emp where empno = 20;

1), verify Union

SELECT * FROM emp

Union

SELECT * from EMP20;

2),UNION ALL

SELECT * FROM emp

UNION ALL

SELECT * from EMP20;

3), verify intersect

SELECT * FROM emp

Intersect

SELECT * from EMP20;

4), verify minus

SELECT * FROM emp

Minus

SELECT * from EMP20;

Basic Operations for database tables