mysql基础语法
1.distinct
SELECT DISTINCT 选择不同语句该SELECT DISTINCT语句仅用于返回不同(不同)的值。
语法:select distinct column1 from table_name;
例子:select count(distinct Country) FROM Customers; //出了不同客户国家/地区的数量 .
2.where
where语法:
SELECT column1, column2, ... FROM table_name WHERE condition;
例1:选取所有价格处于50和60之间的产品
SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
例2:从上面的Customers数据库搜索所有字母s开头的城市
SELECT * FROM Customers WHERE City LIKE 's%';
例3:从Customers数据库中查找所有在Paris和London的用户
SELECT * FROM Customers WHERE City IN ('Paris','London');
3.ORDER BY
该ORDER BY关键字用于按升序或降序对结果集进行排序。ORDER BY默认情况下,关键字按升序对记录进行排序。要按降序对记录进行排序,请使用 DESC关键字 。
ORDER BY 语法
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
例1:从“Customers”表中选择所有客户,按“Country”升序和“CustomerName”列降序排序
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
4.INSERT INTO 插入语句
INSERT语法
1- 指定要插入的列名和值:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
2-如果要为表的所有列添加值,则无需在 SQL 查询中指定列名。但是,请确保值的顺序与表中的列顺序相同。在这里, INSERT INTO语法如下:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
例1:以下 SQL 语句在“Customers”表中插入一条新记录
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
5.NULL 空值
IS NULL 语法
SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NOT NULL 语法
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
6.UPDATE更新语句
UPDATE 语法:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
例1:更新CustomerID = 1的用户名字和城市数据
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
7.DELETE删除语句
删除语法:
DELETE FROM table_name WHERE condition;
例1:从Customers表中删除Country值为“挪威”的所有记录
DELETE FROM Customers WHERE Country = 'Norway';
8.LIMIT
limit语法:
SELECT column_name(s) FROM table_name WHERE condition LIMIT number;
9.MIN() 和 MAX()函数求最大最小值
MIN() 语法
SELECT MIN(column_name) FROM table_name WHERE condition;
例1:查找最便宜产品的价格
SELECT MIN(Price) AS SmallestPrice FROM Products;
MAX() 语法
SELECT MAX(column_name) FROM table_name WHERE condition;
例2:查找最贵产品的价格
SELECT MAX(Price) AS LargestPrice FROM Products;
10.COUNT()、AVG() 和 SUM()函数
该COUNT()函数返回与指定条件匹配的行数。
COUNT() 语法
SELECT COUNT(column_name) FROM table_name WHERE condition;
该AVG()函数返回数字列的平均值。
AVG() 语法
SELECT AVG(column_name) FROM table_name WHERE condition;
该SUM()函数返回数字列的总和。
SUM() 语法
SELECT SUM(column_name) FROM table_name WHERE condition;
例1:查找产品数量
SELECT COUNT(ProductID) FROM Products;
例2:查找所有产品的平均价格
SELECT AVG(Price) FROM Products;
例3:查找“OrderDetails”表中“Quantity”字段的总和
SELECT SUM(Quantity) FROM OrderDetails;
11.LIKE运算符
该LIKE运算符在 WHERE子句中用于搜索列中的指定模式。有两个通配符经常与 LIKE运算符结合使用: 百分号 (%) 代表零、一个或多个字符 下划线 () 代表一个,单个字符
LIKE 语法
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
所对应意思为: 第一行:匹配任何以a开头的字段 第二行:匹配任何以a结尾的字段 第三行:匹配任何具有“or”的字段 第四行:查找第二个位置有“r”的任何值 第五行:查找任何以“a”开头且长度至少为 2 个字符的值 第六行:查找任何以“a”开头且长度至少为 3 个字符的值 第七行:查看以“a”开头并以“o”结尾的任何值
12.IN运算符
IN运算符允许您在 WHERE子句中指定多个值。 IN操作是针对多个速记 OR条件。
IN 语法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
例1:选择位于’Germany’, ‘France’, 'UK’的所有客户
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
例2:选择不在选择位于’Germany’, ‘France’, 'UK’的所有客户
SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');
例3:选择与 Suppliers来自相同国家的所有客户
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
13.BETWEEN 之间运算符
在BETWEEN操作者选择一个给定的范围内的值。值可以是数字、文本或日期。 BETWEEN 语法
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
例1:选择价格在 10 到 20 之间的所有产品。此外;不要显示 CategoryID 为 1,2 或 3 的产品
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3);
14.AS 别名使用
别名列语法
SELECT column_name AS alias_name FROM table_name;
别名表语法
SELECT column_name(s) FROM table_name AS alias_name;
15.GROUP BY语句
该GROUP BY语句将具有相同值的行分组为汇总行,例如“查找每个国家/地区的客户数量”。 该GROUP BY语句通常与聚合函数 ( COUNT(), MAX(), MIN(), SUM(), AVG()) 一起使用, 以按一列或多列对结果集进行分组。
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
例1:列出了每个国家/地区的客户数量
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
例2:列出了每个国家的客户数量,从高到低排序
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;
16.HAVING 子句
语法:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
例1:列出了每个国家/地区的客户数量。仅包括拥有超过 5 个客户的国家/地区
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
例2:列出了每个国家的客户数量,从高到低排序(仅包括客户超过 5 个的国家
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC;
17.EXISTS 运算符
EXISTS运算符用于测试子查询中是否存在任何记录。 EXISTS运算符返回true,如果子查询返回一个或多个记录。
语法
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
例1:返回 TRUE 并列出产品价格等于 22 的供应商
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);