SQL learning using command mode to create, delete, and modify relational tables

First, we'll start by creating, deleting, and modifying relational tables in a command way.

(1), CREATE table

Basic format:create Table table name

(

Column-Name data type column-level integrity constraints, column-name data-type column-level integrity constraints (if there are multiple columns, be careful to separate the English-style commas)

, table-Level integrity constraints (also comma-delimited between table-level integrity constraints and column-level integrity constraints)

)

Column-Level integrity constraints are:
NOT NULL	Limit column values to null
Default	Specify default values for columns
Unique	Define column values cannot be duplicated
Check	Defines the range of values for a column (check is a value constraint between defined multiple columns and can only be defined at a table-level integrity constraint)
Primary key	Defining a Master Code constraint
FOREIGN key	Defining External code constraints

Table-Level integrity constraints:
Unique	Define column values cannot be duplicated
Check	Defines the range of values for a column (check is a value constraint between defined multiple columns and can only be defined at a table-level integrity constraint)
Primary key	Defining a Master Code constraint
FOREIGN key	Defining External code constraints

You know what the constraints are, so how do you define integrity constraints? What are their formats?

Main code constraint: PRIMARY key (column name, column name, column name ...)

External code constraint: FOREIGN key (column name) references appearance name (outer column name)

Unique constraint: Column name data type unique (column name, column name ...)

Default constraint: Column name defaults

CHECK constraint: Check logical expression (cannot contain columns from multiple tables in a logical expression)

(2), modify the table:

Basic format: alter table name

ALTER COLUMN name new data type (to modify the definition of a column)

Add column name data type constraint (add new column)

Drop Column name (delete column)

Add constraint definition (adding constraints)

Drop constraint name (delete constraint)

NOTE: The ALTER TABLE statement format is different for different DBMS, where SQL2014 is the example

(3), delete the table:

Basic format: drop table name

Note: If there are other tables in the deleted table that have constraints on his foreign code reference, you must first delete the table that contains the outer code, and then delete the table that contains the referenced code

Example Description: (1) Create 3 tables, student table (student), Course table (course) and student selection timetable (SC)

Student Table (Student)

Column Name	Meaning	Data type	Constraints
Sno	School Number	CHAR (11)	Main code
Sname	Name	NCHAR (4)	Non-empty
Snumber	ID number	CHAR (18)	Value is not heavy
Ssex	Gender	NCHAR (1)	The default value is ' Male '
Sage	Age	tinyint	Value range is 15-45
Sdept	Your department	nvarchar (20)

To create the student table code:

Use student

CREATE TABLE Student

(

Sno Char (one) primary key,

Sname nchar (4) NOT NULL,

Snumber char (+) Unique,

Ssex nchar (1) Default ' male ',

Sage tinyint Check (Sage >= and Sage <= 45),

Sdept nvarchar (20)

)

Timetable (course)

			constraints
cno	Course number	char (6)	main code
cname	course name	n varchar	non-empty
credit	credits	numeric (3,1)
semester	semester	tinyint

To create the course table code:

Use student

CREATE TABLE Course

(

Cno Char (6) Primary key,

Cname nvarchar () NOT NULL,

Credit numeric (3,1) check (Credits > 0),

Semeter tinyint

)

Selected Timetable (SC)

Column Name	Meaning	Data type	Constraints
Sno	School Number	CHAR (11)	Main code, referring to the external code of Student
Cno	Course Number	CHAR (6)	Main code, referring to the external code of course
Grade	Results	tinyint

To create the SC table code:

Use student

CREATE TABLE SC

(

Sno Char (one) is not NULL,

Cno Char (6) NOT NULL,

Grade tinyint,

Primary KEY (SNO,CNO),

Foreign KEY (SNO) references student (SNO),

Foreign KEY (CNO) References course (CNO)

)

(2) To add the ' Class class ' (type) column for the SC table, which is allowed to be empty, the code is as follows:

ALTER TABLE SC

Add type NCHAR (1) NULL

(3) To add the ' Class of Classes ' (type) column to the SC table modify his data type to nchar (2) and the code is as follows:

ALTER TABLE SC

ALTER COLUMN type NCHAR (2)

(4) Add the ' class ' (type) column to the SC table to add a value range of {required, rebuilt, elective} constraints, the code is as follows:

ALTER TABLE SC

Add check (type in (' Compulsory ', ' rebuilt ', ' elective '))

(5) Delete the Type column with the following code:

ALTER TABLE SC

Drop Column Type

This article from "A Growing small Tree" blog, reproduced please contact the author!

SQL learning using command mode to create, delete, and modify relational tables