1、SELECT 查询语句和条件语句
SELECT [ALL | DISTINCT | DISTINCTROW ]
select_list
FROM tbl_name [AS] alias_name
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
select过滤、去重
DISTINCT、DISTINCTROW对查询返回的结果集提供了一个最基本但是很有用的过滤。那就是结果集中只含非重复行。重复的记录,指的是,字段、值,都相同的记录,而不是部分字段相同的记录。在这里要注意的是,对关键词DISTINCT、DISTINCTROW来说,空值都是相等的,无论有多少NULL值,只选择一个。默认是“all”,表示所有。
select list:其中可以包含一项或多项下列内容:
1、“*”,表示按照create table的顺序排列的所有列。
2、按照用户所需顺序排列的列名的清单。
3、可以使用别名取代列名,形式如下:column name as column_heading。
4、表达式(列名、常量、函数,或以算术或逐位运算符连接的列名、常量和函数的任何组合)。
5、内部函数或集合函数。
6、上述各项的任何一种组合。
表名: 数据库.表名|表名 [AS] 表别名
常用条件:
- <、= 等于 、<>不等于、>、in (n,m) 包含 、 not in (n,m)不包含、like 匹配 、 between n and m (在n~m的范围) 、 not BETWEEN 不在范围 、is null:在搜索一个NULL值时使用。
- 条件运算: and 、 or 、 ( )
2、排序,分组,指针查询,计算
分组语句:group by 字段 (语句最后 order之前)
排序语句:order by 字段,字段 ASC / DESC (语句最后 limit之前,默认 升序,asc)
指针查询:limit 初始值,结束值 (语句最后)
聚合(合计)函数(计算):
COUNT(*)
统计函数
MAX(*)
最大值函数
MIN (*)
最小值函数
AVG(*)
平均值函数
SUM(*)
累计值函数(∑)
控制流程函数
select (case when 1<2 then 'yes' else 'no' end) as test;
select if(1<2,'yes ','no') as test;
3 联合查询(Union):将多条select语句的结果,合并到一起。称之为联合操作。
union联合
使用 union关键字 联合 两个select语句即可:
mysql> select * from `users` where `age`=12
union
select * from `users` where `age`=13;
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小敏 | 12 | 女 |
| 小红 | 12 | 女 |
| 小明 | 13 | 女 |
| 小刚 | 13 | 男 |
| 小默 | 13 | 女 |
+----------+-----+------+
5 rows in set (0.00 sec)
Union all:环境:
1,获得0115班所有代课教师,结果按照代课天数升序排序,同时需要获得0228班,结果按照降序排序
注意,如果union 的结果存在重复的记录,那么会消除重复.
可以通过 union选项 all 达到目的。
mysql> select * from `users` where `age`=12
union all
select * from `users` where `age`=13;
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小敏 | 12 | 女 |
| 小敏 | 12 | 女 |
| 小敏 | 12 | 女 |
| 小红 | 12 | 女 |
| 小明 | 13 | 女 |
| 小明 | 13 | 女 |
| 小刚 | 13 | 男 |
| 小默 | 13 | 女 |
+----------+-----+------+
8 rows in set (0.00 sec)
mysql>
union排序
子语句结果的排序:
- 将子语句包裹子括号内。
- 子语句的order by,只要在配合limit时,才生效。union在做子语句时,会对没有limit的子语句进行优化
mysql> (select * from `users` where `userName`='小敏' ORDER BY `age`)
union all
(select * from `users` where `userName`='小明' ORDER BY `age` desc);
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小敏 | 16 | 女 |
| 小敏 | 10 | 女 |
| 小敏 | 12 | 女 |
| 小明 | 5 | 女 |
| 小明 | 9 | 女 |
+----------+-----+------+
5 rows in set (0.00 sec)
#子语句的order by,只要在配合limit时,才生效
mysql> (select * from `users` where `userName`='小敏' ORDER BY `age` limit 3)
union all
(select * from `users` where `userName`='小明' ORDER BY `age` limit 2);
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小敏 | 10 | 女 |
| 小敏 | 12 | 女 |
| 小敏 | 16 | 女 |
| 小明 | 5 | 女 |
| 小明 | 9 | 女 |
+----------+-----+------+
5 rows in set (0.00 sec)
mysql>
对所以结果进行统一排序:
只需要在最后一个select语句后,增加相应的排序规则即可
mysql> (select * from `users` where `userName`='小敏')
union all
(select * from `users` where `userName`='小明')
ORDER BY `age` ;
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小明 | 5 | 女 |
| 小明 | 9 | 女 |
| 小敏 | 10 | 女 |
| 小敏 | 12 | 女 |
| 小敏 | 16 | 女 |
+----------+-----+------+
5 rows in set (0.00 sec)
**提示:
子语句的括号不是必须的,是为了方便语句识别
注意:
规定,多个select语句的检索到的字段数,必须一致。
更加严格的是,数据类型上,应该也有要求一致:
但是,mysql内部会做类型转换处理,要求是能够转换成功;
检索结果中的列名称问题:
以第一条select语句的列名称而定**
4、子查询:语句内部的查询语句,就是子查询语句。
返回值分类:单一值,一列,多列,表(多行,多列)
出现位置:
- where后:where(subquery)
- exists后:exists(subquery)
- from后:from(subquery)
如何使用:获得一个值(也就是说返回一个值),使用关系运算符,进行判断=、>、>=、<、<=、!=
mysql> SELECT * FROM `users` WHERE `age` = (SELECT max(age) FROM `users`);
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小敏 | 16 | 女 |
+----------+-----+------+
1 row in set (0.00 sec)
集合运算符:当返回多列(多个值时),
使用集合运算符,
in、not in
=any(集合) 等于集合中的任何一个即可。等同于in
!=any(集合)不等于集合中的任意一个。
!=all(集合) 不等于集合中的所有元素
some(集合)集中的一些与any(集合)等同
Not any(集合) 不是任何一个的,与!=all(集合)等同
some==any some在语法上与any相同
返回一行:
在参与比较时,使用括号可以构建一行:
(filed1, field2)
mysql> SELECT * FROM `users` WHERE (`userName`, `age`) = ( SELECT `userName`, `age` FROM `users` ORDER BY `age` DESC LIMIT 1 );
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小敏 | 16 | 女 |
+----------+-----+------+
1 row in set (0.00 sec)
返回一个表:
如果用from子语句内,from子语句内,要求使用一个表,而能是一个结果,一应该给这个查询结果起一个别名。
外部查询所使用的列名,是由子查询指定的。
mysql> SELECT * FROM ( SELECT * FROM `users` WHERE age > 12 ) AS temp WHERE userName LIKE '小%';
+----------+-----+------+
| userName | age | sex |
+----------+-----+------+
| 小敏 | 16 | 女 |
| 小刚 | 13 | 男 |
+----------+-----+------+
2 rows in set (0.00 sec)
mysql>
Exists
Exists(subquery) 判断依据:
如果子查询的 可以返回数据,则认为 exists 表达式 返回真。否者,返回假。
下面的两个语句完成的是同样的事情:
select * from `teacher_class` where exists (select * from `teacher` where `teacher_class`.`id`=`t_id`);
select * from `teacher_class` where `id` in (select `t_id` from `teacher`);
解决思路是不一样的:
exists: 先获得每一条 teacher_class的数据,然后获得id字段,去teacher表内查找对应值,找到,说明符合条件。
in:先获得所有的id的可能性。再在检索teacher_class数据时,判断当前的id是否在id集合内。
5、多表查询(jion)
每个实体,一个表
一个业务逻辑,使用多个实体的数据,
多张表应该在一起使用,将多个表的记录连接起来。
总体思路:
将所有的数据,按照某种条件,连接起来,再进行筛选处理。
连接的分类:
根据连接的条件不同,分类入如下:
1、内连接(inner join)
2、外连接 (outer join)
3、自然连接
测试表
class表
class_id | class_name |
---|---|
1 | a |
2 | b |
3 | c |
student表
student_id | class_id | student_name |
---|---|---|
1 | 1 | 李萌 |
2 | 2 | 王武 |
3 | 1 | 李浩 |
内连接(inner join):
数据内部的连接,要求,连接的多个数据都必须存在才能进行连接
例:
select c.*,s.* from student as s [inner] join class as c on c.id=s.class_id
inner join是Mysql默认的连接方案,可以省略 inner。
这个语法是内连接的另外一种写法,其执行结果与inner join 一样
select * from class,student where class.class_id=student.class_id
查询结果如下
class_id | class_name | student_id | class_id1 | student_name |
---|---|---|---|---|
1 | a | 1 | 1 | 李萌 |
2 | b | 2 | 2 | 王武 |
1 | a | 3 | 1 | 李浩 |
外连接(outer join):
数据内部的连接,要求,连接的多个数据都必须存在才能进行连接
A left join B #的连接的记录数与A表的记录数同
A right join B #的连接的记录数与B表的记录数同
A left join B
#等价
B right join A
例:
select c.*,s.* from student as s left/right join class as c on c.id=s.class_id
(RIGHT JOIN的作用与LEFT JOIN的作用类似。要使代码可以在数据库内移植,建议您使用LEFT JOIN代替RIGHT JOIN
。)
左外连接(left join)
select c.*,s.* from student as s left join class as c on c.class_id=s.class_id
如果左外连接(left join)的话,它将显示class表中所有记录
查询结果如下
class_id | class_name | student_id | class_id1 | student_name |
---|---|---|---|---|
1 | a | 1 | 1 | 李萌 |
2 | b | 2 | 2 | 王武 |
1 | a | 3 | 1 | 李浩 |
右外连接(rgint join)
select c.*,s.* from student as s right join class as c on c.class_id=s.class_id
如果右外连接(rgint join)的话,它将显示student表中所有记录
查询结果如下
class_id | class_name | student_id | class_id1 | student_name |
---|---|---|---|---|
1 | a | 1 | 1 | 李萌 |
2 | b | 2 | 2 | 王武 |
1 | a | 3 | 1 | 李浩 |
3 | c | null | null | null |
自然连接:
通过 mysql 自己的判断完成连接过程!不需要指定连接条件。mysql会使用多表内的,相同字段,作为连接条件(注意,两个表同名的列不能超过1个。) 自然连接分成 内外之分:
内:* natural join *
外:左外:Natrual left join
右外:Natrual right join注意:
* on student.class_id = class.class_id 等同于 using(c1) *
* using:会去掉结果中的 重复字段,并放在列前。 *
* natural join *
select * from student natural join class;
#等同于
select * from student inner join class using(`class_id`);
查询结果如下
class_id | student_id | student_name | class_name |
---|---|---|---|
1 | 1 | 李萌 | a |
1 | 3 | 李浩 | a |
2 | 2 | 王武 | b |
* Natrual left join *
select * from student natural left join class;
#等同于
select * from student left join class using(`class_id`);
查询结果如下
class_id | student_id | student_name | class_name |
---|---|---|---|
1 | 1 | 李萌 | a |
2 | 2 | 王武 | b |
1 | 3 | 李浩 | a |
* Natrual right join *
select * from student natural right join class;
#等同于
select * from student right join class using(class_id);
查询结果如下
class_id | class_name | student_id | student_name |
---|---|---|---|
1 | a | 1 | 李萌 |
2 | b | 2 | 王武 |
1 | a | 3 | 李浩 |
3 | c | null | null |
交叉连接或笛卡尔积(cross join):
内连接,在连接时,是可以省略连接条件的。意味着,所有的左表的数据,都要与右表的记录做一个连接。共存在
N*M
个连接,这种连接,就称之为,交叉连接,或者笛卡尔积。如本例会产生3*2=16条记录,在开发过程中我们肯定是要过滤数据,所以这种很少用。
SELECT * FROM TableA CROSS JOIN TableB ;
#等同于
SELECT * FROM TableA [INNER] JOIN TableB ;
查询结果如下
id | class_name | student_id | class_id | student_name |
---|---|---|---|---|
1 | 1 | 李萌 | 1 | a |
2 | 2 | 王武 | 1 | a |
3 | 1 | 李浩 | 1 | a |
1 | 1 | 李萌 | 2 | b |
2 | 2 | 王武 | 2 | b |
3 | 1 | 李浩 | 2 | b |
1 | 1 | 李萌 | 3 | c |
2 | 2 | 王武 | 3 | c |
3 | 1 | 李浩 | 3 | c |
注意:
mysql中 cross join与inner join相同,但在数据库的定义上,交叉连接就是笛卡尔积。是没有条件的inner join。