Golden SQL notes (1) and golden SQL notes

Golden SQL notes (1) and golden SQL notes

Page (1-75)

It is recommended that the primary key be meaningless for future extension.
PS: assume that the bidding documents are encoded as the primary key, and the associated tables must be modified when the bidding documents are incorrectly entered. if it is a meaningless auto-increment field that is the primary key, there is no such reason.

It is best not to set the primary key as the Union primary key; otherwise, the efficiency will be reduced, which is not conducive to expansion.
PS: original article [the Union primary key can solve the problem that the table does not have a unique primary key, but the Union primary key has the following Disadvantages:]
1. Low efficiency. When adding, deleting, searching, and updating data, the database system must process two fields, which greatly reduces the data processing speed.
2. make the structure design of the database messy. fields that constitute the joint primary key are usually fields with business meanings. This conflicts with the practice of "using the logical primary key instead of the Business primary key", which may cause system development and maintenance troubles.
3. It is difficult to create a foreign key Association to point to this table, or even cannot create a foreign key Association to point to this table.
4. Increased development difficulty. Many development tools and frameworks only provide good support for a single primary key. Special processing is often required for federated primary keys.
Considering these disadvantages, we should use the Union primary key only when it is compatible with legacy systems and other special cases. In other cases, we should use the unique primary key.

Character type knowledge point:
1. in most databases, the fixed-length character type is named char (when data is saved using the fixed-length character type, the rest will be filled with spaces, then, when the field value is read, the space filled later will be read.
2. The variable-length character type is generally varchar.
PS: both fixed-length character types and variable-length character types can only store acⅱ-based characters, which may 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, which can be two bytes to save one character. this will solve the problem of saving strings such as Chinese and Korean. in most databases, the variable-length character type is named nvarchar.
However, if the fields do not contain double-byte characters, try not to use internationalized variable-length characters.
3. fixed-length character types and variable-length character types cannot be too large. For example, a field with a length greater than 1024 is not allowed.

SQL Execution Sequence
The WHERE statement is prior to the group by statement; the SQL statement is calculated before the GROUP.
HAVING statement after group by statement; SQL calculates HAVING statement after GROUP

For groups, SELECT and group by columns must match. This rule is an exception when a SELECT statement contains an aggregate function.

Insert statement
Insert into Table Name (field, field 2, Field 3) values ('value', 'value 1', 'value 2 ')
PS: If fields are ignored, they will be inserted according to the field sequence in the defined table. We recommend that you do not use ignore statements. It is not easy to view the corresponding fields and values after the ignore statements, but prone to errors.

Select * from People-all
Select name, age from People-partial Columns
Select max (age) from People-Maximum Value
Select min (age) from People-Minimum value
Select avg (age) from People-average
Select sum (age) from People-sum
Select count (age) from People-count the number of records

Select * from T_Employee order by FAge asc-Sort asc ascending order can be omitted by default.
Select * from T_Employee order by FAge desc-descending order
Select * from T_Employee order by FAge desc, FSalary desc-multi-group sorting

The left expression of the binary operator or and is the field to be matched, and the right expression is the wildcard expression to be matched.
Select * from T_Employee where FName like'Erry '-single-character matching wildcard""
Select * from T_Employee where FName like '% n _'-the wildcard for multi-character matching is "%"

----- S
Select * from T_Employee where FName like '[SJ] %'-the set matches strings whose first character is S or whose J length is not limited.
Select * from T_Tmployee where FName like '^ [SJ] %'-the above expression returns a string that does not contain the length limit starting with S or J, which is equivalent to the following expression
Select * from T_Tmployee where NOT (FName like's % ') and NOT (FName like 'J %') wildcard filtering is very powerful, but when using wildcard filtering, the database system scans the entire table.
The speed is very slow. Therefore, do not use wildcard filtering too much. When using other methods to achieve the effect, avoid using wildcard filtering.
----- E

Select * from T_Tmployee where FName is null-it IS wrong to use FName = NULL to determine the NULL value!
Select * from T_Tmployee where FName is not null-judge a value NOT empty

-The antsense operator '= ''>'' <' is equal to or greater than or equal to the value that can be passed '! 'To retrieve the back '! = ''!> ''! <'Not equal to not greater than not less
Select * from T_Tmployee where FAge! = 22 and FSALARY! = 2000-search for employees who are not equal to 22 and whose salaries are not equal to 2000
-Not equal to the operation score '<>'
-! The operator can only run on ms sqlserver and DB2 databases. to port the operator to other databases, avoid using this method.
-Synonymous operators can be run on all mainstream databases. however, due to carelessness and other reasons, it is easy to express 'not greater than' as '<', and thus forget that 'not greater than' contains the meanings of 'less than' and 'equal. error-prone.
-Therefore, we recommend that you use the NOT operator. To indicate 'non', except for '<> '.

-A multi-value detection company needs to send a welfare search for employees aged and 28 to retrieve the name and age employee ID.
-In general, our idea is to use or Fage = 23 or Fage = 25 or Fage = 28. Once the quantity increases, it is difficult to maintain it.
-SQL provides the in method. The Fage in (, 28) in statement can only detect multiple discrete values.
-The range value is used to check the value of a certain range, for example, 23-27 years old. If In is used, the query is poor or Fage is greater than or equal to 23 and Fage <= 27
-In SQL, we recommend that you use where between 23 and 27, which is equivalent to Fage> = 23 and Fage <= 27 and has higher performance.

-Use where 1 = 1 with caution. After where 1 = 1 is used, the database cannot use index and other query optimization policies. The database will be forced to scan each data entry (that is, full table scan) to compare whether this row meets the filtering conditions. large databases may be slow

Table creation and case data

USE [NB] GO/****** object: User [sasa] script Date: 06/25/2015 10:40:21 ******/create user [sasa] for login [sasa] WITH DEFAULT_SCHEMA = [dbo] GO/****** object: StoredProcedure [dbo]. [SP_Select] script Date: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate Proc [dbo]. [SP_Select] @ OName varchar (100) As Declare @ Str Varchar (1000), @ dbname varchar (40) set @ dbname = db_name () set @ Str = 'select * from' + @ dbname + '. dbo. '+ @ OName Exec (@ Str) GO/***** object: StoredProcedure [dbo]. [sp_syscolumns] script Date: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate proc [dbo]. [sp_syscolumns] -- Exec sp_syscolumns 'eemployee' @ Object NVARCHAR (1000) As/* Function: obtains the Remark of all columns in an Object (mainly for tables: create By Deam L 2013/4/7 */Begin Set nocount on Declare @ Name NVARCHAR (1000) Select @ Name = Isnull (@ Name + ',','') + name From syscolumns Where id = object_id (@ Object) Print @ Name Set nocount offENDGO/***** Object: StoredProcedure [dbo]. [preface] script Date: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ================ =================================-- Author: <CXP,> -- Create date: <09:11:56,> -- Description: <obtain the procurement process currently applied for by the OA system.,> -- ===================================================== ====== create procedure [dbo]. [preface] ASGO/****** object: StoredProcedure [dbo]. [c_CreateSqlBaseTable] script Date: 06/25/2015 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongocreate procedure [dbo]. [c_CreateSqlBaseTable] ASBEGIN -- T_Person indicates that the primary key field FName indicates the person's name, FAge indicates the age, FRemark indicates the remarks, and -- T_Debt indicates the debt information. where the primary key is FNumber as the debt number, FAmount as the debt amount, and FPerson as the debt owner name, -- create table T_person (FName VARCHAR (20), FAge INT, FRemark VARCHAR (20), primary KEY (FName )) create table T_Debt (FNumber VARCHAR (20), FAmount NUMERIC (10, 2) not null, FPerson VARCHAR (20), primary key (FNumber), foreign key (FPerson) REFERENCES T_Person (FName) -- INSERT sample data insert into T_Person (FName, FAge, FRemark) VALUES ('Tom ', 18, 'USA') insert into T_Person (FName, FAge, FRemark) VALUES ('Jim ', 20, 'usa') insert into T_Person (FName, FAge, FRemark) VALUES ('lili', 22, 'China ') insert into T_Person (FName, FAge, FRemark) VALUES ('xiaowang ', 17, 'China') insert into T_Person (FName, FAge, FRemark) VALUES ('kimisushi', 18, 'korea ') insert into T_Person (FAge, FName) VALUES (22, 'lxf') insert into T_Person VALUES ('lurenl', 23, 'China ') -- this statement is not recommended and is prone to errors: insert into T_Debt (FNumber, FAmount, FPerson) VALUES ('1', 300, 'Jim ') insert into T_Debt (FNumber, FAmount, FPerson) VALUES ('2', 300, 'Jim ') insert into T_Debt (FNumber, FAmount, FPerson) VALUES ('3', 100, 'Tom ') ENDGO/****** object: Table [dbo]. [T_Person] script Date: 10:40:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ongocreate table [dbo]. [T_Person] ([FName] [varchar] (20) not null, [FAge] [int] NULL, [FRemark] [varchar] (20) NULL, primary key clustered ([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] GOSET ANSI_PADDING OFFGO/***** object: Table [dbo]. [T_Debt] script Date: 06/25/2015 10:40:21 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ongocreate table [dbo]. [T_Debt] ([FNumber] [varchar] (20) not null, [FAmount] [numeric] (10, 2) not null, [FPerson] [varchar] (20) NULL, primary key clustered ([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] GOSET ANSI_PADDING OFFGO/***** object: ForeignKey [fk1_t _ Debt _ FPerson _ 07020F21] script Date: 06/25/2015 10:40:21 ******/alter table [dbo]. [T_Debt] with check add foreign key ([FPerson]) REFERENCES [dbo]. [T_Person] ([FName]) GO