MySQL-增删改查函数

查询
（一）基本查询操作

1．基本入门查询
简单查询已经单表心得
格式: select distinct * | 字段1*0.9 [as] 别名1,字段2 + 10000 [as] 别名2 from 表名;

总结:写一些复杂单表查询的技巧:
		1.去掉条件
		2.去掉聚合
		3.简单查询
		4.加上条件
		5.加上聚合	

SELECT {*|<字段列表>}
FROM <表1>, <表2>
[
where <表达式>
[group by definition]
[having [{}…]]
[order by ]
[limlt, ]
];

select [字段1, 字段2 …,字段n]
FROM [表或视图]
where[查询条件];

2．where条件判断符
基本
操作符 说明
= 相等
<> ,!= 不相等
< 小于
<= 小于或者等于

大于
= 大于或等于
between 位于两个值之间
where子句
https://blog.csdn.net/q1054261752/article/details/49766271
在where子句中使用子查询

– 25、查询各科成绩前三名的记录:(不考虑成绩并列情况)；
– 思路：WHERE子句，是对 “每一行” 分别进行计算，如果不符合条件则过滤掉
SELECT a.kecheng, a.xuehao, a.chengji
FROM sc a
WHERE (
SELECT count(kecheng)
FROM sc
WHERE kecheng = a.kecheng
AND a.chengji < chengji
) <= 2
ORDER BY a.kecheng ASC, a.chengji DESC

结果:
kecheng xuehao chengji
1 4 90
1 5 90
1 1 80
1 7 80
2 3 96
2 5 96
2 1 86
3 5 98
3 4 88
3 1 83
4 4 99
4 5 99
4 1 89
5 7 89

3．and和or和圆括号()和not
and和or
and是只有满足所有的查询条件记录才会被返回,可以使用and连接多个查询条件,多个查询条件用and分开,and是显示所有条件都成立的结果

or是把条件拆分开了显示,每一个or算是一个独立的条件,得到的结果是每个条件产生的结果的和,or与and相反,在where声明中使用or操作符,表示只需要满足其中一个条件的记录即可返回,or也可以连接多个条件,多个条件表达式之间用or分开.

示例:
上面两个sql是两个条件,一个结果是4行数据,一个结果是3行数据

下面一个sql是把上面两个条件用or连接起来了,结果是7行数据
查询deptno为10或者comm不为null的数据

圆括号
如果where子句包含了3个或更多条件,且同时使用了and和or操作符,那么需要使用圆括号来明确意图,以使数据库服务器或者以后可能阅读你代码的其它程序员能够理解:

where end_date IS NULL AND (title =’Teller’ OR start_date< ’2007-01-01’)

戳usand, or的三条件评估

中间结果 最终结果
where true and (true or true) true
where true and (true or false) true
where true and (false or true) true
where true and (false or false) false
where false and(true or true) false
where false and (true or false) false
where false and(false or true) false
where false and (false or false) false

圆括号中的条件作为一个整体进行判断
select EMPNO,ENAME,DEPTNO
from emp
where (DEPTNO = 10
or comm is not null or SAL <= 2000)
and DEPTNO = 20;
是先计算括号里面的整体,然后再计算and后面的条件,如果不用圆括号包起来的话,条件会又不一样的.

4．带in关键字的查询
in操作符用来查询满足指定范围内的条件记录,使用in操作符,将所有检索条件用括号括起来,检索条件之间用逗号隔开,只要满足条件范围内的一个值即为匹配项.

select *
from emp
where EMPNO in (“7369”, “7499”)

select *
from emp
where EMPNO not in (“7369”, “7499”);
5．带between and 范围查询
用来查询某个范围内的值,有两个参数,范围的开始值和结束值.

查询工资在2000~3000
select *
from emp
where SAL between 2000 and 3000;

也可以加not,表示不在范围值内的值,

查询工资不在2000~3000
select *
from emp
where SAL not between 2000 and 3000;

6．模糊查询like

通配符是一种在sql的where条件子句中拥有特殊意思的字符,sql语句中支持多种通配符,可以和like一起使用的通配符有’%’和’_’

_表示任意一个字符 %表示任意多个任意字符

SELECT *
FROM project
WHERE id LIKE ‘%1%’

也可以查询以什么为开头以什么为结尾的内容

SELECT *
FROM rests_travel_city
WHERE city_name LIKE ‘台%市’;
– 查询 '台’开头和’市’结尾的城市名字

7．查找处理空值(null值)
查找null值
null的条件比较运算是比较特殊的,不能使用 =null 或者 !=null 来在列中查找null值
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

下面sql能查询出来数据
select * from EMERGENCY_COORDINATION_UNIT where CREATE_ID is null
将null值转成实际值
问题:
在查询结果中会有null值,可以用 ifnull() 或者 coalesce() 函数把null替换成实际的值, 这两个函数的作用是一样的.

SELECT ifnull(comm, ‘asdas’)FROM emp;

SELECT coalesce(comm, 0)FROM emp;

在标量表达式中使用null
假设斯坦30岁,而奥利弗的年龄未知,如果我问你到底是斯坦大还是奥利弗大,你只能回答:”我不知道”,如果我问你斯坦是不是和奥利弗一样大,你还是只能回答:”我不知道”,如果我问你他们两个加起来有多大,你的回答依然如此.

假设查理的年龄也是未知,如果我问你奥利弗和查理是不是一样大,你的答案还是”我不知道”,这就是为什么null=null的结果还是null.
下列列举了一些程序员期望得到某些结果,但事实却不如人意的情况
表达式 期望值 实际值 原因
null=0 true null null不是0
null=12345 false null 如果未指定值和所给值相等则未知
null<>12345 true null 不相等则未知
null+12345 12345 null null不是0
null || ‘string’ ‘string’ null null不是空字符串
null=null true null 未指定值和另一个值相等则未知
null<>null false null 如不同则未知
在布尔表达式中使用null
表达式 期望值 实际值 原因
null and true false null null不是false
null and false false false 真值and false 是伪值
null or false false null null不是false
null or true true true 真值or true 是真值
not (null) true null null 不是false
一个null值当然不是true,同样也不是false,如果是false,对一个null使用not操作符.则应该返回true,但事实是not(null’)依旧返回一个null,这样的行为让那些想要在布尔表达式中使用null的人感到很困惑.
8．排序查询(order by)
简介和单列排序
作用:就是对查询出来的数据,进行排序,比如通过创建时间排序等等.

默认情况下,查询数据按字母升序进行排序(从A~Z),还可以降序,用DESC来
需要注意,对于包含null值的数据行,如果设定按升序排列,他们将出现在查询结果的开头,如果设定按降序排序,他们将出现在查询结果的末尾.如果不想这样,解决办法可以使用if()函数.

SELECT *
FROM merchant
ORDER BY if(type IS NULL, 0, type) – 如果 type 为null的话type就等于0 ,否则type等于原来的值

格式:
order by 字段 ASC(升序,默认)| DESC (降序)

注意:
如果是 order by 字段1 desc,字段2 desc;
排序结构: 首先按照字段1排序,如果字段1相同,再按照字段2排序
如果字段1不相同, 那么不会对字段2排序
多列排序
注意有先后顺序.在多列进行排序的时候,首先排序的第一列必须有重复的列值,才会对第二列进行排序,如果第一列数据中所有的值都是不重复的,将不再对第二列进行排序

多列排序降序需要注意一点.
SELECT f_price, f_name
FROM fruits
ORDER BY f_price DESC, f_name;

desc关键字只对其前面的列进行降序排列,在这里只对 f_price排序,且并没有对 f_name进行排序,因此f_price按降序排列,而f_name列仍按升序排序,如果要对多列都进行降序排序,必须要在每一列的列名后面都加上desc关键字
按子串排序
按字符串的某一部分对查询结果进行排序,列如,要从emp表中返回员工的名字和职位,并且按照职位字段的最后三个字符排序
看右面sql,
substr()是截取的函数

SELECT e.ENAME, e.JOB
FROM emp e
ORDER BY substr(e.JOB, - 3)

根据数据项的键排序
根据某些条件逻辑来排序,列如,如果job是’salesman’,要根据comm来排序,否则,根据sal排序
SELECT ENAME, SAL, JOB, COMM
FROM emp
ORDER BY CASE
WHEN JOB = ‘salesman’
THEN COMM
ELSE SAL END
也可以使用case表达式来动态改变如何对结果排序,传递给order by 的值类似这样:

SELECT ENAME,
SAL,
JOB,
COMM,
CASE
WHEN JOB = ‘salesman’
THEN COMM
ELSE SAL END AS flag
FROM emp
ORDER BY flag
根据表达式排序
使用列数据对结果集进行排序十分有用,但有时或许还需要根据一些并非存放在数据库中的,甚至可能没有在查询中出现的内容进行排序,而在order by子句后增加表达式可以满足这种需求,
列子:
对于customer表,也许你会需要根据客户的联邦个人识别号码(通常是个人的社会安全号码或者企业公司号)的最后3位数字进行排序

SELECT *
FROM customer
ORDER BY right(fed_id, 3)

该查询使用内建函数right()提取了fed_id列的最后三个字符,并根据该值对返回的行排序.

9．分组查询:group by

基本的分组查询
作用: 先分组 再查询
格式化:
group by 字段名

含义: 先分组 ,以字段的值分组,具体分为几组?? 该字段有几种值,那么就会分成几组
			
		注意:
			普通查询后面 条件过滤用where 关键字
			分组查询后面 条件过滤用having关键字 .

group by分组会自动排序:根据分组字段:默认是升序 ,如果不想让它自动排序,在sql后面加order by null,这样可以提升些许性能.

SELECT *
FROM merchant GROUP BY type ORDER BY NULL;

group by 字段[asc|desc]: --对分组的结果然后合并之后的整个结果进行排序

分组的意义是为了统计数据(按组统计:按分组字段进行数据统计),配合聚合函数来使用,如果分组不是为了统计就没有任何意义

列如:要返回每个水果供应商提供的水果种类,这时就要在分组过程中使用 count()函数,把数据分为多个逻辑组,并对每个组进行集合计算.
列子:
根据s_id对fruits表中的数据进行分组,sql语句如下:
select s_id,count(*) as total from fruits group by a_id;

使用having过滤分组
having与where的区别:
having是在数据分组后对数据进行过滤,后面可以使用分组函数(统计函数),having子句作用于组,having能做where所有的事情,只是效率会低.

where是在数据分组前对数据进行过滤,后面不可以使用分组函数,另外where排除的记录不再包括在分组中.where子句作用于表和视图

select count(*),JOB
from emp
group by JOB
having JOB != “CLERK”
/查询职业的总数,条件是:job!=CLERK/

having使用统计函数
having能使用统计函数,而where不能使用, having能使用字段别名,而where不能,因为where是从磁盘取数据,而名字只能是字段名,别名是在字段进入到内存后才会产生的.

显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，having子句可以让我们筛选成组后的各组数据

用group by和having子句联合来查出不重复的记录,sql如下:
select uid,email,count() as ct from edm_user081217 GROUP BY email
然后看这个，就容易理解了
select uid,email,count() as ct from edm_user081217 GROUP BY email HAVING ct > 1
先用group by 对email进行分组,在用having来过滤大于1的,这样查找出来的就是重复的记录了.

having和where同时使用
where是在数据分组(group by)前进行过滤,having是在数据分组后进行过滤,这是一个重要的区别,where排除的行不包括在分组中,这个可能会改变计算值,从而影响having子句中基于这些值过滤掉的分组.
案例:
返回过去12个月内具有两个以上订单的顾客.
where语句过滤出过去12个月内下过的订单,然后再增加having子句过滤出具有两个以上订单的分组.

SELECT *
FROM products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING count(*) >= 2
;
分析
这条语句中,where子句过滤所有prod_price至少为4的行,然后按vend_id分组数据,having子句过滤为2或者2个以上的分组.

统计之后显示统计的总和
with rollup
作用:在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和

select count(*),JOB
from emp
group by JOB
with rollup

查询结果通过group by分组之后,在显示的最后面添加了一行,该行的count一列的