1.基础查询
int foreign key references dept (deptno)
1.name 不分 大小写
2.select distinct 1,2 from dept
3.select name (as)年工资 from
4. isnull(com, 0) //null则为0
5. between 2000 and 2500 //取等于号
6.where name like ‘S%’ //%表示任意 -表示一个字符
7.select * from … where id in(123,234,767)
8. order by … asc/desc
9.select … from … group by, having, order by
* having 分组后进行选择
2.数据分组
- select name form table where sal=(select max(sal) from table)
- avg, sum, max, min, count
3.复杂查询
- 为笛卡尔集
select * from emp, dept
- empty,dept中都有id必须使用别名
3.2自连接,内连接
select name from emp w, emp b where w.name = b.name
- inner join … on …
from emp w inner join emp b on w.name = b.name
3.3左外连接
- left join … on …
左边的表全部显示,不匹配显示null - right join … on …
右边的表全部显示,不匹配显示null
3.4 单行子表,多行子表
where id = (select id from emp)
in (select distinct job from empt)
//多行
3.5 from中使用子查询(子表一定要指定别名)
3.6 分页
select top 4 * from empt
--得到11~14员工的信息
select top 4 * from empt where id not in(select top 10 id from empt)
3.7数据暴增,自增长
--从1开始自增1
id int primary key identity(1, 1)
--增加进去
insert into test(id name) select id,name from test
3.8删除一个重复记录
--插入到temp,temp自动创建
select distinct * into temp from emp
delete from emp
insert into emp select * from temp
drop temp