MySQL Database learning notes (i)

mysql| Notes | data | database
I've been working on Informix and Oracle database development, and one day I found a small, chic database on the web that was widely used, and I downloaded its database software from MySQL's website http://www.mysql.com/, and it felt really good after use, This is a little bit of my learning notes I hope to help you beginners a little.


1. mysql Database introduction

MySQL is a MySQL AB company in Sweden developed a relational database system that can be used in a variety of popular operating system platforms, which has a client/server architecture distributed database management system. MySQL is fully applicable to the network, the database built with it can be accessed anywhere on the Internet, so you can share the database with anyone anywhere on the network. MySQL has the advantages of strong function, simple use, convenient management, fast running, high reliability and strong security and confidentiality. MySQL is written in C and C + +, it works on many platforms (Unix,linux,windows), provides API functions for different programming languages (C,c++,java, etc.), implements multithreading with core threads, and can support multiple CPUs well Provide transactional and non transactional storage mechanisms; Fast thread-based memory allocation system; MySQL uses a dual license that allows users to use MySQL software in the form of free software or open source software under the GNU License terms, or to obtain a formal business license from MySQL AB.

In addition to the above features, MySQL has one of the biggest features, it is in such as UNIX operating systems, it is free, can download its server and client software from the Internet. And it also gets a lot of third-party software or tools to match on the Internet. On Windows systems, the client program and the client library are free.

1.1. Why Use MySQL

If you are looking for a free or inexpensive database management system, you can have several options, such as MySQL, mSQL, Postgres (a free system that does not support the engine from the commercial vendor), and so on. The most important factors to consider when comparing MySQL with other database systems are performance, support, features (consistency with SQL, extensions, and so on), authentication conditions and constraints, prices, and so on. MySQL, by contrast, has many attractions:

1. Speed. MySQL runs very fast. Developers claim that MySQL is probably the fastest database available at the moment. You can access the http://www.mysql.com/benchmark.html (performance comparison page on the MySQL Web site) to investigate this performance.

2. Easy to use. MySQL is a high-performance and relatively simple database system, compared with the setup and management of some larger systems, it is less complex.

3. Price. MySQL is free for most individual users.

4. Compact. 4.1.1 's database distribution is only 21M, and the installation is only 51M.

5. Support Query Language. MySQL can take advantage of SQL (Structured Query language), SQL is a language that all modern database systems use. You can also take advantage of applications that support ODBC (Open Database Connectivity), an ODBC database communication protocol developed by Microsoft.

6. Performance. Many clients can connect to the server at the same time. The MySQL database has no user limit and multiple clients can use the same database at the same time. You can interactively access MySQL using several input queries and viewing the results of the interface. These interfaces are: command line client programs, Web browsers, or X Window System client programs. In addition, there are interfaces written by various languages (such as C, C + +, Eiffel, Java, Perl, PHP, Python, Ruby, and TCL). Therefore, you can choose to use a prepared client program or write your own client application.

7. Connectivity and security. MySQL is fully networked and its databases can be accessed anywhere on the Internet, so you can share a database with anyone anywhere. And MySQL can also access control, you can control who cannot see your data.

8. Portability. MySQL can run on various versions of UNIX and other non-UNIX systems, such as Windows and OS/2. MySQL can be run from home PCs to advanced servers.

9. Open distribution. MySQL is easy to get, as long as you use a Web browser. If you can't understand how something works, or if you're curious about an algorithm, you can take the source code and analyze the source code. If you don't like something, you can change it.

1.2. MySQL provides the tools

MySQL contains the following tools:

1. SQL server. This is the MySQL engine that provides access to the database.

2. Access the server's client program. Includes an interactive program that allows direct input to queries and view results, as well as several management programs and utilities that help manipulate the site. One of the utilities allows you to control the server. Other utilities import or export data, check access licenses, and so on.

3. A client library that helps users write their own programs. MySQL offers numerous APIs (c, C + +, Eiffel, Java, Perl, PHP, Python, Ruby, and TCL)

4. In addition to providing embedded software, MySQL is also used by many talented people. These people like to write software to improve the efficiency of their products, or to make their own software available for others to use. As a result, you can use MySQL to get a variety of third-party tools that make MySQL easier to use, or extend MySQL so that it goes into areas such as WEB site development.

1.3. MySQL is free

MySQL is not an open source product, but it is generally free to use. You can refer to the license description for the MySQL Reference Guide, which is as follows:

1. Client programs and client programming libraries are free on all platforms.

2. On UNIX and other non-Windows platforms, the MySQL server is free to use, but cannot sell it or sell any software or services that require it. If you do this, you should get a license to use the server. The reason is that if you make money with MySQL, you should divide it into MySQL developers ($495 is cheap for a professional RDBMS that helps you make money, and there's plenty of free software available to help you use MySQL more effectively.) ）

3. The Windows version of the MySQL server requires a license. Current MySQL Pro on-line quotes (note MySQL Pro is the business version of MySQL):

Http://www.mysql.com/products/pricing.html

MySQL Pro includes the InnoDB transactional storage engine, which provides, row-level.

Number of licenses

Price per copy

EUR

USD

GBP

1.. 9

440.00

495.00

290.00

10.. 49

315.00

360.00

205.00

50.. 99

255.00

290.00

165.00

100.. 249

195.00

220.00

127.00

250.. 499

155.00

175.00

100.00

500 +

Ask for quote, sales@mysql.com



4. The older version of MySQL is available through the GNU public License (GPL) license and can be used for any purpose without paying. MySQL 3.20.32a is available under GPL conditions.

5. Whether or not you need a server license, you can get formal technical support from MySQL developers on a pay-as-you-go basis, and I draw the reader's attention to this. (especially if your company supervisor is not in favor of using software that is not so guaranteed.) MySQL provides a variety of levels of technical support, in addition to excellent support, you can also help MySQL development, and thus the entire MySQL world to benefit.

6. Description of Windows License

Sometimes someone asks, "I'm using Windows, why do I have to get a MySQL server license?" "This is a very reasonable question, and there is a reasonable answer, that is the cost of software development." To engage in software development, you need an operating system and some development tools, such as editors and compilers. In terms of these requirements, there is a fundamental difference between the development of UNIX and the development of Windows. Under UNIX, you get almost everything for free:

A there are several options for operating systems, such as Linux or free BSD series, such as FreeBSD, NetBSD, and OpenBSD.

b The development tools used, all with editors such as VI and edit macro directives and compilers such as GCC and Egcs.

(c) When upgrading these operating systems or publishing development tools, they can be downloaded from the Internet or from a cheap CD, even if significant modifications are made.

Developing software on Windows is expensive, for example:

A the operating system is not free.

b) Development tools, such as compilers, are not free.

C When an operating system or tool is upgraded, pay again unless minor problems are modified or minor upgrades are made. All of this shows that the cost of software development under UNIX is almost nil., and it costs a considerable amount of money in Windows. MySQL developers may be willing to continue to study MySQL, but they don't want to pay that much for that right. In any case, its Windows development costs must be recovered, and the sale of MySQL licenses is a means.

1.4. Current version

The MySQL version that can be downloaded online is as follows:

MySQL 4.0--Production release (recommended)

MySQL 4.1--Alpha release (with this for new development)

MySQL 5.0--Development tree (with this for previewing and testing new features)

MySQL 3.23--Older production release

Main features of each version:

Feature

MySQL version

Unions

4.0

Subqueries

4.1

R-trees

4.1 (for MyISAM tables)

Stored procedures

5.0

Views

5.0 or 5.1

Cursors

5.0

Foreign keys

5.1 (already implemented in 3.23 for InnoDB)

Triggers

5.1

Full OUTER JOIN

5.1

Constraints

5.1

Version 4.1 MySQL supports the preprocessing prepare method of subqueries and SQL statements, so this report test is mainly for 4.1 versions.

2. Function Details

2.1. Table size and number of records

There is no internal restriction on the size of the table MySQL, only limited by the operating system to the size of the table file, currently on the Linux-intel bit the largest table 2GB, on the Solaris 2.7 is 512GB. In practical application, MySQL database processing has 60,000 tables and 5 billion records of the database system, the table records can reach 5 million.

At present, our OMCR database table is only 134, and the database records are not millions.

2.2. Index

Supports 32 indexes per table, each index can contain 1-16 fields, and the maximum field length is 500 bytes.

2.3. Connection

A sock connection that supports TCP/IP, which facilitates database connectivity across platforms, and also supports named pipe connections and ODBC connections under Windows. At the same time, the MySQL server has no restrictions on the number of access users.

2.4. Character Set

Support Iso-8859-1 (Latin1), German, Big5, ujis character set, we are currently using the iso-8859-1 character set,

2.5. Data type

The data types defined by the MySQL-supported field types for the table are: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, Dates, times, DATETIME, TIMESTAMP, year, SET, and ENUM types can be divided into three types: numeric type, date and time type, character type We are currently mainly using decimal, VARCHAR2, date, Text and BLOB data types these types are already far from our needs, the type description is as follows:

1. decimal[(display length [, decimal digits])] [UNSIGNED] [Zerofill]
Floating-point numbers stored as strings, so the number of bytes is shown by the display length node. The biggest possibility is double. If no display length is specified, the default is 10, or 0 if no decimal digits are specified.

2. [National] VARCHAR (M) [BINARY]
Variable-length string. The size of the string length is 0 to 255. It is insensitive to case unless it is binary. After the MySQL4.1.0 version, the length can exceed 255, and if the length exceeds 255, it is converted to the text type.

3. Blob or text
A blob is used to store binary objects, and text is used to store literal values. A blob is essentially the same as a txt type, unlike TXT, which is insensitive to case sensitivity when making comparisons, and a blob sensitive to case sensitivity. In most cases, TXT can be thought of as an extension of the varchar, its maximum length is larger, the difference is that at the time of storage, TXT will not automatically remove the end of the space, and varchar. A blob can be seen as an extension of the varchar binary. A blob and a txt column cannot have a default value.
There are four kinds of blobs: tinyblob-Maximum length is 255 (2^8-1), blob-maximum length is 65535 (2^16-1), mediumblob-maximum length is 16777215 (2^24-1); longblob- The maximum length is 4294967295 or 4G (2^32-1).
TXT also has four kinds: tinytxt-maximum length is 255;txt-maximum length is 65535;mediumtxt-maximum length is 16777215 (2^24-1), longtxt-maximum length is 4294967295 or 4G (2^32-1).

4. DATETIME
Date and time, 8 bytes. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 ', and MySQL displays its value in the ' yyyy-mm-dd HH:MM:SS ' format, but allows it to be assigned as a string or as a number.

5. timestamp[(display length)]
4 bytes. The scope of support is a simple representation of a moment between the ' 1970-01-01 00:00:00 ' and midnight of December 31, 2037. In MySQL4.0 and earlier versions, its display format was YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD, depending on the length of the display. Change from MySQL4.1 version to ' yyyy-mm-dd HH:MM:SS ' format. Its main purpose is to keep records of updates to the table, when an INSERT or update record is made to a table, if the value of the timestamp column is not supplied, it is set to the time when the operation was made, and it is automatically set to the current time if the value of the supplied timestamp column is null.

2.6. SQL statement

The MySQL database partially supports ANSI sql_99 syntax, and our main use of the Create Table,insert,delete,update,select operation is supported in this regard. However, because MySQL does not support stored procedures, triggers, and views at this time, some of our database services need to be programmed.

2.7. Application Interface API

To facilitate application development, MySQL provides a customer library in Perl, PHP, Java, Python, C + +, and TCL programming languages, allowing access to the MySQL database from within any program. The client library implements the application programming interface (API), which defines how the client program establishes and executes communication with the server. Our OMCR mainly uses C, C + + and Java programming, so from the programming interface to see MySQL has met our needs.