首页 / MYSQL / mysql_复习02
mysql_复习02
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql_复习02,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含13459字,纯文字阅读大概需要20分钟。
内容图文
![mysql_复习02](/upload/InfoBanner/zyjiaocheng/868/75ffdbb34eea4409bb821c1a0e13f91c.jpg)
select
- 基本语法:
select 查询列 from 表名
- 常量
select 常量值1,常量值2,常量值3;
select查询基础篇
- 基础语法:
select 查询的列 from 表名 select a from text;
- 查询常量
select 常量值1,常量值2,常量值3;
- 查询表达式
select 表达式;
- 查询函数
select 函数;
- 指定字段查询语法
select 字段1,字段2,字段3 from 表名;
- 查询所有列
select * from text;
- 列表名
- 语法;
select 列 [as] 别名 from 表;
- 表列名
select 别名.字段,别名.*from 表名 [as] 别名;
例子:
select t.age as '年龄',t.name as '名字' from temp02 as t;
select条件查询
- 语法;
select 列名 from 表名 where 列 运算符 值;
条件运算符
+ = 等于
+ <>或者 != 不等于
+ '>' 大于
+ < 小于
+ '>=' 大于等于
+ <= 小于等于
逻辑运算符
+ AND 多条件成立
+ OR 多个条件满足一个
like模糊查询
- 语法:
select 列名 from 表名 where 列 like pattern;
pattern 中可以包含通配符,有一下几种通配符
%: 表示匹配任意一个或多个字符
_ :表示匹配任意一个字符
between and(区间查询)
- 概念; 操作符between and(区间查询)会选取介于两个值之间的数据范围,这些值可以是数据,文本或者日期,属于一个闭区间查询。
select 列名 from 表名 where 列名 between 值1 and 值2;
返回between和and里面的数据,between and可以提高语句的简洁
IN操作符
- 当查找同一个字段但要查找很多数据,我们会使用or,但是or不够简洁
- IN操作符
- 语法: select 列名 from 表名 where 字段 in(值1,值2,值3);
这样会变得更加简洁
in列表不支持通配符,in满足里面任意一个都会返回对应的值
- 例子:
select * from text t where t.age in(1,2,3,4);
not in 查询
- 概念;not in 和 in刚好相反,in列表匹配会返回,not in是和列表不匹配才会返回.
select 列名 from 表名 where 字段 not in (值1,值2,值3)
NULL存在的坑
- 如果数据里面为null,那么使用条件查询,是查询不到的。所以我们因该使用,NULL的专门查询方式
- IS NULL(返回值为空的记录)
select 列名 from 表名 where 列 is null;
指定的列的值为null的记录
- is not NULL(返回值不为空的记录)
select 列名 from 表名 where 列 is not null;
查询指定的列的值不为null的记录
<=>(安全等于)
- 功能: 既可以判断NULL值,又可以判断普通的数值,可读性较低,用的少.
排序和分页(order by,limit)
select 字段名 from 表名 order by 字段1 [asc|desc],字段2 [asc|desc];
asc|desc表示排序的规则,asc:升序,desc:降序.默认asc
排序支持单字段和多字段排序。
也可以在where后面来排序
limit介绍
- 概念: limit用来限制select查询返回的行数,常用来分页等操作
- 语法:
select 列 from 表 limit [offset,] count;
说明:
offset:表四偏移量,通俗点来讲就是跳过了多少行,offset可以省略,默认为0,表示跳过0行
count :跳过offset行之后开始取数据,取count行记录;范围:[0,+∞]
limit分页查询
- limit分页查询,使用limit来分页
select 列 from 表名 limit (page - 1) * pageSize,pageSize;
page: 表示为第几页
pageSize: 表示每页显示多少条记录
limit中不能使用表达式,limit后面只能跟明确的数字.limit后面数字不能为负数
- 分页查询的坑
- 当b字段出现重复的值,那么排序将会打乱,数据出现重复的值。处理方案:将两个字段都要进行升降排列
分页排序时,排序不要有二义性,二义性情况下可能会导致分页结果乱序,可以在后面追加一个主
键排序
分组查询(group by,having)
- 语法:
SELECT column, group_function,... FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];
- 说明:
group_function:聚合函数。
group_by_expression:分组表达式,多个之间用逗号隔开。
group_condition:分组之后对数据进行过滤。
分组中,select后面只能有两种类型的列:
- 出现在group by后的列
- 或者使用聚合函数的列
- group_function:聚合函数
- max: 指定列的最大值
- min: 查询指定列的最小值
- count: 统计查询结果的行数
- sum: 求和,返回指定的总和
- avg: 求平均数,返回列数据的平均值
having
- 分组后筛选数据 使用having
where & group by & having & order by & limit 一起协
作
select 列 from
表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;
必须按照上面顺序来写
mysql常用函数汇总
数值型函数
函数名称 | 作 用 |
---|---|
abs | 求绝对值 |
sqrt | 求二次方根 |
mod | 求余数 |
ceil 和 ceiling | 两个函数功能相同,都是返回不小于参数的最小整数,即向上取整 |
floor | 向下取整,返回值转化为一个BIGINT |
rand | 生成一个0~1之间的随机数,传入整数参数是,用来产生重复序列 |
round | 对所传参数进行四舍五入 |
sign | 返回参数的符号 |
pow 和 power | 两个函数的功能相同,都是所传参数的次方的结果值 |
sin | 求正弦值 |
asin | 求反正弦值,与函数 SIN 互为反函数 |
cos | 求余弦值 |
acos | 求反余弦值,与函数 COS 互为反函数 |
tan | 求正切值 |
atan | 求反正切值,与函数 TAN 互为反函数 |
cot | 求余切值 |
MySQL 字符串函数
函数名称 | 作 用 |
---|---|
length | 计算字符串长度函数,返回字符串的字节长度 |
concat | 合并字符串函数,返回结果为连接参数产生的字符串,参数可以使一个或多个 |
insert | 替换字符串函数 |
lower | 将字符串中的字母转换为小写 |
upper | 将字符串中的字母转换为大写 |
left | 从左侧字截取符串,返回字符串左边的若干个字符 |
right | 从右侧字截取符串,返回字符串右边的若干个字符 |
trim | 删除字符串左右两侧的空格 |
replace | 字符串替换函数,返回替换后的新字符串 |
substr 和substring | 截取字符串,返回从指定位置开始的指定长度的字符换 |
reverse | 字符串反转(逆序)函数,返回与原始字符串顺序相反的字符串 |
MySQL 日期和时间函数
函数名称 | 作 用 |
---|---|
curdate 和current_date | 两个函数作用相同,返回当前系统的日期值 |
curtime 和current_time | 两个函数作用相同,返回当前系统的时间值 |
now 和 sysdate | 两个函数作用相同,返回当前系统的日期和时间值 |
unix_timestamp | 获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数 |
from_unixtime | 将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数 |
month | 获取指定日期中的月份 |
monthname | 获取指定日期中的月份英文名称 |
dayname | 获取指定曰期对应的星期几的英文名称 |
dayofweek | 获取指定日期是一周中是第几天,返回值范围是1~7,1=周日 |
week | 获取指定日期是一年中的第几周,返回值的范围是否为 0?52 或 1?53 |
dayofyear | 获取指定曰期是一年中的第几天,返回值范围是1~366 |
dayofmonth | 获取指定日期是一个月中是第几天,返回值范围是1~31 |
year | 获取年份,返回值范围是 1970?2069 |
time_to_sec | 将时间参数转换为秒数 |
sec_to_time | 将秒数转换为时间,与TIME_TO_SEC 互为反函数 |
date_add 和 adddate | 两个函数功能相同,都是向日期添加指定的时间间隔 |
date_sub 和 subdate | 两个函数功能相同,都是向日期减去指定的时间间隔 |
addtime | 时间加法运算,在原始时间上添加指定的时间 |
subtime | 时间减法运算,在原始时间上减去指定的时间 |
datediff | 获取两个日期之间间隔,返回参数 1 减去参数 2 的值 |
date_format | 格式化指定的日期,根据参数返回指定格式的值 |
weekday | 获取指定日期在一周内的对应的工作日索引 |
MySQL 流程控制函数
- if 判断,流程控制
IF(expr,v1,v2)
当 expr 为真是返回 v1 的值,否则返回 v2
- ifnull 判断是否为空
IFNULL(v1,v2):v1为空返回v2,否则返回v1.
- case 搜索语句
类似于java中的if..else if..else
CASE <表达式>
?WHEN <值1> THEN <操作>
?WHEN <值2> THEN <操作>
?...
?ELSE <操作>
END CASE;
或者
CASE
?WHEN <条件1> THEN <命令>
?WHEN <条件2> THEN <命令>
?...
?ELSE commands
END CASE;
其他函数
函数名称 | 作用 |
---|---|
version | 数据库版本号 |
database | 当前数据库 |
user | 当前连接用户 |
password | 返回字符串密码形式 |
md5 | 返回字符串md5数据 |
深入了解连接查询及原理
- 但我们要查询多个表的时候,使用连接查询来提高查询效率!
笛卡尔积
- 两个集合a和b,产生的可能是a*b。(两集合相互关联的所有可能)
- 语法:
select 字段 from 表1,表2[,表N];
或者
select 字段 from 表1 join 表2 [join 表N];
内连接
select 字段 from 表1 inner join 表2 on 连接条件;
或
select 字段 from 表1 join 表2 on 连接条件;
或
select 字段 from 表1, 表2 [where 关联条件];
内连接相当于在笛卡尔积的基础上加上了连接的条件
当没有连接条件的时候,内连接上升为笛卡尔积。
内连接使用第三种,简洁!
外连接
- 外连接涉及2个表,分为:主表和从表,要查询的信息主要来自于那个表,谁就是主表
- 外连接查询结果为主表中所有记录。如果从表中有和它匹配的,则显示匹配的值,这部分相当于内连接查询出来的结果;如果从表中没有和它匹配的,则显示null。
- 最终:外连接查询结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录。
- 外连接分为2种:
- 左外链接:使用left join关键字,left join左边的是主表。
- 右外连接:使用right join关键字,right join右边的是主表。
左连接
- 语法;
select 列 from 主表 left join 从表 on 连接条件;
右连接
- 语法:
select 列 from 从表 right join 主表 on 连接条件
子查询?
- 简要:出现在select语句中的select语句,称为子查询或内查询
- 子查询分类
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
- select后面:仅仅支持标量子查询。
- from后面:支持表子查询。
- where或having后面:支持标量子查询(单列单行)、列子查询(单列多行)、行子查询(多列多行)
- exists后面(即相关子查询):表子查询(多行、多列)
select后面的子查询
- select 里面还有select查询
from后面的子查询
- 将子查询结果的表当作一张表,必须起别名,否则找不到表,然后将真实的表和子查询结果的表进行连接查询.
where和having后面的子查询
- mysql中的in,any,some,all
- in,any,some,all分别是子查询关键词之一
- in(not in):列表中的"任意一个"
- any或者some:和子查询返回某一个值比较,比如a>some(10,20,30),a大于子查询中任意一个即可,等同于a>min(10,20,30)
- all:等同于 a>all(10,20,30),a大于子查询中的所有制
NULL的坑
in和null比较
mysql> select * from test1;
+------+------+
| a ??| b ??|
+------+------+
| ??1 | ??1 |
| ??1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from test1 where a in (null);
Empty set (0.00 sec)
mysql> select * from test1 where a in (null,1);
+------+------+
| a ??| b ??|
+------+------+
| ??1 | ??1 |
| ??1 | NULL |
+------+------+
2 rows in set (0.00 sec)
结论: 当in和null比较时,无法查询出为null的记录.
not in和null比较
mysql> select * from test1 where a not in (1);
Empty set (0.00 sec)
mysql> select * from test1 where a not in (null);
Empty set (0.00 sec)
mysql> select * from test1 where a not in (null,2);
Empty set (0.00 sec)
?
mysql> select * from test1 where a not in (2);
+------+------+
| a ??| b ??|
+------+------+
| ??1 | ??1 |
| ??1 | NULL |
+------+------+
2 rows in set (0.00 sec)
结论:当not in后面有null值时,不论什么情况下,整个sql查询结果都为空.
exists,not exists和null比较
mysql> select * from test2;
+------+------+
| a ??| b ??|
+------+------+
| ??1 | ??1 |
| ??1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from test1 t1 where exists (select * from test2 t2 where t1.a =
t2.a);
+------+------+
| a ??| b ??|
+------+------+
| ??1 | ??1 |
| ??1 | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from test1 t1 where not exists (select * from test2 t2 where
t1.a = t2.a);
+------+------+
| a ??| b ??|
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)
结论: 因为=不能比较null
聚合函数的null坑
mysql> select count(a),count(b),count(*) from test1;
+----------+----------+----------+
| count(a) | count(b) | count(*) |
+----------+----------+----------+
| ????2 | ????1 | ????3 |
+----------+----------+----------+
1 row in set (0.00 sec)
count(a)返回了2行记录,a字段为NULL的没有统计出来。
count(b)返回了1行记录,为NULL的2行记录没有统计出来。
count(*)可以统计所有数据,不论字段的数据是否为NULL。
mysql> select * from test1 where a is null;
+------+------+
| a ??| b ??|
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)
?
mysql> select count(a) from test1 where a is null;
+----------+
| count(a) |
+----------+
| ????0 |
+----------+
1 row in set (0.00 sec)
上面第1个sql使用is null查询出了结果,第2个sql中count(a)返回的是0行。
结论:count(字段)无法统计字段为NULL的值,count(*)可以统计值为null的行。
NULL不能作为主键的值
- 但我们创建主键时,虽然没有指定not null但是,使用show create table xx 会发现创建的主键自动变成了not null
总结
- 操作null其实很复杂,也很容易出错,最有效的方法时避免使用null
事务详解
- 什么时事务?
- 数据库中的事务是指对数据库执行一批操作,这些操作最终要么全部执行成功,要么全部失败,不会存在部分成功的情况。
举个例子
比如A用户给B用户转账100操作,过程如下:
如果在事务的支持下,上面最终只有2种结果:
- 数据库中的事务是指对数据库执行一批操作,这些操作最终要么全部执行成功,要么全部失败,不会存在部分成功的情况。
- 操作成功:A账户减少100;B账户增加100
1.从A账户扣100
2.给B账户加100
- 操作失败:A、B两个账户都没有发生变化
如果没有事务的支持,可能出现错:A账户减少了100,此时系统挂了,导致B账户没有加上100,而A账户凭空少了100。
- 事务有四种特性
- 原子性
- 一致性
- 隔离性
- 持久性
mysql中事务操作
隐式事务
- 默认是隐式事务,执行insert,updatae,delete操作的时候。
- 查看变量autocommit是否开启了自动提交
show variables like 'autocommit'
autocommit为NO表示开启了自动提交
显式事务
显式事务需要手动开启,提交或回滚。有开发者自己控制
- 方法一:
- 语法:
//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;
- 方法二:
- 语法:
start transaction;//开启事务
//执行事务操作
commit|rollback;
savepoint关键字
- 功能可以回滚我们想要指定的回滚某个部分
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint part1;//设置一个保存点
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
Query OK, 0 rows affected (0.00 sec)
mysql> commit;//提交事务
Query OK, 0 rows affected (0.00 sec)
?
mysql> select * from test1;
+------+
| a ??|
+------+
| ??1 |
+------+
1 row in set (0.00 sec)
从上面可以看出,执行了2次插入操作,最后只插入了1条数据。
savepoint 需要结合 rollback to sp1 一起使用,可以将保存点 sp1 到 rollback to 之间的操作回滚掉。
只读事务
- 表示在事务中执行的是一些只读操作,如查询,但是不会做insert、update、delete操作,数据库内部对只读事务可能会有一些性能上的优化
- 语法:
start transaction read only;
事务中的一些问题
- 这些问题主要是基于数据在多个事务中的可见性来说的。
脏读
- 一个事务在执行的过程中读取到了其他事务还没有提交的数据。这个还是比较好理解的。
读已提交
- 从字面上我们就可以理解,即一个事务操作过程中可以读取到其他事务已经提交的数据。事务中的每次读取操作,读取到的都是数据库中其他事务已提交的最新的数据(相当于当前读)
可重复读
- 一个事务操作中对于一个读取操作不管多少次,读取结果都是一样的!
幻读
- 幻读在可重复读的模式下才会出现,其他隔离级别中不会出现
事务的隔离级别
- 隔离级别分为4种:
- 读未提交:READ-UNCOMMITTED
- 读已提交:READ-COMMITTED
- 可重复读:REPEATABLE-READ
- 串行:SERIALIZABLE
视图
- 视图好处。
- 简化复制sql操作,不用知道实现细节
- 隔离了原始表,可以不让使用视图的人接触原始的表,从而保护了原始数据,提高了安全性.
- 创建视图
create view 视图名
as
查询语句;
- 修改视图
- 方法一
create or replace view 视图名 as 查询语句;
- 方法二
alter view 视图名 as 查询语句;
- 删除视图
- 语句
drop view 视图名去 [视图名2] [视图名n];
可以同时删除多个视图,多个视图名称之间用逗号隔开。
- 查询视图结构
# 方法一
desc 视图名称
# 方法二
show create view 视图名称;
- 更新视图【基本不用】
内容总结
以上是互联网集市为您收集整理的mysql_复习02全部内容,希望文章能够帮你解决mysql_复习02所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。