(二)MySQL基本语句

查询数据

基本查询语句

1
2
3
4
5
6
7
8
9
10
11
12
select 
{* | <字段列表>}
{
form <表1>,<表2>...
[where <表达式>]
[group by <grop by definition>]
[having <exception> [{<option> <expression>}...]]
[order by <order by definition>]
[limit [<offset>,] <row count>]
}

select [字段1,字段2...] from [表或视图] where [查询条件];

解释:

1
2
3
4
* {* | <字段列表>}  : 表示查询的字段,多个字段间用逗号隔开
* group by<字段> : 告诉MySQL如何显示查询出来的数据,并按照指定的字段分组
* [order by<字段>] : 告诉MySQL按什么样的顺序显示查询出来的数据,如:升序(ASC)、降序(DESC)
* [limit [<offset>] <row count>] : 告诉MySQL每次显示查询出来的数据条数

单表查询

带IN关键字的查询

1
select id from tb_emp_2 where id in(2,3) order by id asc

带BETWEEN AND的范围查询

1
select id from tb_emp_2 where id between 2 and 4

带LIKE的字符匹配查询

1
通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符,SQL语句中支持多种通配符,可以和LIKE一起使用的通配符有'%'和'_'

‘%’通配符,匹配任意长度的字符,甚至包括零字符

1
2
3
4
5
6
7
8
查找’t‘字母开头的员工姓名:
select id,name from tb_emp_2 where name like 't%';

查找包含’t‘的员工姓名:
select id,name from tb_emp_2 where name like '%t%';

查询以’t‘开头,并以’g‘结尾的员工姓名:
select id,name from tb_emp_2 where name like 't%g';

‘_’通配符,一次只能匹配任意一个字符

1
2
查询以’e‘结尾,并且前面包含四个字符的员工姓名:
select id,name from tb_emp_2 where name like '____e';

IS NULL 查询空值

1
select id,name from tb_emp_2 where name is null;

带AND的多条件查询

1
2
3
4
MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回,可以用AND连接多个条件,多个条件见用AND分开

例如:查询名字中包含't'并且年龄等于18岁的员工
select id,name,age from tb_emp_2 where name like '%t%' and age = 18;

带OR的多条件查询

1
2
3
4
在WHERE声明中使用OR操作符,表示只需要满足其中一个条件的记录即可返回。OR可以用来连接多个查询条件。

例如:查询姓名中包含’t‘或'o'且年龄等于18的员工
select id,name,age from tb_emp_2 where name like '%t%' or '%o%' and age = 18;

注意: and的优先级高于OR

DISTINCT查询结果不重复

1
2
如,查询表中所有name不重复的数据:
select id,distinct name from tb_emp_2;

ORDER BY 对查询结果排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
1. 单列排序(默认按字母顺序排列)
select name from tb_emp_2 order by name;

2. 多列排序(哪个字段在前先依照哪个排序)
select age,name from tb_emp_2 order by age,name;

3. 指定排序方向
select name,age from tb_emp_2 order by age desc,name asc;
**注意:MySQL排序的时候默认按照asc升序排序,且第二个字段仅仅是在第一个字段的基础上排序
**如下列情况:
mysql> select age,name from tb_emp_2 order by age,name asc;
+------+--------+
| age | name |
+------+--------+
| 12 | tu |
| 13 | apple |
| 18 | orange |
+------+--------+
mysql> select age,name from tb_emp_2 order by age,name desc;
+------+--------+
| age | name |
+------+--------+
| 12 | tu |
| 13 | apple |
| 18 | orange |
+------+--------+
mysql> select age,name from tb_emp_2 order by age desc,name desc;
+------+--------+
| age | name |
+------+--------+
| 18 | orange |
| 13 | apple |
| 12 | tu |
+------+--------+

前两个结果一样,因为都只是给name字段后加了条件,而不加条件的age字段则默认按圣墟排列,所以无论name字段后加什么排序结果都是一样的。
想要多多列都进行降序排列,要在每个字段后都加desc

GROUP BY 分组查询

1
语法:[group by 字段] [having <条件表达式>]

创建分组

1
2
3
4
5
例如:按照password排序,并记录其相同值的数量
select password count(*) from tb_emp_2 group by password;

例如:查看这些分组的password分别对应哪些name
select password,group_concat(name) from tb_emp_2 group by password;

使用HAVING过滤分组

1
2
3
group by可以和having一起限定表中的数据进行分组,只有满足条件的分组才能被显示出来。
如:按id排序,并查询名字中包含't'的客户
select id,name from tb_emp_2 group by id having name like '%t%';

where和having的区别:
having 是在数据分组之后进行过滤来选择分组的,而where是在分组之前用来选择记录的。另外where排除的记录不再包含在分组中

在GROUP BY子句中使用WITH ROLLUP

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
使用WITH ROLLUP关键字,在所有查询出来的分组记录之后增加一条记录,在记录计算查询出的所有记录的总和,即统计记录数量
例如:
mysql> select f_id,count(*) as total from tb_fruits group by f_id;
+------+-------+
| f_id | total |
+------+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
+------+-------+
mysql> select f_id,count(*) as total from tb_fruits group by f_id with rollup;
+------+-------+
| f_id | total |
+------+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 1 |
| NULL | 6 |
+------+-------+

多字段分组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
使用group by 进行多字段分组:
如:
mysql> select f_id,f_name from tb_fruits group by f_id,f_name;
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | apple |
| 2 | banana |
| 2 | orange |
| 3 | apple |
| 3 | banana |
| 4 | grape |
+------+--------+

但是:
mysql> select * from tb_fruits group by f_id,f_name;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_1.tb_fruits.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

所以:
使用group by 进行多字段分组,要注意你分组的字段在select后指定,

原因:
SQL的GROUP By的语法:
SELECT 选取分组中的列+聚合函数 from 表名称 GROUP BY 分组的列
所以是先有分组,再有检索的列,检索的列只能在分组中的列中选

综上:
就是:GROUP BY后的字段是先确定的,而SELECT后的字段是从GROUP BY分组中选的。
即:GROUP BY后面有的字段,SELECT后面才允许有;反之没有的SELECT也不能有

mysql> select f_id,f_name from tb_fruits group by f_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_1.tb_fruits.f_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select f_id from tb_fruits group by f_id,f_name;
+------+
| f_id |
+------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
+------+
mysql> select f_id,f_name from tb_fruits group by f_id,f_name;
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | apple |
| 2 | banana |
| 2 | orange |
| 3 | apple |
| 3 | banana |
| 4 | grape |
+------+--------+

使用LIMIT限制查询结果的数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
LIMIT返回指定序列行的数据

语法:
LIMIT [位置偏移量,] 行数
解释:
[位置偏移量]:可选参数,不写,默认会从表的第一条记录开始(第一条记录的偏移量是0,第二条是1...)
行数:指示从第[位置偏移量开始],共显示多少行记录

例如:
mysql> select * from tb_fruits limit 4,2;
+----+------+--------+---------+
| id | f_id | f_name | f_price |
+----+------+--------+---------+
| 5 | 3 | apple | 1.02 |
| 6 | 4 | grape | 2.10 |
+----+------+--------+---------+

使用聚合函数查询

1
2
3
4
5
6
7
8
9
10
avg()     返回某列的平均值
count() 返回某列的行数
max() 返回某列的最大值
min() 返回某列的最小值
sum() 返回某列值的和

1. count()函数
注意:
count(*) 计算表中总的行数,不管某列有数值或为空值
count(字段名) 计算指定列下的总行数,计算是将会忽略空值的行

连接查询

INNER JOIN内连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
INNER JOIN使用比较运算符进行表间某些列数据的比较操作
举例:
mysql> select tb_vegetables.f_id,v_name,v_price,f_name,f_price from tb_fruits inner join tb_vegetables on tb_vegetables.f_id = tb_fruits.f_id;
+------+----------+---------+--------+---------+
| f_id | v_name | v_price | f_name | f_price |
+------+----------+---------+--------+---------+
| 1 | tomato | 1.02 | apple | 1.02 |
| 2 | cucumber | 1.00 | orange | 1.5 |
| 2 | cucumber | 1.00 | banana | 1.8 |
| 4 | beans | 1.00 | grape | 2.10 |
+------+----------+---------+--------+---------+

mysql> select tb_vegetables.f_id,v_name,v_price,f_name,f_price from tb_fruits,tb_vegetables where tb_vegetables.f_id = tb_fruits.f_id;
+------+----------+---------+--------+---------+
| f_id | v_name | v_price | f_name | f_price |
+------+----------+---------+--------+---------+
| 1 | tomato | 1.02 | apple | 1.02 |
| 2 | cucumber | 1.00 | orange | 1.5 |
| 2 | cucumber | 1.00 | banana | 1.8 |
| 4 | beans | 1.00 | grape | 2.10 |
+------+----------+---------+--------+---------+
分析:
f_id是tb_vegetables和tb_fruits两张表中共有的字段
因为f_id是共有的字段,也是作为内连接的条件,那么遵循`表名.字段名`格式

外连接查询

外连接查询将查询多个表中相关联的行;内连接时,返回查询结果集合中仅是符合查询条件和连接条件的才行

1
2
LEFT JOIN(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。
RIGHT JOIN(右连接):返回包括左表中的所有记录和左表中连接字段相等的记录。

LEFT JOIN(左连接)

左连接的结果包含LEFT OUTER子句中指定的左表中的所有行,而不仅仅是连接列所匹配的行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> select * from tb_orders;
+-------+---------------------+------+
| o_num | o_date | f_id |
+-------+---------------------+------+
| 1 | 2018-01-01 00:00:00 | 1 |
| 2 | 2018-01-01 00:00:00 | 2 |
| 3 | 2018-01-01 00:00:00 | 4 |
| 4 | 2018-01-01 00:00:00 | 5 |
+-------+---------------------+------+
mysql> select * from tb_fruits;
+----+------+---------+---------+
| id | f_id | f_name | f_price |
+----+------+---------+---------+
| 1 | 1 | apple | 1.02 |
| 2 | 2 | orange | 1.5 |
| 3 | 3 | banana | 1.8 |
| 4 | 2 | banana | 1.8 |
| 5 | 3 | apple | 1.02 |
| 6 | 4 | grape | 2.10 |
| 7 | 5 | beans_h | 1.0 |
+----+------+---------+---------+

mysql> select tb_fruits.f_id,tb_orders.o_num from tb_fruits left outer join tb_orders on tb_fruits.f_id = tb_orders.f_id;
+------+-------+
| f_id | o_num |
+------+-------+
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 4 | 3 |
| 5 | 4 |
| 3 | NULL |
| 3 | NULL |
+------+-------+

根据左连接的定义:左表(tb_fruits)中的所有行都会展示,若右表中没有匹配的行,数据就显示为null

RIGHT JOIN(右连接)

右连接是左连接的反向连接,将返回右表中的所有行。若右表的某行在左表中没有匹配的行,左表返回空值

1
2
3
4
5
6
7
8
9
10
11
mysql> select tb_fruits.f_name,tb_orders.o_num from tb_fruits right outer join tb_orders on tb_fruits.f_id = tb_orders.f_id;
+---------+-------+
| f_name | o_num |
+---------+-------+
| apple | 1 |
| orange | 2 |
| banana | 2 |
| grape | 3 |
| beans_h | 4 |
| NULL | 5 |
+---------+-------+

##子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询,子查询的结果作为外层另一个查询的过滤条件,查询可以基于一张或多张表。
子查询中常用的操作符有:ANY(SOME)、ALL、IN、EXISTS、子查询可以嵌套在SELECT、UPDATE、DELETE语句中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1. 带ANY、SOME的子查询:
select num1 from tb_1 where num1 > any(select num2 from tb_2);
表示如果tb_2中的num2和tb_1中的num1比较,只要大于num2列的任意一个值即可满足

2. 带ALL的子查询
select num1 from tb_1 where num1 > all(select num2 from tb_2);
需要num1大于num2列的所有值才满足条件

3. 带EXISTS的子查询
select * from tb_fruits where exists(select o_num from tb_orders where o_num = 3);
只要tb_orders中存在o_num = 3的数据,子查询就返回true,那么就会进行外层的查询

4. 带IN关键字的查询
select

插入语句

INSERT INTO table_name (column_list) VALUES(value_list)

更新语句

UPDATE table_name SET column_name = value WHERE (condition)

删除语句

DELETE FROM table_name [WHERE <condition>]


交流

如果大家有兴趣,欢迎大家加入我的Java交流群:671017003 ,一起交流学习Java技术。博主目前一直在自学JAVA中,技术有限,如果可以,会尽力给大家提供一些帮助,或是一些学习方法,当然群里的大佬都会积极给新手答疑的。所以,别犹豫,快来加入我们吧!


联系

If you have some questions after you see this article, you can contact me or you can find some info by clicking these links.

如果你觉得这篇文章帮助到了你,你可以帮作者买一杯果汁表示鼓励