首页 > 滚动 > > 内容页

mysql之数据库常用脚本总结

2023-04-03 10:10:47 来源:脚本之家 分享到:


(资料图片仅供参考)

目录
一、库新增库删除库修改库查询库使用库二、表结构2.1表2.2字段三、数据记录新增数据删除数据修改数据查询数据(博大精深,暂不讨论)总结

前言:统计维度 - - 库、表结构、数据记录

一、库

新增库

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
    
    ,
    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}

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。

标签:
x 广告
x 广告

Copyright ©  2015-2022 全球科普网版权所有  备案号:豫ICP备20009784号-11   联系邮箱:85 18 07 48 3@qq.com