Read the full msyql Manual II: database usage

Sorry, I 've been busy reading "Parallel Computing" these two days and finally finished my job today. Now I have time to continue to summarize the "MySQL full manual".

The following section is for database users: Use. The most important part in this book is here. Even though we have already studied database, we can directly go into the development process without looking at it here, but I don't know much about many of its nuances, as a result, Zookeeper cannot be found during development, so we recommend that you check this part. I think this part is quite cool, because most of the SQL statements have been learned, and at the same time, I can understand the details in it. It's really 10 rows, and the check box turns over 200 pages.

Here, I will briefly outline what I think is interesting. Everyone's views are different. If you are interested, you can find the book by yourself.

Chapter 2 SQL Basics
This part is basically not particularly important. The history of SQL is a bit touched. It originated from sequel1 and is part of a research project system/R of IBM, later, as the query language of Oracle commercial databases, it formed a de facto standard. I don't know when it giants such as IBM and Oracle will be available in China, so that Chinese people will feel angry.
There are two small points to note: When you insert a table, you can easily customize the insert sequence; distinct can go out to repeat records

Chapter 2 Mysql Data Types
I strongly recommend this part. I used to explore it randomly. I used to build databases under phpMyAdmin. I don't know why many types of databases are like this. I am not quite sure about the following. Read this chapter.
1. specify the length of char, fill in the missing part, and specify the maximum length of varchar. The missing part is omitted.
2. the unsigned modifier specifies that only the positive value is saved for a field, which can increase the field storage range.
3. The zerofill modifier specifies that 0 can be used to fill the output value.
4. Float () indicates that the displayed value will not exceed 5 digits, with two digits following the decimal point
5. the char type is case-insensitive. If the Query Needs to be case-insensitive, add the keyword binary.
6. text is a case-insensitive blob type.
7. datetime has delimiters while timestamp does not.
8. The Enum type is similar to single choice, and the set type is similar to multiple choice.

Chapter 2 MySQL Operators
This chapter is not very important. You can use phpMyAdmin's query function to guess seven or eight times. It is hard to understand that it is null. If the logic comparison is null, the entire logical expression is null. For example, select 2 = NULL (here = is the = in C), and the value is null, unless the operator <=> (null is safe)
The other is string matching. _ matches one character and % matches one or more characters.

Chapter 2 MySQL Functions
I can see that MySQL is powerful. I used to process MySQL at the application layer. I didn't expect it to be able to process it at the data layer. Although it is powerful, I recommend that you do it at the application layer. Why? Improve efficiency and ease of coding. There is only a pure database user. If there is no other way, you can master these commands. In other cases, you can find the manual.
Categories: mathematical functions, Aggregate functions, string functions, date and time functions, encryption functions, control flow functions, formatting functions, type conversion functions, and system information functions.
A little useful is date functions, encryption functions, and system information functions. Developers can take a look at it. It is best for database users to study it carefully.
I am studying MySQL mainly for development, so I will not elaborate on it myself, just to list one interesting thing: maximum and minimum
Select greatest (157, 88,), returns a maximum value
Select least (-, 23), returns the minimum value-2
Select max (AGE) from user
Select min (AGE) from user returns the largest and smallest age in the User table.

Tired, go to bed first, and go to the following directory tomorrow

Chapter 4 use databases and tables
Chapter 4 Use Data
Chapter 2 connection
Chapter 4 subquery
Chapter 4 Transaction Processing

Take notes at below:

Chapter 4 use databases and tables
Create Database: Database Name;
Select Database: Use Database Name;
Delete Database: drop database database name;
Create TableTable-name(Field-definition, field-definition ,......) Type= Table-type;
Example:
Create Table uses (
Id int (4) not null auto_increment;
Fname varchar (50) not null;
Index (fname), primary key (ID)
) Type = InnoDB;
What's interesting is that index and type: index can speed up database search. If a field needs to be searched frequently, you can add an index, and the database will index the field at idle time, it is convenient to query, because CPU resources are occupied during insertion and deletion. Type is the storage format of the database. Currently, MyISAM (optimized compression ratio and speed, platform-independent) and InnoDB (currently the most comprehensive table style) are commonly used. Other formats include isam, heap, berkeleydb, and merge
Modify Table: Alert tableTable-name(Action field-definition, action field-definition ,......);
The preceding action can be used to add, drop, alter, or change
Delete table: Drop Table Name
Obtains information about databases, tables, fields, and indexes.
Show databases;
Show tables;
DescribeTable-name;

Chapter 4 Use Data
Core, but not especially difficult. Just browse
Insert, update, and delete insert, update, delete, and truncate
Note that delete and truncate: delete records, even if they are Delete * fromTable-Name,The original table still exists. If it is cleared and inserted again, the sequence of autoincrement will be remembered and the serial number will continue. The truncate operation deletes the table and then recreates the table. Therefore, it starts from 1 after it is cleared.
Search record: select
Alias: Select Table name as Alias
Sort results: Order
Group the results: group by, having
Use variable: @ variable name: =

Chapter 2 connection
It is still necessary to study it. The tables used previously are very small tables, and too many connections are not used to design the database. If you do a lot of application design, it is very necessary.
Cross join: similar to Cartesian Product
Internal join: both the left and right tables are retained only to meet the conditions.
Left join: Keep all table on the left, and list matching conditions on the right
Right join: Keep all the tables on the right, and list the matching conditions on the left.
Self-join: you are connected to yourself. It is generally used in the classification of directories. You can use another name for the table to repeat tabulation, and then connect the replicas through other connection methods.

Chapter 4 subquery
A subquery is a SELECT query that is attached to another query. You can escape multiple queries from mysql4.1. Although it is useful, it affects the database performance to a large extent. It is recommended that you use MySQL variables or submit them to the upper-layer application logic for implementation. Therefore, this chapter is just a rough review.
The usage is as follows:
In a where or having clause
Used with comparison and logical operators
Used with in member Testing
Used with the exists Boolean Test
Use in a from clause
Used with connection
Used with update and delete queries

Chapter 4 Transaction Processing
Transaction processing is to integrate a series of MySQL statements into a single transaction unit for processing, and make this unit have the ability to execute automatically. This is a new feature in MySQL. It only supports InnoDB and bdb databases. MyISAM can be simulated by locking. In large database systems (as well as the database tutorials we have learned), Database Support for transaction processing requires many considerations. Each transaction must meet the four attributes of acid, atomicity, consistency, isolation, and durability. Transactions may degrade the performance of database resources because when multiple users access the same database, a large amount of resources are required to isolate transactions. Therefore, starting from the performance perspective, MySQL does not properly support transactions. Therefore, you only need to understand this layer. Roll back the operation or hand it over to the database administrator (the backup work of the database administrator will be discussed in section 3)