一:表单查询
消除重复
select distinct 列名,列名 from 表名;
当两个列里面的所有值都相同的时候,就消除重复
算术操作符
如果算总和,有的列有null,则:
SELECT IFNULL(expr1,expr2) from 表名;
如果expr1不是null, IFNULL()返回expr1, 否则它返回expr2
空值判断
空值的任何算术表达式都等于空.
select * from 表名 where 列名 is null;
select * from 表名 where 列名 is not null;
限定查询
1:字符串和日期要用单引号扩起来
2:数字类型直接书写
3:字符串是大小写不铭感的,日期值是格式铭感的
4:字符串铭感,需要添加binary关键字
比较运算符
1: = != <> > >= < <= 等于和不等于
2:BETWEEN...AND...;在俩值之间(包含开始和结尾);
3:IN(list);匹配列出的值;
4:LIKE:匹配字符串模式;
5:IS NULL:是否为空;
逻辑运算符
AND:如果组合的条件是true,返回true.
OR:如果组合的条件之一是true,返回true.
NOT:如果下面的条件是false,返回true.
优先级:比较运算符 > NOT > AND > OR
结果排序
使用ORDER BY 子句将记录排序
ASC:升序,缺省 DESC:降序
ORDER BY 子句出现在SELECT语句的最后 可以使用别名
二:MySQL查询函数
多行函数:多条数据同时计算,最终得到一条结果数据,也称为聚集函数,分组函数,主要用于完成一些统计功能.
AVG平均,SUM总和,COUNT总和,MAX最大,MIN最小
单行函数:将每条数据进行独立的计算,然后每条数据得到一条结果.
1:字符函数
LOWER(str):返回字符串str变为小写字母的字符
UPPER(str):返回字符串str变为大写字母的字符
CONCAT(str1,str2...);
1:返回结果为连接参数产生的字符串
2:如有任何一个参数为null,则返回值为null
3:允许有一个或多个参数
CHAR_LENGTH:字符串长度;
LENGTH:字符串长度(单位为字节);
LPAD(str,len,padstr)
1:返回字符串str,其左边由字符串padstr,填充到len字符长度.
2:假如str的长度大于len,则返回值被缩短至len字符.
LTRIM(str):左边空格被trim掉;
RTRIM(str):右边空格被trim掉;
REPLACE(str,from_str,to_str);
1:在str中把from_str,全部替换为to_str;
2:大小写铭感;
SUBSTRING(str,pos);
从字符串str返回一个子字符串,起始于位置pos
SUBSTRING(str,pos,len);
从字符串str返回一个长度同len字符相同的子字符串,起始于位置pos.
2:数字函数
ABS(x):返回一个数字的绝对值;
MOD(N,M):返回N被M除去的余数;
CEL(X):返回不小于X的最小整数值;
FLOOR(x):返回不大于X的最大整数值;
ROUND(X)/ROUND(X,D):
1:返回参数X,其值接近于最近似的整数.
2:在有2个参数的情况下,返回X,其值保留到小数点后D位,而第D位的保留方式为四舍五入.
3:日期函数
SATE_ADD(date,INTERVAL exper type) / DATE_SUB(date,INTERVAL expr type)向日期添加指定的时间间隔
1:执行日期运算;
2:date 是一个DATETIME 或 DATE值,用来指定起始时间;
3:exper 是一个字符串表达式,用来指定从起始日期添加或减去的时间间隔;
4:type 为关键字,它指示了表达式被解释的方式
type 参数可以是以下值:MICROSECOND SECOND MINUTE HOUR
current_date() 当前日期
current_time() 当前时间
DATEDIFF(expr,expr2): 返回起始时间expr和结束时间expr2之间的天数
获取日期时间中某个段
DAY HOUR MINUTE MONTH YEAR
LAST_DAY : 获取一个日期或日期时间值,返回该月最后一天对应的值
UNIX_TIMESTAMP(date): 它会将参数值以'1970-01-01 00:00:00'GMT后的秒数的形式返回
FROM_UNIXTIME(unix_timestamp).FROM_UNIXTIME(unix_timestamp,format):返回'YYYY-MM-DD HH:MM:SS'或指定format的日期
4:转换函数
数字和字符串:
FORMAT(X,D)
将数字X的格式写为'#,###,###,##',以四舍五入的方式保留小数点后D位,并将结果以字符串的形式返回
若D为0,则返回结果不带有小数点,或不含小数部分.
日期和字符串的转换:
格式:DATE_FORMAT(date,format):把日期转换为字符串.
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d') FROM DUAL;
STR_TO_DATE(str,format):把字符串转换为日期.
select str_to_date('2018/03/17','%Y/%m/%d') from dual;
5:单行函数的嵌套
单行函数可被嵌入到任何层,在嵌套的时候,最内层的函数最先被执行,执行的结果被传递到它的上层函数,
作为参数使用,然后依次从内向外执行,直到所有的函数执行完.
二:分组查询
使用GROUP BY语法:
1:出现在SELECT列表中的字段,如果出现的位置不是在组函数中,那么必须出现在GROUP BY子句中
2:在GROUP BY子句中出现的字段,可以不出现在SELECT列表中
3:如果没有GROUP BY子句SELECT列表中的任何列或表达式不能使用统计函数:
执行顺序:1.FROM 2.WHERE 3.GROUP BY 4.HAVING 5.SELECT 6.DISTINCT 7.UNION 8.ORDER BY
三:多表查询
笛卡尔乘积:
多表查询时没有连接条件的表关系返回的结果.
多表查询会产生笛卡尔积:
假设集合A={a,b}, 集合B={0,1,2} 则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}.
实际运行环境下,应避免使用全笛卡尔积.
select * form emp,dept;
解决方案:在where加入有效的连接条件-->等值连接
注意: 连接N张表,至少需要N-1个连接条件.
外键约束(POREIGN KEY: FK):用来限定B表中的某一列的数据来源于A表中的主键列,不允许乱写.
1:在employee表中dept_id列的值,就应该来源于department表中的id列,不允许乱写.
2:和查询没有关系,没有外键不影响查询,仅仅是在INSERT数据时会做数据检查.
3:在开发中要支持外键和事务,必须使用InnoDB存储引擎,不能使用MyISAM.
4:使用外键性能较低,在开发中往往删除掉外键约束,所有的数据合法性检查交给Java代码.
5:外键在many方,多个员工同属于一个部门.
6:在添加many方时,往往是通过下拉列表去选择noe方,而不是直接写.
隐式连接:
多表查询:
1):内连接查询(隐士/显示)
2):左外连接查询,左连接查询
3):右外连接查询,右连接查询
4):全外连接查询
5):自连接查询.
一定要记住右图.
A和B好比是两张表.
红色表示查询的结果.
红色的位置对应的是什么连接查询.
所有的连接表示图:
显示连接:
内连接:
隐式内连接的查询语法:
SELECT <select_list> FROM 表名称A,表名称B WHERE 查询条件 AND 消除笛卡尔积的连接条件 [ORDER BY 排序字段[ASC|DESC]..]];
在WHERE 子句中写入连接条件:
当多个表中有重名列时,必须在列的名字前加上表名作为前缀,一般的是给表起别名.
select e.ename,e.sal,d.dname,sg.grade from emp e ,dept d ,salgrade sg where
e.deptno = d.deptno AND e.sal between sg.losal and sg.hisal;
显示内连接的查询语法:
SELECT <select_list> FROM A [INNER] JOIN B ON 消除笛卡尔积的连接条件 [join..]
自然连接的条件是基于表中所有同名列的等值连接
为了设置任意的连接条件或者指定连接的列,需要使用ON子句
连接条件与其他查询条件分开书写,使用ON子句使查询语句更容易理解.
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno;
select e.ename,e.sal,d.dname from emp e join dept d using (deptno);
执行顺序:先执行join再执行where
外连接:
外连接查询,内连接最大的问题在于必须匹配条件才能被查询出来,而在开发中条件存在不
匹配的情况,比如新插入一条员工信息,却还没有分配部门,此时就查询不出该员工.
------------------------------------------------------------------------
SELECT <select_list> from A left join B on A和B的连接条件
左外连接查询(left join). 左连接查询: 查询出join左边表中所以数据,右边表如果不匹配使用null填充.
SELECT <select_list> from B right join A on A和B的连接条件
右外连接查询(right join). 右连接查询: 查询出join右边表中所以数据,左边表如果不匹配使用null填充.
查询出每个部门员工的人数.
select d.dname,count(e.ename) from dept d left join emp e on e.deptno = d.deptno group by d.dname;
+------------+----------------+
| dname | count(e.ename) |
+------------+----------------+
| ACCOUNTING | 0 |
| OPERATIONS | 0 |
| RESEARCH | 2 |
| SALES | 3 |
+------------+----------------+
查询出至少有一个员工的所有部门编号,名称,并统计出这些部门的平均工资,最低工资,最高工资
select d.deptno,d.dname ,avg(e.sal),min(e.sal),max(e.sal) from dept d join emp e on
d.deptno = e.deptno group by d.deptno,d.dname;
+--------+----------+------------+------------+------------+
| deptno | dname | avg(e.sal) | min(e.sal) | max(e.sal) |
+--------+----------+------------+------------+------------+
| 20 | RESEARCH | 1887.5000 | 800 | 2975 |
| 30 | SALES | 1366.6667 | 1250 | 1600 |
+--------+----------+------------+------------+------------+
全连接:
全外连接查询:
FULL OUTER JOIN 中会返回所有右边表中的行和所有的左边表中的行,即使在左边的表中没有可对应的列值或者右边的表中没有可对应的列
MYSQL中暂时不支持全连接 可以通过union左右连接来完成;
自连接查询:
自连接查询:把一张表看成是两张表,使用别名区分.
四:子查询
子查询的分类:是根据子查询的结果集来划分的.
情况1:单行单列子查询:只包含一个字段的查询,返回的查询结果也只包含一行数据:放到WHERE后面.
情况2:多行单列子查询:只包含了一个字段,但返回的查询结果可能多行或者零行:放到WHERE后面
情况3:多列子查询:包含多个字段的返回,查询结构可能是单行或者多行,好比是一张临时表,放到FROM后面.
情况1:单行单列子查询:
字查询返回一行一列记录,好比是一个值
1:返回一行记录
2:使用单行记录比较运算符:=;>;>=;<;<=;<> (针对于一个值的运算符)
情况2:多行子查询返回多行单列:好比是多个值
1:返回多行
2:使用多行比较运算符
IN :与列表中的任意一个值相等
ANY:与子查询返回的任意一个值比较
1):=ANY:此时和IN操作符相等
2):>ANY:大于子查询中最小的数据
3):<ANY:小于子查询中最大的数据
ALL:与子查询返回的每一个值比较
1):>ALL:大于子查询中最大的数据.
2):<ALL:小于子查询中最小的数据.
情况3:多列子查询
子查询返回的结果是多行多列/一行多列,只要是多列,就可以看成一张表.
一般会把多列子查询返回的结果当成一个临时表,接着在临时表上继续查询或者连接查询;
注意,多行多列的子查询返回的结果必须要设置一个临时表的名称;
UNION操作符用于合并两个或多个SELECT语句的结果集.
注意:
1:UNION内部的SELECT语句必须拥有相同的数量列.
2:列也必须拥有兼容的数据类型.
3:每条SELECT语句中的列的顺序必须相同.
4:UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名
5:UNION操作符选取不同的值,如果允许重复的值,请使用UNION ALL(性能高).
五:DML和TCL
数据库事务概念:
事务的基本原理图:
事务的隔离图:
这里最严重的就是丢失更新问题,解决方案:
方案1:悲观锁.事务A在操作时,悲观认为其他事务会进来干扰自己.
方案2:乐观锁.事务A在操作时,乐观认为其他事务不会进来干扰自己.
悲观锁:
使用数据库自身的排他锁机制(写锁)(排斥其他锁).
DML操作会自动加上排它锁.(增删改操作)
DQL操作需要我们手动加上排他锁.(查询操作)
SELECT * FROM 表名 FOR UPDATE.
乐观锁
乐观锁的操作步骤图:
SQL加强课程,重点掌握
1:权限管理,至少会使用客户端配置,分配用户和权限即可.
2:单表和多表查询(内连接/外连接/自连接)
3:查询行数
4:分组查询
5:子查询
6:悲观锁和乐观锁的原理
MYSQL高级:
执行流程 SQL优化 explain profile 索引原理和设计 主从复制/读写分离
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
