mysql语言之SELECT

SELECT

提供数据的查询功能
Syntax: SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]

1)查询表格所有的信息

MariaDB [hellodb]> SELECT * FROM students;

2）查询表格中某个特定字段

MariaDB [hellodb]> SELECT user,host FROM mysql.user;

3)字段显示别名功能

MariaDB [hellodb]> select user as 名字,host as 主机名 FROM mysql.user;
+--------+-----------+
| 名字   | 主机名    |
+--------+-----------+
| root   | 127.0.0.1 |
| root   | ::1       |
|        | ansible   |
| root   | ansible   |
|        | localhost |
| root   | localhost |
+--------+-----------+

4）WHERE过滤条件查询。

名称	操作符
算数比较符	+, -, *, /, %
比较操作符	=, !=, <>, <=>, >, >=, <, <=
判断空值	IS NULL
判断非空	IS NOT NULL
包含哪些字段	IN (element1, element2, …)
某一范围	BETWEEN min_num AND max_num

MariaDB [ydong]> SELECT * FROM students WHERE id!=1;
+----+----------+-------+
| id | name     | phone |
+----+----------+-------+
|  2 | xiaohong |   456 |
|  3 | xiaobai  |  NULL |
+----+----------+-------+
2 rows in set (0.02 sec)

MariaDB [ydong]> SELECT * FROM students WHERE id BETWEEN 1 AND 2;
+----+----------+-------+
| id | name     | phone |
+----+----------+-------+
|  1 | xiaoming |   123 |
|  2 | xiaohong |   456 |
+----+----------+-------+
2 rows in set (0.00 sec)


MariaDB [ydong]> SELECT * FROM students WHERE phone IS NULL;
+----+---------+-------+
| id | name    | phone |
+----+---------+-------+
|  3 | xiaobai |  NULL |
+----+---------+-------+
1 row in set (0.00 sec)


MariaDB [ydong]> SELECT * FROM students WHERE name IN ('xiaobai');
+----+---------+-------+
| id | name    | phone |
+----+---------+-------+
|  3 | xiaobai |  NULL |
+----+---------+-------+
1 row in set (0.01 sec)

5）like，查询符合条件定义的字段

• %：任意长度的任意字符
• _：任意单个字符

MariaDB [ydong]> SELECT * FROM students WHERE name LIKE '%g%';
+----+----------+-------+
| id | name     | phone |
+----+----------+-------+
|  1 | xiaoming |   123 |
|  2 | xiaohong |   456 |
+----+----------+-------+
2 rows in set (0.01 sec)

6）GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

• avg()：求平均数
• max()：最大
• min()：最小
• count()：计数
• sum()：求和

MariaDB [ydong]> select sex,avg(score) from students group by sex; 
+------+------------+
| sex  | avg(score) |
+------+------------+
| f    |         90 |
| m    |       87.5 |
+------+------------+
2 rows in set (0.01 sec)
根据性别取成绩平均值

7. HAVING: 对分组聚合运算后的结果指定过滤条件，只要出现了分组就必须要用having

MariaDB [ydong]> SELECT sex,avg(score) from students group by sex having avg(score) > 80 ;
+------+-------------------+
| sex  | avg(score)        |
+------+-------------------+
| f    | 86.66666666666667 |
+------+-------------------+
1 row in set (0.01 sec)

8）ORDER BY: 根据指定的字段对查询结果进行排序

MariaDB [ydong]> SELECT name,score FROM students ORDER BY score DESC;
+-----------+-------+
| name      | score |
+-----------+-------+
| xiaobai   | 95    |
| xiaohong  | 90    |
| xiaoming  | 80    |
| xiaolan   | 70    |
| xiaocheng | 60    |
| xiaohuang | 100   |
+-----------+-------+
6 rows in set (0.00 sec)
DESC 顺序排列


MariaDB [ydong]> SELECT name,score FROM students ORDER BY score ASC;
+-----------+-------+
| name      | score |
+-----------+-------+
| xiaohuang | 100   |
| xiaocheng | 60    |
| xiaolan   | 70    |
| xiaoming  | 80    |
| xiaohong  | 90    |
| xiaobai   | 95    |
+-----------+-------+
6 rows in set (0.02 sec)
逆序排列




MariaDB [ydong]> SELECT name,phone FROM students ORDER BY -phone ASC;
+-----------+-------+
| name      | phone |
+-----------+-------+
| xiaobai   |  NULL |
| xiaohuang |  NULL |
| xiaolan   |  NULL |
| xiaocheng |  NULL |
| xiaohong  |   456 |
| xiaoming  |   123 |
+-----------+-------+
6 rows in set (0.01 sec)
-phone意思是将null值按照逆序ASC还是顺序DESC排列，仅对数字有效

9)LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制

MariaDB [ydong]> SELECT name,phone FROM students ORDER BY -phone ASC LIMIT 2,4;
+-----------+-------+
| name      | phone |
+-----------+-------+
| xiaolan   |  NULL |
| xiaocheng |  NULL |
| xiaohong  |   456 |
| xiaoming  |   123 |
+-----------+-------+
4 rows in set (0.01 sec)
意思是从第三行往下数4行。

多表查询

1）left join 会读取A所有的数据，即使B表没有相对应的数据
我们以students为A表，B表为teachers表。

MariaDB [hellodb]> select stu.Name as stu_name ,t.Name as teacher_name  from students as stu left join teachers as t on stu.TeacherID=t.TID;

+---------------+---------------+
| stu_name      | teacher_name  |
+---------------+---------------+
| Shi Zhongyu   | Miejue Shitai |
| Shi Potian    | NULL          |
| Xie Yanke     | NULL          |
| Ding Dian     | Lin Chaoying  |
| Yu Yutong     | Song Jiang    |
...

内连接，取两张表交集处

MariaDB [hellodb]> SELECT stu.name as 学生名字,t.name as 教师名字 FROM students as stu inner join teachers as t on stu.TeacherID=t.TID;
+--------------+---------------+
| 学生名字     | 教师名字      |
+--------------+---------------+
| Yu Yutong    | Song Jiang    |
| Shi Zhongyu  | Miejue Shitai |
| Ding Dian    | Lin Chaoying  |
+--------------+---------------+
3 rows in set (0.02 sec)

右连接，与左连接相反

MariaDB [hellodb]> SELECT t.Name as 教师名字, stu.Name as 学生名字 FROM students as stu right join teachers as t on stu.TeacherID=t.TID;
+---------------+--------------+
| 教师名字      | 学生名字     |
+---------------+--------------+
| Miejue Shitai | Shi Zhongyu  |
| Lin Chaoying  | Ding Dian    |
| Song Jiang    | Yu Yutong    |
| Zhang Sanfeng | NULL         |
+---------------+--------------+
4 rows in set (0.01 sec)

4）

A表和B表同时显示

两张表同时显示使用union实现

MariaDB [hellodb]> SELECT Stuid,Name from students
    -> union
    -> SELECT Tid,Name from teachers;

5）

取去A和B交集之后的A的字段

MariaDB [hellodb]> SELECT stu.Name,t.Name AS teacher_name FROM students AS stu LEFT JOIN teachers AS t on stu.TeacherID=t.TID WHERE t.name is null;

子查询

在查询语句中在嵌套查询语句，性能一般来说比较差

用于where语句中的子查询

MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age > (SELECT Avg(Age) FROM students);
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Sun Dasheng  | 100 |
+--------------+-----+
5 rows in set (0.05 sec)

用在IN中的子查询

MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

用于FROM子句中的子查询

MariaDB [hellodb]> SELECT * FROM (SELECT ClassID,AVG(Age) AS aage FROM students GROUP BY ClassID HAVING ClassID IS NOT NULL) AS s WHERE s.aage < 30;
+---------+---------+
| ClassID | aage    |
+---------+---------+
|       1 | 20.5000 |
|       3 | 20.2500 |
|       4 | 24.7500 |
|       6 | 20.7500 |
|       7 | 19.6667 |
+---------+---------+
5 rows in set (0.00 sec)