basic mysql browsing

SOFTWARE: mysql

mysql -uname -p optionaldbname

Here are some basic commands to connect to and browse mysql via command line:

Connect to mysql with your NAME:

mysql -uNAME -p

Enter password at prompt.

Then check out your databases:

show databases;

Then to select a db to browse:

use databaseName;

Optional: add database name at the end of mysql connection to go straight to a database of your choice:

mysql -uNAME -p databaseName

Now that we're in a database:

show tables;

And to get a specific schema from a table:

desc tableName;

From here you can proceed to have fun with your full array of select, update, insert craziness.

MORE INFO: http://www.pantz.org/database/mysql/mysqlcommands.shtml

count

SOFTWARE: mysql

select count(distinct subject) from ncurs;

count something in a table (with a given criteria)

MORE INFO:

simple joins

SOFTWARE: mysql

select t2.name from tabletwo as t2, tableone as t1 where t1.id = t2.id;

A quick syntax for a basic inner join from multiple tables. You can also do aliases to make more concise by add 'as t1, as t2' to table names (see example) and then referencing t1 or t2 instead of the full table names.

Note that this syntax performs an "inner" join by default, meaning that only results that match between both tables are returned! This is because the comma between the two table names is equivalent to basically saying "join". When using a comma, you use "where" in the second part of the statement. To include null results, use the full syntax (calling "left outer join" for example in place of the comma between the tables, and "on" in place of "where").

More info available on the mysql manual site (link below).

MORE INFO: http://dev.mysql.com/doc/refman/5.0/en/join.html

add column to table

SOFTWARE: mysql

alter table applications add column nickname varchar(255);

Use to add a column to a table.

Also you can specify where the new column goes by adding "after some_column_name" at the end of the command:

alter table applications add column nickname varchar(255) after monkeys;

MORE INFO:

update field value

SOFTWARE: mysql

update pets set doggiename = 'rex' where doggiename = 'oscar';

Use to change a field value in mysql. Safest way to use this, since you'll be changing values in the db, is to run a select statement first to make sure you're returning the correct results. Then, when you're sure you're selecting the right things, modify it to use 'update' and 'set' to make the actual changes to the values.

The example here would update a pets table to change all doggie names of oscar to rex.

update pets set doggie_name = 'rex' where doggie_name = 'oscar';

Or if you wanted to set a value to null for a unique record id you could do something like:

update pets set doggie_name = NULL where id = 1234;

MORE INFO:

left outer join

SOFTWARE: mysql

Use of left outer join to get foreign key values returned in place of _id column values in main table. And then also filtering those results with a where clause at the end to limit the result set based on given criteria. In this case, we have three tables: people, dogs, and cats. The people table contains the _id columns for dogs and cats. We first get the actual dog and cat names to replace the _id values (including null dog and cat names since we're using a left outer), then we filter to only see results if the cat's name is 'lumpah'.

select t1.name as person, t2.name as doggie, t3.name as kitty 
from people as t1 
left outer join dogs as t2 on t1.dog_id = t2.id 
left outer join cats as t3 on t1.cat_id = t3.id 
where t3.name = 'lumpah';

MORE INFO:

where field in (value range)

SOFTWARE: mysql

select fieldname from tablename where fieldtoscan in (value1, value2, value3);

If you have a field to scan/select by like "id" and you want to get values back for multiple records you can search them all by using "in":

select field_name from table_name where field_to_scan in (value1, value2, value3);

For example, this will return the email addresses for records 1 through 3 in the people table:

select email_address from people_table where id in (1, 2, 3);

MORE INFO:

rename existing column in table

SOFTWARE: mysql

alter table pets change dogs cats varchar(255);

Use the alter command to change an existing column name to a new name. (Note that you need to declare the type, even if it is not changing.) So to rename a column that's a varchar(255) in the pets table from dogs to cats you do:

alter table pets change dogs cats varchar(255);

MORE INFO:

mysql dump database backup

SOFTWARE: mysql

mysqldump --opt -u username -p databasename > backupfilename.sql

Make the Dump

Backup a mysql database to a file:

mysqldump --opt -u username -p databasename > backupfilename.sql

Dump All Option

mysqldump -p --all-databases > backupfilename.sql

Help

mysqldump --help

Import into Mysql

If you want to turn around and import the dump into another mysql you can run this command on the other mysql:

mysql -u username -p databasename < backupfilename.sql

Import All Option

mysql -u username -p < backupfilename.sql

Note: grants might not show up correctly after this for your mysql users, so you can run this in mysql to straighten them out:

flush privileges;

MORE INFO:

set main password

SOFTWARE: mysql

(as root) mysqladmin password 'password_here'

To create the root password for mysql using mysqladmin when logged in as root:

mysqladmin password 'password_here'

Or, if you need to designate it specifically for root:

mysqladmin -u root password 'password_here

Altho, I'm not sure when you'd really need the second case. Guess if you have priveleges to run mysqladmin but your not logged in as root...

MORE INFO:

grants in mysql

SOFTWARE: mysql

show grants for username@host

Showing Grants

Mysql allows multiple user@host combinations to have different privileges. Find out what privileges (grants) are assigned for users at various hosts with:

show grants for username@host;

Example:

show grants for john@johnscomputer;

Create Grants

Create grants with this type of syntax:

First, let the user connect to mysql fro m their computer (host) with a certain password:

grant usage on *.* to username@hostname identified by 'passwd';

Give a user@host all privileges for a certain db:

grant all on dbname.* to 'user'@'hostname';

If you didn't successfully set up the user/host with a password first, you set the user's password with:

set password for 'user'@'hostname' = password('actual_psswrd_here');

Test Connecting from Remote Host

Test connecting via the commandline (from remote host) with :

mysql -h hostname_of_mysql_server -u username -p 

MORE INFO: http://www.pantz.org/software/mysql/mysqlcommands.html