第八章：用SQL语句操作数据

<->使用T-SQL插入数据

一：SQL的组成

（１）DML（数据操作语言）

插入，删除和修改数据库中的数据INSERT，UPDATE，DELETE等。

（２）：DCL（数据控制语言）

用来控制存取许可，存取权限GRANT，REVOKE等。

（３）：DQL（数据查询语言）

用来查询数据库中的数据SELETE等。

（４）：DDL（数据定义语言）

用来建立数据库，数据库对象和定义表的列GREATE，TABLE，DROP，TABLE等。

二：运算符

算术运算符，赋值运算符，比较运算符，逻辑运算符

<>不等于 !=不等于

三：T-sql

结构化查询语言：不区分大小写。

(1)：插入数据行语法：

INSERT [INTO] 表名 [(列名)] VALUES（值列表）

(2)：插入多行数据语法：

INSERT INTO <表名> (列名)

SELETE <列名>

FROM <源表名>

一：

INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate,Email)
VALUES('S1201302001','zhangsan','张三','男','1','01062768866','解放路','1991-1-1','zhangsan126@126.com');

INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
VALUES('S1201302002','lisi89','李四','男','1','13812345678','长江路','1996-1-1');

INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
VALUES('S1201302003','DEFAULT','王五','男','1','13912345678','DEFAULT','1995-5-3');

INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate,Email)
VALUES('S1201302004','wanglili','王丽丽','女','1','13112345678','DEFAULT','1994-6-2','wangll@sohu.com');

INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeId,Phone,Address,BornDate)
VALUES('S1201302005','DEFAULT','张峰','男','1','13212345678','DEFAULT','1991-8-8');

二：

INSERT INTO Subject (SubjectName,ClassHour,GradeId)VALUES('走进Java编程世界',40,1) 

INSERT INTO Subject (SubjectName,ClassHour,GradeId)VALUES('HTML和CSS网页技术',60,1)

INSERT INTO Subject (SubjectName,ClassHour,GradeId)VALUES('C#语言和数据库技术',70,1)

三：

UPDATE Student 
SET Address ='山东省济南市文化路1号院'
WHERE StudentNo='S1201302004'

UPDATE Student 
SET GradeId ='2'
WHERE StudentNo='S1201302005'

UPDATE Student 
SET Email ='未知@'
WHERE StudentNo='S1201302001'

UPDATE Student 
SET Email ='未知@'
WHERE StudentNo='S1201302002'

UPDATE Student 
SET Email ='未知@'
WHERE StudentNo='S1201302003'

UPDATE Student 
SET Email ='未知@'
WHERE StudentNo='S1201302004'

UPDATE Student 
SET Email ='未知@'
WHERE StudentNo='S1201302005'

UPDATE Subject 
SET ClassHour='55'
WHERE SubjectId='2'

UPDATE Subject 
SET ClassHour='55'
WHERE SubjectId='3'

<->使用T-SQL更新删除数据

一：使用UPDATE更新数据行

语法：

UPDATE 表名 SET 列名=更新值

IWHERE [更新条件]

注意：

1.更新多列数据使用逗号隔开。

2.勿忘条件限制，以防有效数据丢失。

二：使用DELETE删除数据行

语法-：

DELETE [FROM] 表名 [WHERE<删除条件>]

语法二：

TRUNCATE TABLE 表名

作用：删除的数据不能恢复。

INSERT Result (StudentNo,SubjectId,StudentResult,ExamDate)
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'

一：修改记录

UPDATE Result 
SET StudentResult=StudentResult+5
WHERE Id='1'

UPDATE Result 
SET StudentResult=StudentResult+5
WHERE Id='3'

UPDATE Result 
SET StudentResult='55'
WHERE Id='2'


UPDATE Result 
SET StudentResult='55'
WHERE Id='4'

二：删除记录

DELETE FROM Result
WHERE Id='2'

DELETE FROM Result
WHERE Id='4'

<三>使用T-SQL导入导出数据

(1)：使用UPDATE更新数据，一般有限制条件。

(2)：使用DELETE删除数据，不能删除主键值被引用数据行。

(3)：数据导入导出功能可以更文本文件，Excel文件交换数据。

简答2：

INSERT INTO Card(ID,PassWord,Balance,UserName)
VALUES('0023_ABC','abc','100','张军');

INSERT INTO Card(ID,PassWord,Balance,UserName)
VALUES('0024_ABD','abd','200','李公凯');

INSERT INTO Card(ID,PassWord,Balance,UserName)
VALUES('0024_ABE','abe','300','朱俊');

INSERT INTO Card(ID,PassWord,Balance,UserName)
VALUES('0089_EDE','zhang','134','张俊');

INSERT INTO Card(ID,PassWord,Balance,UserName)
VALUES('0036_CCD','36ccd','100','何柳');

UPDATE Card
SET PassWord='0023abc'
WHERE ID='0023_ABC'

UPDATE Card
SET Balance='98'
WHERE ID='0023_ABC'

UPDATE Card
SET Balance='44'
WHERE ID='0024_ABE'

DELETE FROM Card
WHERE ID='0024_ABD'

简答3：

INSERT INTO Trainlnfo(TNumber,TFrom,TDest,TDistance,TStartTime,TEndTime,TDday,TSpeed,TUnitPrice)
VALUES('K12','石家庄','武汉','1233','13:00:00','01:00:00','2','140','1.4');

INSERT INTO Trainlnfo(TNumber,TFrom,TDest,TDistance,TStartTime,TEndTime,TDday,TSpeed,TUnitPrice)
VALUES('K181','长沙','北京','1850','22:14:00','06:40:00','1','120','0.7');

INSERT INTO Trainlnfo(TNumber,TFrom,TDest,TDistance,TStartTime,TEndTime,TDday,TSpeed,TUnitPrice)
VALUES('T5','长沙','北京','1788','18:30:00','23:10:00','1','180','1.3');

INSERT INTO Trainlnfo(TNumber,TFrom,TDest,TDistance,TStartTime,TEndTime,TDday,TSpeed,TUnitPrice)
VALUES('T8','武汉','重庆','1455','22:00:00','21:20:00','2','150','2');

INSERT INTO Trainlnfo(TNumber,TFrom,TDest,TDistance,TStartTime,TEndTime,TDday,TSpeed,TUnitPrice)
VALUES('Z11','武汉','南京','1844','09:30:00','14:20:00','2','135','2');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('K181','2013-05-06','张亮','0','0','0');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('K181','2013-05-07','张亮','0','0','2');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('K181','2013-05-08','张亮','100','50','0');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('T5','2013-05-06','刘军','122','34','6');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('T5','2013-05-07','李章','300','200','50');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('T5','2013-05-08','李章','300','200','10');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('Z11','2013-05-06','杨过','0','0','0');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('Z11','2013-05-07','杨过','20','9','0');

INSERT INTO TrainScheduler(SNumber,SDate,SConductor,SSeatNumber,SLieNumber,SVisaNumber)
VALUES('Z11','2013-05-08','杨过','100','80','0');