SQL查询学习

create database seldata
on
(
name=seldata,
filename='d:\data\seldata.mdf',
size=100MB,
maxsize=2000mb,
filegrowth=5%
)
log on
(
name=seldata_log,
filename='d:\data\seldata.ldf',
size=50MB,
maxsize=1000MB,
filegrowth=5%
)
--创建查询数据库
use seldata
create table teacher
(
tno int not null,
tname char(60) not null,
cno int not null,
sal int ,
dname char(100) not null,
sex char(2) not null default '男',
age int not null age
)
select * from teacher
alter table teacher
add constraint ck_age check(age between 1 and 200)
--为age字段创建录入值约束
alter table teacher
drop constraint ck_age
--删除约束
insert into teacher values (2,'李彤',5,1200,'生物','女',54)
insert into teacher values (3,'王水军',5,900,'计算机','男',40)
insert into teacher values (4,'刘小静',2,1200,'计算机','女',46)
insert into teacher values (5,'高伟',8,2100,'电子工程','男',39)
insert into teacher values (6,'李伟',7,1200,'机械工程','男',29)
insert into teacher values (7,'刘辉',3,900,'生物','女',46)
insert into teacher (tno,tname,cno,dname,sex,age)
values (8,'李伟',9,'计算机','女',43)
insert into teacher values (9,'刘静',12,1300,'经济管理','女',28)
insert into teacher (tno,tname,cno,dname,sex,age)
values (10,'刘一凯',13,'计算机','女',33)
select * from teacher
--添加数据表
select * from teacher where 1>2
--查询数据表结构可以采用条件表达式不成立的方式
select tname from teacher
--单列查询根据列字段查询
select tno,tname from teacher
select distinct dname from teacher
--加入distinct去除重复值 select disctinct 去除重复值字段 from 表 +条件
select * from teacher where 1=2
select tno,tname,sal,sex,age from teacher
--多列查询加入列名以逗号分隔
select distinct tname,sal,age from teacher
--注意：distinct 后接多列字段时，需满足多列均相同才会去除重\
select tname,tno,sal from teacher order by tname
-单列排序
select tname,tno,sal from teacher order by sal desc
--注意：null值为最小值
select tname,sal,sex,age from teacher order by sex,age
--多列排序，先按sex进行排序，有相同记录后再按age进行排序
select tname,sal,sex,age from teacher order by 3,4
--采用序号进行排序，这里的3 和4 分别代表sex第三旬，4为第四列
select tname,sal,sex,age from teacher order by sex,age desc
--对指定序号做反向排序:age desc 而前一列sex依然是正向排序
select tname,sal,sex,age,dname from teacher where dname='计算机'
select top 1* from teacher
create view cp_view
as select tname,sal,sex,age,dname from teacher where dname='计算机'
select * from cp_view
drop view cp_view
--单条件查询
--下面是比较运算
select tname,dname,age,sex from teacher where age>=40 order by age desc
--这是对数字进行比较运算
--同样也可以对字符和时间等进行比较运逄
select tname,dname,sal,age,sex from teacher
where dname like '计%' order by dname,sex
select * from teacher where sex<>'男' order by age
select * from teacher where sex!='男' order by age
select tname,dname,sal,age,sex from teacher
where sal <>1200 order by sal
/*注意：在值为null参与比较运算时得到的结果为false 则结果不会表现在查询
结果集中*/
--关于Between 运算范围
select tname,dname,age,sex from teacher
where age between 30 and 50 order by age
--在between 之间的值结果包括等于范围集。
select tname,dname,age,sex from teacher
where dname between '计算机' and '生物'
--关于null值判断
select tname,sal,age,sex from teacher
where sal is null order by sal

select tname,sal,age,sex from teacher
where sal is not null order by sal
--注意：null值不能参与运算，只能表达为is null 或者is not null