Shulou(Shulou.com)06/01 Report--

First of all, we introduce how to create, delete and modify relational tables by commands.

(1)Create a 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, note that they should be separated by commas in English)

, table-level integrity constraints (also separated by commas between table-level and column-level integrity constraints)

)

Column-level integrity constraints include: not null limit column value is null default default value of specified column unique definition column value cannot be repeated check definition column value range (check is a value constraint defined between multiple columns, and can only be defined at the table level integrity constraint) define primary key definition primary code constraint foreign key definition external code constraint table level integrity constraint: unique definition column value cannot be repeated check definition column value range (check is the value constraint between defined columns, and can only be defined at table level integrity constraint) primary key definition primary code constraint foreign key definition external code constraint

Knowing what constraints there are, how do you define integrity constraints? What is their format?

Primary key constraint: primary key (column name, column name, column name...)

Foreign key constraints: foreign key (column name) references appearance name (appearance column name)

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

default constraint: column name default default

check constraint: check logical expression (logical expression cannot contain columns from multiple tables)

(2), modification table:

Basic format: alter table name

alter column name new data type (modify column definition)

add column name data type constraint (add new column)

drop column name

add constraint definition

drop constraint name

Note: Different DBMS alter table statements have different formats. Take SQL2014 as an example.

(3)Deletion table:

Basic format: drop table table name

Note: If there are other tables in the deleted table that reference constraints on its external code, you must delete the table where the external code is located first, and then delete the table where the quoted code is located.

Example Description: (1) Create 3 tables, student table (student), course table (course) and student course table (sc)

student table

column name meaning data type constraint Sno student number char(11) primary code Sname name nchar(4) non-null Snumber×× sign char(18) value not heavy Ssex gender nchar(1) default value is 'male'Sage age tinyint value range is 15-45Sdept system nvarchar(20)

Create student table code:

use student

create table student

(

Sno char(11) primary key,

Sname nchar(4) not null,

Snumber char(18) unique,

Ssex nchar(1) default 'male',

Sage tinyint check(sage >= 15 and sage 0),

Semeter tinyint

)

Course selection table (sc)

column name meaning data type constraint Sno student number char(11) primary code, reference student's external code Cno course number char(6) primary code, reference course's external code Grade grade tinyint

Create sc table code:

use student

create table sc

(

Sno char(11) 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)Add the column 'Course type'(type) to sc table. It can be blank. The code is as follows:

alter table sc

add type nchar(1) null

(3)Add the column 'Course type'(type) to sc table and modify its data type to nchar(2), code as follows:

alter table sc

alter column type nchar(2)

(4)Add a constraint with a value range of {compulsory, re-study, elective} to the sc table in the column of 'course type'(type). The code is as follows:

alter table sc

add check(type in ('compulsory ',' review','elective'))

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

alter table sc

drop column type

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.