MySQL的单表和多表查询

最新推荐文章于 2024-08-05 23:28:25 发布
最新推荐文章于 2024-08-05 23:28:25 发布
阅读量1k 收藏 20
点赞数 27
分类专栏： MySQL（第一版） 文章标签： mysql android adb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_73168361/article/details/136379684
版权

我们在前面曾构建过三个用于实验的表格，下面将基于这三个表进行实践。

# 建立一个用于实验的三个表格
mysql> create table emp (
    ->   empno varchar(10),
    ->   ename varchar(50),
    ->   job varchar(50),
    ->   mgr int,
    ->   hiredate timestamp,
    ->   sal decimal(10, 2),
    ->   comm decimal(10, 2),
    ->   deptno int
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
    -> ('007369', 'smith', 'clerk', 7902, '1980-12-17 00:00:00', 800.00, null, 20),
    -> ('007499', 'allen', 'salesman', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30),
    -> ('007521', 'ward', 'salesman', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30),
    -> ('007566', 'jones', 'manager', 7839, '1981-04-02 00:00:00', 2975.00, null, 20),
    -> ('007654', 'martin', 'salesman', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30),
    -> ('007698', 'blake', 'manager', 7839, '1981-05-01 00:00:00', 2850.00, null, 30),
    -> ('007782', 'clark', 'manager', 7839, '1981-06-09 00:00:00', 2450.00, null, 10),
    -> ('007788', 'scott', 'analyst', 7566, '1987-04-19 00:00:00', 3000.00, null, 20),
    -> ('007839', 'king', 'president', null, '1981-11-17 00:00:00', 5000.00, null, 10),
    -> ('007844', 'turner', 'salesman', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30),
    -> ('007876', 'adams', 'clerk', 7788, '1987-05-23 00:00:00', 1100.00, null, 20),
    -> ('007900', 'james', 'clerk', 7698, '1981-12-03 00:00:00', 950.00, null, 30),
    -> ('007902', 'ford', 'analyst', 7566, '1981-12-03 00:00:00', 3000.00, null, 20),
    -> ('007934', 'miller', 'clerk', 7782, '1982-01-23 00:00:00', 1300.00, null, 10);
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0


mysql> create table dept (
    ->   deptno int,
    ->   dname varchar(50),
    ->   loc varchar(50)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into dept (deptno, dname, loc) values
    -> (10, 'accounting', 'new york'),
    -> (20, 'research', 'dallas'),
    -> (30, 'sales', 'chicago'),
    -> (40, 'operations', 'boston');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> create table salgrade (
    ->   grade int,
    ->   losal int,
    ->   hisal int
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into salgrade (grade, losal, hisal) values
    -> (1, 700, 1200),
    -> (2, 1201, 1400),
    -> (3, 1401, 2000),
    -> (4, 2001, 3000),
    -> (5, 3001, 9999);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | new york |
|     20 | research   | dallas   |
|     30 | sales      | chicago  |
|     40 | operations | boston   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

还需要使得表之间有联系。

# 关联表格
# 设置主键
mysql> alter table dept modify column deptno int not null, add primary key (deptno);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 添加外键
mysql> alter table emp add foreign key (deptno) references dept(deptno);
Query OK, 14 rows affected (0.08 sec)
Records: 14  Duplicates: 0  Warnings: 0

# 查看设置
mysql> desc emp;
+----------+---------------+------+-----+-------------------+-----------------------------+
| Field    | Type          | Null | Key | Default           | Extra                       |
+----------+---------------+------+-----+-------------------+-----------------------------+
| empno    | varchar(10)   | YES  |     | NULL              |                             |
| ename    | varchar(50)   | YES  |     | NULL              |                             |
| job      | varchar(50)   | YES  |     | NULL              |                             |
| mgr      | int(11)       | YES  |     | NULL              |                             |
| hiredate | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sal      | decimal(10,2) | YES  |     | NULL              |                             |
| comm     | decimal(10,2) | YES  |     | NULL              |                             |
| deptno   | int(11)       | YES  | MUL | NULL              |                             |
+----------+---------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | NO   | PRI | NULL    |       |
| dname  | varchar(50) | YES  |     | NULL    |       |
| loc    | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1.单表查询

  1. 查询工资高于 1000 或岗位为 manager 的雇员，同时还要满足他们的姓名首字母为 j

    # 需求 1
mysql> select * from emp where ((sal>1000 or job='manager') and left(ename, 1)='j');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%';
mysql> select * from emp where (sal>1000 or job='manager') and ename like 'J%';;
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

  2. 按照部门号升序、雇员的工资降序来排序

    # 需求 2
mysql> select sal, deptno, ename from emp order by deptno asc, sal desc;
+---------+--------+--------+
| sal     | deptno | ename  |
+---------+--------+--------+
| 5000.00 |     10 | king   |
| 2450.00 |     10 | clark  |
| 1300.00 |     10 | miller |
| 3000.00 |     20 | scott  |
| 3000.00 |     20 | ford   |
| 2975.00 |     20 | jones  |
| 1100.00 |     20 | adams  |
|  800.00 |     20 | smith  |
| 2850.00 |     30 | blake  |
| 1600.00 |     30 | allen  |
| 1500.00 |     30 | turner |
| 1250.00 |     30 | ward   |
| 1250.00 |     30 | martin |
|  950.00 |     30 | james  |
+---------+--------+--------+
14 rows in set (0.00 sec)

  3. 使用年薪（年薪=月薪*12+奖金）进行降序排序

    # 需求 3
mysql> select ename, sal*12+ifnull(comm,0) as 年薪 from emp order by '年薪' desc;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| smith  |  9600.00 |
| allen  | 19500.00 |
| ward   | 15500.00 |
| jones  | 35700.00 |
| martin | 16400.00 |
| blake  | 34200.00 |
| clark  | 29400.00 |
| scott  | 36000.00 |
| king   | 60000.00 |
| turner | 18000.00 |
| adams  | 13200.00 |
| james  | 11400.00 |
| ford   | 36000.00 |
| miller | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

  4. 显示工资最高的员工的名字和工作岗位

    # 需求 4
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

mysql> select ename, job from emp where sal=5000;
+-------+-----------+
| ename | job       |
+-------+-----------+
| king  | president |
+-------+-----------+
1 row in set (0.00 sec)

mysql> select ename, job from emp where sal=(select max(sal) from emp); # 复合查找，也叫“查找子句”
+-------+-----------+
| ename | job       |
+-------+-----------+
| king  | president |
+-------+-----------+
1 row in set (0.01 sec)

  5. 显示工资高于平均工资的员工信息

    # 需求 5
mysql> select * from emp where sal > (select avg(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | jones | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007698 | blake | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
| 007782 | clark | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
| 007788 | scott | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007839 | king  | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
| 007902 | ford  | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

  6. 显示每个部门的平均工资和最高工资

    # 需求 6
mysql> select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)

  7. 显示平均工资低于 2000 的部门号和它的平均工资

    # 需求 7
mysql> select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;
+--------+--------------+
| deptno | 平均工资     |
+--------+--------------+
|     30 |  1566.666667 |
+--------+--------------+
1 row in set (0.01 sec)

  8. 显示每种岗位的雇员总数，平均工资

    # 需求 8
mysql> select job, format(avg(sal), 2) 平均工资, count(*) 人数 from emp group by job;
+-----------+--------------+--------+
| job       | 平均工资     | 人数   |
+-----------+--------------+--------+
| analyst   | 3,000.00     |      2 |
| clerk     | 1,037.50     |      4 |
| manager   | 2,758.33     |      3 |
| president | 5,000.00     |      1 |
| salesman  | 1,400.00     |      4 |
+-----------+--------------+--------+
5 rows in set (0.00 sec)

2.多表查询

  1. 显示雇员名、雇员工资以及所在部门的名字

    # 需求 1
# 将表合外表整合为一个表
mysql> select * from emp, dept where emp.deptno=dept.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | accounting | new york |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | accounting | new york |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | accounting | new york |
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | research   | dallas   |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | research   | dallas   |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | research   | dallas   |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | research   | dallas   |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | research   | dallas   |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | sales      | chicago  |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | sales      | chicago  |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | sales      | chicago  |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | sales      | chicago  |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | sales      | chicago  |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | sales      | chicago  |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)

mysql> select emp.ename sal dname from emp, dept where emp.deptno=dept.deptno;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dname from emp, dept where emp.deptno=dept.deptno' at line 1
mysql> select emp.ename sal, dname, from emp, dept where emp.deptno=dept.deptno;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from emp, dept where emp.deptno=dept.deptno' at line 1
mysql> select emp.ename, sal, dname from emp, dept where emp.deptno=dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| clark  | 2450.00 | accounting |
| king   | 5000.00 | accounting |
| miller | 1300.00 | accounting |
| smith  |  800.00 | research   |
| jones  | 2975.00 | research   |
| scott  | 3000.00 | research   |
| adams  | 1100.00 | research   |
| ford   | 3000.00 | research   |
| allen  | 1600.00 | sales      |
| ward   | 1250.00 | sales      |
| martin | 1250.00 | sales      |
| blake  | 2850.00 | sales      |
| turner | 1500.00 | sales      |
| james  |  950.00 | sales      |
+--------+---------+------------+
14 rows in set (0.00 sec)

  2. 显示部门号为 10 的部门名，员工名和工资

    # 需求 2
mysql> select dept.dname ,emp.ename, emp.sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| accounting | clark  | 2450.00 |
| accounting | king   | 5000.00 |
| accounting | miller | 1300.00 |
+------------+--------+---------+
3 rows in set (0.00 sec)

  3. 显示各个员工的姓名，工资，及工资级别

    # 需求 3
# (1)查看工资等级
mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

# (2)查看员工信息表
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

# (3)整合两表（求笛卡尔积）
mysql> select * from emp, salgrade;
+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | grade | losal | hisal |
+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     1 |   700 |  1200 |
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     2 |  1201 |  1400 |
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     3 |  1401 |  2000 |
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     4 |  2001 |  3000 |
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     5 |  3001 |  9999 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     1 |   700 |  1200 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     2 |  1201 |  1400 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     3 |  1401 |  2000 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     4 |  2001 |  3000 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     5 |  3001 |  9999 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     1 |   700 |  1200 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     2 |  1201 |  1400 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     3 |  1401 |  2000 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     4 |  2001 |  3000 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     5 |  3001 |  9999 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     1 |   700 |  1200 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     2 |  1201 |  1400 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     3 |  1401 |  2000 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     4 |  2001 |  3000 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     5 |  3001 |  9999 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     1 |   700 |  1200 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     2 |  1201 |  1400 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     3 |  1401 |  2000 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     4 |  2001 |  3000 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     5 |  3001 |  9999 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     1 |   700 |  1200 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     2 |  1201 |  1400 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     3 |  1401 |  2000 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     4 |  2001 |  3000 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     5 |  3001 |  9999 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     1 |   700 |  1200 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     2 |  1201 |  1400 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     3 |  1401 |  2000 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     4 |  2001 |  3000 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     5 |  3001 |  9999 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     1 |   700 |  1200 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     2 |  1201 |  1400 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     3 |  1401 |  2000 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     4 |  2001 |  3000 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     5 |  3001 |  9999 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     1 |   700 |  1200 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     2 |  1201 |  1400 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     3 |  1401 |  2000 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     4 |  2001 |  3000 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     5 |  3001 |  9999 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     1 |   700 |  1200 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     2 |  1201 |  1400 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     3 |  1401 |  2000 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     4 |  2001 |  3000 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     5 |  3001 |  9999 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     1 |   700 |  1200 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     2 |  1201 |  1400 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     3 |  1401 |  2000 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     4 |  2001 |  3000 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     5 |  3001 |  9999 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     1 |   700 |  1200 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     2 |  1201 |  1400 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     3 |  1401 |  2000 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     4 |  2001 |  3000 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     5 |  3001 |  9999 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     1 |   700 |  1200 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     2 |  1201 |  1400 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     3 |  1401 |  2000 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     4 |  2001 |  3000 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     5 |  3001 |  9999 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     1 |   700 |  1200 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     2 |  1201 |  1400 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     3 |  1401 |  2000 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     4 |  2001 |  3000 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     5 |  3001 |  9999 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+-------+-------+-------+
70 rows in set (0.00 sec)

# (4)列出员工姓名、员工工资、工资对应等级
mysql> select emp.ename, emp.sal, salgrade.grade from emp, salgrade where sal between salgrade.losal and salgrade.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| smith  |  800.00 |     1 |
| allen  | 1600.00 |     3 |
| ward   | 1250.00 |     2 |
| jones  | 2975.00 |     4 |
| martin | 1250.00 |     2 |
| blake  | 2850.00 |     4 |
| clark  | 2450.00 |     4 |
| scott  | 3000.00 |     4 |
| king   | 5000.00 |     5 |
| turner | 1500.00 |     3 |
| adams  | 1100.00 |     1 |
| james  |  950.00 |     1 |
| ford   | 3000.00 |     4 |
| miller | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

补充：同一张表也可以进行笛卡尔积，也就是“自连接”

# 尝试自连接
mysql> select * from salgrade as t1, salgrade as t2;
+-------+-------+-------+-------+-------+-------+
| grade | losal | hisal | grade | losal | hisal |
+-------+-------+-------+-------+-------+-------+
|     1 |   700 |  1200 |     1 |   700 |  1200 |
|     2 |  1201 |  1400 |     1 |   700 |  1200 |
|     3 |  1401 |  2000 |     1 |   700 |  1200 |
|     4 |  2001 |  3000 |     1 |   700 |  1200 |
|     5 |  3001 |  9999 |     1 |   700 |  1200 |
|     1 |   700 |  1200 |     2 |  1201 |  1400 |
|     2 |  1201 |  1400 |     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |     2 |  1201 |  1400 |
|     4 |  2001 |  3000 |     2 |  1201 |  1400 |
|     5 |  3001 |  9999 |     2 |  1201 |  1400 |
|     1 |   700 |  1200 |     3 |  1401 |  2000 |
|     2 |  1201 |  1400 |     3 |  1401 |  2000 |
|     3 |  1401 |  2000 |     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |     3 |  1401 |  2000 |
|     5 |  3001 |  9999 |     3 |  1401 |  2000 |
|     1 |   700 |  1200 |     4 |  2001 |  3000 |
|     2 |  1201 |  1400 |     4 |  2001 |  3000 |
|     3 |  1401 |  2000 |     4 |  2001 |  3000 |
|     4 |  2001 |  3000 |     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |     4 |  2001 |  3000 |
|     1 |   700 |  1200 |     5 |  3001 |  9999 |
|     2 |  1201 |  1400 |     5 |  3001 |  9999 |
|     3 |  1401 |  2000 |     5 |  3001 |  9999 |
|     4 |  2001 |  3000 |     5 |  3001 |  9999 |
|     5 |  3001 |  9999 |     5 |  3001 |  9999 |
+-------+-------+-------+-------+-------+-------+
25 rows in set (0.00 sec)

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

有些情况下是需要自连接的，例如“显示员工 ford 的上级领导的编号和姓名”：

# 尝试寻找上级领导
# (1)查看员工表
mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | king   | president | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

# (2)查看某个员工的上级领导编号
mysql> select mgr from emp where ename='ford';
+------+
| mgr  |
+------+
| 7566 |
+------+
1 row in set (0.00 sec)

# (3)查看领导编号对应的领导姓名
mysql> select ename, empno from emp where empno=7566;
+-------+--------+
| ename | empno  |
+-------+--------+
| jones | 007566 |
+-------+--------+
1 row in set (0.00 sec)

# (4)子查询做法
mysql> select ename, empno from emp where empno=(select mgr from emp where ename='ford');
+-------+--------+
| ename | empno  |
+-------+--------+
| jones | 007566 |
+-------+--------+
1 row in set (0.01 sec)

# (5)自连接做法
mysql> select e2.empno 领导编号, e2.ename 领导名  from emp as e1, emp as e2 where e1.ename='ford' and e1.mgr=e2.empno;
+--------------+-----------+
| 领导编号     | 领导名    |
+--------------+-----------+
| 007566       | jones     |
+--------------+-----------+
1 row in set (0.00 sec)
limou3434
关注
  • 27
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
【无标题】mysql用户+角色+密码安全策略
m0_68739559的博客
08-05 685
12.把mysql.server文件放到/etc/init.d/目录下，并改名为mysql8，方便启动mysql服务。
MySQL:VIEW视图
Rverdoser的博客
08-05 226
在MySQL中，VIEW（视图）是一个虚拟表，其内容由查询定义。视图可以使得查询结果更加简洁和安全，因为它们可以隐藏一些复杂的查询逻辑，并提供一个可以被用户查询的接口。需要注意的是，视图的内容是依赖于定义它的查询的。如果基础表的数据发生了变化，视图中的数据也会相应地更新。同时，视图也有一些限制，例如不支持某些类型的操作（如。例如，如果你有一个名为。
mysql的基本应用1
m0_73671133的博客
08-05 358
root@mysql ~]# vim /var/log/mysqld.log 寻找登陆密码，密码不能有空格。
49 mysql 子查询 加 group by 产生的奇怪现象
970655147的专栏
08-05 259
这里要提到的是一个 之前碰到的一个 很令人诧异的查询, 主要是 和 group 查询有关系查询如下, 按照常规理解, “select id from t_user_02 where name = 'jerry' group by age” 会返回 两条数据, 然后 整个查询 会查询出两条数据但是 结果很令人差异, 查询出了 四条数据测试数据表如下, t_user 和 t_user_02 完全一样, t_user_02 是由 t_user 复制而来测试数据如下查询结果如下。
MySQL 5.7使用 GTID 和 Binlog高可用方案
雪花凌落的盛夏
08-05 357
GTID 和 Binlog 是互补的技术。GTID 提供了更高级的事务跟踪和管理机制，而 Binlog 则是底层的日志记录机制。在 GTID 模式下，每个事务在写入 binlog 时都会附带一个 GTID，从而实现更加可靠和简化的复制管理。GTID 用于全局唯一标识事务，简化复制管理和故障恢复。Binlog 用于记录数据更改事件，是数据恢复和复制的基础。在许多高可用方案中，GTID 和 Binlog 是结合使用的。
21 mysql绿色安装版
2301_79379541的博客
08-05 244
2024-08-05T02:02:16.872935Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: zok*m6cVpInL //下面要用mysql密码。[root@mysql ~]# usradd -r -s /sbin/nologin mysql //没有的话添加用户。[root@mysql mysql]# 把mysql.server文件放到/etc/init.d/目录下。
MySQL(四）：函数
SSuperwoman的博客
08-05 143
【代码】MySQL(四）：函数。
乐观锁与悲观锁在MySQL中的应用
likeshopgood的博客
08-05 478
本文由团队出品在数据库管理系统中，锁机制是保证数据一致性和并发控制的重要手段。MySQL，作为广泛使用的数据库系统之一，提供了多种锁策略来处理并发访问时可能引发的数据不一致性问题。其中，乐观锁和悲观锁是两种截然不同但又互补的并发控制策略，它们在不同的应用场景下发挥着各自的优势。本文将深入探讨MySQL中的乐观锁与悲观锁概念、工作原理及实际应用。
mysql注入-sqli-labs-master复现
最新发布
小白
08-05 527
查看数据库，在注入回显时，但应该注意以下的点，数据回显是有限制的他可能不会回显完全，如下显示，用limit显示我们需要的数据，后面还可以依靠group_concat和concat两个函数。查看源码，截取关键部分源码，在源码中可以看到，一条预编译的sql语句，而这条语句配合id传参，就是我们的注入点了，并且这是典型的字符型注入。后面的第二关，为数字型注入，与第一关没有大的区别，但是看源码变量id没有用引号，引起来，所以注入 时不用闭合。我们看第七关，会发现他与其他关有些不同，这关考的是文件的注入。
安装glibc+mysql的权限问题
weixin_70752007的博客
08-05 472
1.远程登录的前提条件：mysql.user表中的host属性为%,如果是localhost就是不允许远程登录，update mysql.user set host="%" where user="root",flush privileges;# lilaosi就获得了test库中所有的表的操作权限，但是，由于 root没有给lilaosimysql库的权限，所以lilaosi账号⽆法查看 mysql库。给角色给权限：jingli (插入，更新，删除，选择)，yuangongjuese(插入，选择)
Java、PHP、Node 操作 MySQL 数据库常用方法
qq_22232065的博客
08-05 665
可以防止 SQL 注入，提高安全性。对于相同结构但参数不同的 SQL 语句，预编译后执行效率更高。容易受到 SQL 注入攻击。每次执行都需要重新编译 SQL 语句，效率较低。在 Java 操作 MySQL 数据库的过程中，关键要点包括选择合适的连接方式（如 JDBC 驱动、连接池或 ORM 框架）以满足不同项目的需求。对于数据操作，使用 PreparedStatement 提高安全性和效率，同时注意合理的索引设计和 SQL 语句优化来提升性能。
MySQL——表的约束（二）非空约束
PAP
08-05 204
MySQL——表的约束（二）非空约束
WEB漏洞-SQL注入之MYSQL注入
weixin_73185660的博客
08-05 483
比如：select * from users where id=-1 union select 1,group_concat(column_name),3 from information_schema.columns where table_name='指定表名' and table_schema='指定数据库名'使用information_schema数据库，这个库中有schemata表记录了当前服务器的所有数据库名称，有tables记录当前数据库的所有表，有columns记录当前表的所有列。
MySQL——表的约束（一）主键约束
PAP
08-05 406
MySQL——表的约束（一）主键约束
MySQL：索引（Index）理论知识
Zachyy的博客
08-05 474
关系型数据库中，索引（Index）是一种用于加快数据检索速度的数据结构。它通过存储某些列的值的指针，来提高查询的效率。 一般情况下，索引是在表中的一个或多个列上创建的，它们可以显著提高查询的效率。 当我们使用 SELECT 语句查询一个表时，如果没有索引，数据库需要扫描每一行数据，这会花费大量时间，特别是对于大型的数据集。如果有了索引，数据库就可以用更快的方式定位到需要的数据行，因为它可以使用索引中存储的值来快速查找匹配的行。
MySQL案例：MHA实现主备切换（主从架构）万字详解
变成我......
08-02 946
Linux案例：在基于主从的架构下使用MHA来管理主备切换的示例步骤
MySQL 是如何实现数据的排序的？
zhishidi的博客
08-05 274
上面2种回答方式:第一种,更偏向余点对点的回答,类似我们读书时候的回答试卷的方式;第二种,更偏向于把理论之前与实际开发结合回答,并且更注重得出结论的思考过程;如果你是面试官,你会觉得那种回答更能得到你的青睐呢?欢迎在评论区给出你的观点!
解决Poco找不到MySQL::client的问题
jiexijihe945的博客
08-02 280
最近在研究Poco，一个很强大的C++库，当我要使用MySQL的时候找不到，解决这个问题其实很简单，不需要大费周折。提示：这里对文章进行总结：例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。
MySQL深分页和浅分页
m0_69057918的博客
08-05 500
数据库深分页和浅分页
mysql 单表多key匹配查询
09-06
MySQL 单表多key匹配查询是指通过在单个表中使用多个字段来实现匹配查询的操作。 在进行单表多key匹配查询时，可以使用多个字段来作为查询条件，以便获取符合条件的记录。 例如，有一个名为"users"的表，包含字段...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

limou3434

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值