Database basics-use SQL statements to operate databases

Database Operations

1. Use the create database Command to create a database
　　
The syntax of create database is as follows:
 
Create Database database_name
[On
{[Primary] (Name = logical_file_name,
Filename = 'OS _ file_name'
[, Size = size]
[, Maxsize = max_size])
} [,... N]
]
[Log on
} (Name = logical_name,
Filename = 'OS _ file_name'
[, Size = size])
} [,... N]
]
[For restore]
　　
Command Format description:
Primary: This option is a keyword used to specify files in the main file group. The primary file group not only contains all the content in the database system table, but also contains all objects not included in the User File Group. A database can only have one master file. By default, when the primary keyword is not specified, the first file in the statement is the main file.
Name: Specifies the logical name of the database. This is the name used in the SQL Server System and the identifier of the database in SQL Server.
Filename: Specifies the name and path of the operating system file where the database is located. The path in OS _file_name must be a folder on the server where SQL server is located. The logical names of the Operating System File and name are one-to-one.
Size: Specifies the size of the database operating system file. You can use either MB or kb to specify the file size. If no unit is specified, the default unit is MB. The minimum file size is 1 MB, that is, the file where the database is located cannot be less than 1 MB. By default, the size of the database data file is 3 MB, and the size of the database log file is 1 MB.
Maxsize: This option is used to specify the maximum size that an operating system file can grow. When you specify the file growth size, you can use either MB or kb. If no unit is specified, the default unit is MB. If you do not specify the maximum size for file growth, there is no limit on the system growth and the entire disk space can be occupied.
Fielgrowth: This option is used to specify the object increment. Of course, this option cannot conflict with the maxsize option. If the data value specified by this option is zero, the file cannot grow. This option can be set to MB, kb, or percentage.
　　
Example:
Create a Cust database. The logical name of the master data file of the database is cust_data, and the operating system file is cust. MDF, which is 15 MB in size and a maximum of 30 mb, increases at a speed of 20%. The logical name of the database's log file is cust_log, and the operating system is cust. LDF, which is 3 MB in size and a maximum of 10 MB, increases at 1 MB.
　　
Create Database cust
On
Primary (name = cust_data,
Filename = 'd:/Cust. MDF ',
Size = 15 MB,
Maxsize = 30 mb,
Filegrowth = 20%)
Log On
(Name = cust_log,
Filename = 'd:/Cust. LDF ',
Size = 3 MB,
Maxsize = 10 MB,
Filegrowth = 1 MB)
　　
2. Increase the database size

(1). Use the alter database command to increase the size of database files
If the size of the configuration file is not automatically increased when the database file is created, you can use the alter database command to increase the size of the file.
　　
Example:
Adjust the cust_data size of the database Cust data file to 50 MB.
Alter database cust
Modify file (name = 'cust _ data', size = 50)
　　
(2) Add a secondary file using Add File
Increase the size of the database by adding secondary data files and log files to the database.
Example:
Alter database cust
Add File
(Name = cust_data2, filename = 'd:/cust2.mdf ',
Size = 5 MB,
Maxsize = 10 MB,
Filegrowth = 10%)
　　
3. Compress databases and data files

(1). Use the DBCC shrinkdatabase command to compress the entire database
Example:
Compress the cust database to 10%
DBCC shrinkdatabase (Cust, 10)
(2) Use the DBCC shrinkfile command to compress a data file in the database
Example:
Compress the cust data file to 5 MB
DBCC shrinkfile (Cust, 5)
　　
4. delete a database

Use the drop command to delete a database
Example:
The Cust database will be deleted
Drop database cust

Table operations

1. Create a table

(1) Use the create table command to create a table
Syntax:
Create Table tabl_name
({
} Column_name as computed_column_expression
}
} [,... N]
)
[On {fiegroup | default}]
[Textimage_on {fiegroup | default}]
　　
Example:
Open the cust database and create a table that contains information about students, including student ID, name, gender, birth date, nationality, contact number, address, and remarks.
Use cust
Create Table students
(
Number int not null,
Name varchar (10) Not null,
Sex char (2) null,
Birthday datetime null,
Hometown varchar (30) null,
Telphone_no varchar (12) null,
Address varchar (30) null,
Others varchar (50) null
)

In this table, number indicates the student code. The data type is int and cannot be blank. name indicates the Student name. The data type is varchar. The length is 10 and cannot be blank; sex indicates the gender of the student. The data type is Char and the length is 2. It can be null. Birthday indicates the birth date of the student. The data type is datetime. It can be null; hometown indicates the student's place of origin, the data type is varchar, the length is 30, allow to be empty; telephone_no indicates the student's contact computer, the data type is varchar, the length is 12, allow to be empty; address indicates the student's address. The data type is varchar. The length is 30 and can be blank. Others indicates the student's remarks. The length is 50 and can be blank.
　　
2. Modify the table structure

Add and delete a new column with T-SQL statements
Syntax:
Alter table table
{
ADO
{[]
| Colun_name as computed_column_expression
| []
} [,... N]
| Drop
{Column
} [,... N]
}
　　
Example: Open the cust database, modify the structure of the table students, and add a new field, with the field name Ying, varchar as the data type, 10 in length, no default value, and a blank value.
Use cust
Alter table students add Ying varchar (10) null
　　
Open the cust database, modify the table students structure, and delete a field named Ying.
Use cust
Alter table students drop column Ying

3. delete a table

Syntax:
Drop table table_name
This statement will delete the table named table_name