SELECT
person_id,
on_time,
off_time,
signin_time,
signout_time,
result,
TIMESTAMPDIFF( MINUTE, on_time, off_time ) scheduleWorkingDuraion,
TIMESTAMPDIFF( MINUTE, signin_time, signout_time ) realWorkingDuraion
FROM
`attendance_results`
WHERE
date >= '2020-07-01 08:05:09'
AND date <= '2020-09-10 08:05:09'
ORDER BY person_id ASC
-- AND person_id IN ( '1','2' )
DBMS数据库管理系统
show databases; 显示当前连接下的所有数据库;
show tables; 显示当前库中所有表
show tables from 库名 显示指定库中所有表
show columns from 表名 显示指定表中所有列
use 库名 打开指定库
DQL 数据查询语言
select 查询列表
from 表名
where 筛选条件
as作为别名 两个数值方可以用“+”符号
去重 distinct
补充函数
select database();
select user();
select ifnull(字段名,表达式);
select concal(字符1,字符2);拼接字符
select length();
二.特点
1.条件表达式筛选,
>= <= <>不等于
2.按逻辑表达式筛选
逻辑运算符:
and or not
3.模糊查询
like 一般和通配符搭配使用
_任意单个字符
%任意多个字符
between and:一般用于某字段是否在指定的区间
in:一般用于某字段是否在指定的列表;
is null 判断null值
三.排序查询
order by 排序列表:放到末尾排序;
特点:
①排序列表是单个字段·多个字段·函数·表达式·列的索引·以及组和别名
②升序 asc
③降序 desc
例题1.查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序。
select last_name,department_id,salary*12*(1+IFNULL(comission_pct,0)) 年薪 from employees
OEDER BY 年薪 DESC,last_name ASC;
四.常见函数
1.自定义函数
2.调用函数
常见函数:字符函数:CONCAT 拼接函数 SUBSTR(‘asdfg’,1,3) 截取子串 //从1开始的几位字母
TRIN去前后空格,LRAD/RPAD('王港',10,‘a’) 左填充、右填充,UPEPER/LOWER 变大写/变小写
STRCMP()/比较函数,LEFT/RIGHT('23333',1) ;
数学函数: ABS()绝对值,CELT/FLOOR() 向上取整/向下取整 , TRUNCATE(1.999,1)截断函数 , NOD() 取余
日期函数: NOW()当前时间,CURDATE(当前日期),CURTIME(当前时间),DATE_FORMAT(datetime,fmt),
STR_TO_DATE() 按指定格式解析字符串为日期类型
流程控制函数:if(100>9,'好',‘坏’), CASE 表达式 WHEN 值1 THNE 结果1,
例题:查询员工表的姓名,要求格式:首性字母大写,名小写,且姓和名之间用_分割;
select UPPER(substr(str,pos,len))from .;
select LOWER(substr(str,pos))from .;
select UPPER(str) ;
select CONCAT(UPPER(substr(str,pos,len)),LOWER(substr(str,pos)),‘_’);
例题: 部门编号是30,工资显示为2倍
部门编号是50,工资显示为4倍
否则不变
显示部门编号,新工资,旧工资;
select department-id,salary,
case department—id
when 30 then salary*2
when 50 then salary*
else salary
end newsalary from 表名;
分组函数:一组数据进行统计计算,最终得到一个值
sum()求和
avg()求平均数
max()求最大值
min()求最小值
count()非空字段的个数
order by()排序列表
groupby()分组列表
执行顺序: ①from
②where
③group by
④having
⑤select
⑥order by
需要分组查询的时候使用GROUP BY子句,例如查询每个部门的工资和,要使用部门来分组。
select 查询列表
from 表名
where 筛选条件
group by 分组列表
特点① 查询列表往往是分组函数和被分组的字段
② 分组查询的筛选一般分为两类
筛选的基表 使用的关键词 位置
分组前筛选 原始表 where GROUP BY的前边
分组后筛选 分组后的结果集 having GROUP BY后边
where--group by--having
例题: select sum(salary)
from employee
GROUP BY department;
例题:搭配distinct实现去重统计
select COUNT(DISTINCT XX)from 表名;
例题:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select job_id,MAX(salary)
from employees
where commission is not null
having MAX(salary)>12000;
连接查询:又称多表查询。
假设多表 A*B*C;
分类:
按年代分类;
sq192标准
sq199标准【推荐】
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接:
---------------------------SQL92------------------------------
1.内连接
1.等值连接
语法:
select 查询列表
from 表名1 as 别名1,表名2 别名2
where 等值连接的连接条件
特点:
1.表字段重名问题用别名解决
2.
例题:查询员工名和部门名
select last_name,‘department_name’
from employee e,department d
where e.'department_id'= d.'department_id';
AND
添加分组加筛选
案例1:查询每个城市的部门个数。1,‘city’
select COUNT(*) as 部门个数,l.'city'
from departments d,localtion_id l
where d.'location_id' = 1.'location_id'
group by l.city;
二.sql99语法
语法:
select
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【GROUP BY 分组】
【having 筛选条件】
【order by 排序列表】
分类
内连接 :inner
外连接
左外:left【outer】
右外:right【outer】
全外:full【outer】
交叉连接:cross
特点:
1.添加排序,分组,筛选
2.inner可以省略
3.筛选条件放在where后面,连接条件放在on后面,提高阅读性
4.内连接和92语法中的等值连接效果一样,都是查询多表的交际
1.等值连接
例题:查询员工名,部门名
select last_name,department_name
from employee e
inner join department d
on e.‘department’ = d.department_id
2.非等值连接
例题查询员工的工资级别
select count(*),grade_level
from empl e
join job g
on e.salry between g.glowest_sal and g.high_sal
group by grade_level
having count(*)>20
order by grade_level desc;
3.自连接
查询员工的名字上级的名字
二.外连接
应用场景:用于查询一个表中有,另一个表中没有的记录
特点:
1.外连接的查询结果为主表中的所有记录
如果表中有和它匹配的,则显示匹配的值
如果表中没有和它匹配的,则显示NULL
外连接查询结果 = 内连接查询结果+主表中有而从表中没有的记录
2.左外连接,left join左边的是主表
右外连接,right join右边的是主表
3.左外和右外连接两个表的顺序,可以实现同样的效果
引用:查询没有男朋友的女神名
select b.name ,bo.*
from beauty b
left outer join boys bo
on b.b_id = bo.id
where bo.id if null;
子查询:
含义:
出现在其他语句中的select语句,称为子查询或内查询
1.在where或者having后边
1.标量子查询(单行子查询)
2.列子查询(多行子查询)
3.行子查询(多列多行)
特点:
1.子查询放在小括号内
2,标量子查询一般搭配这单行操作符使用
> < >= <= = <>
标量子查询
例题1:谁的工资比abel高?
1.查询abel的工资
select salary
from employee
where last_name = ‘able’
2.查询员工的信息,满足salary》1
select salary
from employee
where salary》
(
1
)
分页查询
应用场景:当要显示的数据,一页显示不全需要分页
语法:
select 查询列表
from 表
【join type】 join表2
on 连接条件
where 筛选条件
GROUP BY 分组字段
having 分组后的筛选
order by 排序的字段
limit 起始索引,size
select 查询列表 7
from 表 1
连接类型 join 表2 2
on 连接条件 3
where 筛选条件 4
group by 分组列表 5
having 分组后的筛选 6
order by 排序列表 8
limit 偏移,条目数 9
9.联合查询
union 联合 合并:将多条查询语句合并成一个结果
自主去重 加 all可以不去重
DML语言
数据操作语言
插入insert,修改update,删除delete
语法
插入
insert into 表名(列名) values(值1,。。。)
可以支持插入多行
支持子查询
例题
insert into beauty(id,name,phone)
select id,boyname,‘1234567’
from boys where id<3;
insert into 表名
set 列名 = 值,列名 = 值。。。。
修改数据
语法
1.修改单表的记录
update 表名
set 列=新值,列=新值,...
where 筛选条件
例题修改beauty表中姓唐的女神的电话为138999
update beauty set phone = ‘139999’
where name like‘唐%’;
修改多表的记录
语法:mysql92
update 表1 别名,表2,别名
set 列=值,...
where 筛选条件
语法mysql99
update 表一,别名
inner|left|right join 表2 别名
on 连接条件
set 列 = 值,...
where 筛选条件;
删除语句
1.单表的删除
delete from 表名 where 筛选条件
2.多表的删除
truncate table 表名
DDL语言
1.库的管理
创建修改删除
2.表的管理
创建修改删除
创建 create
修改alter
删除 drop
1.库的管理
库的创建 create database 【if not exists】 库名;
库的修改 更改库的字符集
alter database 库名 character set gbk;
库的删除 drop database 【if exist】 库名;
2.表的管理
表的创建 create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
)
表的修改
修改列名
alter table 表名 change column 列名 被改列名 列类型;
修改列的类型或约束
alter table 表名 modify column 列名 列类型;
添加新列
alter table 表名 add column 新列名 类型;
删除列
altet table 表名 drop column 列名;
修改表名
alter table 表名 rename to 新表名;
复制表结构
create table 表一 like 表二
常见的数据类型
常见约束(限制,用于限制表中的数据,为了保证表中的数据的准确性和可靠性)
{
NOT NULL,非空,用于保证该字段的值不能为空
比如姓名,学号等
default,默认,用于保证该字段有默认值
比如性别
PRIMARY KEY:主键,用于保证唯一性(非空)
UNIQUE 唯一:用于保证字段有唯一性,可以为空
CHECK:检查支持(mysql不支持)
FPREIGN KEY :外键 用于限制两个表的关系,用于保证该字段的值必须来自主表的关系列的值
}
表级约束
添加约束的时机:1.创建表时
2.修改表时
create 表名(
字段名 字段类型 约束
)
主键和唯一的区别
唯一性 允许为空 可以存在多个
主键: √ × 最多提一个
唯一: √ √ 允许多个
标识列(自增长列)
提供默认序列值
1创建表时添加标识列 主键后加 AUTO_INCREMENT
2修改表时添加标识列 后加INT PRINARY KEY;
3修改表时删除标识列
TCL(Transaction Control Language)事务控制语言
事务:一个或一组语句组成一个执行单元,这个执行单元要么全都执行,要么全不执行
事务属性(ACID)
1.原子性 : 一个事务不可再分割,要么都执行,要么都不执行
2.一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
3.隔离性:一个事务的执行不受其他事务的干扰
4.持久性:一个事务一旦提交,则会永久的改变数据库的数据
对于同时运行的多个事务 导致并发的问题:
1.脏读:对于两个事务T1,T2,T1读取了一个字段,然后T2更新了该字段但还没有被提交的字段之后,T2回滚,T1读取的内容就是无效的
2.不可重复读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段,然后T2更新了该字段,之后,T1再次读取了同一个字段,值不相同
3.幻读: 插入时
避免方法:设置隔离级别,避免并发
隔离级别:
脏读 不可重复读 幻读
read uncommitted 可以 可以 可以
read committed 不可以 可以 可以
repeatable 不可以 不可以 可以 (默认第三个默认级别)
serializable 不可以 不可以 不可以
查看隔离级别 select @@tx_isolation
设置隔离级别 set session|global transation isolation level 隔离级别;
truncate(不支持) 与 delete(支持回滚) 在事务中的区别
视图{
含义:虚拟表
mysql5.1出现的新特性,通过表动态生成的数据
}
create view 别名
AS
视图的修改{
1.create or replace view as 没有就创建有就替代
2.alter view 视图名 as
}
视图的删除{
DROP view 视图1,视图2,视图3
}
视图查看 {
DESC 视图名
SHOW CREATE VIEW 视图名;
}
视图的修改
1.插入 insert into 视图名 values(~);
2.修改 update 视图名 xxx=yyy where xxx=zzz
3.删除 delete from 视图名 where xxx=yyy;
视图权限
变量{
系统变量
全局变量:
查看所有的系统变量 show session/global variables;
会话变量
自定义变量
用户变量
局部变量
}
函数 创建语法
CREATE FUNCTION 函数名(参数列表)RETURNS 返回类型
BEGIN
END
{
如果函数体中仅有一句话可以省略begin end
使用delimiter语句设置结束标语
}
例题 根据员工名 返回他的工资
delimiter $
CREATE FUNCTION myf2(rmpName VARCHAR(20))RETURNS double
BEGIN
set sal = 0;//定义用户变量
select salary into sal
from employees
where last_name = empName;
return sal;
END $
查看函数
SHOW CREATE FUNCTION myf3;
删除函数
DROP FUNCTION myf3;
流程控制结构
{
顺序结构:程序从上往下依次进行
分支结构:程序从两条或者多条路径上去找一条去执行
{
if(表达式1,表达式2,表达式3)//若1成立,返回2,否则3
case结构
}
循环结构:程序在满足一定条件的基础上,重复执行一段代码
}
mysql性能下架的原因:执行时间长,等待时间长
{
查询语句写的烂
索引失效:单值/复合
关联查询太多join
服务器调优以及各种参数设置
}
SQL
索引(index)是帮助MYsql高效获取数据的数据结构,可以得到索引的本质是数据结构
排好序的快速查找数据结构,降低i/o成本与cpu消耗
索引的类别:
单值索引:一个索引只包含单个列,一个表可以有多个单列索引
唯一索引:索引列的值必须唯一,但必须有空值
复合索引:一个索引包含多个列
基本索引:{
创建CREATE【unique】 INDEX indexname on mytable (columnname(length))
ALTER mytable ADD 【unique】 INDEX【indexName】ON (columnname(length))
删除 DROP INDEX 【indexName】 ON mytable
查看 SHOW INDEX FROM table_name\G
}
索引分类 BTree
索引结构:1.主键自动建立索引
2.频繁作为查询条件应该创建索引
3.查询中与其他表关联的字段,外键关系建立索引
4.频繁的更新不适合建立索引
5.where条件用不到的字段不建立索引
6.单键/组合索引的选择问题
7.查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段
不适合建立索引:1.表记录太少
2.经常增删改查的表
3.重复记录太多的表
性能分析:1.mysql query optimizer(自带性能优化器)
2.IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3.服务器可以用:top,free,iostat和vmstat来查看
使用explain可以模拟执行查询语句
Explain+SQL语句
表头数据:id select_type table type possible_keys key key_len ref rows
id相同 执行顺序,执行顺序由上到下。如果是子查询,id的序号会递增,id值越大,优先值越高,越先被执行
select_type {
simple:简单的select查询,查询中不包括子查询或者union
primary:查询中包含任何复杂的子部分,最外层查询则被标记为
subquery:在select或者where列表中包含了子查询
derived:在FORM列表中包含的子查询被标记为DERIVED
union:若第二个select出现在union之后,则被标记为
union_result:从union获得的表
}
table:是关于数据是关于哪张表的
type:{
访问类型排列,显示查询使用了哪种类型从最好到最差依次是
system>const>eq_ref>ref>range>index>ALL
SYSTEM:表只有一行记录,const类型的特列,平时不会出现
const:表示通过索引一次就找到了,const用于比较primary key或者union索引。
er_ref:唯一索引扫描,对于每个索引键,表中只有一条记录为之匹配。常见于主键或者唯一索引
ref:非唯一性索引扫描,返回匹配单独值的所有行。
range:只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪些索引,一般就是在你的where语句中出现了between,<,>,in的查询,这种范围查找比全表扫描要好,因为它需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。
index:从索引中读取,而不是all从硬盘中读的。
All:遍历全表找到匹配的行。
}
possible_keys:显示可能应用在这张表中的索引,一个或多个。
key:实际使用的索引
key_len:表示索引中使用的字节数,在不损失精确性的情况下,长度越短越好,key_len显示的是指为索引字段的最大可能长度,并非实际长度,即key_len是根据定义所得,而不是通过表内索引检索出的。
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。
rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
extra:包含不适合在其他列又很重要的信息
{
Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为文件排序。
Using temporary:使用了临时表保存中间结果,MYsql在对查询结果排序使用临时表。常见于排序order by 和分组查询 group by。
Using Index:表示使用的select操作使用了覆盖索引,避免了访问表的数据行,效率不错
}
索引的优化:explain是性能优化的参数
索引失效(应该避免)
{
1.全职匹配最好
2.最佳左前缀法则
3.不在索引列上做任何操作(计算,函数,(自动or手动)类型转换),会导致索引失效而转向全表扫描
4.储存引擎不能使用索引范围的条件右边的列
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*
6.mysql在使用不等于(!=或者《》)的时候无法使用索引会导致全表扫描
7.is null,is not null也无法使用索引
8.like以通配符开头(‘%abc。。。’)mysql索引失效会变成全表扫描的操作
9.字符串不加单引号索引失效
10.少用or,用它来连接时索引会失效
}