Unknown column in ‘where clause’ for alias name

first of all below given is the format for a mysql query with the presedence of different clauses:

    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO @var_name [, @var_name]]

Now the Reason why you cannot use an alias in where clause in a mysql query is:
It is not allowable to refer to a column alias in a
          WHERE clause, because the column value
          might not yet be determined when the WHERE
          clause is executed.

the option you have to this is, you can use having clause instead of where clause.

MySQL statements without delimiter

There are two statements in MySQL that do not require the end delimiter (which is ‘;’ by default). These statements are as given below:


The USE statement is used to select the database in MySQL as your current database. The database selected by USE statement remains default until you change it with another USE statement or the command line session expires. Also it does not harm if you write ‘;’ or the delimiter used by you to end the USE statement. The syntax for use statement is as below:

USE db1; or USE db1 both will work

where db1 is the name of the database.


The above statement is used to quit your current MySQL client session. This command does not affect any nodes connected to the cluster.

Alternative to ‘insert’ in mysql

There is one more method other than using “insert” statement to insert data in mysql tables. This method is to load data from a text (.txt) file. The text file from which you want to insert data into the given table should be in proper format.

Here’s an example how to do it:

suppose this is the table structure to which we want to insert data from a myfriends.txt file.

if you don’t have then you can create a table as given below:

CREATE TABLE myfriends (name VARCHAR(20), location VARCHAR(20), sex CHAR(1), birth DATE);

and the myfriends.txt should be like this:


and the statement to load the above given data goes as below:

mysql> load data local infile ‘myfriends.txt’ into the table myfriends fields terminated by ‘~’ line terminated by ‘#’;

where ‘~’ is the fields separator and ‘#’ is the row or line separator.

also the myfriends.txt file should be in the same directory where the bin folder is present.

Hope this helps you,
Sachin (samsami2u@gmail.com)

MySQL file formats

MySQL uses a number of different file formats for the storage of information. This section covers the different file formats and how to read, write and understand the contents.

MySQL .frm File Format

Regardless of the storage engine you choose, every MySQL table you create is represented, on disk, by a .frm file, which describes the table’s format (i.e. the table definition). The file bears the same name as the table, with a .frm extension. The .frm format is the same on all platforms but in the description of the .frm format that follows, the examples come from tables created under the Linux operating system.

The .frm file associated with table1 can be located in the directory that represents the database (or schema) to which the table belongs. The datadir variable contains the name of this directory:

/mysql/datadir/database_name for windows.

where datadir is the path set in my.ini file of mysql


#Path to the database root

and “database_name" is the name of the database whose table's .frm files you want.

or you can find it out with the help of this mysql statement:


Also there are .frm files for the views of the database.