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.
22 trang |
Chia sẻ: Thục Anh | Ngày: 11/05/2022 | Lượt xem: 410 | Lượt tải: 0
Bạn đang xem trước 20 trang nội dung tài liệu Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Lecture 8: How to insert, update, and delete data - Lê Thị Tú Kiên, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 1
Lecture 8
How to insert, update,
and delete data
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 2
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 3
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 4
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 5
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 © 2012, Mike Murach & Associates, Inc.
Slide 6
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 Le Thi Tu Kien – FIT - HNUE
Slide 7
The values for a new row in the Employee table
Column Value
FName Thanh
Minit T
LName Nguyen
SSN 223344555
BDate 1980-08-15
Address 460 Dallas, Houston,TX
Sex F
Salary 35000
SuperSSN 888665555
DNo 4
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 8
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 9
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 10
The definition of the ColorSample table
Column Data Allow Default
Name Type Length Identity Nulls Value
ID Int 4 Yes No No
ColorNumber Int 4 No No 0
ColorName VarChar 10 No Yes No
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 11
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 12
The ColorSample table after the rows are inserted
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 13
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 14
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 15
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 16
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 17
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 18
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 19
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 20
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 21
Delete all the rows
DELETE EmployeeCopy;
Warning
If you omit the WHERE clause from a DELETE
statement, all the rows in the table will be deleted.
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 22
A subquery used in a search condition
DELETE EmployeeCopy
WHERE DNo = (SELECT DNumber
FROM Department
DName = 'Research')
The same statement using a join
DELETE EmployeeCopy
FROM EmployeeCopy JOIN Department
ON DNo=DNumber
WHERE DName = 'Research';
Các file đính kèm theo tài liệu này:
- bai_giang_he_quan_tri_co_so_du_lieu_sql_server_lecture_8_how.pdf