MySQL(1) 数据库的增删改查

MySQL(1) 数据库的增删改查

# 如果存在删除
drop database if exists kh69;
# 创建数据库
create database kh69;
# 进库
use kh69;
# 创建省份表
create table province(
	provinceId int(2) auto_increment primary key,
	provinceName varchar(20) not null unique key
);
# 创建城市表
create table city(
	cityId int(2) auto_increment primary key,
	cityName varchar(20) not null unique key,
	isProLevel bit not null default 0,
	provinceId int(2)
);
# 创建县区
create table county(
	countyId int(3) auto_increment primary key,
	countyName varchar(20) not null,
	cityId int(2) not null
);
# 创建学生表
create table student(
	stuId int(6) auto_increment primary key,
	stuName varchar(10) not null,
	gender int(1) not null default 0,
	age int(2) unsigned not null,
	phone varchar(20) not null unique key,
	provinceId int(2) not null,
	cityId int(2) not null,
	countyId int(3) not null,
	address varchar(100)
);
# 创建学科表
create table subject(
	subjectId int(2) auto_increment primary key,
	subjectName varchar(20) not null unique key,
	classHours int(3) unsigned not null
);
# 创建成绩表
create table score(
	scoreId int auto_increment primary key,
	stuId int(6) not null,
	subjectId int(2) not null,
	isIn bit not null default 1,
	level int(1) not null default 1,
	score int(3) unsigned
);
#主键绑定
alter table city add constraint fk_city_province_provinceid foreign key(provinceId) references province(provinceId);
alter table county add constraint fk_county_city_cityid foreign key(cityId) references city(cityId);
alter table score add constraint fk_score_student_stuid foreign key(stuId) references student(stuId);
alter table score add constraint fk_score_subject_subjectid foreign key(subjectId) references subject(subjectId);
insert into subject(subjectName,classHours) value('LogicJava',0),('JavaOOP',0),('Java高级特性',0),('HTML',0),('CSS3',0),('JQuery',0);

操作数据库

# 操作数据库
# 新增数据----------------------------------------------------------------------------
insert into province(provinceName) value('江苏');
insert into city(cityName,provinceId) value('南京',1);
insert into city(cityName,isProLevel) value('上海',1);
# 新增多条数据
insert into province(provinceName) value('江苏'),('安徽'),('浙江'),('福建'),('广东');
insert into subject(subjectName,classHours) value('LogicJava',0),('JavaOOP',0),('Java高级特性',0),('HTML',0),('CSS3',0),('JQuery',0);
# 表数据复制
create table provinceCopy(
	provinceName varchar(20) not null unique key
);
insert into provinceCopy
select provinceName from province;
# 表结构和数据复制
create table proCopy2(
	select * from province
);

数据操作

#增加数据
insert into city(cityName,provinceId) value('南京',1),('镇江',1);
# 删除数据----------------------------------------------------------------------------
delete from province where provinceId=3;
# 删除注意事项
delete from city where cityId=2;
delete from province where provinceId=1;
# 修改数据----------------------------------------------------------------------------
update province set provinceName='安徽省' where provinceId=2;
# 查询数据----------------------------------------------------------------------------
# 基础查询
# 查姓名中包含张的学员
select * from student where stuName like '%张%';
# 查姓张的学员
select * from student where stuName like '张%';
# 查姓张名字为两个字的学员
select * from student where stuName like '张_';
# 查县区编号为1,2或3的学员
select * from student where countyId in (1,2,3);
# 查地址为空的学员
select * from student where address is not NULL;
# 查年龄在22至25岁之间的学员
select * from student where age>=22 and age<=25;
select * from student where age between 22 and 25;
# 查省份编号为1(江苏)年龄在22至25岁之间的学员
select * from student where provinceId=1 and age>=22 and age<=25;
# 分组查询
# 查询不同性别的学员人数
select
	gender,count(1)
from
	student
group by
	gender;
# 查询不同性别的学员人数,利用if()函数将性别提炼为汉字
select
	if(gender=0,'男','女') gender,count(1)
from
	student
group by
	gender;
# 查询不同省份，不同城市，不同性别的学生人数
select
	provinceId,cityId,if(gender=0,'男','女') gender,count(1)
from
	student
group by
	provinceId,cityId,gender;
# 查询不同学科的平均成绩
select
	subjectId,avg(score)
from
	score
group by
	subjectId;
# 查询不同学员的平均成绩，并倒序输出
select
	stuId,avg(score) avgScore
from
	score
group by
	stuId
order by
	avgScore DESC;