MySql学习

最新推荐文章于 2023-03-15 19:20:49 发布

小吴小吴头脑全无

最新推荐文章于 2023-03-15 19:20:49 发布

阅读量231

点赞数 1

分类专栏：笔记

本文链接：https://blog.csdn.net/BBBjavaexx/article/details/112849025

版权

笔记专栏收录该内容

17 篇文章 0 订阅

订阅专栏

高性能MySql泛读
 尚硅谷MySql基础

# 高性能MySql泛读

MySql逻辑架构

在这里插入图片描述

最上层不是Mysql独有的，大多数网络、客户端的工具都有类似的架构
第二层提供Mysql的核心服务功能，所有跨存储引擎的功能都在这里实现
第三层包含了存储引擎，负责数据的存储和提起

1.1.1 连接管理和安全性

每个客户端连接都会在服务器中有一个线程，这个链接单独的在线程中执行，轮流在某个CPU核心中运行，服务器只会负责缓存线程，所以不需要我们主动销毁他们

1.2 并发控制

本章讨论：服务器层和存储引擎层的并发问题

1.2.1 读写锁

读锁是共享的，互相不阻塞的
写锁是阻塞的，会阻塞其他所有的写锁和读锁

1.2.2 锁粒度

提高共享资源并发性的方法是让锁定对象更有选择性，尽量之锁定需要修改的部分数据，而不是全部
锁策略就是在锁的开销和数据的安全性之间寻求平衡，一般都是在表上加行级锁

表锁：类似邮箱加锁机制，锁定整个表，一个用户在对表写的时候们必须先得到写锁，只有没有写锁的时候骂别人才可以读锁。且写锁的优先级更高，最小的锁开销。
行级锁：最大程度的支持并发处理，只在存储引擎层实现，最大的锁开销。

尚硅谷MySql基础

基础知识
 语法

基础知识篇

在这里插入图片描述

语法篇

在这里插入图片描述

#基础查询

#进阶1： 基础查询
/*
一、语法
select 查询列表 
from 表名;
二、特点
1、查询列表可以是字段、常量、表达式、函数
2、查询结果是一个虚拟表
*/

USE myemployees;

#1. 查询表中单个字段
SELECT 
  last_name 
FROM
  employees ;

# 2. 查询表中多个字段
SELECT 
  last_name,
  salary,
  email 
FROM
  employees ;

#3. 快速导入表名
SELECT 
  `employee_id`,#这个是键盘数字1前面的着重号`
  `first_name`, #可以区分字段和关键字
  `last_name` 
FROM
  employees ;

# 4. 查询表中所有字段
SELECT 
  * 
FROM
  employees ;
  
# 5.查询常量值
 SELECT 100%96;
 SELECT 'john';
 
# 6.查询函数
 SELECT VERSION();
 
# 7.起别名
SELECT last_name AS 名字1 FROM employees;
SELECT last_name 名字2 FROM employees;

# 案例：查询salary，显示结果为output
SELECT salary AS 'out put' FROM employees;

# 8.去重
# 案例：查询员工设计的部门编号
SELECT DISTINCT department_id FROM employees;

# 9.+号的作用
/*
java中的+号可以做运算符或连接符

而MySQL的+作用：做加法运算
select 数值+数值; 直接运算；100+90
select 字符+数值;先试图将字符转换成数值;'12'+90
	如果转换成功，则继续运算；12+90
	否则转换成0，再做运算；
select null+值;结果都为null
*/
# 案例：查询员工姓名连在一起，并显示姓名
# 使用函数 concat
SELECT CONCAT('a','b','c');

SELECT  
	CONCAT(last_name,' ',first_name) AS 姓名 
FROM
	employees ;

条件查询

#进阶2： 条件查询
/*
一、语法
	select 查询列表 
	from 表名
	where 筛选条件;
	先from，再where，最后select

二、分类
	1. 按条件表达式筛选
	条件运算符：> < = != <> >= <=
	2. 按逻辑表达式筛选
	逻辑运算符：&&  ||  !
		    and or not
	3. 模糊查询
		like
		between and 
		in
		is null
	
*/
#1. 按条件表达式筛选
SELECT
  * 
FROM
  employees 
WHERE salary > 12000 ;

SELECT 
  last_name,
  department_id 
FROM
  employees 
WHERE department_id <> 90 ;

#2. 逻辑表达式筛选
SELECT 
  last_name,
  salary,
  commission_pct 
FROM
  employees 
WHERE salary >= 10000 
  AND NOT(salary <= 20000) ;

#3. 模糊查询
/*
like
    特点：一般和通配符一起用
	  %是多个，_是单个
	  当通配符要在与语句里面使用的时候使用 \ 来转义
	  或者使用ESCAPE关键字
	  
between and 
in
is null
*/

	# like
	SELECT 
	  * 
	FROM
	  employees 
	WHERE last_name LIKE '___' ESCAPE 'a' ;

	# between and相当于区间
	SELECT 
	  * 
	FROM
	  employees 
	WHERE employee_id BETWEEN 100 
	  AND 120 ;
	  
	# in想当于连等
	SELECT 
	  last_name,
	  job_id 
	FROM
	  employees 
	WHERE job_id IN ('IT_PROG', 'AD_VP','AD_PRES');

	# is null =和<>不能判断null！
	SELECT 
	  last_name,
	  commission_pct 
	FROM
	  employees 
	WHERE commission_pct IS NOT NULL ;

# 4.安全等于<=>可以当成equals
SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct <=> NULL ;

条件查询

/* 进阶三：排序查询
一、语法
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc}desc】
*/
# 二、特点
/*
1、asc ：升序，如果不写默认升序
   desc：降序

2、排序列表 支持 单个字段、多个字段、函数、表达式、别名

3、order by的位置一般放在查询语句的最后（除limit语句之外）
*/

# 例子
SELECT 
  salary 
FROM
  employees 
ORDER BY salary DESC ;

# 编号大于等于90且入职时间先后排序
SELECT 
  * 
FROM
  employees 
WHERE department_id >= 90 
ORDER BY hiredate ;

# 按表达式排序
SELECT 
  *,
  salary * 12 * (1+ IFNULL(`commission_pct`, 0)) AS 年薪 
FROM
  employees 
ORDER BY salary * 12 * (1+ IFNULL(`commission_pct`, 0)) DESC ;

# 按别名排序
SELECT 
  *,
  salary * 12 * (1+ IFNULL(`commission_pct`, 0)) AS 年薪 
FROM
  employees 
ORDER BY 年薪;

# 按姓名长度 一个函数
SELECT 
  LENGTH(last_name) AS 字节长度,
  last_name,
  salary 
FROM
  employees 
ORDER BY 字节长度 DESC ;

# 查询员工信息，先按工资再按编号
SELECT 
  * 
FROM
  employees 
ORDER BY salary ASC,
  employee_id DESC ;

单行函数

/*一、概述
功能：类似于java中的方法
好处：提高重用性和隐藏实现细节
调用：select 函数名(实参列表);
*/
# 二、单行函数

# 1.字符函数
# length：获取参数值的字节个数
SELECT 
  LENGTH('john我我我') ;

# concat：拼接字符串
SELECT
	CONCAT( last_name, '_', first_name ) 
FROM
	employees;
	
# upper：变大写 lower：变小写
# 案例：将姓变大写，名变小写，然后拼接 
SELECT
	CONCAT(
		UPPER( last_name ),
		'_',
	LOWER( first_name )) 
FROM
	employees;
	
# substr/substring：截取子串
# 注意：索引从1开始 
SELECT SUBSTR('从入门到删库',5) out_put;# 截取指定索引处后面所有字符
SELECT SUBSTR('hello mysql',1,5) out_put; # 截取指定索引处指定长度的字符
# 案例：姓名中首字符大写，其他字符小写，然后用_拼接
SELECT
	CONCAT(
		UPPER(
		SUBSTR( last_name, 1, 1 )),
		'_',
		LOWER(
		SUBSTR( last_name, 2 ))) out_put 
FROM
	employees;
	
# instr：获取子串第一次出现的索引，如果找不到返回0 
SELECT
	INSTR( 'hello world world mysql', 'world' ) out_put;

# trim：去前后空格
SELECT
	TRIM( '     hello      ' ) out_put;
SELECT
	TRIM( 'a' FROM 'aaaaaheaalloaaa' ) out_put;

# lpad：用指定的字符左填充指定的长度 
# rpad：用指定的字符右填充指定的长度 
SELECT
	LPAD( '数据库', 10, '*' ) out_put;

# replace：替换
SELECT REPLACE
	( '从入门到精通', '精通', '删库' ) out_put;

# 2.数学函数
# round：四舍五入
SELECT ROUND(1.56);
SELECT ROUND(1.567,2);# 保留两位小数 

# ceil：向上取整，返回大于等于该参数的最小整数 
SELECT CEIL(1.02);
SELECT CEIL(-1.02);

# floor：向下取整，返回小于等于该参数的最大整数 
SELECT FLOOR(9.99);
SELECT FLOOR(-9.99);

# truncate：截断
SELECT TRUNCATE(1.599,1);# 保留一位小数

# mod：取余
SELECT MOD(10,3);

# rand：获取随机数，返回0-1之间的小数
SELECT RAND();

# 3.日期函数
# now：返回当前日期+时间
SELECT NOW();

# curdate：返回当前日期
SELECT CURDATE();

# curtime：返回当前时间
SELECT CURTIME();

# 获取指定部分，年、月、日、时、分、秒 
-- year：返回年
-- month：返回月
-- day：返回日
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1');
SELECT MONTH(NOW());
SELECT MONTH('1998-1-1');

# str_to_date：将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1998-1-1','%Y-%c-%d') out_put;
# 案例：查询入职日期为1992-4-3的员工信息 
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');# 防止输入不规则 

# date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') out_put;
# 案例：查询有奖金的员工名和入职如期(xx月/xx日 xx年) 
SELECT
	last_name,
	DATE_FORMAT( hiredate, '%m月/%d日 %y年' ) 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL;

# 4.其他函数
# 当前数据库服务器的版本
SELECT VERSION();
# 当前打开的数据库
SELECT DATABASE();
# 当前用户
SELECT USER();

# if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
SELECT
	last_name,
	commission_pct,
IF
	( commission_pct IS NULL, '没奖金', '有奖金' ) AS '备注 ' 
FROM
	employees;

# 5.流程控制函数
# case情况1:switch case的效果 
CASE 变量或表达式或字段
WHEN 常量1 THEN 值1
WHEN 常量2 THEN 值2
...
ELSE 值n
END
/*
案例：查询员工工资，要求 
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍 
其他显示原工资 
*/
SELECT
	salary 原工资,
	department_id,
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 新工资 
	FROM
		employees;

# case情况2:类似于多重if
CASE 
WHEN 条件1 THEN 值1
WHEN 条件2 THEN 值2
...
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;

在这里插入图片描述

分组函数

/*三、分组函数
用作统计使用，又称为聚合函数或统计函数或组函数。

3.1 分类
max 最大值、min 最小值、sum 和、avg 平均值、count 计算个数

3.2 简单使用
①语法

select MAX(字段) from 表名;
*/

/*
②支持的类型

sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
③以上分组函数都忽略null
*/
SELECT
	SUM( commission_pct ),
	AVG( commission_pct ),
	SUM( commission_pct )/ 35,
	SUM( commission_pct )/ 107 
FROM
	employees;



# ④都可以搭配distinct使用，实现去重的统计

select SUM(distinct 字段) from 表;
1
SELECT
	SUM( DISTINCT salary ),
	SUM( salary ) 
FROM
	employees;


#⑤count函数详细介绍

count(字段)：统计该字段非空值的个数
count(*)：统计结果集的行数
count(1)：统计结果集的行数
/*效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)和count(1)效率>count(字段)*/

# ⑥和分组函数一同查询的字段，要求是group by后出现的字段

# 案例1：查询公司员工工资的最大值、最小值、平均值、总和 
SELECT
	MAX( salary ),
	MIN( salary ),
	AVG( salary ),
	SUM( salary ) 
FROM
	employees;
	
# 案例2：查询员工表中的最大入职时间和最小入职时间相差天数 
SELECT
	DATEDIFF(
		MAX( hiredate ),
	MIN( hiredate )) DIFFERENCE 
FROM
	employees;
	
# 案例3：查询部门编号为90的员工个数
SELECT
	COUNT(*) 个数 
FROM
	employees 
WHERE
	department_id = 90;

insert into employees values (null,null,null,null,null,NULL,NULL,NULL,NULL,NULl,null);
	
select * from employees;

/*效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)和count(1)效率>count(字段)*/
select count(2) from employees;
SELECT COUNT(*) FROM employees;

分组查询

/*group by 子句，having关键字。

分组查询
引入：查询每个部门的平均工资。
这个我们就需要先根据部门的id对员工进行分组，然后每个分组分别求其平均值。这样我们之前学习的就有些捉襟见肘了，我们需要新的查询方式，分组查询。

GRUOP BY 子句
可以使用group by子句将表中的数据分成若干个组。
语法：

SELECT 
  分组函数，列（要求出现在group by的后面）
FROM
  表名 
WHERE 条件 
[GROUP BY 分组的列表]
[ORDER BY 子句] ;


注意：查询列表比较特殊，要求是分组函数和group by后出现的字段
*/
# 简单的应用
# 我们先举一个简单的例子：查询每个工种的最高工资。
SELECT 
  MAX(salary),
  job_id 
FROM
  employees 
GROUP BY job_id;



#查询每个部门的员工数量。
SELECT 
  COUNT(*),
  department_id 
FROM
  employees 
GROUP BY department_id ;



#添加简单的筛选条件

#查询邮箱中包含字符A的，每个部门的平均工资。
SELECT 
  AVG(salary),
  department_id 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY department_id ;


#查询有奖金的每个领导手下员工的最高工资。
SELECT 
  MAX(salary),
  manager_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id ;


#添加复杂的筛选条件、

/*我们举几个例子：

查询哪个部门的员工个数大于2。
①我们应该先查询每个部门的员工个数。
②然后根据①的结果进行筛选，查询哪个部门的员工个数大于2
这个时候我们需要用到新的关键字having，having后面添加的也是筛选条件，但是是根据已经查询到的新的结果集进行筛选。我们可以看下面的代码即可：*/
SELECT 
  COUNT(*),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING COUNT(*) > 2 ;


/*查询每个工种的中有奖金的员工的最高工资>12000的工种编号和最高工资。
①查询每个工种有奖金的员工的最高工资
②根据①的结果继续筛选，筛选出最高工资大于12000的*/
SELECT 
  MAX(salary),
  job_id 
FROM
  employees
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary)>12000 ;

/*查询领导编号大于102的每个领导下的最低工资>5000的领导编号，以及其最低工资。
①查询每个领导下面的员工的最低工资
②添加筛选条件：编号>102
③最低工资大于5000*/
SELECT 
  MIN(salary),
  manager_id 
FROM
  employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING MIN(salary) > 5000 ;

/*我们在上面都是按照列进行分组，也可以利用表达式或者函数进行分组：

按表达式或函数进行分组
我们举几个例子：

按员工的姓名长度进行分组，查询每一组的员工个数，筛选员工个数大于5的有哪些*/
SELECT 
  COUNT(*),
  LENGTH(last_name) 
FROM
  employees 
GROUP BY LENGTH(last_name) 
HAVING COUNT(*) > 5 ;

#按多个字段分组
#查询每个部门每个工种的员工的平均工资。

SELECT 
  AVG(salary) AS 平均工资,
  department_id AS 部门,
  job_id AS 工种 
FROM
  employees 
GROUP BY department_id,
  job_id ;

#添加排序
#查询每个部门每个工种的员工的平均工资，然后根据平均工资从高到低排序。

SELECT 
  AVG(salary) AS 平均工资,
  department_id AS 部门,
  job_id AS 工种 
FROM
  employees 
GROUP BY 
  department_id,
  job_id 
ORDER BY AVG(salary) DESC ;