MySQL: Basics

Some useful commands to deal with MySQL

Assuming, the mysql package was installed and the service is up and running, and you are logged at the same server.

First Time

For the first time, just paste the mysql command, you’ll see a response from the database, and the MySQL command-line prompt:

mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 17 to server version: 4.1.10a-nt

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

When not facing an untrusted environment, you may not want to setup a password. However, it is higly recommended to set up a password for the user root. Once logged in the database server, apply the following command:

SET PASSWORD = PASSWORD('a_new_password_for_root');

Now, you can log out using:

EXIT;

Root Login

When you want to log in as root, you will be promped for the above defined password. Just paste the following command and insert the root password once promped:

mysql -u root -p

Manipulating Databases

Once logged, enter the following command to create a database (e.g., testdb):

CREATE DATABASE testdb;

To verify, the database was created, use:

SHOW DATABASES;

If you want to delete the created database, use:

DROP DATABASE testdb;

Manipulating Tables

The data types are not discussed here, you can always find an up to date documentation on MySQL Reference Manual pages.

To start working with a database, it needs to be selected first:

USE db_name;

To create a table, use:

CREATE TABLE table_name (column_name column_data_type, ...);

For instance:

CREATE TABLE data (id INT(11) NOT NULL auto_increment, name VARCHAR(20) NOT NULL,
release_year YEAR, release_date DATETIME, PRIMARY KEY (id));

To delete a table, use:

DROP TABLE table_name;

To alter the table data, column names, etc., check the Reference Manual

Restrict Access to the Database

In MySQL environment, create a new user:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'p455w0rd';

Grant privileges while assigning the password:

GRANT ALL ON db_name.* TO 'username'@'localhost';

Other references

The primary key feature can be found in the Reference manual.

The join command feature can be found in the Reference manual.

The views feature can be found in the Reference manual.

And many others…

Advertisements
This entry was posted in Linux, Server and tagged , , . Bookmark the permalink.