Are you tired of remembering different database commands or scouring the internet for a complete source?
Well, you’ve come to the right place! This blog series, The Database Cheatsheet, contains everything you need to know about database commands, covering everything developers and database professionals need to know to use and operate their databases.
First, here is a quick disclaimer: these commands are specifically tailored for SingleStore. If you plan to use them on other database platforms, ensure you check the documentation to confirm compatibility or required syntax changes. Need a database to get started with? Sign up for a free SingleStore Helios trial to get up and running in minutes.
In this post, we’ll look at the essential skills to keep your SingleStore data accurate and up to date. Remember, these commands work as-is on SingleStore — but may require small syntax adjustments for other SQL platforms.
Insert data
Inserting data into your database is a critical first and ongoing process. To insert data into the database, you’ll need to use an INSERT statement.
To insert a single row into the database, you would use the following syntax:
INSERT INTO table_name (column_1, column_2, etc.)VALUES (value_1, value_2, etc.)
Running this statement would insert the VALUE parameters into the corresponding columns. For example, if we wanted to insert values into a posts table, here is how you could do it:
INSERT INTO posts (title, body, category)VALUES ('Post One', 'Body of post one', 'News');
After this statement is executed, you’ll see a message indicating how many rows were inserted. If successful, new rows now exist in the “posts” table and can be queried or updated.
For times when you need to insert more than one row, you can add multiple entries into the VALUE portion of the query. For example, if we wanted to add multiple entries into the posts table, we could do so like this:
INSERT INTO posts (title, body, category) VALUES('Post Two', 'Body of post two', 'Technology'),('Post Three', 'Body of post three', 'News');
Running this statement would insert an entry for two posts into the database within a single statement. Similar to the success message from the single row INSERT, you should get a response denoting that two rows have been added to the database.
Select data
Once data is in the database, you need to have a mechanism for retrieval that allows you to display and work with stored data. To retrieve data within the database, you’ll need to use a SELECT statement.
To select all rows and columns from a table, you would use the following syntax:
SELECT * FROM table_name;
Running this statement retrieves every row and column from the specified table. For example, if we wanted to get all posts from a posts table, we could do it like this:
SELECT * FROM posts;
After executing this statement, you’ll receive a result set containing all records stored in the posts table. If you only need specific columns, you can refine your selection by specifying the column names:
SELECT title, category FROM posts;
Running this statement retrieves only the title and category columns for all rows in the posts table, omitting the body column. For times when you need to filter results based on a condition, you can use a WHERE clause. For example, if you want to retrieve only posts in the “News” category, you would use:
SELECT * FROM posts WHERE category = 'News';
Running this statement returns only the rows where the category is set to ‘News’, allowing you to work with a more focused subset of your data.
Update data
Once records exist within a database, the ability to update them is essential for maintaining accurate and up-to-date data in your database. To modify existing rows, you’ll need to use an UPDATE statement.
To update a specific row in a table, you would use the following syntax:
UPDATE table_nameSET column_name = new_valueWHERE condition;
Running this statement modifies existing records that meet the specified WHERE condition. For example, if we wanted to update the body of a specific post in the posts table, we could do it like this:
UPDATE postsSET body = 'Updated body'WHERE title = 'Post One';
After executing this statement, you’ll receive a confirmation message indicating how many rows were updated. If successful, the body column for the post titled “Post One” now contains the text “Updated body” instead of its previous value.
NOTE: Be careful when using UPDATE statements! If you run an update without a WHERE clause, all rows in the table will be modified.
For example, the following statement updates every row in the posts table:
UPDATE posts SET category = 'General';
Running this query would set the category field for all posts to “General”, which may not be the intended result. Always use a WHERE clause unless you intentionally want to update all rows.
Delete data
Deleting records is a permanent (and therefore powerful) action that removes data permanently from your database. To delete records, you’ll need to use a DELETE statement.
To delete a specific row from a table, you would use the following syntax:
DELETE FROM table_name WHERE condition;
Running this statement removes records that match the specified WHERE condition. For example, if we wanted to delete a post titled “Post One”, we could do it like this:
DELETE FROM posts WHERE title = 'Post One';
After executing this statement, you’ll receive a confirmation message indicating how many rows were deleted. If successful, the row where the title was “Post One” is permanently removed from the database.
Similar to UPDATE statements, be extremely cautious when using DELETE statements! If you run a DELETE without a WHERE clause, all rows in the table will be deleted.
For example, running the following query would remove all rows from the posts table:
DELETE FROM posts;
Executing this statement would erase all data from the table, leaving it empty. Unlike updates, deleted data cannot be recovered unless you have a backup, so always double-check before executing a DELETE statement.
Try SingleStore free
In this blog, we explored the essential commands for manipulating data in SingleStore, including inserting, selecting, updating and deleting records. Whether you’re managing application data, updating critical business records or cleaning up outdated entries, these SQL commands give you full control over your database. Check out our other Database Cheatsheet articles or head to the notebook in SingleStore Spaces for easy reference.
Disclaimer
These commands are specifically tailored for SingleStore. If you plan to use them on other SQL platforms, ensure you check the documentation to confirm compatibility or required syntax changes. Need a database to get started with? Sign up for a free SingleStore Helios® trial to get up and running in minutes.
