MySQL guide - Querying MySQL tables

Our employee_data table now contains enough data for us to work with. Let us see how we can extract (query) it. Querying involves the use of the MySQL SELECT command.

Data is extracted from the table using the SELECT SQL command. Here is the format of a SELECT statement:

SELECT column_names from table_name [WHERE ...conditions];

The conditions part of the statement is optional (we'll go through this later). Basically, you require to know the column names and the table name from which to extract the data.

For example, in order to extract the first and last names of all employees, issue the following command.

SELECT f_name, l_name from employee_data;

The statement tells MySQL to list all the rows from columns f_name and l_name.

-----
In this section of the MySQL tutorial we'll look at the format of a SELECT statement we met in the last session in detail. We will learn how to use the select statement using the WHERE clause.

SELECT column_names from table_name [WHERE ...conditions];

Now, we know that the conditions are optional (we've seen several examples in the last session... and you would have encountered them in the assignments too).

The SELECT statement without conditions lists all the data in the specified columns. The strength of RDBMS lies in letting you retrieve data based on certain specified conditions.
In this session we'll look at the SQL Comparision Operators.
The = and != comparision operators for MySQL Select

SELECT f_name, l_name from employee_data where f_name = 'John';

+--------+------------+
| f_name | l_name |
+--------+------------+
| John | Hagan |
| John | MacFarland |
+--------+------------+
2 rows in set (0.00 sec)

This displays the first and last names of all employees whose first names are John. Note that the word John in the condition is surrounded by single quotes. You can also use double quotes. The quotes are important since MySQL will throw an error if they are missing. Also, MySQL comparisions are case insensitive; which means "john", "John" or even "JoHn" would work!

SELECT f_name,l_name from employee_data where title="Programmer";

+--------+------------+
| f_name | l_name |
+--------+------------+
| Fred | Kruger |
| John | MacFarland |
| Edward | Sakamuro |
| Alok | Nanda |
+--------+------------+
4 rows in set (0.00 sec)


Selects the first and last names of all employees who are programmers.

SELECT f_name, l_name from employee_data where age = 32;
+---------+--------+
| f_name | l_name |
+---------+--------+
| John | Hagan |
| Ganesh | Pillai |
| Alok | Nanda |
| Arthur | Hoopla |
| Kim | Hunter |
| Shahida | Ali |
+---------+--------+
6 rows in set (0.00 sec)


This lists the first and last names of all empoyees 32 years of age. Remember that the column type of age was int, hence it's not necessary to surround 32 with quotes. This is a subtle difference between text and integer column types.

The != means 'not equal to' and is the opposite of the equality operator.


http://www.webdevelopersnotes.com/tutorials/sql/mysql_guide_querying_mysql_tables.php3

MySQL Tutorial

1. mysql -h host -u user -p

2. enter password

2.a. Show databases:
SHOW DATABASES;

3. Build a database:
CREATE DATABASE ;

4. Use the Database:
USE ;
SHOW TABLES;

5. Build a table:
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
6. Load data from a file:

LOAD DATA INFILE 'testOne.csv' INTO TABLE ATCCode FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'(ATCCodes, genericName, tradeName, country);

where testOne.csv is the name of the data file and it must be under /var/lib/mysql/
e.g if the dir called testOne, then /var/lib/mysql/testOne/

ATCCode is the name of the table.

1. Using the LOAD DATA INFILE SQL statement For security reasons, no one has the mysql FILE priv, which means you cannot "LOAD DATA INFILE". You can,

1. Using the LOAD DATA INFILE SQL statement

For security reasons, no one has the mysql FILE priv, which means you cannot "LOAD DATA INFILE". You can, however, use a "LOAD DATA LOCAL INFILE" statement as long as you have a mysql prompt on our system and have uploaded the data file to your account here first.

The "LOAD DATA LOCAL INFILE" statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.

To import a file this way, first upload your data file to your home directory on our system with FTP or SCP. Then get a shell prompt on our system, and then a MySQL Monitor prompt so that you can issue the SQL that will import your file.

For example, suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.

To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.
http://www.modwest.com/help/kb6-253.html

Import Unicode CSV files to MySQL

Unicode UTF-8 comma-separated values (CSV) text files, which are exported or generated by such applications as Microsoft Access or Excel, can be imported to MySQL via LOAD DATA INFILE command. CSV data files that are in a Vietnamese legacy encoding should first be converted to Unicode UTF-8, using UnicodeConverter tool, before proceeding with the import.

Make sure that MySQL default charset is utf8. You may need to create the schema (i.e., database structure and tables) before executing the LOAD DATA command. This can be accomplished manually or by MySQL Migration Toolkit to re-create the schema in MySQL database and then use TRUNCATE command to clear the table (delete all rows) before importing. For example:

mysql> TRUNCATE TABLE authors;

or

mysql> DELETE FROM authors;

The import will be executed as follows:

mysql> LOAD DATA LOCAL INFILE 'authors.txt' INTO TABLE authors FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

for a CSV file 'authors.txt' with records having data fields as follows:

"authid","lastname","firstname","address","city","country","phone","email"
1,"Nguyễn","Trần","Lý Thường Kiệt","Sài Gòn","Việt Nam","848-999-9999","nguyentran@yahoo.com"
2,"Lê","Lý","Phố Quang Trung","Hà Nội","Việt Nam","848-888-8888","lely@yahoo.com"

The line terminator '\r\n' is for Windows systems; for Unix/Linux, '\n' is used.
http://vietunicode.sourceforge.net/howto/importcsvmysql.html

how to autorun a program everytime a turn on my pc

hi! im a newbie here.. Just wanna ask how to autorun a program everytime a turn on my pc.. It is a .c file.

Hope someone can help
----
Hi,

You can put the commands to run your program in /etc/init.d/boot.local.

http://www.linuxforums.org/forum/suse-linux-help/113611-how-do-i-auto-run-program-everytime-suse-boots.html

MediaWiki: Adding Ads

Adding Ads

One commonly requested feature for MediaWiki is how to add ads (from Google etc.) to MediaWiki pages. Before you can do this you will first need to set up an account with whoever you want to provide the adds. They will then give you the HTML code needed to display the adds. The next step is to add the code you got to a file named monobook.php which is kept in the skins directory. Simply look through the file until you find