MySQL Cheat Sheet

Connecting to a DB:

mysql -u username -p database_name
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 222
Server version: 5.1.44 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

Simple Query

mysql> SELECT * FROM LANGUAGE;
+----+---------+------+----------------+
| id | version | code | with_phonetics |
+----+---------+------+----------------+
|  1 |       0 | Cn   |               |
|  2 |       0 | En   |                |
|  3 |       0 | Jp   |               |
|  4 |       0 | De   |                |
+----+---------+------+----------------+
4 rows in set (0.38 sec)

Creating a New Database

mysql> CREATE DATABASE new_name_db;
Query OK, 1 row affected (0.07 sec)

Creating a New User

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass'; 

Granting User Rights

mysql>GRANT ALL PRIVILEGES ON  mydbname.*  TO  someuser;

Changing to Another Database

mysql> USE new_name_db;
Database changed
mysql>

Deleting a Database

mysql> DROP DATABASE some_db; Query OK, 1 row affected (0.00 sec)
mysql>

Database Dump

mysqldump -u root -p dbname > filename

Import a Dump

mysql -u username-p password database_name < filename.sql

Altering a Table

ALTER TABLE some_table MODIFY some_number FLOAT(11) NOT NULL DEFAULT 0;

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>