SQL Server Knowledge points

Database Knowledge points

1. Database operations:

Add: INSERT into Table name values (value 1, value 2, value 3)

Delete: Delete column name from table name where
Conditions

Change: Update table Name set = value where
Table name = value

Check: SELECT * FROM username all
Select name from username

2. Aggregation functions:
2.1 SQL aggregate

SUM () Total count
Max () calculates the maximum number
Mix () calculates the minimum number
Count () Total count
AVG () Calculation
Last () Final value

2.2sqlscalar
Len () The length of the text character mid () rounds the specified decimal point from the first four rount () of a byte extraction character now returns the current date and time format character Display LCase () capitalizes the character lowercase ucase ()

3. Filter:

Where:is NULL to determine if NULL
Or: OR
Not: Non-
In (set): Whether in a collection
Between. and >= and <=
><=< = Symbol

4. Multiple Link Tables

Attention:

The condition constraints of the primary key table should be consistent with the condition constraints of the foreign key table

Links within
Inner JOIN table name on condition

External connection:
(left link)
Left JOIN table name on condition
(right link)
Right join table name on condition

All links: Full outer join ();

Grouped queries: GROUP by has
A group query uses aggregate functions to filter the results and then query the criteria before grouping

Sort: Sort the results of a column
Order asc| DESC

Sub-query: Example: SELECT * FROM Customers where ID in (select ID from custmers where salary>4500)
Example 2:select Column_name[column_name] from Table[table2]
WHERE column_name operator
(select Column_name[column_name] from table1[tablee2] [where])

5. Combine queries combine two tables into one table
Union Merge values
UNION ALL to select a duplicate value

6.select Wildcard symbol * Query all
Alias as query new line and rename
Computed field stitching +| |,concat ()

Removal of duplicate records distinct

CREATE table character type: character string Text,varchar,char (size) holds a fixed-length string

Unlcode string nchar (), ntext ()
Binary type bit bitary image
varbinary

Number Type Int,money,float,bigint,tinint

Date Type: Datetime,data

Other data types: xml,cursor

Frequently used field selection: Money type for amount currency selection
bigint type for self-growth
Time type with datetime type
Try not to use the Text,ntext,image type
Try not to use XML type

Constraint: PRIMARY key non-null unique
Non-empty
Only
Primary key self-growth
Foreign key A table can be linked to another table to ensure data integrity
Default constraints for inserting defaults in columns

Update table ALTER add
Query form Show tables
DESC Table Name: Querying the table for more information
Deleting tables Drio table

SQL Server Knowledge points