Abdu's Garden

SQL Syntax and intro

4 min read

Some of The Most Important SQL Commands

SELECT

  • Extracts data from a database

    Syntax like so:

    SELECT column1, column2, ...
FROM table_name;

  • To select all columns from a table: SELECT * FROM table_name

  • The SELECT DISTINCT statement is used to return only distinct (different) values: SELECT DISTINCT Country FROM Customers;

Projection: determines which columns from a table are returned by the query (according to this webpage)

WHERE

This clause is used to filter records. It is used to extract only those records that fulfill a specified condition. Example:

SELECT * FROM Customers
WHERE Name="Bob";

UPDATE

Updates data in a database.

Syntax example:

UPDATE table_name
SET column1 = value1,
		column2 = value2, ...
WHERE condition;
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index
  • We can signal out a primary key for a column using the keyword PRIMARY KEY
    • The feature AUTO_INCREMENT allows SQL to handle unique IDs and therefore don’t need include a primary key when adding a new tuple
  • To enforce no null values for a column we use the NOT NULL keyword
  • And the UNIQUE keyword ensures that for a particular column, all the values are unique and we have no repetition
  • TEXT is different from VARCHAR(n) where the latter is used to short strings (a few words), the prior is used to store longer strings such as whole sentences and paragraphs and whatnot.

Inserting a tuple into a table

INSERT INTO tableName (column1, column2, column3)
VALUES (
....
....
....
);
 
--AUTO_INCREMENT handling the primary key in this example

Limit results when selecting from a table

We can limit the resulting table we get from a SELECT query by using the keyword LIMIT n which limits the results to n rows

SELECT stdName, stdGPA
FROM   Students
LIMIT  10
WHERE  Students.stdGPA > 3.5;
 
-- this will only return 10 students even if there are more with GPAs over 3.5

Ordering the query

We can order the resulting table using the keywords ORDER BY <ordType>

Such that ordType can ASC or DESC

Searching for specific string values

The keyword LIKE is basically a full on comparator of strings Example:

SELECT stdName, stdGPA
FROM   Students
LIMIT  2
WHERE  Students.stdGPA > 3.5 AND Students.stdName LIKE "BoB%";

RELATIONAL TABLES

Have a primary key and a foreign key referencing another relation (table) and here is an example of how you create such a table

CREATE TABLE tableName(
id     INT AUTO_INCREMENT
prop1  VARCHAR(5)
prop2  INT
prop3  INT NOT NULL
PRIMARY KEY (id)
FOREIGN KEY (prop3) REFERENCES otherTableName(id)

JOIN

Used to query tables that have relationships and there are four types:

  • The type used determines the data returned from the query as seen in the illustration above.
  • When specifying conditions with JOINS we use the keyword ON instead of WHERE
  • So LEFT JOIN will return all the tuples in Left even if no relation to tuples in Right (We’ll probably get a few null values)
  • The RIGHT JOIN will return all the tuples in Right even if no relation to tuples in Left (We’ll probably get a few null values)
  • The INNER JOIN only returns the tuples in Left and Right that actually have relationship and reference one another
  • Outer Join returns everything but isn’t supported in MySQL so don’t worry about it

Deleting tables or database

The DROP keyword does just that

Graph View

Backlinks