MySql数据库增删改查操作

已于 2023-04-19 22:19:22 修改
阅读量2k 收藏 29
点赞数 6
分类专栏： 接口测试 SQL 文章标签： 数据库 mysql sql
于 2023-04-19 13:22:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44691253/article/details/129973660
版权

1、配置Mysql

Docker配置Mysql可参考之前写的博文：https://blog.csdn.net/weixin_44691253/article/details/127835697

2、数据库操作

2.1 创建数据库

create database 数据库名称;

在这里插入图片描述刷新数据库连接后，可以看到新创建的数据库 testsql
在这里插入图片描述在这里插入图片描述

2.2 删除数据库

drop database 数据库名称;

在这里插入图片描述再次刷新数据库连接，testsql数据库已删除

3、表操作

3.1 创建表

语法：
create table 表名(
	字段1 类型 [约束],
	字段2 类型 [约束],
	........
);

Sql语句：
-- 创建一个用户信息表，有这些信息：用户名、密码、电话、邮箱
create table userinfo(
	username varchar(20), #字段名 数据类型
	pwd varchar(50),
	tel varchar(15),
	email varchar(20)
);

在这里插入图片描述右键表，选择设计表，可以看到表的字段类型和长度跟创建时是一样的。
在这里插入图片描述

3.2 删除表

drop table 表名;

把表userinfo删除后，刷新连接，表userinfo已删除
在这里插入图片描述

3.3 修改表

为了后面的测试，在把userinfo创建回来。

修改表达用alert语句

3.3.1 向表中添加新字段

-- 向userinfo表中添加一列，字段名：status，类型：int
alter table userinfo add status int;

在这里插入图片描述再次查看设计表，看到新增的status字段，类型为int
在这里插入图片描述

3.3.2 删除表中的已有字段

-- 删除userinfo表中的列email
alter table userinfo drop email;

在这里插入图片描述再次查看表设计，email字段已被删除
在这里插入图片描述

3.3.3 修改表中已有字段的名称

modify不能用来修改字段名，change可以

-- 将userinfo中的列username的名称改成login_name
alter table userinfo change username login_name varchar(20);

在这里插入图片描述再次查看表设计，username已经变成login_name。
在这里插入图片描述
后续不再截图，执行sql语句后，可自行打开设计表进行查看效果。

3.3.4 修改表中已有字段的类型

-- 将userinfo表中的pwd字段类型修改成int
alter table userinfo change pwd pwd int;

3.3.5 同时修改字段名和字段类型

-- 同时修改字段名和数据类型
-- pwd重命名为my_pwd，数据类型从int变成varchar(16)
alter table userinfo change pwd my_pwd varchar(16);

3.3.6 修改字段在表中的显示位置

-- 将status展示在表中第一列
alter table userinfo modify status int first;
-- 将status展示在login_name后边：login_name将变成第一列，status成为第2列
alter table userinfo modify status int after login_name;

4、数据完整性约束-实体完整性

4.1 主键约束

4.1.1 已有表添加主键约束

每个表有且只能有一个主键约束。
语法：

  • 关键字：primary key
  • 添加约束语法：
    alter table 表名 add constraint 约束名 primary key(字段名);
  • 约束名: PK_字段
-- 向userinfo表中添加字段uid
alter table userinfo add uid int;
-- 将uid添加为主键
alter table userinfo add constraint PK_uid primary key(uid);

4.1.2 删除表主键约束

语法：
删除约束语法：alter table 表名 drop primary key;


-- 删除表主键约束，注：只删除约束，不会删除字段
alter table userinfo drop primary key;

4.1.3 创建表指定主键

create table student(
	id int primary key,
	name varchar(20),
	age int
);

4.2 唯一约束

特点：不能重复，可以为空，可以添加多个

  • 关键字：unique
  • 添加约束语法：alter table 表名 add constraint 约束名 unique(字段名);
  • 约束名: UQ_字段
  • 删除约束语法：alter table 表名 drop key 约束名;

4.2.1 创建表指定唯一约束

-- 先删除之前创建的student表
drop table student;
-- 创建student表，指定id_card为唯一约束
create table student(
	id int primary key,
	name varchar(20),
	age int,
	id_card varchar(20) unique
);

4.2.2 已有表添加唯一约束

-- 将userinfo表中的login_name添加为唯一约束
alter table userinfo add constraint UQ_login_name unique(login_name);

4.2.3 已有表删除唯一约束

-- 删除userinfo表中的唯一约束
alter table userinfo drop key UQ_login_name;

4.3 主键自增

  • 特点：从1开始，每次自身加1(在oracle中不能使用)
  • 关键字： auto_increment
  • 只能在创建表的时候添加主键自增的约束，而且必须是主键才可以添加

4.3.1 创建表添加主键约束且自增

-- 先删除之前创建的student表
drop table student;
-- 创建student表，id作为主键，且自增
create table student(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	id_card varchar(20) unique
);

4.3.2 删除表主键自增

-- 删除student表，主键自增：仅删除自增，并没有删除主键
alter table student modify id int;
-- 删除student表，主键约束：同上述4.1.2
alter table student drop primary key;

5、数据完整性约束-域完整性

域完整性约束：保证字段的数据准确的
域完整性包括类型约束、非空约束、默认值

1）非空约束

  • 特点：字段不允许为空
  • 关键字： not null
    2）默认值
  • 特点：设置默认的值
  • 关键字： default
-- 先删除之前创建的student表
drop table student;
-- 创建student表，id作为主键并自增；name不能为空；性别默认为Male；id_card为唯一约束
create table student(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int not null,
	sex char(6) default 'Male',
	id_card varchar(20) unique
);

6、数据完整性约束-引用完整性

一张表中通用列的取值必须参考另外一张表的主键
引用完整性有外键约束
1）外键约束

  • 特点：设置外键的字段的取值只能参考另一张表中同一个字段的值
  • 关键字： foreign key
  • 添加外键约束的语法：
    alter table 表名1 add constraint 约束名 foreign key(字段名) references 表名2(字段名)
  • 删除外键的语法：alter table 表名 drop foreign key 约束名;

注意：
1、主外键关联
2、外键关联字段名称可以不一样，但是类型必须一致

6.1 创建表使用外键约束

-- 先删除之前创建的student表
drop table student;
-- 外键约束：class_id，是classroom表的主键
create table student(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int not null,
	sex char(6) default 'Male',
	id_card varchar(20) unique,
	class_id int #添加外键约束
);

-- 创建classroom表，class_id作为主键
create table classroom(
	class_id int PRIMARY key,
	class_name varchar(20)
);

6.2 已有表添加外键约束

  • 语法：alter table 表名1 add constraint 约束名 foreign key(字段名)
    references 表名2(字段名)
  • 约束名： FK_字段名
-- 向Student表添加外键class_id，引用自classroom表的主键class_id
alter table student add constraint FK_class_id foreign key(class_id) references classroom(class_id);

在这里插入图片描述在这里插入图片描述

6.3 删除表的外键约束

注：如果表A有外键约束，是表B中的主键。那么删除表B时，应该先删除表A的外键约束，才能删除表B。例如，要想删除classroom表，要先删除student表的外键约束，才能删掉classroom表。

-- 删除外键：alter table 表名 drop foreign key 约束名;
alter table student drop foreign key FK_class_id;

7、增删改查Sql

-- 删除外键：alter table 表名 drop foreign key 约束名;
alter table student drop foreign key FK_class_id;

--先删除之前创建的classroom表
drop table classroom;

-- 创建classroom表
create table classroom(
	cid int,
	cname varchar(20),
	des varchar(50)
);

7.1 insert插入数据

语法：insert into 表名[(字段名….)] values(值….)

示例1：

-- 给所有字段添加值，和表中字段顺序一致
insert into classroom values(1,'Coding','java');

-- 给cname字段添加值,和指定字段的顺序必须一致
insert into classroom(cname) values('python');
insert into classroom(cid,cname) values(3,'test');

-- 添加3条记录(批量插入)
insert into classroom values(4,'oracle','oracle_class'), (5,'html','html_class'), (6,'Mysql','Mysql_class');

select * from classroom;

在这里插入图片描述

示例2：
当两个表结构完全相同时，将一个表中的数据插入到另一个表中

-- 将classroom的值整体复制到classroom1
create table classroom1(
	cid int,
	cname varchar(20),
	des varchar(50)
);
insert into classroom1 select * from classroom;
-- 查看classroom1的结果
select * from classroom1;

在这里插入图片描述

7.2 删除表中满足条件的数据

-- 删除表中cname等于python的这条数据
delete from classroom where cname='python';
select * from classroom;

在这里插入图片描述

7.3 删除表中所有数据

-- 删除classroom中的所有数据
delete from classroom;
select * from classroom;

在这里插入图片描述

7.4 truncate和delete的区别

  • truncate清空表的数据

  • 语法：truncate table 表名;

  • 注意：delete与truncate的区别是什么

  • delete是逐行删除，truncate是文件级别的清空

  • delete删除后，自增性会继续执行，不会重置

  • truncate删除后，自增性重置

7.5 Update更新已有数据

-- 刚才删除了classroom的全部数据，现在插入一条数据
insert classroom VALUES (1, 'test1', 'sql_class')

在这里插入图片描述

-- 将classroom中cid是1的这条数据，cname修改成test2
update classroom set cname='test2' where cid=1; -- cid主键列

在这里插入图片描述

-- 插入一条数据
insert classroom(cid, des) VALUES (2, 'oracle_class')
select * from classroom;

在这里插入图片描述

-- 将des是oracle_class的这条数据，cname修改成test1
update classroom set cname='test1' where des='oracle_class';
select * from classroom;

在这里插入图片描述

-- 修改classroom中的所有数据
update classroom set des='class_info';
select * from classroom;

在这里插入图片描述

7.6 Select语句

select 10/3; -- 3.3333 / 会保留小数
select 10 div 3; -- 3 div 整除（只会取整个结果的整数部分）
select 10 % 3; -- 1 % 取模 取余(取结果的余数)
select 10 mod 3; -- 1 mod 取模 取余
select 1>0; -- 1 1==>True
select 1<0; -- 0 0==>False
select 1!=0; -- 1 1==>True
select 1<0 and 2>1; -- 0 0==>False
select 1<0 or 2>1; -- 1 1==>True
select !(1<0); -- 1 1==>True

在这里插入图片描述

8、Select语句

员工表：emp

/*
 Navicat Premium Data Transfer

 Source Server         : mysql001
 Source Server Type    : MySQL
 Source Server Version : 50718
 Source Host           : 127.0.0.1:33506
 Source Schema         : testsql

 Target Server Type    : MySQL
 Target Server Version : 50718
 File Encoding         : 65001

 Date: 10/04/2023 21:39:50
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `JOB` varchar(9) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `MGR` int(4) NULL DEFAULT NULL,
  `HIREDATE` date NULL DEFAULT NULL,
  `SAL` int(7) NULL DEFAULT NULL,
  `COMM` int(7) NULL DEFAULT NULL,
  `DEPTNO` int(2) NULL DEFAULT NULL,
  PRIMARY KEY (`EMPNO`) USING BTREE,
  INDEX `FK_DEPTNO`(`DEPTNO`) USING BTREE,
  CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

SET FOREIGN_KEY_CHECKS = 1;

部门表：

/*
 Navicat Premium Data Transfer

 Source Server         : mysql001
 Source Server Type    : MySQL
 Source Server Version : 50718
 Source Host           : 127.0.0.1:33506
 Source Schema         : testsql

 Target Server Type    : MySQL
 Target Server Version : 50718
 File Encoding         : 65001

 Date: 10/04/2023 21:40:48
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` varchar(14) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `LOC` varchar(13) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');

SET FOREIGN_KEY_CHECKS = 1;

8.1 查询全部信息

select * from emp;

在这里插入图片描述

8.2 查询部分列信息

select ename,sal from emp;

在这里插入图片描述

8.3 查询列（表达式）

SELECT子句中的目标列，可以是属性列，也可以是表达式。比如，sal+sal*0.05就是一个计算表达式。还可以是函数、字符串常量等。例如，lower(ename)

select LOWER(ename),sal+sal*0.05 from emp;

在这里插入图片描述

8.4 查询列指定别名

SELECT ename 姓名, SAL 薪水 from emp

在这里插入图片描述

8.5 DISTINCT去除重复行

清除数据重复的行，使用DISTINCT关键词。
注意是行。对查询结果进行去重，如果查询结果有很多列，所有列数据都完全相同时，才会被去重。
在这里插入图片描述

select  DISTINCT sal, ename  from emp;

例如两条数据，（3000，SCOTT）和（3000，FORD）就不是重复的数据，不会被去重。
在这里插入图片描述

8.6 Where子句

查询满足条件的元组，通过WHERE 子句。多个查询条件中间用AND或者OR连接。AND优先级大于OR

查询条件谓词
比较=、>、<、>=、<=、!=、<>、!>、!<、NOT+上述比较运算符
确定范围BETWEEN AND、NOT BETWEEN AND
确认集合IN、NOT IN
字符匹配LIKE、NOT LIKE
空值IS NULL、IS NOT NULL
多重条件（逻辑运算）AND、OR、NOT

8.6.1 比较

-- 查询工资大于2000的员工信息 --比较运算
select * from emp where sal > 2000;
-- 查询工资在1000-2000之间的员工信息(范围查询) --比较
select * from emp where sal>=1000 and sal<=2000;

8.6.2 范围查询：Between and

-- 查询工资在1000-2000之间的员工信息(范围查询) --比较+逻辑运算
select * from emp where sal between 1000 and 2000; -- 包括边界
-- 查询工资 不在 1000-2000之间的员工信息(范围查询)
select * from emp where sal not between 1000 and 2000;

8.6.3 集合查询：IN、Not in

-- 查询员工编号为7521, 7369, 7788的员工信息(集合查询)
select * from emp where empno=7521 or empno=7369 or empno=7788;
select * from emp where empno in(7521,7369,7788);
select * from emp where empno not in(7521,7369,7788);

8.6.4 字符匹配：LIKE、NOT LIKE

字符匹配，使用LIKE和NOT LIKE，后跟匹配串，匹配串可以包含通配符。

通配符含义
%（百分号）代表任意长度的字符串（长度可以为0）。例如a%b，代表以a开头，以b结尾任意长度的字符串。如ab，aaab, acb都满足
_（下横线）代表任意单个字符。a_b代表以a开头以b结尾的任意长度是3的字符串。
-- 查询ename第2个字母是L的
select * from emp where ename like "_L%";
-- 查询ename第2个字母是L，最后一个字母是N的
select * from emp where ename  like "_L%_N";

在这里插入图片描述

-- 查询ename是4个字母，且第1个字母是K，第3个字母是N
select * from emp where ename like "K_N_";

在这里插入图片描述

-- 查询ename第2个字母不是L的
select * from emp where ename not like "_L%";

在这里插入图片描述
注：如果要查询的数据本身包含通配符，要用ESCAPE进行转义，如DB_Design

SELECT Cno
FROM Course
WHERE Cname LIKE "DB\_Design" ESCAPE '\'

ESCAPE表示’\‘是换码字符，这样匹配串‘\’后面紧跟的’_’

就不是通配符，转义为普通的’_'字符

其他示例：以DB_开头的，倒数第3个字符是i的课程的Cno

WHERE Cname LIKE "DB\_%i__" ESCAPE '\'

8.6.5 空值查询：IS NULL、IS NOT NULL

-- 查询COMM是空值的
select * from emp where COMM is NULL

-- 查询COMM不是空值的
select * from emp where COMM is NOT NULL

8.6.6 多条件查询：AND、OR

-- 查询COMM为空，并且SAL大于4000的数据
select * from emp where COMM is NULL and SAL > 4000

-- 查询COMM为空，或者COMM小于500的数据
select * from emp where COMM is NULL OR COMM < 500

8.7 ORDER BY子句

用处：根据查询结果的1个或者多个列属性进行升降序排列。默认升序ASC (ASC可省略)。降序为DESC
如果有空值，排序时次序按照具体系统实现来决定。例如升序，含空值的元组最后显示。降序，含空值的元组最先显示。不同系统实现可以不同，只要保持一致即可。

8.7.1 升序ASC

select * from emp where ename  like "_L%" ORDER BY ename ASC;

在这里插入图片描述

8.7.2 降序DESC

select * from emp where ename  like "_L%" ORDER BY ename DESC;

在这里插入图片描述

8.7.3 limit

limit m,n
m:开始的位置，索引值默认从0开始 n:取值的长度(个数)
limit 0, 100和limit 100等效。

-- 不使用limit时
select * from emp where ename not like "K_N_" ORDER BY ename;

在这里插入图片描述

-- 从查询的结果中，从索引0开始取结果（包含索引0这条），取2条数据，即前2条数据
select * from emp where ename not like "K_N_" ORDER BY ename limit 0,2;
-- 从查询的结果中，从索引是3开始取结果（包含索引3这条），取5条数据
select * from emp where ename not like "K_N_" ORDER BY ename limit 3,5;

在这里插入图片描述
在这里插入图片描述

9、聚集函数

当聚合函数有空值时，除COUNT(*)外都是跳过空值只处理非空值。因为COUNT是统计数据条数，所以元组中某一列或者部分列有空值并不影响COUNT的统计结果。
注：只有SELECT子句和GROUP BY的HAVING子句才可以用聚集函数，WHERE子句不能使用。

聚集函数SQL含义
COUNT(*)SELECT COUNT(*) FROM 表名统计表中数据条数
COUNT(DISTINCT/ALL 列名) SELECT COUNT(DISTINCT Sno) FROM SC统计表中Sno列值的个数且去重
SUM(DISTINCT/ALL 列名)SELECT SUM(Grade) FROM SC统计表中Grade列值的总和，必须是数值型
AVG(DISTINCT/ALL 列名)SELECT AVG(Grade) FROM SC统计表中Grade列值的平均值，必须是数值型
MAX(DISTINCT/ALL 列名)SELECT MAX(Grade) FROM SC统计表中Grade列值的最大值，必须是数值型
MIN(DISTINCT/ALL 列名)SELECT MIN(Grade) FROM SC统计表中Grade列值的最小值，必须是数值型
-- 查询emp表数据总条数
select count(*) from emp; -- 14
-- 统计COMM列不是null值的员工人数
select count(COMM) from emp; -- 4个 count() 统计的字段的非空值的数量
-- 查询emp中员工都在哪些部门：DEPTNO需要去重
select count(DISTINCT DEPTNO) from emp;  -- 3，因为有3个部门，10，20，30
-- 查询emp中员工的薪资总和
select SUM(SAL) from emp;  -- 29025
-- 查询emp中10号部门的员工的薪资最大值
select MAX(SAL) from emp where deptno=10 ;  -- 5000
-- 查询emp中员工的薪资最小值
select MIN(SAL) from emp;  -- 800

-- 查询emp中员工的薪资平均值
select AVG(SAL) from emp;  -- 2073.2143

10、GROUP BY子句

将查询结果按某一列或者多列的值进行分组，值相等的为一组
WHERE 子句和HAVING短句的区别在于作用对象不同。WHERE作用于基本表和视图。HAVING短句作用于组，从中选择满足条件的组。having后面可接聚合函数，where后不能接聚合函数

注：如果没有对查询结果进行分组，那么聚集函数作用域是整个查询结果。如果有对分组，聚集函数作用域是每个分组。

例如：APP首页的底部有3个btn，点击事件名是一样的，参数值是btn的名字。查询今天点击APP底部btn的数据量。如果没有使用分组，那么将统计的是整个3个btn点击量的总和。采用分组，那将分别统计每个btn的点击量

例子含义
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno按课程进行分组查看选课人数
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3查询选修了3门以上课程的学生学号。HAVING短句给出了选择组的条件，只有满足条件才会被选中。
SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) >= 90选择平均成绩大于等于90分的学生学号和平均成绩
-- 统计不同部门的员工有多少
select  DEPTNO, COUNT(*) from emp GROUP BY DEPTNO

共14个员工，3个来自部门No.10，5个来自部门No.20，30个来自No.30
在这里插入图片描述

-- 统计部门人数超过3人的部门编号及员工个数
select  DEPTNO, COUNT(*) from emp GROUP BY DEPTNO HAVING COUNT(*) >3

在这里插入图片描述

-- 查询平均工资大于2000的部门的编号和平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal)>2000;

在这里插入图片描述

10.1 where后不能接聚合函数

-- 会报错，Invalid use of group function
select * from emp deptno where avg(sal)>2000;

在这里插入图片描述

11、SQL关联查询

关联查询又叫做连接查询，常见的连接查询可分为：内连接、外连接、自然连接、自连接

查询方式区别备注
等值连接连接运算符是=号
非等值连接连接运算符是比较运算符（大于、等于、小于、小于等于、大于等于、不大于、不小于）连接条件中的各连接字段必须是可比的，但名字不必相同。
自然连接目标列中重复的属性列去掉
自身连接一个表与其自身进行连接表2是表2的别名

11.1 内连接

没有主从表之分，只会将两个表中相等的值列出在结果集中。取的是两边相交的共同部分。
关键字：inner join …on
emp表中并没有在40编号的部门，都是编号10,20,30部门的员工。
在这里插入图片描述

dept表中有4个部门：10, 20, 30, 40
在这里插入图片描述
两张表相等的值就是，在10,20,30这3个部门的员工，共14个人。

-- 内连接
-- 没有主从表之分，只会将两个表中相等的值列出在结果集中
select * from emp ORDER BY ename desc;
select * from dept ORDER BY dname desc;
-- 查询员工的姓名及所在部门名称
select ename, dname from emp inner join dept on emp.deptno=dept.deptno ORDER BY ename desc;
-- 查询员工编号是7788的员工的姓名及所在部门名称
select ename, dname from emp inner join dept on emp.deptno=dept.deptno and empno=7788 ORDER BY ename desc;

在这里插入图片描述

11.2 左外连接

left join 前面的是主表，后面的是从表，会遍历主表中的每一个条记录

关键字：left join …on

-- 左外连接
-- 主表、从表 left join 前面的是主表，后面的是从表，会遍历主表中的每一个条记录，
select * from emp ORDER BY ename desc;
select * from dept ORDER BY dname desc;
-- 查询所有部门的名称(dname)和该部门对应的员工姓名(ename)
select * from dept left join emp on dept.deptno=emp.deptno ORDER BY ename desc

在这里插入图片描述

-- 左外连接
-- 主表、从表 left join 前面的是主表，后面的是从表，会遍历主表中的每一个条记录，
select * from emp ORDER BY ename desc;
select * from dept ORDER BY dname desc;
-- 查询所有员工的名称(dname)和员工所所在部门的名称(dname)
select * from emp left join dept on dept.deptno=emp.deptno ORDER BY ename desc;

在这里插入图片描述
从上面两个查询结果可以看出，谁是主表，谁是从表，查询结果的不同。

11.3 右外连接

主表、从表 right join 前面的是从表，后面的是主表，会遍历主表中的每一个条记录
关键词：right join … on

-- 查询所有部门的名称(dname)和该部门对应的员工姓名(ename)
-- 调整主表和从表后，使用左外连接和右外连接是一样的效果。下面两种方式都是将dept作为主表，将emp作为从表。
select dname, ename from emp right join dept on dept.deptno=emp.deptno ORDER BY ename desc;
select dname, ename from dept left join emp on dept.deptno=emp.deptno ORDER BY ename desc;

在这里插入图片描述

-- 查询所有员工的名称(dname)和员工所所在部门的名称(dname)。调整主表和从表后，使用左外连接和右外连接是一样的效果。下面两种方式都是将emp作为主表，将dept作为从表。
select dname, ename from dept right join emp on dept.deptno=emp.deptno ORDER BY ename desc;
select dname, ename from emp left join dept on dept.deptno=emp.deptno ORDER BY ename desc;

在这里插入图片描述

11.4 自连接

给当前表起不同的别名，从而实现自身的连接查询

例如：将emp表指定两个别名，分别是e和p。从e表中获取ename并且给列指定别名“员工”；从p表中获取ename并且给列指定别名“上级领导”。

-- 查询所有员工和他上级领导的姓名
select * from emp;
select e.ename 员工, p.ename 上级领导 from emp e left join emp p on e.mgr=p.empno;

11.5 子查询

嵌套查询，将一个查询结果当做另一个查询的条件或结果集。子查询最接近思考方式，最自然的查询。
分类：单行子查询，多行子查询

-- 子查询
-- 单行子查询
-- 查询和scott在同一部门的所有员工信息
-- 1：scott所在部门编号
select deptno from emp where ename='SCOTT';
-- 2：找到员工和scott部门编号一致的
select * from emp where deptno=(select deptno from emp where ename='SCOTT');


-- 多行子查询
select * from emp ORDER BY DEPTNO desc;
-- 20号部门每个员工的薪水
select sal from emp where deptno=20;
-- 查询薪水和20号部门员工相等的，但是不在20号部门员工的信息
select * from emp where sal in (select sal from emp where deptno=20) and deptno!=20;
select * from emp where sal in (select sal from emp where deptno=20) and deptno!=20;
Summer@123
关注
  • 6
    点赞
  • 踩
  • 29
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
Mysql的增删改查以及常用语法总结
MC6700的博客
06-21 5865
Mysql的增删改查基础，以及常用查询、函数等
数据库基础(增删改查)
qq_62712350的博客
08-13 1712
查询语文成绩在70到80之间的同学。
【 MySQL的增删改查（基础 详解+案例）】
最新发布
m0_64670207的博客
04-24 410
注意：1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个（未定义的）顺序。2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面。插入两条记录，value_list 数量必须和指定列数量及顺序一致。指定列的顺序不需要按定义表的顺序来，列从表中选取需要查询的。对某列重复出现的数据进行删除，即去重。
43.MySQL数据库基本操作——增删改查
Pink_Home的博客
11-13 3044
（1）DDL数据定义语言，用来操作数据库，表，列等；常用语句：create,alter,drop（2）DML数据操作语言，用来操作数据库中表里的数据；常用语句：insert,update,delete（3）DCL数据控制语言，用来操作访问权限和安全级别；常用语句：grant,deny（4）DQL数据查询语言，用来查询数据；常用语句：select。
4. MySQL 的增删查改（重点 9000字详解）
qq_58969626的博客
07-14 1563
MySQL 的增删查改、WorkBench 如何关闭安全模式以及MySQL中部分常用函数。
MySQL 表中数据的增删改查操作
心皿月的博客
08-28 1万+
这篇文章回顾对数据表中的数据进行增、删、改、查的操作。在表中插入数据insert、删除表中数据delete、修改表中数据update以及查询表中数据。查询表中数据包括：1、去重查询distinct2、别名查询3、计算列4、选择查询，即条件查询where子句（关键字：and、or、between and、in关键字、like关键字的通配符匹配查询、空值查询）5、聚合函数6、行数限定查询limit8、对查询结果排序order by asc|desc......
MySQL进阶-增删查改(全网最详细sql教学)
niceffking的博客
03-05 1176
数据库约束约束类型概览 not all unique default primary key foreign key check 表的关系(设计) 一对一 一对多 多对多 进阶增删改查 新增： 删除 修改 查询 聚合查询, group by, 联合查询, 笛卡尔积, join on, 内外连接, 多表连接, 自连接, 子查询, 合并查询,
Android连接MySQL数据库并进行增删改查操作示例讲解
08-18
Android 连接 MySQL 数据库并进行增删改查操作示例讲解是 Android 开发中的一种常见操作，通过 Android 应用程序连接 MySQL 数据库，实现数据的增删改查操作。 知识点 1：Android 连接 MySQL 数据库 * 使用 Java ...
Node.js操作mysql数据库增删改查
10-22
本文将详细讲解如何使用Node.js的`mysql`模块来实现数据库的增删改查（CRUD）操作。 首先，要开始操作MySQL数据库，你需要先安装`mysql`模块。这可以通过Node.js的包管理器npm（Node Package Manager）来完成。在...
MySQL数据库增删改查操作小结
10-23
MySQL数据库增删改查操作小结 MySQL数据库是目前最流行的关系型数据库管理系统之一，学习MySQL数据库技术是JavaEE技术的重要组件。下面是MySQL数据库的基本操作小结，包括增删改查等操作。 增操作 Insert into ...
自写的利用PDO对mysql数据库增删改查操作类
10-18
主要给大家介绍了关于自写的利用PDO对mysql数据库的增删改查操作类的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧。
MySQL数据库：数据基本的增删改查
m0_63020222的博客
01-09 998
MySQL数据库：数据基本的增删改查操作
MySQL的常用增删查改语句以及执行顺序
weixin_48234352的博客
12-24 326
MySQL的常用增删查改语句以及执行顺序
MySQL数据的增删改查
weixin_45601120的博客
02-15 205
DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句。
【MySQL】增删查改基础
学了就忘是正常现象
06-20 1505
什么是excel高级版啊！【战术后仰
mysql常用增删改查sql语句
热门推荐
daitu_的博客
11-29 1万+
文章目录1.增2.删3.改4.查 已插入学生信息为例,表名为student， 1.增 sql语句： insert into student(id,name,sex,age) values(1,'zhangsan','男',18); 也可以写成 insert into student values(1,'zhangsan','男',18); 但是这种写法必须要输入全部数据，如果使用第一种就可以分别插入数据： insert into student(name,sex,age) values('zhangs
Mysql——表操作、插入数据、查询数据、删除和更新数据[笔记整理]
m0_59998867的博客
10-07 2815
MySQL创建表的操作，建表时需要注意的完整性约束等内容，以及数据插入、数据查询、 数据更新、数据删除等操作
mysql数据库增删改查
qeeezz11224的博客
01-09 937
环境 搭建一个mysql源码的数据库 主机192.168.80.100 主机名db https://blog.csdn.net/qeeezz11224/article/details/85093094 //地址搭建数据库过程 第一步 进入数据库操作 mysql -uroot -pabc123 //进入数据库 show databases; //查看所有数据库 ...
【MySQL】数据库中表的增删查改操作详解
moteandsunlight的博客
11-15 6818
sql、curd、表的增删改查、mysql、数据库
mysql数据库增删改查操作命令
09-01
MySQL数据库的增删改查操作命令如下： 1. 插入数据： ```sql INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); ``` 2. 查询数据： ```sql SELECT column1, column2, ... FROM table_name WHERE condition; ``` 3. 更新数据： ```sql UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; ``` 4. 删除数据： ```sql DELETE FROM table_name WHERE condition; ``` 其中，`table_name` 是要操作的表名，`column1, column2, ...` 是要操作的列名，`value1, value2, ...` 是要插入或更新的值，`condition` 是查询或删除的条件。 需要注意的是，以上命令只是基本的语法示例，具体的使用方法还需要根据实际情况进行调整。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

Summer@123

不积跬步无以至千里，感谢支持！

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

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

打赏作者

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

抵扣说明：

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

余额充值