Database Structured Query Language (SQL)

Basic command line operations and syntax

Command line operations in linux (debian-based)

  • Open MySQL command line interface
sudo mysql -u root -p

The -u parameter defines the user (in this case root) while the blank -p triggers user input for the associated password. Below is a a primer for common SQL operations.

Note: SQL commands are terminated by a semi-colon character ;

SQL database operations

  • Create database
CREATE DATABASE databasename;
  • Delete database
DROP DATABASE databasename;
  • List all databases
SHOW DATABASES;

SQL user operations

  • Create user
CREATE USER 'databaseuser'@'localhost' IDENTIFIED BY 'password';
  • Delete user
DROP USER databaseuser;
  • List all users
SELECT user FROM mysql.user;

SQL table operations

  • Create a table with defined columns
CREATE TABLE table_name (column_name column_type, column_name2 column_type2, PRIMARY KEY(primary_key_column_name));
  • Delete a table from current database
DROP TABLE table_name;
  • Select a database for table operations
USE database_name;
  • List all tables in current database
SHOW TABLES;
  • List all entries in a table
SELECT * FROM table_name;

SQL user privilege assignment and user modification operations

  • Grant all privileges for databaseuser on database
GRANT ALL PRIVILEGES ON 'database'.* TO 'databaseuser'@'localhost' IDENTIFIED BY 'password';

Advanced features

More advanced operations and SQL server settings are outlined below.

Enable remote client access

This allows database access from outside of the installation localhost environment

Official documentation

  1. MySQL
  2. MariaDB

Tutorials and resources

  1. MySQL Tutorial