SQL Primary Stage notes

1. DataBase Management Stystem (Database management System) abbreviation: DBSM; Although DBSM is not equal to a database, DBSM is often referred to as a database in the industry, so generally the database refers to DBSM.
2. In short, dbsm is what we know: MYSQL, MSSQLServer, DB2, Oracle, Access, Sybase, and so on.
3. sql<>sqlserver<>mssqlserver. /* Note that the "<>" representation here is not equal to/*.
4. The primary key is divided into a business primary key and a logical primary key .

The so-called business primary key: Use the business-meaningful field key.

The so-called logical primary key: Use a field that does not have any business meaning. (Completely for the program to see)

5. The primary key setting requires :

1. Columns that do not duplicate;

2. It is recommended to use logical primary key as primary key;

6. Why use the primary key :

A table can have no primary key, but it is very difficult to handle, so for no special reason, the table is important to set the primary key.

7. What is a foreign key :

For example: I want to stock, may enter different manufacturers of goods, because in the same factory I do not necessarily only into a class of things, so when I record the bill, it is necessary to record the factory address, telephone, etc. for different types of goods and the same manufacturers to record, but also every time to repeat the factory address, etc. In the database we can use a code name to replace some duplicate data, which also reduces the use of storage space, for this code name, it is repeated to write the address of the table in the primary key, and is the main table of foreign keys!

8. Open the connection after Microsoft SQL Server Management Studio, such as:

　　

The name of the server is filled in as follows: The full computer \ Instance name or the. \ instance name, or you can directly enter "(local)" to connect. This makes it possible to connect via local authentication

9. Create a new database:

In the "Object source Manager" After the connection, we can see "database", then right-click on the database, and then select "New Database (N)" In the menu bar that appears, as shown in:

　　

Just fill in the database name on the top! Then click OK to OK!

10. Create a new table:

Expand your new database, right-click on the "table", then the menu bar appears to select "New Table (N)", then you can add the field is (column), and set the data type, related settings I will write in the back.

You can also create a new table with SQL statements:

CREATE Table Table name (whether the field type name is NULL, whether the field type name is NULL, whether the field type name is null ...)

Example: CREATE TABLE preson (ID int not null,name nvarchar (a), age int)

This establishes the following table:

Id	Name	Age

11. Common data types are:

1. String data type
Char
This data type can store 1~8000 fixed-length strings, string lengths are specified at creation time, or char (1) By default, if unspecified. Each character occupies 1byte of storage space.
NChar
This data type can store 1~4000 fixed-length Unicode strings, string lengths are specified at creation time, and default to nchar (1) if unspecified. Each character occupies 2bytes of storage space.
varchar
This data type can store variable long strings with a maximum value of 8,000 characters. The maximum length of a variable-length string is specified at creation time, such as varchar (50), with each character occupying 1byte of storage space.
nvarchar
This data type can store a variable-length Unicode string with a maximum value of 4,000 characters. The maximum length of a variable-length Unicode string is specified at creation time, such as nvarchar (50), with each character occupying 2bytes of storage space.
Text
This data type stores variable-length text with a maximum value of 2,147,483,647 characters, and does not need to specify its initial value, each character occupies 1byte of storage space, and is typically used to store large sections of the article. The text data type is actually a large object data type, by default, this type of data is not stored in the data row, but instead is stored on a separate large object data page. In addition, the text data type cannot be used as a parameter in a function, stored procedure, or trigger.
ntext
The same text data type, except that it stores Unicode variable-length text with a maximum value of 1,073,741,823 characters, with each character occupying 1byte of storage space.
Note: String values must be enclosed in quotation marks regardless of the string data type used, and single quotation marks are recommended.

In general, I'm so different.

The difference between varchar and nvarchar:

Nvarchar can have words like "kanji", "Japanese", etc., but varchar is not allowed.

varchar (10) and char (10)

char (10) No matter your character is not enough 10, I will leave the space for you.

varchar (10) You have a few I give you a few, but the premise is not more than 10.

2. Numeric data types
Bit
This data type stores binary fields with a value of 0 or 1. Consumes 1byte of storage space.
tinyint
This data type stores 0~255 integers and consumes 1byte of storage space.
smallint
This data type stores -32768~32767 integers and consumes 2bytes of storage space.
Int
This data type stores -2147483648~2147483647 integers and consumes 4bytes of storage space.
bigint
This data type stores -9223372036854775808~9223372036854775807 integers and consumes 8bytes of storage space.
Decimal/numeric
These two data types feature the same features, all of which are floating-point values that store a variable precision. However, it is recommended to use decimal because the data it stores is "more descriptive". This data type is determined by two values of decimal (p,s), p is precision, S is scalar, such as decimal (3,2), where the number 2 is a decimal digit, then decimal (3,2) can be used to store floating-point numbers such as 1.28. The storage space that this data type occupies depends on the precision value p. P is 1~9, occupy 5bytes storage space, p is 10~19, occupy 9bytes storage space, p is 20~28, occupy 13bytes storage space, p is 29~38, occupy 17bytes storage space.
Float
This data type stores 1~53 floating-point values of variable precision, with a precision of float (n), n for the mantissa of scientific notation, a negative value of -1.79e+308~-2.23e-308, and a positive number for 2.23e-308~1.79e+308. Its storage space is determined by the precision value, N is 1~24, occupy 4bytes storage space, N is 25~53, occupy 8bytes storage space.
Real
This data type stores negative numbers for -3.40e+38~-1.18e-38 and positive numbers for 1.18e~3.40e+38. Consumes 4bytes of storage space.
SmallMoney
This data type stores the currency value of -214748.3648~214748.3647, which is accurate to 4 digits after the decimal. Consumes 4bytes of storage space.
Money
This data type stores the currency value of -922337203685477.5808~922337203685477.5807, which is accurate to 4 digits after the decimal. Consumes 8bytes of storage space.
3. Date and Time data types
smalldatetime
This data type stores the date from January 1, 1900 to June 6, 2079. Consumes 4btyes of storage space.
Datetime
This data type stores the date from January 1, 1753 to December 31, 9999. Consumes 8bytes of storage space.
4. Binary data types
Binary
This data type stores 1~8000 binary data with a specified length that is the occupied storage space.
varbinary
This data type stores variable-length binary data that can be specified at the time of creation or unspecified.
5. Other data types
Rowversion/timestamp
These two data types feature the same functionality, but Microsoft recommends that, in any case, you specify rowversion rather than timestamp, as rowversion more accurately reflects the true nature of the data type. The timestamp data type is completely unrelated to time, which indicates the relative order in which data modifications in the database occur. Do not use the timestamp column in the key (especially the primary key) because the timestamp value changes every time the row is modified. When the specified data type is rowversion or timestamp, SQL Server automatically generates a new value when updating operations such as inserting or deleting a table, and puts the new value in the appropriate field. This type of data consumes 8bytes of storage space.
uniqueidentifier
This type of data stores a binary value that acts like a globally unique identifier (GUID). GUIDs are primarily used to assign identifiers that must be unique in a network with multiple nodes and multiple computers. Consumes 16bytes of storage space.

12. Settings for the primary key:

　　

Right click on the position in the pop-up menu to select the primary key is OK. When you want to cancel it as the primary key, right click, check "Delete primary key" is OK.

13. Adding and Removing fields:

In 12, the corresponding "column Life", "attribute Type", "Allow null value" in the corresponding fill can be. If you want to make changes after saving, right click on your new table--"Design (G)--" then you can fill out!

I don't know if you noticed. No: The primary key cannot be empty!

14. Populate the table with the data:

Right click on the new table you created--"edit the first 200 lines (E)--" and then you can fill out!

SQL statement: A statement that is dedicated to the DBMS conversation.

Note: The SQL string is enclosed in single quotes;

SQL statement is not case sensitive;

SQL statements

16.

　　　　

　　

　　　　

　　　　　

　　　　　　

SQL Primary Stage notes