Our site is ads supported. Please disable your ad blocker and reload the page. Thank you!

Basic SQL Commands Every Developer Should Know

1 1 1 1 1 Basic SQL Commands Every Developer Should Know5.00Rating 5.00 (1 Vote)

The SQL or Structured Query Language (structured query language) is used to manage data in a relational database system (RDBMS). In this article, you will learn about frequently used SQL commands that every developer should know. This material is ideal for those who want to refresh their knowledge of SQL before a job interview. We will be using MySQL or MariaDB syntax in this article. But most of the commands are patform-independent and will work on SQL Server, PostgreSQL, SQLite, OracleDB and others. 

Commands for working with databases

1. View available databases
SHOW DATABASES


2. Creating a new database
CREATE DATABASE


3. Selecting the database to use
USE <database_name>


4. Import SQL commands from the .sql file
SOURCE <path_of_.sql_file>


5. Removing the database and all the data
DROP DATABASE <database_name>

Working with tables

6. View tables available in the database

SHOW TABLES

7. Create a new table
CREATE TABLE <table_name1> (
  <col_name1> <col_type1>,
  <col_name2> <col_type2>,
  <col_name3> <col_type3>
  PRIMARY KEY (<col_name1>));

primary key - PRIMARY KEY (col_name1, col_name2, ...);

You can specify more than one primary key. In this case, you will get a composite primary key.

Example
Create the "instructor" table:

CREATE TABLE instructor (
  ID CHAR (5),
  name VARCHAR (20) NOT NULL,
  dept_name VARCHAR (20),
  salary NUMERIC (8,2),
  PRIMARY KEY (ID),
);
8. Information about the table
You can view various information (the type of values, is the key or not) about the columns of the table with the following command:

DESCRIBE <table_name>

9. Adding new data to the table 
INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, ...)
  VALUES (<value1>, <value2>, <value3>, ...);
When you add data to each column of the table, you do not need to specify the column names.

INSERT INTO <table_name>
  VALUES (<value1>, <value2>, <value3>, ...);

10. Updating table data that already exist
UPDATE <table_name>
  SET <col_name1> = <value1>, <col_name2> = <value2>, ...
  WHERE <condition>;
11. Delete all data from the table
DELETE FROM <table_name>;

12. Deleting the table and all data
DROP TABLE <table_name>;

Commands for creating queries

13. SELECT

SELECT is used to retrieve data from a specific table:

SELECT <col_name1>, <col_name2>, ...
  FROM <table_name>;
The following command can print all the data from the table:

SELECT * FROM <table_name>

 

 

Monday the 19th.