1、创建表
CREATE TABLE param_config
(
app_id INTEGER NOT NULL,
param_key VARCHAR(120) NULL,
param_value VARCHAR(120) NULL,
param_id INTEGER NULL AUTO_INCREMENT,(自动增长)
param_category INTEGER NULL,
param_comment VARCHAR(120) NULL,
PRIMARY KEY (param_id), (创建主键)
FOREIGN KEY(app_id) REFERENCES application (app_id) (外键引用)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; (编码)
2、为表新增字段
alter table host add column telecom_ip varchar(30);
3 、删除字段
alter table application drop column resource_deployable;
4、 修改字段名
alter table application change database_ip database_host varchar(30) NULL;
5 、新增字段同时添加外键
alter table application add foreign key (software_id) references software_pool(software_id);
6、 case 的用法 和 coalesce(max(uw3.widget_position),0)的用法
select (case when (select count(*) from user_widget uw1 where uw1.widget_position < 100 and uw1.user_name = 'username') < (select count(*) from user_widget uw2 where uw2.widget_position >= 100 and uw2.user_name = 'username') then (select coalesce(max(uw3.widget_position),0)+1 from user_widget uw3 where uw3.widget_position < 100 and uw3.user_name = 'username') else (select coalesce(max(uw4.widget_position),99)+1 from user_widget uw4 where uw4.widget_position >= 100 and uw4.user_name = 'username') end) as next_position;