MySQL Database model under Linux, Managing table indexes

MySQL Database model under Linux, Managing table indexes

MySQL database and its model under 1.linux

1.1.SHOW TABLE STATUS like ' user ' \g #查看表的存储引擎

SHOW ENGINES; #查看数据库支持的存储引擎

Client tools: MySQL, Mysqladmin, mysqldump, Mysqlimport, Mysqlcheck

Server-side 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 Files--~/.my.cnf

# mysqld--help--verbose

1.3.hostname.err, error log: Previous service not closed, data initialization failed, data directory location error, data directory permissions problem

1.4. Data type: Type of value to be deposited, storage space occupied, fixed or variable length, comparison and sequencing;

1.5. View character set and collation commands:

mysql> SHOW CHARACTER SET;

Mysql> SHOW COLLATION;

1.6.auto_increment: integer non-null 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 Server variables by scope: into global variables and session variables

SHOW GLOBAL VARIABLES #查看全局变量

SHOW [SESSION] VARIABLES #查看会话变量

MySQL server variables fall into two categories in effective time: dynamic and static

Dynamic : can be modified instantly

Static: Written in config file, passed to mysqld by parameter

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;

1.8. Server variable: @@ 变量 name

Display: SELECT @ @global. Sql_mode;

SELECT @ @session. Sql_mode;

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

2. Management table:

2.1. Create a 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 (a) Not NULL, age TINYINT NOT NULL) ENGINE [=] Engine_name

CREATE TABLE tb2 (id INT UNSIGNED NOT NULL auto_increment, Name CHAR (a) 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 (a) not null);

2.3.2. Inserting table data:

INSERT into courses (Course) VALUES (' CCNA '), (' CCNP '), (' RHCA ');

2.3.3. Index of the display table:

SHOW INDEXES from Tb_name;

2.4. Query the data from other tables and create a new table with it: (Note that the table-style definition of such a method differs from the source table)

CREATE TABLE courses_new SELECT * from courses WHERE ID <=2;

2.5. Create an empty table as a template from another table; (This method table-style definition is identical to the source table)

CREATE TABLE courses_good like Courses;

3. Index

3.1. Add an index:

ALTER TABLE courses_good ADD UNIQUE KEY (Course);

3.2. Modify the field name:

ALTER TABLE courses_good MODIFY CID TINYINT UNSIGNED not NULL auto_increment;

ALTER TABLE courses_good Change couse Course VARCHAR (a) not NULL;

3.3. Add Field Name:

ALTER TABLE courses_good ADD startdate Date default ' 2016-06-29 ';

3.4. Modify the table name:

ALTER TABLE Courses_good RENAME to Courses_new;

RENAME TABLE courses_new to Courses_good;

3.5. Delete the table:

DROP TABLE courses_new;

3.6. Establish a relationship between tables:

CREATE TABLE Classroom (SID int UNSIGNED not NULL auto_increment PRIMARY key,name VARCHAR (+), CID int not null);

INSERT into Classromm (name,cid) VALUES (' Jerry ', 3), (' Tom ', 1);

SELECT * from Classromm;

SELECT * from courses;

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/83/6D/wKiom1dzQAeCoFw5AAFgjc17qKc854.jpg-wh_500x0-wm_3 -wmp_4-s_4159688446.jpg "title=" 1.jpg "alt=" Wkiom1dzqaecofw5aafgjc17qkc854.jpg-wh_50 "/>

SELECT name,course from classroom,courses WHERE classroom. Cid=courses.id;

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/83/6D/wKiom1dzQFKjFd16AAC0_fk57eY261.jpg-wh_500x0-wm_3 -wmp_4-s_443674764.jpg "title=" 2.jpg "alt=" Wkiom1dzqfkjfd16aac0_fk57ey261.jpg-wh_50 "/>

In the absence of a foreign key reference: the classroom table can arbitrarily insert data as follows:

INSERT into Classroom (name,cid) VALUES (' Willow ', 6);

DELETE from classroom WHERE cid=6;

When referencing foreign keys: the classroom table can only be inserted against data that is present in the reference table and cannot be arbitrarily inserted into the data

The foreign key must be a transaction-enabled storage engine, such as InnoDB, or an error will be

Foreign key Reference field type parameters also need to be consistent

ALTER TABLE courses Engine=innodb;

ALTER TABLE Classroom ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses (ID);

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/83/6D/wKioL1dzR42i9F4HAAKnwOktlbw776.jpg-wh_500x0-wm_3 -wmp_4-s_221297484.jpg "title=" 3.jpg "alt=" Wkiol1dzr42i9f4haaknwoktlbw776.jpg-wh_50 "/>

SHOW INDEXES from classroom;

The data that is not in the referenced courses table is then inserted into the classroom table, and an error is displayed.

INSERT into Classroom (name,cid) VALUES (' Willow ', 6);

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/83/6D/wKioL1dzUmDTuaaSAAIgecxs_8k353.jpg-wh_500x0-wm_3 -wmp_4-s_3754273954.jpg "title=" 4.jpg "alt=" Wkiol1dzumdtuaasaaigecxs_8k353.jpg-wh_50 "/>

3.7. Create an index:

CREATE INDEX index_on_name on Classroom (name) USING BTREE;

CREATE INDEX index_on_name on Classroom (name (5) DESC) USING BTREE;

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/83/6E/wKiom1dzVjeQnsNYAAJN6E2hMMQ718.jpg-wh_500x0-wm_3 -wmp_4-s_3942279414.jpg "title=" 5.jpg "alt=" Wkiom1dzvjeqnsnyaajn6e2hmmq718.jpg-wh_50 "/>

3.8. Delete the index:

DROP INDEX index_on_name on classroom;

This article is from the "Xavier Willow" blog, please be sure to keep this source http://willow.blog.51cto.com/6574604/1794091

MySQL Database model under Linux, Managing table indexes