大圣Blog

MySQL

数据库操作

数据表操作

Mysql列类型及列属性

MySQL常用函数

MySQL增删改

MySQL查询

JOSN的支持

MySQL流程控制语句

视图

存储过程及存储函数

变量与游标

事务

触发器

权限及安全管理

MySQL日志

二进制日志

Mysql5.7复制功能

数据备份与恢复

性能优化

课程导航

MySQL增删改

JOSN的支持

## 一般查询
语法：SELECT [DISTINCT] 字段1,字段2,...,字段n FROM 表明 WHERE 子句 GROUP BY 子句 HAVING子句 ORDER BY 子句 LIMIT 子句
### 检索不同的行 DISTINCT
默认会检索所有的行，加DISTINCT后可以过滤相同的行
![图片](https://uploader.shimo.im/f/eelG80jW0a0wiUxg.png!thumbnail)
![图片](https://uploader.shimo.im/f/4ibWN4aOfNw7F1sO.png!thumbnail)
### 限制结果LIMIT
![图片](https://uploader.shimo.im/f/Ydu3eaVwmAMuZwUs.png!thumbnail)
### 完全限定表明
```
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');
```
![图片](https://uploader.shimo.im/f/jXiNuS5T6b4H4HML.png!thumbnail)
```
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)

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)
```

MySQL增删改

JOSN的支持

MySQL

课程导航

MySQL查询

文章目录