一 MySQL进阶查询
1.1 常用查询介绍
对MySQL数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理
1.1.1 按关键字查询
语法结构
SELECT column1, column2, … FROM table_name ORDER BY column1, column2, … ASC|DESC;
示例
##数据库内容表插入##
mysql> create database player;
Query OK, 1 row affected (0.00 sec)
mysql> use player;
Database changed
mysql> create table player (id int(4) not null,name varchar(10) not null,level int(3) not null,primary key (`id`));
insert into player (id,name,level) values ('30','抢宝真多呀',47);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into player (id,name,level) values ('30','抢宝真多呀',47);
insert into player (id,name,level) values ('15','新五皇·白胡子',46);
insert into player (id,name,level) values ('63','新五皇–敬神',46);
Query OK, 1 row affected (0.02 sec)
mysql> insert into player (id,name,level) values ('15','新五皇·白胡子',46);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('63','新五皇–敬神',46);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('199','D 丶狙击王',46);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('298','唐三',46);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('51','新五皇·暴雪',45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('272','D 丶抢人头辅助',45);
Query OK, 1 row affected (0.00 sec)
##查询等级大于等于 45 级的用户,并按降序进行排序##
mysql> select id,name,level from player where level >=45 order by level desc;
+-----+----------------------+-------+
| id | name | level |
+-----+----------------------+-------+
| 30 | 抢宝真多呀 | 47 |
| 15 | 新五皇·白胡子 | 46 |
| 63 | 新五皇–敬神 | 46 |
| 199 | D 丶狙击王 | 46 |
| 298 | 唐三 | 46 |
| 51 | 新五皇·暴雪 | 45 |
| 272 | D 丶抢人头辅助 | 45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)
##查询等级在 45 级及以上的用户,并以 level 降序排列和 id 降序排列##
mysql> select id,name,level from player where level >=45 order by level desc,id desc;
+-----+----------------------+-------+
| id | name | level |
+-----+----------------------+-------+
| 30 | 抢宝真多呀 | 47 |
| 298 | 唐三 | 46 |
| 199 | D 丶狙击王 | 46 |
| 63 | 新五皇–敬神 | 46 |
| 15 | 新五皇·白胡子 | 46 |
| 272 | D 丶抢人头辅助 | 45 |
| 51 | 新五皇·暴雪 | 45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)
说明:
ORDER BY 后面跟多个字段时,字段之间使用英文逗号隔开,
优先级是按先后顺序而定。下面以A和B分别表示两个字段。
●ORDER BY A,B desc 指A用升序,B用降序;
●ORDER BY A asc,B desc 指A用升序,B用降序;
●ORDER BY A desc,B desc 指A用降序,B用降序;
1.1.2 对结果进行分组
语法结构
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;
示例
##统计等级在 45 级及以上,以等级为分组,每个等级有多少人##
mysql> select count(name),level from player where level >=45 group by level;
+-------------+-------+
| count(name) | level |
+-------------+-------+
| 2 | 45 |
| 4 | 46 |
| 1 | 47 |
+-------------+-------+
3 rows in set (0.01 sec)
##查询等级在 45 级及以上, 按等级进行分组,并将每个等级的人数按降序排序##
mysql> select count(name),level from player where level >=45 group by level order by count(name) desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
| 4 | 46 |
| 2 | 45 |
| 1 | 47 |
+-------------+-------+
3 rows in set (0.00 sec)
1.1.3 限制结果条目
语法结构
SELECT column1, column2, … FROM table_name LIMIT [offset,] number
示例
##插入数据##
mysql> insert into player (id,name,level) values ('1','修欧拉卡',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('2','起风了',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('3','吊打低V',15);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('4','小花',14);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('5','小舞',35);
##查询表的前 3 个用户的信息##
mysql> select id,name,level from player limit 3;
+----+--------------+-------+
| id | name | level |
+----+--------------+-------+
| 1 | 修欧拉卡 | 10 |
| 2 | 起风了 | 10 |
| 3 | 吊打低V | 15 |
+----+--------------+-------+
3 rows in set (0.00 sec)
##查询记录按等级 level 降序排列,只取前三条记录##
mysql> select id,name,level from player order by level desc limit 3;
+----+----------------------+-------+
| id | name | level |
+----+----------------------+-------+
| 30 | 抢宝真多呀 | 47 |
| 15 | 新五皇·白胡子 | 46 |
| 63 | 新五皇–敬神 | 46 |
+----+----------------------+-------+
3 rows in set (0.00 sec)
##从第 3 条记录开始显示之后的 3 条数据##
mysql> select id,name,level from player limit 2,3;
+----+------------+-------+
| id | name | level |
+----+------------+-------+
| 3 | 吊打低V | 15 |
| 4 | 小花 | 14 |
| 5 | 小舞 | 35 |
+----+------------+-------+
3 rows in set (0.00 sec)
1.1.4 设置别名
语法结构
SELECT column_name AS alias_name FROM table_name;
表的别名语法结构
SELECT column_name(s) FROM table_name AS alias_name;
示例
##统计表内所有记录共有多少条时,使用 count(*), 这么写不便于识别,可以将其别名设置为 number##
mysql> select count(*) as number from player;
+--------+
| number |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)
mysql> select count(*) number from player; ##省略as是一样的结果
+--------+
| number |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)
##将 player 表的别名设置成 p##
mysql> select p.id,p.name from player as p limit 3;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 修欧拉卡 |
| 2 | 起风了 |
| 3 | 吊打低V |
+----+--------------+
3 rows in set (0.00 sec)
mysql> select p.id,p.name from player p limit 3; ##省略 as 是一样的结果
+----+--------------+
| id | name |
+----+--------------+
| 1 | 修欧拉卡 |
| 2 | 起风了 |
| 3 | 吊打低V |
+----+--------------+
3 rows in set (0.00 sec)
##实现用一条 SQL语句完成在创建表tmp的时候将player表内的数据写入 tmp 表##
mysql> create table tmp as select * from player;
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
1.1.5 通配符
常用的通配符
%:百分号表示零个、一个或多个字符
_:下划线表示单个字符
示例
##插入新数据##
mysql> insert into player (id,name,level) values ('238','sagou 轰总',7);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('795','senoku',15);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('2460','shirley',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into player (id,name,level) values ('448','useless',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('713','guess',25);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('1979','Theshy',24);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('2237','leslieF',3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('1735','oooooo',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into player (id,name,level) values ('2718','ooo',1);
Query OK, 1 row affected (0.00 sec)
##name 字段以 s 开头的记录##
mysql> select id,name,level from player where name like 's%';
+------+--------------+-------+
| id | name | level |
+------+--------------+-------+
| 238 | sagou 轰总 | 7 |
| 795 | senoku | 15 |
| 2460 | shirley | 1 |
+------+--------------+-------+
3 rows in set (0.00 sec)
##name 字段以 s 结尾的记录##
mysql> select id,name,level from player where name like '%s';
+-----+---------+-------+
| id | name | level |
+-----+---------+-------+
| 448 | useless | 1 |
| 713 | guess | 25 |
+-----+---------+-------+
2 rows in set (0.00 sec)
##name 字段中间含 es 的记录##
mysql> select id,name,level from player where name like '%es%';
+------+---------+-------+
| id | name | level |
+------+---------+-------+
| 448 | useless | 1 |
| 713 | guess | 25 |
| 1979 | Theshy | 24 |
| 2237 | leslieF | 3 |
+------+---------+-------+
4 rows in set (0.00 sec)
##替换开头的一个字符##
mysql> select id,name,level from player where name like '_uess';
+-----+-------+-------+
| id | name | level |
+-----+-------+-------+
| 713 | guess | 25 |
+-----+-------+-------+
1 row in set (0.00 sec)
##替换结尾的四个字符##
mysql> select id,name,level from player where name like 'use%';
+-----+---------+-------+
| id | name | level |
+-----+---------+-------+
| 448 | useless | 1 |
+-----+---------+-------+
1 row in set (0.00 sec)
##替换中间的一个字符##
mysql> select id,name,level from player where name like 'shi_ley';
+------+---------+-------+
| id | name | level |
+------+---------+-------+
| 2460 | shirley | 1 |
+------+---------+-------+
1 row in set (0.00 sec)
##name 字段中,开头有一个字符,接着是es两个字符,后面再跟着零个、一个或多个字符##
mysql> select id,name,level from player where name like '_es%';
+------+---------+-------+
| id | name | level |
+------+---------+-------+
| 2237 | leslieF | 3 |
+------+---------+-------+
1 row in set (0.00 sec)
1.1.6 子查询
语法结构
<表达式> [NOT] IN <子查询>
示例
##先查出等级大于等于45级的 ID,然后在判断player表内的ID是不是在这个结果集内,如果在就打印此行的名字和等级##
mysql> select name,level from player where id in (select id from player where level>=45);
+----------------------+-------+
| name | level |
+----------------------+-------+
| 新五皇·白胡子 | 46 |
| 抢宝真多呀 | 47 |
| 新五皇·暴雪 | 45 |
| 新五皇–敬神 | 46 |
| D 丶狙击王 | 46 |
| D 丶抢人头辅助 | 45 |
| 唐三 | 46 |
+----------------------+-------+
7 rows in set (0.00 sec)
##通过子查询实现将等级大于等于 47 的用户减去 7##
mysql> select id,name,level from tmp where id=30;
+----+-----------------+-------+
| id | name | level |
+----+-----------------+-------+
| 30 | 抢宝真多呀 | 47 |
+----+-----------------+-------+
1 row in set (0.00 sec)
mysql> update tmp set level = level - 7 where id in (select a.id from (select id from tmp where level >= 47) a);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select id,name,level from tmp where id=30; ##可以看到等级已经少了 7 级
+----+-----------------+-------+
| id | name | level |
+----+-----------------+-------+
| 30 | 抢宝真多呀 | 40 |
+----+-----------------+-------+
1 row in set (0.00 sec)
##查询用户等级不小于 45 级的用户##
mysql> select name,level from tmp where id not in (select id from tmp where level < 45);
+----------------------+-------+
| name | level |
+----------------------+-------+
| 新五皇·白胡子 | 46 |
| 新五皇·暴雪 | 45 |
| 新五皇–敬神 | 46 |
| D 丶狙击王 | 46 |
| D 丶抢人头辅助 | 45 |
| 唐三 | 46 |
+----------------------+-------+
6 rows in set (0.01 sec)
##查询出名字是 shirley 的记录,并输出其 ID、名字和等级信息##
mysql> select id,name,level from tmp where id = (select id from tmp where name='shirley');
+------+---------+-------+
| id | name | level |
+------+---------+-------+
| 2460 | shirley | 1 |
+------+---------+-------+
1 row in set (0.00 sec)
##先通过子查询判断返回是否为TRUE,如果用户shirley存在,则计算整个tmp表的总记录数量##
mysql> select count(*) as number from tmp where EXISTS (select id from tmp where name='shirley');
+--------+
| number |
+--------+
| 18 |
+--------+
1 row in set (0.00 sec)
1.2 NULL值
mysql>drop table test;
Query OK, 0 rows affected (0.01 sec)
mysql>CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`level` int(10) NOT NULL,
`coin` int(32),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##插入的记录中不包括 coin 字段##
mysql> insert into test(name,level) values('aa',10);
Query OK, 1 row affected (0.01 sec)
##插入的记录中包括 coin 字段##
mysql>insert into test(name,level,coin) values('ab',20,100);
Query OK, 1 row affected (0.00 sec)
##查询 coin 字段为空值的记录##
mysql>select * from test where coin is null;
+----+------+-------+------+
| id | NAME | level | coin |
+----+------+-------+------+
| 1 | aa | 10 | NULL |
+----+------+-------+------+
1 row in set (0.00 sec)
##查询coin 字段不为空的记录##
mysql> select * from test where coin is not null;
+----+------+-------+------+
| id | NAME | level | coin |
+----+------+-------+------+
| 2 | ab | 20 | 100 |
+----+------+-------+------+
1 row in set (0.00 sec)
1.3 正则表达式
MySQL 正则表达式通常是在检索数据库记录的时候,根据指定的匹配模式匹配记录中符合要求的特殊字符串。
MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式,REGEXP 操作符所支持的匹配模式
匹配模式 描述 实例
^ 匹配文本的开始字符 ‘^bd’ 匹配以 bd 开头的字符串
$ 匹配文本的结束字符 ‘qn$’ 匹配以 qn 结尾的字符串
. 匹配任何单个字符 ‘s.t’ 匹配任何s 和t 之间有一个字符的字符串
* 匹配零个或多个在它前面的字符 ‘fo*t’ 匹配 t 前面有任意个 o
+ 匹配前面的字符 1 次或多次 ‘hom+’ 匹配以 ho 开头,后面至少一个
m 的字符串字符串 匹配包含指定的字符串 ‘clo’ 匹配含有 clo 的字符串
p1|p2 匹配 p1 或 p2 ‘bg|fg’ 匹配 bg 或者 fg
[...] 匹配字符集合中的任意一个字符 ‘[abc]’ 匹配 a 或者 b 或者 c
[^...] 匹配不在括号中的任何字符 ‘[^ab]’ 匹配不包含 a 或者 b 的字符串
{n} 匹配前面的字符串 n 次 ‘g{2}’ 匹配含有 2 个 g 的字符串
{n,m} 匹配前面的字符串至少 n 次,至多m 次 ‘f{1,3}’ 匹配 f 最少 1 次,最多 3 次
1.3.1 以特定字符串开头的记录
mysql> select id,name,level from player where name REGEXP '^us';
+-----+---------+-------+
| id | name | level |
+-----+---------+-------+
| 448 | useless | 1 |
+-----+---------+-------+
1 row in set (0.00 sec)
1.3.2 以特定字符串结尾的记录
mysql> select id,name,level from player where name REGEXP 'ss$';
+-----+---------+-------+
| id | name | level |
+-----+---------+-------+
| 448 | useless | 1 |
| 713 | guess | 25 |
+-----+---------+-------+
2 rows in set (0.00 sec)
1.3.3 包含指定字符串的记录
mysql> select id,name,level from player where name REGEXP 'ok';
+-----+--------+-------+
| id | name | level |
+-----+--------+-------+
| 795 | senoku | 15 |
+-----+--------+-------+
1 row in set (0.00 sec)
1.3.4 以“.”代替字符串中的任意一个字符的记录
mysql> select id,name,level from player where name REGEXP 'shir.ey';
+------+---------+-------+
| id | name | level |
+------+---------+-------+
| 2460 | shirley | 1 |
+------+---------+-------+
1 row in set (0.00 sec)
1.3.5 匹配包含或者关系的记录
mysql> select id,name,level from player where name REGEXP 'ok|ss';
+-----+---------+-------+
| id | name | level |
+-----+---------+-------+
| 448 | useless | 1 |
| 713 | guess | 25 |
| 795 | senoku | 15 |
+-----+---------+-------+
3 rows in set (0.00 sec)
1.3.6 “*”匹配前面字符的任意多次
mysql> select id,name,level from player where name REGEXP 'oooo*';
+------+--------+-------+
| id | name | level |
+------+--------+-------+
| 1735 | oooooo | 1 |
| 2718 | ooo | 1 |
+------+--------+-------+
2 rows in set (0.00 sec)
1.3.7 “+”匹配前面字符至少一次
mysql> select id,name,level from player where name REGEXP 'oooo+';
+------+--------+-------+
| id | name | level |
+------+--------+-------+
| 1735 | oooooo | 1 |
+------+--------+-------+
1 row in set (0.00 sec)
1.3.8 匹配指定字符集中的任意一个
mysql> select id,name,level from player where name REGEXP '^[d-f]';
+-----+----------------------+-------+
| id | name | level |
+-----+----------------------+-------+
| 199 | D 丶狙击王 | 46 |
| 272 | D 丶抢人头辅助 | 45 |
+-----+----------------------+-------+
2 rows in set (0.00 sec)
##方括号“[]”指定了一个字符集合,只匹配其中的一个字符。“^”不仅可以放到左侧,也可以放到方括号内,放到左侧表示以这些字符集内的字符开头,而放到方括号内则表示不在指定的字符集合内的字符。例如“[^d-f]”表示除 d、e、f 以外的任何字符。
1.4 运算符
MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种,分别是:算术运算符、比较运算符、逻辑运算符和位运算符。
1.4.1 算术运算符
MySQL 支持使用的算术运算符:
运算符 描述
+ 加法
- 减法
* 乘法
/ 除法
% 取余数
##以 SELECT 命令来实现最基础的加减乘除运算##
mysql> select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;
+----------+-------------+----------------+----------+-----------+
| addition | subtraction | multiplication | division | remainder |
+----------+-------------+----------------+----------+-----------+
| 3 | 1 | 6 | 2.0000 | 1 |
+----------+-------------+----------------+----------+-----------+
1 row in set (0.00 sec)
1.4.2 比较运算符
比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出 表中有哪些记录是符合条件的,如果比较的结果为真则返回 1,如果为假则返回 0,比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的,如果要区分大小写可以通过 binary 关键字来实现。常用的比较运算符
运算符 描述 运算符 描述
= 等于 IS NOT NULL 判断一个值是否不为 NULL
> 大于 BETWEEN AND 两者之间
< 小于 IN 在集合中
>= 大于等于 LIKE 通配符匹配
<= 小于等于 GREATEST 两个或多个参数时返回最大值
!=或<> 不等于 LEAST 两个或多个参数时返回最小值
IS NULL 判断一个值是否为 NULL REGEXP 正则表达式
(1)等于运算符
mysql> select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=NULL;
+-----+-------+---------+-------------+----------+
| 2=4 | 2='2' | 'e'='e' | (2+2)=(3+1) | 'r'=NULL |
+-----+-------+---------+-------------+----------+
| 0 | 1 | 1 | 1 | NULL |
+-----+-------+---------+-------------+----------+
1 row in set (0.00 sec)
(2)不等于运算符
mysql> SELECT 'kgc'<>'bdqn', 1<>2, 3!=3, 2.5!=2, NULL<>NULL ;
+---------------+------+------+--------+------------+
| 'kgc'<>'bdqn' | 1<>2 | 3!=3 | 2.5!=2 | NULL<>NULL |
+---------------+------+------+--------+------------+
| 1 | 1 | 0 | 1 | NULL |
+---------------+------+------+--------+------------+
1 row in set (0.00 sec)
(3)大于、大于等于、小于、小于等于运算符
mysql> select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 5>4 | 'a'>'b' | 2>=3 | (2+3)>=(1+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 1 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
1 row in set (0.00 sec)
(4)IS NULL、IS NOT NULL
mysql> select 2 IS NULL,'f' IS NOT NULL,NULL IS NULL;
+-----------+-----------------+--------------+
| 2 IS NULL | 'f' IS NOT NULL | NULL IS NULL |
+-----------+-----------------+--------------+
| 0 | 1 | 1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
(5)BETWEEN AND
mysql> select 4 BETWEEN 2 AND 6,5 BETWEEN 6 AND 8,'c' BETWEEN 'a' AND 'f';
+-------------------+-------------------+-------------------------+
| 4 BETWEEN 2 AND 6 | 5 BETWEEN 6 AND 8 | 'c' BETWEEN 'a' AND 'f' |
+-------------------+-------------------+-------------------------+
| 1 | 0 | 1 |
+-------------------+-------------------+-------------------------+
1 row in set (0.00 sec)
(6)LEAST、GREATEST
mysql> SELECT least(1,2,3),least('a','b','c'),greatest(1,2,3),greatest('a','b','c');
+--------------+--------------------+-----------------+-----------------------+
| least(1,2,3) | least('a','b','c') | greatest(1,2,3) | greatest('a','b','c') |
+--------------+--------------------+-----------------+-----------------------+
| 1 | a | 3 | c |
+--------------+--------------------+-----------------+-----------------------+
1 row in set (0.00 sec)
(7)IN、NOT IN
mysql> SELECT 2 in (1,2,3,4,5),'c' not in ('a','b','c');
+------------------+--------------------------+
| 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') |
+------------------+--------------------------+
| 1 | 0 |
+------------------+--------------------------+
1 row in set (0.00 sec)
(8)LIKE、NOT LIKE
mysql> SELECT 'bdqn' LIKE 'bdq_','kgc' LIKE '%c','etc' NOT LIKE '%th';
+--------------------+-----------------+----------------------+
| 'bdqn' LIKE 'bdq_' | 'kgc' LIKE '%c' | 'etc' NOT LIKE '%th' |
+--------------------+-----------------+----------------------+
| 1 | 1 | 1 |
+--------------------+-----------------+----------------------+
1 row in set (0.00 sec)
1.4.3 逻辑运算符
逻辑运算符又被称为布尔运算符,通常用来判断表达式的真假,如果为真返回 1,否则返回 0,
真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种,
运算符 描述
NOT 或 ! 逻辑非
AND 或 && 逻辑与
OR 或 || 逻辑或
XOR 逻辑异或
(1)逻辑非
逻辑运算符中最简单的运算符就是逻辑非,逻辑非使用 NOT 或!表示。逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL。例如,对非 0 值和 0 值分别作逻辑非运算.
mysql>SELECT not 2,!3,not 0,!(4-4);
+-------+----+-------+--------+
| not 2 | !3 | not 0 | !(4-4) |
+-------+----+-------+--------+
| 0 | 0 | 1 | 1 |
+-------+----+-------+--------+
1 row in set (0.00 sec)
(2)逻辑与
逻辑与通常用于判断两个值或多个值的有效性,如果所有值都是真返回 1,否则返回 0
逻辑与使用 AND 或者&&表示。例如,对非 0 值、0 值和 NULL 值分别作逻辑与运算,
mysql> SELECT 2 AND 3,4 && 0,0 && NULL,1 AND NULL;
+---------+--------+-----------+------------+
| 2 AND 3 | 4 && 0 | 0 && NULL | 1 AND NULL |
+---------+--------+-----------+------------+
| 1 | 0 | 0 | NULL |
+---------+--------+-----------+------------+
1 row in set (0.00 sec)
(3)逻辑或
逻辑或表示包含的操作数,任意一个为非零值并且不是 NULL 值时,返回 1,否则返回0。
逻辑或通常使用 OR 或者||来表示。例如,对非 0 值、0 值和 NULL 值分别作逻辑或运算
mysql>SELECT 2 OR 3,4 || 0,0 OR NULL,1 || NULL; ###有问题
+--------+--------+-----------+-----------+
| 2 OR 3 | 4 || 0 | 0 OR NULL | 1 || NULL |
+--------+--------+-----------+-----------+
| 1 | 1 | NULL | 1 |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)
(4)逻辑异或
两个非 NULL 值的操作数,如果两者都是 0 或者都是非 0,则返回 0;如果一个为 0, 另一个为非 0,则返回结果为 1;当任意一个值为 NULL 时,返回值为 NULL。例如,对非0 值、0 值和 NULL 值分别作逻辑异或运算
mysql>SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;
+---------+---------+---------+------------+---------------+
| 2 XOR 3 | 0 XOR 0 | 0 XOR 5 | 1 XOR NULL | NULL XOR NULL |
+---------+---------+---------+------------+---------------+
| 0 | 0 | 1 | NULL | NULL |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)
1.4.4 位运算符
位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式,然后进行位运算,最后在将计算结果从二进制变回到十进制格式,方便用户查看。MySQL 支持 6 种位运算符
运算符 描述
& 按位与
| 按位或
~ 按位取反
^ 按位异或
<< 按位左移
>> 按位右移
##对数字进行按位与、或和取反运算##
mysql> SELECT 10 & 15, 10 | 15, 10 ^ 15, 5 &~1;
+---------+---------+---------+-------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 |
+---------+---------+---------+-------+
| 10 | 15 | 5 | 4 |
+---------+---------+---------+-------+
1 row in set (0.00 sec)
##对数字进行左移或右移的运算##
mysql> SELECT 1<<2, 2<<2,10>>2,15>>2;
+------+------+-------+-------+
| 1<<2 | 2<<2 | 10>>2 | 15>>2 |
+------+------+-------+-------+
| 4 | 8 | 2 | 3 |
+------+------+-------+-------+
1 row in set (0.00 sec)
运算符的优先级
优先级 运算符 优先级 运算符
1 ! 8 |
2 ~ 9 =,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
3 ^ 10 BETWEEN,CASE,WHEN,THEN,ELSE
4 * , /(DIV), %(MOD) 11 NOT
5 +,- 12 &&,AND
6 >>,<< 13 ||,OR,XOR
7 & 14 :=
1.5 连接查询
MySQL 的连接查询,通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接。首先,要确定一个主表作为结果集,然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括:内连接、左连接和右连接。为了便于理解,这里使用两个表 a_player 和 b_player 来进行演示。首先需要创建这两个表,然后插入一些初始数据。
mysql> CREATE TABLE `a_player` (
-> `a_id` int(11) DEFAULT NULL,
-> `a_name` varchar(32) DEFAULT NULL,
-> `a_level` int(11) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `b_player` (
-> `b_id` int(11) DEFAULT NULL,
-> `b_name` varchar(32) DEFAULT NULL,
-> `b_level` int(11) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a_player(a_id, a_name, a_level) values(1, 'aaaa', 10); insert into a_player(a_id, a_name, a_level) values(2, 'bbbb', 20); insert into a_player(a_id, a_name, a_level) values(3, 'cccc', 30); insert into a_player(a_id, a_name, a_level) values(4, 'dddd', 40);
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> insert into b_player(b_id, b_name, b_level) values(2, 'bbbb', 20); insert into b_player(b_id, b_name, b_level) values(3, 'cccc', 30); insert into b_player(b_id, b_name, b_level) values(5, 'eeee', 50); insert into b_player(b_id, b_name, b_level) values(6, 'ffff', 60);
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
1.5.1 内连接
语法格式
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
示例
mysql>select a_id,a_name,a_level from a_player inner join b_player on a_id=b_id;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
| 2 | bbbb | 20 |
| 3 | cccc | 30 |
+------+--------+---------+
2rows in set (0.00 sec)
1.5.2 左连接
mysql> select * from a_player a left join b_player b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
| 2 | bbbb | 20 | 2 | bbbb | 20 |
| 3 | cccc | 30 | 3 | cccc | 30 |
| 1 | aaaa | 10 | NULL | NULL | NULL |
| 4 | dddd | 40 | NULL | NULL | NULL |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)
1.5.3 右连接
mysql> select * from a_player a right join b_player b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
| 2 | bbbb | 20 | 2 | bbbb | 20 |
| 3 | cccc | 30 | 3 | cccc | 30 |
| NULL | NULL | NULL | 5 | eeee | 50 |
| NULL | NULL | NULL | 6 | ffff | 60 |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)
二 MySQL数据库函数
MySQL 数据库函数提供了能够实现各种功能的方法,使我们在查询记录时能够更高效的输出。MySQL 内建了很多函数,常用的包括数学函数、聚合函数、字符串函数和日期时间函数。
2.1 数学函数
数据库内存储的记录,经常要进行一系列的算术操作,所以 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(x1,x2...) 返回集合中最大的值
least(x1,x2...) 返回集合中最小的值
mysql>select abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
+---------+---------------------+----------+------------+-------------+
| abs(-1) | rand() | mod(5,3) | power(2,3) | round(1.89) |
+---------+---------------------+----------+------------+-------------+
| 1 | 0.46176527949214474 | 2 | 8 | 2 |
+---------+---------------------+----------+------------+-------------+
1 row in set (0.00 sec)
mysql>select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
+-----------------+-------------------+-----------+------------+-----------------------+
| round(1.8937,3) | truncate(1.235,2) | ceil(5.2) | floor(2.1) | least(1.89,3,6.1,2.1) |
+-----------------+-------------------+-----------+------------+-----------------------+
| 1.894 | 1.23 | 6 | 2 | 1.89 |
+-----------------+-------------------+-----------+------------+-----------------------+
1 row in set (0.00 sec)
2.2 聚合函数
MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的,这些函数被称作聚合函数。常见的聚合函数聚合函数 描述
avg() 返回指定列的平均值
count() 返回指定列中非 NULL 值的个数
min() 返回指定列的最小值
max() 返回指定列的最大值
sum(x) 返回指定列的所有值之和
mysql> select sum(level) as sum_level from player;
+-----------+
| sum_level |
+-----------+
| 483 |
+-----------+
1 row in set (0.00 sec)
mysql>select max(level) as max_level from player;
+-----------+
| max_level |
+-----------+
| 47 |
+-----------+
1 row in set (0.00 sec)
mysql>select min(level) as min_level from player;
+-----------+
| min_level |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
2.3 字符串函数
常用函数不仅包括数学函数和聚合函数,还包含字符串函数,MySQL 为字符串的相关操作设计了丰富的字符串函数。常用的字符串函数如表
字符串函数 描述
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 中的字符串 y
strcmp(x,y) 比较 x 和 y,返回的值可以为-1,0,1
substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
reverse(x) 将字符串 x 反转
mysql>select length('bdqn'), trim(' yellow '), concat('bd', 'qn'), upper('abc'), right('hello', 3);
+----------------+------------------+--------------------+--------------+-------------------+
| length('bdqn') | trim(' yellow ') | concat('bd', 'qn') | upper('abc') | right('hello', 3) |
+----------------+------------------+--------------------+--------------+-------------------+
| 4 | yellow | bdqn | ABC | llo
|
+----------------+------------------+--------------------+--------------+-------------------+
1 row in set (0.00 sec)
mysql>select repeat('kgc', 2), replace('hello', 'll', 'kgc'), strcmp(4, 5), substring('bjbdqn', 4, 2), reverse('hello');
+------------------+-------------------------------+--------------+---------------------------+------------------+
| repeat('kgc', 2) | replace('hello', 'll', 'kgc') | strcmp(4, 5) | substring('bjbdqn', 4, 2) | reverse('hello') |
+------------------+-------------------------------+--------------+---------------------------+------------------+
| kgckgc | hekgco | -1 | dq
| olleh |
+------------------+-------------------------------+--------------+---------------------------+------------------+
1 row in set (0.00 sec)
2.4 日期时间函数
MySQL 也支持日期时间处理,提供了很多处理日期和时间的函数。一些常用的日期时间函数
字符串函数 描述
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 是本年的第几天
mysql>select curdate(),curtime(),now(),month('2020-02-09'), week('2020-02-09'), hour('21:13:53');
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| curdate() | curtime() | now() | month('2020-02-09') | week('2020-02-09') | hour('21:13:53') |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| 2020-02-09 | 21:14:34 | 2020-02-09 21:14:34 | 2 | 6 |
21 |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
1 row in set (0.00 sec)
mysql>select minute('21:13:53'),second('21:13:53'), dayofweek('2020-02-09'), dayofmonth('2020-02-09'), dayofyear('2020-02-09');
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
| minute('21:13:53') | second('21:13:53') | dayofweek('2020-02-09') | dayofmonth('2020-02-09') | dayofyear('2020-02-09') |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
| 13 | 53 | 1 |
9 | 40 |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
1 row in set (0.00 sec)
三 MySQL存储过程
3.1 存储过程简介
● MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的,它可以加快数据库的处理速度,增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存 储过程时,只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译,然后再去执行,跟存储过程一对比,明显存储过程在执行上速度更快,效率更高。
● 存储过程在数据库中创建并保存,它不仅仅是 SQL 语句的集合,还可以加入一些特殊的控制结构,也可以控制数据的访问方式。存储过程的应用范围很广,例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。如果了解一些编程语言,可能会发现存储过程更像是面向对象方法的模拟。
● 优点
- 存储过程执行一次后,生成的二进制代码就驻留在缓冲区,之后如果再次调用的话,将 直接调用二进制代码,使 得存储过程的执行效率和性能得到大幅提升。
- 存储过程是 SQL 语句加上控制语句的集合,有很强的灵活性,可以完成复杂的运算。
- 存储过程存储在服务器端,客户端调用时,直接在服务器端执行,客户端只是传输的调 用语句,从而可以降低网 络负载。
- 存储过程被创建后,可以多次重复调用,它将多条 SQL 封装到了一起,可随时针对 SQL语句进行修改,不影响调用它的客户端。
- 存储过程可以完成所有的数据库操作,也可以通过编程的方式控制数据库的信息访问权 限。
3.2创建存储过程
语法格式
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名><类型>
示例
##通过存储过程查询 player 表的三条数据,存储过程是不带参数的##
mysql> delimiter $$
mysql> CREATE PROCEDURE PlayerRole()
-> BEGIN
-> SELECT id,name,level from player limit 3;
-> END $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> call PlayerRole();
+----+--------------+-------+
| id | name | level |
+----+--------------+-------+
| 1 | 修欧拉卡 | 10 |
| 2 | 起风了 | 10 |
| 3 | 吊打低V | 15 |
+----+--------------+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
##通过存储过程查询 player 表中某一条记录,存储过程是带参数的##
mysql> DELIMITER $$
mysql> CREATE PROCEDURE GetRole(IN inname VARCHAR(16))
-> BEGIN
-> SELECT id,name,level from player where name=inname;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER;
mysql> call GetRole('shirley');
+------+---------+-------+
| id | name | level |
+------+---------+-------+
| 2460 | shirley | 1 |
+------+---------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.3修改存储过程
语法结构
ALTER PROCEDURE <过程名> [ <特征> … ]
存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储 过程。
3.4删除存储过程
语法结构
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
示例
mysql>DROP PROCEDURE PlayerRole;
Query OK, 0 rows affected (0.00 sec)
mysql>CALL PlayerRole;
ERROR 1305 (42000): PROCEDURE test.PlayerRole does not exist
需要注意的是:存储过程名称后面没有参数列表,也没有括号。在删除之前,必须确认 该存储过程没有任何依赖关系,否则会导致与之关联的存储过程无法运行。