【数据库系列】你想要的sql全都有plus

‍

目录

详细sql

-- ===============================================================================================
-- 数据准备（前提：先执行下方创库创表sql）
INSERT INTO `dbname`.`t_table_info`(`id`, `name`, `age`, `sex`, `job`, `json_set`, `text`, `blob`, `create_time`, `update_time`) VALUES (1, '张三', 1, '男', '律师', '{"uuid":"123"}', '文本1', NULL, '2021-10-27 23:28:35', '2022-01-09 12:37:07');
INSERT INTO `dbname`.`t_table_info`(`id`, `name`, `age`, `sex`, `job`, `json_set`, `text`, `blob`, `create_time`, `update_time`) VALUES (2, '李四', 2, '男', '律师', '{"uuid":"123"}', '文本2', NULL, '2021-10-13 23:28:38', '2022-01-09 12:37:09');
INSERT INTO `dbname`.`t_table_info`(`id`, `name`, `age`, `sex`, `job`, `json_set`, `text`, `blob`, `create_time`, `update_time`) VALUES (3, '王五', 3, '男', '医生', '{"uuid":"123"}', '文本3', NULL, '2021-10-20 23:30:56', '2022-01-09 12:37:11');
INSERT INTO `dbname`.`t_table_info`(`id`, `name`, `age`, `sex`, `job`, `json_set`, `text`, `blob`, `create_time`, `update_time`) VALUES (4, '赵六', 4, '女', '医生', '{"uuid":"456"}', '文本4', NULL, '2021-10-27 23:31:28', '2022-01-09 12:37:12');
INSERT INTO `dbname`.`t_table_info`(`id`, `name`, `age`, `sex`, `job`, `json_set`, `text`, `blob`, `create_time`, `update_time`) VALUES (5, '嘎嘎大王', 5, '女', '教师', '{"uuid":"456"}', '文本5', NULL, '2021-11-21 21:58:17', '2022-01-09 12:37:14');
INSERT INTO `dbname`.`t_table_info`(`id`, `name`, `age`, `sex`, `job`, `json_set`, `text`, `blob`, `create_time`, `update_time`) VALUES (6, '呱呱大王', 6, '女', '教师', '{"uuid":"456"}', '文本6', NULL, '2021-11-21 21:59:32', '2022-01-09 12:37:16');





-- ===============================================================================================
-- 一、ddl（data define language）数据定义语言
-- 1、库管理
-- 1、库的创建（create）
create database if not exists dbname;

-- 2、库的修改（alter）
alter database dbname character set utf8;

-- 3、库的删除（drop）
drop database if exists dbname;


-- 2、表管理
-- 1、表的创建（create）
drop table if exists `t_table_info`;
create table `t_table_info`  (
  `id` int(0) not null,
  `name` varchar(255) character set utf8mb4 collate utf8mb4_0900_ai_ci null default null comment '姓名（varchar(m) 存放长度不固定的字符）',
  `age` int(0) null default null comment '年龄',
  `sex` varchar(255) character set utf8mb4 collate utf8mb4_0900_ai_ci null default null comment '性别',
  `job` varchar(255) character set utf8mb4 collate utf8mb4_0900_ai_ci null default null comment '职业',
  `json_set` varchar(100) character set utf8mb4 collate utf8mb4_0900_ai_ci null default null comment 'json集合',
  `text` text character set utf8mb4 collate utf8mb4_0900_ai_ci null comment '文本（text 存放长文本）',
  `blob` blob null comment '不录歌（blob 存放较大的二进制，如图片等）',
  `create_time` datetime(0) null default null comment '创建时间',
  `update_time` datetime(0) null default null on update current_timestamp(0) comment '更新时间',
  primary key (`id`) using btree,
  unique index `index_id`(`id`) using btree,
  index `index_name`(`name`) using btree,
  index `index_age_sex`(`age`, `sex`) using btree
) engine = innodb character set = utf8mb4 collate = utf8mb4_0900_ai_ci row_format = dynamic;

-- 复制表（create）
create table dbname.`t_table_info_v2` like dbname.`t_table_info`;

-- 2、表的修改（alter）
-- 添加列
alter table dbname.`t_table_info` add new_col varchar(255) null comment '新增列'; 
-- 修改列
alter table dbname.`t_table_info` change new_col new_col_v2 varchar(50);
-- 删除列
alter table dbname.`t_table_info` drop column new_col_v2;

-- 3、表的删除（drop）
drop table dbname.`t_table_info_v2`;

-- 4、表的查询（desc）
desc dbname.`t_table_info`;
show columns from dbname.`t_table_info`;

-- 5、复制表数据
insert into dbname.`t_table_info_v2` select * from dbname.`t_table_info`;

-- 6、重命名表
rename table dbname.`t_table_info_v2` to dbname.`t_table_info_v3`;

-- 7、清空表
truncate table dbname.`t_table_info_v3`;




-- ===============================================================================================
-- 二、dml（data manipulation language）数据操作语言
-- 1、增（insert）
insert into `dbname`.`t_table_info`(`id`, `name`, `age`, `sex`, `job`, `json_set`, `text`, `blob`, `create_time`, `update_time`, `new_col_v2`) 
values (23, '呱呱大王', 7, '女', '教师', '{"uuid":"456"}', null, null, now(), now(), null);

-- 2、删（delete）
delete from dbname.`t_table_info` where id = '1';

-- 3、改（update）
update `dbname`.`t_table_info` set `name` = '呱呱大王', `age` = 7, `sex` = '女', `job` = '教师', `json_set` = '{"uuid":"456"}', `text` = null, `blob` = null, `create_time` = now(), `update_time` = now(), `new_col_v2` = null where `id` = 1;




-- ===============================================================================================
-- 三、dql（data query language）数据查询语言
-- 1、基础查询（select）
select * from dbname.`t_table_info`;

-- 2、条件查询（where）
select * from dbname.`t_table_info` where id = '1';
-- and和or查询
select * from dbname.`t_table_info` where job in ('律师') and (id in ('1') or age in ('5'));
-- between筛选某个范围内的值
select * from dbname.`t_table_info` where age between '1' and '5';
-- 模糊查询
select * from dbname.`t_table_info` where name like '%大王%';
-- 多条件模糊查询 
select * from dbname.`t_table_info` where name like '%大王' or job like '%律师%';

-- 3、分页查询（limit）
-- 查询5条数据
select * from dbname.`t_table_info` limit 5;
-- 查询最新一条数据
select * from dbname.`t_table_info` order by update_time desc limit 1;

-- 4、连接查询（join）
-- 内连接-1：内连接查询操作列出与连接条件匹配的数据行
select * from t_table_info a, t_table_info_v2 b where a.id = b.id;
-- 内连接-2：内连接查询操作列出与连接条件匹配的数据行
select * from t_table_info a inner join t_table_info_v2 b on a.id = b.id;
-- 外连接-左连接：以左表为基准，右表的列为null
select * from t_table_info a left join t_table_info_v2 b on a.id = b.id;
-- 外连接-右连接：以右表为基准，左表的列为null
select * from t_table_info a right join t_table_info_v2 b on a.id = b.id;
-- 交叉连接：交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
select * from t_table_info a cross join t_table_info_v2 as b on a.id = b.id;

-- 5、子查询(in)
select * from dbname.`t_table_info` where id in ('1','5');

-- 6、排序查询（order by）
select * from dbname.`t_table_info` order by update_time desc, id desc;

-- 7、分组查询（group by）
select job, count(0) as '重复次数' from dbname.`t_table_info` group by job;
select job, count(job) as '重复次数' from dbname.`t_table_info` group by job having count(job)>1;

-- 8、时间查询
select * from dbname.`t_table_info`;
-- 查询今天的数据
select * from dbname.`t_table_info` where to_days(create_time) = to_days(now());
-- 查询昨天的数据
select * from dbname.`t_table_info` where datediff(create_time, now())=-1;
-- 查询最近7天的数据
select * from dbname.`t_table_info` where date_sub(curdate(), interval 7 day) <= date(create_time);
-- 查询本月的数据
select * from dbname.`t_table_info` where date_format(create_time, '%y%m') = date_format(curdate(), '%y%m');
-- 查询上月数据
select * from dbname.`t_table_info` where period_diff(date_format(now(),'%y%m'), date_format(create_time, '%y%m'))=1;

-- 9、其它查询
-- 查询json：json_unquote() 去除双引号; json_extract() 提取json的key
select json_unquote(json_extract(json_set,'$.uuid')) from dbname.`t_table_info`;




-- ===============================================================================================
-- 四、dcl（data control language）数据控制语言
-- 查看用户表
select * from mysql.`user`;

-- 1、创建用户（create）
create user student identified by 'student';

-- 2、用户号授权（grant）
grant select, insert, update, delete on `dbname`.* to `student`@`%`; flush privileges;

-- 3、撤销权限（revoke）
revoke delete on `dbname`.* from `student`@`%`;

-- 4、查看权限（show）
show grants for 'student'@'%';

-- 5、删除用户（drop）
drop user 'student'@'%';

-- 6、修改用户（update）
update mysql.`user` set user = 'student_v2' where user ='student' and host = '%';



-- ===============================================================================================
-- 五、索引操作
-- 1、索引创建（create）
-- 创建唯一索引
create unique index index_id on dbname.`t_table_info` (id);
-- 创建普通索引,允许重复值,1个索引
create index index_name on dbname.`t_table_info` (name);
-- 创建普通索引,允许重复值,2个索引
create index index_age_sex on dbname.`t_table_info` (age, sex);

-- 2、索引删除（drop）
drop index index_name on dbname.`t_table_info`;

-- 3、索引查询（show）
show index from dbname.`t_table_info`;


-- ===============================================================================================
-- 六、存储过程
-- 简单查询
drop procedure if exists get_info;
create procedure get_info()
begin 
  select * from dbname.`t_table_info`;
end;
call get_info();


-- 批量插入数据
drop procedure if exists batch_data;
create procedure batch_data()
begin
declare i int;
  set i=7;
  while(i<=8) do
           insert into dbname.`t_table_info` (id, name, age, sex, job, json_set, create_time, update_time) values(i, concat('name',id), 1, '男', '律师', '{"uuid":"123"}', now(), now());
    set i=i+1; 
  end while;
end;
call batch_data();


-- ===============================================================================================
-- 七、相关函数
-- 1、时间函数
-- 获取当前时间
select
now(),
curdate(),
curtime(3),
current_date(),
current_time(3),
current_timestamp(2),
current_date,
current_time,
current_timestamp,
unix_timestamp(now());

-- 获取当前日期
select date(curdate());

-- 获取当前月份
select month(curdate());

-- 获取本月第一天
select date_add(curdate(), interval-day(curdate())+1 day); 

-- 获取本月最后一天
select last_day(curdate()); 

-- 获取本月天数
select day(last_day(curdate()));

-- 获取当前年份
select year(curdate());

-- date_format(date, format)函数用于以不同的格式显示日期/时间数据，date是日期列,format是格式
select date_format(now(),'%y-%m-%d');

-- str_to_date(str,format) 将字符串转成日期
select str_to_date(now(),'%y-%m-%d') from dbname.`t_table_info`;

-- 查询当前日期是哪个季度的
select extract(quarter from str_to_date(now(),'%y-%m-%d'));

-- 时间区间
select datediff('2022-12-31',now());

-- 加减某个时间间隔函数date_add()与date_sub()
-- date_add('某个日期时间',interval 1 时间种类名); quarter:季，week:周，day:天，hour:小时，minute:分钟，second:秒，microsecond:毫秒
-- 加1天
select date_add('2022-01-01', interval 1 day); 
-- 加1月
select date_add(now(), interval 1 month); 

-- 日期相减
select datediff('2022-12-31', '2022-01-01');

-- 时间相减
select timediff('2022-01-01 00:00:00', '2021-01-02 00:00:00');
select timediff('12:00:00','12:30:00');

-- 时间相减-转换为秒数
select time_to_sec(timediff('12:00:00', '12:02:00'));

-- 查询当年月份，前提：dbname.`t_table_info`大于等于12条数据
select 
case 
  when length(mon)=1 then concat(left(current_date,5),'0',mon) 
  else concat(left(current_date,5),mon) end months 
  from (select @m:=@m+1 mon from dbname.`t_table_info`,(select @m:=0) a ) aa limit 12;

-- 2、随机数
-- 生成小于1的随机数
select rand();

-- 生成100以内的随机数
select floor(1 + (rand() * 100));

-- 生成3位随机数
select ceiling(rand()*900+100);

-- 生成4位随机数
select ceiling(rand()*9000+100);

-- 3、其它函数
-- 统计
select count(*) from dbname.`t_table_info`;

-- 查询平均值
select avg(id) from dbname.`t_table_info`;

-- 查询最大值
select max(id) from dbname.`t_table_info`;

-- 查询最小值
select min(id) from dbname.`t_table_info`;

-- 求和
select sum(id) from dbname.`t_table_info`;

-- concat：在字段值前加上前缀aaa-
update dbname.`t_table_info` set name = concat('aaa-',name) where name in ('张三','李四');

-- replace：去掉字段值的某一部分
update dbname.`t_table_info` set name=replace(name,'aaa-','') where name in ('aaa-张三','aaa-李四');

-- case举例
select
  name,
  (case when name = '张三' then '张三呀'
        when name = '李四' then '李四呀'
        when name is null then '空值'
        else '其它情况' end) as 备注
from dbname.`t_table_info`;




-- ===============================================================================================
-- 八、其它内容
-- 1、查看数据库大小
-- 查询所有数据库的表大小
select table_name, concat(truncate(data_length/1024/1024,2),' mb') as data_size,concat(truncate(index_length/1024/1024,2),' mb') as index_size
from information_schema.tables
group by table_name
order by data_length desc;

-- 查询某个数据库的表大小
select table_name, concat(truncate(data_length/1024/1024,2),' mb') as data_size,concat(truncate(index_length/1024/1024,2),' mb') as index_size
from information_schema.tables where table_schema = 'dbname'
group by table_name
order by data_length desc;

-- 2、设置变量
-- set设置变量
set @job = '教师';
set @age = (select age from dbname.`t_table_info` where id in ('6'));
select * from dbname.`t_table_info` where job in (@job) and age in (@age);

-- select设置变量
select @job := '教师';
select * from dbname.`t_table_info` where job in (@job);

-- 查询结果并赋值
select age, job into @age, @job from dbname.`t_table_info` where id in ('6');
select * from dbname.`t_table_info` where age in (@age) and job in (@job);

-- 3、系统变量
-- 查看所有的会话变量
show session variables;

-- 查看所有的全局变量
show global variables;

-- 查看数据库隔离级别
show variables like '%isolation';
select @@global.tx_isolation;

-- 查看mysql版本
show variables like 'version%';

-- 查看端口、目录、数据存放目录、服务器id
select @@port, @@basedir, @@datadir, @@server_id;

-- 4、explain
-- 分析查询的sql语句
explain select * from dbname.`t_table_info`;

-- 5、批量删除表（将结果复制出来并执行）
select concat('drop table if exists ', table_name, ';') from information_schema.tables where table_schema = 'dbname';

关注【嘎嘎软件测试】

搞测试，不迷路

呱呱大王本呱带你飞！

嘎嘎软件测试

本公众号将分享个人成长、团队管理、软件测试技能知识等内容，更新频率一周两篇，做到有思想、有观点、有深度，欢迎订阅。