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

mysql增、删、改和单表查询多表查询

news 2025/4/1 19:40:17

一、四大名著表t_hero的相关操作：

1.进入并创建db_ck数据库：

create database if not exists db_ck;
show databases;
use db_ck;

2.创建四大名著表t_hero并且插入一些数据：

创建t_hero表：

create table t_hero (
id int,
hero_name varchar(50),
book_name varchar(50)
);

插入数据：

insert into t_hero value
(1, '贾宝玉', '《红楼梦》'),
(2, '林黛玉', '《红楼梦》'),
(3, '薛宝钗', '《红楼梦》'),
(4, '王熙凤', '《红楼梦》');insert into t_hero value
(5, '孙悟空', '《西游记》'),
(6, '猪八戒', '《西游记》'),
(7, '沙僧', '《西游记》'),
(8, '唐僧', '《西游记》');insert into t_hero value
(9, '宋江', '《水浒传》'),
(10, '林冲', '《水浒传》'),
(11, '吴用', '《水浒传》'),
(12, '武松', '《水浒传》');insert into t_hero value
(13, '刘备', '《三国演义》'),
(14, '关羽', '《三国演义》'),
(15, '张飞', '《三国演义》'),
(16, '诸葛亮', '《三国演义》');

3. 添加id为主键约束：

查看表结构：

desc t_hero;

添加主键约束：

alter table t_hero modify column id int auto_increment primary key;

4. 添加hero_name为唯一约束，且不为空：

alter table t_hero modify column hero_name varchar(50) not null,
add unique unique_hero_name(hero_name);

5.增加人物出现时的事件的列：

alter table t_hero add column incident varchar(255) not null;

6.更新各个人物出现的事件：

update t_hero set incident = "桃园结义" where hero_name = "刘备";
update t_hero set incident = "桃园结义" where hero_name = "关羽";
update t_hero set incident = "桃园结义" where hero_name = "张飞";
update t_hero set incident = "三顾茅庐" where hero_name = "诸葛亮";
update t_hero set incident = "开天辟地" where hero_name = "孙悟空";
update t_hero set incident = "高老庄" where hero_name = "猪八戒";
update t_hero set incident = "流沙河" where hero_name = "沙僧";
update t_hero set incident = "贞观十三年" where hero_name = "唐僧";
update t_hero set incident = "红楼梦元年" where hero_name = "贾宝玉";
update t_hero set incident = "红楼梦二年二月十二" where hero_name = "林黛玉";
update t_hero set incident = "红楼梦三年正月二十一" where hero_name = "薛宝钗";
update t_hero set incident = "红楼梦前九年九月初二" where hero_name = "王熙凤";
update t_hero set incident = "题反诗" where hero_name = "宋江";
update t_hero set incident = "误入白虎堂" where hero_name = "林冲";
update t_hero set incident = "景阳冈打虎" where hero_name = "武松";
update t_hero set incident = "十万生辰纲" where hero_name = "吴用";

7.增加四大名著中的各个人物：

insert into t_hero(hero_name, book_name, incident) values("李逵", "《水浒传》", "真假李逵");
insert into t_hero(hero_name, book_name, incident) values("贾母", "《红楼梦》", "主持大观园建造");
insert into t_hero(hero_name, book_name, incident) values("曹操", "《三国演义》", "官渡之战击败袁绍");
insert into t_hero(hero_name, book_name, incident) values("白龙马", "《西游记》", "往西取经去，向东驮经来");

8.删除四大名著当中的一位人物：

delete from t_hero where hero_name = "贾母";
delete from t_hero where hero_name = "李逵";
delete from t_hero where hero_name = "曹操";
delete from t_hero where hero_name = "白龙马";

9. 相关查询语句（不完全）：

查询表中所以信息：

select * from t_hero;

查询需要的字段信息：

select id, hero_name from t_hero;

查询一个字段，一个等值条件：

select hero_name from t_hero where id = 1;

查询所有西游记的人物信息：

select * from t_hero where book_name = "《西游记》";

查询所有西游记以外的人物信息：

select * from t_hero where book_name != "《西游记》";

id降序或升序排序查询：

select * from t_hero order by id desc;
select * from t_hero order by id asc;

查询id小于12大于等于3的所有信息：

select * from t_hero where id >= 3 and id < 12;
select * from t_hero where id between 3 and 11;

二、worker表的单表查询：

创建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(*) from worker;

4.列出最高工和最低工资：

select max(工资), min(工资) from worker;

5.列出职工的平均工资和总工资：

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

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

create table worker_date (职工号 int(11) not null,姓名 varchar(50) not null,参加工作 varchar(255) not null,primary key(职工号)) engine=innodb default charset=utf8 row_format=dynamic;

insert into (职工号, 姓名, 参加工作)
values (1007, "王小白", "网络工程师");
insert into (职工号, 姓名, 参加工作)
values (1008, "刘姥姥", "渗透工程师");
insert into (职工号, 姓名, 参加工作)
values (1009, "陈小明", "网络安全工程师");
insert into (职工号, 姓名, 参加工作)
values (1010, "李小红", "");
insert into (职工号, 姓名, 参加工作)
values (1011, "李家明", "逆向工程师");
insert into (职工号, 姓名, 参加工作)
values (1012, "何下岑", "安全规划与设计");

7.显示所有女职工的年龄：

在worker表中添加性别字段：

alter table worker add column gender enum("男", "女") not null;

修改worker表中员工性别信息：

update worker set gender = "女" where between 1003 and 1005;

所有女职工的年龄：

select 姓名, gender, timestampdiff(year, 出生日期, curdate()) as age from worker where gender = "女";

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

将姓刘的职工信息插入worker表：

insert into worker(部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期, gender)
values(103, 1007, "2018-06-06", 6000.00, "党员", "刘姥姥", "2000-02-02", "女");
insert into worker(部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期, gender)
values(103, 1008, "2017-06-06", 6500.00, "团员", "刘老城", "1999-02-02", "男");

姓刘的职工的职工号、姓名和出生日期：

select 职工号, 姓名, 出生日期 from worker where 姓名 like "刘%";

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

插入1960年以前出生的职工信息：

insert into worker(部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期, gender)
values(103, 1009, "1998-06-06", 6500.00, "团员", "陈平安", "1950-02-02", "男");

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

select 姓名, 工作时间 from worker where 出生日期 < "1960-01-01";

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

插入工资在1000-2000之间的职工信息：

insert into worker(部门号, 职工号, 工作时间, 工资, 政治面貌, 姓名, 出生日期, gender)
values(104, 1010, "2024-06-06", 1500.00, "团员", "顾灿", "2002-02-02", "男");

工资在1000－2000之间的所有职工姓名：

select 姓名 from worker where 工资 between 1000 and 2000;

11.列出所有陈姓和李姓的职工姓名：

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

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

 select 职工号, 姓名, 政治面貌 from worker where 部门号 like "%2" or 部门号 like "%3";

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

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

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.列出总人数大于3的部门号和总人数：

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

三、多表查询：

1.创建student和score表：

create table student (id int(10) not null unique primary key,name varchar(20) not null,sex varchar(20),birth year,department varchar(20),address varchar(50)
);

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 order by id asc limit 3 offset 1;

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

select id, name, department from student;

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

select * from student where department = "计算机系" or department = "英语系";

7.从student表中查询年龄18~22岁的学生信息（没有查出来没有找出原因）：

 select * from student where timestampdiff(year, birth, curdate()) between 18 and 22;select * from student where year(curdate()) - year(birth) between 18 and 22;

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

 select department, count(department) 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 student, score where student.id = score.stu_id and name = "李四";

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

select * from student inner join score on student.id = score.stu_id;

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

select student.name, sum(score.grade) as 总成绩 from student, score where student.id = score.stu_id group by score.stu_id;

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

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

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

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

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

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

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

 select * from student inner join score on student.id = score.stu_id where c_name = "计算机" order by grade desc;

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

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

 select student.name, department, score.c_name, score.grade from student inner join score on student.id = score.stu_id where student.name like "张%" or student.name like "王%";

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩（不知道年纪为啥为空）：

select student.name, timestampdiff(year, birth, curdate()) as age, department, score.c_name, grade from student inner join score on student.id = score.stu_id where address like "湖南%";select student.name, year(curdate())-year(birth) as age, department, score.c_name, grade from student inner join score on student.id = score.stu_id where address like "湖南%";