赞
踩
在对 MySQL 数据库的增、删、改、查操作有一定了解之后,就可以学习一些 SQL 语句的高级使用方法,下面咱们一起来研究吧!
对 MySQL 数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等…
使用order by语句来实现排序
排序可针对一个或多个字段
ASC:升序,默认排序方式
DESC:降序
order by的语法结构
select 字段1,字段2 from 表名 order by 字段1 desc|asc,字段2 desc|asc;
按单字段排序
mysql> select * from info; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 19 | | 2 | ben | 14 | | 3 | chalice | 26 | | 4 | david | 17 | | 5 | ellen | 24 | | 6 | fros | 19 | +----+---------+------+ 6 rows in set (0.00 sec) mysql> select * from info where age > 20 order by age; +----+---------+------+ | id | name | age | +----+---------+------+ | 5 | ellen | 24 | | 3 | chalice | 26 | +----+---------+------+ 2 rows in set (0.00 sec) mysql> select * from info order by age desc; +----+---------+------+ | id | name | age | +----+---------+------+ | 3 | chalice | 26 | | 5 | ellen | 24 | | 1 | alice | 19 | | 6 | fros | 19 | | 4 | david | 17 | | 2 | ben | 14 | +----+---------+------+ 6 rows in set (0.00 sec)
mysql> select * from info order by age ,id desc;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | ben | 14 |
| 4 | david | 17 |
| 6 | fros | 19 |
| 1 | alice | 19 |
| 5 | ellen | 24 |
| 3 | chalice | 26 |
+----+---------+------+
6 rows in set (0.00 sec)
mysql> select count(name),age from info group by age;
+-------------+------+
| count(name) | age |
+-------------+------+
| 1 | 14 |
| 1 | 17 |
| 2 | 19 |
| 1 | 24 |
| 1 | 26 |
+-------------+------+
5 rows in set (0.00 sec)
mysql> select count(name),age from info group by age order by age desc;
+-------------+------+
| count(name) | age |
+-------------+------+
| 1 | 26 |
| 1 | 24 |
| 2 | 19 |
| 1 | 17 |
| 1 | 14 |
+-------------+------+
5 rows in set (0.00 sec)
只返回select查询结果的第一行或第几行
使用limit语句限制条目
limit语法结构
语法: select column1,column2,... from 库名 limit 位置偏移量 mysql> select * from info limit 3; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 19 | | 2 | ben | 14 | | 3 | chalice | 26 | +----+---------+------+ 3 rows in set (0.00 sec) mysql> select * from info limit 2,3; +----+---------+------+ | id | name | age | +----+---------+------+ | 3 | chalice | 26 | | 4 | david | 17 | | 5 | ellen | 24 | +----+---------+------+ 3 rows in set (0.00 sec) #2,3表示从第三行开始数(0,1,2),显示后三行
使用AS语句设置别名,关键字AS可省略
设置别名时,保证不能与库中其他表或字段名称冲突
别名的语法结构
列的别名: select 列名 as 列名别名 from 库名; 表的别名: select 列名 from 库名 as 库名别名; mysql> select i.id as 学号,name 姓名,age as 年龄 from info as i; +--------+---------+--------+ | 学号 | 姓名 | 年龄 | +--------+---------+--------+ | 1 | alice | 19 | | 2 | ben | 14 | | 3 | chalice | 26 | | 4 | david | 17 | | 5 | ellen | 24 | | 6 | fros | 19 | +--------+---------+--------+ 6 rows in set (0.00 sec)
mysql> create table test as select * from info; Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 19 | | 2 | ben | 14 | | 3 | chalice | 26 | | 4 | david | 17 | | 5 | ellen | 24 | | 6 | fros | 19 | +----+---------+------+ 6 rows in set (0.00 sec)
用于替换字符串中的部分字符
通常配合like一起使用,并协同where完成查询
常用通配符
%:表示0个,1个或多个
_:表示单个字符
mysql> select * from info where name like 'a%';
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | alice | 19 |
+----+-------+------+
1 row in set (0.00 sec)
mysql> select * from info where name like 'be_';
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | ben | 14 |
+----+------+------+
1 row in set (0.00 sec)
也称作内查询或者嵌套查询
先于主查询被执行,其结果将作为外层查询的条件
在增删改查中都可以使用子查询
支持多层嵌套
IN语句是用来判断某个值是否在给定的结果集中
子查询的用法
查询: mysql> select id,age from info where age in (select age from info where age >=20); +----+------+ | id | age | +----+------+ | 3 | 26 | | 5 | 24 | +----+------+ 2 rows in set (0.00 sec) 查询结合降序使用: mysql> select id,age from info where age in (select age from info where age >=20)order by age; +----+------+ | id | age | +----+------+ | 5 | 24 | | 3 | 26 | +----+------+ 2 rows in set (0.00 sec) 插入: mysql> create table test as select * from info; mysql> delete from test_; mysql> insert into test select * from info where age in (select age from info where age >=20); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+---------+------+ | id | name | age | +----+---------+------+ | 3 | chalice | 26 | | 5 | ellen | 24 | +----+---------+------+ 2 rows in set (0.00 sec) 修改: mysql> update test set score=80 where age in (select age from info where age >=20); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from test; +----+---------+------+-------+ | id | name | age | score | +----+---------+------+-------+ | 3 | chalice | 26 | 80 | | 5 | ellen | 24 | 80 | +----+---------+------+-------+ 2 rows in set (0.00 sec) 删除: mysql> create table test as select * from info; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> delete from test where age in (select age from (select * from info where age >=20)a); Query OK, 2 rows affected (0.01 sec) mysql> select * from test; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | alice | 19 | | 2 | ben | 14 | | 4 | david | 17 | | 6 | fros | 19 | +----+-------+------+ 4 rows in set (0.00 sec)
表示缺失的值
与数字0或者空白(spaces)是不同的
使用IS NULL或IS NOT NULL进行判断
NULL值和空值的区别
空值长度为0,不占空间;NULL值的长度为NULL,占用空间
IS NULL无法判断空值
空值使用“=”或者“<>”来处理
COUNT()计算时,NULL会忽略,空值会加入计算
插入空值: mysql> alter table test add column height varchar(8); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into test values(7,'gary',14,''); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+-------+------+--------+ | id | name | age | height | +----+-------+------+--------+ | 1 | alice | 19 | NULL | | 2 | ben | 14 | NULL | | 4 | david | 17 | NULL | | 6 | fros | 19 | NULL | | 7 | gary | 14 | | +----+-------+------+--------+ 5 rows in set (0.00 sec) null的用法: mysql> select * from test where height is null; +----+-------+------+--------+ | id | name | age | height | +----+-------+------+--------+ | 1 | alice | 19 | NULL | | 2 | ben | 14 | NULL | | 4 | david | 17 | NULL | | 6 | fros | 19 | NULL | +----+-------+------+--------+ 4 rows in set (0.01 sec) mysql> select * from test where height is not null; +----+------+------+--------+ | id | name | age | height | +----+------+------+--------+ | 7 | gary | 14 | | +----+------+------+--------+ 1 row in set (0.00 sec)
字符 | 说明 |
---|---|
^ | 匹配开始字符 |
$ | 匹配结束字符 |
. | 匹配任意单个字符 |
* | 匹配任意个前面的字符 |
+ | 匹配前面字符至少1次 |
p1|p2 | 匹配p1或p2 |
[…] | 匹配字符集中括号内的任何字符 |
[^…] | 匹配不在括号内的任何字符 |
{n} | 匹配前面的字符串n次 |
{n,m} | 匹配前面的字符串至少n次,至多m次 |
mysql> select * from info; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 19 | | 2 | ben | 14 | | 3 | chalice | 26 | | 4 | david | 17 | | 5 | ellen | 24 | | 6 | fros | 19 | | 7 | alley | 21 | | 8 | allen | 27 | +----+---------+------+ 8 rows in set (0.00 sec) 以a开头的姓名:(^) mysql> select * from info where name regexp '^a'; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | alice | 19 | | 7 | alley | 21 | | 8 | allen | 27 | +----+-------+------+ 3 rows in set (0.00 sec) 以e结尾的姓名:($) mysql> select * from info where name regexp 'e$'; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 19 | | 3 | chalice | 26 | +----+---------+------+ 2 rows in set (0.01 sec) 匹配单个字符(.) mysql> select * from info where name regexp 'alic.'; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 19 | | 3 | chalice | 26 | +----+---------+------+ 2 rows in set (0.00 sec) 匹配前面字符至少1次(+) mysql> insert into info values(9,'lee',14),(10,'leee',18),(11,'leeee',24); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from info where name regexp 'leee+'; +----+-------+------+ | id | name | age | +----+-------+------+ | 10 | leee | 18 | | 11 | leeee | 24 | +----+-------+------+ 2 rows in set (0.00 sec) 匹配任意个前面的字符(*) mysql> select * from info where name regexp 'leee*'; +----+-------+------+ | id | name | age | +----+-------+------+ | 9 | lee | 14 | | 10 | leee | 18 | | 11 | leeee | 24 | +----+-------+------+ 3 rows in set (0.00 sec) 匹配p1或p2(p1|p2) mysql> select * from info where name regexp 'le|d'; +----+-------+------+ | id | name | age | +----+-------+------+ | 4 | david | 17 | | 5 | ellen | 24 | | 7 | alley | 21 | | 8 | allen | 27 | | 9 | lee | 14 | | 10 | leee | 18 | | 11 | leeee | 24 | +----+-------+------+ 7 rows in set (0.00 sec) 匹配字符集中括号内的任何字符([...]) mysql> select * from info where name regexp '[ab]'; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 19 | | 2 | ben | 14 | | 3 | chalice | 26 | | 4 | david | 17 | | 7 | alley | 21 | | 8 | allen | 27 | +----+---------+------+ 6 rows in set (0.00 sec) 匹配前面的字符串n次{n} mysql> select * from info where name regexp 'e{2}'; +----+-------+------+ | id | name | age | +----+-------+------+ | 9 | lee | 14 | | 10 | leee | 18 | | 11 | leeee | 24 | +----+-------+------+ 3 rows in set (0.00 sec) 匹配前面的字符串至少n次,至多m次({n,m}) mysql> select * from info where name regexp 'e{3,4}'; +----+-------+------+ | id | name | age | +----+-------+------+ | 10 | leee | 18 | | 11 | leeee | 24 | +----+-------+------+ 2 rows in set (0.00 sec)
字符 | 说明 |
---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法 |
% | 取余数 |
mysql> select 3+2,7-3,3*5,6/3,5%3;
+-----+-----+-----+--------+------+
| 3+2 | 7-3 | 3*5 | 6/3 | 5%3 |
+-----+-----+-----+--------+------+
| 5 | 4 | 15 | 2.0000 | 2 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)
比较运算符
字符串的比较默认不区分大小写,可使用binary来区分
常用比较运算符
运算符 | 说明 |
---|---|
= | 等于 |
> | 大于 |
< | 小于 |
>= | 大于或等于 |
<= | 小于或等于 |
!=或<> | 不等于 |
IN | 在集合中 |
LIKE | 通配符匹配 |
IS NULL | 判断一个值是否为NULL |
IS NOT NULL | 判断一个值是否不为NULL |
BETWEEN AND | 两者之间 |
GREATEST | 两个或多个参数时返回最大值 |
LEAST | 两个或多个参数时返回最小值 |
mysql> select 1=2,2='2','ab'='ab',(1+4)=(2+3),'h'=NULL; +-----+-------+-----------+-------------+----------+ | 1=2 | 2='2' | 'ab'='ab' | (1+4)=(2+3) | 'h'=NULL | +-----+-------+-----------+-------------+----------+ | 0 | 1 | 1 | 1 | NULL | +-----+-------+-----------+-------------+----------+ 1 row in set (0.00 sec) mysql> select 4!=5,null is null,null is not null,3 between 5 and 8; +------+--------------+------------------+-------------------+ | 4!=5 | null is null | null is not null | 3 between 5 and 8 | +------+--------------+------------------+-------------------+ | 1 | 1 | 0 | 0 | +------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> select greatest(1,6,3),least(5,3,36); +-----------------+---------------+ | greatest(1,6,3) | least(5,3,36) | +-----------------+---------------+ | 6 | 3 | +-----------------+---------------+ 1 row in set (0.00 sec)
注:
如果两者都是整数,则按整数值进行比较
如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较
如果两者都是字符串,则按照字符串进行比较
如果两者中至少有一个值是NULL,则比较的结果是NULL
称为布尔运算符
用来判断表达式的真假
常用的逻辑运算符
运算符 | 说明 |
---|---|
NOT或! | 逻辑非 |
AND或&& | 逻辑与 |
OR或|| | 逻辑或 |
XOR | 逻辑异或 |
mysql> select not 3,!5,not 0,!(6-6);
+-------+----+-------+--------+
| not 3 | !5 | not 0 | !(6-6) |
+-------+----+-------+--------+
| 0 | 0 | 1 | 1 |
+-------+----+-------+--------+
1 row in set (0.00 sec)
mysql> select 2 and 5,3 && 0, 1 || NULL ,0 and NULL;
+---------+--------+-----------+------------+
| 2 and 5 | 3 && 0 | 1 || NULL | 0 and NULL |
+---------+--------+-----------+------------+
| 1 | 0 | 1 | 0 |
+---------+--------+-----------+------------+
1 row in set (0.00 sec
对二进制数进行计算的运算符
常用的位运算符
运算符 | 说明 |
---|---|
& | 按位与 |
| | 按位或 |
~ | 按位取反 |
^ | 按位异或 |
<< | 按位左移 |
>> | 按位右移 |
mysql> select 5&6,5|6,5&~6,5^6,3<<2,3>>1;
+-----+-----+------+-----+------+------+
| 5&6 | 5|6 | 5&~6 | 5^6 | 3<<2 | 3>>1 |
+-----+-----+------+-----+------+------+
| 4 | 7 | 1 | 3 | 12 | 1 |
+-----+-----+------+-----+------+------+
1 row in set (0.00 sec)
MySQL的连接查询,通常都是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接,首先,要确定一个主表作为结果集,然后将其他表的行有选择性的连接到选定的主表结果集上,使用较多的连接查询包括:内连接、左连接和右连接
mysql> select * from name; +------+---------+ | id | name | +------+---------+ | 1 | alice | | 2 | ben | | 3 | chalice | | 5 | ellen | +------+---------+ 4 rows in set (0.00 sec) mysql> select * from hobby; +------+------------+ | id | hobby | +------+------------+ | 1 | volleyball | | 2 | basketball | | 3 | football | | 4 | ping-pang | +------+------------+ 4 rows in set (0.00 sec)
mysql> select n.id,n.name,h.hobby from name n inner join hobby h on n.id=h.id;
+------+---------+------------+
| id | name | hobby |
+------+---------+------------+
| 1 | alice | volleyball |
| 2 | ben | basketball |
| 3 | chalice | football |
+------+---------+------------+
3 rows in set (0.00 sec)
mysql> select n.id,n.name,h.hobby from name n left join hobby h on n.id=h.id;
+------+---------+------------+
| id | name | hobby |
+------+---------+------------+
| 1 | alice | volleyball |
| 2 | ben | basketball |
| 3 | chalice | football |
| 5 | ellen | NULL |
+------+---------+------------+
4 rows in set (0.00 sec)
mysql> select n.id,n.name,h.hobby from name n right join hobby h on n.id=h.id;
+------+---------+------------+
| id | name | hobby |
+------+---------+------------+
| 1 | alice | volleyball |
| 2 | ben | basketball |
| 3 | chalice | football |
| NULL | NULL | ping-pang |
+------+---------+------------+
4 rows in set (0.00 sec)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。