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

Profile parameters

Server parameters / variables: setting the running characteristics of MySQL

Display:

Mysql > SHOW GLOBA | [SESSION] VARIABLES [LIKE clause]; LIKE filtering

State (statistical) parameters / variables: save statistics or state data in MySQL operation

Mysql > SHOW GLOBA | [SESSION] STATUS [LIKE clause]

The method of displaying the setting value of a single variable: exact match

Mysql > SELECT @ @ [global. | session.] system_var_name

%: matches any character of any length

_: match any single character

Variable / parameter level:

Global (GLOBA): set the default for all sessions; only root permissions can be modified

Session (SESSION): related to a single session; session establishment inherits globally

How to adjust the server variable:

Runtime modifications:

Mysql > HELP SET

SET [GLOBAL | SESSION] system_var_name = expr

SET [@ @ global. | @ @ session. | | @] system_var_name = expr |

Modify through the configuration file before startup:

Security initialization after installation:

Mysql_secure_installation

Delete anonymous user administrator settings password

Parameters that are often modified before running:

The innodb_file_per_table=ON innodb engine uses a single tablespace per table

Skip_name_resolve=ON prohibits dns parsing

...

SQL:ANSI SQL standard

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03,... (it is also graded. The higher the level, the stricter the limit, the lower the performance

Data type of MySQL:

Character type

Numerical type

Date and time type

Built-in type

Character type:

CHAR (#), BINARY: fixed length; CHAR is not case-sensitive, while BINARY is case-sensitive; poor performance

VARCHAR (#), VARBINARY: variable length

TEXT:TINYTEXT (1 byte), TEXT (2), MEDIUMTEXT (3), LONGTEXT (4) text

BLOB:TINYBLOB (255bytes), BLOB (65k), MEDIUMBLOB (16m), LONGBLOB (4G) binary large objects

Numerical type:

Floating point: approximate

FLOAT

DOUBLE

REAL

BIT

Integers: precise

INTEGER:TINYINT (1), SMALLINT (2), MEDIUMINT (3), INT (4), BIGINT (8)

DECIMAL

Date and time type:

Date: DATE

Time: TIME

Date j time: DATETIME

Timestamp: TIMESTAMP

Year: YEAR (2), YEAR (4)

Built-in:

ENUM: enumerating

ENUM ('Sun','Mon','Tue','Wed') can only fill in one of the data defined by the enumeration

SET: assembles various combinations in a pile of characters

Type modifier:

Character type:

NOT NULL is not empty

NULL can be empty

DEFALUT 'STRING' default value

CHARACET SET' CHARSET' character set format

COLLATION 'collocation' collation

Integer:

NOT NULL

NULL

DEFALUT value cannot be in quotation marks

AUTO_INCREMENT self-increasing

UNSIGNED unsigned integer

Date and time type: NOT NULL, NULL, DEFAULT

* * *

SQL MODE: define settings such as the response behavior of mysqld to constraints and other violations; (such as data type error or overflow)

Commonly used MODE:

Traditional restriction mode of TRADITIONAL

STRICT_TRANS_TABLES supports tabular restrictions on transaction operations.

STRICT_ALL_TABLES restricts all tables

Modification method:

Mysql > SET GLOBAL sql_mode='MODE'

Mysql > SET @ @ global.sql_mode='MODE'

Insert a value greater than 255 again and an error will be reported.

This is valid. If the restart fails, you need to modify the value in the configuration file.

* * *

Database:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name

Create database [if it does not exist] Database name specifies character set collation

ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name COLLATE [=] collation_name

Modify the collation of the database name character set

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Delete database [if present] database name

Table:

CREATE

(1) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name # TEMPORARY temporary Table

(create_definition,...) # Field definition format

[table_options] # Table options

[partition_options] # Table partitioning options

CREATE TABLE [IF NOT EXISTS] tble_name (col_name data_type | INDEX | CONSTRAINT)

Example: MariaDB > CREATE TABLE student (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,name CHAR (50) NOT NULL, age TINYINT UNSIGNED,INDEX (name))

(2) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name copy table data

[(create_definition,...)]

[table_options]

[partition_options]

Select_statement

Create the table directly and insert the results of the query statement into the newly created table

Example: MariaDB [mydb] > CREATE TABLE users (Name VARCHAR, Host VARCHAR, Passwd CHAR) SELECT User AS Name,Host,Password As passwd FROM mysql.user

If the column name of the new table does not correspond to the original, you must specify the name old_name As new_name

Query the data in mysql.user and import it into the new (users table)

(3) creation of CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name replication table structure

{LIKE old_tbl_name | (LIKE old_tbl_name)}

Example: CREATE TABLE user LIKE mysql.user

View table data: SELECT * FROM user

View table structure: DESC user

Copy the structure of an existing table to create a new empty table; the data will not be replicated

***************

Table_options:

ENGINE [=] engine_name storage engine

View all supported storage engines:

Mysql > SHOW ENGINES

View the storage engine for the specified table:

Mysql > SHOW TABLE STATUS LIKE clause

ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}

Line format

*************************

DROP:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]

ALTER:

ALTER TABLE tbl_name

[alter_specification [, alter_specification]...]

Modifiable content: alter_specification

(1) table_options

(2) add definition: ADD

Fields, field collections, indexes, constraints

(3) modify fields:

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name] modify the name and options

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] can only change the name

(4) delete operation: DROP

Fields, indexes, constraints

Table rename:

RENAME [TO | AS] new_tbl_name

View the table structure definition:

DESC tbl_name

View the table definition:

SHOW CREATE TABLE tbl_name

View table property information:

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

Example: SHOW TABLESTATUS [FROM mysql] LIKE user; does not specify a table to represent the current table

* *

Index:

Create:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)

Create as one | full text | Spatial index name index type

Example: CREATE INDEX name_on_students ON students (Name)

View:

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]

Example: SHOW INDEX FROM students [WHERE Column_name='Name']

Delete:

DROP INDEX index_name ON tbl_name

Example: DROP INDEX name_on_students ON students

* *

Index type:

Clustered index, nonclustered index: see if the index exists with the data

Primary key index, secondary index

Dense index, sparse index: whether every data item is indexed; dense index indicates that the data corresponding to each index is sparse, on the contrary, the general first-level index is dense, second-and third-level index may not be dense.

BTREE (B+) balanced tree index

HASH

R Tree spatial index

FULLTEXT full-text index

BTREE: left prefix

EXPLAIN: analyze the execution path of the query statement

View: VIEW

Virtual table: stored SELECT statement; (hides fields from some users)

Create:

CREATE VIEW view_name [(column_list)] AS select_statement

Modify:

ALTER VIEW view_name [(column_list)] AS select_statement

Delete:

DROP VIEW [IF EXISTS] view_name [, view_name]...

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.