mysql 数据表的基本操作
来源：互联网 发布：lolob软件 编辑：程序博客网 时间：2024/04/28 12:06
掌握创建表，添加各类约束，查看表结构，以及删除也修改操作.
create database Market;Query OK, 1 row affected (0.00 sec)mysql> use Market;Database changedmysql> 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 existmysql> 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 existmysql> 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: 0mysql> 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 1mysql> 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 1mysql> alter table customers change c_contact c_phone varchar(50);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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 1mysql> alter table customers modify c_birth varchar(5);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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 1mysql> alter table customers add c_gender char(1) not null;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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 existmysql> 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: 0mysql> desc customers_ifno;ERROR 1146 (42S02): Table 'Market.customers_ifno' doesn't existmysql> 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 existmysql> 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: 0mysql> show create table customers_ifno;ERROR 1146 (42S02): Table 'Market.customers_ifno' doesn't existmysql> 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 1mysql> alter table orders engine=innodb;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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 1mysql> 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 1mysql> 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 1mysql> 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: 0mysql> 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: 0mysql> 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) 0 0
- mysql 数据表的基本操作
- MySQL:数据表的基本操作
- mysql:数据表的基本操作
- MySQL 数据表的基本操作
- mysql 数据表的基本操作
- MySQL数据表基本操作
- MySql数据库的基本操作-修改数据表
- mysql-数据表的基本操作1
- MySQL——数据表的基本操作
- MySQL（二）--数据表的基本操作
- Mysql数据库、数据表、数据的基本操作
- MySQL对数据表的基本操作语句
- MySQL数据库 之 数据表的基本操作
- MySql学习--数据表的基本操作--创建数据表
- 数据表的基本操作
- 数据表的基本操作
- 数据表的基本操作
- 数据表的基本操作
- Android性能调优工具TraceView介绍
- 文章标题
- javascript-jquery插件--FlexGrid 用法参数
- QT跨线程的信号与槽[2]---后台SOCKET
- UVAoj 11178Morley's Theorem
- mysql 数据表的基本操作
- cdev_init和cdev_add之后在/dev目录下没有长出设备节点原因
- Android内存监测工具DDMS->Heap,内存分析工具->MAT
- XtraBackup 实现不停机不锁表搭建mysql主从
- 深入理解Java注解(2)：高级应用
- POJ 1423.Big Number
- FZU 2177 动态规划
- 网络的五层协议体系---互联网协议入门
- 啰里啰嗦学IOS CoreDate入门
