Digital Technologies @ Waimea College

DT » Web Development » MySQL » Snippets

MySQL Snippets

These examples demonstrate the syntax for various MySQL CRUD operations:

• Create - INSERT queries
• Read - SELECT queries
• Update - UPDATE queries
• Delete - DELETE queries

SELECT Queries (to READ data)

Simple SELECT Query

Select (read) data from table fields

SELECT field1, field2, field3, field4

  FROM table1

SELECT Query with ORDER BY sorting

Select data from table and sorted via ORDER BY clause

SELECT field1, field2, field3, field4

  FROM table1

 ORDER BY field3 ASC, 
          field5 DESC

SELECT Query with WHERE filter

Select data from table with various filters via WHERE clause

SELECT field1, field2, field3, field4

  FROM table1

 WHERE field1 = 42

Multiple clauses can be added to the WHERE

SELECT field1, field2, field3, field4

  FROM table1

 WHERE field1 = 42               -- exact match
   AND field2 BETWEEN 0 AND 10   -- from 0 to 10
   AND field3 IS NOT NULL        -- not empty
   AND field4 IN ('A', 'B', 'C') -- one of A, B or C
   AND field5 LIKE '%text%'      -- contains 'text'
   AND field6 LIKE '_ing'        -- any letter, then 'ing'

Note: AND makes the filtering more specific - all rules have to be true. OR can be used if alternative matches are ok.

SELECT field1, field2, field3, field4

  FROM table1

 WHERE field1 = 42
    OR field2 BETWEEN 0 AND 10

SELECT Query with JOIN

Select data values from multiple tables via JOIN

SELECT table1.field1,
       table1.field2,
       table1.field3,
       
       table2.field1,
       table2.field2,
       
       table3.field1

  FROM table1
  JOIN table2 ON table1.foreign = table2.key
  JOIN table3 ON table2.foreign = table3.key

*Note: if the tables have fields of the same name, you will need to alias these using AS…

SELECT orders.id AS orderID,
       orders.date,
       users.id  AS userID,
       users.name

  FROM orders
  JOIN users ON orders.user = users.id

INSERT Queries (to CREATE records)

Simple INSERT Query

Insert (create) a new records in a table, with given data values

INSERT INTO table1 
            (field1, field2, field3, field4) 
     VALUES (value1, value2, value3, value4)

UPDATE Queries (to UPDATE existing data)

Simple UPDATE Query

Update values of fields in a specific record of a table

UPDATE table1

   SET field1 = value1, 
       field2 = value2, 
       field3 = value3

 WHERE keyField = id

UPDATE Query to Change All Records

To update values of fields in every record of a table, simply have no WHERE clause

UPDATE table1

   SET field1 = value1, 
       field2 = value2, 
       field3 = value3

DELETE Queries (to DELETE existing data)

Simple DELETE Query

Delete a specific record of a table

DELETE FROM table1

      WHERE keyField = id