聚合函数
将多行汇聚成一行进行输出,聚合函数具有自动过滤的功能,将null值自动过滤,使他不参与运算,适用范围:select子句、having子句、order by子句中
create table student(
id int primary key auto_increment,
name varchar(20) unique,
age int default 18,
birthday timestamp not null
)
1.count()
统计表中数据的行数(不包含null值)
#统计学生人数
select count(*) from student;
2.max()
计算指定列的最大值,如果该列是字符串,按照字符串排序规则进行运算
#计算年龄最大的学生
select max(age) from student;
3.min()
计算指定列的最小值,如果该列是字符串,按照字符串排序规则进行运算
#计算年龄最小的学生
select min(age) from student;
4.avg()
计算平均值,如果计算的列不是数值,则会报 ERROR 1054 (42S22): Unknown column 'name' in 'field list' 的错误
#计算年龄的平均值
select avg(age) from student;
5.sum()
计算指定列的总和,如果该列不是数值类型,则结果为0
#计算年龄的总和
select sum(age) from student;
时间函数
select now();
#查询系统当前时间yyyy-MM-dd HH:mm:ss
select current_timestamp();
#查询系统当前时间 yyyy-MM-dd HH:mm:ss
select day(now())
#查询今天是本月的第几天
select date(now())
#查询当前日期 yyyy-MM-dd
select current_date();
#查询当前日期 yyyy-MM-dd
select time(now())
#查询当前时间 HH:mm:ss
select current_time();
#查询当前时间 HH:mm:ss
select year(now())
#查询当前年份
select month(now())
#查询当前月份
select addtime('14:00:00','13:00:00');
#时间加法运算
select date_add(now(),interval 1 day);
#当前时间加1天后的时间
select date_sub(now(),interval 1 day);
#当前时间减去一天的时间
字符串函数
select concat('字符串1','字符串2'); #连接两个字符串
select instr('源字符串','目标字符串','开始位置','第几次出现');#查找字符串位置
select length('字符串'); #统计字符串的长度
数学函数
select abs(-2345); #绝对值
select floor(234.22); #向下取整
select ceiling(224.13); #向上取整
条件查询
查询语句书写顺序:select===>from===> where===>group by===>having===> order by===>limit
查询语句执行顺序:from===>where===>group by===>having===>select===>order by===>limit
1.group by 和聚合函数一起使用
#统计各部门编号大于100并且工资大于8000的各部门员工个数
select departmentnumber,sum(salary) from employee group by department having sum(salary)>8000;
2.order by 字段 asc|desc 对指定字段进行排序 asc升序 desc降序
#按照年龄升序
select * from student order by age asc;
多表连接查询
交叉连接查询
交叉连接返回的是;两个表中所有数据行的笛卡尔积,实际运用中没有任何意义
select * from 表1 cross join 表2;
内连接查询(简单连接/自然连接)
使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行
select 字段1,字段2,...from 表1 [inner] join 表2 on 表1.关系字段=表2.关系字段
外连接查询
内连接查询结果只会返回符合查询条件和连接条件的数据。实际应用中不仅需要符合条件的结果,还需要左表、右表、两张表的所有数据,这是就用到了外连接。
左外连接查询
返回包含左表中所有记录和右表中符合连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。
select 字段1,字段2,...from 表1 left [outer] join 表2 on 表1.关系字段=表2.关系字段
右外连接查询
返回包含右表中所有记录和左表中符合连接条件的记录。如果右表中某条记录在左表中不存在则在左表中显示为空。
select 字段1,字段2,...from 表1 right [outer] join 表2 on 表1.关系字段=表2.关系字段
子查询
将查询的结果作为条件嵌套在另一个查询的语句的查询条件中。在执行查询时,首先会执行子查询中的语句,再将返回的结果作为外层查询的过滤条件
带比较运算符的子查询(= != <> > >= <
select 字段 from 表1 where 表1.关系字段 比较运算符(select 表2.关系字段 from 表2 where 表2.关系字段='XXX')
带EXISTS关键字的子查询
EXISTS关键字后面的参数可以是任意一个子查询, 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行
select 字段 from 表1 where 表1.关系字段 exists(select 表2.关系字段 from 表2 where 表2.关系字段='XXX')
带ANY关键字的子查询
ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件
select 字段 from 表1 where 表1.关系字段 any(select 表2.关系字段 from 表2 where 表2.关系字段='XXX')
带ALL关键字的子查询
ALL关键字表示满足其中所有条件才会返回查询结果。
select 字段 from 表1 where 表1.关系字段 all(select 表2.关系字段 from 表2 where 表2.关系字段='XXX')
索引
索引主要时为了加快数据检索速度而出现的数据库对象。
在mysql中,访问数据库 表主要有两种:
顺序访问 :进行全表扫描
索引访问:通过遍历索引访问
#创建索引 create index 索引名 on 表名 (列名) #查看表中所有的索引 show index from 表名 #查看索引使用情况 explain select * from 表明 where 查询字段='XXx' #删除索引 drop index 索引名 on 表名 alter table 表名 drop index 索引名 #添加索引 alter table 表名 add 索引类型 索引名称(列名)
索引使用原则:
- 在经常需要搜索的列上建立
- 作为主键的列上
- 经常使用表连接的列上
- 经常需要根据范围进行搜索的列上、
- 经常需要排序的列上
- 经常使用where子句的列上
索引虽然可以加快查询效率,但是也有弊端:
- 创建索引和维护需要耗费的时间长,
- 所索引会占用数据空间
索引失效
- 不满足最左前缀原则
- 范围索引列没有放在最后
- 使用select *
- 索引列上有计算
- 索引列上使用了函数
- 字符类型没加引号
- 用 is null和is not null没注意字段是否允许为空
- like查询左边有%
- 使用or关键字时没注意
索引设计的建议
- 优先使用唯一索引
- 为常用查询字段建立索引
- 为排序、分组、和联合查询字段建立索引
- 一张表的索引数量不超过5个
- 表数据量少,可以不用索引
- 尽量使用占用空间少的字段建立索引
- 使用idx_或者unx_等前缀命名索引
- 删除没用的索引
分类
主键索引:建立在主键上的索引
唯一索引:建立在unique字段上的索引
普通索引:建立在普通字段上的索引
全文索引
视图
基于sql语句的可视化表。视图是一张虚表,
#创建视图 create view 视图名 as select 字段 from 表名 #删除视图 drop view 视图名
特点
通过对视图的操作,会修改原表数据
作用
- 简化了操作,把经常使用的数据定义为视图
- 安全,用户只能查询和修改能够看到的数据
- 逻辑上的独立性,屏蔽了表结构带来的影响
慢SQL优化
执行时间超过1s的SQL就可以认为时慢sql
原因
i/o操作过多。
优化步骤
可以从两个方面进行:减少i/o次数,耗时考虑
可以使用exlpain命令查看sql的执行情况,可以加入sql_no_cache指令,得到准确的执行时间。MySQL执行某个SQL后,会把相应的数据加载到缓冲区进行预热。因此初次执行的时间跟多次执行的时间会有一定的差别。
慢sql常见的类型
索引问题:索引是否失效
分页查询
子查询
连表查询
常见优化策略
优化sql:
- 避免使用select * ,使用具体的字段
- 在经常查询的列上添加索引
- 尽量避免使用子查询,用关联查询代替
使用内存级别的数据库(redis)
限定访问数据的范围
分库分表
水平分表
垂直分表
优点:缩小结构,易于维护
缺点:主键冗余