MySQL查询操作

一般查询

语法：SELECT [DISTINCT] 字段1,字段2,…,字段n FROM 表明 WHERE 子句 GROUP BY 子句 HAVING子句 ORDER BY 子句 LIMIT 子句

检索不同的行 DISTINCT

默认会检索所有的行，加DISTINCT后可以过滤相同的行

限制结果LIMIT

完全限定表明

SELECT scores.score FROM scores;

排序

mysql> SELECT score FROM scores ORDER BY score ASC;

WHERE 子句

操作符： =；<>、!=;<;>;<=;>=;BETWEEN a AND b;IS NULL;IS NOT NULL;IN;AND;OR;NOT

mysql> SELECT * FROM scores WHERE score_id=1;
+----------+------------+----------+------------+-------+
| score_id | student_id | class_id | project_id | score |
+----------+------------+----------+------------+-------+
|        1 |          1 |        2 |          1 |    80 |
+----------+------------+----------+------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM scores WHERE score_id<>1;
+----------+------------+----------+------------+-------+
| score_id | student_id | class_id | project_id | score |
+----------+------------+----------+------------+-------+
|        2 |          1 |        2 |          2 |    95 |
|        3 |          1 |        2 |          3 |    78 |
|        4 |          2 |        1 |          1 |    69 |
|        5 |          2 |        1 |          2 |    52 |
|        6 |          2 |        1 |          3 |    98 |
|        7 |          3 |        3 |          1 |    46 |
|        8 |          3 |        3 |          2 |    87 |
|        9 |          3 |        3 |          3 |    56 |
|       10 |          4 |        2 |          1 |   100 |
|       11 |          4 |        2 |          2 |    59 |
|       12 |          4 |        2 |          3 |    87 |
|       13 |          5 |        3 |          1 |    69 |
|       14 |          5 |        3 |          2 |    74 |
|       15 |          5 |        3 |          3 |    87 |
+----------+------------+----------+------------+-------+
14 rows in set (0.00 sec)

mysql> SELECT * FROM scores WHERE score<60;
+----------+------------+----------+------------+-------+
| score_id | student_id | class_id | project_id | score |
+----------+------------+----------+------------+-------+
|        5 |          2 |        1 |          2 |    52 |
|        7 |          3 |        3 |          1 |    46 |
|        9 |          3 |        3 |          3 |    56 |
|       11 |          4 |        2 |          2 |    59 |
+----------+------------+----------+------------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM scores WHERE score>=70 AND score<80;
+----------+------------+----------+------------+-------+
| score_id | student_id | class_id | project_id | score |
+----------+------------+----------+------------+-------+
|        3 |          1 |        2 |          3 |    78 |
|       14 |          5 |        3 |          2 |    74 |
+----------+------------+----------+------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM scores WHERE score BETWEEN 60 AND 70;
+----------+------------+----------+------------+-------+
| score_id | student_id | class_id | project_id | score |
+----------+------------+----------+------------+-------+
|        4 |          2 |        1 |          1 |    69 |
|       13 |          5 |        3 |          1 |    69 |
+----------+------------+----------+------------+-------+
2 rows in set (0.00 sec)

通配符：\%通配符：匹配任意字符（NULL除外） -通配符：匹配任意单个字符

SELECT * FROM  users WHERE username LIKE '\%gwx\%'
SELECT * FROM users WHERE username LIKE 'gw_'

正则搜索：

mysql> SELECT * FROM students WHERE student REGEXP  '^宋';
+------------+---------+----------+
| student_id | student | class_id |
+------------+---------+----------+
|          2 | 宋娜娜  |        1 |
|          5 | 宋健宇  |        3 |
+------------+---------+----------+
2 rows in set (0.00 sec)

创建计算字段

mysql> SELECT score,IF(score>60,'及格','不及格') AS '是否及格'  FROM scores;
+-------+----------+
| score | 是否及格 |
+-------+----------+
|    80 | 及格     |
|    95 | 及格     |
|    78 | 及格     |
|    69 | 及格     |
|    52 | 不及格   |
|    98 | 及格     |
|    46 | 不及格   |
|    87 | 及格     |
|    56 | 不及格   |
|   100 | 及格     |
|    59 | 不及格   |
|    87 | 及格     |
|    69 | 及格     |
|    74 | 及格     |
|    87 | 及格     |
+-------+----------+
15 rows in set (0.00 sec)

汇总数据

count avg sum max min

mysql> SELECT COUNT(*) FROM scores;
+----------+
| COUNT(*) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM scores WHERE score>60;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT AVG(score) FROM scores WHERE project_id=1;
+------------+
| AVG(score) |
+------------+
|    72.8000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(score),MIN(score) FROM scores;
+------------+------------+
| MAX(score) | MIN(score) |
+------------+------------+
|        100 |         46 |
+------------+------------+
1 row in set (0.00 sec)

分组数据

注：where与having区别，where过滤行，having过滤分组

-- 找出三年三班学生中平均分大于70分的
mysql> SELECT student_id,AVG(score) AS s_avg FROM scores WHERE class_id=3 GROUP BY student_id HAVING s_avg>70;
+------------+---------+
| student_id | s_avg   |
+------------+---------+
|          5 | 76.6667 |
+------------+---------+
1 row in set (0.00 sec)

子查询

WHERE子查询

-- 找出桂文贤同学的所有老师
-- 步骤：1.找出桂文贤同学所在班级2.找出教该班级的老师
mysql> SELECT teacher_id FROM teacher_project WHERE class_id=
    -> (SELECT class_id FROM students WHERE student='桂文贤');
+------------+
| teacher_id |
+------------+
|          1 |
|          5 |
|          2 |
+------------+
3 rows in set (0.00 sec)
-- 找出三年二班及三年三班学生每个人的总成绩
mysql> SELECT student_id,SUM(score) FROM scores WHERE class_id IN
    -> (SELECT class_id FROM classes WHERE class_name='三年三班' OR class_name='三年二班')
    -> GROUP BY student_id;
+------------+------------+
| student_id | SUM(score) |
+------------+------------+
|          1 |        253 |
|          3 |        189 |
|          4 |        246 |
|          5 |        230 |
+------------+------------+
4 rows in set (0.00 sec)

FROM子查询

SELECT student_id,score FROM (SELECT * FROM scores WHERE score > 60) AS jg_score WHERE class_id=2;

作为计算字段子查询

SELECT (SELECT student_id FROM students LIMIT 1);

EXISTS查询、ANY子查询、ALL子查询

关键字exists，内层查询不返回查询记录，而返回一个真价值。

SELECT * FROM book WHERE EXISTS (SELECT * FROM tb_book WHERE id=76);

ANY表示满足其中任意一个条件即可

SELECT * FROM book WHERE row<ANY(SELECT row FROM tb_row)

ALL表示满足所有条件才行

SELECT * FROM book WHERE row<ALL(SELECT row FROM tb_row)

联结查询

1、内联接 [INNER] JOIN

（典型的联接运算，使用像 =  或 <> 之类的比较运算符）。包括相等联接和自然联接。     
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。   

mysql> SELECT class_name,student FROM classes AS a JOIN students AS b ON a.class_id=b.class_id;
+------------+---------+
| class_name | student |
+------------+---------+
| 三年二班   | 桂文贤  |
| 三年一班   | 宋娜娜  |
| 三年三班   | 江少峰  |
| 三年二班   | 李震卫  |
| 三年三班   | 宋健宇  |
+------------+---------+
5 rows in set (0.00 sec)

    
2、外联接。
外联接可以是左向外联接、右向外联接或完整外部联接。     
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     
1）LEFT  JOIN或LEFT OUTER JOIN     
左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       

DROP TABLE IF EXISTS students_info; 
CREATE TABLE IF NOT EXISTS students_info(
  student_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    email VARCHAR(128) NOT NULL DEFAULT ''
);
INSERT INTO students_info VALUES(1,'11@QQ.COM'),(2,'SDF@SINA.COM');

mysql> -- 使用内联接查询出的结果
mysql> SELECT a.student,b.email FROM students AS a JOIN students_info AS b ON a.student_id=b.student_id;
+---------+--------------+
| student | email        |
+---------+--------------+
| 桂文贤  | 11@QQ.COM    |
| 宋娜娜  | SDF@SINA.COM |
+---------+--------------+
2 rows in set (0.00 sec)

mysql> -- 使用外联结查询出的结果
mysql> SELECT a.student,b.email FROM students AS a LEFT JOIN students_info AS b ON a.student_id=b.student_id;
+---------+--------------+
| student | email        |
+---------+--------------+
| 桂文贤  | 11@QQ.COM    |
| 宋娜娜  | SDF@SINA.COM |
| 江少峰  | NULL         |
| 李震卫  | NULL         |
| 宋健宇  | NULL         |
+---------+--------------+
5 rows in set (0.00 sec)

2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN     
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       
3）FULL  JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   

3、交叉联接
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。     

mysql> SELECT class_name,student FROM classes,students;
+------------+---------+
| class_name | student |
+------------+---------+
| 三年一班   | 桂文贤  |
| 三年二班   | 桂文贤  |
| 三年三班   | 桂文贤  |
| 三年一班   | 宋娜娜  |
| 三年二班   | 宋娜娜  |
| 三年三班   | 宋娜娜  |
| 三年一班   | 江少峰  |
| 三年二班   | 江少峰  |
| 三年三班   | 江少峰  |
| 三年一班   | 李震卫  |
| 三年二班   | 李震卫  |
| 三年三班   | 李震卫  |
| 三年一班   | 宋健宇  |
| 三年二班   | 宋健宇  |
| 三年三班   | 宋健宇  |
+------------+---------+
15 rows in set (0.00 sec)

组合查询UNION

在大多数开发中，使用一条SELECT查询就会返回一个结果集。如果，我们想一次性查询多条SQL语句，并将每一条SELECT查询的结果合并成一个结果集返回。就需要用到Union操作符，将多个SELECT语句组合起来，这种查询被称为并（Union）或者复合查询。
组合查询适用于下面两种情境中：
从多个表中查询出相似结构的数据，并且返回一个结果集
从单个表中多次SELECT查询，将结果合并成一个结果集返回。

mysql> SELECT * FROM scores WHERE class_id=1
    -> UNION
    -> SELECT * FROM scores WHERE class_id=2 ORDER BY score ;
+----------+------------+----------+------------+-------+
| score_id | student_id | class_id | project_id | score |
+----------+------------+----------+------------+-------+
|        5 |          2 |        1 |          2 |    52 |
|       11 |          4 |        2 |          2 |    59 |
|        4 |          2 |        1 |          1 |    69 |
|        3 |          1 |        2 |          3 |    78 |
|        1 |          1 |        2 |          1 |    80 |
|       12 |          4 |        2 |          3 |    87 |
|        2 |          1 |        2 |          2 |    95 |
|        6 |          2 |        1 |          3 |    98 |
|       10 |          4 |        2 |          1 |   100 |
+----------+------------+----------+------------+-------+
9 rows in set (0.00 sec)