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

This article mainly introduces what data types Mysql has and how to use SQL structured query statements, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand what data types Mysql has and how to use SQL structured query statements.

I. brief introduction

Developed by MySQL AB, it is the most popular open source SQL database management system with the following main features:

1. It is a database management system.

2. It is an associated database management system.

3. It is a kind of open source software, and there are a lot of shared MySQL software available

4. MySQL database cloud servers are fast, reliable and easy to use.

5. MySQL CVM works in client / CVM mode, or in embedded system

The InnoDB storage engine saves InnoDB tables in a tablespace that can be created by several files. In this way, the size of the table can exceed the maximum capacity of individual files. Tablespaces can include raw disk partitions, making large tables possible. The maximum capacity of the tablespace is 64TB.

2. MySQL storage engine, also known as table type

2.1. 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)

2.2. 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

2.3. How the program language connects the data:

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

Embedded SQL:

2.4. 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 (supports multiple instances)

2.5. 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

2.6. The reasons for MySQL startup failure are as follows:

1. The mysql service did not shut down killall mysqld before.

2. Data initialization failed

3. Data directory location error

4. Data directory permissions

2.7. 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

2.8. 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

2.9. 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

Eg: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 ()

2.10. 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)

After reading the above about what data types Mysql has and how to use SQL structured query statements, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.