Mysql的数据查询操作
2018-04-23 本文已影响0人
A04
创建一张数据表并插入数据，作为例子
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)
-> );
Query OK, 0 rows affected (0.39 sec)
mysql>
mysql> 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);
Query OK, 16 rows affected (0.09 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql>
1. 查询所有字段
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
mysql>
2. 查询指定字段
mysql> select f_id,f_name from fruits;
+------+------------+
| f_id | f_name |
+------+------------+
| a1 | apple |
| a2 | apricot |
| b1 | blackberry |
| b2 | berry |
| b5 | xxxx |
| bs1 | orange |
| bs2 | melon |
| c0 | cherry |
| l2 | lemon |
| m1 | mango |
| m2 | xbabay |
| m3 | xxtt |
| o2 | coconut |
| t1 | banana |
| t2 | grape |
| t4 | xbababa |
+------+------------+
16 rows in set (0.00 sec)
mysql>
3. 查询指定记录
mysql> SELECT f_name, f_price
-> FROM fruits
-> WHERE f_price = 10.2;
+------------+---------+
| f_name | f_price |
+------------+---------+
| blackberry | 10.20 |
+------------+---------+
1 row in set (0.03 sec)
mysql>
mysql> SELECT f_name, f_price
-> FROM fruits
-> WHERE f_name = 'apple';
+--------+---------+
| f_name | f_price |
+--------+---------+
| apple | 5.20 |
+--------+---------+
1 row in set (0.00 sec)
mysql> SELECT f_name, f_price FROM fruits WHERE f_price < 10;
+---------+---------+
| f_name | f_price |
+---------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+---------+---------+
11 rows in set (0.00 sec)
mysql>
4. 带IN关键字的查询
mysql> SELECT s_id,f_name, f_price
-> FROM fruits
-> WHERE s_id NOT IN (101,102)
-> ORDER BY f_name;
+------+---------+---------+
| s_id | f_name | f_price |
+------+---------+---------+
| 103 | apricot | 2.20 |
| 104 | berry | 7.60 |
| 103 | coconut | 9.20 |
| 104 | lemon | 6.40 |
| 106 | mango | 15.60 |
| 105 | melon | 8.20 |
| 107 | xbababa | 3.60 |
| 105 | xbabay | 2.60 |
| 105 | xxtt | 11.60 |
| 107 | xxxx | 3.60 |
+------+---------+---------+
10 rows in set (0.00 sec)
mysql>
5. 带BETWEEN AND的范围查询
mysql> SELECT f_name, f_price
-> FROM fruits
-> WHERE f_price BETWEEN 2.00 AND 10.20;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
12 rows in set (0.00 sec)
mysql> SELECT f_name, f_price
-> FROM fruits
-> WHERE f_price NOT BETWEEN 2.00 AND 10.20;
+--------+---------+
| f_name | f_price |
+--------+---------+
| orange | 11.20 |
| mango | 15.60 |
| xxtt | 11.60 |
| banana | 10.30 |
+--------+---------+
4 rows in set (0.00 sec)
mysql>
6. 带LIKE的字符匹配查询
百分号通配符（%）
mysql> SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%';
+------+------------+
| f_id | f_name |
+------+------------+
| b1 | blackberry |
| b2 | berry |
| t1 | banana |
+------+------------+
3 rows in set (0.00 sec)
mysql> SELECT f_id, f_name FROM fruits WHERE f_name LIKE '%g%';
+------+--------+
| f_id | f_name |
+------+--------+
| bs1 | orange |
| m1 | mango |
| t2 | grape |
+------+--------+
3 rows in set (0.00 sec)
mysql> SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';
+------------+
| f_name |
+------------+
| blackberry |
| berry |
+------------+
2 rows in set (0.00 sec)
下划线通配符（_）
mysql> SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____y';
+------+--------+
| f_id | f_name |
+------+--------+
| b2 | berry |
+------+--------+
1 row in set (0.00 sec)
mysql>
创建另一张数据表并插入数据，作为例子
mysql> 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)
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> 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');
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
7. 查询空值
mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;
+-------+----------+---------+
| c_id | c_name | c_email |
+-------+----------+---------+
| 10003 | Netbhood | NULL |
+-------+----------+---------+
1 row in set (0.00 sec)
mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;
+-------+---------+-------------------+
| c_id | c_name | c_email |
+-------+---------+-------------------+
| 10001 | RedHook | LMing@163.com |
| 10002 | Stars | Jerry@hotmail.com |
| 10004 | JOTO | sam@hotmail.com |
+-------+---------+-------------------+
3 rows in set (0.00 sec)
mysql>
8. 带AND的多条件查询
mysql> SELECT s_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5;
+------+---------+------------+
| s_id | f_price | f_name |
+------+---------+------------+
| 101 | 5.20 | apple |
| 101 | 10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.00 sec)
mysql> SELECT f_id, f_price, f_name FROM fruits
-> WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';
+------+---------+--------+
| f_id | f_price | f_name |
+------+---------+--------+
| a1 | 5.20 | apple |
+------+---------+--------+
1 row in set (0.00 sec)
mysql>
8. 带OR的多条件查询
mysql> SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 101 | blackberry | 10.20 |
| 102 | orange | 11.20 |
| 101 | cherry | 3.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
+------+------------+---------+
6 rows in set (0.00 sec)
mysql> SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102);
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 101 | blackberry | 10.20 |
| 102 | orange | 11.20 |
| 101 | cherry | 3.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
+------+------------+---------+
6 rows in set (0.00 sec)
mysql>
9. 查询结果不重复
mysql> SELECT DISTINCT s_id FROM fruits;
+------+
| s_id |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
+------+
7 rows in set (0.00 sec)
mysql>
10. 对查询结果排序
单列排序
mysql> SELECT f_name, f_price FROM fruits ORDER BY f_name;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.60 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
+------------+---------+
16 rows in set (0.00 sec)
mysql>
多列排序
mysql>SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.60 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
+------------+---------+
16 rows in set (0.00 sec)
mysql>
指定排序方向
mysql> SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
+------------+---------+
| f_name | f_price |
+------------+---------+
| mango | 15.60 |
| xxtt | 11.60 |
| orange | 11.20 |
| banana | 10.30 |
| blackberry | 10.20 |
| coconut | 9.20 |
| melon | 8.20 |
| berry | 7.60 |
| lemon | 6.40 |
| grape | 5.30 |
| apple | 5.20 |
| xxxx | 3.60 |
| xbababa | 3.60 |
| cherry | 3.20 |
| xbabay | 2.60 |
| apricot | 2.20 |
+------------+---------+
16 rows in set (0.00 sec)
mysql> SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;
+---------+------------+
| f_price | f_name |
+---------+------------+
| 15.60 | mango |
| 11.60 | xxtt |
| 11.20 | orange |
| 10.30 | banana |
| 10.20 | blackberry |
| 9.20 | coconut |
| 8.20 | melon |
| 7.60 | berry |
| 6.40 | lemon |
| 5.30 | grape |
| 5.20 | apple |
| 3.60 | xbababa |
| 3.60 | xxxx |
| 3.20 | cherry |
| 2.60 | xbabay |
| 2.20 | apricot |
+---------+------------+
16 rows in set (0.00 sec)
mysql>
11. 分组查询
创建分组
mysql> SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;
+------+-------+
| s_id | Total |
+------+-------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
+------+-------+
7 rows in set (0.00 sec)
mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;
+------+-------------------------+
| s_id | Names |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 106 | mango |
| 107 | xxxx,xbababa |
+------+-------------------------+
7 rows in set (0.00 sec)
mysql>
使用HAVING过滤分组
mysql> SELECT s_id, GROUP_CONCAT(f_name) AS Names
-> FROM fruits
-> GROUP BY s_id HAVING COUNT(f_name) > 1;
+------+-------------------------+
| s_id | Names |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 107 | xxxx,xbababa |
+------+-------------------------+
6 rows in set (0.00 sec)
mysql>
在GROUP BY子句中使用WITH ROLLUP
mysql> SELECT s_id, COUNT(*) AS Total
-> FROM fruits
-> GROUP BY s_id WITH ROLLUP;
+------+-------+
| s_id | Total |
+------+-------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
| NULL | 16 |
+------+-------+
8 rows in set (0.00 sec)
mysql>
多字段分组
mysql> SELECT * FROM fruits group by s_id,f_name;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| bs1 | 102 | orange | 11.20 |
| a2 | 103 | apricot | 2.20 |
| o2 | 103 | coconut | 9.20 |
| b2 | 104 | berry | 7.60 |
| l2 | 104 | lemon | 6.40 |
| bs2 | 105 | melon | 8.20 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| m1 | 106 | mango | 15.60 |
| t4 | 107 | xbababa | 3.60 |
| b5 | 107 | xxxx | 3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
mysql>
GROUP BY和ORDER BY 一起使用完成对分组的排序
mysql> 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)
-> ) ;
Query OK, 0 rows affected (0.20 sec)
mysql> 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);
Query OK, 11 rows affected (0.08 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT o_num, SUM(quantity * item_price) AS orderTotal
-> FROM orderitems
-> GROUP BY o_num
-> HAVING SUM(quantity*item_price) >= 100;
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30001 | 268.80 |
| 30003 | 1000.00 |
| 30004 | 125.00 |
| 30005 | 236.85 |
+-------+------------+
4 rows in set (0.01 sec)
mysql> SELECT o_num, SUM(quantity * item_price) AS orderTotal
-> FROM orderitems
-> GROUP BY o_num
-> HAVING SUM(quantity*item_price) >= 100
-> ORDER BY orderTotal;
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30004 | 125.00 |
| 30005 | 236.85 |
| 30001 | 268.80 |
| 30003 | 1000.00 |
+-------+------------+
4 rows in set (0.00 sec)
mysql>
12. 使用LIMIT限制查询结果数量
mysql> SELECT * From fruits LIMIT 4;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
+------+------+------------+---------+
4 rows in set (0.00 sec)
mysql> SELECT * From fruits LIMIT 4, 3;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
+------+------+--------+---------+
3 rows in set (0.00 sec)
mysql>
