文章目录
表的创建、修改与删除
表的创建
直接创建表
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] -- 字符集编码,例:CHARACTER SET utf-8 或 default charset=utf8
[COLLATE collate_value] -- 列排序和比较时的规则(是否区分大小写等)
注意:“|”表示 或,[…|…|…|…]的内容顺序随便。
题目:
https://www.nowcoder.com/practice/a61ee5519d14444aa99e530309a8e043?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
create table if not exists user_info_vip(
id int primary key auto_increment comment "自增ID", # 由于id是自增的,不会为NULL,所以即便题中有要求也不要写NOT NULL.
# id int not null primary key auto_increment comment "自增ID",
uid int not null unique 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
从另一张表复制其表结构来创建表
CREATE TABLE tb_name LIKE tb_name_old
从另一张表的查询结果来创建表
CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE option
表的修改
ALTER TABLE 表名 修改选项 。
选项集合:
{ ADD COLUMN <列名> <类型> [after|first 新列名] -- 增加列
| CHANGE <旧列名> <新列名> <新列类型> -- 修改列名或类型
| MODIFY COLUMN <列名> <类型> [新约束] -- 修改列类型或增加约束
| DROP COLUMN <列名> -- 删除列
| RENAME [TO] <新表名> -- 修改表名
| CHARACTER SET <字符集名> -- 修改字符集
| COLLATE <校对规则名> } -- 修改校对规则(比较和排序时用到)
题目:
https://www.nowcoder.com/practice/d08209df6f464cebafda5dfd5de03fce?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1:
alter table user_info add column school varchar(15) after level;
alter table user_info change column job profession varchar(10);
alter table user_info modify column achievement int(11) default 0;
法2:
alter table user_info
add school varchar(15) after level,
change job profession varchar(10),
# modify column achievement int(11) default 0;
change achievement achievement int(11) default 0; # 用change也可以
注意:
1.对某列修改或添加默认值必须先写上该列的类型
。
2.列名前不加“column”也OK。
表的删除
DROP TABLE [IF EXISTS] 表名1 [ ,表名2]
题:https://www.nowcoder.com/practice/df2f634a53324bbd9369fe6723fedc49?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
drop table if exists exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014
或
drop table if exists exam_record_2011;
drop table if exists exam_record_2012;
drop table if exists exam_record_2013;
drop table if exists exam_record_2014;
表的索引的创建、删除
索引的创建
create方式创建索引:
CREATE
[UNIQUE -- 唯一索引
| FULLTEXT -- 全文索引
]
INDEX index_name ON table_name -- 默认是 普通索引
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引
主键索引: 它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候指定了主键,就会创建主键索引, CREATE INDEX不能用来创建主键索引.
唯一索引: 与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须一。
普通索引: 这是最基本的索引,它没有任何限制。
全文索引: FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR, VARCHAR和 TEXT列。
alter方式创建索引:
ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] INDEX index_name(column_name)
题目:
https://www.nowcoder.com/practice/f2ea9ccf33c740d58576608940981807?tpId=240&tqId=2223570&ru=/practice/f2ea9ccf33c740d58576608940981807&qru=/ta/sql-advanced/question-ranking
法1:用create方式
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); # 全文索引
法2:用alter方式
alter table examination_info
add index idx_duration(duration),
add unique index uniq_idx_exam_id(exam_id),
add fulltext index full_idx_tag(tag);
索引的删除
drop方式删除索引:
DROP INDEX <索引名> ON <表名>
alter方式删除索引:
ALTER TABLE <表名> DROP INDEX <索引名> [,DROP INDEX <索引名>,....]
题目
https://www.nowcoder.com/practice/4963f6d63dde48d787aaa2b43460fb4b?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
法1:drop方式
drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;
法2:alter方式,注意它有2种
alter table examination_info
drop index uniq_idx_exam_id,
drop index full_idx_tag;
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;
索引使用的注意点
- 索引使用时满足最左前缀匹配原则,即对于组合索引(col1, col2),在不考虑引擎优化时,条件必须是col1在前col2在后,或者只使用col1,索引才会生效;
- 索引不包含有NULL值的列
- 一个查询只使用一次索引,where中如果使用了索引,order by就不会使用
- like做字段比较时只有前缀确定时才会使用索引
- 在列上进行运算后不会使用索引,如year(start_time)<2020不会使用start_time上的索引