Basic MySQL Statements Every Programmer's Should Learn

I'm making here a reference for sql statements that might come in handy since I usually surf for the proper ways to do each and everyone of it.

So here are some sql syntax that every programmer (and like) should know. I'll just go with the basic CRUD and sight some examples.



1) Creating a Database

CREATE DATABASE database_name

2) Drop Database

3) Creating Table

CREATE TABLE "table_name"(
 "column 1" "data_type_for_column_1",
 "column 2" "data_type_for_column_2",
 ... 
)

Sample:

a) Basic:

CREATE TABLE example (
 id INT,
 data VARCHAR(100)
);

b) With Auto Increment and Primary Key:

CREATE TABLE example_autoincrement (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 data VARCHAR(100)
);

c) With timestamp:

CREATE TABLE example_timestamp (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 data VARCHAR(100),
 cur_timestamp TIMESTAMP(8)
);

d) With default value:

CREATE TABLE example_default_now (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 data VARCHAR(100),
 created TIMESTAMP DEFAULT NOW()
);

4) Updating or Altering an existing Table

a) Adding a column

ALTER TABLE table_name 
ADD column_name datatype;

b) Dropping / Deleting a table column

ALTER TABLE table_name
DROP COLUMN column_name;

c) Altering / Updating a table column

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

d) Updating Column Name

ALTER TABLE table_name CHANGE old_column_name  new_column_name type size;

e) Alter Table Name

ALTER TABLE old_table_name RENAME TO new_table_name;

5) Select Items on Table
NOTE: Here, column_name(s) can be replaced to * if you want to return all columns

a) Basic:

SELECT column_name(s)
FROM table_name

or

SELECT * FROM table_name

b) With Conditions:

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

c) In specific order:

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

6) Insert item on Table

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

7) Update item on Table

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

8) Delete item on Table

DELETE FROM table_name
WHERE some_column=some_value

9) View all databases and/or tables

SHOW databases;

USE database_name;
SHOW tables;

10) Describe a table

DESCRIBE table_name;

These are just the basic things that should be learn in handling mySql database. Happy coding! :)