--创建数据库
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
)
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
)