2017-03-10 T-SQL statement advanced query

T-SQL statement:

To create a database:

1. Click New Query to write code on the page that pops up. Click on an available database, and the page that determines the current action before writing is the interface you want to operate on.

2, the database creation statement create datebase database name (database name can not be used in Chinese characters, cannot start with a number symbol) after the completion of the selection of the current statement select execution (want to execute a code, the code is selected separately).

Database Comments /* What to annotate */- content to comment

Delete Library:drop datebase database name

To create a table:

1, find yourself a database that you want to add / Modify table operations, or use a statement to find the use database name

2, table creation statement

CREATE table table name

(// here parentheses in English format

Column Name  format conditions (set as needed, no need to fill out) English comma

..... primary Key (Set Primary key column)

..... unique (set unique columns)

..... NOT NULL (cannot be empty)

..... identity(1,1) (self-increment, the first number represents the increment from several beginnings, and the second number represents how much to increase each time)

)

Select the code for the created table and click Execute.

Delete tables:drop table table name

To modify a table structure:

To add a column:

Alter table name add column name data type (data length) (cannot be null, must be removed if one is set to null)

To delete a column:

Alter table table name drop column name

Add Data:

Insert into table name values(add the data sequentially in the form of a table, separated by commas, self-increment can be ignored, string, and time-type are enclosed by single quotes,bit Type input 0/1 without single quotation marks,true/false must be added,decimal type can not be added)

To modify the data:

Update table name set column name = Modify content, (once the entire contents of the modified column can be modified to make multiple table modifications, separated by commas)

Delete data:

Delete from table name (row by line Delete, delete is logged) /Truncate table name (clears all data immediately, no record is made)

Query data:

Select *from table name (asterisk for all columns to query)

Conditional modification:

Update table name set column name = value where column name = Value

Example:update car set time= ' 2015-1-1 ' where code= ' co14 '

in table car , Change the value of code to All lines of c014 to ' 2015-1-1 '

if the value to be modified requires more than one condition to be met, then they are connected with and, and if multiple conditions are met one can be linked with or

Example:update car set time= ' 2015-1-1 ' where code= ' c014 ' and powers = 188

Conditional Delete:

Dalete from table name where column name = value

Example:delete from car where code= ' c013 '

Delete all rows that meet the criteria for code = ' c013 '

Advanced query:

--Conditional query

--Check Column * instead of columns to be viewed, separated by commas between multiple columns

Select Code,name from car

query Table car in code and name column

--Filter Criteria where column name = > = <= < > value and or

--Fuzzy query

Select *from table name where column name like '% value %'

% wildcard character

% only in front of the value indicates that you want to query for a condition that begins with a value, followed by a condition that ends with a value, and before and after the query contains a value.

--Sort Query

SELECT * from table name order by column name ASC Ascending order

Select *from Table name order by column name desc Descending Order

when you sort a particular condition, you only need to insert the query criteria before order by , without linking the two with and

--Go to re-query

Select distinct column name from table name

You can only check one column at a time

--Group Query

Select A column name the column name of the From table name Group by ( the two column names in this sentence are the same column name )

You can only check one column at a time

Select oil from car GROUP by oil

--Sub-query

use a query statement as a value

Example: Select *from Car where brand=(select Brand_Code from brand where brand_name =' BMW 5 Department '

）

looking for column brand=in table car ( looking for column brand_code intable brand ) brand_name =All lines of the ' BMW 5 series ' ).

The code above is stitched together by two lines of code below.

Select Brand _code from brand where brand_name = ' BMW 5 series '

Look for the brand_name in the column brand_code in table brand =' BMW 5 Series ' of all lines

Select *from car where brand= ' b004 '

Find All rows of column brand=' b004 ' in table car

---------- foreign KEY constraints

ALTER TABLE foreign key table name add constraint constraint name foreign key(foreign key field) references primary key table name ( constraint column name )

such as table the Ids in a are primary keys, and to constrain the Aid column in table B , the statement should be:

ALTER TABLE B add constraint a_b_ids foreign key (Aid) references A (IDS)

2017-03-10 T-SQL statement advanced query