MySQL必会知识点笔记—表操作

快速掌握MySQL必会知识点
数据库表操作
创建表
Create table 表名（列名 类型1，列名 类型2，….）;

Create table if not exists user(
User_id int primary key auto_increment,
User_name varchar(32),
Common varchar(64)
)
Engine = myisam
Charset=utf8;

删除表
Drop table 表名;
Drop tanle user;

查看表结构
Desc user;

查看建表语句
Show create table user;

修改表
修改列数据类型
Alter table 表名 modify 列名 数据类型；
Alter table 表名 modify 列名 数据类型 位置；

添加列
Alter table 表名 add 列名 数据类型；
Alter table 表名 add 列名 数据类型 位置；

修改列名和数据类型
Alter table 表名 change 旧列名 新列名 新数据类型；
修改表字段
Alter table user modify user_name varchar(64);

字段重命名(drop -> add)
Alter table user1 change user_name name varchar(64);

增加字段
Alter table user modify user add age int；

增加到指定位置after ，first
Alter table user1 add NO int after user_id;

删除字段
Alter table user drop age

修改表名
Alter table user rename user1；

修改引擎
Alter table user1 engine=innodb；

修改字符集
Alter table user1 charset=utf8

字段限制
Primary key
Not null
Unique
Default

删除列
Alter table 表名 drop 列名；
复制表
Create table 表名 select * from 原始表；

复制表结构
Create table 表名 like 原始表；

复制表记录
Insert into 表名 select * from 原始表；

复制一列
Inster into 表名 select 列 from 原始表；

主键
Select * from students where id=100;
定义：在多条记录中用于确定一条记录时使用的标识符
特点：非空 唯一
创建主键：
Create table 表名 （列名1 类型1 primary key，列名2 类型2 ，…）

Create table t1（
t_id int primary key auto_increment,
name varchar(32)
）;
Create table t2(
U_id varchar (64) primary key,
Name varchar(32)
);

复合主键(一个表中存在的)
创建；
Create table 表名（
列名1 类型1，
列名2 类型2，…
Primary key (列名1，列名2)）;

Create table t3(
Id int,
Name varchar(64),
Age int,
Primary key (id,name)
);
INSERT INTO course.t3(id,name,age) VALUES (1, ‘JACK’, 10)
INSERT INTO course.t3(id,name,age) VALUES (2, ‘JACK’, 10)

联合主键（多个表）
建表
学生表
Create table student（
Id int primary key auto_increment comment ‘学生表主键’，
Name varchar (32) comment ‘姓名’,
Age int comment ‘年龄’
）

课程表
Create table course(
Course_id int primary key auto_increment comment ‘课程表主键’，
Name varchar(32) comment ‘课程名称’,
)

学生课程表
Create table course_student(
Id int auto_increment primary key comment ‘学生课程ID’,
Student_id int comment ‘学生ID’,
Course_id int comment ‘课程ID’
);

超键，候选键(单表操作)
Create table student3(
Id int primary key auto_increment comment ‘学号’，
Name varchar (32) comment ‘姓名’,
Age int comment ‘年龄’,
Gender varchar (2) comment ‘性别’,
Birth_day date comment ‘生日’，
Id_num varchar(32) comment ‘身份证号’
);
超键 ：id，（id，name），（id，name，birth_day）
超键包含候选键和主键
Id_num
候选键：最小的超键（id，name，birth_day,）

外键：
学生表
Create table student3(
Id int primary key auto_increment comment ‘学号’，
Name varchar (32) comment ‘姓名’,
Age int comment ‘年龄’,
Gender varchar (2) comment ‘性别’,
Birth_day date comment ‘生日’，
Id_num varchar(32) comment ‘身份证号’
);

班级表
创建外键方式一
Creat table class1（
Id int auto_increment primary key，
Class_name varchar(64),
Student_id int references student3(id)
）;
方式二
Creat table class2（
Id int，
Class_name varchar(64),
Student_id int,
Primary key (id),
Foreign key (student_id) references student3(id)
）;
方式三：fk_子表_父表
Creat table class3（
Id int auto_increment，
Class_name varchar(64),
Student_id int,
Primary key (id),
Constraint fk_class_student3 foreign key (student_id) references student3(id)
）;

索引
语法
CREATE INDEX index_name on table_name (column_name)
创建：
Create index 索引名 on 表名 （列名）；
Alter table 表名 add index 索引名（列名）；
Create table 表名 （index [索引名]（列名（长度）））；

例子：
Creat table person(
Id int auto_increment primary key,
Name varchar(64),
Age int
);
加索引
Create index i_name on person (name);

方式二
语法
ALTER TABLE table_name ADD INDEX index_name (column_name)；
例子
Creat table person(
Id int auto_increment primary key,
Name varchar(64),
Age int
);
加索引
Alter table person1 add index i_name (name);

方式三
Create table person2(
Id int auto_increment primary key,
Name varchar(64),
Age int,
Index I_name (name)
);

删除索引
Drop index i_name on person1;

唯一索引
Alter table person1 add unique index i_name (name);允许有空值，之前的不允许
Create unique index i_name on person2 (name(32));
Create table person3(
Id int auto_increment primary key,
Name varchar(64),
Age int,
unique I_name (name)
);

全文索引
创建
方法一
Create table text(
Id int auto_increment primary key,
Content text not null,
Title varchar(64) not null,
Fulltext key ct(content,title)
);
方法二
Create fulltext index rn on student(name,remark);
方法三
Alter table student3 add fulltext index rn (name,id_num);

删除索引
Drop index rn on student3;
Alter table students drop index rn;

怎么看索引是否启用
Explain 会返回一条列表
Explain select * from students where id > 100;

显示索引
Show index from students;