1

2

create database estore;

create database estore character set 'utf8mb4';

1

drop database estore;

1

alter database estore character set 'utf8mb4';

1

2

show databases;查看所有数据库名

show create database estore;查看库编码

1

2

use estore;切换数据库

select database();-- 查询当前使用的数据库名称

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

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 '信息员表';

1

DROP TABLE t_user ;

1

2

3

4

5

6

-- 改表名

rename table t_user to t_user_info;

-- 改编码集

alter table t_user character set 'utf8mb4';

-- 改存储引擎

alter table applicationdesk ENGINE = InnoDB;

1

2

3

4

5

6

7

8

9

10

11

12

-- 查选中数据库的所有表名

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';

1

2

-- 删除索引

DROP INDEX uniq_phone ON t_sd_messenger_info;

1

2

3

4

5

6

-- 新增字段(默认在最后一列追加)

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;　　

1

alter table t_user  drop name;

1

2

3

4

-- 修改字段名 

alter table t_user change address address_level varchar(2)  DEFAULT NULL COMMENT '地区级别' ; 

-- 修改属性

alter table t_user modify name varchar(50) not null;

1

2

3

4

5

6

7

8

9

-- 插入三种方式

-- 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');

1

2

3

4

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}

1

2

3

4

-- 修改单个或多个字段

update t_user set code = 'xxx', pid = 456 where id = 888892;

-- 修改某个值的部分字符

update t_role set PERMISSION = REPLACE(PERMISSION,'\"角色设置\"','\"角色设置\", \"信息员列表\"') where name = 'xxx';

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

-- 查询列名

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}