【數據庫】你想要的sql全都有

呱呱大王 嘎嘎軟件測試


我很早就想整理一波常用sql,這不就搞起來了。


先執行初始化sql,後面的就可以在這個數據表上面練習了,拿來即用。


我後面會慢慢完善這個大一統的sql集合的,感興趣的話可以持續關注呀~



/* 目錄
數據準備user表相關選擇數據庫設計表結構索引相關增刪改查條件查詢時間查詢連接查詢存儲過程查詢數據庫大小設置變量系統變量時間函數其它函數隨機數
**/
-- 數據準備----------------------------------------
CREATE TABLE `t_table_info` ( `id` int(11) NOT null, `name` varchar(255) DEFAULT null COMMENT '姓名', `age` int(11) DEFAULT null COMMENT '年齡', `sex` varchar(255) DEFAULT null COMMENT '性別', `job` varchar(255) DEFAULT null COMMENT '職業', `json_set` varchar(100) DEFAULT null COMMENT 'json集合', `create_time` datetime DEFAULT null COMMENT '創建時間', `update_time` datetime DEFAULT null ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `index_id` (`id`), KEY `index_name` (`name`), KEY `index_age_sex` (`age`,`sex`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

INSERT INTO dbname.t_table_info (id, name, age, sex, job, json_set, create_time, update_time) VALUES(1, '張三', 1, '男', '律師', '{"uuid":"123"}', '2021-10-27 23:28:35', now());INSERT INTO dbname.t_table_info (id, name, age, sex, job, json_set, create_time, update_time) VALUES(2, '李四', 2, '男', '律師', '{"uuid":"123"}', '2021-10-13 23:28:38', now());INSERT INTO dbname.t_table_info (id, name, age, sex, job, json_set, create_time, update_time) VALUES(3, '王五', 3, '男', '醫生', '{"uuid":"123"}', '2021-10-20 23:30:56', now());INSERT INTO dbname.t_table_info (id, name, age, sex, job, json_set, create_time, update_time) VALUES(4, '趙六', 4, '女', '醫生', '{"uuid":"456"}', '2021-10-27 23:31:28', now());INSERT INTO dbname.t_table_info (id, name, age, sex, job, json_set, create_time, update_time) VALUES(5, '嘎嘎大王', 5, '女', '教師', '{"uuid":"456"}', '2021-11-21 21:58:17', now());INSERT INTO dbname.t_table_info (id, name, age, sex, job, json_set, create_time, update_time) VALUES(6, '呱呱大王', 6, '女', '教師', '{"uuid":"456"}', '2021-11-21 21:59:32', now());

-- user表相關----------------------------------------
-- 查看用戶表select * from mysql.`user`;
-- 創建用戶create user student identified by 'student';
-- 修改用戶名稱update mysql.`user` set user = 'student_v2' where user ='student' and Host = 'localhost';
-- 查看用戶權限show grants for 'student'@'%';

-- 選擇數據庫----------------------------------------
-- 選擇數據庫use dbname;
-- 查看表show tables;
-- 刷新權限變更flush privileges

-- 設計表結構----------------------------------------
select * from dbname.`t_table_info`;
-- 查看表結構desc dbname.`t_table_info`;
-- 添加列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;
-- 復制表結構create table dbname.`t_table_info_v2` like dbname.`t_table_info`;
-- 復制表數據insert into dbname.`t_table_info_v2` select * from dbname.`t_table_info`;
-- 重命名表rename table dbname.`t_table_info_v2` to dbname.`t_table_info_v3`;
-- 刪除表-- drop table dbname.`t_table_info_v3`;
-- 清空表-- truncate table dbname.`t_table_info_v3`;


-- 索引相關----------------------------------------
-- 查看索引show index from dbname.`t_table_info`;
-- 創建唯一索引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);
-- 刪除索引drop index index_name on dbname.`t_table_info`;


-- 增刪改查----------------------------------------
-- 插入數據insert into dbname.`t_table_info` (id, name, age, sex, job, json_set, create_time, update_time, new_col) values(floor(1 + (rand() * 100)), '呱呱大王', 6, '女', '教師', '{"uuid":"456"}', now(), now(), null);
-- 刪除數據delete from dbname.`t_table_info` where id in ('6');
-- 修改數據update dbname.`t_table_info` set name='呱呱大王', age=6, sex='女', job='教師', json_set='{"uuid":"456"}', create_time='2021-11-21 21:59:32', update_time=now() where id in ('6');
-- 查詢數據select * from dbname.`t_table_info`;
-- 查詢json:json_unquote() 去除雙銀行; json_extract() 提取json的keyselect json_unquote(json_extract(json_set,'$.uuid')) from dbname.`t_table_info`;
-- 批量刪除表:將結果復制出來並執行select concat('drop table if exists ', table_name, ';') from information_schema.tables where table_schema = 'dbname';

-- 條件查詢----------------------------------------
select * from dbname.`t_table_info`;
-- 選取指定的條數數據select * from dbname.`t_table_info` limit 5;
-- 查詢最新一條數據select * from dbname.`t_table_info` order by update_time desc limit 1;
-- 升降序排序select * from dbname.`t_table_info` order by update_time desc, id desc;
-- in查詢select * from dbname.`t_table_info` where id in ('1','5');
-- 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 '%律師%';
-- 分組查詢數據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;

-- 時間查詢----------------------------------------
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;

-- 連接查詢----------------------------------------
select * from dbname.`t_table_info`;
-- 內連接-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;
-- 外連接-左連接:以左表為基準,右表的列為NULLselect * from t_table_info a left join t_table_info_v2 b on a.id = b.id;
-- 外連接-右連接:以右表為基準,左表的列為NULLselect * 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;

-- 存儲過程----------------------------------------
-- 簡單查詢 --
select * 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();

-- 批量插入數據----------------------------------------
select * from dbname.`t_table_info`;
drop procedure if exists batch_data;create procedure batch_data()begindeclare 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();

-- 查詢數據庫表大小----------------------------------------
select * from dbname.`t_table_info`;
-- 查詢所有數據庫的表大小select table_name, concat(truncate(data_length/1024/1024,2),' mb') as data_size,concat(truncate(index_length/1024/1024,2),' mb') as index_sizefrom information_schema.tablesgroup by table_nameorder 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_sizefrom information_schema.tables where table_schema = 'dbname'group by table_nameorder by data_length desc;

-- 設置變量----------------------------------------
select * from dbname.`t_table_info`;
-- 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);

-- 系統變量----------------------------------------
-- 查看所有的會話變量show session variables;
-- 查看所有的全局變量show global variables;
-- 查看數據庫隔離級別show variables like '%isolation';select @@global.tx_isolation;
-- 查看MySQL版本show variables like 'version%';
-- 查看端口、目錄、數據存放目錄、服務器idselect @@port, @@basedir, @@datadir, @@server_id;


-- 時間函數----------------------------------------
-- 獲取當前日期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());

-- 其它函數----------------------------------------
-- 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`;

-- 隨機數----------------------------------------
-- 生成3位隨機數select ceiling(rand()*900+100);
-- 生成4位隨機數select ceiling(rand()*9000+100);
-- 生成100以內的隨機數select floor(1 + (rand() * 100));



文章推薦