SQL Server Basic Operations

SQL full name is a structured Query language (structured Query Language), which is the standard language for relational database management systems

1, separate the database: the current database file and database engine relationship is disconnected, there is no relationship, so that you can arbitrarily copy, cut
Action: On a database file, right---------click "Delete Link"--OK

2, Offline: Tell the database engine to suspend the operation of the current database, can also be arbitrarily copied, cut

Primary key:

The primary key is the unique identifier of the data row. Duplicate data columns do not appear to be primary keys. A table can have no primary key, but it is very difficult to process, so there are no special reasons for the table to set primary key primary key has two choices: business primary key and logical primary key. Business primary key is the use of business-meaningful field key, such as a social Security number, bank account, etc., the logical primary key is to use a field without any business meaning the key, completely to the program, the business people will not look at the data. The logical primary key is recommended because it is difficult to ensure that the business primary key is not duplicated (the ID is duplicated) and does not change (account number is raised).

Business PRIMARY key: logical, such as ID card, bank card, employee number
Logical PRIMARY key: No actual meaning, only to identify the current column's unique identity in the current data table, can not be manually edited
Primary key identity column: Even if the data rows are deleted, the growth figures are
Primary FOREIGN Key:
1, role: In order to reduce duplication of data, a table of duplicate data extracted, placed separately in another table, in the original table as long as the storage of a point to the extracted data table row

3. Setting constraints

Select a column name in the database right-click Set CHECK constraint:
For example:
The size of classesid (int) does not exceed 10

Expressions only need to write classesid<10

Parentheses are automatically added to the system after saving
([classesid]< (10))

4. Add foreign keys

SQL Basic Statement Daquan:

1, build the database:

Create DatabaseStudb on  Primary  --by default it belongs to the primary filegroup, which can be omitted and written directly on(/*-- specific description of the data file--*/name='Studb',--logical name of the master data fileFileName='D:\stuDB.mdf',--physical name of the master data file    /*The following size, maxsize, filegrowth can be omitted*/size=5MB,--Initial size of master data fileMaxSize=100MB,--maximum number of main data file growthFileGrowth= the%--growth rate of master data files)/*The following logs on log file can be omitted*/Log  on(/*--The specific description of the log file, the meaning of each parameter ibid .*/name='Studb_log', filename='D:\stuDB_log.ldf', size=2MB, FileGrowth=1MB)//abbreviated versionCreate DatabaseDb on(Name='Db', filename='d:\ Download File \sql\db.mdf')

2, build the table:

Create Table table name (    intIdentity(1,1primarykey,-- identity set growth rate, primary key set primary key    nvarchar (not null,    intis notnull) 

3. By deleting the data in the table, the table is still

Delete  from table name

4. Empty the data in the table, reset the data in the table

TRUNCATE  Table Name

If there is a delete that generates a lot of logs (how many records are in the table, how many logs are generated), and if truncate generates a row of logs

5, delete the table directly (with caution), completely delete the table, the table does not exist

Drop  Table Name

Change and delete

1, add the statement:

Insert  into table name (column name)values(corresponding values)insertintovalues('  Admin',123);

Note: When added, the bit field is represented by 0 or 1, and the Time field is quoted in single quotes, but the underlying time format is followed.
The column name can be omitted, but in addition to the identity column, the remaining fields must be assigned in order, in quantity, by
You need to enter two single quotes when inserting a single quote in the data

2. Delete

Delete  from Table name Delete  from where field = value

3. Modify the data:

Update set Field = value

Where: Multiple conditions can be judged later, and multiple conditions are separated by and
and takes precedence over or execution
IS is a null value (NULL) judgment, directly with the = sign is not working, the space can use the = number
Update table name set column name = value, column name = value where condition and condition

4. Check

Select *  from where field = value

SQL Server Basic Operations