Do, or do not. There is no ‘try’

This is a list of handy MySQL commands that I use time and time again. At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP and Perl API functions you can use to interface with MySQL. To use those you will need to build PHP with MySQL functionality. To use MySQL with Perl you will need to use a Perl module.

To login (from unix shell) use -h only if needed.

[mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.
create database [databasename];

List all databases on the sql server.
show databases;

Switch to a database.
use [db name];

To see all the tables in the db.
show tables;


To see database’s field formats.
describe [table name];

To delete a db.
drop database [database name];

To delete a table.
drop table [table name];

Show all data in a table.
SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.
show columns from [table name];

Show certain selected rows with the value “whatever”.
SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name “Bob” AND the phone number ‘3444444′.
SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name “Bob” AND the phone number ‘3444444′ order by the phone_number field.
SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444′.
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.
SELECT * FROM [table name] WHERE rec RLIKE "^a$";

Show unique records.
SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.
SELECT COUNT(*) FROM [table name];

Sum column.
SELECT SUM(*) FROM [table name];

Join tables on common columns.
select lookup.illustrationid, lookup.personid,person.birthday from lookup

left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Switch to the mysql db. Create a new user.
INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));

Change a users password.(from unix shell).
[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'

Change a users password.(from MySQL prompt).
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Allow the user “bob” to connect to the server from localhost using the password “passwd”
grant usage on *.* to bob@localhost identified by 'passwd';

Switch to mysql db.Give user privilages for a db.
INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

or

grant all privileges on databasename.* to username@localhost;
To update info already in a table.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.
DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privilages.
FLUSH PRIVILEGES;

Delete a column.
alter table [table name] drop column [column name];

Add a new column to db.
alter table [table name] add column [new column name] varchar (20);

Change column name.
alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.
alter table [table name] add unique ([column name]);

Make a column bigger.
alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.
alter table [table name] drop index [colmn name];

Load a CSV file into a table.
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.
[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.
[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.
[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

MYSQL Statements and clauses

mysql_unbuffered_query

Me: bienbuon

Him: linshi

——————————-

29/11/2008

Conversation

(01:06:56) linshipl: yo
(01:07:06) bienbuon: hi ya
(01:07:15) bienbuon: i thot you dont wanna talk to me
(01:07:28) bienbuon: i got your message
(01:07:33) bienbuon: that was very nice
(01:07:43) linshipl: yep
(01:07:45) linshipl: very nice
(01:08:06) linshipl: but i didn`t think u`ll get it serious
(01:08:07) linshipl:
(01:08:13) linshipl: u know me too much
(01:08:21) bienbuon: well, i was so furious
(01:08:24) linshipl: i was angry cos u said u`ll came over
(01:08:36) linshipl: and u gave me 5 dates but u newer
(01:08:38) linshipl:
(01:08:43) linshipl: thats i was angry
(01:08:50) bienbuon: i thot you sent me that text cus i couldnt go over, and it was your birthday
(01:08:55) linshipl: and whatdo u think about my new mobile ?
(01:09:03) linshipl: sony ericsson xperia x1
(01:09:08) bienbuon: at the time i was moving hose
(01:09:10) bienbuon: house
(01:09:10) linshipl: i paid 550 quid for it
(01:09:16) linshipl: when u`ll came
(01:09:20) bienbuon: and was too busy
(01:09:35) bienbuon: and forgot to reply ur text
(01:09:36) linshipl: now i`ll invite u for christmas eve
(01:09:43) linshipl: feel inviited
(01:09:47) linshipl: christmas eve
(01:09:59) linshipl: it`s most important day of christmas for poles
(01:10:12) linshipl: i hope u`ll came
(01:10:30) bienbuon: i got to be honest
(01:10:40) bienbuon: it’ not possible this time
(01:10:42) linshipl: if not, then, i`ll never talk to u again ( and this time i`m serious)
(01:10:44) bienbuon: im sorry
(01:10:51) linshipl: than……………..
(01:10:54) linshipl: take care
(01:10:56) linshipl: ………………………..
(01:10:56) bienbuon: it’s up to you, this my last year
(01:10:57) linshipl: bye
(01:11:15) linshipl: i did invited u
(01:11:16) bienbuon: im so disapointed
(01:11:18) bienbuon: thanks
(01:11:18) linshipl: 123124124 times
(01:11:23) linshipl: and u never came
(01:11:33) linshipl: that means u dont want to came
(01:11:37) linshipl: take care dude
(01:11:44) bienbuon: i wish i could, but you can see, i cant even get out of inverness
(01:11:56) linshipl: excuses excuse
(01:12:06) bienbuon: do you think that im not sick of thic place
(01:12:07) linshipl: stop laiyng
(01:12:13) linshipl: bye
(01:12:22) bienbuon: well, whatever you think man
(01:12:31) linshipl: i tought if i`ll talk to you this time u`ll came
(01:12:33) bienbuon: dont call me a lier
(01:12:44) linshipl: but now i know u don`t fucking care
(01:12:52) linshipl: take care
(01:12:53) bienbuon: its not as easy as you thought
(01:13:13) linshipl: everything it`s easy if u only want it
(01:13:22) bienbuon: stop being like that
(01:13:31) linshipl: i`m earning fucking 12.79/h
(01:13:42) linshipl: and i can get off whenever i want to
(01:13:44) linshipl: then stop
(01:13:45) bienbuon: im not earning that much
(01:13:50) linshipl: have good life
(01:14:01) bienbuon: wel, yeh, sorry lukasz , we should stop talking
(01:14:27) linshipl: u just where banned by linshi
(01:14:37) linshipl: and he think, u r fucking lier
———————————————————————————————

Back to the message which i highlighted in the conversation, i got a message from him the other day like this:

“I’ve deleted everything from you, your contact information, your email, everything…, don’t ever talk to me or try to contact me again, have a good life”.
And that was it, i didn’t know why he text me like that, so i didn’t talk to him since. but let’s go back to the old day before i had that message from him.

We used to be good friends, we worked in the same place, we lived in the same flat, we shared things in common.

Our hobbies were Linux. at the time he did help me a lot about Linux stuff, in return i helped him with English and other stuff. but let’s not talk about helping story, as friends i really didn’t expect getting anything from friends.

We were good friends for a while. what remind me of him was that he didn’t have any friends except his girlfriend. what i like about him was that he was clever and a bit eccentric, he lived in his own world, he didn’t like to talk to other people. There’s restriction of his own world which tied him indoor. i don’t think he realizes that.

After a while he left his job and moved away, he found a new job, a new place, got a new life. I was glad that he’s happy because he wasn’t happy here the place where i live.

I thought i would have time to visit him one day, unfortunately I’m always busy, I thought I’ll go when things are settled for me. he kept invited me to his new place so we can have a chat, a drink like the old days, i thought about that and i really really wanted to go.

I thought i would go to see him last year but time to go never fit in, there’s always something stopped me. I planned to go, i thought i definitely could go but again, there’s always something came up, i got promoted at work as a manager, i need to work harder, new course arrive, i had to work harder to pay for the study fee, work harder to pay for living, work, work, and work, and number of reasons like that stop me going anywhere.

I cant afford a day or 2 days away to somewhere else, i wish i could have a holiday, i wish i could finish my study so that i could have a proper holiday, i could earn more money, i could go to visit my friends. In my mind i thought to my self that ” Please be patient, you have to stick to what you’re doing, there’s only one year left, you can go to the end of the world if you achieve this very last year, patient and patient…”

I wish my friends, my family could understand me like they could read my mind so that they don’t demand so much from me when i can’t do such things for them while im busy.

back to my old friend there, he said I’m a fucking lier and i don’t want to go to visit him, im so disappointed, so furious.

how can a friend call you a lier while you cant afford time to go to see him? if you don’t trust your friends why have friends?

what do i have to lose when a friend said bullsh*t to me? NOTHING, i have nothing to lose just sorry for your narrow minded.

what is the point of being sulk to a friend? DAFT POINT! Who the hell do you think you are to talk to me like that? my friend DO NOT talk to me like that!

Why would i have to tell lie to you? am i getting something out of it if I’m lying to a friend? NO, nothing, pointless.

I’m a straight guy, if i said i cannot go, that mean i cannot go, if i said my name Is Hung that mean it is my name, it’s the truth!

I’ve been away from home for 5 years now and i haven’t been back to my country to visit my family, my friends cus i haven’t had a chance, i would go to morrow if i have a chance. Am i lying to myself that i haven’t been back to my country for 5 years? the fact is i have not!

only people has a narrow minded that cant even think about the obvious facts like that.

I wrote this here not really because i lost my old friend or because i feel sorry about it. NO! I’ve just lost 20 stones, well my friend is that heavy!

I wrote this here for you, whoever read this to open your mind if you think that your friend is lying to you, if you think that your friend can’t give you what you demand.

Everyone has their own reasons to do something.

Finally can you define meaning of friends? if you can’t then i don’t think that you have so many friends and you should find the meaning of it.

To me, the person who understands more about the meaning of “friends” they would have more friends than other person who doesn’t understand much about it.

I lost a friend but i gain the better friends and of course i have many good friends.

# You have a file that contains the URLs you want to download? Use the `-i’ switch:

wget -i file

If you specify `-’ as file name, the URLs will be read from standard input.

# Create a five levels deep mirror image of the GNU web site, with the same directory structure the original has, with only one try per document, saving the log of the activities to `gnulog’:

wget -r http://www.gnu.org/ -o gnulog

# The same as the above, but convert the links in the HTML files to point to local files, so you can view the documents off-line:

wget –convert-links -r http://www.gnu.org/ -o gnulog

# Retrieve only one HTML page, but make sure that all the elements needed for the page to be displayed, such as inline images and external style sheets, are also downloaded. Also make sure the downloaded page references the downloaded links.

wget -p –convert-links http://www.server.com/dir/page.html

The HTML page will be saved to `www.server.com/dir/page.html’, and the images, stylesheets, etc., somewhere under `www.server.com/’, depending on where they were on the remote server.

# The same as the above, but without the `www.server.com/’ directory. In fact, I don’t want to have all those random server directories anyway–just save all those files under a `download/’ subdirectory of the current directory.

wget -p –convert-links -nH -nd -Pdownload \

http://www.server.com/dir/page.html

# Retrieve the index.html of `www.lycos.com’, showing the original server headers:

wget -S http://www.lycos.com/

# Save the server headers with the file, perhaps for post-processing.

wget -s http://www.lycos.com/
more index.html

# Retrieve the first two levels of `wuarchive.wustl.edu’, saving them to `/tmp’.

wget -r -l2 -P/tmp ftp://wuarchive.wustl.edu/

# You want to download all the GIFs from a directory on an HTTP server. You tried `wget http://www.server.com/dir/*.gif’, but that didn’t work because HTTP retrieval does not support globbing. In that case, use:

wget -r -l1 –no-parent -A.gif http://www.server.com/dir/

More verbose, but the effect is the same. `-r -l1′ means to retrieve recursively (see section 3. Recursive Retrieval), with maximum depth of 1. `–no-parent’ means that references to the parent directory are ignored (see section 4.3 Directory-Based Limits), and `-A.gif’ means to download only the GIF files. `-A “*.gif”‘ would have worked too.

# Suppose you were in the middle of downloading, when Wget was interrupted. Now you do not want to clobber the files already present. It would be:

wget -nc -r http://www.gnu.org/

# If you want to encode your own username and password to HTTP or FTP, use the appropriate URL syntax (see section 2.1 URL Format).

wget ftp://hniksic:mypassword@unix.server.com/.emacs

Note, however, that this usage is not advisable on multi-user systems because it reveals your password to anyone who looks at the output of ps.

# You would like the output documents to go to standard output instead of to files?

wget -O – http://jagor.srce.hr/ http://www.srce.hr/

You can also combine the two options and make pipelines to retrieve the documents from remote hotlists:

wget -O – http://cool.list.com/ | wget –force-html -i -

————————————–

Basics
Wget is one of the powerful tools available there to download stuff from internet. You can do a lot of things using wget. Basic use is to download files from internet.

To download a file just type

wget http://your-url-to/file

But you cannot resume broken downloads.use -c option to start resumable downloads

wget -c http://your-link-to/file

You can also mask the program as web browser using -U.
This helps when the sites doesn’t allow download managers.

wget -c -U Mozilla http://your-link-to/file

Return To Contents

Download Entire Website
You can download an entire website using -r option.

wget -r http://your-site.com

But be careful. It downloads the entire website for you. Since this tool can put a large load on servers it obeys robot.txt you can mirror a site on you local drive using -m option.

wget -m http://your-site.com

You can select the levels up to which you can dig into the site and downloads using -l option.

wget -r -l3 http://your-site.com

This will download only up to 3 levels. Suppose you want download only sub folders in a website url use –no-parent option. With this option wget downloads only the sub folders and ignores,the parent folders

wget -r –no-parent http://your-site.com/subfldr/subfolder

Now coming to terrible ideas.. to the hell with webmasters, not allowing to download the website type to ignore the robots.txt.

wget -r -U Mozilla -erobots=off http://url-to-site/

p.s. masking like a browser is a crime in some countries…. or something like that, i have heard on net.

Return To Contents

Fooling the Webmasters
Do you think the web master cannot stop u with above command. to fool him use

wget -r -U Mozilla -erobots=off -w 5 –limit-rate=20 http://url-to-site/

here -w 5 instructs wget to wait 5 secs before downloading another file and –limit-rate=20 makes wget to cap the download speed to 20KBps. So u can fool the webmaster ….

Return To Contents

Download all PDFs
You can download all files of a particular format , like all pdfs listed on a webpage,

wget -r -l1 -A.pdf –no-parent http://url-to-webpage-with-pdfs/

This is most useful for students. When they find a webpage of a professor with the files they can use this command to download all pdfs or lecture notes.
————————————————————————————————————————

Let wget working after log out from ssh connection

I usually connect through ssh to my office (better ADSL than my home’s) and download the files there over the night, the next day I bring them home.

So, to make wget continue working after the log out, because I do not want to let my home PC on all night long, so the command is:

wget -b http://some.server.com/file
Logging the output to a file

This is useful when you are working with wget in the background, to be able to know what was wrong if anything goes wrong, use the -o option and specify a file to store the logs.

wget http://some.server.com/file -o $HOME/log.txt

Of course you can combine the options, and put something like this:

wget -b -c http://some.server.com/file –limit-rate=20K -o $HOME/log.txt

Virtual hosting allows you to host multiple websites, accessed by unique domain names, from the same IP address (same server). I use this technique on my web server to host multiple websites for myself and a few others. Setting up virtual hosting is not difficult, and can be done with a stock Ubuntu 8.04 server installation and multiple domain names.

What you’ll need:

* Configured Server: Install and configure Ubuntu Server 8 at least to the specifications mentioned in my installation and domain name configuration tutorials. You must have command line access to the server (physical or via ssh).
* Domain Names: In order for virtual hosting to be effective, you obviously need more than one domain name. If you do not own multiple domains, you can create sub-domains (”sub.domain.com”). This can be done in your Namecheap domain manager on the Domain Registration page. You can modify the sub-domains once you create them on the All Host Records Page. When a new domain or sub-domain is added, it will take up to two days to process before it can be accessed. Note: All sub-domains will have the same Dynamic DNS password as their parent domain.

Server Configuration:

* Setup User Accounts: Each of the virtual sites that you setup will have an author with a user account on the server. These users must have access to a home directory to store their website. The user can then transfer files to and from the server via SSH/SFTP (look for a FTP server tutorial in the future). To setup these accounts, type the following into the server command line, replacing newuser with the desired username of the web author, and password with the desired initial password of the web author:

sudo useradd newuser -d /home/newuser

sudo mkdir /home/newuser

sudo passwd newuser password

sudo chown newuser /home/newuser

Repeat this for each website (with related author) you plan to add to your server. Provide the new user with the password you just created for them, and instruct them to change it when they login (SSH) by typing passwd into the server command line.
* Setup Site Directory: In my installation tutorial, we moved the web directory for the server’s default website to /home/yourusername/www. This is fine for a simple, single-website server, but since we will be setting up new virtual sites anyway, we might as well set up an organized website directory structure. Your web directory should consist of three parts, a documents folder where your pages are stored, a cgi-bin folder where your cgi scripts are stored, and a logs folder where your access and error logs are stored. Since you will have different sites with different owners, replicate this structure in the home folder of each site’s author. To do this, type the following into the server command line, replacing username with the username of the web author:

sudo mkdir /home/username/htdocs

sudo chown username /home/username/htdocs

sudo mkdir /home/username/cgi-bin

sudo chown username /home/username/cgi-bin

sudo mkdir /home/username/logs

sudo chown username /home/username/logs

Repeat this for each web author. Note: If you already have an established website for yourself in /home/yourusername/www folder such as mentioned in my installation tutorial, just rename the folder to “htdocs”. Type the following into the server command line, replacing yourusername with your username:

sudo mv /home/yourusername/www /home/yourusername/htdocs

* Enable Virtual Hosting:The first step to setting up virtual hosting is to enable virtual hosts in Apache. This is done by creating a virtual hosts configuration file. Type the following into the server command line:

sudo nano /etc/apache2/conf.d/virtual.conf

Add the following line to the new file:

NameVirtualHost *

Press ctrl+x to quit, y to save changes, then enter to confirm.
* Setup Virtual Sites: Apache makes managing multiple virtual sites easy with its modular structure. It stores each sites configuration file in the /etc/apache2/sites-available/ directory, and allows the administrator to enable or disable them individually with a single command. First, we need to disable and delete the configuration for the default site. Type the following into the server command line:

sudo a2dissite default

sudo /etc/init.d/apache2 reload

sudo rm /etc/apache2/sites-available/default

Now that the default site is gone, create a virtual site for each web author. Type the following into the server command line, replacing sub.domain.com with the [sub]domain name of your site:

sudo nano /etc/apache2/sites-available/sub.domain.com

Now paste the following into the new file, replacing the italicized portions with the appropriate values discussed previously:

#
# sub.domain.com (/etc/apache2/sites-available/sub.domain.com)
#

ServerAdmin youremailaddress
ServerName sub.domain.com
ServerAlias sub.domain.com

# Indexes + Directory Root.
DirectoryIndex index.html index.htm index.php
DocumentRoot /home/authorsusername/htdocs/
# CGI Directory
ScriptAlias /cgi-bin/ /home/authorsusername/cgi-bin/

Options +ExecCGI

# Logfiles
ErrorLog /home/authorsusername/logs/error.log
CustomLog /home/authorsusername/logs/access.log combined

Press ctrl+x to quit, y to save changes, then enter to confirm. Repeat this step for each virtual website.
* Enable site: Now that you have setup your virtual websites, all that is left to do is enable them. Enable each virtual website, one at a time by typing the following into the server command line, replacing sub.domain.com with the names of sites you added in the previous step:

sudo a2ensite sub.domain.com

Note: you can disable a site by typing the similar command into the server command line:

sudo a2dissite sub.domain.com

That’s it! Now, just restart the Apache server and your sites should be online. Type the following into the server command line:

sudo /etc/init.d/apache2 reload

Now you can access separate websites on the same server via unique [sub]domain names. Please comment with any suggestions or additions to these instructions.

from: http://www.corey-m.com/blog/?p=315

Download Title