SQL database development knowledge Summary: Basics

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.

1. 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 ).

1. 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: Completely givenProgramThe salesman does not read the data.

1. 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.

1. 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.

1. 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, set "yes" to "yes" for the field "yes". A table can only have one ID column.

(3) guidAlgorithmIt is an efficient algorithm that can generate unique identifiers. It uses the MAC address of the NIC, the time in the nanoseconds, And the chip ID code, so that the guid generated each time will never be repeated, whether on the same computer or different computers, SQL Server generates the guid function newid (),. method to generate a guid in. Net: guid. newguid (). The returned value is of the guid type.

Example: SQL SERVER: Select newid ()

C #CodeMedium: 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.

1. 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)

1. 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 <>.

1. 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.

1. 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 ().

1. Data Summary

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

1. 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

1. 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 %'

1. Null Value Processing

(1) If no value is specified for a column in the database, the value is null. The null value is different from the null value in C #. The Null Value in the database indicates "unknown ", the result of select null + 1 is null because you do not know whether to add 1 or not ".

(2) Select * From t_employee where fname = NULL and select * From t_employee where fname! = NULL. There are no returned results because the Database "does not know ".

(3) SQL uses is null and is not null for null determination.

Select * From t_employee where fname is null

Select * From t_employee where fname is not null

1. Multi-value matching

(1) Select Fage, fnumber, fname from t_employee where Fage in (34,23, 35)

(2) range value:

1) Select * From t_emploee where Fage> = 23 and Fage <= 27

2) Select * From t_employee where Fage between 23 and 27

1. Array grouping

(1) count the number of people of all age groups by age group:

Select Fage, count (*) from t_employee group by Fage

(2) The Group by clause must be placed behind the WHERE clause.

(3) columns that do not appear in the group by clause cannot be placed in the column name list after the SELECT statement (except for Aggregate functions ).

1) error: Select Fage, fsalary from t_employee group by Fage.

2) Correct: Select Fage, AVG (fsalary) from t_employee group by Fage.

1. Having clause

(1) Aggregate functions cannot be used in the WHERE clause. Having must be used and having must be placed after group.

Select Fage, count (*) as count from t_employee group by Fage having count (*)> 1

(2) Note that having cannot use columns for parameter grouping, and having cannot replace where. Having has different functions. Having filters groups.

1. Limit the number of rows in the result set

(1) Select top 5 * From t_employee order by fsalary DESC

(2) (*) retrieve information of three people from the sixth place in descending order of wages

Select top 3 * From t_employee where fnumber not in (select top 5 fnumber from t_employee order by fsalary DESC) order by fsalary DESC.

(3) Simplified Implementation of the row_number function after SQL server2005

1. Remove duplicate data

(1) Select fdepartment from t_employee --> select distinct fdepartment from t_employee

(2) distinct repeats data processing on the entire result set, rather than targeting a column. Therefore, the following statement does not only retain fdepartment for repeated value processing.

Select distinct fdepartment, fsubcompany from t_employee