Getting started with MySQL

MySQL is the most popular open source SQL database management system, developed, published, and supported by Mysqlab Corporation. Mysqlab is a commercial company founded by several MySQL developers. It is a second-generation open source company that combines open source value orientation, methodologies, and successful business models.

On the MySQL website (http://www.mysql.com/), the latest information on MySQL and MySQL is given.

MySQL is a database management system

A database is a structured collection of data. It can be anything from a simple shopping list to an exhibition, or a huge amount of information in a corporate network. To add data to a database, or to access and process data saved in a computer database, you need to use a database management system, such as a MySQL server. Computers are the ideal tool for processing large amounts of data, so the database management system plays a central role in computing, either as a standalone utility or as part of other applications.

MySQL is an associated database management system

The associated database saves the data in a different table, rather than putting all the data in a large warehouse. This increases speed and increases flexibility. MySQL's SQL refers to a "structured Query Language". SQL is the most commonly used standardized language for accessing databases, and it is defined by the Ansi/isosql standard. The SQL standard has evolved since 1986 and has several versions. In this manual, "SQL-92" refers to the standard published in 1992, "sql:1999" refers to the standard published in 1999, "sql:2003" refers to the current version of the standard. We use the term "SQL standard" to mark the current version of the SQL standard.

MySQL software is an open source software

"Open source" means that anyone can use and change the software. Anyone can download MySQL software from the internet without paying any fees. If you want, you can study the source code and make the appropriate changes to meet your own needs. The MySQL software uses the GPL (GNU General Public License),http://www.fsf.org/licenses/, which defines what can and cannot be done under different circumstances. If you are dissatisfied with the GPL or need to embed the MySQL code in a commercial application s program, you can purchase a commercially licensed version from us.

MySQL database server with fast, reliable and easy to use features

If it's what you're looking for, try it. MySQL server also has a set of useful features that have been developed in close collaboration with our users. On our benchmark home page, a comparison of MySQL server and other database managers is given.

Originally developed for processing large databases, MySQL server is faster than existing solutions and has been successfully used in many demanding production environments for many years. Although MySQL is always evolving, MySQL server is now able to provide rich and useful functionality. It has good connectivity, speed, and security, which makes MySQL ideal for accessing databases on the Internet.

MySQL server works in client/server mode, or embedded system

MySQL database software is a client/server system consisting of 1 multi-threaded SQL Servers supporting different backend, several different client programs and libraries, numerous management tools and a wide range of application programming interface APIs.

We can also provide MySQL servers in the form of embedded multi-line libraries, which you can link to your application for smaller, faster, and more manageable products.

There are a large number of shared MySQL software available

It is possible that your favorite applications and languages support MySQL database servers.

The official pronunciation of "MySQL" is "Myessqueell" (not "Mysequel"), but we do not mind that your pronunciation is "mysequel" or other local way.

History of MySQL

Our initial starting point was to use mSQL to connect our tables, which used our fast low-level (ISAM) subroutines. However, after some testing, we concluded that the speed or flexibility of the msql was not sufficient to meet our requirements. As a result, a new SQL interface is provided for our database, but the API interface is almost the same as the mSQL. The purpose of this API is to allow third-party code written for mSQL to be easily ported to MySQL.

The origin of the MySQL name is unknown. For more than more than 10 years, our basic catalogue and a large number of libraries and tools have been prefixed with "my". However, co-founder Montywidenius's daughter's name is also called "My". Today, the origin of the MySQL name is still a mystery, even for us.

The name of the Mysqldolphin (our logo) is "Sakila", which is selected by the founders of Mysqlab company from a number of suggestions provided by the user in the "Dolphin Naming" contest. The name was presented by Ambrosetwebaze, an open source software developer from Swaziland, Africa. According to Ambrose, the feminine name Sakila originated from SiSwati in the local language of Swaziland. Sakila is also the town name of a town in Tanzania and Arusha, close to Ambrose's home country, Uganda.

MySQL stability

The MySQL server uses multi-layered design and standalone modules. Some of the newer modules are listed here, and their test conditions are indicated.

· Replication (Stable)

A large number of servers using replication are in production mode with good results. In mysql5.x, the replication feature continues to be enhanced.

· InnoDB Table (Stable)

The INNODB transaction storage engine has been stable since version 3.23.49. InnoDB is being used in large-scale, heavy-duty production systems.

· BDB Table (Stable)

BerkeleyDB code is very stable, but in MySQL server we are still improving the BDB transaction storage engine.

• Full Text search (Stable)

Full-Text search is used in a wide range of applications. In MySQL4.0 and 4.1, significant feature enhancements have been added.

· MyODBC3.51 (Stable)

MYODBC3.51 uses ODBCSDK3.51 and is widely used in production activities. Some occurrences appear to be application-related, regardless of the ODBC driver or the underlying database server.

What maximum MySQL table can achieve

MySQL3.22 limits the table size to 4GB. Due to the use of the MyISAM storage engine in MySQL3.23, the maximum table size was increased to 65536TB (2567–1 bytes). Due to the larger table size allowed, the maximum valid table size for MySQL database is usually determined by the operating system's limit on file size, rather than the MySQL internal limit.

The InnoDB storage engine saves the InnoDB table in a table space, which can be created by several files. In this way, the size of the table can exceed the maximum capacity of the individual file. Table spaces can include raw disk partitions, making large tables possible. The maximum capacity of a tablespace is 64TB.

In the table below, some examples of operating system file size limits are listed. This is only a preliminary guide and is not final. For the latest information, refer to the documentation for the operating system.

Operating system	File Size Limits
Linux2.2-intel32-bit	2GB (LFS:4GB)
linux2.4+	(Usingext3filesystem) 4TB
Solaris9/10	16TB
Netwarew/nssfilesystem	8TB
Win32w/fat/fat32	2gb/4gb
Win32w/ntfs	2TB (possibly larger)
macosxw/hfs+	2TB

Under the Linux2.2 platform, a myisam table of more than 2GB can be obtained by using a large file support (LFS) patch on the ext2 file system. Under the Linux2.4 platform, there are patches for reiserfs that can support large files (up to 2TB). Most of the Linux versions currently released are based on the 2.4 kernel, which contains all the required LFS patches. Using JFS and Xfs,petabyte (gigabit) and larger files can also be implemented on Linux. However, the maximum available file size still depends on a number of factors, one of which is the file system used to store the MySQL table.

Install MySQL

Before installing MySQL, you should:

1. Determine if MySQL will run on your platform.

2. Select the distribution version of the installation.

3. Download the distribution and verify its integrity.

Key steps for MySQL installation

Key Step one: Double click to open the MySQL installation program, go to the Select installation type step. There are three types of installation options available:

L Typical: Typical installation

L Complete: Fully installed.

L Custom: Customize

In general, we choose "Complete: Full Installation", and for some skilled operators you can choose to customize the installation (custom: Customized).

Key Step two: When installing to the dataset selection step, be careful: Be sure to select the third one and choose the "GBK" DataSet. As shown: (Some do not match the picture, to take the actual example)

The above choice of "data set" ensures that we can enter Chinese characters conveniently when creating database and inputting data.

Key step three: "Set Windows Options"; This is the service item for Windows that chooses to install MySQL and the MySQL binary run directory that you installed is included in the Windows Path environment variable. If there is no special need, ask for both items to be checked, as shown in:

For more information on Windows services and the path of Windows, see other materials or search the "Baidu" website for answers to more questions.

Key step four: Configure and start the MySQL service, and finally discover that the MySQL installation is successful by starting with the options shown below.

Note that the keyword "writeconfigurationfile", followed by the directory "(C:\Soft\MySQL5.0\my.ini)", is the installation directory that you selected when you installed MySQL. "My.ini" is the MySQL configuration file, if you find a configuration problem later, you need to modify this file to run the service properly.

Start MySQL

After MySQL installation, you need to start MySQL, you can do the establishment of databases and build tables and other operations; Here are some steps to take care of starting MySQL.

Start MySQL note-Confirm that the service has been started

In general, if you have just installed MySQL, then the service has started, if you have finished installing MySQL and restarted the computer, you need to restart the MySQL service;

There are two ways to start the MySQL service: "Service item Start" and "command line Startup".

Service Item Start: Open Windows Service item, find MySQL service, right mouse button to start.

Command line Startup: "Start Menu"-"Run"-"Enter" Netstartmysql "

If you need to start and close MySQL at the command line, you need to use the following command:

Netstopmysql: Stop Service

Netstartmysql: Start Service

Start MySQL considerations-Enter using the database username and password

After you start the service, you need to start the database schema:

Start Menu-"Run"-"type

"Mysql-uroot-p123456"

"-uroot" means the user name is root; "-p123456" means the database password is 123456

Note: Depending on the root password you specified during installation, do not assume that all database passwords are 123456, and be sure to enter MySQL edit mode according to the actual installation password.

Supplemental knowledge: All databases are supported by default command line, if you want to do some advanced action, backup, export the database, generally need to use the command line, so that the efficiency is higher.

Simple MySQL Statement

The basic difference between MySQL and the SQL Server we have learned is not very large; most statements are similar; So, the main focus is on the use of special MySQL statements.

Common MySQL Statements

1. Use the show statement to find out what database currently exists on the server:

mysql>showdatabases; +----------+ | database| +----------+ |mysql| |test| +----------+ 3rowsinset (0.00SEC)

2. Create a database Abccs

mysql>createdatabaseabccs;

Note the sensitivity of the different operating systems to the case.

3. Select the database you created

Mysql>useabccs

Databasechanged

At this point you have entered the database Abccs you just created.

4. Create a database table

First look at what tables are present in your database:

mysql>showtables;

Emptyset (0.00SEC)

Indicates that there are no database tables in the database that you just created. Next, create a database table MyTable:

We are going to create a birthday table for your employees whose contents include the employee's name, gender, date of birth, and city of birth.

Mysql>createtablemytable (Namevarchar), Sexchar (1), ->birthdate,birthaddrvarchar (20)); Queryok,0rowsaffected (0.00SEC)

Because the column values of name and Birthadd are variable, you choose varchar, whose length is not necessarily 20. You can choose any length from 1 to 255, and if you need to change its word size later, you can use the Altertable statement. ); Gender can be represented by a single character: "M" or "F", so char (1) is selected, and the birth column uses the date data type.

After creating a table, we can look at the results we have just made and use Showtables to show which tables are in the database:

mysql>showtables; +---------------------+ | tablesinmenagerie| +---------------------+ |mytables| +---------------------+

5, the structure of the display table:

mysql>describemytable; +-------------+-------------+------+-----+---------+-------+ | field| type| null| key| Default| extra| +-------------+-------------+------+-----+---------+-------+ |name|varchar (20) | yes| | null| | |sex|char (1) | yes| | null| | |birth|date| yes| | null| | |deathaddr|varchar (20) | yes| | null| | +-------------+-------------+------+-----+---------+-------+ 4rowsinset (0.00SEC)

6. Add a record to the table

Let's first use the Select command to view the data in the table:

mysql>select*frommytable;

Emptyset (0.00SEC)

This means that the table you just created is not yet documented.

Add a new record:

Mysql>insertintomytable ->values (′abccs′,′f′,′1977-07-07′,′china′); Queryok,1rowaffected (0.05SEC)

Then use the Select command above to see what has changed. We can add the records of all employees to the table in this way, one at a line.

Modify the type or length of a field in MySQL

Altertable table name modifycolumn field name type;

For example, the User table name field in the database is varchar (30);

Altertableusermodifycolumnnamevarchar (50);

Modify the database structure

1. Add Field

altertabledbnameaddcolumn< Field name >< field Options >

2. Modify Fields

altertabledbnamechange< old field name >< new field name >< options >

3. Delete fields

altertabledbnamedropcolumn< Field Name >

4. Modify the table name

altertablejsxtrenameasjsxt_tmp;

Altertablepersonadd (Pidintauto_incrementprimarykey);

Altertablearticlemodifyidint (Ten) auto_increment;

Other statement usages

UPDATE statement

mysql>updatepersondatasetage=age+1;

To insert a value into a field

Insertintousessetname= ' Yao Ming ', age=25;

Inserting multiple values at once

Insertintousers (Name,age)

VALUES (' Yao ', 25), (' Bill Gates ', 50), (' Martians ', 600);

Delete a line of statements

Deletefromusetablewherename= ' Xiaoli ';

Modify a field

Altertabletablenamechangefield_name_tobe_changenew_namevarchar

(//altertableusetablechangeagesexint); (10);

Delete a field

Altertabletablenamedropfield_name_tobe_delete

Renaming a table

Altertabletablenamerenameasnewtablename

Practical questions:

1. If you want to record a company's employees, do you use plain paper, Office software, or a database?

Summary:

In this chapter, we have mainly studied:

The concept and history of U MySQL

U MySQL installation and use

English vocabulary:

English full text Chinese

MySQL MySQL is a sophisticated SQL database management system, although it is not an open source product, but in some cases you are free to use. Thanks to its power, flexibility, rich application programming Interface (API) and sophisticated system architecture, it has been favored by a wide range of free software enthusiasts and even commercial software users, especially with Apache and Php/perl, providing a powerful impetus for building database-based dynamic websites. Especially in conjunction with PHP used by Linux under the web Developer called PHP Gold partner.

Practice Items:

Use the MySQL database you learned in this chapter to create a record of your monthly income and expenses (create a database with your own initials, create a table with letters with English meaning)

Getting started with MySQL