Bài giảng Microsoft SQL server: Bài 9 - TS. Lê Thị Tú Kiên

"Bài giảng Microsoft SQL server - Bài 9: Chèn, sửa, xóa dữ liệu" đưa ra các thông số kỹ thuật cho một truy vấn hành động, viết mã câu lệnh INSERT, UPDATE hoặc DELETE để thực hiện hành động; tạo một bản sao của bảng bằng cách sử dụng mệnh đề INTO của câu lệnh SELECT.
Nội dung trích xuất từ tài liệu:
Bài giảng Microsoft SQL server: Bài 9 - TS. Lê Thị Tú Kiên Lecture 8 How to insert, update, and delete dataMurachs 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.Murachs 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]Murachs 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)>60Murachs 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.Murachs 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]...)...]Murachs SQL Server 2012, C7 © 2012, Mike Murach & Associates, Inc. Slide 6 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 4Murachs SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE Slide 7Insert the row without using a column listINSERT INTO EmployeeCopyVALUES (Thanh, T, Nguyen, 223344555, 1980-08-15,460 Dallas, Houston,TX, F, 35000, 888665555,4);Insert the row using a column listINSERT 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) Murachs SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE Slide 8Insert three rowsINSERT INTO EmployeeVALUES(NJonh , NB, NSmith, N123456789,CAST(0x21F20A00 AS Date), N731 Fondren,Houston, TX, NM, 30000, N333445555, 5),(NFranklin , NT, NWong, N333445555,CAST(0x83050B00 AS Date), N638 Voss, Houston,TX, NM, 40000, N888665555, 5),(NJoyce , NA, NEnglish, N453453453,CAST(0xE8FC0A00 AS Date), N5631 Rice, Houston,TX, NM, 25000, N333445555, 5);The response from the system (3 row(s) affected) Murachs SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE Slide 9 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 NoMurachs 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;Murachs SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE Slide 11 The ColorSample table after the rows are insertedMurachs SQL Serv ...