SQL: ORDER BY - DELETE FROM - DROP TABLE - UPDATE - LIMIT/OFFSET

 SQL: ORDER BY  - DELETE FROM - 

DROP TABLE - UPDATE - LIMIT/OFFSET

ORDER BY is ascending by default but can be descending with the DESC additional keyword.

"SELECT * FROM players ORDER BY name"

"SELECT * FROM players ORDER BY name DESC"

name is a column in the values part of the table players.

-----------------------

DELETE FROM table_name WHERE condition

deletes records only.

forgetting to use WHERE will delete all records

A .commit() is required.

Use %s in the WHERE qualifier to prevent SQL injections.

sql = "DELETE FROM players WHERE name = %s"

nam = ('Smith',)

mycursor.execute(sql, nam)

mydb.commit()

--------------------------

sql = "DROP TABLE IF EXISTS table_name"

removes table and all of its records.

This will remove all records and no .commit() needed. The optional IF EXISTS will prevent an error message.

------------------------------

UPDATE is a more complex SQL statement.

The format is:

sql_comm = "UPDATE table_name SET column_name='user_input' WHERE column_name='user_value'"

If you forget the WHERE conditional, then all records will be updated to user_input.

A .commit() call is required and it is best to use %s holder to prevent SQL injection.

Note that when 2 or more placeholders are used, then a trailing comma is not needed in the tuple.

sql_command = "UPDATE players SET name = %s WHERE name = %s"

person = ('Jonathan', 'John')

mycursor.execute(sql_command, person)

mydatab.commit()

print(mycursor.rowcount, "record(s) affected")

.rowcount() gives the number of matches.

-----------------------

Use LIMIT at the end of the SELECT statement to restrict the first # matches.

sql_command = "SELECT * FROM players WHERE name = %s LIMIT 3"

nam = ('Steve',)

mycursor.execute(sql_command, nam)

just returns the first 3 names of Steve, even if there are 4 or more matches.

Since results are returned in ascending key order, the optional OFFSET keyword will stagger down the list and thus skip the OFFSET # of first (early) matches.

sql_command = "SELECT * FROM players WHERE name = %s LIMIT 3 OFFSET 3"

makes the first returned match be the 4th actual match overall in the table.