1.创建数据库
CREATE DATABASE database-name -- database-name 数据库名称
2.删除数据库
drop database database-name -- database-name 数据库名称
3.创建表
create table table-name(col1 type1 [not null] [primary key],col2 type2 [not null],..)
3.1根据已有的表创建新表:
create table table_new like table_old (使用旧表创建新表)
create table table_new as select col1,col2… from table_old definition only
4.删除表
drop table table-name
5.增加一个列
alter table table-name add column col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变
唯一能改变的是增加varchar类型的长度。
6.添加、删除主键
//添加主键
alter table table-name add primary key(col)
//删除主键
alter table table-name drop primary key(col)
7.创建删除、索引
//创建索引
create [unique] index idxname on tabname(col….)
//删除索引
drop index idxname
注:索引是不可更改的,想更改必须删除重新建。
8.创建、删除试图
//创建视图
create view viewname as select statement
//删除视图
drop view viewname
9.增删改查语句!
//SQL简单查询
select * from tablename where 范围
//SQL插入语句
insert into tablename (field1,field2) values(value1,value2)
//SQL删除语句
delete from tablename where 范围
//SQL更新语句
update tablename set field1=value1 where 范围
//SQL查找语句
select * from tablename where field1 like ’%value1%’ ---like的语法很精妙,查资料!
//SQL排序语句
select * from tablename order by field1,field2 [desc]
//SQL计算总数语句
select count(field1) as totalcount from tablename
//SQL计算求和语句
select sum(field1) as sumvalue from tablename
//SQL计算平均数语句
select avg(field1) as avgvalue from tablename
//SQL统计最大数语句
select max(field1) as maxvalue from tablename
//SQL统计最小数语句
select min(field1) as minvalue from tablename