MySQL (iii)-database operations

Database Storage Engine

To set the default storage engine:

vim /etc/my.conf[mysqld]default_storage_engine= InnoDB;

To view the storage engines supported by MySQL:

show engines;

To view the current default storage engine:

show variables like ‘%storage_engine%‘;

View the storage engines used by all tables in the library

Show table status from db_name;

View the storage engine for the specified table in the library

show table status like ‘tb_name‘;show create table tb_name;

To set the storage engine for a table:

CREATE TABLE tb_name(... ) ENGINE=InnoDB;ALTER TABLE tb_name ENGINE=InnoDB;

Database operation naming rules:

必须以字母开头可包括数字和三个特殊字符（# _ $）不要使用MySQL的保留字同一database(Schema)下的对象不能同名

Create a database

create database if not exists newdb;       if not exists：条件判断，如果不存在就创建数据库创建一个默认字符集为utf8的数据库create database db default character set utf8;create database db default collate ‘utf8_general_ci‘;

View the default character set when a database is created

show create database newdb;+----------+------------------------------------------------------------------+| Database | Create Database                                                  |+----------+------------------------------------------------------------------+| newdb    | CREATE DATABASE `newdb` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+------------------------------------------------------------------+

View support for all character sets

SHOW CHARACTER SET;

View support for all collations

SHOW COLLATION;

Modify Database

语法：ALTER database 数据库名 修改的属性 ...修改指定数据库默认字符集alter database db default character set utf8;

Delete database (delete operation cannot be recovered)

drop database if exists newdb;               if exists：条件判断，如果不存在就创建数据库

Data Table Operations View table structure

desc 表名;

View table Status

show table status from db

Create a table

Create a new table

CREATE TABLE [IF NOT EXISTS] ‘表名‘ (字段名1 类型 修饰符, 字段名2 类型 修饰符, ...)

Create a new table based on an existing table, the primary key is lost

create table 新表名 select * from 旧表名;

INSERT into a new table based on existing table data, the new table structure must be consistent with the old table

insert into db select * from mysql.user;

所有类型：?NULL 数据列可包含NULL值?NOT NULL 数据列不允许包含NULL值?DEFAULT 默认值?PRIMARY KEY 主键?UNIQUE KEY 唯一键?CHARACTER SET name 指定一个字符集?数值型?AUTO_INCREMENT 自动递增，适用于整数类型?UNSIGNED 无符号

Example

create table students     -> (id int unsigned not null primary key,    -> name varchar(20) not null,    -> age tinyint unsigned);desc students;+-------+---------------------+------+-----+---------+-------+| Field | Type                | Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| id    | int(10) unsigned    | NO   | PRI | NULL    |       || name  | varchar(20)         | NO   |     | NULL    |       || age   | tinyint(3) unsigned | YES  |     | NULL    |       |+-------+---------------------+------+-----+---------+-------+取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)

Table Aliases

select user as 用户,host as 主机 from mysql.user;+--------+-----------+| 用户   | 主机      |+--------+-----------+| hunk3  | %         || root   | 127.0.0.1 |

Create a pure Index

Advantages: Improve Query speed

Cons: Takes up extra space and affects insertion speed

Index implementation in the storage engine

View indexes on a table

SHOW INDEXES FROM [db_name.]tbl_name;

Found the Rafah record 4321 lines before we could get the index.

explain select * from city where NAME=‘RAFAH‘\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: city         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4321             查找了多少行        Extra: Using where

Create an index

Create index name on table name (field to be indexed) CREATE INDEX index_name on city (name); *************************** 3.  Row *************************** table:city non_unique:1 key_name:index_name Here is the index name seq_in_index:1 Column_name:name collation:a cardinality:184 sub_part:null packed:null Null:Index_type:BTR EE comment:index_comment: At the same time, when looking at the table structure, found in the name of the key bar appears Muldesc City, +-------------+----------+------+-----+ ---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+----------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | Name | char (35) | NO |         MUL |                | || CountryCode | CHAR (3) | NO |         MUL |                | || District | char (20) |     NO |         |                | || Population | Int (11) |     NO | |                0 | |+-------------+----------+------+-----+---------+----------------+ 

After using the index

explain select * from city where NAME=‘RAFAH‘\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: city         type: refpossible_keys: index_name          key: index_name      key_len: 35          ref: const         rows: 1                查找了一行        Extra: Using index condition

Delete Index

drop index 索引名 on 表名drop index index_name on city;

MySQL (iii)-database operations