SQL Server Learning Note (i) basic knowledge of the database, basic operations (detach, offline, shrink, backup, restore, attach), and basic syntax

In the software testing, the database is the necessary knowledge, holiday busy stealing busy, organized a little study notes, and discuss together.

Read Catalogue

Basic knowledge

• History of Database
• Database nouns
• SQL composition

Basic operations

• Log on to database operations
• Database Remote Connection operation
• Database Detach operation
• Database offline, online operations
• Database Shrink Operations
• Database backup, restore operations
• Database Permissions settings

Basic syntax

• Create a database
• Create a table
• Increase
• By deleting
• Change
• Check
• Add constraint
• Data type

Basic knowledge

What is the role of the database in the end? Look, it should be clear.

When an application sends a request operation, it queries the data in the server database and then returns it to the application.

The role of the database is to store data, retrieve data, generate new data, and the corresponding requirements are: unity, security, performance.

The database can store large amounts of data, facilitate the retrieval and access, maintain the consistency, integrity, sharing and security of data information, and generate new useful information through combinatorial analysis.

History of Database

The growth of all things, the database used today is not an overnight, but also slowly developed. Can be divided into four stages:

① embryonic stage-file system, just use disk file to store data

② Primary Stage-first generation database access, which is a network model and a hierarchical model database

③ Intermediate Stage-second generation database SQL Server, Mysql, relational database and structured Query language

④ advanced Stage-Next-generation database Oracle, "Relational-object" type database

The most popular databases today are Oracle, SQL Server, DB2

Oracle is the relational-to-object database, which is characterized by free products, service charges

SQL Server is a relational database, multi-version, good usability

DB2 supports multiple operating systems, multiple types of hardware and devices

Database nouns

DBA-----Database Administrators

DB-------Database

dbms--Database Management System

System database: Master, model, tempdb, msdb, cannot be changed arbitrarily

Data files are. mdf or. ndf

Log file is. ldf

The default path for data is: C:\Program Files\Microsoft SQL Server\mssql.1\mssql\data

SQL makes up DML (data manipulation language)

--querying, inserting, deleting, and modifying data in the database;

--select, INSERT, UPDATE, delete, etc.;

DCL (Data Control Language)

--To control access permission, access rights, etc.;

--grant, REVOKE, etc.;

DDL (data definition language)

--Used to build databases, database objects, and define their columns

--create table, DROP table, etc.

TCL (transaction control statement)

Variable description, Process Control, function function

--defining variables, judgments, branching, looping structures, etc.

--date function, mathematical function, character function, system function, etc.

Database Basic Operations Login Database

After the installation is successful, you will see the following folder in the Start menu:

Click on SQL Server Management Studio to see the following login screen:

Database Remote connection Settings

First step, open the Perimeter configurator settings

The second step, the corresponding setting

The third step, the connection operation

Detach operations for a database

Separation refers to the fact that the database is online by default, we can not do any copy deletion of database files, and so on, if the database is separated, the data files may be copied, cut, deleted and so on. Generally want to directly back up data files, the first separation of the database, and then copy the data file to another place, and then attach the data file back to it.

The operation is as follows:

Detach the database First:

Complete the above operation, the database will be separated successfully, and then you can do the corresponding replication operations.

Separation can also be implemented using code, as follows

sp_detach_db Demo

Then attach the detached database to the work panel:

Attach can also be implemented using code, as follows:

sp_attach_db Demo

Offline, online operation of the database

Offline and online is a relative operation, the first to be offline to perform online operations, and separation is different, the offline database just cannot be accessed, but also in the work panel, but the detached database, there is no working panel. When you want to use the database again, the offline database only needs to be online, and the detached database needs to be attached.

Offline operations are as follows:

Offline can also be implemented with code as follows:

ALTER DATABASE demo set offline

The online operation is as follows:

Online can also be implemented with code as follows:

ALTER DATABASE demo SET online

Database Shrink Operations

Shrinking is shrinking the database and the memory of the log

Operation is as follows

Shrink Database operations:

Shrink log file operations:

You can also use the following command to shrink a file:

DBCC shrinkfile (' database name ',  targetsize);            /*  */DBCC shrinkfile (' database name _log ',  targetsize);        /* */

It is important to note that:

Targetsize: The unit is in megabytes and must be an integer, and DBCC Shrinkfile tries to shrink the file to the specified size.

DBCC Shrinkfile does not shrink the file to a size less than the actual space used, for example, allocate space is 10M, actual space is 6M, and when the targetsize is 1 o'clock, the file shrinks to 6 m, and the file is not shrunk to 1M.

To shrink a database, use the following command:

DBCC shrinkdatabase (database name, percent)

The percentage here: "The maximum free space in the file after shrinking", the value range "is greater than or equal to 0, less than 100%", the actual use is set to 0.

Backup, restore operations for the database

The backup operation is to ensure the integrity of the data and avoid abnormal operations, resulting in data irreparable operation, the personal feel is very very necessary.

The database backup is divided into the following types:

full backup : Backs up all selected folders and does not rely on the archive properties of the files to determine which files to back up. (During the backup process, any existing tags are cleared and each file is marked as backed up, in other words, the archive attribute is cleared). A full backup is also called a full backup.

differential backup : A differential backup is for full backups: All files that have changed since the last full backup was backed up. (During a differential backup, only those selected files and folders that are marked are backed up.) It does not clear the tag, i.e. it is not marked as a backed up file after backup, in other words, the archive attribute is not cleared.

Transaction Log Backups : Full database backups and last differential backups (if any) that were performed before a particular transaction log backup. Any transaction log backups that are performed after a full database backup or a differential backup that was performed before a specific transaction log backup, if you restored a differential backup. If you set the recovery mode to "simple", you will not be able to use the "transaction log" backup. SQL Server 2000 and SQL Server 2005: Create transaction log backups, you must use the full recovery or bulk-logged recovery model.

The backup operation is as follows, a full backup is selected here:

Restore DATABASE Operations

Permission settings

The permission setting is to set the login name and user name action.

The difference between a login name and a user name:

1. The login name refers to the program software that can log in to the database using the new login name and password, but cannot open or expand the database created by the user himself;

2. The username is a new viewable user for the database created by the user on the basis of the login name, but it is still necessary for the user to add usage rights for the additional check and delete operation. The user name can be the same as the login name, or it can be different. A database can have multiple users, and one user can access multiple databases.

Log in with SA as login first to do the following:

Create Login Name:

Log in with the login you just created to use the database created by the SA user and discover that it is not available:

Create users, assign permissions

Once added, you can manipulate the database created by SA.

Basic syntax

Said so much, and then to see how database syntax operation, database four operations, is to delete and change the search. In doing these operations, the first need to have a database, there is a table, so that can be manipulated. Simplify the problem and think of it as your usual archive. Database is the archives, you can store a lot of files, the table is the file in the archive bag.

Create a database

The database and the build table operations can be created manually or by using code creation, which is created manually:

Manually create the database operations as follows:

The good one database has already been created by completing these two steps.

When working with code, you need to do the following and connect:

To create a database using code, implement the following:

Create Database Demouse Demo

Another way to create a database syntax is to specify database file parameters and log file parameters, with the following syntax:

Create  database name on [primary] (  < data file parameters > [, ... n]  [< filegroup parameter >]) [Log on]  (  < log file parameters > [, ... n])

Examples are as follows:

CREATE DATABASE studb   On  PRIMARY  -- The default is the PRIMARY primary filegroup, which can be omitted (name= ' Studb_data ',  -- The logical name of the main data file filename= ' D : \project\studb_data.mdf ',  -- The physical name of the master data filesize =5MB,  -- Primary data file Initial size MAXSIZE=100MB,  -- main data file growth Max filegrowth=15%   -- growth rate of main data file) LOG on (  NAME= ' Studb_log ',  FILENAME= ' D:\project\stuDB_log.ldf ',  SIZE=2mb,  filegrowth=

If you want to create multiple databases or multiple log files at once, enclose the parentheses with a number

Create a table

To create a table manually, proceed as follows:

Save the input table name

Use the code to create the table as follows:

The code is:

CREATE TABLE Demo (Nname nchar (tenint)

Database and tables have been created.

First look at the increase

Insert a piece of data:

Method One: Yasuteru column name in order to give the data value, cannot exchange sequence, data type can not be wrong

INSERT INTO demovalues (' Zhang ', 25, 1)

Method Two:

INSERT INTO demo (Nname,age,sex) VALUES (' san ', 18,0)

Method Three: Exchange order, the value according to the column name on the line

INSERT INTO demo (Nname,sex,age) VALUES (' san ', 1,22)

Method Four: Null is displayed for columns that do not give values

INSERT INTO demo (Nname) VALUES (' li ')

Inserting multiple data: Using select,union refers to the meaning of the connection

' Wang ', 28,0' Wu ', 30,1

Insert a column

Int

Copy the data from the demo table to the Demo1 table:

Insert into Demo1 (nname,nage,asex) Select Nname,age,sexfrom Demo

Precautions

1: It is not possible to insert only half a row or columns of data each time a row is inserted, so whether the inserted data is valid will be checked according to the integrity of the whole line;

2: The data type, precision, and scale of each data value must match the corresponding column;

3: Cannot specify a value for an identity column because its number is automatically increased;

4: If you specify that a column is not allowed to be empty when you design the table, you must insert the data;

5: Inserted data items that require compliance with the requirements of the check constraint

6: Columns with default values, you can use the default (default) keyword instead of the inserted value

Secondly, to delete

Delete a row:

Delete from demowhere nname= ' Li '

To delete a column:

ALTER TABLE Demodrop column sex

To delete an entire table:

Delete from demo--no Where condition is to delete entire table

Empty the entire table:

TRUNCATE TABLE Demo

Note: Delete the entire table operation and clear the entire table operation comparison

Delete is delete all, including table structure

Emptying only clears the data in the table, but the table structure is still there, emptying faster

Deleting a database

New

You can also query whether the database, and then delete operations, the code is as follows:

If exists (SELECT * from sys.databases where name= ' new 'new

Make an If judgment, sys.databases refers to the entire system database, delete the database is the precondition is that the database to be deleted is not used

And look at the changes.

Change a column of data

update Demosetage= ' "where nname= ' Zhang '

Set is the data to be changed

To change multiple columns of data:

update Demosetnname= ' Zhao ',age = ' $ ',sex= ' 0 'where nname= ' Li '

The last is query

Querying all the data in a table, * represents all columns:

SELECT * FROM demo

Query a row of all data in a table:

SELECT * from demowhere nname= ' Li '

To query the data for a column in a row in a table:

Select Agefrom demowhere nname= ' Li '

The query results columns are aliased with as:

Select Age as ' ages ' fromdemowhere nname= ' Li '

Query the result column with = Alias:

Select wname = Nnamefrom Demo

Note: Wname is a new column name, Nname is a column name that already exists in the table

Add constraint

constraint Purpose: To ensure the integrity and validity of data

The constraints are divided into the following types:

1. PRIMARY KEY constraint: unique non-null (feature) primary key Pk_

Only one primary key can exist in a table

Allow two columns to be present as a primary key

2. Default constraint: Default key Dk_

Default to set values when data is not filled

3. Unique constraint: Data is not duplicated, allowing one row of data to be empty unique Key only allows an empty

4. Check constraint: Used to check if the data is filled to meet the requirements check Key

5. Non-null constraint: cannot be null NOT NULL

6. FOREIGN KEY constraint: The foreign key refers to the ID in table A as the primary key, but in table B the ID is not the primary key, and the ID of table A is the ID foreign key of table B.

FOREIGN KEY Syntax:

ALTER TABLE name add constraint fk_ field name --"FK"--note ' associated table name ' and ' Associated field name '

There are three ways to add a constraint, one in the table (added to the column name and not in the column name), after the table is built

method One : Add a constraint to the column name in the build table, with the syntax: Column name data type constraints, the code is as follows:

CREATE TABLE dome5 (SID  SmallInt primary KEY, sname nchar (nulldefault ' Zhangsan ', Saddre nvarchar, sage tinyint unique key,  semailcharcheck (semail like '%@%.% ') , Createtime datetime  default  getdate ())

method Two : The build table does not add a constraint to the column name, the syntax is: Constraint constraint name constraint syntax, the code is as follows:

CREATE TABLE Stdent2 (    int,    sname nchar (nulldefault ' Zhangsan ' ,    saddress nvarchar (+),    sage tinyint,    Char(a),     default getdate (),    constraint Pk_sid primary key (SID),    constraint ck_sage check (sage>10),     '%@%.% '))

It is important to note that the default constraint can only be added after the column name data type, meaning that the default constraint is to be used in method one to add

Method Three: After the completion of the table and then add the constraint, the code is as follows:

ALTER TABLE Stuadd constraint ck_ssex check (ssex=0 or Ssex=1)

Alter is to modify the table structure

Data type

The data types are shown in the following table:

Classification	Notes and Notes	Data type	Description
Binary data types	Storing data that is not character and text	Image	can be used to store images
Text data type	Character data includes any combination of letters, symbols, or numeric characters	Char	Fixed-length non-Unicode character data, high efficiency
		Varchar	Variable-length non-Unicode data for space saving
		Nchar	Fixed-length Unicode data
		Nvarchar	Variable-length Unicode data
		Text	Storing long text information
		Ntext	Store long text with variable length
Date and time	Date and time entered in single quotation marks	Datetime	Date and time
Digital data	This data contains only numbers, including positive, negative, and fractional	Bigint, int, smallint, tinyint	Integer
		float, Real	Digital
Currency data type	Used for decimal currency values	Money
Numeric type	Example: Stuid numeric (18,0), which represents a 18-digit number with a scale of 0, is a floating-point type	Numeric (18,0)
Identity column	Identity (start value, increment amount)	Identity (+)	Auto Increment
Bit data type	Data that represents yes/No	Bit	Storing Boolean data types

This article is for the author's opinion only, the author at the temperature of a pot of sake published. Reprint Please specify source: http://www.cnblogs.com/hong-fithing/

SQL Server Learning Note (i) basic knowledge of the database, basic operations (detach, offline, shrink, backup, restore, attach), and basic syntax