10-28c# Basics-Database Manager (Base operation)

C # Foundation - database (used to store large amounts of data)

The only way to manipulate database files is SQL Server, a structured query language referred to as SQL.

Analysis Services: analyzing mining data

Reporting Services: Report Server Administration Tools

Intergration Services: Responsible for data extraction, conversion and loading operations.

First, database Foundation - Open, new, start, close and so on;

1. database files

. sql-- Database Writing code, querying files

. dll-- Database program generation file

. mdf-- data files, have and only one, store data, start information, delete is completely deleted

. ldf-- log file, must have at least one, record operation information

. ndf-- Secondary Files

Back up the . mdf and . ldf files when you save the database file, backup them in a timely manner, and once they are lost, they cannot be retrieved;

2. Open the SQL interface:

Interface composition:

Object Explorer

Execute Code Window

Results Display window

3, the database includes the system database (own) and the user database (user-created)

There are 4 operating system databases :master/model/msdb/tempdb

User database NEW: Right-click on "Database"-- Select "New Database", the following interface appears, you can add the name of the database, change the database file storage location.

4, how to find the user database file storage location

Click New File- right - Click on the file-- to view the storage location after the log file and data file;

5, how to control the database startup and shutdown (run CMD)

Once the database is not used, it should be closed in time. The database occupies a large amount of space, even if the database management platform is closed, the database is running in the background, so use cmd to open and close the database, this is the real stop running the database.

start the database in the control Board input net start MSSQLserver

In the Control Panel, enter net stop MSSQLserver to close the database

To pause the database in the control board input net pause MSSQLserver

In the control board input net continue MSSQLserver continue the database

6. Security

Login name -- Right-click, you can rename, new login name;

Server role -- The server has permissions, is the level of logins, different roles have different permissions;

The server role is used to grant server-wide security privileges to the user.

7. Log in to the remote database

1) Server logon change SQL server mixed authentication Mode

2) authentication is divided into Windows authentication and SQL Server authentication (including user name and password, password can not be forgotten)

General logon By default is Windows authentication (that is, the computer default, do not lose password)

If the login remote server must first modify the authentication mode;

How to do this: Right-click on the server name login under Object Explorer and enter the "Properties"---"Security"--Modify the authentication mode.

Such as:

3) Start your account

4) Set or change the password

After the modification, click "File" Connection Object Explorer, modify the authentication, enter the login name and password;

Second, new table, add data, modify data, delete data, query data

1. Create a new database:

2. New Table

3. Add Data

4, query data: New Table Right click "Select before ", query to the data;

5.Delete data: Create new Table Right click "Before editing " , then right click on the small triangle in front of the edit box, select Delete, delete the whole line permanently;

Third, the data type:

1, Integer class shape:

(key memory, commonly used) symbolic shaping: int--data for each int type is stored in 4 bytes, where 1 bits represent the sign of the integer value, and the other 31 bits represent the integer value length and size;

Big shaping: bigint--data per bigint type is 8 bytes

Small shaping: smallint--each smallint type of data is stored in 2 bytes, where 1 bits represent the sign of the integer value, and the other 15 bits represent the value length and size;

2, floating-point type:

Real accurate to 7th decimal place, accounting for 4 bytes

(Key memory, common) float: accurate to 15 decimal, accounting for 8 bytes

(Key memory, Common) decimal: High Precision Decimal

Numeric with decimal, there is no difference in function

2, Binary type:

Binary: defined as binary (n) n means that the length of the data is 1~8000, and the data must be entered with the ox as the binary identifier before the data.

Binary (50): A binary number of 50 bits;

varbinary: Custom binary length data type; varbinary (n);

3. Logic type:

Bit: Byte type, only one byte can be placed, the value is 0 or 1, if the input other value will default to 1, cannot be defined as null.

4. Character Type:

(Key memory, Common) char (10): A string of length 1--8000,10 characters, char (n) n represents the occupied storage space;

Nchar (10): Length 1--4000, character type, with n representing Unicode standard character set, not commonly used;

(Key memory, common) varchar: defined as varchar (n), n range 1~8000;

Nvarchar (50):

Nvarchar (max): Custom size, can change size

5, Date and time type:

Time (7): 24-hour, only for the duration; the larger the value in parentheses, the higher the precision of the seconds.

(Key memory, Common) DateTime: 1753-1-1 00:00:00 to A.D. 9999-12-31 23:59:59

Datetime2 (7): 24-hour date-time

Datetimeofset: DateTime type with time zone

6. Currency type:

Money: The type of the stored number, this type of data is a decimal value with four decimal places, which accounts for 8 bytes, plus a currency symbol before the data to differentiate the country, otherwise the default ¥.

SmallMoney: Small range of storage currencies

7, text graphic type:

Ntext: Storing documents;

(key memory, commonly used) Text: storage documents, the difference between the above is the length of different, commonly used;

Image: Store picture (binary point, not real picture)

8, Sql-variant: Storage of various data types, equivalent to the object in vs.

9. Infrequently used data types:

Geometry: Geometry

Geography: Map

10, Storage hierarchy relationship (table and table relationship): HierarchyID

11, (Key memory, Common) XML: a special writing rules of the programming language, specifically for storing some fixed data;

In Vs2012, there is xml,configuration in config: Storing some configuration information, parameters and so on.

12, Transact-SQL: Database programming language;

Iv. query statements (new query, query data, insert, modify, delete)

1, (select) new query, by specifying the database, query (all, row, column)

Use Firstdata-This is the operation using the specified database

INSERT into Shuiguo values (1, ' Apple ', 3,5)

Select *from Student--query statement, * is to query all

Select name from student--query a column

Select Name,sex from student--query multiple columns, separated by commas

SELECT * from Student where code=2--query a row by condition

Select name from Student where code=2--query a column by condition

Select Name,sex from Student where code=2--query multiple columns by criteria, separated by commas

2, (insert) in the query, you can also insert new data

INSERT into Student values (2, ' John Doe ', ' 1992-12-20 ', ' female ', 165,7.00,null)-This is the format of the INSERT statement

Insert into Student (Code,name,sex) VALUES (3, ' Harry ', ' female ')--if set to NULL, how to insert a new value, by field name, column name order one by one corresponding to insert;

3. (update) Modify the statement

Update Student set birthday= ' 1991-1-1 ' where code=1--modify the statement to modify only the place that you currently want to modify

4. (delete) DELETE statement

Delete from student--remove all

Delete from Student where code=2--deletes a row

5, in the query, by writing code to create a new database, create a new table, insert, modify, delete data

1) Code New database

Create database onedata--Create new databases from code

Go

2) using the new database

Use Onedata

Go

3) New Table

CREATE TABLE Shuiguo

（

code int,

Name varchar (20),

Price Decimal (18,2)

）

Go

4) inserting data into the new table

CREATE TABLE Guoshu
(
Code int,
Name varchar (20),
Price Decimal (18,2)
)
Go
INSERT into Guoshu values (1, ' Apple ', 3.5)
INSERT into Guoshu values (2, ' banana ', 2.5)
INSERT into Guoshu values (3, ' cherry ', 6.5)
INSERT into Guoshu values (5, ' grapes ', 3.4)

10-28c# Basics-Database Manager (Base operation)