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

This article will introduce you in detail the introduction and use of T-SQL query statements in SQL Server. The detailed steps are clear and the details are handled properly. I hope you can gain something through this article. Let's first take a look at the relevant terms and concepts:

A brief introduction to SQL

In data management, using SSMS for data maintenance has the advantages of visualization and convenience, but in batch maintenance or repeated maintenance of data, it is not only inconvenient to use SSMS every time, but also prone to errors. It is easy to solve the problem of repetitive or batch maintenance of data by writing SQL statements to maintain the database.

1. SQL and T-SQL

SQL is the abbreviation of Structured Query Language, that is, structured query language. The widespread adoption of SQL illustrates its advantages, benefiting all users, including application programmers, database administrators, and end users.

1) non-procedural language

SQL is a non-procedural language because it processes one record at a time and provides automatic navigation of the data. SQL allows users to work on high-level data structures without manipulating individual records and manipulating recordsets. All SQL statements can accept a collection as input and return a collection as output. The collection feature of SQL allows the output of one SQL statement to be the input of another SQL statement.

2) uniform language

SQL can be used for database activity models for all users, including system administrators, database administrators, application programmers, decision support system personnel, and many other types of end users. SQL provides commands for many tasks, including:

Query data; insert, modify and delete records in tables; create, modify and delete data objects; control access to data and data objects; ensure database consistency and integrity; 2. Composition of T-SQL

The T-SQL language mainly consists of the following parts:

Data manipulation language: used to query, insert, delete and modify data in a database, such as select, insert, update, delete

Data definition language: used to create databases, database objects and define their columns, mostly commands that start with CREATE, such as CREATE, ALTER, DROP

Data control language (Data Control Language,DCL): used to control the access permissions of database components, such as GRANT, REVOKE; II, using T-SQL statements to manipulate data tables

It is relatively simple to insert, update or delete table data in SQL Server Management. In addition, you can also use T-SQL statements to insert, update or delete table data.

1. Insert data

Insert data into a table using the insert statement

INSERT [INTO] [column name] VALUES

Where:

[INTO] is optional and can be omitted; table names are required, while table column names are optional, and if omitted, the order is the same as the order of the fields in the data table; multiple column names and multiple values lists are separated by commas

When inserting data, you need to be aware of the following:

It is impossible to insert only half a row or a few columns of data one row at a time

The number of data values must be the same as the number of columns, and the data type, precision and decimal places of each data value must match the corresponding columns.

The INSERT statement cannot specify a value for the identity column because its value grows automatically

For character type columns, you need to use single quotation marks when inserting data

If you specify that a column is not allowed to be empty when designing the table, the column must insert data, otherwise an error message will be reported

The inserted data item is required to meet the requirements of the CHECK constraint

Although you can not specify a column name, you should get into the habit of clearly specifying the inserted column and the corresponding value 1) create database, switch database, and create table create database benet; use benet Create table student statistics table (no. Int identity (1) not null) not null, name nvarchar (3) not null, gender nvarchar (2) not null, age varchar (3) null, identity card number varchar (18) primary key, date of birth datetime not null, intended subject nvarchar (5) null, tuition money not null check (tuition > = 0 and tuition fees insert into student statistics (name, sex, age) ID card number, date of birth, intended subject, tuition fee) values ('Zhang San', 'male','21 'memorial1111111111111111111111) 2001pxxxxxxxxxxxxxxxxxxxxxxxxxxxx) Insert into student statistics (name, gender, identity card number, date of birth, tuition fee) values ('Lili', 'female', '2222222222222222); insert into student statistics (name, sex, ID card number, date of birth, tuition) values (' Ma San', 'male', '3333333333'); select * from student statistics where tuition fees 6500) Select * from Student Statistics Table where tuition fee > = 8000 position select * from Student Statistics Table where tuition fee

< 6000;select * from 学生统计表 where 学费 select * from 学生统计表 where 学费 select * from 学生统计表 where 学费 between 6500 and 8500;select * from 学生统计表 where 学费 in (8000,7500,3000);select * from 学生统计表 where 年龄 is null;select * from 学生统计表 where 姓名 like '李%';select * from 学生统计表 where 姓名 like '李__'select * from 学生统计表 where 姓名 like '杜_'select * from 学生统计表 where 姓名 like '[杜-王]三';select * from 学生统计表 where 姓名 like '杜[^三六]';select * from 学生统计表 where 姓名='丽丽' and 学费=5000;select * from 学生统计表 where 姓名='张三' or 学费=8760;select * from 学生统计表 where not 学费=8000;select top 3 * from 学生统计表;select 姓名 as name,身份证号码 as idcard from 学生统计表;select * from 学生统计表 order by 学费 desc;select * from 学生统计表 order by 学费 asc;select distinct 意向科目 from 学生统计表;select * into new1 from 学生统计表;=7500 3、使用UNION关键字 UNION关键字用于将多个不同的数据或查询结果合并成一个新的结果集。不同的数据或查询结果要求数据个数、顺序、数据类型都一致。 insert into new2 (姓名,性别,年龄,身份证号码) select '娟娟','女','31','444444444444444444');select '杜五','男','27','555555555555555555');select '李二的','男','18','666666666666666666');select 姓名,性别,年龄,身份证号码 from 学生统计表五、T-SQL语句单表查询案例1、创建products表 create table products ( 编号 int identity (1,1) primary key, 名称 nvarchar(10) not null, 种类 nvarchar(10) not null, 成本 money not null check (成本 >

= 0 and cost ('watermelons', 'fruits', '4.1 lemons'), ('celery', 'vegetables', '1.0 lemons'), ('tomatoes', 'vegetables', '2.9 lemonade 2017UniUniver'), ('cucumbers', 'vegetables', '2.2 lemmings'), ('bananas', 'fruits', '6.1') ), ('walnuts', 'nuts', '28.5 pistachios', 'nuts'), ('pistachios', 'nuts', '38.11 pistachios', 'nuts', '38.11 pistachios', '20176plash 21'), ('blueberries', 'fruit', '50.2', '50.2', '50.2', '50.2', '2017, 05, 15') Insert into products values ('watermelon', 'fruit', '4.1 lemon'); insert into products values ('celery', 'vegetable', '1.0 lemon'); insert into products values ('tomato', 'vegetable', '2.9 lemon'); insert into products values ('cucumber', 'vegetable', '2.2 lemon', '2.2 lemon', 2017Universe 09') Insert into products values ('banana', 'fruit','6. 1'); insert into products values ('walnut', 'nut', '28.5'); insert into products values ('pistachio', 'nut', '38.11'); insert into products values ('blueberry', 'fruit', '50.2', '50.2'); select * from products 3 and cost < 40 order by cost desc

4) query the top 5 products with the highest cost select * from products where cost in (4.1meme 6.1 mine28.5pint 38.11pl 50.2)

5) query what types of products are available, select distinct types, from products

6) insert the name, category and factory date information of all fruits in the products table and insert the select name, category, and factory date into products_new from products;select * from products_new in the new table products_new.

After reading the above, do you have any further understanding of the introduction and use of T-SQL query statements in SQL Server? If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading.

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.