SQL Server T-SQL statements

First, the basic method of statement operation

1, check the execution.

2, the method of annotation '--'.

Ii. Types of data

Integer: Int,bigint,smallint

Fractional: float,decimal (length, precision)

Character: char (n), varchar (n)

text--large text.

Logic: Bit 0,1 (true/false)

Binary type: binary 8000,image

Date: DateTime (1753-1-1~9999,12,31), smalldatetime (1900.1.1--2079.6.6)

The role of data types: Build tables, SQL programming

Three. Creation of the database

1. Create a database

Create DATABASE MyDB
On primary
(
Name=mydb,
Filename= "D:\MyDB.MDF"
),--Create a master data file called MyDB, placed in the D drive.
(
NAME=MYDB1,
Filename= "E:\MyDB1.NDF"
),--Create a secondary data file called MyDB1, placed in the E-disk.
Log on
(
Name= "Mydb_log",
Filename= "E:\MyDB.Log"
)--Create the MyDB log file and place it on the E disk.

2. Open the Database
Use MyDB
Use master

3. Modify the data
ALTER DATABASE MyDB--Modify MyDB databases
Add file--adding file files
(
Name= "MyDB2",--Create a secondary data file called MyDB1.

Filename= "C:\MYDB2.NDF"--Place the MyDB data file in the C drive.
)

4. Deleting a database

Drop Database MyDB

5. View the database information
sp_helpdb MyDB
6. Renaming a database
Sp_renamedb ' Newmydb ', ' MyDB '

Iii. creation of forms

1. Create a table
CREATE TABLE Login
(
UserName varchar (primary) key,--the primary key.

Primary KEY (Code,chengwei)--Federated primary Key
Password varchar () NOT NULL,--cannot be a null value.
Name varchar (unique)--a unique key is built.
The SEX bit default 1---------Defaults to constraints (default constraints).
Birthday datetime Check (birthday> ' 1900-1-1 ')--Constructs a check constraint.
)

Attention:

Both the primary key and the unique key are unique constraints. But there's a big difference:

1. The 1 or more columns that are primary key must be not NULL,

If this column is set to NULL when the table is built, the column is automatically changed to NOT NULL when primary key is added.

The unique key constraint column can be null, which is the biggest difference between the primary key and the unique key.

2. A table can have only one primary key (single or multiple columns, multi-column primary key called Federated Primary Key), but multiple unique keys.

2. Modify the form
ALTER TABLE Login add column name (money) variable type (float)--add
ALTER TABLE Login Drop column money--delete
ALTER TABLE Login ALTER COLUMN money Real--Modify

3. Delete a table
drop table Login

Iv. three paradigms of database design

First paradigm: (1NF): The atomicity of columns, each column can no longer be split down

Second paradigm: (2NF): For federated primary Keys, some of these columns have a relationship with only one primary key column that violates the second paradigm.

Third paradigm: (3NF): The table cannot have columns that are indirectly associated with the primary key, only columns that have a direct relationship

Key words:
Primary key: Primary key
Identity: Self-growth
References table name (column name): FOREIGN key relationship

Iii. Examples of paradigm application

The first paradigm (1NF): All properties of the relational mode R are required to be indivisible basic data items, which means that each column of a database table is an inseparable basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties.

For example, some database systems need to use the "address" attribute, the "address" attribute should be directly designed as a database table field of the row. However, if the system often accesses the "city" part of the "address" attribute, then it is not to be the "address" attribute to be re-split into provinces, cities, detailed address and other parts of storage, so that in the address of a part of the operation will be very convenient. This design satisfies the first paradigm of the database, as shown in the following table.

User Information table

numbering	Name	Sex	Age	Contact phone	Provinces	City	Detailed Address
1	Zhang Hongxin	Man	26	0378-23459876	Henan	Kaifeng	No. 23rd Xinhualu, Chaoyang District
2	Li Shiping	Woman	32	0751-65432584	Guangzhou	Guangdong	No. 148th, Tianming Road, Baiyun District
3	H	Man	21st	0371-87659852	Henan	Zhengzhou	No. 198th Erqi District University Road
4	Guo Xiaoming	Woman	27	0371-62556789	Henan	Zhengzhou	No. No. 218, Xinzheng bei Jie, Xue Dian

The user information shown in the table above follows the requirements of the first paradigm, which makes it very convenient to classify users using cities, and also improves the performance of the database.

The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). The second paradigm (2NF) first requires that the primary key must first be in the database table. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. Second, it requires that the entity's attributes depend entirely on the primary keyword. The so-called full dependency is the inability to have a property that depends only on the primary key, and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. Using projection decomposition method to decompose a 1NF relationship into a number of 2NF relationships, it can alleviate the problems of insertion anomaly, deletion anomaly, data redundancy and complex modification in the original 1NF relationship to some extent.

For example, to design an order information table, because there may be more than one item in the order, the order number and the product number are used as the federated primary key for the database table, as shown in the following table.

Order Information Form

platform

order number	product number	product name	number	unit	product price
001	1	excavator	1	1200000￥ 
002	2	Impact drill	8		230￥
003	3	forklift	2		980000￥

This creates a problem: The table is the Union primary key with the order number and the product number. In this table, the product name, unit, commodity price and other information is not related to the table's primary key, but only related to the product number. So this violates the design principle of the second paradigm.

And if the Order Information table is split, the product information is separated into another table, it is very perfect. As shown in the following two.

Order Information Form

Order Number	Product number	Quantity
001	1	1
002	2	8
003	3	2

Product Information Sheet

Product number	Product Name	Unit	Commodity price
1	Excavator	Console	1200000￥
2	Impact Drills	A	230￥
3	Forklift	Car	980000￥

This design, to a large extent, reduces the redundancy of the database. If you want to get the product information for an order, use the item number to inquire in the product information sheet.

The third paradigm 3NF is a subset of the second paradigm (2NF), that is, satisfying the third paradigm must satisfy the second paradigm. The third paradigm needs to ensure that each column of data in a data table is directly related to the primary key, and not indirectly, that any non-primary attribute must not be passed dependent on the primary attribute. In short, a relationship is required to not contain non-primary key information that has already been included in other relationships. (The projection decomposition method is often used to solve the problem)

For example, when designing an order data table, the customer number can be used as a foreign key and order table to establish the corresponding relationship. Instead of adding fields to the order form about other customer information (such as name, company, etc.). The design shown in the following two tables is a database table that satisfies the third paradigm.

Order Information Form

order number	order items	owner	clerk	order quantity	customer number
001	excavator	Liu Ming	partner		1
002	percussion drill	li Gang	Hownie	8	2
003	forklifts	Kwok one	ai beautiful	2 cars	1

Customer Information Form

Customer number	Customer Name	Affiliated Companies	Contact information
1	Li Cong	51 Construction	13253661015
2	Liu Xinmin	Self-employed	13285746958

In this way, when the order information is queried, the customer number can be used to refer to the records in the Customer information table, and the data redundancy is reduced without having to enter the contents of the customer information multiple times in the Order Information table.

SQL Server T-SQL statements