SQL表操作DDL，DML和辅助的操作语句

 

在开发工作中经常使用到对DB进行操作，各种数据库类型的操作语句类似的，下面是以mysql为主进行举例，复制表结构，然后插入数据，再增加新字段，再给新字段复制，再做其它操作（省略），完成后修改新字段长度未50，再删除新增的字段，再改表未新表名，部分删除数据，再清空所有数据，最后删该表；
 

-- 创建表
CREATE TABLE co_product (
  `id` int(12) AUTO_INCREMENT not null comment '记录唯一主键ID',
  `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL comment '商品名称',
  `proClassId` int(11) DEFAULT NULL comment '商品类别ID',
  `num` int(12) DEFAULT NULL default 0 comment '商品数量',
  `create_time` datetime not null default current_timestamp comment '创建时间',
  `update_time` datetime not null default current_timestamp comment '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 comment '商品信息表';


-- 数据批量插入
insert into co_product(name,proClassId,num)
values ('春季衣服',1,10),('夏季衣服',2,20),('秋季衣服',3,30),('冬季衣服',4,40)

-- 复制表结构建立新表
create table if not exists new_co_product (like co_product);

-- 全部字段，插入和查询字段要顺序一致
insert into new_co_product select * from co_product;

-- 部分字段，插入和查询字段要顺序一致，数据按条件筛选
insert into new_co_product(field1,field2,field3...) 
select field1,field2,field3... from co_product where ...

-- 增加字段
alter table new_co_product 
add column proClassName varchar(30) default null comment '产品类别' after id;

-- 给新字段值进行初始化
udpate new_co_product set proClassName = 
(select className from productClass id = proClassId) where proClassName is null;

-- 其它操作省略

-- 修改字段的长度
alter table new_co_product modify proClassName varchar(100);

-- 删除前面新增加的字段
alter table new_co_product drop column proClassName;

-- 修改表名
alter table new_co_product rename to new_co_product_tmp;

-- 符合条件数据创建时间增加30天
update new_co_product_tmp set create_time = create_time + interval 30 day 
where id > 300 and id < 400;

-- 删除符合条件数据
delete from new_co_product_tmp where id > 600;

-- 清空表所有数据，保留表结构
truncate table new_co_product_tmp;

-- 删除表
drop table new_co_product_tmp;

以下为辅助操作的SQL,

-- 其它辅助查询
-- 查询mysql版本信息
select version();

-- 字符数据转换为数字，然后计算，再把结果格式化输出字符型数字，小数点第二位四舍五入
-- 直接计算保留所有小数点，结果为数值型
select convert('7.93', decimal(15,2))*4.05 as num1;

-- 格式化显示，小数点最后一位四舍五入，结果为字符型，
-- 注意：整数部分超过1000时采用逗号分隔的科学计数法显示
select format(convert('7.93', decimal(15,2))*4.05,2) as num2;

-- 格式化显示，小数点最后一位四舍五入，结果保留为数值型
select round(convert('7.93', decimal(15,2))*4.05,2) as num3;

-- 字符串截取, 返回逗号分割符后面部分 345
select substring_index('12,345',',',-1) as aa; 

-- 字符串截取, 返回逗号分割符后面部分 12
select substring_index('12,345',',',1) as bb; 

-- 字符串转为日期型
select str_to_date(''2024-01-30 12:34:56'','%Y-%m-%d %H:%i:%s') as myDateTime;
SELECT STR_TO_DATE('2022-01-01', '%Y-%m-%d') AS myDate2;
SELECT CAST('2024-01-30' AS DATE) AS myDate3;
SELECT DATE('2024-01-30') AS myDate4;

-- mysql查询某库中数据量大的表和大小信息
select concat(table_schema,'.',table_name) as table_n,
table_rows as num_of_row,
concat(round(data_length)/1024/1024,6),'MB') as data_size,
concat(round(index_length)/1024/1024,6),'MB') as index_size,
concat(round(data_length+index_length)/1024/1024,6),'MB') as total 
from information_schema.tables 
where table_schema = 'myDBName'
order by data_length+index_length desc;

/* mysql数据存储空间压减方法：
一般数据库表随着使用，其占用存储空间只会变大，不会变小；表操作数据一般是先插入，再删除，但被删除的记录所占的空间会保留；那进行 
*/

alter table xxxTable engine = Innodb; 

/*
相当于回收了那些已经被删除的记录所占的空间，然后mysql的db文件将变得非常小，相当于缩减db的存储空间；*/

还有很多，后续再逐步丰富...