目录
7.1 基本查询语句
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);
SELECT f_id, f_name FROM fruits;7.2 单表查询
SELECT f_name FROM fruits;
SELECT f_name, f_price FROM fruits;
SELECT f_name, f_price FROM fruits WHERE f_price = 10.2;
SELECT f_name, f_price FROM fruits WHERE f_name = 'apple';
SELECT f_name, f_price FROM fruits WHERE f_price < 10;
-- IN
SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN (101,102) ORDER BY f_name;
SELECT s_id,f_name, f_price FROM fruits WHERE s_id NOT IN (101,102) ORDER BY f_name;
-- BETWEEN ... AND
SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;
SELECT f_name, f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20;
-- LIKE
-- % ： 匹配任意长度的字符，甚至包括零字符
-- _ ： 匹配任意一个字符
SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%';
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '%g%';
SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '----y';
-- 查询空值
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;
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;
SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;
SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;
SELECT f_id, f_price, f_name FROM fruits WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';
SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;
SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102);
-- 去重
SELECT DISTINCT s_id FROM fruits;
-- 排序
SELECT f_name FROM fruits ORDER BY f_name;
SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;
SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;
-- 分组
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
SELECT * FROM fruits GROUP BY s_id, f_name;
-- 连接分组中的信息
SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;
-- 过滤分组
SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1;
-- WITH ROLLUP 在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的哦在那个和，即统计记录数量
SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP;
-- 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)
);
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);
SELECT o_num, SUM(quantity * item_price) AS orderTotal FROM orderitems GROUP BY o_num HAVING SUM(quantity*item_price) >= 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 [位置偏移量,] 行数
SELECT * From fruits LIMIT 4;
SELECT * From fruits LIMIT 4, 3;7.3 使用聚合函数查询
-- COUNT(*) 计算表表中总行数，不管某列有数值或者为空值
-- COUNT(字段名) 计算指定列下的总行数，计算时会略空值的行
SELECT COUNT(*) AS cust_num FROM customers;
SELECT COUNT(c_email) AS email_num FROM customers;
SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num;
-- SUM
SELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005;
SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num;
-- AVG
SELECT AVG(f_price) AS avg_price FROM fruits WHERE s_id = 103;
SELECT s_id,AVG(f_price) AS avg_price FROM fruits GROUP BY s_id;
-- MAX
SELECT MAX(f_price) AS max_price FROM fruits;
SELECT s_id, MAX(f_price) AS max_price FROM fruits GROUP BY s_id;
SELECT MAX(f_name) FROM fruits;
-- MIN
SELECT MIN(f_price) AS min_price FROM fruits;
SELECT s_id, MIN(f_price) AS min_price FROM fruits GROUP BY s_id;7.4 连接查询
-- 内连接（INNER JOIN）
-- 使用比较运算符进行表间的某些列的比较操作。并列出浙西而表中与连接体哦阿健相匹配的数据行，
-- 组合成新记录，也就是说在内连接查询中，只有满足条件的记录才能出现在结果关系中
-- 外连接
-- 左外连接（LEFT JOIN） ：返回包括【左表】中的所有记录和【右表】中连接字段相等的记录
-- 右外连接（RIGHT 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(10) 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 (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;
-- 条件查询
SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits ,suppliers WHERE fruits.s_id = suppliers.s_id;
-- 内连接查询
SELECT suppliers.s_id, s_name,f_name, f_price FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
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';
-- 左连接
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);
SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id = orders.c_id;
-- 右连接
SELECT customers.c_id, orders.o_num FROM customers RIGHT OUTER JOIN orders ON customers.c_id = orders.c_id;
SELECT customers.c_id, orders.o_num FROM customers INNER JOIN orders ON customers.c_id = orders.c_id AND customers.c_id = 10001;
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;7.5 子查询
-- ANY && SOME || ALL
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);
SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);
-- EXISTS
-- 当子查询无返回行时，不进行父查询
SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);
SELECT * FROM fruits WHERE f_price>10.20 AND EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);
SELECT * FROM fruits WHERE NOT EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);
SELECT c_id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');
SELECT c_id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');
SELECT c_id FROM orders WHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');
SELECT s_id, f_name FROM fruits WHERE s_id =(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
SELECT s_id, f_name FROM fruits WHERE s_id <>(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
7.6 合并查询结果（UNION）
利用UNION关键字，可以给出多条SELECT语句，并将他们的结果组成成单个结果集。合并时，两个表对应的【列数】和【数据类型】必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分割。不使用ALL，执行的时候删除重复记录 ，所有返回行都是唯一的；使用关键字ALL的作用时不删除重复行也不对结果进行自动排序。
SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);
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);7.7 为表和字段取别名
SELECT * FROM orders AS o
WHERE o.o_num = 30001;
SELECT c.c_id, o.o_num
FROM customers AS c
LEFT OUTER JOIN orders AS o
ON c.c_id = o.c_id;
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';
SELECT f1.f_name AS fruit_name,
f1.f_price AS fruit_price
FROM fruits AS f1
WHERE f1.f_price < 87.8 使用正则表达式查询
-- ^ 匹配以特定字符或这字符串开头的文本
SELECT * FROM fruits WHERE f_name REGEXP '^b';
SELECT * FROM fruits WHERE f_name REGEXP '^be';
-- $ 匹配以特定字符或者字符串结尾的文本
SELECT * FROM fruits WHERE f_name REGEXP 'y$';
SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
-- . 来替代字符串中的任意一个字符
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
-- * 匹配前面的字符任意多次，包括0次
-- + 匹配前面的字符至少一次
SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
-- 匹配指定字符串，只要这个字符串在嘻哈寻文件中即可
-- 如要匹配多个字符串，多个字符串之间使用分隔符|隔开
-- LIKE关键字也可以匹配指定的字符串，但与REGEXP不同，LIKE匹配的字符串如果出现在文本中间，则找不到
SELECT * FROM fruits WHERE f_name REGEXP 'on';
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
SELECT * FROM fruits WHERE f_name LIKE 'on';
-- [] 指定一个字符集合，只匹配其中任何一个字符
-- [a-z] 表示集合区间为从a~z的字母
-- [0-9] 表示集合区间为所有数字
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
SELECT * FROM fruits WHERE s_id REGEXP '[456]';
SELECT * FROM fruits WHERE s_id REGEXP '[4-6]';
-- [^字符集合] 匹配不在指定集合中的任何字符
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
-- {m,n} 匹配前面的字符串不少于n次，不多于m次
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
扫一扫
264
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
