Friday, February 8, 2008

Using the Python Interpreter

Invoking the Interpreter
The Python interpreter is usually installed as /usr/local/bin/python on those machines where it is available; putting /usr/local/bin in your Unix shell's search path makes it possible to start it by typing the command

python
to the shell. Since the choice of the directory where the interpreter lives is an installation option, other places are possible; check with your local Python guru or system administrator. (E.g., /usr/local/python is a popular alternative location.)
On Windows machines, the Python installation is usually placed in C:\Python24, though you can change this when you're running the installer. To add this directory to your path, you can type the following command into the command prompt in a DOS box:
set path=%path%;C:\python24
Typing an end-of-file character (Control-D on Unix, Control-Z on Windows) at the primary prompt causes the interpreter to exit with a zero exit status. If that doesn't work, you can exit the interpreter by typing the following commands: "import sys; sys.exit()".
The interpreter's line-editing features usually aren't very sophisticated. On Unix, whoever installed the interpreter may have enabled support for the GNU readline library, which adds more elaborate interactive editing and history features. Perhaps the quickest check to see whether command line editing is supported is typing Control-P to the first Python prompt you get. If it beeps, you have command line editing; see Appendix A for an introduction to the keys. If nothing appears to happen, or if P is echoed, command line editing isn't available; you'll only be able to use backspace to remove characters from the current line.
The interpreter operates somewhat like the Unix shell: when called with standard input connected to a tty device, it reads and executes commands interactively; when called with a file name argument or with a file as standard input, it reads and executes a script from that file.
A second way of starting the interpreter is "python -c command [arg] ...", which executes the statement(s) in command, analogous to the shell's -c option. Since Python statements often contain spaces or other characters that are special to the shell, it is best to quote command in its entirety with double quotes.
Some Python modules are also useful as scripts. These can be invoked using "python -m module [arg] ...", which executes the source file for module as if you had spelled out its full name on the command line.
Note that there is a difference between "python file" and "python When a script file is used, it is sometimes useful to be able to run the script and enter interactive mode afterwards. This can be done by passing -i before the script. (This does not work if the script is read from standard input, for the same reason as explained in the previous paragraph.)
Argument Passing
When known to the interpreter, the script name and additional arguments thereafter are passed to the script in the variable sys.argv, which is a list of strings. Its length is at least one; when no script and no arguments are given, sys.argv[0] is an empty string. When the script name is given as '-' (meaning standard input), sys.argv[0] is set to '-'. When -c command is used, sys.argv[0] is set to '-c'. When -m module is used, sys.argv[0] is set to the full name of the located module. Options found after -c command or -m module are not consumed by the Python interpreter's option processing but left in sys.argv for the command or module to handle.
Interactive Mode
When commands are read from a tty, the interpreter is said to be in interactive mode. In this mode it prompts for the next command with the primary prompt, usually three greater-than signs (">>> "); for continuation lines it prompts with the secondary prompt, by default three dots ("... "). The interpreter prints a welcome message stating its version number and a copyright notice before printing the first prompt:
python
Python 1.5.2b2 (#1, Feb 28 1999, 00:02:06) [GCC 2.8.1] on sunos5
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>>
Continuation lines are needed when entering a multi-line construct. As an example, take a look at this if statement:
>>> the_world_is_flat = 1
>>> if the_world_is_flat:
... print "Be careful not to fall off!"
...
Be careful not to fall off!
The Interpreter and Its Environment
Error Handling
When an error occurs, the interpreter prints an error message and a stack trace. In interactive mode, it then returns to the primary prompt; when input came from a file, it exits with a nonzero exit status after printing the stack trace. (Exceptions handled by an except clause in a try statement are not errors in this context.) Some errors are unconditionally fatal and cause an exit with a nonzero exit; this applies to internal inconsistencies and some cases of running out of memory. All error messages are written to the standard error stream; normal output from executed commands is written to standard output.
Typing the interrupt character (usually Control-C or DEL) to the primary or secondary prompt cancels the input and returns to the primary prompt.2.1Typing an interrupt while a command is executing raises the KeyboardInterrupt exception, which may be handled by a try statement.
Executable Python Scripts
On BSD'ish Unix systems, Python scripts can be made directly executable, like shell scripts, by putting the line
#! /usr/bin/env python
(assuming that the interpreter is on the user's PATH) at the beginning of the script and giving the file an executable mode. The "#!" must be the first two characters of the file. On some platforms, this first line must end with a Unix-style line ending ("\n"), not a Mac OS ("\r") or Windows ("\r\n") line ending. Note that the hash, or pound, character, "#", is used to start a comment in Python.
The script can be given an executable mode, or permission, using the chmod command:
$ chmod +x myscript.py
Source Code Encoding
It is possible to use encodings different than ASCII in Python source files. The best way to do it is to put one more special comment line right after the #! line to define the source file encoding:
# -*- coding: encoding -*-
With that declaration, all characters in the source file will be treated as having the encoding encoding, and it will be possible to directly write Unicode string literals in the selected encoding. The list of possible encodings can be found in the Python Library Reference, in the section on codecs.
For example, to write Unicode literals including the Euro currency symbol, the ISO-8859-15 encoding can be used, with the Euro symbol having the ordinal value 164. This script will print the value 8364 (the Unicode codepoint corresponding to the Euro symbol) and then exit:
# -*- coding: iso-8859-15 -*-
currency = u"€"
print ord(currency)
If your editor supports saving files as UTF-8 with a UTF-8 byte order mark (aka BOM), you can use that instead of an encoding declaration. IDLE supports this capability if Options/General/Default Source Encoding/UTF-8 is set. Notice that this signature is not understood in older Python releases (2.2 and earlier), and also not understood by the operating system for script files with #! lines (only used on Unix systems).
By using UTF-8 (either through the signature or an encoding declaration), characters of most languages in the world can be used simultaneously in string literals and comments. Using non-ASCII characters in identifiers is not supported. To display all these characters properly, your editor must recognize that the file is UTF-8, and it must use a font that supports all the characters in the file.
The Interactive Startup File
When you use Python interactively, it is frequently handy to have some standard commands executed every time the interpreter is started. You can do this by setting an environment variable named PYTHONSTARTUP to the name of a file containing your start-up commands. This is similar to the .profile feature of the Unix shells.
This file is only read in interactive sessions, not when Python reads commands from a script, and not when /dev/tty is given as the explicit source of commands (which otherwise behaves like an interactive session). It is executed in the same namespace where interactive commands are executed, so that objects that it defines or imports can be used without qualification in the interactive session. You can also change the prompts sys.ps1 and sys.ps2 in this file.
If you want to read an additional start-up file from the current directory, you can program this in the global start-up file using code like "if os.path.isfile('.pythonrc.py'): execfile('.pythonrc.py')". If you want to use the startup file in a script, you must do this explicitly in the script:
import os
filename = os.environ.get('PYTHONSTARTUP')
if filename and os.path.isfile(filename):
execfile(filename)

Thursday, February 7, 2008

Tutorial on ruby-debug

Installation
The installation procedure requires rubygems package and C compiler available.
Two notes worth to mention:
For Debian users, you must have ruby-dev package installed.
For Windows users, I don't have a precompiled version for Windows available yet.
The installation procedure is very simple: $ sudo gem install ruby-debug

Basic usages
There are two way you can use this library.
rdebug script.
When you start your application with rdebug script, the debugger is activated by default and the execution of your script is halted at the first line of code:$ cat test.rb
puts 'ok'
$ rdebug test.rb
./test.rb:1:puts 'ok'
(rdb:1) list
[-4, 5] in ./test.rb
=> 1 puts 'ok'
(rdb:1)
From this point you can invoke any commands available, such as you can create breakpoints and step through your code.
On demand invocation
You can require ruby-debug library from inside of your application and use a very simple API in order to summon up the debugger. Let's see how it can be done with a Rails application. I'm going to demonstrate it on my BugTrack application.
Open /config/environments/development.rb file and append the following line to the end of the file:require 'ruby-debug'
Open /app/controllers/user_cotroller.rb file and find login action. What we want is to stop our application at this point and explore what is going on in there:def login
debugger # add this line
user = User.auth(@params['login'], @params['pwd'])
if user
@session[USER_PARAM] = user
set_filter(user)
...
end
Note that I've added Kernel#debugger method call in the beginning of this method. When the execution gets to this point, we should see our debugger's command prompt.
Now start the application using webrick. Note you can't use lighttpd, because it forks fastcgi processes in the background and they don't have access to the current terminal. $ ./script/server webrick
=> Booting WEBrick...
=> Rails application started on http://0.0.0.0:3000
=> Ctrl-C to shutdown server; call with --help for options
[2006-07-11 12:42:56] INFO WEBrick 1.3.1
[2006-07-11 12:42:56] INFO ruby 1.8.5 (2006-07-11) [i686-darwin8.7.1]
[2006-07-11 12:42:56] INFO WEBrick::HTTPServer#start: pid=27917 port=3000
Now let's try to login. As soon as I send a login request to the server, on the console I see the debugger prompt:./script/.../controllers/user_controller.rb:59: \
user = User.auth(@params['login'], @params['pwd'])
(rdb:2) _
It displays the name of the file .../user_controller.rb where we hit the breakpoint, the line number (59, in that case) and the line of code which will be executed next. At this point you can start playing with the debugger by entering debugger commands and executing them. For the list of all available commands use help command.
Several most-used commands.
Note that most commands have one-two character abbreviations. Again check the output of help command.
Show me the code. Use list command to browse code in the current context:(rdb:2) list
[54, 63] in ./script/../config/../app/controllers/user_controller.rb
54 end
55 end
56
57 def login
58 debugger
=> 59 user = User.auth(@params['login'], @params['pwd'])
60 if user
61 @session[USER_PARAM] = user
62 set_filter(user)
63 else
You can continue browsing by entering more list commands. In order to move in the opposite direction, use - argument. Also you can specify a line number you want to start your listing from or a range of lines separated by a dash character.
Evaluate an expression in the current context. Just type any expression you want and the debugger will show you a result of this expression evaluation or an exception: (rdb:2) params
{"action"=>"login", "controller"=>"user", "pwd"=>"letmein", \
"login"=>"admin"}
You can also use library by using *pp* command.
Find where we are at this point. Use where command to see the full stack trace.(rdb:2) where
--> #1 ./script/../config/../app/controllers/user_controller.rb:59:in `login'
#2 /usr/.../action_controller/base.rb:910:in `perform_action_without_filters'
#3 /usr/.../action_controller/filters.rb:368:in `perform_action_without_benchmark'
#4 /usr/.../action_controller/benchmarking.rb:69:in `measure'
#5 /usr/.../action_controller/benchmarking.rb:69:in `perform_action_without_rescue'
#6 /usr/.../action_controller/rescue.rb:82:in `perform_action'
...
(rdb:2)
The --> arrow indicates the current stack frame selected. It also shows the current evaluation context (see the next command).
Move up and down the stack frame. You can use up and down commands in order to change the context to the upper or to the lower frame respectively. Eventually, you can evaluate an expression in the new context. Again, you can use where command to see which frame is currently activate.(rdb:2) up 2
#3 /usr/.../action_controller/filters.rb:368:in `perform_action_without_benchmark'
(rdb:2) l
[363, 372] in /usr/.../action_controller/filters.rb
363
364 def perform_action_with_filters
365 before_action_result = before_action
366
367 unless before_action_result == false performed?
=> 368 perform_action_without_filters
369 after_action
370 end
371
372 @before_filter_chain_aborted = (before_action_result == false)
(rdb:2) before_action_result
[:verify_login, :verify_access]
(rdb:2) performed?
false
(rdb:2) down 2
Stepping the program. Use step command to make a single step, use next command to move to the next line without descending inside methods. Both commands accept an optional numeric argument which specifies how many steps to make:(rdb:2) s
script/../config/../app/models/user.rb:27: find :first,
(rdb:2) l
[22, 31] in script/../config/../app/models/user.rb
22 def status_name
23 STATUS_NAMES[self.status]
24 end
25
26 def self.auth(login, pwd)
=> 27 find :first,
28 :conditions => ["login = ? AND pwd = ? AND status = ?",
29 login, pwd, ACTIVE]
30 end
31
32 def is_admin?
Note that you can move up along the frame stack with *up* command and then use next command to continue execution at the new stack level.
Threads listing. Use thread list to display all threads and their statuses. The plus + character and the number indicates the current thread of execution:(rdb:2) thread list
1 # /usr/local/lib/ruby/1.8/webrick/server.rb:91
+2 # script/../config/../app/models/user.rb:27
31 # /usr/local/lib/ruby/1.8/drb/drb.rb:944
(rdb:2)
By the way, the debugger prompt also shows the current thread number (rdb:2).
Display variables from the current context. Use var local and var global to display local and global variables respectively.(rdb:2) var local
login => "admin"
pwd => "letmein"
(rdb:2) var global
$! => nil
$" => ["rubygems.rb", "rbconfig.rb", "rubygems/rubygems_version.rb",
...
Setting breakpoints. Use break [[file:]line] command to add a breakpoint at the given point in the file:(rdb:2) b 69
Set breakpoint 1 at ./script/.../controllers/user_controller.rb:69
or at the method execution:(rdb:2) b User.auth
Set breakpoint 1 at User.auth
Optionally, you can set an expression which defines whether the debugger should stop when it reaches the breakpoint. This expression is evaluated each time at the context of the breakpoint position:(rdb:2) b 72 if params['user'] == 'admin'
Set breakpoint 1 at ./script/.../controllers/user_controller.rb:69
To list all breakpoints use break command without parameters:(rdb:2) break
Breakpoints:
1 ./script/.../user_controller.rb:69
2 ./script/.../user_controller.rb:72 if params['user'] == 'admin'
Continue execution. To continue execution use cont command.(rdb:2) cont
127.0.0.1 - - [11/07/2006:15:09 EDT] "POST /user/login HTTP/1.1" 302 96
http://localhost:3000/bug/list -> /user/login
127.0.0.1 - - [11/07/2006:15:12 EDT] "GET /bug/list HTTP/1.1" 200 3830
http://localhost:3000/bug/list -> /bug/list
List of all commands (rdb:1) help
ruby-debug help v.0.1.3
Commands
b[reak] [fileclass:] [if expr]
b[reak] [class.] [if expr]
set breakpoint to some position,
optionally if expr == true
cat[ch] set catchpoint to an exception
cat[ch] show catchpoint
disp[lay] add expression into display expression list
undisp[lay][ nnn] delete one particular or all display expressions
b[reak] list breakpoints
del[ete][ nnn] delete some or all breakpoints
c[ont] run until program ends or hit breakpoint
r[un] alias for cont
s[tep][ nnn] step (into methods) one line or till line nnn
n[ext][ nnn] go over one line or till line nnn
w[here] display frames
f[rame] alias for where
l[ist][ (-nn-mm)] list program, - list backwards, nn-mm list given lines
up[ nn] move to higher frame
down[ nn] move to lower frame
fin[ish] return to outer frame
q[uit] exit from debugger
v[ar] g[lobal] show global variables
v[ar] l[ocal] show local variables
v[ar] i[nstance] "<"object">" show instance variables of object
v[ar] c[onst] "<" object">" show constants of object
m[ethod] i[nstance] show methods of object
m[ethod] show instance methods of class or module
th[read] l[ist] list all threads
th[read] c[ur[rent]] show current thread
th[read] [sw[itch]] switch thread context to nnn
th[read] stop stop thread nnn
th[read] resume resume thread nnn
p expression evaluate expression and print its value
pp expression evaluate expression and print its value
h[elp] print this help
evaluate
If you find a bug, please file it to the project tracker. Thank you.

Monday, February 4, 2008

MYSQL PART II Update Statement

UPDATE Statement :
The UPDATE query is used to change or modify the existing values in a table.
The Update Syntax is
UPDATE tbl_name SET
col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition];

The UPDATE query updates the columns of existing rows in a table with the new values. The SET clause is used to indicate which columns to be modified. The WHERE clause is used to specify the conditions that identify which rows to be updated.
The following example will set the address of the student to a new address.
mysql> update student set address='welling street' where
address='victoria street';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
But this will set all the address of the students who ever lives in victoria street will be changed to welling street.
Suppose if we want to set the address of a single student to a new address then we can choose the below option.
mysql> update student set address='welling street' where name='jack';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
If we want to change a students mark we can use the below statement.
mysql> update student set marks=100 where name='david';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
This can also be rewritten as the following.
mysql> update student set marks=marks+2 where name='david';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
In UPDATE statement we can also use the arithmetic operations
Operator Precedence:
Operators are used to operate with two operands. Wide collection of Operators are available in MySQL. The operator precedences are shown below in the table.
Highest Precedence :=
1 , OR, XOR
2 &&, AND
3 BETWEEN, CASE, WHEN, THEN, ELSE
4 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
5
6 &
7 <<, >>
8 -, +
9 *, /, DIV, %, MOD
10 ^
11 - (unary minus), ~ (unary bit inversion)
12 !, NOT
Lowest BINARY, COLLATE
If a statement contains paranthesis, then the operations inside the paranthesis are performed first. These operators will be explained in the following sections.
Type Conversion :
Type conversion takes place when operators are used with different types of operands in an expression. Some conversions are done implicitly and some need explicit conversions.
In MySQL the numbers are converted to strings and sometimes strings to numbers depending upon the condition.
Let us consider an example for converting a string to an integer.
mysql> Select 1+'11';
--> 12
Here the string '11' is converted to a number and the result of the expression is also a number.
Lets see another example for converting an integer to a string.
mysql> select concat(1, ' HIOX');
--> '1 HIOX'
We can convert or casting a number to a string explicitly. Here we use CAST() or CONCAT() function.
mysql> select 12, cast(12 as char);
--> 12, '12'
mysql> select 12, concat(12);
--> 12, '12'
Logical Operator :
MySQL supports the following logical operations :
AND(&&) Operator
OR() Operator
NOT(!) Operator
AND(&&) Operator :
The logical AND(&&) operator indicates whether the both operands are true. Lets see a statement using AND operator.
mysql> select studid, name from student where marks > 80
and marks <> select studid, name from student where marks > 80
&& marks <> select name, marks, address from student where
name like 'a%' or name like 's%';(or)
mysql> select name, marks, address from student where
name like 'a%' name like 's%';
+-------+-------+------------------+
name marks address
+-------+-------+------------------+
steve 100 5th cross street
anne 100 downing street
steve 75 downing street
anne 80 edinburgh
+-------+-------+------------------+
4 rows in set (0.00 sec)
In the above statement it will list the name, marks and address of the student whose name starts with the letter A and S.
NOT(!) Operator :
The logical NOT(!) operator have only one operand and it returns the inverse of the value.
mysql> select * from student where not (studid=1);(or)
mysql> select * from student where ! (studid=1);
+--------+-------+-------+-----------------+---------+
studid name marks address phone
+--------+-------+-------+-----------------+---------+
2 david 100 welling street 547896
4 jack 82 welling street 2436821
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
8 mille 98 victoria street 1236547
+--------+-------+-------+-----------------+---------+
6 rows in set (0.00 sec)
It will list all the student details except the studid 1.
Comparison Operator :
Comparison operator is used to compare expressions or values. The result of the comparison will be either True(1) or False(0). MySQL supports the following comparison operators :
EQUAL(=)
LESS THAN(<) LESS THAN OR EQUAL(<=) GREATER THAN(>)
GREATER THAN OR EQUAL(>=)
NOT EQUAL(<>,!=)
BETWEEN
GREATEST
= :
Equal.
mysql> select 1 = 0;
--> 0
mysql> select 0.0 = 0;
--> 1
< : Less than. mysql> select 4.5 <> 1
mysql> select 1.1 <> 0
<= : Less than or equal. mysql> select 2.2 <= 2.2; --> 1
mysql> select 2.2 <= 2.1; --> 0
> :
Greater than.
mysql> select 7 > 2;
--> 1
mysql> select 4 > 4.1;
--> 0
>= :
Greater than or equal.
mysql> select 10 >= 10;
--> 1
mysql> select 4.4 >= 4.5;
--> 0
<>, != :
Not equal.
mysql> select 8 <> 8;
--> 0
mysql> select 7 != 7.7;
--> 1
expr BETWEEN min AND max :
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0.
mysql> select 5 between 5 and 6;
--> 1
mysql> select 'N' between 'M' and 'O';
--> 1
This is same for the expr NOT BETWEEN min AND max, but Not.
GREATEST(value1,value2,...) :
This operator returns the largest argument, compared with two or more arguments.
mysql> select greatest('N', 'M', 'O');
--> O
mysql> select greatest(1, 2);
--> 2
The same rule is applied in finding the LEAST().
Numeric Functions :
Numeric function consists of two main sections. They are :
Arithmetic Operations
Mathematical Functions
Now, lets first discuss about Arithmetic operations.
Arithmetic Operations :
In MySQL, we have the usual Arithmetic operations. Lets see the arithmetic operators one by one with an example.
Addition (+) :
mysql> select 5+5;
--> 10
Subtraction (-) :
mysql> select 25-18;
--> 7
Multiplication (*) :
mysql> select 4*4;
--> 16
Division (/) :
mysql> select 5/3;
--> 1.67
Next lets move to the Mathematical functions
Mathematical Functions :
ABS ACOS ASIN ATAN CEIL
COS COT DEGREES EXP FLOOR
FORMAT
LN LOG LOG(B,X) LOG2
LOG10 MOD PI POWER RADIANS
RAND ROUND SIGN SIN SQRT
TAN TRUNCATE
Now lets see some Mathematical functions with an example.
ABS(X) :
This function returns the Absolute value of the given value.
mysql> select abs(5);
--> 5
mysql> select abs(-25);
--> 25
ACOS(X) :
Returns the arc Cosine value of the given number.
mysql> select acos(0);
--> 1.5707963267949
mysql> select acos(1);
--> 0
ASIN(X) :
Returns the arc Sine value of the given number.
mysql> select asin(1);
--> 1.5707963267949
mysql> select asin(0);
--> 0
ATAN(X) :
Returns the arc Tangent value of the given number.
mysql> select atan(1);
--> 0.78539816339745
mysql> select atan(2);
--> 1.1071487177941
CEIL(X) or CEILING(X) :
Returns the smallest integer nearest to the given value but not less than that.
mysql> select ceil(1.14);
--> 2
mysql> select ceiling(-1.14);
--> -1
COS(X) :
Returns the Cosine of X, where X is given in radians.
mysql> select cos(pi());
--> -1
mysql> select cos(0);
--> 1
COT(X) :
Returns the cotangent value of X.
mysql> select cot(1);
--> 0.64209261593433
mysql> select cot(45);
--> 0.61736962378356
Mathematical Functions :
Now lets see some Mathematical functions with an example.
DEGREES(X) :
This function returns the argument X, converted from radians to degrees.
mysql> select degrees(pi());
--> 180
mysql> select degrees(0.78539816339745);
--> 45
EXP(X) :
Returns the value of the base of natural logarithms(e) raised to the power of X.
mysql> select exp(-1);
--> 0.36787944117144
mysql> select exp(1);
--> 2.718281828459
FLOOR(X) :
Returns the largest integer value not greater than X.
mysql> select floor(5.68);
--> 5
mysql> select floor(-1.26);
--> 2
FORMAT(X,D) :
Formats the given number in a ##,###.## format,rounded to D decimal places.
mysql> select format(7895423.23478,2);
--> 7,895,423.23
mysql> select format(1287654.458,0);
--> 1,287,654
LN(X) :
Returns the natural logarithm of X, the base-e logarithm of X.
mysql> select ln(3);
--> 1.0986122886681
mysql> select ln(-1);
--> NULL
LOG(X) :
This function returns the natural logarithm of X, if it contains a single parameter.
mysql> select log(1);
--> 0
mysql> select log(-2);
--> NULL
LOG(B,X) :
If it contains two parameters, then returns the natural logarithm of X for an arbitrary base B .
mysql> select log(3,4512);
--> 7.659204143237
mysql> select log(1,250);
--> NULL
Mathematical Functions :
Now lets see some Mathematical functions with an example.
LOG2(X) :
Returns the base-2 logarithm of X.
mysql> select log2(7215);
--> 12.816783679379
mysql> select log2(-10);
--> NULL
LOG10(X) :
Returns the base-10 logarithm of X.
mysql> select log10(5);
--> 0.69897000433602
mysql> select log10(-5);
--> NULL
MOD(N,M), N % M, N MOD M :
This function returns the remainder of N divided by M.
mysql> select mod(123, 10);
--> 3
mysql> select 111 % 8;
--> 7
mysql> select 45 mod 4;
--> 1
PI() :
Returns the value of ?(pi).
mysql> select pi();
--> 3.141593
POW(X,Y), POWER(X,Y) :
This function returns the value of XY.
mysql> select pow(3,4);
--> 81
mysql> select pow(3,-4);
--> 0.012345679012346
RADIANS(X) :
This function returns the argument X, converted from degrees to radians.
mysql> select radians(30);
--> 0.5235987755983
mysql> select radians(45);
--> 0.78539816339745
RAND(X) :
This function returns the random floating point number between 0 & 1. If a number is given within this function ,it produces repeatable sequence of values.
mysql> select rand();
--> 0.13388662052703
mysql> select rand(10);
--> 0.65705152196535
mysql> select rand(10);
--> 0.65705152196535
mysql> select rand();
--> 0.82382530888899
mysql> select rand();
--> 0.7174667135975
mysql> select rand(10);
--> 0.65705152196535
Mathematical Functions :
Now lets see some Math functions with an example.
ROUND(X), ROUND(X,D) :
Returns the argument X, rounded to the nearest integer. If it contains two arguments, then returns X rounded to D decimal places.
mysql> select round(12.589);
--> 13
mysql> select round(-12.23);
--> -12
mysql> select round(5.258, 1);
--> 5.3
mysql> select round(5.258, -1);
--> 10
SIGN(X) :
Returns the sign of the given number as -1 if negative,0 if zero, or 1 if positive.
mysql> select sign(-89);
--> -1
mysql> select sign(0);
--> 0
mysql> select sign(123);
--> 1
SIN(X) :
Returns the sine value of X,X given in radians.
mysql> select sin(45);
--> 0.85090352453412
mysql> select sin(pi());
--> 1.2246063538224e-016
SQRT(X) :
Returns the square root of the number,the number should be a positive number.
mysql> select sqrt(144);
--> 12
mysql> select sqrt(741);
--> 27.221315177632
TAN(X) :
Returns the tangent of X, where X is given in radians.
mysql> select tan(45);
--> 1.6197751905439
mysql> select sqrt(90);
--> -1.9952004122082
TRUNCATE(X,D) :
The number is truncated to D digits after decimal point. If D is negative, the D digits before the decimal point are converted to 0.
mysql> select truncate(4.556,1);
--> 4.5
mysql> select truncate(444.556,-1);
--> 440
String Function :
Functions are predefined set of instructions that returns a value. Functions which involves strings are called as String functions. There are different types of functions availble in MySQL.
The important string functions are,
CHAR_LENGTH
CONCAT CONCAT_WS FORMAT
LCASE
LENGTH LOCATE REPEAT
REPLACE REVERSE SUBSTRING
CHAR_LENGTH(str) or CHARACTER_LENGTH(str) :
This string function returns the length of the string.
mysql> select char_length("hioxindia");
--> 9
mysql> select character_length("easycalculation");
--> 15
CONCAT(str1,str2,...) :
Returns the concatenated string of the given arguments.
mysql> select concat('hiox','india');
--> 'hioxindia'
CONCAT_WS() :
It stands for Concatenate With Separator and is a special form of CONCAT function. Returns the concatenated string of the given arguments seperated by given seperator.
mysql> SELECT CONCAT_WS('!','One','Two','Three');
--> 'One!Two!Three'
( Here '!' is the seperator)
FORMAT() :
Formats the given no and rounds to the given digits after decimal point.
mysql> SELECT FORMAT(12332.123456, 4);
--> '12,332.1235'
LCASE(str) or LOWER() :
Returns the lowercase of the given string.
mysql> select lcase('HIOX');
--> 'hiox'
mysql> select lower('EASYCALCULATION');
--> easycalculation
Like wise UPPER or UCASE returns the uppercase of the given string.
LENGTH(str) :
Returns the length of the given string in bytes. If there is a 2 byte character the length is calculated as 2. Whereas the CHAR_LENGTH calculates only the character length.
mysql> select length('HIOXINDIA');
--> 9
LOCATE(substr,str) or POSITION(substr IN Str) :
Returns the position of the first occurance of the substring in the string.
mysql> select locate('ind','hioxindia');
--> 5
mysql> select position('cul' in 'easycalculation');
--> 8
REPEAT(str,count) :
The given string is repeated for the given count.
mysql> select repeat('HIOX',2);
--> 'HIOXHIOX'
REPLACE(str,from_str,to_str) :
In the given string 'str' the 'from_str' is replaced by the 'to_str' string.
mysql> select replace('MyMYSql','My','you');
--> youMYSql
The given 'from_str' is case sensitive. Here in the above example the first 'My' is changed but not the second('MY').
REVERSE(str) :
The given string is reversed and returned.
mysql> select reverse('HIOX');
--> 'XOIH'
SUBSTRING(str,pos) :
The function returns a substring from the string 'str' starting at position 'pos'.
mysql> select substring('EASYCALCULATION', 5);
--> 'CALCULATION'
Date and Time Functions :
This function is used to manipulate the display format of a date and time. Lets see some basic functions for date and time.
CURDATE CURTIME DATEDIFF DATE_ADD
DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR
HOUR MINUTE MONTH MONTHNAME
NOW CURDATE() :
This date function returns the current date in the format 'YYYY-MM-DD' or 'YYYYMMDD'.
mysql> select curdate();
--> 2007-01-03
CURTIME() :
Returns the current time in the format 'HH:MM:SS' or 'HHMMSS'.
mysql> select curtime();
--> 17:33:07
DATEDIFF(expression1,expression2) :
expression1 and expression2 are date or date-and-time expressions. This function returns expression1 – expression2 expressed as a value in days from one date to the other. Here only the date parts will be considered for calculation.
mysql> select datediff('2007-2-6 17:33:25','2007-1-1');
--> 36
DATE_ADD(datetime, INTERVAL expression datetimetype) :
This date function adds the expression to the datetime supplied.
mysql> select date_add('2007-1-14', interval 15 day);
--> 2007-01-29
This function is same for DATE_SUB, but subtracting will take place instead of adding.
DAYNAME(date) :
Returns the name of the day for the specified date.
mysql> select dayname('2007-01-04');
--> Thursday
DAYOFMONTH(date) or DAY(date) :
Returns the date for the day of the month in the range of 1 to 31. DAY() is a synonym for DAYOFMONTH().
mysql> select dayofmonth('2007-01-04');
--> 4
DAYOFWEEK(date) :
Returns the day of the week in the numeric format as 1 for Sunday to 7 for Saturday.
mysql> select dayofweek('2007-01-04');
--> 5
DAYOFYEAR(date) :
Returns the day of the year for given date in the numeric format, in the range 1 to 366.
mysql> select dayofyear('2007-07-09');
--> 190
HOUR(time) :
Returns the hour of the specified time in the numeric format from 0 to 23.
mysql> select hour('14:46:12');
--> 14
MINUTE(time) :
Returns the minute of the specified time in the numeric format from 0 to 59.
mysql> select minute('14:46:12');
--> 46
MONTH(date) :
Returns the month for the given date in the numeric format, in the range 0 to 12. mysql> select month('2007-07-09');
--> 7
MONTHNAME(date) :
Returns the name of the month for the specified date.
mysql> select monthname('2007-07-09');
--> July
NOW() :
This date time function returns the current date and time in the format 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.
mysql> select now();
--> 2007-01-04 14:56:15

Sunday, February 3, 2008

MYSQL PART II TABLES

Creating tables :
Once you have selected the database, we can start creating tables. The CREATE statement is used to create a table in MySQL with constraint. A Constraint is restriction to the behavior of a variable.
The Create syntax is

CREATE TABLE tableName
( fieldName1 dataType(size) [NULL NOT NULL]
fieldName2 dataType(size) [NULL NOT NULL] );
If NULL is specified, the field is allowed to be left empty. If NOT NULL is specified, the field must be given a value. In the absence of either a NULL or NOT NULL, NULL is assumed.
The below example query will help you in creating table:
CREATE TABLE student
( studID INT(5),
name VARCHAR(30),
);
The above query will create the table student with fields ID and Name.
PRIMARY KEY :
A PRIMARY KEY is a field in a table that uniquely identifies a record. This attribute is used to define the field name to create a primary key.
Example :
fieldName INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
The PRIMARY KEY is specified after defining the fields in the below example:
CREATE TABLE student
( studID INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY(studID)
);
We can also create a compound primary key. A compound primary key is where more than one field is used to uniquely identify a record.
Lets create a table for holding student details in a class.
mysql> create table student(studid int(10), name varchar(20), address varchar(40), phone int(10));
Query OK, 0 rows affected (0.05 sec)
Desc table :
We can examine the structure of a table using the DESCRIPTION or DESC statement. The following query describes the structure of the student table.
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
We can also use the SHOW FIELDS FROM statement to display the same structure.
mysql> SHOW FIELDS FROM student;
Listing Tables :
We can list all the tables in the database using SHOW TABLES query. The following query will list the tables in the current database.
mysql> show tables;
+--------------------+
Tables_in_sample
+--------------------+
student
+--------------------+
1 row in set (0.00 sec)
Deleting tables :
The DROP statement is used to delete one or more tables completely from a database.
The syntax is
DROP TABLE tbl_name
The following example deletes the student table.
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
This query will permanently remove or delete the table student.
DROP TABLE query drops all fields in the table and deletes the table. Once the DROP TABLE statement is used, we cannot use that table. So, we should be careful with this statement.
Renaming tables :
The RENAME statement is used to rename one or more tables in a database.
The syntax is
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
The following example query renames the student table as class table.
mysql> rename table student to class;
Query OK, 0 rows affected (0.00 sec)
Now we can view the table whether the name is changed by the following query.
mysql> show tables;
+--------------------+
Tables_in_sample
+--------------------+
class
+--------------------+
1 row in set (0.00 sec)
If the query renames more than one table, renaming operations are done from left to right.
We can also swap two table names. Let us assume tmp table which does not exists.
Example :
RENAME TABLE emp1 TO tmp,
emp2 TO emp1,
tmp TO emp2;
We can also use RENAME TABLE to move a table from one database to another.
Example :
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
ALTER TABLE :
ALTER TABLE is used to change the structure of an existing table. We can add or delete columns, change the type of existing columns, or rename columns or the table itself. We can also change the comment for the table and type of the table.
The Syntax is
ALTER TABLE tbl_name alter_specification [, alter_specification] ...
The below table will describe the alter specification :
Alter Specification Description
Rename Rename a Table name
Add Add a new column, key, index
Add First Add a column First
Add After Add a column After
Drop Drop a column, Index, key
Change Change a column name
Change Type Change a column type
Modify Modify a column type
Renaming a Table :
We can also RENAME the table using ALTER TABLE. The following example query renames the table student to class.
mysql> ALTER TABLE student RENAME class;
The above query will change the table name.
Adding a column to a table :
The ADD COLUMN modifier is used to add a column to a table. The following example query adds a field called marks to the student table.
mysql> ALTER TABLE student ADD COLUMN marks INT(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
marks int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Next we move to the alterations in displaying the tables.
Add a column First :
We can position the field using FIRST and AFTER modifiers. The following example query will place the new field as the first field in the table.
mysql> ALTER TABLE student ADD COLUMN marks INT(10) FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
marks int(10) YES NULL
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Add a column After :
We can also place the new field next to any of the field. The following example query will place the new field immediately after the field name.
mysql> ALTER TABLE student ADD COLUMN marks INT(10) AFTER names;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
marks int(10) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Next we can see how to delete and change a field.
Delete a column :
The DROP COLUMN is used to delete a column from the table.
The syntax is
ALTER TABLE tbl_name DROP col_name;
The following query drops the field marks.
mysql> ALTER TABLE student DROP COLUMN marks;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
name varchar(20) YES NULL
address varchar(40) YES NULL
phone int(10) YES NULL
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Change a column name :
When we modify a column, we have to specify the attribute of the column again. The following example renames the name field to stud_name in the student table.
mysql> ALTER TABLE student CHANGE name stud_name VARCHAR(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+-----------+-------------+------+-----+---------+-------+
studid int(10) YES NULL
stud_name varchar(20) YES NULL
Counting Rows :
COUNT(*) counts the number of rows in a table.
The syntax is
SELECT COUNT(*) from tbl_name;
Example :
mysql> select count(*) from student;
+----------+
count(*)
+----------+
5
+----------+
1 row in set (0.00 sec)
The above query will list the number of rows in the student table.
After inserting datas into the table, we probably want to check the datas are stored correctly. To do so, we use the SELECT statement.
The Select syntax is
SELECT what_to_select from tbl_name;
To view all the data from the table, we use the below query.
mysql> select * from student;
+--------+---------+-------+------------------+---------+
studid name marks address phone
+--------+---------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
8 mille 98 victoria street 1236547
+--------+---------+-------+------------------+---------+
8 rows in set (0.00 sec)
The above example query will list the complete details of the student table. Here * will select all the columns from the table.
LIMIT :
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. It takes one or two numeric arguments, which must both be non-negative integer constants.
Lets see an example query for SELECT LIMIT statement.
mysql> select * from student limit 2,5;
+--------+---------+-------+-----------------+---------+
studid name marks address phone
+--------+---------+-------+-----------------+---------+
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
+--------+---------+-------+-----------------+---------+
5 rows in set (0.00 sec)
Here the first argument of th query specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. Therefore it retrieves the rows from 3 - 7 from the student table.
We can also return the values from the beginning of the result set by specifying the number of rows in a single argument as follows.
mysql> select * from student limit 5;
+--------+---------+-------+------------------+---------+
studid name marks address phone
+--------+---------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
+--------+---------+-------+------------------+---------+
5 rows in set (0.08 sec)
The above query will retrieve the first five rows of the student table.
We can select a particular column to display, regretting the entire rows. Suppose you want to see the name of the students alone we can use the below query.
mysql> select name from student;
+---------+
name
+---------+
steve
david
michael
jack
anne
steve
anne
mille
+---------+
8 rows in set (0.27 sec)
We can also select multiple columns, separated by commas as given in the below query.
mysql> select name, marks from student;
+---------+-------+
name marks
+---------+-------+
steve 100
david 98
michael 75
jack 82
anne 100
steve 75
anne 80
mille 98
+---------+-------+
8 rows in set (0.03 sec)
In the above query, we have selected both name and marks from the table student
In the preceding examples, the query for selected rows are displayed in no particular order. We can also select the rows to display in an ordered format using ORDER BY Clause.
The following example query will sort the rows in an ascending order based on the marks.
mysql> select name, marks from student order by marks;
+---------+-------+
name marks
+---------+-------+
michael 75
steve 75
anne 80
jack 82
david 98
mille 98
steve 100
anne 100
+---------+-------+
8 rows in set (0.03 sec)
We can also sort the orders in descending order. In the below example query the marks are sorted in descending order.
mysql> select name, marks from student order by marks desc;
+---------+-------+
name marks
+---------+-------+
steve 100
anne 100
david 98
mille 98
jack 82
anne 80
michael 75
steve 75
+---------+-------+
8 rows in set (0.00 sec)
Next we can see how to sort the multiple columns.
We can also sort multiple columns in different directions as given in the below query.
mysql> select name, marks, address from student order by name,
marks desc;
+---------+-------+------------------+
name marks address
+---------+-------+------------------+
anne 100 downing street
anne 80 edinburgh
david 98 welling street
jack 82 victoria street
michael 75 edinburgh
mille 98 victoria street
steve 100 5th cross street
steve 75 downing street
+---------+-------+------------------+
8 rows in set (0.00 sec)
Here we have selected three columns name, marks and address. In this query we have sorted the column name alone in ascending order and we have additionally mentioned marks in descending order.
So if there are same names, the highest mark will be taken as the first priority. In the above example query, there are 2 anne, so the anne with highest mark will be displayed first
We can select a particular row using the WHERE clause statement. We can also check any condition using WHERE clause or keyword. Where condition comes handy when we come across a big table having huge volume of data but we might want to see only small number of rows satisfying a condition.
The select where syntax is
SELECT what_to_select FROM tbl_name WHERE conditions_to_satisfy;
Here the conditions_to_satisfy specifies one or more conditions that rows must satisfy to qualify for retrieval.
Let's see an example query for retrieving a single student data using the WHERE clause.
mysql> select * from student where name = 'jack';
+--------+------+-------+-----------------+---------+
studid name marks address phone
+--------+------+-------+-----------------+---------+
4 jack 82 victoria street 2436821
+--------+------+-------+-----------------+---------+
1 row in set (0.00 sec)
Here in the above example query we have retrieved a single student details. Suppose if we want to retrieve the student's details who have secured more than 90 marks, we can use the below query.
mysql> select * from student where marks > 90;
+--------+-------+-------+------------------+---------+
studid name marks address phone
+--------+-------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
5 anne 100 downing street 2634821
8 mille 98 victoria street 1236547
+--------+-------+-------+------------------+---------+
4 rows in set (0.05 sec)
Sometimes we may need to look for the table with a certain matching character. In MySQL we use LIKE or NOT LIKE operator for comparison. In MySQL the patterns are case-insensitive by default.
Let us consider an example query to display the student names starting with the letter M.
mysql> select * from student where name like 'm%';
+--------+---------+-------+-----------------+---------+
studid name marks address phone
+--------+---------+-------+-----------------+---------+
3 michael 75 edinburgh 2598234
8 mille 98 victoria street 1236547
+--------+---------+-------+-----------------+---------+
2 rows in set (0.01 sec)
In the above example query, it will list all the names that starts with the letter M from the table student.
The following example query will list the names that ends with letter e.
mysql> select * from student where name like '%e';
+--------+-------+-------+------------------+---------+
studid name marks address phone
+--------+-------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
5 anne 100 downing street 2634821
6 steve 75 downing street 2874698
7 anne 80 edinburgh 2569843
8 mille 98 victoria street 1236547
+--------+-------+-------+------------------+---------+
5 rows in set (0.00 sec)
We can also list the names that contains a specific letter anywhere. The following example query will list the names that contains "a".
mysql> select * from student where name like '%a%';
+--------+---------+-------+-----------------+---------+
studid name marks address phone
+--------+---------+-------+-----------------+---------+
2 david 98 welling street 547896
3 michael 75 edinburgh 2598234
4 jack 82 victoria street 2436821
5 anne 100 downing street 2634821
7 anne 80 edinburgh 2569843
+--------+---------+-------+-----------------+---------+
5 rows in set (0.00 sec)
Suppose if we want to find the names that contain exactly five characters, we use a special character "_"(underscore). The following query will list all the five letter names from the table student.
mysql> select * from student where name like '_____';
+--------+-------+-------+------------------+---------+
studid name marks address phone
+--------+-------+-------+------------------+---------+
1 steve 100 5th cross street 2456987
2 david 98 welling street 547896
6 steve 75 downing street 2874698
8 mille 98 victoria street 1236547
+--------+-------+-------+------------------+---------+
4 rows in set (0.00 sec)
Group By :
The Group by clause is used to display the rows and columns grouped by selective columns. It can be used to perform the aggregate functions, such as count().
The following example query will list the name of the student and also count the repeative names using Group By clause in the select statement.
mysql> select name, count(name) from student group by name;
+-------+-------------+
name count(name)
+-------+-------------+
anne 2
david 1
jack 1
mille 1
steve 2
+-------+-------------+
5 rows in set (0.03 sec)
The below query will display the name and sum of marks of the student using groupby clause.
mysql> select name,sum(marks),count(*) from students group by name;
+----------+------------+----------+
name sum(marks) count(*)
+----------+------------+----------+
anne 175 2
maichael 82 1
mike 182 2
rock 100 1
steve 175 2
+----------+------------+----------+
5 rows in set (0.00 sec)