MySQL 数据的查询（单表，多表）_将table查询,列查询,数据查询-优快云博客

本文链接：https://blog.youkuaiyun.com/DK_one/article/details/131628411

本文介绍了SQL中的数据插入、替换、更新、删除和查询语句，包括单表和多表操作，如INSERT用于添加数据，REPLACE在冲突时删除并插入新记录，UPDATE用于修改数据，DELETE和TRUNCATE分别删除数据但TRUNCATE更快，SELECT支持多种查询方式，JOIN用于多表关联查询，同时提供了多个练习题帮助读者巩固学习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.INSERT ：

介绍：在数据中添加或者插入数据

语法：

INSERT INTO table [(column [, column...])] VALUES(value [, value...]); 默认情况下，一次插入操作只插入一行；如果为每列都指定值，则表名后不需列出插入的列名

一次性插入多条记录： INSERT INTO table [(column [, column...])] VALUES(value [, value...])，(value [, value...])

如果不想在表名后列出列名，可以为那些无法指定的值插入null 可以使用如下方式一次插入多行

insert into 表名[(列名,…)]

2.REPLACE语句：

介绍：在数据中添加或者插入数据

语法：

语法格式1：

replace into 表名 [（字段列表）] values （值列表）

语法格式2：

replace [into] 目标表名[(字段列表1) select (字段列表2) from 源表 where 条件表达式

语法格式3：

replace [into] 表名 set 字段1=值1, 字段2=值2

replace语句的功能与insert语句的功能基本相同，不同之处在于：使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再插入新记录。

使用replace的最大好处就是可以将delete和insert合二为一(效果相当于更新)，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了

3.UPDATE语句：

介绍：修改数据

语法：

UPDATE table SET column = value [, column = value] [WHERE condition];

修改可以一次修改多行数据，修改的数据可用where子句限定，where子句里是一个条件表达式，只有符合该条件的行才会被修改。没有where子句意味着where字句的表达式值为true。也可以同时修改多列，多列的修改中间采用逗号(,)隔开

4.DELETE 和TRUNCAT语句：

介绍：

delete：删除数据，保留表结构，可以回滚，如果数据量大，很慢

truncate：删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快

drop：删除数据和表结构,删除速度最快

语法：

DELETE FROM table_name [where 条件];

TRUNCATE TABLE table_name

5.SELECT语句：

介绍：匹配表中数据同时支持多种规则以及多种表匹配

语法：

SELECT {*, column [alias],...} FROM table; // *：表明匹配所有 from：提供数据源（表名或视图）；默认选择所有行。

对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。

SELECT last_name, salary, salary*12 FROM employees;

MySQL的+默认只有一个功能：

运算符 SELECT 100+80; # 结果为180

SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203

SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80

SELECT 'This'+'is'; # 转换不成功，结果为0

SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL

优先级

乘法和除法的优先级高于加法和减法

同级运算的顺序是从左到右

表达式中使用括号可强行改变优先级的运算顺序

SELECT last_name, salary, salary*12+100 FROM employees;

SELECT last_name, salary, salary*(12+100) FROM employees;

NULL值的使用：

空值是指不可用、未分配的值

空值不等于零或空格

任意类型都可以支持空值

包括空值的任何算术表达式都等于空

字符串和null进行连接运算，得到也是null.

安全等于<=>

1.可作为普通运算符的=

2.也可以用于判断是否是NULL

如：where salary is NULL/(is not NULL) ->where salary NULL

示例1：

查询emp表奖金为空的员工信息。 select * from emp where comm NULL;

示例2：

查询emp表奖金为50000的员工信息 select * from emp where comm 50000;

利用 as 定义字段的别名：

改变列的标题头

用于表示计算结果的含义

作为列的别名

如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现。

SELECT last_name as “姓名”, salary “薪水” FROM employees;

SELECT last_name, salary*12 “年薪” FROM employees;

重复记录：

缺省情况下查询显示所有行,包括重复行

SELECT department_id FROM employees;

使用DISTINCT关键字可从查询结果中清除重复行

SELECT DISTINCT department_id FROM employees;

DISTINCT的作用范围是后面所有字段的组合

SELECT DISTINCT department_id , job_id FROM employees;

利用WHERE条件限制：

使用WHERE子句限定返回的记录 WHERE子句在FROM 子句后

SELECT [DISTINCT] {*, column [alias], ...} FROM table [WHEREcondition(s)];

WHERE中的字符串和日期值字符串和日期要用单引号扩起来字符串是大小写敏感的,日期值是格式敏感的

SELECT last_name, job_id, department_id FROM employees WHERE last_name = "king";

WHERE中比较运算符:

SELECT last_name, salary, commission_pct FROM employees WHERE salary<=1500;

其他比较运算符使用BETWEEN运算符显示某一值域范围的记录

SELECTlast_name, salary FROM employees WHERE salary BETWEEN 1000 AND 1500;

使用IN运算符使用IN运算符获得匹配列表值的记录

SELECTemployee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (7902, 7566, 7788);

使用LIKE运算符使用LIKE运算符执行模糊查询查询条件可包含文字字符或数字 (%) 可表示零或多个字符 ( _ ) 可表示一个字符

SELECT last_name FROM employees WHERE last_name LIKE '_A%';

使用IS NULL运算符查询包含空值的记录

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;

逻辑运算符

使用AND运算符 AND需要所有条件都是满足T.

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary>=1100–4 AND job_id='CLERK';

使用OR运算符 OR只要两个条件满足一个就可以

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary>=1100 OR job_id='CLERK';

使用NOT运算符 NOT是取反的意思

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('CLERK','MANAGER','ANALYST');

使用正则表达式：

REGEXP regexp '正则表达式' select * from product where product_name regexp '^2018';

数据分组--GROUP BY

GROUP BY子句的真正作用在于与各种聚合函数配合使用。

它用来对查询出来的数据进行分组。

分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。

分组函数忽略空值,。

结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句。

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

数据分组--限定组的结果：HAVING子句：

HAVING子句用来对分组后的结果再进行条件过滤。

SELECT column, group_function

FROM table

[WHERE condition] where限定

[GROUP BY group_by_expression] 分组

[HAVING group_condition] 对分组后的数据限定

[ORDER BYcolumn]; 排序输出

HAVING子句用来对分组后的结果再进行条件过滤。

不能在WHERE 子句中限制组. 限制组必须使用HAVING 子句. 不能在WHERE 子句中使用组函数

多行数据合并 GROUP_CONCAT：

mysql> select s_id,group_concat(f_name) //将f_name中的数据合并

from fruits

group by s_id; //利用s_id分组

LIMT 限制检索记录：

SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/

SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/

6.多表关联查询：

1. inner join：代表选择的是两个表的交差部分。

内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。

语法如下： SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

2. left join：代表选择的是前面一个表的全部。左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。

语法如下： SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

3. right join：代表选择的是后面一个表的全部同理，右连接将会以右边作为基准，进行检索。

语法如下： SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

4.自连接自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)

5.交叉连接：不适用任何匹配条件。生成笛卡尔积

示例：

以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25, 即找出公司所有部门中年龄大于25岁的员工。

select * from employee1 inner join department on employee1.dep_id=department.id and age>25;

以内连接的方式查询employee和department表，并且以age字段的升序方式显示 select * from employee1 inner join department on employee1.dep_id=department.id and age>25 and age>25 order by age asc;

以自连接的方式列出所有员工的姓名及其直接上级的姓名 mysql> select e.ename,m.ename boss_name from emp e -> left join emp m on e.mgr=m.empno;

7.练习：

单表练习

mysql> create table employee(id int(10) primary key auto_increment, name varchar(20) not null, gender enum('M','F') default 'M', salary int(10) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into employee(name,gender,salary) values('zhangshan','M',2000), ('lishi','M',1000), ('wangwu','F',4000);
Query OK, 3 rows affected (0.01 sec)

将所有员工薪水修改为5000元

update employee set salary = 5000 ;

将姓名为张三的员工薪水修改为3000元
update employee set salary = 3000 where name = 'zhangshan';

将姓名为李四的员工薪水修改为4000元，gener改为女
update employee set salary = 4000 where name = 'lishi';

将王五的薪水在原有基础上增加1000元
update employee set salary = salary + 1000 where name = 'wangwu';

CREATE TABLE emp ( empno int(4) NOT NULL,

ename varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

job varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, mgr int(4) NULL DEFAULT NULL,

hiredate date NOT NULL,

sai int(255) NOT NULL,

comm int(255) NULL DEFAULT NULL,

deptno int(2) NOT NULL,

PRIMARY KEY ( empno ) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20); INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30); INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30); INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20); INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30); INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30); INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10); INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20); INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10); INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30); INSERT INTO emp VALUES (1011, '周泰', '文员', 1006, '2007-05-23', 11000, NULL, 20); INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30); INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20); INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10); INSERT INTO emp VALUES (1015, '张三', '保洁员', 1001, '2013-05-01', 80000, 50000, 50);

1. 查询出部门编号为30的所有员工
mysql> select * from emp where deptno = 30;
2. 所有销售员的姓名、编号和部门编号。
mysql> select ename,empno,mgr from emp;
3. 找出奖金高于工资的员工。
mysql> select * from emp where sai < comm;
4. 找出奖金高于工资60%的员工。
mysql> select * from emp where sai < (comm * 0.6);
5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
mysql> select * from emp where deptno in (10,20) and job in ('经理','销售员');
6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工
资大或等于20000的所有员工详细资料。
mysql> select * from emp where (deptno = 10 and job = '经理') or (deptno = 20 and job = '销售员') or (sai >= 20000 and job != '经理' and job !='销售员');
7. 无奖金或奖金低于1000的员工。
mysql> select * from  emp where comm is null or comm < 1000;
8. 查询名字由三个字组成的员工。
mysql> select * from  emp where length(ename) = 9;  
9.查询2000年入职的员工。
mysql> select * from emp where year(hiredate)=2000;
10. 查询所有员工详细信息，用编号升序排序
mysql> select * from emp order by empno;
11. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
mysql> select * from emp order by sai desc,hiredate;
12.查询每个部门的平均工资
mysql> select deptno,avg(sai) as total from emp group by deptno;
13.查询每个部门的雇员数量
mysql> select deptno,count(job) as total from emp group by deptno;
14.查询每种工作的最高工资、最低工资、人数
mysql> select job,max(sai),min(sai),count(job) from emp group by job;

多表连接查询

use mydb3;

-- 创建部门表

create table if not exists dept3(

deptno varchar(20) primary key , -- 部门号

name varchar(20) -- 部门名字

);

创建员工表

create table if not exists emp3(

eid varchar(20) primary key , -- 员工编号

ename varchar(20), -- 员工名字

age int, -- 员工年龄

dept_id varchar(20) -- 员工所属部门

);

给dept3表添加数据

insert into dept3 values('1001','研发部');

insert into dept3 values('1002','销售部');

insert into dept3 values('1003','财务部');

insert into dept3 values('1004','人事部');

给emp3表添加数据

insert into emp3 values('1','乔峰',20, '1001');

insert into emp3 values('2','段誉',21, '1001');

insert into emp3 values('3','虚竹',23, '1001');

insert into emp3 values('4','阿紫',18, '1001');

insert into emp3 values('5','扫地僧',85, '1002');

insert into emp3 values('6','李秋水',33, '1002');

insert into emp3 values('7','鸠摩智',50, '1002');

insert into emp3 values('8','天山童姥',60, '1003');

insert into emp3 values('9','慕容博',58, '1003');

insert into emp3 values('10','丁春秋',71, '1005');

---1 查询每个部门的所属员工
 select name,GROUP_CONCAT(ename)from emp3 e RIGHT JOIN dept3 d on  e.dept_id = d.deptno GROUP BY name ;
-- 2 查询研发部门的所属员工
select name,GROUP_CONCAT(ename)  from emp3 e RIGHT JOIN dept3 d on  e.dept_id = d.deptno and name='研发部';
-- 3、查询研发部和销售部的所属员工
select name, GROUP_CONCAT(ename) from emp3 e RIGHT JOIN dept3 d on  e.dept_id = d.deptno group by name and d.name='研发部' or d.name='销售部' ;
-- 4、查询每个部门的员工数,并升序排序
select name,count(deptno) as total from emp3 e left JOIN dept3 d on  e.dept_id = d.deptno GROUP BY d.name  HAVING NAME is not null ORDER BY total;
-- 5、查询人数大于等于3的部门，并按照人数降序排
select name,count(deptno) as total from emp3 e left JOIN dept3 d on  e.dept_id = d.deptno GROUP BY d.name  HAVING NAME is not null and total > 1 ORDER BY total desc

create table dept (dept1 int ,dept_name varchar(11));
create table emp (sid int ,name varchar(11),age int,worktime_start date,incoming int,dept2 int);

   insert into dept values
   (101,'财务'),
   (102,'销售'),
   (103,'IT技术'),
   (104,'行政');

   insert into emp values
   (1789,'张三',35,'1980/1/1',4000,101),
   (1674,'李四',32,'1983/4/1',3500,101),
   (1776,'王五',24,'1990/7/1',2000,101),
   (1568,'赵六',57,'1970/10/11',7500,102),
   (1564,'荣七',64,'1963/10/11',8500,102),
   (1879,'牛八',55,'1971/10/20',7300,103);

-- 1.找出销售部门中年纪最大的员工的姓名
select name from emp e ,dept d where e.dept2=d.dept1 and dept_name='销售' and age=(select max(age) from emp where dept2=(select dept1 from dept where dept_name='销售'));

-- 2.求财务部门最低工资的员工姓名
select name from emp e ,dept d where e.dept2=d.dept1 and dept_name='财务' and incoming=(
select min(incoming) from emp where dept2=(select dept1 from dept where dept_name='务'));

-- 	3.列出每个部门收入总和高于9000的部门名称
select dept_name from dept d,(select dept2 from emp group by dept2 having sum(incoming)> 9000 ) e where d.dept1=e.dept2;

-- 	4.求工资在7500到8500元之间，年龄最大的人的姓名及部门
select dept_name,`name`  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  WHERE incoming >= 7500 AND incoming <= 8500 and d.dept_name = '销售'  ORDER BY age desc LIMIT 1;
-- 	5.找出销售部门收入最低的员工入职时间 
SELECT worktime_start FROM emp e RIGHT JOIN dept d ON e.dept2 = d.dept1 where  d.dept_name = '销售'  ORDER BY incoming ASC LIMIT 1;
-- 	6.财务部门收入超过2000元的员工姓名
select name  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  where d.dept_name = '财务' and incoming > 2000 ;
-- 	7.列出每个部门的平均收入及部门名称
select dept_name,AVg(incoming)  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1 group by dept_name;
-- 	8.IT技术部入职员工的员工号
select sid  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  where d.dept_name = 'IT技术'
-- 	9.财务部门的收入总和；
select dept_name,sum(incoming)  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1 WHERE d.dept_name = '财务' ;
-- 	10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表
select *  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  ORDER BY dept2,worktime_start;
-- 	11.找出哪个部门还没有员工入职；
select dept_name from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1 WHERE worktime_start is NULL;
-- 	12.列出部门员工收入大于7000的部门编号，部门名称；
select dept1,dept_name from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1 WHERE incoming > 7000;
-- 	13.列出每一个部门的员工总收入及部门名称；
select dept_name,sum(incoming)  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1 group by dept_name
-- 	14.列出每一个部门中年纪最大的员工姓名，部门名称；
select MAX(age),dept_name from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1 group by dept_name
-- 	15.求李四的收入及部门名称
select `name`,incoming,dept_name  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  where name = '李四'
-- 	16.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
select name,MAX(incoming),dept_name  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  group by dept_name ;
-- 	17.列出部门员工数大于1个的部门名称
select dept_name,dept2  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  group by dept_name HAVING COUNT(dept2) > 1;
-- 	19.查找张三所在的部门名称
select dept_name  from emp e RIGHT JOIN dept d on  e.dept2 = d.dept1  where name = '张三'