Khawar Jatoi

MySQL Basics: A Quick Command Reference

Mon Jul 20 2020 in MySQL, CLI, Refrence

A handful of MySQL commands I reach for often, kept in one place.

Create & select a database

CREATE DATABASE database_name;
use database_name;

Create a table

Note: no trailing comma after the last column definition.

CREATE TABLE users (
    id         INT(11)     NOT NULL AUTO_INCREMENT,
    username   VARCHAR(50) NOT NULL,
    password   VARCHAR(40) NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    last_name  VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

Create a dedicated user

Better than using root for everyday app access.

-- MySQL 8.0: create the user first, then grant.
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password_for_this_user';
GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost';
FLUSH PRIVILEGES;
-- Note: ".*" is a wildcard meaning "every table in this database".

Change a user's password

-- Modern (MySQL 5.7.6+ and 8.0):
ALTER USER 'user_name'@'localhost' IDENTIFIED BY 'new_password';

-- Legacy (pre-8.0):
UPDATE mysql.user SET Password = PASSWORD('new_password') WHERE User = 'user_name';

Import a large .sql file

-- After creating and selecting the database:
source /path/to/file.sql;

Use forward slashes /, not backslashes. On a server, upload the file first, then reference its path, e.g. source /var/www/html/khawar/file.sql.

Select where a column is empty / not empty

-- phone2 is NOT empty:
SELECT phone, phone2 FROM jewishyellow.users
WHERE phone LIKE '813%' AND phone2 <> '';

-- phone2 IS empty:
SELECT phone, phone2 FROM jewishyellow.users
WHERE phone LIKE '813%' AND phone2 = '';