Reading Notes of TZX

<link rel="stylesheet" href="Photo-Sphere-Viewer/dist/photo-sphere-viewer.min.css">

<script src="three.js/three.min.js"></script>
<script src="D.js/lib/D.min.js"></script>
<script src="uevent/uevent.min.js"></script>
<script src="doT/doT.min.js"></script>
<script src="Photo-Sphere-Viewer/dist/photo-sphere-viewer.min.js"></script>

<script>
  var viewer = PhotoSphereViewer({
    container: 'container-id',
    panorama: 'path/to/panorama.jpg'
  });
</script>

-- select
SELECT column1, column2, ...
FROM table_name;
--
SELECT * FROM table_name;

-- distinct
SELECT DISTINCT column1, column2, ...
FROM table_name;

-- where
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- and, or, not
WHERE condition1 AND condition2 AND condition3 ...;
WHERE NOT condition;
WHERE NOT Country='Germany' AND NOT Country='USA';
WHERE Country='Germany' AND (City='Berlin' OR City='München');

-- order by (default to asc)
ORDER BY column1, column2, ... ASC|DESC;

-- insert into
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
--
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

-- null
WHERE column_name IS NULL;
WHERE column_name IS NOT NULL;

-- update
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- delete
DELETE FROM table_name
WHERE condition;

-- select top (count/percent), limits
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

-- min, max
SELECT MIN(column_name)
FROM table_name
WHERE condition;
--
SELECT MIN(Price) AS SmallestPrice

-- count, avg, sum
COUNT(column_name)

-- like
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
--
WHERE CustomerName LIKE 'a%';
WHERE CustomerName LIKE '_r%';
WHERE City LIKE '[bsp]%';
WHERE City LIKE '[!bsp]%';
WHERE City NOT LIKE '[bsp]%';

-- column in operator
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
--
SELECT * FROM Customers
WHERE Country NOT IN (SELECT Country FROM Suppliers);

-- between
WHERE column_name BETWEEN value1 AND value2;
--
WHERE Price BETWEEN 10 AND 20;
WHERE Price NOT BETWEEN 10 AND 20;
WHERE OrderDate BETWEEN #27/04/1996# AND #27/09/1996#;

-- alias column
SELECT column_name AS alias_name
FROM table_name;
-- alias table
SELECT column_name(s)
FROM table_name AS alias_name;
--
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
--
SELECT CustomerName, CONCAT(Address,',',PostalCode,',',City,',',Country) AS Address
FROM Customers;

-- inner join
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
--
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

-- left join
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

-- right join
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

-- full outer join
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

-- SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
-- no dups
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
--
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

-- group by
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

-- having
The HAVING clause was added to SQL because the WHERE keyword could not
be used with aggregate functions.
--
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
--
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

-- exists
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

-- any, all
The following SQL statement returns TRUE and lists the productnames if
it finds ANY records in the OrderDetails table that quantity = 10:
--
SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
--
The following SQL statement returns TRUE and lists the productnames if
ALL the records in the OrderDetails table has quantity = 10:
SELECT ProductName
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

-- select into (直接新建 table)
The SELECT INTO statement copies data from one table into a new table.
SELECT * INTO CustomersBackup2017
FROM Customers;
-- create table from another one
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

-- insert into
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
--
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

-- null functions: ifnull
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products
--
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products

CREATE DATABASE databasename;
DROP DATABASE databasename;

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

DROP TABLE table_name;

-- delete entries but not the table itself
TRUNCATE TABLE table_name;

ALTER TABLE table_name
--> ADD column_name datatype;
--> DROP COLUMN column_name;
--> ALTER COLUMN column_name datatype;
--> MODIFY COLUMN column_name datatype;