本文最后更新于：11 天前

ROIS夏令营

WEEK1

MySQL学习记录

解决mysql任意密码登录的问题：

1
2
3

update user set plugin="mysql_native_password";
flush privileges;
#重启mysql服务

docker拉mysql：

1 2	`docker pull mysql:latest#可将latest换成具体的版本号 docker images`

1	`docker run -d --name=mysql-server -p 3306:3306 -v mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=your_password mysql`

7.9

配置环境参考docker-note中文章

默认在3306端口运行MySQL服务。

数据库管理系统：

用来管理数据库中数据的，对数据增删查改。常见：MySQL、DB2、Oracle等。

执行MySQL命令时注意以分号结尾，不见分号不执行

SQL语句不区分大小写

常用命令：

show databases;#查看有哪些数据库
use <DB>;#使用某个数据库(自带4个数据库，SQLI注入时与其中某些息息相关，存有数据库表名等等信息)
create database <DB>;#创建数据库
exit;#退出
show tables;#查看某个数据库内的表

表(table)：

行(row)

列(字段)(column)

SQL语句：

DQL：数据查询语言(带SELECT)

SELECT <字段名> FROM <表名>#如果查询多个字段字段间用逗号隔开，查询全部用*或者一个一个字段敲进去，星号效率低，实际开发不建议使用
SELECT <字段1>,<字段2>,<字段3>... FROM <表名> WHERE <条件>;#条件中>、<、>=、<=、=不用赘述，between .. and ..介于两者之间(左小右大)、<>或!=不等于、or 或、and 与
#同大多数编程语言一样，and优先级高于or，最好用括号明确逻辑层次
SELECT
	...
FROM
	...
WHERE
	...
GROUP BY
	...
ORDER  BY
	...;#关键字顺序不能错

DML：数据操作语言(对表当中数据增删改)

1
2
3

INSERT#增
DELETE#删
UPDATE#改

DDL：数据定义语言(CREATE、DROP、ALTER，主要操作表结构而不是修改数据，如删掉某列，增加新的一个字段、创建、删除表)

CREATE#新建
ALTER#修改
DROP#删除
#不同于DML对表数据的增删改，DDL是对表结构进行操作

TCL：事务控制语言

1 2	`COMMIT#事务提交 ROLLBACK#事务回滚`

DCL：数据控制语言

1
2
3

#eg:
GRAND#授权
REVOKE#撤销权限

下面用上面学到操作的先制作一个测试用数据库练练手

先看一下有哪些数据库 show databases;，之前复现题目环境时创建了个flag数据库，正好可以试下删除数据库的操作

use flag;进入该数据库，show tables;查看数据库内所有表

浅查一下

查完，删表跑路（bushi

可以看到我们的test数据库内只有一个flag表，下面删除该表drop table flag;

使用drop table <表名>;删表，这时候我们再查数据库中的表名，已经是空的了，那一不做二不休，试一下删数据库drop database flag;

可以看到flag数据库已经被删除了。

这里先开navicat可视化快速创个数据库然后导出，一会尝试从文件导入

导入文件的时候踩了个坑(其实还有个坑，这个坑也是几乎所有编程语言共有的，养成良好习惯从我做起（，路径中憋整中文)

使用命令source <file_url>;导入sql文件(意外发现其实source命令也可以不需要分号，但是养成好习惯，都加)

要先选中一个数据库再导入

如图所示便导入成功：

查看表结构，不看数据，使用describe <表名>;命令(或者desc <表名>也可)

然后感觉自己创的表太草率了，找了个更好的数据库导入了（

下面进行desc查表结构以及使用as关键字对查询字段起别名(注意as只对一个字段生效)(as可省，但是注意别名和真名间不要有逗号)：

起别名不对原表字段名造成影响

SELECT语句永远无法进行修改操作

在所有数据库中，字符串是用单引号括起来的，双引号在Oracle中无法使用但是在MySQL中可以，例别名中有空格别名用单引号括起来

字段可以使用数学表达式：

7.10

MySQL中NULL不能用=衡量，只能用is/is not，因为数据库中的NULL是表示该位置为空，而不是一个值，是一个属性

IN关键字(等价于多个or)，取反用not in()：

like(模糊查询，sql注入会使用到)：

‘%’匹配任意个字符（可理解为正则表达式.*?）；
‘_’一个下划线只匹配一个字符（可以理解为正则表达式的.）；

eg：

查询名字里含有字符’o’的：

1	`select ename from emp where ename like '%o%';`

OUTPUT:

eg：

以T结尾

1	`select ename from emp where ename like '%T';`

以K开始

1	`select ename from emp where ename like 'K%';`

找出第二个字母是A的

1	`select ename from emp where ename like '_A%';`

找出含下划线的

1	`select ename from emp where ename like '%\_%';#使用转义字符'\'`

order by排序：

order by sal默认升序，指定降序order by sal desc(descend)，指定升序order by sal asc(ascend)

多字段排序：

1 2	`#查询员工名字和薪资，要求按照薪资升序，薪资相同按照名字升序 select ename,sal from emp order by sal asc,ename asc;#sal在前起主导，sal相等时才比较ename`

根据字段位置排序(sql注入常用)

1	`select ename,sal from emp order by 2;#2表示第二列`

数据处理函数：

Lower()#转换小写
Upper()#转换大写
******Substr()#取子串(sub(string,start,length))
******Length()#取长度
Trim()#去空格
str_to_date()#将字符串转化成日期
data_format()#格式化日期
format()#设置千分位
round()#四舍五入，用法：round(待变值，保留小数位)
rand()#生成随机数
******concat()#字符串拼接，而不能用加号
ifnull()#将null赋予值，ifnull(数据，被当做哪个值)，如果数据是null,将数据当做哪个值

substr()起始下标从1开始而不是0！

找出员工名字第一个字母是A的员工信息？

两种方法：

select ename from emp where ename like 'A%';
select ename from emp where substr(ename,1,1)='A';

 select ename,sal + comm as salcomm from emp;#数据库中有null参与的数据运算结果都为null
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |    NULL |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  |    NULL |
| MARTIN | 2650.00 |
| BLAKE  |    NULL |
| CLARK  |    NULL |
| SCOTT  |    NULL |
| KING   |    NULL |
| TURNER | 1500.00 |
| ADAMS  |    NULL |
| JAMES  |    NULL |
| FORD   |    NULL |
| MILLER |    NULL |
+--------+---------+

ifnull()的用途：

select ename,sal + ifnull(comm,0) as salcomm from emp;
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  | 2975.00 |
| MARTIN | 2650.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

case...when...then...when...then...else...end：

#eg:当员工工作岗位是MANAGER时工资上调10%，当员工工作岗位是SALESMAN时工资上调15%，其他正常
 select ename,job,sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+

7.11

分组函数：

输入多行，输出一行

count()#计数，count(*)表示统计所有行数，count(具体字段)代表统计该字段下不为null的
sum()#求和
avg()#平均值
max()#最大值
min()#最小值

分组函数自动忽略null，不需要对null进行处理

分组函数在使用时必须分组，没分组默认整张表是一组

分组函数不能直接使用在where语句：

select ename,sal from emp where sal > min(sal);#表面上没问题
ERROR 1111 (HY000): Invalid use of group function
#实际报错，无效使用分组函数
#为什么？分组查询(group by)
#分组函数在使用时必须先分组，但是WHERE语句在执行时排在GROUP BY后面，此时还没分组
#为什么select sum(sal) from emp;不用分组也能用呢？
#因为执行顺序
#from,where,group by,select,order by，select在group by之后执行

分组查询：(SQL注入常用)

#找出每个工作岗位的工资和？
#思路：按工作岗位分组，对工资求和
select job,sum(sal) from emp group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| ANALYST   |  6000.00 |
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+

在一条SELECT语句中如果有GROUP BY，SELECT后面只能跟参加分组的字段和分组函数：

select ename,sal,deptno from emp;#先查一下表，可以看到10部门中最高工资是KING，20部门中最高工资是FORD和SCOTT，30部门中最高工资是BLAKE
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| SMITH  |  800.00 |     20 |
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| JAMES  |  950.00 |     30 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
select deptno,max(sal) from emp group by deptno;#查一下每个部门的最高工资
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
select ename,deptno,max(sal) from emp group by deptno;#select后加上ename
+-------+--------+----------+
| ename | deptno | max(sal) |
+-------+--------+----------+
| CLARK |     10 |  5000.00 |
| SMITH |     20 |  3000.00 |
| ALLEN |     30 |  2850.00 |
+-------+--------+----------+
#结果可以看到ename和后面的工资部门信息并不对应，ename无意义，并且Oracle中会报错
#如果想实现输出预期结果，需要用到表连接

按多个字段联合分组：

#找出每个部门，不同工作岗位的最高薪资
select ename,job,sal,deptno from emp order by deptno;#先查询下按部门分组情况，为便于分析，按部门排序
+--------+-----------+---------+--------+
| ename  | job       | sal     | deptno |
+--------+-----------+---------+--------+
| MILLER | CLERK     | 1300.00 |     10 |
| KING   | PRESIDENT | 5000.00 |     10 |
| CLARK  | MANAGER   | 2450.00 |     10 |


| FORD   | ANALYST   | 3000.00 |     20 |
| ADAMS  | CLERK     | 1100.00 |     20 |
| SCOTT  | ANALYST   | 3000.00 |     20 |
| JONES  | MANAGER   | 2975.00 |     20 |
| SMITH  | CLERK     |  800.00 |     20 |


| BLAKE  | MANAGER   | 2850.00 |     30 |
| MARTIN | SALESMAN  | 1250.00 |     30 |
| TURNER | SALESMAN  | 1500.00 |     30 |
| WARD   | SALESMAN  | 1250.00 |     30 |
| JAMES  | CLERK     |  950.00 |     30 |
| ALLEN  | SALESMAN  | 1600.00 |     30 |
+--------+-----------+---------+--------+

select deptno,job,max(sal) from emp group by deptno,job;#联合分组查询
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+

如果想对分完组之后的数据进一步过滤，不要用WHERE(参考上面的内容)，而应该用HAVING子句

#eg：找出每个部门最高薪资，要求显示最高薪资大于3000的
select deptno,max(sal) from emp group by deptno;#先不考虑要求，查最大薪资
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
select deptno,max(sal) from emp group by deptno having max(sal)>3000;#考虑限制要求
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
#只有5000被留下来了
#having语句要和group by配套使用，不能代替where

但是上述语句效率相对比较较低，实际上可以这样考虑：

先将薪资大于3000的用WHERE过滤了，不大于3000的就不进行分组了

select deptno,max(sal) from emp where sal > 3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+

where和having优先选择where，where没办法的再选择having(比如对每个部门平均薪资进行限制的)

distinct关键字：

把查询结果去除重复记录，注：原表数据不会被修改，只是查询结果去重

select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+
select distinct job from emp;#去重
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

distinct只能出现在所有字段的最前方，如果后面有多个字段，则联合去重：

select job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| SALESMAN  |     30 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| CLERK     |     20 |
| CLERK     |     30 |
| ANALYST   |     20 |
| CLERK     |     10 |
+-----------+--------+
select distinct job,deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+

连接查询：

笛卡尔积现象：

select ename from emp;#单独查emp表的ename字段
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
select dname from dept;#单独查dept表的dname字段
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
select ename,dname from emp,dept;#对两张表进行连接查询，没有任何限制的时候，查询结果条数是两个表条数的乘积
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
| WARD   | OPERATIONS |
| JONES  | ACCOUNTING |
| JONES  | RESEARCH   |
| JONES  | SALES      |
| JONES  | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH   |
| MARTIN | SALES      |
| MARTIN | OPERATIONS |
| BLAKE  | ACCOUNTING |
| BLAKE  | RESEARCH   |
| BLAKE  | SALES      |
| BLAKE  | OPERATIONS |
| CLARK  | ACCOUNTING |
| CLARK  | RESEARCH   |
| CLARK  | SALES      |
| CLARK  | OPERATIONS |
| SCOTT  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| SCOTT  | SALES      |
| SCOTT  | OPERATIONS |
| KING   | ACCOUNTING |
| KING   | RESEARCH   |
| KING   | SALES      |
| KING   | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH   |
| TURNER | SALES      |
| TURNER | OPERATIONS |
| ADAMS  | ACCOUNTING |
| ADAMS  | RESEARCH   |
| ADAMS  | SALES      |
| ADAMS  | OPERATIONS |
| JAMES  | ACCOUNTING |
| JAMES  | RESEARCH   |
| JAMES  | SALES      |
| JAMES  | OPERATIONS |
| FORD   | ACCOUNTING |
| FORD   | RESEARCH   |
| FORD   | SALES      |
| FORD   | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH   |
| MILLER | SALES      |
| MILLER | OPERATIONS |
+--------+------------+

加限制条件：

select emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno order by dname;#只有emp中的deptno和dept中的deptno相等时
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SCOTT  | RESEARCH   |
| FORD   | RESEARCH   |
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JONES  | RESEARCH   |
| ALLEN  | SALES      |
| JAMES  | SALES      |
| MARTIN | SALES      |
| TURNER | SALES      |
| WARD   | SALES      |
| BLAKE  | SALES      |
+--------+------------+
#最终查询结果减少了，但是匹配次数没减少，还是4*14=56条
#'select emp.ename,dept.dname from'部分如果换成'select ename,dname from'效率降低，因为虽然dept表中没有ename字段，但是MySQL还是会去找，emp和dname同理，而且逻辑上不够严谨
select e.ename,d.dname from emp as e,dept as d where e.deptno = d.deptno order by dname;
#给表起别名很重要，提高效率

内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接(左连接)
- 右外连接(右连接)

内连接——等值连接

上述笛卡尔积现象SQL语句是SQL92语法，SQL99语法如下：

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno order by dname;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SCOTT  | RESEARCH   |
| FORD   | RESEARCH   |
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JONES  | RESEARCH   |
| ALLEN  | SALES      |
| JAMES  | SALES      |
| MARTIN | SALES      |
| TURNER | SALES      |
| WARD   | SALES      |
| BLAKE  | SALES      |
+--------+------------+
#实现了外连接与后续where语句的分离，结构更清晰
#SQL99语法：
SELECT
	...
FROM
	A
JOIN #其实join前面省略了个inner，代表内连接，可省，但是保留可读性更好
	B
ON
	A和B的连接条件 #如果是等量关系就称为等值连接
WHERE
	筛选条件

内连接——非等值连接：

#eg：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级
select * from salgrade;#先看下薪资表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
select e.ename,e.sal,s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

内连接——自连接：

#查询员工的上级领导、要求显示员工名和对应的领导名
select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+
select a.ename as '员工',b.ename as '领导' from emp as a join emp as b on a.mgr=b.empno;
+--------+-------+
| 员工   | 领导  |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
#自连接的技巧：一张表看做两张表，员工的领导编号等于领导的员工编号
#KING没有上级

外连接：

查询员工表可以发现员工表中有10、20、30部门：

select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

查询部门表发现除了10/20/30部门还有40部门：

select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

如果希望将除了员工表里有的部门输出外，同时输出员工表中没有的部门，就要用到外连接了：

select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;#right表示将join关键字右边的这张表看做主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表
#同内连接一样，join前面有个可省可不省的outer
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
| NULL   | OPERATIONS |
+--------+------------+
#除了14个员工对应的部门外，还有一个匹配不上的OPERATIONS部门也显示出来了，对应的员工为空

举一反三一下很容易得出左连接的写法：

select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;#记得把join两侧表名换位置
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
| NULL   | OPERATIONS |
+--------+------------+

外连接查询结果条数大于等于内连接

同样是上面员工领导的问题，使用外连接，可将显示所有员工的结果：

select a.ename as '员工',b.ename as '领导' from emp as a left join emp as b on a.mgr = b.empno;
+--------+-------+
| 员工   | 领导  |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |#KING显示
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

总结：内连接取交集，外连接取并集

多张表连接：

语法：

SELECT
	...
FROM
	A
JOIN
	B
ON
	f(A,B)
JOIN
	C
ON
	g(A,C)
JOIN
	D
ON
	h(A,B)
#内连接外连接可以混合

子查询：

select语句的嵌套

where中的子查询：

select ename,sal from emp where sal > min(sal);#这句语句是错误的，shiyong max()前未分组
select ename,sal from emp where sal > (select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

from中的子查询：

注：from后面的子查询可以将子查询的查询结果当做一张临时表

#eg:找出每个岗位的平均工资的薪资等级
#第一步：找出每个岗位的平均工资（按岗位分组）
select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+#现在就将左表看做是一张临时表
#第二步：将以上查询结果当做一张真实存在的表t
select * from salgrade;#查薪资等级
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+#起别名s
#将t表和s表进行表连接，连接条件是t.avg(sal) between s.losal and s.hisal
select t.job,s.grade from (select job,avg(sal) from emp group by job) as t join salgrade as s on t.avg(sal) between s.losal and s.hisal;
#执行以上命令出现报错：
#ERROR 1630 (42000): FUNCTION t.avg does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
#原因是on后面的t.avg(sal)被认为是分组函数，外层select未进行分组
#修改方法：起别名
select t.job,s.grade from (select job,avg(sal) as avg from emp group by job) as t join salgrade as s on t.avg between s.losal and s.hisal;
+-----------+-------+
| job       | grade |
+-----------+-------+
| ANALYST   |     4 |
| CLERK     |     1 |
| MANAGER   |     4 |
| PRESIDENT |     5 |
| SALESMAN  |     2 |
+-----------+-------+

联合查询(UNION)：

合并查询结果集

select ename,job from emp where job = 'MANAGER';
+-------+---------+
| ename | job     |
+-------+---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+-------+---------+
select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

注意：union联合查询的两个结果要求列的数目以及数据类型相同

limit：

WEEK2

<!DOCTYPE html>是H5的声明位于文档的最前面，处于标签之前。他是网页必备的组成部分，避免浏览器的怪异模式。

meta标签用于描述一个HTML网页文档的属性，关键词等，eg：<meta charset='UTF-8'>，是单标签

生成<h1>到<h6>快捷键：h$*6

align属性，调整标题摆放位置

<h1 align='left|right|center'>居左，右，中，默认居左</h1>>

段落通过<p>标签定义<p>这是一个段落</p>

<p>这是一个可以换<br>行的标签</p>

<hr/>标签在html页面中创建水平线，<hr color="" width="" size="" aligh="" />

color：颜色
width：长度
size：水平线高度
align：对齐方式，默认居中

ROIS相关

#ROIS

imaginary_ctf 上一篇

2023FCTF 下一篇

ROIS_Summer