Basic SQLite Commands
Being able to work with a database is important. With SQLite we’re able to create new data tables, retrieve certain information from those tables, run queries to match certain data we want to retrieve, and much more. Here are the SQL commands that are most commonly used when working with a SQLlite database.
Creating a table
When creating a new table for a database, the command we use is “CREATE TABLE” followed by the name of the table and the attributes you want to include and their respective data types. It’s also important to have some form of ID in your tables and list it as a primary key
CREATE TABLE bears (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, gender TEXT, color TEXT, temperament TEXT);Inserting data into a table
To insert data into our newly created table we use “INSERT INTO” with the name of the table we’re inserting data into. Then we list the attributes of the table we want to add, followed by the keyword “VALUES” with the values of the new data you’re inserting matching the order of the attributes.
INSERT INTO bears (id, name, age, gender, color, temperament, alive) VALUES(1,"Pooh", 20, "Male", "dark brown", "calm")Adding Columns
Let’s say that, after creating a database and creating a table to live inside that database, we decide we want to add a column. We can do so with the “ALTER TABLE” statement.
ALTER TABLE bears ADD COLUMN alive BOOLEAN;Delete a table
Deleting a table is very simple we just simply use the “DROP TABLE” statement followed by the name of the table
DROP TABLE bears;Retrieving data from a table
So we have data within our table now and now we want to retrieve some of that data. The “SELECT” Statement helps us do just that. We select the names of the columns we are going to select(you can use a * if you want to select all) then use the FROM keyword and pick the table we are selecting from.
SELECT * From bears;But this will return us every instance from that table, if we want to be more specific on which data we want to get back we would include conditionals using the “WHERE” statement.
SELECT * FROM bears WHERE name = "Pooh";SELECT * from bears WHERE age > 5;
Updating data
If we wanted to update, or change, data in our table rows. We do this with the “UPDATE” keyword. The Update statement uses a “WHERE” clause to grab the row you want to update. It identifies the table name you are looking in and resets the data in a particular column to a new value.
UPDATE bears SET age = 21 WHERE name = "Pooh";Delete data from a table
If we wanted to delete some data from a table we use the “DELETE” statement, the table we want to access, then the conditional to find a specific piece of data to delete
DELETE FROM bears WHERE id = 1;Ordered list
If we wanted to retrieve data in a certain order based on certain attributes in a table we use the “ORDER BY” statement. This modifier allows us to order the table rows returned by a certain “SELECT” statement. we can use “ASC” for ascending order and “DESC” for descending order.
SELECT * FROM bears ORDER BY age DESC;Limit
If we want to select extremes from a database table — for example, what if we only wanted the youngest or oldest bear? we can use ORDER BY in conjunction with LIMIT.
LIMIT is used to determine the number of records you want to return from a dataset.
SELECT * FROM bears ORDER BY age DESC LIMIT 1;Count
Count is a SQL aggregate function that will count the number of records that meet a certain condition.
SELECT COUNT(gender) FROM bears WHERE gender = "Female";This will return us the total count of bears in our database that are female.
Grouping data
If we wanted to group our data based on certain column we use the “GROUP BY” statement.
SELECT gender, COUNT(gender) FROM bears GROUP BY gender;This will return us a data table with a count of how many male and female bears we have.
These are some of the common SQLite commands that are used mst frequently and are good to have handy!
