mysql 创建操作表,MySQL操作汇总（单表创建查询）

最新推荐文章于 2023-03-10 20:15:30 发布

投资人股海明灯

最新推荐文章于 2023-03-10 20:15:30 发布

阅读量270

点赞数

文章标签： mysql 创建操作表

简介

数据库增删改查操作当中，查询是使用最广泛、最频繁的操作。即从表中检索一个或多个数据列的操作。我们可以通过一些图形化工具来进行信息查询，比如通过Navicat之类的工具。但是最多的还是通过编写SQL语句来查询，这样可以方便地嵌入到程序当中。SQL语句是由简单的英文单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。大概，最经常使用的SQL语句就是SELECT语句了。它的用途是从一个或者多个表中检索信息。

准备工作

一个MySQL数据库服务端可能包含多个数据库。每个数据库包含一到多张表。一张表是由行和列构成。我们可以使用以下指令来显示服务器中存在的数据库。

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| girls |

| halo |

| information_schema |

| iotat |

| myemployees |

| myflixdb |

| mysql |

| performance_schema |

| personinfo |

| south |

| sys |

+--------------------+

11 rows in set (0.00 sec)

为了执行查询操作，我们先创建一个单独的数据库用于测试，再往里面添加相应的表。以商品信息表为例，表格内容如下图，接下来我们展示如何创建这张表，并且插入相应的数据。

7629970acd6b

商品信息表

创建一个数据库

使用CREATE DATABASE databaseName来创建创建一个数据库，使用DROP DATABASE databaseName来删除一个数据库。也可以使用IF EXISTS或者IF NOT EXISTS来做这些之类，如下：

mysql> CREATE DATABASE southwind;

Query OK, 1 row affected (0.03 sec)

mysql> DROP DATABASE southwind;

Query OK, 0 rows affected (0.11 sec)

mysql> CREATE DATABASE IF NOT EXISTS southwind;

Query OK, 1 row affected (0.01 sec)

mysql> DROP DATABASE IF EXISTS southwind;

Query OK, 0 rows affected (0.00 sec)

注意DROP或者DELETE命令是不可逆的，谨慎使用。

使用数据库

使用命令USE databaseName来将一个数据库设置为默认数据库。你可以使用tableName来指定使用默认数据库中的一张表。但是如果要使用不在默认数据库中的表，那么可以使用databaseName.tableName来指定，即需要说明表是来自于哪一个数据库。在我们的例子中，我们创建了一个名为southwind的数据库，并且添加了一个名为products的表。如果我们使用USE southwind来将southwind设置为默认的表，那么我们可以直接通过products来引用这张表。

创建和删除一张表

我们可以使用CREATE TABLE tableName和DROP TABLE tableName来创建和删除表。要创建一张表，我们需要定义表中所有的列，通过提供列名、类型、以及属性。接下来，我们在southwind数据库中创建名为products的表。

mysql> CREATE DATABASE southwind;

Query OK, 1 row affected (0.01 sec)

mysql> USE southwind;

Database changed

-- Show the current (default) database

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| southwind |

+------------+

-- Show all the tables in the current database.

-- "southwind" has no table (empty set).

mysql> SHOW TABLES;

Empty set (0.00 sec)

创建表格products：

mysql> CREATE TABLE IF NOT EXISTS products (

productID INT UNSIGNED NOT NULL AUTO_INCREMENT,

productCode CHAR(3) NOT NULL DEFAULT '',

name VARCHAR(30) NOT NULL DEFAULT '',

quantity INT UNSIGNED NOT NULL DEFAULT 0,

price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,

PRIMARY KEY (productID)

);

Query OK, 0 rows affected (0.08 sec)

-- Show all the tables to confirm that the "products" table has been created

mysql> SHOW TABLES;

+---------------------+

| Tables_in_southwind |

+---------------------+

| products |

+---------------------+

-- Describe the fields (columns) of the "products" table

mysql> DESCRIBE products;

+-------------+------------------+------+-----+------------+----------------+

+-------------+------------------+------+-----+------------+----------------+

| quantity | int(10) unsigned | NO | | 0 | |

| price | decimal(7,2) | NO | | 99999.99 | |

+-------------+------------------+------+-----+------------+----------------+

我们一共定义了5个列，分别为productID, productCode, name, quantity, price，这些类型描述如下：

productID是INT类型的 -- 非负整数

productCode是CHAR(3)类型的，即由3个字符组成的固定长度的字母数字字符串。

name是VARCHAR(30) - 是一个边长的字符串，最多30个字符。

quantity 也是 INT UNSIGNED 类型的

price是DECIMAL(10,2) - 一个包含2个小数点的小数

属性NOT NULL代表这一列不能包含NULL值。我们将productID这一列当成所谓的主键。主键的值必须是唯一的。每张表都应该包含一个主键，这确保每一行都能与其他行区分开来。当然主键也不一定只包含一列，我们也可以同时设置多列共同充当主键。

插入行

现在我们创建好了prodcuts表，接下来我们需要插入一下数据到表中去。我们将第一条记录的productID设置为1001，对于剩下的记录，我们直接将productID设为'NULL'，因为我们设置了自增的属性，故其余的记录的编号会自动增加。数据插入如下：

mysql> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO products VALUES

-> (NULL, 'PEN', 'Pen Blue', 8000, 1.25),

-> (NULL, 'PEN', 'Pen Black', 2000, 1.25);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO products (productCode, name, quantity, price) VALUES

-> ('PEC', 'Pencil 2B', 10000, 0.48),

-> ('PEC', 'Pencil 2H', 8000, 0.49);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO products (productCode, name) VALUES ('PEC', 'Pencil HB');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);

ERROR 1048 (23000): Column 'productCode' cannot be null

mysql> SELECT * FROM products;

+-----------+-------------+-----------+----------+----------+

+-----------+-------------+-----------+----------+----------+

| 1001 | PEN | Pen Red | 5000 | 1.23 |

| 1002 | PEN | Pen Blue | 8000 | 1.25 |

| 1003 | PEN | Pen Black | 2000 | 1.25 |

| 1004 | PEC | Pencil 2B | 10000 | 0.48 |

| 1005 | PEC | Pencil 2H | 8000 | 0.49 |

| 1006 | PEC | Pencil HB | 0 | 99999.99 |

+-----------+-------------+-----------+----------+----------+

6 rows in set (0.00 sec)

mysql> DELETE FROM products WHERE productID = 1006;

Query OK, 1 row affected (0.01 sec)

数据库查询操作

数据库查询操作是最通用、最重要也是最复杂的任务，即使用SELECT语句从数据库查询出符合用户要求的数据，SELECT语句有以下语法：

-- List all the rows of the specified columns

SELECT column1Name, column2Name, ... FROM tableName

-- List all the rows of ALL columns, * is a wildcard denoting all columns

SELECT * FROM tableName

-- List rows that meet the specified criteria in WHERE clause

SELECT column1Name, column2Name,... FROM tableName WHERE criteria

SELECT * FROM tableName WHERE criteria

举个例子，从商品表中查询出name和price列：

mysql> SELECT name, price FROM products;

+-----------+-------+

| name | price |

+-----------+-------+

| Pen Red | 1.23 |

| Pen Blue | 1.25 |

| Pen Black | 1.25 |

| Pencil 2B | 0.48 |

| Pencil 2H | 0.49 |

+-----------+-------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM products;

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1001 | PEN | Pen Red | 5000 | 1.23 |

| 1002 | PEN | Pen Blue | 8000 | 1.25 |

| 1003 | PEN | Pen Black | 2000 | 1.25 |

| 1004 | PEC | Pencil 2B | 10000 | 0.48 |

| 1005 | PEC | Pencil 2H | 8000 | 0.49 |

+-----------+-------------+-----------+----------+-------+

5 rows in set (0.00 sec)

SELECT语句也可以不搭配表使用，举个例子，我们可以使用SELECT语句来对表达式或者内置函数进行求值：

mysql> SELECT 1+1;

+-----+

| 1+1 |

+-----+

| 2 |

+-----+

1 row in set (0.00 sec)

mysql> SELECT NOW();

+---------------------+

| NOW() |

+---------------------+

| 2012-10-24 22:13:29 |

+---------------------+

1 row in set (0.00 sec)

// Multiple columns

mysql> select 1+1, now();

+-----+---------------------+

| 1+1 | now() |

+-----+---------------------+

| 2 | 2021-03-09 20:02:46 |

+-----+---------------------+

1 row in set (0.00 sec)

比较运算符

对于数值类型的数据，我们可以使用表达式操作符，包含=, <>或者!=, >, >=, <=,等。举个例子，我们需要筛选出price>1，quantitiy<=500的数据。

mysql> SELECT name, price FROM products WHERE price < 1.0;

+-----------+-------+

| name | price |

+-----------+-------+

| Pencil 2B | 0.48 |

| Pencil 2H | 0.49 |

+-----------+-------+

2 rows in set (0.00 sec)

mysql> SELECT name, quantity FROM products WHERE quantity <= 2000;

+-----------+----------+

| name | quantity |

+-----------+----------+

| Pen Black | 2000 |

+-----------+----------+

1 row in set (0.00 sec)

mysql> SELECT name, price FROM products WHERE productCode = 'PEN';

-- String values are quoted

+-----------+-------+

| name | price |

+-----------+-------+

| Pen Red | 1.23 |

| Pen Blue | 1.25 |

| Pen Black | 1.25 |

+-----------+-------+

3 rows in set (0.00 sec)

字符串模式匹配 LIKE

对于字符串，除了使用全匹配操作符比如 '='和'<>'，也可以使用操作符LIKE或者NOT LIKE加上通配符的方式来进行模式匹配。通配符_可以匹配任意单个字符，%可以匹配任意数量字符，举个例子：

'abc%'匹配以abc开头的字符串

'%xyz' 匹配以xyz结尾的字符串

'%aaa%' 匹配包含aaa的字符串

'___' 匹配只包含3个字符的字符串

'a_b%' 匹配字符串以a开头，随后跟了一个单个字符，接下来跟一个字符b，最后是任意个字符

下面是几个示例：

-- "name" begins with 'PENCIL'

mysql> SELECT name, price FROM products WHERE name LIKE 'PENCIL%';

+-----------+-------+

| name | price |

+-----------+-------+

| Pencil 2B | 0.48 |

| Pencil 2H | 0.49 |

+-----------+-------+

-- "name" begins with 'P', followed by any two characters,

-- followed by space, followed by zero or more characters

mysql> SELECT name, price FROM products WHERE name LIKE 'P__ %';

+-----------+-------+

| name | price |

+-----------+-------+

| Pen Red | 1.23 |

| Pen Blue | 1.25 |

| Pen Black | 1.25 |

+-----------+-------+

数值运算操作符

可以使用数值运算操作符来对数值类型的数据进行操作，主要有以下操作符：

7629970acd6b

逻辑运算操作符

可以使用布尔值操作符AND, OR, XOR来组合多个条件。例子如下：

mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %';

+-----------+-------------+----------+----------+-------+

+-----------+-------------+----------+----------+-------+

| 1001 | PEN | Pen Red | 5000 | 1.23 |

| 1002 | PEN | Pen Blue | 8000 | 1.25 |

+-----------+-------------+----------+----------+-------+

mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %';

+-----------+-------------+---------+----------+-------+

+-----------+-------------+---------+----------+-------+

| 1001 | PEN | Pen Red | 5000 | 1.23 |

+-----------+-------------+---------+----------+-------+

mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %');

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1003 | PEN | Pen Black | 2000 | 1.25 |

| 1004 | PEC | Pencil 2B | 10000 | 0.48 |

| 1005 | PEC | Pencil 2H | 8000 | 0.49 |

+-----------+-------------+-----------+----------+-------+

IN、 NOT IN

使用IN操作符来判断一个元素是否存在或者不存在于一个集合中，这个比起ANR 和OR表达式更加简洁。例子：

mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1001 | PEN | Pen Red | 5000 | 1.23 |

| 1003 | PEN | Pen Black | 2000 | 1.25 |

+-----------+-------------+-----------+----------+-------+

BETWEEN, NOT BETWEEN

检查某个元素是否在某个范围之内，使用BETWEEN...AND...语法。例子：

mysql> SELECT * FROM products

WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1003 | PEN | Pen Black | 2000 | 1.25 |

+-----------+-------------+-----------+----------+-------+

IS NULL， IS NOT NULL

NULL是一个特殊值，代表没有值，缺失值或者未知值等。你可以通过IS NULL或者IS NOT NULL来检查一个列是否包含NULL。例子：

mysql> SELECT * FROM products WHERE productCode IS NULL;

Empty set (0.00 sec)

ORDER BY排序

ORDER BY关键字对选择的列进行排序，使用以下语法：

SELECT ... FROM tableName

WHERE criteria

ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...

选择的列会根据columnA进行排序，默认是升序排列，也可以通过指定DESC指定降序排列。例子如下：

mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC;

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1002 | PEN | Pen Blue | 8000 | 1.25 |

| 1003 | PEN | Pen Black | 2000 | 1.25 |

| 1001 | PEN | Pen Red | 5000 | 1.23 |

+-----------+-------------+-----------+----------+-------+

-- Order by price in descending order, followed by quantity in ascending (default) order

mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC, quantity;

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1003 | PEN | Pen Black | 2000 | 1.25 |

| 1002 | PEN | Pen Blue | 8000 | 1.25 |

| 1001 | PEN | Pen Red | 5000 | 1.23 |

+-----------+-------------+-----------+----------+-------+

LIMIT

SELECT语句可能会选出很多行，我们可以使用LIMIT关键字来指定要显示的行的数量：

-- Display the first two rows

mysql> SELECT * FROM products ORDER BY price LIMIT 2;

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1004 | PEC | Pencil 2B | 10000 | 0.48 |

| 1005 | PEC | Pencil 2H | 8000 | 0.49 |

+-----------+-------------+-----------+----------+-------+

也可以指定从多少行开始，显示多少行，语法为LIMIT s,l，其中s代表起始行，l代表长度。例子：

mysql> SELECT * FROM products ORDER BY price LIMIT 2, 1;

+-----------+-------------+---------+----------+-------+

+-----------+-------------+---------+----------+-------+

| 1001 | PEN | Pen Red | 5000 | 1.23 |

+-----------+-------------+---------+----------+-------+

AS 别名

可以使用AS关键字来为一行、一个表定义一个别名。这个别名会用来显示名称，也可以用来简化SQL编写。例子：

mysql> SELECT productID AS ID, productCode AS Code,

name AS Description, price AS `Unit Price` -- Define aliases to be used as display names

FROM products

ORDER BY ID; -- Use alias ID as reference

+------+------+-------------+------------+

+------+------+-------------+------------+

| 1001 | PEN | Pen Red | 1.23 |

| 1002 | PEN | Pen Blue | 1.25 |

| 1003 | PEN | Pen Black | 1.25 |

| 1004 | PEC | Pencil 2B | 0.48 |

| 1005 | PEC | Pencil 2H | 0.49 |

+------+------+-------------+------------+

CONCAT

CONCAT用来将若干列连接起来。例子：

mysql> SELECT CONCAT(productCode, ' - ', name) AS `Product Description`, price FROM products;

+---------------------+-------+

| Product Description | price |

+---------------------+-------+

| PEN - Pen Red | 1.23 |

| PEN - Pen Blue | 1.25 |

| PEN - Pen Black | 1.25 |

| PEC - Pencil 2B | 0.48 |

| PEC - Pencil 2H | 0.49 |

+---------------------+-------+

DISTINCT

一列可能有多个相同的值，可以使用DISTINCT关键字来进行数据去重。例子：

-- Without DISTINCT

mysql> SELECT price FROM products;

+-------+

| price |

+-------+

| 1.23 |

| 1.25 |

| 0.48 |

| 0.49 |

+-------+

-- With DISTINCT on price

mysql> SELECT DISTINCT price AS `Distinct Price` FROM products;

+----------------+

| Distinct Price |

+----------------+

| 1.23 |

| 1.25 |

| 0.48 |

| 0.49 |

+----------------+

-- DISTINCT combination of price and name

mysql> SELECT DISTINCT price, name FROM products;

+-------+-----------+

| price | name |

+-------+-----------+

| 1.23 | Pen Red |

| 1.25 | Pen Blue |

| 1.25 | Pen Black |

| 0.48 | Pencil 2B |

| 0.49 | Pencil 2H |

+-------+-----------+

GROUP BY分组

GROUP BY允许用户对具有公共值的多条记录进行分组操作。在使用GROUP BY子句之前，需要知道一些重要的规定：

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算(所以不能从个别的列取回数据)。

GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

GROUP BY函数本身没有什么意义，它一般搭配聚合函数使用，比如COUNT(),AVG(),SUM()等来产生分组之后的结果。例子：

mysql> SELECT productCode, count(*) as number FROM products GROUP BY productCode;

+-------------+--------+

| productCode | number |

+-------------+--------+

| PEN | 3 |

| PEC | 2 |

+-------------+--------+

2 rows in set (0.01 sec)

mysql> SELECT productCode, COUNT(*) AS count

-> FROM products

-> GROUP BY productCode

-> ORDER BY count ASC;

+-------------+-------+

| productCode | count |

+-------------+-------+

| PEC | 2 |

| PEN | 3 |

+-------------+-------+

2 rows in set (0.00 sec)

除了基本的COUNT()函数，还有许多其他的GROUP BY聚合函数，比如AVG(),MAX(),MIN()以及SUM()。例子如下：

mysql> SELECT productCode, MAX(price) AS `Highest Price`, MIN(price) AS `Lowest Price`

-> FROM products

-> GROUP BY productCode;

+-------------+---------------+--------------+

| productCode | Highest Price | Lowest Price |

+-------------+---------------+--------------+

| PEN | 1.25 | 1.23 |

| PEC | 0.49 | 0.48 |

+-------------+---------------+--------------+

2 rows in set (0.00 sec)

mysql> SELECT productCode, MAX(price), MIN(price),

CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,

CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,

SUM(quantity)

FROM products

GROUP BY productCode;

-- Use CAST(... AS ...) function to format floating-point numbers

+-------------+------------+------------+---------+---------+---------------+

+-------------+------------+------------+---------+---------+---------------+

| PEC | 0.49 | 0.48 | 0.49 | 0.01 | 18000 |

| PEN | 1.25 | 1.23 | 1.24 | 0.01 | 15000 |

+-------------+------------+------------+---------+---------+---------------+

HAVING语句

HAVING类似于WHERE，但是它可以与GROUP BY搭配使用，而WHERE只能作用于列上。例子：

mysql> SELECT

-> productCode AS `Product Code`,

-> COUNT(*) AS `Count`,

-> CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`

-> FROM products

-> GROUP BY productCode

-> HAVING Count >=3;

+--------------+-------+---------+

| Product Code | Count | Average |

+--------------+-------+---------+

| PEN | 3 | 1.24 |

+--------------+-------+---------+

1 row in set (0.01 sec)

HAVING和WHERE的差别，这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

UNION 组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。有两种基本情况，其中需要使用组合查询:

在单个查询中从不同的表返回类似结构的数据

对单个表执行多个查询，按单个查询返回数据

利用UNION操作符来组合数条SQL查询，并且将结果组合成单个结果集合。例子：

mysql> SELECT productID, name, price from products

-> WHERE name LIKE '%Black' AND price > 1.23

-> UNION

-> SELECT productID, name, price from products

-> WHERE productID IN (1004, 1005);

+-----------+-----------+-------+

| productID | name | price |

+-----------+-----------+-------+

| 1003 | Pen Black | 1.25 |

| 1004 | Pencil 2B | 0.48 |

| 1005 | Pencil 2H | 0.49 |

+-----------+-----------+-------+

3 rows in set (0.00 sec)

也可以对组合查询的结果进行排序。不过使用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。只能对结果集使用。例子：

mysql> SELECT productID, name, price from products

-> WHERE name LIKE '%Black' AND price > 1.23

-> UNION

-> SELECT productID, name, price from products

-> WHERE productID IN (1004, 1005)

-> ORDER BY price ASC;

+-----------+-----------+-------+

| productID | name | price |

+-----------+-----------+-------+

| 1004 | Pencil 2B | 0.48 |

| 1005 | Pencil 2H | 0.49 |

| 1003 | Pen Black | 1.25 |

+-----------+-----------+-------+

3 rows in set (0.00 sec)

UPDATE

如果想修改已经存在的数据，可以使用UPDATE...SET命令，语法如下:

UPDATE tableName SET columnName = {value|NULL|DEFAULT}, ... WHERE criteria

举个例子：

mysql> UPDATE products SET price = price * 2;

Query OK, 5 rows affected (0.02 sec)

Rows matched: 5 Changed: 5 Warnings: 0

mysql> SELECT * FROM products;

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1001 | PEN | Pen Red | 5000 | 2.46 |

| 1002 | PEN | Pen Blue | 8000 | 2.50 |

| 1003 | PEN | Pen Black | 2000 | 2.50 |

| 1004 | PEC | Pencil 2B | 10000 | 0.96 |

| 1005 | PEC | Pencil 2H | 8000 | 0.98 |

+-----------+-------------+-----------+----------+-------+

5 rows in set (0.01 sec)

mysql> UPDATE products SET quantity = quantity - 100 WHERE name = 'Pen Red';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM products WHERE name = 'Pen Red';

+-----------+-------------+---------+----------+-------+

+-----------+-------------+---------+----------+-------+

| 1001 | PEN | Pen Red | 4900 | 2.46 |

+-----------+-------------+---------+----------+-------+

1 row in set (0.00 sec)

mysql> UPDATE products SET quantity = quantity + 50, price = 1.23 WHERE name = 'Pen Red';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM products WHERE name = 'Pen Red';

+-----------+-------------+---------+----------+-------+

+-----------+-------------+---------+----------+-------+

| 1001 | PEN | Pen Red | 4950 | 1.23 |

+-----------+-------------+---------+----------+-------+

1 row in set (0.00 sec)

注意如果在UPDATE的时候，不指定WHERE语句的话，那么默认所有的行都会被更新。

DELETE ROW

使用DELETE FROM命令来删除一个表中的某些行，语法如下：

-- Delete all rows from the table. Use with extreme care! Records are NOT recoverable!!!

DELETE FROM tableName

-- Delete only row(s) that meets the criteria

DELETE FROM tableName WHERE criteria

举个例子：

mysql> DELETE FROM products WHERE name LIKE 'Pencil%';

Query OK, 2 row affected (0.00 sec)

mysql> SELECT * FROM products;

+-----------+-------------+-----------+----------+-------+

+-----------+-------------+-----------+----------+-------+

| 1001 | PEN | Pen Red | 4950 | 1.23 |

| 1002 | PEN | Pen Blue | 8000 | 1.38 |

| 1003 | PEN | Pen Black | 2000 | 1.38 |

+-----------+-------------+-----------+----------+-------+

-- Use this with extreme care, as the deleted records are irrecoverable!

mysql> DELETE FROM products;

Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM products;

Empty set (0.00 sec)

参考

投资人股海明灯

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 创建操作表,MySQL操作汇总（单表创建查询）

简介数据库增删改查操作当中，查询是使用最广泛、最频繁的操作。即从表中检索一个或多个数据列的操作。我们可以通过一些图形化工具来进行信息查询，比如通过Navicat之类的工具。但是最多的还是通过编写SQL语句来查询，这样可以方便地嵌入到程序当中。SQL语句是由简单的英文单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。大概，最经常使用的SQL语句就是SELECT语句了。它的用...
复制链接

扫一扫