Basic SQL statement operations

-- Create a database
Create database Etp;

-- Connect to the database
Connect to Etp;

-- Disconnect
Disconnect Etp;

-- View tables in the current database
List tables;

-- Create a table
Create table studentInfo (
Stuno char (5) not null,
Stuname varchar (8 ),
Stubirth date
);

-- View table structure
Describe table studentinfo;

-- New table Field
Alter table studentinfo add stutel int;
Alter table studentinfo add abc int;

-- Modify the field type
Alter table studentinfo alter column stutel set data type char (11 );

-- Delete a field
Alter table studentinfo drop column abc;

-- Add a non-empty Constraint
Alter table studentinfo alter column stuname set not null;

 

-- Restructured the table
Reorg table studentinfo;

 

-- Adds a unique constraint.
Alter table studentinfo alter column stutel set not null;
Alter table studentinfo add constraint un_stutel unique (stutel );

 

-- Add check Constraints
Alter table studentinfo add column stuAge int;
Alter table studentinfo add constraint ch_stuAge check (stuAge> 0 and stuAge <150 );

 

-- Add primary key constraints
Alter table studentinfo add constraint pk_stuno primary key (stuno );

 

-- Delete a table
Drop table studentinfo;

 

-- Add constraint method 1 when creating a table
Create table studentinfo (
StuNo int not null,
StuName varchar (8) not null,
StuAge int,
StuTel char (8 ),
Constraint pk_stuNo primary key (stuNo ),
Constraint un_stuName unique (stuName ),
Constraint ch_stuAge check (stuAge> = 0 and stuAge <150)
);

 

-- Add constraints when creating a table 2
Create table studentinfo (
StuNo int not null primary key,
StuName varchar (8) not null unique,
StuAge int check (stuAge> = 0 and stuAge <150 ),
StuTel char (8)
);

 

-- Add a primary foreign key
-- Adds a class table.
Create table classInfo (
ClassId int not null primary key,
ClassName varchar (20)
);

 

-- Add Foreign keys when creating a table
Create table studentinfo (
StuNo int not null,
StuName varchar (8) not null,
StuBirth date not null,
StuAge int,
StuTel char (8 ),
FclassId int,
StuBirth date not null,
Constraint pk_stuNo primary key (stuNo ),
Constraint un_stuName unique (stuName ),
Constraint ch_stuAge check (stuAge> = 0 and stuAge <150 ),
Constraint fk_fcalssId foreign key (fclassid) references classInfo (classId)
);

-- Auto-Increment
Create table studentinfo (
StuNo int not null generated always as identity (start with 1, increment by 1 ),
StuName varchar (8) not null,
StuAge int,
StuTel char (8 ),
FclassId int,
StuBirth date not null,
Constraint pk_stuNo primary key (stuNo ),
Constraint un_stuName unique (stuName ),
Constraint ch_stuAge check (stuAge> = 0 and stuAge <150 ),
Constraint fk_fcalssId foreign key (fclassid) references classInfo (classId)
);

1 2 Next Page