T-SQL-simple syntax

SQL Server is the first database that I learned. It has a special sentiment for SQL Server. The following describes the simple Syntax of SQL Server, which is suitable for beginners.

1. create database

Create database My_FrirstCreate -- create a database gouse My_FrirstCreate -- connect to the database go

2. create table

Create table dbo. Students -- create a table (data type, whether it is NULL) (StudentID int primary key not null, Name varchar (25) not null, Scores int null) go

3. insert data

Insert dbo. students (StudentID, Name, Scores) -- insert data values (100204201, 'zhang san', 50) goinsert dbo. students values (100204202, 'Li si', null) goinsert into table1 -- insert data to the table using insert and select: select ID, Name, Date from table2where Name = "Zhang San"; go

4. Use select and into to create a new table

Select {Column name} -- use select and into to create the new table into new table name from old table;

5. update and delete data update and delete

Update dbo. Students -- update Data set Scores = 70 where StudentID = 100204202 godelete from Students where Name = 'zhangsan'

 

6. Change the attribute of a field.

Alter table Produce. Product -- alter column Name char (50) not null

7. Data Type Conversion

Print cast ('1970-12-12 'as datetime) -- cast type conversion print convert (datetime, getdate () -- convert type conversion

8. like query syntax

-- Retrieve the select t whose name starts with 'hl. productKey, t. modelNamefrom dbo. dimProduct twhere t. modelName like 'hl % '; -- retrieves information that ends with 'hl'. select t. productKey, t. modelNamefrom dbo. dimProduct twhere t. modelName like '% hl'; -- retrieves information similar to 'hl '. select t. productKey, t. modelNamefrom dbo. dimProduct twhere t. modelName like '% hl % ';

9. Conditional query syntax

-- Each color has multiple products: select COUNT (*) from dbo. dimProduct; select * from dbo. dimProduct where Color = 'black'; select count (*) from dbo. dimProduct where Color = 'black'; -- group: select color from dbo. dimProduct; select color, COUNT (*) from dbo. dimProductgroup by Color; -- product database: select color, COUNT (*) from dbo. dimProductgroup by Colorhaving count (*)> = 50; select * from dbo. dimProductorder by Color asc; select color, COUNT (*) from dbo. dimProductgroup by Colorhaving count (*)> = 50 order by COUNT (*) asc; select color, COUNT (*) from dbo. dimProductgroup by Colorhaving count (*)> = 50 order by COUNT (*) desc; -- in the product Library: select color for products with the same color Quantity greater than 5, COUNT (*) from dbo. dimProductwhere YEAR (StartDate) = 1998 group by Colorhaving count (*)> = 50 order by COUNT (*) desc; select color, count (*) from dbo. dimProduct twhere YEAR (t. startDate)> 1998 group by colorhaving COUNT (*)> 50 order by COUNT (*) desc;

 

10. join syntax

Select m. loginID as ManagerLoginID, e. * -- left join from HumanResources. employee eleft join HumanResources. employee mon m. employeeID = e. managerIDselect m. loginID as ManagerLoginID, e. * -- right join from HumanResources. employee eright join HumanResources. employee mon m. employeeID = e. managerID

 

This article only briefly introduces the T-SQL syntax. The complex syntax will be explained in the following article...