Database-relational database standard language SQL

SQL (structured Query Language)

结构化查询语言，是关系数据库的标准语言

SQL is a universal, highly functional relational database language

SQL features

1. Integrated and unified
Set data definition language (DDL), Data Manipulation language (DML), Data Control Language (DCL) function in one.
All activities in the database life cycle can be completed independently:
Define the relationship pattern, insert the data, and set up the database;
Query and update the data in the database;
Database refactoring and Maintenance
Database security, integrity control, etc.
After the user database is put into operation, the mode can be modified at any time, without affecting the operation of the data.
Uniform Data operators

The data manipulation language of non-relational data model "process oriented", must establish access path
SQL does not need to know the access path as long as it proposes "what to do".
The selection of access paths and the operation of SQL are done automatically by the system.
The non-relational data model uses a record-oriented operation, and the object is a record
SQL takes the set operation mode

 操作对象、查找结果可以是元组的集合 一次插入、删除、更新操作的对象可以是元组的集合

SQL is a stand-alone language
The ability to use it independently for online interactions
SQL is also embedded language
SQL can be embedded in a high-level language (such as a C,c++,java) program for programmers to design programs using

Basic concepts of SQL

Basic table
Tables that exist independently of themselves
A relationship in SQL corresponds to a basic table
One (or more) base table corresponding to one storage file
A table can take several indexes
Storing files
The logical structure makes up the inner mode of the relational database
The physical structure is arbitrary and transparent to the user
View
Tables exported from one or several base tables
Only the definition of the view is stored in the database and not the corresponding data of the view
View is a virtual table
The user can then define the view on the view

Student-Curriculum Database design

学生-课程模式 S-T :        学生表：Student(Sno,Sname,Ssex,Sage,Sdept)    课程表：Course(Cno,Cname,Cpno,Ccredit)    学生选课表：SC(Sno,Cno,Grade)

School Number	name	Sex	Age	your Department
200215121	Tom	Man	21st	Cs
200215122	John doe	Woman	22	MA
200215123	Harry	Man	25	Ia

Defining patterns

 [例1]定义一个学生-课程模式S-T            CREATE SCHEMA “S-T” AUTHORIZATION WANG;             为用户WANG定义了一个模式S-T    [例2]CREATE SCHEMA AUTHORIZATION WANG；            <模式名>隐含为用户名WANG

Defining a schema actually defines a namespace
In this space, you can define the database objects that the schema contains, such as basic tables, views, indexes, and so on.
Create table,create View and grant clauses can be accepted in the Create schema.
CREATE Schema < schema name > AUTHORIZATION < user name >[< table definition clause >|< VIEW definition clause >|< authorization definition clause;]

[example 3] CREATE  schema  TEST authorization  ZHANG Span class= "Hljs-keyword" >create  table  TAB1 (COL1 smallint , COL2 in T , COL3 char  (20 ), COL4 numeric  (10 , 3 ), COL5 decimal  (5 , 2 ) ) Creates a schema test for user Zhang and defines a table TAB1 in it. 
 

Delete mode

DROP Schema (schema name) (cascade| RESTRICT)
CASCADE (Cascade)
Delete all the database objects in the schema while deleting the schema
RESTRICT (limit)
If the subordinate's database object (such as a table, view, and so on) is defined in the pattern, the execution of the DELETE statement is rejected.
Executes when there are no subordinate objects in the pattern.

[例4]  DROP SCHEMA ZHANG CASCADE；         删除模式ZHANG         同时该模式中定义的表TAB1也被删除

Definition, deletion and modification of basic tables

First, define the basic table

<表名>      （<列名><数据类型><列级完整性约束条件> ]      [，<列名><数据类型><列级完整性约束条件>] ] …      [，<表级完整性约束条件> ] ）；

If the integrity constraint involves more than one property column for the table, it must be defined at the table level, otherwise it can be defined at the column level or at the table level.

[Example 5] establish the "Student" table student, the school number is the main code, the name is the only value. create  TABLE  Student (Sno char  (9 ) primary  key ,/* column-level integrity constraints */Sname 
     
      char 
      (20 ) unique             ,/* sname takes a unique value */Ssex char  (2 ), Sage smallint , sdept char  (20 ));   

 [example 6] Create a "course" Table course create  table  Course (Cno char  (4 ) primary  key , Cname char  (40), cpno char  (4 ), Ccredit smallint , 
     
      foreign 
      key  (cpno)  REFERENCES  Course (Cno));  

[example 7] set up a "Student elective" table SC create  Span class= "Hljs-keyword" >table  SC (Sno char  (9 ), Cno char  (4 ), Grade smallint , primary  KEY  (SNO,CNO),/* Main code consists of two attributes and must be defined as table-level integrity */foreign  key  (Sno) references  Student (Sno),/* table-level Integrity constraints, SNO is a foreign code, the referenced table is student */foreign  key  (Cn O) references  Course (Cno)/* table-level integrity constraints, Cno is external code, referenced table is course*/);  

Data type

The concept of domain in SQL is implemented with data types
You need to specify the data type and length of the table's properties when you define it
What type of data to choose
Range of values
Which operations to do

Patterns and tables

Each basic table belongs to a pattern
A pattern contains multiple base tables
Define the schema to which the base table belongs
Method One: The schema name is clearly given in the table name
Create table "S-t". Student (...) ）; / mode is named S-t/
Create table "S-t". Cource (...) ）;
Create table "S-t". SC (...) ）;
Method Two: Create a table at the same time in the CREATE SCHEMA statement
Method Three: Set the owning mode
When creating a base table (same as other database objects), if no schema is specified, the system determines the mode to which the object belongs based on the search path
The RDBMS uses the first existing pattern in the pattern list as the schema name for the database object
If the schema name in the search path does not exist, the system will give an error
Displays the current search path: show Search_path;
The current default value for the search path is: $user, public

Database-relational database standard language SQL