MySQL commands for a developer


Α reference of some common MySQL commands useful to a developer.

Start the server

MySQL should be installed as a service. However, if it’s set to manual you may start it with:

mysqld-nt --console

Stop the server

mysqladmin -u root -p shutdown

Login

Just use the entry in the Start menu.

Start → Programs → MySQL → MySQL server 5.0 → MySQL Command Line Client

Even better, if the bin folder of the MySQL installation is in the path you may simply type

mysql -u root -p

List database schemas

show databases;

Add a schema

CREATE DATABASE fcom;

Delete a schema

DROP DATABASE fcom;

Select the current schema

USE fcom;

List available tables

show tables;

Add a table

INSERT TABLE clients (c_id INT AUTO_INCREMENT, c_name VARCHAR(50), 
    c_address VARCHAR(200), c_tel VARCHAR(20), PRIMARY KEY(c_id));

Delete a table

DROP TABLE clients;

Add an account

GRANT ALL PRIVILEGES ON fcom.* TO 'george'@'localhost'
IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Where fcom is the database schema this user may access, george is the username and mypass is the password. Note that the username is explicitly associated with a host. This command grants privileges to george, only when he logins from the same computer as the MySQL instance.

Show privileges of an account

SHOW GRANTS FOR 'george'@'localhost';

Delete an account

DROP USER 'george'@'localhost';

References

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: