MySQL table creation, deletion, modification, deletion, viewing and indexing

Create a database

Create Database|schema [if not exists] db_name [character set=] [collate=]

Note: Schema can be understood as a scheme or database, with a meaning

Example creates a students database with the default character set of ' GBK ' and the default sort of ' gbk_chinese_ci ';

Create schema if not EXISTS students character set ' GBK ' collate ' gbk_chinese_ci ';

Deleting a database

drop {Database|schema} [if exists] db_name;

How tables are Created

1. Create an empty table directly

2. Query out data from other tables and create a table with data

3. Create an empty table for a template with another table
   

CREATE TABLE [if not EXISTS] tb.name (field name 1 field definition constraint index, field name 2 field definition constraint index)

Cases:

Mysql> CREATE TABLE tb1 (id int unsigned NOT NULL auto_increment primary Key,name char (a) not null,age tinyint not nul L) engine= ' engine_name ';

The primary key can also be defined separately as follows

Mysql> CREATE TABLE tb1 (id int unsigned NOT NULL auto_increment, name char (NO) null,age tinyint not null,primary K EY (ID));

You can also use multiple fields together as a primary key

Mysql> CREATE TABLE tb1 (id int unsigned NOT NULL auto_increment, name char (NO) null,age tinyint not null,primary K EY (id,name));

Note: Any one auto_increment field must be defined as the primary key

The ID is defined as the primary key, and the name is defined as a unique key, and age is defined as an index

Mysql> CREATE TABLE tb1 (id int unsigned NOT NULL auto_increment, name char (NO) null,age tinyint not null,primary K EY (ID), unique key (name) index (age);

Mysql> Show tables;

+--------------------+

| tables_in_students |

+--------------------+

| tb1 |

+--------------------+

1 row in Set (0.00 sec)

Mysql> select Id,name,age from TB1;

A key is a special index whose value cannot be the same, and the index allows the same value to be present, and the key to be a constraint, which belongs to the B + Tree index structure

There are two types of indexes

1. Btree Index

2. Hash index

Each field supports a B-tree index, but does not necessarily support a hash index

Mysql> CREATE TABLE Corses (CID tinyint unsigned NOT NULL auto_increment, course varchar (+) not null,primary key (CID)) ;

Query OK, 0 rows affected (0.15 sec)

Mysql> Show Table status like ' Corses ' \g

1. Row ***************************

Name:corses

Engine:innodb

Version:10

Row_format:compact

rows:0

avg_row_length:0

data_length:16384

max_data_length:0

index_length:0

data_free:0

Auto_increment:1

Create_time:2017-02-12 10:45:11

Update_time:null

Check_time:null

Collation:utf8_general_ci

Checksum:null

Create_options:

Comment:

1 row in Set (0.00 sec)

Note: If you do not specify a character set, collation, and so on when creating a table, you inherit from the database, and the database is created by specifying or taking the default

Inserting and viewing data into a table insert into and select

Cases:

mysql> INSERT into Corses (course) VALUES (' Kuihuabaodian '), (' jiuyingzhenjing '), (' Rulaishezhang '); # #向corses表的courses字段插入数据

Query OK, 3 rows affected (0.06 sec)

Records:3 duplicates:0 warnings:0

Mysql> select * from Corses; #查看

+-----+-----------------+

| CID | Course |

+-----+-----------------+

| 1 | Kuihuabaodian |

| 2 | jiuyingzhenjing |

| 3 | Rulaishezhang |

+-----+-----------------+

3 Rows in Set (0.00 sec)

Show index from Tb_name;

Cases:

Mysql> Show index from CORSES\G

1. Row ***************************

Table:corses Table Name

Whether the non_unique:0 is a unique primary key, 0 is a unique key, and 1 means that it is not a unique key

Key_name:primary Key Name

Seq_in_index:1 the 1th index of this table, a table can be the first multiple index

Column_name:cid index on which field (CID)

Collation:a Sorting rules

Cardinality:3

Sub_part:null Index Length

Packed:null

Null:

Index_type:btree Index Type

Comment:

Index_comment:

1 row in Set (0.00 sec)

Find data from a table and create a new table

Mysql> CREATE TABLE testcourses select * from Corses where cid<2;

Query OK, 1 row affected (0.12 sec)

Records:1 duplicates:0 warnings:0

Mysql> select * from Testcourses;

+-----+---------------+

| CID | Course |

+-----+---------------+

| 1 | Kuihuabaodian |

+-----+---------------+

1 row in Set (0.00 sec)

Mysql> CREATE TABLE testcourses select * from Corses where cid<3;

ERROR 1050 (42S01): Table ' testcourses ' already exists

mysql> desc courses;

ERROR 1146 (42S02): Table ' students.courses ' doesn ' t exist

mysql> desc testcourses;

+--------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+-------+

| CID | tinyint (3) unsigned |     NO | | 0 |

| Course | varchar (50) |     NO | | NULL |

+--------+---------------------+------+-----+---------+-------+

2 rows in Set (0.05 sec)

mysql> desc Corses;

+--------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra

+--------+---------------------+------+-----+---------+----------------+

| CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

| Course | varchar (50) |     NO | | NULL |

+--------+---------------------+------+-----+---------+----------------

Look at the structure of the two tables with DESSC, you can see different

Some fields disappear when you copy data from a table

Create a structure-like empty table for a template with a table

Mysql> CREATE table test like corses;

Query OK, 0 rows affected (0.16 sec)

mysql> desc test;

+--------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+----------------+

| CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

| Course | varchar (50) |     NO | | NULL |

+--------+---------------------+------+-----+---------+----------------+

2 rows in Set (0.00 sec)

mysql> desc Corses;

+--------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+----------------+

| CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

| Course | varchar (50) |     NO | | NULL |

+--------+---------------------+------+-----+---------+----------------+

Desc shows two tables in the same format

Note:

So to copy data from a table, it's best to create an identical empty table based on the original table and import the data from the original table with the Insert command.

Modify Table

ALTER TABLE Tb_name (ADD, delete, modify field, modify index, change table name, modify table properties)

Cases

Mysql> ALTER TABLE test add unique key (course);

Add a unique key (course) to the test table

mysql> ALTER TABLE Test change course course varchar (a) not null;

Change the Course field (property) to course and varchar (50) cannot be empty

Add a Date field

Cases:

Mysql> ALTER TABLE Test add starttime date default ' 2017-2-12 ';

mysql> desc test;

+-----------+---------------------+------+-----+------------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+---------------------+------+-----+------------+----------------+

| CID | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |

| Course | varchar (50) | NO | UNI |                NULL | |

| StartTime | Date |     YES | |                2017-02-12 | |

+-----------+---------------------+------+-----+------------+----------------

Modify table name test to MyTest

mysql> ALTER TABLE test rename mytest;

Delete a table

Mysql> CREATE TABLE hehe (SID int unsigned NOT NULL auto_increment primary key,name varchar (+), CID int not null);

Query OK, 0 rows affected (0.09 sec)

mysql> INSERT into hehe (name,cid) VALUES (' Jiamian ', 2), (' Zxl ', 1);

Mysql> select * from hehe;

+-----+---------+-----+

| Sid | name | CID |

+-----+---------+-----+

| 1 |   Jiamian | 2 |

| 2 |   ZXL | 1 |

+-----+---------+-----+

2 rows in Set (0.00 sec)

Mysql> select * from Courses;

ERROR 1146 (42S02): Table ' students. Courses ' doesn ' t exist

Mysql> select * from Corses;

+-----+-----------------+

| CID | Course |

+-----+-----------------+

| 1 | Kuihuabaodian |

| 2 | jiuyingzhenjing |

| 3 | Rulaishezhang |

+-----+-----------------+

3 Rows in Set (0.00 sec)

Make two-table conditional display

Mysql> Select Name,course from hehe,corses where hehe.cid=corses.cid;

+---------+-----------------+

| name | Course |

+---------+-----------------+

| ZXL | Kuihuabaodian |

| Jiamian | jiuyingzhenjing |

+---------+-----------------+

2 rows in Set (0.01 sec)

Add a FOREIGN KEY constraint

1. FOREIGN KEY constraints can only be added to the storage engine that supports things, and the storage engine is the same

2. The FOREIGN KEY constraint has the same two field type as the associated one
   

mysql> ALTER TABLE Corses ENGINE=INNODB; Modify Engine

Mysql> ALTER TABLE hehe modify CID tinyint unsigned not null; Modify the field type as

Mysql>alter table hehe Add foreign key foreign_cid (CID) references Corses (CID);

Establish a FOREIGN KEY constraint with the CID field of the Hehe table associated with the CID field of the Corses table, with a foreign key name of Foreign_cid

Mysql> CREATE TABLE test1 (CID int unsigned NOT NULL auto_increment primary key,name varchar (a) not null,sid char not NULL);

mysql> INSERT INTO test1 (CID,NAME,SID) VALUES (1, ' zxl ', ' A '), (2, ' Jiamian ', ' B '), (3, ' fade ', ' C ');

Mysql> CREATE TABLE test2 (CID int unsigned NOT NULL auto_increment primary key,name varchar (50));

mysql> INSERT INTO test2 (cid,name) VALUES (1, ' hehe '), (2, ' haha ');

Mysql> ALTER TABLE test1 add foreign key foreign_cid (CID) References courses (CID);

Index: Can be created, viewed, deleted, cannot be modified

Create INDEX index_name on tb_name (field) using Btree/hash;

And yes (field (length) DESC|ASC)

Length indicates the number of characters in the index;

ASC indicates that the index is created in ascending order, and Desc is sorted by descending

Mysql> CREATE index name_on_student on test1 (name) using BTREE;

Index is created in the Name field of the Test1 table, and the Btree index

Mysql> Show index from TEST1\G

1. Row ***************************

Table:test1

non_unique:0

Key_name:primary

Seq_in_index:1

Column_name:cid

Collation:a

Cardinality:3

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

Index_comment:

2. Row ***************************

Table:test1

Non_uniqu

Key_name:name_on_student

Seq_in_index:1

Column_name:name

Collation:a

Cardinality:3

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

Index_comment

Mysql> DROP index name_on_student ontest1;

Create a btree index with a length of 5, in descending order, in the Name field of the Test1 table

Mysql> CREATE index name_on_student on test1 (name (5) DESC) using BTREE;

Mysql> Show index from TEST1\G

1. Row ***************************

Table:test1

non_unique:0

Key_name:primary

Seq_in_index:1

Column_name:cid

Collation:a

Cardinality:

Sub_part:null

Packed:null

Null:

Index_type:btree

Comment:

Index_comment:

2. Row ***************************

Table:test1

Non_unique:1

Key_name:name_on_student Index Name

Seq_in_index:1 The first index of this table

Column_name:name the field in which the index is located (name)

Collation:a

Cardinality:3

Sub_part:5 Index Length is 5

Packed:null

Null:

Index_type:btree

Comment:

Index_comment:

This article is from the "11097124" blog, please be sure to keep this source http://11107124.blog.51cto.com/11097124/1897041

MySQL table creation, deletion, modification, deletion, viewing and indexing