Mysql 基础_习题三

题目集一

# 建表
create table if not exists book
(
    id       int primary key auto_increment not null,
    name     varchar(20),
    pub_date varchar(30),
    price    decimal(10, 2)
);
# 插入数据
insert into book(name, pub_date, price)
values ('射雕英雄传', '1970-05-01 00:00:00', 36.60),
       ('天龙八部', '1986-07-24 00:00:00 ', 50.20),
       ('笑傲江湖 ', '1995-12-24 00:00:00', 40.00),
       ('雪山飞狐 ', '1987-11-11 00:00:00', 29.00);

create table if not exists heroes
(
    hid     int primary key auto_increment not null,
    name    varchar(20),
    age     tinyint(4),
    gender  varchar(4),
    skill   varchar(20),
    book_id int
);

insert into heroes(name, age, gender, skill, book_id)
values ('郭靖', 30, '男', '降龙十八掌', 1),
       ('黄蓉', 30, '女', '打狗棍法 ', 1),
       ('黄药师', 60, '男', '弹指神通', 1),
       ('欧阳锋', 65, '男', '蛤蟆功', 1),
       ('梅超风 ', 40, '女', '九阴白骨爪', 1),
       ('乔峰 ', 33, '男', '降龙十八掌', 2),
       ('段誉 ', 25, '男', '六脉神剑', 2),
       ('虚竹 ', 27, '男', '天山六阳掌', 2),
       ('王语嫣 ', 18, '女', '神仙姐姐', 2),
       ('令狐冲', 32, '男', '独孤九剑', 3),
       ('任盈盈', 24, '女', '弹琴', 3),
       ('岳不群  ', 50, '保密', '华山剑法', 3),
       ('东方不败 ', 99, '中性', '葵花宝典', 3),
       ('胡斐', 26, '男', '胡家刀法', 4),
       ('苗若兰', 16, '女', '黄衣', 4),
       ('程灵素', 20, '女', '医术', 4),
       ('袁紫衣', 22, '女', '六合拳', 4);

3、修改book表pub_date字段类型为date类型

alter table book
    modify pub_date date;

4、查询所有的英雄信息以及对应的书名

select h.*, b.name
from heroes h
         join book b on h.book_id = b.id;

5、查询80年代出版的书中所有的女性英雄信息以及对应的书的信息

select h.*, b.*
from heroes h,
     book b
where h.book_id = b.id
  and gender = '女'
  and b.pub_date in (
    select b.pub_date
    from book b
    where b.pub_date > '1980-01-01 00:00:00'
);

6、查出会"降龙十八掌"的英雄名字以及对应的书名

select h.name, b.name
from heroes h,
     book b
where h.skill = '降龙十八掌'
  and h.book_id = b.id;

7、查询每本书中英雄年龄的平均值

select b.name, avg(age)
from heroes h,book b
where h.book_id = b.id
group by h.book_id;

8、查询每本书中年纪最大的英雄

select b.name, avg(age)
from heroes h,book b
where h.book_id = b.id
group by h.book_id;


select b.name 书名,h.name 人名
from heroes h,book b
where h.book_id = b.id and age = (
    select max(age)
    from heroes
)
group by h.book_id;


select h.name as "英雄", h.age, b.name as "书名"
from heroes as h
         inner join (select max(age) as mage, book_id as bid from heroes group by book_id) as m
         inner join book as b on h.age = m.mage and m.bid = b.id;

题目集二

1. 登录 mysql 然后创建一个名为py2888 数据库

create database py2888;
use py2888;

2. 然后在这个数据库下创建一个 职工表worker(工号id,姓名name,能力值 level<整数>,所属部门dep_id）

create table if not exists worker
(
    id     int comment '工号',
    name   varchar(10) comment '姓名',
    level  int comment '能力值',
    dep_id int comment '所属部门'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

3. 创建一个 部门表department(部门编号id,部门名称name,主管工号mid)

create table if not exists department
(
    id   int comment '部门编号',
    name varchar(10) comment '部门名称',
    mid  int comment '主管工号'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

4. 现有职工数据如下 请使用 insert 语句插入到worker表中

insert into worker
values (1, '黄蓉', 80, 100),
       (2, '东邪', 95, 100),
       (3, '梅超风', 90, 100);

5. 现有职工数据如下 请使用 insert 语句插入到worker表中

insert into worker
values (4,'尹志平', 67, 200),
       (5,'丘处机', 85, 200),
       (6,'周伯通', 93, 200);

6. 现有部门数据如下 请使用 insert 语句插入到departmen表中

insert into department(id, name)
values (100, '桃花岛'),
       (200, '全真教');

7. 求出所有 职工中 能力值高于70的人的信息

select *
from worker
where level > 70;

8. 求出所有 职工中 能力值前三甲的人的信息

select *
from worker
order by level desc
limit 3;

9. 求出所有 职工的 平均能力值

select avg(level)
from worker;

10. 使用 SQL 语句将 东邪 设置为桃花岛组的主管

update department
set mid = 2
where id = 100;

11. 使用 SQL 语句将 丘处机设置为全真教组的主管

update department
set mid = 5
where id = 200;

12. 求出每个部门的 平均能力值

select distinct dep_id,avg(level)
from worker
group by dep_id;

13. 求出部门平均值 > 40的所有部门及其相关平均能力值信息。

select distinct dep_id,avg(level)
from worker
group by dep_id
having avg(level) > 40;

14. 使用连接求出所有 部门名称、主管姓名、主管能力值

select d.name,w.name,w.level
from worker w
join department d on w.id = d.mid;

题目集三

1. 创建 公司 数据库 字符集 utf-8

create database gongsi character set utf8;
use gongsi;

2. 建立部门表,包含下列字段: 部门编号：整型，主键自动增长 部门名称：字符型 长度 20

create table if not exists t_department
(
    id   int primary key auto_increment not null,
    name varchar(20)
);

3. 建立员工表，包含下列字段 ：

/* 员工编号 ： 整型，主键自动增长，
   员工姓名 ： 字符型  长度为20
   员工性别 ： 枚举类型 {‘male’，‘female’}
   员工年龄 ： 整型
   员工工龄 ： 整型
   员工工资 ： 浮点类型，小数点保留两位
   员工部门编号：整型
 */
create table if not exists t_staff
(
    id        int primary key auto_increment not null comment '员工编号',
    name      varchar(20) comment '员工姓名',
    gender    enum ('male','female') DEFAULT NULL COMMENT '员工性别',
    age       int comment '员工年龄',
    seniority int comment '员工工龄',
    salary    decimal(10, 2) comment '员工工资',
    did       int comment '员工部门编号'
);

4. 将以下语句插入到表格中，确保插入成功；

insert into t_department
values (0, '人事部'),
       (0, '销售部'),
       (0, '开发部');

insert into t_staff
values (0, 'Tom', 'male', 25, 1, 4500, 1),
       (0, 'Jack', 'male', 28, 3, 6500, 1),
       (0, 'Rose', 'female', 24, 1, 8500, 2),
       (0, 'Alice', 'female', 24, 1, 8500, 2),
       (0, 'Alex', 'female', 26, 1, 8500, 2),
       (0, 'Tony', 'male', 30, 4, 12000, 2),
       (0, 'Lily', 'female', 35, 7, 25000, 2),
       (0, 'Lucy', 'female', 32, 4, 20000, 2),
       (0, 'Rose', 'female', 26, 2, 10000, 3),
       (0, 'Max', 'male', 28, 3, 15000, 3),
       (0, 'Jean', 'female', 22, 1, 10000, 3),
       (0, 'Kate', 'female', 23, 1, 10000, 3),
       (0, 'Karry', 'male', 42, 15, 50000, 3),
       (0, 'Finn', 'male', 35, 7, 30000, 3),
       (0, 'Kylo', 'male', 32, 6, 35000, 3),
       (0, 'Rose', 'female', 24, 1, 15000, 3);

5. 将Tony的部门调整到开发部

update t_staff
set did = 3
where name = 'Tony';

6. 将工资低于10000的员工加薪1000

update t_staff
set salary = salary + 1000
where salary < 10000;

7. 删除工资大于30000 的员工

delete
from t_staff
where salary > 30000;

题目集四

create table if not exists students
(
    studentNo int primary key auto_increment not null,
    name      varchar(10),
    sex       varchar(10),
    hometown  varchar(20),
    age       tinyint(4),
    class_id  int                            not null,
    card      varchar(20)
);

insert into students (name, sex, hometown, age, class_id, card)
values ('王昭君', '女', '北京', 20, 1, '340322199001247654'),
       ('诸葛亮', '男', '上海', 18, 2, '340322199002242354'),
       ('张飞', '男', '南京', 24, 3, '340322199003247654'),
       ('白起', '男', '安徽', 22, 4, '340322199005247654'),
       ('大乔', '女', '天津', 19, 3, '340322199004247654'),
       ('孙尚香', '女', '河北', 18, 1, '340322199006247654'),
       ('百里玄策', '男', '山西', 20, 2, '340322199007247654'),
       ('小乔', '女', '河南', 15, 3, null),
       ('百里守约', '男', '湖南', 21, 1, ''),
       ('妲己', '女', '广东', 26, 2, '340322199607247654'),
       ('李白', '男', '北京', 30, 4, '340322199005267754'),
       ('孙膑', '男', '新疆', 26, 3, '340322199000297655');

1. 查询学生"百里守约"的基本信息

select *
from students
where name = '百里守约';

2. 查询学生"百里守约"或”百里玄策”的基本信息

select *
from students
where name = '百里守约'
   or name = '百里玄策';

3. 查询姓"张"学生的姓名，年龄，班级

select name, age, class_id
from students
where name like '张%';

4. 查询姓名中含有"约"字的学生的基本信息

select *
from students
where name like '%约%';

5. 查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号

select studentNo, name, age, class_id, card
from students
where name like '孙__';

6. 查询姓"百"或者姓”孙”的学生的基本信息

select *
from students
where name like '百%'
   or name like '孙%';

7. 查询姓"百"并且家乡是"山西"的学生信息

select *
from students
where name like '百%'
  and hometown = '山西';

8. 查询家乡不是"北京"、“新疆”、“山东”、"上海"的学生的信息

select *
from students
where hometown not in ('北京', '新疆', '山东', '上海');

9. 查询姓"孙"，但是家乡不是"河北"的学生信息

select *
from students
where name like '孙%'
  and hometown != '河北';

10. 查询家乡不是"北京"、“新疆”、“山东”、"上海"的学生的信息

select *
from students
where hometown not in ('北京', '新疆', '山东', '上海');

11. 查询全部学生信息，并按照“性别”排序

select *
from students
order by sex;

12. 查询所有男生，并按年龄升序排序

select *
from students
where sex = '男'
order by age;

13. 统计共有多少个学生

select count(*)
from students;

14. 统计年龄大于20岁的学生有多少个

select count(*)
from students
where age > 20;

15. 统计男生的平均年龄

select avg(age)
from students
where sex = '男';

16. 查询1班学生中的最大年龄是多少

select max(age)
from students
where class_id = 1;

17. 统计2班男女生各有多少人

select sex, count(studentNo)
from students
where class_id = 2
group by sex;

18. 查询年龄最小的学生的全部信息

select *
from students
where age = (
    select min(age)
    from students
);