mysql插入数据自增

最新推荐文章于 2024-07-30 03:49:45 发布

cherishSpring

最新推荐文章于 2024-07-30 03:49:45 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： # mysql 文章标签： mysql 编码 utf-8

本文链接：https://blog.youkuaiyun.com/cherishSpring/article/details/51747188

mysql 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一个学生信息管理系统的设计与实现过程，包括创建数据库、学生表、课程表及成绩表，并进行了数据填充。通过SQL语句实现了数据的存储与管理。

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);