SQL from getting started to basic –03 SQL Server Foundation 1 (primary key selection, data insertion, data update)

first, Getting started with SQL statements

1. SQL statements are statements that are specific to the DBMS "talk", and the SQL syntax is recognized by different DBMS.

2. The string in the SQL statement is enclosed in single quotation marks.

3. SQL statements are insensitive to the case of SQL keywords and are case-sensitive for string values.

4. Create TABLE, delete table not only can be done by hand, can also execute SQL statement completion, in automation deployment, data import with many, create TABLE T_person (Id int not null,name nvarchar (), age int NULL), Drop Table T_person1

5. Simple INSERT statement:

INSERT into T_person (id,name,age) VALUES (1, ' Jim ', 20)

6. SQL is mainly divided into two categories: DDL (data definition Statement) and DML (data manipulation language). Create table, Drop table, alter (modify) table, etc. belong to Ddl,select, Insert, Update, delete, etc. belong to DML.

second, the primary key selection

1. Two commonly used primary key data types in SQL Server: Int (or bigint) + identity column (also known as autogrow field); uniqueidentifier (also known as GUID, UUID)

2. Using an identity column to implement field self-increment avoids problems such as concurrency, and does not allow developers to control their own growth. Fields with an identity column do not specify the value of the primary key when you insert it. Set the IS Identity column of the field to Yes, and a table can have only one identity column.

3. The GUID algorithm is an efficient algorithm that produces a unique identity, which is calculated using the network card Mac, address, nanosecond time, chip ID code, and so on, so that each generated GUID will never be duplicated, whether it is the same computer or a different computer. The function newid () that generates the GUID in SQL Server. NET in the method that generates the GUID: Guid.NewGuid (), which returns the GUID type.

(1) GUID generator

(2) function newid () for generating GUIDs in SQL Server

(3). How to generate GUIDs in net: Guid.NewGuid (), Return is GUID type

4. int self-increment field advantages: Small footprint, no developer intervention, easy to read; disadvantage: low efficiency; data import and export is inconvenient.

5. The advantages of GUID: High efficiency, convenient data import and export, the disadvantage occupies a large space, difficult to read.

6. The industry mainstream tends to use GUIDs.

third, data insertion

1. The INSERT statement can save the column name after the token name, but is not recommended.

2. If the values of some fields in the inserted row are indeterminate, then insert does not specify those columns.

(1)

(2)

3. You can set a default value for the field, which is automatically generated if the default value of the GUID type primary key is set to NEWID (). (rarely used)

iv. update of data

1. Updating a column: Update T_person Set age=10

2. Update a column: Update T_person Set age=10,name= ' Tom '

3. Update part of the data: Update T_person Set age=10 where name= ' Tom ', with the where statement to update only the row Name is ' Tom ', note that SQL is equal to the judgment with a single "=" instead of "= =".

4. Complex logic judgments can also be used in the where

Update T_person Set age=10 where name= ' Tom ' or age<25,or equivalent in C # | | (OR).

5. Other logical operators that can be used in where: or, and, not, <, >, >=, <=,! = (or <>), and so on.

SQL from getting started to basic –03 SQL Server Foundation 1 (primary key selection, data insertion, data update)