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

一些基础概念
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 FilesMySQLMySQL Server 5.5bin（请注意一定要是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_PUTselect 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 employeesWHERE `employee_id`=143
);2having 标量子查询
#查询最低工资大于50号部门最低工资的部门ID和最低工资
SELECT `department_id`,MIN(salary)
FROM employees
GROUP BY `department_id`
HAVING MIN(salary)>(SELECT MIN(salary)FROM employeesWHERE `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 employeesWHERE 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 weatherSET Temperature = CASE idWHEN 1 THEN 111WHEN 2 THEN 222WHEN 3 THEN 333END
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_nameCOLLATE 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 字段名=新值[, 字段名=新值] [更新条件]

Published by

风君子

独自遨游何稽首 揭天掀地慰生平