Day 25 数据库查询

已于 2024-05-21 20:34:54 修改
阅读量986 收藏 7
点赞数 8
文章标签： 数据库 linux
于 2024-05-07 22:34:57 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xlv133922/article/details/138548407
版权

数据库查询

一：基本查询

1.简介

​ 单表查询

​ 简单查询

​ 通过条件查询

​ 查询排序

​ 限制查询记录数

​ 使用集合函数查询

​ 分组查询

​ 使用正则表达式查询

2.案例

创建案例所需表：company.employee5

	雇员编号	 id				        int
	雇员姓名	 name			       varchar(30)
	雇员性别	 sex				   enum
	雇用时期	 hire_date			   date
	职位		  post					varchar(50)
	职位描述	 job_description	    varchar(100)
	薪水		   salary				double(15,2)
	办公室		  office			    int
	部门编号	 dep_id				   int

MySQL [(none)]> CREATE TABLE company.employee5(
          id int primary key AUTO_INCREMENT not null,
          name varchar(30) not null,
          sex enum('male','female') default 'male' not null,
          hire_date date not null,
          post varchar(50) not null,
          job_description varchar(100),
          salary double(15,2) not null,
          office int,
          dep_id int
          );

插入模拟数据：

MySQL [(none)]> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values 
    ('jack','male','20180202','instructor','teach',5000,501,100),
    ('tom','male','20180203','instructor','teach',5500,501,100),
    ('robin','male','20180202','instructor','teach',8000,501,100),
    ('alice','female','20180202','instructor','teach',7200,501,100),
    ('','male','20180202','hr','hrcc',600,502,101),
    ('harry','male','20180202','hr',NULL,6000,502,101),
    ('emma','female','20180206','sale','salecc',20000,503,102),
    ('christine','female','20180205','sale','salecc',2200,503,102),
    ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
    ('gougou','male','20180205','sale','',2200,503,102);

语法格式：

​ select 字段名称,字段名称2… from 表名 [条件]

a.简单查询

select 字段名 from 表名
所有字段：*

MySQL [company]> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 |           | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

MySQL [company]> select name, salary, dep_id from employee5 where id <=5;
+-------+---------+--------+
| name  | salary  | dep_id |
+-------+---------+--------+
| jack  | 5000.00 |    100 |
| tom   | 5500.00 |    100 |
| robin | 8000.00 |    100 |
| alice | 7200.00 |    100 |
|       |  600.00 |    101 |
+-------+---------+--------+
5 rows in set (0.00 sec)
b.避免重复

select distinct 需要去重的字段名 from 表名

不能部分使用DISTINCT，通常仅用于某一字段

MySQL [company]> SELECT post FROM employee5;
+------------+
| post       |
+------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr         |
| hr         |
| sale       |
| sale       |
| sale       |
| sale       |
+------------+
10 rows in set (0.00 sec)

MySQL [company]> SELECT distinct post  FROM employee5;
+------------+
| post       |
+------------+
| instructor |
| hr         |
| sale       |
+------------+
3 rows in set (0.00 sec)
c.四则运算查询
MySQL [company]> SELECT name, salary, salary*14 FROM employee5;
+-----------+----------+-----------+
| name      | salary   | salary*14 |
+-----------+----------+-----------+
| jack      |  5000.00 |  70000.00 |
| tom       |  5500.00 |  77000.00 |
| robin     |  8000.00 | 112000.00 |
| alice     |  7200.00 | 100800.00 |
|           |   600.00 |   8400.00 |
| harry     |  6000.00 |  84000.00 |
| emma      | 20000.00 | 280000.00 |
| christine |  2200.00 |  30800.00 |
| zhuzhu    |  2200.00 |  30800.00 |
| gougou    |  2200.00 |  30800.00 |
+-----------+----------+-----------+
10 rows in set (0.01 sec)

MySQL [company]> SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
+-----------+----------+---------------+
| name      | salary   | Annual_salary |
+-----------+----------+---------------+
| jack      |  5000.00 |      70000.00 |
| tom       |  5500.00 |      77000.00 |
| robin     |  8000.00 |     112000.00 |
| alice     |  7200.00 |     100800.00 |
|           |   600.00 |       8400.00 |
| harry     |  6000.00 |      84000.00 |
| emma      | 20000.00 |     280000.00 |
| christine |  2200.00 |      30800.00 |
| zhuzhu    |  2200.00 |      30800.00 |
| gougou    |  2200.00 |      30800.00 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)

MySQL [company]> SELECT name, salary, salary*14 Annual_salary FROM employee5;
+-----------+----------+---------------+
| name      | salary   | Annual_salary |
+-----------+----------+---------------+
| jack      |  5000.00 |      70000.00 |
| tom       |  5500.00 |      77000.00 |
| robin     |  8000.00 |     112000.00 |
| alice     |  7200.00 |     100800.00 |
|           |   600.00 |       8400.00 |
| harry     |  6000.00 |      84000.00 |
| emma      | 20000.00 |     280000.00 |
| christine |  2200.00 |      30800.00 |
| zhuzhu    |  2200.00 |      30800.00 |
| gougou    |  2200.00 |      30800.00 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)
d.定义显示格式

​CONCAT() 函数：通过字符串连接不同的字段，使其形成一个复合字段，并将复合字段视为一个新的字段。
select 字段①, concat(字段②,‘连接字段的字符串’,字段③) as 复合字段名,字段④…from 表名;

MySQL [company]> SELECT concat(name, 's annual salary: ', salary*14)  AS Annual_salary FROM employee5;
+------------------------------------+
| Annual_salary                      |
+------------------------------------+
| jacks annual salary: 70000.00      |
| toms annual salary: 77000.00       |
| robins annual salary: 112000.00    |
| alices annual salary: 100800.00    |
| s annual salary: 8400.00           |
| harrys annual salary: 84000.00     |
| emmas annual salary: 280000.00     |
| christines annual salary: 30800.00 |
| zhuzhus annual salary: 30800.00    |
| gougous annual salary: 30800.00    |
+------------------------------------+
10 rows in set (0.00 sec)
e.单条件查询
MySQL [company]> SELECT name,post FROM employee5 WHERE post='hr';
+-------+------+
| name  | post |
+-------+------+
|       | hr   |
| harry | hr   |
+-------+------+
2 rows in set (0.00 sec)
f.多条件查询
MySQL [company]> SELECT name,salary FROM employee5 WHERE post='hr' AND salary>10000;
Empty set (0.00 sec)

MySQL [company]> select * from employee5 where salary>5000 and salary<10000  or dep_id=102;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
8 rows in set (0.00 sec)
g.关键字

​select 字段名 from 表名 where 字段名 BETWEEN X AND Y
X <= 字段值 <= Y

MySQL [company]> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| tom   | 5500.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
5 rows in set (0.00 sec)

MySQL [company]> SELECT name,salary FROM employee5  WHERE salary NOT BETWEEN 5000 AND 15000;
+-----------+----------+
| name      | salary   |
+-----------+----------+
|           |   600.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
5 rows in set (0.00 sec)

​ IS NULL

MySQL [company]> SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| harry  | NULL            |
| zhuzhu | NULL            |
+--------+-----------------+
2 rows in set (0.00 sec)

MySQL [company]> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
+-----------+-----------------+
| name      | job_description |
+-----------+-----------------+
| jack      | teach           |
| tom       | teach           |
| robin     | teach           |
| alice     | teach           |
|           | hrcc            |
| emma      | salecc          |
| christine | salecc          |
| gougou    |                 |
+-----------+-----------------+
8 rows in set (0.00 sec)

MySQL [company]> SELECT name,job_description FROM employee5  WHERE job_description='';
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| gougou |                 |
+--------+-----------------+
1 row in set (0.00 sec)

注意：（NULL说明）

​ 等价于没有任何值、是未知数

​ NULL与0、空字符串、空格都不同,NULL没有分配存储空间

​ 对空值做加、减、乘、除等运算操作，结果仍为空

​ 比较时使用关键字用“is null”和“is not null”

​ 排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前

IN集合查询

MySQL [company]> SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)

MySQL [company]> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)

MySQL [company]> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| tom       |  5500.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
|           |   600.00 |
| emma      | 20000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
+-----------+----------+
8 rows in set (0.01 sec)
h.模糊查询

​ 关键字LIKE

​ 通配符%：所有字符

​ 通配符_： 一个字符

MySQL [company]> SELECT * FROM employee5 WHERE name LIKE 'al%';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

MySQL [company]> SELECT * FROM employee5 WHERE name LIKE 'al___';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)
i.排序查询

select 字段名 from 表名 order by 字段 (desc);
默认为升序（asc），降序（desc）需要额外附加

MySQL [company]> select name,salary from employee5 order by salary;
+-----------+----------+
| name      | salary   |
+-----------+----------+
|           |   600.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
| jack      |  5000.00 |
| tom       |  5500.00 |
| harry     |  6000.00 |
| alice     |  7200.00 |
| robin     |  8000.00 |
| emma      | 20000.00 |
+-----------+----------+
10 rows in set (0.01 sec)

MySQL [company]> select name,salary from employee5 order by salary desc; 
+-----------+----------+
| name      | salary   |
+-----------+----------+
| emma      | 20000.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
| harry     |  6000.00 |
| tom       |  5500.00 |
| jack      |  5000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
|           |   600.00 |
+-----------+----------+

MySQL [company]> select name,salary from employee5 order by salary desc limit 3;   //控制显示前3行
+-------+----------+
| name  | salary   |
+-------+----------+
| emma  | 20000.00 |
| robin |  8000.00 |
| alice |  7200.00 |
+-------+----------+
3 rows in set (0.00 sec)

MySQL [company]> select name,salary from employee5 order by salary desc limit 1,3;  //从序号1开始显示三行的内容
+-------+---------+
| name  | salary  |
+-------+---------+
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
3 rows in set (0.00 sec)

注意：

​ ascending 美音 /ə’sɛndɪŋ/ 升序

​ descending 美音 /dɪ’sɛndɪŋ/ 降序

j.集合函数查询

​ count：可以查看共有多少条记录

MySQL [company]> select count(*)  from employee5;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

MySQL [company]> select count(name) from employee5;
+-------------+
| count(name) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

​ max：查看最大值

MySQL [company]> select max(salary) from employee5;
+-------------+
| max(salary) |
+-------------+
|    20000.00 |
+-------------+
1 row in set (0.00 sec)

​ min：查看最小值

MySQL [company]> select min(salary) from employee5;
+-------------+
| min(salary) |
+-------------+
|      600.00 |
+-------------+
1 row in set (0.00 sec)

​ avg：查看平均值

MySQL [company]> select avg(salary) from employee5;
+-------------+
| avg(salary) |
+-------------+
| 5890.000000 |
+-------------+
1 row in set (0.00 sec)

​ sum：求和

​ sale这个部门的总工资

MySQL [company]> select concat("Total Department Wages：",sum(salary)) from employee5 where post='sale';
+-------------------------------------------------+
| concat("Total Department Wages：",sum(salary))  |
+-------------------------------------------------+
| Total Department Wages：26600.00                |
+-------------------------------------------------+
1 row in set (0.00 sec)

​ 获取薪水最高的这个人的详细信息

MySQL [company]> select * from employee5 where salary = (select max(salary) from employee5);
+----+------+--------+------------+------+-----------------+----------+--------+--------+
| id | name | sex    | hire_date  | post | job_description | salary   | office | dep_id |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
|  7 | emma | female | 2018-02-06 | sale | salecc          | 20000.00 |    503 |    102 |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
1 row in set (0.00 sec)
k.分组查询

​ GROUP BY和GROUP_CONCAT()函数一起使用

​ 获取部门ID相同的员工并把名字拼接到一起

MySQL [company]> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
+--------+------------------------------+
| dep_id | GROUP_CONCAT(name)           |
+--------+------------------------------+
|    100 | jack,tom,robin,alice         |
|    101 | ,harry                       |
|    102 | emma,christine,zhuzhu,gougou |
+--------+------------------------------+
3 rows in set (0.01 sec)

​ GROUP BY和集合函数一起使用

​ 获取部门最高薪资

MySQL [company]> SELECT post,max(salary) FROM employee5 GROUP BY post;
+------------+-------------+
| post       | max(salary) |
+------------+-------------+
| hr         |     6000.00 |
| instructor |     8000.00 |
| sale       |    20000.00 |
+------------+-------------+
3 rows in set (0.00 sec)
l.正则查询

select 字段① from 表名 where 字段② regexp ‘正则表达式’;
字段②匹配正则表达式

以什么开头
MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP '^ali';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

以什么结尾
MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP 'ce$';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.01 sec)

连续出现n次
MySQL [company]> SELECT * FROM employee5 WHERE name REGEXP 'm{2}';
+----+------+--------+------------+------+-----------------+----------+--------+--------+
| id | name | sex    | hire_date  | post | job_description | salary   | office | dep_id |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
|  7 | emma | female | 2018-02-06 | sale | salecc          | 20000.00 |    503 |    102 |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
1 row in set (0.00 sec)

二：多表联合查询

【扩展了解】

1.数据准备
表：company.employee6

创建表：

MySQL [company]> create table employee6( 
     emp_id int auto_increment primary key not null, 
     emp_name varchar(50), 
     age int, 
     dept_id int);
Query OK, 0 rows affected (0.65 sec)

查看表结构：

MySQL [company]> desc employee6;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| emp_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| emp_name | varchar(50) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
| dept_id  | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

插入模拟数据：

MySQL [company]> insert into employee6(emp_name,age,dept_id) values
     ('',19,200),
     ('tom',26,201),
     ('jack',30,201),
     ('alice',24,202),
     ('robin',40,200),
     ('xingdian',16,200),
     ('natasha',28,204);

查看数据：

MySQL [company]> select * from employee6;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 |          |   19 |     200 |
|      2 | tom      |   26 |     201 |
|      3 | jack     |   30 |     201 |
|      4 | alice    |   24 |     202 |
|      5 | robin    |   40 |     200 |
|      6 | xingdian |   16 |     200 |
|      7 | natasha  |   28 |     204 |
+--------+----------+------+---------+
7 rows in set (0.00 sec)
表：company.department6

创建表：

MySQL [company]> create table department6(
     dept_id int,
     dept_name varchar(100)
     );
Query OK, 0 rows affected (0.33 sec)

查看表结构：

MySQL [company]> desc department6;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| dept_id   | int(11)      | YES  |     | NULL    |       |
| dept_name | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

模拟插入数据：

MySQL [company]> insert into department6 values
     (200,'hr'),
     (201,'it'),
     (202,'sale'),
     (203,'fd');

查看数据：

MySQL [company]> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
|     202 | sale      |
|     203 | fd        |
+---------+-----------+
4 rows in set (0.01 sec)
2.多表的连接查询

​ 交叉连接：生成笛卡尔积，它不使用任何匹配条件；交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合

​ 内连接：只连接匹配的行

​ 外连接

​ 左连接：会显示左边表内所有的值，不论在右边表内匹不匹配

​ 右连接：会显示右边表内所有的值，不论在左边表内匹不匹配

​ 全外连接：包含左、右两个表的全部行

交叉连接

select 字段名 from 表①,表②;
若两张表存在字段名相同，使用 表①.字段名,表②.字段名 来区分避免冲突

MySQL [company]> select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;
+----------+------+---------+-----------+
| emp_name | age  | dept_id | dept_name |
+----------+------+---------+-----------+
|          |   19 |     200 | hr        |
|          |   19 |     200 | it        |
|          |   19 |     200 | sale      |
|          |   19 |     200 | fd        |
| tom      |   26 |     201 | hr        |
| tom      |   26 |     201 | it        |
| tom      |   26 |     201 | sale      |
| tom      |   26 |     201 | fd        |
| jack     |   30 |     201 | hr        |
| jack     |   30 |     201 | it        |
| jack     |   30 |     201 | sale      |
| jack     |   30 |     201 | fd        |
| alice    |   24 |     202 | hr        |
| alice    |   24 |     202 | it        |
| alice    |   24 |     202 | sale      |
| alice    |   24 |     202 | fd        |
| robin    |   40 |     200 | hr        |
| robin    |   40 |     200 | it        |
| robin    |   40 |     200 | sale      |
| robin    |   40 |     200 | fd        |
| xingdian |   16 |     200 | hr        |
| xingdian |   16 |     200 | it        |
| xingdian |   16 |     200 | sale      |
| xingdian |   16 |     200 | fd        |
| natasha  |   28 |     204 | hr        |
| natasha  |   28 |     204 | it        |
| natasha  |   28 |     204 | sale      |
| natasha  |   28 |     204 | fd        |
+----------+------+---------+-----------+
28 rows in set (0.00 sec)
内连接

select 字段名 from 表① inner join 表② on 表①.匹配字段=表②.匹配字段；

​ 获取有部门的员工 (部门表中没有natasha所在的部门)

MySQL [company]> select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6 where employee6.dept_id=department6.dept_id;
+----------+------+---------+-----------+
| emp_name | age  | dept_id | dept_name |
+----------+------+---------+-----------+
|          |   19 |     200 | hr        |
| tom      |   26 |     201 | it        |
| jack     |   30 |     201 | it        |
| alice    |   24 |     202 | sale      |
| robin    |   40 |     200 | hr        |
| xingdian |   16 |     200 | hr        |
+----------+------+---------+-----------+
6 rows in set (0.00 sec)

MySQL [company]> select employee6.emp_name,department6.dept_name from employee6 inner join department6 on employee6.dept_id=department6.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
|          | hr        |
| tom      | it        |
| jack     | it        |
| alice    | sale      |
| robin    | hr        |
| xingdian | hr        |
+----------+-----------+
6 rows in set (0.01 sec)
外连接

语法：

​ SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

注意：

​ 先用谁谁就是左

左连接 left join

select 字段名 from 表① left join 表② on 表①.匹配字段=表②.匹配字段；

找出所有员工及所属的部门，包括没有部门的员工
MySQL [company]> select emp_id,emp_name,dept_name from  employee6 left join department6 on employee6.dept_id = department6.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 |          | hr        |
|      5 | robin    | hr        |
|      6 | xingdian | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      7 | natasha  | NULL      |
+--------+----------+-----------+
7 rows in set (0.00 sec)
右连接right join

select 字段名 from 表① right join 表② on 表①.匹配字段=表②.匹配字段；

找出所有部门包含的员工，包括空部门
MySQL [company]> select emp_id,emp_name,dept_name from  employee6 right join department6 on employee6.dept_id = department6.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 |          | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      4 | alice    | sale      |
|      5 | robin    | hr        |
|      6 | xingdian | hr        |
|   NULL | NULL     | fd        |
+--------+----------+-----------+
7 rows in set (0.00 sec)
全外连接

select 字段名 from 表① full join 表②；

MySQL [company]> select * from employee6 full join department6;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 |          |   19 |     200 |     200 | hr        |
|      1 |          |   19 |     200 |     201 | it        |
|      1 |          |   19 |     200 |     202 | sale      |
|      1 |          |   19 |     200 |     203 | fd        |
|      2 | tom      |   26 |     201 |     200 | hr        |
|      2 | tom      |   26 |     201 |     201 | it        |
|      2 | tom      |   26 |     201 |     202 | sale      |
|      2 | tom      |   26 |     201 |     203 | fd        |
|      3 | jack     |   30 |     201 |     200 | hr        |
|      3 | jack     |   30 |     201 |     201 | it        |
|      3 | jack     |   30 |     201 |     202 | sale      |
|      3 | jack     |   30 |     201 |     203 | fd        |
|      4 | alice    |   24 |     202 |     200 | hr        |
|      4 | alice    |   24 |     202 |     201 | it        |
|      4 | alice    |   24 |     202 |     202 | sale      |
|      4 | alice    |   24 |     202 |     203 | fd        |
|      5 | robin    |   40 |     200 |     200 | hr        |
|      5 | robin    |   40 |     200 |     201 | it        |
|      5 | robin    |   40 |     200 |     202 | sale      |
|      5 | robin    |   40 |     200 |     203 | fd        |
|      6 | xingdian |   16 |     200 |     200 | hr        |
|      6 | xingdian |   16 |     200 |     201 | it        |
|      6 | xingdian |   16 |     200 |     202 | sale      |
|      6 | xingdian |   16 |     200 |     203 | fd        |
|      7 | natasha  |   28 |     204 |     200 | hr        |
|      7 | natasha  |   28 |     204 |     201 | it        |
|      7 | natasha  |   28 |     204 |     202 | sale      |
|      7 | natasha  |   28 |     204 |     203 | fd        |
+--------+----------+------+---------+---------+-----------+
28 rows in set (0.00 sec)
3.复合条件连接查询
案例一

​ 找出公司所有部门中年龄大于25岁的员工

​ 以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25

MySQL [company]> select emp_id,emp_name,dept_name FROM employee6,department6 WHERE employee6.dept_id = department6.dept_id AND age > 25;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      5 | robin    | hr        |
|      2 | tom      | it        |
|      3 | jack     | it        |
+--------+----------+-----------+
3 rows in set (0.01 sec)
案例二

​ 以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示

MySQL [company]> select emp_id,emp_name,dept_name FROM employee6,department6 WHERE employee6.dept_id = department6.dept_id ORDER BY age asc;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      6 | xingdian | hr        |
|      1 |          | hr        |
|      4 | alice    | sale      |
|      2 | tom      | it        |
|      3 | jack     | it        |
|      5 | robin    | hr        |
+--------+----------+-----------+
4.子查询

​ 子查询是将一个查询语句嵌套在另一个查询语句中

​ 内层查询语句的查询结果，可以为外层查询语句提供查询条件

​ 子查询中可以包含：IN、NOT IN等关键字；还可以包含比较运算符：= 、 !=、> 、<等

案例一

​ 带IN关键字的子查询；查询employee表，但dept_id必须在department表中出现过

MySQL [company]> select * from employee6 WHERE dept_id IN (select dept_id FROM department6);
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 |          |   19 |     200 |
|      2 | tom      |   26 |     201 |
|      3 | jack     |   30 |     201 |
|      4 | alice    |   24 |     202 |
|      5 | robin    |   40 |     200 |
|      6 | xingdian |   16 |     200 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)
案例二

​ 带比较运算符的子查询；查询年龄大于等于25岁员工所在部门（查询老龄化的部门）

MySQL [company]> select dept_id,dept_name FROM department6 WHERE dept_id IN (SELECT DISTINCT dept_id FROM employee6 WHERE age >=25);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     201 | it        |
|     200 | hr        |
+---------+-----------+
2 rows in set (0.00 sec)
月之菈妮
关注
Day025 数据库相关知识
06-12
Day025 数据库相关知识
DAY 8 网络编程 数据库
10-20
DAY 8 网络编程 数据库
Day02操作数据库.rar
08-21
"Day02操作数据库.rar"这个压缩包文件很可能是包含一系列关于数据库操作的学习资料，可能是教程、笔记或者示例代码。根据提供的链接，我们可以预想这可能是一个Python相关的数据库学习资源，因为Gitee仓库的名称包含...
day46 数据库 60题 21题
09-24
day46 数据库 60题 21题
oceanbase V4.2.2社区版集群离线部署
king_harry的专栏
11-01 677
版本，选择是上传文件，包含oceanbase-ce、oceanbase-ce-libs、oceanbase-ce-utils。关闭时展示，为 OBProxy 集群的访问地址，仅用于生成租户的连接串，不影响实际使用，需要自主配置负载均衡，如果是 VIP 地址，还需要您自主申请并绑定到 OBProxy Server。集群配置选项卡—更多配置—更改系统内存保留选项，默认是30G，如果测试机内存只有16G，则需要调小，本测试调整为2G，这样ocp-server安装不会失败。自定义待管理的集群的名称。
基于vue框架的的汇生活家居商城的设计与实现bdjlq（程序+源码+数据库+调试部署+开发环境）系统界面在最后面。
h2345678的博客
10-29 1371
项目功能：商品分类,商品信息,用户。
【FL0013】基于SpringBoot和微信小程序的机电公司管理信息系统
最新发布
Wo_Hui12138的博客
11-03 340
如今社会上各行各业，都喜欢用自己行业的专属软件工作，互联网发展到这个时候，人们已经发现离不开了互联网。新技术的产生，往往能解决一些老技术的弊端问题。因为传统机电公司管理信息系统信息管理难度大，容错率低，管理人员处理数据费工费时，所以专门为解决这个难题开发了一个机电公司管理信息系统管理系统，可以解决许多问题。 机电公司管理信息系统管理系统按照操作主体分为管理员和用户。管理员的功能包括字典管理、公告管理、考勤管理、客户管理、机电零件管理、机电零件订单管理、请假管理、机电设备管理、机电设备订单管理、用户管理、管理
JAVA 插入 JSON 对象到 PostgreSQL
qq_52143611的博客
10-30 864
在现代软件开发中，由于 JSON 数据的轻量和通用性，处理 JSON 数据已经变得无处不在。PostgreSQL凭借其对 JSON 的强大支持，为存储和查询 JSON 数据提供了出色的平台。为了将JSON数据保存到PostgreSQL数据库中，我们可以将JSON数据转换为PostgreSQL的JSONB类型数据，然后将其存储在数据库中。在Java中，我们可以使用JDBC或者基于ORM框架的方式来实现这一功能。
【重学 MySQL】七十九、深入探索用户变量
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
10-30 871
定义：用户变量以“@”符号开头，后面跟着变量名。变量名可以是任何随机的、复合的标量表达式，只要其中没有列指定。赋值可以使用SET语句来定义和赋值用户变量，如。也可以使用SELECT语句来为用户变量赋值，但必须使用“:=”赋值运算符，因为在SELECT语句中，MySQL将“=”运算符视为等于运算符。例如，。作用域：会话用户变量的作用域是当前会话，而局部变量的作用域是其定义的BEGIN/END语句块。命名：会话用户变量以“@”符号开头，而局部变量不需要。使用场景。
JAVA中的MVC三层架构
楠寻寻的博客
10-30 1005
MVC（Model-View-Controller）是一种软件设计模式，用于将应用程序分为主要的部分：模型（Model）、视图（View）和控制器（Controller）。这种架构有助于提高代码的可维护性和可扩展性。Spring框架通过@Controller@Service和注解清晰地划分了MVC三层架构的各个层次，使得开发人员能够更高效地构建和维护复杂的Web应用。POJO类：用于表示业务实体，通常包含属性和相关的getter/setter方法。DAO类：用于封装对数据库的操作，提供数据访问的接口。
真·香！深度体验 zCloud 数据库云管平台 -- DBA日常管理篇
weixin_54551388的博客
11-01 460
点击蓝字 关注我们zCloud 作为一款业界领先的数据库云管平台，通过云化自治的部署能力、智能巡检和诊断能力、知识即代码的沉淀能力，为DBA的日常管理工作带来了革新式的简化与优化。经过一周的深度体验，今天笔者与您深入探讨 zCloud 在数据库用户、参数以及日志管理方面的具体应用，看它如何为DBA的日常管理工作带来价值。数据库用户管理“常在河边走哪有不湿鞋”，作为DBA或多或少都曾遇到过因权限配置...
数据库 二
wjj20040809的博客
11-02 459
表与表的关系：核心表mysql/oracle、SQLServer(微软)SQL。
SpringSession源码分析
lkr_lkr的博客
11-01 249
默认对常规Session的理解和使用，如何使用Set-Cookie。
电科金仓（人大金仓）更新授权文件（致命错误: XX000: License file expired.）
轩小川
10-30 2018
问题：电科金仓（人大金仓）数据库链接异常，重启失败，查看日志如下：致命错误: XX000: License file expired.位置: PostmasterMain, postmaster.c:725。
【知识科普】常见网络安全问题以及应对方式
wendao76的专栏
11-03 328
通过实现。
闲一品交易：SpringBoot框架下的创新实践
2401_85760095的博客
10-30 743
Java语言具有很好的面向对象性，可以符合人的思维模式进行设计，封装是将对象的属性和方法尽可能地隐藏起来，使得外界并不知道是如何实现的，外界能通过接口进行访问，继承是指每个类都会有一个父类，所有的子类都有父类的方法，可以进行继承，但是只有final修饰的类不能被继承，通过继承可以使得代码得到重新利用，能够提高软件的开发效率，也是多态的前提。
MySQL查询语句简介
licy__的博客
10-30 354
MySQL是一种广泛使用的开源关系数据库管理系统（RDBMS），它使用SQL（Structured Query Language）作为其数据库语言。SQL是用于管理和操作关系数据库的标准语言。通过SQL，用户可以创建、更新、删除以及查询数据库中的数据。
SpringBoot starter机制
路漫漫其修远兮，吾将上下而求索
10-30 276
在spring boot之前，想要在spring中集成redis，需要做的事情包括：（1）引入jar包：包括redis的jar包，redis依赖的jar包，相应的版本，与spring版本是否兼容等（2）在配置文件中配置连接地址、端口号等（3）以Jedis为例，增加增加JedisConnectionFactory、RedisTemplate 的bean初始化代码（4）使用RedisTemplate可以看到，集成过程比较复杂，而这些过程跟业务开发没有任何关系。
shodan7，shodan参数使用，常用端口，Google语法
2202_75361164的博客
10-31 363
泷羽sec课程笔记，星河飞雪计划 截图搜索参数has_screenshot:true cisco。我们能在web页面上面去做，而且更加方便，所以就不多讲了。搜索关于cisco产品的截图。
VB程序实现西门子数据库查询及时间处理
"西门子数据库查询VB程序用于在西门子系统中执行数据库操作，主要涉及时间转换、数据库连接及SQL查询等技术。" 在西门子的自动化系统中，数据库查询通常用于收集和分析过程数据。VB（Visual Basic）程序在此场景下...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值