MySQL Finishing (i)

　　First, the development stage of data management

　　　　Human management stage → file system stage → database system phase

Second, the database management system provides the functions

(1) Data Definition Language DDL: Provides data definition language definition database and various objects, define the data integrity constraints and confidentiality restrictions and other constraints.

(2) Data Manipulation language DML: Provides data manipulation language operation data, mainly divided into two categories: Search (query) and update (INSERT, delete, update)

(3) Data Control Language DCL: Provide Data Control Language control database integrity limitations, data security control.

Third, why use MySQL

　　　　MySQL was developed by the original MySQL AB company, has been acquired by Sun, is currently the most popular open source database management system in IT industry, and it is also a support multi-threaded high concurrency multiuser relational database management system.

(1) Functionality: Meet general business needs

(2) Ease of use: compared to large-scale commercial database management system, MySQL for ordinary users, it is easy to use, small installation package, create a database and other simple operation.

(3) Performance: In the process of testing and comparing various database TPCC values (Tpcc-mysql is the benchmark tool developed by Percona Company) in the authoritative third-party evaluation agencies, MySQL has always been very good performance, and in all other commercial common database management system, Only Oracle database can be compared with one of the higher

(4) Reliability: Ranked in the top 10 of the world's large web site, there are some of the business is running in the MySQL database environment, such as Yahoo, Google and so on.

Overall, the MySQL database has been pursuing three principles in its development process: simple, efficient and reliable.

Iv. MySQL Related commands

DOS window connection MySQL	Mysql–h 127.0.0.1–u Root-p
Show all databases	show databases;
Create a database	Create DATABASE KK;
Using the specified database	Use KK;
Deleting a database	Drop database KK;
Display the list of storage engines	Show engines;
View the default storage engine	Show variables like ' storage_engine% '
Create a table	CREATE TABLE Table name ( ID int, Name varchar (20), Age float );
View Table Definition	Describe table name
Modify Table Name	ALTER TABLE cousin name rename new table name
Add a field at the last position of the table	ALTER TABLE name Add field name fields type definition
Add a field to the first position in a table	ALTER TABLE name Add field Name fields type define first
Add a field after a specified field in a table	ALTER TABLE name Add field Name field type define after specified fields
Delete a field	ALTER TABLE name drop field name
Modifying a field definition	ALTER TABLE name modify field moniker type definition
Modify field names	ALTER TABLE name change old field name new field name Old field data type definition
Modify field names and data types	ALTER TABLE name change old field name new field name new field data type definition
Modify field position	ALTER TABLE name modify field name data type First (field is adjusted to position one) Alter Table name Modify field name 1 data type after field name 2

V. MySQL storage engine and data type

　　　　1.1. MySQL introduced the concept of storage engine, because the storage engine is introduced in the form of plug-in, it is necessary to select the appropriate storage engine according to the actual application and domain, if you want to create a table, you need to understand the data type, and the storage engine determines the data types that can be stored in the table.

One of the engines has the following parameters:

Engine:innodb

Support:default

Comment:supports transactions, Row-level locking, and foreign keys

Transactions:yes

Xa:yes

Savepoints:yes

(1) Engine: Storage Engine name

(2) Support: Indicates whether MySQL supports the storage engine, default means

(3) Comment: A brief comment on the storage engine

(4) Transactions: Indicates whether the storage engine supports transactions

(5) XA: Indicates whether distributed storage supported by the storage engine complies with the XA specification

(6) Savepoints: Indicates whether the stored procedure supports a savepoint in a transaction

　　　　　1.2. The MySQL storage engine supports data types such as Integer type, floating-point type, fixed-point number type and bit type, date and time type, and string type.

(1) Storing decimal data, you can choose float or double, need to be accurate to 10 digits after the decimal point, you want to select a double type.

(2) The fixed-point number type (the decimal position of the number participating in the operation is fixed) DEC (m,d) and decimal (M,D) are more accurate than double, valid range is M (total data length) and D (after the decimal point length)

(3) The use of the month and day is generally a date type, and the month Day is the datetime type. The timestamp type is generally used when the time to insert or update frequently is the current time of the system. Time division seconds is the time type. Year in years.

(4) storing a small number of strings can be used char and varchar type, if necessary to change frequently, choose the varchar type.

(5) The Blob is very similar to the text type, except that it can store binary data (Pictures, audio files), while the latter only stores character data.

(6) binary and varbinary are very similar to char and varchar string types, except that the former can store binary data (Pictures, audio files), while the latter only stores character data.

MySQL Finishing (i)