Mysql基础学习day02-作业

2025-09-11 49

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

云数据库 RDS PostgreSQL，高可用系列 2核4GB

简介： 本教程介绍了数据库表的创建与管理操作，包括创建员工表、插入测试数据、删除记录、更新数据以及多种查询操作，涵盖了SQL语句的基本使用方法，适合初学者学习数据库操作基础。

准备工作

create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) not null comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
    salary int unsigned comment '薪资',
    image varchar(300) comment '头像',
    entry_date date comment '入职日期',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';


-- 准备测试数据
INSERT INTO emp(id, username, password, name, gender, phone, job, salary, image, entry_date, create_time, update_time)
VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'1.jpg','2000-01-01','2023-10-27 16:35:33','2023-10-27 16:35:35'),
     (2,'songjiang','123456','宋江',1,'13309090002',2,8600,'2.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:35:37'),
     (3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'3.jpg','2008-05-01','2023-10-27 16:35:33','2023-10-27 16:35:39'),
     (4,'wuyong','123456','吴用',1,'13309090004',2,9200,'4.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:35:41'),
     (5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'5.jpg','2012-12-05','2023-10-27 16:35:33','2023-10-27 16:35:43'),
     (6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'6.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:45'),
     (7,'chaijin','123456','柴进',1,'13309090007',1,4700,'7.jpg','2005-08-01','2023-10-27 16:35:33','2023-10-27 16:35:47'),
     (8,'likui','123456','李逵',1,'13309090008',1,4800,'8.jpg','2014-11-09','2023-10-27 16:35:33','2023-10-27 16:35:49'),
     (9,'wusong','123456','武松',1,'13309090009',1,4900,'9.jpg','2011-03-11','2023-10-27 16:35:33','2023-10-27 16:35:51'),
     (10,'lichong','123456','林冲',1,'13309090010',1,5000,'10.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:53'),
     (11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'11.jpg','2007-02-01','2023-10-27 16:35:33','2023-10-27 16:35:55'),
     (12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'12.jpg','2008-08-18','2023-10-27 16:35:33','2023-10-27 16:35:57'),
     (13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'13.jpg','2012-11-01','2023-10-27 16:35:33','2023-10-27 16:35:59'),
     (14,'shijin','123456','史进',1,'13309090014',2,10600,'14.jpg','2002-08-01','2023-10-27 16:35:33','2023-10-27 16:36:01'),
     (15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'15.jpg','2011-05-01','2023-10-27 16:35:33','2023-10-27 16:36:03'),
     (16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'16.jpg','2010-01-01','2023-10-27 16:35:33','2023-10-27 16:36:05'),
     (17,'liying','12345678','李应',1,'13309090017',1,5800,'17.jpg','2015-03-21','2023-10-27 16:35:33','2023-10-27 16:36:07'),
     (18,'shiqian','123456','时迁',1,'13309090018',2,10200,'18.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:09'),
     (19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'19.jpg','2008-01-01','2023-10-27 16:35:33','2023-10-27 16:36:11'),
     (20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'20.jpg','2018-01-01','2023-10-27 16:35:33','2023-10-27 16:36:13'),
     (21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'21.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:15'),
     (22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'22.jpg','2016-01-01','2023-10-27 16:35:33','2023-10-27 16:36:17'),
     (23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'23.jpg','2012-01-01','2023-10-27 16:35:33','2023-10-27 16:36:19'),
     (24,'tongwei','123456','童威',1,'13309090024',5,5000,'24.jpg','2006-01-01','2023-10-27 16:35:33','2023-10-27 16:36:21'),
     (25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'25.jpg','2002-01-01','2023-10-27 16:35:33','2023-10-27 16:36:23'),
     (26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'26.jpg','2011-01-01','2023-10-27 16:35:33','2023-10-27 16:36:25'),
     (27,'lijun','123456','李俊',1,'13309090027',5,6600,'27.jpg','2004-01-01','2023-10-27 16:35:33','2023-10-27 16:36:27'),
     (28,'lizhong','123456','李忠',1,'13309090028',5,5000,'28.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:36:29'),
     (29,'songqing','123456','宋清',1,'13309090029',5,5100,'29.jpg','2020-01-01','2023-10-27 16:35:33','2023-10-27 16:36:31'),
     (30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'30.jpg','2020-03-01','2023-10-27 16:35:33','2023-10-27 16:36:31');

往emp表中插入一条测试数据，全部的字段都需要插入值。

insert into emp
values (31, 'aming', '123456', '阿铭', 1, '13309090031', 5, 8000, '31.jpg', '2020-03-01', '2023-10-27 16:35:33',
        '2023-10-27 16:36:31');
# insert into emp
# values (34, 'aming', '123456', '阿铭', 1, '13309090031', null, 8000, '31.jpg', '2020-03-01', '2023-10-27 16:35:33',
#         '2023-10-27 16:36:31');

批量往emp表中插入两条测试数据，全部的字段都需要插入值。

insert into emp
values (32, 'xiaoming', '123456', '小明', 1, '13309090032', 5, 8000, '32.jpg', '2020-03-01', '2023-10-27 16:35:33',
        '2023-10-27 16:36:31'),
       (33, 'erming', '123456', '二明', 1, '13309090033', 5, 8000, '33.jpg', '2020-03-01', '2023-10-27 16:35:33',
        '2023-10-27 16:36:31');

删除id为30,31的员工信息

delete
from emp
where id in (30, 31);

将id为6，7，9，12这个几个员工的入职日期修改为 '2018-09-10' , 密码修改为 '1234567'

update emp
set entry_date = '2018-09-10',
    password   = '1234567'
where id in (6, 7, 9, 12);

查询指定字段 name,entry_date 并设置别名为姓名、入职日期并返回

select name as '姓名', entry_date as '入职日期'
from emp;

查询返回所有字段（两种写法哦）

select *
from emp;

select id,
       username,
       password,
       name,
       gender,
       phone,
       job,
       salary,
       image,
       entry_date,
       create_time,
       update_time
from emp;

查询性别为女且有职位的员工，要求查询出 name,entry_date 并起别名 (姓名、入职日期)

select name as '姓名', emp.entry_date as '入职日期'
from emp
where gender = 2
  and job is not null;

查询已有的员工关联了哪几种职位(不要重复)

select distinct job
from emp;

查询姓名为 '童威' 或入职时间在 '2000-01-01' 到 '2010-01-01' 的员工

select *
from emp
where name = '童威'
  and entry_date between '2000-01-01' and '2010-01-01';

查询在薪资小于等于5000 的员工信息

select *
from emp
where salary <= 5000;

查询没有分配职位的员工信息

select *
from emp
where job is null;

查询有职位的员工信息

select *
from emp
where job is not null;

查询密码不等于 '123456' 或没有职位的员工信息

select *
from emp
where password != '123456'
   or job is null;

查询入职日期在 '2000-01-01' (包含) 到 '2015-01-01'(包含) 之间的员工信息

select *
from emp
where entry_date between '2000-01-01' and '2015-01-01';

查询入职时间在 '2000-01-01' (包含) 到 '2015-01-01'(包含) 之间且性别为女的员工信息

select *
from emp
where entry_date between '2000-01-01' and '2015-01-01'
  and gender = 2;

查询职位是 2 (讲师) 或 3 (学工主管) 或 4 (教研主管) 的员工信息（两种写法实现）

select *
from emp
where job = 2
   or job = 3
   or job = 4;

select *
from emp
where job in (2, 3, 4);

查询姓名为两个字且性别为男且薪资高于5000 的员工信息

select *
from emp
where name like '__'
  and gender = 1
  and salary > 5000;

查询姓名中包含 '小' 的员工信息且入职时间在 '2008-01-01' 之后入职的员工

select *
from emp
where name like '%小%'
  and entry_date > '2008-01-01';

查询出性别为男 , 或入职时间在 '2010-01-01' 之后入职的员工

select *
from emp
where gender = 1
   or entry_date > '2010-01-01';

查询有职位的，且姓名为两个字的员工信息

select *
from emp
where job is not null
  and name like '__';

查询所有的性别为男(gender 为 1)的讲师 (job 为 2) 的员工信息

select *
from emp
where gender = 1
  and job = 2;

查询薪资小于 8000 或入职时间在 2010-10-09 之后入职的员工信息

select *
from emp
where salary < 8000
   or entry_date > '2010-10-09';

根据性别分组 , 统计男性和女性员工的数量

select gender, count(*)
from emp
group by gender;

先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位

select job, count(*)
from emp
where entry_date <= '2015-01-01'
group by job
having count(*) >= 2;

Mysql基础学习day02-作业

准备工作

热门文章

最新文章

相关课程

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

Mysql基础学习day02-作业

准备工作

热门文章

最新文章

相关课程

推荐镜像