MySql学习

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

# 高性能MySql泛读

MySql逻辑架构

在这里插入图片描述

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

1.1.1 连接管理和安全性

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

1.2 并发控制

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

1.2.1 读写锁

  1. 读锁是共享的,互相不阻塞的
  2. 写锁是阻塞的,会阻塞其他所有的写锁和读锁

1.2.2 锁粒度

  1. 提高共享资源并发性的方法是让锁定对象更有选择性,尽量之锁定需要修改的部分数据,而不是全部
  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 ;

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值