mySQL学习记录（局域网连接/基础查询/条件查询/常见函数/窗口函数/表连接/子查询/插修删建表）

小猪比有个秘密

已于 2023-05-08 20:41:15 修改

阅读量1.6k

点赞数 1

分类专栏：笔记文章标签： mysql 数据库

于 2021-12-01 09:44:13 首次发布

本文链接：https://blog.csdn.net/weixin_45621531/article/details/113820150

版权

笔记专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一些基础概念
DB 数据库存储数据的容器
DBMS 数据库管理系统又称数据库软件、产品如mysql/oracle/db2/sqlserver
SQL 结构化查询语言是所有的DBMS都使用的一种DML（数据库操作语言）具体看下面链接的P46（mysql远程授权访问 LINUX等概念）

B站该视频P45讲解了数据库的结构和相关概念

局域网连接mysql

命令台输入

 -h 192.168.1.191 -u 用户名 -p

IP地址是被访问主机的Ip 用ipconfig查询
被访问主机作为Host 需要提前创建新用户、同时放权限给申请访问的主机，注意服务器权限和数据库权限都要，最后注意防火墙问题，具体可自行csdn

当然如果是自己本人登录，用root账户直接登陆即可，PS平板可以使用APP：TablePlus

命令台登录SQL

翻页键可以快速寻找已输入命令
不区分大小写
每条命令;结尾，可以分行空格并不影响
单行注释：#注释
多行注释：/注释/
光标消失切换一下中英文输入法

1注意在用MySQL前必须先启动服务（相当于给仓库的门通电）
方法一：右键我的电脑——管理——双击服务——找到mysql80启动
方法二：WIN+R管理员权限打开cmd

net start mysql80  启动服务 (具体的MySQL名字还是要看方法一名字)
net stop mysql80   停止服务

2随后用密码登录
WIN+R管理员权限打开cmd——键入

mysql -h localhost -P3306 -u root -p

记住这一步和下面‘库的连接’效果是一样的，下面只不过是通过可视化软件SQLYOG的可视化界面进行操作，背地里运行的代码就是上面的命令。
(【h主机名 P端口要大写】 u用户名 p密码) 【代表可省略下文不提示】
即也可以mysql -uroot -p
mysql的默认端口是3306，可以编辑用户目录下的 .my.cnf 文件进行修改；
我的登录密码zhuming277

3退出命令行键入

mysql> exit

若出现问题可能是环境变量没有设置
右击“我的电脑”–>属性–>高级系统设置–>环境变量–>系统变量–>PATH–>新增–>上面的“可执行路径”
定位到你的 MYSQL安装目录之后再找到bin目录，如： C:\Program Files\MySQL\MySQL Server 5.5\bin（请注意一定要是server，并且在该地址最后加上\）

基本命令

注意所有命令都要用；结尾！！！不再提示

mysql> SHOW DATABASES;  #展示当前所拥有的所有数据库
mysql> CREATE DATABASE sqllearning;  #创建新的数据库
mysql> USE sqllearning;  #打开指定的数据库
mysql> SHOW TABLES;     #展示该数据库内的表格
Empty set (0.02 sec)
mysql> SHOW TABLES FROM mysql;  #展示其他库内的表格（并未进入该库，仍然处于sqllearning内）
mysql> SELECT DATABASE();       #查询现在处于何库
+-------------+
| DATABASE()  |
+-------------+
| sqllearning |
+-------------+
1 row in set (0.00 sec)
mysql> select version();  #查询当前MYSQL版本
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)
mysql> exit;          #退出MYSQL系统

库的连接

打开sqlyog 进行连接在这里插入图片描述

用sqlyog链接库报错2058，登录mysql后输入以下代码可解决

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';  #密码是之前设置的ROOT密码
Query OK, 0 rows affected (0.09 sec)

打开SQL文件

对于保存好的SQL文件不要直接双击打开
打开sqlyog后选择文件再进行打开 方便继续学习

SQL导入数据（命令台）

若直接用sqlyog直接导入SQL脚本即可（会自动创建一个库？）

若用命令台则需要先创建数据库：（P47 数据库定义语言讲）

 CREATE DATABASE girls;
 use girls;
 复制https://blog.csdn.net/GongmissYan/article/details/102937816
 内的对应代码
 #注意此处的girls是一个库，内部有多个表

正式学习

语法学习

1、基础查询

#光标选中个别字段可单独执行(F9) 格式化(F12)
#进阶1：基础查询 

select 查询的东西 from 表格;
注意：
可查询字段（所谓字段就是表格的第一行变量名称/列头）、常量、表达式、函数
查询出来的表格是一个虚拟的表格

#查询单个字段
select last_name from employees;
#查询多个字段
select last_name,salary from employees;
#查询全部字段
SELECT * FROM employees;

查询常量值/表达式/函数
SELECT 100;
SELECT `LEX`; #着重号`可以区分出字段或常量值表达式
SELECT version();

起别名（可嵌套

（as可省略）
SELECT last_name AS 姓 ,first_name AS 名 FROM employees;
SELECT last_name 姓 ,first_name 名 FROM employees;       #as可省略
SELECT last_name AS "out put" #别名加双引号加以区分防止报错 单引号也可
可以嵌套运行
SELECT last_name,salary as sal FROM employees;

distinct去重

查询所有的部门编号
SELECT DISTINCT department_id FROM employees;
注意去重不能同时对多个字段去重，可能导致表格的不规则

concat拼接字段和代数运算的区别

拼接字段正确做法：
select concat(last_name,first_name) as 姓名 from employees;

错误做法：
select last_name+first_name as 姓名
mysql中+只能做运算符 
select 100+90;    操作两个数值型√
select `100`+90;  操作有一个是字符型，会尝试转化√
select `lex`+90;  无法转化时会代入0
select null+90;   只要有null结果必为null 

**代数运算正确做法
SELECT employee_id,last_name,
salary * 12 "ANNUAL SALARY"  #此处空格代替AS 
FROM employees;

desc显示表的结构

***************显示departments表的结构 并查询全部信息***************
DESC departments; #显示该表的字段字符类型等信息
SELECT * FROM departments;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210216230708530.png)
***

null处理（包括COALESCE查找NULL值？）

要点：拼接字段时null值的处理

***#显示出表employees的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT

select 
     concat(`first_name`,`last_name`,`email`,`commission_pct`) as out_put 
FROM employees;
#不报错，但会显示NULL，因为commission_pct奖金率 有NULL 

***************（因此要先将NULL值进行处理）********************
 基本用法  ifnull(要查询的列，满足null返回的值) as "一个新列"
select 
      ifnull(`commission_pct`,0) as 奖金率 #用IFNULL函数将NULL值代为0
,`commission_pct`                          #此处提取原数列只是为了对比
FROM employees;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210216232226820.png)

随后继续执行上面的命令即可，可嵌套!!!!!!!!!!!!!
select 
  concat(`first_name`,`last_name`,`email`,ifnull(`commission_pct`,0)) as out_put 
FROM employees;

2、条件查询

where筛选(in between and ＞ != 通配符转义符)

语法顺序
*select 字段 from 表格 where 条件
（但其实程序执行顺序为 from → where → select）

#where条件分类
*一·按条件表达式
   条件运算符：> < =  != >= （!=  是 不等于）注意不要用中文标号

#案例1:查询工资大于12000的员工信息
select * from employees where salary>12000;
#案例2:查询部门编号不等于90的员工名和部门编号
SELECT 
      `last_name`,`department_id` 
FROM   
       employees 
WHERE 	
      `department_id`!=90;


*二·逻辑表达式
   逻辑运算符：and  or  not 
               &&  ||   !
               且  或   非
#案例1：查询工资在10000到20000的员工名和工资
SELECT 
      `last_name`,`salary` 
FROM   
       employees 
WHERE 	
      `salary`>=10000 and `salary`<=20000;


*三·模糊查询 
    LIKE 
    BETWEEN AND 
    IN 
    IS NULL
    
通配符 ： % 任意位置任意字符 可以代表空字符
           _ 一个位置任意字符 必须有一个字符
           [] 你指定的字符集
           ^ 在上面的字符集内使用 代表否定  如 [^JM] 非J且非M
转义符 \ ： 意思是\后面的东西是字符，而不是任何特殊通配符等。如案例3

　 ①模糊查询like用法

#案例1：查询员工名 有a 的员工信息
SELECT * FROM employees 
WHERE  `last_name` LIKE '%a%';    

#案例2：查询员工名第三个字符是n，第五个字符是l的员工信息                        # _
SELECT * FROM employees 
WHERE  `last_name` LIKE '__n_l%';  

#案例3：查询员工名第二个字符为_的员工信息 
SELECT * FROM employees 
WHERE 	 `last_name` LIKE '_\_%';

*也有NOT LIKE ' '的用法

经典的面试题:
SELECT * FROM 表格
SELECT * FROM 表格 where XX like '%%' and XX like '%%'
上面两个不一样，因为 XX里可能有NULL值

　 ②between and用法

选取介于两个值（包含两个边界值）之间的数据
也可以not between and

#案例1：查询员工编号在100到120的员工信息
SELECT * FROM  employees 
WHERE  `employee_id` BETWEEN 100 AND 120;

#案例2：查询工资不在8000到17000的员工
SELECT * FROM  employees 
WHERE  `salary` NOT BETWEEN 100 AND 120;
或者
SELECT * FROM  employees 
WHERE  NOT (`salary` BETWEEN 100 AND 120)

　 ③精确查询in 和not in用法（不可以通配符）

in就是把OR OR OR简化

以前的写法
SELECT last_name,job_id FROM employees WHERE job_id='A'OR job_id='B'OR job_id='C';


1、in会筛选出所有括号内的数值所对应的行
     WHERE age IN (3,6,9)只筛选出年龄字段为3、6、9的行
2、not in筛选出所有非括号内的数值所对应的行
     WHERE age NOT IN (1,2)筛选出年龄字段不为1和2的行
*注意IN相当于= 因此不能在IN括号内加入通配符（不是LIKE）
**注意（）内若非数字型字符 记得加单引号

#在where后有多个筛选条件时，需要用and相连
如：where age IN (3,6,9) AND age NOT IN (1,2)才能同时筛选出年龄字段为3、6、9并且不为1和2的行

　 ④is null / is not null用法（null不是字符）

#案例1：查询没有奖金的员工信息
SELECT * FROM  employees 
WHERE  `commission_pct` = NULL; 是错误的 因为NULL不算真正意义上的字符

两种做法
1·SELECT * FROM  employees 
WHERE  `commission_pct` IS NULL;      *IS NULL才对
2·SELECT * FROM  employees 
WHERE  `commission_pct` <=> NULL;      *安全等于 <=>也对
安全等于无敌 既可以判断NULL也可判断数值 但用的很少可读性低

#反之查询有奖金的应为 WHERE  `commission_pct` is not NULL; 
常见错误:只有IS NOT NULL 
WHERE job_id IS not "IT";
应该改为
WHERE job_id <>"IT";
WHERE job_id !="IT";

以上内容都在P38有综合复习

3、order by排序查询

order by XX desc/asc；
注意该子句一般放在所有查询的最后只有limit在他后面

SELECT * FROM employees ORDER BY salary DESC;
asc 升序 从低到高（默认）
desc降序 从高到低

1 基本练习：
查询部门编号≥90的员工信息 按入职时间先后进行排序
SELECT * FROM employees 
WHERE `department_id`>=90 
ORDER BY `hiredate` ASC;

2 函数排序：
查询姓和工资 按姓长度排序（按函数排序）
SELECT LENGTH (`last_name`) AS 字节长度,last_name,salary
FROM employees 
ORDER BY 字节长度 DESC;

3 order by 内嵌套
#也可以不去命名一个新字段 
SELECT last_name,salary
FROM employees 
ORDER BY LENGTH (`last_name`) DESC;
#【我自己的思考：如果是姓名长度呢
SELECT LENGTH (CONCAT(last_name,first_name)) AS 长度,CONCAT(last_name,first_name),salary
FROM employees 
ORDER BY 长度 DESC;   #跑出来是正确的语法耶】

4 两次排序：
先按工资升序 再按员工编号降序
SELECT * FROM employees 
ORDER BY salary ASC,`employee_id` DESC;

练习题：查询邮箱中含e的员工信息 并按邮箱长度降序 部门编号升序
SELECT *,LENGTH (`email`) AS 邮箱长度 FROM employees
WHERE `email` LIKE '%e%'
ORDER BY 邮箱长度 DESC,`department_id` ASC;

常见错误：总之大多数错误只要考虑‘相同值、NULL’的问题

将员工姓按首字母排序 
SELECT `last_name` FROM employees 
ORDER BY SUBSTR(`last_name`,1,1);  
          vs
SELECT `last_name` FROM employees 
ORDER BY last_name;     #和上面相比在首字母相同情况下 后续字母也会升序排列

从中我们也可以看出字母是有默认顺序的因此在下面的MAX函数中
MAX(last_name)；是正确的

常常和limit 连用

limit x;  #返回X个数据

limit x,y;  #表示的是从x+1开始取y个数据

limit X offset y; #表示从第y+1开始取x个数据

4、 SQL常见函数

一、单行函数

输入单个字段，返回一个值

　1、字符函数

①length 算长度

字节长度（只有此处算字节长度，其余都是算字符长度）

1若（）内是表中的字段则必须From
SELECT LENGTH (`email`) AS 邮箱长度 FROM employees
2若（）内不是表中的字段则不用
SELECT LENGTH ('啊ssss');    #直接输出7  因为utf8下一个中文占三个字节
#注意是 是3个字节 不是字符 ！ 即一个汉字代表1个字符 3个字节长度

②concat 合并

合并中间可以选择用'XX'连接
SELECT CONCAT(`job_id`,'__',`first_name`) FROM employees;

注意concat(内部多嵌套)如下示例

③upper lower 大小写

upper 将字符变为大写
lower 将字符变为小写

1 将姓变大写，名变小写然后拼接
SELECT CONCAT (UPPER(last_name),LOWER(first_name))AS 姓名 
FROM employees;

③substr 索引字符串

从原来的字符串中截取特定长度的字符串（从1开始数）

SELECT SUBSTR('字段',起始字符的位置,截取的字符长度)；
#注意第三个空是字符的长度而不是字节的长度，一个汉字是1个字符3个字节长度
SELECT SUBSTR('李莫愁爱上了陆展元',7) AS 截取字节；  #返回陆展元
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS 截取字节；#返回李莫愁

套娃练习：
姓的首字符大写 其他小写然后用_拼接并显示
SELECT CONCAT (UPPER(SUBSTR(`last_name`,1,1)),'_',LOWER (SUBSTR(`last_name`,2))) 
FROM employees

④instr 查找字符位置

返回特定字符串第一次出现的相对位置（在第几个字符出现），没找到就是0

SELECT INSTR ('杨不悔爱上了殷六侠','殷六侠');        #返回7
SELECT INSTR ('杨殷六侠不悔爱上了殷六侠','殷六侠');  #返回2

⑥trim 移除两侧字符

注意trim后面跟的() 不能有空格,不知道为啥有空格就报错了

SELECT TRIM('   小猪比    ');  #默认移除空格
SELECT TRIM('s' FROM 'sssss小sss猪比ssssss'); #中间的不移除 返回小sss猪比

⑦lpad rpad 左右填充

中间的代表最终的字符串字符数

SELECT LPAD ('小猪比',10,'A');  #返回AAAAAAA小猪比（保证总字符串10字符）
SELECT LPAD ('小猪比',2,'A');   #返回小猪

⑧replace 替换

SELECT REPLACE ('张无忌爱上了周芷若','周芷若','赵敏'); 
# 返回张无忌爱上了赵敏

（补充left和coalsesce函数）

left(s,n) 返回字符串s的左边n个字符
coalesce(列/字段名字,’ a’) 将某一列内的NULL值替换成a

　2数学函数

注意数字函数用的极少，看弹幕说在生产数据库中用甚至会被骂，所以不学也问题不大，只是为了系统完整。

①round 四舍五入

按照指定的小数位数进行四舍五入，也可以对日期操作

SELECT ROUND (1.8617,3);  
#小数点后保留3位 输出1.862
SELECT ROUND (-1.8617,3);  
#先取绝对值 小数点后保留3位 输出-1.862

②truncate 截断

SELECT TRUNCATE(1.123456,3);
#截取小数点后3位 返回1.123

③mod 取余数

SELECT MOD (11,3);
#11÷3余2

④ceil floor 向上下取整

SELECT CEIL (1.00001);
#返回≥参数的最小整数 2
SELECT FLOOR (1.00001);
#返回≤参数的最小整数 1

　3日期函数

处理日期的函数

①now curdate curtime查询系统时间

cur 就是current

SELECT NOW(); #2021-04-07 13:17:04
SELECT CURDATE();#2021-04-07
SELECT CURTIME();#13:18:44

也有查询年份 月份等操作
1查询员工入职年份 #日期格式要正确1992-1-1
SELECT YEAR(`hiredate`) FROM employees; #1992

② **日期格式的转换

此函数要常用的多！！！务必记住
STR_TO_DATE
DATE_FORMAT
在这里插入图片描述

1 SELECT STR_TO_DATE 将非标准的日期格式转换为标准日期格式
 #即被转换的可以是顺序错乱的
语法：
select str_to_date('12/08/2017','%m/%d/%Y')
2017-12-08
select str_to_date('20170422154706','%Y%m%d%H%i%s')
2017-04-22 15:47:06

工作场景中的日期格式有可能是乱的，就需要你进行调整
SELECT * FROM employees 
WHERE `hiredate` = STR_TO_DATE ('4-3/1992','%c-%d/%Y');
#返回 1992-04-03 的员工信息

2 SELECT DATE_FORMAT 将已经是标准格式的转换为你想要的日期格式
#要求被转换不能倒序 但是你可以把他转换成倒序（当然一般不这么做）
SELECT DATE_FORMAT('2017-04-22 15:47:06','%m-%Y-%d')
04-2017-22

工作场景
查询有奖金的员工的入职日期 并将入职日期返回成XX年XX月XX日 （原表格中是XX-XX-XX）
SELECT DATE_FORMAT(`hiredate`,'%Y年%m月%d日') FROM employees
WHERE `commission_pct`IS NOT NULL;

③ **datediff日期差额

SELECT DATEDIFF ('1998-5-1','1998-5-3'); #返回-2 因为是前-后

　4其他函数

SELECT VERSION(); #当前版本号8.0.23
SELECT DATABASE();#当前数据库myemployees
SELECT USER();    #当前用户root@localhost

　5流程控制函数

①if 函数

类似excel中的

语法：if(设定条件，满足返回，不满足返回)
SELECT `last_name`,IF(`commission_pct`IS NULL ,'没奖金淦','有奖金哈哈')AS 内心OS
FROM employees;

②case 函数（多个IF

简单来说IF只能对一个字段判断一次条件
case可以对一个字段判断多次条件
同时注意CASE END后返回的所有值构成一个新的字段列因此可以命名
整个case 是select后 from 前的字段

语法1： 用于= 
CASE 要判断的字段
WHEN 常量1 THEN 要返回的值1或语句1
WHEN 常量2 THEN 要返回的值2或语句2
…………
else 要显示的值n
end

具体案例：查询员工的工资 要求
部门=30 的 返回原工资的1.1倍
部门=40 的 返回原工资的1.2倍
部门=50 的 返回原工资的1.3倍   其余部门仍然原工资
SELECT salary ,`department_id`,  #提取salary 为了对比
(CASE `department_id`
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary  
END )AS 调整后的工资   # 整个case-end后整个部分看做返回一个新变量所以可以AS
FROM employees;

语法2： 用于< >
CASE 
WHEN 字段满足条件1 THEN 要返回的值1或语句1
WHEN 字段满足条件2 THEN 要返回的值1或语句1
…………
else 要显示的值n
end

案例分析
查询员工工资等级
若工资>20000 返回A级别
若工资>15000 返回B级别
若工资>10000 返回C级别   否则显示D级别
SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资等级
FROM employees;

二、分组/聚合函数sum max 【count 】等简单统计函数

统计使用的，输入多个字段，返回一个值

SELECT SUM(salary) FROM employees ;  # 忽略null 即NULL=0
SELECT AVG(salary) FROM employees ;  #平均值 忽略null 
SELECT MIN(salary) FROM employees ;  #忽略null 
SELECT MAX(salary) FROM employees ;  #支持数字 英文字母 日期
SELECT COUNT(salary) FROM employees ;#注意只计算非NULL数量

可以和其他函数嵌套
SELECT SUM(DISTINCT salary) FROM employees ;
SELECT ROUND (AVG(salary),2) FROM employees ;  #平均值保留两位小数

非常重要!!!!!:  一般来说工作中用的多 统计XX数据个数（行数）
SELECT COUNT(*) FROM employees ;#只要一个数据不是全部为NULL就会被计数
SELECT COUNT(1) FROM employees ;#效果相当于在旁边写一列1 统计1的个数 返回值和上面一样
一般来说只要让你去计数 无脑count(*)就好
#理解他还有一个重要的例子 见SQL99内查询

常见错误：查询分组函数时对一起查询的字段有限制
要求是group by（见后面）

SELECT AVG(salary),`employee_id`FROM employees ; 
语法没问题，但前者是一个值，后者是一列 不能同时查询

5、分组/聚合查询 Group by

where语句要在group by 前面
group by相当于创建了一个新表（把原来的表格按要求字段分块了），然后select从这个表中取数

简单分组查询

1简单分组查询 查询每个工种的最高工资
SELECT MAX(salary) FROM employees
GROUP BY `job_id`;

2条件分组查询 查询每个部门中邮箱包含a字符的员工的平均工资
SELECT ROUND(AVG(salary),2)FROM employees
WHERE `email` LIKE '%a%'
GROUP BY `department_id`;

having 分组后筛选

即分组后再进行筛选
（where是直接对原来的母表进行筛选
现在要求对一个通过分组建立的子表格再进行筛选用having）

简而言之：group by 后面的筛选一定用having 而不是where！！
在这里插入图片描述
大招：分组函数(max min count)作为条件一定是放在having内的

1 分组后的筛选  
查询员工个数大于2的部门  
（分解成①查询每个部门的员工数 ②在子表中员工数＞2的部门）
①
SELECT COUNT(`employee_id`)AS 员工数量, `department_id` FROM employees  #这里最好用count(*)防止员工编号有null
GROUP BY `department_id`;  #此操作建立了一个新的子表格
②
SECOND `department_id` FROM employees
WHERE 员工数量>2; 

把上面两个结合的方法是Having
SELECT COUNT(`employee_id`)AS 员工数量 ,`department_id` FROM employees  
GROUP BY `department_id`
HAVING 员工数量>2;

**********显然题目中的条件 ‘员工个数大于2’ 是无法从母表中直接筛选的*****
********************************************************************

2进阶思维！！
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
分解 查工种编号和最高工资
     筛选条件1 要求有奖金 
     筛选条件2 在1的前提下 最高工资>12000  #这就是having子表格筛选

即 查询每个工种有内奖金员工的工种编号和最高工资形成子表格 #第一次筛选
   在对子表格进行筛选 要求最高工资要>12000                #第二次筛选

SELECT `job_id`,MAX(salary)AS '每个工种有奖金员工内的最高工资' FROM employees
WHERE `commission_pct`IS NOT NULL
GROUP BY `job_id`
HAVING 每个工种有奖金员工内的最高工资>12000;

**********题目中的条件 ‘要求有奖金’ 是可以从母表中直接筛选的**********
********************************************************************
说出下面语句的含义
SELECT `manager_id`,MIN(`salary`)AS 最低工资 FROM employees
WHERE `manager_id`>102
GROUP BY manager_id
HAVING 最低工资>5000;

子表：按领导分组，筛选出领导编号大于102的几组，挑出这几组中的最低工资和领导编号
再筛选：对子表中的数据在筛出最低工资大于5000的
所以该语句查询的是 编号大于102的领导，其手下员工最低工资大于5000的人的最低工资和领导编号。

大招：分组函数(max min count)作为条件一定是放在having内的

按多字段分组

多字段分组顺序先后不影响结果

SELECT AVG(salary),`department_id`,`job_id` FROM employees
GROUP BY `department_id`,`job_id`; #顺序不一致 不影响结果

6、*窗口函数 over

group by是去重合并，而窗口函数是分组（不去重）进行分组排序

 OVER (PARTITION BY yr ORDER BY votes DESC) 
以年份作为分区依据，
根据每一年内每一个候选人的votes进行从大到小的排序

具体又分两个

rank 在每组内分组排名

SELECT *,
rank() over(PARTITION by sex ORDER BY salary desc) 
from salary
#若想改更显示顺序可以在此行进行order by

显示如下（以性别为依据分组，在每组内部以工资排序，
同时在每组内部生成一个序号字段）
在这里插入图片描述

1、排序 dense_rank 、row_number区别

rank函数：如果有并列名次的行，会占用下一名次的位置。
比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
dense_rank函数：如果有并列名次的行，不占用下一名次的位置。(方便理解 dense稠密的，说明序号之间是连续的且很稠密每个序号有好几个)
比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
row_number函数：也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

2、聚合 sum. avg, count, max, min等每组内部的累计计数

SELECT *,
sum(salary) over(PARTITION by sex ORDER BY salary  ) 
from salary

显示如下：以性别为依据分组，在每组内部以工资排序，
同时在每组生成一个字段，计算的是每组内部的累计数字
在这里插入图片描述

3、topN

7、连接查询/多表查询

把不同的两个表连接成1个表
引子：

笛卡尔乘积现象：表1有m行 表2有n行，则命令有m*n行
SELECT NAME ,boyName FROM boys,beauty;  #错误连接
解决方法：用 where 加上有效的连接条件
SELECT NAME ,boyName FROM boys,beauty
WHERE `beauty`.`boyfriend_id`=`boys`.`id`; # A.XX 代表 A表的XX字段

一、sql 92标准（用得少可以跳过）

作用是可以了解，从而能读懂别人的代码，万一有老古董呢

1、两表等值连接

SELECT NAME ,boyName FROM boys,beauty
WHERE `beauty`.`boyfriend_id`=`boys`.`id`; 

注意点1：两个表中有相同的字段  要通过给表起别名避免歧义
SELECT last_name,A. `job_id`,`job_title`  #A B表中都有job_id 因此要明确提取哪一个
FROM `jobs`AS A,`employees`AS B
WHERE A.`job_id`=B.`job_id`;

同时要回顾最开始的学习内容 真正的 执行逻辑是 from → select 
#因此若取别名，就不能再用原来名字，相当于只认得一个表A 不认识表job
SELECT last_name,`jobs`.`job_id`,`job_title` 
FROM `jobs`AS A,`employees`AS B

（多表查询后的筛选用and）

区别于分组后的查询having

查询有奖金的 员工名和部门名
SELECT `last_name`,`department_name`,commission_pct
FROM `employees`AS A,`departments` AS B
WHERE A.`department_id`=B.`department_id`
AND A.`commission_pct`IS NOT NULL;   #区别于分组后的查询having

（多表查询后的排序）

查询 每个工种的名字和对应的员工个数，并且按照员工个数降序
SELECT `job_title`,COUNT(*) AS 员工个数
FROM `jobs`AS A,`employees`AS B
WHERE A.`job_id`=B.`job_id`
GROUP BY `job_title`
ORDER BY 员工个数 DESC;

我的心得：
在多表查询中，连接两个表格要在脑海中有一个场景（两张n行的表格，因为有一列共同的列，可以横向拼接成一张表格这就是FROM WHERE干的事。）
然后连接的事情就结束了，我们就对这一张大表进行下面的group by、order by 和select

2、n表等值连接

n表连接至少需要n-1个等值关系
where 后用 and 承接

查询首字母为s的城市名及其拥有的部门名和员工名
SELECT `city`,`last_name`,`department_name`
FROM `departments`AS A,`employees` AS B,`locations` AS C
WHERE A.`department_id`=B.`department_id`
AND A.`location_id`=C.`location_id`       
AND `city`LIKE 's%';

3、非等值连接

一般是通过 between and 和其他不等关系

SELECT salary ,`grade_level`
FROM `job_grades`,`employees`
WHERE `salary`BETWEEN `lowest_sal` AND `highest_sal`
AND `grade_level`='A';

4、自连接

和等值连接类似
但自连接前提是该表格内有两列字段代表的含义重复
比如一张员工表中 A员工的上司是B，同时B也在员工内，即员工ID和上司ID是有重复的，是可以等值的。
（想象画面将这张表复制成两份，一份是员工表一份是领导表，将相同含义的列等值起来，然后拼接）

自连接的代码关键是把一张表格命名成两张

查询所有员工的名字和他们对应的上级名字
SELECT A.`employee_id` AS 员工ID ,A.`last_name` AS 员工名 ,
       B.`employee_id` AS 领导ID ,B.`last_name` AS 领导名
FROM `employees`AS A,`employees` AS B  #A看成员工表 B看成领导表
WHERE A.`manager_id`=B.`employee_id`;

二、sql 99标准（用的多）join

相比之前的92标准 99标准提高了代码的可读性所以用的多
[inner] join 默认是内连接
left [outer] join 有Left 就是外连接

语法
SELECT 查询列表
FROM  表1 别名              
【连接类型】JOIN  表2 别名  #内连=inner 左外=left[outer]
ON    连接的条件   
【连接类型】JOIN  表3 别名  #三表连接
ON    连接的条件
【WHERE 筛选条件】    #相比92 连接类型 筛选和连接条件分离 一目了然
【GROUP BY】 
【HAVING】   
【ORDER BY】

1、内连接（等值、非等值、自连接）

所谓内连接指的是取两个表的交集记录

一、等值连接
查询名字中包含e的员工名及其部门名
SELECT `last_name`,`department_name`
FROM `employees`AS A          
INNER JOIN `departments`AS B           #连接的两个表格
ON A.`department_id`=B.`department_id` #连接条件
WHERE `last_name` LIKE '%e%';          #筛选条件

理解COUNT(*)的例子
SELECT COUNT(*) ,`department_name`
FROM `employees` AS A 
INNER JOIN `departments`AS B              
ON A.`department_id`=B.`department_id` #1、连接两个表格
GROUP BY `department_name`  #2、对新表格以部门名进行分组，此时应该是有好多个重复的部门1，但是每个部门1旁边列的数据不同
HAVING COUNT(*)>3           #3、数出每个部门重复的次数，挑出重复次数>3的部门
ORDER BY COUNT(*) DESC;          #4、并以次数进行降序
因此该语句可以是  ’找出所有部门中有超过3条数据（员工、领导、邮箱）的部门‘ 
即无论问超过3个什么结果都是一样的，所以直接无脑count(*)

其余的非等值和自连接 逻辑和上面一样 只是语法不同 不再赘述

2、外连接（重要sql92没有）

左外连接左边的是主表 left outer join
右外连接右边的是主表 right outer join
(一般来说要查的信息所在的表是主表)
在这里插入图片描述

查询哪个部门只有2个员工
SELECT A.`department_name`,COUNT(*)AS 员工数量
FROM `departments` AS A
LEFT OUTER JOIN `employees` AS B        #左外连接
ON A.`department_id`=B.`department_id`
GROUP BY `department_name`             #到这步是每行不同部门
HAVING 员工数量 =2;

**(一般来说要查的信息所在的表 是主表)**

不同连接的效果表达：
在这里插入图片描述

8、子查询（突击）

select出现在where等其他语句后面都可以叫子查询
子查询先运行
主查询用到了子查询的结果作为条件

同时
子查询的结果只有一行一列（一个单元格）时，称为标量子查询
子查询的结果只有多行，称为多行子查询
当主查询的筛选条件是=<>等时后面跟着的只能是标量子查询
当主查询的筛选条件是in any all等时后面跟着的可以是多行子查询

一、where / having 子查询

1、标量子查询

1、where后的标量子查询
#查询谁的工资比Abel高？
SELECT * FROM employees
WHERE salary>(
	SELECT salary 
	FROM employees 
	WHERE last_name='Abel'   #为了看出是子查询一般会缩进
);   

两次where标量子查询可以AND
#查询job_id 和141员工相同 工资比143多的员工的姓名 
SELECT `last_name`,salary,`job_id` FROM employees
WHERE job_id=(
	SELECT job_id FROM employees 
	WHERE `employee_id`=141
)AND salary>(
	SELECT salary FROM employees
	WHERE `employee_id`=143
);

2having 标量子查询
#查询最低工资大于50号部门最低工资的部门ID和最低工资
SELECT `department_id`,MIN(salary)
FROM employees
GROUP BY `department_id`
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE `department_id`=50
);

2、列子查询

1where列子查询
#返回地区ID是1400或者1700的部门中的所有员工姓名
SELECT `last_name`FROM employees
WHERE `department_id` IN (           #in 和 =any 效果一样的 类似的 NOT IN 和 <>all 效果一样   
	SELECT DISTINCT `department_id`    #列子查询因为是一列 因此建议去重
	FROM`departments`
	WHERE `location_id` IN (1400,1700)  #子查询的结果是一列 上面也要用IN
);

#返回其工种中比job_id是IT_PROG的工种中任一工资低的员工的信息
SELECT * FROM employees
WHERE salary< ANY(
	SELECT DISTINCT salary   #去重
	FROM employees
WHERE job_id ='IT_PROG'
)         #到这一步求的是所有员工里工资比 ’IT工种的最高工资‘这个数字小的人因此可能包含IT工种的人
AND `job_id`<>'IT_PROG';   #去掉IT部门的人

PS：比任一小:任一就是随便取一个，我比取的这个小  只要比最大的小即可
SELECT * FROM employees
WHERE salary< (            # <any (1,2,3) 和  <(  max（1,2，3,） ) 是一样的
	SELECT MAX(salary )
	FROM employees
	WHERE job_id ='IT_PROG'
)       
AND `job_id`<>'IT_PROG'; 
结果和上面是一样的

二、select 子查询

注意点：！select后面跟子查询一定要起别名否则会报错

SELECT子查询在SELECT子句中使用查询的结果(一般会和dual空表一起使用)
#职位是SALESMAN的员工占总员工的比例
SELECT 
	(SELECT COUNT(*) FROM EMP 
	WHERE JOB = 'SALESMAN')
FROM DUAL;

三、from 子查询

#查询每个部门的平均工资的工资等级
SELECT A.*,G.grade_level
FROM(
	SELECT AVG(salary) AS 平均工资 ,`department_id` #养成给字段命名的习惯吧 
	FROM `employees`
	GROUP BY `department_id`     #返回的是一个12*2的表格 即各部门平均工资
) AS A                                #一定给新表起一个表名
INNER JOIN `job_grades` AS G
ON A.平均工资 BETWEEN `lowest_sal` AND`highest_sal`;  #这里如果上面没有命名直接写A.AVG()会报错不知道为啥

本质上是一个不等值内连接 只不过FROM后面是一个创建出来的新表格

四、exist 相关子查询

exist()意思是后面括号里有没有具体的值
有返回1
无返回0 内容是 P93
和其他的子查询不同（其他是先执行子查询主查询用到了子查询的结果表格）
这边的子查询是先进行主查询，然后子查询作为一个筛选条件

查找未分配具体部门的员工的所有信息
查询有员工的部门名字
传统做法：思路是对部门表里的27个ID筛选，要求要出现在员工表里的ID中
SELECT `department_name` FROM `departments`
WHERE `department_id` IN (SELECT DISTINCT `department_id`FROM employees );

思路：选出部门ID 要求部门表的部门ID=员工表的部门ID 
******这里其实就 不  用  连  接  两个表****
SELECT `department_name` FROM `departments`
WHERE EXISTS (
	SELECT `employee_id`FROM`employees` 
	WHERE `departments`.`department_id`=`employees`.`department_id`);

查询没有女友的男神信息
SELECT * FROM `boys`
WHERE NOT EXISTS(
	SELECT *FROM `beauty`
	WHERE `beauty`.`boyfriend_id`=`boys`.`id`);

分页查询limit

联合查询

union 联合多个查询结果就是连接多个SELECT
注意点：
1、两个select后面要查询的列数要一样
2、当两个表中的数据有重复时，union显示出来的结果会去重
（这时候要用 union all 才不会去重）

#查询部门编号大于90或者邮箱包含a的信息
SELECT * FROM employees WHERE `email`LIKE'%a%' OR `department_id`>90;
和下面的效果一毛一样
SELECT * FROM employees WHERE `email`LIKE'%a%'
UNION
SELECT * FROM employees WHERE `department_id`>90;

他的好处是 当两个select要从多个不同的table中取数据时也可以直接用UNION
相当于把两个不同表的查询结果合并了！这样就不需要联合查询！
而且有时候后两个表之间没有连接的信息，因此只能用UNION去取不同的表中的数据

********************************************************

DML数据操作语言（插修删）

另外数据类型中
INT是数字型
varchar是字符型

一、insert 插入

基本语法1 支持同时插入多行 支持子查询
INSERT INTO 表名(列名) 
VALUES(值1,值2……)， #添加第一行
VALUES(值1,值2……)； #添加第二行
#例子
INSERT INTO beauty (`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) 
VALUES(13,'唐艺昕','','','',NULL); 
#若果表名后不添加（列名） 默认全部
#自己一一对应 注意数据格式
#记住varchar格式的 多用引号 不然就报错了

基本语法2 不支持同时插入多行
INSERT INTO 表名
SET列名=值, 列名=值 , ……

常见错误：没有用分号隔开，只用逗号

Create table If Not Exists Logs (id int, num int);
Truncate table Logs;  
insert into Logs (id, num) values ('1', '1');
insert into Logs (id, num) values ('2', '1');
insert into Logs (id, num) values ('3', '1');
insert into Logs (id, num) values ('4', '2');
insert into Logs (id, num) values ('5', '1');
insert into Logs (id, num) values ('6', '2');
insert into Logs (id, num) values ('7', '2');

如果要用逗号应该是
Create table If Not Exists Logss (id int, num int);
Truncate table Logss;
insert into Logss (id, num) 
values ('1', '1'),('2', '1') …………;

二、update set 修改

基本语法1 修改单一表格
UPDATE 表名
SET 列=新值,列=新值,……
WHERE 筛选条件
#执行顺序是 先找到表 然后筛选出具体行数据，然后SET改写

基本语法2 修改多个表格
UPDATE 表1 AS 别名
[INNER…]JOIN 表2 AS 别名
ON 接连条件
SET 列=新值,列=新值,……
WHERE 筛选条件

结合CASE WHEN批量修改一个表中的多行数据

UPDATE weather
     SET Temperature = CASE id
         WHEN 1 THEN 111
         WHEN 2 THEN 222
         WHEN 3 THEN 333
     END
WHERE id IN (1,2,3) 
#请一定记得加上最后一行，否则其他ID＞3的 Temperature 会变成NULL

二、delete删除

truncate暂时没学 P109内容

基本语法 单表删除
DELETE FROM 表名  #delete后面就是什么都没有不能加字段
WHERE 筛选条件 #删除一定是整个行都删掉 不会只删一个单元格

基本语法 多表删除
DELETE FROM 表1的别名,表二的别名 #3、在选出的行数据中，选择要删哪个表中的字段 这边就写哪个别名 记住只要写别名
FROM 表1 AS 别名 
JOIN 表2 AS 别名 ON 条件   #1、这里建立一个新表
WHERE 筛选;      #2、在新表中筛选出要删除的具体的行

DDL数据库定义语言创修删库、表格

创建表格语法

→查询创建表及其数据类型

create table 表名称(       #create 别拼错了
	字段1 类型(长度) 约束,
	字段2 类型(长度) 约束,
	字段3 类型(长度) 约束
	);

创建表的时候，后面用小括号，后面分号。
编写字段，字段与字段之间使用逗号，最后一个字段不能使用逗号。
如果声明字符串数据的类型，长度是必须指定的。
如果不指定数据的长度，有默认值的。int类型的默认长度是11。

在这里插入图片描述

暂时不学了 P112
属于数仓的技能
数据库操作（截取的是他人笔记）

-- 查看当前数据库
    SELECT DATABASE();
-- 显示当前时间、用户名、数据库版本
    SELECT now(), user(), version();
-- 创建库
    CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
    数据库选项：
        CHARACTER SET charset_name
        COLLATE collation_name
-- 查看已有库
    SHOW DATABASES[ LIKE 'PATTERN']
-- 查看当前库信息
    SHOW CREATE DATABASE 数据库名
-- 修改库的选项信息
    ALTER DATABASE 库名 选项信息
-- 删除库
    DROP DATABASE[ IF EXISTS] 数据库名
        同时删除该数据库相关的目录及其目录内容

表的操作

-- 创建表
    CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]
        每个字段必须有数据类型
        最后一个字段后不能有逗号
        TEMPORARY 临时表，会话结束时表自动消失
        对于字段的定义：
            字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- 表选项
    -- 字符集
        CHARSET = charset_name
        如果表没有设定，则使用数据库字符集
    -- 存储引擎
        ENGINE = engine_name
        表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同
        常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
        不同的引擎在保存表的结构和数据时采用不同的方式
        MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
        InnoDB表文件含义：.frm表定义，表空间数据和日志文件
        SHOW ENGINES -- 显示存储引擎的状态信息
        SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息
    -- 自增起始数
        AUTO_INCREMENT = 行数
    -- 数据文件目录
        DATA DIRECTORY = '目录'
    -- 索引文件目录
        INDEX DIRECTORY = '目录'
    -- 表注释
        COMMENT = 'string'
    -- 分区选项
        PARTITION BY ... (详细见手册)
-- 查看所有表
    SHOW TABLES[ LIKE 'pattern']
    SHOW TABLES FROM 表名
-- 修改表
    -- 修改表本身的选项
        ALTER TABLE 表名 表的选项
        eg: ALTER TABLE 表名 ENGINE=MYISAM;
    -- 对表进行重命名
        RENAME TABLE 原表名 TO 新表名
        RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）
        -- RENAME可以交换两个表名
    -- 修改表的字段机构（13.1.2. ALTER TABLE语法）
        ALTER TABLE 表名 操作名
        -- 操作名
            ADD[ COLUMN] 字段定义       -- 增加字段
                AFTER 字段名          -- 表示增加在该字段名后面
                FIRST               -- 表示增加在第一个
            ADD PRIMARY KEY(字段名)   -- 创建主键
            ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
            ADD INDEX [索引名] (字段名) -- 创建普通索引
            DROP[ COLUMN] 字段名      -- 删除字段
            MODIFY[ COLUMN] 字段名 字段属性     -- 支持对字段属性进行修改，不能修改字段名(所有原来有属性也需写上)
            CHANGE[ COLUMN] 原字段名 新字段名 字段属性      -- 支持对字段名修改
            DROP PRIMARY KEY    -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
            DROP INDEX 索引名 -- 删除索引
            DROP FOREIGN KEY 外键    -- 删除外键
-- 删除表
    DROP TABLE[ IF EXISTS] 表名 ...
-- 清空表数据
    TRUNCATE [TABLE] 表名
-- 复制表结构
    CREATE TABLE 表名 LIKE 要复制的表名
-- 复制表结构和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
-- 检查表是否有错误
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- 增
    INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
        -- 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。
        -- 可同时插入多条数据记录！
        REPLACE 与 INSERT 完全一样，可互换。
    INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]
-- 查
    SELECT 字段列表 FROM 表名[ 其他子句]
        -- 可来自多个表的多个字段
        -- 其他子句可以不使用
        -- 字段列表可以用*代替，表示所有字段
-- 删
    DELETE FROM 表名[ 删除条件子句]
        没有条件子句，则会删除全部
-- 改
    UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]