一、MySQL查询
1、mysql查询的执行路径
- 单表查询：简单查询
- 多表查询：连续查询
- 联合查询：
3、查询类型
- 投影：挑选要显示的字段。
语句说明：SELECT 字段1, 字段2, ... FROM tb_name; //挑选指定字段
SELECT * FROM tb_name; //挑选所有字段
- 选择：挑选符合条件的行。
语句说明：SELECT 字段1, ... FROM tb_name WHERE 子句; //子句也就是布尔表达式
4、布尔表达式
比较操作符，用以做数值比较：
= ：等值比较
<=> ：控制安全比较，用以与NULL做比较，
<> ：不等于
<
<=
>
>=
判断空值比较：
IS NULL ：是空值
IS NOT NULL ：不是空值
模糊匹配，用来做字符比较：
LIKE： 支持的通配符： %(任意长度的任意字符)， _（任意单个字符）
RLIKE， REGEXP： 支持使用正则表达式 。
组合条件表达式：
NOT, !
AND, &&
OR, ||
其他一些表达式：
IN： 判断指定字段的值是否在给定在列表中；
BETWEEN ... AND ...：位于指定的范围之间 ；
ORDER BY .... ASC | DESC ：ASC为升序并为默认排序方式，DESC为降序排序；
聚合函数：
SUM(), AVG(), MAX(), MIN(), COUNT()
SELECT语句的执行流程：
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT
SELECT常用修饰符：
- DISTINCT：指定的结果相同的只显示一次；
- SQL_CACHE：缓存于查询缓存中；
- SQL_NO_CACHE：不缓存查询结果；
二、举例
1、创建students表
MariaDB [test]> 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(20),ClassID TINYINT UNSIGNED);
Query OK, 0 rows affected (0.03 sec)
MariaDB [test]> 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(20) | YES | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec) 2、插入数据
MariaDB [test]> SELECT * FROM students;
+-----+---------------+-----+--------+-------------+---------+
| SID | Name | Age | Gender | Tutor | ClassID |
+-----+---------------+-----+--------+-------------+---------+
| 1 | Feng QinYang | 25 | M | Lu Zhishen | 2 |
| 2 | Yang Guo | 23 | M | Xiao Longnv | 1 |
| 3 | Pan Jinlian | 28 | F | Xi Menqing | 2 |
| 4 | Duan Yu | 20 | M | Mu Wanqing | 3 |
| 5 | Xu Zhu | 26 | M | Yuan Ji | 3 |
| 6 | Xiao Feng | 35 | M | Jue Yuan | 3 |
| 7 | Mie Jueshitai | 56 | F | Zhou Zhiruo | 1 |
| 8 | Guo Jing | 30 | M | Song Jiang | 1 |
| 9 | Zhao Ming | 24 | F | Zhang Wuji | 2 |
+-----+---------------+-----+--------+-------------+---------+
9 rows in set (0.00 sec) 3、查询举例
查询年龄在24之30之间的人：
MariaDB [test]> SELECT Name,Age FROM students WHERE Age BETWEEN 24 AND 30;
+--------------+-----+
| Name | Age |
+--------------+-----+
| Feng QinYang | 25 |
| Pan Jinlian | 28 |
| Xu Zhu | 26 |
| Guo Jing | 30 |
| Zhao Ming | 24 |
+--------------+-----+
5 rows in set (0.05 sec) 查询年龄是24，25，26的人：
MariaDB [test]> SELECT Name,Age FROM students WHERE Age IN (24,25,26);
+--------------+-----+
| Name | Age |
+--------------+-----+
| Feng QinYang | 25 |
| Xu Zhu | 26 |
| Zhao Ming | 24 |
+--------------+-----+
3 rows in set (0.00 sec) 查询Name以Z开头的人：
MariaDB [test]> SELECT Name FROM students WHERE Name LIKE 'Z%';
+-----------+
| Name |
+-----------+
| Zhao Ming |
+-----------+
1 row in set (0.00 sec) 也可以使用正则：
MariaDB [test]> SELECT Name FROM students WHERE Name RLIKE '^Z.*';
+-----------+
| Name |
+-----------+
| Zhao Ming |
+-----------+
1 row in set (0.06 sec) 查询Tutor为NULL值的人
MariaDB [test]> INSERT INTO students VALUES (10,'Xiao Ming',10,'M',NULL,4);
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> SELECT Name,Tutor FROM students WHERE Tutor IS NULL;
+-----------+-------+
| Name | Tutor |
+-----------+-------+
| Xiao Ming | NULL |
+-----------+-------+
1 row in set (0.02 sec) 查询年龄小于30且Gender为F的人：
MariaDB [test]> SELECT Name,Age,Gender FROM students WHERE Age < 30 AND Gender ='F';
+-------------+-----+--------+
| Name | Age | Gender |
+-------------+-----+--------+
| Pan Jinlian | 28 | F |
| Zhao Ming | 24 | F |
+-------------+-----+--------+
2 rows in set (0.00 sec) 上例的输出结果以降序输出，若不指定即为升序输出：
MariaDB [test]> SELECT Name,Age,Gender FROM students WHERE Age < 30 AND Gender ='F' ORDER BY Name DESC;
+-------------+-----+--------+
| Name | Age | Gender |
+-------------+-----+--------+
| Zhao Ming | 24 | F |
| Pan Jinlian | 28 | F |
+-------------+-----+--------+
2 rows in set (0.00 sec) 更加性别分组并进行求和：
MariaDB [test]> SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| F | 108 |
| M | 169 |
+--------+----------+
2 rows in set (0.05 sec) 根据班级分组并求出每班级的人数及年龄之和：
MariaDB [test]> SELECT ClassID,Count(Name),Sum(Age) FROM students GROUP BY ClassID;
+---------+-------------+----------+
| ClassID | Count(Name) | Sum(Age) |
+---------+-------------+----------+
| 1 | 3 | 109 |
| 2 | 3 | 77 |
| 3 | 3 | 81 |
| 4 | 1 | 10 |
+---------+-------------+----------+
4 rows in set (0.00 sec) 查询班级人数大于等于2人的班级：
MariaDB [test]> SELECT ClassID FROM students GROUP BY ClassID HAVING Count(Name) >= 2;
+---------+
| ClassID |
+---------+
| 1 |
| 2 |
| 3 |
+---------+
3 rows in set (0.00 sec) 查询students表仅显示前两个：
MariaDB [test]> SELECT * FROM students LIMIT 2;
+-----+--------------+-----+--------+-------------+---------+
| SID | Name | Age | Gender | Tutor | ClassID |
+-----+--------------+-----+--------+-------------+---------+
| 1 | Feng QinYang | 25 | M | Lu Zhishen | 2 |
| 2 | Yang Guo | 23 | M | Xiao Longnv | 1 |
+-----+--------------+-----+--------+-------------+---------+
2 rows in set (0.00 sec) 查询students表跳过前两行后在显示三行：
MariaDB [test]> SELECT * FROM students LIMIT 2,3;
+-----+-------------+-----+--------+------------+---------+
| SID | Name | Age | Gender | Tutor | ClassID |
+-----+-------------+-----+--------+------------+---------+
| 3 | Pan Jinlian | 28 | F | Xi Menqing | 2 |
| 4 | Duan Yu | 20 | M | Mu Wanqing | 3 |
| 5 | Xu Zhu | 26 | M | Yuan Ji | 3 |
+-----+-------------+-----+--------+------------+---------+
3 rows in set (0.02 sec) ![一个命令备份/还原Win10驱动，第三方软件可下岗[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)