mysql库表操作

一：创建数据库 Market，在 Market 中创建数据表customers。

首先创建数据库

mysql> create database Market
    -> ;
Query OK, 1 row affected (0.00 sec)
 
mysql> use Market
Database changed

然后创建列表

mysql> create table customers
    -> ;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table customers(
    -> c_num INT(11) primary key auto_increment not null,
    -> c_name varchar(50),
    -> c_contact varchar(50),
    -> c_city varchar(50),
    -> c_birth datetime not null);
mysql> desc customers;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| c_num     | int(11)     | NO   | PRI | NULL    | auto_increment |
| c_name    | varchar(50) | YES  |     | NULL    |                |
| c_contact | varchar(50) | YES  |     | NULL    |                |
| c_city    | varchar(50) | YES  |     | NULL    |                |
| c_birth   | datetime    | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

然后创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加非空约束。

        CREATE TABLE customers (
    ->   `c_num` int(11) NOT NULL AUTO_INCREMENT,
    ->   `c_name` varchar(50) NULL,
    ->   `c_city` varchar(50) NULL,
    ->   `c_birth` datetime NOT NULL,
    ->   PRIMARY KEY (`c_num`)
    -> );

再然后将c_contact字段插入到c_birth字段后面

alter table customers ADD c_contact varchar(50)；

修改字段c_name类型：

mysql> alter table customers 
    -> modify c_name varchar(70);
Query OK, 0 rows affected (0.00 sec)

修改字段名字：

mysql> alter table customers
    -> change c_contact c_phone varchar(70);

增加字段：

mysql> alter table customers 
    -> add c_gender char(1);
Query OK, 0 rows affected (0.00 sec)

修改表名：mysql> alter table customers rename customers_info;
删除字段：

mysql> alter table customers_info
    -> drop c_city;

修改存储引擎：

mysql> alter table customers_info 
    -> ENGINE=MyISAM;

二：在 Market中创建数据表orders

创建orders表

mysql> mysql> ctreate table orders(
    ->     ->  o_num INT(11) primary key AUTO_INCREMENT not null,
    ->     -> o_date DATE,
    ->     -> c_id varchar(50),
    ->     -> );
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 'mysql> ctreate table orders(
    ->  o_num INT(11) primary key AUTO_INCREMENT no' at line 1
mysql> create table orders(  o_num INT(11) primary key AUTO_INCREMENT not null,  o_date DATE, c_id varchar(50) REFERENCES customers_info(c_num));;
Query OK, 0 rows affected (0.01 sec)
 
mysql> desc orders;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| o_num  | int(11)     | NO   | PRI | NULL    | auto_increment |
| o_date | date        | YES  |     | NULL    |                |
| c_id   | varchar(50) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

添加外键

tips:创建外键错误的原因大概有一下几个原因：
1、关联的两个字段的字段的类型不一致
2、设置外键删除时set null (有无符号)
3、两张表的引擎不一致

mysql> alter table orders add FOREIGN KEY(c_id)  REFEREnces customers_info(c_num);
Query OK, 0 rows affected (0.01 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(10) 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=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table orders drop foreign key orders_ibfk_1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0