SQL数据库查询语句

SQL数据查询语句

select语句的基本结构

select …from…where…
group by … having…
order by …

select…from…子句

select *=select all （查询所有列）
select distinct 列 (去掉重复列)
select top n （结果中显示前N列）
select 列名 as 新列名 (另起列名）
from 表

group by…having…子句

对表进行分组

select * from student
group by age having age>25

order by 子句

排序，默认ASC升序，DESC降序

select * from student
order by age desc

into 子句

用into生成新表，将查询语句插入新表

select name,age
into people
from student

compute 子句

生成合计，出现在结果列

select * from student
order by age
compute avg(age)

where子句

1. 逻辑运算符 and not or

   select * from student
   where age >20 and age<30

   select * from student
   where not age>50

   select * from student
   where age<30 or age>40

2. 比较运算符 > , = , <, >= ,<=,!=,!>,!<

3. like
   1.%: 任意字符

   select * from student where name like ‘张%’

   2._: 单个字符

   select * from student where name like ‘张_’

   3.[a-b]: a-b范围内的单个字符

   select * from student where age like ‘2[2-3]’

   4.[^a-b]：不在a-b范围内的单个字符

   select * from student where age like ‘2[^2-3]’

4. is (not) null

select * from book
where author is not null

5. in

select * from student
where add in (‘shanghai’,‘beijing’)

6. (not) between

select * from student
where age between 20 and 30

多表查询

内连接:查询的结果为两个表匹配的数据

select * from student
inner join people
on student.id=people.id

左连接:查询的结果根据左表进行连接

SELECT *
FROM employee LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID

右连接:查询的结果根据右表进行连接，用的较少
自连接:自己连接自己

SELECT 学生表.*
　FROM 学生表 JOIN 学生表 AS 学生表1 ON 学生表.班级=学生表1.班级
　WHERE 学生表1.学号=‘20210’