How To Select Statement Extensions With Order by

Until now, we have been showing simple SELECT commands both with and without conditions. The SELECT command has a whole list of extensions that can be used to further filter and manipulate the data received from the database.

When we pulled data from the database table in the query examples earlier, MySQL returned the data in the same order it was placed in the table. For the most part, this works just fine because we just want to get the data out of the database. At other times, it might be important that the data be ordered in some specific fashion. For example, suppose you want to sort the data in ascending order (the default) based on the username:

mysql> SELECT * FROM person ORDER BY username;

+---------+-----------+----------+

| acc_id | username | password |

+---------+-----------+----------+

| 1000003 | John | jhn |

| 1000004 | Michael | Mic |

+---------+-----------+----------+

2 rows in set (0.00 sec)

As you can see in the output from the query, the data is displayed in alphabetical order based on the username. You can also sort based on a numeric column

mysql> SELECT * FROM person ORDER BY id;

+---------+-----------+----------+

| id | username | password |

+---------+-----------+----------+

| 1000003 | John | jhn |

| 1000004 | Michael | Mic |

+---------+-----------+----------+

2 rows in set (0.00 sec)

How To Select Data From The Table

Once you’ve inserted your data into a database, you can extract that data to make business decisions. You pull data from the database by using the SELECT command, which has the following format :

SELECT <columns>

FROM <databaseTable>

WHERE <conditions for data>

The SELECT command has three different components at its core. The first is the <columns> element, which tells the database the columns where values should be returned. The element can be * (representing all columns) or a list of columns separated by commas. The second component is the <databaseTable> element, which represents the exact table from which the data should come.The third component is the <conditions for data> element, which represents under what conditions the data should be pulled from the database.First, we pull data using the simplest SELECT:

mysql> select * from person;

+---------+-----------+----------+

| id | username | password |

+---------+-----------+----------+

| 1034033 | John | jhn |

| 1034055 | Michael | Mic |

+---------+-----------+----------+

2 rows in set (0.00 sec)

This SELECT command tells the database to pull all columns, using the * character, from the person table. The database responds with a “table” using a heading with the column names found in the database used when we first defined the table. Next, all of the data from the table is placed in the output “table” and displayed accordingly.

Now we can limit the columns of data with our SELECT:

mysql> SELECT id, username FROM person;

+---------+-----------+

| id | username |

+---------+-----------+

| 1034033 | john |

| 1034055 | Michael |

+---------+-----------+

2 rows in set (0.00 sec)

In this example, we have specifically listed the columns we wish to pull data from and at the same time requested all of the data. The system will output the data in the familiar table format. The same query will be used, but a condition is placed on the data we wish to pull.

mysql> SELECT id, username FROM person WHERE username = ’john’;

+---------+----------+

| id | username |

+---------+----------+

| 1034034 | john |

+---------+----------+

1 row in set (0.00 sec)

The same query is used here, but a WHERE clause limits the data to be pulled based on the actual value found in the username field. The condition with the SELECT query can hold logical operators to further refine the selection criteria.

For example:

mysql> SELECT * FROM person WHERE password IS NULL

AND username = ’michael’;

+---------+----------+----------+

| id | username | password |

+---------+----------+----------+

| 1034034 | michael | NULL |

+---------+----------+----------+

1 row in set (0.00 sec)

In this query, the system selects all of the rows in the person table where a value is NULL, and the username value is michael.

How To Insert Data Into Table

With our database and table defined, we need to add it with sample data.Here’s the data that we would like to insert into the table:

id username password

1000001 jhon jhn

1000002 Michael Mic

We can place the data in the database table by using the INSERT command. The format of the MySQL INSERT command is:



INSERT INTO <table> VALUES(<columnValue>,<columnValue>,..)



We have to issue three INSERT commands to get all of the information into the database. Here’s the output from one INSERT:



mysql> INSERT INTO person VALUES(1000001, ’jhon’, ’jhn’);

Query OK, 1 row affected (0.00 sec)

Introducing

Let’s examine the INSERT command a little more closely. First consider the order of the data. The order must match the columns defined in the table as shown by the DESCRIBE command. The second important factor is the use of single quotes to indicate a value is a string and should be treated as such by MySQL. If you didn’t want to insert a password into a row, you could use a NULL value. For example:



mysql> INSERT INTO person VALUES(1000002, ’Michael’, NULL);

Query OK, 1 row affected (0.00 sec)



In this example, the NULL value is placed directly into the database in place of a string value.

How To Create Table

The table is where all of the data is stored in a particular database. Because we are working with a relational database system, the data is stored in rows and columns. Our goal in creating a table is to determine what will be stored in each table column. Once this information has been established, we can decide on the column types and potential sizes.

Once we have identified all of the fields and assigned each a type, we can create the table. To create a table, use this command:

mysql> create table person (

id int primary key,

username varchar(20),

password varchar(15)

Query OK, 0 rows affected (0.00 sec)



We can see all of the tables in our database with the following command:



mysql> show tables;

+--------------------+

Tables_in_samples

+--------------------+

person

+--------------------+

1 row in set (0.00 sec)

How To Create Database In MySQL

A database is just a container for components called tables. A DBMS can have as many databases as needed for a given application.For the most part, you create a database when your application needs a place to store data.



The client tool contacts the local MySQL installation and returns a prompt as shown here:



Welcome to the MySQL monitor. Commands end with ; or ’g.

Your MySQL connection id is 139 to server version: 4.1.10a-nt

Type ’help;’ or ’’h’ for help. Type ’’c’ to clear the buffer.

mysql>

In our example, we want to create a new database called samples that will hold numerous tables all related to samples needed by some application. In the most basic form, the database is created with the following command:



mysql> create database samples;

Query OK, 1 row affected (0.00 sec)



nb : you can call mysql with the run menu and type mysql an click OK button as shown in picture below :

How to make pascal program readable

The most important syntax is the semicolon after the program heading (which is used

as a separator) and the period after the word END (which terminates the program).

A common convention is to write Pascal keywords in uppercase and the user-invented

names (identifiers) in lowercase with the first letter capitalized. If the name consists of

more than one word (which is the case in this program), the first letter in each word is

capitalized. So, in Pascal programs you may see identifiers like:

Wages

Payroll

HoursWorkedPerWeek

This is just a convention to make your program readable, but Pascal compilers are not

case-sensitive. This means that you can write the entire program in lowercase as in

Example 1-2, or in uppercase as in Example 1-3. All three versions of the program will

compile and run.

{ ---------------------------- Example 1-2 ----------------------------- }

program hello_world;

begin

writeln(’Hello World!’)

end.

{ ----------------------------------------------------------------------- }

{ ----------------------------- Example 1-3 ----------------------------- }

PROGRAM HELLO_WORLD;

BEGIN

WRITELN(’Hello World!’)

END.

{ ---------------------------------------------------------------------- }

All blank lines, indentation, and spaces (except those following the Pascal keywords)

are optional, but it is a good programming habit to use this method to make your program

well-organized and readable.

How to make program heading

The second line in pascal program is called the program heading. It starts with the keyword PROGRAM,

followed by a space, followed by the program name (Hello_World). The program

name is a user-invented word. User-invented words are classified in Pascal as identifiers.

An identifier must begin with a letter and may contain any number of letters or

digits (in Turbo Pascal it may contain underscores as well). You are free to choose any

meaningful name for your program, but do not expect a program name like BEGIN or

PROGRAM to be accepted. These words are called reserved words, and they are only

used in the proper place in the program.

In Turbo Pascal the program heading is optional. You may skip the whole line and start

your program with the word BEGIN.

How to make Comments in Pascal

Consider the first line in the program:

{ ---------------------------- Comments Example ----------------------------- }

This is a comment and is totally ignored by the compiler. Comments can appear anywhere

in the Pascal program between two braces ({}) or between the two symbols (*

and *) like this:

(* This is a comment *)

How to make "Hello Word" Program

Let us start with the smallest Pascal program, which is shown in the following example.

It displays on your screen the phrase “Hello World!”

{ ----------------------------- Smallest Programs Example ----------------------------- }

PROGRAM Hello_World;

BEGIN

WRITELN(’Hello World!’)

END.

{ ---------------------------------------------------------------------- }

Whether the Pascal program is small or large, it must have a specific structure. This

program consists mainly of one statement, WRITELN, which does the actual work

here, as it displays whatever comes between the parentheses. The statement is

included inside a frame starting with the keyword BEGIN and ending with the keyword

END. This is called the program main body (or the program block) and usually

contains the main logic of data processing. The Pascal program may also contain other

building blocks called procedures and functions, which are explained in the other article.