MySQL之表的创建、删除、修改、删除、查看及索引
创建数据库create database|schema db_name
注：schema可以理解为方案或者数据库，与database一个意义
例创建一个students数据库，且默认字符集为‘gbk’、默认排序为'gbk_chinese_ci';
create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci';
删除数据库
drop {database|schema} db_name;
创建表的方式
[*]直接创建一个空表
[*]从其他表中查询出数据，并以此创建一个有数据的表
[*]以其他表为模板创建一个空表
create table tb.name (字段名称1 字段定义 约束 索引，字段名称2 字段定义 约束 索引）
例：
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';
主键还可以如下单独定义
mysql> create table tb1 (id int unsigned not null auto_increment ,name char(20) not null,age tinyint not null，primary key（id）)；
也可以将多个字段一起作为主键
mysql> create table tb1 (id int unsigned not null auto_increment ,name char(20) not null,age tinyint not null，primary key（id，name）)；
注：任何一个auto_increment字段都必须定义为主键
将id定义为主键，name定义为唯一键，age定义为索引
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;
键是一种特殊的索引，其数值不能相同，而索引允许出现相同值，称键为约束，属于B+树索引结构
索引类型有两种
[*]BTREE索引
[*]HASH索引
每个字段都支持B树索引，但不一定支持HASH索引
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)
注：创建表时如果未指定字符集，排序规则等，则从数据库继承；而数据库创建时指定或者采取默认
向表中插入及查看数据 insert into 和select
例：
mysql> insert into corses (course) values ('kuihuabaodian'),('jiuyingzhenjing'),('rulaishezhang'); ##向corses表的courses字段插入数据
Query OK, 3 rows affected (0.06 sec)
Records: 3Duplicates: 0Warnings: 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<2;
Query OK, 1 row affected (0.12 sec)
Records: 1Duplicates: 0Warnings: 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 |
+--------+---------------------+------+-----+---------+----------------
用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> 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 testadd 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:
页:
[1]