SQL語句的基本操作

--建立資料庫

create database etp;

--連接配接資料庫

connect to etp;

--斷開連接配接

disconnect etp;

--檢視目前資料庫下有哪些表

list tables;

--建表

create table studentinfo(

 stuno char(5) not null,

 stuname varchar(8),

 stubirth date

);

--檢視表結構

describe table studentinfo;

--新增表字段

alter table studentinfo add stutel int;

alter table studentinfo add abc int;

--修改字段類型

alter table studentinfo alter column stutel set data type char(11);

--删除字段

alter table studentinfo drop column abc;

--增加一個非空限制

alter table studentinfo alter column stuname set not null;

--重構表

reorg table studentinfo;

--增加一個唯一限制

alter table studentinfo alter column stutel set not null;

alter table studentinfo add constraint un_stutel unique(stutel);

--添加檢查限制

alter table studentinfo add column stuage int;

alter table studentinfo add constraint ch_stuage check(stuage > 0 and stuage <150);

--添加主鍵限制

alter table studentinfo add constraint pk_stuno primary key(stuno);

--删除表

drop table studentinfo;

--建立表的同時添加限制方式1

 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)

--建立表的同時添加限制方式2

 stuno int not null primary key,

 stuname varchar(8) not null unique,

 stuage int check(stuage >=0 and stuage <150),

 stutel char(8)

--添加主外鍵

--新增班級表

create table classinfo(

 classid int not null primary key,

 classname varchar(20)

--建表的同時添加外鍵

 stubirth date not null,

 fclassid int,

 constraint ch_stuage check(stuage >=0 and stuage <150),

 constraint fk_fcalssid foreign key(fclassid) references classinfo(classid)

-- 自增

 stuno int not null generated always as identity(start with 1 ,increment by 1),

--先建表再添加外鍵

alter table studentinfo add constraint fk_classid foreign key(fclassid) references classinfo(classid);

--從系統表中查詢限制名

select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references;

--插入

insert into classinfo values(1,'etp-1');

insert into studentinfo values(1,'xy,20,'12345',1,'1995-01-21');

--不是全部插入則需要寫列名

insert into studentinfo(stuno,stuname,stutel) values(2,'wj','111');

-- 有自增長的列要寫清楚列名

insert into studentinfo(stuname,stuage,stutel,fclassid,stubirth) values('xy,20,'12345',1,'1995-01-21');

insert into studentinfo(stuname,stuage,stutel,fclassid,stubirth) values('tom',22,'12345',2,'1995-01-21');

--更新

update studentinfo set stubirth = '1990-02-21' where stuname='xy';

update studentinfo set stubirth = '1990-02-21',stuage = 21 where stuname='xy';

--删除

deleted from studentinfo where stuname='xy';

--查詢

select * from studentinfo where stuname='xy';

select stuname,stuage from studentinfo;

--别名查詢

select stuname as 姓名,stuage as 年齡 from studentinfo;

select s.stuname as 姓名,s.stuage as 年齡 from studentinfo s;

--運算查詢

select s.stuname as 姓名,s.stuage+5 as 年齡 from studentinfo s;

--串聯運算查詢

select stuname||stuage from studentinfo;

--and 和 or

select s.stuname as 姓名,s.stuage+5 as 年齡 from studentinfo s where s.stuname='xy' and s.stuage=20;

select s.stuname as 姓名,s.stuage+5 as 年齡 from studentinfo s where s.stuname='xy' or s.stuage=20;

--null

select * from studentinfo where stuage is null; 

select * from studentinfo where stuage is not null; 

--between and 包括邊界 相當于>=和<=s

select s.stuname as 姓名,s.stuage+5 as 年齡 from studentinfo s where s.stuage between 10 and 20

--in

select * from studentinfo  where stuname in ('xy','wj');

select * from studentinfo  where stuname not in ('xy','wj');

--模糊查詢 like%，%表示多個字元

select * from studentinfo where stuname like 'x%'

--模糊查詢 like_ ， _表示單個字段

select * from studentinfo where stuname like 'x_'；

--排序 order by

select * from studnetinfo order by fclassid desc;

select * from studnetinfo order by fclassid asc;

--distinct去掉重複

select distinct stuage as 年齡 from studentinfo;

--group by,使用的時候,select 後面隻能加2種字段: 1.group by 後面出現的，2.聚合函數

select fclassid as 班級号,count(stuname) as 學生個數 from studentinfo group by fclassid;

-having 在分組的基礎上過濾,出現順序where-group by-having

select fclassid as 班級号,count(stuname) as 學生個數 from studentinfo group by fclassid having count(stuname)>=2