SQL基础

创建表

CREATE TABLE
IF NOT EXISTS tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

例如要创建以下表结构

CREATE TABLE IF NOT EXISTS user_info_vip (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    nick_name varchar(64) COMMENT '昵称',
    achievement int DEFAULT 0 COMMENT '成就值',
    `level` int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
) CHARACTER SET utf8 COLLATE utf8_general_ci;

创建表的另外两种方式：
  从另一张表复制表结构创建表：CREATE TABLE tb_name LIKE tb_name_old
  从另一张表的查询结果创建表：CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

修改表

在某列之后增加列

alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)
例如在user_info表中的level字段后添加school字段：alter table user_info add school varchar(15) after level;

更换列的名称及数据类型

alter table user_info change 原列名 新列名 修改数据类型
例如将job字段修改为profession字段：alter table user_info change job profession varchar(10);

更改数据类型

alter table 表名 modify 修改列名称 数据类型 默认值等
alter table user_info modify achievement int(11) default 0;

增

  新插入整行 insert into 表名 values()
  指定所要插入数据的列 insert into 表名(列1, 列2,…) VALUES (值1, 值2,…)

删

  delete from 表名 where 列名称 = 值
  删除表 truncate table 表名;
  delete from 表名;
  drop table if exists 表名;

改

update 表名称 set 列名称 = 新值 where 列名称 = 某值
更改多列 update 表名称 set 列1 = 新值, 列2 = 新值 where 列名称 = 某值

创建索引

cteate方式创建索引

CREATE 
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引  

alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_name(列名)

删除索引

drop方式删除索引：DROP INDEX <索引名> ON <表名>
alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

在duration列创建普通索引idx_duration

CREATE INDEX idx_duration ON examination_info(duration);
ALTER TABLE examination_info ADD index idx_duration(duration);

在exam_id列创建唯一性索引uniq_idx_exam_id

CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
ALTER TABLE examination_info ADD unique uniq_idx_exam_id(exam_id);

在tag列创建全文索引full_idx_tag

CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);
ALTER TABLE examination_info ADD Fulltext full_idx_tag(tag);

DROP、TRUNCATE 和DELETE 的区别

  1.DROP TABLE　清除数据并且销毁表，是一种数据库定义语言(DDL Data Definition Language), 执行后不能撤销，被删除表格的关系，索引，权限等等都会被永久删除。
  2.TRUNCATE TABLE　只清除数据，保留表结构，列，权限，索引，视图，关系等等，相当于清零数据，是一种数据库定义语言(DDL Data Definition Language)，执行后不能撤销。
  3.DELETE TABLE　删除（符合某些条件的）数据，是一种数据操纵语言(DML Data Manipulation Language)，执行后可以撤销（在事务中delete可以回滚）