9. SQL Server Operational Data

Inserting data

Insert using INSERT INTO

if (exists (SELECT * from sys.databases, where name = ' webdb '))    drop database webdbgo--Create databases creation db WEBDB on prim ary (    name = ' WebDB ',    filename= ' d:\webDB.mdf ',    size = 5MB,    maxsize=unlimited,    filegrowth=10%) Log on (    name = ' Webdb_log ',    filename = ' d:\webDB.ldf ',    size=3mb,    maxsize=50mb,    filegrowth= 2MB) Use webdbgo--CREATE TABLE student (    ID int identity (primary) key,    name varchar (a) NOT NULL,    Sex char (2) NOT NULL, age    int)--Inserts a row of data using INSERT into student (Name,sex,age) VALUES (' Bai Davei ', ' man ', ')--id for the identity column Autogrow does not need to add a value for it-insert a column inserts into student (Name,sex) VALUES (' Weilifu ', ' demon ')--if all columns are inserted, you can insert into student values without writing the column name (' Li Dong ', ' male ', 37)

Insert identity column SET IDENTITY_INSERT

--If you need to insert a value in the identity column, you can use the SET IDENTITY_INSERT statement, but note that the primary key is not repeatable! SET IDENTITY_INSERT student on--set Allow Insert identity column INSERT into student (Id,name,sex,age) VALUES (4, ' beam not cheap ', ' male ', ') set Identity_ Insert student off--close after insertion

Insert multiple rows of data using INSERT into

--using INSERT INTO to insert multiple rows of data, using commas to split insert into student (Name,sex,age) VALUES (' Zhang San ', ' Male ', 18), (' John Doe ', ' female ', 19), (' Harry ', ' female ', 20)

Using the SELECT statement to insert

--Use the SELECT statement to insert multiple rows of data from an existing table into the target table insert into student (name,sex,age) select Sname,ssex,sage from oldtable

Insert using SELECT INTO

--Insert data using SELECT INTO--this method actually creates a new table and then adds all the rows to the newly created table by the SELECT statement. Select Id,name,sex,ageinto newtablefrom student--The table created in this way, there are no primary keys, indexes, and constraints, and the length of the table columns also changes. Not suitable for creating permanent tables

Update data

Updating data using the UPDATE statement

Note that if you do not limit the scope, the entire table will update the update student set Name= ' King sledgehammer ' where id = 3

Referencing multi-table update data

--Sometimes you'll need to refer to another table to restrict the update record CREATE TABLE [user] (    UserID int identity (primary) key,    userName varchar () NOT NULL,    PassWord varchar (+) NOT NULL,    Roleid int NOT NULL) CREATE TABLE [role] (    Roleid int identity (primary) key,    roleName varchar (+) NOT NULL,    Rolecontent varchar (not NULL) inserts into [user] values (' admin ', ' admin ', 1), (' Editor ', ' editor ', ' 2 '), (' System ', ' System ', 3) insert into [role] values (' admin ', ' webmaster '), (' Edit ', ' site Editor '), (' System ', ' System Administrator ') Update [role] Set rolecontent = ' Only the username in the user table is similar to ADM that modifies the ' from Role rinner join [user] UoN R.roleid = u.roleidwhere u.username like ' adm% '--if there is no such restriction, the entire table is updated SELECT * FROM [role]

Delete data

Delete with delete

-The entire table will be deleted if the condition is not restricted. Delete from student where id = 1

Referencing multiple tables deleting data

--First remove admindelete from user table from [user] where userName = ' Admin ' delete from [role]from [role] rleft outer join [user] UoN r.ro LeId = U.useridwhere U.roleid is null--delete the data that is not referenced in the user table in the Permissions table, the administrator is removed select * FROM [role]

Delete all rows using truncate

TRUNCATE TABLE oldtable--truncate deletes all rows and cannot specify a range.

Merging data

The Merge statement is a mixed statement of multiple functions that can be done in a single query, such as INSERT, Update, delete, and so on.

Performs an insert, update, or delete operation on the target table, based on the results of the join with the source table. The source table contains data rows that are about to be added (or updated) to the target table, and the target table accepts an insert (or update) operation, which can be synchronized for two tables.

SQL Server 2008 is not in the previous version, so it was previously deleted and then added, or write some branch conditions to determine if there is no re-insert or update.

--Creating the source table Create TABLE SourceTable (    ID int,    content varchar (30))--Creates the target table create TABLE targettable (    ID int,    Content varchar (30))--inserting test data insert into SourceTable values (1, ' S001 '), (2, ' S002 '), (3, ' S003 '), (4, ' S004 '), (5, ' S005 ') INSERT into targettable values (1, ' target001 '), (2, ' target002 '), (6, ' target006 '), (7, ' target007 ') SELECT * FROM sourcetable--source table--1        s001--2        s002--3        s003--4        s004--5        s005select * from targettable--target table--1        target001--2        target002--6        target006--7        target007--Writing merge statement merge into Targettable T    -- Target table using SourceTable s            --source table on t.id = S.id                --similar to join completes the match between two tables                when matched--if there are values in the two tables matched, update then the then update set T . Content = S.content            When not matched--if there is no matching result, insert then insert VALUES (s.id,s.content) if not matched by source
   
    --the target table exists but does not exist in the source table, delete then delete;--query again, the two tables synchronize
   

Returns the output data

The OUTPUT clause can return the affected data rows to any interface that executes the request, and can be inserted into a table.

The output clause can refer to a inserted or deleted virtual table, depending on the data before (deleted) or after modification (inserted) that needs to be modified.

Results of output INSERT statement execution

Insert INTO Studentoutput inserted.id,inserted.name,inserted.sex,inserted.age--inserted.* all select ' haha ', ' women ', 24-- Returns the record inserted by the INSERT statement, which is useful for finding the server-generated value and returning it to the application.

Output the execution result of the UPDATE statement

Update student set name= ' Chen ' output deleted.name as oldname,inserted.name as Updatevaluewhere id = 4--Returns the name before the modification and the modified name--can be To track the deletion of the database

Inserts the output result into a single table

--Create an audit table Db_audit (    ID int not NULL,    name varchar (a) NOT null,    sex  varchar () is not NULL,    Age int,    deletedate datetime NOT NULL        constraint df_deletedate_today default (GETDATE ())--defaults constraint current time) Delete from Studentoutput deleted.id,deleted.name,deleted.sex,deleted.ageinto Db_audit (id,name,sex,age) where ID <10-- Remove all of the IDs less than 10 and insert them into the audit table select * FROM Db_audit

The merge uses the OUTPUT clause to export the data that has just been changed.

Merge into Targettable t    --target table using SourceTable s            --source table on t.id = S.id                --Similar join completes match between two tables when matched                -- If the values in both tables are matched, update then the set t.content = S.content            When is not matched--if there is no matching result, insert then insert VALUES (s.id,s.content) When the not matched by source    --exists in the target table but does not exist in the source table, delete then Deleteoutput $action as the ID inserted by the as action,inserted.id as Inserted.content as inserted content, deleted.id as deleted id,deleted.content as deleted content;

9. SQL Server Operational Data