"2017-03-10" T-SQL base statement and condition, advanced query

First, T-SQL base statement

1. Creating database: Create DATABASEname (not Chinese, cannot start with a number, cannot start with a symbol)

2. Delete the database:drop databases database name

3. Select database: Use database name

4. Creating tables: Create tabletable name

(

Column name data type,

Column name data type,

Column name data type

Set Primary key column:primary key

Set unique columns:unique

Set non-null: NOT NULL

Set self-increment column:identity ( up to)-counting from 1, 1 per increment

)

5. Delete tables:drop table name

6. Add column:ALTER TABLE name add column name data type

7. Delete columns:ALTER TABLE table name drop column name

8. Add Data:insert into table name values (' String ', ' 2017-1-1 ', ' true/false ', number)

9, modify a column of data:Update table name set column name = value (whole column modification, the modified data are the same)

10, delete the data: Delete from thetable name (row by line Delete, log save record, the increment column sequence number will not be deleted, will be superimposed)/truncate from the table name (empty the contents of all tables, the log will not be remembered Record, self-added column serial number records will also be deleted, use with caution! )

11, query data:Select *from table name (query all data of the table)

12. Set the FOREIGN KEY constraint:

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

Example: If IDs in Table A is a primary key, to constrain the aid column in table B, then the statement should be:

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

Tips

1.--Comment Line

2./* Note content */Comment paragraph

Second, database data conditions, advanced query

1. Modification of conditions

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

Example: Update car set time = ' 2017-1-10 ' where oil =7.4 and powers =188

2. Conditional deletion

Delete from table name where column name = value

Example: Delete from car where code= ' c013 ' or brand = ' b001 '

3. Conditional query

1. Query a row:select *from table name where column name = <= >= < > value multiple conditions with and/or

2. Querying a column:Select column name from table name

See comma-separated examples for multiple columns: Select Code,name from car

4. Fuzzy query

Select *from table name where column name like '% value% ' % denotes wildcard '% value% '--contains this value, ' value% '--starts with this value '% '--ends with this value

5. Sort Query

SELECT * FROM table name order BY column name (this column numeric type) ASC Ascending/desc Descending

Order by must be placed after the conditional statement, without and, indicating a condition query and then sorting

6, go to re-query

Select DISTINCT column name from table name

The queried content is the value after the column is removed from the repeating section

7. Group Query

Select Column name from table name Group BY column name

The effect is the same as the de-query, but the group query can also do other things, but it is not yet learned, and two column names are consistent

8, sub-query

The value of the primary key table is used in the foreign key table as a query statement in the two tables that are normally constrained by foreign keys.

Brand_Code The value of this column corresponds to the value of the brand column in the car table, you can associate two tables with this.

"2017-03-10" T-SQL base statement and condition, advanced query