SqlServer教程

SQL Server 常用关键字

本文主要是介绍SQL Server 常用关键字,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

SQL Server 常用关键字

 

 

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 top 10 * from student            查询前十条

select top 10 percent * 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>90 and ID<100 and ID%2=0

select * from student where ID>100 or ID<10 and 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,B.UserName from CLN_Resource as A inner join NT_User as B on A.UserID =B.UserID

select * from CLN_Resource inner join NT_User on CLN_Resource.UserID=NT_User.UserID
这篇关于SQL Server 常用关键字的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!