Mysql的查询操作：
单表查询：简单查询
多表查询：连续查询
联合查询：
选择和投影：
投影： 挑选要显示的字段
选择： 挑选符合条件的行
投影： SELECT 字段1，字段2，...FROM tb_name;
SELECT * FROM tb_name;
选择： SELECT 字段1，... FROM tb_name WHERE 字句;
布尔条件表达式
布尔条件表达式操作符：
=
<=>:
<>
<
<=
>
>=
IS NULL
IS NOT NULL
LIKE: 支持的通配符： %(任意长度的任意字符）， _（任意单个字符）
RLIKE，REGEXP：支持使用正则表达式
IN： 判断制定字段的值是否在给定的列表中：
BETWEEN ... AND ...: 位于制定的范围之间
组合条件测试
NOT, !
AND,&&
OR, ||
聚合函数：
SUN(), AVG(), MAX(), MIN(), count()
SELECT 语句的执行流程
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... -->
SELECT --> LIMIT
SELECT语句：
DISTINCT： 指定的结果相同的只显示一次：
SQL_CACHE： 缓存于查询中：
SQL_NO_CACHE : 不缓存查询结果：
SELECT语句的执行流程：
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY --> SELECT --> LIMIT
SELECT语句：
DISTINCT： 指定的结果相同的只显示一次：
SQL_CACHE：缓存于查询缓存中：
SQL_NO_CACHE：不缓存查询结果：
示例：（创建表并指定查询条件）
mysql> CREATE TABLE students (SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED NOT NULL,Gender ENUM('F','M') NOT NULL,Tutor CHAR(30));
Query OK, 0 rows affected (0.04 sec)
mysql> DESC students;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| SID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | char(30) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| Tutor | char(30) | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)
mysql> INSERT INTO students VALUES (1,'Guo Jing',27,'M','Song Jiang'),(2,'Yang Guo',28,'M','Hu Sanniang'),(3,'Guo Polu',21,'M','Jia
Baoyu');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM students;
+-----+----------+-----+--------+-------------+
| SID | Name | Age | Gender | Tutor |
+-----+----------+-----+--------+-------------+
| 1 | Guo Jing | 27 | M | Song Jiang |
| 2 | Yang Guo | 28 | M | Hu Sanniang |
| 3 | Guo Polu | 21 | M | Jia Baoyu |
+-----+----------+-----+--------+-------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO students VALUES (4,'Xue Baochai',19,'F','Rong Momo'),(5,'Xia Yuhe',37,'F','Shi Qian'),(6,'Wu Yong',51,'M','Lin Daiyu');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM students;
+-----+-------------+-----+--------+-------------+
| SID | Name | Age | Gender | Tutor |
+-----+-------------+-----+--------+-------------+
| 1 | Guo Jing | 27 | M | Song Jiang |
| 2 | Yang Guo | 28 | M | Hu Sanniang |
| 3 | Guo Polu | 21 | M | Jia Baoyu |
| 4 | Xue Baochai | 19 | F | Rong Momo |
| 5 | Xia Yuhe | 37 | F | Shi Qian |
| 6 | Wu Yong | 51 | M | Lin Daiyu |
+-----+-------------+-----+--------+-------------+
6 rows in set (0.00 sec)
mysql> SELECT Name FROM students WHERE Age BETWEEN 25 AND 40;
+----------+
| Name |
+----------+
| Guo Jing |
| Yang Guo |
| Xia Yuhe |
+----------+
3 rows in set (0.00 sec)
mysql> SELECT Name,Age FROM students WHERE Age IN (25,26,27,28,29);
+----------+-----+
| Name | Age |
+----------+-----+
| Guo Jing | 27 |
| Yang Guo | 28 |
+----------+-----+
2 rows in set (0.00 sec)
mysql> SELECT Name FROM students WHERE Name LIKE 'X%';
+-------------+
| Name |
+-------------+
| Xue Baochai |
| Xia Yuhe |
+-------------+
2 rows in set (0.02 sec)
mysql> SELECT Name FROM students WHERE Name RLIKE '^X.*';
+-------------+
| Name |
+-------------+
| Xue Baochai |
| Xia Yuhe |
+-------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO students VALUES(7,'tom',11,'M','jerry');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO students VALUES(8,'tommy',13,'M',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+-----+-------------+-----+--------+-------------+
| SID | Name | Age | Gender | Tutor |
+-----+-------------+-----+--------+-------------+
| 1 | Guo Jing | 27 | M | Song Jiang |
| 2 | Yang Guo | 28 | M | Hu Sanniang |
| 3 | Guo Polu | 21 | M | Jia Baoyu |
| 4 | Xue Baochai | 19 | F | Rong Momo |
| 5 | Xia Yuhe | 37 | F | Shi Qian |
| 6 | Wu Yong | 51 | M | Lin Daiyu |
| 7 | tom | 11 | M | jerry |
| 8 | tommy | 13 | M | NULL |
+-----+-------------+-----+--------+-------------+
8 rows in set (0.00 sec)
mysql> SELECT Name,Tutor FROM students WHERE Tutor IS NULL;
+-------+-------+
| Name | Tutor |
+-------+-------+
| tommy | NULL |
+-------+-------+
1 row in set (0.01 sec)
mysql> SELECT Name,Tutor FROM students WHERE Tutor IS NOT NULL;
+-------------+-------------+
| Name | Tutor |
+-------------+-------------+
| Guo Jing | Song Jiang |
| Yang Guo | Hu Sanniang |
| Guo Polu | Jia Baoyu |
| Xue Baochai | Rong Momo |
| Xia Yuhe | Shi Qian |
| Wu Yong | Lin Daiyu |
| tom | jerry |
+-------------+-------------+
7 rows in set (0.01 sec)
mysql> SELECT Name,Age FROM students WHERE Age > 25 AND Gender = 'M';
+----------+-----+
| Name | Age |
+----------+-----+
| Guo Jing | 27 |
| Yang Guo | 28 |
| Wu Yong | 51 |
+----------+-----+
3 rows in set (0.00 sec)
根据名字进行排序：
mysql> SELECT Name,Age FROM students WHERE Age > 25 AND Gender = 'M' ORDER BY Name;
+----------+-----+
| Name | Age |
+----------+-----+
| Guo Jing | 27 |
| Wu Yong | 51 |
| Yang Guo | 28 |
+----------+-----+
3 rows in set (0.04 sec)
升序排序
mysql> SELECT Name,Age FROM students WHERE Age > 25 AND Gender = 'M'ORDER BY Name ASC;
+----------+-----+
| Name | Age |
+----------+-----+
| Guo Jing | 27 |
| Wu Yong | 51 |
| Yang Guo | 28 |
+----------+-----+
3 rows in set (0.01 sec)
降序排序
mysql> SELECT Name,Age FROM students WHERE Age > 25 AND Gender = 'M'ORDER BY Name DESC;
+----------+-----+
| Name | Age |
+----------+-----+
| Yang Guo | 28 |
| Wu Yong | 51 |
| Guo Jing | 27 |
+----------+-----+
3 rows in set (0.03 sec)
求所有用户的年龄之和
mysql> SELECT SUM(Age) FROM students;
+----------+
| SUM(Age) |
+----------+
| 207 |
+----------+
1 row in set (0.04 sec)
求所有用户年龄的平均值
mysql> SELECT AVG(Age) FROM students;
+----------+
| AVG(Age) |
+----------+
| 25.8750 |
+----------+
1 row in set (0.00 sec)
求所有用户年龄的最大值
mysql> SELECT MAX(Age) FROM students;
+----------+
| MAX(Age) |
+----------+
| 51 |
+----------+
1 row in set (0.00 sec)
求所有用户年龄的最小值
mysql> SELECT MIN(Age) FROM students;
+----------+
| MIN(Age) |
+----------+
| 11 |
+----------+
1 row in set (0.01 sec)
查看年龄字段的记录数
mysql> SELECT COUNT(Age) FROM students;
+------------+
| COUNT(Age) |
+------------+
| 8 |
+------------+
1 row in set (0.01 sec)
查看年龄大于25的人的个数
mysql> SELECT COUNT(Age) FROM students WHERE Age > 25;
+------------+
| COUNT(Age) |
+------------+
| 4 |
+------------+
1 row in set (0.01 sec)
查看年龄大于25的年龄之和
mysql> SELECT SUM(Age) FROM students WHERE Age > 25;
+----------+
| SUM(Age) |
+----------+
| 143 |
+----------+
1 row in set (0.00 sec)
根据Gendar进行分组，求各组的年龄之和
mysql> SELECT SUM(Age) FROM students GROUP BY Gender;
+----------+
| SUM(Age) |
+----------+
| 56 |
| 151 |
+----------+
2 rows in set (0.03 sec)
mysql> SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| F | 56 |
| M | 151 |
+--------+----------+
2 rows in set (0.00 sec)
添加一个新的字段
mysql> ALTER TABLE students ADD CLASSID TINYINT UNSIGNED;
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM students;
+-----+-------------+-----+--------+-------------+---------+
| SID | Name | Age | Gender | Tutor | CLASSID |
+-----+-------------+-----+--------+-------------+---------+
| 1 | Guo Jing | 27 | M | Song Jiang | NULL |
| 2 | Yang Guo | 28 | M | Hu Sanniang | NULL |
| 3 | Guo Polu | 21 | M | Jia Baoyu | NULL |
| 4 | Xue Baochai | 19 | F | Rong Momo | NULL |
| 5 | Xia Yuhe | 37 | F | Shi Qian | NULL |
| 6 | Wu Yong | 51 | M | Lin Daiyu | NULL |
| 7 | tom | 11 | M | jerry | NULL |
| 8 | tommy | 13 | M | NULL | NULL |
+-----+-------------+-----+--------+-------------+---------+
8 rows in set (0.04 sec)
更改字段CLASSID的值
mysql> UPDATE students SET ClassID = 2 WHERE SID = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE students SET ClassID = 3 WHERE SID = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE students SET ClassID = 3 WHERE SID = 3;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE students SET ClassID = 1 WHERE SID = 4;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE students SET ClassID = 2 WHERE SID = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE students SET ClassID = 1 WHERE SID = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE students SET ClassID = 1 WHERE SID = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE students SET ClassID = 4 WHERE SID = 8;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM students;
+-----+-------------+-----+--------+-------------+---------+
| SID | Name | Age | Gender | Tutor | CLASSID |
+-----+-------------+-----+--------+-------------+---------+
| 1 | Guo Jing | 27 | M | Song Jiang | 2 |
| 2 | Yang Guo | 28 | M | Hu Sanniang | 3 |
| 3 | Guo Polu | 21 | M | Jia Baoyu | 3 |
| 4 | Xue Baochai | 19 | F | Rong Momo | 1 |
| 5 | Xia Yuhe | 37 | F | Shi Qian | 2 |
| 6 | Wu Yong | 51 | M | Lin Daiyu | 1 |
| 7 | tom | 11 | M | jerry | 1 |
| 8 | tommy | 13 | M | NULL | 4 |
+-----+-------------+-----+--------+-------------+---------+
8 rows in set (0.00 sec)
根据班级分组，统计每班有多少人
mysql> SELECT CLASSID,COUNT(*) FROM students GROUP BY CLASSID;
+---------+----------+
| CLASSID | COUNT(*) |
+---------+----------+
| 1 | 3 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
+---------+----------+
4 rows in set (0.00 sec)
查看每一班学生年龄之和
mysql> SELECT CLASSID,COUNT(Name),Sum(Age) FROM students GROUP BY CLASSID;
+---------+-------------+----------+
| CLASSID | COUNT(Name) | Sum(Age) |
+---------+-------------+----------+
| 1 | 3 | 81 |
| 2 | 2 | 64 |
| 3 | 2 | 49 |
| 4 | 1 | 13 |
+---------+-------------+----------+
4 rows in set (0.00 sec)
统计每班人数大于2的班级
mysql> SELECT CLASSID FROM students GROUP BY CLASSID HAVING COUNT(Name) >=2;
+---------+
| CLASSID |
+---------+
| 1 |
| 2 |
| 3 |
+---------+
3 rows in set (0.00 sec)
查看年龄之和小于等于50的班级
mysql> SELECT CLASSID FROM students GROUP BY CLASSID HAVING SUM(Age) <= 50;
+---------+
| CLASSID |
+---------+
| 3 |
| 4 |
+---------+
2 rows in set (0.00 sec)
只显示students表的2行
mysql> SELECT * FROM students LIMIT 2;
+-----+----------+-----+--------+-------------+---------+
| SID | Name | Age | Gender | Tutor | CLASSID |
+-----+----------+-----+--------+-------------+---------+
| 1 | Guo Jing | 27 | M | Song Jiang | 2 |
| 2 | Yang Guo | 28 | M | Hu Sanniang | 3 |
+-----+----------+-----+--------+-------------+---------+
2 rows in set (0.00 sec)
以CLASSID做分组，并且班级号是非空，统计班级人的数量
mysql> SELECT CLASSID,Count(Name) FROM students WHERE CLASSID IS NOT NULL GROUP BY CLASSID;
+---------+-------------+
| CLASSID | Count(Name) |
+---------+-------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
+---------+-------------+
4 rows in set (0.00 sec)
统计班级里的人数至少有两人的班级
mysql> SELECT CLASSID,Count(Name) FROM students WHERE CLASSID IS NOT NULL GROUP BY CLASSID HAVING Count(Name) >= 2;
+---------+-------------+
| CLASSID | Count(Name) |
+---------+-------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 2 |
+---------+-------------+
3 rows in set (0.00 sec)
以性别分组统计各性别的年龄之和
mysql> SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| F | 56 |
| M | 151 |
+--------+----------+
2 rows in set (0.01 sec)
显示平均年龄大于25的班级
mysql> SELECT CLASSID,AVG(Age) FROM students GROUP BY CLASSID HAVING AVG(AGE) > 25;
+---------+----------+
| CLASSID | AVG(Age) |
+---------+----------+
| 1 | 27.0000 |
| 2 | 32.0000 |
+---------+----------+
2 rows in set (0.00 sec)
以性别为分组，显示年龄大于25的学生的年龄之和
mysql> SELECT Gender,SUM(Age) FROM students WHERE Age > 25 GROUP BY Gender;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| F | 37 |
| M | 106 |
+--------+----------+
2 rows in set (0.00 sec)
找出年龄大于19岁的人的名字和年龄，基于ORDER BY 排序
mysql> SELECT Age,Name FROM students WHERE Age > 19 ORDER BY Age;
+-----+----------+
| Age | Name |
+-----+----------+
| 21 | Guo Polu |
| 27 | Guo Jing |
| 28 | Yang Guo |
| 37 | Xia Yuhe |
| 51 | Wu Yong |
+-----+----------+
5 rows in set (0.00 sec)
新建一个students 数据表：
mysql> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 2 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | 1 | NULL |
| 25 | Sun Dasheng | 100 | M | 2 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
以ClassID为分组，显示每班的人数统计
mysql> SELECT ClassID,Count(Name) From students GROUP BY ClassID;
+---------+-------------+
| ClassID | Count(Name) |
+---------+-------------+
| 1 | 5 |
| 2 | 5 |
| 3 | 3 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+-------------+
7 rows in set (0.01 sec)
以ClassID为分组，统计班级人数至少有2个的班级
mysql> SELECT ClassID,Count(Name) From students GROUP BY ClassID HAVING Count(Name) >= 2;
+---------+-------------+
| ClassID | Count(Name) |
+---------+-------------+
| 1 | 5 |
| 2 | 5 |
| 3 | 3 |
| 4 | 4 |
| 6 | 4 |
| 7 | 3 |
+---------+-------------+
6 rows in set (0.00 sec)
以Gender为分组，显示年龄之和
mysql> SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| F | 190 |
| M | 495 |
+--------+----------+
2 rows in set (0.00 sec)
以班级为分组，统计平均年龄大于25的班级
mysql> SELECT AVG(Age),ClassID FROM students GROUP BY ClassID HAVING AVG(Age) > 25;
+----------+---------+
| AVG(Age) | ClassID |
+----------+---------+
| 45.0000 | 2 |
| 46.0000 | 5 |
+----------+---------+
2 rows in set (0.00 sec)
以Gender为分组，显示各组中学员年龄大于25的学员年龄之和
mysql> SELECT Gender,SUM(Age) FROM students WHERE Age >25 GROUP BY Gender ;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| M | 317 |
+--------+----------+
1 row in set (0.00 sec)
阅读(1356) | 评论(0) | 转发(0) |
