欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  科技

MySQL数据库创建、修改和删除表操作实例介绍

程序员文章站 2023-12-10 16:24:28
MySQL数据库创建、修改和删除表操作实例介绍...

其实对很多人来说对于sql语句已经忘了很多,或者说是不懂很多,因为有数据库图形操作软件,方便了大家,但是我们不能忘记最根本的东西,特别是一些细节上的东西,可能你用惯了hibernate,不用写sql语句,但是不是任何项目都要用到大框架的,如果不用,那你是不是就不会操作数据库了呢,所以我们最好还是熟悉一点好,对我们以后找工作和工作都有帮助。

在说创建、修改和删除表前,我们还是要进行一个操作的简单说明:

1.登陆数据库系统

在命令行中登陆mysql数据库管理系统,输入一下内容:

mysql -h localhost -u root -p

很多人都知道这个,但是其中参数的具体表示什么我们还是要了解的,其中,“-h”参数指连接的主机名,所以后面是localhost;“-u”参数表示用户名,此处的用户名为root;“-p”参数表示用户的密码,按下enter键后就显示“enter password:”,输入密码即可登录进去了。

2.创建数据库

在创建数据库之前,我们可以查看已经存在的数据库:

mysql> show databases;+--------------------+| database           |+--------------------+| information_schema || community          || community_test     || data               || mydata             || mysql              || performance_schema || test               |+--------------------+8 rows in set (0.04 sec)

创建数据库的格式:create database 数据库名;

示例:创建一个名为example的数据库

mysql>  create database example;query ok, 1 row affected (0.00 sec)mysql>  show databases;+--------------------+| database           |+--------------------+| information_schema || community          || community_test     || data               || example            || mydata             || mysql              || performance_schema || test               |+--------------------+9 rows in set (0.00 sec)

3.删除数据库:

格式:drop database 数据库名;

示例:删除example数据库

mysql>  drop database example;query ok, 0 rows affected (0.07 sec)mysql> show databases;+--------------------+| database           |+--------------------+| information_schema || community          || community_test     || data               || mydata             || mysql              || performance_schema || test               |+--------------------+8 rows in set (0.00 sec)

4.数据库存储引擎

存储引擎就是指表的类型,数据库存储引擎决定了表在计算机的存储方式。

mysql中查询存储引擎的类型命令:show engines;

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| engine             | support | comment                | transactions | xa   | savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| federated          | no      | federated mysql storage engine                | null         | null | null       || mrg_myisam         | yes     | collection of identical myisam tables                | no           | no   | no         || myisam             | yes     | myisam storage engine                | no           | no   | no         || blackhole          | yes     | /dev/null storage engine (anything you write to it disappears) | no           | no   | no         || csv                | yes     | csv storage engine                | no           | no   | no         || memory             | yes     | hash based, stored in memory, useful for temporary tables      | no           | no   | no         || archive            | yes     | archive storage engine                | no           | no   | no         || innodb             | default | supports transactions, row-level locking, and foreign keys     | yes          | yes  | yes        || performance_schema | yes     | performance schema                | no           | no   | no         |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)

查询结果中,engine参数指存储引擎名称;support参数说明mysql是否支持该类型引擎;comment参数表示对该引擎的评论;transaction参数表示是否支持事务处理;xa参数表示是否分布式交易处理的xa规范;savepoints参数表示是否支持保存点,以方便事务的回滚操作;由上面我们看到innodb存储引擎是default的,也就是数据库默认的存储引擎,下面我们简单介绍一下innodb。

innodb是mysql的一种存储引擎,innodb给mysql提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。innodb是mysql上第一个提供外键约束的表引擎,而且对事务处理的能力,也是其他存储引擎不能比拟的。不过这种引擎的缺点就是读写效率稍差,占用的数据空间相对比较大。

下面就是正式的内容:

创建表:

1)创建表的形式:

create table 表名 (    属性名 数据类型 [完整约束条件],    属性名 数据类型 [完整约束条件],    ...    ...    属性名 数据类型 [完整约束条件]);

如果你很急的登陆进去就创建表,恭喜你,你会出现“no database selected”的错误,因为你没有告诉别人你要选择在哪个数据库创建表,所以在创建之前要选择数据库,格式:use 数据库名;

示例创建一个student表:

mysql> use example;database changedmysql> create table student (    -> id int,    -> name varchar(20)    -> );query ok, 0 rows affected (0.09 sec)

上面创建表的时候涉及到一个完整性约束条件,下面就列出一个完整性约束条件表:

约束条件说明primary key 标识该属性为该表的主键,可以唯一的标识对应的元组foreign key 标识该属性为该表的外键,是与之联系某表的主键 not null 标识该属性不能为空unique 标识该属性的值是唯一的auto_increment 标识该属性的值是自动增加,这是mysql的sql语句的特色 default为该属性设置默认值

下面讲解一下上面完整性约束条件的应用:

2)设置表的主键

单字段主键格式:属性名 数据类型 primary key

示例:

mysql>  create table student1 (    -> id int primary key,    -> name varchar(20)    -> );query ok, 0 rows affected (0.06 sec)

多字段主键格式:primary key(属性名1,属性名2....属性名n)

示例:

mysql> create table student2 (    -> id int,    -> stu_id int,    -> name varchar(20),    -> primary key(id,stu_id)    -> );query ok, 0 rows affected (0.00 sec)

3)设置表的外键

格式:constraint 外键别名 foreign key(属性1,属性2,....属性n) references 表名(属性1',属性2',...属性n')

示例:

mysql> create table teacher (    -> id int primary key,    -> stu_id int,    -> name varchar(20),    -> constraint stuid foreign key(stu_id) references student1(id)    -> );query ok, 0 rows affected (0.00 sec)

4)设置表的非空约束

简单的说就是不让这个属性的值为空,不填的话就会报错

格式:属性名 数据类型 not null

5)设置表的唯一性约束

就是这个属性的值是不能重复的

格式:属性名 数据类型 unique

6)设置表的属性值自动增加

auto_increment约束的字段可以是任何整数类型(tinyint、smallint、int和bigint),在默认的情况下,该字段的值是从1开始自增

格式:属性名 数据类型 auto_increment

7)设置表的属性的默认值

格式:属性名 数据类型 default 默认值

下面对4-7进行综合示例:

mysql> create table student3 (    -> id int primary key auto_increment,    -> teacher_id int unique,    -> name varchar(20) not null,    -> sex varchar(10) default 'male'    -> );query ok, 0 rows affected (0.01 sec)

查看表结构

查看表基本结构语句describe

格式:describe 表名;

通过查看表的结构,就很明确的对表进行解读,而且可以查看一下自己创建的表有没错误,这个sql语句必须会用啊

示例:

mysql> desc student3;+------------+-------------+------+-----+---------+----------------+| field      | type        | null | key | default | extra          |+------------+-------------+------+-----+---------+----------------+| id         | int(11)     | no   | pri | null    | auto_increment || teacher_id | int(11)     | yes  | uni | null    |                || name       | varchar(20) | no   |     | null    |                || sex        | varchar(10) | yes  |     | male    |                |+------------+-------------+------+-----+---------+----------------+4 rows in set (0.01 sec)

查看表详细结构语句show create table

通过这个sql语句可以查看表的详细定义,除了字段名、字段的数据类型、约束条件外,还可以查看表的默认存储引擎和字符编码

格式:show create table 表名;

示例:

mysql> show create table student3;+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| table    | create table                                             |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student3 | create table `student3` (  `id` int(11) not null auto_increment,  `teacher_id` int(11) default null,  `name` varchar(20) not null,  `sex` varchar(10) default 'male',  primary key (`id`),  unique key `teacher_id` (`teacher_id`)) engine=innodb default charset=gb2312 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

修改表:

1)修改表名

表名可以在一个数据库中唯一的确定一张表。

格式:alter table 旧表名 rename 新表名;

示例:

mysql> alter table student rename student4;query ok, 0 rows affected (0.11 sec)mysql> describe student;error 1146 (42s02): table 'example.student' doesn't exist

由上面可以看出,改名后的表已经不存在了。

2)修改字段的数据类型

格式:alter table 表名 modify 属性名 数据类型;

示例:

mysql> describe student1;+-------+-------------+------+-----+---------+-------+| field | type        | null | key | default | extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | no   | pri | null    |       || name  | varchar(20) | yes  |     | null    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.08 sec)mysql> alter table student1 modify name varchar(30);query ok, 0 rows affected (0.06 sec)records: 0  duplicates: 0  warnings: 0mysql> describe student1;+-------+-------------+------+-----+---------+-------+| field | type        | null | key | default | extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | no   | pri | null    |       || name  | varchar(30) | yes  |     | null    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)

3)修改字段名:

格式:alter table 表名 change 旧属性名 新属性名 新数据类型;

示例:

mysql> describe student1;+-------+-------------+------+-----+---------+-------+| field | type        | null | key | default | extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | no   | pri | null    |       || name  | varchar(30) | yes  |     | null    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table student1 change name stu_name varchar(40);query ok, 0 rows affected (0.01 sec)records: 0  duplicates: 0  warnings: 0mysql> describe student1;+----------+-------------+------+-----+---------+-------+| field    | type        | null | key | default | extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | no   | pri | null    |       || stu_name | varchar(40) | yes  |     | null    |       |+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

这里我修改的字段名的同时也修改了数据类型了,如果你不想修改数据类型的话就按照原来的写就行了。

4)增加字段

格式:alter table 表名 add 属性名1 数据类型 [完整性约束条件] [first | after 属性名2];

其中,“属性名1”参数指需要增加的字段的名称;“first”参数是可选参数,其作用是将新增字段设置为表的第一个字段;“after”参数也是可选的参数,其作用是将新增字段添加到“属性名2”后面;“属性名2”当然就是指表中已经有的字段

示例:

mysql> describe student1;+----------+-------------+------+-----+---------+-------+| field    | type        | null | key | default | extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | no   | pri | null    |       || stu_name | varchar(40) | yes  |     | null    |       |+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table student1 add teacher_name varchar(20) not null after id;query ok, 0 rows affected (0.01 sec)records: 0  duplicates: 0  warnings: 0mysql> describe student1;+--------------+-------------+------+-----+---------+-------+| field        | type        | null | key | default | extra |+--------------+-------------+------+-----+---------+-------+| id           | int(11)     | no   | pri | null    |       || teacher_name | varchar(20) | no   |     | null    |       || stu_name     | varchar(40) | yes  |     | null    |       |+--------------+-------------+------+-----+---------+-------+3 rows in set (0.01 sec)

5)删除字段

格式:alter table 表名 drop 属性名;

示例:

mysql> describe student1;+--------------+-------------+------+-----+---------+-------+| field        | type        | null | key | default | extra |+--------------+-------------+------+-----+---------+-------+| id           | int(11)     | no   | pri | null    |       || teacher_name | varchar(20) | no   |     | null    |       || stu_name     | varchar(40) | yes  |     | null    |       |+--------------+-------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> alter table student1 drop teacher_name;query ok, 0 rows affected (0.01 sec)records: 0  duplicates: 0  warnings: 0mysql> describe student1;+----------+-------------+------+-----+---------+-------+| field    | type        | null | key | default | extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | no   | pri | null    |       || stu_name | varchar(40) | yes  |     | null    |       |+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

6)更改表的存储引擎

格式:alter table 表名 engine = 存储引擎名;

示例:

mysql> show create table student2;+----------+------------------------------------------------------------------------------------------------------------------------------------------------| table    | create table+----------+------------------------------------------------------------------------------------------------------------------------------------------------| student2 | create table `student2` (  `id` int(11) not null default '0',  `stu_id` int(11) not null default '0',  `name` varchar(20) default null,  primary key (`id`,`stu_id`)) engine=innodb default charset=gb2312 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.05 sec)mysql> alter table student2 engine = myisam;query ok, 0 rows affected (0.02 sec)records: 0  duplicates: 0  warnings: 0mysql> show create table student2;+----------+------------------------------------------------------------------------------------------------------------------------------------------------| table    | create table+----------+------------------------------------------------------------------------------------------------------------------------------------------------| student2 | create table `student2` (  `id` int(11) not null default '0',  `stu_id` int(11) not null default '0',  `name` varchar(20) default null,  primary key (`id`,`stu_id`)) engine=myisam default charset=gb2312 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)

7)删除表的外键约束

格式:alter table 表名 drop foreign key 外键别名;

示例:

mysql> show create table teacher;+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| table   | create table                                                  |+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| teacher | create table `teacher` (  `id` int(11) not null,  `stu_id` int(11) default null,  `name` varchar(20) default null,  primary key (`id`),  key `stuid` (`stu_id`),  constraint `stuid` foreign key (`stu_id`) references `stu) engine=innodb default charset=gb2312 |+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.08 sec)mysql> alter table teacher drop foreign key stuid;query ok, 0 rows affected (0.04 sec)records: 0  duplicates: 0  warnings: 0mysql> show create table teacher;+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| table   | create table                                                        |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| teacher | create table `teacher` (  `id` int(11) not null,  `stu_id` int(11) default null,  `name` varchar(20) default null,  primary key (`id`),  key `stuid` (`stu_id`)) engine=innodb default charset=gb2312 |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

删除表:

格式:drop table 表名;

删除没有被关联的普通表:直接上面的sql语句就行了

删除被其他表关联的父表:

方法一:先删除子表,在删除父表

方法二:删除父表的外键约束(上面有介绍),再删该表

相关标签: MySQL 数据库