About MySQL

If you don’t have MySQL installed, you can quickly download it.

sudo apt-get install mysql-server

How to Access the MySQL shell

Once you have MySQL installed, you can access the MySQL shell by typing the following command into terminal:

mysql -u root -p

After entering the root MySQL password into the prompt, you will be able to start building your MySQL database.

Two points to keep in mind:

All MySQL commands end with a semicolon; if the phrase does not end with a semicolon, the command will not execute.
Also, although it is not required, MySQL commands are usually written in uppercase and databases, tables, usernames, or text are in lowercase to make them easier to distinguish. However, the MySQL command line is not case sensitive.

How to Create and Delete a MySQL Database

MySQL organizes its information into databases; each one can hold tables with specific data.

You can quickly check what databases are available by typing:

SHOW DATABASES;

Creating a database is very easy:

CREATE DATABASE <database_name>;

In MySQL, the phrase most often used to delete objects is Drop. You would delete a MySQL database with this command:

DROP DATABASE <database_name>;

How to Access a MySQL Database

Once we have a new database, we can begin to fill it with information.

The first step is to create a new table within the larger database.

Let’s open up the database we want to use:

USE <database_name>;

In the same way that you could check the available databases, you can also see an overview of the tables that the database contains.

SHOW tables;

Since this is a new database, MySQL has nothing to show, and you will get a message that says, “Empty set”

Manipulation

six statements commonly used to manage data stored in a relational database.

CREATE TABLE creates a new table.
INSERT INTO adds a new row to a table.
SELECT queries data from a table.
UPDATE edits a row in a table.
ALTER TABLE changes an existing table.
DELETE FROM deletes rows from a table.

CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);

INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);

SELECT * FROM celebs;

SELECT name FROM celebs;

UPDATE celebs SET age = 22 WHERE id = 1;

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

UPDATE celebs SET twitter_handle = '@taylorswift13' WHERE id = 4;

DELETE FROM celebs WHERE twitter_handle IS NULL;

Import / Export a mysql database on the command line

To export a mysql database (as a dump) from the command line run:

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

To import a mysql database dump into a database:

mysql -u username -p database_name < database_exportname.sql

To export all databases into a dump:

mysqldump --all-databases > all_databases_export.sql

To import one of these mysql databases from the dump into a database:

mysql --one-database database_name < all_databases_export.sql

To load a sql.gz file into a database:

zcat /path/to/file.sql.gz | mysql -u db_user -p your_database

How to Create a New User

Let’s start by making a new user within the MySQL shell:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the Select command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users’ privileges

To provide a specific user with a permission, you can use this framework:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

DROP USER ‘demo’@‘localhost’;

To test out your new user, log out by typing

quit

and log back in with this command in terminal:

mysql -u [username]-p

101.sql