掌握创建表,添加各类约束,查看表结构,以及删除也修改操作.
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)