MySQL高级SQL语句操作
按关键字排序
使用ORDER BY语句来实现排序
排序可针对一个或多个字段
ASC:升序默认排序方式
DESC:降序
ORDER BY的语法结构
SELECT column1, column2,... FROM table_name ORDER BY column1, column2,...ASC|DESC;
按单字段排序
按多字段排序
使用 ORDER BY 进行字段查询
select name,age from aa order by age desc;
select name,age from aa order by age;
使用 ORDER BY 进行多字段查询
select age,name,height from aa order by age desc,height desc;
select age,name,height from aa order by age,height ;
对结果进行分组
使用GROUP BY语句来实现分组
通常结合聚合函数一起使用
可以按一个或多个字段对结果进行分组
GROUP BY分组示例
select name,age from aa where height >=200 group by age;
GROUP BY分组示例
select name,age from aa where height >=200 group by age order by count(name) desc;
限制结果条目
只返回SELECT查询结果的第一行或前几行
使用LIMIT语句限制条目
LIMIT语法结构
SELECT column1, column2,... FROM table_name LIMIT [offset,] number;
LIMIT限制结果条数
不从第一条开始取值
以此表进行限制条目操作
限制结果的行数
限制表的前 2行
select * from aa limit 2;
不从第一条开始取值
select * from aa limit 1,3;
设置别名
使用AS语句设置别名,关键字AS可省略
设置别名时,保证不能与库中其他表或字段名称冲突
别名的语法结构
SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name AS alias_name;
AS的用法
select count(*) as number from player;
select p.id,p.name from player as p limit 1;
AS作为连接语句
create table tmp as select * from player;
AS 的第一种操作
select count(*) as num from aa;
AS 的第二种操作
设置别名，id 设为 aa，height 设为 bb，表 aa 设为 表 aaa
select id aa,height bb,from aa as aaa;
+----+--------+
| aa | | bb |
+----+--------+
| 2 | 200 |
| 3 | 300 |
| 4 | 400 |
+----+--------+
select id aa,height bb from a as aa where aa.height>=200;
+----+--------+
| aa | | bb |
+----+--------+
| 3 | 300 |
| 4 | 400 |
+----+--------+
AS作为连接语句
复制表的结构以及数据
create table bb as select * from aa;
select * from bb;
通配符
用于替换字符串中的部分字符
通常配合 LIKE 一起使用，并协同 WHERE 完成查询
常用通配符
%表示零个、一个或多个
_表示单个字符
通配符%的用法
select id,name,level from player where name like "%s";
通配符_的用法
select id,name,level from player where name like '_uess";
使用 % 进行检索查询
查询表里以 Z 开头的 name
select * from aa where name like 'z%';
查询表里以 n 结尾的 name
select * from aa where name like '%n';
通配符_的用法
查询 name 里的单个字符
select * from aa where name like 'zhangs_n';
子查询
也称作内查询或者嵌套查询
先于主查询被执行，其结果将作为外层主查询的条件
在增删改查中都可以使用子查询
支持多层嵌套
IN语句是用来判断某个值是否在给定的结果集中
子查询的用法
查询条件
select name,level from player where id in (select id from player where level>=45);
插入数值
insert into tmp select * from player where id in (select id from player；
创建别名查询
update tmp set level = level -7 where id in (select a.id from (select id from tmp where level >= 47) a);
删除符合条件后查询
delete from tmp where id in (select a.id from(select id from tmp where level=47) a);
EXIST 关键字子查询
EXIST这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回 TRUE;反之，则返回 FALSE。例如，先通过子查询判断返回是否为 TRUE，如果用户shirley存在，则计算整个tmp表的总记录数量
select count(*) as number from tmp where EXISTS (select id from tmp where name='shirley);
查询列表信息
查询 name 和 height 两列并列出 height 大于 180 的
select name,height from aa where height in (select height from aa where height >=200);
查询 name 和 height 两列并列，后面不添加条件
select name,height from aa where height in (select height from aa where height );
插入数值
重新复制表 aa 的表结构，为表 cc
create table cc like aa;
符合条件的数值从表 aa 中 复制给 表 cc
将表 aa 中的 height 大于等于 100 的插入给表 cc
insert into cc select * from aa where height in (select height from aa where height >=100);
定义别名根据条件查询
定义别名为 bb ，查询 height 大于等于 200 的以 name 和 height 列表显示
select bb.name,bb.height from (select name,height from aa where height>=200) as bb;
使用 update 对 bb 表里符合条件的 height 的数值加 10 （条件为 height 值大于等于200）
update bb set height=height+10 where height in(select bb.height from (select height from bb where height>=200) as bb);
删除符合条件后删除
删除 height 值大于等于 100 小于等于 200 的值将被删除
delete from bb where height in (select height from (select height from bb where height >=100 and height<=200)bb);
NULL值
表示缺失的值
与数字0或者空白(spaces)是不同的
使用IS NULL或IS NOT NULL进行判断
NULL值和空值的区别
空值长度为0，不占空间;NULL值的长度为NULL，占用空间
IS NULL无法判断空值
空值使用“=”或者“<>”来处理
COUNT()计算时，NULL会忽略，空值会加入计算
测试 NULL 值为空
添加 NULL
insert into bb values(default,default,default,default,default);
正则表达式
根据指定的匹配模式匹配记录中符合要求的特殊字符
使用REGEXP关键字指定匹配模式
常用匹配模式
^匹配开始字符
p1|p2匹配p1或p2
$匹配结束字符
[…]匹配字符集中的任意一个字符
.匹配任意单个字符
[^…]匹配不在中括号内的任何字符
*匹配任意个前面的字符
{n}匹配前面的字符串n次
＋匹配前面字符至少1次
{n,m} 匹配前面的字符串至少n次,至多m次
查询 name 段以 w 开头的数据
select * from aa where name REGEXP '^z';
查询 name 段以 n 结尾的数据
select * from aa where name REGEXP 'n$';
查询 name 段的任意字符
select * from aa where name REGEXP '[a-z]';
查询不在括号内的任何字符
select * from aa where name REGEXP '[^a-z]';
查询 name 段中至少有一个 i 的数据
select * from aa where name REGEXP 'i{1}';
查询 name 段中至少有两个 i 的数据
select * from aa where name REGEXP 'i{2}';
查询 name 段某个范围的数据
select * from aa where name REGEXP 'zhangsan|zhaoliu';
查询 name 段中至少有一个 m 的
select * from aa where name REGEXP 'z+';
运算符
用于对记录中的字段值进行运算
运算符分类
算术运算符
比较运算符
逻辑运算符
位运算符
算术运算符
MySQL支持的算术运算符
+加法
-减法
*乘法
/除法
%取余数
示例
加法
select 6+6;
多个算数在一起
select 2+2,3-2,1*2,3/3,2*3;
查看算数表结构
创建表
create table xx select 2+2,3-2,1*2,3/3,2*3;
比较运算符
字符串的比较默认不区分大小写,可使用binary来区分
常用比较运算符
= 等于
< 小于、>大于
<= 小于等于、>=大于等于
!=或 <> 不等于
IN 在集合中
LlKE 通配符匹配
IS NULL 判断一个值是否为NULL
IS NOT NULL判断一个值是否不为NULL
BETWEEN AND 两者之间
GREATEST两个或多个参数时返回最大值
LEAST 两个或多个参数时返回最小值
注释
等号 =
是用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0
如果比较的两者有一个值是NULL，则比较的结果就是NULL
其中字符的比较是根据ASCII码来判断的，如果 ASCII码相等，则表示两个字符相同;如果ASCII码不相等，则表示两个字符不相同
select 1=2,3=3,5='5','a'='b';
结论
如果两者都是整数，则按照整数值进行比较
如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较
如果两者都是字符串，则按照字符串进行比较
如果两者中至少有一个值是 NULL，则比较的结果是 NULL
不等于运算符
不等于号有两种写法，分别是<>或者=，用于针对数字、字符串和表达式不相等的比较
如果不相等则返回1，如果相等则返回0，这点正好跟等于的返回值相反
需要注意的是不等于运算符不能用于判断 NULL
select 3!=5,3!=3,9<>'9','a'<>'b','a'<>'a';
大于、大于等于、小于、小于等于运算符
大于(>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1，否则返回0，同样不能用于判断NULL
小于(<) 运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回1，否则返回0，同样不能用于判断NULL
大于等于(>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，不能用于判断NULL
小于等于(<=) 判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，不能用于判断NULL
select 6>9;
select 6<9;
select 6>=9;
select 6<=9;
select 2>NULL;
select 2=NULL;
select 2>=NULL;
IS NULL、IS NOT NULL
IS NULL判断一个值是否为 NULL，如果为 NULL返回1，否则返回0
IS NOT NULL判断一个值是否不为NULL，如果不为 NULL返回1，否则返回0
select 10 is NULL;
select 10 is not NULL;
BETWEEN AND
BETWEEN AND比较运算通常用于判断一个值是否落在某两个值之间
例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间
5 在3 与 6 之间
select 5 between 3 and 6;
7 不在 3 与 6 之间
select 7 between 3 and 6;
b 在 a 与 c 之间
select 'b' between 'a' and 'c';
c 不在 a 与 b 之间
select 'c' between 'a' and 'b';
LEAST、GREATEST
LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为NULL，则返回结果就为NULL
GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为NULL，则返回结果就为NULL
比较最小值
select least(1,2,3);
select least('a','b','c');
比较最大值
select greatest(1,2,3);
select greatest('a','b','c');
LEAST 比较的参数为数字时，返回的是其中最小的值；当比较的参数为字符串时，返回字母表中顺序最靠前的字符
GREATEST比较的参数为数字时，返回的是其中最大的值；当比较的参数为字符串时，返回字母表中顺序最靠后的字符
判断 1 是否在数值里面，为真则返回 1
select 1 in (1,2,3);
判断 4 是否在数值里面，为假则返回 0
select 4 in (1,2,3);
判断 3 不在数值里面，为假则返回 0
select 3 not in (1,3,5);
判断 7 不在数值里面，为真则返回 1
select 7 not in (1,3,5);
LIKE、NOT LIKE
LIKE 用来匹配字符串，如果匹配成功则返回1，反之返回O
LIKE 支持两种通配符：‘%’ 用于匹配任意数目的字符；而 ‘_’ 只能匹配一个字符
NOT LIKE正好跟LlKE 相反，如果没有匹配成功则返回1，反之返回0
select 'abcd' like 'ab';
‘%’ 用于匹配任意数目的字符
select 'abcd' like '%d';
‘_’ 用于只能匹配一个字符
select 'abcd' like 'ab_d';
逻辑运算符
逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假
如果为真返回1，否则返回0，真和假也可以用 TRUE 和 FALSE 表示
MySQL 中支持使用的逻辑运算符有四种
| 运算符 | 描述 |
|---|---|
| NOT或！ | 逻辑非 |
| AND或&& | 逻辑与 |
| OR或II | 逻辑或 |
| XOR | 逻辑异或 |
逻辑非
逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用NOT或!表示
逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真
如果 NOT后面的操作数为0时，所得值为1；如果操作数为非0时，所得值为0；如果操作数为NULL时，所得值为NULL
not 或 ！
9 为真非 0 值；为 1，1 取反就是 0
相反为真
select !0；
AND 或 &&
5 为非 0 值，统一为真，真的话就是 1
select 5 or 0;
5 和 3 都为非 0 值，两个真值相与便为真
select 5 && 3;
OR 或 ||
两个数值类型都为非 0 值，为真
select 5 or 4;
有一个数值类型为真便为真
select 5 or 0;
XOR
select 3 xor 0;
两个数值类型都为真，为假
select 3 xor 2;
两个非 0 值也为假
select 0 xor 0;
异或：同为真或同为假都为假，一个真一个假时才为真
逻辑或
逻辑或表示包含的操作数，任意一个为非零值并且不是NULL值时，返回1，否则返回0
逻辑或通常使用 OR 或者 || 来表示
逻辑异或
两个非 NULL值的操作数，如果两者都是О或者都是非0，则返回0
如果一个为0，另一个为非0，则返回结果为1
当任意一个值为NULL 时，返回值为 NULL
位运算符
位运算符实际上是对二进制数进行计算的运算符
MySQL内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看
MySQL支持6种位运算符
| 运算符 | 描述 |
|---|---|
| & | 按位与 |
| I | 按位或 |
| ~ | 按位取反 |
| ^ | 按位异或 |
| << | 按位左移 |
| >> | 按位右移 |
按位与
5 的二进制为 101；3 的二进制位 011，相乘得出 1
select 5 & 3;
按位或
select 5 | 3;
按位取反
select ~4;
按位异或
5 为101，3 为 011，1 与 0 异或为 1；0 与 1 异或为 1；1 与 1 异或为 0，结果为 110，换算成 6
select 5 ^ 3;
按位左移
5 为 101，左移两位为 10100，换算成 20
select 5 << 2;
3 为 011，左移两位为 01100，换算成 12
select 3 << 2;
按位右移
10 为 1010，右移两位为 10，换算成 2
select 10 >> 2;
运算符的优先级
决定了不同的运算诱在计算过程中的先后顺序
优先级高的先进算。同级的按从左到右进行计算
可以使用()小括号来改变计算优先级
表的连接方式
内连接
两张或多张表中同时符合某种条件的数据记录组合
FROM子句中使用INNER JOIN关键字连接多张表，并使用ON设置连接条件
是系统默认的表连接方式,可以省略INNER关键字
多表支持连续使用 INNER JOUN，建议不超过三个表
语法结构
SELECT column_name(s)FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
新建两张表
create table qq(id int(1),height int(3));
create table ww(id int(1),weight int(3));
往两张表里添加数据
insert into qq values(1,10),(2,20),(3,30),(4,40),(5,50);
insert into ww values(1,10),(2,20),(3,30),(8,80),(9,90),(10,100);
将两张表内连接
select * from qq inner join ww on qq.id=ww.id;
外连接
左连接
**也被称为左外连接
在FROM子句中使用LEFT JOIN关键字来表示
匹配左表中所有行及右表中符合条件的行
实现原理**
将内连接创建的两张表左连接
select * from qq left join ww on qq.id=ww.id;
右连接
也被称为右外连接
在FROM子句中使用RIGHT JOIN关键字来表示
匹配右表中所有行及左表中符合条件的行
实现原理
将内连接创建的两张表右连接
select * from qq right join ww on qq.id=ww.id;
数据库函数
MySQL提供了实现各种功能的函数
常用的函数分类
数学函数
聚合函数
字符串函数
日期时间函数
常用的数学函数
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(×1,x2…) 返回集合中最大的值
least(x1,×2…) 返回集合中最小的值
示例
abs(x) 返回x的绝对值
-5的绝对值为5
select abs(-5);
rand() 返回0到1的随机数
随机返回 0-1 的数
select rand();
mod(x,y) 返回x除以y以后的余数
得出的结果为1
power(x,y) 返回x的y次方
4 的 4 次方为 256
select power(4,4);
round(x) 返回离x最近的整数
离 5.4 最近的整数为 5
离 5.5 最近的整数为 6
select round(5.5);
（2）常用的聚合函数
1：对表中数据记录进行集中概括而设计的一类函数
2：常用的聚合函数
- avg() 返回指定列的平均值
- count() 返回指定列中非NULL值的个数
- min() 返回指定列的最小值
- max() 返回指定列的最大值
- sum() 返回指定列的所有值之和
示例
以此表为例
mysql> select * from a;
+------+------+----------+--------+--------+
| id | age | name | height | weight |
+------+------+----------+--------+--------+
| 1 | 16 | liming | 175 | 130 |
| 2 | 18 | zhangsan | 165 | 160 |
| 3 | 17 | wangwu | 170 | 140 |
| 4 | 18 | lisi | 180 | 120 |
| 5 | 19 | weipai | 190 | 180 |
| 6 | 20 | zhucii | 150 | 90 |
| 7 | 20 | maliu | 180 | 140 |
| 8 | 22 | wuhan | 190 | 170 |
| 9 | 19 | lisi | 168 | 130 |
+------+------+----------+--------+--------+
avg() 返回指定列的平均值
- 得出 height 字段的平均值
mysql> select avg(height) from a;
+-------------+
| avg(height) |
+-------------+
| 174.2222 |
+-------------+
1 row in set (0.00 sec)
count() 返回指定列中非NULL值的个数
- 得出 height 字段右 9 行
mysql> select count(height) from a;
+---------------+
| count(height) |
+---------------+
| 9 |
+---------------+
1 row in set (0.00 sec)
min() 返回指定列的最小值
- 得出 height 字段最小值为 150
mysql> select min(height) from a;
+-------------+
| min(height) |
+-------------+
| 150 |
+-------------+
1 row in set (0.00 sec)
max() 返回指定列的最大值
- 得出 height 字段最大值为 190
mysql> select max(height) from a;
+-------------+
| max(height) |
+-------------+
| 190 |
+-------------+
1 row in set (0.00 sec)
sum() 返回指定列的所有值之和
- 得出 height 字段总和为 1568
mysql> select sum(height) from a;
+-------------+
| sum(height) |
+-------------+
| 1568 |
+-------------+
1 row in set (0.00 sec)
（3）常用的字符串函数
- length(x)返回字符串x的长度
- trim()返回去除指定格式的值
- concat(x,y)将提供的参数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个空格
- replace(x,y,z)将字符串z替代字符串x中的字符串
- strcmp(x,y)比较x和y，返回的值可以为-1,0,1
- substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串
- reverse(x)将字符串x反转
示例
length(x)返回字符串x的长度
- 一共有 5 个字段
mysql> select length('12345');
+-----------------+
| length('12345') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.01 sec)
trim()返回去除指定格式的值
- 去除了结果的前面的空格
mysql> select trim('12345');
+---------------+
| trim('12345') |
+---------------+
| 12345 |
+---------------+
1 row in set (0.00 sec)
concat(x,y)将提供的参数x和y拼接成一个字符串
- 拼接成 123456
mysql> select concat('123','456');
+---------------------+
| concat('123','456') |
+---------------------+
| 123456 |
+---------------------+
1 row in set (0.00 sec)
upper(x)将字符串x的所有字母变成大写字母
- 大写字母为 H
mysql> select upper('h');
+------------+
| upper('h') |
+------------+
| H |
+------------+
1 row in set (0.00 sec)
lower(x)将字符串x的所有字母变成小写字母
- 小写字母为 h
mysql> select lower('H');
+------------+
| lower('H') |
+------------+
| h |
+------------+
1 row in set (0.00 sec)
left(x,y)返回字符串x的前y个字符
- 字符串前 3 位为 abc
mysql> select left('abcdefg',3);
+-------------------+
| left('abcdefg',3) |
+-------------------+
| abc |
+-------------------+
1 row in set (0.00 sec)
right(x,y)返回字符串x的后y个字符
- 字符串后 3 位为 efg
mysql> select right('abcdefg',3);
+--------------------+
| right('abcdefg',3) |
+--------------------+
| efg |
+--------------------+
1 row in set (0.00 sec)
repeat(x,y)将字符串x重复y次
- 字符串重复 3 次
mysql> select repeat('abcdefg',3);
+-----------------------+
| repeat('abcdefg',3) |
+-----------------------+
| abcdefgabcdefgabcdefg |
+-----------------------+
1 row in set (0.00 sec)
space(x)返回x个空格
- 长度不一样
mysql> select space(5);
+----------+
| space(5) |
+----------+
| |
+----------+
1 row in set (0.00 sec)
mysql> select space(35);
+-------------------------------------+
| space(35) |
+-------------------------------------+
| |
+-------------------------------------+
1 row in set (0.00 sec)
replace(x,y,z)将字符串z替代字符串x中的字符串
- 将字符串中的 1 替换 6
mysql> select replace('123456','1','6');
+---------------------------+
| replace('123456','1','6') |
+---------------------------+
| 623456 |
+---------------------------+
1 row in set (0.00 sec)
strcmp(x,y)比较x和y，返回的值可以为-1,0,1
- 返回值不同
mysql> select strcmp(2,3);
+-------------+
| strcmp(2,3) |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
mysql> select strcmp(2,2);
+-------------+
| strcmp(2,2) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> select strcmp(3,2);
+-------------+
| strcmp(3,2) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
substring(x,y,z)获取从字符串x中的第y个位置开始长度为z的字符串
- 字符串第 3 个开始的后 6 位数为 345678
mysql> select substring('123456789',3,6);
+----------------------------+
| substring('123456789',3,6) |
+----------------------------+
| 345678 |
+----------------------------+
1 row in set (0.00 sec)
reverse(x)将字符串x反转
- 将字符串倒过来
mysql> select reverse('123456789');
+----------------------+
| reverse('123456789') |
+----------------------+
| 987654321 |
+----------------------+
1 row in set (0.00 sec)
（4） 常用的日期时间函数
- curdate()返回当前时间的年月日
- curtime()返回当前时间的时分秒
- now()返回当前时间的日期和时间
- month(x)返回日期x中的月份值
- week(x)返回日期x是年度第几个星期
- hour(x)返回x中的小时值
- minute(x)返回x中的分钟值
- second(x)返回x中的秒钟值
- dayofweek(x)返回x是星期几，1星期曰，2星期—
- dayofmonth(x)计算曰期x是本月的第几天
- dayofyear(x)计算日期x是本年的第几天
示例
curdate()返回当前时间的年月日
- 当前日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-12-27 |
+------------+
1 row in set (0.00 sec)
curtime()返回当前时间的时分秒
- 当前时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:37:51 |
+-----------+
1 row in set (0.00 sec)
now()返回当前时间的日期和时间
- 当前日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-12-27 14:38:27 |
+---------------------+
1 row in set (0.00 sec)
month(x)返回日期x中的月份值
- 返回月份
mysql> select month('2020-12-27');
+---------------------+
| month('2020-12-27') |
+---------------------+
| 12 |
+---------------------+
1 row in set (0.00 sec)
week(x)返回日期x是年度第几个星期
- 为第 52 个星期
mysql> select week('2020-12-27');
+--------------------+
| week('2020-12-27') |
+--------------------+
| 52 |
+--------------------+
1 row in set (0.00 sec)
hour(x)返回x中的小时值
- 获取小时
mysql> select hour('6:30:55');
+-----------------+
| hour('6:30:55') |
+-----------------+
| 6 |
+-----------------+
1 row in set (0.00 sec)
minute(x)返回x中的分钟值
- 获取分钟
mysql> select minute('6:30:55');
+-------------------+
| minute('6:30:55') |
+-------------------+
| 30 |
+-------------------+
1 row in set (0.00 sec)
second(x)返回x中的秒钟值
- 获取秒
mysql> select second('6:30:55');
+-------------------+
| second('6:30:55') |
+-------------------+
| 55 |
+-------------------+
1 row in set (0.00 sec)
dayofweek(x)返回x是星期几，1星期曰，2星期—
- 礼拜天为 1
mysql> select dayofweek('2020-12-30');
+-------------------------+
| dayofweek('2020-12-30') |
+-------------------------+
| 4 |
+-------------------------+
1 row in set (0.00 sec)
dayofmonth(x)计算曰期x是本月的第几天
- 30 为本月的第 30 天
mysql> select dayofmonth('2020-12-30');
+--------------------------+
| dayofmonth('2020-12-30') |
+--------------------------+
| 30 |
+--------------------------+
1 row in set (0.00 sec)
dayofyear(x)计算日期x是本年的第几天
- 日期为今年的第 362 天
mysql> select dayofyear('2020-12-30');
+-------------------------+
| dayofyear('2020-12-30') |
+-------------------------+
| 365 |
+-------------------------+
1 row in set (0.00 sec)
二：存储过程
（1）简介
1：是一组为了完成特定功能的SQL语句集合
2：比传统SQL速度更快、执行效率更高
3：存储过程的优点
- 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
- SQL语句加上控制语句的集合，灵活性高
- 在服务器端存储，客户端调用时，降低网络负载
- 可多次重复被调用，将多条 SQL 封装到了一起，针对 SQL 可随时修改，不影响客户端调用
- 可完成所有的数据库操作，也可控制数据厍的信息访问权限
4：使用CREATE PROCEDURE语句创建存储过程
5：创建存储过程的语法结构
CREATE PROCEDURE <过程名>([过程参数[....]])<过程体>
[过程参数[,...]]格式
[ IN | OUT | INOUT ]<参数名><类型>
6：参数分为
- 输入参数:IN
- 输出参数:OUT
- 输入/输出参数:INOUT
（2）创建存储过程
1：存储过程的主体部分，被称为过程体
2：以BEGIN开始,以END结束，若只有一条SQL语句,则可以省略BEGIN-END
3：以DELIMITER 开始和结束
mysql> DELIMITER $$ //$$是用户自定义的结束符
//省略存储过程其他步骤
mysql> DELIMITER ;/分号前有空格
4：不带参数的存储过程
mysql> use xinxi $$
mysql> delimiter $$ # 结束符号定义为$$
mysql> create procedure cc() #创建存储过程为 aa
-> begin
-> create table xinxi.tt(name varchar(64),height int(3));
-> insert into xinxi.tt values('lisi',170),('wangwu',180);
-> select * from xinxi.tt；
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call cc();
+--------+--------+
| name | height |
+--------+--------+
| lisi | 170 |
| wangwu | 180 |
+--------+--------+
2 rows in set (0.01 sec)
5：带参数的存储过程
mysql> use xinxi $$
Database changed
mysql> delimiter $$
mysql> create procedure cc1(IN a varchar(64)) #参数类型为 IN 类型，参数名称为 a
-> begin
-> select * from tt where name=a;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call cc1('lisi');
+------+--------+
| name | height |
+------+--------+
| lisi | 170 |
+------+--------+
1 row in set (0.00 sec)
（3）修改存储过程
1：存储过程的修改分为特征修改和内容修改
2：特征修改的方法
ALTER PROCEDURE<过程名>[<特征>...]
3：内容修改可先删除原有存储过程，之后再创建的方法
（4）删除存储过程
1：删除存储过程的语法
DROP{ PROCEDURE|FUNCTION }[ IF EXISTS]<过程名>
2：删除的具体用法
mysql> drop procedure cc;
Query OK, 0 rows affected (0.00 sec)
mysql> call cc;
ERROR 1305 (42000): PROCEDURE xinxi.cc does not exist
mysql> call cc();
ERROR 1305 (42000): PROCEDURE xinxi.cc does not exist
存储过程 in、out、inout 参数示例与总结
mysql> set @num1=1,@num2=2,@num3=3;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure z(in num1 int,out num2 int,inout num3 int)
-> begin
-> select num1,num2,num3;
-> set num1=100,num2=200,num3=300;
-> select num1,num2,num3;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call z(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 1 | NULL | 3 |
+------+------+------+
1 row in set (0.00 sec)
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 100 | 200 | 300 |
+------+------+------+
1 row in set (0.00 sec)
总结1：in 和 inout 参数会将全局变量的值传入存储过程中，而 out 参数不会将全局变量的值传入存储过程中。在存储过程使用中，参数值 in out inout 都会发生改变。
mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
| 1 | 200 | 300 |
+-------+-------+-------+
1 row in set (0.00 sec)
1146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
