MySQL 实验五：验证性实验

初始化数据

创建数据库和使用数据库

create database bumeninfo;
use bumeninfo;

创建表和插入数据

说明

#'#'为sql语句的注释
# comment 用来给字段添加注释，即字段描述。

创建 bumen 表

CREATE TABLE bumen(
    d_id int(4) NOT NULL comment '部门号',
    d_name varchar(20) NOT NULL comment '部门名称',
    function varchar(20) DEFAULT NULL comment '部门职能',
    address varchar(20) DEFAULT NULL comment '工作地点',
    PRIMARY KEY (d_id),
    UNIQUE KEY d_id (d_id),
    UNIQUE KEY d_name (d_name)
)default charset=utf8,collate=utf8_general_ci;#直接设置字符集，避免中文乱码

或

create table bumen(
    d_id int(4) primary key not null unique comment '部门号',
    d_name varchar(20) not null unique comment '部门名称',
    function varchar(20) comment '部门职能',
    address varchar(20) comment '工作地点'
)default charset=utf8,collate=utf8_general_ci;#直接设置字符集，避免中文乱码

创建 yuangong 表

create table yuangong(
    id int(4) primary key not null unique comment '员工号',
    name varchar(20) not null comment '姓名',
    sex varchar(4) not null comment '性别',
    age int(4) comment '年龄',#注意我这里年龄字段名是age而不是原文中的birthday。
    d_id int(4) not null comment '部门号',
    salary float comment '工资',
    address varchar(50) comment '家庭住址',
    foreign key (d_id) REFERENCES bumen(d_id)#设置本表的d_id字段为bumen表中d_id的外键（因为是注释，所以这句话不删除也能执行）
)default charset=utf8,collate=utf8_general_ci;#直接设置字符集，避免中文乱码

在 bumen 表中插入数据

insert into bumen values
(1001,'人事部','人事管理','北京'),
(1002,'科研部','研发产品','北京'),
(1003,'生产部','产品生产','天津'),
(1004,'销售部','产品销售','上海');

在 yuangong 表中插入数据

insert into yuangong values
(8001,'韩鹏','男',25,1002,4000,'北京市海淀区'),
(8002,'张峰','男',26,1001,2500,'北京市昌平区'),
(8003,'欧阳','男',20,1003,1500,'湖南省永州市'),
(8004,'王武','男',30,1001,3500,'北京市顺义区'),
(8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区'),
(8006,'呼延','男',28,1003,1800,'天津市南开区');

插曲：关于自增的小坑

#创建food表以及添加字段描述（注释）以及修改表的字符集和校验集
create table food(
    foodid int(4) primary key not null unique auto_increment comment '食品编号' ,
    name varchar(20) not null comment '食品名称',
    company varchar(30) not null comment '生产厂商',
    price float not null comment '价格（单位：元）',
    product_time year comment '生产年份',
    validity_time int(4) comment '保质期（单位：年）',
    address varchar(50) comment '厂址'
)default charset=utf8,collate=utf8_general_ci;
#我这里为了防止插入数据时出错，直接在创建表的时候修改了这张表的默认字符集和校验规则。

可以看到 foodid 这个字段设置了自增，一开始插入数据时，我以为 foodid 这个字段可以省略（因为它会从某个数（默认是 1）开始自增），于是插入数据时输入的语句如下：

insert into food values('QQ饼干','QQ饼干厂',2.5,'2019',3,'北京');

报错：ERROR 1136 (21S01): Column count doesn't match value count at row 1翻译过来就是列计数与第一行的数值不匹配，当时这个问题困扰了我好久。不过后来，还是找到了问题出现的原因，原因就在于我错误的认为自增的字段在插入时可以省略。**自增的字段插入时也需要填写数据占位(0 或’’或 null)**。语句更改为如下就不会报错了：

insert into food values(0,'QQ饼干','QQ饼干厂',2.5,'2019',3,'北京');
#或
insert into food values('','QQ饼干','QQ饼干厂',2.5,'2019',3,'北京');
#或
insert into food values(null,'QQ饼干','QQ饼干厂',2.5,'2019',3,'北京');

验证

1. 查询 yuangong 表的所有记录。

   SELECT * FROM yuangong;

   或者列出 yuangong 表的所有字段名称。

   SELECT id,name,sex,age,d_id,salary,address FROM yuangong;

2. 查询 yuangong 表的第四条到第五条记录。

   SELECT id,name,sex,age,d_id,salary,address FROM yuangong LIMIT 3,2;

3. 从 bumen 表查询部门号（d_id）、部门名称（d_name）和部门职能（function）。

   SELECT d_id,d_name,function FROM bumen;

4. 从 yuangong 表中查询人事部和科研部的员工的信息。先从 bumen 表查询出人事部和科研部的部门号。然后到 yuangong 表中去查询员工的信息。

   SELECT * FROM yuangong WHERE d_id = ANY(SELECT d_id FROM bumen WHERE d_name IN ('人事部','科研部'));

   或者使用下面的代码。

   SELECT * FROM yuangong WHERE d_id IN (SELECT d_id FROM bumen WHERE d_name='人事部' OR d_name='科研部');

5. 从 yuangong 表中查询年龄在 25 到 30 之间的员工的信息。可以通过两种方式来查询。第一种方式:

   SELECT * FROM yuangong WHERE age BETWEEN 25 AND 30;

   第二种方式:

   SELECT * FROM yuangong WHERE age >= 25 AND age <= 30;

6. 查询每个部门有多少员工。先按部门号进行分组，然后用 COUNT()函数来计算每组的人数。

   SELECT d_id,COUNT(id) FROM yuangong GROUP BY d_id;

   或者给 COUNT(id)取名为 sum。

   SELECT d_id,COUNT(id) AS sum FROM yuangong GROUP BY d_id;

7. 查询每个部门的最高工资。先按部门号进行分组，然后用 MAX()函数来计算最大值。

   SELECT d_id,MAX(salary) FROM yuangong GROUP BY d_id;

8. 用左连接的方式查询 bumen 表和 yuangong 表。使用 LEFTJOINON 来实现左连接。

   SELECT bumen.d_id,d_name,function,bumen.address,id,name,age,sex,salary,yuangong.address FROM bumen LEFT JOIN yuangong ON yuangong.d_id = bumen.d_id;

9. 计算每个部门的总工资。先按部门号进行分组，然后用 SUM()函数来求和。

   SELECT d_id,SUM(salary) FROM yuangong GROUP BY d_id;

10. 查询 yuangong 表，按照工资从高到低的顺序排列。

    SELECT * FROM yuangong ORDER BY salary DESC;

11. 从 bumen 表和 yuangong 表中查询出部门号，然后使用 UNION 合并查询结果。

    SELECT d_id FROM yuangong UNION SELECT d_id FROM bumen;

12. 查询家是北京市员工的姓名、年龄、家庭住址。这里使用 LIKE 关键字。

    SELECT name,age,address FROM yuangong WHERE address LIKE'北京%';

强烈建议先自己模仿验证性实验里的查询语句，尝试写出来设计性实验里的问题，如果你尝试了，但遇到了一些困难，可以查看这里实验五：设计性实验。

如果你有什么关于本文章的问题，欢迎留言讨论。