SQL language basics and application examples

SQL language basics and application examples

Database

I.SQL language Overview

SQL (Structured Query Language) is a relational database language used to establish, store, modify, retrieve, and manage data in a relational database.

Relationship between PL/SQL and SQL:

L PL/SQL is an extension of Oracle's relational database language SQL. It combines the SQL data manipulation function with the procedural language data processing function.

L PL/SQL is an important development tool for Oracle database applications.

II.Composition of SQL language

Data Definition Language DDL: CREATE, ALTER, DROP

Data manipulation language DML: INSERT, DELETE, UPDATE, SELECT

Transaction control statement TCL: COMMIT, ROLLBACK, SAVEPOINT (partial ROLLBACK)

Data Control Language DCL: GRANT, REVOKE

III.Data Type

Common data types:

1. String

CHAR fixed change default 1 byte, up to 2000 bytes

VARCHAR2 Variable Length: 0 byte by default, maximum: 4000 bytes

NVARCHAR2 Variable Length 0 characters by default, maximum 4000 bytes

Differences between VARCHAR2 and NVARCHAR2:

1. NVARCHAR2 has better performance;

2. varchar2 (number of bytes), nvarchar2 (number of characters)

LONG variable length up to 2 GB

2. Value Type

NUMBER (p, s) 1-22 bytes

Example:

NUMBER is an integer by default.

NUMBER (5) is an integer of 5, for example, 12345.

NUMBER (5, 1) is 5 in length and has 1 decimal place, for example: 1234.5

3. date type

DATE: Save DATE and Time

In addition to saving the date and time, TIMESTAMP can also save decimal seconds. the decimal point can be specified as 0-9. The default value is 6 digits.

Create table tb_date (

D1 DATE,

D2 TIMESTAMP (9)

);

Insert Date data

Method 1: insert into tb_date (D1, D2)

VALUES (DATE '2014-2-16 ', TIMESTAMP '2014-2-16 13:24:52. 123 ');

Method 2: insert into tb_date (D1, D2)

VALUES (TO_DATE ('2017-2-16 10:20:30 ', 'yyyy-MM-DD HH24: MI: ss'), TO_TIMESTAMP ('2017-2-16 13:24:52. 123456 ', 'yyyy-MM-DD HH24: MI: SS. ff6 '));

4.LOB large object type

BLOB stores binary objects (images, videos, etc)

CLOB stores large objects in character format

Tip: In application development, we store images, videos, music, and other addresses (strings, for example, E: \ abc \ 110.jpg) in the database table, find the corresponding image, video, and music based on the address.

5. Data Integrity (constraints)

Data integrity requires that the data in the database be accurate. Accuracy is achieved through the design and constraints of database tables.

To achieve data integrity, the database needs to do the following:

First, ensure that the data in each row meets the requirements.

Second, ensure that the data in each column meets the requirements.

Five constraints:

Primary key constraint: primary key foreign key constraint: foreign key unique constraint: unique

Null Value constraint: null check constraint: check default constraint: default

4. Create a table structure

Create table Name (

Column Name Data Type integrity constraints,

......

);

Case: column-Level Definition

Create table t_class (

Class_id number constraint pk_class_id primary key, -- add a primary key constraint for class_id

Cname VARCHAR2 (20) CONSTRAINT uq_cname UNIQUE -- add a unique constraint for cname

)

Case: Table-Level Definition

Create table tb_student (

Stu_id NUMBER (5 ),

Stu_name VARCHAR2 (10) not null, -- NULL Constraint

U_sex VARCHAR2 (6 ),

Bitrhday date default sysdate, -- DEFAULT constraint: system time)

Class_id NUMBER (3 ),

CONSTRAINT pk_stu_id primary key (stu_id), -- PRIMARY KEY CONSTRAINT

CONSTRAINT fk_class_id foreign key (class_id) REFERENCES t_class (class_id), -- FOREIGN KEY CONSTRAINT

CONSTRAINT sex_chk CHECK (sex IN ('male', 'female ') -- CHECK Constraints

);

Note: The null and default constraints, both column-level and table-level constraints, are placed behind the column, primary key constraints, foreign key constraints, and unique constraints, check constraints can be defined at the column level or at the table level.

I, Modify Table Structure1. Modify Constraints

Alter table name add constraint name Constraints

Case:

Alter table t_student

Add constraint uq_username UNIQUE (stuname) -- create a UNIQUE CONSTRAINT

Add constraint pk_uid primary key (stuID) -- create a PRIMARY KEY CONSTRAINT

Add constraint fk_cid foreign key (cid) REFERENCES t_class (cid) -- create a FOREIGN KEY CONSTRAINT

Add constraint ck_age CHECK (age> 0 AND age <150) -- create CHECK Constraints

---- Default value Constraints

Alter table tb_student

MODIFY birthday DEFAULT SYSDATE

2. Delete Constraints

Delete the pk_uid constraint in the T_STUDENT table.

Alter table t_student

Drop constraint pk_uid

3. View Constraints

View All constraints in the T_STUDENT table. Note:The table name must be in uppercase.

SELECT * FROM user_constraints

WHERE table_name = 't_ STUDENT'

4. Use the alter table command to add new fields

Alter table <TABLE Name>

ADD column name type constraints

5. Use the alter table command to delete Fields

Alter table <TABLE Name>

Drop column name

 

6. Use the alter table command to modify Fields

Alter table <TABLE Name>

MODIFY column name type (modified) Constraint (modified)

Modify the field name in oracle,

Alter table [table name] rename column old field name to new field name;

 

7. Rename the basic table

RENAME <original table name> TO <target table name>

Or:

Alter table <original TABLE name>

Rename to <target table name>

 

8.Append Constraint

Primary key constraint for appending a table:

Alter table <TABLE Name>

Add constraint <primary key name> primary key (column name)

 

Unique constraint for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name> UNIQUE (column name)

 

Check constraints for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name> CHECK (expression)

 

The foreign key constraint for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name>

Foreign key (column name) REFERENCES main table name (column name)

II, Delete table

Drop table Name

Truncate table name truncate

Note: After the command is executed, the target table is removed from the database to the recycle bin,

Indexes Based on the table, triggers and other data objects are also deleted.

III, Copy a table1. Copy table structure and data

-- Insert the data in the emp table to the tb_new table (the tb_new table must be created first, and the table structure is the same as the SELECT result set)

Insert into tb_new

SELECT * FROM emp;

-- Copy an emp table

Create table tb_new

As select * FROM emp;

-- Copy the structure of the emp table

Create table tb_new

As select * FROM emp WHERE 1 = 2;

2. Copy table structure

Create table tb_new

As select * FROM emp WHERE 1 = 2;

IV, Set multiple users to access the same table

Create a table and use the Administrator to grant access permissions to multiple users.

Database

I.SQL language Overview

SQL (Structured Query Language) is a relational database language used to establish, store, modify, retrieve, and manage data in a relational database.

Relationship between PL/SQL and SQL:

L PL/SQL is an extension of Oracle's relational database language SQL. It combines the SQL data manipulation function with the procedural language data processing function.

L PL/SQL is an important development tool for Oracle database applications.

II.Composition of SQL language

Data Definition Language DDL: CREATE, ALTER, DROP

Data manipulation language DML: INSERT, DELETE, UPDATE, SELECT

Transaction control statement TCL: COMMIT, ROLLBACK, SAVEPOINT (partial ROLLBACK)

Data Control Language DCL: GRANT, REVOKE

III.Data Type

Common data types:

1. String

CHAR fixed change default 1 byte, up to 2000 bytes

VARCHAR2 Variable Length: 0 byte by default, maximum: 4000 bytes

NVARCHAR2 Variable Length 0 characters by default, maximum 4000 bytes

Differences between VARCHAR2 and NVARCHAR2:

1. NVARCHAR2 has better performance;

2. varchar2 (number of bytes), nvarchar2 (number of characters)

LONG variable length up to 2 GB

2. Value Type

NUMBER (p, s) 1-22 bytes

Example:

NUMBER is an integer by default.

NUMBER (5) is an integer of 5, for example, 12345.

NUMBER (5, 1) is 5 in length and has 1 decimal place, for example: 1234.5

3. date type

DATE: Save DATE and Time

In addition to saving the date and time, TIMESTAMP can also save decimal seconds. the decimal point can be specified as 0-9. The default value is 6 digits.

Create table tb_date (

D1 DATE,

D2 TIMESTAMP (9)

);

Insert Date data

Method 1: insert into tb_date (D1, D2)

VALUES (DATE '2014-2-16 ', TIMESTAMP '2014-2-16 13:24:52. 123 ');

Method 2: insert into tb_date (D1, D2)

VALUES (TO_DATE ('2017-2-16 10:20:30 ', 'yyyy-MM-DD HH24: MI: ss'), TO_TIMESTAMP ('2017-2-16 13:24:52. 123456 ', 'yyyy-MM-DD HH24: MI: SS. ff6 '));

4.LOB large object type

BLOB stores binary objects (images, videos, etc)

CLOB stores large objects in character format

Tip: In application development, we store images, videos, music, and other addresses (strings, for example, E: \ abc \ 110.jpg) in the database table, find the corresponding image, video, and music based on the address.

5. Data Integrity (constraints)

Data integrity requires that the data in the database be accurate. Accuracy is achieved through the design and constraints of database tables.

To achieve data integrity, the database needs to do the following:

First, ensure that the data in each row meets the requirements.

Second, ensure that the data in each column meets the requirements.

Five constraints:

Primary key constraint: primary key foreign key constraint: foreign key unique constraint: unique

Null Value constraint: null check constraint: check default constraint: default

4. Create a table structure

Create table Name (

Column Name Data Type integrity constraints,

......

);

Case: column-Level Definition

Create table t_class (

Class_id number constraint pk_class_id primary key, -- add a primary key constraint for class_id

Cname VARCHAR2 (20) CONSTRAINT uq_cname UNIQUE -- add a unique constraint for cname

)

Case: Table-Level Definition

Create table tb_student (

Stu_id NUMBER (5 ),

Stu_name VARCHAR2 (10) not null, -- NULL Constraint

U_sex VARCHAR2 (6 ),

Bitrhday date default sysdate, -- DEFAULT constraint: system time)

Class_id NUMBER (3 ),

CONSTRAINT pk_stu_id primary key (stu_id), -- PRIMARY KEY CONSTRAINT

CONSTRAINT fk_class_id foreign key (class_id) REFERENCES t_class (class_id), -- FOREIGN KEY CONSTRAINT

CONSTRAINT sex_chk CHECK (sex IN ('male', 'female ') -- CHECK Constraints

);

Note: The null and default constraints, both column-level and table-level constraints, are placed behind the column, primary key constraints, foreign key constraints, and unique constraints, check constraints can be defined at the column level or at the table level.

I, Modify Table Structure1. Modify Constraints

Alter table name add constraint name Constraints

Case:

Alter table t_student

Add constraint uq_username UNIQUE (stuname) -- create a UNIQUE CONSTRAINT

Add constraint pk_uid primary key (stuID) -- create a PRIMARY KEY CONSTRAINT

Add constraint fk_cid foreign key (cid) REFERENCES t_class (cid) -- create a FOREIGN KEY CONSTRAINT

Add constraint ck_age CHECK (age> 0 AND age <150) -- create CHECK Constraints

---- Default value Constraints

Alter table tb_student

MODIFY birthday DEFAULT SYSDATE

2. Delete Constraints

Delete the pk_uid constraint in the T_STUDENT table.

Alter table t_student

Drop constraint pk_uid

3. View Constraints

View All constraints in the T_STUDENT table. Note:The table name must be in uppercase.

SELECT * FROM user_constraints

WHERE table_name = 't_ STUDENT'

4. Use the alter table command to add new fields

Alter table <TABLE Name>

ADD column name type constraints

5. Use the alter table command to delete Fields

Alter table <TABLE Name>

Drop column name

 

6. Use the alter table command to modify Fields

Alter table <TABLE Name>

MODIFY column name type (modified) Constraint (modified)

Modify the field name in oracle,

Alter table [table name] rename column old field name to new field name;

 

7. Rename the basic table

RENAME <original table name> TO <target table name>

Or:

Alter table <original TABLE name>

Rename to <target table name>

 

8.Append Constraint

Primary key constraint for appending a table:

Alter table <TABLE Name>

Add constraint <primary key name> primary key (column name)

 

Unique constraint for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name> UNIQUE (column name)

 

Check constraints for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name> CHECK (expression)

 

The foreign key constraint for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name>

Foreign key (column name) REFERENCES main table name (column name)

II, Delete table

Drop table Name

Truncate table name truncate

Note: After the command is executed, the target table is removed from the database to the recycle bin,

Indexes Based on the table, triggers and other data objects are also deleted.

III, Copy a table1. Copy table structure and data

-- Insert the data in the emp table to the tb_new table (the tb_new table must be created first, and the table structure is the same as the SELECT result set)

Insert into tb_new

SELECT * FROM emp;

-- Copy an emp table

Create table tb_new

As select * FROM emp;

-- Copy the structure of the emp table

Create table tb_new

As select * FROM emp WHERE 1 = 2;

2. Copy table structure

Create table tb_new

As select * FROM emp WHERE 1 = 2;

IV, Set multiple users to access the same table

Create a table and use the Administrator to grant access permissions to multiple users.

Database

I.SQL language Overview

SQL (Structured Query Language) is a relational database language used to establish, store, modify, retrieve, and manage data in a relational database.

Relationship between PL/SQL and SQL:

L PL/SQL is an extension of Oracle's relational database language SQL. It combines the SQL data manipulation function with the procedural language data processing function.

L PL/SQL is an important development tool for Oracle database applications.

II.Composition of SQL language

Data Definition Language DDL: CREATE, ALTER, DROP

Data manipulation language DML: INSERT, DELETE, UPDATE, SELECT

Transaction control statement TCL: COMMIT, ROLLBACK, SAVEPOINT (partial ROLLBACK)

Data Control Language DCL: GRANT, REVOKE

III.Data Type

Common data types:

1. String

CHAR fixed change default 1 byte, up to 2000 bytes

VARCHAR2 Variable Length: 0 byte by default, maximum: 4000 bytes

NVARCHAR2 Variable Length 0 characters by default, maximum 4000 bytes

Differences between VARCHAR2 and NVARCHAR2:

1. NVARCHAR2 has better performance;

2. varchar2 (number of bytes), nvarchar2 (number of characters)

LONG variable length up to 2 GB

2. Value Type

NUMBER (p, s) 1-22 bytes

Example:

NUMBER is an integer by default.

NUMBER (5) is an integer of 5, for example, 12345.

NUMBER (5, 1) is 5 in length and has 1 decimal place, for example: 1234.5

3. date type

DATE: Save DATE and Time

In addition to saving the date and time, TIMESTAMP can also save decimal seconds. the decimal point can be specified as 0-9. The default value is 6 digits.

Create table tb_date (

D1 DATE,

D2 TIMESTAMP (9)

);

Insert Date data

Method 1: insert into tb_date (D1, D2)

VALUES (DATE '2014-2-16 ', TIMESTAMP '2014-2-16 13:24:52. 123 ');

Method 2: insert into tb_date (D1, D2)

VALUES (TO_DATE ('2017-2-16 10:20:30 ', 'yyyy-MM-DD HH24: MI: ss'), TO_TIMESTAMP ('2017-2-16 13:24:52. 123456 ', 'yyyy-MM-DD HH24: MI: SS. ff6 '));

4.LOB large object type

BLOB stores binary objects (images, videos, etc)

CLOB stores large objects in character format

Tip: In application development, we store images, videos, music, and other addresses (strings, for example, E: \ abc \ 110.jpg) in the database table, find the corresponding image, video, and music based on the address.

5. Data Integrity (constraints)

Data integrity requires that the data in the database be accurate. Accuracy is achieved through the design and constraints of database tables.

To achieve data integrity, the database needs to do the following:

First, ensure that the data in each row meets the requirements.

Second, ensure that the data in each column meets the requirements.

Five constraints:

Primary key constraint: primary key foreign key constraint: foreign key unique constraint: unique

Null Value constraint: null check constraint: check default constraint: default

4. Create a table structure

Create table Name (

Column Name Data Type integrity constraints,

......

);

Case: column-Level Definition

Create table t_class (

Class_id number constraint pk_class_id primary key, -- add a primary key constraint for class_id

Cname VARCHAR2 (20) CONSTRAINT uq_cname UNIQUE -- add a unique constraint for cname

)

Case: Table-Level Definition

Create table tb_student (

Stu_id NUMBER (5 ),

Stu_name VARCHAR2 (10) not null, -- NULL Constraint

U_sex VARCHAR2 (6 ),

Bitrhday date default sysdate, -- DEFAULT constraint: system time)

Class_id NUMBER (3 ),

CONSTRAINT pk_stu_id primary key (stu_id), -- PRIMARY KEY CONSTRAINT

CONSTRAINT fk_class_id foreign key (class_id) REFERENCES t_class (class_id), -- FOREIGN KEY CONSTRAINT

CONSTRAINT sex_chk CHECK (sex IN ('male', 'female ') -- CHECK Constraints

);

Note: The null and default constraints, both column-level and table-level constraints, are placed behind the column, primary key constraints, foreign key constraints, and unique constraints, check constraints can be defined at the column level or at the table level.

I, Modify Table Structure1. Modify Constraints

Alter table name add constraint name Constraints

Case:

Alter table t_student

Add constraint uq_username UNIQUE (stuname) -- create a UNIQUE CONSTRAINT

Add constraint pk_uid primary key (stuID) -- create a PRIMARY KEY CONSTRAINT

Add constraint fk_cid foreign key (cid) REFERENCES t_class (cid) -- create a FOREIGN KEY CONSTRAINT

Add constraint ck_age CHECK (age> 0 AND age <150) -- create CHECK Constraints

---- Default value Constraints

Alter table tb_student

MODIFY birthday DEFAULT SYSDATE

2. Delete Constraints

Delete the pk_uid constraint in the T_STUDENT table.

Alter table t_student

Drop constraint pk_uid

3. View Constraints

View All constraints in the T_STUDENT table. Note:The table name must be in uppercase.

SELECT * FROM user_constraints

WHERE table_name = 't_ STUDENT'

4. Use the alter table command to add new fields

Alter table <TABLE Name>

ADD column name type constraints

5. Use the alter table command to delete Fields

Alter table <TABLE Name>

Drop column name

 

6. Use the alter table command to modify Fields

Alter table <TABLE Name>

MODIFY column name type (modified) Constraint (modified)

Modify the field name in oracle,

Alter table [table name] rename column old field name to new field name;

 

7. Rename the basic table

RENAME <original table name> TO <target table name>

Or:

Alter table <original TABLE name>

Rename to <target table name>

 

8.Append Constraint

Primary key constraint for appending a table:

Alter table <TABLE Name>

Add constraint <primary key name> primary key (column name)

 

Unique constraint for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name> UNIQUE (column name)

 

Check constraints for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name> CHECK (expression)

 

The foreign key constraint for appending a table:

Alter table <TABLE Name>

Add constraint <CONSTRAINT name>

Foreign key (column name) REFERENCES main table name (column name)

II, Delete table

Drop table Name

Truncate table name truncate

Note: After the command is executed, the target table is removed from the database to the recycle bin,

Indexes Based on the table, triggers and other data objects are also deleted.

III, Copy a table1. Copy table structure and data

-- Insert the data in the emp table to the tb_new table (the tb_new table must be created first, and the table structure is the same as the SELECT result set)

Insert into tb_new

SELECT * FROM emp;

-- Copy an emp table

Create table tb_new

As select * FROM emp;

-- Copy the structure of the emp table

Create table tb_new

As select * FROM emp WHERE 1 = 2;

2. Copy table structure

Create table tb_new

As select * FROM emp WHERE 1 = 2;

IV, Set multiple users to access the same table

Create a table and use the Administrator to grant access permissions to multiple users.