Tue, Jan 22, 2019
A quick tutorial for all my SQL queries.
After getting started and installing the dependencies for mysql, we run the following on the command line:
mysql -uroot -p[Insert password here]
Now we can begin the SQL fun.
Databases and Tables
SQL contains Tables located within Databases.
When we enter our SQL server, we can first check out which Database we want to enter. This is similar to the analogous “ls” command:
We always add a semicolon because the semicolon serves as a delimiter, that is, indicates that the query is finished.
To enter into the database and begin the query, we can proceed to perform the following:
use [name of a specific database];
When we are creating a new database, we can do the following:
CREATE DATABASE db;
Now we can delete
DROP DATABASE db;
Now that we are in a database, we can access all the table values. This is where we rinse and repeat.
To change out of a database into another database, we can still show and use database as shown above.
Creating a table is somewhat similar:
CREATE TABLE table ( Integer int, String varchar(255) );
Maybe occasionally you need to truncate to delete all the data inside a table:
TRUNCATE TABLE table
Adding a column of type DATE in our table:
ALTER TABLE Table ADD COLUMN DATE;
Once we are in a database with many tables, we can begin the SQL magic.
For the purposes of this next step, we will create designated keywords Table to be a designated table name, column to be a designated column name, such as the following:
SELECT Column FROM Table; SELECT * FROM Table; SELECT DISTINCT Column FROM Table;
We denote a text-based value as ‘String’
SELECT * FROM Table WHERE NOT Column = 'String';
Let’s do this for integer values as well. And/or statements are also pretty straightforward.
SELECT * FROM Table WHERE Column = 'String' AND Column2 = 1;
SELECT * FROM Table ORDER BY Column;
Let’s sort by Column first, then sort by Column2 reverse
SELECT * FROM Table ORDER BY Column, Column2 DESC;
INSERT INTO Table ( Column, Column2, Column3) VALUES ( 'String', 1, 'String2');
SELECT * FROM Table WHERE Column IS NOT NULL;
UPDATE & SET
Set the value of the Column column to ‘String’ and Column1 column to ‘String1’, but only the ones where the Column2 column has the value “String2”.
UPDATE Table SET Column = 'String', Column1 = 'String1' WHERE Column2 = 'String2';
DELETE FROM Table WHERE Column2 = 'String2';
If you are really sure about deleting a table, execute the following:
DELETE FROM Table;
Use the MIN function to select smallest of the Column column
SELECT MIN(Column) FROM Table;
The MAX function operates just as the MIN function would.
Return the number of records where Column value is set to 1
SELECT COUNT(*) FROM Table WHERE Column = 1;
Average value (AVG) and summation (SUM)
SELECT AVG(Column) FROM Table;
Select all records where the value of the Column column starts with the letter “a”.
SELECT * FROM Table WHERE Column LIKE 'a%';
Select all records where the value of the Column column ends with the letter “a”.
SELECT * FROM Table WHERE Column LIKE '%a';
Select all records where the second letter of the Column is an “a”.
SELECT * FROM Table WHERE Column LIKE '_a';
Select all records where the first letter of the Column is an “a” or a “b” or a “c”.
SELECT * FROM Table WHERE Column LIKE '[abc]%';
Select all records where the first letter of the Column is NOT an “a” or a “b” or a “c”.
SELECT * FROM Table WHERE City LIKE '[!abc]%';
IN AND BETWEEN
Use the IN operator to select all the records where Country is either “Thing1” or “Thing2”.
SELECT * FROM Table WHERE Column IN ('Thing1', 'Thing2');
The BETWEEN operator selects all the records where the value of Column column is NOT between 1 and 10
SELECT * FROM Table WHERE Column NOT BETWEEN 10 AND 20
When displaying the Table table, make an ALIAS of the Column column, the column should be called C instead.
SELECT Column AS C FROM Table;
Insert the missing parts in the JOIN clause to join the two tables Table1 and Table2, using the ID field in both tables as the relationship between the two tables.
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID;
Left join takes the outer join of the first table, right join takes the outer join of the second table, while outer join joins both tables.
List the number of customers in each country.
SELECT (CustomerID), Country FROM Customers GROUP BY Country;
An SQL Query generally follows the following style
SELECT / DELETE function([Column]) FROM Table WHERE Column condition;