＜MySQL入门到精通＞阅读笔记之查询数据

基本查询语句

定义数据库表

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 
('al', 101, 'apple',5.2),
('bl', 101, 'blackberry',10.2),
('bs1', 102, 'organge',11.2)
 ('cO',101,'cherry',3.2),
 ('12',104,'lemon',6.4),
 ('b2',104,'berry',7.6),
 ('ml1',106,'mango',15.7),
 ('m2',105,'xbabay',2.6),
 ('14',107,'xbababa',3.6),
 ('m3',105,'xxtt',11.6),
('b5',107,'xxxx', 3.6);

查询f_id和f_name 字段的数据

SELECT f_id,f_name FROM fruits

单表查询

查询所有字段

SELECT * FROM fruits

在SELECT中使用*通配符查询所有字段

在SELECT语句中指定所有字段

SELECT f_id,s_id,f_name,f_price FROM fruits

一般情况下 除非需要表中的所有字段 否则最好不要使用通配符* 在获取不需要的列通常会降低查询效率

查询单个字段

SELECT f_id FROM fruits

查询多个字段

SELECT f_id,s_id,f_name,f_price FROM fruits

查询指定记录

在SELECT 语句中可以用个WHERE对数据进行过滤

// 查询价格为10.2的水果
SELECT f_name,f_price FROM fruits WHERE f_price = 10.2
// 查询水果名为apple
SELECT f_name,f_price FROM fruits WHERE f_name = 'apple'

带IN关键字的查询

IN关键字用来查询满足指定范围的条件记录

SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN(104,107)

相反的 可以使用关键字NOT来检索不在条件范围内的记录

SELECT s_id,f_name,f_price FROM fruits WHERE s_id NOT IN(104,107)

带BETWEEN AND的范围查询

BETWEEN AND 用来从查询某个范围内的值

// 查询2到10之间的价格
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 2 AND  10

// 查询不是2到10之间的价格
SELECT f_name,f_price FROM fruits WHERE f_price NOT  BETWEEN 2 AND  10

带LIKE的字符匹配查询

百分号通配符’%’ 匹配任意长度字符 包括零字符

// 查询名字B开头的水果
SELECT f_name FROM fruits WHERE f_name LIKE 'b%'
// 查询名字包含G的水果
SELECT f_name FROM fruits WHERE f_name LIKE '%g%'
// 查询名字B开头G结尾的水果
SELECT f_name FROM fruits WHERE f_name LIKE 'b%g'

下划线通配符’_’ 一次只能匹配任意字符

// 查询前面只有四个字母的且以'y'结尾的水果
SELECT f_name FROM fruits WHERE f_name LIKE '____y'

查询空值

数据表中可以指定某列是否可以包含NULL值 空值不等于0也不等于空字符串 在
SELECT语句中使用 IS NULL可以查询某字段内容为空的记录

// 查询名字为NULL值的水果
SELECT f_name FROM fruits WHERE f_name IS NULL 

// 查询名字不为NULL值的水果
SELECT f_name FROM fruits WHERE f_name NOT IS NULL 

带AND的多条件查询

使用SELECT 查询时 可以增加查询的限制条件 这样可以使结果更加精确

SELECT f_name FROM fruits WHERE f_name = 'apple' AND f_price > 10 

带OR 的多条件查询

与AND相反 在WHERE使用OR 表示只需要满足其中一个条件记录即可返回

SELECT f_name,s_id FROM fruits WHERE s_id =101 OR s_id = 102

查询结果不重复

SELECT DISTINCT s_id FROM fruits 

对查询结果排序

单列排序

在SELECT 语句中使用ORDER BY子句 对查询结果排序

// 对水果名排序 按字母表升序排序
SELECT f_name FROM fruits ORDER BY f_name

多列排序

SELECT f_name,f_price FROM fruits ORDER BY f_name,f_price

指定排序方向

默认情况 查询数据按字母升序排序 可以通过关键字DESC降序排序 ASC为升序

// 价格为降序排序
SELECT f_price FROM fruits ORDER BY f_price DESC
// 价格降序 名字升序
SELECT f_price,f_name FROM fruits ORDER BY  f_price DESC,f_name

分组查询

分组查询对数据按照某个或多个字段进行分组
GROUP BY 通常和集合函数一起使用例如 MAX MIN COUNT SUM AVG

创建分组

// 根据s_id 分组
SELECT s_id,COUNT(*) AS total FROM fruits GROUP BY s_id;

// 将每个分组中各个字段的值显示出来
SELECT s_id,GROUP_COncat(f_name) AS namess FROM  fruits GROUP BY  s_id;

使用HAVING 过滤分组

GROUP BY可以和HAVING一起限定显示记录满足条件 只有满足条件的分组才会被显示

select  s_id,group_concat(f_name)as namess from fruits group by s_id having count(f_name)>1

在GROUP BY 子句中使用WITH RILLUP

使用WITH RILLUP关键字后 在所有查询出的分组新增一条记录 该记录用于统计记录总和

SELECT s_id, COUNT(*) AS total FROM fruits GROUP BY s_id WITH ROLLUP

多字段分组

SELECT * FROM fruits GROUP BY s_id,f_name

GROUP BY 和 ORDER BY 一起使用

select * from fruits group by s_id order by s_id desc

使用Limit限制查询数量

SELECT * FROM fruits LIMIT 4

使用聚合函数查询

COUNT函数

COUNT函数统计数据表中包含的记录行的总数

SELECT COUNT(*) FROM fruits

SUM函数

SUM是一个求总和函数 返回指定列值的总和

SELECT SUM(f_price) FROM fruits

AVG函数

AVG通过计算返回的函数和每一行数据的和 求得指定列的平均值

SELECT AVG(f_price) FROM fruits

MAX函数

返回指定列中的最大值

SELECT MAX(f_price) FROM fruits

MIN函数

返回指定列中的最小值

SELECT MIN(f_price) FROM fruits

连接查询

连接查询是关系数据库主要特点 包括内外连接等 通过连接运算符实现多个表查询

内连接查询

内连接查询(INNER JOIN）使用比较运算符进行表间某些数据的操作 列出表中与连接条件相匹配的数据 组成新记录

CREATE TABLE suppliers(
  s_id INT NOT NULL AUTO_INCREMENT,
  s_name CHAR(50) NOT NULL ,
  s_city CHAR(50) NULL,
  s_zip CHAR(50) NULL,
  s_call CHAR(50) NOT NULL,
  PRIMARY KEY(s_id)
);

INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call)
VALUES(102,'LT Supplies','Chongqing', '400000','44333')

suppliers和fruits表都有相同的字段s_id 可以通过该字段来建立联系

SELECT s.s_id,s.s_name,f.f_name,f.f_price FROM  suppliers s,fruits f WHERE s.s_id = f.s_id

下面的内连接查询与前面完全相同的结果

SELECT s.s_id,s.s_name,f.f_name,f.f_price FROM fruits f INNER JOIN suppliers s ON s.s_id = f.s_id

使用WHERE定义连接条件比较简单明了 WHERE字句在某些时候会影响查询性能

SELECT * FROM  fruits f1,fruits f2 WHERE f1.s_id = f2.s_id 

此处查询的两个表示相同的表

外连查询

外连接查询将查询多个表中相关联的行， 内连接时， 返冋查询结果集合中的仅是符合
查询条件和连接条件的行。

LEFT JOIN ( 左连接 )

左连接的结果包括 LEFT OUTER 子句中指定的左表的所有行， 而不仅仅是连接列所匹配的行。 如果左表的某行在右表中没有匹配行， 则在相关联的结果行中， 右表的所有选择列表列均为空值

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'),

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

子查询

子查询指-个查询语句嵌套在另一个查询语句内部的查询

CREATE TABLE tbll ( numl INT NOT NULL);
CREATE TABLE tbl2 ( num2 INT NOT NULL);

INSERT INTO tbll VALUES(1), (5), (13), (27);
INSERT INTO tbl2 VALUES(6) ,(14), (11), (20);

带ANY SOME 关键字的子查询

SELECT numl FROM tbll WHERE numl > ANY(SELECT num2 FROM tbl2)

在子查询中， 返回的是 tbl2 表的所有 num2 列结果（ 6,14，11,20) , 然后将 tbll 中的 numl列的值与之进行比较， 只要大于 num2 列的任意一个数即为符合条件的结果

带 ALL 关键字的子查询

SELECT numl FROM tbll WHERE numl > ALL(SELECT num2 FROM tbl2)

在子查询中， 返回的是 tbl2 的 所 有 num2 列结果 (6,14,11,20) , 然后将 tbll 中的 numl
列的值与之进行比较， 大于所有 num2 列值的 numl 值只有 27, 因此返回结果为 27

带EXISTS关键字的字查询

EXISTS 关键字后面的参数是一个任意的子查询， 系统对子查询进行运算以判断它是否返回行， 如果至少返回一行， 那么 EXISTS 的结果为 true, 此时外层查询语句将进行查询； 如果子查询没有返冋任何行， 那么 EXISTS 返回的结果是 false, 此时外层不进行查询。

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

带IN 关键字的子查询

IN 关键字进行子查询时， 内层查询语句仅仅返回一个数据列， 这个数据列里的值将提供给外层查询语句进行比较操作

SELECT c_id FROM orders WHERE o_man IN(SELECT o_num FROM orderitems WHERE f_id = 10);