Operation of SQL Basic statement in DB2

Completely reprinted from: http://blog.sina.com.cn/s/blog_67aaf4440100v01p.html

--Create a database
Create Database ETP;

--Connect to database
Connect to ETP;

--Disconnect
Disconnect ETP;

--See what tables are in the current database
List tables;

--Build a table
CREATE TABLE Studentinfo (
Stuno char (5) NOT NULL,
Stuname varchar (8),
Stubirth Date
);

--View table structure
Describe table studentinfo;

--Add 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 Typechar (11);

--delete Field
ALTER TABLE studentinfo drop column ABC;

--Add a non-null constraint
ALTER TABLE studentinfo ALTER COLUMN stuname set NOT NULL;

--Refactoring tables
reorg table Studentinfo;

--Add a UNIQUE constraint
ALTER TABLE studentinfo ALTER COLUMN Stutel set NOT NULL;
ALTER TABLE STUDENTINFO add constraint Un_stutelunique (Stutel);

--Adding CHECK constraints
ALTER TABLE studentinfo add column stuage int;
ALTER TABLE studentinfo add constraint ch_stuage check (stuage> 0 and Stuage <150);

--Add a PRIMARY KEY constraint
ALTER TABLE STUDENTINFO add constraint Pk_stuno PrimaryKey (Stuno);

--Delete Table
drop table studentinfo;

--Adding a constraint when creating a table 1
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)
);

--Adding a constraint 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 andstuage <150),
Stutel Char (8)
);

--Add primary foreign key
--New Class table
CREATE TABLE ClassInfo (
ClassId int NOT null primary key,
ClassName varchar (20)
);

-Add foreign key
CREATE table Studentinfo (
 stuno int not null for table,
 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)
);

--Self-increment
CREATE TABLE Studentinfo (
Stuno int NOT NULL generated all asidentity (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)
);

--First build the table and add the foreign key
ALTER TABLE STUDENTINFO add constraint fk_classid ForeignKey (fclassid) references ClassInfo (classId);

--Query the constraint name from the system table
Select Constname, TabName, Refkeyname, Reftabname, Colcount,deleterule, updaterule from Syscat.references;

--Insert
INSERT into ClassInfo values (1, ' ETP-1 ');
INSERT into Studentinfovalues (1, ' xy ', 20, ' 12345 ', 1, ' 1990-02-28 ');

--Not all inserts need to write the column name
Insert into Studentinfo (Stuno,stuname,stutel) VALUES (2, ' WJ ', ' 111 ');

--have self-growing columns to write a clear list of names
Insert into Studentinfo (Stuname,stuage,stutel,fclassid,stubirth) VALUES (' xy ', 20, ' 12345 ', 1, ' 1990-02-28 ');
Insert into Studentinfo (Stuname,stuage,stutel,fclassid,stubirth) VALUES (' Tom ', 22, ' 12345 ', 2, ' 1990-02-28 ');

--Update
Update studentinfo Set Stubirth = ' 1990-02-21 ' wherestuname= ' xy ';
Update studentinfo Set Stubirth = ' 1990-02-21 ', stuage = Wherestuname= ' xy ';

--Delete
deleted from Studentinfo where stuname= ' xy ';

--Query
SELECT * from studentinfo where stuname= ' xy ';
Select Stuname,stuage from Studentinfo;

--alias Query
Select Stuname as name, stuage as age from Studentinfo;
Select S.stuname as name, s.stuage as age from Studentinfo s;

--Operation Query
Select S.stuname as name, s.stuage+5 as age from Studentinfo s;

--Series Operation query
Select Stuname| | Stuage from Studentinfo;

--and and OR
Select S.stuname as name, s.stuage+5 as age from Studentinfo s wheres.stuname= ' xy ' and s.stuage=20;
Select S.stuname as name, s.stuage+5 as age from Studentinfo s wheres.stuname= ' xy ' or s.stuage=20;

--null
SELECT * from Studentinfo where Stuage isnull;
SELECT * from Studentinfo where stuage is notnull;

--between and includes boundaries equivalent to >= and <=s
Select S.stuname as name, s.stuage+5 as age from Studentinfo s wheres.stuage between and 20

--in
SELECT * from Studentinfo where stuname in (' XY ', ' WJ ');
SELECT * from Studentinfo where stuname not in (' xy ', ' WJ ');

--Fuzzy query like%,% represents multiple characters
SELECT * from Studentinfo where stuname like ' X-percent '

--Fuzzy Query Like_, _ Represents a single field
SELECT * from Studentinfo where stuname like ' x_ ';

--Sort order BY
SELECT * from Studnetinfo ORDER by fclassid Desc;
SELECT * from Studnetinfo ORDER by fclassid ASC;

--distinct Remove Duplicates
SELECT distinct Stuage as age from Studentinfo;

--group by, when used, only 2 kinds of fields can be added after the select: 1.group by, 2. Aggregate function

Select Fclassid as class number, COUNT (Stuname) as number of students from Studentinfogroup by FCLASSID;

-having filtering on the basis of grouping, appearing in order Where-group by-having

Select Fclassid as class number, COUNT (Stuname) as number of students from Studentinfogroup by FCLASSID have count (stuname) >=2

Operation of SQL Basic statement in DB2