SQL基础

这是一篇关于SQL基础的思维导图，包括：Select 、select Distinct、Where、And,Or,Not、Order By 、lnsertlnto等。

SQL

• basic
  • Select
  • Select Distinct
  • Where
  • And,Or,Not
  • Order By
    • SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
  • Insert Into
    • INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
  • Null values
    • SELECT column_names FROM table_name WHERE column_name IS NULL;
  • Update
    • UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
  • Delete
    • DELETE FROM table_name WHERE condition;
  • Select Top
    • Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.
  • Min and Max
    • SELECT MIN(column_name) FROM table_name WHERE condition;
  • Count,Avg,Sum
    • SELECT COUNT(column_name) FROM table_name WHERE condition;
  • Like
    • SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
  • Wildcard
    • *?[]!-#
  • In
    • SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
  • Between
    • SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;
  • Aliases
  • Join
    • inner left right full self
  • Union
    • The UNION operator is used to combine the result-set of two or more SELECT statements.
  • Grounp
    • SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
  • Having
    • SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) ASC;
  • Exists
    • SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
  • Any，All
    • SELECT ProductName FROM Products WHERE ProductID = Any (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
  • Select Into
    • The SELECT INTO statement copies data from one table into a new table.
  • Insert Into Select
    • The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match
  • Case
    • SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30' WHEN Quantity = 30 THEN 'The quantity is 30' ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails;
  • Null Fucntions
    • In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.
  • Stored Procedures
  • Comments
  • Operators
• senior