MySQL installation and use (ii)

Operating system: CentOS release 5.10 (Final)

MySQL version: 5.1.72-community

First, MySQL database simple migration (backup and restore)

Reference: Http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#backup-strategy-example

Sometimes we want to migrate the database, including the database structure and the data itself, from host A to Host B, which is relatively straightforward to use the following methods.

1, run on host a.

shell> mysqldump--single-transaction--all-databases > Backup_sunday_1_pm.sql

2. Copy the Backup_sunday_1_pm.sql to Host B and enter the directory.

shell> MySQL < Backup_sunday_1_pm.sql

Second, Structured Query Language (structured query Language) SQL

Data definition language,ddl, used to create various object tables, views, indexes, and synonyms in a database

Data Control LANGUAGE,DCL, which is used to grant or reclaim some kind of privilege to access a database, and to control the time and effect of database manipulation, monitor the database, etc.

Data manipulation LANGUAGE,DML, which provides query, modification, and deletion of database database data through several statements

1. Data Definition language

Create creates database objects, database objects including databases, tables, and so on.

Create statement usage	The Created object
CREATE DATABASES < database name >	Create a database
CREATE table < table name > (< table definition >)	Create a table

The ALTER statement provides a mechanism for changing the definition of an object.

Change statement syntax	Role
ALTER Database < databases name > < file specification >	Change the file that stores the database
ALTER TABLE < table name > < change definition >	Change the definition of a table

The drop statement is used to delete a database object. Grammar:

CREATE < object types >< object names >

2. Data Control Language

Database Control Language (DCL) statements are used to change the permissions associated with a library user or role.

Statement	Description
GRANT	Create an entry in the security system that enables users in the current database to process data in the current database or execute specific Transact-SQL statements
DENY	Create an entry in the security system to deny permissions to the security account in the current database and prevent the security account from inheriting permissions as group or role memberships
REVOKE	Revoke a previously granted or denied permission on a user in the database

By default, only the Systemadmin, dbcreator, db_owner, or db_securityadmin roles can execute DCL statements.

3. Data Manipulation language

Database manipulation Language (DML) statements can manipulate data in a database. By using DML statements, you can change data or retrieve information. DML statements include:

• SELECT
• INSERT
• UPDATE
• DELETE

Three, column (data) type 1, numeric type

· bit[(M)]

· tinyint[(M)] [UNSIGNED] [Zerofill]

· BOOL,BOOLEAN

· smallint[(M)] [UNSIGNED] [Zerofill]

· mediumint[(M)] [UNSIGNED] [Zerofill]

· int[(M)] [UNSIGNED] [Zerofill]

· integer[(M)] [UNSIGNED] [Zerofill]

· bigint[(M)] [UNSIGNED] [Zerofill]

· float[(M,D)] [UNSIGNED] [Zerofill]

· double[(M,D)] [UNSIGNED] [Zerofill]

· DOUBLE precision[(M,D)] [UNSIGNED] [Zerofill], real[( M, D)] [UNSIGNED] [Zerofill]

· FLOAT (p) [UNSIGNED] [Zerofill]

· decimal[(M[,D])] [UNSIGNED] [Zerofill]

· dec[(M[,D])] [UNSIGNED] [Zerofill], numeric[( M [,D])] [UNSIGNED] [Zerofill] , fixed[(M[,D])] [UNSIGNED] [Zerofill]

2. Date and Time type

· DATE

· DATETIME

· timestamp[(M)]

· Time

· year[(2|4)]

3. String type

· [National] CHAR (M) [binary| ASCII | UNICODE]

· CHAR

· [National] VARCHAR (M) [BINARY]

· BINARY (M)

· VARBINARY (M)

· tinyblob

· tinytext

· blob[(M)]

· text[(M)]

· mediumblob

· mediumtext

· longblob

· longtext

· ENUM ('value1', 'value2',...)

· SET ('value1', 'value2',...)