SQL练习

--Create Database school
--go
--use school
--go
--create table student
--(
--ID int IDentity(1,1) not null,
--S_ID int primary key not null,
--S_Name nvarchar(50) not null,
--T_ID INT not null,
--C_ID INT not null,
--)
--Create table Teacher
--(
--ID int identity(1,1) not null,
--T_ID int primary key not null,
--T_Name nvarchar(50) not null,
--SU_ID INT not null
--)
--Create table Class
--(
--ID int identity(1,1) not null,
--C_ID int primary key not null,
--C_Name nvarchar(50) not null,
--)
--Create TABLE Subjects
--(
--ID int identity(1,1) not null,
--SU_ID int primary key not null,
--SU_Name nvarchar(50) not null
--)
--insert into class values(001,'电子电子商务班')
--insert into class values(002,'汽修班')
--insert into class values(003,'物联网')
--insert into class values(004,'市场营销')
--insert into class values(005,'动漫设计')
--insert into class values(006,'工程造价')
--insert into class values(007,'软件工程')
--insert into Class values(008,'土木工程')

--insert into subjects values(1,'高等数学')
--insert into subjects values(2,'离散性数学')
--insert into subjects values(3,'市场营销')
--insert into subjects values(4,'JAVA开发')
--insert into subjects values(5,'动漫概论')
--insert into subjects values(6,'单片机开发')
--insert into subjects values(7,'毛概')

--insert into teacher values(1,'张三',1)
--insert into teacher values(2,'李四',2)
--insert into teacher values(3,'王五',3)
--insert into teacher values(4,'朱军',4)
--insert into teacher values(5,'谭浩强',5)
--insert into teacher values(6,'李宁',6)
--insert into teacher values(7,'王琦',7)

--insert into student values(101,'杨一',2,6)
--insert into student values(102,'杨二',1,1)
--insert into student values(103,'杨三',3,4)
--insert into student values(104,'杨四',4,7)
--insert into student values(105,'杨五',7,2)
--insert into student values(106,'杨六',5,3)
--insert into student values(107,'杨七',6,5)
--insert into student values(108,'杨八',5,9)

--select *from student

--select s_name 学生姓名,t_name 老师姓名,c_name 班级 from Student as s,teacher as t,class as c where s.t_id=t.t_id and s.c_id=c.c_id --多表连接查询

--select s_name 学生姓名, t_name 老师姓名,c_name 班级 from Student s inner join teacher t on s.t_id=t.t_id inner join Class c on s.c_id=c.c_id --内连接

--select s_name 学生姓名,t_name 老师姓名,c_name 班级 from Student s left join Teacher t on s.T_ID= t.T_ID left join Class c on s.C_ID=c.C_ID --左连接

--select s_name 学生姓名,t_name 老师姓名,c_name 班级 from student s right join Teacher t on s.T_ID=t.T_ID right join Class c on s.C_ID=c.C_ID --右连接

--select s.S_ID 学号,s.S_Name 姓名,t.T_Name 老师 from student s inner join Teacher t on s.T_ID=t.T_ID where t.ID=5 --条件查询

--select COUNT(s_id) from student--统计

--select s_name 学生姓名,t_name 老师姓名,c_name 班级 from student s inner join Teacher t on s.T_ID=t.T_ID inner join Class c on s.C_ID=c.C_ID where t.T_Name like '%李%' --模糊查询

--修改
--update student set S_Name='李二狗' where S_ID=104

--删除
--delete from student where S_ID=101

--视图
--create view stu as select *from student
--select *from stu

--存储过程
--create proc get_Student
--(
--@t_name nvarchar(50),
--@s_name nvarchar(50)
--)
--as
--select s.S_Name,s.S_ID,s.C_ID, t.t_name from student s inner join Teacher t on s.T_ID=t.T_ID where t.T_Name=@t_name and s.S_Name=@s_name
--exec get_student'张三','杨二'