MySQL基础——插入、更新和删除数据，创建和操纵表

最新推荐文章于 2022-07-05 15:11:30 发布

话说一半的王

最新推荐文章于 2022-07-05 15:11:30 发布

阅读量405

点赞数

分类专栏：《MySQL必知必会》学习笔记文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_41966900/article/details/105282115

版权

《MySQL必知必会》学习笔记专栏收录该内容

12 篇文章 0 订阅

订阅专栏

插入数据

INSERT 用来插入（或添加）行到数据库表中：

插入完整的行；
插入行的一部分；
插入多行；
插入某些查询的结果。

插入完整的行

指定表名和被插入到新行中的值

mysql> INSERT INTO Customers
    -> VALUES(NULL,
    -> 'Pep E. LaPew',
    -> '100 Main Street',
    -> 'Los Angeles',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
Query OK, 1 row affected (0.08 sec)

上述语句尽管简单，但很不安全，因为高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。
编写INSERT语句的更加安全（不过更繁琐）的方法如下：

mysql> INSERT INTO Customers(cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email)
    -> VALUES('Pep E. LaPew',
    -> '100 Main Street',
    -> 'Los Angeles',
    -> 'CA',
    -> '90046',
    -> 'USA');
Query OK, 1 row affected (0.01 sec)

使用这种语法，还可以省略列。省略的列必须满足以下某个条件：

该列定义为允许NULL值（无值或空值）
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

插入多个行

可以使用多条INSERT语句，用分号隔开。或者对插入的每组值用一对圆括号括起来，用逗号分隔。

mysql> INSERT INTO Customers(cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email)
    -> VALUES('Pep E. LaPew',
    -> '100 Main Street',
    -> 'Los Angeles',
    -> 'CA',
    -> '90046',
    -> 'USA'),
    -> ('M. Martian',
    -> '42 Galaxy Way',
    -> 'New York',
    -> 'NY',
    -> '11213',
    -> 'USA');

插入检索出的数据

使用INSERT INTO 表1(列) SELECT 列 FROM 表2;
INSERT SELECT中的列名 事实上，MySQL不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充INSERT表列中指定的第一个列，如此类推。

更新和删除数据

更新数据

为了更新（修改）表中的数据，可使用UPDATE语句。可采用两种方式使用UPDATE：

更新表中特定行；
更新表中所有行。

基本的UPDATE语句由3部分组成：

要更新的表；
列名和它们的新值；
确定要更新行的过滤条件。

举个例子，客户10005现在有了电子邮件地址，因此他的记录需要更新，语句如下：

mysql> UPDATE customers
    -> SET cust_email = 'elmer@fudd.com'
    -> WHERE cust_id = 10005;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

如果没有WHERE子句，UPDATE会用这个电子邮件地址更新customers表中所有行。
在更新多个列时，只需要使用单个set，不同’列=值’用逗号分隔。
UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。
可以通过用UPDATE语句设置NULL（假如表定义允许NULL值）来删除某个列的值。

IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行发生错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使发生错误也继续更新，可使用IGNORE关键字：UPDATE IGNORE customers…

删除数据

为了删除（去掉）表中的数据，可使用DELETE语句。可采用两种方式使用DELETE：

从表中删除特定的行；
从表中删除所有行。

mysql> DELETE FROM customers
    -> WHERE cust_id = 10006;
Query OK, 1 row affected (0.01 sec)

在使用DELETE时一定要注意不要省略WHERE子句，除非要删除表中所有行…

创建和操纵表

创建表

一般有两种创建表的方法：

使用具有交互式创建和管理表的工具
表也可以直接用MySQL语句操纵

CREATE TABLE 需要给出下列信息：

新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔。

CREATE TABLE语句也可能包含其他关键字或选项，但至少要包括表的名字和列的细节。

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

上面的例子应该注意一下几点：

列名应唯一，各列的定义间用逗号分隔
表的主键可以在创建时用PRIMARY KEY关键字指定
语句的格式化很重要，缩进对齐
不可以覆盖创建，即创建新表时，指定的表名必须不存在。如果仅想在如果不存在这个表的情况下创建它，应该在表明后给出IF NOT EXISTS，这样做只检查表名是否存在，并且在表名不存在时创建它。
主键必须是唯一的。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。主键中只能使用不允许NULL值的列。
AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。让MySQL自动增量方式生成主键的缺点是不知道这个值是多少，当然可以用Max函数来查询，但也可以使用SELECT last_insert_id()：

mysql> SELECT * FROM orders;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
+-----------+---------------------+---------+

mysql> INSERT INTO orders
    -> VALUES(NULL,
    -> '2005-10-10 00:00:00',
    -> 10001);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM orders;
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20005 | 2005-09-01 00:00:00 |   10001 |
|     20006 | 2005-09-12 00:00:00 |   10003 |
|     20007 | 2005-09-30 00:00:00 |   10004 |
|     20008 | 2005-10-03 00:00:00 |   10005 |
|     20009 | 2005-10-08 00:00:00 |   10001 |
|     20010 | 2005-10-10 00:00:00 |   10001 |
+-----------+---------------------+---------+

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|            20010 |
+------------------+

指定默认值

DEFAULT关键字 如果插入行时没有给出值，MySQL允许指定此时使用的默认值。
在CREAT TABLE语句中使用：
列名数据类型 NOT NULL DEFAULT 1
不支持使用函数作为默认值。

引擎类型

MySQL有一个具体管理和处理数据的内部引擎。
MySQL具有多种引擎，它们各具不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
如果省略ENGINE= 语句，则使用默认引擎（很可能是MyISAM）。

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用，但外键不能跨引擎。

更新表

为更新表定义，可使用ALTER TABLE语句。但理想状态下，当表中存储数据后，就不应该再被更新。
在ALTER TABLE更改表结构，必须给出下面的信息：

在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
所做更改的列表。

下面的例子给表添加/删除一个列：

mysql> ALTER TABLE vendors
    -> ADD vend_phone CHAR(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM vendors;
+---------+----------------+-----------------+-------------+------------+----------+--------------+------------+
| vend_id | vend_name      | vend_address    | vend_city   | vend_state | vend_zip | vend_country | vend_phone |
+---------+----------------+-----------------+-------------+------------+----------+--------------+------------+
|    1001 | Anvils R Us    | 123 Main Street | Southfield  | MI         | 48075    | USA          | NULL       |
|    1002 | LT Supplies    | 500 Park Street | Anytown     | OH         | 44333    | USA          | NULL       |
|    1003 | ACME           | 555 High Street | Los Angeles | CA         | 90046    | USA          | NULL       |
|    1004 | Furball Inc.   | 1000 5th Avenue | New York    | NY         | 11111    | USA          | NULL       |
|    1005 | Jet Set        | 42 Galaxy Road  | London      | NULL       | N16 6PS  | England      | NULL       |
|    1006 | Jouets Et Ours | 1 Rue Amusement | Paris       | NULL       | 45678    | France       | NULL       |
+---------+----------------+-----------------+-------------+------------+----------+--------------+------------+
6 rows in set (0.00 sec)

mysql> ALTER TABLE vendors
    -> DROP COLUMN vend_phone;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM vendors;
+---------+----------------+-----------------+-------------+------------+----------+--------------+
| vend_id | vend_name      | vend_address    | vend_city   | vend_state | vend_zip | vend_country |
+---------+----------------+-----------------+-------------+------------+----------+--------------+
|    1001 | Anvils R Us    | 123 Main Street | Southfield  | MI         | 48075    | USA          |
|    1002 | LT Supplies    | 500 Park Street | Anytown     | OH         | 44333    | USA          |
|    1003 | ACME           | 555 High Street | Los Angeles | CA         | 90046    | USA          |
|    1004 | Furball Inc.   | 1000 5th Avenue | New York    | NY         | 11111    | USA          |
|    1005 | Jet Set        | 42 Galaxy Road  | London      | NULL       | N16 6PS  | England      |
|    1006 | Jouets Et Ours | 1 Rue Amusement | Paris       | NULL       | 45678    | France       |
+---------+----------------+-----------------+-------------+------------+----------+--------------+
6 rows in set (0.00 sec)

ALTER TABLE的一种常见用途是定义外键。

mysql> ALTER TABLE orderitems
    -> ADD CONSTRAINT fk_orderitems_orders
    -> FOREIGN KEY (order_num) REFERENCES orders (order_num);

删除表

DROP TABLE tablename;

重命名表

RENAME TABLE tablename1 TO tablename2;

话说一半的王

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础——插入、更新和删除数据，创建和操纵表

插入数据INSERT 用来插入（或添加）行到数据库表中：插入完整的行；插入行的一部分；插入多行；插入某些查询的结果。插入完整的行指定表名和被插入到新行中的值mysql> INSERT INTO Customers -> VALUES(NULL, -> 'Pep E. LaPew', -> '100 Main Street', ...
复制链接

扫一扫

专栏目录