MySQL查询select常见用法
MySQL数据库使用 SQL select语句来查询数据。我们可以通过mysql>命令提示窗口在数据库查询数据。语法
SELECT column_name,column_name FROM table_name
SELECT * FROM table_name; 投影
SELECT column_name,column_name FROM table_name WHERE qualification; 选择
FROM自己：要查询的关系，表、多个表、其它SELECT语句。
WHERE子句：布尔关系表达式 =、> 、< 、>=、 use jiaowu;
mysql> show tables;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses |
| scores |
| sct |
| student |
| tutors |
+------------------+
5 rows in set (0.00 sec)
查看students表所有内容：
mysql> SELECT * FROM students;
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name | Age| Gender | CID1 | CID2 | TID| CreateTime |
+-----+--------------+------+--------+------+------+------+---------------------+
| 1 | GuoJing | 19| M | 2 | 7 | 3 | 2012-04-06 10:00:00 |
| 2 | YangGuo | 17| M | 2 | 3 | 1 | 0000-00-00 00:00:00 |
| 3 | DingDian | 25| M | 6 | 1 | 7 | 2012-04-06 10:00:00 |
| 4 | HuFei | 31| M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
| 5 | HuangRong | 16| F | 5 | 9 | 9 | 2012-04-06 10:00:00 |
| 6 | YueLingshang | 18| F | 8 | 4 | NULL| 2012-04-06 10:00:00 |
| 7 | ZhangWuji | 20| M | 1 | 7 | NULL| 2012-04-06 10:00:00 |
| 8 | Xuzhu | 26| M | 2 | 4 | NULL| 2012-04-06 10:00:00 |
| 9 | LingHuchong | 22| M | 11 | NULL | NULL | 0000-00-00 00:00:00 |
|10 | YiLin | 19| F | 18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
查看tutor表所有内容：
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age|
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai| F | 72 |
| 4 | OuYangfeng | | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang| M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
查看学生表中Name和Age
mysql> SELECT Name,Age FROM students;
+--------------+------+
| Name | Age|
+--------------+------+
| GuoJing | 19 |
| YangGuo | 17 |
| DingDian | 25 |
| HuFei | 31 |
| HuangRong | 16 |
| YueLingshang | 18 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LingHuchong | 22 |
| YiLin | 19 |
+--------------+------+
10 rows in set (0.00 sec)
用where条件语句查询
使用WHERE条件查询Age>20的学生；
mysql> SELECT Name,Age FROM students WHERE Age>20;
+-------------+------+
| Name | Age|
+-------------+------+
| DingDian | 25 |
| HuFei | 31 |
| Xuzhu | 26 |
| LingHuchong | 22 |
+-------------+------+
4 rows in set (0.00 sec)
多个条件查询
mysql> SELECT Name,Age FROM students WHERE Age>20 AND Gender='m';
+-------------+------+
| Name | Age|
+-------------+------+
| DingDian | 25 |
| HuFei | 31 |
| Xuzhu | 26 |
| LingHuchong | 22 |
+-------------+------+
4 rows in set (0.00 sec)
使用NOT语句查询
mysql> SELECT Name,Age,Gender FROM students WHERE NOT (Age>20 OR Gender='M');
+--------------+------+--------+
| Name | Age| Gender |
+--------------+------+--------+
| HuangRong | 16 | F |
| YueLingshang | 18 | F |
| YiLin | 19 | F |
+--------------+------+--------+
3 rows in set (0.00 sec)
使用between...and 条件语句
mysql> SELECT Name,Age,Gender FROM students WHERE Age BETWEEN 20 AND 25;
+-------------+------+--------+
| Name | Age| Gender |
+-------------+------+--------+
| DingDian | 25 | M |
| ZhangWuji | 20 | M |
| LingHuchong | 22 | M |
+-------------+------+--------+
使用IN条件语句：
mysql> SELECT Name,Age,Gender FROM students WHERE Age IN (18,20,25);
+--------------+------+--------+
| Name | Age| Gender |
+--------------+------+--------+
| DingDian | 25 | M |
| YueLingshang | 18 | F |
| ZhangWuji | 20 | M |
+--------------+------+--------+
查询字符为空
mysql> SELECT Name,Age,CID2 from students WHERE CID2 IS NULL;
+-------------+------+------+
| Name | Age| CID2 |
+-------------+------+------+
| LingHuchong | 22| NULL |
| YiLin | 19| NULL |
+-------------+------+------+
使用ORDER BY 排序；
mysql> SELECT Name,Age,CID2 from students WHERE CID2 IS NOT NULL ORDER BY Name;
+--------------+------+------+
| Name | Age| CID2 |
+--------------+------+------+
| DingDian | 25 | 1 |
| GuoJing | 19 | 7 |
| HuangRong | 16 | 9 |
| HuFei | 31 | 10 |
| Xuzhu | 26 | 4 |
| YangGuo | 17 | 3 |
| YueLingshang | 18 | 4 |
| ZhangWuji | 20 | 7 |
+--------------+------+------+
使用like语句
mysql> SELECT Name,Age FROM students WHERE Name LIKE 'Y%';
+--------------+------+
| Name | Age|
+--------------+------+
| YangGuo | 17 |
| YueLingshang | 18 |
| YiLin | 19 |
+--------------+------+
用AS取别名，用LIMIT取前2行
mysql> SELECT Name AS Student_name,Age FROM students LIMIT 2;
+--------------+------+
| Student_name | Age|
+--------------+------+
| GuoJing | 19 |
| YangGuo | 17 |
+--------------+------+
LIMIT 2,3略过前2行，
mysql> SELECT Name AS Student_name,Age FROM students LIMIT 2,3;
+--------------+------+
| Student_name | Age|
+--------------+------+
| DingDian | 25 |
| HuFei | 31 |
| HuangRong | 16 |
使用聚合运算 SUN()、AVG()、 MAX()、 MIN() 、COUNT()
mysql> SELECT AVG(Age) FROM students;
+----------+
| AVG(Age) |
+----------+
|21.3000 |
+----------+
mysql> SELECT MAX(Age) FROM students;
+----------+
| MAX(Age) |
+----------+
| 31 |
+----------+
mysql> SELECT MIN(Age) FROM students;
+----------+
| MIN(Age) |
+----------+
| 16 |
+----------+
mysql> SELECT COUNT(Age) FROM students;
+------------+
| COUNT(Age) |
+------------+
| 10 |
+------------+
mysql> SELECT SUM(Age) FROM students;
+----------+
| SUM(Age) |
+----------+
| 213 |
+----------+
GROUP BY 分组；
mysql> SELECT COUNT(CID1),CID1 FROM students GROUP BY CID1;
+-------------+------+
| COUNT(CID1) | CID1 |
+-------------+------+
| 1 | 1 |
| 3 | 2 |
| 1 | 5 |
| 1 | 6 |
| 2 | 8 |
| 1 | 11 |
| 1 | 18 |
+-------------+------+
GROUP BY 和HAVING 条件语句
mysql> SELECT COUNT(CID1) AS Person,CID1 FROM students GROUP BY CID1 HAVING Person>=2;
+--------+------+
| Person | CID1 |
+--------+------+
| 3 | 2 |
| 2 | 8 |
+--------+------+
多表查询
mysql> SELECT * FROM students,courses WHERE students.CID1=courses.CID;
+-----+--------------+------+--------+------+------+------+---------------------+-----+-------------------+-----+
| SID | Name | Age| Gender | CID1 | CID2 | TID| CreateTime | CID | Cname | TID |
+-----+--------------+------+--------+------+------+------+---------------------+-----+-------------------+-----+
| 1 | GuoJing | 19| M | 2 | 7 | 3 | 2012-04-06 10:00:00 | 2 | TaiJiquan | 3 |
| 2 | YangGuo | 17| M | 2 | 3 | 1 | 0000-00-00 00:00:00 | 2 | TaiJiquan | 3 |
| 3 | DingDian | 25| M | 6 | 1 | 7 | 2012-04-06 10:00:00 | 6 | Qishangquan | 5 |
| 4 | HuFei | 31| M | 8 | 10 | 5 | 2012-04-06 10:00:00 | 8 | Wanliduxing | 8 |
| 5 | HuangRong | 16| F | 5 | 9 | 9 | 2012-04-06 10:00:00 | 5 | Qianzhuwandushou | 4 |
| 6 | YueLingshang | 18| F | 8 | 4 | NULL | 2012-04-06 10:00:00 | 8 | Wanliduxing | 8 |
| 7 | ZhangWuji | 20| M | 1 | 7 | NULL | 2012-04-06 10:00:00 | 1 | Hamagong | 2 |
| 8 | Xuzhu | 26| M | 2 | 4 | NULL | 2012-04-06 10:00:00 | 2 | TaiJiquan | 3 |
+-----+--------------+------+--------+------+------+------+---------------------+-----+-------------------+-----+
mysql> SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1=courses.CID;
+--------------+-------------------+
| Name | Cname |
+--------------+-------------------+
| GuoJing | TaiJiquan |
| YangGuo | TaiJiquan |
| DingDian | Qishangquan |
| HuFei | Wanliduxing |
| HuangRong | Qianzhuwandushou |
| YueLingshang | Wanliduxing |
| ZhangWuji | Hamagong |
| Xuzhu | TaiJiquan |
+--------------+-------------------+
JOIN LEFT 左连接
mysql> SELECT s.SID,s.Name,c.CID,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;
+-----+--------------+------+-------------------+
| SID | Name | CID| Cname |
+-----+--------------+------+-------------------+
| 1 | GuoJing | 2 | TaiJiquan |
| 2 | YangGuo | 2 | TaiJiquan |
| 3 | DingDian | 6 | Qishangquan |
| 4 | HuFei | 8 | Wanliduxing |
| 5 | HuangRong | 5 | Qianzhuwandushou |
| 6 | YueLingshang | 8 | Wanliduxing |
| 7 | ZhangWuji | 1 | Hamagong |
| 8 | Xuzhu | 2 | TaiJiquan |
| 9 | LingHuchong | NULL | NULL |
|10 | YiLin | NULL | NULL |
+-----+--------------+------+-------------------+
子查询
mysql> SELECT Name,Age FROM students WHERE Age >(SELECT AVG(Age) FROM students);
+-------------+------+
| Name | Age|
+-------------+------+
| DingDian | 25 |
| HuFei | 31 |
| Xuzhu | 26 |
| LingHuchong | 22 |
显示老师和学生年龄一样，用IN查询
mysql> SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM tutors);
Empty set (0.00 sec)
UNION 联合
mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
+--------------+------+
| Name | Age|
+--------------+------+
| GuoJing | 19 |
| YangGuo | 17 |
| DingDian | 25 |
| HuFei | 31 |
| HuangRong | 16 |
| YueLingshang | 18 |
| ZhangWuji | 20 |
| Xuzhu | 26 |
| LingHuchong| 22 |
| YiLin | 19 |
| HongQigong | 93 |
| HuangYaoshi| 63 |
| Miejueshitai | 72 |
| OuYangfeng | 76 |
| YiDeng | 90 |
| YuCanghai | 56 |
| Jinlunfawang | 67 |
| HuYidao | 42 |
| NingZhongze| 49 |
+--------------+------+
页:
[1]