mysql常用汇总

工作常用工具

索引

1. 创建索引

ALTER TABLE ecv_runnotice_user ADD INDEX IDX_USERNAME (username) USING BTREE;

修改字段

1. 添加字段及注释

alter table 表名 add COLUMN 字段名 类型长度 DEFAULT NULL COMMENT ‘注释内容’;
eg: alter table student add COLUMN sex varchar(2) DEFAULT '1' COMMENT ‘性别’;

2. 修改字段名

alter table 表名 rename A to B;

3. 修改字段类型

alter table 表名 modify column 字段名 数据类型;

4. 修改字段注释

ALTER TABLE 表名 MODIFY 字段名 数据类型 COMMENT ‘注释内容’;
eg:ALTER TABLE student MODIFY sex varchar(2) COMMENT ‘性别编码’;

5. 删除字段

alter table 表名 drop column 字段名;

小技巧

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

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

6. 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='李深龙');

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

8. 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`;
  

10. create table … select

需要快速备份表时通常分两步：

1. 创建一张临时表
   create table je_core_enduser_back like je_core_enduser;
2. 将数据插入临时表
   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;