工作常用工具
索引
- 创建索引
ALTER TABLE ecv_runnotice_user ADD INDEX IDX_USERNAME (username) USING BTREE;
修改字段
- 添加字段及注释
alter table 表名 add COLUMN 字段名 类型长度 DEFAULT NULL COMMENT ‘注释内容’;
eg: alter table student add COLUMN sex varchar(2) DEFAULT '1' COMMENT ‘性别’;
- 修改字段名
alter table 表名 rename A to B;
- 修改字段类型
alter table 表名 modify column 字段名 数据类型;
- 修改字段注释
ALTER TABLE 表名 MODIFY 字段名 数据类型 COMMENT ‘注释内容’;
eg:ALTER TABLE student MODIFY sex varchar(2) COMMENT ‘性别编码’;
- 删除字段
alter table 表名 drop column 字段名;
小技巧
- group_concat
把确认人相同的姓名拼接在一起
SELECT
TWTZ_QRR, group_concat( TWTZ_XM )
FROM
yk_twtz
GROUP BY
TWTZ_QRR;
2. char_length
需要获取字符的长度
SELECT
distinct USERNAME
FROM
je_core_enduser
WHERE
USERNAME LIKE '张%'
ORDER BY
char_length( USERNAME ) ASC;
3. locate
查找某个字符串中的位置,除此之外,还有instr和position函数,它们的功能跟locate函数类似
SELECT
USERNAME
FROM
je_core_enduser
WHERE
locate( '李深龙', USERNAME ) = 1;
4. replace
替换字符串中部分内容
update je_core_enduser set USERNAME = REPLACE(USERNAME,'李深龙','李生龙') where id = 'ecdgd334521erdf4546';
- insert into … select
插入数据
INSERT INTO `je_core_enduser` ( `id`, `code`, `USERNAME`, `edit_date` )
SELECT ID,
CODE,
USERNAME,
now()
FROM
je_core_enduser_back
WHERE
CODE IN ( '004', '005' );
- insert into … ignore
插入时防止出现重复数据
方法一:
INSERT ignore INTO `je_core_enduser`(`id`, `code`, `USERNAME`, `edit_date`)
VALUES ('FEWTCGWER4TRY243GFH', '108', '李深龙', now));
方法二:
INSERT INTO `je_core_enduser`(`id`, `code`, `USERNAME`, `edit_date`)
select 'FEWTCGWER4TRY243GFH','108', '李深龙',now(3)
from dual where not exists (select * from `je_core_enduser` where USERNAME='李深龙');
- select … for update
是为了在查询时,避免其他用户以该表进行插入,修改或删除等操作,造成表的不一致性
set autocommit=0;
//设置完autocommit后,我们就可以执行我们的正常业务了。具体如下:
//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;
- on duplicate key update
插入数据之前判断,如果主键或唯一索引不存在,则插入数据。如果主键或唯一索引存在,则执行更新操作
INSERT INTO `je_core_enduser` (`USERID`, `USERCODE`, `USERNAME`, `JZSJ` )
VALUES
(
'WEDSGF5465768723DSFVB',
'13387690876',
'ZHANGSAN',
now())
ON DUPLICATE KEY UPDATE USERID = '23',
USERNAME = 'LISI',
JZSJ = now();
-
查看表情况
- 快速查看某张表的字段情况
desc 表名;
esc je_core_enduser;
- 快速查看某张表的索引情况
show index from 表名;
show index from `je_core_enduser`;
- 查看建表情况
show create table 表名;
show create table `je_core_enduser`;
- create table … select
需要快速备份表时通常分两步:
- 创建一张临时表
create table je_core_enduser_back like
je_core_enduser;
- 将数据插入临时表
insert into je_core_enduser_back select * from
je_core_enduser;
而 create table … select 能实现上面这两步的功能
create table je_core_enduser_back select * from
je_core_enduser;