测试用例

CREATE DATABASE db_test;
USE db_test;
CREATE TABLE `tbl_dept` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR(30) DEFAULT NULL,
  `locAdd` VARCHAR(40) DEFAULT NULL COMMENT '楼层',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_emp`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) DEFAULT NULL,
  `deptId` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_dept_id`(`deptId`)
  #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO tbl_dept(deptName,locAdd) VALUES ('RD',11), ('HR',12), ('MK',13), ('MIS',14), ('FD',15);
INSERT INTO tbl_emp(name,deptId) VALUES('z3',1), ('z4',1), ('z5',1);
INSERT INTO tbl_emp(name,deptId) VALUES('w5',2), ('w6',2);
INSERT INTO tbl_emp(name,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(name,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(name,deptId) VALUES('s9',51);  # 故意错的51

select d.id deptId ,d.deptName,d.locAdd,e.id empId,e.name,e.deptId 
  from tbl_dept d, tbl_emp e 
  where d.id = e.deptId;

去重查询

SELECT DISTINCT NAME FROM courses;

删除重复的保留最大的

DELETE from courses WHERE id NOT IN (
SELECT maxid from (SELECT max(id) maxid FROM courses GROUP BY Name) temp
)

添加新字段

ALTER TABLE <tablename> ADD <columnname> <type> ...;
ALTER TABLE hotel_brand ADD C_VIP SMALLINT(6) comment '提供专属优待';

修改字段约束

ALTER TABLE <tablename> MODIFY <columnname> <type> NULL;
ALTER TABLE sys_region MODIFY C_AREA_ID BIGINT(20) NULL;

修改表存储引擎

ALTER TABLE 表名 ENGINE=InnoDB;

批量更新

方式一：case when then....

UPDATE yoiurtable
        SET dingdan = CASE id 
            WHEN 1 THEN 3 
            WHEN 2 THEN 4 
            WHEN 3 THEN 5 
        END
    WHERE id IN (1,2,3)


UPDATE categories      
        SET dingdan = CASE id 
            WHEN 1 THEN 3 
            WHEN 2 THEN 4 
            WHEN 3 THEN 5 
        END, 
        title = CASE id 
            WHEN 1 THEN 'New Title 1'
            WHEN 2 THEN 'New Title 2'
            WHEN 3 THEN 'New Title 3'
        END
    WHERE id IN (1,2,3)

方式二：insert into ... on duplicate key update

如果列不能为空或者没有默认值也许会报错：Field XXX doesn't have a default value，
insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

方式三：replace into

这种方式只有替换的字段(示例中ID,C_NAME_PINYIN)有值，其余都会被清空掉
REPLACE INTO sys_region_continents(ID,C_NAME_PINYIN) VALUES (1,'a'),(2,'b')...;