常用SQL汇总

MYSQL

建表

CREATE TABLE student (
  id bigint not null primary key auto_increment COMMENT '自增主键ID',
  name varchar(50) COMMENT '名称',
  vdf1 varchar(50) COMMENT '预留字段1',
  vdf2 varchar(50) COMMENT '预留字段2',
  vdf3 varchar(50) COMMENT '预留字段3',
  vdf4 varchar(50) COMMENT '预留字段4',
  vdf5 varchar(50) COMMENT '预留字段5',
  create_time datetime COMMENT '创建时间',
  update_time datetime COMMENT '修改时间',
  is_enable int COMMENT '1有效 0禁用，禁用账号无法登录',
  is_delete int COMMENT '1有效 0逻辑删除，删除账号无法登录'
) COMMENT='表注释'

1.唯一约束（联合主键）

新增时：

CREATE TABLE students (
    id int NOT NULL, 
    lastName varchar (255) NOT NULL, 
    firstName varchar (255), 
    city varchar (255), 
    UNIQUE (id,lastName) 
)
修改时：
ALTER TABLE students ADD CONSTRAINT unique_name UNIQUE (id,lastName)

删除时:

ALTER TABLE UserInfo DROP INDEX unique_name 

2.新增字段

alter table students add column new_id varchar(50) DEFAULT NULL COMMENT '新字段id'

3.修改字段

-- 修改一个字段的类型
alter table students MODIFY new_id int
-- 修改一个字段的名称，此时一定要重新指定该字段的类型
alter table students CHANGE new_id new_id2 varchar(10)

4.删除字段

alter table students DROP COLUMN new_id2

5.添加注释，修改注释

-- 可以为表添加注释
ALTER TABLE `students` COMMENT'注释'; 
-- 为字段添加/修改注释
ALTER TABLE students MODIFY COLUMN new_id varchar(50) COMMENT '新id'

6.mybatis批量新增或修改：on duplicate key update

注：会根据id以及唯一约束，判断是新增或者修改

       修改时，自增主键不变，下一次新增的 id + 1

<insert id="insertList" parameterType="java.util.List">
    insert into T_BUS_STATION (STATION_ID, NAME,
      LINE_ID, SERIAL_NUMBER, LONGITUDE,
      LATITUDE, LINE_DIRECTION, SOURCE_ID,
      UPDATE_TIME) values
    <foreach collection="list" item="item" separator=",">
      (#{item.siteId,jdbcType=VARCHAR}, #{item.siteLevelName,jdbcType=VARCHAR},
      #{item.lineId,jdbcType=VARCHAR}, #{item.seq,jdbcType=SMALLINT}, #{item.siteLongitude,jdbcType=DECIMAL},
      #{item.siteDimension,jdbcType=DECIMAL}, #{item.lineDirection,jdbcType=TINYINT}, #{item.sourceId,jdbcType=BIGINT},
      #{item.updateTime,jdbcType=TIMESTAMP})
    </foreach>
    on duplicate key update
      STATION_ID = VALUES(STATION_ID),
      NAME = VALUES(NAME),
      LONGITUDE = VALUES(LONGITUDE),
      LATITUDE = VALUES(LATITUDE),
      UPDATE_TIME = VALUES(UPDATE_TIME)
  </insert>