文章目录

• 数据准备
• 一、子查询
• • 结合布尔值
• 二、视图
• • 创建视图
• 三 null值
• • null和控制的区别
• 四 正则表达式
• • 匹配正则
  • 运算符
  • 算数运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符
  • 优先级
• 五 连接查询
• • 1 内连接
  • 2 左连接
  • 3 右连接
• 六 函数
• • 1 数学函数
  • 2 聚合函数
  • 3 字符串函数
  • 4 时间函数
• 七 存储过程
• • 概念
  • 特点
  • 语法

数据准备

 CREATE TABLE test1 ( a_id int(11) DEFAULT NULL, a_name varchar(32) DEFAULT NULL, a_level int(11) DEFAULT NULL);

CREATE TABLE test2 (
    -> b_id int(11) DEFAULT NULL,
    -> b_name varchar(32) DEFAULT NULL,
    -> b_level int(11) DEFAULT NULL);

insert into test1(a_id, a_name, a_level) values(1, 'aaaa', 10); 
Query OK, 1 row affected (0.00 sec)

insert into test1(a_id, a_name, a_level) values(2, 'bbbb', 20);
Query OK, 1 row affected (0.00 sec)

insert into test1(a_id, a_name, a_level) values(3, 'cccc', 30);
Query OK, 1 row affected (0.00 sec)

insert into test1(a_id, a_name, a_level) values(4, 'dddd', 40);
Query OK, 1 row affected (0.00 sec)

insert into test2(b_id, b_name, b_level) values(2, 'ata', 20); 
Query OK, 1 row affected (0.01 sec)

insert into test2(b_id, b_name, b_level) values(3, 'a7v', 20);
Query OK, 1 row affected (0.00 sec)

insert into test2(b_id, b_name, b_level) values(4, 'FT', 30);
Query OK, 1 row affected (0.00 sec)

insert into test2(b_id, b_name, b_level) values(6, 'MKVI', 40);
Query OK, 1 row affected (0.00 sec)

一、子查询

子查询也叫内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。 子查询语句先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。 子语句和主语句可以查的是同一个表 也可以是不同表。

select a_name,a_level from test1 where a_id in (select a_id from test1 where a_level>10)0);

in后的子语句会给之前的主语句的where判断提供一个范围 做为where的判断条件

select * from test1 where a_level in (select a_level from test2);

结合布尔值

select count(*) level from test1 where exists (select a_level from test2 where a_level== '20');

二、视图

视图是数据库中的虚拟表，这张虚拟表中不包含数据 只做了一个映射 简单来说就是一个投影 其结果数据是动态保存 根据真实数据来。展示的数据可以自定义 可以根据权限来决定什么用户可以查看。

创建视图

create view level as select * from test1 where a_level >= 10;

查看视图

 insert into level values(5,'ee5','1060');

select * from level;

原来的表的数据也添加了一个 所以修改视图也会对表的数据也会变

三 null值

在sql语句中 null表示缺失的值 即表示该值是没有值的。
在创建表时 要限制某些字段不可以为空 可以使用not null 不使用则默认可以为空

null和控制的区别

空值的长度为零 不占空间 null值长度为null，占用空间 is null无法判断空值
空值使用"="计算时，null会忽略，空值会加入计算‘

mysql> update test1 set a_name=''  where a_level='1060';

 select count(a_level) from test1;

null不会被加入计算 空值会加入计算

select * from test1 where a_level is null;

查询null值

select * from test1 where a_name is not null;

查询非null值

四 正则表达式

MySQL的正则表达式和shell脚本的正则相似 也是用于匹配数据
MysQL的正则表达式使用REGEXP这个关键字来决定使用什么匹配方式

匹配正则

regexp使用的正则表达式
^ 匹配文本的开始字符
$ 匹配文本的结束字符
. 匹配任何单个字符
*匹配零个或多个在它前面的字符
+匹配前面的字符 1 次或多次
字符串 匹配包含指定的字符串
p1Ip2 匹配 p1 或 p2
[…] 匹配字符集合中的任意一个字符
[^…] 匹配不在括号中的任何字符
{n} 匹配前面的字符串 n 次
{n,m} 匹配前面的字符串至少 n 次，至多m 次
语法格式

select * from 表名 where 要查询的位置 regexp '正则表达式';

部分实例
匹配开始字符

匹配结束字符

匹配单个字节 用. 来匹配这个字节

匹配多个字符

匹配字符串

运算符

MySQL的运算符为4种 算数运算符 比较运算符 逻辑运算符 位运算符

算数运算符

除法运算时 除数不可为0 若除数为0，这返回结果为null
多个运算符运算时 遵循先乘除后加减来运算
语法格式
select 要运算的值
实例

比较运算符

数值的比较不是比较数值的本身，而是比较数值对应的ascll表 如果在ascll表里 两个数值相等 那么这两个字符就是相等的 同理可得 大小也是比较ascll表的值。

= 比较两个数值的大小 根据返回值来判断是否相等 0为真 1为假
比较的都是整数，则会进行整数的比较
比较的是一个字符串和整数 则会把字符串换算后在进行比较
如果有一个值是null值，那么返回的就是null值
都是字符串的话就按照字符串计算

!=不等于，<> 表达不相等的关系
null无法比较不相等 同理=看返回值

小于 大于 小于等于 大于等于
也是看返回值 null值不可用于比较

判断一个是否为null值

逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回1，否则返回0，真和假也可以用TRUE和FALSE表示。
MySQL中支持使用的逻辑运算符有四种
not 或 ! ：逻辑非
and 或 && ：逻辑与
or ：逻辑或
xor ：逻辑异或
非（not）
逻辑非将跟在他后面的值取反，如果NOT后面的操作数为0时，所得值为1
如果操作数为非0时，所得值为0
如果操作数为NULL时，所得值为NULL

与（and）
当所有操作数都为非零值并且不为NULL时，返回值为1
当一个或多个操作数为0时，返回值为0
操作数中有任何一个为NULT时，返回值为NULL

或（or）
当两个操作数都为非NULL值时，如果有任意一个操作数为非零值，则返回值为1，否则结果为0
当有一个操作数为NULL时，如果另一个操作数为非零值，则返回值为1，否则结果为NULL
假如两个操作数均为NULL时,则返回值为NULL。

异（xor）
当任意一个操作数为NULL时，返回值为NULL
对于非NULL的操作数，如果两个操作数都是非0值或者都是0值，则返回值为0
如果一个为0值，另一个为非0值，返回值为1

位运算符

优先级

！
~
^
*、/、%
+、-
<<、>>
&
|
=、<=>、>=、>、<=、<、<>、!=、like、regexp、in
between、case、when、then、else
not
&&、and
or、xor
:=

五 连接查询

MysQL的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。
首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括：内连接、左连接和右连接

1 内连接

MySQL中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。
通常在 from 子句中使用关键字 inner join 来连接多张表，并使用 on 子句设置连接条件
内连接是系统默认的表连接，所以在 from 子句后可以省略 inner 关键字，只使用关键字 join ，同时有多个表时，也可以连续使用 inner join 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
语法

select 字段 from 1表名 a inner join 2表名 b on a.表一字段=b.表2字段；

2 左连接

左连接也可以被称为左外连接，在 from 子句中用 left join 或者 left out join 关键字来表示。
左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。
左连接中，左表的记录会全部表示出来，而右表只会显示符合搜索条件的记录，不足的地方均为 null
语法

select 字段 from 1表名 a left join 2表名 b on a.表一字段=b.表2字段

3 右连接

右连接也被称为右外连接，在 from 子句中用 right join 或者 right out join 关键字来表示。
右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配
语法

select 字段 from 1表名 a right join 2表名 b on a.表一字段=b.表2字段

六 函数

Mysql 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出，类似excel表格的函数
Mysql 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数

1 数学函数

abs(x) 返回 x 的绝对值
rand() 返回 0 到 1 的随机数
mod(x,y) 返回 x 除以 y 以后的余数
power(x,y) 返回 x 的 y 次方
round(x) 返回离 x 最近的整数
round(x,y) 保留 x 的 y 位小数四舍五入后的值
sqrt(x) 返回 x 的平方根
truncate(x,y) 返回数字 x 截断为 y 位小数的值
ceil(x) 返回大于或等于 x 的最小整数
floor(x) 返回小于或等于 x 的最大整数
greatest(x1,x2…) 返回集合中最大的值
least(x1,x2…) 返回集合中最小的值
语法

select abs(5),rand(),power(4,12),sqrt(8);

2 聚合函数

特意为库内记录求和或者对表中的数据进行几种概括而设计的，这些函数被称作聚合函数
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum(x) 返回指定列的所有值之和
语法

select 函数（要处理的字段） from 表名 

3 字符串函数

trim() 返回去除指定格式的值
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
length(x) 返回字符串 x 的长度
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
upper(x) 将字符串 x 的所有字母变成大写字母
lower(x) 将字符串 x 的所有字母变成小写字母
left(x,y) 返回字符串 x 的前 y 个字符
right(x,y) 返回字符串 x 的后 y 个字符
repeat(x,y) 将字符串 x 重复 y 次
space(x) 返回 x 个空格
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
reverse(x) 将字符串 x 反转
语法

select 函数（要操作的字段）；

4 时间函数

curdate() 返回当前时间的年月日
curtime() 返回当前市价你的时分秒
now() 返回当前时间的日期和时间
month(x) 返回日期x中的月份值
week(x) 返回日期x是年度的第几个周
hour(x) 返回x中的小时值
minute(x) 返回日期x中的分钟值
second(x) 返回日期x中的秒数值
dayotweek(x) 返回x是星期几，1为星期日，2为星期一
replace(x,y,z) 将字符z替代字符串x中的字符串y
dayotmonth(x) 计算日期x是本月的第几天
dayotyear(x) 计算日期x是本年的第几天
语法

select 函数（要查询的函数）

七 存储过程

概念

MysQL数据库存储过程是一组为了完成特定功能的SQL语句的集合。
存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。
操作数据库的传统SQL语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高。存储过程在数据库中创建并保存，它不仅仅是SQL语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。

特点

执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
SQL语句加上控制语句的集合，灵活性高
在服务器端存储，客户端调用时，降低网络负载
可多次重复被调用，可随时修改，不影响客户端调用
可完成所有的数据库操作，也可控制数据库的信息访问权限

语法

DELIMITER !!    				#将语句的结束符号从分号；临时修改，以防出问题，可以自定义
CREATE PROCEDURE XXX()  	#创建存储过程，过程名自定义,（）可带参数
 	BEGIN   		    	#过程体以关键字BEGIN开始
 	select * from xxx;  	#过程体语句
 	END!!    				#过程体以关键字END结尾
DELIMITER ; 			    	#将语句的结束符号恢复为分号

call XXX;	  				#调用存储过程

====查看存储过程====
show create procedure [数据库.]储存过程名; 	  #查看某个储存过程的具体信息
show create procedure XXX;
show procedure status [like '%XXX%'] \G

查看存储过程
语法

show create procedure 过程名/G