Shulou(Shulou.com)06/01 Report--

T-SQL query statement

1. Tansact-SQL programming language

The American National Standards Institute (ANSI) and the International Standards Organization (ISO) define standards for SQL. Microsoft is compatible with Transact-SQL and ANSI-SQL, and Transact-SQL also includes several extensions that enhance performance.

The composition of T-SQL:

Data definition language (Date Definition Language) statement is abbreviated to DDL statement

The DDL statement is used to build the database, database objects (create,alter,drop)

Create object_name

Alter object_name

Drop object_name

Give an example of the use of the DDL statement:

Use schoolDB

Create table teacher

(cust_idint, company varchar (40), contact varchar (30), phone char (12))

Go

Alter table teacher add ageint default 30

Data Control language (Date Control Language) statement

Used to change permissions related to a user or role

Grant

Deny

Revoke

Examples are as follows:

Use schoolDB

Grant select on products to public

Go

Data manipulation language (Date Manipulation Language) statement

Manipulate the data in the database. You can change data in the database or query information in the database

Examples are as follows:

Select*from dbo.TStudent / / query data in the TStudent table

Insert dbo.TStudent (StudentID,Sname,sex) values ('0000001901' Chen Yinghong', male') / / insert the corresponding record for the studentID,Sname,sex column in the TStudent table

Update dbo.TStudent set Sname=' Zhang Chunhai', sex=' male 'the name of studentID 0000000569 in the where studentid='0000000569'// update table was changed to' Zhang Chunhai'

Delete dbo.TStudent where StudentID='0000000020'// deletes records with a studentID of 0000000020

2. Ttansact-SQL grammatical elements

1. Batch processing

Go

A batch command tells SQLServer to parse and run all instructions for a batch

It's not really a Transact-SQL statement, it's just a description of a batch. The scope of local variables is limited to one batch and must be on a single line.

USE schoolDB

Go

Select * from dbo.TScore-- look from that table

Where mark+9 = 70 and mark95

Having is equivalent to condition.

4. Data modification

4.1. Insert data

Insert dbo.TStudent values ('0000001902,' Li Weiwei', 'male', '132302197506055634', '1984-3-4', 'Liweiweibdqn.com', 'network class', getdate ())

4.2. Insert part of the data

Insert student number; name, gender, other columns are empty

Insert dbo.TStudent (StudentID,sex,Sname) values ('0000001903' male', 'Zhang Guoqiang')

4.3. Insert query records into the new table created

Create tables using SELECT INTO.

Query the development class in the student table from the student to a new table.

Select StudentID,Sname,sex,Email into TDe from dbo.TStudent where Class=' development class'

4.4. Delete data

Delete the student whose student number is 0000000020

Delete dbo.TStudent where StudentID='0000000020'

Delete students born before 1982

Deletedbo.TStudentwhereBirthday

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.