--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;

select top 10 * from student            查询前十条

select top 10 percent * from student    查询前10%；

select distinct * from student   查询不重复

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   求最小

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

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 '_ 三 %'      查询第二个为三的

select * from NT_User order by UserAge  按年龄大小排序

select * from NT_User order by UserAge asc  降序

select * from NT_User order by UserAge desc  升序

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的

select Name from Student union all select Name from Teacher  两表联查 所有的

select Name from Student union select Name from Teacher      两表联查 去除重复的

select * into teacher from Student          创建Teacher表 并把Student表里面的数据复制到Teacher表中

insert into teacher select * from Student   把Student表里面的数据复制到Teacher表中

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