use demol
create table test(id int(11),name varchar(50));
desc test;
create table student(sno char(11) primary key,sname varchar(20) not null);
desc student;
create table course
-> (
-> cno varchar(20) primary key,
-> course_name varchar(50) not null,
-> cpno varchar(20),
-> course_credit decimal(4,1)
-> );
desc course;
show tables;
增加列: alter table student
-> add ssex char(2) not null
-> ;
为列设置默认值: alter table student
-> alter ssex set default '女'
-> ;
显示一下: desc student
-> ;
为表中字段设置默认值
alter table student
->alter ssex set default '男'
->;
给text表重命名为test2:
方法1: rename table test to test2;
方法2: alter table test2
-> rename to test;
更改student表中ssex的数据类型为枚举类型(‘male’,’female’),默认值为‘male’
方法:alter table student
-> modify ssex enum('male','female')not null;
//更改已有字段的类型
alter table student
-> alter ssex set default'male'
-> ;
为id列添加主键
alter table test
-> add primary key(id)
-> ;
显示course表:
完成sc表的创建:
create table sc(sno char(11),
-> cno varchar(20),
-> grade decimal(6,2)
-> );
显示一下sc表:
desc sc;
修改表sc,为其增加一个外键sno,让其参照表student:
alter table sc
-> add foreign key(sno) references student(sno);
显示一下sc表:
desc sc;
修改表sc,为其增加一个外键cno,让其参照表course:
alter table sc
-> add foreign key(cno) references course(cno);
显示一下sc:
desc sc;
修改sc 表,将sno,cno设置为组合主键:
alter table sc
-> add primary key(sno,cno);
显示一下sc:
desc sc;