MySQL之表操作
♛素心♛ 人气:2基本操作
mysql -u root -p登陆MySQL服务器
-h : host (主机名)
-u : username （用户名）
-p : password （密码）
-- 查看现有的数据库
show databases;
-- 创建数据库
create databases testdb;
-- 删除数据库
drop database testdb;
-- 选定数据库
use testdb;数据表操作
语法：
create table 表名(
属性名 数据类型 [完整性约束],
属性名 数据类型 [完整性约束]),
......
);-- 创建表
create table example0(
id int,
name varchar(20),
sex boolean,
);主键
介绍：特殊的字段，能够唯一标识每条记录，不能重复
目的：为了让MySQL能以最快的速度查找到改记录
创建语法：
-- 单字段主键
属性名 数据类型 primary key
create table example1(
stu_id int primary key,
stu_name varchar(20),
stu_sex boolean
);
-- 多字段主键
primary key (属性1, ... , 属性n)
create table example2(
stu_id int,
course_id int,
grade_id float,
primary key (stu_id, course_id)
);外键
介绍：与父表建立关系的字段
原则：必须依赖与父表已存在的主键
目的：保证数据的完整性
语法：
constraint 外键别名 foreign key(字段1.1, 字段1.2) references 主表名(字段2.1， 字段2.2)create table example3(
id int primary key,
stu_id int,
course_id int,
constraint FK_STU_COURSE foreign key(stu_id, course_id) references example2(stu_id, course_id)
);注意：外键一定要和主表主键的类型一致
完整性约束
- 非空约束：
属性名 类型 not null - 唯一约束：
属性名 类型 unique - 自增长：
属性名 类型 auto_increment（类型必须是整型） - 默认值：
属性名 类型 default 默认值
create table example4(
id int primary key auto_increment,
stu_id int unique,
name varchar(20) not null,
address varchar(255) default 'China'
);表结构
查看表结构
基本结构
语法：describe 表名
可简写：desc 表名
mysql> desc example4;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| stu_id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
| address | varchar(255) | YES | | China | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)详细结构
语法：show create table 表名
mysql> show create table example4 \G;
*************************** 1. row ***************************
Table: example4
Create Table: CREATE TABLE `example4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_id` int(11) DEFAULT NULL,
`name` varchar(20) NOT NULL,
`address` varchar(255) DEFAULT 'China',
PRIMARY KEY (`id`),
UNIQUE KEY `stu_id` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
-- 这里的\G是格式化输出，方便查阅，无其他作用修改表
修改表名
语法：alter table 旧表名 rename [to] 新表名
mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| example0 |
| example1 |
| example2 |
| example3 |
| example4 |
+-------------------+
5 rows in set (0.00 sec)
mysql> alter table example0 rename user;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| example1 |
| example2 |
| example3 |
| example4 |
| user |
+-------------------+
5 rows in set (0.00 sec)修改字段
修改字段类型
语法：
alter table 表名 change 旧属性名 新属性名 属性类型mysql> desc example1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_id | int(11) | NO | PRI | NULL | | | stu_name | varchar(20) | YES | | NULL | | | stu_sex | tinyint(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table example1 change stu_id stu_id int(4); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc example1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_id | int(4) | NO | PRI | NULL | | | stu_name | varchar(20) | YES | | NULL | | | stu_sex | tinyint(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)修改字段名
mysql> desc example1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | stu_id | int(4) | NO | PRI | NULL | | | stu_name | varchar(20) | YES | | NULL | | | stu_sex | tinyint(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table example1 change stu_id id int(4); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc example1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | stu_name | varchar(20) | YES | | NULL | | | stu_sex | tinyint(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
新增字段
语法：alter table 表名 add 新属性名 新属性类型 [完整性约束] [first | after 原有字段]
新增无完整性约束的字段
语法：
alter table 表名 add 新属性名 新属性类型 原有字段mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table user add phone varchar(11); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)新增有完整性约束的字段
语法：
alter table 表名 add 新属性名 新属性类型 [完整性约束]mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table user add age int(3) not null; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)将字段添加到第一位
语法：
alter table 表名 add 新属性名 新属性类型 [完整性约束] firstmysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table user add num int primary key first; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)将字段添加到某个字段之后
语法：
alter table 表名 add 新属性名 新属性类型 [完整性约束] after 原有字段mysql> desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table user add address varchar(255) after phone; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | address | varchar(255) | YES | | NULL | | | age | int(3) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
删除字段
语法：alter table 表名 drop 字段名
mysql> desc user;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| num | int(11) | NO | PRI | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| age | int(3) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> alter table user drop id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| num | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| age | int(3) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)修改字段排列顺序
将字段按修改到首位
语法：
alter table 表名 modify 属性名 属性类型 [完整性约束] [first]mysql> desc user; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | address | varchar(255) | YES | | NULL | | | age | int(3) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table user modify name varchar(20) first; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | num | int(11) | NO | PRI | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | address | varchar(255) | YES | | NULL | | | age | int(3) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)将字段修改到指定位置
语法：
alter table 表名 modify 属性名 属性类型 [完整性约束] [after 原有字段]mysql> desc user; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | num | int(11) | NO | PRI | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | address | varchar(255) | YES | | NULL | | | age | int(3) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table user modify address varchar(255) after age; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | num | int(11) | NO | PRI | NULL | | | sex | tinyint(1) | YES | | NULL | | | phone | varchar(11) | YES | | NULL | | | age | int(3) | NO | | NULL | | | address | varchar(255) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
删除外键
语法：alter table 表名 drop foreign key 外键名;
清空表
清空表有两种方式
delete
语法：delete from 表名
Truncate
语法：truncate table 表名
删除表
语法：drop table 表名
mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| example1 |
| example2 |
| example3 |
| example4 |
| user |
+-------------------+
5 rows in set (0.00 sec)
mysql> drop table example4;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| example1 |
| example2 |
| example3 |
| user |
+-------------------+
4 rows in set (0.00 sec)
- 在删表的时候必须谨慎，以免误删而导致数据丢失，所以在删除前做好备份工作
- 在删除表时，如果当前表存在外键，则先删除外键，再删除表
- 在删除关联外键表时，则先删除子表[存在外键的表]，再删除主表
加载全部内容