MySQL SQL
SQL \text {SQL} SQL 是处理关系数据库的标准语言,用于插入、搜索、更新和删除数据库记录。
SQL \text {SQL} SQL 关键字不区分大小写:SELECT
与SELECT
相同。
https://en.wikiversity.org/wiki/Database_Examples/Northwind/MySQL
一些重要 的 SQL \text {SQL} SQL 命令
SQL \text {SQL} SQL 命令
含义
SELECT
extracts data from a database \text {extracts data from a database} extracts data from a database
UPDATE
updates data in a database \text {updates data in a database} updates data in a database
DELETE
deletes data from a database \text {deletes data from a database} deletes data from a database
INSERT INTO
inserts new data into a database \text {inserts new data into a database} inserts new data into a database
CREATE DATABASE
creates a new database \text {creates a new database} creates a new database
ALTER DATABASE
modifies a database \text {modifies a database} modifies a database
CREATE TABLE
creates a new table \text {creates a new table} creates a new table
ALTER TABLE
modifies a table \text {modifies a table} modifies a table
DROP TABLE
deletes a table \text {deletes a table} deletes a table
CREATE INDEX
creates an index (search key) \text {creates an index (search key)} creates an index (search key)
DROP INDEX
deletes an index \text {deletes an index} deletes an index
Northwind \text {Northwind} Northwind 数据库是一个示例数据库,最初由 Microsoft \text {Microsoft} Microsoft 创建用于演示其数据库产品 Microsoft Access \text {Microsoft Access} Microsoft Access 的功能。后来,它也被用于演示其他 Microsoft \text {Microsoft} Microsoft 数据库技术,如 SQL Server \text {SQL Server} SQL Server 和 Visual Studio \text {Visual Studio} Visual Studio 的数据工具。Northwind \text {Northwind} Northwind 数据库包含了一个虚构公司(名为“Northwind Traders \text {Northwind Traders} Northwind Traders ”)的业务数据,这个公司主要经营食品的批发。这个数据库通常用于教育和演示目的,因为它包含了典型的商业数据,如订单、产品、客户、员工和供应商等。通过这些数据,用户可以学习如何创建查询、执行数据分析和管理数据库。
Northwind \text {Northwind} Northwind 数据库包含的主要表格包括:
Customers(客户) : 客户信息。
Categories(类别) : 商品分类。
Employees(员工) : 员工信息。
Order Details(订单详情) : 订单的具体商品信息。
Orders(订单) : 订单信息。
Products(产品) : 产品信息。
Shippers(承运商) : 运输公司信息。
Suppliers(供应商) : 供应商信息。
MySQL SELECT
SELECT
:column1 \text {column1} column1 、column2 \text {column2} column2 、 ⋯ \cdots ⋯ 是要从中选择数据的表的字段名。
📝 SELECT Syntax \text {SELECT Syntax} SELECT Syntax
1 2 SELECT column1, column2, ...FROM table_name;
如果要选择表中可用的所有字段:
1 SELECT * FROM table_name;
⭐ 示例:从 customer \text {customer} customer 表中选择 CustomerName \text {CustomerName} CustomerName 、City \text {City} City 和 Country \text {Country} Country 列:
1 SELECT CustomerName, City, Country FROM Customers;
⭐ 示例:从 customer \text {customer} customer 表中选择所有列:
1 SELECT * FROM Customers;
📝 SELECT DISTINCT Syntax \text {SELECT DISTINCT Syntax} SELECT DISTINCT Syntax :
1 2 SELECT DISTINCT column1, column2, ...FROM table_name;
一列通常包含许多重复的值,仅用于返回不同的值。
⭐ 示例:比较从 customer \text {customer} customer 表中选择 Country \text {Country} Country 列:
从 customer \text {customer} customer 表的 Country \text {Country} Country 列中选择所有(包括重复的)值:
1 SELECT Country FROM Customers;
从 customer \text {customer} customer 表的 Country \text {Country} Country 列中选择 DISTINCT \text {DISTINCT} DISTINCT 值:
1 SELECT DISTINCT Country FROM Customers;
计算并返回 customer \text {customer} customer 表中不同国家的数目:
1 SELECT COUNT (DISTINCT Country) FROM Customers;
MySQL WHERE
WHERE
:用于筛选记录,提取满足指定条件的记录。
📝 WHERE Syntax \text {WHERE Syntax} WHERE Syntax :
1 2 3 SELECT column1, column2, ...FROM table_nameWHERE condition ;
❗ WHERE \text {WHERE} WHERE 子句不仅用于 SELECT \text {SELECT} SELECT 语句,还用于 UPDATE \text {UPDATE} UPDATE 、 DELETE \text {DELETE} DELETE 等!
⭐ 示例:从 customer \text {customer} customer 表中选择 Country \text {Country} Country 是 Mexico \text {Mexico} Mexico 的所有客户:
1 2 SELECT * FROM CustomersWHERE Country = 'Mexico' ;
Text Fields vs. Numeric Fields \text {Text Fields vs. Numeric Fields} Text Fields vs. Numeric Fields
文本值 周围使用单引号(大多数数据库系统也允许使用双引号),数字字段 不需要用引号括起来。
在 WHERE \text {WHERE} WHERE 可以使用的运算符:
Operator \text {Operator} Operator
=
等于
>
大于
<
小于
> =
大于等于
<=
小于等于
<>
不相等。**注意:**在某些版本的SQL中,此运算符可能写为!=
BETWEEN
在一定范围之间
LIKE
搜索模式
IN
为列指定多个可能的值
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 SELECT * FROM ProductsWHERE Price = 18 ;SELECT * FROM ProductsWHERE Price > 30 ;SELECT * FROM ProductsWHERE Price < 30 ;SELECT * FROM ProductsWHERE Price >= 30 ;SELECT * FROM ProductsWHERE Price <= 30 ;SELECT * FROM ProductsWHERE Price <> 18 ;SELECT * FROM ProductsWHERE Price BETWEEN 50 AND 60 ;SELECT * FROM CustomersWHERE City LIKE 's%' ;SELECT * FROM CustomersWHERE City IN ('Paris' ,'London' );
MySQL AND,OR,NOT
WHERE
子句可以与AND
、OR
和NOT
运算符组合。
📝 AND Syntax \text {AND Syntax} AND Syntax :
1 2 3 SELECT column1, column2, ...FROM table_nameWHERE condition1 AND condition2 AND condition3 ...;
📝 OR Syntax \text {OR Syntax} OR Syntax :
1 2 3 SELECT column1, column2, ...FROM table_nameWHERE condition1 OR condition2 OR condition3 ...;
📝 NOT Syntax \text {NOT Syntax} NOT Syntax :
1 2 3 SELECT column1, column2, ...FROM table_nameWHERE NOT condition ;
⭐ 示例:从 customer \text {customer} customer 表中选择 Country \text {Country} Country 是 Germany \text {Germany} Germany 、City \text {City} City 是 Berlin \text {Berlin} Berlin 的所有客户:
1 2 SELECT * FROM CustomersWHERE Country = 'Germany' AND City = 'Berlin' ;
⭐ 示例:从 customer \text {customer} customer 表中选择 City \text {City} City 是 Berlin \text {Berlin} Berlin 或 Stuttgart \text {Stuttgart} Stuttgart 的所有客户:
1 2 SELECT * FROM CustomersWHERE City = 'Berlin' OR City = 'Stuttgart' ;
⭐ 示例:从 customer \text {customer} customer 表中选择 Country \text {Country} Country 是 Germany \text {Germany} Germany 或 Spain \text {Spain} Spain 的所有客户:
1 2 SELECT * FROM CustomersWHERE Country = 'Germany' OR Country = 'Spain' ;
⭐ 示例:从 customer \text {customer} customer 表中选择 Country \text {Country} Country 不是 German \text {German} German 所有客户:
1 2 SELECT * FROM CustomersWHERE NOT Country = 'Germany' ;
⭐ 示例:从 customer \text {customer} customer 表中选择 Country \text {Country} Country 是 German \text {German} German ,City \text {City} City 是 Berlin \text {Berlin} Berlin 或 Stuttgart \text {Stuttgart} Stuttgart 的所有客户:
1 2 SELECT * FROM CustomersWHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart' );
⭐ 示例:从 customer \text {customer} customer 表中选择 Country \text {Country} Country 不是 German \text {German} German 和 USA \text {USA} USA 的所有客户:
1 2 SELECT * FROM CustomersWHERE NOT Country = 'Germany' AND NOT Country = 'USA' ;
MySQL ORDER BY
ORDERBY
用于按升序或降序对结果集进行排序。
默认情况下,按升序对记录进行排序(降序使用DESC
。
📝 ORDER BY Syntax \text {ORDER BY Syntax} ORDER BY Syntax :
1 2 3 SELECT column1, column2, ...FROM table_nameORDER BY column1, column2, ... ASC | DESC ;
⭐ 示例:从 customer \text {customer} customer 表中选择所有客户,并且按照 Country \text {Country} Country 列排序:
1 2 SELECT * FROM CustomersORDER BY Country;
⭐ 示例:从 customer \text {customer} customer 表中选择所有客户,并且按照 Country \text {Country} Country 列降序排序:
1 2 SELECT * FROM CustomersORDER BY Country DESC ;
⭐ 示例:从 customer \text {customer} customer 表中选择所有客户,并且按照 Country \text {Country} Country 列和 CustomerName \text {CustomerName} CustomerName 列排序:
1 2 SELECT * FROM CustomersORDER BY Country, CustomerName;
某些行有相同的国家,按照顾客名称排序
⭐ 示例:从 customer \text {customer} customer 表中选择所有客户,并且按照 Country \text {Country} Country 列升序排序和 CustomerName \text {CustomerName} CustomerName 列降序排序:
1 2 SELECT * FROM CustomersORDER BY Country ASC , CustomerName DESC ;
MySQL INSERT INTO
📝 INSERT INTO Syntax \text {INSERT INTO Syntax} INSERT INTO Syntax :
1 2 INSERT INTO table_name (column1, column2, column3, ...)VALUES (value1, value2, value3, ...);
指定要插入的列名和值。
1 2 INSERT INTO table_nameVALUES (value1, value2, value3, ...);
为所有列添加值。
⭐ 示例:在 customer \text {customer} customer 插入一条新记录:
1 2 INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal' , 'Tom B. Erichsen' , 'Skagen 21' , 'Stavanger' , '4006' , 'Norway' );
⭐ 示例:在 customer \text {customer} customer 插入一条新记录,但只在 CustomerName \text {CustomerName} CustomerName ,City \text {City} City ,Country \text {Country} Country 列插入数据:
1 2 INSERT INTO Customers (CustomerName, City, Country)VALUES ('Cardinal' , 'Stavanger' , 'Norway' );
MySQL NULL Values
📝 IS NULL Syntax \text {IS NULL Syntax} IS NULL Syntax :
1 2 3 SELECT column_namesFROM table_nameWHERE column_name IS NULL ;
📝 IS NOT NULL Syntax \text {IS NOT NULL Syntax} IS NOT NULL Syntax :
1 2 3 SELECT column_namesFROM table_nameWHERE column_name IS NOT NULL ;
⭐ 示例:从 customer \text {customer} customer 表中选择 Address \text {Address} Address 为空的客户:
1 2 SELECT * FROM CustomersWHERE Address IS NULL ;
⭐ 示例:从 customer \text {customer} customer 表中选择 Address \text {Address} Address 不为空的客户:
1 2 SELECT * FROM CustomersWHERE Address IS NOT NULL ;
MySQL UPDATE
📝 UPDATE Syntax \text {UPDATE Syntax} UPDATE Syntax :
1 2 3 UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition ;
⭐ 示例:从 customer \text {customer} customer 表中更新第一个客户 ContactName \text {ContactName} ContactName 为 Alfred Schmidt \text {Alfred Schmidt} Alfred Schmidt 和 City \text {City} City 为 Frankfurt \text {Frankfurt} Frankfurt :
1 2 3 UPDATE CustomersSET ContactName = 'Alfred Schmidt' , City = 'Frankfurt' WHERE CustomerID = 1 ;
⭐ 示例:从 customer \text {customer} customer 表中更新 Country \text {Country} Country 为 Mexico \text {Mexico} Mexico PostalCode \text {PostalCode} PostalCode 设置为 0000 \text {0000} 0000 的客户:
1 2 3 UPDATE CustomersSET PostalCode = 00000 WHERE Country = 'Mexico' ;
❗ 更新记录时要小心。如果省略WHERE
子句,则所有记录都将更新!
MySQL DELETE
📝 DELETE Syntax \text {DELETE Syntax} DELETE Syntax :
1 DELETE FROM table_name WHERE condition ;
⭐ 示例:从 customer \text {customer} customer 表中删除客户 Alfreds Futterkiste \text {Alfreds Futterkiste} Alfreds Futterkiste :
1 2 DELETE FROM Customers WHERE CustomerName= 'Alfreds Futterkiste' ;
删除表的所有行(表结构、属性和索引是完整的)
❗ 删除记录时要小心。如果省略WHERE
子句,则所有记录都将删除!
MySQL LIMIT
📝 LIMIT Syntax \text {LIMIT Syntax} LIMIT Syntax :
1 2 3 4 SELECT column1, column2, ...FROM table_nameWHERE condition LIMIT number;
⭐ 示例:从 customer \text {customer} customer 表中选择前三条记录:
1 2 SELECT * FROM CustomersLIMIT 3 ;
⭐ 示例:从 customer \text {customer} customer 表中选择第 4 ∼ 6 4 \sim 6 4 ∼ 6 条记录:
1 2 SELECT * FROM CustomersLIMIT 3 OFFSET 3 ;
⭐ 示例:从 customer \text {customer} customer 表中选择 Country \text {Country} Country 是 Germany \text {Germany} Germany 的前三条记录:
1 2 3 SELECT * FROM CustomersWHERE Country= 'Germany' LIMIT 3 ;
MySQL MIN and MAX
📝 MIN() Syntax \text {MIN() Syntax} MIN() Syntax :
1 2 3 SELECT MIN (column_name)FROM table_nameWHERE condition ;
📝 MAX() Syntax \text {MAX() Syntax} MAX() Syntax :
1 2 3 SELECT MAX (column_name)FROM table_nameWHERE condition ;
⭐ 示例:从 products \text {products} products 表中选择最便宜产品的价格:
1 2 SELECT MIN (Price) AS SmallestPriceFROM Products;
⭐ 示例:从 products \text {products} products 表中选择最昂贵产品的价格:
1 2 SELECT MAX (Price) AS LargestPriceFROM Products;
MySQL COUNT,AVG, SUM
📝 COUNT() Syntax \text {COUNT() Syntax} COUNT() Syntax :
1 2 3 SELECT COUNT (column_name)FROM table_nameWHERE condition ;
📝 AVG() Syntax \text {AVG() Syntax} AVG() Syntax :
1 2 3 SELECT AVG (column_name)FROM table_nameWHERE condition ;
📝 SUM() Syntax \text {SUM() Syntax} SUM() Syntax :
1 2 3 SELECT SUM (column_name)FROM table_nameWHERE condition ;
⭐ 示例:从 products \text {products} products 表中选择产品数目:
1 2 SELECT COUNT (ProductID)FROM Products;
⭐ 示例:从 products \text {products} products 表中选择产品平均价格:
1 2 SELECT AVG (Price)FROM Products;
⭐ 示例:从 orderdetails \text {orderdetails} orderdetails 表中选择产品的订购数量总和:
1 2 SELECT SUM (Quantity)FROM OrderDetails;
❗ 以上都忽略了 NULL \text {NULL} NULL 值。
MySQL LIKE
LIKE
指定在列中搜索指定的模式,包含两个通配符: