SQL查询学习笔记

一、SQL简单查询

1、语法：

SELECT select_list [INTO newlist] FROM table_list [WHERE search_condition] [GROUP BY group_by_list] [HAVING search_condition] [ORDER BY order_by_list[DESC|ASC]]

2、查询不重复的字段( distinck)

select distinct user_name from user ;
3、查询前N条数据

mysql

select * from user limit N ;sqlserverselect top N * from user ;oracleselect * from user where rownum <= N;

4、查询条件

比较：

select * from user where user_age > 22 ;select * from user where user_age <> 22 ;

确定范围(between and , not between and )：

select * from user where user_age between 22 and 55 ;select * from user where user_age not between 22 and 55 ;

确定集合( in,not in)

select * from user where user_age in (22, 55) ;select * from user where user_age not in (22, 55) ;

字符匹配( like, not like)，通配符(%，_，[]，[^])
select * from user where user_name like 'zhang%' ;-- %匹配任意多字符select * from user where user_name like 'zhang_a_' ;-- _匹配单个字符

空值：( null,not null)

select * from user where user_name is null ;
连接符( and,or,not)

5、排序[ORDER BY {order_by_list[ASC|DESC]}[,..n]]

对于一级排序中有重复的列，则启用后面的排序对其进行排序，如user表中用user_name进行排序会有重复的情况，若重复则使用user_id在进行排序

select * from user where user_age >0 order by user_name asc,user_id desc ;
6、分组

select user_id, user_name, count(*) c from user where user_age >0 group by user_name order by user_id desc;
7、聚合函数

SUM()

select user_name, sum(user_age) from user group by user_name ;AVG()

select user_name, avg(user_age) from user group by user_name ;MIN() ;
select user_name, min(user_age) from user ;MAX()
select user_name, max(user_age) from user ;COUNT([disctinct]*)
select count(distinct user_name) from user ;
二、多表查询

1、嵌套查询

user--------M:N(score)---------course用户和课程是多对多关系，使用score作为中间表

查询选择 数据结构这门课程的用户

select user_name from user where user_id in (select user_id from score where course_id='sjjg') ;
查询分数>60的课程信息

select * from course where course_id in (select course_id from score where score >60) ;
2、连接查询

a)、交叉连接(产生笛卡尔积)

select * from user cross join course ;
b)、内连接(普通或自然连接)：从一个表中依次拿出每条记录与第二个表中的每条记录进行比较，如果满足连接条件就将其拼接选择出来，否则进行下一行记录的比较，直到最后一条记录。

语法：

SELECT column_list FROM table1 [INNER] JOIN table1 ON table1.column=table2.column ;

如：

select * from user join score on user.user_id = score.user_id ;或select * from user u,score c where u.user_id = c.user_id ;
c)、外连接：补充内连接的功能，它会把内连接中删除原表中的一些行保留下来，可分为左外连接、右外连接、全外连接。

i、左外连接：保留在from子句中左边出现的表的所有行，若右表中没有与之匹配的，则补null

select * from user u left join score s on u.user_id = s.user_id ;+---------+-----------+----------+----------+---------+-----------+-------+
| user_id | user_name | user_age | score_id | user_id | course_id | score |
+---------+-----------+----------+----------+---------+-----------+-------+
| 1 | zhangsan | 1 | 1 | 1 | ddwx | 89 |
| 1 | zhangsan | 1 | 2 | 1 | kjjs | 23 |
| 1 | zhangsan | 1 | 3 | 1 | rjjs | 56 |
| 1 | zhangsan | 1 | 4 | 1 | sjjg | 54 |
| 2 | lisi | 11 | 5 | 2 | rjjs | 98 |
| 3 | lisi | 22 | 6 | 3 | ddwx | 100 |
| 4 | zhangsan | 33 | 7 | 4 | sjjg | 87 |
| 5 | NULL | 44 | 8 | 5 | rjjs | 92 |
| 6 | 23gus | 55 | 9 | 6 | kjjs | 65 |
| 7 | fd34fd | 45 | NULL | NULL | NULL | NULL |
| 8 | 123456 | 78 | NULL | NULL | NULL | NULL |
| 9 | 223456 | 87 | 10 | 9 | sjjg | 87 |
+---------+-----------+----------+----------+---------+-----------+-------+

ii、右外连接：保留右表中所有的行，左表中没有与之匹配对应的行则补null

select * from user u right join score s on u.user_id = s.user_id ;+---------+-----------+----------+----------+---------+-----------+-------+
| user_id | user_name | user_age | score_id | user_id | course_id | score |
+---------+-----------+----------+----------+---------+-----------+-------+
| 1 | zhangsan | 1 | 1 | 1 | ddwx | 89 |
| 1 | zhangsan | 1 | 2 | 1 | kjjs | 23 |
| 1 | zhangsan | 1 | 3 | 1 | rjjs | 56 |
| 1 | zhangsan | 1 | 4 | 1 | sjjg | 54 |
| 2 | lisi | 11 | 5 | 2 | rjjs | 98 |
| 3 | lisi | 22 | 6 | 3 | ddwx | 100 |
| 4 | zhangsan | 33 | 7 | 4 | sjjg | 87 |
| 5 | NULL | 44 | 8 | 5 | rjjs | 92 |
| 6 | 23gus | 55 | 9 | 6 | kjjs | 65 |
| 9 | 223456 | 87 | 10 | 9 | sjjg | 87 |
+---------+-----------+----------+----------+---------+-----------+-------+

iii、全外连接(mysql不支持)select * from user u full outer join score s on u.user_id = s.user_id ;或

select * from user u left join score s on u.user_id = s.user_id union select * from user u right join score s on u.user_id = s.user_id ;
d)、自身连接(为一个表取多个别名来代替)

select u1.user_id,u1.user_name from user u1,user u2 where u1.user_age = u2.user_age and u2.user_id=4;
3、合并查询（两个表相加）

合并操作与连接相似，因为他们都是将两个表合并起来的另一个表的方法，然而他们的合并方法有本质的区别，合并是两个表的相加，连接时时两个表的相乘。

在合并中两个表原列的数量与数据类型必须相同（兼容亦可，列数少的表上加null）

使用union all 加上关键字all 功能是不删除重复行也不对进行自动排序加上all 关键字需要计算资源少 所以尽可能的使用它 尤其是处理大型表的时候 下列情况应使用union all情况

union加上关键字：

01 知道有重复行但是想保留这些行，

02 知道不可能有重复行，

03 不在乎是否用重复行

合并表时有且只有一个order by 子句并且必须将它放置语句的末尾 他的两个select 语句都提供了用于合并所有行的排序 下面的order by子句可以使用排序依据

01 来自第一个select子句别名

02 来自第一个select子句列别名

select user_name from user union all select course_name from course order by user_name desc ;
except（减去）

intersect（交集）