Basic concepts and usage of SQL

I have learned SQL and ADO. net over the past two days ......

I. Database Overview:

1. DBMS (databasemanagementsystem, database management system) and database. When it comes to "databases", there may be two meanings: MSSQLServer, Oracle, and other DBMS; a type that stores a bunch of data tables (Catalog)

2. Database composition-Manage Software/services/data files (tables, views ...)

3. DBMS of different brands has their own characteristics: MySQL, MSSQLServer, DB2, Oracle, access, Sybase, etc. Similar for developers

4. The full name of SQL is Structured Query Language (Structured Query Language) SQL <> sqlserver <> MSSQLServer. The most common error.

5. In addition to access, sqlserverce, and other file-type databases, most databases require database servers to run. When learning/developing, the database is connected to the local database, and when going online, the database runs on a separate server.

Ii. Concepts in database development:

1. Catalog (Category) (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 (table): books are all placed on the shelves, bowls are placed in the cupboard, and different types of data are placed in different "grids, this region is called a table ). Different tables optimize the space based on different data to facilitate searching.

3. Concepts of columns and fields

4. Primary (primary key): 1) The primary key is the unique identifier of each data row in a table. Columns with no duplicate values can be used as primary keys. A table does not have a primary key, but it is very difficult to process. Therefore, you must set a primary key for a table without special reasons.

2) There are two primary key selection policies: Business primary key and logical primary key. The business primary key uses fields with business significance as the primary key, such as the ID card number and bank account. The logical primary key uses fields without any business significance as the primary key, which is completely visible to the program, data that business personnel do not read. 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.

Iii. Database Management:

1. You need to install sqlserver2005 or sqlserver2008. To use the sqlserver management tool for development, you must install SQL Server Management studio. You can also use Visual Studio for management.

2. Use the free sqlserverexpress version and the server name of the Express version. \ sqlexpress, which is no different for developers than other versions.

3. Two authentication methods for sqlserver: User Name authentication and Windows authentication. You can use Windows for verification during development.

4. Developers focus on development rather than configuration/backup, which is what DBAs do.

Create databases, tables, and primary keys

5. In sqlserver2008: Edit row 200; In sqlserver2005: Open the table.

6. Common field types: Bit (optional values: 0 and 1), datetime, Int, varchar, and nvarchar (may contain nvarchar for Chinese)

7. nvarchar (50) and nvarchar (max)

8. Differences between varchar, nvarchar, and char (n): spaces are used to fill the section with less than n characters in length. VaR: Variable, variable.

Iv. SQL statements:

1. An SQL statement is a special statement used to "talk" with a DBMS. Different DBMS recognize the SQL syntax.

2. single quotation marks are used for strings in SQL statements.

3. SQL statements are case-insensitive. Insensitive means SQL keywords, string values, or case-sensitive.

4. Creating and deleting tables not only can be done manually, but can also be completed by executing SQL statements. Many of them are used in automated deployment and data import. Create Table t_person (ID int not null, name nvarchar (50), age int null), drop table t_person1

5. Simple insert statements. Insert into t_person (ID, name, age) values (1, 'Jim ', 20)

6. (*) 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, while select, insert, update, and delete are DML.

7. An SQL query example: (conditional query)

The table has three columns a B c, which are implemented using SQL statements: When Column A is greater than Column B, select column A; otherwise, select Column B, if column B is greater than column C, column B is selected; otherwise, column C is selected.

Select (case when A> B then a else B End), (case when B> C then B else C END) from t

 

5. primary key selection: (globally unique identifier ))

1. Two common primary key data types in sqlserver: int (or bigint) + ID column (also called automatic growth field); uniqueidentifier (also called guid and UUID)

2. Use the ID column to implement field auto-increment to avoid concurrency and other problems. do not control the auto-increment by developers. You do not need to specify the value of the primary key when inserting a field that identifies a column. Set the "yes Id column" of the field to "yes". A table can only have one ID column.

3. The GUID algorithm is an efficient algorithm that generates unique identifiers. It is calculated using the NIC Mac, address, Nanosecond-level time, And Chip ID code, this ensures that the guid generated each time will never be repeated, whether on the same computer or different computers. The GUID generated before January 1, 3400 AD is different from any other generated guid. The GUID generation function newid () in SQL Server. The GUID Generation Method in. NET is guid. newguid (), and the returned type is guid.

4. (*) Advantages of int auto-increment fields: small space occupation, no need for developer intervention, easy to read; disadvantages: low efficiency; pain points during data import and export.

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

6. The industry tends to use guid.

6. Add (insert) data: inert into table (COL, col2....) values (data1, data2 ...)

1. The insert statement can omit the column name after the table name, but it is not recommended

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 give the field default value. If the default value of the guid-type primary key is set to newid (), it is automatically generated.

4. primary key:

Insert into person3 (name, age) values ('lily', 38 );

Insert into person4 (ID, name, age) values (newid (), 'Tom ', 30 );

For example, if you understand the following sentence, you will use insert.

1) insert into book (B _title, B _cid, B _content, B _author, B _isdel, B _addtime)

Values ('chuanzhi pods', 5, 'rigorous response', 'response', 0, getdate ())

2) insert into book

Values (1, 'chuanzhi pods', 'rigorous response', 'response', 0, getdate ())

3) insert into book (B _title, B _content, B _author, B _isdel, B _addtime)

Values ')

4) insert into book (B _cid, B _title, B _content, B _author, B _isdel, B _addtime)

Values (2, 'chuanzhi pods', 'rigorous response', 'response', 'delete', getdate ())

5) insert into book (B _cid, B _title, B _content, B _author, B _isdel, B _addtime)

Values (1, 'chuanzhi pods', 'rigorous response', 'response', 0, getdate ())

6) insert into book (B _cid, B _title, B _isdel, B _addtime)

Values (1, 'chuanzhi pods', 0, getdate ())

7) insert into book (B _cid, B _title, B _content, B _author, B _isdel, B _addtime)

Values (1, 'have female ', 'rigorous response', 'response', 0, getdate ())

8) insert into book (B _cid, B _title, B _content, B _author, B _isdel, B _addtime)

Values (1, 'chuanzhi pods', 'rigorous response', 'response', default, getdate ())

 

VII. Update Data: Update table set Col = value, col2 = value2 where...

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. In the WHERE clause, you can use complicated logic to determine whether update t_person set age = 30 Where name = 'Tom 'or age <25, or is equivalent to | (OR) in C)

5. Update person1 set nickname = n'two'

6. Where (age> 20 and age <30) or (age = 80)

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

 

8. delete data: Delete [from] table where Col = Data

1. Delete all data in the table: delete from t_person.

2. Delete only deletes data, and the table is still there. It is different from drop table.

3. Delete can also include a where clause to delete a part of data: delete from t_person where Fage> 20

 

9. Data retrieval: Select Col, col2 [, *] from table where...

1. Execute the code in the remarks to create a test data table.

2. Simple data retrieval: Select * From t_employee

3. Retrieve only required columns: Select fnumber from t_employee, select fname, Fage from t_employee

4. Column alias: Select fnumber as number, fname as name, Fage as age111 from t_employee

5. Use Where to retrieve Qualified Data: Select fname from t_employee where fsalary <5000.

6. You can also retrieve data not associated with any table: select 1 + 1; select newid (); select getdate ();

 

10. Data aggregation (aggregate function)

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

2. The highest salary for employees over 25 years old:

Select max (fsalary) from t_employee where Fage> 25

3. Minimum wage and maximum wage:

Select min (fsalary), max (fsalary) from t_employee

11. 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 (from large to small, DESC ).

2. List of all employees in ascending order of age:

Select * From t_employee order by Fage ASC

3. 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 (multiple sorting conditions)

4. The order by clause should be placed after the WHERE clause: Select * From t_employee where Fage> 23 order by Fage DESC, fsalary DESC

12. wildcard filter:

1. Use like to filter out wildcard characters.

2. A single-character matching wildcard is a half-width underline "_", which matches a single occurrence character.

Eg: 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 occurs at any number of times (zero or multiple. "K %" matches strings starting with "K" and with any length

Eg: Search for employee information whose name contains the letter "N"

Select * From t_employee where fname like '% N % '.

 

 

 

13. null value processing:

1. If no value is specified for a column in the database, the value is null. null in this column and null in C # indicate "unknown" in the database, rather than "NONE.

2. Therefore, the result of select null + 1 is null, because the result of adding 1 is unknown ".

3. Select * From t_employee where fname = NULL; select * From t_employee where fname! = NULL;

There are no returned results because the Database "does not know ".

4. Use is null and is not null in SQL to judge null values:

5. Select * From t_employee where fname is null; select * From t_employee where fname is not null;

14. Data grouping:

1. Grouping by age: Select Fage, count (*) from t_employee group by Fage

2. The group by clause must be placed after the where statement.

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

Error:

Select Fage, fsalary from t_employee group by Fage

Correct:

Select Fage, AVG (fsalary) from t_employee group by Fage

15. Having Statement (query Condition After grouping)

1. You cannot use aggregate functions in where. Having must be used. 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 that are not in the group, and having cannot replace where. Having filters groups.

3. sequence: Where, group by, and having.

16. Limit the number of rows in the result set

1. Select top 5 * From t_employee order by fsalary DESC

2. (*) search for information of three people from the sixth place in descending order of salary:

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. added the simplified implementation of the row_number function after sqlserver2005.

17. distinct: Remove duplicate data

1. Execute the SQL statement in the remarks. Alter and insert are executed separately.

Select fdepartment from t_employee

Select distinct fdepartment from t_employee

 

2. Distinct repeat the data of the entire result set, instead of for each column. Therefore, the following statement does not only retain the fdepartment for repeat processing:

Select distinct fdepartment, fsubcompany

From t_employee

18. Union: Union result set

1. Execute the code in the remarks

2. Simple result set union:

3. Select fnumber, fname, Fage from t_employee Union select fidcardnumber, fname, Fage from t_tempemployee

4. Basic Principle: each result set must have the same number of columns. The columns in each result set must be of the same type.

Select fnumber, fname, Fage, fdepartment from t_employee Union select fidcardnumber, fname, Fage, 'temporary worker, no departments' from t_tempemployee

19th, Union all

Select fname from t_employee Union

Select fname from t_tempemployee

1. Union merges two query result sets and combines completely duplicated data rows into one

Select fname from t_employee

Union all

Select fname from t_tempemployee

2. Because Union performs repeated value scanning, the efficiency is low. If you are not sure to merge duplicate rows, use Union all

Case:

1) query the minimum and maximum age of employees, and query the minimum and maximum age of temporary and formal employees respectively.

Select 'maximum age of formal employees', max (Fage) from t_employee Union all

Select 'minimum formal employees', min (Fage) from t_employee Union all

Select 'maximum temporary worker age', max (Fage) from t_tempemployee Union all

Select 'minimum temporary employee age', min (Fage) from t_tempemployee Union all

2) query the information of each formal employee, including the employee ID and salary, and add the total salary of all employees to the last row.

Select fnumber, fsalary from t_employee Union all select 'payroll total', sum (fsalary) from t_employee

20. Numeric functions:

Execute the code in the remarks:

1. Abs (): returns the absolute value.

2. Ceiling (): round to the maximum integer. 3.33 is rounded to 4, 2.89 is rounded to 3, and-3.61 is rounded to-3. Ceiling → ceiling

3. Floor (): round to the smallest integer. 3.33 is rounded to 3, 2.89 is rounded to 2, and-3.61 is rounded to-4. Floor → floor.

4. Round (): rounding. Round to "number nearest to my radius ". Round → "radius ". Round (3.1425, 2 ).

21. String functions:

1. Len (): calculates the string length.

2. Lower () and Upper (): lower case and upper case

3. ltrim (): removes spaces on the left of the string.

4. rtrim (): remove the spaces on the right of the string.

Ltrim (rtrim ('bb '))

5. substring (string, start_position, length)

The parameter string is the main string, start_position is the starting position of the sub-string in the main string, and length is the maximum length of the sub-string.

Select substring ('abcdef111', 2, 3)

22. Date functions:

1. getdate (): Get the current date and time

2. dateadd (datepart, number, date) to calculate the date after the addition. The date parameter is the date to be calculated, the number parameter is incremental, and the datepart parameter is the unit of measurement. For optional values, see the remarks. Dateadd (day, 3, date) is the date after date3, while dateadd (month,-8, date) is the date before date8 months.

3. datediff (datepart, startdate, enddate): calculate the difference between two dates. Datepart is the unit of measurement. For more information, see dateadd.

Count the number of employees of different service ages:

Select datediff (year, findate, getdate (), count (*) from t_employee group by datediff (year, findate, getdate ())

4. datepart (datepart, date): returns a specific part of a date.

Count the number of employees' employment years:

Select datepart (year, findate), count (*) from t_employee

Group by datepart (year, findate)

23. type conversion functions:

1. Cast (expression as data_type)

2. Convert (data_type, expression)

3. Select fidnumber,

Right (fidnumber, 3) as the last three digits,

Cast (right (fidnumber, 3) as integer) as the last three digits of the integer form,

Cast (right (fidnumber, 3) as integer) + 1 as the last three digits plus 1,

Convert (integer, right (fidnumber, 3)/2 as the last three digits divided by 2

From t_person

Twenty-four: null value processing function:

1. Execute the code in the remarks

2. isnull (expression, value ):

If expression is not empty, expression is returned; otherwise, value is returned.

Select isnull (fname, 'Alias name') as name from

T_employee

25: Case function usage:

1. Single-value judgment, equivalent to switch case

Case expression

When value1 then returnvalue1

When value2 then returnvalue2

When value3 then returnvalue3

Else defaultreturnvalue

End

2. Example:

Select fname,

(Case flevel when 1 then 'vip customer'

When 2 then 'advanced customer'

When 3 then 'common customer'

Else 'customer type error'

End) as flevelname

From t_customer

3. Test Data in remarks

Case

When condition1 then returnvalue1

When condition 2 then returnvalue2

When Condition 3 then returnvalue3

Else defaultreturnvalue

End

Equivalent to if... Else... Else ....

Select fname, fweight,

(Case

When fweight <40 then 'skinny'

When fweight> 50 then 'fat'

Else 'OK'

End) as isnormal

From t_person

26. Index:

1. Full table scan: full table scan is the most efficient way to search for data (select.

2. If there is no directory, You need to flip the Chinese Dictionary page by page. If you have a directory, you only need to query the directory. To improve the search speed, you can add indexes to columns that are frequently searched, which is equivalent to creating directories.

3. Create an index. Right-click the Table Designer and choose "index/Key"> "add"> "columns included in the index.

4. Using indexes can improve query efficiency, but indexes also occupy space. You also need to synchronously update indexes when adding, updating, and deleting data, therefore, the insert, update, and delete operations are reduced. You can only create an index on a field that is frequently searched (where.

5. (*) even if an index is created, it is still possible to scan the entire table, such as like, function, and type conversion.

 

27. Table join:

1. There are two tables: the customer table (t_customers) and the order table (t_orders). The customer table fields are ID, name, and age, and the order table fields are ID, billno, and customerid, the Order table uses mermerid to associate the customer table. For test data, see the remarks.

2. Select O. billno, C. Name, C. Age from t_orders as O

3. Join t_customers as C on O. customerid = C. ID

4. What is the relationship between join and the table connected after on. (Multi-table)

5. The order number, customer name, and customer age of all customers older than 15 years old must be displayed.

6. display orders purchased by customers older than the average age

7. Inner join, left join, and right join

28th: subquery:

1. Using a query statement as a result set for other SQL statements is called a subquery, just like using a common table. Almost all tables that can be used can be replaced by subqueries. Select * from (select * From T2 where Fage <30)

2. Execute the SQL statement in the remarks.

3. Single value as subquery: select 1 as F1, 2, (select Min (fyearpublished) from t_book), (select max (fyearpublished) from t_book) as F4

4. Only subqueries that return only one row and one column of data can be considered as single-value subqueries. The following is incorrect: select 1 as F1, 2, (select fyearpublished from t_book)

5. Select * From t_readerfavorite where fcategoryid = (select FID from t_category where fname = 'Story ')

Example: two special cases of subquery:

If a subquery is a multi-row, single-column subquery, The result set of this subquery is actually a set.

Select * From t_reader where fyear of join in (select fyearpublished from t_book)

Restrict the result set. Returns data from rows 3rd to 5th (row_number cannot be used in the WHERE clause, so the execution result with a row number can be used as a subquery, and the result can be used as a table ):

Select * from (select row_number () over (order by fsalary DESC) as rownum, fnumber, fname, fsalary, Fage from t_employee) as a where. rownum> = 3 and. rownum <= 5

Basic concepts and usage of SQL