Sort database statements

1. Create a database

Create Database Name

On

(

Name = n' logical name ',

Filename = n' path \ filename ',

Size = 1 MB,

Filegrowth = 1 MB

)

Log On

(

Name = n' logical name_log ',

Filename = n' path \ file name _ log. ldf ',

Siez = 10 MB,

Filegrowth = 10%

)

Ii. Create a framework

Create schema [ouyubin] authorization [DBO]

3. Create a table based on the schema

Create Table jktest. tbl1

(

Id int identity (1, 1) primary key not null,

Qq varchar (15) not null,

PWD varchar (20) not null

);

4. modify a table

1. Set the primary key

Alter table jktest. tbl1

Add constraint pk_tb1_id primary key (ID)

2. Add a unique constraint

Add constraint uq _ TABLE name_field name unique (field)

3. Add default Constraints

Add constraint DF _ TABLE name_field name default (value) for Field

4. Add check Constraints

Add constraint CK _ TABLE name_field name check (stuage> = 0 and stuage <= 150 or stuage is null expression)

5. Add foreign key constraints

Add constraint FK _ foreign key table _ primary key table _ field foreign key (field in the foreign key table) References primary key table name (field in the primary key table)

6. Delete:

Delete constraint: alter table Table Name drop constraint name

Delete column: alter table Table Name drop column name

7. Add:

Add a column: alter table table name Add column name Type

8. Modify the Data Type:

Alter table table name alter column name Type

5. Use System View SYS. objects to view all database objects.

Select * From SYS. objects where type = 'U'

6. Complete query statement writing format

Note that the branch can write SQL statements, so that you do not need to directly comment it out somewhere. If you do not need to query a field, comment it before the field instead of deleting it.

Select top number percent distinct

, Field

, Constant

, Aggregate Functions

From

Data Source

Where

Condition

Group

Field

Having

Condition

Order

Field;

VII,

1. Match: _ any character; % any number of characters; [_] _ willing to be any character, in [] represents _, [] can also represent the range, for example, [A-Z]

2. Range: Between and; in (hash, hash)

3. Group by: only the data is displayed for each group. Select can only be followed by fields grouped with you.

4. Having clause: Select stname form table name group by stname having count (stname)> 3

Having is used for grouping and filtering

5. Sort the result set by order

VIII. Aggregate functions

1. AVG average

2. sum

3. Count count

4. max value

5. Min

9. Window Function

Select *, AVG (score) over () from Table Name