SQL

The details of the following page were initially written by @OneMiss

NOTE: this list may not show all snippets included with this repository. To view the most up-to-date listing, check the snippets file (here)[https://github.com/rafamadriz/friendly-snippets/blob/main/snippets/sql.json].

createt Create Regular Table

CREATE TABLE tableName (
  attribute(s)
);

createi Create Table with Conditional

CREATE TABLE IF NOT EXISTS tableName (
  attribute(s)
);

created Create Regular Database

CREATE DATABASE name;

createdi Create Database with Conditional

CREATE DATABASE IF NOT EXISTS;

insert Insert value(s)

INSERT INTO tableName (
  attribute(s)
) VALUES ( values )

dropt Drop table

DROP TABLE tableName;

dropd Drop Database

DROP DATABASE dbName;

dropti Drop Table with Conditional

DROP TABLE IF EXISTS tableName;

dropdi Drop Database with Conditional

DROP DATABASE IF EXISTS tableName;

showt Show Tables

SHOW TABLES;

showd Show Databases

SHOW DATABASES;

select Regular Select

SELECT attribute(s) FROM tableName;

selectd Select Distinct

SELECT DISTINCT attribute(s)
  FROM tableName;

selectw Select with Condition

SELECT attribute(s)
  FROM tableName
  WHERE condition;

selector Select with order

SELECT attribute(s)
  FROM tableName
  ORDER BY attribute(s) ASC|DESC;

updatet Update table

UPDATE tableName
  SET attribute(s)
  WHERE condition;

delete Delete records

DELETE FROM tableName
  WHERE condition;

altert Alter table

ALTER TABLE tableName
   intructions;

alterad Alter table - Add column

ALTER TABLE tableName
  ADD COLUMN col_name;

alteraf Alter table - Add column after

ALTER TABLE tableName
  ADD COLUMN col_name
  AFTER col_name;

alterdb Alter database

ALTER DATABASE dbName
  CHARACTER SET charset
  COLLATE utf8_unicode_ci;

ijoin Inner Join

SELECT attribute(s)
  FROM tableName
  INNER JOIN tableName2
  ON match;

rjoin Right Join

SELECT attribute(s)
  FROM tableName
  RIGHT JOIN tableName2
  ON match;

ljoin Left Join

SELECT attribute(s)
  FROM tableName
  LEFT JOIN tableName2
  ON match;

fjoin Full Join

SELECT attribute(s)
  FROM tableName
  FULL JOIN OUTER tableName2
  ON match
  WHERE condition;

union Regular Union

SELECT attribute(s) FROM tableName
UNION
SELECT attribute(s) FROM tableName2;

uniona All union

SELECT attribute(s) FROM tableName
UNION ALL
SELECT attribute(s) FROM tableName2;

groupb Group by

SELECT attribute(s)
  FROM tableName
  GROUP BY attribute(s);

bakupd Backup database

BACKUP DATABASE dbName
  TO DISK filepath;

bakupdw Differential backup database

BACKUP DATABASE dbName
  TO DISK filepath
  WITH DIFERENTIAL;

primaryk Primary Key

PRIMARY KEY(attribute)

primarykc Constraint Primary Key

CONSTRAINT attribute PRIMARY KEY(attribute(s))

foreingk Foreign Key

FOREIGN KEY(attribute) REFERENCES tableName(attribute)

foreingkc Constraint foreign Key

CONSTRAINT attribute FOREIGN KEY (attribute(s))
  REFERENCES tableName(attribute)

check Check

CHECK ( condition )

createuser Create User

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

deleteuser Delete User

DELETE FROM mysql.user WHERE user = 'userName';

grantuser Grant Privileges

GRANT ALL PRIVILEGES ON db.tb TO 'user_name'@'localhost';