MySQL cheatsheet

I made yet another casino-game at the end of this assigment from school. Its pretty boring, yes indeed. Anyways, i had this pretty stupid function that was supposed to save user stats into a MySQL DB. It might be one of many stupid ideas while programming the casino, but its a good way to learn.... i hope.

Since i forget the commands between each time i use mysql i created this cheat-sheet. Hopefully this will help me remembering until next time i work with MySQL. Oh, this article doesnt describe anything about securing MySQL.

Oh yeah, i forgot to be a gentleman and use capital letters on sql commands.. shame on me. Its easier to read and easier to sort out variables from commands etc... bear that in mind.

So, assuming you have a mysql root account:
Login:
$ mysql -h localhost -u root_acc -p
-p prompt for password during login.
-h host to connect, this is optional while using localhost.

Create a new user:
mysql> create user 'casino'@'localhost' identified by 'secretpassword';
NOTE: If you want remote connections, use @'IP/HOSTNAME' from the host you are connecting from


Grant access to db:
mysql> grant all on casino_db.* to 'casino'@'localhost'


So, maybe its time to create the supposed casino_db aswell
create database casino_db;


Create tables into DB:
mysql> use casino_db;
mysql> select database();

mysql> create table player
    -> (
    -> PRIMARY KEY (name),
    -> name VARCHAR(25) NOT NULL,
    -> played INT UNSIGNED NOT NULL
    -> );

Inspect database tables (to see variables etc):

mysql> describe player;

Insert into database table:
mysql> insert into player values("NAME", playedINTEGER);

Show table content:
mysql> select * from player

Delete user:
mysql> drop user 'casino'@'localhost'


Delete table:
mysql> drop table player;

Show users:
mysql> select * from mysql.user;


All of this could be put into a *.sql file and implemented by using command
mysql -u root_acc -p < my_template.sql (-u casino -p should also work if you granted user access to specific database)


my_template.sql:


CREATE DATABASE casino_db;
USE casino_db;
SELECT DATABASE();
CREATE TABLE player
(
  PRIMARY KEY (name),
  name VARCHAR(25) NOT NULL,
  played INT UNSIGNED NOT NULL
); 

Remote connections; open /etc/mysql/my.cnf, replace this with your IP.
bind-address            = 127.0.0.1


And restart mysql service
$ sudo service mysql restart