`

mysql - 创建、修改和删除表

 
阅读更多

原文链接 http://www.cnblogs.com/weixing/p/3340341.html 

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 tempor
ary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine
                | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and f
oreign 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 changed
mysql> 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: 0

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

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)

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

 

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

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)

 

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

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)

 

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

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=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: 0

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`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------------+
1 row in set (0.00 sec)

 

  删除表:

  格式:DROP TABLE 表名;

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

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

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

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

分享到:
评论

相关推荐

    mysql-5.7.17-winx64.zip 解压版

    修改里面的basedir值和datadir值(不要手动创建data文件夹) 以管理员身份运行cmd. 进入到mysql根目录下的bin目录,依次输入 (安装) mysqld --install (初始化,会自动在mysql根目录下创建data文件夹,就是my.ini...

    mysql高级笔记 P1 day01-02. MySQL 高级 - Linux上安装MySQL P2 day01-03. MyS

    mysql高级笔记 P1 day01-02. MySQL 高级 - Linux上安装MySQL P2 day01-03. MySQL 高级 - 启动及登录MySQL... MySQL 高级 - 视图 - 创建及修改视图 P13 day01-14. MySQL 高级 - 视图 - 查看及删除视图 P14 day01-15. MyS

    实验训练1 在MySQL中创建数据库和表.docx

    有安装、创建库、创建表、增加、修改、删除、查询等等一共16页,有截图、很详细的步骤讲解、操作,用于国家开发大学形考考试作业用。

    MySQL:创建、修改和删除表.docx

    MySQL:创建、修改和删除表.docxMySQL:创建、修改和删除表.docx

    MySQL数据库:表的创建和管理Navicat.pptx

    数据库和表的创建和管理 表的创建和管理-navicat 课程目标 LOGO 掌握 —— 利用navicat图形化创建和管理表; 掌握 —— 利用navicat编写SQL语句创建和管理表。 表的创建和管理-navicat Navicat图形化创建数据表 ...

    MySQL高级笔记.zip

    笔记目录: P1 day01-02. MySQL 高级 - Linux上安装MySQL P2 day01-03. MySQL 高级 - 启动及登录MySQL ... MySQL 高级 - 视图 - 创建及修改视图 P13 day01-14. MySQL 高级 - 视图 - 查看及删除视图 ...........

    数据库原理及应用实验一(创建数据库和表)

    数据库原理及应用实验一(创建数据库和表),一、 实验目的 熟悉SQL Server Management Studio;掌握通过SQL Server Management Studio管理数据库的方法;掌握数据库及其物理文件的结构关系;掌握通过SQL Server ...

    mysql-operator:在Kubernetes中创建,操作和扩展自愈MySQL集群

    MySQL运算符MySQL创建,配置和管理在Kubernetes上运行MySQL InnoDB集群。 它不适用于MySQL NDB群集。 MySQL Operator对集群的配置方式持保留意见。 我们基于和来为在Kubernetes上运行MySQL提供完整的高可用性解决...

    Mysql 8免安装版配置步骤

    7. 执行mysqld --initialize-insecure --user=mysql创建无密码root用户 8. 执行net start mysql 如果返回发生系统错误 193。删除bin文件夹的mysqld文件,无后缀名的文件 9. 执行mysql -u root -p,下行会弹出Enter ...

    MySQL高级课程视频.zip

    - 视图 - 创建及修改视图14. - 查看及删除视图15. MySQL 高级 - 存储过程 - 概述16. 创建调用查询删除语法17. 语法 - 变量18. MySQL 高级 - 语法 - if判断19. MySQL 高级 - 输入参数20. MySQL 高级- 输出参数.........

    mysql数据库实验报告 数据表的操作

    MySQL数据库的创建、查看、删除、使用命令。 表结构创建和修改、表约束的创建和修改; 表数据的插入、删除和修改; 表联系的创建和修改。

    MySQL创建数据库与创建用户以及授权

    1、create schema [数据库名称] default character set utf8 collate utf8_general_ci;--创建数据库 ...MySQL创建数据库与创建用户以及授权相关SQL语句,摘自博客http://www.cnblogs.com/janken/p/5500320.html

    mysql创建、删除、修改表

    此文档讲解了mysql的常用语法,包括创建,修改,删除等

    MySQL 5.1参考手册

    3.4. 获得数据库和表的信息 3.5. 在批处理模式下使用mysql 3.6. 常用查询的例子 3.6.1. 列的最大值 3.6.2. 拥有某个列的最大值的行 3.6.3. 列的最大值:按组 3.6.4. 拥有某个字段的组间最大值的行 3.6.5. 使用用户...

    mysql基础笔记

    /* Windows服务 */ -- 启动MySQL net start mysql ...-- 修改库的选项信息 ALTER DATABASE 库名 选项信息 -- 删除库 DROP DATABASE[ IF EXISTS] 数据库名 同时删除该数据库相关的目录及其目录内容

    db-mysql-event

    mysql event scheduler 使用详解,含全局设置、创建event、修改event、删除event及相应的example,测试环境 mysql ver 14.14 distrib 5.7.26

    MySQL 入门学习 ——基础教程

    上篇我们学会了如何创建一个数据库和数据库表,并知道如何向数据库表中添加记录。那么我们如何从数据库表中检索数据呢? · 1、从数据库表中检索信息 实际上,前面我们已经用到了SELECT语句,它用来从数据库表中...

    mysql镜像备份mysql镜像备份

    1、主库创建/etc/my.cnf,修改[mysqld]里边的键值增加 server-id=1 log-bin=binlog_name 2、主库增加用户,用于从库读取主库日志。 grant replication slave,reload,super on *.* to 'slave'@'192.168.0.206' ...

    mysql基础入门课程

    07. 基础-SQL-DDL-表操作-修改&删除 08. 基础-SQL-图形化界面工具DataGrip 09. 基础-SQL-DML-插入 10. 基础-SQL-DML-更新和删除 11. 基础-SQL-DQL-基础查询 12. 基础-SQL-DQL-条件查询 13. 基础-SQL-DQL-聚合函数 14...

    mysql笔记.docx

    1命令行登录mysql:mysql -u 用户名 -p(回车后输入密码) 2。退出:quit/exit. 3.远程登录mysql:mysql -h IP -u root -p(回车后输入密码) ...5.显示数据库:show databases;...9. 删除数据库: drop database mydb3;

Global site tag (gtag.js) - Google Analytics