create database sqlserver
use sqlserver
--创建学校
create table school(id int primary key ,name varchar(255) default '耶鲁大学')
insert into school(id) values(1)
select * from school
--创建年级
use sqlserver
truncate from table school/**truncate好像输出表了 待验证**/
delete from school
create table class(id int constraint PK_class primary key ,name varchar(255),sid int constraint FK_class_school foreign key(sid) references school(id))
select * from class
insert into class values(1,'一班',1)
--创建学生
create table student(id int ,name varchar(255),age int,cid int)
alter table student alter column id int not null
go
alter table student add constraint PK_student primary key(id)
go
alter table student add check(age>0)
go
alter table student add constraint FK_student_class foreign key(cid) references class(id)
go
insert into student values(1,'Jack',23,1)
---联合查询
select school.name as 学校 , class.name as 年级,student.name as 姓名 ,student.age 年龄
from student student left join class class on student.cid=class.id left join school school on class.sid=school.id
---把结果放在一行
select name as 信息 from school
union all
select name from class
union all
select name from student
union all
select convert(varchar(255),age) from student
--查询表的信息
select * from sysobjects where id= object_id('school')
select * from syscolumns
--删除数据库
use master
drop database sqlserver
use sqlserver
--创建学校
create table school(id int primary key ,name varchar(255) default '耶鲁大学')
insert into school(id) values(1)
select * from school
--创建年级
use sqlserver
truncate from table school/**truncate好像输出表了 待验证**/
delete from school
create table class(id int constraint PK_class primary key ,name varchar(255),sid int constraint FK_class_school foreign key(sid) references school(id))
select * from class
insert into class values(1,'一班',1)
--创建学生
create table student(id int ,name varchar(255),age int,cid int)
alter table student alter column id int not null
go
alter table student add constraint PK_student primary key(id)
go
alter table student add check(age>0)
go
alter table student add constraint FK_student_class foreign key(cid) references class(id)
go
insert into student values(1,'Jack',23,1)
---联合查询
select school.name as 学校 , class.name as 年级,student.name as 姓名 ,student.age 年龄
from student student left join class class on student.cid=class.id left join school school on class.sid=school.id
---把结果放在一行
select name as 信息 from school
union all
select name from class
union all
select name from student
union all
select convert(varchar(255),age) from student
--查询表的信息
select * from sysobjects where id= object_id('school')
select * from syscolumns
--删除数据库
use master
drop database sqlserver
