This is a collection of various tips and things I use in MySQL. This is actually up on the web more as a reference to myself than anything else. I keep learning this stuff, but since I don’t use it all that frequently I tend to forget how to do something exactly the next time i need to. So here are little commands I use and some tiny descriptions. If you have some tips tricks or think i am doing something the hard way please feel free to comment and share. If you have any MySQL questions please feel free to post them if you think it is something I might be able to help you with. That said here is mysql information…

<b>MySQL Commands:</b>

//to start mysql on the command line Mysql �u root �p (It will then ask for your password)

//To start mysql server Sudo bash�bin/safe-mysqld� from the mysql root directory

//How to select data using rules involving more than one table from MYSQL: Select * from user leftjoin link on user.id = from Left join msg on to=msg.id where user.name = �john�; Database:mysql -u root �p ***

//snowmass //to create a text dump of a database mysqldump �-user [user name] �-password=[password] [database name] > [dump file]

//to create a new databasemy sql> create database somedb;

//granting privileges to a user grant all privileges on *.* to NewsShaker@�localhost" > identified by 'passwrd';

//shows all the tables of a database show tables;

//creates a new table in the database your currently using create table "tablename" ("column1" "data type", "column2" "data type", "column3" "data type");

//creates a link table (which is really the same as other tables but just//a way to associate data between tablescreate table link (catID bigint, pageID bigint);

//deletes an entire tabledrop table Pages;

//deletes an entry from table idgen where the item has a uid of 12delete from idgen where uid=12;

//show only the colums of a table not the datashow columns from tablename;

//To change the data of an already existing entry update tablename set columname = "whatever" where columnname = "something";

//to alter a table if you need to add a new field alter table tablename add column_name column_type after column_name2;

//creates a dump of database called newsshaker ./mysqldump �u root �p newsshaker > ../../../ddmayer/newsshakerdb.txt//at command line in the mysql/bin directory

//to recreate the database from the dump file ./mysql -u root -pPASSWORD newsshaker < ./newsshakerdb.txt//at the command line in the mysql/bin directory

//to get the count in a category since a date: select count(*) as co from Pages as p left join link as l on p.UID = l.PageID left join categories as c on c.UID = l.CatID where c.name like 'autism' and p.date > 20031201000000 group by c.UID;

//to get only 5 in order as the very newest: select p.UID,p.date,c.UID from Pages as p left join link as l on p.UID = l.PageID left join categories as c on c.UID = l.CatID where c.name like 'autism' and p.date > 20031201000000 order by p.date DESC limit 5;

//to get a whole category: select p.UID,p.date,c.UID from Pages as p left join link as l on p.UID = l.PageID left join categories as c on c.UID = l.CatID where c.name like 'autism';

//add a new user to your mysql system mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

//getting random results from a mysql query or mysql table mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 1")

//renaming multiple tables at the same time. RENAME TABLE old_table TO backup_table, new_table TO old_table, backup_table TO new_table;

Links:

MySQL home

My SQL commands tutorial

Java MySQL tutorial

Great java Mysql developer tutorial



blog comments powered by Disqus
Dan Mayer Profile Pic
Welcome to Dan Mayer's development blog. I primary write about Ruby development, distributed teams, and dev/PM process. The archives go back to my first CS classes during college when I was first learning programming. I contribute to a few OSS projects and often work on my own projects, You can find my code on github.

Twitter @danmayer

Github @danmayer