devbox
空白诗007
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Mysql数据库内联查询、左连接查询、右连接查询、自连接查询介绍，库和表的相关练习

作者：空白诗007 | 2024-08-22 07:44:16

赞

踩

左连接查询

目录

一.内联查询

1.inner join:只查询键值一致（交叉）的部分 

2.演示

二.左连接

1.left join:以左表为标准，查询输出左表中没有的字段信息

2.演示

三.右连接

1.right join:以右表为标准，查询输出右表中没有的字段信息

2.演示

四.自连接

将一张表通过别名虚拟为两张表，查询比较其中的不同字段

五.练习

1.新建/删除库和字段

2.单/多表查询

一.内联查询

1.inner join:只查询键值一致（交叉）的部分 

select (*|指定字段) from 表1 inner join 表2 on 匹配条件;

可以配合where语句、order by语句、逻辑语句等一起使用

2.演示

  1. --t5的id和t6的id具有外键关系
  2. mysql8.0 [SLB]>select * from t5;
  3. +----+---------------+-----------+
  4. | id | name          | major     |
  5. +----+---------------+-----------+
  6. |  1 | sulibao       | dianshang |
  7. |  2 | lixinjin      | dianshang |
  8. |  3 | zhanghongyuan | jike      |
  9. +----+---------------+-----------+
  10. 3 rows in set (0.00 sec)
  11.  
  12. mysql8.0 [SLB]>select * from t6;
  13. +------+--------+------+
  14. | age  | weight | id   |
  15. +------+--------+------+
  16. |   18 |   75.5 |    1 |
  17. |   19 |   75.5 |    2 |
  18. |   25 |     90 |    3 |
  19. +------+--------+------+
  20. 3 rows in set (0.00 sec)
  21.  
  22. mysql8.0 [SLB]>select * from t5 inner join t6 on t5.id=t6.id;  --联合查询所有字段
  23. +----+---------------+-----------+------+--------+------+
  24. | id | name          | major     | age  | weight | id   |
  25. +----+---------------+-----------+------+--------+------+
  26. |  1 | sulibao       | dianshang |   18 |   75.5 |    1 |
  27. |  2 | lixinjin      | dianshang |   19 |   75.5 |    2 |
  28. |  3 | zhanghongyuan | jike      |   25 |     90 |    3 |
  29. +----+---------------+-----------+------+--------+------+
  30. 3 rows in set (0.00 sec)
  31.  
  32. mysql8.0 [SLB]>select t5.id,t5.name,t5.major,t6.age,t6.weight from t5 inner join t6 on t
  33. 5.id=t6.id;
  34. --联合查询指定字段
  35. +----+---------------+-----------+------+--------+
  36. | id | name          | major     | age  | weight |
  37. +----+---------------+-----------+------+--------+
  38. |  1 | sulibao       | dianshang |   18 |   75.5 |
  39. |  2 | lixinjin      | dianshang |   19 |   75.5 |
  40. |  3 | zhanghongyuan | jike      |   25 |     90 |
  41. +----+---------------+-----------+------+--------+
  42. 3 rows in set (0.00 sec)
  43.  
  44. mysql8.0 [SLB]>select a.id,a.name,a.major,b.age,b.weight from t5 a inner join t6 b on a.id=b.id;
  45. --也可以为名称较长的表取别名
  46. +----+---------------+-----------+------+--------+
  47. | id | name          | major     | age  | weight |
  48. +----+---------------+-----------+------+--------+
  49. |  1 | sulibao       | dianshang |   18 |   75.5 |
  50. |  2 | lixinjin      | dianshang |   19 |   75.5 |
  51. |  3 | zhanghongyuan | jike      |   25 |     90 |
  52. +----+---------------+-----------+------+--------+
  53. 3 rows in set (0.00 sec)
  54.

二.左连接

1.left join:以左表为标准，查询输出左表中没有的字段信息

select (*|指定字段) from 左表 left join 从表 on 匹配条件;

可以配合where语句、order by语句、逻辑语句等一起使用

2.演示

(1)

  1. mysql8.0 [SLB]>select * from t5;
  2. +----+---------------+-----------+
  3. | id | name          | major     |
  4. +----+---------------+-----------+
  5. |  1 | sulibao       | dianshang |
  6. |  2 | lixinjin      | dianshang |
  7. |  3 | zhanghongyuan | jike      |
  8. +----+---------------+-----------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql8.0 [SLB]>select * from t6;
  12. +------+--------+------+
  13. | age  | weight | id   |
  14. +------+--------+------+
  15. |   18 |   75.5 |    1 |
  16. |   19 |   75.5 |    2 |
  17. |   25 |     90 |    3 |
  18. +------+--------+------+
  19. 3 rows in set (0.00 sec)
  20.  
  21. mysql8.0 [SLB]>select * from t5 left join t6 on t5.id=t6.id;
  22. +----+---------------+-----------+------+--------+------+
  23. | id | name          | major     | age  | weight | id   |
  24. +----+---------------+-----------+------+--------+------+
  25. |  1 | sulibao       | dianshang |   18 |   75.5 |    1 |
  26. |  2 | lixinjin      | dianshang |   19 |   75.5 |    2 |
  27. |  3 | zhanghongyuan | jike      |   25 |     90 |    3 |
  28. +----+---------------+-----------+------+--------+------+
  29. 3 rows in set (0.01 sec)
  30.  
  31. mysql8.0 [SLB]>select * from t6 left join t5 on t5.id=t6.id;
  32. +------+--------+------+------+---------------+-----------+
  33. | age  | weight | id   | id   | name          | major     |
  34. +------+--------+------+------+---------------+-----------+
  35. |   18 |   75.5 |    1 |    1 | sulibao       | dianshang |
  36. |   19 |   75.5 |    2 |    2 | lixinjin      | dianshang |
  37. |   25 |     90 |    3 |    3 | zhanghongyuan | jike      |
  38. +------+--------+------+------+---------------+-----------+
  39. 3 rows in set (0.00 sec)

(2)

左表有的字段而从表没有对应内容则映射为null

  1. mysql8.0 [SLB]>insert t5 values(4,"niexincheng","dianshang");
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql8.0 [SLB]>select * from t5 left join t6 on t5.id=t6.id;
  5. +----+---------------+-----------+------+--------+------+
  6. | id | name          | major     | age  | weight | id   |
  7. +----+---------------+-----------+------+--------+------+
  8. |  1 | sulibao       | dianshang |   18 |   75.5 |    1 |
  9. |  2 | lixinjin      | dianshang |   19 |   75.5 |    2 |
  10. |  3 | zhanghongyuan | jike      |   25 |     90 |    3 |
  11. |  4 | niexincheng   | dianshang | NULL |   NULL | NULL |
  12. +----+---------------+-----------+------+--------+------+
  13. 4 rows in set (0.00 sec)

左表没有的字段而从表有则不查询这些字段内容

  1. mysql8.0 [SLB]>select * from t6 left join t5 on t5.id=t6.id;
  2. +------+--------+------+------+---------------+-----------+
  3. | age  | weight | id   | id   | name          | major     |
  4. +------+--------+------+------+---------------+-----------+
  5. |   18 |   75.5 |    1 |    1 | sulibao       | dianshang |
  6. |   19 |   75.5 |    2 |    2 | lixinjin      | dianshang |
  7. |   25 |     90 |    3 |    3 | zhanghongyuan | jike      |
  8. +------+--------+------+------+---------------+-----------+
  9. 3 rows in set (0.00 sec)

三.右连接

1.right join:以右表为标准，查询输出右表中没有的字段信息

2.演示

用法同左连接类似

  1. mysql8.0 [SLB]>select * from t6 right join t5 on t5.id=t6.id;
  2. +------+--------+------+----+---------------+-----------+
  3. | age  | weight | id   | id | name          | major     |
  4. +------+--------+------+----+---------------+-----------+
  5. |   18 |   75.5 |    1 |  1 | sulibao       | dianshang |
  6. |   19 |   75.5 |    2 |  2 | lixinjin      | dianshang |
  7. |   25 |     90 |    3 |  3 | zhanghongyuan | jike      |
  8. | NULL |   NULL | NULL |  4 | niexincheng   | dianshang |
  9. +------+--------+------+----+---------------+-----------+
  10. 4 rows in set (0.01 sec)
  11.  
  12. mysql8.0 [SLB]>select * from t5 right join t6 on t5.id=t6.id;
  13. +------+---------------+-----------+------+--------+------+
  14. | id   | name          | major     | age  | weight | id   |
  15. +------+---------------+-----------+------+--------+------+
  16. |    1 | sulibao       | dianshang |   18 |   75.5 |    1 |
  17. |    2 | lixinjin      | dianshang |   19 |   75.5 |    2 |
  18. |    3 | zhanghongyuan | jike      |   25 |     90 |    3 |
  19. +------+---------------+-----------+------+--------+------+
  20. 3 rows in set (0.00 sec)

四.自连接

将一张表通过别名虚拟为两张表，查询比较其中的不同字段

示例：yl表中查询yage<lage的所有信息，yl表中查询yage>lage的所有信息

  1. mysql8.0 [SLB]>select y1.* from yl y1 join yl y2 on y1.lname=y2.lname and y1.yage<y2.lage;
  2. +----------+------+------+-------+
  3. | name     | yage | lage | lname |
  4. +----------+------+------+-------+
  5. | sulibao  |   20 |   25 | lh    |
  6. | lixinjin |   21 |   29 | cq    |
  7. +----------+------+------+-------+
  8. 2 rows in set (0.00 sec)
  9.  
  10. mysql8.0 [SLB]>select * from yl;
  11. +-------------+------+------+-------+
  12. | name        | yage | lage | lname |
  13. +-------------+------+------+-------+
  14. | sulibao     |   20 |   25 | lh    |
  15. | lixinjin    |   21 |   29 | cq    |
  16. | niexincheng |   28 |   25 | zxw   |
  17. +-------------+------+------+-------+
  18. 3 rows in set (0.00 sec)
  19.  
  20. mysql8.0 [SLB]>select y1.* from yl y1 join yl y2 on y1.lname=y2.lname and y1.yage>y2.lage;
  21. +-------------+------+------+-------+
  22. | name        | yage | lage | lname |
  23. +-------------+------+------+-------+
  24. | niexincheng |   28 |   25 | zxw   |
  25. +-------------+------+------+-------+
  26. 1 row in set (0.01 sec)

五.练习

1.新建/删除库和字段

（1）创建数据库，删除数据库，查询创建数据库的语句，使用数据库，查询当前默认的数据库以及使用的编码方式校验规则

  1. mysql8.0 [S1]>create database S1;
  2. mysql8.0 [S1]>show create database S1;
  3. +----------+------------------------------------------------------------------------------------------------------------------------------+
  4. | Database | Create Database                                                                                                              |
  5. +----------+------------------------------------------------------------------------------------------------------------------------------+
  6. | S1       | CREATE DATABASE `S1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
  7. +----------+------------------------------------------------------------------------------------------------------------------------------+
  8. 1 row in set (0.00 sec)
  9. mysql8.0 [S1]>use S1;
  10. mysql8.0 [S1]>show variables like 'character%';
  11. +--------------------------+----------------------------+
  12. | Variable_name            | Value                      |
  13. +--------------------------+----------------------------+
  14. | character_set_client     | utf8mb4                    |
  15. | character_set_connection | utf8mb4                    |
  16. | character_set_database   | utf8mb4                    |
  17. | character_set_filesystem | binary                     |
  18. | character_set_results    | utf8mb4                    |
  19. | character_set_server     | utf8mb4                    |
  20. | character_set_system     | utf8mb3                    |
  21. | character_sets_dir       | /usr/share/mysql/charsets/ |
  22. +--------------------------+----------------------------+
  23. 8 rows in set (0.01 sec)
  24.  
  25. mysql8.0 [S1]>show variables like 'collation%';
  26. +----------------------+--------------------+
  27. | Variable_name        | Value              |
  28. +----------------------+--------------------+
  29. | collation_connection | utf8mb4_0900_ai_ci |
  30. | collation_database   | utf8mb4_0900_ai_ci |
  31. | collation_server     | utf8mb4_0900_ai_ci |
  32. +----------------------+--------------------+
  33. 3 rows in set (0.01 sec)
  34. mysql8.0 [S1]>drop database S1;
  35. Query OK, 0 rows affected (0.03 sec)
  36.  
  37. mysql8.0 [(none)]>

（2）在一张表中定义多个字段，要使用今天提到的所有的数据类型（数字，文本，日期）

  1. mysql8.0 [SLB]>create table t6 (
  2.     -> name char(10),
  3.     -> email varchar(30),
  4.     -> address tinytext,
  5.     -> classmates text,
  6.     -> mess blob,
  7.     -> mateaddress mediumblob,
  8.     -> data mediumblob,
  9.     -> phonelocation longtext,
  10.     -> peopleid longblob,
  11.     -> sex enum('male','female'),
  12.     -> clothcolor set('red','blue','black'),
  13.     -> num tinyint(127), 
  14.     -> phonelast smallint(255),
  15.     -> port mediumint(255),
  16.     -> id int(255),
  17.     -> idcardid bigint(255),
  18.     -> score float(100,9), 
  19.     -> scoresum double(100,0),
  20.     -> averscore decimal(65,0),
  21.     -> today date,
  22.     -> hcomeback datetime,
  23.     -> log timestamp,
  24.     -> nowtime time,
  25.     -> thisyear year);
  26. Query OK, 0 rows affected, 7 warnings (0.07 sec)
  27.  
  28. mysql8.0 [SLB]>desc t6;
  29. +---------------+---------------------------+------+-----+---------+-------+
  30. | Field         | Type                      | Null | Key | Default | Extra |
  31. +---------------+---------------------------+------+-----+---------+-------+
  32. | name          | char(10)                  | YES  |     | NULL    |       |
  33. | email         | varchar(30)               | YES  |     | NULL    |       |
  34. | address       | tinytext                  | YES  |     | NULL    |       |
  35. | classmates    | text                      | YES  |     | NULL    |       |
  36. | mess          | blob                      | YES  |     | NULL    |       |
  37. | mateaddress   | mediumblob                | YES  |     | NULL    |       |
  38. | data          | mediumblob                | YES  |     | NULL    |       |
  39. | phonelocation | longtext                  | YES  |     | NULL    |       |
  40. | peopleid      | longblob                  | YES  |     | NULL    |       |
  41. | sex           | enum('male','female')     | YES  |     | NULL    |       |
  42. | clothcolor    | set('red','blue','black') | YES  |     | NULL    |       |
  43. | num           | tinyint                   | YES  |     | NULL    |       |
  44. | phonelast     | smallint                  | YES  |     | NULL    |       |
  45. | port          | mediumint                 | YES  |     | NULL    |       |
  46. | id            | int                       | YES  |     | NULL    |       |
  47. | idcardid      | bigint                    | YES  |     | NULL    |       |
  48. | score         | float(100,9)              | YES  |     | NULL    |       |
  49. | scoresum      | double(100,0)             | YES  |     | NULL    |       |
  50. | averscore     | decimal(65,0)             | YES  |     | NULL    |       |
  51. | today         | date                      | YES  |     | NULL    |       |
  52. | hcomeback     | datetime                  | YES  |     | NULL    |       |
  53. | log           | timestamp                 | YES  |     | NULL    |       |
  54. | nowtime       | time                      | YES  |     | NULL    |       |
  55. | thisyear      | year                      | YES  |     | NULL    |       |
  56. +---------------+---------------------------+------+-----+---------+-------+
  57. 24 rows in set (0.01 sec)
  58.

2.单/多表查询

题1

表信息

  1. CREATE TABLE `worker` (
  2.  `部门号` int(11) NOT NULL,
  3.  `职工号` int(11) NOT NULL,
  4.  `工作时间` date NOT NULL,
  5.  `工资` float(8,2) NOT NULL,
  6.  `政治面貌` varchar(10) NOT NULL DEFAULT '群众',
  7.  `姓名` varchar(20) NOT NULL,
  8.  `出生日期` date NOT NULL,
  9.  PRIMARY KEY (`职工号`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  11. INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1001, '2015-5-4', 3500.00, '群众', '张三', '1990-7-1');
  12. INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (101, 1002, '2017-2-6', 3200.00, '团员', '李四', '1997-2-8');
  13. INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1003, '2011-1-4', 8500.00, '党员', '王亮', '1983-6-8');
  14. INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1004, '2016-10-10', 5500.00, '群众', '赵六', '1994-9-5');
  15. INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1005, '2014-4-1', 4800.00, '党员', '钱七', '1992-12-30');
  16. INSERT INTO `worker` (`部门号`, `职工号`, `工作时间`, `工资`, `政治面貌`, `姓名`, `出生日期`) VALUES (102, 1006, '2017-5-5', 4500.00, '党员', '孙八', '1996-9-2');

要求

（1）显示所有职工的基本信息  

  1. mysql8.0 [SLB]>select * from worker;
  2. +-----------+-----------+--------------+---------+--------------+--
  3. | 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓
  4. +-----------+-----------+--------------+---------+--------------+--
  5. |       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张
  6. |       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李
  7. |       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王
  8. |       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵
  9. |       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱
  10. |       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙
  11. +-----------+-----------+--------------+---------+--------------+--
  12. 6 rows in set (0.00 sec)

（2）查询所有职工所属部门的部门号，不显示重复的部门号 

  1. mysql8.0 [SLB]>select distinct 部门号 from worker;
  2. +-----------+
  3. | 部门号    |
  4. +-----------+
  5. |       101 |
  6. |       102 |
  7. +-----------+
  8. 2 rows in set (0.02 sec)

（3）求出所有职工的人数

  1. mysql8.0 [SLB]>select count(姓名) from worker;;
  2. +---------------+
  3. | count(姓名)   |
  4. +---------------+
  5. |             6 |
  6. +---------------+
  7. 1 row in set (0.02 sec)

（4）列出最高工资和最低工资

  1. mysql8.0 [SLB]>select max(工资),min(工资) from worker;
  2. +-------------+-------------+
  3. | max(工资)   | min(工资)   |
  4. +-------------+-------------+
  5. |     8500.00 |     3200.00 |
  6. +-------------+-------------+
  7. 1 row in set (0.01 sec)

（5）列出职工的平均工资和总工资

  1. mysql8.0 [SLB]>select avg(工资),sum(工资) from worker;
  2. +-------------+-------------+
  3. | avg(工资)   | sum(工资)   |
  4. +-------------+-------------+
  5. | 5000.000000 |    30000.00 |
  6. +-------------+-------------+
  7. 1 row in set (0.01 sec)

（6）创建一个只有职工号、姓名和参加工作日期的新表，名为工作日期表

  1. mysql8.0 [SLB]>create table 工作日期表 select 职工号,姓名,工作时间 from worker;
  2. Query OK, 6 rows affected (0.05 sec)
  3. Records: 6  Duplicates: 0  Warnings: 0
  4. mysql8.0 [SLB]>select * from 工作日期表;
  5. +-----------+--------+--------------+
  6. | 职工号    | 姓名   | 工作时间     |
  7. +-----------+--------+--------------+
  8. |      1001 | 张三   | 2015-05-04   |
  9. |      1002 | 李四   | 2017-02-06   |
  10. |      1003 | 王亮   | 2011-01-04   |
  11. |      1004 | 赵六   | 2016-10-10   |
  12. |      1005 | 钱七   | 2014-04-01   |
  13. |      1006 | 孙八   | 2017-05-05   |
  14. +-----------+--------+--------------+
  15. 6 rows in set (0.00 sec)

（7）显示所有女职工的年龄，手动添加

  1. mysql8.0 [SLB]>select * from worker;
  2. +-----------+-----------+--------------+---------+--------------+--------+--------------+--------+--------+
  3. | 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     | 性别   | 年龄   |
  4. +-----------+-----------+--------------+---------+--------------+--------+--------------+--------+--------+
  5. |       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   | 女     |     29 |
  6. |       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   | 男     |     33 |
  7. |       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   | 男     |     25 |
  8. |       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   | 女     |     19 |
  9. |       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   | 女     |     18 |
  10. |       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   | 女     |     29 |
  11. +-----------+-----------+--------------+---------+--------------+--------+--------------+--------+--------+
  12. 6 rows in set (0.01 sec)
  13. mysql8.0 [SLB]>select 年龄 from worker where 性别='女';
  14. +--------+
  15. | 年龄   |
  16. +--------+
  17. |     29 |
  18. |     19 |
  19. |     18 |
  20. |     29 |
  21. +--------+
  22. 4 rows in set (0.00 sec)

（8）列出所有姓刘的职工的职工号、姓名和出生日期

  1. mysql8.0 [SLB]>select 职工号,姓名,出生日期 from worker where 姓 名 regexp '^刘';
  2. Empty set (0.01 sec)

（9）列出1960年以前出生的职工的姓名、参加工作日期

  1. mysql8.0 [SLB]>select 姓名,工作时间 from worker where 出生日期 < 1960-01-01;
  2. Empty set, 1 warning (0.00 sec)

（10）列出工资在1000－2000之间的所有职工姓名

  1. mysql8.0 [SLB]>select 姓名 from worker where 工资 >= 1000 and 工资 <= 2000;
  2. Empty set (0.00 sec)

（11）列出所有陈姓和李姓的职工姓名

  1. mysql8.0 [SLB]>select 姓名 from worker where 姓名 regexp '^陈' or 姓名 regexp '^李';
  2. +--------+
  3. | 姓名   |
  4. +--------+
  5. | 李四   |
  6. +--------+
  7. 1 row in set (0.00 sec)

（12）列出所有部门号为2和3的职工号、姓名、党员否

  1. mysql8.0 [SLB]>select 职工号,姓名,政治面貌 from worker where 部门号=103 or 部门号=102;
  2. +-----------+--------+--------------+
  3. | 职工号    | 姓名   | 政治面貌     |
  4. +-----------+--------+--------------+
  5. |      1003 | 王亮   | 党员         |
  6. |      1004 | 赵六   | 群众         |
  7. |      1005 | 钱七   | 党员         |
  8. |      1006 | 孙八   | 党员         |
  9. +-----------+--------+--------------+
  10. 4 rows in set (0.00 sec)

（13）将职工表worker中的职工按出生的先后顺序排序

  1. mysql8.0 [SLB]>select 姓名 from worker order by 出生日期;
  2. +--------+
  3. | 姓名   |
  4. +--------+
  5. | 王亮   |
  6. | 张三   |
  7. | 钱七   |
  8. | 赵六   |
  9. | 孙八   |
  10. | 李四   |
  11. +--------+
  12. 6 rows in set (0.00 sec)

（14）显示工资最高的前3名职工的职工号和姓名

  1. mysql8.0 [SLB]>select 职工号,姓名 from worker order by 工资 desc limit 3;
  2. +-----------+--------+
  3. | 职工号    | 姓名   |
  4. +-----------+--------+
  5. |      1003 | 王亮   |
  6. |      1004 | 赵六   |
  7. |      1005 | 钱七   |
  8. +-----------+--------+
  9. 3 rows in set (0.00 sec)

（15）求出各部门党员的人数

  1. mysql8.0 [SLB]>select 部门号,count(*) from worker where 政治面貌='党员' group by 部门号;
  2. +-----------+----------+
  3. | 部门号    | count(*) |
  4. +-----------+----------+
  5. |       102 |        3 |
  6. +-----------+----------+
  7. 1 row in set (0.01 sec)

（16）统计各部门的工资和平均工资

  1. mysql8.0 [SLB]>select 部门号,sum(工资),avg(工资) from worker group by 部门号;
  2. +-----------+-------------+-------------+
  3. | 部门号    | sum(工资)   | avg(工资)   |
  4. +-----------+-------------+-------------+
  5. |       101 |     6700.00 | 3350.000000 |
  6. |       102 |    23300.00 | 5825.000000 |
  7. +-----------+-------------+-------------+
  8. 2 rows in set (0.00 sec)

（17）列出总人数大于4的部门号和总人数

  1. mysql8.0 [SLB]>select 部门号,count(姓名) as 总人数 from worker group by 部门号 having count(姓名)>=4;
  2. +-----------+-----------+
  3. | 部门号    | 总人数    |
  4. +-----------+-----------+
  5. |       102 |         4 |
  6. +-----------+-----------+
  7. 1 row in set (0.00 sec)

题2

表信息

1cebb8414a6149778bab34a663b39c88.png

要求

（1）查询student表的所有记录

  1. mysql8.0 [SLB1]>select * from student;
  2. +-----+-----------+------+-------+--------------+--------------------+
  3. | id  | name      | sex  | birth | department   | address            |
  4. +-----+-----------+------+-------+--------------+--------------------+
  5. | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
  6. | 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |
  7. | 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |
  8. | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
  9. | 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
  10. | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
  11. +-----+-----------+------+-------+--------------+--------------------+
  12. 6 rows in set (0.00 sec)

（2）查询student表的第2到4条记录

  1. mysql8.0 [SLB1]>select * from student limit 1,3;
  2. +-----+-----------+------+-------+------------+--------------------+
  3. | id  | name      | sex  | birth | department | address            |
  4. +-----+-----------+------+-------+------------+--------------------+
  5. | 902 | 张老二    | 男   |  1986 | 中文系     | 北京市昌平区       |
  6. | 903 | 张三      | 女   |  1990 | 中文系     | 湖南省永州市       |
  7. | 904 | 李四      | 男   |  1990 | 英语系     | 辽宁省阜新市       |
  8. +-----+-----------+------+-------+------------+--------------------+
  9. 3 rows in set (0.00 sec)

（3）从student表查询所有的学生的学号（id），姓名（name），和院系（department）的信息

  1. mysql8.0 [SLB1]>select id,name,department from student;
  2. +-----+-----------+--------------+
  3. | id  | name      | department   |
  4. +-----+-----------+--------------+
  5. | 901 | 张老大    | 计算机系     |
  6. | 902 | 张老二    | 中文系       |
  7. | 903 | 张三      | 中文系       |
  8. | 904 | 李四      | 英语系       |
  9. | 905 | 王五      | 英语系       |
  10. | 906 | 王六      | 计算机系     |
  11. +-----+-----------+--------------+
  12. 6 rows in set (0.00 sec)

（4）从student表中查询计算机系和英语系的学生的信息

  1. mysql8.0 [SLB1]>select * from student where department='计算机系' or department='英语系';
  2. +-----+-----------+------+-------+--------------+--------------------+
  3. | id  | name      | sex  | birth | department   | address            |
  4. +-----+-----------+------+-------+--------------+--------------------+
  5. | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
  6. | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
  7. | 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
  8. | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
  9. +-----+-----------+------+-------+--------------+--------------------+
  10. 4 rows in set (0.01 sec)

（5）从studnet表中查询年龄18-22岁的学生信息

  1. mysql8.0 [SLB1]>select * from student where (select year(curdate())-birth) between 18 and 22;
  2. Empty set (0.01 sec)
  3. mysql8.0 [SLB1]>select * from student where (select year(curdate())-birth) between 35 and 40;
  4. +-----+-----------+------+-------+--------------+--------------------+
  5. | id  | name      | sex  | birth | department   | address            |
  6. +-----+-----------+------+-------+--------------+--------------------+
  7. | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
  8. | 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |
  9. | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
  10. +-----+-----------+------+-------+--------------+--------------------+
  11. 3 rows in set (0.00 sec)

（6）从student表中查询每个院系有多少人

  1. mysql8.0 [SLB1]>select department,count(*) from student group by department;
  2. +--------------+----------+
  3. | department   | count(*) |
  4. +--------------+----------+
  5. | 计算机系     |        2 |
  6. | 中文系       |        2 |
  7. | 英语系       |        2 |
  8. +--------------+----------+
  9. 3 rows in set (0.00 sec)

（7）从score表中查询每个科目的最高分

  1. mysql8.0 [SLB1]>select c_name,max(grade) from score group by c_name;
  2. +-----------+------------+
  3. | c_name    | max(grade) |
  4. +-----------+------------+
  5. | 计算机    |         98 |
  6. | 英语      |         94 |
  7. | 中文      |         95 |
  8. +-----------+------------+
  9. 3 rows in set (0.00 sec)

（8）查询李四的考试科目（c_name）和考试成绩（grade）

  1. mysql8.0 [SLB1]>select c_name,grade from score as sc,student as st where sc.stu_id=st.id and name='李四';
  2. +-----------+-------+
  3. | c_name    | grade |
  4. +-----------+-------+
  5. | 计算机    |    70 |
  6. | 英语      |    92 |
  7. +-----------+-------+
  8. 2 rows in set (0.00 sec)

（9）用连接的方式查询所有学生的信息和考试信息

  1. mysql8.0 [SLB1]>select st.id,st.name,st.sex,st.birth,st.department,st.address,sc.id as corid,sc.c_name,sc.grade from student as st inner join score as sc onst.id=sc.stu_id;
  2. +-----+-----------+------+-------+--------------+--------------------+-------+-----------+-------+
  3. | id  | name      | sex  | birth | department   | address            | corid | c_name    | grade |
  4. +-----+-----------+------+-------+--------------+--------------------+-------+-----------+-------+
  5. | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |     1 | 计算机    |    98 |
  6. | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |     2 | 英语      |    80 |
  7. | 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |     3 | 计算机    |    65 |
  8. | 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |     4 | 中文      |    88 |
  9. | 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |     5 | 中文      |    95 |
  10. | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |     6 | 计算机    |    70 |
  11. | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |     7 | 英语      |    92 |
  12. | 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |     8 | 英语      |    94 |
  13. | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |     9 | 计算机    |    90 |
  14. | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |    10 | 英语      |    85 |
  15. +-----+-----------+------+-------+--------------+--------------------+-------+-----------+-------+
  16. 10 rows in set (0.00 sec)

（10）计算每个学生的总成绩

  1. mysql8.0 [SLB1]>select st.name,sum(sc.grade) from student as st inner join score as sc on st.id=sc.stu_id group by name;
  2. +-----------+---------------+
  3. | name      | sum(sc.grade) |
  4. +-----------+---------------+
  5. | 张老大    |           178 |
  6. | 张老二    |           153 |
  7. | 张三      |            95 |
  8. | 李四      |           162 |
  9. | 王五      |            94 |
  10. | 王六      |           175 |
  11. +-----------+---------------+
  12. 6 rows in set (0.00 sec)

（11）计算每个考试科目的平均成绩

  1. mysql8.0 [SLB1]>select c_name,avg(grade) from score group by c_name;
  2. +-----------+------------+
  3. | c_name    | avg(grade) |
  4. +-----------+------------+
  5. | 计算机    |    80.7500 |
  6. | 英语      |    87.7500 |
  7. | 中文      |    91.5000 |
  8. +-----------+------------+
  9. 3 rows in set (0.01 sec)

（12）查询计算机成绩低于95的学生信息

  1. mysql8.0 [SLB1]>select st.id,st.name,st.sex,st.birth,st.department,st.address,sc.c_name,sc.grade from student as st inner join score as sc on st.id=sc.stu_id where sc.c_name='计算机' and sc.grade<95;
  2. +-----+-----------+------+-------+--------------+--------------------+-----------+-------+
  3. | id  | name      | sex  | birth | department   | address            | c_name    | grade |
  4. +-----+-----------+------+-------+--------------+--------------------+-----------+-------+
  5. | 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       | 计算机    |    65 |
  6. | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       | 计算机    |    70 |
  7. | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 计算机    |    90 |
  8. +-----+-----------+------+-------+--------------+--------------------+-----------+-------+
  9. 3 rows in set (0.00 sec)

（13）查询同时参加计算机考试和英语考试的学生的信息

  1. mysql8.0 [SLB1]>select st.id,st.name,st.sex,st.birth,st.department,st.address,concat(sc1.c_name,sc2.c_name) as exam from student as st,(select * from score where c_name='计算机') as sc1 inner join (select * from score where c_name='英语') as sc2  on sc1.stu_id=sc2.stu_id where st.id=sc1.stu_id;
  2. +-----+-----------+------+-------+--------------+--------------------+-----------------+
  3. | id  | name      | sex  | birth | department   | address            | exam            |
  4. +-----+-----------+------+-------+--------------+--------------------+-----------------+
  5. | 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       | 计算机英语      |
  6. | 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       | 计算机英语      |
  7. | 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 计算机英语      |
  8. +-----+-----------+------+-------+--------------+--------------------+-----------------+
  9. 3 rows in set (0.00 sec)

（14）将计算机考试成绩按从高到低进行排序

  1. mysql8.0 [SLB1]>select * from score order by grade desc;
  2. +----+--------+-----------+-------+
  3. | id | stu_id | c_name    | grade |
  4. +----+--------+-----------+-------+
  5. |  1 |    901 | 计算机    |    98 |
  6. |  5 |    903 | 中文      |    95 |
  7. |  8 |    905 | 英语      |    94 |
  8. |  7 |    904 | 英语      |    92 |
  9. |  9 |    906 | 计算机    |    90 |
  10. |  4 |    902 | 中文      |    88 |
  11. | 10 |    906 | 英语      |    85 |
  12. |  2 |    901 | 英语      |    80 |
  13. |  6 |    904 | 计算机    |    70 |
  14. |  3 |    902 | 计算机    |    65 |
  15. +----+--------+-----------+-------+
  16. 10 rows in set (0.00 sec)

（15）从student表和score表中查询出学生的学号，然后合并查询结果

  1. mysql8.0 [SLB1]>select st.id,st.name from student as st inner join score as sc on st.id=sc.stu_id;
  2. +-----+-----------+
  3. | id  | name      |
  4. +-----+-----------+
  5. | 901 | 张老大    |
  6. | 901 | 张老大    |
  7. | 902 | 张老二    |
  8. | 902 | 张老二    |
  9. | 903 | 张三      |
  10. | 904 | 李四      |
  11. | 904 | 李四      |
  12. | 905 | 王五      |
  13. | 906 | 王六      |
  14. | 906 | 王六      |
  15. +-----+-----------+
  16. 10 rows in set (0.01 sec)

（16）查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

  1. mysql8.0 [SLB1]>select st.name,st.department,sc.c_name,sc.grade from student as st inner join score as sc on st.id=sc.stu_id where name like '张%' or name likke '王%';
  2. +-----------+--------------+-----------+-------+
  3. | name      | department   | c_name    | grade |
  4. +-----------+--------------+-----------+-------+
  5. | 张老大    | 计算机系     | 计算机    |    98 |
  6. | 张老大    | 计算机系     | 英语      |    80 |
  7. | 张老二    | 中文系       | 计算机    |    65 |
  8. | 张老二    | 中文系       | 中文      |    88 |
  9. | 张三      | 中文系       | 中文      |    95 |
  10. | 王五      | 英语系       | 英语      |    94 |
  11. | 王六      | 计算机系     | 计算机    |    90 |
  12. | 王六      | 计算机系     | 英语      |    85 |
  13. +-----------+--------------+-----------+-------+
  14. 8 rows in set (0.00 sec)

（17）查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

  1. mysql8.0 [SLB1]>select st.name,(year(curdate())-birth) as age,st.address,st.department,sc.c_name,sc.grade from student as st inner join score as sc on st.id=sc.stu_id where address like '%湖南%';
  2. +--------+------+--------------------+--------------+-----------+-------+
  3. | name   | age  | address            | department   | c_name    | grade |
  4. +--------+------+--------------------+--------------+-----------+-------+
  5. | 张三   |   33 | 湖南省永州市       | 中文系       | 中文      |    95 |
  6. | 王六   |   35 | 湖南省衡阳市       | 计算机系     | 计算机    |    90 |
  7. | 王六   |   35 | 湖南省衡阳市       | 计算机系     | 英语      |    85 |
  8. +--------+------+--------------------+--------------+-----------+-------+
  9. 3 rows in set (0.00 sec)
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/空白诗007/article/detail/1015370
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号