MySQL心得总结<一>:

1、场景：查询一张表，并把查询结果insert到另一张表中(数据迁移)

insert into table1 (col1.col2,col3,col4,col5) select (col1.col2,col3,col4,col5) from table2

注意：各个字段的值要一一对应

2、场景：修改表结构为表增加一列

alter table student add age int (11) default 0 not null COMMENT ‘年龄’

3、场景：表连接时，添加数据条件，筛选子表数据。

举例：table1 table2 表连接时值只保留table2中is_delete=0(没有删除)的数据；

table1 left join table2 on table1.id = table2.pid and table2.is_delete=0

4、group by 进行分组后，查询的字段只能是分组字段，那么需要其他字段怎么办?
使用聚合函数包一下：max(emp_name)

5、向一张表中插入一条数据，如果存在的话，就update(一条SQl,与数据库只有一次交互，效率高)需要创建唯一索引(可以为多个字段建立唯一索引)

方案一：需要为表增加一个唯一索引 创建唯一索引方式：

唯一索引:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

5.1、创建表时增加

CREATE TABLE `IdCard` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `number` VARCHAR(10)  NOT NULL COMMENT '身份证号',
    `born_Number` VARCHAR(10)  NOT NULL COMMENT '出生编号',
    `name` VARCHAR(10) NOT NULL  COMMENT '名字',
    `addr`ARCHAR(20) NOT NULL,
    `birthday` VARCHAR(20) NOT NULL,
    `gmt_modified` datetime NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_IdCard_number` (`number`,`born_Number`))

5.2、添加唯一索引:

CREATE UNIQUE INDEX indexName ON tableName(tableColumns)

5.3、修改表结构添加唯一索引:

ALTER TABLE table_name ADD UNIQUE ON (tableColumns)

5.4：一条SQL实现插入或修改；

INSERT INTO IdCard (`number`,`born_Number` 
,`name`,`birthday`,`gmt_modified`)VALUES(:number,:born_Number,:name,:birthday
,:gmtModified)
ON DUPLICATE KEY UPDATE `name`=:name,`gmt_modified`=NOW()

SQL分析：IdCard表有number和born_Number的组合唯一索引，出现重复后会执行UPDATE操作，修改name， modify_empid，gmt_modified

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行

6、场景升级：查询一张表,sum(col)出合计，再重新插入改表中，如果存在改条合计数据，则update，否则insert。

创建一张表：

CREATE TABLE `income_money` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `product_id` BIGINT(20) NOT NULL COMMENT '产品id',
    `month` VARCHAR(10) NULL DEFAULT NULL COMMENT '月份'
    `money` BIGINT(20) NULL DEFAULT NULL COMMENT '每月收入',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_income_money_product_id` (`project_id`,`month`)
)

统计产品的年收入，并插入一条产品年收入数据，如果该数据已经存在则修改:

CREATE TABLE `income_money` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `product_id` BIGINT(20) NOT NULL COMMENT '产品id',
    `income_type` VARCHAR(20) NOT NULL COMMENT 
    '类型(incomeMonth:月 incomeYear:年)',
    `month` VARCHAR(10) NULL DEFAULT NULL COMMENT '月份'
    `money` BIGINT(20) NULL DEFAULT NULL COMMENT '收入',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_income_money` (`project_id`,`income_type`,`month`)
)

INSERT INTO income_money (`project_id`,`income_type`,`month`,`money`)
SELECT tmp.project_id,tmp.income_type,tmp.month,tmp.money
FROM (SELECT `project_id`,'incomeYear' AS income_type, :month AS month, SUM(`money`) AS money 
FROM income_money
WHERE project_id=:projectId AND income_type<>'all'
GROUP BY project_id,`month`) tmp
ON DUPLICATE KEY UPDATE `month`=tmp.month,`gaap`=tmp.money