数据表的基本操作

最新推荐文章于 2023-03-01 19:12:57 发布

对方真在输入...

最新推荐文章于 2023-03-01 19:12:57 发布

阅读量203

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45563049/article/details/104936815

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

关于表

表示数据库存储数据的基本单位，一个表可以包含若干个字段或者是记录。表的操作包括创建表、修改表和删除表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性约束的过程

1、创建表

数据表属于数据库，在创建数据表之前，应该使用“USE database_name;”移动到指定的数据库下，如果没有选择数据库，创建表时会报错

database_name ： 代表数据库名 可以任意指定

创建表的语法：

mysql> CREATE TABLE table_name
(
字段1   数据类型   [完整性约束条件],
字段2   数据类型   [完整性约束条件],
......
);

示例：

mysql> CREATE DATABASE example;
mysql> USE example;
mysql> CREATE TABLE student
    -> (
    -> name VARCHAR(20),
    -> sex VARCHAR(20),
    -> AGE INT(11));
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

mysql> DESC student;   //查看表的表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
| AGE   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

2、什么是完整性约束条件？

完整性约束条件对字段进行限制，要求用户只能向字段中写入符合条件的数据，如果不满足约束条件数据库将不执行写入操作。

常用的约束条件：

PRIMARY KEY: 标识该字段为该表的主键，可以唯一的标识数据。（特点：非空、唯一）
FOREIGN KEY: 标识该字段为该表的外键，是与之联系的某表的主键
NOT NULL：非空，标识该字段的值不能为空值
UNIQUE：唯一，标识该字段的值是唯一的
AUTO_INCREMENT：自增，标识该字段的值自动增加，一般对应INT，默认从1开始递增
DEFAULT：为该字段设置默认值
UNSIGNED：无符号，值从0开始，无负数
ZERFOFILL：零填充，当数据的显示长度不够的时候可以使用在前面补0的效果填充值指定长度，字段会自动添加UNSIGNED约束

2.1 使用主键约束

主键是一个特殊字段，每一个表只能设置一个主键。主键约束要求主键列的数据唯一，可以唯一
的标识表中的数据并且不能为空（非空且唯一）。
添加主键的语法：

1.在创建数据表的时候设置主键：

（1）单字段主键：

语法：字段名数据类型 PRIMARY KEY

mysql> CREATE TABLE example2
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(10),
-> sex VARCHAR(10)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC example2;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
±------±------------±-----±----±--------±------+

语法：在所有字段都规定好了之后，在所有字段的最后面来设置主键
字段1 数据类型 [完整性约束]，
字段2 数据类型 [完整性约束]，
字段N 数据类型 [完整性约束]，
[CONSTRAINT id_pri] PRIMARY KEY （字段）
CONSTRAINT 用来添加主键名，可以不添加，不添加主键名就是字段名

多字段主键：

字段1 数据类型 [完整性约束]，
字段2 数据类型 [完整性约束]，
字段N 数据类型 [完整性约束]，
[CONSTRAINT<主键名>] PRIMARY KEY (字段1,字段2,字段n)

mysql> create table example3
    -> (
    -> id int(11),
    -> name varchar(20),
    -> sex varchar(20),
    -> CONSTRAINT lh_pri PRIMARY KEY (id,name,sex)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc example3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | NO   | PRI |         |       |
| sex   | varchar(20) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

用修改表的方法添加主键：

ALTER TABLE 表名 ADD PRIMARY KEY (字段名)

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
| AGE   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE student ADD PRIMARY KEY (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   | PRI |         |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
| AGE   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.2使用外键

外键用来在两个数据表之间建立连接，它可以是一列也可以是多列；

语法：

字段1 数据类型 [完整性约束]，
字段2 数据类型 [完整性约束]，
字段N 数据类型 [完整性约束]，
[CONSTRAINT<外键名>] FOREIGN KEY (字段1,字段2,字段3) REFERENCES 主表 (主键列)

mysql> CREATE TABLE test
    -> (
    -> t_id INT(11),
    -> t_name VARCHAR(20),
    -> t_sex VARCHAR(20),
    -> CONSTRAINT f_id FOREIGN KEY (t_id) REFERENCES example2(id)
    -> );
Query OK, 0 rows affected (0.01 sec)


mysql> desc test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| t_id   | int(11)     | YES  | MUL | NULL    |       |
| t_name | varchar(20) | YES  |     | NULL    |       |
| t_sex  | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改表的方式来添加外键：

ALTER TABLE 子表名 ADD CONSTRAINT 外键名 FOREIGN KEY （字段）REFERENCES 主表名（主键列）

注意：
（1）关联的父表列，一定是父表的主键列
（2）关联的父表如果为联合主键时，先关联联合主键中的第一个字段
（3）关联父表时，被关联的外键的数据类型要和父表主键的数据类型一致，否则关联失败
（4）关联父表时，父表和子表的存储引擎必须为InnoDB，MyISAM引擎不支持外键功能
（5）避免书写错误
（6）如果两个表之间有外键关联，如果我们想要删除父表，必须先解除外键关系，或者先删除字表，否则无法删除父表

2.4 使用非空约束：

非空约束指字段的值不能为空。

语法：

在创建表时

字段名数据类型 NOT NULL

mysql> CREATE TABLE test2 
    -> (
    -> id INT(11),
    -> name VARCHAR(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC test2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.5 唯一约束：

给字段的值添加唯一属性，添加了之后，该字段不能插入重复的值。

语法：

1、直接在创建表时，在数据类型的后面加UNIQUE即可

2、在所有字段后面添加： [CONSTRAINT <约束名>] UNIQUE (字段);

mysql> create table test3
    -> (
    -> id int(11) unique
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc test3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.6 使用默认约束条件：

默认约束是给字段设置默认值，如果在插入数据的时候没有给该列赋值，则系统会自动将默认值插入到该列

语法：

字段名数据类型 DEFAULT (默认值)

mysql> CREATE TABLE test
    -> (
    -> id int(11),
    -> name varchar(20) DEFAULT 'wuming',
    -> sex varchar(20)
    -> );

mysql> CREATE TABLE test1 
    -> ( 
    -> id int(11) DEFAULT 0, 
    -> name varchar(20), 
    -> sex varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

2.7 设置自增约束：

要求字段的数据类型为数字类型

语法：

字段数据类型 UNIQUE AUTO_INCREMENT

mysql> CREATE TABLE test3 
    -> (
    -> id INT(11) UNIQUE AUTO_INCREMENT,
    -> name VARCHAR(20),
    -> sex VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test3(name,sex) VALUES('zhangsan','f'),('lisi','m');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test3;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | zhangsan | f    |
|  2 | lisi     | m    |
+----+----------+------+
2 rows in set (0.01 sec)
//未指定插入的ID 但是第一列ID序号会自动增加

3.修改数据表

3.1 修改表名

一个数据库里的每一个数据表的名称都是唯一的

语法：

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| student           |
| test              |
| test1             |
| test3             |
+-------------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE student RENAME test4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| test              |
| test1             |
| test3             |
| test4             |
+-------------------+
4 rows in set (0.00 sec)

注意：

可以通过修改表名的语法把A库里面的表移动到B库里面

ALTER TABLE

mysql> USE ll;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> USE example;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| test              |
| test1             |
| test3             |
| test4             |
+-------------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE test RENAME TO ll.test;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_example |
+-------------------+
| test1             |
| test3             |
| test4             |
+-------------------+
3 rows in set (0.00 sec)


mysql> USE ll;
Database changed
mysql> show tables;
+--------------+
| Tables_in_ll |
+--------------+
| test         |
+--------------+
1 row in set (0.00 sec)
//通过重命名的方法 将example库中的test 移动到 ll库中

3.2修改字段名：

语法：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新的数据类型>;

mysql> DESC test3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| sex   | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE test3 CHANGE id age INT(11);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESC test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.3修改字段的数据类型：

语法:

ALTER TABLE <表名> MODIFY <字段> <新数据类型>

mysql> DESC test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE test3 MODIFY age varchar(20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESC test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age   | varchar(20) | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加新的字段：

语法：

ALTER TABLE <表名> ADD <新字段名> <数据类型> <完整性约束条件> [first/after 已存在的字段];

如果不加后面方括号的内容默认添加字段到表的末尾 first和after可以自定义添加字段的位置

mysql> create table test
    -> (
    -> id int(11),
    -> name varchar(20),
    -> sex varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> ALTER TABLE test ADD age int(11) not null after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.5修改字段的排列位置

语法：

ALTER TABLE <表名> MODIFY <字段名> <数据类型> FIRST/AFTER <字段>

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE test MODIFY id int(11) AFTER age;      // 通过修改将第一行的ID移动到 age下面 如下图
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> ALTER TABLE test MODIFY id int(11) FIRST;   // first/after 后面不加字段名 表示移动到表的 首行和尾行
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.6删除字段

语法：

ALTER TABLE <表名> DROP <字段名>;

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE test DROP age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.7创建表的时候选择不同的存储引擎：

语法：

CREATE TABLE <表名>
(
字段1 数据类型约束条件,
字段2 数据类型约束条件.
…
) ENGINE=InnoDB/MyISAM;

3.8更改表的存储引擎

语法：

ALTER TABLE <表名> ENGINE=更改后的引擎;

3.9 删除数据表：

语法：

DROP TABLE [IF EXISTS] 表1,表1,表n;
[IF EXISTS]:不加这个，如果我们要删除的表不存在会报错，加上以后不存在也会执行操作，不会反悔错误信息。

mysql> show tables;
+-----------------+
| Tables_in_zuoye |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS test2;     //加了 IF EXISTS 之后 库中没有test2仍然执行操作 并未报错
Query OK, 0 rows affected, 1 warning (0.00 sec)

注意：如果我们要删除的表是另一个设置了外键的表的父表，无法直接删除，我们先需要删除外键或者是把子表删掉，才可以删除这个表。

3.10删除表的外键约束

语法：

ALTER TABLE <表名> DROP FOREIGN KEY <外键名>;

注意：如果没有设置外键名外键名就是字段名

mysql> create table test1
    -> (
    -> id int(11),
    -> name varchar(20),
    -> sex varchar(20),
    -> CONSTRAINT f_id FOREIGN KEY (id) REFERENCES test(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE test1 DROP FOREIGN KEY f_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

对方真在输入...

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据表的基本操作

关于表表示数据库存储数据的基本单位，一个表可以包含若干个字段或者是记录。表的操作包括创建表、修改表和删除表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性约束的过程1、创建表数据表属于数据库，在创建数据表之前，应该使用“USE database_name;”移动到指定的数据库下，如果没有选择数据库，创建表时会报错database_name ：代表数据库名可以任意指定...
复制链接

扫一扫