wshy-docssql基础语句
| 语句 | 作用 |
|---|---|
| SELECT id,city FROM Customers; | 从全部记录中返回id和city属性 |
SELECT DISTINCT city FROM Customers; | 从全部记录中返回不重复的city |
SELECT COUNT(DISTINCT city) FROM Customers; | 所有不重复的city的数量 |
SELECT * FROM CustomersWHERE Country='Mexico'; | 选择**Country='Mexico'**的记录 |
| SELECT * FROM Products WHERE Price BETWEEN 50 AND 60; | 选择Price 在50到60之间的记录 |
| SELECT * FROM Products WHERE Price <> 18; | 选择Price 不是18的记录 |
| SELECT * FROM Customers WHERE City LIKE 's%'; | 选择City以s开头的记录 |
| SELECT * FROM Customers WHERE City IN ('Paris','London'); | 选择City是Paris或者London的记录 |
| SELECT * FROM Products ORDER BY Price ASC; | 按Price由低到高升序排列 ASC可省略 |
| SELECT * FROM Products ORDER BY Price DESC; | 按Price由高到低降序排列 |
| SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; | 多条件排序 优先前者升序，后者降序 |
| SELECT * FROM Customers WHERE Country = 'Spain' AND CustomerName LIKE 'G%'; | 多条件筛选 名称以G开头并且西班牙客户 |
| SELECT * FROM Customers WHERE Country = 'Spain' OR CustomerName LIKE 'G%'; | 多条件筛选 名称以G开头或者西班牙客户 |
| SELECT * FROM Customers WHERE NOT Country = 'Spain'; | 非西班牙客户 |
| SELECT * FROM Customers WHERE CustomerName NOT LIKE 'A%'; | 名字非A开头 |
| INSERT INTO Users (id, name) VALUES (1, 'Tom'); | 插入数据 如果要插入全部数据且顺序正确时， Users后面的部分可以省略（不推荐） |
| INSERT INTO Users (id, name) VALUES (1, 'Tom1'), (2, 'Tom2'); | 插入多行，用逗号分隔 |
| SELECT *column_names *FROM table_name WHERE column_name IS NULL; | 查找表中空数据 |
| UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico'; | 更新语句，省略WHERE会更新全部 |
| DELETE FROM table_name WHERE condition; | 删除语句，省略WHERE会删除全部 |
| DROP TABLE Customers; | 删除表 |
| SELECT * FROM Customers LIMIT 3; | 截取前三个 |
| SELECT * FROM Customers ORDER BY CustomerName DESC; | 降序排序 |
| SELECT * FROM Customers ORDER BY CustomerName ASC; | 升序排序，ASC可忽略 |
| SELECT MIN(Price) FROM Products; | 最小值 |
| SELECT MAX(Price) AS SmallestPrice FROM Products; | 最大值并重命名 |
| SELECT COUNT(*) FROM Products; | 总个数 |
| SELECT COUNT(DISTINCT Price) FROM Products; | 去重后的总个数 |
| SELECT SUM(Quantity) FROM OrderDetails; | 总和 |
| SELECT AVG(Price) FROM Products; | 平均值 |
| SELECT * FROM Products WHERE price > (SELECT AVG(price) FROM Products); | 查找大于平均值的记录 |
| SELECT * FROM t WHERE a LIKE 'a%'; | 以a开头的记录，通配符%表示任意个字符 |
| SELECT * FROM t WHERE a LIKE 'a_'; | 通配符_表示一个字符 |
| SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); | 指定多个值查询NOT IN表示相反 |
| SELECT * FROM t1 WHERE id IN (SELECT id FROM t2); | 两个表联合查询 |
| SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; | 指定范围查询NOT BETWEEN表示相反 |
| SELECT * FROM Orders WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'; | 指定日期范围 |
| SELECT o.id, c.n, o.d FROM Orders o INNER JOIN Customers c ON o.id= c.id; | 内联接，选择列个表内有匹配值的记录INNER可省略 |
| SELECT column_names FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field; | 左联接，即时右表没有匹配值，也会填充NULLRIGHT JOIN右联接类似 |
| SELECT A.name AS n1, B.name AS n2, A.City FROM Customers A, Customers B WHERE A.name <> B.name AND A.City = B.City ORDER BY A.City; | 自连接，对同一个表中的数据进行排列组合 |
| SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; | 组合多个select的结果并去重，数据类型要一致UNION ALL不去重，速度会更快一点 |
| SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country; | 将结果集中的记录分组 通常与聚合函数（如 SUM、COUNT等）一起使用以便对每个分组执行某种计算 |
| SELECT Salesperson, SUM(Amount) AS TotalSales FROM Sales GROUP BY Salesperson HAVING SUM(Amount) > 10000; | WHERE 子句：分组前对行过滤，适用于非聚合条件。 HAVING 子句：分组和聚合后对分组进行过滤 |
| SELECT name FROM t1 WHERE EXISTS ( SELECT 1 FROM t2 WHERE t2.id= t1.id ); | 至少有一条订单的所有客户EXISTS用于判断子查询有没有返回 |
| SELECT name FROM t1 WHERE id = ANY (SELECT id FROM t2 WHERE q= 10); | 比较一个值与子查询返回的任意值ANY表示一个符合即可ALL表示全部都要符合 |
| CREATE TABLE new_employees AS SELECT * FROM employees WHERE join_date > '2010-01-01'; | 将查询到的值复制到一个新表中 |
| INSERT INTO t1(name, City) SELECT name, City FROM t2; | 将选择的值插入到另一个表中，要求数据格式正确 |
| SELECT Name, Department, Salary, CASE WHEN Department = 'Sales' THEN Salary * 1.10 WHEN Department = 'Engineering' THEN Salary * 1.15 ELSE Salary * 1.05 END AS AdjustedSalary FROM Employees; | CASE语句返回一个动态计算的值 |
| SELECT name, num1 + COALESCE(num2, 0) FROM Products; | COALESCE返回第一个非NULL值，可接受多个参数 IFNULL同作用，不过接收参数更只有两个 |
| DELIMITER // CREATE PROCEDURE UpdateSalary(IN EmpID INT, IN NewSalary DECIMAL(10,2)) BEGIN UPDATE Employees SET Salary = NewSalary WHERE EmployeeID = EmpID; END // DELIMITER ; | 创建函数 |
| CALL UpdateSalary(123, 75000.00); | 调用函数 |