mysql 数据表的基本操作

最新推荐文章于 2023-11-10 08:33:08 发布
最新推荐文章于 2023-11-10 08:33:08 发布
阅读量356 收藏
点赞数
分类专栏： 数据结构 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012412427/article/details/47860801
版权

掌握创建表，添加各类约束，查看表结构，以及删除也修改操作.

create database Market;
Query OK, 1 row affected (0.00 sec)

mysql> use Market;
Database changed
mysql> 
mysql> show tables;
Empty set (0.00 sec)

mysql> 
mysql> create table customers
    -> (
    ->   c_num int(11) not null primary key auto_increment,
    ->   c_name varchar(50),
    ->   c_contact varchar(50),
    ->   c_city varchar(50),
    ->   c_birth datetime not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table customer4s;
ERROR 1146 (42S02): Table 'Market.customer4s' doesn't exist
mysql> 
mysql> show tables;
+------------------+
| Tables_in_Market |
+------------------+
| customers        |
+------------------+
1 row in set (0.00 sec)

mysql> show create table customres;
ERROR 1146 (42S02): Table 'Market.customres' doesn't exist
mysql> show create table customers;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                          |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers | CREATE TABLE `customers` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(50) DEFAULT NULL,
  `c_contact` varchar(50) DEFAULT NULL,
  `c_city` varchar(50) DEFAULT NULL,
  `c_birth` datetime NOT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> **alter table customers modify c_name varchar(50) after c_birth;**
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_contact | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
| c_name    | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table change c_contact c_phone ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'change c_contact c_phone' at line 1
mysql> alter table customers  change c_contact c_phone;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> alter table customers  change c_contact c_phone varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc customers;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| c_num   | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_phone | varchar(50) | YES  |     | NULL    |                |
| c_city  | varchar(50) | YES  |     | NULL    |                |
| c_birth | datetime    | NO   |     | NULL    |                |
| c_name  | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table customers modify c_birth varhcar(5);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varhcar(5)' at line 1
mysql> alter table customers modify c_birth varchar(5);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc customers;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| c_num   | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_phone | varchar(50) | YES  |     | NULL    |                |
| c_city  | varchar(50) | YES  |     | NULL    |                |
| c_birth | varchar(5)  | YES  |     | NULL    |                |
| c_name  | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table add c_gender char(1) not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add c_gender char(1) not null' at line 1
mysql> alter table customers add c_gender char(1) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc customers;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| c_num    | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_city   | varchar(50) | YES  |     | NULL    |                |
| c_birth  | varchar(5)  | YES  |     | NULL    |                |
| c_name   | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table customers rename customers_info;
Query OK, 0 rows affected (0.00 sec)

mysql> desc customers;
ERROR 1146 (42S02): Table 'Market.customers' doesn't exist
mysql> show tables;
+------------------+
| Tables_in_Market |
+------------------+
| customers_info   |
+------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show tables;
+------------------+
| Tables_in_Market |
+------------------+
| customers_info   |
+------------------+
1 row in set (0.00 sec)

mysql> desc customers_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| c_num    | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_city   | varchar(50) | YES  |     | NULL    |                |
| c_birth  | varchar(5)  | YES  |     | NULL    |                |
| c_name   | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table customers_info drop c_city;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc customers_ifno;
ERROR 1146 (42S02): Table 'Market.customers_ifno' doesn't exist
mysql> 
mysql> show tables;
+------------------+
| Tables_in_Market |
+------------------+
| customers_info   |
+------------------+
1 row in set (0.00 sec)

mysql> desc customers_ifno;
ERROR 1146 (42S02): Table 'Market.customers_ifno' doesn't exist
mysql> desc customers_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| c_num    | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_phone  | varchar(50) | YES  |     | NULL    |                |
| c_birth  | varchar(5)  | YES  |     | NULL    |                |
| c_name   | varchar(50) | YES  |     | NULL    |                |
| c_gender | char(1)     | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show create table customers_info;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                             |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers_info | CREATE TABLE `customers_info` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_birth` varchar(5) DEFAULT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `c_gender` char(1) NOT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show create table customers_info;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                             |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers_info | CREATE TABLE `customers_info` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_birth` varchar(5) DEFAULT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `c_gender` char(1) NOT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table  customers_info engine=innodb;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table customers_ifno;
ERROR 1146 (42S02): Table 'Market.customers_ifno' doesn't exist
mysql> show create table customers_info;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                             |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers_info | CREATE TABLE `customers_info` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_birth` varchar(5) DEFAULT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `c_gender` char(1) NOT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show create table customers_info;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                             |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers_info | CREATE TABLE `customers_info` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_birth` varchar(5) DEFAULT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `c_gender` char(1) NOT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table orders
    -> (
    -> 
    ->  o_num int(11) primary key auto_increment,
    ->  o_date date,
    ->  c_id varchar(50),
    ->  foreign key (c_id) references customers_info(c_num)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table order engine=innodb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order engine=innodb' at line 1
mysql> alter table orders  engine=innodb;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table orders;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                              |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
  `o_num` int(11) NOT NULL AUTO_INCREMENT,
  `o_date` date DEFAULT NULL,
  `c_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`o_num`),
  KEY `c_id` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table  orders modify c_id varchar(50) foreign key customers_info(c_num);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foreign key customers_info(c_num)' at line 1
mysql> alter table  orders modify c_id varchar(50) foreign key c_id references customers_info(c_num);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foreign key c_id references customers_info(c_num)' at line 1
mysql> alter table  orders add  foreign key c_id references customers_info(c_num);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references customers_info(c_num)' at line 1
mysql> alter table  orders add  foreign key (c_id)  references customers_info(c_num);
ERROR 1005 (HY000): Can't create table 'Market.#sql-51f_1143b' (errno: 150)
mysql> alter table  orders add  foreign key (c_id)  references customers_info(c_num);
ERROR 1005 (HY000): Can't create table 'Market.#sql-51f_1143b' (errno: 150)
mysql> show create table orders;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                              |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
  `o_num` int(11) NOT NULL AUTO_INCREMENT,
  `o_date` date DEFAULT NULL,
  `c_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`o_num`),
  KEY `c_id` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table customers_info;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                             |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers_info | CREATE TABLE `customers_info` (
  `c_num` int(11) NOT NULL AUTO_INCREMENT,
  `c_phone` varchar(50) DEFAULT NULL,
  `c_birth` varchar(5) DEFAULT NULL,
  `c_name` varchar(50) DEFAULT NULL,
  `c_gender` char(1) NOT NULL,
  PRIMARY KEY (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table orders modify c_id int(11);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table  orders add  foreign key (c_id)  references customers_info(c_num);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table orders;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                   |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
  `o_num` int(11) NOT NULL AUTO_INCREMENT,
  `o_date` date DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`o_num`),
  KEY `c_id` (`c_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `customers_info` (`c_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2015_thinker
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
EWSA 5.5.282.0 简体中文完美破解版
12-04
快速攻破无线网络WPA-PSK及WPA2-PSK密码
清华cmos模电课件
04-24
清华cmos模电课件 清华cmos模电课件
用multisim仿真的30秒计数器
04-29
这是一个用multisim仿真的三十秒计数器，可以实现从三十开始倒数到一，用了两块7448N芯片和两块74192N芯片等。
apm2.8 接线图
05-11
apm2.8飞控的接线图，信号线在内，地线在外。注意顺序
判断每个输入的数是否可被3，5和7整除，并将整除状态输出。
10-29
对每个输入数，分别输出对3，5和7的整除状态； 能实现多次输入输出，最好能有一些输入提示； 亦可同时输入和处理多个数，并输出多个数的整除状态。 考虑使用switch 或if。
MySQL 数据表的基本操作（详解）
梁辰兴的博客
10-20 4604
修改数据表的前提是数据库中已经存在该表。数据表属于数据库，在创建数据表之前，应使用语句“USE”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出 No database selected 的错误。提示：修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。选择创建表的数据库 test_db，创建 tb_emp1 数据表，输入的 SQL 语句和运行结果如下所示。
MySQL创建数据库和创建数据表
weixin_43790276的博客
10-24 31万+
MySQL 创建数据库和创建数据表 MySQL 是最常用的数据库，在数据库操作中，基本都是增删改查操作，简称CRUD。 在这之前，需要先安装好 MySQL ，然后创建好数据库、数据表、操作用户。 一、数据库操作语言 数据库在操作时，需要使用专门的数据库操作规则和语法，这个语法就是SQL(Structured Query Language) 结构化查询语言。 SQL 的主要功能是和数据库...
MySQL数据库基本操作
Drw_Dcm的博客
09-09 5万+
MySQL数据库基本操作
MySQL高频基本面试问题整理
热门推荐
张彦峰的博客
04-07 170万+
总结MySQL高频基本面试问题整理，用于快速查缺补漏
【MySQL基础】MySQL基本操作详解
小杨的博客
10-29 3万+
大家好，我是小杨！今天我将详细的为大家介绍MySQL数据库的基本操作，希望大家能够从中收获多多！
plc五种编程语言
11-25
学习plc编程学习
计算机操作系统课后答案 参考答案
11-14
计算机操作系统答案 高清PDF 标准答案
怎么使用Keil C51建立一个新项目并添加C语言程序文件
11-27
怎么使用Keil C51建立一个新项目并添加C语言程序文件 很详细简洁的说明
APM2.8原理图
12-10
新编ＡＰＭ２.８原理图　ＰＤＦ格式，改进了电源部分，
西门子授权工具sim_ekb_install_2016_10_26
11-08
西门子最新授权工具
mysql数据,字段基本操作
weixin_45185267的博客
06-14 1万+
msyql数据,字段基本操作,增删改查
MySQL数据库创建表一系列操作
君莫笑的博客
10-01 5万+
使用SHOW COLUMNS语句或DESCRIBE/DESC语句可以查看表的基本结构,包括列名、列的数据类型、长度、是否为空、是否为主键、是否有默认值等。CHANGE [ COLUMN]old_col_name column_definition [FIRST|AFTER col_name]/*对列重命名*/修改表用于更改原有表的结构,可以添加列、修改列、删除列、重命名列或表等。查看表包括查看表的名称、查看表的基本结构、查看表的详细结构等，下面分别介绍。可以使用SHOW TABLES语句查看表的名称。
MySQL数据库的基本操作以及数据类型
未见花闻的博客
03-13 7287
本篇文章介绍MySQL（数据库）基本的操作和数据类型，如显示服务端的数据库，创建数据库，认识MySQL基本的数据类型。
头歌——MySQL 基本操作
荆鹏的博客
11-10 5043
数据库部分一条一条的写，可鼠标手动粘贴，除特定命令外未分大小写。CREATE TABLE 表名。第5关：添加常用约束。第3关：使用主键约束。
mysql数据表的基本操作课件
最新发布
12-11
MySQL数据表的基本操作包括创建数据表、插入数据、查询数据、更新数据和删除数据。创建数据表时，需要指定表名和字段名，并为每个字段指定数据类型和约束条件。可以使用CREATE TABLE语句来创建数据表。插入数据时，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值