建表
create table userinfo
(
id int primary key not null identity (1,1),
[name] char(20) unique,
age int check(age>10),
sex char(2)
)
create table love1
(
id int primary keyidentity(1,50),自动增长 50间距
name nvarchar(20)
)
查询语句
给查询的列名起别名:select name as "姓名" from emp
查询去除了某列值相等的项:select distinct name from emp
查询对(name,sex)两项都相同的项进行过滤后的项:select distinct name,sex from emp
对于没有的列查询的话,不会发生错误,只会对其值赋予列名。
查询满足(某列值==给的值)的项:* from emp where name="徐学逸"
查询在某一个区间范围的项:
select * from emp where id>1 and id<4
select * from emp where id>1 or id>4
select * from emp where id between 1 and 4
select * from emp where id not between 5 and 4
查询在不在某个给定的集合里面:
select * from ggwhere id in (select id from gg) :集合是张子表
select * from emp where id in (1,2)
select * from emp where id not in (1,2)
select * from emp where id<>1
按照顺序排列查询后的结果:select * from emp order byid desc
查询某列的值为空:
select * from emp where name is null
select * from emp where name is not null
查询后的结果改变列值:select id*12 from emp
查询按照分类,按照前面的:select * from emp order by id,name
模糊查询like:
select * from emp where name like '%徐%'
select * from emp where name like '_学%' 第二个字母是徐的
select * from emp where name like '_[A-F]%'第二个字母是A-F
select * from emp where name like '%\_%' escape '\' 利用转义字符查询
聚合查询:
select lower(name) from emp 首字母小写
select lower(name) from emp 首字母大写
select max(id) from emp 返回所有id最大的值
select max(id) from emp返回所有id平均的值
select count(id) from emp 返回所有id的个数
select count(*) from emp 返回所有的个数
select count(distinct name) from emp 去除重复项
分组查询:
selectdeptno,avg(sal) as "部门平均工资" from gg group by deptno 分组查询各个部门平均工资
select* from gg group by deptno,jop 先按照部门查询,后按照工作查询
select* ,avg(sal) as "平均工资" from gg group by deptno,jop 上面扩展,另外加上最后分组的平均工资
Having group by: having是对分组后的过滤
selectavg(sal) as "平均工资" from gg group by deptno having avg(sal)>30000 分组后信息过滤
selectdeptno, avg(sal) as "平均工资" from gg group by deptno havingcount(*)>3 根据数量过滤
selectdeptno, avg(sal) as "平均工资" from gg where name not like '徐%' group by deptno having avg(sal)>300 先根据名字过滤出一个子表在分组查询
selectdeptno, avg(sal) as "平均工资" from gg where sal>30000 group bydeptno having avg(sal)>300 先根据工资过滤出一个子表在分组查询
内连接
两个表某列值(表1)==某列值(表2)表1可以利用该列值访问表2的信息:select "A".name "员工姓名","B".dname "部门名称" from gg"A" join dp "B" on"A".deptno="B".deptno 多join几次多加几个表
selectgg.name "员工姓名",dp.dname "部门名称" from gg,dp where gg.deptno=dp.deptno 同样的效果
select"A".name "员工姓名","A".sal "工资","B".dname "部门名称" from gg"A" join dp "B" on "A".sal>30000 and"A".deptno="B".deptno
两个条件限制
select* from gg ,dp where id=11 不要选择两个表都有的项
表一同时与表二,表三关联,并且表一还有限制条件:select"A".name "员工姓名","S".grade "工资","B".dname "部门名称" from gg"A" join dp "B" on"A".deptno="B".deptno
joinrank "S" on "A".sal>"S".low and"A".sal<"S".high where A.sal>100
表一分组后生成子表再与表二关联查询:
select* from (select deptno,avg(sal) as "avg_sal" from gg group by deptno)"T" join rank "R" on "T".avg_sal between"R".low and "R".high
先分组出一张子表然后进行过滤:select *from(select deptno, avg(sal) as "平均工资" from gggroup by deptno) "A" where "A".平均工资>1000
select * fromgg where sal>(select min(sal) "最低工资" fromgg) order by sal desc
外连接:如果左表与右表没有匹配项则输出一行,右表那部分全为null
自连接
select*from gg ”A1” ,gg “A2”
联合:两次查询结果联合起来
select "A".name "员工姓名","B".[name] "上司" from allinfo "A" join allInfo"B" on "A".boss_id=B.id
union select name "员工姓名",'老板' from allinfo where boss_id isnull
createtable allinfo
(
id int,
namenvarchar(20),
boss_idint
)
insertinto allinfo(id,name,boss_id) values(7,'徐星',3)
select"A".name "员工姓名","B".[name] "老板" from allinfo "A" join allInfo "B" on"A".boss_id=B.id
分页查询:
使用top语句加上 聚合查询 not in(集合)
插入语句
插入一项:insertinto emp(name,sex) values('徐学逸','凤飞飞') //emp表名,name 列名,sex 列名
视图:
视图用于生成子表,使得查询看起来更清晰
create view v_myview as
select deptno, avg(sal) "avg_sal"from gg( ) //gg表名
group by deptno //创建视图
事务:
select *from v_myview
删除语句:
删除为空的:DELETEFROM gg WHERE id is null
更改语句:UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值