Table operations in SQL statements

The main part of the database is a different table, and some operations on the table are provided in the SQL statement. The operations we are discussing are in SQL service.

First, the new table:

The new table has the syntax format: CREATE TABLE table name (column name data type [integrity constraint], column name data type [integrity constraint],...); Where the integrity constraints can be added or not, if not added can also be added after the table definition is complete, such as we now need to create a student table, you can use such code to create:

Create Table Char (PRIMARYKEY,     char(    ),Char (2UNIQUE,    int,    Char(20 )    );

PRIMARY key indicates that the SNO is the main code for this table;

In addition to this form of constraints, there is a table-level constraint, the table-level constraints are generally placed after all the properties of the table is defined, when a property is a foreign code or a main property consists of multiple attributes, the format of the code definition is generally: FOREIGN KEY (as an attribute of the outer code) referenced by the table ( Referenced property name), the example of the outer code is as follows:

 reate  table  Course (Cno char  (primary  key  , Cname  char  (20   char  (4 ), Span style= "color: #008080;" >--  Note that the data type is the same as the referenced data type  ccredit   foreign  key  (cpno) references  Course (Cno) -- Cpno refers to CNO  
 in this table); 

When there are multiple attributes in the main code, the main code is defined in the following format: PRIMARY KEY (Attribute column, property column,... ）

For example, now we have a score table, the table of the course number and the main code, and the study number quoted in the Student table, the course number refers to the course number in the curriculum:

CREATE TABLESC (SnoChar(Ten), CnoChar(4), Gradesmallint,    PRIMARY KEY(Sno, Cno),--table-level constraints must be used when there are multiple master codes in the table    FOREIGN KEY(Sno)REFERENCESStudent (Sno),FOREIGN KEY(Cno)REFERENCESCourse (Cno));

Where the range of values for each property is called a field, we can define the domain of the attribute according to the actual situation, choose the appropriate data type according to the domain, the following table shows the main data types:

Data type	Meaning
CHAR (N)	A string of length n, which occupies a fixed memory, but only shows the number of characters stored by the user
VARCHAR (N)	A string with a maximum length of n, in which memory is determined based on the length of the user-defined string
Int	Integral type
SMALLINT	Short-integer
Number (P, D)	Decimals, the total number of digits is P (including fractional parts but not decimal points), and the number of decimal places is D
REAL	Floating-point number, depending on the machine's floating-point accuracy
Double Precision	Dual-progress floating-point numbers also depend on the number of machine bits
FLOAT (N)	Floating-point number is at least n
DATA	Date, including year, month, and day. Format is YYYY-MM-DD
Time	Time, including hours, minutes, seconds. Format is HH:MM:SS

Ii. changes to the table definition:

When we have defined a table, we may need to change the definition of the table according to the specific requirements, SQL also provides related operations, SQL is generally modified with the ALTER TABLE statement, the format is as follows:

ALTER Table Table Name

[Add new column name data type [integrity constraint]]//NEW column

[Drop < integrity constraint >]//Delete an integrity constraint condition

[ALTER column < new column name >< data type;] Modify column

[Drop Column < column name >]//Delete a property

These statements can only modify the table structure, cannot modify the data in the table, such as the school number 00001, when we change the column name of the school number to the student ID number, this 00001 will not change.

Here are a few small examples:

ALTER TABLE Add Sentrance DATE; -- an option to add an entry time to the student table ALTER TABLE ADD UNIQUE (Cname); -- Add an integrity constraint to the curriculum ALTER TABLE DROP COLUMN Sentrance; -- Delete the admission time option in the student table

Third, delete the table:

Delete Table used statement for drop table name [RESTRICT | CASCADE]

When the Restrict option is selected, deleting a table is conditional, the deleted table cannot be referenced by another table constraint (CHECK FOREIGN key, etc.), cannot have views, indexes, triggers, stored procedures

When choosing cascade, these restrictions will be removed as they are not concerned.

Table operations in SQL statements