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

This article is mainly about the mysql database model, management tables and indexes under linux. If you are interested, let's take a look at this article. I believe that after reading the mysql database model under linux, the management tables and indexes are of some reference value to everyone.

Mysql Database and its Model under 1.linux

1.1.SHOW TABLE STATUS LIKE 'user'\ G # View the storage engine of the table

SHOW ENGINES; # View the storage engines supported by the database

Client tools: mysql, mysqladmin, mysqldump, mysqlimport, mysqlcheck

CVM tools: mysqld, mysqld_safe, mysqld_multi

1.2.my.cnf check order:

/ etc/my.cnf-- > / etc/mysql/my.cnf-- > $MYSQL_HOME/my.cnf-- >

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

# mysqld-help-verbose

1.3.hostname.err, error log: previous service did not shut down, data initialization failed, data directory location error, data directory permission problem

1.4. Data type: type of value stored; storage space occupied; fixed or variable length; comparison and sorting; whether it can be indexed

1.5. View character set and collation commands:

Mysql > SHOW CHARACTER SET

Mysql > SHOW COLLATION

1.6.AUTO_INCREMENT: integer non-empty unsigned primary key or unique key

CREATE TABLE test (ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, Name CHAR (20)

Mysql > SELECT LAST_INSERT_ID ()

1.7.MySQL CVM variables are divided into global variables and session variables by scope

SHOW GLOBAL VARIABLES # View global variables

SHOW [SESSION] VARIABLES # View session variables

MySQL CVM variables are divided into two categories according to their effective time: dynamic and static

Dynamic: can be modified instantly

Static: written in the configuration file and passed 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

1.8. CVM variable: @ @ variable name

Display: SELECT @ @ global.sql_mode

SELECT @ @ session.sql_mode

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

two。 Management tables:

2.1. Create the database:

CREATE DATABASE IF NOT EXISTS students CHARACTER SET 'gbk' COLLATE' gbk_chinese_ci'

2.2. Delete the database:

DROP DATABASE [IF EXISTS] db_name

2.3. Create a table:

CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination, constraint)

2.3.1. Create an empty table directly:

CREATE TABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR (20) NOT NULL, Age TINYINT NOT NULL) ENGINE [=] engine_name

CREATE TABLE tb2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR (20) NOT NULL, Age TINYINT NOT NULL, PRIMARY KEY (id), UNIQUE KEY (name), INDEX (age))

CREATE TABLE courses (ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Course VARCHAR (50) NOT NULL)

2.3.2. Insert table data:

INSERT INTO courses (Course) values ('CCNA'), (' CCNP'), ('RHCA')

2.3.3. Displays the index of the table:

SHOW INDEXES FROM tb_name

2.4. Query data from other tables and create new tables from them: (note that the table format definition of this method is different from that of the source table)

CREATE TABLE courses_new SELECT * FROM courses WHERE ID

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.