MySQL (I) and mysql (I)

MySQL (I) and mysql (I)
1. Start the MySQL service and log on to the MySQL database

1. Start the MySQL Service

 

1. Click the Start Menu of a single machine. In the displayed menu, select the run command to open the run dialog box.

2. Enter "services. msc" in the text box and click OK to open the "Service Manager" for Windows]

3. Input "net start mysql" in DOS to start the service. Enter "net stop mysql" to close the service

 

2. log on to the MySQL database

 

1. Log On in Windows

 

Click Start> Run. cmd> myslq-h-hostname-u username-p.

-H is followed by the server address

-The parameter after u is the Database User Name

-The parameter after p is the user's logon password.

 

2. Log On with MySQL Command Line Client

 

Click Start> All Programs> MySQL Server> MySQL Command Line Cliennt]

Enter Password Input window

 

3. log on to the database using the MySQL graphical management tool.

 

 

3. Configure the Path variable

 

The reason for logging on to the MySQL database through Windows is that the bin directory of MySQL is configured in the Path variable.

To configure Path:

Right-click my computer and choose Properties> System Properties> advanced> environment variables> Path> edit ]-> Add the bin directory of MySQL to the directory-> [OK]

Ii. Change the MySQL Configuration

1. Use the Configuration Wizard to change the configuration

 

Enter the mysqlbininstallation directory and start the mysqlinstanceconfig.exe file.

 

2. Manually change the configuration

 

Configure the my. ini file in the MySQL installation directory

Ii. Basic MySQL database operations 1. Create a database

After MySQL is installed, several required databases are automatically created under the data Directory. You can use the show database; statement to view all existing databases.

 

Statement for creating a database:

Create database database_name;

 

Example:

Create test_db

Create database test_db;

After creation, you can use show create database to view DATABASE definitions.

2. delete a database

 

Deleting a database deletes an existing database from the disk. After the database is cleared, the data in the database is also clear.

Statement for deleting a database:

Drop database database_name;

Example:

Delete test_db

Drop database test_db;

 

Iii. database storage engine

 

The database storage engine is the underlying software component of the database. The database management system uses the data engine to create, query, update, and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and other functions. Different storage engines can also obtain specific functions.

1. MySQL storage engine Introduction

 

View supported engine types

Show engines;

 

2. InnoDB Storage Engine

 

The preferred engine for InnoDB Transaction-type databases. It supports transaction security tables, row locking, and foreign keys. After MySQL5.5.5, InnoDB is used as the default storage engine. The main features of InnoDB include:

A. InnoDB provides A transaction security storage engine capable of MySQL commit, rollback, and crash recovery. InnoDB is locked at the row level and provides a non-locked read similar to Oracle in the SELECT statement.

B. InnoDB is designed to handle the maximum performance of massive data volumes.

C. The InnoDB Storage engine is completely compatible with the MySQ server. the InnoDB Storage engine caches data and indexes in the main memory and maintains its own buffer pool.

D. InnoDB supports foreign key integrity constraints

E. InnoDB is used on many large database sites that require high performance.

 

InnoDB does not create a directory. When InnoDB is used, MySQL will create a 10 MB auto-extended data file named ibdata1 under the MySQL database directory, and two 5 MB log files named ib_logfile0 and ib_logfile1.

 

3. MyISAM storage engine

MyISAM is based on and extended by the ISAM storage engine. He is one of the longest-Used storage engines in the Web, data warehouse, and other environments. MyISAM has a high insert and query speed, but does not support transactions. In versions earlier than MySQL 5.5.5, MyISAM is the default storage engine. MyISAM has the following features:

A. Large files. Supported on file systems and operating systems that support large files

B. When the delete, update, and insert operations are mixed, dynamic-size rows produce fewer fragments.

C. The maximum number of indexes in each MyISAM table is 64, which can be changed by recompiling. The maximum number of columns for each index is 16.

D. The maximum key length is 1000 bytes. It can be changed through compilation.

E, BLOB, and TEXT columns can be indexed

F and NULL values are allowed in the index column.

G. All numeric key values are stored with high byte priority to allow a higher index compression

H. internal processing of an AUTO_INCREAMENT column in each table

I. data files and index files can be stored in different directories.

J. Each character column can have different character sets.

K. Tables with VARCHAR can have fixed or dynamic record lengths.

L maximum size of 64 kb for varchar and CHAR Columns

 

When you create a database using the MyISAM engine, three files are generated. The file name starts with the table name. The extension indicates the file type: frm file storage table definition. The data file extension is. MYD (MYData), and the index file extension is. MYI (MYIndex)

 

4. MEMORY storage engine

The MEMORY storage engine Stores Table data in the MEMORY to provide quick access for querying and referencing other data.

MEMORY features:

A. The size of each table in the MEMORY table is 32 indexes, each index has 16 columns, and the maximum key length of 500 bytes.

B. The MEMORY storage engine performs HASH and BTREE indexes.

C. You can have a non-unique key in the MEMEORY table.

D. Use a fixed record length format for the MEMORY table

E. MEMORY does not support BLOB and TEXT columns.

F. MEMORY supports AUTO_INCREMENT columns and indexes that can contain NULL values.

G. MEMORY tables are shared among all clients.

H. The content of the MEMORY table is stored in the MEMORY. The MEMORY is the MEMORY used by the MEMORY table and the server to share the internal table during query and processing.

I. when you no longer need the content of the MEMORY TABLE, you should DELETE FROM or TRUNCATE TABLE or DELETE the entire TABLE to release the MEMORY used by the MEMORY TABLE.