Sql mutations

Changing data in your database.

Databases

CREATE DATABASE [IF NOT EXISTS] pets_db  # Create Database
SHOW DATABASES;                          # Show All Databases
USE pets_db;                             # Select database to work with
DROP DATABASE pets_db;                   # Delete Database

Tables

CREATE

CREATE TABLE users (
  id         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(10),
  last_name  VARCHAR(10),
  age        TINYINT
);

ALTER

ALTER TABLE users RENAME TO employees;

DROP

DROP TABLE users;

Columns

ALTER TABLE pet ADD COLUMN alive INT;             # Add Column
ALTER TABLE pet ADD COLUMN alive INT  AFTER dead; # Add Column, and choose placement
ALTER TABLE pet MODIFY alive dead INT;            # Rename Column/modify datatype
ALTER TABLE pet DROP COLUMN alive;                # Delete Column

Indexes

CREATE INDEX my_index ON my_table (first_name, last_name);
SHOW INDEX FROM table;

Rows

INSERT

Add rows to a table.

Insert multiple rows.

#           table (          columns                        )
#             |                 |
INSERT INTO tasks(subject, start_date, end_date, description)
VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'),
       ('Task 2','2010-01-01','2010-01-02','Description 2'),
       ('Task 3','2010-01-01','2010-01-02','Description 3');

Insert row if match does not exist.

# Add 'Rupert' to users if does not exist
INSERT INTO users (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (SELECT name FROM users WHERE name = 'Rupert') LIMIT 1;

UPDATE

Update an existing row.

UPDATE users
set    email = "user@domain.com"
WHERE  id = 190;

DELETE

Delete an existing row.

DELETE FROM users
WHERE id = 100;