Basic knowledge about MySQL Databases

The following articles briefly describe some basic knowledge about MySQL databases. With the wide application of MySQL databases, the related knowledge is also paid more and more attention, the following articles are intended for beginners of MySQL. The following are the main content of this article.

Install

Most major package formats such as RPM, DBE, and TGZ can be obtained on the MySQL site. The client library and Wrapper in various languages can be obtained in separate RPM formats. The RPM format does not require much trouble and requires no initial configuration. In rc3.d, RedHat RPM is used as an example to generate an initial script. Therefore, the MySQL daemon is started when it is restarted in multi-user mode. MySQL daemon MySQLd) consumes a very small amount of memory on the Pentium 5.1 running RedHat 133. Each daemon uses KB of memory and 4 MB of shared memory) mySQL can be loaded to the processor only when real queries are executed, which means that MySQL can be easily used for small databases without affecting other system functions.

Data Type

It is a good thing that fields support a large number of data types. Generally, integers, floating-point numbers, strings, and numbers are expressed in multiple lengths, and the variable-length BLOBBinary Large OBject type is supported. The automatic increment option can also be used to represent integer fields.

Unlike most other database systems, MySQL provides two relatively uncommon field types: ENUM and SET. ENUM is an enumeration type. It is very suitable for an enumeration type in Pascal language. It allows programmers to see field values similar to 'Red, 'green', and 'Blue, mySQL databases only store these values as one byte. SET is also borrowed from Pascal. It is also an enumeration type, but a single field can store multiple values at a time, this ability to store multiple enumerated values may not give you some impression and may threaten the definition of the third paradigm), but correct use of the SET and CONTAINS keywords can save a lot of table connections, it can achieve good performance improvement.

SQL compatibility

MySQL contains some different transformations from SQL standards. Most of them are designed as a compensation for the lack of SQL scripting language. However, other extensions make MySQL different. For example, LINK clause search automatically ignores case sensitivity. MySQL also allows users to customize SQL functions. In other words, a programmer can write a function and integrate it into MySQL. what it represents with any basic functions such as SUM () or AVG () there is no difference. The FUNCTION must be compiled into a shared library file (. so file) and loaded with a load function command.

It also lacks some common SQL functions and has no sub-selection (query in the query ). The View is gone. Of course, most subqueries can be rewritten using simple join clauses, but sometimes it is easier to think about problems with two nested queries than a large join. Similarly, the view only hides the where clause for programmers, but this is another convenience that programmers expect.

Stored Procedures and triggers

MySQL does not have a Stored Procedure language, which is the biggest restriction on programmers who are used to enterprise-level databases. Multi-statement SQL commands must be coordinated by the client code. In this case, multi-statement execution is allowed only by means of a sound Query Language and the ability to lock and unlock tables on the client.

Referential Integrity-RI)

One of the major defects of MySQL is the lack of a standard RI mechanism. However, the creators of MySQL databases do not ignore their users' desires and provide some solutions. One of them is the support for unique indexes. The lack of Rule restrictions (a fixed range limit on the given field) is compensated by a large number of data types. It does not simply provide check constraints (limits on the value of one field relative to another field in the same row), external keywords, and frequently RI-related "Cascading deletion" functions. Interestingly, when these functions are not supported, the SQL analyzer tolerates the syntax of these statements. This is intended to facilitate the migration of databases to MySQL. This is a good attempt, and it does leave a convenient way to support this function in the future. However, those who have not carefully read the document may mistakenly think that these functions actually exist.

Security

From beginning to end, my biggest complaint about MySQL was its security system. Its only drawback was its complexity and non-standard. In addition, it only changed when I called MySQLadmin to re-read user permissions. Generally, SQL GRANT/REVOKE statements are only supported in the latest version, but at least they have them now. MySQL database writers widely record their specific security systems, but it does need a learning process that may not be the same.