当前位置：首页 > news >正文

数据库练习2

news 2025/3/26 9:13:43

1.向heros表中新增一列信息，添加一些约束，并尝试查询一些信息

2.课堂代码练习

插入语句 INSERT INTO

删除语句DELETE和TRUNCATE

更新语句UPDATE和replace

查询语句SELECT

条件查询

select语句中的特殊情况

查询排序 order by

分组查询 group by

分页功能 limit

3.题目如下

一、单表查询

1、显示所有职工的基本信息。

2、查询所有职工所属部门的部门号，不显示重复的部门号。

3、求出所有职工的人数。

4、列出最高工和最低工资。

5、列出职工的平均工资和总工资。

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

7、显示所有职工的年龄。

8、列出所有姓刘的职工的职工号、姓名和出生日期。

9、列出1960年以前出生的职工的姓名、参加工作日期。

10、列出工资在1000－2000之间的所有职工姓名。

11、列出所有陈姓和李姓的职工姓名。

12、列出所有部门号为2和3的职工号、姓名、党员否。

13、将职工表worker中的职工按出生的先后顺序排序。

14、显示工资最高的前3名职工的职工号和姓名。

15、求出各部门党员的人数。

16、统计各部门的工资和平均工资

17、列出总人数大于4的部门号和总人数。

二、多表查询

3.查询student表的所有记录

编辑4.查询student表的第2条到4条记录

编辑5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

编辑6.从student表中查询计算机系和英语系的学生的信息

编辑7.从student表中查询年龄18~22岁的学生信息

编辑8.从student表中查询每个院系有多少人

编辑9.从score表中查询每个科目的最高分

编辑10.查询李四的考试科目（c_name）和考试成绩（grade）

编辑11.用连接的方式查询所有学生的信息和考试信息

编辑12.计算每个学生的总成绩

编辑13.计算每个考试科目的平均成绩

编辑14.查询计算机成绩低于95的学生信息

编辑15.查询同时参加计算机和英语考试的学生的信息

编辑16.将计算机考试成绩按从高到低进行排序

编辑17.从student表和score表中查询出学生的学号，然后合并查询结果

编辑18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

编辑19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

1.向heros表中新增一列信息，添加一些约束，并尝试查询一些信息

原表信息如下：

//向表中添加一列age信息
alter table heros add column age int;//id列添加主键约束，设置自增
alter table heros modify column id int auto_increment primary key;//name列添加唯一约束
alter table heros add constraint unique_name unique (name);//novel列添加非空约束
alter table heros modify column novel varchar(50) not NULL;

执行sql语句后：

尝试插入违反约束的信息：

查询：

//查询id小于5的信息，包括id，name，novel
select id,name,novel from heros where id<5;//查询gender为女的信息，包括name，novel
select name,novel from heros where gender='女';//查询novel为三国演义的信息，包括id，name，novel
select id,name,novel from heros where novel='三国演义';...

2.课堂代码练习

DML语句练习：

插入语句 INSERT INTO

语法：insert into table_name[(column1,column2...)] values (value1,value2...);

插入语句用于向某张表中插入数据，table_name后可选择只向某些列插入，values 后面括号里面的值要和前面列向对应；如果前面没有指定列，那么表中所有列的数据都要在value中写全，未写的列的值默认为NULL。

//向表中插入一条信息
insert into t_student value(1, '小明', '男', 18, '139', '成都');//插入id，姓名，性别
insert into t_student(id,name,gender) value(1, '小强', '男');

删除语句DELETE和TRUNCATE

语法：delete from table_name [where 条件...];truncate table table_name;

如果后面没有带where条件，相当于truncate 直接删除全表；truncate删除数据不经过数据字典，即执行后无法找回数据，谨慎使用。

//删除名字叫小强的信息
delete from t_student where name='小强';

更新语句UPDATE和replace

语法：update table_name set 字段1=新值[, 字段2=新值...]  [where 条件];

用于修改表中的数据，如果没有加入where条件会将整列所有的值都进行修改。

//将id为2的同学年龄改为30
update t_student set age = 30 where id = 2;//将id为1的同学电话改为110，住址改为四川
update t_student set tel='110' , address='四川' where id=1;//将所有的年龄都改为20
update t_student set age=18+2;

语法：replace into table_name(column1, column2...) values (value1, value2...);

该语句是集更新和插入为一体的一个语句，如果表中没有这条数据，则执行插入，否则执行更新（是按照主键区别）。注意：replace的更新，本质是先删除，再插入，因此没有写插入数据的列内容会自动为NULL。

replace into t_student (id, name, age) values(1, '小强', 22);

查询语句SELECT

语法：select *|字段1 [, 字段2 ……] from 表名称 [, 表名称2……] [where 条件] [group by 字段 [having 分组后的筛选]] [order by 字段 [desc|asc] [字段2 [desc|asc] ……]] [limit 分页]

select能做的操作很多，如排序order by，分组group by，分页limit...根据后面的条件以及各种操作查询前面select需要的表中的列。

条件查询

语法：
select column1, column2...
from table_name
where 条件

案例：

//查询t_student中的所有信息
select * from t_student;//查询表中某些字段的信息
select id,name,tel from t_student;//查询某些特定字段的信息
select * from t_student where id=1;
select id,name,tel from t_student where id=1;

select语句中的特殊情况

        对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）
        对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）
        运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算

SELECT 100+80; # 结果为180
SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT 'This'+'30is'; # 'This' 无法转换为数值，因此会被视为 0，'30is' 从开头部分可以转换为数值 30（直到遇到非数字字符为止）。
SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL

查询排序 order by

语法： select * from table_name order by 字段 asc|desc [,字段2 asc|desc ...];

将表内数据按照某一字段进行排序，asc升序（由小到大），desc降序（由大到小）；mysql中默认升序。后面可以加多个字段，会首先根据第一个字段排序，其次对于第一个字段相同的值按照第二个字段进行排序...

//将表内数据按照年龄大小排序select * from t_student order by age;//将表内数据按照id大小降序排序
select * from t_student order by id desc;

//将查询结果按照首先按照年龄继续宁排序，其次按照id大小进行排序
mysql> select * from t_student order by age,id desc;
mysql> select * from t_student order by age,id;

分组查询 group by

语法：select 分组字段 [,聚合函数] from table_name [where 条件] group by 分组字段 [having 条件];
【在分组前用where筛选，分组后用having】

sql中，分组是一种统计概念。查询的数据，进行数据分析时，可能需要将相同的数据分成一组。当存在where条件时写在条件后面。注意：分组查询时，查询字段必须是分组的字段，或者是聚合函数。

//查询分别有多少男生女生
select gender, count(gender) from t_student group by gender;//查询id<5的同学中分别有多少男生女生
select gender, count(gender) from t_student where id<5 group by gender;

如果直接在分组后的结果集上进行条件判断，将条件写在where中，会报错，因为结果集是分组后才能做的判断，而where实在查询前的条件判断。所以不能使用where，必须使用having。

//查询女生有多少人(分组前筛选)
select gender, count(gender) from t_student where gender='女' group by gender;//查询女生有多少人(分组后筛选)
select gender, count(gender) from t_student group by gender having gender='女';

分页功能 limit

语法：select column1, column2... from table_name limit number;

select语句，查询数据时，可能结果会非常多，此时就不能直接展示，分页展示。

总数量（all_data）：查询 select count(*)
每页展示的数量(page_size)：程序员定
当前页(cur_page)：默认第一页，用户自己点击选择
总页数(all_page)：总数量 % 每页的数量 == 0 整除后的商 ： 商 + 1limit num  # 查询多少条limit num1, num2; # num1: 偏移量, num2 : 每页的数量  limit cur_page * (page_size - 1), page_size;

//返回前三行的数据
select * from t_student limit 3;

3.题目如下

一、单表查询

素材： 表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等
CREATE TABLE `worker` (`部门号` int(11) NOT NULL,`职工号` int(11) NOT NULL,`工作时间` date NOT NULL,`工资` float(8,2) NOT NULL,`政治面貌` varchar(10) NOT NULL DEFAULT '群众',`姓名` varchar(20) NOT NULL,`出生日期` date NOT NULL,PRIMARY KEY (`职工号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生
日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');

1、显示所有职工的基本信息。

 select * from worker;

2、查询所有职工所属部门的部门号，不显示重复的部门号。

select distinct `部门号` from worker;

3、求出所有职工的人数。

select count(*) as `职工总人数` from worker;

4、列出最高工和最低工资。

select max(`工资`) as `最高工资`,min(`工资`) as `最低工资` from worker;

5、列出职工的平均工资和总工资。

select avg(`工资`) as `平均工资`, sum(`工资`) as `总工资` from worker;

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

create table `工作日期表` select `职工号`, `姓名`, `工作时间` from worker;
select * from `工作日期表`;

7、显示所有职工的年龄。

select `姓名`, 2025-year(`出生日期`) as `年龄` from worker;

8、列出所有姓刘的职工的职工号、姓名和出生日期。

select `职工号`, `姓名`, `出生日期` from worker where `姓名` like '刘%';
//因为原表没有姓刘的没有查询结果

9、列出1960年以前出生的职工的姓名、参加工作日期。

 select `姓名`, `工作时间` from worker where year(`出生日期`) < 1960;

10、列出工资在1000－2000之间的所有职工姓名。

select `姓名` from worker where `工资` >= 1000 and `工资` <= 2000;
select `姓名` from worker where not (`工资`<=1000 or `工资`>2000);
select `姓名` from worker where `工资` between 1000 and 2000;
//因为没有符合 1000-2000 的所以下面用 3000-5000 的演示

11、列出所有陈姓和李姓的职工姓名。

 select `姓名` from worker where `姓名` like '陈%' or `姓名` like ' 李%';

12、列出所有部门号为2和3的职工号、姓名、党员否。

select `部门号`, `职工号`, `姓名`, `政治面貌` from worker where `部门号`=102 or`部门号`=103;

13、将职工表worker中的职工按出生的先后顺序排序。

select * from worker order by `出生日期`;

14、显示工资最高的前3名职工的职工号和姓名。

select `职工号`, `姓名` from worker order by `工资` desc limit 3;

15、求出各部门党员的人数。

select `部门号`, count(*) as `党员人数` from worker where `政治面貌` = '党员' group by `部门号`;

16、统计各部门的工资和平均工资

select `部门号`, sum(`工资`) as `总工资`, avg(`工资`) as `平均工资` from worker group by `部门号`;

17、列出总人数大于4的部门号和总人数。

select `部门号`, count(*) as `总人数` from worker group by `部门号`having count(*)>4;

二、多表查询

1.创建student和score表
CREATE TABLE student (id INT(10) NOT NULL UNIQUE PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(4),birth YEAR,department VARCHAR(20),address VARCHAR(50)
);
创建score表。SQL代码如下：
CREATE TABLE `score` (`id` INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,`stu_id` INT(10) NOT NULL,`c_name` VARCHAR(20),`grade` INT(10)
);
2.为student表和score表增加记录
向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

3.查询student表的所有记录

select * from student;

4.查询student表的第2条到4条记录

select * from student limit 3 offset 1;

5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select `id`, `name`, `department` from student;

6.从student表中查询计算机系和英语系的学生的信息

select * from student where `department` like '计算机系' or `department` like '英语系';

7.从student表中查询年龄18~22岁的学生信息

select * from student where 2025 - `birth` between 18 and 22;
//因为没有18-22，下面演示的是18-50

8.从student表中查询每个院系有多少人

select `department`, count(*) as `人数` from `student` group by `department`;

9.从score表中查询每个科目的最高分

select `c_name`, max(`grade`) as `最高分` from score group by `c_name`;

10.查询李四的考试科目（c_name）和考试成绩（grade）

select c_name, grade from score where score.stu_id=(select id from
student where name='李四');

11.用连接的方式查询所有学生的信息和考试信息

select * from student, score where student.id = score.stu_id;

12.计算每个学生的总成绩

select stu_id, sum(grade) as `总成绩` from score group by stu_id;

13.计算每个考试科目的平均成绩

select c_name, avg(grade) as `平均成绩` from score group by c_name;

14.查询计算机成绩低于95的学生信息

select * from student join score on student.id=score.stu_id where c_name='计算机' and grade<95;

15.查询同时参加计算机和英语考试的学生的信息

select * from student where id in (select stu_id from score where c_name='计算机') and id in (select stu_id from score where c_name='英语');

16.将计算机考试成绩按从高到低进行排序

select * from score where c_name = '计算机' order by grade desc;

17.从student表和score表中查询出学生的学号，然后合并查询结果

select id from student union select stu_id from score;

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

select name, department, c_name, grade from student, score where student.id=score.stu_id and (name like '张%' or name like '王%' );

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

select name, 2025-birth as `age`, department, c_name, grade from student, score where student.id=score.stu_id and address like '湖南%' ;

查看全文

http://www.mrgr.cn/news/95547.html

JVM垃圾回收笔记02-垃圾回收器

论文阅读笔记：Denoising Diffusion Probabilistic Models (2)

【记录一下】LMDeploy学习笔记及遇到的问题

【算法】常见dp、多状态dp、背包问题、子序列问题

蓝桥杯劲舞团

给语言模型增加知识逻辑校验智能，识别网络信息增量的垃圾模式

大数据环境搭建

关于网络的一点知识（持续更新）

LangChain Chat Model学习笔记

windows清除电脑开机密码，可保留原本的系统和资料，不重装系统

python-selenium 爬虫由易到难

用 pytorch 从零开始创建大语言模型（零）：汇总

Ubuntu实时读取音乐软件的音频流

‘闭包‘, ‘装饰器‘及其应用场景

(四)---四元数的基础知识-(定义)-(乘法)-(逆)-(退化到二维复平面)-(四元数乘法的导数)

链表题型-链表操作-JS

ffmpeg介绍(一)——解封装

pycharm快捷键汇总（持续更新）

ROS melodic 安装 python3 cv_bridge

JVM垃圾回收笔记01-垃圾回收算法

1.向heros表中新增一列信息，添加一些约束，并尝试查询一些信息

2.课堂代码练习

插入语句 INSERT INTO

删除语句DELETE和TRUNCATE

更新语句UPDATE和replace

查询语句SELECT

条件查询

select语句中的特殊情况

​​​查询排序 order by

分组查询 group by

分页功能 limit

3.题目如下

一、单表查询

1、显示所有职工的基本信息。

2、查询所有职工所属部门的部门号，不显示重复的部门号。

3、求出所有职工的人数。

4、列出最高工和最低工资。

5、列出职工的平均工资和总工资。

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

7、显示所有职工的年龄。

8、列出所有姓刘的职工的职工号、姓名和出生日期。

9、列出1960年以前出生的职工的姓名、参加工作日期。

10、列出工资在1000－2000之间的所有职工姓名。

11、列出所有陈姓和李姓的职工姓名。

12、列出所有部门号为2和3的职工号、姓名、党员否。

13、将职工表worker中的职工按出生的先后顺序排序。

14、显示工资最高的前3名职工的职工号和姓名。

15、求出各部门党员的人数。

16、统计各部门的工资和平均工资

17、列出总人数大于4的部门号和总人数。

二、多表查询

3.查询student表的所有记录

4.查询student表的第2条到4条记录

5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

6.从student表中查询计算机系和英语系的学生的信息

7.从student表中查询年龄18~22岁的学生信息

8.从student表中查询每个院系有多少人

9.从score表中查询每个科目的最高分

10.查询李四的考试科目（c_name）和考试成绩（grade）

11.用连接的方式查询所有学生的信息和考试信息

12.计算每个学生的总成绩

13.计算每个考试科目的平均成绩

14.查询计算机成绩低于95的学生信息

15.查询同时参加计算机和英语考试的学生的信息

16.将计算机考试成绩按从高到低进行排序

17.从student表和score表中查询出学生的学号，然后合并查询结果

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

相关文章：

查询排序 order by