MYSQL数据的增删改查

实验内容：

创建数据库expr03

切换当前数据库

创建学生表student，插入6条记录

创建分数表score，插入10条记录

更新分数表score中第8条记录分数为84

更新分数表score中第10条记录为906，计算机，99

更新分数表score中前3条记录分数均为100

删除分数表中前3条记录

删除分数表中第7条记录

删除分数表后，再次添加数据，使其score_id自增从1开始

#创建expr03数据库
create database expr03;
#查看数据库建立情况
show databases;
#进入expr03数据库
use expr03;
#创建数据表student
create table student(
	stu_id int unsigned primary key comment'学号',
	stu_name varchar(20) not null comment'姓名',
	sex varchar(2) comment'性别',
	brith year comment'出生年份',
	department varchar(20) not null comment'院系',
	addr varchar(50) comment'家庭住址'
	)character set utf8 collate utf8_bin;
#查看创建情况
show tables;
#插入6个信息
insert into student (stu_id,stu_name,sex,brith,department,addr)
	values (901,'张飞','男',1985,'计算机系','河北省涿州市');
insert into student (stu_id,stu_name,sex,brith,department,addr)
	values (902,'关羽','男',1986,'中文系','山西省运城市');
insert into student (stu_id,stu_name,sex,brith,department,addr)
	values (903,'貂蝉','女',1990,'中文系','山西省忻州县');
insert into student (stu_id,stu_name,sex,brith,department,addr)
	values (904,'刘备','男',1990,'英语系','河北省涿州市');
insert into student (stu_id,stu_name,sex,brith,department,addr)
	values (905,'小乔','女',1991,'英语系','安徽省潜山市');
insert into student (stu_id,stu_name,sex,brith,department,addr)
	values (906,'赵云','男',1988,'计算机系','河北省正定市');
#查看数据表信息
desc student;
select * from student;
#创建数据表score
create table score(
	score_id int unsigned primary key auto_increment comment'编号',
	stu_id int not null comment'学号',
	c_name varchar(20) comment'课程名',
	grade int comment'分数'
	)character set utf8 collate utf8_bin;
#查看创建情况
show tables;
desc score;
#在数据表score中插入10条信息
insert into score (score_id,stu_id,c_name,grade)
	values (1,901,'计算机',98);
insert into score (score_id,stu_id,c_name,grade)
	values (2,901,'英语',80);
insert into score (score_id,stu_id,c_name,grade)
	values (3,902,'计算机',65);
insert into score (score_id,stu_id,c_name,grade)
	values (4,902,'中文',88);
insert into score (score_id,stu_id,c_name,grade)
	values (5,903,'中文',95);
insert into score (score_id,stu_id,c_name,grade)
	values (6,904,'计算机',70);
insert into score (score_id,stu_id,c_name,grade)
	values (7,904,'英语',92);
insert into score (score_id,stu_id,c_name,grade)
	values (8,905,'英语',94);
insert into score (score_id,stu_id,c_name,grade)
	values (9,906,'计算机',90);
insert into score (score_id,stu_id,c_name,grade)
	values (10,906,'英语',85);
#查看情况
desc score;
select * from score;
#查看分数
select score_id,grade from score;
#更新分数表score中第8条记录分数为84
update score set grade=84 where score_id = 8;
select score_id,grade from score;
#更新分数表score中第10条记录为906，计算机，99
update score set stu_id=906,c_name='计算机',grade=99 where score_id=10;
select score_id,stu_id,c_name,grade from score;
#更新分数表score中前3条记录分数均为100
update score set grade=100
	limit 3;
select * from score;
#删除分数表中前3条记录
delete from score 
	limit 3;
select * from score;
#删除分数表中第7条记录
delete from score where score_id = 7;
select * from score;
#删除分数表后，再次添加数据，使其score_id自增从1开始
insert into score values(0,904,'英语',92);
select * from score;