基本查询语句
一条查询语句的顺序应该是
from
where
group by
having
select
distinct
order by
limit
这是必须搞清的
这要以后写sql的时候不会出现 group by 写在 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)
);
# 为了演示如何使用SELECT语句,需要插入如下数据:
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);
- 使用SELECT语句查询f_id,f_name字段的数据。
SELECT f_id, f_name FROM fruits;
where
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’;
- 查询价格在5-10之间的所有信息
select *
from fruits
where f_price between 5 and 10 ;
- 查询价格小于10的水果的名称
SELECT f_name, f_price
FROM fruits
WHERE f_price < 10;
IN 关键字查询
- s_id为101和102的记录
SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id IN (101,102) ;
- 查询所有s_id不等于101也不等于102的记录
SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id NOT IN (101,102);
-
BETWEEN 关键字
-
查询价格在2.00元到10.20元之间的水果名称和价格
SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;
LIKE 字符串匹配
- 查询以’b’开头,并以’y’结尾的水果的名称
% 通配符匹配任意多个
SELECT f_name
FROM fruits
WHERE f_name LIKE ‘b%y’;
- 在fruits表中,查询以字母’y’结尾,且’y’前面只有4个字母的记录
下划线 _ 表示一个字符
SELECT f_id, f_name FROM fruits WHERE f_name LIKE ‘____y’;
空值查询 IS NULL , IS NOT NULL
建表时是可以指定列包含空值的,空值不是数字0也不是一个空的字符串,若果当你的数据中有空值,你使用 where field=’’ 或者 where field=0 是不能查出为null的那一行数据
准备测试数据
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');
SELECT COUNT(*) AS cust_num FROM customers;
- 查询customers表中c_email为空的记录的c_id、c_name和c_email字段值
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;
和is null 相反查询不为空使用 is not null
ADN OR 多条件查询
- 在fruits表中查询s_id = 101,并且f_price大于等于5的水果价格和名称
SELECT f_id, f_price, f_name FROM fruits WHERE s_id = ‘101’ AND f_price >=5;
- 在fruits表中查询s_id = 101或者102,且f_price大于5,并且f_name=‘apple’的水果价格和名称
SELECT f_id, f_price, f_name FROM fruits
WHERE s_id IN(‘101’, ‘102’) AND f_price >= 5 AND f_name = ‘apple’;
- 查询s_id=101或者s_id=102的水果供应商的f_price和f_name,
SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;
我们可以看到 IN 可以做的OR也可以实现,但还是要注意应用场景
DISTINCT 去重
我们可以看到当我们查询s_id 的时候,每一行的s_id 都会给列出来,如果我们在实际的应用场景中需要去重怎么办,可以使用distinct
distinct 去重
重复的s_id已经被去掉了
order by 排序
单列排序
- 查询fruits表的f_price字段值,并对其进行排序,默认是(asc)升序,
SELECT f_name FROM fruits ORDER BY f_price;
多列排序
- 查询fruits表中的f_name和f_price字段,先按f_price排序,再按f_name排序,
SELECT f_name, f_price FROM fruits ORDER BY f_price,f_name;
当f_price 一样的时候,对f_name这个字段进行排序
- 查询fruits表中的f_name和f_price字段,对结果按f_price降序方式排序
SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
- 查询fruits表,先按f_price降序排序,再按f_name字段升序排序
SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;
group by 数据分组
这里需要注意一下,分组查询的字段,假设你对 name 字段进行group by了,那么你select 的时候需要使用name字段,如果是别的字段则会抛异常的,如下图
这里抛不抛异常其实是取决于你的sql_mode这个变量, 这里有个例子很好的解释了这个变量的作用
sql_mode变量使用参考链接
- 根据s_id对fruits表中的数据进行分组
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
第二句错误的sql,看到那个f_name字段没,为什么会抛异常,看上面那个参考链接
- 根据s_id对fruits表中的数据进行分组,将每个供应商的水果名称显示出来
SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;
- 根据s_id对fruits表中的数据进行分组,并显示水果种类大于1的分组信息
SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id HAVING COUNT(f_name) > 1;
group by with rollup的使用
- 根据s_id对fruits表中的数据进行分组,并显示记录数量
SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id WITH ROLLUP;
WITH ROLLUP 很好的可以统group by之后的数量,使用了with rollup 关键字之后,在所有查询出来的分组记录之后增加一条记录,记录计算查询出的所有记录的综合,即统计记录数量
- group by 多字段分组
group by 和 order by 结合使用
到这里相信你对sql的执行顺序已经很了解了
准备测试数据
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;
- 我们对上面那条sql排个序
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 使用
- 显示fruits表查询结果的前4行
SELECT * From fruits LIMIT 4;
- 在fruits表中,使用LIMIT子句,返回从第5个记录开始的,行数长度为3的记录,SQL语句如下:
SELECT * From fruits LIMIT 4, 3;
聚合函数 AVG COUNT MAX MIN SUM
- 查询customers表中总的行数,SQL语句如下:
SELECT COUNT(*) AS cust_num
FROM customers;
- 】查询customers表中有电子邮箱的顾客的总数,SQL语句如下:
SELECT COUNT(c_email) AS email_num
FROM customers;
- 在orderitems表中,使用COUNT()函数统计不同订单号中订购的水果种类,SQL语句如下:
SELECT o_num, COUNT(f_id)
FROM orderitems
GROUP BY o_num;
- 在orderitems表中查询30005号订单一共购买的水果总量,SQL语句如下:
SELECT SUM(quantity) AS items_total
FROM orderitems
WHERE o_num = 30005;
- 在fruits表中,查询s_id=103的供应商的水果价格的平均值,SQL语句如下:
SELECT AVG(f_price) AS avg_price
FROM fruits
WHERE s_id = 103;
#查询每一个供应商水果价格,我们对供应商分组即可,剩下的交给聚合函数
- 在fruits表中,查询每一个供应商的水果价格的平均值,SQL语句如下:
SELECT s_id,AVG(f_price) AS avg_price
FROM fruits
GROUP BY s_id;
- 在fruits表中查找市场上价格最高的水果,SQL语句如下:
SELECT MAX(f_price) AS max_price FROM fruits;
连表查询
表连接查询分为:①内连接,②外连接查询
准备测试数据
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');
#查看我们要连接表的结构
DESC fruits;
DESC suppliers;
连表之后你要知道这之后的表示怎么样的,这样在实际的应用中才可以更好的应对
- 查询供应f_id= ‘a1’的水果供应商提供的其他水果种类,SQL语句如下:
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’;
虽然where也可以连表,但是使用 inner join 是一种规范
- 在fruits表和suppliers表之间,使用INNER JOIN语法进行内连接查询,SQL语句如下:
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语句如下:
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 join :返回包括左表中的所有记录,和右边中连接字段相等的记录,即以左表为主
right 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)
) ;
# 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);
将要连接两张表的数据
我们可以看到在左表中c_id 只有 1,2,3,4 而orders 表中c_id =2 的数据是没有的所以当连表时,1002的数据会为null,即是以左表为主
右连接
与左连接相反
子查询
创建两张表
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);
# 插入数据
INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);
any关键字查询
ANY关键字接在一个比较操作符的后面,表示若与子查询返回的任何值比较为TRUE,则返回TRUE。
返回tbl2表的所有num2列,然后将tbl1中的num1的值与之进行比较,只要大于num2的任何1个值
SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
all关键字查询
all 关键字,要符合所有才为TRUE
返回tbl1表中比tbl2表num2 列所有值都大的值
SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);
exists 关键字
where exists (select * from xxx)
即至少有一条数据返回,才会为TRUE,反之FALSE
- 查询suppliers表中是否存在s_id=107的供应商,如果存在,则查询fruits表中的f_price大于10.20的记录
IN 关键字
- 在orderitems表中查询f_id为c0的订单号,并根据订单号查询具有订单号的客户c_id,
其实就是看 o_num 在子查询中的 o_num的数据
SELECT c_id FROM orders WHERE o_num IN
(SELECT o_num FROM orderitems WHERE f_id = ‘c0’);
别名
语法格式
表名 AS 表的别名
列名 AS 列的别名
正则表达式查询
- 在fruits表中,查询f_name字段以字母’b’开头的记录
SELECT * FROM fruits WHERE f_name REGEXP ‘^b’;
- 在fruits表中,查询f_name字段以“be”开头的记录
SELECT * FROM fruits WHERE f_name REGEXP ‘^be’;
- 在fruits表中,查询f_name字段以字母’y’结尾的记录
SELECT * FROM fruits WHERE f_name REGEXP ‘y$’;
字符’.’匹配任意一个字符。
在fruits表中,查询f_name字段值包含字母’a’与’g’且两个字母之间只有一个字母的记录
SELECT * FROM fruits WHERE f_name REGEXP ‘a.g’;
使用"“和”+"来匹配多个字符
星号’’匹配前面的字符任意多次,包括0次。加号’+’匹配前面的字符至少一次。
- 在fruits表中,查询f_name字段值以字母’b’开头,且’b’后面出现字母’a’的记录
SELECT * FROM fruits WHERE f_name REGEXP ‘^ba*’;
以字母b开头,匹配任意多个a字母
- 在fruits表中,查询f_name字段值以字母’b’开头,且’b’后面出现字母’a’至少一次的记录
SELECT * FROM fruits WHERE f_name REGEXP ‘^ba+’;