Linux commands: MySQL series of three--mysql data types and SQL Structured query statements using

The MySQL storage engine, also known as the table type:

MyISAM table: No transaction processing function, support table lock

. frm: Table structure definition file

. MYD: Table Data file

. MYI: Table Index file

InnoDB table: Support transaction function, support row lock

. frm: Table structure definition file

. IBD: Table space (contains data and index files)

Common query commands for MySQL:

SHOW ENGINES; #查看数据库支持的引擎及状态.

Show TABLE STATUS like ' user ' \g #查看表user的属性信息, \g vertical display

Mysqld--help--verbose #查看mysql支持的各种相关指令

SHOW CHARACTER SET; #显示所有支持的字符集

SHOW COLLATION; #显示各个字符集下的排序规则

How the program language connects data:

Dynamic SQL: Establishes a connection to the database service through a function or method.

Embedded SQL:

MySQL Tools:

Client tools: MySQL (login tool), mysqladmin (management tool), mysqldump (Backup utility), Mysqlimport,mysqlcheck

Server-side tools: mysqld (Boot process), Mysqld_safe (thread), Mysqld_multi (multi-instance support)

MySQL configuration file: my.cnf

Configuration file boot order is,/etc/my.cnf-->/etc/mysql/my.cnf-$MYSQL _home/my.cnf---

--default-extra-file=/path/to/somefile-->~/.my.cnf

The reasons for MySQL startup failure are:

1. The MySQL service was not closed before

2. Data initialization failure

3, Data Directory location error

4, Data Directory permissions issues

What the DBA is doing:

Development DBA: Database design, SQL statements, stored procedures, stored functions, triggers

Manage DBAs: Install, upgrade, backup, restore, user management, rights management, monitoring, performance analysis, benchmarking

MySQL data type:

Numeric type:

Exact numeric type: Int (integer) decimal (decimal type)

Approximate numeric type: float (single-precision floating-point type) double (double-precision floating-point type) real (real)

Character type:

Fixed-length character type: CHAR (NUM), BINARY (case-sensitive) up to 255 characters long

Variable length character type: VARCHAR (NUM), VARBINARY (case-sensitive) up to 65535

enum enum type: Enum (' A ', ' BB ', ' CC ', ' DD ') user can only select one in the enumeration

Set enumeration String type

Date and Time type:

Date, time, datetime, timestamp timestamp, year

data types need to have the following points:

1. Type of value deposited

2, occupy the storage space

3. Is it long or long?

4. How to compare and sort

5. Is it possible to index

Auto_increment automatic growth type, to meet the following: Must be integer, non-empty, meta-symbol, primary key or unique key

Usage: CREATE TABLE Test (ID INT UNSIGNED auto_increment not NULL PRIMARY key,name CHAR )

Create a test table with 2 field IDs and the Name,id field modifier must be int (integer), UNSIGNED (unsigned)

Auto_increment (autogrow), NOT null (non-NULL), PRIMARY key (primary key).

The Name field type is char (20) (The fixed-length character length is 20).

mysql> SELECT last_insert_id ();

MySQL Server variables:

by scope, divided into two categories:

Global Variables

Show global VARIABLES like "; View Globals

Session variables

Show [session] VARIABLES like "; View Session Variables

By effective time, divided into two categories:

Dynamically adjustable variables: can be modified instantly

Static variables:

Written in config file, passed to mysqld by parameters

Dynamic adjustment of the parameters of the effective way:

Global variable: Invalid for the current session, only valid for new session;

Session variable: Immediate effect, but only valid for the current session;

server variable: @@ 变量 name

Display: SELECT

Setting: Set global| SESSION Variable name = ' value '

mysql> SET GLOBAL sql_mode= ' strict_all_tables '; #设定sql_mode的值为strict_all_tables

Query OK, 0 rows Affected (0.00 sec)

mysql>  SELECT @ @global. Sql_mode; #查看sql_mode的全局变量

+-------------------+

| @ @global. Sql_mode |

+-------------------+

| Strict_all_tables |

+-------------------+

1 row in Set (0.00 sec)

mysql>  SELECT @ @sql_mode; #查看sql_mode的会话变量

+------------+

| @ @sql_mode |

+------------+

| |

+------------+

1 row in Set (0.00 sec)

This article is from the "Learn Linux history" blog, please be sure to keep this source http://woyaoxuelinux.blog.51cto.com/5663865/1918247

Linux commands: MySQL series of three--mysql data types and SQL Structured query statements using