SQL Server T-SQL statements

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.