表管理和索引，外键作用：
创建数据库
create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci';
cd students
ls
file db.opt
cat db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci
修改数据库
help alter database;
help drop databases;
help create table;
创建表的3种方法：
1）直接定义一张空表；
2)从查询结果中创建新表
3）以其他表为模板创建一个空表 like关键字
单字段：
primary key 主键
unique key 唯一键
单或多字段：
primary key (col,...)
unique key (col,...)
index (col,...)
create table if not exists tb_name (col_name col_definition,constraint)
create table tb1 (id int unsigned not null auto_increment primary key,name char(20) not null,age tinyint not null)
id字段设置为主键
create table tb2 (id int unsigned not null auto_increment ,name char(20) not null,age tinyint not null,primary key(id))
主键，唯一键，索引字段
create table tb3 (id int unsigned not null auto_increment ,name char(20) not null,age tinyint not null,primary key(id),unique key(name),index(age)) engine = engine_name
主键 唯一键 索引键的却别：
键也称作约束，可用作索引，属于是特殊索引（有特殊限定）：B+Tree
create index
create table courses(cid tinyint unsigned not null auto_increment primary key,couse varchar(50) not null) engine = innodb;
show table status like 'courses';
show table status like 'courses'G
drop table courses;
insert into courses(couse) values('python'),('c++'),('mysql');
select * from courses;
show indexes from courses;
显示指定表的索引
show indexes from tb_name;
查询结果创建新表
create table testcourses select * from courses where cid <=2;
show tables;
select * from testcourses;
显示表结构
desc courses;
desc testcourses;
以其他表为模板创建一张空表
create table test like courses;
desc test;
修改表结构：alter table
help alter table
增删改 字段
增删改 索引
修改表属性
show indexes from test;
alter table test add unique key (couse); 添加唯一键索引
alter table test change couse course varchar(50) not null; 修改字段名称
alter table test add startdate date default '2020-01-01'; 增加字段
help alter table
alter table test rename as|to courses2; 修改表名称
rename table courses2 to test;
删除表
help drop table
drop table if exists tb_name;
create table student (sid int unsigned not null auto_increment primary key,name varchar(30),cid int not null);
insert into student (name,cid) values ('wqd',2),('zqd',1);
select * from student;
select * from courses;
select name,couse from student,courses where student.cid=courses.cid;
insert into student (name,cid) values('chenchen',5);
delete from student where cid =5;
添加外键
作用：学生表的cid和课程表的cid 引用外键约束，学生表里插入不存在的课程时 会报错
alter table student modify cid tinyint unsigned not null;
alter table student add foreign key foreign_cid(cid) references courses(cid);
show indexes from student;
修改表的引擎：
alter table courses engine = innodb;
alter table courses engine = mysiam;
insert into student (name,cid) values ('chenchen',3);
delete from courses where cid=3; 会报错
只有innodb引擎才支持外键
外键约束能够防止 表 被误删
索引：创建 和 修改
help create index ;
help drop index;
create index index_name on tb_name;
drop index index_name on tb_name;
create index name_on_student on student (name) using btree;
create index name_on_student on student (name(5) desc) using btree; 长度5字符 降序
drop index name_on_student on student;