mysql 常用语句记录

索引

-- 修改字段名
alter table TABLE_NAME rename column A to B;
 
-- 修改字段类型(版本)
alter table TABLE_NAME alter column COLUMN_NAME COLUMN_TYPE not null ;
alter table TABLE_NAME modify column COLUMN_NAME COLUMN_TYPE not null ;
 
-- 添加字段类型
alter table TABLE_NAME add COLUMN COLUMN_NAME1 COLUMN_TYPE default null comment '' after COLUMN_NAME2;

-- 删除字段类型
ALTER TABLE TABLE_NAME DROP COLUMN;


-- 创建索引
1.PRIMARY  KEY（主键索引）
  ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
2.UNIQUE(唯一索引)
  ALTER  TABLE  `table_name`  ADD  UNIQUE  (`column` ) 
3.INDEX(普通索引)
  ALTER  TABLE  `table_name`  ADD  INDEX  index_name (  `column`  )
4.FULLTEXT(全文索引)
  ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
  ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

-- 删除索引
1.PRIMARY  KEY（主键索引）
  ALTER  TABLE  `table_name`  DROP PRIMARY KEY 
2.INDEX(普通索引)或UNIQUE(唯一索引)
  ALTER  TABLE  `table_name`  DROP INDEX index_name
  DROP INDEX index_name ON `talbe_name`

-- 查看索引
1 show index from table_name;

行转列

SELECT supplier_name,
MAX(CASE confirmation WHEN '0' THEN price ELSE 0 END) as '未确认',
MAX(CASE confirmation WHEN '1' THEN price ELSE 0 END) as '已确认',
MAX(CASE confirmation WHEN '2' THEN price ELSE 0 END) as '未确认'
FROM price_detail GROUP BY supplier_name;

列转行

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.6.26-log 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `test` (
	`a` varchar (33),
	`b` varchar (33),
	`c` varchar (33),
	`d` varchar (33),
	`e` varchar (33),
	`date` varchar (33)
); 
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-04-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-03-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-02-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-01-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-05-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-06-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-07-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-08-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-09-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-10-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-11-01');
insert into `test` (`a`, `b`, `c`, `d`, `e`, `date`) values('1','2','3','4','5','2018-12-01');






/*mysql 查询统计示例SQL*/
SELECT ttt.fei, SUM(CASE ttt.date WHEN '2018-01-01' THEN ttt.val ELSE 0 END) 1月
, SUM(CASE DATE WHEN '2018-02-01' THEN ttt.val ELSE 0 END)2月
, SUM(CASE DATE WHEN '2018-03-01' THEN ttt.val ELSE 0 END)3月
, SUM(CASE DATE WHEN '2018-04-01' THEN ttt.val ELSE 0 END)4月
FROM 

(SELECT DATE, 'sa' fei, a AS val FROM test 
UNION 
SELECT DATE, 'sb' fei, b AS val FROM test 
UNION 
SELECT DATE, 'sc' fei, c AS val FROM test 
UNION
SELECT DATE, 'sd' fei, d AS val FROM test 
UNION
SELECT DATE, 'se' fei, e AS val FROM test ) ttt

GROUP BY ttt.fei
 
 

存在就update，不存在就insert：会根据主键或者唯一索引来校验是更新还是插入，只更新update之后的值

原值为空update，不存在就insert
INSERT IGNORE INTO `tableName` ( `id`, `field_1` )
VALUES
	( '1', '100' ),
	( '2', '200' ) 
	ON DUPLICATE KEY UPDATE field_1 =
IF
	( tableName.field_1, tableName.field_1, VALUES ( field_1 ) )


新值大于旧值update，不存在就insert
INSERT IGNORE INTO `tableName` ( `id`, `field_1` )
VALUES
	( '1', '100' ),
	( '2', '200' ) 
	ON DUPLICATE KEY UPDATE field_1 =
IF
	( VALUES ( field ) > tableName.field_1, VALUES ( field_1 ), tableName.field_1 )


存在就update，不存在就insert
INSERT IGNORE INTO `tableName` ( `id`, `field_1` )
VALUES
	( '1', '100' ),
	( '2', '200' ) 
	ON DUPLICATE KEY UPDATE field_1 = VALUES ( field_1 )

存在就忽略，不存在就插入:会根据主键或者唯一索引来校验是否忽略

INSERT IGNORE INTO `tableName` ( `id`, `field_1` )
VALUES
	( '1', '100' ),
	( '2', '200' ) ;

存在就替换，不存在就插入:会根据主键或者唯一索引来校验是替换还是插入

replace INTO `tableName` ( `id`, `field_1` )
VALUES
	( '1', '100' ),
	( '2', '200' ) ;