Go: How to learn SQL (Part one: SQL Basics)

Transferred from: Http://blog.163.com/[email protected]/blog/static/285720652010950712271/

1. Why Learn SQL

Since the formation of human society, the operation of society is constantly producing and using all kinds of information (literature, archives, information, data, etc.); In today's so-called information age, the generation and use of information has reached unprecedented breadth and depth due to the role of computers and the Internet. How to control and use good information is (and will always be) an important area of the IT industry.
In the past few decades, relational databases have dominated this area, and SQL, based on relational theory, has become the established standard in the database domain.

The current data storage domain can be called a three-point world:
A. Storage of small amounts of data:
Custom data files or universal data files (stand-alone databases) that access data through custom interfaces or generic APIs. If you need a standalone software or a small dynamic website that stores data.
B. Storage of large amounts of data with high conformance requirements:
relational database management System (RDBMS). such as a variety of traditional information systems (ERP, CRM, HRMS, MIS, etc.).
C. Storage of large amounts of data with high concurrency requirements:
NoSQL database System. such as the Web2.0 website of the background data system.

Above, "Hujiahuwei" to borrow the importance of data storage to explain the great significance of learning SQL. But it is important to have a clear understanding of what SQL is good at and what it is not good at doing.


2. Learn the SQL Reference

Recently compiled the database book ABC article, the database related reference materials made a rough classification.

For beginners, you can combine the database system concept (5th edition) and the introductory technical Manual for a DBMS platform to find or conceive a small need, build a database, create several tables and views, and practice writing queries and modifying statements. Basic theory and technical practice can promote each other.


3. Several sets of basic concepts

3.1. Stand-alone database and server-level database

Single-machine databases (such as SQLite, access, and so on) are database engines that are applied to a single computer, often without network connectivity, for small applications, and when a program is deployed, it is generally only necessary to have data files attached. Sometimes also called a desktop database.
Server-level databases (such as Oracle, DB2, SQL Server, MySQL, PostgreSQL, etc.) are DBMS with network connectivity and can be used as a separate database server for large-scale information systems; When you deploy a program, you need to specifically install the appropriate DBMS. Even the architecture of the database server should be designed separately. This kind of database is the focus of our discussion.

3.2. Servers (server) vs. clients (client)

A database server is a service program running on a host (host) (or cluster) that maintains one or more databases and responds to SQL statements submitted by the database client over a network connection.
A database client is an application that sends query requests to a database server, possibly a GUI management interface or command-line application for a DBMS, or a Web server on the front-end. The database client and database server may be on the same host, but in many cases it is located on a different host and is accessed over the LAN.

For example, for SQL Server, a server instance (Instance) is a database server that can have multiple server instances installed on one host, while Query Analyzer or SSMS, SQLCMD, and IIS that connect to the database server are database clients.

For example, you are backing up/restoring/Attaching a database in SSMs, or executing a command program through xp_cmdshell, which is the file of the host that hosts the database server you are connecting to, not the one on which you run the SSMS database client.

A host is sometimes referred to as a single (operating system) server, and the database server and Web server are application servers running on top of the host. They are all called servers, so don't confuse them.

An example architecture for a typical SQL Server-based Web site system is as follows:
User browser (web Client) <----> IIS (Web server/db client) <----> SQL server (DB server)

3.3. Database (DB) and database management System (DBMS)

The term "database" has been misused and may be used to refer to a data system (such as a Chinese mobile number database) that may be used to refer to a data storage technology (such as relational databases and NoSQL databases) and possibly to a DBMS (as people often say SQL Server is a database software). This confusion has already formed, I am afraid it is difficult to change (as described above). We can only judge the specific meaning according to the context.
In the narrowest sense of technology, a database is a library that resides on a database server instance, whereas a DBMS refers to such software as SQL Server, Oracle, and so on. Beginners should pay attention to the differences between these concepts. Forum often see such a post: "Not connected to the database", "Database is not open", there is no context, it can be seen that the questioner conceptual confusion, the answer is also confused.

3.4. SQL and SQL dialect (dialect)

SQL is a relational database query language standard, and SQL dialect is a variety of DBMS on the SQL standard extension, such as the addition of new keywords, query functions, unique data types, supporting the process of control flow statements and so on. SQL Server, for example, T, and Oracle PL/SQL, are common and commonly used.
This is like the difference between the ANSI C standard and the C language implemented by various compilers. However, the differences between different SQL dialects are much larger than the differences between the different C compilers. The differences between SQL dialects must be noted for learning and developing across DBMS.

3.5. Statements, expressions, and assertions

Statement (statement) is a unit in SQL that can be executed separately. such as SELECT * from table, which is a statement that contains the SELECT clause (clause) and the FROM clause. The SQL standard specifies a semicolon as the end of the statement, but in the current T-SQL, the semicolon of the end of the statement is optional.

An expression is a value in SQL (which may be a variable, constant, query field, or computed result) that corresponds to a specific data type. Expressions in SQL are scalar and table-valued expressions, where a table-valued expression is a SELECT statement as a separate statement, or a subquery as part of a statement. For example, 0, col + 2, DATEADD (second, S, GETDATE ()) are all (scalar) expressions.

In particular, a case in SQL is a caption expression, not a conditional statement. For example, you can use an expression in a case, but you cannot use a statement; The result of a case is a scalar value of a particular data type; A case can be used in a select, GROUP by, or ORDER BY clause, but not if else.

Assertions (predicate) are the result of comparisons in SQL, that is, true values, which can be understood as Boolean expressions, because there is no BOOL data type in SQL, so the assertion is specifically distinguished from the expression. For example, 1 is a scalar expression, while 1 = 1 is an assertion, the latter can be used where, on, have, check, and so on, but the former can not. Because of the existence of NULL, assertions in SQL are three-valued logic, namely True/false/unknown, as described in the following "null and three-valued logic".

The calculation and comparison of two words are used more than once in the following paragraphs. Expressions and expressions are evaluated, the result is a new expression, the expression is compared with an expression, the result is an assertion, the assertion and assertion can be logically run (and/or/not), and the result is a new assertion. Notice the difference.


4. SQL differs from General programming language where

4.1. SQL operations are data

SQL is the query language of a database, and thus can have a persistent effect on system data. In general programming, an error usually results in the crash or bug of the program, which can be modified and re-debugged, while in SQL, a careless result can cause damage and loss of system data. Often a novice executes SQL, inadvertently omitting the WHERE clause in the DELETE or UPDATE statement, which is often a big hassle.

Therefore, to learn and use SQL, you should start with two habits:
1. Be careful. When executing the SQL statement, check carefully to see what you are doing.
2. Timely backup, and consider the system's meta-data version control. Prepare a regret pill for occasional accidents.

Common SQL keywords that can cause devastating effects: DELETE, UPDATE, DROP, TRUNCATE TABLE.

4.2. SQL is a collection-based, descriptive language

SQL is good at assembling operations, not loops. In the so-called descriptive language, you just need to tell SQL what needs to be done, not how.

Beginners of SQL, thinking often inevitably with the traces of programming language, solve problems often do not consider the cycle. When learning SQL programming, whenever you want to use a loop, ask yourself: Does this question have to be solved with loops? In fact, in most cases, the answer to this question is no.

In this book, the occurrence of an if in the SQL code is reduced by a few points (for example, 1), and a while is reduced by many points (for example, 10 or-50, not exaggerated). You can try to rate your stored procedures in such a way.


5. Three subsets of SQL

SQL can be functionally divided into three subsets:

1. DML (Data manipulation Language):
Data manipulation language is the language of querying and modifying (adding, deleting, changing) the data.
Include statement: Select+insert/delete/update
Objects used: DB User

2. DDL (Data Definition Language):
A data definition language, which is a language that defines fields (data types) and relationships (tables) and other database objects.
Include statement: Create/drop/alter
Objects used: DB designer/developer

3. DCL (Data Control Language):
Data Control Language: a language that controls access to data.
Include statement: Grant/deny/revoke
Working with objects: DBA

Additional notes:
-Strictly speaking, DML contains only statements that modify the data (Insert/delete/update), but the SELECT statement is closely related to DML and is similar in form, so it is usually put together. can be collectively referred to as DML, and can also be called QUERY+DML.
-The above three subsets do not contain a backup/restore statement.

Trackback:http://topic.csdn.net/u/20100826/18/08132ed8-a383-46ea-b08f-6794702ff236.html

Go: How to learn SQL (Part one: SQL Basics)