›
›

Bài giảng Microsoft SQL Server - Lecture 8: How to insert, update, and delete data - Lê Thị Tú Kiên

Download
Lecture 8  
How to insert, update,  
and delete data  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 1  
Objectives  
Applied  
Given the specifications for an action query, code the INSERT,  
UPDATE, or DELETE statement for doing the action.  
Create a copy of a table by using the INTO clause of the SELECT  
statement.  
Knowledge  
Describe the three types of action queries.  
Explain how to handle null values and default values when coding  
INSERT and UPDATE statements.  
Explain how the FROM clause is used in an UPDATE or  
DELETE statement.  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 2  
The syntax of the SELECT INTO statement  
SELECT select_list  
INTO table_name  
FROM table_source  
[WHERE search_condition]  
[GROUP BY group_by_list]  
[HAVING search_condition]  
[ORDER BY order_by_list]  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 3  
Create a complete copy of the Employee table  
SELECT *  
INTO EmployeeCopy  
FROM Employee;  
Create a partial copy of the Employee table  
SELECT *  
INTO RetireEmp  
FROM Employee  
WHERE YEAR(GETDATE())- YEAR(bdate)>60  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 4  
Create a table with summary rows  
SELECT DNo, SUM(Salary) AS  
SumOfSalaries  
INTO DepSumSalary  
FROM Employee  
GROUP BY DNo;  
Warnings  
When you use the SELECT INTO statement to create a table, only  
the column definitions and data are copied.  
Definitions of primary keys, foreign keys, indexes, default values,  
and so on are not included in the new table.  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 5  
The syntax of the INSERT statement  
INSERT [INTO] table_name  
[(column_list)]  
[DEFAULT] VALUES (expression_1 [,  
expression_2]...)  
[, (expression_1 [,  
expression_2]...)...]  
Murach's SQL Server 2012, C7  
© 2012, Mike Murach & Associates, Inc.  
Slide 6  
The values for a new row in the Employee table  
Column  
FName  
Minit  
Value  
Thanh  
T
LName  
SSN  
Nguyen  
223344555  
BDate  
Address  
Sex  
1980-08-15  
460 Dallas, Houston,TX  
F
Salary  
SuperSSN  
DNo  
35000  
888665555  
4
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 7  
Insert the row without using a column list  
INSERT INTO EmployeeCopy  
VALUES ('Thanh', 'T', 'Nguyen', '223344555',  
'1980-08-15','460 Dallas, Houston,TX',  
'F', 35000, 888665555,4);  
Insert the row using a column list  
INSERT INTO EmployeeCopy  
(FName, Minit,LName,SSN,BDate,Address,  
Sex,Salary,SuperSSN,DNo)  
VALUES ('Thanh', 'T', 'Nguyen', '223344555',  
'1980-08-15','460 Dallas, Houston,TX',  
'F', 35000, 888665555,4);  
The response from the system  
(1 row(s) affected)  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 8  
Insert three rows  
INSERT INTO Employee  
VALUES  
(N'Jonh  
', N'B', N'Smith', N'123456789',  
CAST(0x21F20A00 AS Date), N'731 Fondren,  
Houston, TX', N'M', 30000, N'333445555', 5),  
(N'Franklin ', N'T', N'Wong', N'333445555',  
CAST(0x83050B00 AS Date), N'638 Voss, Houston,  
TX', N'M', 40000, N'888665555', 5),  
(N'Joyce  
', N'A', N'English', N'453453453',  
CAST(0xE8FC0A00 AS Date), N'5631 Rice, Houston,  
TX', N'M', 25000, N'333445555', 5);  
The response from the system  
(3 row(s) affected)  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 9  
The definition of the ColorSample table  
Column  
Name  
ID  
ColorNumber Int  
ColorName  
Data  
Type  
Int  
Allow Default  
Length Identity Nulls Value  
4
4
Yes  
No  
No  
No  
No  
Yes  
No  
0
No  
VarChar 10  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 10  
Six INSERT statements for the ColorSample table  
INSERT INTO ColorSample (ColorNumber)  
VALUES (606);  
INSERT INTO ColorSample (ColorName)  
VALUES ('Yellow');  
INSERT INTO ColorSample  
VALUES (DEFAULT, 'Orange');  
INSERT INTO ColorSample  
VALUES (808, NULL);  
INSERT INTO ColorSample  
VALUES (DEFAULT, NULL);  
INSERT INTO ColorSample  
DEFAULT VALUES;  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 11  
The ColorSample table after the rows are inserted  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 12  
The syntax of the INSERT statement  
for inserting rows selected from another table  
INSERT [INTO] table_name [(column_list)]  
SELECT column_list  
FROM table_source  
[WHERE search_condition]  
Insert retire employees into the RetireEmp table  
INSERT INTO RetireEmp  
SELECT *  
FROM Employee  
WHERE YEAR(GETDATE()) - YEAR(bdate)>60  
Note: The table RetireEmp must aready  
exist in the database COMPANY.  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 13  
The same INSERT statement with a column list  
INSERT INTO RetireEmp  
(FName, Minit,LName,SSN,BDate,  
Address,Sex,Salary,SuperSSN,DNo)  
SELECT  
FName, Minit,LName,SSN,BDate,  
Address,Sex,Salary,SuperSSN,DNo  
FROM Employee  
WHERE YEAR(GETDATE()) - YEAR(bdate)>60  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 14  
The syntax of the UPDATE statement  
UPDATE table_name  
SET column_name_1 = expression_1 [,  
column_name_2 = expression_2]...  
[FROM table_source [[AS] table_alias]  
[WHERE search_condition]  
Update two columns of a single row  
UPDATE EmployeeCopy  
SET Salary = 40000,  
DNo = 5  
WHERE SSN = '223344555';  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 15  
Update one column of multiple rows  
UPDATE EmployeeCopy  
SET DNo=3  
WHERE DNo = 4;  
Update a column using an arithmetic expression  
UPDATE EmployeeCopy  
SET Salary = Salary + 1000  
WHERE DNo = 3;  
Warning  
If you omit the WHERE clause, all the rows in the  
table will be updated.  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 16  
A subquery that returns the value assigned  
to a column  
UPDATE EmployeeCopy  
SET Salary = (SELECT AVG(Salary)  
FROM EmployeeCopy  
WHERE DNo = 5)  
WHERE SSN = '223344555';  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 17  
A subquery used in a search condition  
UPDATE EmployeeCopy  
SET Salary = Salary + 1000  
WHERE DNo = (SELECT DNumber  
FROM Department  
WHERE DName = 'Research');  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 18  
A column in a joined table  
used in a WHERE clause  
UPDATE EmployeeCopy  
SET Salary = Salary + 1000  
FROM EmployeeCopy JOIN Department  
ON DNo=DNumber  
WHERE DName = 'Research';  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 19  
The syntax of the DELETE statement  
DELETE [FROM] table_name  
[FROM table_source]  
[WHERE search_condition]  
Delete a single row from the EmployeeCopy table  
DELETE EmployeeCopy  
WHERE Ssn = '223344555';  
(1 row(s) affected)  
Delete all the employees for a department  
DELETE EmployeeCopy  
WHERE Dno = 3;  
(3 row(s) affected)  
Murach's SQL Server 2012, C7  
Le Thi Tu Kien – FIT - HNUE  
Slide 20  
Tải về để xem bản đầy đủ
pdf 22 trang yennguyen 20340 Free
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Microsoft SQL Server - Lecture 8: How to insert, update, and delete data - Lê Thị Tú Kiên", để tải tài liệu gốc về máy hãy click vào nút Download ở trên

File đính kèm:

  • pdfbai_giang_microsoft_sql_server_lecture_8_how_to_insert_updat.pdf