Writing in the front

This period of time because the task requires SQL Server 2008 R2 database, so I learned a little, in this record learning process important things.

A simple introduction

SQL

  • Structured Query Language
  • Is a database query and programming language for accessing data and querying, updating, and managing relational database systems

T-SQL

  • Full name: Transact – SQL
  • Is an enhanced version of the standard SQL programming language, which is the primary language used to allow applications to communicate with SQL Server

T-sql is the SQL syntax supported by SQL Server, not software. It’s mainly the new syntax Microsoft has added to database operations

The main components of THE T-SQL language

  • DML (Data Manipulation Language) is used to query, insert, modify, and delete data in a database
SELECT, INSERT, UPDATE, DELETECopy the code
  • DDL (Data Definition Language) is used to create databases, tables, views, indexes, and so on in a database system
CREATE DATABASE, DROP DATABASECopy the code
  • DLC (Data Control Language) is used to control access permissions, permissions, and so on
GRANT, REVOKECopy the code
  • Others (including variable descriptions, internal functions, etc.)

Database correlation

  • Physical storage structure (storage format of database files on disk) — Primary data file: xx.mdf — Secondary data file: xx.ndf — log file: xx.ldf

  • The logical storage structure consists of different database objects, such as tables, views, and indexes, which are used to store specific information and support specific functions

  • The first character must be a letter or _ @ #. The database name cannot be a T-SQL reserved word – Spaces or other special characters are not allowed to be embedded

  • Db classification – System database (LLDB) : Master, Model, MSDB, Resource – User database (db) : database created by users

  • File size: Specifies the maximum or unlimited file size. Compatibility level: Version number: specifies whether the database is read-only. File size: specifies the maximum or unlimited file size. Default flase — Restrict access: MULTI_USER (normal state), SINGLE_USER (state of maintenance operations), RESTRICTED_USER

Basic database operation commands

The following are all operations using SQL statements, ignoring graph operations

  1. Typically, when executing a command, you start by pointing to the object of the operation and adding the batch flag (execute)
USEObject (database name) LLDB: -USE master -- Points to the database master that is currently in use
- USE E_Market -- points to the current database E_MarketComplete example:USE E_Market -- points to the current database E_Market
GO -- Flag of batch processing (this command is required at the end of executing commands)
Copy the code
  1. Create a database (including data files and log files)
USE master
GO
CREATE DATABASEThe database nameON PRIMARY -- Primary file group
-- Master data file
(
	NAME='E_Market_data'.-- Logical file name of the main file
	FILENAME='F:\Project\E_Market_data.mdf'.-- The physical file name of the main file, including the storage location
	SIZE=5MB,	-- Initial size of the main file
	MAXSIZE=100MB, -- Maximum growth of the main file
	FILEGROWTH=15%	-- Master file growth rate
),
First file group (primary file group)

FILEGROUP FG	-- Second file group (secondary data files)
(
	NAME='FG_E_Market_data'.-- Secondary logical file name
	FILENAME='F:\Project\FG_E_Market_data.ndf'.-- The physical file name of the secondary file, including the storage location
	SIZE=3MB,	Initial size of auxiliary files
	MAXSIZE=7MB, -- Maximum growth of secondary files
	FILEGROWTH=0	Auto growth is not enabled for secondary files
)

LOG ON	Log files do not belong to any file groups
(
	NAME='E_Market_log'.-- Logical log file name
	FILENAME='F:\Project\E_Market_log.ldf'.-- Physical log file name, including storage location
	SIZE=5MB,	-- Initial log file size
	FILEGROWTH=0 -- Automatic growth is not enabled
)

GO -- Batch processing flag
Copy the code
> note: language use parentheses * * * * * * * * (), not curly braces {}, after the command used in the process of comma * * * *, * * * * instead of a semicolon; The last sentence of the command does not need a commaCopy the code
  1. Add file groups and data files to an existing database
USE E_Market	-- Points to the database currently in use
ALTER DATABASE E_Market ADD FILEGROUP FG1	Add a file group named FG1 to the database
GO

Add datafile to filegroup FG1
ALTER DATABASE E_Market ADD FILE
(
	NAME='FG1_E_Market_data',
	FILENAME='F:\Project\FG_E_Market_data.ndf'.SIZE=5MB,
	FILEGROWTH=10%)TO FILEGROUP FG1
GO

-- Set FG1 as the default file group
ALTER DATABASE E_Market
MODIFY FILEGROUP FG1 DEFAULT
GO
Copy the code
> After the selected file group is set to the default file group, the added files will belong to the default file groupCopy the code
  1. Deleting a Database
DROP DATABASE E_Market

Additional: -
IF EXISTS (SELECT * FROM sysdatabases WHERE name='E_Market')	Check whether the database you want to delete exists
DROP DATABASE E_Market

-- Use judgment to check whether the database exists. If yes, delete the database. Otherwise, do not delete the database

Copy the code