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

一、四大名著表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;
select * from student where 2025-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表中查询出学生的学号，然后合并查询结果：

select student.id as student_id from student union select stu_id as 学号 from 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.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩（使用代码1和2不知道年纪为啥为空，但方法3能够查出结果）：

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