SQL学习笔记

建表

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 列名称 = 某值