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 ;