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 条件