MySQL Cheat Sheet

This article introduces some basic MySql database concepts including definitions, database connections sql, sql to import and create a MySql database.

The examples will use the mysql> command line (as opposed to phpmyadmin).

Some Definitions

  1. RDBMS : Relational Database Management System eg mySQL
  2. Users: Users can interact with one or more databases on the RDBMS.
  3. Schema: A database schema of a database system is its structure described in a formal language supported by the database management system (DBMS). The term “schema” refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases).

    In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

    Some other database products draw a distinction. For example, in the Oracle Database product, a schema represents only a part of a database: the tables and other objects owned by a single user.

MySQL Command Line

MySQL ROOT PASSWORD
This is blank as a default.

CONNECT TO DATABASE SERVER

shell>mysql -u {mysql-username} -p{mysql-password} -h {mysql-server}
  • -u {mysql-user} : Specify MySQL user name. Use root only when connecting to local system.
  • -p {mysql-password}: Specify password, Employ the specified password when connecting to the database server. If a password is not supplied, it will be requested interactively.
  • -h {mysql-server}: Connect to the specified host (remote or local)

eg

shell>mysql -u admin -p secretpassword -h localhost

CREATE DATABASE

From within the mysql program:

mysql>  CREATE DATABASE <DATABASENAME>

SHOW ALL DATABASES

From within the mysql program:

mysql>  SHOW DATABASES;

IMPORT DUMP:

mysql -u username -p -h <hostname> DATABASE-NAME < data.sql

Will prompt for password

CREATE USER FOR DATABASE

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

eg

GRANT ALL PRIVILEGES ON [database name].* TO ‘[username]’@'localhost’;

then reload the privileges:

FLUSH PRIVILEGES;

SHOW ALL USERS

select * from mysql.user;

Pin It on Pinterest

Share This