mysql插入数据自增

create database stuDB;
/*更改数据库的编码格式*/
ALTER DATABASE stuDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use stuDB;


/*创建学生表*/
create table stuInfo
(
sno       varchar(10)  not null  primary key,
name      varchar(10)  not null,
sex  char(2)not null,
birthday  datetime not null,
phone     varchar(20)  not null,
address   varchar(50)  not null,
remark  text(500)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*更改表的编码格式*/
ALTER TABLE stuinfo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123001','张三','男','1990-12-3','13156785958','大学城');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123002','李四','男','1989-7-29','13156786744','大学城');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123003','王五','女','1972-1-31','13767545673','杨家坪');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123004','周六','女','1992-1-31','13767545673','杨家坪');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123005','周杰伦','男','1994-1-31','13767545173','杨家坪');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123006','王大爷','女','2002-1-31','13767545673','杨家坪');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123007','欧巴','女','2001-1-31','16767545673','杨家坪');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123008','路飞','女','1982-1-31','18767545673','陈家沟');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123009','黑崎一护','女','1992-1-31','15767545673','杨家坪');
insert into stuInfo(sno,name,sex,birthday,phone,address)
values('T123010','宇智波鼬','男','1993-2-1','15767545673','杨家坪');


/*创建课程表*/
create table course
(
cno    int not null primary key AUTO_INCREMENT,
cname  varchar(30) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*设置表的自增初始值从1开始*/
alter table course AUTO_INCREMENT=1;


/*更改表的编码格式*/
ALTER TABLE course DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


/*插入数据，一定要在表名后面跟上要插入的字段，不然不能自增*/
insert into course(cname) values('html');
insert into course(cname) values('stb');
insert into course(cname) values('c');
insert into course(cname) values('html');
insert into course(cname) values('sql');
insert into course(cname) values('c#');
insert into course(cname) values('java');


/*创建学生表*/
create table score
(
no   int not null primary key AUTO_INCREMENT,
sno  varchar(10)  not null references stuInfo(sno),
cno  int not null references course(cno),
cj   int not null check(cj>=0 and cj<=100) 
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


/*更改表的编码格式*/
ALTER TABLE score DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


/*插入数据，一定要在表名后面跟上要插入的字段，不然不能自增*/
insert into score(sno,cno,cj) values('T123001',1,89);
insert into score(sno,cno,cj) values('T123001',2,75);
insert into score(sno,cno,cj) values('T123001',3,60);
insert into score(sno,cno,cj) values('T123002',1,100);
insert into score(sno,cno,cj) values('T123002',3,50);
insert into score(sno,cno,cj) values('T123003',2,80);
insert into score(sno,cno,cj) values('T123003',3,70);
insert into score(sno,cno,cj) values('T123004',3,90);
insert into score(sno,cno,cj) values('T123005',2,72);
insert into score(sno,cno,cj) values('T123006',1,50);
insert into score(sno,cno,cj) values('T123015',2,50);