12. SQL refresher

Here we will meet some of the SQL core vocabulary.

12.01 Changing data

SELECT * FROM planets WHERE diameter > 5;

SELECT lets us query the database to get all or certain items according to conditions or constraints (in this case, diameter > 5).

The main clauses you need to remember are SELECT, FROM and WHERE.

SELECT * FROM planets WHERE name LIKE '%er';

We can do approximate matching with LIKE. This will retrieve all names using a wildcard %. It is case insensitive.

SELECT DISTINCT Title, Year, Name, DOB FROM Locations JOIN Actors on Actor1=ENName LIMIT 0,10;

This is an example of selecting records from multiple tables, in this case Locations and Actors.

The fields Title, Year, Name, DOB appear in either of the two tables – we’re not specifying which tables here.

LIMIT 0,10 limits us to 10 records, starting at line 0.

The use of DISTINCT is to remove any duplicates, as the Locations table may have duplicate entries for these fields.

UPDATE planets SET name='Mars' WHERE name='Mers';

UPDATE changes data that already exists. We use SET to say what we need to change it to, and WHERE to show which items need changing.

INSERT INTO planets (name, diameter) VALUES ('Gethen', 9483);

INSERT adds new rows. We can optionally name columns. If we don’t put any names in it assumes we’re adding in all of the columns. The query returns an error if you don’t give enough information (values) to fill all of those columns.

INSERT INTO planets (name, diameter) VALUES ('Gethen', 9483), ('Athshe', 8210);

We can add as many new rows as we want by separating those bracketed groups with commas. This can be better than having lots of INSERT commands inserting one piece of data each time.

INSERT INTO planets (name) SELECT DISTINCT name FROM moons;

We can also INSERT the results of a query into a table. This command above would run a query SELECT DISTINCT name FROM moons; and find all the planet names in there, and then insert them into planets.

DELETE FROM planets WHERE diameter < 2500;

This command deletes all the rows according to the condition attached, i.e. diameter < 2500. 12.02 Changing the database structure

DROP TABLE planets;

This deletes the table planets.

TRUNCATE TABLE planets;

This is less dramatic than DROP. This removes all the entries in the table, but leaves the structure intact.

This is different to DELETE * from planets, because that triggers all the cascades in foreign keys and deletes related items. TRUNCATE is just a straightforward removal of data with none of the logic attached.

CREATE TABLE planets (
PlanetName CHAR(8),
DayLength (INT),
YearLength (INT),
PRIMARY KEY (PlanetName)
);

The CREATE command creates a table with a list of all the columns, primary key etc.

ALTER TABLE planets ADD COLUMN Diameter INT;

If you create a table and something isn't quite right about it, you don't need to delete it and start again. You can use the ALTER TABLE command just to tweak something, say to change the type of a column or the text encoding used, or the names of a column, etc.

12.03 Getting data in and out of a database

SELECT * FROM planets INTO OUTFILE 'planets.txt';

INTO OUTFILE will export data into a CSV file called planets.txt.

LOAD DATA INFILE 'planets.txt' INTO TABLE planets;

Similarly this loads data into a table from an external file.

Tuesday 23 November 2021, 478 views

Next post: 13. Grouping data in SQL
Previous post: 11. Malice and accidental damage