Skip to content

Basic MySQL Statements

This note collects common MySQL administration and SQL statements.

1. Connect to MySQL

mysql -u root -p

On Linux, you may need to run it with sudo depending on the local installation and permissions.

2. User and database management

(1). Add a user

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON DATABASE.*
    -> TO 'user'@'localhost'
    -> IDENTIFIED BY 'password';

The statement grants permissions on DATABASE to user with password password. MySQL statements end with ;.

(2). Select a database

mysql> use DATABASE;

After selecting a database, subsequent commands operate on that database by default.

(3). List databases

SHOW DATABASES;

(4). List tables

SHOW TABLES;

(5). Show table columns

SHOW COLUMNS FROM table_name;

(6). Show table indexes

SHOW INDEX FROM table_name;

3. Create and delete databases

CREATE DATABASE database_name;
mysqladmin -u root -p create database_name
DROP DATABASE database_name;
mysqladmin -u root -p drop database_name

4. Create and delete tables

CREATE TABLE table_name(
   -> id INT NOT NULL AUTO_INCREMENT,
   -> title VARCHAR(100) NOT NULL,
   -> author VARCHAR(40) NOT NULL,
   -> date DATE,
   -> PRIMARY KEY ( id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE table_name;

5. Insert, query, update, and delete records

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

DELETE FROM table_name [WHERE Clause]

6. Common clauses

  • WHERE: filter rows by conditions.
  • LIKE: match text patterns, usually with %.
  • UNION: merge result sets.
  • ORDER BY: sort query results.
  • GROUP BY: group rows for aggregate queries.
  • JOIN: combine rows from multiple tables.
  • IS NULL / IS NOT NULL: compare NULL values safely.

REF

[1]. https://www.runoob.com/mysql/