题一
– 1.创建test数据库
create DATABASE test character set utf8
– 2.在test数据库中创建student表,表结构如下图所示(id设置为主键自增长)
-- create table student (
-- id int auto_increment,
-- name VARCHAR(20),
-- score FLOAT(3.2),
-- address VARCHAR(50),
-- useremali VARCHAR(20),
-- primary KEY(id)
-- );
– 3.向student表中添加记录如下图所示(10分)
-- INSERT into student values (1,'张三',98,'北京','1111111111111@qq.com'),
-- (2,'李四',88,'上海','1111111111111@qq.com'),
-- (3,'王五',80,'广州','1111111111111@qq.com'),
-- (4,'赵六',70,'深圳','1111111111111@qq.com'),
-- (5,'孙七',68,'杭州','1111111111111@qq.com'),
-- (6,'小红',40,'北京','1111111111111@qq.com'),
-- (7,'小黑',90,'广州','1111111111111@qq.com'),
-- (8,'小绿',100,'深圳','1111111111111@qq.com'),
-- (9,'小粉',60,'杭州','1111111111111@qq.com'),
-- (10,'小紫',70,'黑龙江','1111111111111@qq.com')
4.使用sql语句查询出表中的所有内容
select * from
– 5.使用sql语句查询出表中所有score>70的同学的id,name,score
select id, name,score from student where score>70
– 6.更改name字段的数据类型为varchar(50)
ALTER table student MODIFY name varchar(50)
– 7.向表中添加一个字段,字段名称为“pingjia”,字段类型为varchar(20)
alter table student add pingjia varchar(20) DEFAULT NULL
– 8.更改姓名是张三的同学的分数为88
update student set score=88 where name='张三'
– 9.如果80分为及格线,查询出所有及格的同学的详细信息
select *
from student
where score > 80
– 10.使用关键字in,查询id值是1或5或7的同学的基本信息
select *
from student
where id in(1,5,7)
– 11.查询id值在5至8的所有同学的基本信息
select *
from student
where id between 5 and 8
– 12.查询姓名是小红并且分数大于60的同学的基本信息
select *
from student
WHERE name='小红' and score > 60
– 13.查询姓名是小红或者分数大于90的同学的基本信息
select *
from student
WHERE name='小红' or score > 90
题二
- 创建db_test数据库
create database db_test character set utf8
- 在test数据库中创建teacher表,表结构如下图所示(id设置为主键自增长)(10分)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9kxeddGj-1630927335660)(file:///C:\Users\Y\AppData\Local\Temp\ksohtml12156\wps5.jpg)]
create table teacher (
Number int PRIMARY key auto_increment UNIQUE not NULL,
Name varchar(30) not null,
Sex varchar(4),
DepNo int,
Salary float(9,2) default 0 ,
AddERSS varchar(100)
-- foreign key(DepNo) references student(id)
)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zWkiqpzC-1630927335661)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906185322576.png)]
- 向teacher表中添加记录如下图所示(10分)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xdARSFtv-1630927335663)(file:///C:\Users\Y\AppData\Local\Temp\ksohtml12156\wps6.jpg)]
INSERT INTO teacher VALUES
(201,'刘芸','女', 604, 4500,'河北沧州省'),
(202,'周星驰','男', 601, 1600,'山东省济南'),
(203,'亲木质','男', 604, 2800,'北京昌平'),
(204,'周运发','男', 602, 3500,'河北沧州省'),
(205,'林双','女', 605, 1200,'河北沧保定'),
(206,'张帆','女', 602, 3000,'北京昌平'),
(207,'程良友','男', 604, 2800,'山西大同'),
(208,'张国荣','男', 601, 2500,'山东烟台')
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7dQIlPMN-1630927335666)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906190015627.png)]
使用sql语句完成以下功能:
- 向表中添加记录,字段对应值分别为(209,毛不易,男,604,4000,黑龙江齐齐哈尔)(10分)
insert into teacher
value (209, '毛不易','男', 604, 4000, '黑龙江哈尔滨')
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CZXGZ0CS-1630927335668)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906190250106.png)]
查询表中所有记录(10分)
select * from teacher
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-innZ8sjG-1630927335673)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906190432739.png)]
删除表中Number是201并且sex是女的教师信息
delete from
teacher where Number=201 and sex = '女'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NuJc6Sjh-1630927335674)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906190751148.png)]
查询表中Number字段的值是202,205或207教师的所有记录
select * from teacher where Number in(202, 205, 207)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DAMkrt4G-1630927335675)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906191304914.png)]
修改表中Number值是202教师的姓名为“马云”
Update teacher set name = '马云' where Number='202'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M1XnRG5P-1630927335677)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906191418220.png)]
查询成绩最高的前三条信息
select *
from teacher
ORDER BY Salary
desc limit 3
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H6CY5Iu2-1630927335678)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906191642436.png)]
查询姓名中包含张的信息
select *
from teacher
where name LIKE '%张%'
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YBFUhskZ-1630927335679)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906191728169.png)]
查询男女生的个数信息
select sex,count(1)
from teacher
GROUP BY sex
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qD7kLDaD-1630927335680)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906191945144.png)]
查询薪资在1800到3600之间的信息(使用多种方式实现)
select *
from teacher
where Salary BETWEEN 1800 and 3600
ORDER BY Salary desc
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r2GpjJPL-1630927335683)(C:\Users\Y\AppData\Roaming\Typora\typora-user-images\image-20210906192114446.png)]