示例表
mysql> DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | char(10) | NO | | NULL | |
| pirates | char(10) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| post | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
一、插入数据
插入完整行
使用 INSERT 插入完整行它要求指定 表名和插入到新行中的值。
mysql> INSERT INTO one_piece
-> VALUES('1',
-> '草帽海贼团',
-> '路飞',
-> 'age',
-> '团长',
-> '1500000000');
注意
- 必须每列提供一个值,空值使用 NULL
- 各列必须以它们在表定义中出现的次序填充
插入部分行
INSERT 推荐的插入方法是明确给出表的列名。这样还可以省略列,即只给某些列提供值,其他列不提供值。
省略的列必须满足以下某个条件:
- 该列定义为允许 NULL 值(无值或空值)。
- 在表定义中给出默认值(如果不给出值,将使用默认值)。
如果表中不允许有 NULL 值或者默认值,这时却省略了表中的值, DBMS 就会产生错误消息,相应的行不能成功插入。
现在同样在 one_piece 表中插入一行。
mysql> INSERT INTO one_piece(id,
-> pirates,
-> name)
-> VALUES('1',
-> '草帽海贼团',
-> '路飞');
不管使用哪种INSERT 语法,VALUES 的数目都必须正确。如果不提供列名,则必须给每个表列提供一个值;如果提供列名,则必须给列出的每个列一个值。否则,就会产生一条错误消息,相应的行不能成功插入。
从一个表复制到另一个表
有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表(运行中创建的表)。
mysql> CREATE TABLE one_pieceCopy AS
-> SELECT * FROM one_piece;
任何 SELECT 选项和子句都可以使用,包括 WHERE 和 GROUP BY。
可利用联结从多个表插入数据。
不管从多少个表中检索数据,数据都只能插入到一个表中。
主要用途:它是试验新 SQL 语句前进行表复制的很好工具。先进行复制,可在复制的数据上测试 SQL 代码,而不会影响实际的数据。
二、更新数据
使用 UPDATE 语句,更新(修改)表中的数据。
有两种使用 UPDATE 的方式:
- 更新表中的特定行
- 更新表中的所有行
使用时要明确是 更新特定行 还是 更新所有行。
UPDATE 语句中可以使用子查询,使得能用 SELECT 语句检索出的数据 更新列数据。
更新单行单列
将 路飞 的赏金更新为 10000000000
mysql> UPDATE one_piece
-> SET bounty = 10000000000
-> WHERE name = '路飞';
更新单行多列
在更新多个列时,只需要使用一条 SET 命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)。
mysql> UPDATE one_piece
-> SET bounty = 10000000000,
-> age = '19'
-> WHERE name = '路飞';
更新所有行
不使用 WHERE 限制条件,即更新表中所有行。
mysql> UPDATE one_piece
-> SET bounty = 10000000000,
-> age = '19'
三、删除数据
使用 DELETE 语句,删除表中的数据。
有两种使用 DELETE 的方式:
- 删除表中的特定行
- 删除表中的所有行
使用时要明确是 删除特定行 还是 删除所有行。
删除单行
删除 one_piece 表中 name 为 路飞 的行。
mysql> DELETE FROM one_piece
-> WHERE name = '路飞';
删除所有行
删除 Customers 中的所有行。不删除表本身。
mysql> DELETE FROM one_piece;
如果想从表中删除所有行,推荐使用 TRUNCATE TABLE 语句,它完成相同的工作,而速度更快(因为不记录数据的变动)。
但要注意: TRUNCATE 属于数据定义语言( DDL ),且 TRUNCATE 命令执行后无法回滚,使用 TRUNCATE 命令之前最好对当前表中的数据做备份。
mysql> TRUNCATE TABLE one_piece;
四、约束
约束
DBMS 通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的,用 CREATE TABLE 或是 ALTER TABLE 语句。
主键
主键是一种特殊的约束,用来保证一列(或 一组列)中的值是唯一的,而且永不改动。没有主键,要安全地 UPDATE 或 DELETE 特定行而不影响其他行会 非常困难。
主键的条件:
- 任意两行的主键值都不相同。
- 每行都具有一个主键值(即列中不允许 NULL 值)。
创建表时定义主键。
CREATE TABLE teacher
(
id INT(11) PRIMARY KEY,
teacher_name VARCHAR(10)
);
使用 ALTER TABLE 添加主键。
ALTER TABLE teacher
ADD CONSTRAINT PRIMARY KEY(id);
删除主键约束。
ALTER TABLE teacher DROP PRIMARY KEY;
外键
外键是表中的一列,其值必须列在另一表的主键中。外键是保证引用完 整性的极其重要部分。
下面新建 student 表并添加外键 teacher_id 与 teacher 表中的主键 id 进行关联。
在创建表的时定义外键。
CREATE TABLE student
(
stu_id INT(11) PRIMARY KEY,
teacher_id INT(11) REFERENCES teacher(id),
stu_name VARCHAR(10)
);
使用 ALTER TABLE 添加外键。
ALTER TABLE student
ADD CONSTRAINT teacher_id_id
FOREIGN KEY (teacher_id) REFERENCES teacher(id);
使用外键可以有效地防止意外删除,比如在上面两表中如果删除 teacher 表中的信息,如果该 id 在 student 表中也有出现,那么 Mysql 会防止删除操作。当然也可以启用级联删除的特性,那么在删除时就会删除所有相关信息。
删除外键
ALTER TABLE student DROP FOREIGN KEY teacher_id_id;
唯一约束
唯一约束用来保证一列(或一组列)中的数据是唯一的。它们类似于主 键,但存在以下重要区别。
- 表可包含多个唯一约束,但每个表只允许一个主键。
- 唯一约束列可包含 NULL 值。
- 与主键不一样,唯一约束不能用来定义外键。
在创建表的时定义唯一约束。
CREATE TABLE student
(
stu_id INT(11) PRIMARY KEY,
teacher_id INT(11) REFERENCES teacher(id),
stu_name VARCHAR(10)
);
使用 ALTER TABLE 添加唯一约束。
ALTER TABLE student
ADD CONSTRAINT unique_id UNIQUE(stu_id);
删除唯一性约束。
ALTER TABLE student DROP INDEX unique_id;
检查约束
检查约束用来保证一列(或一组列)中的数据满足一组指定的条件。
常见用途:
- 检查最小或最大值。
- 指定范围。
- 只允许特定的值。
下面创建一个检查约束来限制性别列只能输入男、女。
在创建表的时定义检查约束。
CREATE TABLE student
(
stu_id INT(11) PRIMARY KEY,
gender VARCHAR(1) CHECK(gender IN('男', '女'))
);
使用 ALTER TABLE 添加检查约束。
ALTER TABLE student ADD CONSTRAINT check_gender CHECK(gender in ('男', '女'));
删除检查约束。
ALTER TABLE student DROP CHECK check_gender;