MySQL和DB2常用sql及创建并使用索引

一、MySQL常用sql
1.创建数据库
create database test;

2.使用数据库
use test;

3.创建表
create table dms_op_yk_temp (
id bigint primary key not null auto_increment comment ‘id’,
city_id integer comment ‘地市id’,
city_name varchar(64) comment ‘地市名称’,
brk_name varchar(64) comment ‘开关名称’,
occur_time varchar(64) comment ‘时间’,
content varchar(120) comment ‘内容’,
if_yk varchar(64) comment ‘是否遥控’
) comment ‘遥控表’;

4.删除表
drop table dms_op_yk_temp

5.表中新增一列(mysql中支持after，在某一列后面新增一列，db2不支持此功能，只能在表的最后面新增一列)
alter table dms_op_yk_temp add yksyl varchar(64) after brk_name;
– 一次新增多列，语法上没错，但是执行了100多秒都没有结果，应该是不行，或者说不如分成多条sql执行，每次添加一个字段来得快（db2也不支持一次添加多个字段）
alter table dms_op_yk_temp add column yksyl varchar(64),add column if_tj int;

6.表中删除某一列
alter table dms_op_yk_temp drop column yksyl;

7.修改某一列的数据类型
alter table dms_op_yk_temp modify column brk_name varchar(100);

8.修改某一列的列名
alter table dms_op_yk_temp change column yk_info if_yk varchar(32);

9.插入数据
insert into dms_op_yk_temp(city_id,city_name,brk_name,occur_time,if_yk) values(2,‘无锡’,‘3328开关’,‘2021-03-17 00:15:16’,‘否’);

10.更新数据
update dms_op_yk_temp set content = ‘无锡FA测试GGGKKKBfgerffkkgg数据’ where city_id = 2;

11.查询结果只取前十条(limit n,m 参数一：从第 n 条数据之后开始取；参数二：取 m 条数据)
select * from dms_op_yk_temp where city_name = ‘常州’ limit 5,10;

12.查询结果排序，并给出序号，取序号在100到200之间的数据
select k.* from (
select @rownum:=@rownum+1 as rownum,a.* from dms_op_yk_temp a,(select @rownum:=0) b order by a.occur_time desc
) k where k.rownum >= 100 and k.rownum < 200;

13.重置表的自增长主键的起始值（包含1000）
alter table dms_op_yk_temp auto_increment = 1000;

14.查询数据库版本
select version();

15.截取字符串
left() 从左边开始截取 参数一：截取的字段名；参数二：截取几位；
select content,left(content,4) as re from dms_op_yk_temp where city_id = 2;
right() 从右边开始截取 �参数一：截取的字段名；参数二：截取几位；
select content,right(content,4) as re from dms_op_yk_temp where city_id = 2;
substring() 参数一：截取的字段名；参数二：从第几位开始截取；参数三：截取几位；
select content,substring(content,4,6) as re from dms_op_yk_temp where city_id = 2;

16.拼接字符串
select city_name,brk_name,concat(city_name,brk_name) as re from dms_op_yk_temp where city_id = 2;

17.查询指定表的大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),‘MB’) as table_size from information_schema.tables where table_schema = ‘test’ and table_name = ‘article’
查询指定数据库的大小
select concat(round(sum(DATA_LENGTH/1024/1024/1024),4),‘GB’) as table_size from information_schema.tables where table_schema = ‘test’
查询所有表的大小并排序
select concat(table_schema,’.’,table_name) as table_name,concat(round(table_rows/1000000,4),‘MB’) as number_rows,concat(round(data_length/(102410241024),4),‘GB’) as table_size,concat(round(index_length/(102410241024),4),‘GB’) as index_size,concat(round((data_length+index_length)/(102410241024),4),‘GB’) as total from information_schema.tables order by total desc;

18.查看表结构
desc article;

19.查看表有哪些索引（查看表 article 中有哪些索引）
show indexes from article;

20.删除表中的某个索引（删除表 article 中的 index_title_time 索引，index_title_time 是 show indexes from article; 查询结果中的 key_name）
drop index index_title_time on article;

21.通过修改表结构创建索引
alter table article add index index_content(content);

22.创建表之后再创建普通索引
create index index_content on article(content);

23.创建组合索引
alter table article add index index_title_time (title,occur_time);

24.创建唯一性索引(字符串类型的字段创建索引的时候是可以指定一个值的，比如说某个字段是varchar(200)类型的，你可以指定前100个字符包含索引，下面是指定了前64个字符包含索引)
create unique index index_article_title on article(title(64));

25.创建表的时候指定索引
create table article(
id bigint not null primary key auto_increment,
title varchar(64),
content varchar(64),
occur_time varchar(64),
rdl int,
index index_content on article(content)
);

索引类型：1.普通索引; 2.主键索引；3.唯一性索引；4.组合索引；5.全文索引；6.前置索引。

二、DB2常用sql
1.删除表
drop table dlquser.dms_dispatcher_user;

2.删除数据
delete from dlquser.dms_fa_gzfx_13 where city_id = 13;

3.添加一列
alter table dlquser.dms_fa_gzfx_1 add if_zq int;

4.创建表
create table dlquser.dms_dispatcher_user(
id bigint not null generated by default as identity,
city_id integer,
user_id integer,
user_name varchar(64),
occur_time varchar(64),
primary key (id)
);
comment on table dlquser.dms_dispatcher_user is ‘调度人员信息表’;
comment on column dlquser.dms_dispatcher_user.id is ‘id’;
comment on column dlquser.dms_dispatcher_user.city_id is ‘地市id’;
comment on column dlquser.dms_dispatcher_user.user_id is ‘用户id’;
comment on column dlquser.dms_dispatcher_user.user_name is ‘用户名称’;
comment on column dlquser.dms_dispatcher_user.occur_time is ‘注册时间’;

5.插入数据
insert into dlquser.dms_fajd_report(city_id,area_id,data_period,data_year,start_time,end_time,zy_num,facs_plannum,ljwccs_num,jhwcl)
values(13,0,28,‘2020’,‘2020-06-04’,‘2020-06-10’,null,367,188,0.5123);

6.更新表数据
update dlquser.dms_fajd_report set ljwccs_num = 0,jhwcl = 0 where end_time = ‘2020-06-24’;

7.批量替换某些字段中的特殊符号 replace() 参数一：将要替换的字段名；参数二：将要替换的目标字符；参数三：替换后的字符；
update dlquser.dms_fa_gzfx_detail_8 set checked_id = replace(checked_id,‘g’,‘G’) where checked_id like ‘%g%’

8.解除锁定（db2 中添加一列或是修改字段的数据类型之后都会导致表被锁定，锁定之后不能再操作这张表，插入、修改等都不行，必须先解锁才可以）
call sysproc.admin_cmd(‘reorg table dlquser.DMS_FA_GZFX_13’);

9.删除一列
alter table dms.dms_ld_device_1 drop column yc_id;

10.添加一列
alter table dlquser.DMS_FA_GZFX_1 add file_name varchar(200);

11.修改某一列的数据类型
alter table dlquser.DMS_YX_SOE_LOSE_12 alter column yx_id set data type varchar(20);

12.查询结果只取前 10 条
方法一：fetch 关键字
select * from dlquser.dms_fa_gzfx_13 order by update_time desc fetch first 10 rows only;

方法二：row_number() over() 分组聚合函数
select * from (
select
row_number() over(order by update_time desc) as num,a.*
from dlquser.dms_fa_gzfx_13 a
) k where k.num <= 10

13.db2 数据库中 row_number() over() 的用法（术语：分组聚合，就是先分组再排序）
– row_number() over(partition by yx_id order by update_time desc) 其中 partition by 后面跟的就是分组的字段，order by 后面就是排序的字段，这里排序是在分组的内部进行排序，不是所有排序
select k.* from (
select
row_number() over(partition by yx_id order by update_time desc) as num,yx_id,update_time,if_xy,if_cg,if_zq,brk_id,jlrq,city_id
from dlquser.v_dms_fa_gzfx
) k where k.num = 1

14.重置表的自增长主键的起始值
alter table dlquser.dms_fa_gzfx_13 alter id restart with 1242;

15.查询数据库版本
select service_level from sysibmadm.env_inst_info

16.将从 1970-01-01 08:00:00 开始的秒数转换成 yyyy-MM-dd HH:mm:ss 格式的时间
参数一：时间单位（s：秒）；参数二：将要转换的字段名称；参数三：计算的起始时间
select para_7,dateadd(s,para_7,‘1970-01-01 08:00:00’) as result from d5000.dms_channel_info where para_7 > 0
PARA_7 RESULT
103147200 1973-04-09 04:00:00.000000
将从 1970-01-01 08:00:00 开始的毫秒值转换成 yyyy-MM-dd HH:mm:ss 格式的时间
select para_7,dateadd(s,para_7/1000,‘1970-01-01 08:00:00’) as result from d5000.dms_channel_info where para_7 > 0
para_7 result
103147200 1970-01-02 12:39:07.000000

17.截取字符串的方法 substring() 参数一：截取的字段名；参数二：从第几位开始截取（起始位置是 1）；参数三：截取到第几位结束；
注意：参数一是 0 和 1 效果一样
select distinct filter,substring(filter,1,4) as container_type from smartsys.obj_dev where filter = ‘feedersegment’
mysql截取字符串：left() 从左边开始截取 参数一：截取的字段名；参数二：截取到第几位；right() 从右边开始截取 参数一：截取的字段名；参数二：截取到第几位；
注意：left(name,4) 在mysql中截取中文字符串的时候能截取到四个中文字，但是在DB2中只能截取到两个中文字
select id,left(id,4) as name,brk_name from dlquser.dms_yx_bw_day_stat_5 where brk_id = 3800475135547280863
select id,right(name,2) as name from category where id = ‘1-10’

18.拼接字符串方法 concat()
可以有两个参数，也可以有三个参数。
DB2拼接参数时：参数一：字符串1；参数二：字符串2。MySQL的concat()方法可以拼接多个字符串，DB2的concat()方法只能拼接两个字符串
select concat(term_id,term_name) from dms.dms_com_terminal_1 where id = 3356852
MySQL拼接参数时：参数一：字符串1；参数二：字符串2；参数三：字符串3。如果两个字符串中有一个是null，那结果就是null
select concat(username,null,password) from user where id = 3
MySQL拼接参数时：参数一：字符串1；参数二：：字符串2。如果两个字符串中有一个是null，那结果也是null.MySQL的concat()方法可以拼接多个字符串，DB2的concat()方法只能拼接两个字符串
select concat(username,password) from user where id = 3

19.查看表的大小
sql:
select
a.tabschema,a.tabname,a.tbspace,b.pagesize,a.npages,decimal(cast(b.pagesize as double)a.npages/(10241024*1024),20,5) as tablesize
from syscat.tables a
left join syscat.tablespaces b
on a.tbspaceid = b.tbspaceid
where type = ‘T’ and a.tabschema = ‘DLQ’ and tabname = ‘DMS_YP_YK_8’
order by a.tbspace
参数讲解：
tabschema：模式名
tabname：表名
pagesize: 表空间每页的大小
npages：表占用表空间的页数
注意：
①表大小(tablesize) = 表占用表空间的页数(npages) * 表空间每页的大小(pagesize);
②tabname = ‘DMS_YP_YK_8’ 这里表名只能用大写的字母；
③函数 decimal()，参数一：将要处理的数值；参数二：可保留的最大数字位（包含整数位和小数位，例如：decimal(5,2),取值范围是：-999.99 到 999.99）；参数三：小数点后保留几位数。
④pagesize和npages都不是double类型的，而decimal()函数对于不是double类型的数字，只是在小数点后面添加0，而不是四舍五入保留几位小数，所以这里计算表大小，想要正确保留几位小数的话需要使用cast(b.pagesize as double)将其中一个数字转成double，然后再计算，得到的结果为double类型，最后decimal()四舍五入保留几位小数。下面两张图是decimal() 函数处理不同类型数值的结果：

三、MySQL创建索引并使用
1.首先创建一张表 article（id是自增长的主键，title是随机生成的长度为10的字符串，content是随机生成的长度为100的字符串，occur_time是yyyy-MM-dd HH:mm:ss格式的字符串类型的时间，rdl是随机生成的0-99的随机数），写个程序，存进去 200万 条数据；
create table article(
id bigint not null primary key auto_increment,
title varchar(64),
content varchar(64),
occur_time varchar(64),
rdl int
);
2.在没有创建任何索引的前提下，执行下面的sql，记录sql执行时间
查询sql：select distinct occur_time from article;
执行时间：4.374s
3.直接在时间上创建一个索引（不带长度），创建索引后再执行上面的sql，查看sql执行时间
创建索引sql：create index index_title on article(occur_time);
查询sql：select distinct occur_time from article;
执行时间：0.040s
4.rdl字段上没有创建索引之前，执行下面sql进行查询，记录sql执行时间
查询sql：select rdl,count() from article group by rdl order by rdl asc;
执行时间：4.987s
5.通过修改表结构在rdl字段上面创建索引，再次执行上面的查询sql，对比sql执行时间
创建索引sql：alter table article add index index_rdl(rdl);
查询sql：select rdl,count() from article group by rdl order by rdl asc;
执行时间：0.380s
6.执行下面的查询sql，进行模糊查询
查询sql-1：select * from article where content like ‘覆覆%’;
执行时间：6.289s
查询sql-2：select * from article where content like ‘%覆覆%’;
执行时间：3.586s
在 content 字段上创建索引：create index index_content on article(content);
创建索引后再执行上面两个查询sql，看看执行时间
查询sql-1：select * from article where content like ‘覆覆%’;
执行时间：0.078s
查询sql-2：select * from article where content like ‘%覆覆%’;
执行时间：3.590s
总结：使用模糊查询的时候，如果是以通配符（%或者_）开头的话，是不会使用索引的，所以执行时间并没有减少；如果模糊查询没有使用通配符（%或者_）开头的话，是会使用索引的，所以查询时间很明显的缩短了。
7.把前面创建的索引全部删除掉（上面有删除索引的方法），在没有任何索引的前提下，执行下面的查询sql，然后创建组合索引，再次执行同一条sql，对比执行时间
查询sql：select * from article where title like ‘覆%’ and occur_time > ‘2021-03-17 11:31:56’ order by occur_time desc;
执行时间：7.073s
创建组合索引：alter table article add index index_title_time (title,occur_time);
创建组合索引之后再次执行查询sql：select * from article where title like ‘覆%’ and occur_time > ‘2021-03-17 11:31:56’ order by occur_time desc;
执行时间：0.349s
结论：创建组合索引之后查询时间大幅缩短。