Shulou(Shulou.com)06/01 Report--

The following mainly brings you mysql data types and the method of using SQL structured query statements. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit mysql data types and use SQL structured query statement methods. All right, don't talk too much nonsense, let's just read the following.

MySQL storage engine, also known as table type:

MyISAM table: no transaction function, support table lock

.frm: table structure definition file

.MYD: table data file

.MYI: table index file

InnoDB table: supports transaction processing and row locks

.frm: table structure definition file

.ibd: tablespaces (including data and index files)

Common query commands for MySQL:

SHOW ENGINES; # to view the supported engines and status of the database.

SHOW TABLE STATUS LIKE 'user'\ G # View the attribute information of table user and display it vertically.

Mysqld-- help-- verbose # View the various related instructions supported by mysql

SHOW CHARACTER SET; # displays all supported character sets

SHOW COLLATION; # displays the collation under each character set

How the program language connects the data:

Dynamic SQL: establishing a connection to a database service through a function or method

Embedded SQL:

MySQL tool:

Client tools: mysql (login tool), mysqladmin (administrative tool), mysqldump (backup tool), mysqlimport,mysqlcheck

CVM-side tools: mysqld (start process), mysqld_safe (safe thread), mysqld_multi (multi-instance support)

MySQL profile: my.cnf

The configuration file startup sequence 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 as follows:

1. The mysql service has not been shut down before.

2. Data initialization failed

3. Data directory location error

4. Data directory permissions

The work of DBA:

Developing DBA: database design, SQL statements, stored procedures, stored functions, triggers

Manage DBA: installation, upgrade, backup, restore, user management, rights management, monitoring, performance analysis, benchmarking

MySQL data type:

Numerical type:

Exact numerical type: int (integer type) decimal (decimal type)

Approximate numerical type: float (single precision floating point type) double (double precision floating point type) real (real number type)

Character type:

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

Variable length characters: VARCHAR (NUM), VARBINARY (case sensitive) up to 65535

ENUM enumerated type: ENUM ('Achilles Magi, BBs, CCLs, DD') users can only select one of the enumerations.

SET enumerated string type

Date and time type:

Date date, time time, datetime date time, timestamp timestamp, year year

Data types need to have the following points:

1. The type of value stored

2. Storage space occupied

3. Is it variable length or fixed length

4. How to compare and sort

5. Whether it can be indexed

AUTO_INCREMENT auto-growth type, which needs to meet several points: 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 (20))

Create a test table that contains 2 fields. The modifiers for ID and Name,ID fields must be INT (integer) and UNSIGNED (unsigned).

AUTO_INCREMENT (autogrow), NOT NULL (non-empty), PRIMARY KEY (primary key).

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

Mysql > SELECT LAST_INSERT_ID ()

MySQL CVM variables:

There are two categories according to scope:

Global variable

SHOW GLOBAL VARIABLES LIKE'; view global variables

Session variable

SHOW [SESSION] VARIABLES LIKE''; view session variables

It can be divided into two categories according to the effective time:

Dynamically adjustable variables: can be modified instantly

Static variable:

Write it in the configuration file and pass it to mysqld through parameters

The effective mode of dynamically adjusting parameters:

Global variable: invalid for current session, valid only for newly established session

Session variable: effective immediately, but only for the current session

CVM variable: @ @ variable name

Display: SELECT

Setting: SET GLOBAL | SESSION variable name = 'value'

Mysql > SET GLOBAL sql_mode='strict_all_tables'; # set sql_mode to strict _ all_tables

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT @ @ global.sql_mode; # View the global variables of sql_mode

+-+

| | @ @ global.sql_mode |

+-+

| | STRICT_ALL_TABLES |

+-+

1 row in set (0.00 sec)

Mysql > SELECT @ @ sql_mode; # View the session variables of sql_mode

+-+

| | @ @ sql_mode |

+-+

| | |

+-+

1 row in set (0.00 sec)

For the above about the mysql data type and the use of SQL structured query statement method, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.