#enum set 单选 多选的用法
create table person(
id int primary key auto_increment,
sex enum('男','女','保密'),
favor set('篮球','排球','足球','中国足球','台球')
)
insert into person (id,sex,favor) values (1,'男','篮球,足球');
insert into person (id,sex,favor) values (null,'男',19);
#时间类型
create table tab_time(
dt datetime,
d2 date,
t2 time,
y year,
ts timestamp
)
insert into tab_time(dt,d2,t2,y) values (
'2017-7-8 10:35:30','2017-7-8','10:35:30','2014')
insert into tab_time(dt,d2,t2,y) values (now(),now(),now(),'2015')
#主键索引 唯一索引
create table tab_shuxing(
id int auto_increment primary key,
username varchar(20) not null unique key,
password varchar(20) not null,
age int default 18,
email varchar(30) comment '电子邮件'
);
insert into tab_shuxing (username,password,email) values ('user2','aaaa','8745@');
create table tab_test(
id int primary key auto_increment not null,
username varchar(20) not null unique key comment '用户名',
age tinyint default 18 comment '年龄',
num1 float,
num2 decimal(4,2),
str1 char(4),
time datetime);
insert into tab_test(username,num1,num2,str1,time) value ('admin2','3.22','22.22','dddd',now());\
#添加外键索引
alter table xuesheng engine=InnoDB;\
表的引擎需要 innodb
create table table_class (
cid int primary key not null,
cname varchar(20) not null);
create table table_student(
sid int primary key not null,
sname varchar(20) not null,
cid int,
foreign key (cid) references table_class(cid)
);
insert into table_class(cid,cname) values (001,'一班');
insert into table_student(sid,sname,cid) values (1212,'llll',001);
#修改表 添加字段
alter table table_class add column bzr varchar(20);
#修改表 修改字段
alter table table_class change bzr banzhuren varchar(30);
#修改表 添加约束
alter table table_class add unique key (banzhuren);
#复制表结构
create table table_person like table_student;
