# MySQL **Repository Path**: hrbu2024/my-sql ## Basic Information - **Project Name**: MySQL - **Description**: No description available - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2026-10-10 - **Last Updated**: 2026-10-11 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # MySQL 关系型数据库: UTF8 ## 数据库的操作 创建 ```sql CREATE DATABASE `his` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ; ``` 删除 ```sql drop database his; ``` ## 表的操作 ### 创建 ```sql CREATE TABLE `user`( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', `username` VARCHAR(50) NOT NULL COMMENT '用户名', `password` VARCHAR(50) NOT NULL COMMENT '密码', `expire` DATETIME COMMENT '过期时间', `last_login` DATETIME COMMENT '最后一次登录时间', `create_by` VARCHAR(50) COMMENT '创建用户', `create_time` DATETIME DEFAULT NOW() COMMENT '创建时间', `update_by` VARCHAR(50) COMMENT '更新用户', `update_time` DATETIME COMMENT '更新时间', `is_delete` VARCHAR(5) DEFAULT '0' COMMENT '是否删除，0 正常，1删除', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8mb4; ``` MYSQL的数据类型 - 数值( int bigint ) - 字符串( varchar text 。。。) - 日期（date datetime timestamp ....） ### 删除表 ```sql drop table user; ``` ## 数据的操作 DML ### 增加数据 ```sql insert into tablesName (字段1，字段2,.... ) values (值1 ，值2，。。。。) ; ``` ```sql insert into user (`username`, `password`, `expire`, `last_login`, `create_by` ) values ( 'admin' ,'123456', '2026-10-10 09:06:10' ,null,'admin' ) ``` ![image-20261010090730656](assets/image-20261010090730656.png) ### 批量插入 ```sql insert into user (`username`, `password`, `expire`, `last_login`, `create_by` ) values ( 'root' ,'123456', '2026-10-10 09:06:10' ,null,'admin' ), ( 'ry' ,'123456', '2026-10-10 09:06:10' ,null,'admin' ), ( 'jashan' ,'123456', '2026-10-10 09:06:10' ,null,'admin' ); ``` ![image-20261010090900593](assets/image-20261010090900593.png) ### 更新数据 ```sql UPDATE 表格 SET 字段名称=字段的值 WHERE id = 1 AND username ='admin' ``` ```sql /*** 更新数据 */ UPDATE USER SET last_login= NOW(), update_by = 'admin', update_time = '2026-10-10 09:39:01' WHERE id = 1 AND username ='admin' ; ``` ### 删除数据 慎重操作 产生日志 redo ```sql delete from 表 where delete from user where id = 3 or id = 4 ; ``` 截断数据库 不产生日志 ```sql truncate 表 /*** 完全删除 不保留日志 慎重操作**/ TRUNCATE USER ; ``` # DQL 查询数据 select ```sql DROP DATABASE IF EXISTS TEST; CREATE DATABASE IF NOT EXISTS TEST; USE TEST; DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `empno` int NOT NULL COMMENT '员工编号', `ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '员工姓名', `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '岗位、职位', `mgr` int DEFAULT NULL COMMENT '上级编号', `hiredate` date DEFAULT NULL COMMENT '入职日志', `sal` decimal(7,2) DEFAULT NULL COMMENT '薪水', `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金', `deptno` int DEFAULT NULL COMMENT '归属部门编号', PRIMARY KEY (`empno`), KEY `fk_deptno` (`deptno`), CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*Data for the table `emp` */ insert into `emp`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) values (7369,'SMITH','CLERK',7902,'1980-12-17','800.00',NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20','1600.00','300.00',30),(7521,'WARD','SALESMAN',7698,'1981-02-22','1250.00','500.00',30),(7566,'JONES','MANAGER',7839,'1981-04-02','2975.00',NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28','1250.00','1400.00',30),(7698,'BLAKE','MANAGER',7839,'1981-05-01','2850.00',NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09','2450.00',NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19','3000.00',NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08','1500.00','0.00',30),(7876,'ADAMS','CLERK',7788,'1987-05-23','1100.00',NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03','950.00',NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03','3000.00',NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23','1300.00',NULL,10); /*Table structure for table `salgrade` */ DROP TABLE IF EXISTS `salgrade`; CREATE TABLE `salgrade` ( `grade` int DEFAULT NULL COMMENT '级别', `losal` decimal(7,2) DEFAULT NULL COMMENT '级别的最低工资', `hisal` decimal(7,2) DEFAULT NULL COMMENT '级别的最高工作' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*Data for the table `salgrade` */ insert into `salgrade`(`grade`,`losal`,`hisal`) values (1,'700.00','1200.00'),(2,'1201.00','1400.00'),(3,'1401.00','2000.00'),(4,'2001.00','3000.00'),(5,'3001.00','9999.00'); ``` ## DQL-简单查询 [DQL-简单查询](https://www.yuque.com/donghua-nhdx0/abx4x1/cw9rqnt5rnzhwpx0#RRIb8) ```sql /**简单查询*/ SELECT * FROM emp ; SELECT ename,sal FROM emp; SELECT sal ,ename FROM emp; SELECT * FROM dept; SELECT dname FROM dept; /**运算 **/ SELECT ename ,sal, sal*12 FROM emp; -- 列的别名 SELECT ename ,sal, sal*12 '年 薪' , sal*12 sal_year , sal*12 AS sal_month FROM emp; -- null 不代表任何值 和任何值运算 都是 null SELECT ename,comm, comm*12 FROM emp ; -- null 的处理 SELECT ename,comm, comm+100 l,IFNULL(comm,0) +100 FROM emp ; -- 消除重复行 -- 查询所有管理岗的员工编号 SELECT mgr FROM emp ; SELECT DISTINCT mgr FROM emp ; ``` ## 条件查询 [DQL-条件查询](https://www.yuque.com/donghua-nhdx0/abx4x1/gq498lyh21wpp7a7) ```sql /******************** 条件查询 *****/ -- 查询部门编号为20的员工编号、姓名、所在部门编号。 SELECT empno ,ename,deptno FROM emp WHERE deptno = 20 ; /** 2、字符型列做为查询条件： 例：查询职位为CLERK的员工编号、姓名、职位。 */ SELECT empno,ename,job FROM emp WHERE job = 'CLERK'; /***** 3、日期型列做为查询条件： 例：查询入职日期在1985年1月1日之后的员工编号、姓名、入职日期 */ -- TAb 自动补全 SELECT empno,ename,hiredate FROM emp WHERE hiredate > '1985-01-01' /***** 2.3. 特殊比较运算符 *********/ -- 查询工资在1000到1500之间的员工编号，姓名，工资。 SELECT empno ,ename,sal FROM emp WHERE sal >=1000 AND sal <=5000 ; -- between and SELECT empno ,ename,sal FROM emp WHERE sal BETWEEN 1000 AND 5000 ; /***** 2.3.2 IN运算符 使用IN运算符可以查询出某列的值是否和给定集合内的任意一个值相等。 语法：WHERE 列名 IN （值1, 值2, ....值n) 例：使用IN运算符，查询出部门为10号或者20号的员工编号，姓名，部门编号。 */ SELECT * FROM emp WHERE deptno = 10 OR deptno = 20 ; -- IN 理论上数量有限制 oralce SELECT * FROM emp WHERE deptno IN (10,20 )； /******************** 2.3.4 LIKE运算符 使用LIKE运算符可以查询出某列的值是否满足关键字匹配，也叫模糊查询。如查询员工名字中含有字母S的员工，就可以使用LIKE运算符来解决。 先介绍两个通配符： % : 百分号，代表匹配零个或任意个字符。 _ : 下划线，代表匹配1个任意字符。 例：查询员工姓名中以S开头的员工。 */ -- S 开头的 SELECT * FROM emp WHERE ename LIKE 'S%'; -- N 结尾的 ALLEN SELECT * FROM emp WHERE ename LIKE '%N'; -- 匹配名字是5个字母 SELECT * FROM emp WHERE ename LIKE '____N'; /**** 2.3.4.1 区分大小写的比较（扩展） 如果想要在比较的时候区分大小写，可以在字符串前面添加关键字 BINARY 例：查询员工姓名中第二个字母是L的员工(值区分大小写) */ SELECT * FROM emp WHERE ename = 'KING' ; -- 修改成小写的ing UPDATE emp SET ename = 'King' WHERE ename = 'KING' ; -- 使用大写的N去like 也能查到小写的， SELECT * FROM emp WHERE ename LIKE '%N%'; SELECT * FROM emp WHERE BINARY ename LIKE '%N%'; ``` ## 多条件查询 | **运算符** | **含义** | | ---------: | ------------------------------------------------------------ | | AND | 逻辑与，用来连接两个条件表达式。如果每个条件表达式的结果都为TRUE，整个表达式的结果才为TRUE。 | | OR | 逻辑或，用来连接两个条件表达式。只要有1个条件表达式的结果为TRUE，整个表达式的结果就为TRUE。 | | NOT | 逻辑非，用来对条件表达式取反。TRUE取反为FALSE，FALSE取反为TRUE。 | **NOT运算符还可以和BETWEEN…AND、IN、LIKE、IS NULL一起使用，表示的含义分别为：** NOT BETWEEN .. AND .. :不在某个区间 NOT IN (集合）：不在某个集合内 NOT LIKE ：不像..... IS NOT NULL: 不是空 ```sql /********************************** ****************************************************************** ****************************************************************** ****************************************************************** 3. 多条件查询 ****************************************************************** ****************************************************************** ****************************************************************** */ -- 本题查询出的员工，需要同时满足两个条件， -- 条件1：工资>1100，条件2：职位='CLERK'，因此本题可以用逻辑与来解决， SELECT * FROM emp WHERE sal > 1100 AND job = 'CLERK' ; -- 查询工资大于1100或职位为‘CLERK’的员工编号、姓名、职位、工资。 SELECT * FROM emp WHERE sal > 1100 OR job = 'CLERK'; -- 查询工资不在3000到5000之间的员工姓名; SELECT * FROM emp WHERE sal NOT BETWEEN 3000 AND 5000 -- 2.查询部门该编号不在10号或20号的员工姓名 SELECT * FROM emp WHERE deptno NOT IN (10,20) -- 3.查询姓名不以字母J开头的员工姓名 SELECT * FROM emp WHERE ename NOT LIKE 'J%'; -- 4.查询有津贴的员工姓名 SELECT * FROM emp WHERE comm IS NOT NULL ``` ### 练习 #### 2.2 课堂练习(4分钟） 1. 查询职位为SALESMAN的员工编号、姓名、职位。 2. 查询1985年12月31日之前入职的员工编号、姓名、入职日期。 3. 查询部门编号不为10部门的员工编号、姓名、部门编号。 ##### 2.3.3 课堂练习(2分钟） 1. 查询入职日期在82年至85年的员工姓名，入职日期。 2. 查询工资在3000到5000的员工姓名，工资。 ##### 2.3.5 课堂练习(3分钟) 1. 查询员工姓名以W开头的员工姓名。 2. 查询员工姓名倒数第2个字符为T的员工姓名。 3. 查询津贴为空的员工姓名，津贴。 #### 3.3 课堂练习(4分钟) 1. 查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。 2. 查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。 3. 查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。 4. 查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。 ```sql /***** ### 练习 #### 2.2 课堂练习(4分钟） 1. 查询职位为SALESMAN的员工编号、姓名、职位。 2. 查询1985年12月31日之前入职的员工编号、姓名、入职日期。 3. 查询部门编号不为10部门的员工编号、姓名、部门编号。 ##### 2.3.3 课堂练习(2分钟） 1. 查询入职日期在82年至85年的员工姓名，入职日期。 2. 查询工资在3000到5000的员工姓名，工资。 ##### 2.3.5 课堂练习(3分钟) 1. 查询员工姓名以W开头的员工姓名。 2. 查询员工姓名倒数第2个字符为T的员工姓名。 3. 查询津贴为空的员工姓名，津贴。 #### 3.3 课堂练习(4分钟) 1. 查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。 2. 查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。 3. 查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。 4. 查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。 */ -- 1. 查询职位为SALESMAN的员工编号、姓名、职位。 SELECT empno,ename,job FROM emp WHERE job ='SALESMAN' ; -- 2. 查询1985年12月31日之前入职的员工编号、姓名、入职日期。 SELECT empno,ename,hiredate FROM emp WHERE hiredate > '1985-12-31'; -- 3. 查询部门编号不为10部门的员工编号、姓名、部门编号。 SELECT empno,ename,deptno FROM emp WHERE deptno <> 10 ; SELECT empno,ename,deptno FROM emp WHERE deptno !=10 ; -- 1. 查询员工姓名以W开头的员工姓名。 SELECT ename FROM emp WHERE ename LIKE 'W%'; -- 2. 查询员工姓名倒数第2个字符为T的员工姓名。 SELECT ename FROM emp WHERE ename LIKE '%T_'; -- 3. 查询津贴为空的员工姓名，津贴。 SELECT ename, comm FROM emp WHERE comm IS NULL; -- 1. 查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。 SELECT * FROM emp WHERE sal>2000 AND ( job = 'MANAGER' OR job ='SALESMAN' ) SELECT * FROM emp WHERE sal>2000 AND job IN ('MANAGER' , 'SALESMAN' ) -- 2. 查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。 SELECT * FROM emp WHERE (deptno IN (10,20) ) AND ( sal BETWEEN 3000 AND 5000 ) ; SELECT * FROM emp WHERE deptno IN (10,20) AND sal BETWEEN 3000 AND 5000 ; -- 3. 查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。 SELECT * FROM emp WHERE (hiredate BETWEEN '1981-01-01' AND '1981-12-31') AND job NOT LIKE 'SALES%' ; -- 4. 查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。 SELECT * FROM emp WHERE job IN ('SALESMAN','MANAGER') AND deptno IN ('10','20') AND ename LIKE '%A%' ; ``` ## 排序 ### ORDER BY子句 #### 语法 SELECT [ DISTINCT ] * | 列名 | 表达式 [别名] [,...] FROM 表名 [WHERE 条件] [ORDER BY 列名1 | 列别名1 | 列序号1 [ ASC | DESC ], 列名2 | 列别名2 | 列序号2 [ ASC | DESC ]; 说明： **1、ORDER BY子句前面的内容，已经讲过，不再赘述。** **2、可以按照3种方式进行排序：分别是按列名排序、按列别名排序、按列序号排序。** **3、ASC表示按升序排序(默认值), DESC表示按降序排序。** **4、可以同时按照多个列名进行排序** **5、ORDER BY 子句必须写在SELECT语句的最后** 6、数字升序排列小值在前，大值在后。即按照数字大小顺序由小到大排列。 7、日期升序排列相对较早的日期在前，较晚的日期在后。 8、字符升序排列按照字母由小到大的顺序排列，即由A-Z排列。 9、空值在升序排列中排在最前面，在降序排列中排在最后。 ```sql /********************************* 排序 **************/ -- 查询所有员工姓名，职位，部门编号，入职日期，并把结果集按**入职日期升序**排序。 SELECT job, deptno ,hiredate FROM emp ORDER BY hiredate ASC ; -- 查询所有员工姓名，职位，部门编号，入职日期，并把结果集按入职日期降序排序。 SELECT job, deptno ,hiredate FROM emp ORDER BY hiredate DESC ; -- desc emp 用于查询表结构的 -- 查询所有员工编号，姓名，全年工资总和，并把结果集按全年工资总和升序排序，全年工资总和的别名是 annsal。 SELECT empno 员工编号 , ename 姓名 , sal*12 annsal FROM emp ORDER BY annsal ASC ; -- 按列序号排序 SELECT `sal`, `empno`, `ename`, `job`, `mgr`, `hiredate`, `comm`, `deptno` FROM `emp` ORDER BY 6 DESC -- 同时按多列排序 -- 优先按照 部门 升序，相同部门 按照工资降序 SELECT ename,deptno,sal FROM emp ORDER BY deptno ,sal DESC ; ``` ### 练习 ```sql /*** 2.5 课堂练习（3分钟） 1. 查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。 2. 查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。 3. 查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。 3. 课后作业 1. 查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。 2. 查询20号部门中工资大于1500的员工编号，员工姓名和员工工资（查询结果字段名和以上信息一致）并按照工资降序做排序。 **/ -- 1. 查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。 SELECT ename,deptno ,sal FROM emp WHERE deptno IN (20,30) ORDER BY sal; -- 2. 查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。 SELECT ename,deptno ,sal FROM emp WHERE sal BETWEEN 3000 AND 5000 AND deptno <> 10 ; -- 了解字符串和数值之间的转换 SELECT * FROM emp WHERE deptno = '10'; -- 3. 查询入职日期在81年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。 SELECT * FROM emp WHERE hiredate BETWEEN '1981-01-01' AND ' 1983-12-31' AND ( job LIKE 'SALES%' OR job LIKE 'MAN%') ORDER BY hiredate DESC ; -- 1. 查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。 SELECT * FROM emp t WHERE t.`deptno` = 30 ORDER BY sal DESC , ename ASC ; -- 2 查询20号部门中工资大于1500的员工编号， -- 员工姓名和员工工资（查询结果字段名和以上信息一致）并按照工资降序做排序。 SELECT ename,sal FROM emp WHERE deptno = 20 AND sal > 1500 ORDER BY sal DESC ; ``` ## DQL-多表查询 单元概述本单元主要讲解数据库多表查询功能，可以实现从多个表中查询数据，多表查询内容包括内连接，外连接，笛卡尔积，等值连接，非等值连接，SQL99标准连接语法以及联合查询。教学目标学习本单元之后，学员可达到：掌握多表查询的基本语法。掌握内连接和外连接，等值连接和非等值连接的使用。掌握自连接的特殊多表查询的写法。掌握SQL99标准连接语法实现多表查询。了解UNION联合查询的使用。 ![img](assets/1604969648177-9f670eea-86a3-4da8-a6c5-7735579395f2.png) ![image-20261010143311659](assets/image-20261010143311659.png) ```sql /******** 多表联合查询 **************/ -- 查询员工的所有信息以及 部门信息 -- 4 SELECT * FROM dept ; -- 14 SELECT * FROM emp ; -- 笛卡尔积 -- 往往有冗余数据 SELECT * FROM emp ,dept -- 在笛卡尔积的基础上 做过滤 -- 错误代码： 1052 -- Column 'deptno' in where clause is ambiguous -- SELECT * FROM emp ,dept where deptno = deptno ; SELECT * FROM emp ,dept WHERE emp.deptno = dept.deptno ; -- 给表名启别名 SELECT * FROM emp e ,dept d WHERE e.deptno = d.deptno ; -- 非等值连接 -- 查询每个员工的姓名，工资，工资等级 SELECT * FROM emp , SALGRADE WHERE sal BETWEEN losal AND hisal; /**** 多于两个表的连接 ● 如果想从员工表，部门表，工资级别表中同时检索数据，那么就会涉及三张表做连接。这个时候就需要至少两个连接条件。 */ -- 查询每个员工的员工姓名，员工工资，部门名称，工资级别 SELECT * FROM emp ,dept ,SALGRADE WHERE emp.`deptno` = dept.`deptno` AND sal BETWEEN losal AND hisal AND loc ='CHICAGO' ; ``` ## 6.6 课上练习（3分钟） 1. 写一个查询，显示所有员工姓名，部门编号，部门名称。 2. 写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金 3. 写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。 4. 写一个查询，显示RESEARCH部门下的员工工资在1500以上的员工姓名，部门编号，部门名称，员工工资 ## 6.11 自连接 ### 6.11.1 什么是自连接 - 思考：查询每个员工的姓名和直接上级姓名？ - 自身连接，也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样 ## 6.12 ANSI SQL：标准的连接语法 ### 6.12.1 ANSI SQL：1999标准的连接语法 除了上述已经讲过的连接语法外，多表查询还有美国国家标准协会（ANSI）的SQL标准语法：1999标准的连接语法。 ```sql SELECT table1.column, table2.column FROM table1 [JOIN table2 ON(table1.column_name = table2.column_name)] | [LEFT | RIGHT OUTER JOIN table2 ON (table1.column_name = table2.column_name)]; ``` ```sql /****** 自连接 */ -- 查询每个员工的姓名和直接上级姓名？ -- e.* ,m.* SELECT e.ename ,m.ename FROM emp e ,emp m WHERE e.mgr = m.empno; /********** 外连接 查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来 ***********************/ SELECT * FROM emp JOIN dept ON emp.`deptno` = dept.`deptno` -- 左外链接 SELECT * FROM emp LEFT JOIN dept ON emp.`deptno` = dept.`deptno` -- 右外 SELECT emp.* ,dept.* FROM emp RIGHT JOIN dept ON emp.`deptno` = dept.`deptno` ; -- 左外 SELECT emp.* ,dept.* FROM dept LEFT JOIN emp ON emp.`deptno` = dept.`deptno` ; -- 内连接 SELECT * FROM emp INNER JOIN dept ON emp.`deptno` = dept.`deptno` /************** 6.13.1 UNION查询 对于UNION查询，就是把多个查询结果合并起来，形成一个新的查询结果集（并集），ALL表示包含两个结果集中重复数据是否消除，加all不消除重复，否则消除重复 注：目前MySQL暂时不支持全外连接，所以可以使用UNION/UNION ALL进行实现 语法： SELECT 字段列表 FROM 表A UNION [ALL] SELECT 字段列表 FROM 表B */ -- 查询员工 部门是10的 -- 查询员工部门是20的 SELECT * FROM emp WHERE deptno = 10 UNION SELECT * FROM emp WHERE deptno = 20; SELECT 1 UNION SELECT 2 ; SELECT 1 UNION ALL SELECT 1 ; ``` ### 课上练习 1. 查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。 2. 查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。 3. 使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。 4. 使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。 5. 查询工作地点在CHICAGO或者职位是MANAGER的员工编号，员工姓名，员工职位，部门编号，部门工作地点 ## 分组查询 掌握MAX、MIN、AVG、SUM、COUNT，5个分组函数的用法。 以deptno 相同的为一组，计算每组中的 最大的薪水max、最小 min、平均 avg、总的 sum、数量 count ![image-20261011112719640](assets/image-20261011112719640.png) ```sql -- 分组查询 SELECT deptno ,MAX(sal) ,MIN(sal),AVG(sal),SUM(sal),COUNT(sal) , SUM(sal)/COUNT(sal) AVG FROM emp GROUP BY deptno ``` - 查询部门30有多少个员工有津贴 ``` SELECT COUNT(sal) FROM emp GROUP BY deptno ``` ```sql -- 分组查询 SELECT deptno ,MAX(sal) ,MIN(sal),AVG(sal),SUM(sal),COUNT(sal) , SUM(sal)/COUNT(sal) AVG FROM emp GROUP BY deptno ； -- count 和以在查询结果为1组的情况使用 SELECT deptno ,COUNT(sal) FROM emp WHERE deptno = 30 ; -- 等同 SELECT deptno , COUNT(sal) FROM emp WHERE deptno = 30 GROUP BY deptno ; /*** 2.5 分组函数中的DISTINCT DISTINCT会消除重复记录后再使用分组函数 例：查询有员工的部门数量。 */ -- SELECT COUNT(DISTINCT deptno ) FROM emp; -- 需求 : 查询一共多少个员工 -- 分组函数 对 null 会去掉 SELECT COUNT(0) ,COUNT(1) ,COUNT(100) FROM emp ; SELECT COUNT(1), COUNT(empno ),COUNT(ename ),COUNT(comm ) FROM emp ; -- 分组函数的练习 /************** 1. 查询部门20的员工的工资总和及平均工资。 2. 查询员工表中一共有几种岗位类型。 3. 查询每位员工的年收入（年收入=基本工资+佣金） 4. 查询工作在30号部门的员工人数，最高工资及最低工资。 ***********/ -- 1. 查询部门20的员工的工资总和及平均工资。 SELECT SUM(sal) , AVG(sal) FROM emp WHERE deptno = 20 ; -- 2 . 查询员工表中一共有几种岗位类型。 SELECT COUNT( DISTINCT job ) FROM emp ; -- 3. 查询每位员工的年收入（年收入=基本工资+佣金） SELECT ename , sal+IFNULL(comm,0) FROM emp -- 查询工作在30号部门的员工人数，最高工资及最低工资。 SELECT COUNT(1) ,MAX(sal) ,MIN(sal) FROM emp WHERE deptno = 30 ; ``` ### 练习 1. 查询工资在2500元以上的员工的平均工资及部门编号。 2. 查询部门平均工资在2500元以上的部门编号及平均工资。 3. 查询部门平均工资在2500元以上的部门名称及平均工资。 4. 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。 5. 查询部门人数在2人以上的部门名称、最低工资、最高工资。 6. 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。 ## 子查询 28-JDBC-MySQL复习-子查询.mp4 1. 查询入职日期最早的员工姓名，入职日期 2. 查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称 3. DML-数据操作 单元概述 本单元主要讲解使用DML语言操作表中的数据，主要包括数据的插入、修改和删除。 数据操作DML-数据操作 单元概述 本单元主要讲解使用DML语言操作表中的数据，主要包括数据的插入、修改和删除。 教学目标 学习本单元之后，学员可达到： 1、熟练插入数据的语法，插入单行、多行、给所有列赋值、部分列赋值、插入空值、插入日期值。 2、掌握如何修改数据。 3、掌握如何删除数据。 1. DML概述 DML：全称Data Manipulation Language，数据操作语言，用来实现对表中数据的插入、修改、删除。包含3个关键字：INSERT、UPDATE、DELETE。 2. 插入数据 2.1 插入单行数据 插入单行数据的通用语法： INSERT INTO 表名 ( 列名1 ,列名2, ......列名n ) VALUES (值1, 值2, ... 值n); 说明： 1、值与列必须位置对应，个数相同，类型相同 2、值的数据大小必须在列的长度范围内 3、除了数值类型外，其它数据类型的值必须使用单引号 4、如果要插入空值，可以不写列名，或者写NULL 2.1.1 给所有列赋值 包含两种写法： 写法1：所有的列名都写出来，语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) VALUES (值1, 值2, 值3); 例：向部门表中插入一条数据，部门编号为50，部门名称为DEVELOPMENT，部门所在地为NEW YORK 对应SQL语句及执行结果如下： 方法2：省略列列表，语法如下： INSERT INTO 表名 VALUES (值1, 值2, 值3…); 例：向部门表中插入一条数据，部门编号为60，部门名称为HUMAN RESOURCE，部门所在地为BOSTON 对应SQL语句及执行结果如下： 2.1.2 给部分列赋值 要赋值的列名写出来，语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) VALUES (值1, 值2, 值3); 例：向部门表中插入一条数据，部门编号为70，部门名称为MARKET，部门所在地为NULL 对应SQL语句及执行结果如下： 该案例也可以使用如下SQL： 2.1.3 练习（3分钟） 向部门表新增一个部门，部门编号为80，部门名称为HR，工作地点为SY。 向部门表新增一个部门，部门编号为90，部门名称为MARKET。 2.1.4 插入日期值 SYSDATE() ：返回系统当前日期和时间，可直接使用或放在SELECT子句中使用，如： 例：向员工表中插入一条记录，员工编号为7196，姓名为GREEN，职位为SALESMAN，上级编号为7782，入职日期为系统当前日期，工资为2000，津贴为空，所在部门为10. SQL语句及执行结果如下： 例：向员工表中插入一条记录，员工编号为2296，姓名为ABC，职位为SALESMAN，上级编号为7782，入职日期为1977年2月3日，工资为1300，津贴为空，所在部门为10. SQL语句及执行结果如下： 2.1.4 练习（2分钟） 向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为当前系统时间，薪资3000，奖金和部门为空。 2.2 插入多行数据 2.2.1 插入多行数据语法 也可以一次性向表中插入多行数据，语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) VALUES (值1, 值2, 值3), (值1, 值2, 值3), .... (值1, 值2, 值3); 注：值列表之间用逗号隔开 例：如下SQL语句可以一次性向员工表中插入多条数据 2.2.2 通过子查询插入多行数据 通过子查询可以一次性向表中插入多行数据，也叫蠕虫复制。作用就是将查询的结果集复制表中。语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) 查询语句; 说明： 1、不必书写values子句 2、列名列表中列的位置、列的数量、数据类型，必须和子查询中列的位置、数量和类型相匹配 例：使用如下SQL先创建经理表manager，然后再把EMP表中职位为MANAGER的记录复制过去。 第一步：创建manager表的SQL： 第二步：向manager表中插入职位为MANAGER的记录 上述语句执行成功后，可以看出manger表中已经有3条记录。 2.2.3综合练习（3分钟） 使用CREATE TABLE emp_back as SELECT * FROM EMP WHERE 1=0，创建emp_back表,复制表结构即可。 把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。 3. 修改数据 修改数据主要用来按照修改表中某些行的列数据。既可以修改所有行，也可以修改满足条件的行。 3.1 修改所有行 语法： UPDATE 表名 SET 列名1 = 值1，列名2 = 值2，....列名n = 值n; 说明： SET子句用来限定修改哪些列，一次可以修改一个列，也可以同时修改多个列，每个列之间用逗号分割。 例：把所有员工的部门编号修改为20，工资上浮100 3.2 修改部分行 语法： UPDATE 表名 SET 列名1 = 值1，列名2 = 值2，....列名n = 值n WHERE 条件表达式； 说明： WHERE子句用来过滤修改哪些行。 例：把员工编号为7782的部门编号修改为20 3.3 练习（5分钟） 修改部门20的员工信息，把职位更新成"CLERK"。 修改奖金为null的员工，奖金设置为0 。 修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500 4. 删除数据 删除数据主要用来删除表中的行。既可以删除部分行，也可以删除所有行。 4.1 删除所有行 语法： DELETE FROM 表名; 例：删除emp_back表中所有行 4.2 删除部分行 语法： DELETE FROM 表名 WHERE 条件表达式; 例：删除职位是CLERK的员工记录 4.3 练习（4分钟） 删除经理编号为7566的员工记录 删除工作在NEW YORK的员工记录 删除工资大于所在部门平均工资的员工记录 5. 单元小结 本单元主要讲解了3个DML语句，重点讲解： 插入数据的方法：一次插入一行，插入空值、插入日期值、插入多行； 修改表中数据：修改指定行，修改所有列、修改多列； 删除表中数据：删除指定行、删除所有行； 6. 课后作业 使用如下语句，创建学生表student和班级表class SQL 运行代码复制代码 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE student ( --学生表 ​ xh char(4),--学号 ​ xm varchar(10),--姓名 ​ sex char(2),--性别 ​ birthday date,--出生日期 ​ sal double(7,2), --奖学金 ​ studentcid int(2) --学生班级号 ) CREATE TABLE class ( --班级表 ​ classid in(2), --班级编号 ​ cname varchar(20),--班级名称 ​ ccount int(3) --班级人数 ) 2.基于上述学生表和班级表，完成如下问题 ​ （1）添加三个班级信息为： SQL 运行代码复制代码 1 2 3 1，JAVA1班，null 2，JAVA2班，null 3，JAVA3班，null ​ （2）添加学生信息如下：'A001','张三','男','01-5月-05',100,1 ​ （3）添加学生信息如下：'A002','MIKE','男','1905-05-06',10 ​ （4）插入部分学生信息： 'A003','JOHN','女' ​ （5）将A002学生性别修改为'女' ​ （6）将A003学生信息修改如下：性别为男，生日设置为1980-04-01 ​ （7）将学生班级号为空的学生班级修改为Java3班 学习本单元之后，学员可达到： 1、熟练插入数据的语法，插入单行、多行、给所有列赋值、部分列赋值、插入空值、插入日期值。 2、掌握如何修改数据。 3、掌握如何删除数据。 1. DML概述 DML：全称Data Manipulation Language，数据操作语言，用来实现对表中数据的插入、修改、删除。包含3个关键字：INSERT、UPDATE、DELETE。 2. 插入数据 2.1 插入单行数据 插入单行数据的通用语法： INSERT INTO 表名 ( 列名1 ,列名2, ......列名n ) VALUES (值1, 值2, ... 值n); 说明： 1、值与列必须位置对应，个数相同，类型相同 2、值的数据大小必须在列的长度范围内 3、除了数值类型外，其它数据类型的值必须使用单引号 4、如果要插入空值，可以不写列名，或者写NULL 2.1.1 给所有列赋值 包含两种写法： 写法1：所有的列名都写出来，语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) VALUES (值1, 值2, 值3); 例：向部门表中插入一条数据，部门编号为50，部门名称为DEVELOPMENT，部门所在地为NEW YORK 对应SQL语句及执行结果如下： 方法2：省略列列表，语法如下： INSERT INTO 表名 VALUES (值1, 值2, 值3…); 例：向部门表中插入一条数据，部门编号为60，部门名称为HUMAN RESOURCE，部门所在地为BOSTON 对应SQL语句及执行结果如下： 2.1.2 给部分列赋值 要赋值的列名写出来，语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) VALUES (值1, 值2, 值3); 例：向部门表中插入一条数据，部门编号为70，部门名称为MARKET，部门所在地为NULL 对应SQL语句及执行结果如下： 该案例也可以使用如下SQL： 2.1.3 练习（3分钟） 向部门表新增一个部门，部门编号为80，部门名称为HR，工作地点为SY。 向部门表新增一个部门，部门编号为90，部门名称为MARKET。 2.1.4 插入日期值 SYSDATE() ：返回系统当前日期和时间，可直接使用或放在SELECT子句中使用，如： 例：向员工表中插入一条记录，员工编号为7196，姓名为GREEN，职位为SALESMAN，上级编号为7782，入职日期为系统当前日期，工资为2000，津贴为空，所在部门为10. SQL语句及执行结果如下： 例：向员工表中插入一条记录，员工编号为2296，姓名为ABC，职位为SALESMAN，上级编号为7782，入职日期为1977年2月3日，工资为1300，津贴为空，所在部门为10. SQL语句及执行结果如下： 2.1.4 练习（2分钟） 向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为当前系统时间，薪资3000，奖金和部门为空。 2.2 插入多行数据 2.2.1 插入多行数据语法 也可以一次性向表中插入多行数据，语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) VALUES (值1, 值2, 值3), (值1, 值2, 值3), .... (值1, 值2, 值3); 注：值列表之间用逗号隔开 例：如下SQL语句可以一次性向员工表中插入多条数据 2.2.2 通过子查询插入多行数据 通过子查询可以一次性向表中插入多行数据，也叫蠕虫复制。作用就是将查询的结果集复制表中。语法如下： INSERT INTO 表名 (列名1, 列名2, 列名3…) 查询语句; 说明： 1、不必书写values子句 2、列名列表中列的位置、列的数量、数据类型，必须和子查询中列的位置、数量和类型相匹配 例：使用如下SQL先创建经理表manager，然后再把EMP表中职位为MANAGER的记录复制过去。 第一步：创建manager表的SQL： 第二步：向manager表中插入职位为MANAGER的记录 上述语句执行成功后，可以看出manger表中已经有3条记录。 2.2.3综合练习（3分钟） 使用CREATE TABLE emp_back as SELECT * FROM EMP WHERE 1=0，创建emp_back表,复制表结构即可。 把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。 3. 修改数据 修改数据主要用来按照修改表中某些行的列数据。既可以修改所有行，也可以修改满足条件的行。 3.1 修改所有行 语法： UPDATE 表名 SET 列名1 = 值1，列名2 = 值2，....列名n = 值n; 说明： SET子句用来限定修改哪些列，一次可以修改一个列，也可以同时修改多个列，每个列之间用逗号分割。 例：把所有员工的部门编号修改为20，工资上浮100 3.2 修改部分行 语法： UPDATE 表名 SET 列名1 = 值1，列名2 = 值2，....列名n = 值n WHERE 条件表达式； 说明： WHERE子句用来过滤修改哪些行。 例：把员工编号为7782的部门编号修改为20 3.3 练习（5分钟） 修改部门20的员工信息，把职位更新成"CLERK"。 修改奖金为null的员工，奖金设置为0 。 修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500 4. 删除数据 删除数据主要用来删除表中的行。既可以删除部分行，也可以删除所有行。 4.1 删除所有行 语法： DELETE FROM 表名; 例：删除emp_back表中所有行 4.2 删除部分行 语法： DELETE FROM 表名 WHERE 条件表达式; 例：删除职位是CLERK的员工记录 4.3 练习（4分钟） 删除经理编号为7566的员工记录 删除工作在NEW YORK的员工记录 删除工资大于所在部门平均工资的员工记录 5. 单元小结 本单元主要讲解了3个DML语句，重点讲解： 插入数据的方法：一次插入一行，插入空值、插入日期值、插入多行； 修改表中数据：修改指定行，修改所有列、修改多列； 删除表中数据：删除指定行、删除所有行； 6. 课后作业 使用如下语句，创建学生表student和班级表class SQL 运行代码复制代码 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE student ( --学生表 ​ xh char(4),--学号 ​ xm varchar(10),--姓名 ​ sex char(2),--性别 ​ birthday date,--出生日期 ​ sal double(7,2), --奖学金 ​ studentcid int(2) --学生班级号 ) CREATE TABLE class ( --班级表 ​ classid in(2), --班级编号 ​ cname varchar(20),--班级名称 ​ ccount int(3) --班级人数 ) 2.基于上述学生表和班级表，完成如下问题 ​ （1）添加三个班级信息为： SQL 运行代码复制代码 1 2 3 1，JAVA1班，null 2，JAVA2班，null 3，JAVA3班，null ​ （2）添加学生信息如下：'A001','张三','男','01-5月-05',100,1 ​ （3）添加学生信息如下：'A002','MIKE','男','1905-05-06',10 ​ （4）插入部分学生信息： 'A003','JOHN','女' ​ （5）将A002学生性别修改为'女' ​ （6）将A003学生信息修改如下：性别为男，生日设置为1980-04-01 ​ （7）将学生班级号为空的学生班级修改为Java3班 1. 查询部门平均工资高于10号部门平均工资的部门编号和平均工资。