SQL知识点--表与索引操作

SQL知识点--表与索引操作

前文回顾:

SQL知识点--插入记录

SQL知识点--更新记录、删除记录

表的创建、修改与删除

CREATE TABLE 语句

CREATE TABLE 语句用于创建数据库中的表

表的创建、修改与删除:

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 tb_name LIKE tb_name_old

  • 从另一张表的查询结果创建表: CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

  •  修改表ALTER TABLE 表名 修改选项 。选项集合:

{ ADD COLUMN <列名> <类型>  -- 增加列

 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型

 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值

 | MODIFY COLUMN <列名> <类型> -- 修改列类型

 | DROP COLUMN <列名> -- 删除列

 | RENAME TO <新表名> -- 修改表名

 | CHARACTER SET <字符集名> -- 修改字符集

 | COLLATE <校对规则名> } -- 修改校对规则(比较和排序时用到)

  • 删除表DROP TABLE [IF EXISTS] 表名1 [ ,表名2]

数据类型(data_type)规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型: 

数据类型 描述
  • integer(size)
  • int(size)
  • smallint(size)
  • tinyint(size)
仅容纳整数。在括号内规定数字的最大位数。
  • decimal(size,d)
  • numeric(size,d)

容纳带有小数的数字。

"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。

char(size)

容纳固定长度的字符串(可容纳字母、数字以及特殊字符)。

在括号中规定字符串的长度。

varchar(size)

容纳可变长度的字符串(可容纳字母、数字以及特殊的字符)。

在括号中规定字符串的最大长度。

date(yyyymmdd) 容纳日期。

1、现有一张用户信息表,其中包含多年来在平台注册过的用户信息,随着牛客平台的不断壮大,用户量飞速增长,为了高效地为高活跃用户提供服务,现需要将部分用户拆分出一张新表。

原来的用户信息表:

SQL知识点--表与索引操作

 作为数据分析师,请创建一张优质用户信息表user_info_vip,表结构和用户信息表一致。

SQL知识点--表与索引操作

create table if not exists user_info_vip 
(id int(11) primary key auto_increment comment "自增ID",
 uid int(11) UNIQUE not null comment"用户ID",
 nick_name varchar(64) comment"昵称",
 achievement int(11) default 0 comment"成就值",
 level int(11) comment"用户等级",
 job varchar(32) comment"职业方向",
 register_time datetime Default CURRENT_TIMESTAMP comment"注册时间"
) CHARACTER SET utf8 COLLATE utf8_general_ci;
  • 自增ID:AUTO_INCREMENT;
  • 设置主键:PRIMARY KEY;
  • 唯一性约束:UNIQUE
  • 非空约束:NOT NULL
  • 设置默认值:DEFAULT 0
  • 当前时间戳:CURRENT_TIMESTAMP
  • 评论/注释:COMMENT
  • 如果该表已创建过,正常返回:IF NOT EXISTS

2、请在用户信息表,字段level的后面增加一列最多可保存15个汉字的字段school;并将表中job列名改为profession,同时varchar字段长度变为10;achievement的默认值设置为0

alter table user_info add school varchar(15) after level;
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)
alter table user_info change job  profession varchar(10);
alter table user_info change 原列名 修改列名 修改数据类型
alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

3、 现有一张试卷作答记录表exam_record,其中包含多年来的用户作答试卷记录。一般每年都会为exam_record表建立一张备份表exam_record_{YEAR},{YEAR}为对应年份。现在随着数据越来越多,存储告急,请你把很久前的(2011到2014年)备份表都删掉(如果存在的话)。

DROP TABLE IF EXISTS exam_record_2011, exam_record_2012, exam_record_2013, exam_record_2014;

索引的创建、删除

索引创建、删除与使用:

  •  create方式创建索引:

CREATE

  [UNIQUE -- 唯一索引

  | FULLTEXT -- 全文索引

  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引

  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引 

  • alter方式创建索引

  • ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

  • drop方式删除索引DROP INDEX <索引名> ON <表名>

  • alter方式删除索引ALTER TABLE <表名> DROP INDEX <索引名>

  • 索引的使用:

    • 索引使用时满足最左前缀匹配原则,即对于组合索引(col1, col2),在不考虑引擎优化时,条件必须是col1在前col2在后,或者只使用col1,索引才会生效;
    • 索引不包含有NULL值的列
    • 一个查询只使用一次索引,where中如果使用了索引,order by就不会使用
    • like做字段比较时只有前缀确定时才会使用索引
    • 在列上进行运算后不会使用索引,如year(start_time)<2020不会使用start_time上的索引

1、现有一张试卷信息表examination_info,其中包含各种类型试卷的信息。为了对表更方便快捷地查询,需要在examination_info表创建以下索引,规则如下:在duration列创建普通索idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。

SQL知识点--表与索引操作

create INDEX idx_duration on examination_info(duration);
create unique INDEX uniq_idx_exam_id on examination_info(exam_id);
create fulltext INDEX full_idx_tag on examination_info(tag);
  • 在duration列创建普通索引idx_duration;
  • 在exam_id列创建唯一性索引uniq_idx_exam_id;
  • 在tag列创建全文索引full_idx_tag;

2、请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

方法一:
drop index uniq_idx_exam_id on examination_info ;
drop  index full_idx_tag  on examination_info ;
方法二:
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;
  • 删除examination_info表上的唯一索引uniq_idx_exam_id;
  • 删除全文索引full_idx_tag;

SQL知识点--表与索引操作

上一篇:F - Two Exam(AtCoder Beginner Contest 238)


下一篇:目标检测的算法