SQL database development knowledge Summary: Basics

Summary of SQL database development knowledge: Base Article Postedon1. database Overview (1) Disadvantages of saving data in a custom file format. (2) DBMS (DataBaseManagementSystem, database management system) and database, usually speaking of a database may have two meanings: MSSQLServer, Oracle, and other DBMS; a classification of storing a bunch of databases

Summary of SQL database development knowledge: basic article Posted on 1. Database Overview (1) Disadvantages of saving data in a custom file format. (2) DBMS (DataBase Management System) and DataBase, usually speaking of the DataBase may have two meanings: MSSQL Server, Oracle and other DBMS; a classification of storing a bunch of databases

SQL database development knowledge Summary: Basics post on

1. Database Overview

(1) the disadvantage of saving data in a custom file format.

(2) DBMS (DataBase Management System) and DataBase, usually speaking of "DataBase" may have two meanings: MSSQL Server, Oracle, and other DBMS; A category that stores a bunch of databases (CateLog ).

(3) DBMS of different brands has their own characteristics: MYSQL, MSSQLServer, DB2, Oracle, Access, Sybase, etc. For developers, It is similar.

(4) In addition to Access, SQLServerCE, and other file-type databases, most databases require database servers to run. Learning, the database is connected to the local database during development, and the database runs on a separate server during online operation.

Concepts in Databases

(1) CataLog (class) (also called DataBase and TableSpace). Data of different classes should be stored in different databases.

1) facilitates personalized management of each CataLog

2) avoid naming conflicts

3) higher security

(2) Table. Different types of data are placed in different "grids". This region is called "Table ", different tables optimize the space based on different data to facilitate searching.

(3) Column and Field ).

Primary Key)

(1) The primary key is the unique identifier of the Data row. Only columns that do not repeat can be used as the primary key. A table can have no primary key, but it is very difficult to handle it. Therefore, tables with no special reason will set the primary key.

(2) There are two primary key selection policies: Business primary key and logical primary key. A business primary key uses a field with business significance as the primary key, such as an ID card number or a bank account. The logical primary key uses fields that do not have any business significance as the primary key, because it is difficult to ensure that the business primary key will not be repeated (the ID card number is repeated) and will not change (the account is upgraded). Therefore, we recommend that you use the logical primary key.

(3) logical primary key: data that is completely presented to the program and not viewed by the salesman.

SQL Server Management

(1) There are two ways to verify SQL Server: User Name verification and Windows verification. You can use Windows for verification during development.

(2) common field types: bit (optional value: 0, 1), datetime, int, varchar, and nvarchar (which may contain nvarchar for Chinese characters ).

(3) differences between varchar () and nvarchar (). char (n): spaces are used to fill the section where char (n) is less than n. Var: variable.

SQL statements

(1) SQL statements are special statements used to "talk" with DBMS. Different DBMS recognize SQL syntax.

(2) strings in SQL statements are enclosed in single quotes.

(3) SQL statements are case-insensitive. They are SQL keywords, string values, and case-sensitive statements.

(4) Creating and deleting tables not only can be done manually, but can also be done by executing SQL statements. It is used in automated deployment and data import.

Create table T_Person: drop table T_Person

(

ID int not null,

Name nvarchar (50 ),

Age int null

)

(5) execute a simple Insert statement: insert into T_Person (ID, Name, Age) values (1, 'Jim ', 39)

(6) * (familiar): SQL is mainly divided into two types: DDL (Data Definition Language) and DML (data operation language). create table, drop table, and alter table are DDL, select, insert, Update, delete, etc. are DML.

Primary Key Selection

(1) Two common primary key data types in SQL Server: int (or bigint) + identify column (also called automatic growth field); uniqueidentifier (also called Guid ).

(2) Using the ID column to implement field auto-increment can avoid concurrency and other problems. Developers do not need to control automatic growth. When using the field of the ID column, you do not need to specify the primary key value during insert, in the Hong Kong Space, set "yes" to "yes" for the field "yes". A table can only have one ID column.

(3) The Guid algorithm is an efficient algorithm that generates unique identifiers. It uses the nic mac address, Nanosecond-level time, And Chip ID code, in this way, the GUID generated each time will never be repeated. Whether on the same computer or on different computers, the newid () function of the GUID generated in SQL Server (),. method to generate a GUID in. NET: Guid. newGuid (). The returned value is of the GUID type.

Example: SQL Server: select newID ()

C # In the code: Guid id = Guid. NewGuid ();

(4) (*) Advantages of int auto-increment fields: it occupies less space and is easy to read without developer intervention. Disadvantage: low efficiency, which is difficult to import and export data.

(5) (*) Advantages of Guid: High Efficiency and convenient data import and export; disadvantages: large space occupation and difficulty in reading.

Data insertion

(1) The insert statement can omit the column name after the table name, but it is not recommended. Insert into Person2 values ('Lucy ', '38 ')

(2) If the values of some fields in the inserted rows are uncertain, do not specify those columns during the insert operation.

(3) You can set the default value for the field. If the default value of the primary key of the Guid type is set to newid (), it will be automatically generated. Seldom do this: insert into person3 (ID, Name, Age) values (newid (), 'Tom ', 30)

Data Update

(1) update a column: update T_Person set Age = 30

(2) update multiple columns: update T_Person set Age = 30, Name = 'Tom'

(3) update part of the data: update T_Person set Age = 30 where Name = 'Tom '. Use the where statement to update only the rows whose Name is 'Tom, note that SQL is more suitable than single =, rather than =.

(4) complicated logic judgment can also be used in the where statement:

Update T_Person set Age = 30 where Name = 'Tom 'or Age <25. or is equivalent to | (or) in C ).

(5) other logical operators that can be used in where are: or, and, not, <, >,>=, <= ,! = (Or <>.

Data deletion

(1) delete all data in the table: delete from T_Person.

(2) delete only deletes data, and the table is still there, which is different from drop table.

(3) delete can also include the where clause to delete part of the data: delete from T_Person where FAge> 30.

Data Retrieval

(1) simple data retrieval: select * from T_Employee.

(2) retrieve only the required columns: select FName from T_Employee.

(3) column alias: select FNumber as number, FName as name from T_Employee.

(4) You can also retrieve data not associated with any table: select 1 + 1, select newId (), select getDate ().

Data Summary

(1) SQL Aggregate functions: max (maximum), min (minimum), avg (average), sum (sum), count (Quantity ).

Data Sorting

(1) The order by clause is located at the end of the select statement. It allows you to specify to sort by one or more columns. You can also specify to sort by Ascending order (ascending order, ASC) or descending order (descending order, DESC ).

(2) sort by age from large to small. If the age is the same, sort by salary from large to small.

Select * from T_Employee order by FAge Desc, FSalary Desc

(3) The order by clause must be placed after the where clause:

Select * from T_Employee where FAge> 23 order by FAge Desc, FSalary Desc

Wildcard filter (fuzzy match)

(1) Use Like for wildcard filtering.

(2) The wildcard matching a single character is a half-width underscore (_). It matches a single occurrence character, starts with any character, and the rest is "erry ":

Select * from T_Employee where FName like '_ erry'

(3) The wildcard for multi-character matching is a half-width percent sign "%", which matches any character that appears at any number of times (zero or multiple), and "K %" matches to start with K, A string of any length. Retrieve the information of an employee whose name contains "n:

Select * from T_Employee where FName like '% n %'

Null Value Processing