查看数据库连接
select * from information_schema.PROCESSLIST;
查询当前库中表是否存在
select count(*) from information_schema.tables where TABLE_SCHEMA=database() and table_name ='bp_import_tb';
查看表结构的关键信息
SELECT t.column_name, t.column_comment, t.ordinal_position, t.column_type FROM information_schema.COLUMNS t
WHERE table_name = 'bu_data_tb' order by t.ordinal_position;
查看库使用空间
select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables group by TABLE_SCHEMA order by data_size desc;
查看具体表的索引
show index from bp_index_tb;
查看有索引的表
SELECT distinct table_name, index_name FROM mysql.innodb_index_stats WHERE database_name = database() order by table_name, index_name;
通过系统表查询记录总数
select table_name,table_rows from information_schema.tables
where TABLE_SCHEMA = database() order by table_name asc;
判断subject_no是否全为数字
SELECT * FROM t_major_catalog where length(0+subject_no)=length(subject_no);
判断sub_subject_name字段是否为中文
SELECT * FROM t_major_catalog WHERE NOT (sub_subject_name REGEXP "[u0391-uFFE5]");
判断sub_subject_name字段是否包含数字
SELECT * FROM tb_temp where sub_subject_name regexp '[0-9]'
修改列名
alter table t_tmp_qtj_xls你好 change field0 aaa varchar(1024)
已有数据表中添加自增长qtj_id
alter table t_tmp_qtj_xls你好 add qtj_id int
alter table t_tmp_qtj_xls你好 change qtj_id qtj_id int not null auto_increment primary key;
查询uuid
select uuid();
select replace(uuid(), '-', '');
大小写转换
SELECT LOWER('MySql');
SELECT UPPER('MySql');
关联修改
update t_major_catalog a, tb_temp b set a.sub_subject_name = b.sub_subject_name
where a.sub_subject_no=b.sub_subject_no
main_major_name右取总长度-2
update t_main_major set main_major_name = substr(main_major_name,3)
将长度为5的前补零
update t_subject set subject_no = concat('0', subject_no) where length(subject_no)=5
mysql 取日期和时间
select now(), curdate(),curtime(),left(sysdate(),10), right(sysdate(),8)
varchar转int
select field1,cast(field1 as SIGNED INTEGER) from t_test order by cast(field1 as SIGNED INTEGER)
有就不插入
insert into sc_field_append_tb (table_name, field_name, insert_date, insert_time )
select 'bu_data_rsda_tb', 'category_no', curdate(), curtime() from
DUAL where not exists (select * from sc_field_append_tb where table_name = 'bu_data_rsda_tb' and field_name = 'category_no')
表连接，没有设置为0
select concat(en_id, ' ', a.category_name), IFNULL(b.cnt,0) from sc_qtj_rsda_define_tb a left
join (select big_category, count(*) cnt from bu_qtj_xml_category_tb
where xml_md5 = '5dfafc20e8391023da30c1329ca956aa' group by big_category) b
on a.category_id=b.big_category order by a.category_id
将order_sub_no字段中的左边的-符号去除
update bu_temp_rsda_catalogue_tb set order_sub_no = trim(leading '-' from order_sub_no)
where archive_unique_code = '2401121032';
创建方法
DROP FUNCTION IF EXISTS func_import_excel;
delimiter ;;
CREATE DEFINER=root@localhost FUNCTION func_import_excel() RETURNS int(11)
BEGIN
insert into bu_import_tb(guid_,insert_date, insert_time, name, id_card, old_archive_no, now_archive_no,
sex, key_no, archive_time, work_company, phone, archive_status, graduate_time, graduate_school, highest_degree)select guid_,curdate(), curtime(), 姓名, 身份证号, 原档案号, 档案号, 性别, 唯一码, 存档时间,
工作单位, 手机号码, 档案状态, 毕业时间, 毕业学校, 最高学历 from t_tmp_import where guid_ not in
(select guid_ from bu_import_tb);
RETURN 0;END
;;
delimiter ;
使用方法
select func_import_excel()
创建存储过程
delimiter ;;
CREATE PROCEDURE proc_import_excel()
BEGIN
insert into bu_import_tb(guid_,insert_date, insert_time, name, id_card, old_archive_no, now_archive_no,
sex, key_no, archive_time, work_company, phone, archive_status, graduate_time, graduate_school, highest_degree)select guid_,curdate(), curtime(), 姓名, 身份证号, 原档案号, 档案号, 性别, 唯一码, 存档时间,
工作单位, 手机号码, 档案状态, 毕业时间, 毕业学校, 最高学历 from t_tmp_import where guid_ not in
(select guid_ from bu_import_tb);END
;;
delimiter ;
使用存储过程
call proc_import_excel()
