#if函数: if else 的效果SELECTIF(10<5,'大','小');#ifNull函数SELECT IFNULL(NULL,"RUNOOB");RUNOOB
#流程控制表达式: case-when-thenselectcase price when price<100then'便宜'when price>=100and price <1000then'优惠'when price >1000then'昂贵'else'其他'endas'价格' form foodtable;#VERSION:获取当前数据库版本SELECT VERSION();#DATABASE: 获取当前数据库的数据库名SELECTDATABASE();#USER:获取当前数据库用户名SELECTUSER();
五、分组函数
#简单 的使用SELECTSUM(salary)FROM employees;SELECTAVG(salary)FROM employees;SELECTSUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;#参数支持哪些类型SELECTSUM(last_name),AVG(last_name)FROM employees;SELECTSUM(hiredate),AVG(hiredate)FROM employees;SELECTMAX(last_name),MIN(last_name)FROM employees;SELECTMAX(hiredate),MIN(hiredate)FROM employees;SELECTCOUNT(commission_pct)FROM employees;SELECTCOUNT(last_name)FROM employees;#是否忽略nullSELECTSUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107FROM employees;SELECTMAX(commission_pct),MIN(commission_pct)FROM employees;SELECTCOUNT(commission_pct)FROM employees;#和distinct搭配SELECTSUM(DISTINCT salary),SUM(salary)FROM employees;SELECTCOUNT(DISTINCT salary),COUNT(salary)FROM employees;#count函数的详细介绍#效率:MYISAM存储引擎下 ,COUNT(*)的效率高INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些SELECTCOUNT(salary)FROM employees;SELECTCOUNT(*)FROM employees;SELECTCOUNT(1)FROM employees;#和分组函数一同查询的字段有限制SELECTAVG(salary),employee_id FROM employees;#去重复distinct关键字的使用SELECTdistinct name salary FROM employees;#先进行条件筛选 where 后分组 group bySELECT classid FROM student WHERE sex="男"GROUPBY classid
#在查询student之前需要考虑 到底是先分组还是先筛选条件先筛选 where 后分组 group by想分组 group by 后筛选 havingHAVINGAVG(chinese)=79
#笛卡尔集select*from dept, emp;#等值连接SELECT beauty.id, NAME ,boyname FROM beauty , boys
WHERE beauty.`boyfriend id‘=boys . id;#非等值连接SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal`AND g.`highest_sal`;#自连接:自身与自身连接查询,也是属于等值查询,只是涉及一张表SELECT.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
二、join on 连接查询
/*
select 查询列表
from 表1 别名 【连接类型】 join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
*/#内连接SELECT employee_id, city, department_name
FROM employees e
InnerJOIN departments d
ON d.department_id = e.department_id
InnerJOIN locations l
ON d.location_id = l.location_id;#左外连接查询:在内连接查询的基础上,加上左边表有而右边表没有的记录select*from dept d leftjoin emp e on e.dept_id=d.id;#右外连接查询:在内连接查询的基础上,加上右边表有而左边表没有的记录select*from dept d rightjoin emp e on e.dept_id=d.id;#全外连接查询:在内连接查询的基础上,加上左边表有而右边表没有的记录 和 右边表有而左边表没有的记录,通过union关键字来模拟全外连接查询select*from dept leftjoin emp on emp.dept_id = dept.id
unionselect*from dept rightjoin emp on emp.dept_id = dept.id;#交叉连接select*from TestA a crossjoin TestB b
三、子查询
#单行单列子查询--查询公司之中工资最低的雇员的完整信息SELECT*FROM emp e WHERE e.sal=(SELECTMIN(sal)FROM emp);-- 查询出基本工资比ALLEN低的全部雇员信息SELECT*FROM emp e WHERE e.sal<(SELECT sal FROM emp WHERE ename='ALLEN');#单行多列子查询。--查找出与ALLEN从事同一工作,并且基本工资高于雇员编号为7521的全部雇员信息,SELECT*FROM emp e WHERE e.job=(SELECT job FROM emp WHERE ename='ALLEN')AND e.sal>(SELECT sal FROM emp WHERE empno=7521);--查询与雇员7566从事同一工作且领导相同的全部雇员信息SELECT*FROM emp e WHERE(e.job,e.mgr)=(SELECT job,mgr FROM emp WHERE empno=7566);#多行单列子查询 主要使用三种操作符:IN、ANY(> ,>=最小值 <、<=最大值 =任意值)、ALL(> ,>=最大值 <、<=最小值 <>、!=任意值)SOME(> ,>=最小值 <、<=最大值 =任意值)#SOME找set1中有哪些元素,刚好等于集合set2中的元素呢 select*from set1 where number =some(select*from set2)--查询出与每个部门中最低工资相同的全部雇员信息SELECT*FROM emp e WHERE e.sal IN(SELECTMIN(sal)FROM empGROUP BY deptno);--查询出不与每个部门中最低工资相同的全部雇员信息SELECT*FROM emp e
WHERE e.sal
NOTIN(SELECTMIN(sal)FROM empGROUP BY deptno);--查询出每个部门经理的工资SELECT*FROM emp WHERE sal =ANY(SELECTMIN(sal)FROM emp WHERE job='MANAGER'GROUPBY deptno);--查询出每个部门不等于经理的工资 !=(或<>)ALL不等于子查询中所有值SELECT*FROM emp WHERE sal <>ALL(SELECTMIN(sal)FROM emp
WHERE job='MANAGER'GROUPBY deptno);#子查询作为临时表使用SELECT FoodName,Price,SalesVo,AddDate,f2.FoodName
FROM FoodName f1
innerjoin(SELECT*FROM FoodTypeTable) f2
on f1.FoodTypeID=f2.FoodtypeID
#子查询作为列使用SELECT FoodName,Price,SalesVo,AddDate,(SELECT FoodTypeName from FoodTypeTable f2
where f1.FoodTypeID=f2.FoodTypeID),FoodTypeName
from FoodTable
# IN 和NOT IN 通常在WHERE 子句中使用,在IN 和NOT IN后接的子查询中,可以有多个值出现,NOT IN 的用法与IN 一样,唯一的区别就是意义相反#查询菜品编号为1和3的菜品信息SELECT FoodName,Price,SalesVo,AddDate from FoodTable where FoodID in(1,3)#使用in子查询 查询包含范围的数据select FoodName, Price, SalesVolume, AddDate
from FoodTable f1where FoodTypeID
in(select FoodTypeID from FoodTypeTable where FoodTypeID =4)#使用Exists子查询 验证是否包含成功会返回数据,不成功则什么数据都不会返回#在语句中会判断EXISTS 和 NOT EXISTS 后接的子句是否存在和是否不存在select*from FoodTypeTable f1 WhereExists(SELECT*from FoodTable f2
where f1.FoodTypeID = f2.FoodTypeID and Price>50)
四、聚合技术
#GROUP BY子句有个缺点,就是返回的结果集中只有合计数据,而没有原始的详细记录。#COMPUTE BY 子句使您得以用同一 SELECT 语句既查看明细行,又查看汇总行,可以计算子组的汇总值,也可以计算整个结果集的汇总值。Select 列名1,列名2……列名n from 表名 orderby列名1computeby] 函数名(列名2)
selecttype,price from titles orderbytypecomputeavg(price)
#With...As...语句创建#问题:查询学员与其相应的成绩-- WITH 创建方法名 AS 后则是方法体--公式表表达式--在一个批中建立一个临时表WITH StuInfo_Marks
AS(SELECT S1.StuID,S1.StuName,S2.Subject, S2.Score
FROM StuInfo S1,StuMarks S2
WHERE S1.StuID=S2.StuID)#调用 SELECT*FROM StuInfo_Marks
1.使用索引查询
--1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引-- 2.对于多列索引,不是使用的第一部分,则不会使用索引-- 3.like查询是以%开头-- 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引-- 5.条件左边使用函数,如 left(name,2) == 'AA';2.sql优化操作
-- 1、在表中建立索引,优先考虑where、group by使用到的字段。-- 2、尽量避免使用select *,返回无用的字段会降低查询效率。如下: -- SELECT * FROM t -- 优化方式:使用具体的字段代替*,只返回使用到的字段。-- 3、尽量避免使用in 和not in,会导致数据库引擎放弃索引进行全表扫描。如下:SELECT*FROM t WHERE id IN(2,3)SELECT*FROM t1 WHERE username IN(SELECT username FROM t2)-- 优化方式:如果是连续数值,可以用between代替。如下:SELECT*FROM t WHERE id BETWEEN2AND3-- 如果是子查询,可以用exists代替。如下:SELECT*FROM t1 WHEREEXISTS(SELECT*FROM t2 WHERE t1.username = t2.username)--4、尽量避免使用or,会导致数据库引擎放弃索引进行全表扫描。如下:SELECT*FROM t WHERE id =1OR id =3--优化方式:可以用union代替or。如下:SELECT*FROM t WHERE id =1UNIONSELECT*FROM t WHERE id =3--(PS:如果or两边的字段是同一个,如例子中这样。貌似两种方式效率差不多,即使union扫描的是索引,or扫描的是全表)--5、尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:SELECT*FROM t WHERE username LIKE'%li%'--优化方式:尽量在字段后面使用模糊查询。如下:SELECT*FROM t WHERE username LIKE'li%'--6、尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:SELECT*FROM t WHERE score ISNULL--优化方式:可以给字段添加默认值0,对0值进行判断。如下:SELECT*FROM t WHERE score =0.--7、尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。如下:SELECT*FROM t2 WHERE score/10=9SELECT*FROM t2 WHERE SUBSTR(username,1,2)='li'--优化方式:可以将表达式、函数操作移动到等号右侧。如下:SELECT*FROM t2 WHERE score =10*9SELECT*FROM t2 WHERE username LIKE'li%'3.Sql优化的规则
--在MySQL中可以使用连接(JOIN)查询来替代子查询--1.不要有超过5个以上的表连接(JOIN)--2.连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用到索引的话,性能会更好--3.连接之所以更有效率,是因为MySQL不需要在内存中创建临时表来完成查询工作。--4.考虑使用临时表或表变量存放中间结果。--5.少用子查询--6.视图嵌套不要过深,一般视图嵌套不要超过2个为宜--7.使用EXPLAIN或者DESCRIBE检查select 语句进行性能分析--8.避免索引失效--9.子查询尽量使用join查询
解释:
-- 连接的表越多,其编译的时间和连接的开销也越大,性能越不好控制。-- 最好是把连接拆开成较小的几个部分逐个顺序执行。-- 优先执行那些能够大量减少结果的连接。-- 拆分的好处不仅仅是减少SQL Server优化的时间,更使得SQL语句能够以你可以预测的方式和顺序执行。-- 如果一定需要连接很多表才能得到数据,那么很可能意味着设计上的缺陷。-- 连接是outer join,非常不好。因为outer join意味着必须对左表或右表查询所有行。-- 如果表很大而没有相应的where语句,那么outer join很容易导致table scan或index scan。-- 要尽量使用inner join避免scan整个表。4.Sql查询缓慢的原因
-- 1.数据量过大-- 2.表设计不合理-- 3.sql语句写得不好-- 4.没有合理使用索引-5.针对SQL语句的优化
-- 1.查询语句中不要使用 *-- 2.尽量减少子查询,使用关联查询(left join,right join,inner join)替代-- 3.减少使用IN或者NOT IN ,使用exists,not exists或者关联查询语句替代-- 4.or 的查询尽量用 union或者union all 代替 (在确认没有重复数据或者不用剔除重复数据时,union all会更好)-- 5.合理的增加冗余的字段(减少表的联接查询)--6.增加中间表进行优化(这个主要是在统计报表的场景,后台开定时任务将数据先统计好,尽量不要在查询的时候去统计)--7.建表的时候能使用数字类型的字段就使用数字类型(type,status...),数字类型的字段作为条件查询比字符串的快--8.那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾--9.合理使用like模糊查询1、 select*from student where name like'%姜小鱼%'--会造成全表扫描2、 select*from student where name like'姜小鱼%'--不会造成全表扫描 --10.where子句使用 != 或 <> 操作符优化在where子句中使用 != 或 <>操作符,索引将被放弃使用,会进行全表查询1SELECT id FROM A WHERE ID !=5--会造成全表扫描2SELECT id FROM A WHERE ID>5OR ID<5--不会造成全局表描--12.where子句中使用 IS NULL 或 IS NOT NULL 的优化 在where子句中使用 IS NULL 或 IS NOT NULL 判断,索引将被放弃使用,会进行全表查询。1、 SELECT id FROM A WHERE num ISNULL--会造全表扫描2、 SELECT id FROM A WHERE num=0--优化成num上设置默认值0,确保表中num没有null--13.where子句使用or的优化 很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。1.SELECT id FROM A WHERE num =10or num =20--索引失效2.SELECT id FROM A WHERE num =10unionallSELECT id FROM A WHERE num=20--优化后--14.where子句使用IN 或 NOT IN的优化in和not in 也要慎用,否则也会导致全表扫描。
方案一:between替换in1.SELECT id FROM A WHERE num in(select num from b )--会造成全表扫描2.SELECT num FROM A WHERE num exists(select1from B where B.num = A.num)--不会造成全表扫描
方案二:exist替换in1.SELECT id FROM A WHERE num in(select num from b )--会造成全表扫描2.SELECT num FROM A WHERE num exists(select1from B where B.num = A.num)--不会造成全表扫描
方案三:leftjoin替换in1.SELECT id FROM A WHERE num in(select num from B)--会造成全表扫描2 、2.SELECT id FROM A LEFTJOIN B ON A.num = B.num --不会造成全表扫描 --15.where子句中对字段进行表达式操作的优化--不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算,否则系统将可能无法正确使用索引。1SELECT id FROM A WHERE num/2=100--会造成索引失效2SELECT id FROM A WHERE num =100*2--优化后3SELECT id FROM A WHERE substring(name,1,3)='abc'--会造成索引失效4SELECT id FROM A WHERELIKE'abc%'--优化后5SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0--会造成索引失效6SELECT id FROM A WHERE createdate>='2016-11-30'and createdate<'2016-12-1'--不会造成索引失效 7.SELECT id FROM A WHEREyear(addate)<2016--会造成索引失效8.SELECT id FROM A where addate<'2016-01-01'--不会造成索引失效--16使用“临时表”暂存中间结果--采用临时表暂存中间结果好处:
(1)避免程序中多次扫描主表,减少程序执行“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。
(2)尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
(3)避免频繁创建和删除临时表,以减少系统资源的浪费。
(4)尽量避免向客户端返回大数据量,若数据量过大,应考虑相应需求是否合理。
--17.limit分页优化 当偏移量特别时,limit效率会非常低1SELECT id FROM A LIMIT1000,10--超级快2SELECT id FROM A LIMIT90000,10--特别慢
方法一:
select id from A orderby id limit90000,10;-- 很快,0.04秒就OK。因为用了id主键做索引当然快
方法二:
select id,title from A where id>=(select id from collect orderby id limit90000,1)limit10;
方法三:
select id from A orderby id between10000000and10000010;--18.批量插入优化1.INSERTinto person(name,age)values('A',14)2.INSERTinto person(name,age)values('B',14)3.INSERTinto person(name,age)values('C',14)
可优化为:
INSERTinto person(name,age)values('A',14),('B',14),('C',14)--21.尽量不要使用 BY RAND()命令--BY RAND()是随机显示结果,这个函数可能会为表中每一个独立的行执行BY RAND()命令,这个会消耗处理器的处理能力。SELECT*FROM A orderby rand()limit10;--优化之后SELECT*FROM A WHERE id >=((SELECTMAX(id)FROM A)-(SELECTMIN(id)FROM A))* RAND()+(SELECTMIN(id)FROM A)LIMIT10--22. 排序的索引问题 --Mysql查询只是用一个索引,因此如果where子句中已经使用了索引的话,--那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下--不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。--23.尽量用 union all 替换 union--union和union all的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,--这就会涉及到排序,增加大量的cpu运算,加大资源消耗及延迟。--所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用union all而不是union--24.避免类型转换--这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。--人为的上通过转换函数进行转换,直接导致mysql无法使用索引。如果非要转型,应该在传入参数上进行转换。--例如utime 是datetime类型,传入的参数是“2016-07-23”,在比较大小时通常是 date(utime)>"2016-07-23",可以优化为utime>"2016-07-23 00:00:00"--25.exist 代替 in SELECT*from A WHERE id in(SELECT id from B)SELECT*from A WHERE id EXISTS(SELECT1from A.id= B.id)--26.索引的分类:-- 2.2.1、单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。这里不要搞混淆了。-- 2.2.2、普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。-- 2.2.3、唯一索引:索引列中的值必须是唯一的,但是允许为空值,-- 2.2.4、主键索引:是一种特殊的唯一索引,不允许有空值。(主键约束,就是一个主键索引)-- 2.3、组合索引:在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。例如,这里由id、name和age3个字段构成的索引,索引行中就按id/name/age的顺序存放,索引可以索引下面字段组合(id,name,age)、(id,name)或者(id)。如果要查询的字段不构成索引最左面的前缀,那么就不会是用索引,比如,age或者(name,age)组合就不会使用索引查询-- 2.4、全文索引:全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,介绍了要求,说说什么是全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行,比如有"你是个大煞笔,二货 ..." 通过大煞笔,可能就可以找到该条记录。这里说的是可能,因为全文索引的使用涉及了很多细节,我们只需要知道这个大概意思。-- 2.5、空间索引:空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时,使用SPATIAL关键字。要求,引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。可能跟游戏开发有关。--
四、视图
#视图的优势
第一,增强数据安全性
第二,提高灵活性,操作变简单
第三,提高数据的逻辑独立
#视图的使用select
视图定义列
from
视图名
--删除视图的语法DROPVIEWifEXISTS view_FoodTable;-- 创建视图,查询菜品名称、菜品类型名称、销量 --创建名为view_FoodTable的视图 在创建视图view_FoodTable之前,判断视图是否已经存在,
存在则将其先删除
CREATEVIEW view_FoodTable
ASSELECT FoodName,FoodTypeName,SalesVolume
FROM FoodTable f1
innerjoin FoodTypeTable f2
ON f1.FoodTypeID = f2.FoodTypeID;#视图的修改 这里的所有参数都与创建视图的参数一样。createorreplaceview
视图名
asselect 语句;#视图的查看--查看视图是指查看数据库中已经存在的视图的定义。查看视图必须要有show view的权限。
查看视图的几种方法:
1)describe语句,语法格式:describe 视图名称; 或者desc视图名称;2)showtablestatus语句,语法格式: showtablestatuslike'视图名'3)showcreateview语句,语法格式:showcreateview'视图名'4)查询information_schem数据库下的views表
语法格式:select*from information_schema.views where table_name ='视图名'5)SELECT*FROM view_FoodTable
-- 写出创建满足下述要求的视图的 SQL语句。查询学生的学号、姓名、课程号、课程名、成绩--创建名为view stu course的视图CREATEVIEW view_stu_course
ASSelect sno, sname, cno, cname, score
From StuMarks s1
innerjoin StuInfo s2 on s1. sno=s2 . sno
innerjoin course C on s1. cno=C. cno
--利用刚才建立的视图,完成如下查询:查询考试成绩大于等于 90 分的学生的姓名、课程名和成绩SELECT sname,cname,score FROM view_stu_course where score >=90
四、变量事物
#变量的分类
一、系统变量
#注意:全局变量需要添加global关键字,会话变量需要添加session关键字,如果不写,默认会话级别1)全局变量
#变量由系统定义,不是用户定义,属于服务器层面--作用域:针对于所有会话(连接)有效,但不能跨重启
①查看所有全局变量
SHOWGLOBAL VARIABLES;
②查看满足条件的部分系统变量
SHOWGLOBAL VARIABLES LIKE'%char%';
③查看指定的系统变量的值
SELECT @@global.autocommit;
④为某个系统变量赋值
SET @@global.autocommit=0;SETGLOBAL autocommit=0;2)会话变量
--作用域:作用域:针对于当前会话(连接)有效
①查看所有会话变量
SHOWSESSION VARIABLES;
②查看满足条件的部分会话变量
SHOWSESSION VARIABLES LIKE'%char%';
③查看指定的会话变量的值
SELECT @@autocommit;SELECT @@session.tx_isolation;
④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';SETSESSION tx_isolation='read-committed';
二、自定义变量
1)用户变量
--说明:变量由用户自定义,而不是系统提供的--作用域:针对于当前会话(连接)有效,作用域同于会话变量
使用步骤:
1、声明
2、赋值
3、使用(查看、比较、运算等)
赋值操作符:=或:=
①声明并初始化
SET @变量名=值;SET @变量名:=值;SELECT @变量名:=值;
②赋值(更新变量的值)
方式一:
SET @变量名=值;SET @变量名:=值;SELECT @变量名:=值;
方式二:
SELECT 字段 INTO @变量名 FROM 表;
③使用(查看变量的值)
SELECT @变量名;2)局部变量
--作用域:仅仅在定义它的begin end块中有效--应用在 begin end中的第一句话
①声明
DECLARE 变量名 类型;DECLARE 变量名 类型 【DEFAULT 值】;
②赋值(更新变量的值)
方式一:
SET 局部变量名=值;SET 局部变量名:=值;SELECT 局部变量名:=值;
方式二:
SELECT 字段 INTO 局部变量名 FROM 表;
③使用(查看变量的值)
SELECT 局部变量名
3)案例
--案例:声明两个变量,求和并打印
用户变量
SET@m=1;SET@n=1;SET@sum=@m+@n;SELECT@sum;
局部变量
DECLARE m INTDEFAULT1;DECLARE n INTDEFAULT1;DECLARE SUM INT;SET SUM=m+n;SELECT SUM;