common SQL commands

ALTER TABLE
ALTER TABLE table_name 
ADD column_name datatype;
ALTER TABLE lets you add columns to a table in a database.

AND
SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
  AND column_2 = value_2;
AND is an operator that combines two conditions. Both conditions must be true for the row to be included in the result set.

AS
SELECT column_name AS 'Alias'
FROM table_name;
AS is a keyword in SQL that allows you to rename a column or table using an alias.

AVG()
SELECT AVG(column_name)
FROM table_name;
AVG() is an aggregate function that returns the average value for a numeric column.

BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;

The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text or dates.

CASE
SELECT column_name,
  CASE
    WHEN condition THEN 'Result_1'
    WHEN condition THEN 'Result_2'
    ELSE 'Result_3'
  END
FROM table_name;
CASE statements are used to create different outputs (usually in the SELECT statement). It is SQL’s way of handling if-then logic.

COUNT()
SELECT COUNT(column_name)
FROM table_name;
COUNT() is a function that takes the name of a column as an argument and counts the number of rows where the column is not NULL.

CREATE TABLE
CREATE TABLE table_name (
  column_1 datatype, 
  column_2 datatype, 
  column_3 datatype
);
CREATE TABLE creates a new table in the database. It allows you to specify the name of the table and the name of each column in the table.

DELETE
DELETE FROM table_name
WHERE some_column = some_value;
DELETE statements are used to remove rows from a table.

GROUP BY
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
GROUP BY is a clause in SQL that is only used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.

HAVING
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
HAVING was added to SQL because the WHERE keyword could not be used with aggregate functions.

INNER JOIN
SELECT column_name(s)
FROM table_1
JOIN table_2
  ON table_1.column_name = table_2.column_name;

An inner join will combine rows from different tables if the join condition is true.

INSERT
INSERT INTO table_name (column_1, column_2, column_3) 
VALUES (value_1, 'value_2', value_3);
INSERT statements are used to add a new row to a table.

IS NULL / IS NOT NULL
SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;
IS NULL and IS NOT NULL are operators used with the WHERE clause to test for empty values.

LIKE
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

LIMIT
SELECT column_name(s)
FROM table_name
LIMIT number;
LIMIT is a clause that lets you specify the maximum number of rows the result set will have.

MAX()
SELECT MAX(column_name)
FROM table_name;
MAX() is a function that takes the name of a column as an argument and returns the largest value in that column.

MIN()
SELECT MIN(column_name)
FROM table_name;
MIN() is a function that takes the name of a column as an argument and returns the smallest value in that column.

OR
SELECT column_name
FROM table_name
WHERE column_name = value_1
   OR column_name = value_2;
OR is an operator that filters the result set to only include rows where either condition is true.

ORDER BY
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;
ORDER BY is a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically.

OUTER JOIN
SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
  ON table_1.column_name = table_2.column_name;

An outer join will combine rows from different tables even if the join condition is not met. Every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table.

ROUND()
SELECT ROUND(column_name, integer)
FROM table_name;
ROUND() is a function that takes a column name and an integer as arguments. It rounds the values in the column to the number of decimal places specified by the integer.

SELECT
SELECT column_name 
FROM table_name;
SELECT statements are used to fetch data from a database. Every query will begin with SELECT.

SELECT DISTINCT
SELECT DISTINCT column_name
FROM table_name;
SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s).

SUM
SELECT SUM(column_name)
FROM table_name;
SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column.

UPDATE
UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;
UPDATE statements allow you to edit rows in a table.

WHERE
SELECT column_name(s)
FROM table_name
WHERE column_name operator value;
WHERE is a clause that indicates you want to filter the result set to include only rows where the following condition is true.

WITH
WITH temporary_name AS (
   SELECT *
   FROM table_name)
SELECT *
FROM temporary_name
WHERE column_name operator value;
WITH clause lets you store the result of a query in a temporary table using an alias. You can also define multiple temporary tables using a comma and with one instance of the WITH keyword.
The WITH clause is also known as common table expression (CTE) and subquery factoring.




Shortcut Keys for DB Browser for SQLite

Main window
Alt + 1 switches to the Database Structure tab.
Alt + 2 switches to the Browse Data tab.
Alt + 3 switches to the Edit Pragmas tab.
Alt + 4 switches to the Execute SQL tab.

Database Structure tab
Ctrl + R and F5 refresh the database structure.

Browse Data tab
Ctrl + R and F5 refresh the currently browsed table.
Ctrl + " duplicates the currently selected record.
Ctrl + ' copies the data from the cell above the current (future version 3.11).
Ctrl + PageUp and Ctrl + PageDown switch the currently browsed table to the previous or the next table.
Delete and Backspace delete the content of the current cell(s), setting it to an empty string.
Alt + Delete and Alt + Backspace delete the content of the current cell(s), setting them to NULL.
Tab moves to the next cell when the table browser is focused. When being on the last cell, a new row is inserted automatically.

Edit Pragmas tab
Ctrl + R and F5 refresh the pragma list.

Execute SQL tab
Ctrl + Return executes the SQL commands.
Ctrl + R and F5 execute the SQL commands.
Shift + F5 executes the SQL commands on the current line only.

Edit dialog and edit pane
Ctrl + Return clicks the Apply button.
Insert toggles overwrite mode in both text editor and hex editor.
Ctrl + F Opens the Find dialog (future version 3.12).
Ctrl + H Opens the Find/Replace dialog (future version 3.12).

Edit Table dialog
Ctrl + Return clicks the OK button.
SQL, JSON and XML editors
Key	Command
Down	Move down one line
Shift+Down	Extend selection down one line
Alt+Shift+Down	Extend rectangular selection down one line
Ctrl+Down	Scroll view down one line
Up	Move up one line
Shift+Up	Extend selection up one line
Alt+Shift+Up	Extend rectangular selection up one line
Ctrl+Up	Scroll view up one line
Ctrl+]	Move down one paragraph
Ctrl+Shift+]	Extend selection down one paragraph
Ctrl+[	Move up one paragraph
Ctrl+Shift+[	Extend selection up one paragraph
Left	Move left one character
Shift+Left	Extend selection left one character
Alt+Shift+Left	Extend rectangular selection left one character
Right	Move right one character
Shift+Right	Extend selection right one character
Alt+Shift+Right	Extend rectangular selection right one character
Ctrl+Left	Move left one word
Ctrl+Shift+Left	Extend selection left one word
Ctrl+Right	Move right one word
Ctrl+Shift+Right	Extend selection right one word
Ctrl+\	Move right one word part
Ctrl+Shift+\	Extend selection right one word part
Alt+Home	Move to start of display line
Home	Move to first visible character in document line
Shift+Home	Extend selection to first visible character in document line
Alt+Shift+Home	Extend rectangular selection to first visible character in document line
End	Move to end of document line
Shift+End	Extend selection to end of document line
Alt+Shift+End	Extend rectangular selection to end of document line
Alt+End	Move to end of display line
Ctrl+Home	Move to start of document
Ctrl+Shift+Home	Extend selection to start of document
Ctrl+End	Move to end of document
Ctrl+Shift+End	Extend selection to end of document
PgUp	Move up one page
Shift+PgUp	Extend selection up one page
Alt+Shift+PgUp	Extend rectangular selection up one page
PgDown	Move down one page
Shift+PgDown	Extend selection down one page
Alt+Shift+PgDown	Extend rectangular selection down one page
Del	Delete current character
Backspace	Delete previous character
Ctrl+Backspace	Delete word to left
Ctrl+Del	Delete word to right
Ctrl+Shift+Backspace	Delete line to left
Ctrl+Shift+Del	Delete line to right
Ctrl+Shift+L	Delete current line
Ctrl+L	Cut current line
Ctrl+Shift+T	Copy current line
Ctrl+T	Transpose current and previous lines
Ctrl+A	Select all
Ctrl+D	Duplicate selection
Ctrl+U	Convert selection to lower case
Ctrl+Shift+U	Convert selection to upper case
Ctrl+X	Cut selection
Ctrl+C	Copy selection
Ctrl+V	Paste
Ins	Toggle insert/overtype
Return	Insert newline
Tab	Indent one level
Shift+Tab	De-indent one level
Esc	Cancel
Ctrl+Z	Undo last command
Ctrl+Y	Redo last command
Ctrl++	Zoom in
Ctrl+-	Zoom out
Ctrl + F	Open the Find dialog (future version 3.12) or the search bar in the Execute SQL editors
Ctrl + H	Open the Find/Replace dialog


Executing SQL Statements from a Text File
mysql> source C:\Users\User\Desktop\init2.sql

To put SQL statements in a file and then tell mysql to read its input from that file. 
To do so, create a text file text_file that contains the statements you wish to execute. 

Then invoke mysql as shown here:

shell> mysql db_name < text_file

If you place a USE db_name statement as the first statement in the file, it is unnecessary to specify the database name on the command line:

shell> mysql < text_file

If you are already running mysql, you can execute an SQL script file using the source command or \. command:

mysql> source file_name
mysql> \. file_name

Sometimes you may want your script to display progress information to the user. 

For this you can insert statements like this:

SELECT '' AS ' ';

The statement shown outputs .

You can also invoke mysql with the --verbose option, which causes each statement to be displayed before the result that it produces.

mysql ignores Unicode byte order mark (BOM) characters at the beginning of input files. 

Previously, it read them and sent them to the server, resulting in a syntax error. 

Presence of a BOM does not cause mysql to change its default character set. 
To do that, invoke mysql with an option such as --default-character-set=utf8.

For more information about batch mode, see Section 3.5, “Using mysql in Batch Mode”.

init.sql
CREATE DATABASE test;
  use test;
  CREATE TABLE users (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL,
    age INT(3),
    location VARCHAR(50),
    date TIMESTAMP
  );

Listing all databases inside mysql
mysql> show databases;

Listing tables and their structure with the MySQL Command Line

select a database
list all the tables in the selected database with the following command:
mysql> show tables;

We can show the structure of the table using the "desc" command:
mysql> desc tableName;

show the indexes from a particular table:
mysql> show keys from tableName;

rename and duplicate table
RENAME TABLE tb1 TO tb2;

CREATE TABLE newtable LIKE oldtable; 
INSERT INTO newtable SELECT * FROM oldtable;