数据库的基本操作
一 设置主键 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