001-mysql Basic-mysql Overview

Overview of MySQL

MySQL is a small relational database management system, the developer of the Swedish MySQL AB company. was acquired by Sun Company on January 16, 2008. In 2009, Sun was acquired by Oracle. No one is optimistic about MySQL's future. MySQL is now widely used in small and medium-sized websites on the internet. Because of its small size, fast speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database. The web site of MySQL's official website is: www.mysql.com

1. MySQL Features

• Written in C and C + +, and tested with a variety of compilers to ensure the portability of the source code.
• Supports various operating systems such as AIX, FreeBSD, HP-UX, Linux, OpenBSD, Os/2 Wrap, Solaris, Windows, and more.
• Provides APIs for a variety of programming languages. Includes C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.
• Support multi-threading, make full use of CPU resources
• Optimized SQL query algorithm to improve query speed effectively.
• Provides multiple database connectivity pathways such as TCP/IP, ODBC, and JDBC
• Provides management tools for managing, inspecting, and optimizing database operations
• You can handle large databases that have thousands records.

2, the application of MySQL

Compared to other large databases such as Oracle, DB2, SQL Server, MySQL has its shortcomings, such as small size, limited functionality, but this does not reduce its popularity. MySQL provides more than enough functionality for general personal users and small and midsize businesses, and because MySQL is open source software, it can significantly reduce total cost of ownership. Currently, the popular web site architecture is lamp (linux+apache+mysql+php), that is, using Linux as the operating system, Apache as a Web server, MySQL as a database, PHP as a server-side script interpreter. Since these four software are free or open source software (FLOSS), using this method can build a stable, free website system without spending a penny.

3. mysql Management

You can use the command-line tools to manage MySQL databases (command MySQL and mysqladmin), or you can download the graphics management tools MySQL administrator and MySQL Query Browser from the MySQL Web site.

phpMyAdmin is a MySQL repository system management program written by PHP that allows administrators to manage MySQL repositories in a web interface.

Phpmybackuppro is also written in PHP and can be used to create and manage databases through a web interface. It can create pseudo-cronjobs that can be used to automatically back up MySQL databases at a certain time or period.

In addition, there are other GUI management tools, such as the previous Mysql-front and EMS MySQL Manager,navicat and so on.

4. Technical composition

databases : Containers for database System storage data

database Management System,dbms user to create, manage and maintain the database software, located between the user and the operating system, to define the data storage structure, provide the operation mechanism of the data, maintain the security of the database, Integrity and reliability.

database applications: Databases APPLICATION,DBA In many cases, the DBMS is unable to meet the requirements for data management, making the management process more intuitive and friendly, and the database application is responsible for communicating with the DBMS. Access and manage data stored in the DBMS, allowing users to insert, modify, and delete data in the DB.

5, SQL Introduction

SQL has 3 Main standards: ANSI (U.S. National Standards Agency) sql,92 Revised standard for SQL-92, the recent SQL99 standard, from SQL92 expansion, increased the object relationship features and other new features.

The SQL language consists of 4 parts:

1. Data definition Language (DDL): DROP, CREATE, ALTER, etc.

2. Data manipulation Language (DML): INSERT, UPDATE, delete, and so on.

3. Data Query Language (DQL): Select and so on.

4. Data Control Language (DCL): GRANT, REVOKE, COMMIT, rollback, and other statements.

Recommended format for standard SQL statements:

CREATE TABLE Students

（

Student_idint UNSIGNED,

Namevarchar (30),

Sex CHAR (2),

Birth DATE,

PRIMARY KEY (student_id)

）;

Keywords are all uppercase, the field is all lowercase, not recommended for mixed case, MySQL internal storage default is lowercase, when stored in the need for extra conversions. At the same time in different systems in, the case is differentiated.

6. Database Access Technology

ODBC: Open database Connectivity is used to connect a common interface provided by different databases. The advantage is that all databases are processed in a uniform manner. But at the same time, some database performance is not optimal.

JDBC: Java database Connectivity a standard way for user Java to connect to a database.

ADO is Microsoft in. NET Framework for database interaction standards.

PDO: Php Data object defines a lightweight, consistent interface for the PHP Access database, and can execute queries and fetch data through suppressed functions, regardless of the database. is a major feature of PHP5.

7. mysql Common tools

• Mysqladmin Create delete database, authorization, etc.
• Mysqlbinlog A tool that reads statements from a binary log,
• Mysqlcheck inspection, repair, analysis and optimization of table maintenance procedures
• Mysqldump dumps the MySQL database to a file.
• Mysqlhotcopy a tool to quickly back up a myisam or ISAM table when the server is running.
• Mysqlimport uses load data infile to import a text file into the client program for the related table.
• Mysqlshow client programs that display database, table, column, and index-related information.
• perror display system or MySQL error code meaning tool.

8. mysql Common management tools

(1) Mysqlworkbench for Visual Database design software,

Main functions, database design and model building.

SQL Development (replace MySQL Query Browser)

Database management (replace MySQL Administrator)

(2) phpMyAdmin, PHP writing, through the Web management, the use is also very extensive.

(3) Navicat is a powerful database management and development tool.

(4) SQLyog is a simple, efficient and powerful graphical MySQL.

001-mysql Basic-mysql Overview