SQLServer常用关键字

SQLServer常⽤关键字SQL 建库建表
--1.创建⼀个数据库
create database School;
--删除数据库
drop database School;
--创建数据库的时候指定⼀些选项。

create database School
on primary
(
name='School',
filename='C:\Program Files\SQL\MSSQL12.MSSQL\MSSQL\DATA\School.mdf',
size=5MB,
--filegrowth=10MB,
filegrowth=10%,--按照⽂件的百分⽐来增长
maxsize=100MB
)
log on
(
name='School_log',
filename='C:\Program Files\SQL\MSSQL12.MSSQL\MSSQL\DATA\School.ldf',
size=3MB,
filegrowth=3%,
maxsize=20MB
)
--切换数据库
use School;
TOP
select top10*from student 查询前⼗条
select top10percent*from student 查询前10%；
Distinct
select distinct*from student 查询不重复
Avg Count Sum Min MAx
select AVG(ID) from student 平均数
select COUNT(ID) from student 总数
select Sum(ID) from student 求和
select Max(ID) from student 求最⼤数
select Min(ID) from student 求最⼩
Where And Or
select*from student where ID>90and ID<100and ID%2=0
select*from student where ID>100or ID<10and ID%2!=0
模糊查询 Like Not Like % _
select*from student where Name like'%三%'查询带有三的
select*from student where Name not like'%三%'查询不带有三的
select*from student where Name like'三%'查询三开头的
select*from student where Name like'%三'查询三结尾的
select*from student where Name like'_ 三 %'查询第⼆个为三的
Order By where desc asc 排序
select * from NT_User order by UserAge 按年龄⼤⼩排序
select * from NT_User order by UserAge asc 降序
select * from NT_User order by UserAge desc 升序
Group By Having 分组（要和聚合函数⼀起使⽤）
select UserGender from NT_User group by UserGender 按性别分成三组
select UserGender, Count(*) from NT_User group by UserGender 查询三组性别的每个的个数
select UserGender,COUNT(*) from NT_User group by UserGender having COUNT(*) >1000 查询三组性别的每个的个数⼤于1000的 Union Union All
select Name from Student union all select Name from Teacher 两表联查所有的
select Name from Student union select Name from Teacher 两表联查去除重复的
Select Into Insert Into
select * into teacher from Student 创建Teacher表并把Student表⾥⾯的数据复制到Teacher表中
insert into teacher select * from Student 把Student表⾥⾯的数据复制到Teacher表中
Inner Join on 两表联查
select A.Content,erName from CLN_Resource as A inner join NT_User as B on erID =erID
select * from CLN_Resource inner join NT_User on CLN_erID=NT_erID。