2017-07-31

数据库-查询数据

略

学习目标

了解基本查询语句
掌握表单查询的方法
掌握连接查询的方法
掌握如何使用子查询
熟悉合并查询结果
使用正则表达式查询

基本查询语句

SELECT语句的基本格式为:

SELECT
    {* | <字段列表>}
    [
        FROM <表1>,<表2>...
        [ WHERE <表达式>]
        [GROUP BY <group by definition>]
        [HAVING <expression> [{<operator> <expression>}...]]
        [ORDER BY <order by definition>]
        [LIMIT [<offset>,] <row count>]
    ]

SELECT [字段1,字段2,....]
FROM [表或者视图]
WHERE [查询条件];

一个例子:

CREATE TABLE fruits
(
f_id    char(10)        NOT NULL,
s_id    INT         NOT NULL,
f_name  char(255)   NOT NULL,
f_price decimal(8,2)    NOT NULL,
PRIMARY KEY(f_id)
);

-- 数据如下
 INSERT INTO fruits (f_id, s_id, f_name, f_price)
     VALUES('a1', 101,'apple',5.2),
     ('b1',101,'blackberry', 10.2),
     ('bs1',102,'orange', 11.2),
     ('bs2',105,'melon',8.2),
     ('t1',102,'banana', 10.3),
     ('t2',102,'grape', 5.3),
     ('o2',103,'coconut', 9.2),
     ('c0',101,'cherry', 3.2),
     ('a2',103, 'apricot',2.2),
     ('l2',104,'lemon', 6.4),
     ('b2',104,'berry', 7.6),
     ('m1',106,'mango', 15.6),
     ('m2',105,'xbabay', 2.6),
     ('t4',107,'xbababa', 3.6),
     ('m3',105,'xxtt', 11.6),
     ('b5',107,'xxxx', 3.6);

单表查询

单表查询是指从一个表中查询所需的数据,主要的操作有:

查询所有字段
查询指定字段
查询指定记录
查询空值
多条件的查询
对查询结果进行排序,等

查询所有字段

使用星号(`*`)通配符查询所有字段

1	SELECT * FROM 表名;

题目: 从fruits表中查询出所有的字段的数据

使用指定所有字段查询所有值

星号通配符可以代表表内所有字段的名字,当然也可以直接表明要查询什么数据.例如:

1	SELECT `f_id`,`s_id`,`f_name`,`f_price` FROM fruits;

查询指定字段

如果仅仅需要表内的特定列的数据,那就可以使用指定字段的查询方法,与上一查询类似,例如:

1	SELECT 列名1,列名2,列名3.... FROM 表名;

题目: 查询fruits表中的f_name字段和f_price的数据

带IN关键字的查询

IN关键字代表了在什么之中查询,将索引条件用括号包裹,多个条件用逗号分开,只有满足范围内的一个即为匹配项

例如:查询s_id为101和102的记录,语句如下:

1
2
3

SELECT `s_id`,`f_name`,`f_price`
FROM fruits
WHERE s_id IN (101,102);

同样也可以查询不在101和102中的数据,只需要在IN改为NOT IN即可.例如.

1
2
3

SELECT `s_id`,`f_name`,`f_price`
FROM fruits
WHERE s_id NOT IN (101,102);

带BETWEEN AND 的范围查询

对于数值类型的数据,可以用BETWEEN a AND b 来限定范围.

例如查询2.00元到10.20元范围内的水果.

1
2
3

SELECT `f_name`,`f_price`
FROM fruits
WHERE `f_price` BETWEEN 2.00 AND 10.20;

如同IN,BETWEEN AND也可以使用NOT关键字表示不在这个范围内.

1
2
3

SELECT `f_name`,`f_price`
FROM fruits
WHERE `f_price` NOT BETWEEN 2.00 AND 10.20;

带LIKE的字符匹配查询

当我们知道一个水果名中包含了一个字符b,但是全称不知道时,可以用LIKE进行模糊查询.

用法为:

1	SELECT * FROM 表名 WHERE 列名 LIKE '匹配规则';

`%`通配符,表示任意长度的任意字符,包括空

`_`通配符,一次表示一个任意字符

题目: 查询fruits表中水果名中包含b的水果数据.

加入新数据:

CREATE TABLE customers
(
  c_id      int       NOT NULL AUTO_INCREMENT,
  c_name    char(50)  NOT NULL,
  c_address char(50)  NULL,
  c_city    char(50)  NULL,
  c_zip     char(10)  NULL,
  c_contact char(50)  NULL,
  c_email   char(255) NULL,
  PRIMARY KEY (c_id)
);
-- 为了演示需要插入数据，请读者插入执行以下语句。
INSERT INTO customers(c_id, c_name, c_address, c_city,
c_zip,  c_contact, c_email)
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin',
 '300000',  'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane',
 'Dalian', '116000',  'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao',  '266000',
 'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou',
 '570000',  'YangShan', 'sam@hotmail.com');

查询空值

表中会存在为空的数据,可以通过IS NULL查询出来,同样也可以用IS NOT NULL查询出所有不为空的数据

1
2
3

SELECT * FROM 表名 WHERE 列名 IS NULL;

SELECT * FROM 表名 WHERE 列名 IS NOT NULL;

带AND的多条件查询

当查询条件有多个,而且每一个都有符合时,就可以使用AND关键字.相当与&运算

1	SELECT * FROM 表名 WHERE 查询条件1 AND 查询条件2 AND 查询条件3.....;

带OR的多条件查询

当查询条件有多个,仅仅符合一个即可,就可以使用OR关键字.相当与|运算

1	SELECT * FROM 表名 WHERE 查询条件1 OR 查询条件2 OR 查询条件3.....;

查询结果不重复

使用DISTINCT关键字可以去掉查询出来的数据的重复项,使用方法如下:

1	SELECT DISTINCT 字段名 FROM 表名;

题目: 仅仅查询fruits中的s_id,要求结果不重复

对结果进行排序

使用ORDER BY可以对查询出来的数据进行排序

单列排序

ORDER BY 后接按照哪一个列进行排序,例如:

1	SELECT * FROM 表名 WHERE 查询条件 ORDER BY 列名;

如果列为数值类型,则默认为升序排列,如果为字符类型,则按照字母表进行升序排序.

多列排序

ORDER BY后接多个字段名,中间用逗号隔开,例如:

1	SELECT * FROM 表名 WHERE 查询条件 ORDER BY 字段1,字段2,....;

先按照字段1排序,对于字段1相同的数据,再按照字段2排序,依次类推

排序的方向

就是升序和降序,默认为升序,降序在语句最后接上DESC即可.

分组查询

基础语法为:

1	[GROUP BY 字段] [HAVING <条件表达式>]

GROUP BY 通常和聚合函数一起使用,例如MAX(),MIN(),COUNT(),SUM(),AVG().

例如:查询fruits表中每个编号的水果数量

1
2
3

SELECT `s_id`,COUNT(*)
FROM fruits
GROUP BY `s_id`;

对分组进行过滤

HAVING 用来和为分组过滤,例如上个例子中,过滤掉s_id为101的数据.

SELECT `s_id`,COUNT(*)
FROM fruits
GROUP BY `s_id`
HAVING 's_id' NOT IN (101);

多字段分组

类似于排序先对字段1分组,然后再在分组的基础上对字段2进行分组.例如:

1	SELECT * FROM fruits GROUP BY `s_id`,`f_name`;

GROUP BY 与 ORDER BY 一起使用

需要新数据:

CREATE TABLE orderitems
(
  o_num      int          NOT NULL,
  o_item     int          NOT NULL,
  f_id       char(10)     NOT NULL,
  quantity   int          NOT NULL,
  item_price decimal(8,2) NOT NULL,
  PRIMARY KEY (o_num,o_item)
) ;
-- 然后插入演示数据。SQL语句如下：
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);

查询订单大于100的订单号和订单总价格

SELECT `o_num` SUM(quantity * item_price) AS orderTotal
FROM orderitems
GROUP BY o_num
HAVING SUM(quantity * item_price) >= 100
ORDER BY orderTotal;

使用LIMIT限制查询结果的数量

LIMIT [偏移量,] 行数

数据库表数据从0开始计算,偏移量为0时代表第一条数据.

偏移量可以省略,LIMIT 0,n 代表LIMIT n.

聚合函数

具体不写了,自己想.

COUNT()

SUM()

AVG()

MAX()

MIN()

连接查询

连接是关系型数据库的主要特点,连接查询是关系数据库中最重要的查询.包括内连接,外连接等.

内连接查询

内连接(INNER JOIN)使用比较运算符进行表间某些列数据的比较操作,并列出这些表中与连接条件匹配的数据行,组合成新记录,也就是说,在内连接查询中只有满足条件的记录才能出现在关系中.

新使用的SQL如下:

CREATE TABLE suppliers
(
  s_id      int      NOT NULL AUTO_INCREMENT,
  s_name    char(50) NOT NULL,
  s_city    char(50) NULL,
  s_zip     char(10) NULL,
  s_call    CHAR(50) NOT NULL,
  PRIMARY KEY (s_id)
) ;
-- 插入需要演示的数据，SQL语句如下：
INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');

在fruits和suppliers表之间使用内连接查询

先通过DESC查看两个表的结构,发现水果表和供应商表中,可以根据两个表中公共属性供应商ID来实现连接,语句如下:

1
2
3

SELECT `suppliers.s_id`, `s_name`, `f_name`, `f_price`
FROM fruits,suppliers
WHERE `fruits.s_id` = `suppliers.s_id`;

两个表通过连接组成了一个新表.

还可以通过INNER JOIN关键字来进行,并且强烈推荐使用INNER JOIN,它更加规范一些,而且能够确保不会忘记写连接条件,WHERE子句在某些时候会影响查询性能.

使用INNER JOIN的SQL语句,作用于使用WHERE相同:

1
2
3

SELECT `suppliers.s_id`, `s_name`, `f_name`, `f_price`
FROM fruits INNER JOIN suppliers
ON `fruits.s_id` = `suppliers.s_id`;

骚操作之自连接

连接是两个表之间的,然而还可以把表本身当成另外一个表来进行连接,物理上是一个表,逻辑上是两个表.

例如,查询供应f_id=’a1’的水果供应商提供的水果种类.sql语句如下:

1
2
3

SELECT `f1.f_id`, `f1.f_name`
FROM fruits AS f1, fruits AS f2
WHERE `f1.s_id` = `f2.s_id` AND `f2.f_id` = 'a1';

外连接查询

内连接只能查询到两个表中符合条件的行,有时候需要包含没有关联的行中数据,使用到外连接,外连接包括:

左连接(LEFT JION) 返回包括左表中所有的数据和右表中连接字段相等的记录.
右连接(RIGHT JION) 返回包括右表中所有的数据和左表中连接字段相等的记录.

LEFT JOIN (左连接)

需要新数据:

CREATE TABLE orders
(
  o_num  int      NOT NULL AUTO_INCREMENT,
  o_date datetime NOT NULL,
  c_id   int      NOT NULL,
  PRIMARY KEY (o_num)
) ;
-- 插入需要演示的数据，SQL语句如下：
INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);

题目: 查询customers表和orders表中,查询所有客户,包括没有订单的客户

SELECT
    customers.c_id,
    orders.o_num
FROM
    customers
LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;

RIGHT JOIN (右连接)

同样上题,查询所有订单,包括没有客户的订单

SELECT
    customers.c_id,
    orders.o_num
FROM
    customers
RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;

记住格式:

SELECT
    $列名
FROM
    $左表
(RIGHT|LEFT) OUTER JOIN $右表 ON $条件;

复合条件连接查询

复合条件连接查询是在连接查询的过程中,通过添加过滤条件,限制查询结果,使查询的结果更加准确.

题目: 在customers表和orders表中,使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息

1
2
3

SELECT `customers.c_id`, `orders.o_num`
FROM customers INNER JOIN orders
ON `customers.c_id` = `orders.c_id` AND `customers.c_id` = 10001;

题目: 在fruits表和suppliers表之间,使用INNER JOIN语法进行查询,并排序

SELECT `suppliers.s_id`, `s_name`, `f_name`, `f_price`
FROM fruits INNER JOIN suppliers
ON  `fruits.s_id` = `suppliers.s_id`
ORDER BY `fruits.s_id`

子查询

子查询指一个查询语句嵌套在另外一个语句内部,子查询结果作为另外一个查询的过滤条件,查询可以基于一个或者多个表.
常用的操作符有ANY,ALL,IN,EXISTS.子查询可以运用到SELECT,UPDATE,DELETE中,还可以使用比较运算符.

带ANY,SOME关键字的子查询

ANY和SOME关键字是同义词,表示满足其中任一条件.例如:

CREATE table tb1(num1 INT NOT NULL);
CREATE table tb2(num2 INT NOT NULL);

-- 插入数据
INSERT INTO tb1 values(1),(5),(13),(27);
INSERT INTO tb2 values(6),(14),(11),(20);

-- 查询
SELECT num1 FROM tb1 WHERE num1 > ANY (SELECT num2 FROM tb2);

查询出来的值问tb1中大于tb2中任意的一个值即可.结果为13和27.

带ALL关键字的子查询

ALL与ANY不一样,需要满足全部条件才可以被查询出来,例如上面代码中如果ANY改为ALL,结果为27.

带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意子查询,系统对其判断是否有返回值,没有返回值EXISTS为false,此时外层的语句将要不会查询.

例如:查询suppliers表中是否存在s_id=107的供应商,如果存在则查询fruits表中的数据:

1
2
3

SELECT * FROM fruits
WHERE EXISTS
(SELECT `s_name` FROM suppliers WHERE `s_id` = 107);

EXISTS关键字可以接NOT修饰,表示如果不存在就会执行外层语句.

1
2
3

SELECT * FROM fruits
WHERE NOT EXISTS
(SELECT `s_name` FROM suppliers WHERE `s_id` = 107);

带IN关键字的子查询

IN 关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列的值将提供给外层查询语句进行比较操作.与上文讲的IN是一个东西,也可以用NOT IN.

合并查询结果

UNION关键字,用来合并两个结果格式相同的SELECT语句查询出来的结果.

例如:

SELECT s_id,f_name,f_price
FROM fruits
WHERE f_price < 9.0
UNION (ALL)
SELECT s_id,f_name,f_price
FROM fruits
WHERE s_id IN (101,103);

加ALL可以不删重复行,如果确定不会存在重复行,就用NUION ALL来减少查询所占资源.

别名的使用

格式为:

1	表名\|列名 [AS] 别名

不说,都懂.

使用正则表达式进行查询

与LIKE用法类似,区别是LIKE后接带’_’或者’%’的字符串,REGEXP后面接的是代表正则的字符串.

正则表达式参考其他的文档.