Database special training Series of the basic supplements

Reason

Study for a period of time every day to find, details study, the daily work to help a lot, to avoid the previous tread of various pits;

But for how these pits are formed, digging for a while will not work.

Thinking, found that the usual learning as the study of how to pits;

Systematic learning, is, from the cross-sectional view of the pit, the time span to see the formation of this pit and fill it, it can be said, is to upgrade our shovels;

That sentence how to say, have a hammer in hand, see what is a nail;

So we start with the basics;

Have written "Must know Will" the book, long time No, now need to pick up something again

The most basic part

A few questions to consider:

• What is a database?
• What is SQL?
• What is MySQL?

This is in fact the entire database the most basic place, but always let the big God can be stuck under the shell;

In fact, simple understanding is good:

• A database is a warehouse of data, where data is stored (software). ）；
• SQL, structured Query language for communication with the database;
• MySQL is a DBMS

is not what all understand, and what do not understand, that is right;

Go on

• The database is used for storing data, storing the kind of data that can have logical relations, in the database, all data is structured description;
• SQL, is a language, simple but powerful statement, to communicate with the database, to achieve a variety of database operations, is to give the database the way of command;
• MySQL is a DBMS,DBMS is a database management system, usually we say the database;

Pretty much, that's a little more specific, some other concepts:

• Column: A field, an attribute of the data;
• Line: a particular piece of data;
• Table: = column + row; a structured list of data;
• MySQL is a client + server-based, database operations actually occur in the server, transparent to the client; The client can be a tool provided by MySQL (Command-line tool), which can be in various languages: Php,java, etc. (note the understanding of the concept here)
• Use of the show command:

SHOW DATABASES;SHOW TABLES;SHOW STATUS;## 显示服务器状态SHOW COLUMNS FORM table_name;SHOW CREATE database;SHOW CREATE table;## 显示创建语句SHOW WARNINGS;SHOW ERRORS;SHOW GRANTS;## 显示授权用户

SELECT

Select is the database, the most commonly used operations, we focus on this, the other related to the words of specific supplement;

DISTINCT

Distinct is not to find duplicates of the line;

It is important to note that, for multiple columns, it is required that each is not the same line, on a single no problem, a number of problems, reference:

2. Acting on multiple columns
Example 2.1
Select DISTINCT name, ID from A
The following results are performed:

In fact, it is the same way that access and SQL Server support it, depending on the name and ID two fields.

Example 2.2
Select distinct Xing, Ming from B
Returns the following results:

The returned result is two lines, indicating that distinct is not the Xing and Ming two columns "string concatenation" after the heavy, but respectively for the Xing and Ming columns.

To specify multiple column sorting

Sorting in accordance with the prescribed order;

For example, according to the Age,gender two column sorting, will be in accordance with the age first platoon, and then the same old, followed by gender to row;

In operator

The in operator functions almost the same as or, but we recommend using the in

• more intuitive;
• Easy to manage;
• In general faster than or list execution (note condition)
• More suitable for sub-query cases

Wildcard characters

Under normal circumstances, must not put in the starting position, particularly slow!!!

GROUP by etc.

For aggregation type, be sure to add group by;

Use having to filter group by;

This brings up the order of select:

SELECT id,COUNT(count),nameFROM test_tableWHERE id > 100GROUP BY nameHAVING COUNT(count) > 2ORDER BY id DESCLIMIT 100

Join

Self-junction = multiple conditional queries
Natural coupling, external coupling what a good halo, plan to stay in the pit

Where you can open a pit.

• Regular expressions in SQL (regular expressions should also be reviewed ...) ）
• junction of SQL
• Combination Query
• Full-Text Search
• View
• Stored Procedures
• Cursor
• Trigger
• Transaction processing
• Globalization and Localization
• Security management
• Maintenance
• Performance optimization

Each one can pull a whole lot. Let's have a good fight for one months.

Database special training Series of the basic supplements