mysql之資料庫常用指令碼總結

2023-04-02 06:02:46

一、庫

新增庫

create database estore;
create database estore character set 'utf8mb4';

刪除庫

drop database estore;

修改庫

alter database estore character set 'utf8mb4';

查詢庫

show databases;檢視所有資料庫名
show create database estore;檢視庫編碼

使用庫

use estore;切換資料庫
select database();-- 查詢當前使用的資料庫名稱

二、表結構

2.1表

新增表

CREATE TABLE `t_user ` (
  `id` varchar(22) NOT NULL,
  `openid` varchar(50) NOT NULL COMMENT '微信opedid',
  `name` varchar(50) DEFAULT NULL COMMENT '使用者名稱稱',
  `phone` varchar(20) NOT NULL COMMENT '手機號碼',
  `province_code` varchar(10) DEFAULT NULL COMMENT '省份程式碼',
  `city_code` varchar(10) DEFAULT NULL COMMENT '城市程式碼',
  `county_code` varchar(10) DEFAULT NULL COMMENT '區縣程式碼',
  `card_type` varchar(5) DEFAULT NULL COMMENT '證件型別',
  `id_card` varchar(30) DEFAULT NULL COMMENT '身份證',
  `region` varchar(10) DEFAULT NULL COMMENT '地區',
  `user_id` varchar(22) DEFAULT '0' COMMENT '普通使用者表id',
  `is_homeowner` varchar(2) DEFAULT NULL COMMENT '是否有房',
  `insurance_code` varchar(10) DEFAULT NULL COMMENT '所屬保險公司code',
  `promo_code` varchar(50) DEFAULT NULL COMMENT '推廣碼',
  `create_time` datetime DEFAULT NULL,
  `create_user` varchar(22) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_user` varchar(22) DEFAULT NULL,
  `delete_flag` varchar(2) DEFAULT NULL COMMENT '刪除標識 1是 0 否',

  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_phone` (`phone`) USING BTREE COMMENT '微信使用者手機號，唯一索引',
  KEY `index_open_id` (`openid`) USING BTREE COMMENT 'opid普通索引，用於靜默登入等'
) COMMENT '資訊員表';

刪除表

DROP TABLE t_user ;

改表名、改編碼集、改儲存引擎

-- 改表名
rename table t_user to t_user_info;
-- 改編碼集
alter table t_user character set 'utf8mb4';
-- 改儲存引擎
alter table applicationdesk ENGINE = InnoDB;

查表

-- 查選中資料庫的所有表名
show tables;
-- 查表結構(表中所有欄位)
desc t_user;
show columns from t_user;-- 展示表的列詳情
-- 查建表資訊
show create table t_user;
show create table t_mdm_employee G;
-- 查詢所有表及其註釋
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='estore' ORDER BY table_name;
-- 查詢庫中所有欄位及其相關屬性，如註釋等
select table_name,column_name,column_type,column_comment from information_schema.columns where table_schema='custom_sd';

改索引

-- 刪除索引
DROP INDEX uniq_phone ON t_sd_messenger_info;

2.2欄位

新增欄位 - - add

-- 新增欄位(預設在最後一列追加)
ALTER TABLE t_user ADD name varchar(22)  DEFAULT NULL COMMENT '使用者名稱稱' ;
-- 在age欄位後面新增sex欄位：
alter table t_user  add sex tinyint not null COMMENT '使用者性別' after age ;
-- 在第一列新增sex欄位
alter table t_user  add sex tinyint not null first;

刪除欄位 - - drop

alter table t_user  drop name;

修改欄位 - - change 、modify

-- 修改欄位名 
alter table t_user change address address_level varchar(2)  DEFAULT NULL COMMENT '地區級別' ; 
-- 修改屬性
alter table t_user modify name varchar(50) not null;

三、資料記錄

新增資料

-- 插入三種方式

-- 1.僅對特定欄位進行賦值插入

INSERT INTO t_user ( id, code ) VALUES ( 888892, 'eee' );
-- 2.對所有欄位進行賦值插入
INSERT INTO `custom_sd`.`t_user `(`id`, `code`, `pid`, `name`, `delete_flag`, `create_time`, `create_user`, `update_time`, `update_user`, `standby_01`, `standby_02`, `standby_03`, `standby_04`, `standby_05`, `district_level1`) VALUES (245, '379999', '371755', '哈利路亞', 0, '2021-01-14 18:03:42', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '5');
-- 3.對所有欄位進行賦值插入（可不指定欄位）
INSERT INTO `custom_sd`.`t_user ` VALUES (888893, '379999', '371755', '哈利路亞', 0, '2021-01-14 18:03:42', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '5');

刪除資料

-- 根據條件刪除
delete from t_user where id = 888893;
-- 多表聯合刪除(無關聯記錄匹配時，不進行刪除，開發中需注意區分)
delete p,pp from t_a p, t_b pp where p.id = pp.PLAN_ID and pp.PRODUCT_ID = #{vo.id}
delete p,pp,e from t_a p, t_b pp,t_c e where p.id = pp.PLAN_ID and e.PLAN_ID = p.ID and pp.PRODUCT_ID = #{vo.id}

修改資料

-- 修改單個或多個欄位
update t_user set code = 'xxx', pid = 456 where id = 888892;
-- 修改某個值的部分字元
update t_role set PERMISSION = REPLACE(PERMISSION,'"角色設定"','"角色設定", "資訊員列表"') where name = 'xxx';

查詢資料(博大精深，暫不討論)

-- 查詢列名
SELECT
    GROUP_CONCAT( COLUMN_NAME SEPARATOR "," ) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_NAME = 't_order_ext2' and TABLE_SCHEMA = 'biz_channel';
    
-- 查詢列名，並顯示駝峰別名 (僅支援單個下劃線)
SELECT
    GROUP_CONCAT( CONCAT(COLUMN_NAME," ",if(LOCATE('_', "abc_bcd")>0,COLUMN_NAME,CONCAT(left(COLUMN_NAME,LOCATE('_', COLUMN_NAME)-1),UPPER(substring(COLUMN_NAME,LOCATE('_', COLUMN_NAME) + 1,1)),right(COLUMN_NAME,LENGTH(COLUMN_NAME) - LOCATE('_', COLUMN_NAME) - 1)))) SEPARATOR "," ) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_NAME = 't_order_ext2';
    
-- 查詢列名，並顯示駝峰別名 (可支援兩個下劃線)
-- 注意：有重複資料，需取後面，可使用關鍵字去重，但去重後預設進行了排序
SELECT GROUP_CONCAT(CONCAT(COLUMN_NAME," ",IF (LOCATE('_',COLUMN_NAME)=0,COLUMN_NAME,CONCAT(LEFT (COLUMN_NAME,LOCATE('_',COLUMN_NAME)-1),UPPER(substring(COLUMN_NAME,LOCATE('_',COLUMN_NAME)+1,1)),IF (LOCATE('_',RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1))=0,RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1),CONCAT(LEFT (RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1),LOCATE('_',RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1))-1),UPPER(substring(RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1),LOCATE('_',RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1))+1,1)),RIGHT (RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1),LENGTH(RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1))-LOCATE('_',RIGHT (COLUMN_NAME,LENGTH(COLUMN_NAME)-LOCATE('_',COLUMN_NAME)-1))-1)))))) SEPARATOR ",") FROM information_schema.COLUMNS WHERE TABLE_NAME='imgrecord';
            
-- 統計查詢 if使用方法 mybatis
  select
    <include refid="BaseSql"/>
    ,
    sum(if(u.is_entering = 0,1,0)) AS count,
    sum(if(u.is_entering = 1,1,0)) AS total,
    sum(if(u.is_entering = 1 and is_supplementary_data = 1,1,0))  AS countTotal
    from t_sd_info m
    left join t_su_info u on m.id= u.im_id
    where m.u_id = #{uId}