MySQL: We've had the theory, and some administrivia for setting up passwords, adding and dropping users, and backing up a database, and some bits about extracting data for applications. Now, here are my most-used commands for making changes within a database itself. I've used these commands with MySQL, though they should work across all SQL-compliant databases.

A fuller explanation of how these commands work can be found here. This page is a quick guide to the syntax. More will be added over time...

See also the cheat sheet for MySQL administration.

Getting Around

You work with MySQL through a command line (mysql>). It is not case-sensitive (though database names will be, if you are working through them in Unix). Commands can span multiple lines. You end them with a “;” QUIT gets you out.

This command:

mysql> SELECT VERSION(), CURRENT_DATE;

gives you info about the current version of MySQL and the current date.

Selecting a database:

USE [Name of database here]

Create a database:

mysql> CREATE DATABASE [Name of database];

Show table command:

mysql> SHOW TABLES;

When you first log in, you want to see what databases MySQL is running...

show databases;

Then, pick a database...

use [database];

See what tables the database has...

show tables;

See the structure of the database table...

describe [table];

See everything in the database...

select * from [table];

Query Patterns

SELECT [Field], [Field], ... FROM Table WHERE [Field]='[Value]' ORDER by [FIELD] [desc/ascend] limit [value]

Delete an row from a database:

DELETE FROM [table] WHERE [field] = [value];

Update a field in a database:

UPDATE [table] SET [field] = [value] WHERE [field] = [value]; 

Make a change of a time-formatted entry in a database row:

SELECT FileName FROM SiteStories where Published between '2018-01-01 00:00:00' and '2018-12-31 23:59:00'