数据库的基本操作

一  设置主键 primary key

　　方法一

　　

 create table t1(id int primary key,name char(4));

show create table t1;

+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

　　方法二

　　

 create table t2(id int,name char(4),primary key(id));

show create table t2;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

二 联合唯一

　　

mysql> create table t3(id int primary key,ip char(15),port int ,unique key(ip,port));
Query OK, 0 rows affected (0.27 sec)

mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| ip    | char(15) | YES  | MUL | NULL    |       |
| port  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

三 枚举与集合类型

　　枚举

mysql> create table t4(id int ,sex enum('male','female'));
Query OK, 0 rows affected (0.26 sec)

mysql> insert t4 values
    -> (1,'male'),
    -> (2,'男');
Query OK, 2 rows affected, 1 warning (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from t4;
+------+------+
| id   | sex  |
+------+------+
|    1 | male |
|    2 |      |
+------+------+
2 rows in set (0.00 sec)

　　集合：

mysql> create table t5(id int ,hobbies set('football','games','soccer','shopping'));
Query OK, 0 rows affected (0.29 sec)

mysql> insert t5 values
    -> (1,'football,shopping'),
    -> (2,'beauty');
Query OK, 2 rows affected, 1 warning (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from t5;
+------+-------------------+
| id   | hobbies           |
+------+-------------------+
|    1 | football,shopping |
|    2 |                   |
+------+-------------------+
2 rows in set (0.00 sec)

四 字符类型

　　char：

　　范围：0-255

　　定长，简单粗暴，浪费空间（待存储的数据长度<宽度限制），存取速度快。

　　varchar：

　　范围：0-21844
　　特点：
　　变长，精准，节省空间（待存储的数据长度<宽度限制），存取速度慢。

五 日期类型

　　date

　　time

　　datetime

　　timestamp

　　year

　

mysql> create table t6(date_time datetime,time_stamp timestamp);
Query OK, 0 rows affected (0.25 sec)

mysql> insert t6 values
    -> (now(),now());
Query OK, 1 row affected (0.06 sec)

mysql> select * from t6;
+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2017-10-23 19:27:34 | 2017-10-23 19:27:34 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> desc t6;
+------------+-----------+------+-----+-------------------+-----------------------------+
| Field      | Type      | Null | Key | Default           | Extra                       |
+------------+-----------+------+-----+-------------------+-----------------------------+
| date_time  | datetime  | YES  |     | NULL              |                             |
| time_stamp | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

六 数值类型

　　1 整型

　　使用默认参数就行。pass

　　2 小数

　　float

　　double

　　decimal

　　由上而下，精度越来越高。

　

七 复制表的操作。

　　

　　只是复制表的结构和记录 ，约束不会复制。（key不会复制: 主键、外键和索引）

　　

mysql> create table t2 select * from t1;
Query OK, 3 rows affected (0.27 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` char(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

　　

　　如果只想复制结构，很简单，where字句条件不成立即可。

mysql> create table t3 select * from t1 where 1>2;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 0       |       |
| name  | char(6) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from t3;
Empty set (0.00 sec)

八 重置序列

　　

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | alex |
|  2 | egon |
|  3 | yuan |
|  4 | wenz |
|  5 | wupe |
|  6 | jing |
+----+------+
6 rows in set (0.00 sec)

mysql> delete from t1 where id =4;
Query OK, 1 row affected (0.30 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | alex |
|  2 | egon |
|  3 | yuan |
|  5 | wupe |
|  6 | jing |
+----+------+
5 rows in set (0.00 sec)

mysql> alter table t1 drop id;
Query OK, 5 rows affected (0.70 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| name |
+------+
| alex |
| egon |
| yuan |
| wupe |
| jing |
+------+
5 rows in set (0.00 sec)

mysql> alter table t1 add id int primary key auto_increment first;
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0