mysql常用语法

2022-01-20 23:39:20

查看数据库连接

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)