MySQL之表的创建、删除、修改、删除、查看及索引

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

注：创建表时如果未指定字符集，排序规则等，则从数据库继承；而数据库创建时指定或者采取默认

向表中插入及查看数据 insert into 和select

例：

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； 显示表的索引

例：

mysql> show index from corses\G

*************************** 1. row ***************************

Table: corses 表名

Non_unique: 0 是否为唯一主键；0表示是唯一键，1表示不是唯一键

Key_name: PRIMARY 键名

Seq_in_index: 1 这个表的第1个索引，一个表中可以第一多个索引

Column_name: cid 索引在哪个字段上（cid）

Collation: A 排序规则

Cardinality: 3

Sub_part: NULL 索引长度

Packed: NULL

Null:

Index_type: BTREE 索引类型

Comment:

Index_comment:

1 row in set (0.00 sec)

从以张表中查找出数据并建立一个新表

mysql> create table testcourses select * from corses where cid

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

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 |

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

用dessc查看两张表的结构，可以看出不一样

当从一张表中复制数据时某些字段会消失

以一个表为模板创建一个结构一样的空表

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显示两个表的格式相同

注：

所以要想复制一张表中的数据，最好先依据原表创建一个完全相同的空表，在用insert命令导入原表的数据

修改表

alter table tb_name（添加，删除，修改字段，修改索引，改表名mysql删除表，修改表属性）

例

mysql> alter table test add unique key(course);

为test表添加一个唯一键（course）

mysql> alter table test change course Course varchar(50) not null;

将course字段（属性）改为Course 且为varchar(50) 不能为空

新增一个日期字段

例：

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 | |

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

修改表名test为mytest

mysql> alter table test rename mytest;

删除表

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)

做两表的条件显示

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)

添加外键约束

外键约束只能添加在支持事物的存储引擎上，且存储引擎要一样

外键约束的关联的两个字段类型要一样

mysql> alter table corses engine=innodb; 修改引擎

mysql> alter table hehe modify cid tinyint unsigned not null; 修改字段类型一样

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

将hehe表的cid字段与corses表的cid字段关联一起建立一个外键约束，外键名称为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,'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);

索引：可以创建，查看，删除，不可以修改

create index index_name on tb_name(字段）using BTREE/HASH;

且可以 （字段（length）desc|asc ）

length表示索引长度、占的字符数；

asc表示创建索引后按照升序排，desc表示按照降序排

mysql> create index name_on_student on test1(name) using BTREE;

在test1表的name字段创建索引，并且为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;

在test1表的name字段创建一个长度为5,且降序排列的BTREE索引

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 索引名称

Seq_in_index: 1 这个表的第一个索引

Column_name: name 索引所在的字段（name）

Collation: A

Cardinality: 3

Sub_part: 5 索引长度为5

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!