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

Create a database

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

Note: schema can be understood as a solution or database, which has the same meaning as database.

For example, create a students database with a default character set of 'gbk' and a default sort of' gbk_chinese_ci'

Create schema if not exists students character set 'gbk' collate' gbk_chinese_ci'

Delete database

Drop {database | schema} [if exists] db_name

How to create a table

Create an empty table directly

Query data from other tables and create a table with data

Create an empty table using other tables as templates

Create table [if not exists] tb.name (field name 1 field defines constraint index, field name 2 field defines constraint index)

Example:

Mysql > create table tb1 (id int unsigned not null auto_increment primary key,name char (20) not null,age tinyint not null) 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 (20) not null,age tinyint not null,primary key (id))

You can also use multiple fields together as primary keys

Mysql > create table tb1 (id int unsigned not null auto_increment, name char (20) not null,age tinyint not null,primary key (id,name))

Note: any auto_increment field must be defined as the primary key

Define id as the primary key, name as the unique key, and age as the index

Mysql > create table tb1 (id int unsigned not null auto_increment, name char (20) not null,age tinyint not null,primary key (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

The key is a special index, its value can not be the same, but the index allows the same value, which is called constraint and belongs to the Btree index structure.

There are two types of indexes.

BTREE index

HASH index

Each field supports B-tree index, but not necessarily HASH index.

Mysql > create table corses (cid tinyint unsigned not null auto_increment, course varchar (50) 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, etc., when creating a table, you will inherit from the database, while when you create a database, you specify or take default.

Insert and view data insert into and select into the table

Example:

Mysql > insert into corses (course) values ('kuihuabaodian'), (' jiuyingzhenjing'), ('rulaishezhang'); # # insert data into the courses field of the corses table

Query OK, 3 rows affected (0.06 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > select * from corses; # View

+-+ +

| | cid | course |

+-+ +

| | 1 | kuihuabaodian |

| | 2 | jiuyingzhenjing |

| | 3 | rulaishezhang |

+-+ +

3 rows in set (0.00 sec)

Show index from tb_name; displays the index of the table

Example:

Mysql > show index from corses\ G

* * 1. Row *

Table: corses table name

Non_unique: whether 0 is the unique primary key; 0 is the unique key, and 1 is not the unique key

Key_name: PRIMARY key name

Seq_in_index: 1 the first index of this table. You can have the first multiple indexes in a table.

Column_name: which field is the cid index on (cid)

Collation: a collation

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 select * from testcourses

+-+ +

| | cid | course |

+-+ +

| | 1 | kuihuabaodian |

+-+ +

1 row in set (0.00 sec)

Mysql > create table testcourses select * from corses where cid 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.05sec)

Mysql > desc corses

+-+ +

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

+-+ +

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

| | course | varchar (50) | NO | | NULL |

+-

Looking at the structure of the two tables with dessc, we can see that they are different.

Some fields disappear when you copy data from a table

Use a table as a template to create an empty table with the same structure

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 that the two tables have the same format

Note:

So in order to copy the data in a table, it is 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 the table

Alter table tb_name (add, delete, modify fields, modify indexes, change table names, modify table properties)

Example

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 (50) not null

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

Add a date field

Example:

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 |

+-

Change the table name test to mytest

Mysql > alter table test rename mytest

Delete tabl

Mysql > create table hehe (sid int unsigned not null auto_increment primary key,name varchar 30, 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 a conditional display of two tables

Mysql > select name,course from hehe,corses where hehe.cid=corses.cid

+-+ +

| | name | course |

+-+ +

| | zxl | kuihuabaodian |

| | jiamian | jiuyingzhenjing |

+-+ +

2 rows in set (0.01sec)

Add a foreign key constraint

Foreign key constraints can only be added to a storage engine that supports things, and the storage engine should be the same

The two field types associated with the foreign key constraint should be the same

Mysql > alter table corses engine=innodb; modification engine

Mysql > alter table hehe modify cid tinyint unsigned not null; is the same as modifying field types

Mysql > alter table hehe add foreign key foreign_cid (cid) references corses (cid)

Associate the cid field of the hehe table with the cid field of the corses table to create a foreign key constraint with the name of the foreign key foreign_cid

Mysql > create table test1 (cid int unsigned not null auto_increment primary key,name varchar (50) not null,sid char not null)

Mysql > insert into test1 (cid,name,sid) values (1), (2) (2), (3), (3)

Mysql > create table test2 (cid int unsigned not null auto_increment primary key,name varchar (50))

Mysql > insert into test2 (cid,name) values (1memorialhehe'), (2recovery')

Mysql > alter table test1 add foreign key foreign_cid (cid) references courses (cid)

Index: can be created, viewed, deleted, not modified

Create index index_name on tb_name (field) using BTREE/HASH

And can (field (length) desc | asc)

Length indicates index length, number of characters occupied

Asc indicates that the index is sorted in ascending order, while desc means that it is sorted in descending order.

Mysql > create index name_on_student on test1 (name) using BTREE

Create an index in the name field of the test1 table and index it for 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: 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 5-length, descending BTREE index 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: the field in which the name index is located (name)

Collation: A

Cardinality: 3

Sub_part: 5 Index length is 5

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

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.