Summary of SQL

Summary of SQL Basics

1. The database and database management systems are differentiated by: Databases (DB), Databases Management system (DBMS).
2.Type of DBMS:

1. Hierarchical databases (hierarchical database, HDB)
2. Relational database (relational Db,rdb)
3. Object-oriented database (oriented DB,OODB)
4.XML database (XML db,xmldb)
5. Key-Value Storage System (Key_value store, KVS)
PS: The main summary is the RDBMS. There are several common RDBMS:
. Oracle Corporation
. SQL Server Microsoft Corporation
. DB2 IBM Inc.
. PostgreSQL Open Source
. MySQL ditto

3.RDBMS common system Structure:

The C/S structure (client/server) client makes the request and the server responds.

4. Structure of the table:

What is returned according to the SQL statement must also be the structure of the two-dimensional table. Where rows are also known as records, columns are also called fields. The relational database reads the data in the behavior unit. Unlike tables in Excel, a cell can hold only one data, and there is no action to merge cells.

5.SQL Overview:

Divided into standard SQL and specific SQL. The so-called specific originates from different RDBMS.

6.SQL Statement Classification:

1.DDL (Data definiton Language): Used to create or delete tables and databases consists of three instructions: Create;drop; Alter.
2.DML (Data Manipulation Language): Used to query or change records in a table. directives are: Select;insert; Update;delete.
3.DCL (Data Control Language): Used to confirm or cancel changes to data in the database. directive: COMMIT; ROLLBACK; GRANT; REVOKE.
90% of the statements belong to DML.

7. Basic rules for statement writing:

. End with a semicolon.
. The case of the keyword is not distinguished.
. Constants are written in a fixed manner. String and date constants are enclosed in single quotes, and numbers can be written directly.
. Words need to be separated by half-width spaces or line breaks.

8. Operation of the database:

To import a database directive from a file:
\i C:/<location of file‘s name>.sql;
naming rules: You can use half-width English letters, half-width numbers, underscores, but only with the English alphabet as the beginning.
* * Data type: **1.integer,char,varchar,date.
constraint settings: key-value constraints, such as not null,primary key.
statement syntax: There are three operation statements for tables and databases. Create,drop,alter. Specifically, you can refer to the documentation.

9.select statement:

select <column_name> from <table_name>
where * can represent all columns. Use as to set the alias of the column, use the Chinese character as an alias when using double quotation marks.

The understanding of the SELECT statement: According to my current feeling, is a circular lookup statement. You can restrict and group lookups. The number of cycles is determined by the table and constraint conditions. Therefore, when the query constant, the Changshu loop input the same number of times.

Precede the column of the query with distinct to remove the accented rows from the results. You can also add distinct to multiple columns and write only one.

WHERE clause: Set the constraint with the column name, remembering the column name, identity equivalence. The order of the statement execution should be the following, select the table, then select the set of records that satisfy the criteria based on the where condition, and execute the select sentence on the basis of the collection. The order in which the statements are written is fixed.

How to write comments: single-line comments and multiline comments. A single line is used –; Multiple lines are made with/*. similar to C + + usage.

10. Arithmetic and comparison operators, logical operators:

1.select words can be used in changshu or expression, the definition of expression can refer to C + +, this place refers to the column name of the addition and subtraction operations.
2. The calculated result that contains null is definitely null.
3. The comparison operator is used to set conditions in the WHERE clause. Pay attention to the non-equal writing. <> (This is not an equal sign). The rest is more common.
4. Do not use the comparison operator for NULL. To operate on NULL, you can use a specific operator, such as IS null;
5. There are and,or,not. Very common.
6. Unique three-valued logic: Come out true/false, plus an indeterminate, such as null is indeterminate. Therefore, it is prudent to have NULL in the logical operation. However, you can set a not NULL constraint on the column.

11. Aggregate query:

1. Five aggregate functions: Count,sum,max,min,avg. are common. Count is used to count the number of rows. Sum/max/min/avg can be understood by literal value.
Description of whether NULL is counted in the number of rows: The count(*) instruction contains null, but does not include a null row for a particular column.
Also only the Count function can be used *, the rest cannot. This function can be used in the body of the function distinct (this limit is for the column name), written in the outside to perform the different.

12.GROUP by sentence:

Use this sentence to group, and Null to be a group. The column names used for grouping can be called aggregation keys or grouped columns. The order in which the primary statement is combined. For example, the select * from max where col=‘max‘ group by col; order is from, where, group by, select.

Common errors when using: 1. A column name other than the aggregation key is written in the SELECT clause, count(*) not counted. 2. The alias of the column is used in the GROUP BY clause. 3. The aggregation function is used in the WHERE clause, as mentioned above, where the operand of the WHERE clause is a column name, but the aggregate function is used as a collection.
The group by sentence is used in conjunction with the HAVING clause.
Order by sort, default ASC, you can choose Desc. You can specify more than one sort key. The order by sentence is executed last, so do not use aliases arbitrarily.

13. Insert and delete data:

1.insert Statement Use
2.insert.. Select implements a copy between tables.
3. delete from <table_name> ;
4 Search-Type Delete:
DELETE FROM <TABLE_NAME> WHERE <CONDITION> ;
5. Updating of data:
update <table_name> set <column_name>=<expr>;
6. The search-type update is similar to delete.
7. Multiple-column update statements.
8. Transaction definition: A series of update processing collections executed in the same processing unit. A block of statements similar to a programming language.
Syntax format:
begin transaction ;commit(rollback);
Different RDBMS makes a difference, and you can view the document in detail.
Four characteristics of a transaction: ACID

14. View:

Grammar:
CREATE VIEW <View_name> (列1，..列n) AS <SELECT 字句>;
Related understanding: A mapping relationship, the view is a virtual table, does not store the actual data, the required data is obtained through select words in real time, so it can be dynamic. Agree that you can think of a view as a temporary transit point. When you want to write a very complex command but not a good start, you can use the view as a broker, the last query results saved on the view, and then the next query based on the view.

subquery: A one-time view, lack of functionality as a transit. In fact, it is also a nested statement. When we have a SELECT clause in the FROM clause, it is nested or called a subquery. We know that the from is followed by a table, we cannot write a column name, but the return result of the select sentence is an unnamed table. Similar to anonymous variables in C + +.
scalar Quantum query: (Scalar subquery) is a subquery that returns a single value. The result is only one row. Since the result of its return is only one column, unlike a normal subquery that returns a table that can only be used in a particular place, a scalar subquery may be used where constants or column names can be used.
correlated subqueries: specific.

15. function, predicate, CASE expression:

1. Function classification: arithmetic, string, date, conversion, aggregate function. Refer to the documentation for specific use. Commonly used can be kept in mind.
2. Predicate classification: like，between,in,not in,is null,is not null ,exists specific use slightly.
3.case expression: Similar to a switch statement in C + +.
Grammar:
case when<expr> then<expr>...else <expr> end
More is the judgment of the condition choice.

16. Operation of the set:

1.UNION, go and join two query results. No all option will go heavy.
2.INTERSECT, take the intersection. No all go heavy.
3.EXCEPT, go to complement set.
4. Junction of the table: two internal and external coupling + cross-junction. Check the documentation specifically.

17. Window functions:

In short, it combines grouping and sorting capabilities. For example, use the Rank function to sort by grouping, sorting within each group.

Follow up: The content of <<SQL基础教程[日]>> the blog is summarized. The database used is PostgreSQL, short and easy to learn, but recommended to use the latest version, the book version is too old, there may be a variety of problems. Writing is very basic, but there is a book in the use of the column name and Japanese-related, it may seem more strange. All in all, for beginners to get started. The source code in the book can be downloaded from the Turing community.

Write in the back of the nonsense:

First write about the recent learning to read the sentiment, I am afraid I will not be in time to record this evening may forget. When I read my senior year, I received a word of encouragement: 如果现在不做，那么以后永远都不会做。 This sentence has kept me motivated and I don't like procrastination. So I wrote where I wanted to be. Recently in Reading <<数据结构与算法分析>> a book, it is God book, write better. It suddenly reminds me of the first time I studied data structure, I felt so difficult at the time, I couldn't help it, the list was difficult to understand. I must have had a big head at that time. At that time learned very tired, but still have to bite the bullet to learn again. Then it was a long time without contact. This semester, opened a database course, assembly language. Because the requirements are not high, so learn the power, and bought a data structure of the book. But this time the book will not appear so difficult, but there is a feeling of being on the horizon. At that time I thought of the theory that I saw on the understanding. Learning knowledge is divided into three parts. I will, I feel I will, I will not. The book I will read is certainly in a good mood. But the latter two do not seem so pleasant. But how to learn I will not have something to remain happy? I feel the need to contact, no matter how hard the first time, be sure to bite the bullet to look at, then when you second contact with the mood will be much better. Because knowledge has changed from you do not have to me feel I will, so it might be easier to learn.

April 11, 2015 9:13 PM By Max ；

Summary of SQL