Golden Code SQL notes (1)

Page (1-75)

the primary key is preferably a meaningless field for later expansion.
PS: Assuming the tender code as the primary key, after the bid code to fill the wrong time, the related tables need to be changed. If it is a meaningless self-increment field is the primary key for no such reason.

The primary key is best not to be set as a federated primary key, otherwise it reduces efficiency and is not conducive to expansion
PS: The original [Federated primary key can solve the problem that there is no unique primary key in the table, but the Federated primary Key has the following disadvantages:]
1. Low efficiency. When data is added, deleted, searched and updated, the database system must process two fields, which greatly reduces the processing speed of the data.
2. Make the structure design of the database become a cake. The fields that make up the federated primary key are usually fields that have business meaning, which conflict with the practice of using logical primary keys instead of business primary keys, which can easily cause problems with system development and maintenance.
3. Making it difficult to create a foreign key association relationship to this table cannot even create a foreign key association relationship to this table.
4. It is more difficult to develop. Many development tools and frameworks only have good support for a single primary key, and very complex special handling is often required for federated primary keys.
In view of these shortcomings, we should only use the federated primary key for special occasions such as compatibility with legacy systems, and we should use unique primary keys on other occasions.

character type knowledge points:
1. In most databases, fixed-length character type names are char (when you save data with fixed-length character types, because the remainder is filled with spaces, the space that is filled in after the value of the field is read.)
2. Variable-length character types are generally varchar
PS: fixed-length character types and variable-length character types can only store acsii-based characters, which can cause storage problems for programs that use Unicode character sets such as Chinese, Korean, and Japanese. To solve this problem, we can use the internationalized variable-length character type, This type can be used to save a character in two bytes. This will solve the problem of saving Chinese and Korean strings. Variable-length character type names are nvarchar in most databases.
However, if the field does not contain double-byte characters, try not to use internationalized variable-length characters.
3. Fixed-length character types and variable-length character types generally cannot specify overly large lengths, such as exceeding 1024 in length are not allowed. More than this length is recommended to use the Big character type field

SQL execution Order
The WHERE statement precedes the group BY statement, and SQL calculates the where statement before grouping.
Having a statement after the group BY statement; SQL calculates the having statement after grouping

For grouping, select and group by columns must match. This rule is an exception when the SELECT statement contains aggregate functions.

Insert statement
Insert into table name (field, Field 2, Field 3) VALUES (' Value ', ' value 1 ', ' Value 2 ')
PS: If you omit a field, it will be inserted in the order of the fields in the definition table. It is recommended that you do not use the ignore notation. Ignoring the corresponding fields and values is easy and error-prone.

SELECT * FROM people– all
Select Name,age from people– partial column
Select Max (age) from people– Max
Select min (age) from people– min
Select AVG (age) from people– average
Select SUM (age) from people– summation
Select count (age) from people– statistics Record count

SELECT * from T_employee ORDER by Fage asc– sort ASC Ascending default ascending can omit
SELECT * from T_employee ORDER by Fage desc– Descending
SELECT * from T_employee ORDER by Fage Desc,fsalary desc– Multi-Group ordering

The binary operator or and left expression is the field to be matched, and the right expression is a wildcard expression to match.
SELECT * from T_employee where FName like 'Erry ' – wildcard characters matching single-character ""
SELECT * from T_employee where FName like '%n_ ' – wildcard characters for multi-character matching are "%"

———— –S
SELECT * from T_employee where FName like ' [sj]% ' – Set match matches the first character as s or J length unlimited string
SELECT * from T_tmployee where FName like ' ^[sj]% ' – The above expression is reversed i.e. an unlimited length string that does not begin with S or J equals the following expression
SELECT * from T_tmployee where not (FName like ' s% ') and not (FName like ' j% ') wildcard filtering is a very powerful feature, but when using wildcard filtering, the database system will scan the entire table, so Yes
It's very slow. Therefore, do not use wildcard filtering too much. You should avoid using wildcard filtering when you use other methods to achieve results
———— –E

SELECT * from T_tmployee where FName is null– null value do not use when FName = NULL to determine this notation is wrong!
SELECT * from T_tmployee where FName are not null– to determine values that are NOT NULL

– the antisense operator ' = ' > ' < ' equals greater than less than is possible by '! ' to take anti '! = ' '!> '!< ' not equal to not greater than not less than
SELECT * FROM T_tmployee where Fage! = and Fsalary! = 2000–present Retrieve age not equal to 22 employees who pay not equal to 2000 members
– Not equal to the operation of ' <> '
– ! Operators can run on both MS SQL Server and DB2 databases, and avoid this if you are porting to a different database.
– The synonym operator is able to run on all major databases. But because of carelessness and other reasons it is easy to ' not greater than ' as ' < ', thus forgetting ' no greater than ' is the meaning of ' less than ' and ' equals '. Error prone.
– It is therefore recommended to use the NOT operator. to mean ' non ' means other than ' <> '

– Multi-value testing company to 23,25,28 year-old employee hair Benefits Search Name Age work number
– General our idea is to use or Fage = Fage = or Fage = 28 quantity Once it becomes more difficult to maintain
–sql provides in mode. The Fage in (23,25,28) in statement can only detect multiple discrete values.
– Range value detection queries a range of values such as 23-27 years of age. Use in or Fage >=23 and Fage <=27
– It is recommended to use where between and 27 in SQL equivalent to Fage >=23 and Fage <= 27 and with higher performance.

– With the use of where 1=1, when a database cannot use a query optimization strategy such as an index after using the where 1=1, the database will be forced to scan each data (that is, full table scan) to compare whether this row satisfies the filtering criteria. The database is slow when it is large

Building tables and Case data

Use [Nb]go/****** object: User [Sasa] script date: 06/25/2015 10:40:21 ******/CREATE USER[Sasa] forLOGIN [Sasa] withDEFAULT_SCHEMA=[DBO]GO/****** object: StoredProcedure [dbo]. [Sp_select] Script Date: ./ -/ - Ten: +: +******/SETAnsi_nulls onGOSETQuoted_identifier onGOCreateProc [dbo]. [Sp_select] @ONamevarchar( -) as  Declare@StrVarchar( +), @dbnamevarchar( +)Set@dbname =db_name ()Set@Str =' Select * from '[Email protected]+'. dbo. '[Email protected]Exec(@Str)GO/****** object: StoredProcedure [dbo]. [Sp_syscolumns] Script Date: ./ -/ - Ten: +: +******/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATEPROC [dbo]. [Sp_syscolumns]--ExecSp_syscolumns' Eemployee '@Object NVARCHAR ( +) as/* Function: The item that gets all the columns in an object (mainly for tables) Remark:Create  byDeam L -/4/7*/Begin    SetNocount on    Declare@Name NVARCHAR ( +)Select@Name =isnull (@Name +', ',"') +name fromsyscolumnsWhereid=object_id (@Object) Print @NameSetNocount OFFENDGO/****** object: StoredProcedure [dbo]. [Preface] Script Date: ./ -/ - Ten: +: +******/SETAnsi_nulls onGOSETQuoted_identifier onGO--=============================================--Author: <cxp,,>--Create Date: < the-Ten-8  the: One: About,, >--Description: < Obtain the current procurement process for the OA system, >--=============================================CREATE PROCEDURE[dbo]. Objective asGO/****** object: StoredProcedure [dbo]. [c_createsqlbasetable] Script Date: ./ -/ - Ten: +: +******/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE PROCEDURE[dbo]. [C_createsqlbasetable] asBEGIN--t_person the data sheet for the recording personnel where the primary key field fname is the person's name, Fage is the age, Fremark is the memo information, and--T_DEBT is the debt information. Where the primary key is fnumber for the debt number, the Famount is the amount of indebtedness, Fperson is the debtor's name,--fperson and the T_person in the FName field establish the Foreign Key association relationshipCREATE TABLET_person (FNameVARCHAR( -), FageINT, FremarkVARCHAR( -),Primary KEY(FName))CREATE TABLET_DEBT (FnumberVARCHAR( -), FamountNUMERIC(Ten,2) not NULL, FpersonVARCHAR( -),PRIMARY KEY(Fnumber),FOREIGN KEY(Fperson)REFERENCEST_person (FName))--Inserting sample dataINSERT  intoT_person (Fname,fage,fremark)VALUES(' Tom ', -,' USA ')INSERT  intoT_person (Fname,fage,fremark)VALUES(' Jim ', -,' USA ')INSERT  intoT_person (Fname,fage,fremark)VALUES(' Lili ', A,' China ')INSERT  intoT_person (Fname,fage,fremark)VALUES(' Xiaowang ', -,' China ')INSERT  intoT_person (Fname,fage,fremark)VALUES(' Kimisushi ', -,' Korea ')INSERT  intoT_person (Fage,fname)VALUES( A,' LXF ')INSERT  intoT_personVALUES(' lurenl ', at,' China ')--not recommended, error-proneINSERT  intoT_DEBT (Fnumber,famount,fperson)VALUES(' 1 ', -,' Jim ')INSERT  intoT_DEBT (Fnumber,famount,fperson)VALUES(' 2 ', -,' Jim ')INSERT  intoT_DEBT (Fnumber,famount,fperson)VALUES(' 3 ', -,' Tom ')ENDGO/****** object:Table[dbo]. [T_person] Script Date: ./ -/ - Ten: +: +******/SETAnsi_nulls onGOSETQuoted_identifier onGOSETAnsi_padding onGOCREATE TABLE[dbo]. [T_person] ([FName] [varchar]( -) not NULL, [Fage] [int]NULL, [Fremark] [varchar]( -)NULL,PRIMARY KEYCLUSTERED ([FName]ASC) with(Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = on) on[PRIMARY]) on[PRIMARY]GOSETAnsi_padding OFFGO/****** object:Table[dbo]. [T_debt] Script Date: ./ -/ - Ten: +: +******/SETAnsi_nulls onGOSETQuoted_identifier onGOSETAnsi_padding onGOCREATE TABLE[dbo]. [T_DEBT] ([Fnumber] [varchar]( -) not NULL, [Famount] [Numeric](Ten,2) not NULL, [Fperson] [varchar]( -)NULL,PRIMARY KEYCLUSTERED ([Fnumber]ASC) with(Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = on) on[PRIMARY]) on[PRIMARY]GOSETAnsi_padding OFFGO/****** object: ForeignKey [fk__t_debt__fperson__07020f21] Script Date: ./ -/ - Ten: +: +******/ALTER TABLE[dbo]. [T_DEBT] with CHECK ADD FOREIGN KEY([Fperson])REFERENCES[dbo]. [T_person] ([FName])GO

Golden Code SQL notes (1)