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

发布时间:2020-06-15 16:28:12 作者:随風
来源:网络 阅读:1035


创建数据库

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

注:schema可以理解为方案或者数据库,与database一个意义


例创建一个students数据库,且默认字符集为‘gbk’、默认排序为'gbk_chinese_ci';

create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci';


删除数据库

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


创建表的方式

  1. 直接创建一个空表

  2. 从其他表中查询出数据,并以此创建一个有数据的表

  3. 以其他表为模板创建一个空表



create table [if not exists] 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+树索引结构

索引类型有两种

  1. BTREE索引

  2. 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: 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<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    |          

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


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



添加外键约束

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

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

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. MongoDB索引管理——创建索引,查看索引,删除索引,重建
  2. mysql索引的创建和和删除

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 删除 创建

上一篇:laravel 中间件的使用

下一篇:模拟Nginx服务启动关闭

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》