Table of Contents
创建数据库
如果数据库不存在则创建,存在则不创建。
创建 test 数据库,并设定编码集为utf8
create database if not exists test default charset utf8;
直接创建数据库
create database test1;
删除数据库
drop database test1;
创建表
create table user(
id int unsigned not null AUTO_INCREMENT PRIMARY KEY,
username varchar(30) not null,
password char(32) not null,
email varchar(100) not null,
pic varchar(50) default './public/img/pic.jpg'
)engine=innodb default charset=utf8;
查看表结构
mysql> desc user;
+----------+------------------+------+-----+----------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+----------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| pic | varchar(50) | YES | | ./public/img/pic.jpg | |
+----------+------------------+------+-----+----------------------+----------------+
5 rows in set
查看建表语句
show create table user;
修改表结构
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set
添加列字段:
在表的最后添加字段
alter table 表名 add 字段名 字段信息
alter table sheng add age int not null default 18;
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| age | int(11) | NO | | 18 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set
在指定位置添加字段
alter table 表名 add 字段名 字段信息 after 指定的字段名
alter table sheng add aaa varchar(20) after pname;
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| aaa | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | 18 | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set
在最前面添加字段
alter table 表名 add 字段名 字段信息 first
alter table sheng add sex int not null first;
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| sex | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| aaa | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | 18 | |
+-------+------------------+------+-----+---------+----------------+
5 rows in set
添加多列
alter table 表名 add (字段名 字段信息 , 字段名 字段信息 , ... ...)
alter table sheng add (a int default 10,b int default 20,c int default 30);
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| sex | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| aaa | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | 18 | |
| a | int(11) | YES | | 10 | |
| b | int(11) | YES | | 20 | |
| c | int(11) | YES | | 30 | |
+-------+------------------+------+-----+---------+----------------+
8 rows in set
删除列
删除单个
alter table 表名 drop 字段名
alter table sheng drop a;
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| sex | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| aaa | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | 18 | |
| b | int(11) | YES | | 20 | |
| c | int(11) | YES | | 30 | |
+-------+------------------+------+-----+---------+----------------+
7 rows in set
删除多个
alter table 表名 drop 字段名, drop 字段名
alter table sheng drop b,drop c;
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| sex | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| aaa | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | 18 | |
+-------+------------------+------+-----+---------+----------------+
5 rows in set
修改字段
modify 只修信息,不修改名
alter table 表名 modify 字段名 修改字段信息
alter table sheng modify aaa int not null default 666;
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| sex | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| aaa | int(11) | NO | | 666 | |
| age | int(11) | NO | | 18 | |
+-------+------------------+------+-----+---------+----------------+
5 rows in set
change 修改信息,修改名
alter table 表名 change 字段名 修改的字段名 修改字段信息
alter table sheng change aaa class int not null default 5;
mysql> desc sheng;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| sex | int(11) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
| class | int(11) | NO | | 5 | |
| age | int(11) | NO | | 18 | |
+-------+------------------+------+-----+---------+----------------+
5 rows in set
添加主键
添加主键
alter table 表名 add [constraint [约束名]] primary key [index_type](列名,...)
mysql> desc user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set
alter table user2 add primary key (id);
mysql> desc user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set
添加外键
alter table students add foreign key(class_id) references class(id);
删除主键
mysql> alter table user2 drop primary key;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | PRI | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set
唯一索引
alter table user2 add unique (username);
mysql> desc user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set
删除唯一索引
alter table user2 drop index username;
添加和删除默认值
添加默认值
mysql> desc user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set
mysql> alter table user2 alter pid set default 20;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | | 20 | |
| id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set
删除默认值
mysql> alter table user2 alter pid drop default;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user2;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(20) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | |
+----------+----------------------+------+-----+---------+-------+
3 rows in set
修改表名3种
alter table 旧表名 rename [to|as] 新表名
rename table 旧表名 to 新表名
rename table lll to aaa,lll2 to bbb; # 修改多个表
mysql> alter table user2 rename as user;
Query OK, 0 rows affected
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set
修改 AUTO_INCREMENT 初始值
alter table 表名 auto_increment = 1
修改表类型
alter table 表名 ENGINE='InnoDB'
清空数据表
truncate table 表名
查看当前在哪个数据库
select database();