sqlServer

--创建数据库
create database fan1
on(
name="fan1_table",
filename="D:\\SQL\\fan1.mdf",
size=5,
filegrowth=10%
)
log on(
name="fan1_table_log",
filename="D:\\SQL\\fan1_table_log.ldf",
size=1,
filegrowth=10%
)
 --备份数据库
  backup database qy73_123 to disk ='e:\SQL\qy73_123.bak'


  --还原数据库
  restore database qy73_123 from disk ='e:\SQL\qy73_123.bak' with replace


--用fan1数据库创建表
use fan1
go 
create table stuifo(
id int primary key not null,
name varchar(10)
)
--修改列属性
create table classifo(
id int
)
--为表增加列
alter table classifo
add name varchar(10)


--为表中的列增加属性]
alter table classifo
alter column id int not null


--查询表
select * from classifo


--为表中数据添加主键
alter table classifo
add  constraint pk_classifo_id primary key(id)


--添加外键约束
alter table classifo
add constraint fk_fan1_classifo_id foreign key (id)references stuifo(id)


--删除外键约束
alter table classifo
drop constraint  fk_fan1_classifo_id


--添加约束
alter table classifo
add constraint ck_classifo_city check (city in('深圳','上海'))


--添加默认约束
alter table classifo
add constraint df_classifo_provice default ('北京')for provice 


--添加唯一约束
alter table classifo
add constraint uq_classifo_id unique(id)


--向表中添加一行数据
insert into classifo(id,name,city,provice)
values (5,'张三','深圳','广州')


--向表中添加多行数据
insert into classifo(id,name,city,provice)
select 13,'李四','上海','南京'union
select 10,'李四','上海','南京'union
select 11,'李四','上海','南京'union
select 12,'李四','上海','南京'


--查询前10列
select top 10 percent * from userifo


--为表添加别名
select id as 学号,age as 年龄,name as 姓名,city as 居住地 from userifo
--增加数据
insert into table() values()

--删除数据
delete from  table where ID = ？

--修改数据
update userifo set city='洛阳'where id=3


--order by排序查询
select *from userifo order by age desc


--按组查询
select t.TeacherName,count(s.SubjectID) as '课程总数'  from Teacher as t  join TeachSubject as s   on t.TeacherID=s.TeacherID group by t.TeacherName


--
select empno ,empname from teacher where title =(select title from teacher where EmpName='钱学森')


--继续使用NewsInfo表和ReplyInfo表，要求查询出回复数量最多的新闻标题及回复个数
select top 1 n.title,count(r.recontent)as '回复个数' from newsinfo n left join replyinfo r on n.newsid=r.newsId 
group by title order by count(r.recontent) desc

--连接查询
select a.classname,count(b.studentid) from class a left join studentinfo b on a.classid=b.classid group by  a.classname  
order by count(b.studentid)  desc


--分页语法
select top pageSize * from tablename where id not in(
select top (pageNO-1)*pageSize from tablename
)