Create a database

Tansact-sql T-SQL

1. 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 drive.

2. Open database use MyDB use master

3. Modify the data ALTER DATABASE MyDB--Modify MyDB Databases Add File--Add File Files (name= "MyDB2",--Create 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 database information sp_helpdb mydb 8. Renaming the database Sp_renamedb ' newmydb ', ' mydb '

6. Creating Tables CREATE TABLE Login

(UserName varchar PRIMARY KEY,--the 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. )

7. Modify table Alter tables login add Money float--add ALTER TABLE login drop column money--delete ALTER TABLE login ALTER COLUMN Mon EY Real--Modify

8. Delete Table drop tables Login

9. Three major 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

Keyword: PRIMARY key: Primary key identity: self-growing references table name (column name): Foreign key relationship primary KEY (Code,chengwei) Federated primary Key

10, the database table has four constraints:

A, PRIMARY KEY constraint: Do not allow duplication, to represent each data, automatic sorting (equivalent to a social Security number)----PRIMARY key

B, FOREIGN KEY constraint: The row in the right-level table where you want to add a foreign key ——— relationship ———— Add ———— Add a table name ———— Select the table name ———— click Table and column specifications in the right-hand border ————

C, Uniqueness constraint: cannot be repeated when filling; (How do I create a uniqueness constraint with the mouse?) ---1, select the row where you want to constrain the only variable, find the "identity specification" in the column properties below, and change (the identity) option to "yes")

D, check constraint: Find the column to constrain ———— right-———— check constraint ———— add constraint ———— Check constraint ———— (general): Expression ———— fill in an expression

E, DEFAULT constraints:

11. Note:

A, the foreign key of the layer table must connect the primary key of the main table;

b, Main table, layer table: The reference is the primary table (that is, from which table is referenced), the reference is the layer table

C, foreign key reference must be of the same type, such as int type can only reference int type, and cannot reference varchar type

D, when the primary key is set, the display is automatically sorted

Homework:

Design Database: Save order information, merchandise can have multiple, order to have: order number, belongs to which customer (code), the name of the customer, the name of the product, the price of the goods, the total price of goods, the number of goods, commodity code.

Create DATABASE dingdan--Create a database dingdanuse dingdancreate table Kehu--Create the Main table Kehu (code varchar ( -) Primary Key,--customer code, set as primary key Kname varchar ( -),--Customer name) gocreate table Shangpin (Scode varchar ( -) Primary Key,--commodity code, set as primary key sname varchar ( -),--Product Name Sdingdanfloat,--commodity price) gocreate table Dingdan (IdsintPrimary KEY,--self-growing column code varchar ( -),--Order number Kehu varchar ( -) references Kehu (code),--customer code, set as foreign key Shangpin varchar ( -) References Shangpin (Scode),--product Code, set as foreign key Shuliangint,--quantity of goods purchased Zongjiafloat,--Total price of this product)

Create a database