15-07-15 Database

Database: Divided into hierarchical type, network type, relational type. Now, it's usually the relationship type.

Commonly used: Sqlserver,oracle,db2,access,visual Foxpro,mysql

sql:structured Query Language

SQL Server is a large database software that is used to manage and modify the database

Several elements of SQL Server login:

1. Server type--database engine

2. Server name--native. or (local), remote computer name or IP

3. Authentication--windows authentication (administrator use), SQL Server Authentication (developer)

4. User name: SA-Super admin

5. Password:

How do I turn on SQL Server authentication?

1. In the server properties, modify the authentication mode. SQL Server and Windows authentication.

2. Enable two features of the SA account: Grant access to the database. Enable login.

3. Modify the login password for SA

SQL Server three-tier structure:

1.. mdf in the Data folder of the hard drive. Where the data is stored.

2.SqlServer of service.

3. Database Management interface

File type: the

1. master data file:. MDF has only one

2. Secondary data file:. NDF can have multiple

3. log file:. LDF can have multiple

Data type:

Integer data type (int): int, smallint, bigint, tinyint

Floating-point data type (double): float, real, decimal

Decimal can limit width to precision, written as: decimal[p,s],p for width (worth of total digits), s for precision (digits after decimal)

Boolean (BOOL): bit (includes only 0 or 1, can use the bit data type to represent ture or false)

String type (String): char, varchar, nchar, nvarchar, text (typically using varchar, fixed-length with char)

Date Time Type (datetime): DateTime (1753-1-1,9999-12-31), smalldatetime (1900-1-1,2079-6-6)

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
8. Renaming a database
Sp_renamedb ' Newmydb ', ' MyDB '

6. Create a table
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 the table
ALTER TABLE Login Add money float--add
ALTER TABLE Login Drop column money--delete
ALTER TABLE Login ALTER COLUMN money Real--Modify

8. Delete a table
drop table Login

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

Primary KEY (Code,chengwei) Federated primary Key

15-07-15 Database