Database basics-SQL

-- Chapter 1 computer-create a BBS database createdatabase database name onprimary create a database file (name database name _ mdf, filename Path, size initial size, maxsizeunlimited-Indicates unlimited growth, filegrowth15 %-compare by percentage) logon creates transaction log files (nameBBS_ldf, filenamed:

-- Chapter 1 computer -- create a BBS database create database name on primary // create a database file (name database name _ mdf, filename = 'path', size = initial size, maxsize = unlimited-Indicates unlimited growth, filegrowth = 15%-compare by percentage) log on transaction creation log file (name = BBS_ldf, filename = 'd :\

-- Chapter 1 Computer

-- Create a BBS Database
Create database Name
On primary // create a database file
(
Name library name_mdf,
Filename = 'path ',
Size = initial size,
Maxsize = unlimited-Indicates unlimited growth,
Filegrowth = 15%-compare by percentage
)
Log on creates a transaction log file
(
Name = BBS_ldf,
Filename = 'd: \ BBSdatabase \ BBS. ldf ',
Size = 3 mb,
Maxsize = 30 mb,
Filegrowth = 5%
)
-- Delete a database
Drop database Name

-- Chapter 2 computer

-- Create the table structure

Use Database Name
Create table new table name
(
Column name int identity () not null primary key, -- ID column, automatic Growth
Name of the column varchar (16) not null default ('20140901'), -- not empty, default
Column name datetime, -- date is represented by datetime
Column name varchar (13) not null unique check (len (column name) = 15 or len (column name) = 18 ),
Column name varchar (50) not null check (column name Like 'expression' or len (column name) = 11)
Column name varchar (20) not null foreign key references reference primary key table (corresponding column name)
)
-- Create constraints for an existing table
Name of the table in Alter table
Add constraint name check (len (UPassword)> = 6 ),
Constraint CK_id check (UEmail like '% @ % ')

-- Create a primary-foreign key relationship between existing tables

Alter table name
Add constraint name foreign key (sub-table column name) references primary table name (corresponding primary table column name ),
Constraint FK_TUID foreign key (TUID) references BBSUsers (UID)
......

-- Create a database Relationship Diagram

-- Right-click the Data Relationship Diagram, right-click to create a new relationship diagram, and press CTRL to select all tables.

-- Insert data to a table method 1 insert data to a table

Insert into Table Name (column name 1, column name 2, column name 3 ,......)
Values ('value corresponding to column 1 ', 'value corresponding to column 2', 'value corresponding to column 3 ',......)

-- Insert data to a table using select

Insert into Table Name (column name 1, column name 2, column name 3 ,......)
Select 'value corresponding to column 1 ', 'value corresponding to column 2', 'value corresponding to column 3 ',...... Union // Insert the first row of data
Select 'column name 2 value', 'column name 2 value', 'column name 3 value ',...... Union // Insert the last row of data
...... Union
Select 'value corresponding to column N ',...... Remember that the last row has no union

-- Execute update
Update <需要更新的表名> Set <列名=更新值> [Where <更新列名的条件> ] // Update values that can be followed by multiple data Columns

-- Delete: delete data
Delete from <表名> [Where <删除条件> ]

-- Truncae Table: delete data to delete all rows in all tables.
Truncate table name