Shulou(Shulou.com)06/01 Report--

This article is mainly for you to summarize the basic knowledge points of MySql database, the content is easy to understand, I hope you can learn, after learning, there will be a harvest, the following let the editor take you to take a look at it.

This paper describes the basic knowledge points of MySql database with an example. Share with you for your reference, the details are as follows:

Basic database operations 1) create a database

Basic syntax: create database database name

Create a database that should be named itcast. The sql syntax is as follows:

``create database `itcast `; ``

The main thing to need is that in order to avoid conflicts between user-defined names and system commands, it is best to use backquotes (``) to include database / field names and data table names

If the creation database exists, the program will report an error. To prevent this from happening, you can use "if not exists" to create the database. The syntax is as follows:

Recate database if not exists `itcast` / / this statement means that if a database named itcast does not exist in the database, the database is created, otherwise the operation of creating the database itcast is not performed. 2) View the database.

After you finish creating the database, to view the information about the database, you can use this statement

``show caeate database database name``

View databases that already exist on the MySql database server

``show databases ``3) Select a database

There may be multiple data on the database server. Select the command syntax for the database:

``use database name``4) Delete the database

The delete operation of the database will not only delete the data in it, but also reclaim the originally allocated storage space.

``drop database database name``

When you delete a database using the "drop database" command, if the delete database does not exist, the MySql server will report an error, so when you delete the database again, use "if existe"

``drop database if exists `itcase` / / if the database itcase exists in the MySql database server, delete the database, otherwise the operation of deleting the database itcasse will not be performed ``data type

When creating a data table, you need to select a data type for each field, and the selection of the data type determines the storage format, valid range and corresponding limitations of the data.

Mysql provides a variety of data types, mainly divided into three categories

Numerical type

String type

Date and time type

1) numeric type

MySql provides a number of numeric types, generally divided into integer and floating-point types

Integer types are divided into int,smallint and so on according to the range of values.

Floating point type is divided into float,declmal and so on.

Integer type

Floating point type

The valid range of values for the decimal type is determined by M and D. Among them, M and D decide. Where M represents the length of the data and D represents the length after the decimal point. For example, the data type is set to DECIMAL (4jue 1), and when 3.1415926 is inserted into the database, the result is displayed.

String type

When developing a project, most of the data that needs to be stored is in string format, so MySQL provides many data types for storing strings.

Both BLOB and TEXT are used to store large amounts of data, but the difference is that when sorting and comparing stored data, BLOB is case-sensitive, while TEXT is case-insensitive.

Date and time type

To facilitate the storage of dates and times in the database, MySQL provides several related data types that can be flexibly selected according to actual development.

2) Storage type

In the database, whether the design of the data table is reasonable or not directly affects the efficiency of the database, while the choice of storage engine when designing the data table determines what functions the data table has. Next, we will introduce the common storage engines of MySQL and their functions.

InnoDB storage engine

MyISAM storage engine

MEMORY storage engine

ARCHIVE storage engine

InnoDB engine

The InnoDB storage engine has been designated as the default storage engine since MySQL version 5.5, which is used to perform transaction security processing for transaction, rollback, crash repair, and multi-version concurrency control. At the same time, it is also the first table engine in MySQL to provide foreign key constraints, especially for transaction processing, which can not be matched by other MySQL storage engines.

The advantage of InnoDB is that it provides good transaction management, crash repair and concurrency control.

MyISAM storage engine

MyISAM storage engine is developed based on ISAM storage engine. It not only solves many shortcomings of ISAM, but also adds a lot of useful extensions.

Among them, the data table using the MyISAM storage engine will be stored as three files with the same file name as the table name, with file extensions of frm, myd, and myi, respectively.

Compared with InnoDB, MyISAM has the advantage of fast processing speed, and the disadvantage is that it does not support transaction processing.

MEMORY storage engine

MEMORY storage engine is a special kind of storage engine in MySQL. In the tables of the MEMORY storage engine, all the data is stored in memory, so the data can be processed quickly, but it cannot be persisted (the data will be lost when the program goes wrong or when the computer is shut down), and cannot store too much data. MEMORY storage engine is an ideal choice for data that requires fast read and write speed, but a small amount of data and does not need to be persisted.

ARCHIVE storage type

The ARCHIVE storage engine is suitable for storing a large amount of data that is maintained for a long time but is rarely accessed. For data tables using the ARCHIVE storage engine, the zlib compression library is used for data storage, and the records are decompressed in real time when the record is requested. It should be noted that the ARCHIVE storage engine only supports queries and inserts, and the query efficiency is low because it does not support data indexing.

The above is about summarizing the basic knowledge points of MySql database, if you have learned knowledge or skills, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.