SQL语句的基本操作

--创建数据库
create database Etp;

--连接数据库
connect to Etp;

--断开连接
disconnect Etp;

--查看当前数据库下有哪些表
list tables;

--建表
create table studentInfo(
<wbr><wbr>stuno char(5) not null,<br><wbr><wbr>stuname varchar(8),<br><wbr><wbr>stubirth date<br> );</wbr></wbr></wbr></wbr></wbr></wbr>

--查看表结构
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;

<wbr><wbr></wbr></wbr>

--重构表
reorg table studentinfo;

<wbr><wbr></wbr></wbr>

--增加一个唯一约束
alter table studentinfo alter column stutel set not null;
alter table studentinfo add constraint un_stutel unique(stutel);

<wbr><wbr></wbr></wbr>

--添加检查约束
alter table studentinfo add column stuAge int;
alter table studentinfo add constraint ch_stuAge check(stuAge > 0 and stuAge <150);

<wbr><wbr></wbr></wbr>

--添加主键约束
alter table studentinfo add constraint pk_stuno primary key(stuno);

<wbr><wbr></wbr></wbr>

--删除表
drop table studentinfo;

<wbr><wbr></wbr></wbr>

--创建表的同时添加约束方式1
create table studentinfo(
<wbr><wbr>stuNo int not null,<br><wbr><wbr>stuName varchar(8) not null,<br><wbr><wbr>stuAge int,<br><wbr><wbr>stuTel char(8),<br><wbr><wbr>constraint pk_stuNo primary key(stuNo),<br><wbr><wbr>constraint un_stuName unique(stuName),<br><wbr><wbr>constraint ch_stuAge check(stuAge &gt;=0 and stuAge &lt;150)<br> );</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr></wbr></wbr>

--创建表的同时添加约束方式2
create table studentinfo(
<wbr><wbr>stuNo int not null primary key,<br><wbr><wbr>stuName varchar(8) not null unique,<br><wbr><wbr>stuAge int check(stuAge &gt;=0 and stuAge &lt;150),<br><wbr><wbr>stuTel char(8)<br> );</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr></wbr></wbr>

--添加主外键
--新增班级表
create table classInfo(
<wbr><wbr>classId int not null primary key,<br><wbr><wbr>className varchar(20)<br> );</wbr></wbr></wbr></wbr>

<wbr><wbr></wbr></wbr>

--建表的同时添加外键
create table studentinfo(
<wbr><wbr>stuNo int not null,<br><wbr><wbr>stuName varchar(8) not null,<br><wbr><wbr>stuBirth date not null,<br><wbr><wbr>stuAge int,<br><wbr><wbr>stuTel char(8),<br><wbr><wbr>fclassId int,<br><wbr><wbr>stuBirth date not null,<br><wbr><wbr>constraint pk_stuNo primary key(stuNo),<br><wbr><wbr>constraint un_stuName unique(stuName),<br><wbr><wbr>constraint ch_stuAge check(stuAge &gt;=0 and stuAge &lt;150),<br><wbr><wbr>constraint fk_fcalssId foreign key(fclassid) references classInfo(classId)<br> );</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

-- 自增
create table studentinfo(
<wbr><wbr>stuNo int not null generated always as identity(start with 1 ,increment by 1),<br><wbr><wbr>stuName varchar(8) not null,<br><wbr><wbr>stuAge int,<br><wbr><wbr>stuTel char(8),<br><wbr><wbr>fclassId int,<br><wbr><wbr>stuBirth date not null,<br><wbr><wbr>constraint pk_stuNo primary key(stuNo),<br><wbr><wbr>constraint un_stuName unique(stuName),<br><wbr><wbr>constraint ch_stuAge check(stuAge &gt;=0 and stuAge &lt;150),<br><wbr><wbr>constraint fk_fcalssId foreign key(fclassid) references classInfo(classId)<br> );</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

--先建表再添加外键
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,'徐越',20,'12345',1,'1995-01-21');

--不是全部插入则需要写列名
insert into studentinfo(stuNo,stuName,stuTel) values(2,'wj','111');

<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br> -- 有自增长的列要写清楚列名<br> insert into studentinfo(stuName,stuAge,stuTel,fclassid,stuBirth) values('徐越',20,'12345',1,'1995-01-21');<br> insert into studentinfo(stuName,stuAge,stuTel,fclassid,stuBirth) values('tom',22,'12345',2,'1995-01-21');</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

--更新
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;<wbr><wbr><br> select * from studentinfo where stuAge is not null;<wbr><wbr></wbr></wbr></wbr></wbr>

<wbr><wbr></wbr></wbr>

--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<wbr><wbr> where stuName in ('xy','wj');<br> select * from studentinfo<wbr><wbr> where stuName not in ('xy','wj');</wbr></wbr></wbr></wbr>

--模糊查询 like%，%表示多个字符
select * from studentinfo where stuName like 'x%'

<wbr><wbr></wbr></wbr>

--模糊查询 like_ ， _表示单个字段
select * from studentinfo where stuName like 'x_'；

<wbr><wbr></wbr></wbr>

--排序 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