mysql高级语句
mysql进阶查询
按关键字排序
使用order by语句来实现
排序可针对一个或多个字段
asc：升序，默认排序方式
desc：降序
语法结构
select colum1，column2，。。。from table_name order by column1 desc|asc ,column2 desc|asc........;步骤
单字段排序
select 按顺序显示想要显示的元素 from 表名 order by 排序的元素 asc|desc；
select name,score from b order by score asc; #显示排序的name和score列，score按照升序排序
select name,score from b order by score desc; #降序
多字段排序
主要参考字段写在前面，辅助参考字段写在后面。先比较主要参考字段，如果相同，再比较辅助参考字段
select name,score from b order by score desc,name desc; #将表b，score字段降序排序，name升序排序
对结果进行分组
使用group by语句来实现分组
通常结合聚合函数一起使用
可以按一个或多个字段对结果进行分组
group by的语法结构
select count(score),score from b where score>=83 group by score desc; #对表b内score值大于等于83的数据进行降序排序，并计数
限制结果条目
只返回select查询结果的第一行或前几行
使用limit语句限制条目
limit语法结构
select 字段1，字段2 from 表名 limit [offset,] number；
offset：位置偏移量，从0开始
number：返回记录行的最大数目
select name,score from b order by score desc limit 3; #按照score进行降序排序，只显示前3行
select name,score from b order by score desc limit 2,3;#按照score进行降序排序，只显示从第2行起的前3行
设置别名
使用as语句设置别名，关键字as可省略
设置别名时，保证不能与库中其他表或字段名称冲突
设置字段别名
select 字段 as 别名 from 表名；
select name as id from b; #将表b的字段name设置别名为id
设置表的别名
select bb.name from b as bb; 
表的别名与字段的别名联用
在数据库a下创建一个表c
create table c(name varchar(128), id int(4)); #在数据库a下创建一个表c
insert into c values('p',1); #加入数据
insert into c values('ph',2);
insert into c values('a',3);
insert into c values('b',4);
insert into c values('c',5);
insert into c values('d',6);
insert into c values('a',7);
select * from c; #查看表c
select p.name as 姓名,p.score as 成绩,h.id 序号 from b as p inner join c as h on p.name=h.name;
通配符
用于替换字符串中的部分字符
通常配合like一起使用，并协同where完成查询
常用通配符
%表示零个、一个或多个即任意字符
_表示单个字符
mysql> select 查看的字符段 from 表名 where 匹配字符 like ‘_a%’;
select * from b where name like 'p%'; 
子查询
也称作内查询或者嵌套查询
先于主查询被执行，其结果将作为外层主查询的条件
在增删改查中都可以使用子查询
支持多层嵌套
in语句用来判断某个值是否在给定的结果集中
select name,score from b where name in(select name from b where score>83);
select name,score from b where name =(select name from b where scorre=90);
select name,score from b where name !=(select name from b where scoore=90);
select name,score from b where name <>(select name from b where scorore=90);
#使用=时，后面的值只能为一个，用!=和<>不匹配字符是同样如此
布尔值是“真” True 或“假” False 中的一个。动作脚本也会在适当时将值 True 和 False 转换为 1 和 0。布尔值经常与动作脚本语句中通过比较控制脚本流的逻辑运算符一起使用。
多层嵌套
select多层嵌套
update b set score=score+1 where name in(select b.name from (select name from b where score<90)b);
select * from b;
delete的多层嵌套
delete from b where name in (select b.name from(select name from b where score=82)b);
select * from b;
exists判断
判断后面结果是否存在，若不存在，则不执行前面的命令，存在则执行
select * from b where exists (select * from b where score=80);
select * from b where exists (select * from b where score=84);
NUULL值
null：不存在任何值或符号，也不存在空格
‘’：存在空格
表示缺失的值
与数字0或者空白（space）是不同的
使用is null或is not null进行判断null值和空值（‘’）的区别
空值长度为0，不占空间；null值的长度为null，占用空间
is null无法判断空值
空值使用“=”或者“<>”来处理
count()计算时，null会忽略，空值会加入计算
alter table b add column aaa int(3);
update b set aaa='1' where score=84;
select * from b;
select count(aaa) from b;
select count(id) from b;
select * from b where aaa is null;
select * from b where aaa is not null;
正则表达式
根据指定的匹配模式匹配记录中符合要求的特殊字符
使用regexp关键字指定匹配模式
常用匹配模式
不同的匹配示例
select * from b where score regexp '^9'; #表b的score字段，以9开头的数据
select * from b where score regexp '4$'; #表b的score字段，以4结尾的数据
select * from b where name regexp 'p'; #表b的name字段，包含p的数据
select * from b where name regexp '.h'; #表b的name字段，h前包含一个字符的数据
select * from b where score regexp '.4|9.'; #表b的score字段，4前包含一个字符或9后包含一个字符的数据
select * from b where name regexp 'ph*';#表b的name字段，p后面的h出现任意次数的数据
select * from b where name regexp 'ph+';#表b的name字段，p后面的h出现一次以上的数据
select * from b where name regexp '[a-d]'; #表b的name字段,包含在【a-d】的数据
运算符
用于对记录中的字段值进行运算
MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符## 算数运算符
+加法
-减法
*乘法
/除法
%取余
用select实现
整数相除，出来的结果是浮点型的。
在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。
需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算
注意：某些字符串类型的字段存储的数字型字符串，这些字段在进行算术运算时将会被自动转换为数字的值。如果字符串的开始部分是数字，在转换时将被转换为这个数字。如果是既包含字符又包含数字得的混合字符串，无法转换为数字时，将被转换为 0
比较运算符
=等于
<小于
>大于
<=小于等于
>=大于等于
!=或<>不等于
is null 是否为null
is no null是否不为null
between and 二者之间
in 在集合中
like 通配符匹配
greatest 两个及以上参数时返回最大值
least 两个及以上参数时返回最小值
regext 正则表达式
比较运算规则：
如果两者都是整数，则按照整数值进行比较。
如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较。
如果两者都是字符串，则按照字符串进行比较。
如果两者中至少有一个值是 NULL，则比较的结果是 NULL。成立返回1，不成立返回0
逻辑运算符
又被称为布尔运算符，用来判断表达式的真假
NOT或！—非
AND或&& —与
OR或I —或
XOP —异或
非
逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。
如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。
注意：非0值都是1
与
只有所有值都为真，返回为1，否则返回0
或
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。
异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一个为非 0，则返回结果为 1；
当任意一个值为 NULL 时，返回值为 NULL。
位运算符
& 按位与 是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0
| 按位或 是对应的二进制位只要是 1 的，它们的运算结果就为 1，否则为 0
~ 按位取反 是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1
^ 按位异或 是对应的二进制位不相同时，运算结果 1，否则为 0
<< 按位左移
>> 按位右移
常用的运算符优先级
！
~
^
*,/,%
+,-
<<,>>
&
|
=,<=,>=,<,>,!=,<>,like,regexp,in
between,case,when,then,else
not
&&,and
II,or,xor
:=
连接查询
通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。
要先确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。
使用较多的连接查询包括：内连接、左连接和右连接
内连接在
from子句中使用关键字 inner join 来连接多张表，并使用 on子句设置连接条件。
select b.name,score,c.id from c inner join b on b.name=c.name;
内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。
同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。
外连接
左连接
主表在左边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来
select b.name,score,c.id from b left join c on b.name=c.name;右连接
主表在右边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来
select b.name,score,c.id from b right join c on b.name=c.name;
mysql数据库函数
常用的函数分类
数学函数
聚合函数
字符串函数
日期时间函数
常用的数学函数
abs(x) 返回x的绝对值
rand() 返回0-1的随机数
rand()*10 返回0-10的随机数
mod(x,y) 返回x除以y以后的余数
power(x,y) 返回x的y次方
round(x) 返回离x最近的整数
roundI(x,y) 保留x的y位小数四舍五入后的值
sqrt(x) 返回x的平方根
truncate(x,y) 返回数字x截断为y位小数的值
ceil(x) 返回大于或等于x的最小整数
floor(x) 返回小于或等于x的最大整数
greatest(x1,x2…) 返回集合中最大的值
least(x1,x2…) 返回集合中最小的值
聚合函数
对表中数据记录进行集中概括而设计的以类函数
常用的聚合函数
avg() 返回指定列的平均值
count() 返回指定列中非null在值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum()返回指定列的所有值的和
字符串函数
常用的字符串函数
length() 返回字符串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中的字符串y
strcmp(x,y) 比较x和y，返回的值可以是-1,0,1
substring(x,y,z) 获取从字符串x中的第y个位置开始长度为z的字符串
reverse(x) 将字符串x反转
日期函数
常用的日期函数
curdate() 返回当前时间的年月日
curtime() 返回当前时间的时分秒
now() 返回当前时间的日期和时间
month(x) 返回x日期的月份
week(x) 返回x日期的年度第几周
hour(x) 返回日期x中的小时值
minute(x) 返回日期x中的分钟值
second(x) 返回日期x中的秒数值
dayofweek(x) 返回x是周几
dayofmonth(x) 返回x是本月的第几天
dayofyear(x) 返回x是本年的第几天
存储过程
简介
是一组为了完成特定功能的SQL语句集合
比传统SQL速度更快，执行效率更高
存储过程的主体部分，被称为过程体
以BEGIN开始，以END结束，可只有一条SQL语句
以DELIMITER开始和结束
存储过程的优点
执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
SQL语句加上控制语句的集合，灵活性高
在服务器端存储，客户端调用时，降低网络负载
可多次重复被调用，可随时修改，不影响客户端调用
可完成所有的数据库操作，也可控制数据库的信息访问权限
参数
IN输入参数 表示调用者向过程传入值（传入值可以是字面量或变量)
OUT输出参数 表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT输入/输出参数 既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
创建存储过程
使用CREATE PROCEDURE语句创建存储过程
创建存储过程的语法结构
CREATE PROCEDURE <过程名> （过程参数[...]）<过程体> [过程参数[...]] 格式 [IN|OUT|INOUT] <参数名> <类型>
mysql> delimiter $$
mysql> create procedure a()
-> begin
-> select * from b;
-> end $$
mysql> delimiter ;
mysql> call a();
use a;
delimiter $$
mysql> create procedure ph(in xi int,out xo int,inout xio int)
-> begin
-> select xi,xo,xio;
-> set xi=1,xo=2,xio=3;
-> select xi,xo,xio;
-> end $$
mysql> delimiter ;
mysql> set @xi=10,@xo=20,@xio=30;
mysql> call ph(@xi,@xo,@xio);
xi在存储过程中被修改，当定义了变量并把值带入进去后，第一个查询出的是外面定义变量的值，而后在存储过程中被修改，所以值变为了1。
因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的xo为null,调用了out_param存储过程，输出参数，改变了xo变量的值
调用了存储过程，接受了输入的参数30，也输出参数3，改变了变量
修改存储过程
存储过程的修改分为特征修改和内容修改
特征修改的方法
ALTER PROCEDURE <过程名> [<特征>…]
内容修改可先删除原有存储过程，之后再创建方法
删除存储过程
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
drop procedure a;
call a();
635
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
