MySQL Self-study article (i)

First, startMySQLService and LoginMySQLDatabase

First, start the MySQL service

1. Stand-alone Start menu, select "Run" from the pop-up menu to open the "Run" dialog box

2. Enter "services.msc" in the text box, click the "OK" button to open the Windows "Service Manager"

3.DOS Enter "net start MySQL" to start the service. Enter "net stop mysql" to shut down the service

Second, log in to MySQL Database

1. Log on as Windows

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

the parameter after-H is the server address

the parameter after-U is the database user name

The parameter after-P is the user's login password

2. log in with MySQL Command line Client

Click "Start"->"All Programs"->"MySQL Server"->" MySQL Command line cliennt"

Enter Password Entry window

3.MySQL Graphical management tool login Database

Third, configure the Path variable

When you log on through Windows , you can log on to the MySQL database because the MySQL bin is configured The directory is in the Path variable.

steps to configure Path :

Right-click on "My Computer"->"Properties"->"System Properties"->"Advanced"->"Environment variables"->"Path"->"Edit"->will beMySQLof thebinTable of Contents join->"OK"

Second, change the configuration of MySQL

1. Configuration Wizard to change configuration

Enter the MySQL installation bin directory to start the MySQLInstanceConfig.exe file

2. Manually change the configuration

Configuring The my.ini file under the MySQL installation directory

TwoMySQLBasic operations of the database1, create a database

Once the MySQL installation is complete, you will automatically create several necessary databases in its data directory, which you can use to SHOW database; statement to view all currently existing databases.

To create a database statement:

CREATE DATABASE database_name;

Example:

Create a test database test_db

CREATE DATABASE test_db;

After creation, you can use show create database to view the DB definition

2, delete database

Deleting a database deletes the existing database from the disk, and the data in the database will be clear together.

To delete a database statement:

DROP DATABASE database_name;

Example:

Deleting a test database test_db

DROP DATABASE test_db;

Third, the database storage engine

The database storage engine is the database underlying software component, and the database management system uses the data engine to create, query, update, and delete data operations. Different storage engines provide different storage mechanisms, indexing techniques, lock levels, and other features that use different storage engines and can also get specific functionality.

1,MySQLIntroduction to the storage engine

To view supported engine types

SHOW ENGINES;

2,InnoDBStorage Engine

InnoDB the preferred engine for the transaction type database, supports transaction security tables, supports row locking and foreign keys. after MySQL5.5.5 ,InnoDB as the default storage engine,InnoDB main features are:

A, InnoDB provides a transactional secure storage engine for MySQL commit, rollback, and crash resilience. InnoDB locks the row level and also provides a non-locking read similar to Oracle in the SELECT statement.

B, InnoDB is designed to handle the maximum performance of large amounts of data

C, the InnoDB storage engine is fully associated with the mysq Server,and theInnoDB Storage Engine maintains its own buffer pool by caching data and indexes in main memory.

D, InnoDB support foreign key integrity constraints

E, InnoDB is used in many large database sites that require high performance

InnoDB does not create a directory, when using InnoDB ,mysql will create a name in the MySQL database directory under ibdata1 of the 10MB the size of the auto-extended data file, and two named Ib_logfile0 and the Ib_logfile1 of the 5MB the size of the log file.

3,MyISAMStorage Engine

The MyISAM is based on the ISAM storage engine and extends it. He is One of the most used storage engines in the WEB, Data Warehouse, and other environments. MyISAM has a high insertion and query speed, but does not support transactions. in Previous versions of MySQL5.5.5,MyISAM was the default storage engine. The main features of MyISAM are:

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

B, when the deletion and update and insert operations mixed use, dynamic size of the line to produce less fragmentation.

C, the maximum number of indexes per MyISAM table is $, which can be changed by recompiling. The maximum number of columns per index is three.

D, the maximum key length is The number of bytes. Can be changed by compiling.

E, BLOB, and TEXT columns can be indexed

F, the NULL value is allowed in the index column.

G, all numeric key values are stored in high-byte precedence to allow a higher index compression

H, internal processing of one auto_increament column per table

I, data files and index files can be placed in different directories

J, each character column can have a different character set

K, the table with VARCHAR can be fixed or dynamic record length

L, VARCHAR, and CHAR columns can be as large as 64KB

Creating a database using the MyISAM engine will result in 3 files. The name of the file begins with the name of the table, and the extension indicates the file type:frm File store table definition, data file extension . MYD (MYData), the index file name extension is . MYI (Myindex)

4,MEMORYStorage Engine

The memory storage engine stores the data in a table in the RAM, providing quick access to queries and references to other data.

MEMORY Main Features:

A, the MEMORY table can be a large number of indexes per table , each index , and The maximum key length of five bytes

B, memory storage Engine Execution HASH and BTREE Index

C, can have non-unique keys in the memeory table

D, MEMORY table using a fixed record length format

E, MEMORY does not support BLOB and TEXT columns

F, MEMORY supports auto_increment columns and indexes on columns that can contain NULL values

G, MEMORY tables are shared among all clients

H, the Memory table content is in memory, the memories table and the server in the query processing idle, the creation of internal table sharing

I, when the contents of the memory table are no longer needed, to release the memories used by the storage table, you should perform a DELETE from or TRUNCATE table or delete the entire sheet.

MySQL Self-study article (i)