数据库练习三

小鸡食米

已于 2025-01-18 20:37:03 修改

阅读量561

点赞数 20

文章标签：数据库 mysql sql

于 2025-01-18 15:09:15 首次发布

本文链接：https://blog.youkuaiyun.com/hello2236/article/details/145225357

版权

一、作业

1. 建库建表

(1) 创建数据库 `mydbll_stu` 并使用数据库

CREATE DATABASE mydbll_stu;
USE mydbll_stu;

(2) 创建 `student` 表

CREATE TABLE student (
    id INT(10) NOT NULL UNIQUE PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(4),
    birth YEAR,
    department VARCHAR(20),
    address VARCHAR(50)
);

(3) 创建 `score` 表

CREATE TABLE score (
    id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    studid INT(10) NOT NULL,
    c_name VARCHAR(20),
    grade INT(10)
);

2. 插入数据

(1) 向 `student` 表插入记录

INSERT INTO student VALUES (901, '张三丰', '男', 2002, '计算机系', '北京市海淀区');
INSERT INTO student VALUES (902, '周全有', '男', 2000, '中文系', '北京市昌平区');
INSERT INTO student VALUES (903, '张思维', '女', 2003, '中文系', '湖南省永州市');
INSERT INTO student VALUES (904, '李广昌', '男', 1999, '英语系', '辽宁省泰新市');
INSERT INTO student VALUES (905, '王翰', '男', 2004, '英语系', '福建省厦门市');
INSERT INTO student VALUES (906, '王心凌', '女', 1998, '计算机系', '湖南省衡阳市');

(2) 向 `score` 表插入记录

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, '计算机', 49);
INSERT INTO score VALUES (NULL, 906, '英语', 83);

3. 查询操作

(1).分别查询student表和score表的所有记录

(2).查询student表的第2条到5条记录

(3).从student表中查询计算机系和英语系的学生的信息

(4).从student表中查询年龄小于22岁的学生信息

(5).从student表中查询每个院系有多少人

(6).从score表中查询每个科目的最高分

(7).查询李广昌的考试科目(c_name)和考试成绩(grade)

(8).用连接的方式查询所有学生的信息和考试信息

(9).计算每个学生的总成绩

(10).计算每个考试科目的平均成绩

(11).查询计算机成绩低于95的学生信息

(12).将计算机考试成绩按从高到低进行排序

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

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

(15).查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

二、练习

1.建库建表并且插入数据

mysql> use mydb7_openlab;
 
mysql> create  table  emp_new(sid int, name varchar(11), age int, worktime_start date, incoming int, dept2 int );
 
mysql> insert into emp_new values (1789,'张三',35,'1980/1/1',4000,101), (1674,'李四',32,'1983/4/1',3500,101), (1776,'王五',24,'1990/7/1',2000,101), (1568,'赵六',57,'1970/10/11',7500,102), (1564,'荣七',64,'1963/10/11',8500,102), (1879,'牛八',55,'1971/10/20',7300,103);
 
mysql> select * from emp_new;
+------+--------+------+----------------+----------+-------+
| sid  | name   | age  | worktime_start | incoming | dept2 |
+------+--------+------+----------------+----------+-------+
| 1789 | 张三   |   35 | 1980-01-01     |     4000 |   101 |
| 1674 | 李四   |   32 | 1983-04-01     |     3500 |   101 |
| 1776 | 王五   |   24 | 1990-07-01     |     2000 |   101 |
| 1568 | 赵六   |   57 | 1970-10-11     |     7500 |   102 |
| 1564 | 荣七   |   64 | 1963-10-11     |     8500 |   102 |
| 1879 | 牛八   |   55 | 1971-10-20     |     7300 |   103 |
+------+--------+------+----------------+----------+-------+
 
mysql> create table dept(dept1 int, dept_name varchar(11));
 
mysql> insert into dept values(101,"财务"),(102,"销售"),(103,"运维"),(104,"行政");
mysql> select * from dept;
+-------+-----------+
| dept1 | dept_name |
+-------+-----------+
|   101 | 财务      |
|   102 | 销售      |
|   103 | 运维      |
|   104 | 行政      |
+-------+-----------+

2.查询

1.找出销售部门中年纪最大的员工的姓名
mysql> select a.name,a.age from emp_new a join dept b  on b.dept1 = a.dept2 where b.dept_name = '销售' order by age desc limit1;
+--------+------+
| name   | age  |
+--------+------+
| 荣七   |   64 |
+--------+------+

2.求财务部门最低工资的员工姓名
mysql> select name from  emp_new a join  dept b on a.dept2=b.dept1 where dept_name = '财务' order by incoming asc limit 1;
+--------+
| name   |
+--------+
| 王五   |
+--------+


3.列出每个部门收入总和高于9000的部门名称
mysql> SELECT b.dept_name 
    -> FROM emp_new a 
    -> JOIN dept b ON a.dept2 = b.dept1 
    -> GROUP BY b.dept_name 
    -> HAVING SUM(a.incoming) > 9000;
+-----------+
| dept_name |
+-----------+
| 财务      |
| 销售      |
+-----------+

                         
4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
mysql> SELECT a.name, b.dept_name 
    -> FROM emp_new a 
    -> JOIN dept b ON a.dept2 = b.dept1 
    -> WHERE a.incoming BETWEEN 7500 AND 8500 
    -> ORDER BY a.age DESC 
    -> LIMIT 1;
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| 荣七   | 销售      |
+--------+-----------+


5.找出销售部门收入最低的员工入职时间
mysql> select worktime_start FROM emp_new a  JOIN dept b ON a.dept2 = b.dept1 where dept_name ='销售' order by incoming asc limit 1;
+----------------+
| worktime_start |
+----------------+
| 1970-10-11     |
+----------------+


6.财务部门收入超过2000元的员工姓名
mysql> select name FROM emp_new a  JOIN dept b ON a.dept2 = b.dept1 where dept_name ='财务' and incoming > 2000;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
+--------+


7.列出每个部门的平均收入及部门名称
mysql> select avg(incoming) ,dept_name FROM emp_new a  JOIN dept b ON a.dept2 = b.dept1 group by dept_name;
+---------------+-----------+
| avg(incoming) | dept_name |
+---------------+-----------+
|     3166.6667 | 财务      |
|     8000.0000 | 销售      |
|     7300.0000 | 运维      |
+---------------+-----------+


8.运维部入职员工的员工号
mysql> select sid FROM emp_new a  JOIN dept b ON a.dept2 = b.dept1 where dept_name ='运维' ;
+------+
| sid  |
+------+
| 1879 |
+------+


9.财务部门的收入总和
mysql> select dept_name, sum(incoming)from emp_new a JOIN dept b ON a.dept2 = b.dept1 where dept_name ='财务' group by dept_name ;
+-----------+---------------+
| dept_name | sum(incoming) |
+-----------+---------------+
| 财务      |          9500 |
+-----------+---------------+


10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
mysql> select * from emp_new   order by dept2 asc, worktime_start asc ;
+------+--------+------+----------------+----------+-------+
| sid  | name   | age  | worktime_start | incoming | dept2 |
+------+--------+------+----------------+----------+-------+
| 1789 | 张三   |   35 | 1980-01-01     |     4000 |   101 |
| 1674 | 李四   |   32 | 1983-04-01     |     3500 |   101 |
| 1776 | 王五   |   24 | 1990-07-01     |     2000 |   101 |
| 1564 | 荣七   |   64 | 1963-10-11     |     8500 |   102 |
| 1568 | 赵六   |   57 | 1970-10-11     |     7500 |   102 |
| 1879 | 牛八   |   55 | 1971-10-20     |     7300 |   103 |
+------+--------+------+----------------+----------+-------+

11.找出哪个部门还没有员工入职
mysql> select dept_name  from emp_new a right JOIN dept b ON a.dept2 = b.dept1 where sid is null;
+-----------+
| dept_name |
+-----------+
| 行政      |
+-----------+

12.列出部门员工收入大于7000的部门编号，部门名称
mysql> select dept2, dept_name from emp_new a right JOIN dept b ON a.dept2 = b.dept1 where incoming > 7000;
+-------+-----------+
| dept2 | dept_name |
+-------+-----------+
|   102 | 销售      |
|   102 | 销售      |
|   103 | 运维      |
+-------+-----------+

13.列出每一个部门的员工总收入及部门名称
mysql> select dept_name, sum(incoming)  from emp_new a right JOIN dept b ON a.dept2 = b.dept1 group by dept_name;
+-----------+---------------+
| dept_name | sum(incoming) |
+-----------+---------------+
| 财务      |          9500 |
| 销售      |         16000 |
| 运维      |          7300 |
| 行政      |          NULL |
+-----------+---------------+

14.列出每一个部门中年纪最大的员工姓名，部门名称
mysql> SELECT d.dept_name '部门名称', e.name '姓名' 
    -> FROM emp_new a 
    -> JOIN dept b ON a.dept2 = b.dept1 
    -> WHERE (a.dept2, a.age) IN (
    ->     SELECT dept2, MAX(age) 
    ->     FROM emp_new 
    ->     GROUP BY dept2
    -> );
+--------------+--------+
| 部门名称     | 姓名   |
+--------------+--------+
| 财务         | 张三   |
| 销售         | 荣七   |
| 运维         | 牛八   |
+--------------+--------+

15.求李四的收入及部门名称
mysql> select dept_name ,incoming from emp_new e join dept d on e.dept2 = d.dept1 where name ='李四' ;
+-----------+----------+
| dept_name | incoming |
+-----------+----------+
| 财务      |     3500 |
+-----------+----------+

16.列出部门员工数大于1个的部门名称

mysql> select d.dept_name from emp_new a join dept d on a.dept2 = d.dept1 group by d.dept_name having count(a.sid) > 1;
+-----------+
| dept_name |
+-----------+
| 财务      |
| 销售      |
+-----------+