Shulou(Shulou.com)06/01 Report--

1. Data type

Create table nnnn

(

Column_char char (10), / / character

Column_varchar varchar (10), / / variable length characters

Column_text text, / / stores characters greater than 8000

Column_int int, / / Integer, 4B

Column_smallint smallint, / / short integer, 2B

Column_bigint bigint, / / Integer, 8B

Column_tinyint tinyint, / / Integer, 1B

Column_bit bit, / / bits, 1b

Column_decimal decimal (10BL3), / / Precision 10, 3 decimal places

Column_float float, / / floating point, available when precision is not required

Column_datetime datetime, / / date, accurate to 1/1000 seconds

Column_smalltime smalldatetime, / / date, accurate to points

Column_money money, / / currency, 2 4Bs, preceded by integers and followed by decimals

Column_smallmoney smallmoney, currency, 2 2B, preceded by integers and followed by decimals

Column_binary binary, / / binary, maximum length is 8KB, fixed length

Column_varbinary varbinary, / / binary, maximum length is 8KB, variable length

Column_p_w_picpath p_w_picpath, / / surpasses 8KB, and can store pictures, etc.

Column_nchar nchar, / / Unicode data type, stores double-byte characters, up to 4000 characters, fixed length

Column_nvarchar nvarchar, / / Unicode data type, stores double-byte characters, up to 4000 characters, variable length

Column_ntext ntext, / / stores more than 4000 characters

Column_sql_variant sql_variant / / Custom data types

)

Go

Note: the / / above represents comments, but is not recognized in SQL SERVER

two。 Identifier-delimiter

Use the delimiter [] or "" for identifiers that do not meet all the rules

3. Identifier

(1) the first character must be

Letters defined in Unicode Standard 2. 0: axiz, Abacz, alphabetic characters from other languages

Underscore _, at symbol @, numeric symbol #

Note: the identifier at the beginning of @ indicates a local variable or parameter, the identifier at the beginning of a numeric symbol represents a temporary table or procedure, and the identifier at the beginning of a double numeric symbol # indicates a global temporary object.

(2) subsequent characters, letters, decimal numbers, underscores

(3) it cannot be a reserved word of Transact-SQL

(4) embedding spaces or other special characters is not allowed

(5) for identifiers that do not comply with the rules, the user needs to use the delimiter [] or "" in the SQL statement.

4. The full name of an object

Server name. Database name. Owner name. Object name

Server.database.owner_name.object_name

5. Integrity

Entity integrity: entity integrity defines the record as the only entity of a particular table.

Note: each row is unique to this table and cannot be repeated with other rows

Domain integrity: domain integrity refers to the input validity of a given field.

Note: the range of values is determined by CHECK constraints, FOREIGN KEY constraints, DEFAULT definitions, and NOT NULL definitions.

Referential integrity: when entering or deleting records, referential integrity maintains defined relationships between tables, and related schedule records are automatically deleted when master table records are deleted.

User-defined integrity: users can implement the integrity of a specific database through objects such as stored procedures, triggers, etc.

6. Constraint

NOT NULL: non-null constraint

UNIQUE: uniqueness constraint

PRIMARY KEY: primary key constraint

FOREIGN KEY: foreign key constraint

CHECK: checking constraints

7.CHECK constraint

{constant | column_name | function | (subquery)}

[

{operator | AND | OR | NOT}

{constant | column_name | function | (subquery)}

]

Note: constant, literal, numeric or character data; column_name, specified column; function, built-in function; operator, operator.

For example, material code LIKE'[0-9] [0-9] [0-9] [0-9] [0-9]'

Unit price > 0

[material Code] > = 0 and [material Code] = 0 and [material Code] 1992

GO

3. Declare variable

DECLARE

{

@ myint int

@ sdf char (8)

}

GO

Note: two comparison variables @ myint and @ sdf are declared

4. Assign a value to a variable

SET @ myint = 12

SET @ myint = 1, @ sdf = 'dDD'

5. Logical operation

All, all Boolean expressions are true and return true

Any. One is true and returns true.

Like, which returns true if the Operand matches a pattern

In returns true if the Operand is equal to one of the expressions in the list.

And, or, not

6. Non-empty judgment

Where hire_name is not null

7. Turns text in row on or off, and text, ntext, and p_w_picpath strings are stored in / out of the data line (save the data to a separate text or image page, where the text line is a pointer to the data)

Sp_tableoption TableName, 'text in row',' ON'

8.WRITETEXT, UPDATETEXT, and READTEXT for handling large text and image data

9.IF...ELSE structure

IF condition judgment

Process 1

ELSE

Process 2

For example:

IF @ myint==2

SET @ myint=3

ELSE

SET @ myint=4

GO

10.IF EXISTS (structure)

IF [NOT] EXISTS (SELECT statement)

Process 1

ELSE

Process 2

11.BEGIN...END structure

It can treat multiple commands as a whole.

12.WHILE cycle

WHILE judgment expression

{cycle body}

[BREAK]

{cycle body}

[CONTINUE]

Note: BREAK, jump out of the loop; CONTINUE, start the next cycle

13.GOTO statement

Jump to the corresponding label

Label1:

PRINT 'HHA'

GOTO lable1

GO

Note: lable1: create tags

14.CASE statement

CASE field name

WHEN records THEN result values

WHEN records THEN result values

...

ELSE fruit

END

SELECT, GROUP BY and HAVING, ALL, TOP, ORDER BY, ROWCOUNT, DISTINCT, UNION, WHERE, CASE, INSETR JOIN...ON...

SELECT

SELECT *

FROM TABLE_NAME

TOP

SELECT [TOP n [PERCENT] column_name [,... n]]

FROM tablename

Note: TOP n returns the first n rows of data

TOP n PERCENT returns n percent of the data.

N cannot be replaced by expressions or variables

ROWCOUNT

SET ROWCOUNT 3

SELECT *

FROM material inventory record

GO

SET ROWCOUNT 0

SET ROWCOUNT 3 means to set the number of rows returned as 3

SET ROWCOUNT 0 means to restore ROWCOUNT

DISTINCT

Name of SELECT DISTINCT material

Basic information of FROM materials

GO

Note: DISTINCT is used to remove duplicate rows from the result set of the SELECT statement.

WHERE

Conditions

BETWEEN...AND structure

Field name or expression [NOT] BETWEEN expression 1 AND expression 2

IN

Expression or field name [NOT] IN (list or subquery)

LIKE

String expression [NOT] LIKE can have a wildcard string expression

[ESCAPE string expression]

Note: ESCAPE indicates that it is allowed to search for wildcards in strings instead of using E as wildcards.

Note: for fuzzy queries, the wildcards%, _, [] and [^] used in conjunction with LIKE must be combined with LIKE to make sense, otherwise they are ordinary characters

%: represents any arbitrary character

_: represents 1 arbitrary character

[]: indicates that it can be any character listed in square brackets

[^]: indicates any character not listed in square brackets

EXISTS

The EXISTS keyword is used to specify a subquery that detects the existence of rows. That is, if the result set found by the subquery specified by the EXISTS keyword is not empty, the SELECT query of the principal is executed, otherwise the returned result set is empty.

SELECT *

FROM TABLE_NAME

WHERE EXISTS (SELECT * FROM TAB WHERE NAME='1')

GO

Set the display name of the query field

1. Use the AS keyword

two。 Use the "=" sign

3. Give the name directly.

For example:

SELECT emp_id 'employee number', fname+''+'lname' 'name'

FROM employee

GO

For example:

SELECT emp_id AS 'employee number', fname+''+'lname' AS 'name'

FROM employee

GO

For example:

SELECT 'employee number' = emp_id, 'name' = fname+''+'lname'

FROM employee

GO

Note: as a field name, Chinese characters may not use'', but as a value, you must use''.

Statistical function

SUM (), AVG (), MAX (), MIN (), COUNT ()

Summation, average, maximum, minimum, number of records

GROUP BY and HAVING

[GROUP BY expression]

[HAVING expression]

Note: GROUP BY is used to group data, and HAVING is used to set conditions for grouped data.

SELECT *

FROM A

WHERE price is NOT NULL

GROUP BY type

HAVING COUNT (*) > 3

GO

ALL

Used in the GROUP BY clause, but ALL is meaningful only when the WHERE clause is included in the SELECT statement

SELECT *

FROM B

Number of WHERE > 10

GROUP BY ALL unit

Note: using the ALL keyword, the query results will include all groups generated by the GROUP BY clause, even if some groups do not have rows that meet the search criteria.

ORDER BY

SELECT *

FROM C

ORDER BY price DESC

Note: there is no limit to the number of fields in the clause. ASC indicates ascending order, DESC indicates descending order, and null value is regarded as the lowest value.

Multi-table query

SELECT pub.pub_id, title.id

FROM pub, title

UNION

UNION can combine the query results of multiple SELECT statements together

The result set using UNION must have the same field structure, the number of fields must be equal, and the data types of the responding fields must be compatible.

CASE 、 INNER JOIN...ON... Expression.

INSERT

INSERT [INTO] table name or attempt [field list] values values list

INSERT INTO material inventory record (item number, item name) VALUES ('LP12',' screw')

UPDATE

UPDATE table name or view

SET {Field name = {expression | default | null}}

[FROM {} [,...]]

DELETE

DELETE table or view

FROM other tables

WHERE expression

View

CREATE VIEW [database_name.] [owner.] view_name [(column [,... n])]

[WITH [,... n]]

AS

Select_statement

[WITH CHECK OPTION]

:: = {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}

Modify the view

ALTER

VIEW [database_name.] [owner.] view_name [(column [,... n])]

[WITH [,... n]]

AS

Select_statement

[WITH CHECK OPTION]

:: = {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.