I have been engaged in Informix and Oracle database, one day, I found a small and unique database on the network, widely used, from MySQL website http://www.mysql.com/ I download its database software, I feel It's really good, this is my little learning note hopes to help every beginner.
1. MySQL Database Introduction
MySQL is a relational database system developed by MySQL AB, Sweden, which has a distributed database management system for all popular operating system platforms. It has a distributed database management system for client / server architecture. MySQL is completely applicable to the network, and the database built can be accessed anywhere on the Internet, so you can share the database anywhere in any part of the network. MySQL has the advantages of strong functional, simple use, convenient management, fast running, high reliability, and strong security and security. MySQL is written with C and C , which works on many platforms (Unix, Linux, Windows), providing API functions for different programming languages (C, C, C , Java, etc.); using core threads to achieve multi-thread, can be very good Support multi-CPU; a storage mechanism for transaction and non-transaction; fast thread-based memory allocation system; MySQL uses a dual license, users can use mysql software in the GNU license terms, can also use mysql software, or Get formal business licenses from MySQL AB.
In addition to the above features, MySQL has a maximum feature, which is on an operating system such as UNIX, which is free to download its servers and client software on the Internet. And you can also get a number of third-party software or tools that match them from the Internet. And on the Windows system, its client programs and client libraries are free.
1.1. Why choose mysql
If you are looking for a free or expensive database management system, you can have several options, such as Mysql, MSQL, Postgres (a free but do not support system from business supplier engine). When comparing MySQL with other database systems, the most important factors to consider are performance, support, characteristics (consistency, expansion, etc.), certification conditions, and constraints, prices, etc. In contrast, MySQL has many attractive things:
Speed. MySQL is running fast. Developers claim that mysql may be the fastest database currently available. This performance is investigated in http://www.mysql.com/benchmark.html (Performance Compare Page on the MySQL Web site).
2. It is easy to use. MySQL is a high-performance and relatively simple database system that is less complex than some larger systems.
3. Price. MySQL is free to most individual users.
4. Small. 4.1.1 The database distribution is only 21m, and the installation is completed is only 51m.
5. Support query language. MySQL can take advantage of SQL (Structured Query Language), SQL is a language that is selected by all modern database systems. You can also use an application that supports ODBC (open database connections), ODBC is a database communication protocol developed by Microsoft.
6. Performance. Many clients can connect to the server simultaneously. The MySQL database does not have the number of users, and multiple clients can use the same database simultaneously. Mysql can be accessed by using several input queries and views the interfaces to interactively access MySQL. These interfaces are: command line client programs, web browser, or x window system client programs. In addition, there are interfaces written in various languages (such as C, C, C, C , Eiffel, Java, Perl, PHP, Python, Ruby, and TCL). Therefore, you can choose to use a compiled client programs or write your own client applications. 7. Connectivity and security. MySQL is completely networked, and its database can be accessed anywhere on the Internet, so you can share the database with anyone else anywhere. And MySQL can also access control, which can control which people can't see your data.
8. Portability. MySQL can run in various versions of UNIX and other non-Unix systems such as Windows and OS / 2). MySQL can run from home PC to advanced servers.
9. Open distribution. MySQL is easy to get; just use a web browser. If you can't understand how something is rootable, or if you feel 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. Tools provided by mysql
MySQL contains the following tools:
1. SQL server. This is the mysql engine that provides access to the database.
2. Access the client program. Including an interactive program that allows you to enter queries and view results, as well as several managers and utilities that help manipulate sites. One of these utilities allows control of the server. Other utility import or export data, check access licenses, etc.
3. Help users write their own program's client library. 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 their product efficiency, or willing to use software written to others. Therefore, using MySQL can get a wide variety of third-party tools, these tools make Mysql use more convenient, or extend MySQL to enable them to develop such, such as Web sites.
1.3. Is MySQL?
MySQL is not an open source product, but it can generally be used free of charge. The basic content of the "Mysql Reference Guide" can be referred to 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 can be used free of charge, but can not sell any software or services that require it. If so, you should get the license of the server. The reason is that if you make money with MySQL, you should take some developers to MySQL ($ 495 for the professional RDBMS that helps you make money, and there are also a lot of free software, which can be used to help you more effective Use mysql using mysql.)
3. The MYSQL server's Windows requires a license. Currently Mysql Pro is online offer (Note MySQL Pro is a commercial version of MySQL):
Http://www.mysql.com/products/pricing.htmlmlmlmysql Pro Includes The InnoDB Transactional Storage Engine, Which Provides Row-Level Locking.
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 can be obtained through the GNU Public License (GPL) license and can be used for any use without paying. MySQL 3.20.32a is available under GPL conditions.
5. Whether you need a server license, you can get formal technical support from MySQL developers on a payment basis. I will pay attention to this. (Especially if your company is not approved with software without such guaranteed software.) Mysql provides a number of technical support, in addition to getting excellent support, you can help Mysql development, which MySQL world benefits.
6. Description of the Windows license
Sometimes someone will ask, "I use Windows, why must I get the license of the mysql server?" This is a very reasonable question, and there is a reasonable answer, that is, the cost of software development. In order to engage in software development, an operating system and some development tools such as editors and compilers are required. For these needs, there is a fundamental difference between UNIX development and Windows development. Under UNIX, you can get almost everything for free:
a) The operating system used has several options, such as Linux or free BSD series, such as FreeBSD, NetBSD, and OpenBSD.
b) Developing tools used, these operating systems have compilers such as VI and editing program macros and GCC and EGCS.
c) When upgrading these operating systems or publishing a development tool, they can download them from the Internet or from a cheap CD, even if they are significant modifications.
And developing software on Windows is quite expensive, for example:
a) The operating system is not free.
b) Development tools, such as compilers are not free.
c) When an operation system or tool upgrade is, unless a small problem modification or a micro-upgrade, otherwise pay again. All of these indicates that software development costs under UNIX are almost zero, and the cost is considerable in Windows. MySQL developers may be willing to continue to study MySQL, but they don't want to pay so much money for this right. Anyway, its development cost of Windows must be recovered, and the MYSQL license 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 (Use this for new development)
Mysql 5.0 - Development Tree (use this for previewing and testing new features) mysql 3.23 - Older Production Release
The 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 Implement In 3.23 for InnoDB)
Triggers
5.1
Full Outer Join
5.1
Constraints
5.1
A version of the version of the Mysql supports subqueries and SQL statements preprocess Prepare methods, so this report is subject to a version 4.1 version.
2. Details
2.1. Table size and number of records
There is no internal limit on the size of the size mysql, which is limited to the limitations of the operating system on the table file size. At present, the top of the Linux-Intel 32 bit is up to 2GB, 512GB on Solaris 2.7. In practical applications, the mysql database processing has a database system with 60000 tables and 500 billion-recorded database systems, and the table record can reach 5 million.
At present, there are only 134 OMCR database tables, and the database record has not yet been millions.
2.2. Index
Supports each table 32 index, each index can contain 1-16 fields, and the maximum field length is 500 bytes.
2.3. Connection
Support TCP / IP SOCK connection, so that it is easy to connect across platforms, and also supports named pipe connections under Windows, ODBC connections. At the same time, the MySQL server is not limited to the number of access users.
2.4. Character set
Support ISO-8859-1 (Latin1), German, BIG5, UJIS character set, we currently use ISO-8859-1 character set,
2.5. Data type
Mysql supported field type defined data types: Signed / Unsigned Integers 1, 2, 3, 4, And 8 Bytes long, float, double, char, varchar, text, blob, date, time, 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 used by Decimal, Varchar2, Date, Text and BLOB data types These types have been far away to meet our needs, Type Description as follows:
1. Decimal [(Display Length [, Digital])] [unsigned] [Zerofill] stores floating point numbers in the form of strings, so the number of bytes is displayed by the display length node. Its maximum may be Double. If the display length is not specified, the default is 10; if no count of few digits is specified, the default is 0.
2. [National] varchar (m) [binary] becomes a long string. String length is 0 to 255. It is not sensitive to case, unless it is binary. After mysql4.1.0, the length can exceed 255, if the length exceeds 255, will be converted to the TEXT type.
3. BLOB or Text Blob is used to store binary objects, and text is used to store text values. BLOB is basically the same as the TXT type. Different is that TXT is not sensitive to case when making comparisons, and Blob is sensitive to case. In most cases, TXT can be regarded as an extension of VARCHAR. Its maximum length is larger. Different is that txt does not automatically remove spaces when storing, while Varchar will. Blob can be regarded as an extension to VARCHAR BINARY. BLOB and TXT columns cannot have a default. Blob has four: 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- largest The length is 4294967295 or 4g (2 ^ 32-1). TXT also has four: TinyTXT-max length is 255; TXT-max length is 65535; MediumTXT-maximum length is 16777215 (2 ^ 24-1); longtxt-max length is 4294967295 or 4G (2 ^ 32-1). 4. DateTime date and time, 8 bytes. Supported range is '1000-01-01 00:00:00' to '9999-12-31 23: 5999-12-31 23: 59: 59', Mysql Displays its value with 'YYYY-MM-DD HH: MM: SS' format, However, it is allowed to assign it in a string or number.
5. TimeSTAMP [(Display Length)] 4 bytes. The support range is the simple representation of '1970-01-01 00:00:00' to a certain moment between December 31, 2037. In MySQL 4.0 and earlier, its display format is YYYYMMDDHHMMSS, YYMMDDHMMMS, YYYYMMDD, or YYMMDD, which relies on the value of the display length. After the mysql4.1, change to 'YYYY-MM-DD HH: MM: SS' format. Its main purpose is to keep the record of the table update, when the table INSERT or UPDATE is recorded, if there is no value to provide the value of the TIMESTAMP column, it will be set to the time of the operation, if the value of the TIMESTAMP column is supplied to NULL It will be automatically set to the current time.
2.6. SQL statement
MySQL Database section supports ANSI SQL_99 syntax, which mainly uses Create Table, Insert, Delete, Update, and SELECT operations in this regard. But since MySQL does not support storage procedures, triggers, and views, some of our database services require programming.
2.7. Application Interface API
To facilitate the development of applications, MySQL provides client libraries written in Perl, PHP, Java, Python, C , TCL programming languages, which allows the mysql database from internal access from any program. The client library implements the application programming interface (API), how the API defines how the client program establishes and executes communication with the server. Our OMCR mainly uses C, C , and Java programming, so we look at MySQL from the programming interface to meet our needs.
