SQL
Zhu, Justin

SQL
Tue, Jan 22, 2019


SQL

A quick tutorial for all my SQL queries.

Logging in

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.

show databases;

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.

show tables;

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.

SELECT

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;

WHERE

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;

ORDER BY

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

INSERT INTO Table (
Column,
Column2,
Column3)
VALUES (
'String',
1,
'String2');

NULL

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

DELETE FROM Table
WHERE Column2 = 'String2';

If you are really sure about deleting a table, execute the following:

DELETE FROM Table;

Functions

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.

COUNT

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;

LIKE

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

ALIAS

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;

JOIN

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.

GROUP BY

List the number of customers in each country.

SELECT (CustomerID),
Country
FROM Customers
GROUP BY Country;

Summary

An SQL Query generally follows the following style

SELECT / DELETE function([Column])
FROM Table
WHERE Column condition;