SQL sever查询总结

use master

go

if exists(select * from sysdatabases where name = 'CodesKeys')

drop database CodesKeys

create database CodesKeys

on primary 

(

name = 'CodesKeys_data',

filename = 'E:\DATASTORE\CodesKeys_data.mdf',

size = 10MB,

filegrowth = 1MB

)

log on

(

name = 'CodesKeys_log',

filename = 'E:\DATASTORE\CodesKeys_log.ldf',

size = 10MB,

filegrowth = 1MB

)

go

if exists(select * from sysobjects where name = 'Users')

drop table Users

create table Users

(

Id int identity not null,

Name varchar(10) not null,

IdCard int not null

)

go

insert Users(Name, IdCard)values('linukey', 1001)

insert Users(Name, IdCard)values('binghe',  1002)

insert Users(Name, IdCard)values('紫枫',    1003)

insert Users(Name, IdCard)values('好的',    1004)

go

if exists(select * from sysobjects where name = 'pk_Id')

alter table Users

drop constraint pk_Id

alter table Users

add constraint pk_Id primary key(Id)

go

/*1*/

select Id, Name, IdCard from Users where Id = 1 and Name = 'linukey'

/*2*/

select * from Users where Name like 'l%' order by IdCard ASC

/*3*/

select * from Users where Id between 1 and 4 and IdCard > 1002

/*4*/

select Name as '姓名', '身份号' = IdCard, '加密号' = Id + IdCard, 所在学校 = '天津师范大学' from Users

/*5*/

select * from Users where IdCard in (1001,1002)

/*6*/

select 总人数 = COUNT(Id) from Users

/*

重点掌握:

like between in

sum avg max min count

*/

if exists(select * from sysobjects where name = 'Tiezi')

drop table Tiezi

create table Tiezi

(

Id int identity not null,

Name varchar(10) not null,

TieCount int not null

)

go

insert Tiezi(Name, TieCount)values('linukey', 10)

insert Tiezi(Name, TieCount)values('binghe',  20)

insert Tiezi(Name, TieCount)values('紫枫',    30)

go

/*多表查询*/

select Users.Name,IdCard,TieCount from Users inner join Tiezi on Users.Name = Tiezi.Name where TieCount > 10

/*三张表的话加两个inner*/

/*左外链接*/

select Users.Name,IdCard,TieCount from Users left outer join Tiezi on Users.Name = Tiezi.Name 

/*右外链接*/

select Users.Name,IdCard,TieCount from Users right outer join Tiezi on Users.Name = Tiezi.Name

/*分组查询*/

group by

/*使用having筛选分组统计后的结果*/

group by

having 条件