(SQL) 第八章 用SQL语句操作数据

上机练习一 增加学生记录

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [StudentNo]
      ,[LoginPwd]
      ,[StudentName]
      ,[Sex]
      ,[GradeId]
      ,[Phone]
      ,[Address]
      ,[BornDate]
      ,[Email]
  FROM [MySchool].[dbo].[Student]
  
  --INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate,Email)
 --VALUES('S12301302001','zhangsan','张三','男',1,'01062768866','解放路','1991-1-1','zhangsan@126.com')
  
  --INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
  --VALUES('S1201302002','lisi89','李四','男',1,'13812345678','长江路','1995-5-3')
 
 --INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
  --VALUES('S1201302003',DEFAULT,'王五','男',1,'13912345678',DEFAULT,'1994-5-3') 
  
 -- INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate,Email)
   --VALUES('S1201302004','wanglili','王丽丽','女',1,'13112345678',DEFAULT,'1994-6-2','wanglili@.solu.com')
 
 INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
  VALUES('S1201302005',DEFAULT,'张峰','男',1,'13212345678',DEFAULT,'1991-8-8')
  use MySchool

上机练习三 增加科目记录

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [SubjectName]
      ,[ClassHour]
      ,[Gradeld]
  FROM [MySchool].[dbo].[Table_1]
  USE MySchool
  INSERT Table_1(SubjectName,ClassHour,Gradeld)
  SELECT '走进JAVA编程世界',40,1 UNION
  SELECT 'HTML和CSS网页技术',60,1 UNION
  SELECT 'C#语言和数据库技术',70,1 

上机练习四 增加成绩记录

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [StudentNo]
      ,[kemu]
      ,[Score]
      ,[time]
  FROM [MySchool].[dbo].[Table_2]
  USE MySchool
  INSERT Table_2(StudentNo,kemu,Score,time)
  SELECT 'S1201302001',1,80,'2013-9-13' UNION
  SELECT 'S1201302002',1,45,'2013-9-13' UNION
  SELECT 'S1201302001',2,90,'2013-10-18' UNION
  SELECT 'S1201302002',2,60,'2013-10-18'
  

上机练习五 (修改)

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [StudentNo]
      ,[LoginPwd]
      ,[StudentName]
      ,[Sex]
      ,[GradeId]
      ,[Phone]
      ,[Address]
      ,[BornDate]
      ,[Email]
  FROM [MySchool].[dbo].[Student]
  USE MySchool
  UPDATE Student SET Address='山东省济南市文化路1号院'
  WHERE  StudentNo='S1201302004'
  UPDATE Student SET GradeId=2
  WHERE StudentNo='S1201302005'
  
UPDATE  dbo.Table_1 SET ClassHour=55 where SubjectName='HTML和CSS网页技术'

课后作业

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [id]
      ,[pwd]
      ,[blance]
      ,[name]
  FROM [ONE].[dbo].[Table_1]
  USE ONE
  INSERT INTO dbo.Table_1(id,pwd,blance,name)
  SELECT '0023_ABC','abc',100,'张军' UNION
  SELECT '0024_ABD','abd',200,'李功凯' UNION
  SELECT '0024_ABE','abe',300,'朱俊' UNION
  SELECT '0036CCD','36ccd',100,'何柳' UNION
  SELECT '0089_EDE','zhang',134,'张俊'
  UPDATE dbo.Table_1 SET pwd='0023abc' WHERE name='张军'
  UPDATE dbo.Table_1 SET blance=98  WHERE name='张军'
  UPDATE dbo.Table_1 SET blance=44 WHERE name='朱俊'
  DELETE FROM dbo.Table_1  WHERE id='0024_ABD'