SQLis a programming language that talks with the database
sqlstatement myst end with a semi-colon
- Uppercase for
FROMis a convention, recommended but not required.
- We search columns first and tables next
WHERElet’s you specify conditions to filter data. You use conditional operators to build your conditions
ORlet you specify multiple conditions.
ANDmeans all conditions must be met,
ORmeans any condition can be met
ORDER BYis for sorting data in
- When adding and deleting data, make sure to provide a
WHEREclause, or it’ll mess with the entire table or columns.
Getting and filtering existing data
We retrieve data using
# SELECT ___ FROM ___; # SELECT columnName FROM tableName; # select the `title` column from the `movies` table SELECT title FROM movies; # select data from multiple columns SELECT id, title, genre, duration FROM movies; # select all columns SELECT * FROM movies;
- To filter data we use the
WHEREclause (within a
- The sql command isn’t done when it finds the particular item, it continues till it’s done with all entries (e.g. If you’re looking for a movie title with an id of 2, it’ll find 2, and continue through the rest 3,4,5 and so on until it has gone through all entries, to make sure there was only one cell with id of 2)
# SELECT ___ FROM ___ WHERE ___; # SELECT columnName FROM tableName WHERE condition; SELECT title FROM movies WHERE id = 2; # numbers SELECT * FROM movies WHERE title = 'The Kid'; # strings (match exact sequence)
Sort by Order
ORDER BYclause is used to sort data in ascending
# SELECT ___ FROM ___ ORDER BY ____ ASC|DESC; SELECT title FROM movies ORDER by duration; # get movie titles in (default) ascending order of duration (shortest first) SELECT title FROM movies ORDER by duration DESC; # get movie titles in descending order of duration (longest first)
Sort by Range
- We can specify a range using comparison operators
<>is same as
!=, it means not equal to
# SELECT ___ FROM ___ WHERE ____ > ____; SELECT * FROM movies WHERE duration > 100; SELECT * FROM movies WHERE duration < 100; SELECT * FROM movies WHERE duration >= 94;
Sort by multiple conditions
ORoperators let’s you add multiple conditions
- When you use
ANDto combine multiple conditions, the all conditions must be met to get any results
ORgives you results if any of the multiple conditions is met
SELECT title FROM movies WHERE id = 1 AND genre = 'Comedy'; # with AND, both conditions must be met SELECT title FROM movies WHERE id = 1 OR genre = 'Comedy'; # will get results if either condition is met
INSERTis the keyword for adding data.
- The values must be in the respective order of column names you mention. The first column you mention will get the first value you specify. If you’re not specifying any column names, the order of values must match the order of columns in the table
- If you are inserting data into all the columns of the table, mentioning every column name is not necessary
- The order of the columns/values is very important whether you mention the column names or not.
NULLis the data type for when there’s no value in the cell (empty cell, missing data).
NULLis a placeholder for unknown data
# INSERT INTO ___ (___, ___) VALUES (___, ___); # INSERT INTO tableName (columnName, columnName) VALUES (value, value); INSERT INTO movies (id, title, genre, duration) VALUES (5, 'The Circus', 'Comedy', 71); INSERT INTO movies VALUES (5, 'The Circus', 'Comedy', 71); # same as above since movies has only 4 columns INSERT INTO movies (title, duration) VALUES ('The Fly', 80)
- Primary keys are unique for a table. They’re never blank or empty. Once value in the column can not be the same as another value in the column.
- SQL can auto-increment the primary key for a table for new rows. Each time a row is added to the table, the key gets automatically incremented and added to the row.
- Primary Keys are usually used as reference keys, you use these to identify and get the rows. Some examples are customer_id, product_id, post_id etc.
- Primary keys are unique identifiers
Changing existing data
UPDATEis the keyword for updating existing data
- Withut a
WHEREclause, the entire column will update for all rows
# UPDATE ___ SET ___ = ___ (WHERE ___) # UPDATE tableName SET columnName = columnValue WHERE clause UPDATE movies SET genre = 'Romance' WHERE id = 5; UPDATE movies SET genre = 'Comedy', duration = 70 # update multiple values WHERE id = 5; UPDATE movies SET genre = 'Romance' WHERE id = 5 OR id = 7; # update multiple rows since id is unique
DELETEis the keyword
WHEREcan be used for further refining the statement
- If you don’t mention a
WHEREclause, it’ll delete all data from the table
# DELETE FROM ___ (WHERE ___) # DELETE FROM tableName (WHERE clause) DELETE FROM movies WHERE id = 5; DELETE FROM movies WHERE duration < 100;
CREATE TABLEare the self-explanatory keyword for creating databases and tables
DROP DATABASEis when you want to remove a database (i.e. drop it)
- Dropping databases is irreversible
# CREATE DATABASE ___; CREATE DATABASE Chaplin Theaters; # DROP DATABASE ___; DROP DATABASE Chaplin Theaters; # CREATE TABLE ___ ( columnName dataType, columnNamedataType) CREATE TABLE movies ( id int, title varchar(320), genre varchar(160), duration int ); # DROP TABLE ___; DROP TABLE movies;
- When you want to add/remove columns to/from a table, you use the
ALTER TABLEkeyword in conjunction with
# ALTER TABLE ___ ADD COLUMN ___ ___; ALTER TABLE movies ADD COLUMN ratings int; # ALTER TABLE ___ DROP COLUMN ___; ALTER TABLE movies DROP COLUMN ratings;
- CodeSchool: Try SQL free video course with code challenges (less than 3 hrs approx for the entire course, Took me 1 hour per Level while i was also taking notes)