LEARN MYSQL(CLASS 6)

ASIT KUMAR MAHAPATRA
BAY AREA INFOTECH

Basic MySQL Commands

This document provides an introduction to the MySQL-client binary. Feel free to forward questions or clarification requests to portal@aero.und.edu.
· Login to a machine with mysql client capabilities
· ssh to shell
· login to a linux lab machine
The Short Answer
We recommend that all new MySQL users read the remainder of this document. For the impatient we will make this command available.
mysql -h mysql.aero.und.edu -p -u $USER
Linux Environment
Let's begin by exploring our local Linux environment as if we were lost in a dark room, and have to find the light. After all, your terminal session is white on black… so this aids in the effect :)
$ which mysql
/usr/bin/mysql
$ rpm -qa grep -i mysql
MySQL-python-0.9.1-6
MySQL-shared-standard-4.1.15-0.rhel3
libdbi-dbd-mysql-0.6.5-5
php-mysql-4.3.2-25.ent
perl-DBD-MySQL-2.1021-3
MySQL-client-4.1.15-0.glibc23
MySQL-devel-4.1.14-0.glibc23
$ rpm -qf `which mysql`
MySQL-client-4.1.15-0.glibc23
Let's find out what kind of system we're logged in to.
$ cat /etc/redhat-release
Red Hat Enterprise Linux AS release 3 (Taroon Update 5)
If we were inclined, we could download the appropriate package here http://dev.mysql.com/downloads/mysql/4.1.html.
MySQL Server -- Where art thou?
First let's investigate the local system shell.aero.und.edu.
$ grep mysql /etc/services
mysql 3306/tcp # MySQL
mysql 3306/udp # MySQL
When installed, the MySQL server runs on port 3306.
$ telnet localhost 3306
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused
No, MySQL is not running locally. This is confirmed with the following command.
$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
The MySQL server is running somewhere other than shell. Luckily we remember that our friendly System Administrators gave us the necessary information in the reply to our MySQL Account Request.
The Connection
We're left wondering, “How do we connect to the MySQL server at mysql.aero.und.edu?”
After investigating the manual page by running “man mysql” we see the following.
mysql(1) MySQL database mysql(1)

NAME
mysql - text-based client for mysqld, a SQL-based relational database
daemon

USAGE
mysql [OPTIONS] [Database]

SYNOPSIS
mysql [-B--batch] [-#--debug= logfile] [-T--debug-info] [-e--exec=
command] [-f--force] [-?--help] [-h--host= hostname]
[-n--unbuffered] [-p[pwd]] [--password=[pwd]] [-P--port= pnum]
[-q--quick] [-r--raw] [-s--silent] [-S--socket= snum] [-u--user=
uname] [-v--verbose] [-V--version] [-w--wait]

DESCRIPTION
The mysql program provides a curses-based interface to the SQL-based
database server daemon, mysqld(1). Full fuller documentation, refer to
the HTML documents installed with the package.
Here are the interesting “OPTIONS”
· -h (hostname)
· -p (password)
· -u (username)
mysql -h mysql.aero.und.edu -p -u caylan
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1266841 to server version: 4.1.12-standard

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

mysql>
Excellent!
Basic Queries
Show Databases
mysql> show databases;
+----------+
Database
+----------+
caylan
test
+----------+
2 rows in set (0.00 sec)
Use DB & Show Tables
We must “use” or “select” a database before we can go any further.
mysql> use caylan
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
Tables_in_caylan
+------------------+
notes
+------------------+
1 row in set (0.00 sec)

mysql> select * from notes;
+----+------------+-----------+
id date content
+----+------------+-----------+
1 2005-11-28 Test Note
4 2005-11-28 My Note
+----+------------+-----------+
2 rows in set (0.01 sec)
Simple Insert
mysql> insert into notes (id, date, content) values (null, now(), "Hello World");
Query OK, 1 row affected (0.00 sec)

mysql> select * from notes;
+----+------------+-------------+
id date content
+----+------------+-------------+
1 2005-11-28 Test Note
4 2005-11-28 My Note
7 2005-12-05 Hello World
+----+------------+-------------+
3 rows in set (0.00 sec)
Loading Schema from File
Sometimes a database schema is preserved in a file, and the user copy/pastes into their terminal with the mysql client open. Although this is fine in practice, it soon gets tedious. Enter the world of the command-line.
$ mysql -h mysql.aero.und.edu -p -u $USER caylan < my_schema.sql
Enter password:
drop table if exists notes;

CREATE TABLE `notes` (
`id` int(11) NOT NULL auto_increment,
`date` date default NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into notes (id, date, content) values (null, now(), "Test Note");
insert into notes (id, date, content) values (null, now(), "Test Sioux");
insert into notes (id, date, content) values (null, now(), "Sioux Note");
insert into notes (id, date, content) values (null, now(), "My Note");
Additional Reading

LEARN MYSQL(CLASS 5)

MySQL Basics -- A Helpful MySQL Tutorial
Introduction
Welcome. This MySQL tutorial is a guide for new MySQL users, providing a reference of very basic, step by step, instructions on how to get started. This MySQL help page also gives more advanced users assistance in upgrading and running multiple versions of MySQL on a computer at the same time.
MySQL is an SQL based relational database management system (DBMS) that runs under a broad array of operating systems. MySQL is frequently used by PHP and Perl scripts. The SQL commands discussed in this tutorial apply to MySQL operating under all operating systems. Only the installation instructions are Windows specific. The focus is on Windows XP Professional and Windows 2000 Professional machines.
We'll be covering the installation of MySQL versions 5.0.18, 4.1.16 and 4.0.21 as well as how to configure everything so they'll all be able to run on your system at the same time. Plus, the data directories will be moved to another location in order to make backups easier. If you only want to install one version of MySQL, ignore the instructions relating to the other versions.
If your operating system or versions of MySQL are different, the instructions should still work, but keep an eye out for slight differences, like which working directory you'll need to be in.
Usage: Commands to type in and items that require appropriate substitutions are displayed like this. Resulting screen text looks like this. File names appear in this fashion.
Contents
· Windows 2000 and Windows XP Installation
· Creating Command Prompt Shortcuts
· Operating System Privileges for the MySQL Service
· File System Permissions for the MySQL Directories
· Quick Tips for Other Windows Operating Systems
· Start the Client
· Tighten Security
· Creating a Simple Database and Displaying its Structure
· Putting Data into a Table
· Adding Fields
· Multi-line Command Entry
· Insert Some More Records into the Table
· Updating Existing Records
· Deleting Records
· Time to Call it Quits
· In Closing
Windows 2000, XP and NT 4.0 Installation
Log in to your computer using an account having Administrator privileges.
If you're not upgrading, just skip down to the next section.
If you are upgrading, there are MANY changes that you HAVE TO be aware of. Carefully read the pages in the MySQL manual that apply to you. If you intend to jump over a release series in your upgrade (e.g.: going straight from 4.0 to 5.0) you are strongly advised NOT to do so. You should do the upgrade process one release series at a time (e.g.: go from 4.0 to 4.1, then to 5.0).
· Upgrading from MySQL 5.0
· Upgrading from MySQL 4.1 to 5.0 (also includes several changes made between 5.0 versions)
· Upgrading from MySQL 4.0 to 4.1
Backup Old Data
Here's a quick rundown of how to get your data from old to new systems.
Open up a Command Prompt window. If you don't know how to do that, go to Start Run, then type in cmd and hit ENTER.
Switch to the drive which has your present mysql\bin directory. Eg: L:\> c:
Move into the MySQL bin directory: cd mysql\bin
Dump your data: mysqldump -u root -p --extended-insert=false -c -A -r c:\secure\myold.sql
Stop the MySQL service: net stop mysql
The ISAM table format has been deprecated in 4.0 and removed from 5.0. If have some tables in that format, you'll need to fix that now. There are several ways to do this. Here are three possibilities:
sed: sed 's/^) TYPE=ISAM/) TYPE=MyISAM/' c:\secure\myold.sql > c:\secure\mynew.sql
Perl: perl -wpe 's;^\) TYPE=ISAM;\) TYPE=MyISAM;i' <> c:\secure\mynew.sql
Your favorite text editor: open c:\secure\myold.sql then do a search and replace on TYPE=ISAM to TYPE=MyISAM. Save the file as mynew.sql.
Remove the existing service by typing in mysqld --remove
Jump up two directory levels: cd ..\..
Rename your old mysql directory as a backup: ren mysql myold
Get and Install a Recent Version
Download the "Windows (x86)" file for the versions you want: 5.0, 4.1 and/or 4.0. For now, do NOT pick the "Windows Essentials (x86)" or "Without installer (unzip in C:\)" downloads.
Find the downloaded file, unzip it and start the setup program:
· Via Cygwin:
cd /path/to/file
unzip mysql-version.number-win.zip -d temp
chmod 700 temp/*
explorer temp
Double click on Setup.exe.
· Via Windows XP:
Open up Windows Explorer. If you don't know how to do that, go to Start Run, type in explorer and hit ENTER.
Find the downloaded file.
Double click on it.
Windows XP has a built in zip file reader.
In the zip viewer, double click on Setup.exe .
· Via WinZip or PKZIP for Windows, or other such utilities:
Open up Windows Explorer. If you don't know how to do that, go to Start Run, type in explorer and hit ENTER.
Find the downloaded file.
Double click on it.
Extract the files.
Flip back to Windows Explorer and double click on Setup.exe .
In the Installation Program:
· MySQL 5.0.x and 4.1.x:
In the "Please select a setup type" step, click the "Custom" radio button, then "Next."
The next step gives you choices of which components to install and where to install it.
Click the "Change" button at the bottom right of the dialog box and put in c:\program files\mysql41\ then click "OK"
If you're upgrading, make sure to click on the plus sign next to the "Developer Components" item, then click on "Scripts, Examples" and set it to "This feature will be installed on the local hard drive."
Adjust the other entries per your desires. Make sure the "MySQL Server" and the "Client Programs" are selected. Once you have everything lined up as you want, click "Next."
Click "Install."
When you get to the "MySQL.com Sign-Up" step, let's cut to the chase... click the "Skip Sign-Up" radio button and go to "Next."
UN-check "Configure the MySQL Server" and click "Finish."
· MySQL 4.0.x:
For the first two steps, click "Next."
In the "Choose Destination Location" step, click the "Browse" button and type in c:\program files\mysql40\ then hit the ENTER key and click the "Next" button.
On the "Setup Type" screen, click the "Custom" radio button then "Next."
The "Select Components" step presents several options:
The MySQL Servers
Required. The executables that run the system.
The MySQL clients and Maintenance Tools
Optional, though highly recommended. Command line programs used for editing and mataining databases.
The MySQL Documentation with defferent formats.
Optional. Manuals on how to use MySQL.
Examples, Libraries, Includes and Script files.
Optional. Benchmarking utilities, system tests, Libmysql for embedding functionality into applications, C header files and other utilities. If you are upgrading, make sure this option is selected.
The Grant Tables and Core Files
Required. The components that regulate access to the database.
Once you have made your choices, click the "Next" button.
On the next screen, click "Finish."
Taking a moment to make Command Prompt shortcuts now will save loads of time later:
Open Windows Explorer (Start Run, explorer, ENTER).
Navigate to C:\Documents and Settings\All Users\Start Menu directory.
Create a new directory (File New Folder). That makes a new folder called, "New Folder" (imagine that!) which is now ready for you to type in a new name for. So, type in Databases and hit ENTER.
Now hit ENTER again to get into that folder.
Open another Windows Explorer window.
In the Address Bar, type in %UserProfile% and hit the ENTER key. If the Address Bar isn't visible, in Windows Explorer's menu: View Toolbars Address Bar.
Navigate to the Start Menu\Programs\Accessories directory.
Select the Command Prompt file.
Hit CTRL-C
Now come back to the "Databases" folder window and hit CTRL-V. That should have pasted a copy of the Command Prompt shortcut in your new directory.
Select the Command Prompt file.
What you do here depends on whether you're installing 5.0, 4.1 and/or 4.0.
· MySQL 5.0.x: Use the follow these steps verbatim.
· MySQL 4.1.x and/or 4.0.x: Use these steps, but change 50 to 41 or 40.
· Multiple versions: Do the steps for 50, make a copy of MySQL 50 Prompt and then follow steps I and II, but change 50 to 41 and/or 40.
Hit the F2 key and rename the shortcut MySQL 50 Prompt then hit ENTER.
Open it's properties via File Properties and go to the "Shortcut" tab, then the "Start In" box and type the following in, including the quotes: "c:\program files\mysql50\bin"
Go to the "Options" tab and check the "QuickEdit mode" and "Insert mode" boxes.
Go to the "Layout" tab and go to the "Height" box in the "Screen buffer size" section and put in a larger number, like 2000.
Click OK
Now, you'll be able to quickly get to either of the Command Prompts by going to Start Databases MySQL 50 Prompt.
Plus, you'll be able to paste commands into the the Command Prompt! To do that, copy the command from this tutorial (or wherever) then go to the Command Prompt window and hit the following keys:
ALT-SPACE (to open the menu in the upper left corner of the window)
E (the access key for Edit)
P (the access key for Paste)
For the rest of this lesson, we'll be calling this these the "MySQL 50 Shortcut", "MySQL 41 Shortcut" and "MySQL 40 Shortcut."
Time to create the data directories. MySQL's data files should be stored on a drive, or a portion thereof, that contains your other data files. This will simplify finding and backup up your information. The D: drive on my machine is dedicated solely to storing actively used data. That's the location we'll use in this tutorial.
Open up a Command Prompt window. If you don't know how to do that, go to Start Run, then type in cmd and hit ENTER.
Switch to the drive where the data will be placed: C:\> d:
Make the needed directories:
· MySQL 5.0.x:
mkdir mysql50
mkdir mysql50\data
mkdir mysql50\data\mysql
mkdir mysql50\ibdata
mkdir mysql50\iblogs
· MySQL 4.1.x:
mkdir mysql41
mkdir mysql41\data
mkdir mysql41\data\mysql
mkdir mysql41\ibdata
mkdir mysql41\iblogs
· MySQL 4.0.x:
mkdir mysql40
mkdir mysql40\data
mkdir mysql40\data\mysql
mkdir mysql40\ibdata
mkdir mysql40\iblogs
Copy the default mysql database/privileges to the new location:
· MySQL 5.0.x:
copy "c:\program files\mysql50\data\mysql" mysql50\data\mysql
· MySQL 4.1.x:
copy "c:\program files\mysql41\data\mysql" mysql41\data\mysql
· MySQL 4.0.x:
copy "c:\program files\mysql40\data\mysql" mysql40\data\mysql
I suggest putting empty my.cnf files in place in order to keep your MySQL servers from being subverted. While this step isn't strictly necessary, it's better to be safe than sorry. We'll do this via the Command Prompt window we were using above.
· Just MySQL 5.0.x:
notepad mysql50\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql50\data\my.cnf "c:\program files\mysql50\data"
· Just MySQL 4.1.x:
notepad mysql41\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql41\data\my.cnf "c:\program files\mysql41\data"
· Just MySQL 4.0.x:
notepad mysql40\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql40\data\my.cnf "c:\program files\mysql40\data"
· All of the above
notepad mysql50\data\my.cnf
In notepad, type in [mysqld] , save the file and exit.
copy mysql50\data\my.cnf "c:\program files\mysql50\data"

copy mysql50\data\my.cnf mysql41\data
copy mysql50\data\my.cnf "c:\program files\mysql41\data"

copy mysql50\data\my.cnf mysql40\data
copy mysql50\data\my.cnf "c:\program files\mysql40\data"
The my.cnf configuration files need to be put in place. First, we'll put blank files in the default locations in order to prevent the server from potential subversion. Do this via the Command Prompt window we were using above.
notepad c:\my.cnf
In notepad, paste in the following, then adjust the the port numbers and drive letters and paths as needed.
NOTE: This sample file puts 4.1.x on the default port (3306), 5.0.x on 3350 and 4.0.x on 3340. These port numbers will be used throughout the rest of the tutorial. So, if you change anything in your my.cnf file, you will need to adjust all of our commands accordingly. On the command line, MySQL uses the -P flag to tell the client which port to connect to, for example -P 3340.
# This config file contains settings for MySQL versions
# 5.0.x, 4.1.x and 4.0.x. Be aware that only one server
# instance can be on a given port. This sample puts
# version 4.1.x on MySQL's default port, 3306. Adjust
# the port numbers to suit your situation.

[mysql]
# Displaying the version number in the prompt
# helps when running multiple instances.
prompt = mysql\\_\v>\\_

[mysqld-5.0]
port = 3350
basedir = C:/Program Files/mysql50/
datadir = D:/mysql50/data/
innodb_data_home_dir = D:/mysql50/ibdata/
innodb_log_group_home_dir = D:/mysql50/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0

[mysqld-4.1]
port = 3306
basedir = C:/Program Files/mysql41/
datadir = D:/mysql41/data/
innodb_data_home_dir = D:/mysql41/ibdata/
innodb_log_group_home_dir = D:/mysql41/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0

# If you're running versions < 4.0.14, change the next line to [mysqld]
[mysqld-4.0]
port = 3340
basedir = C:/Program Files/mysql40/
datadir = D:/mysql40/data/
innodb_data_home_dir = D:/mysql40/ibdata/
innodb_log_group_home_dir = D:/mysql40/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0
Save the file and exit.
Be aware that on Windows Server 2003 machines, Windows Explorer hides the .cnf file extension even if Windows Explorer's "hide file extensions" option is turned off. JOY!
Next, we need to "install" the appropriate MySQL services.
· MySQL 5.0: Open the MySQL 50 Shortcut, pick one of the following and type its command into the Command Prompt:
NT/2000/XP. Transactions. Named pipes. (recommended) mysqld-max-nt --install mysql50
NT/2000/XP. Transactions. mysqld-max --install mysql50
NT/2000/XP. Named pipes. mysqld-nt --install mysql50
Transactions. mysqld --install mysql50
Support for debugging MySQL crashes. mysqld-debug --install mysql50
· MySQL 4.1: Open the MySQL 41 Shortcut, pick one of the following and type its command into the Command Prompt:
NT/2000/XP. Transactions. Named pipes. (recommended) mysqld-max-nt --install mysql41
NT/2000/XP. Transactions. mysqld-max --install mysql41
NT/2000/XP. Named pipes. mysqld-nt --install mysql41
Transactions. mysqld --install mysql41
Support for debugging MySQL crashes. mysqld-debug --install mysql50
· MySQL 4.0: Open the MySQL 40 Shortcut, pick one of the following and type its command into the Command Prompt:
NT/2000/XP. Transactions. Named pipes. (recommended) mysqld-max-nt --install mysql40
NT/2000/XP. Transactions. mysqld-max --install mysql40
NT/2000/XP. Named pipes. mysqld-nt --install mysql40
Transactions. mysqld --install mysql40
Optimised binary with no support for transactional tables. mysqld-opt --install mysql40
Good security procedures call for running services as a non-privileged user. So, let's create such a user and configure the MySQL services to use it:
net user mysql * /add /passwordreq:yes /passwordchg:no /comment:"runs MySQL services"
You'll be prompted for the password.
Start up the services manager by typing in services.msc and hitting ENTER.
Perform the following steps for each mysql server instance, substituting your version numbers for the underscores (__):
Locate the "mysql__" service in the right hand pane and double click it.
Go to the "Log On" tab
Click the "This account" radio button
Type mysql into the box.
Enter the password into the "Password" and "Confirm password" inputs.
Hit OK
Let's lock down the file permissions on the directories we created by entering the following commands into a Command Prompt: cacls c:\my.cnf /g administrators:f system:r mysql:r
If you may get the following error message: The cacls command can only be run on disk drives that use the NTFS file system. Don't worry about it. Skip all the cacls commands in the rest of our tutorial.
Enter each line below separately because some of them require user confirmation.
· MySQL 5.0.x:
cacls d:\mysql50 /t /g administrators:f system:f mysql:c
cacls d:\mysql50\data\my.cnf /t /g administrators:f system:r
cacls "c:\program files\mysql50" /t /g administrators:f system:f mysql:r users:r
cacls "c:\program files\mysql50\data" /t /g administrators:f
· MySQL 4.1.x:
cacls d:\mysql41 /t /g administrators:f system:f mysql:c
cacls d:\mysql41\data\my.cnf /t /g administrators:f system:r
cacls "c:\program files\mysql41" /t /g administrators:f system:f mysql:r users:r
cacls "c:\program files\mysql41\data" /t /g administrators:f
· MySQL 4.0.x:
cacls d:\mysql40 /t /g administrators:f system:f mysql:c
cacls d:\mysql40\data\my.cnf /t /g administrators:f system:r
cacls "c:\program files\mysql40" /t /g administrators:f system:f mysql:r users:r
cacls "c:\program files\mysql40\data" /t /g administrators:f
Start the services:
· MySQL 5.0.x: net start mysql50
· MySQL 4.1.x: net start mysql41
· MySQL 4.0.x: net start mysql40
If System error 1069 has occurred comes up, your passwords didn't match. You can create a new password by typing net user mysql * into the Command Prompt. Then put that new password into the MySQL services' properties' "Log On" tab.
Getting a System error 5 has occurred indicates problems getting to the directory containing the MySQL executables (for example, insufficient permissions).
If you get an error saying System error 1067 has occurred it means a configuration directive in c:\my.cnf couldn't be processed. That can be due to a typographical error in the name or value of a setting. Similarly, the path referred to by the setting's value could be inaccessible due to insufficient permissions.
If the error says The service is not responding to the control function you're likely running into a bug in MySQL 4.0 that causes problems when trying to run the service as an unpriviliged user. Until that gets fixed, go back to the server logon section above and change the "Log On" to the "System" radio button.

To help narrow down what's going on, check out the Event Viewer: Start Run, then type in eventvwr.msc and hit ENTER. Click the "Application" item in the left pane. Now examine the top of the right hand pane for recent MySQL errors. Double click the first item and look at the message in the "Description" box.

Either of the following mean there's a boo-boo in the path value assigned to the datadir setting in c:\my.cnf: Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist or Warning: Can't create test file In this situation, make sure the actual path is a case-sensitive match to the path in the configuration file and that the directories in question have the permissions they need to be accessed by the MySQL daemon. Then try the net start command again.
Along the same lines, error logs showing Can't find messagefile 'C:\Program Files\mysql\share\english\errmsg.sys' points to a mismatch between the basedir value and the actual path to the MySQL executables.
An unknown variable notice is clear enough, no?
If there are no MySQL related error messages in the Event Log's Application section, you have a major typo in the c:\my.cnf. Perhaps one of the [mysql...] section headings are wrong.
If you're upgrading and followed our instructions at the top of this tutorial, now it's time to import your old data. These steps assume upgrading from 4.1 to 5.0, but they're the same, except for the port number, for upgrading from 4.0 to 4.1.
C:\Program Files\mysql50\bin> mysql -u root -P 3350 < c:\secure\myold.sql
C:\Program Files\mysql50\bin> mysql -u root -P 3350 -f mysql < ..\scripts\mysql_fix_privilege_tables.sql
Log into the server: C:\Program Files\mysql50\bin> mysql -u root P 3350 mysql
The upgrade script gives some privilges that you probably don't want most users having. Let's change them back (adjusting the "otherimportantusers..." below as needed for your system): mysql 5.0.18-nt-max> UPDATE user SET Create_tmp_table_priv='N', Lock_tables_priv='N' WHERE user NOT IN ('root', 'otherimportantusers...');
Effectuate your new privileges: mysql 5.0.18-nt-max> flush privileges;
Stay logged in here in here for now in case something went wrong.
Jump down to the Start the Client section...
Tighten MySQL's Security
MySQL has good security controls, but the default installation is wide open. So, if you're doing a new installation, let's close things up before we go any further.
In the following commands, don't forget that if you changed the ports in the my.cnf file, you'll have to adjust the port numbers here.
· MySQL 5.0.x:
Activate the "MySQL 50 Shortcut" we created earlier then type in: mysql -u root -P 3350 mysql
· MySQL 4.1.x:
Activate the "MySQL 41 Shortcut" we created earlier then type in: mysql -u root mysql
· MySQL 4.0.x:
Activate the "MySQL 40 Shortcut" we created earlier then type in: mysql -u root -P 3340 mysql
(If you get the following error message: ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) That means the servers didn't stay started. The most likely reason is the my.cnf file has errors in it. Go back to the Start the services step and carefully read the section about checking the Event Viewer logs.)
Once you are logged in, copy the following queries to Notepad and change NewPw to something unique. Now copy that and paste it into the command prompt window(s) you just opened.
delete from user where Host <> 'localhost' and User <> 'root';
delete from db;
update user set Password=password('NewPw') where User='root';
flush privileges;
exit
Tada!
Quick Tips for Other Windows Operating Systems
If you're using Windows 95, 98 or ME do not run mysqld --install. These operating systems don't have the ability to host a "service." So, you need to run MySQL as a standalone application by executing the command mysqld --standalone.
I haven't run MySQL on these systems myself, so, for more information, check out the Starting MySQL on Windows 95, 98 or Me and Running MySQL on Windows sections of the manual or explore the MySQL Win32 Mailing List Archive.
Start the Client
We'll be using MySQL 4.1.x here. Adjust the shortcut and port as necessary.
Activate the "MySQL 41 Shortcut" we created earlier and type in mysql -u root -p then enter your password when prompted. You will then see the following output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
If your root account doesn't have a password on it, the above command won't work for you. You really need to get on top of security. Log in without the password:
mysql -u root mysql
Then set a password (changing "NewPw", of course):
update user set Password=password('NewPw') where User='root';
flush privileges;
Creating a Simple Database and Displaying its Structure
Instruct MySQL to setup a new database
mysql 4.1.16-nt-max> create database database01;
Database "database01" created.
All that really does is create a new subdirectory in your D:\mysql40\data directory.
Open the database
mysql 4.1.16-nt-max> use database01
Database changed

Create a table
mysql 4.1.16-nt-max> create table table01 (field01 integer, field02 char(10));
Query OK, 0 rows affected (0.00 sec)
Enclose entire list of field names between one pair of parentheses. Commas are used between each field. A space may be used after the comma between fields. A comma is not used after last field. This, and all SQL statements, are concluded by a semicolon ";".
List the tables
mysql 4.1.16-nt-max> show tables;
+----------------------+
Tables in database01
+----------------------+
table01
table02
+----------------------+

List the fields in a table
mysql 4.1.16-nt-max> show columns from table01;
+---------+----------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+----------+------+-----+---------+-------+
field01 int(11) YES
field02 char(10) YES
+---------+----------+------+-----+---------+-------+



Congratulations! Pretty straightforward, eh?
Putting Data into a Table
Insert a record
mysql 4.1.16-nt-max> insert into table01 (field01, field02) values (1, 'first');
Query OK, 1 row affected (0.00 sec)
Enclose entire list of field names between one pair of parentheses. Enclose the values to be inserted between another pair of parentheses. Commas are used between each field and between each value. A space may be used after the comma between fields.
List all the records in a table
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+
field01 field02
+---------+---------+
1 first
+---------+---------+


Excellent!
Adding Fields
...one field at a time
mysql 4.1.16-nt-max> alter table table01 add column field03 char(20);
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

...more than one at a time
mysql 4.1.16-nt-max> alter table table01 add column field04 date, add column field05 time;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
The "add column" must be restated for each column. Commas are used between each add column statement. A space may be used after these commas.
The MySQL Manual fully explains each possible column data type.
Did it work?
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+---------+---------+---------+
field01 field02 field03 field04 field05
+---------+---------+---------+---------+---------+
1 first NULL NULL NULL
+---------+---------+---------+---------+---------+


Now we're getting somewhere!
Multi-line Command Entry
The MySQL command line interface allows you to put a statement on one line or spread it across multiple lines. There's no difference in syntax between the two. Using multiple lines allows you to break down the SQL statement into steps you may more easily comprehend.
In multiple line mode, the interpreter appends each line to the prior lines. This continues until you enter a semicolon ";" to close out the SQL statement. Once the semicolon is typed in and you hit enter, the statement is executed.
Here's an example of the same exact SQL statement entered both ways:
Single Line Entry
mysql 4.1.16-nt-max> create table table33 (field01 integer,field02 char(30));
Multiple Line Entry
mysql 4.1.16-nt-max> create table table33 -> (field01 -> integer, -> field02 -> char(30));
Don't break up words:
Valid
Invalid
mysql 4.1.16-nt-max> create table table33 -> (field01 -> integer, -> field02 -> char(30));
mysql 4.1.16-nt-max> create table table33 -> (field01 inte -> ger, -> field02 -> char(30));
When inserting or updating records, do not spread a field's string across multiple lines, otherwise the line breaks are stored in the record:
Standard Operation
mysql 4.1.16-nt-max> insert into table33 (field02) -> values -> ('Who thought of foo?');
Line Break Stored in Record
mysql 4.1.16-nt-max> insert into table33 (field02) -> values -> ('Pooh thought -> of foo.');
Results
mysql 4.1.16-nt-max> select * from table33;
+---------+---------------------+
field01 field02
+---------+---------------------+
NULL Who thought of foo?
NULL Pooh thought
of foo.
+---------+---------------------+

Insert Some More Records into the Table
Add this record
mysql 4.1.16-nt-max> insert into table01 (field01,field02,field03,field04,field05) values -> (2, 'second', 'another', '1999-10-23', '10:30:00');
Query OK, 1 row affected (0.00 sec)
Quotes must go around text values.
Standard date format is "yyyy-mm-dd". Standard time format is "hh:mm:ss". Quotes are required around the standard date and time formats, noted above. Dates may also be entered as "yyyymmdd" and times as "hhmmss". If entered in this format, values don't need to be quoted.
Numeric values do not need to be quoted. This holds true regardless of the data type a column is formatted to contain (e.g. text, date, time, integer).
MySQL has a useful command buffer. The buffer stores the SQL statements you've entered thus far. Using it keeps you from having to retype the same commands over and over. Let's use this next step as an example.
Add another record using the command buffer (and optional date and time formats)
Hit the up arrow key twice.
Hit the ENTER key.
Type in the new values between a pair parentheses and stick a closing semicolon on the end. (3, 'a third', 'more foo for you', 19991024, 103004);
Hit the ENTER key.
Voilà!
Is it in there?
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first NULL NULL NULL
2 second another 1999-10-23 10:30:00
3 a third more foo for you 1999-10-24 10:30:01
+---------+-----------+------------------+------------+----------+


It's in there!
Now, we're almost done...
Updating Existing Records
Modify one field at a time
Again, be careful with syntax. Quote marks need to go around text but not around numbers.
mysql 4.1.16-nt-max> update table01 set field03='new info' where field01=1;
Query OK, 1 row affected (0.00 sec)

Change multiple fields at once
Remember to put commas between each field you're updating.
mysql 4.1.16-nt-max> update table01 set field04=19991022, field05=062218 where field01=1;
Query OK, 1 row affected (0.00 sec)

So, what's up with our data?
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first new info 1999-10-22 06:22:18
2 second another 1999-10-23 10:30:00
3 third one more foo for you 1999-10-24 10:30:01
+---------+-----------+------------------+------------+----------+

Update multiple records in one stroke
mysql 4.1.16-nt-max> update table01 set field05=152901 where field04>19990101;
Query OK, 3 rows affected (0.00 sec)

Survey says...
mysql 4.1.16-nt-max> select * from table01;
+---------+-----------+------------------+------------+----------+
field01 field02 field03 field04 field05
+---------+-----------+------------------+------------+----------+
1 first new info 1999-10-22 15:29:01
2 second another 1999-10-23 15:29:01
3 third one more foo for you 1999-10-24 15:29:01
+---------+-----------+------------------+------------+----------+

Wee haw!
Deleting Records
The delete command
mysql 4.1.16-nt-max> delete from table01 where field01=3;
Query OK, 1 row affected (0.01 sec)
mysql 4.1.16-nt-max> select * from table01;
+---------+---------+----------+------------+----------+
field01 field02 field03 field04 field05
+---------+---------+----------+------------+----------+
1 first new info 1999-10-22 15:29:01
2 second another 1999-10-23 15:29:01
+---------+---------+----------+------------+----------+


Time to Call it Quits
mysql 4.1.16-nt-max> quit
Bye


In Closing
Now you know some rudimentary commands for running a database in MySQL. Since MySQL is operated by executing SQL calls, you have a broad array of very powerful tools at your disposal. For instance, you're able to display data from several tables at once by joining related fields.
Similarly, SQL permits complex displays, updates or deletions of multiple records which fit specific criteria. So, your next step toward mastery is learning all about SQL.
James Hoffman has put a tutorial page up on the web entitled Introduction to Structured Query Language.
Another thing to note is MySQL offers good security features you'll need to use when operating on networks.
To learn more about MySQL and how to use it, the manual should be your first stop. Also, Paul DuBois' book, MySQL, comes highly recommended. In addition, the archives of the main list and the Win32 list are tremendous resources. The NYPHP user group has started a MySQL interest group you may find helpful.
If you're curious about database portability, you may find the Building Truly Portable Database Applications in PHP presentation interesting.
If you'll be developing hypertext interfaces to your databases using PHP, check out our SQL Solution™. It's a powerful, user friendly, platform independent API that will make your job a snap! Similarly, you might want to examine PEAR DB, a popular open source database abstraction layer (of which I'm the lead developer).
Also, if your scripts accept user input, the Form Solution™ is a handy tool for cleaning user input, generating HTML / XHTML compliant date/time form elements, validating and formatting date/time inputs and holding all variables submitted by a form. The result is improved security and data quality.

LEARN MYSQL(CLASS 4)

Basic MySQL Commands
· ASIT KUMAR MAHAPATRA.
· SOFTWARE ENGINEER TRAINEE
· BAY AREA INFOTECH
· CREATE TABLE syntax
· Silent column specification changes
· DROP TABLE syntax
· DELETE syntax
· SELECT syntax
· JOIN syntax
· INSERT syntax
· REPLACE syntax
· UPDATE syntax

CREATE TABLE syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
col_name type [NOT NULL NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)

type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...)

index_col_name:
col_name [(length)]

reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT CASCADE SET NULL NO ACTION SET DEFAULT

table_options:
TYPE = {ISAM MYISAM HEAP}
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 1}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 1}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 1}
or ROW_FORMAT= { default dynamic static compressed }

select_statement:
[IGNORE REPLACE] SELECT ... (Some legal select statement)
CREATE TABLE creates a table with the given name in the current database. Rules for allowable table names are given in Database, table, index, column and alias names. An error occurs if there is no current database or if the table already exists. The table name can be specified as db_name.tbl_name. This works whether or not there is a current database.
You can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted).
You can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the table structures are identical.
Each table tbl_name is represented by some files in the database directory. In the case of MyISAM-type tables you will get:
File
Purpose
tbl_name.frm
Table definition (form) file
tbl_name.MYD
Data file
tbl_name.MYI
Index file
For more information on the properties of the various column types, see section Column types.
· If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.
· An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused. If you delete all rows in the table, the sequence starts over. Note: There can be only one AUTO_INCREMENT column per table, and it must be indexed. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query:
SELECT * FROM tbl_name WHERE auto_col IS NULL
· NULL values are handled differently for TIMESTAMP columns than for other column types. You cannot store a literal NULL in a TIMESTAMP column; setting the column to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. On the other hand, to make it easier for MySQL clients to use TIMESTAMP columns, the server reports that such columns may be assigned NULL values (which is true), even though TIMESTAMP never actually will contain a NULL value. You can see this when you use DESCRIBE tbl_name to get a description of your table. Note that setting a TIMESTAMP column to 0 is not the same as setting it to NULL, because 0 is a valid TIMESTAMP value.
· If no DEFAULT value is specified for a column, MySQL automatically assigns one. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type:
· For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence.
· For date and time types other than TIMESTAMP, the default is the appropriate ``zero'' value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See Date and time types.
· For string types other than ENUM, the default is the empty string. For ENUM, the default is the first enumeration value.
· KEY is a synonym for INDEX.
· In MySQL, a UNIQUE key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row.
· A PRIMARY KEY is an unique KEY with the extra constraint that all key columns must be defined as NOT NULL. In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.
· A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY KEY(index_col_name, ...) syntax.
· If you don't assign a name to an index, the index will be assigned the same name as the first index_col_name, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See SHOW syntax.
· Only the MyISAM table type supports indexes on columns that can have NULL values. In other cases you must declare such columns NOT NULL or an error results.
· With col_name(length) syntax, you can specify an index which uses only a part of a CHAR or VARCHAR column. This can make the index file much smaller. See Column indexes.
· Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index:
CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
· When you use ORDER BY or GROUP BY with a TEXT or BLOB column, only the first max_sort_length bytes are used. See The BLOB and TEXT types.
· The FOREIGN KEY, CHECK and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references.
· Each NULL column takes one bit extra, rounded up to the nearest byte.
· The maximum record length in bytes can be calculated as follows:
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + 7)/8
+ (number of variable-length columns)
· The different table types are:
ISAM
The original table handler
MyISAM
The new binary portable table handler
HEAP
The data for this table is only stored in memory
The other table options are used to optimize the behavior of the table. In most cases, you don't have to specify any of them. The options work for all table types, if not otherwise indicated.
AUTO_INCREMENT
The next auto_increment value you want to set for your table (MyISAM)
AVG_ROW_LENGTH
An approximation of the average row length for your table. You only need to set this for tables with variable size records.
CHECKSUM
Set this to 1 if you want MySQL to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM)
COMMENT
A 60 character comment for your table
MAX_ROWS
Max number of rows you plan to store in the table
MIN_ROWS
Minimum number of rows you plan to store in the table
PACK_KEYS
Set this to 1 if you want to have smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM).
PASSWORD
Encrypt the .frm file with a password. This option doesn't do anything in the standard MySQL version.
DELAY_KEY_WRITE
Set this to 1 if want to delay key table updates until the table is closed (MyISAM).
ROW_FORMAT
Defines how the rows should be stored (for the future).
When you use a MyISAM table, MySQL uses the product of max_rows * avg_row_length to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables). If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL will use prefix compression. This means that you will only get a big benefit of this if you have many numbers that are the same. Prefix compression means that every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key (note that the pointer to the row is stored in high-byte-first-order directly after the key, to improve compression. This means that if you have many equal keys on two rows in a row, all following 'same' keys will usually only take 2 bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take 'storage_size_for_key' + pointer_size (usually 4). On the other hand, if all keys are totally different, you will lose 1 byte per key, if the key isn't a key that can have NULL values (In this case the packed key length will be stored in the same byte that is used to mark if a key is NULL).
· If you specify a SELECT after the CREATE STATEMENT, MySQL will create new fields for all elements in the SELECT. For example:
CREATE TABLE test (a int not null auto_increment,
primary key (a), key(b))
TYPE=HEAP SELECT b,c from test2;
This will create a HEAP table with 3 columns. Note that the table will automatically be deleted if any errors occur while copying data into the table.

Silent column specification changes
In some cases, MySQL silently changes a column specification from that given in a CREATE TABLE statement. (This may also occur with ALTER TABLE.)
· VARCHAR columns with a length less than four are changed to CHAR.
· If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columnss. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster.
· TIMESTAMP display sizes must be even and in the range from 2 to 14. If you specify a display size of 0 or greater than 14, the size is coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.
· You cannot store a literal NULL in a TIMESTAMP column; setting it to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. DESCRIBE tbl_name always reports that a TIMESTAMP column may be assigned NULL values.
· MySQL maps certain column types used by other SQL database vendors to MySQL types. See Using column types from other database engines.
If you want to see whether or not MySQL used a column type other than the one you specified, issue a DESCRIBE tbl_name statement after creating or altering your table.
DROP TABLE syntax
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
DROP TABLE removes one or more tables. All table data and the table definition are removed, so be careful with this command!
You can use the keywords IF EXISTS to prevent an error from occurring for tables that don't exist.
DELETE syntax
DELETE [LOW_PRIORITY] FROM tbl_name
[WHERE where_definition] [LIMIT rows]
DELETE deletes rows from tbl_name that satisfy the condition given by where_definition, and returns the number of records deleted.
If you issue a DELETE with no WHERE clause, all rows are deleted. MySQL does this by recreating the table as an empty table, which is much faster than deleting each row. In this case, DELETE returns zero as the number of affected records. (MySQL can't return the number of rows that were actually deleted, since the recreate is done without opening the data files. As long as the table definition file `tbl_name.frm' is valid, the table can be recreated this way, even if the data or index files have become corrupted.).
If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form:
DELETE FROM tbl_name WHERE 1>0;
Note that this is MUCH slower than DELETE FROM tbl_name with no WHERE clause, because it deletes rows one at a time.
If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table.
Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See OPTIMIZE TABLE syntax.
The MySQL-specific LIMIT rows option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a specific DELETE command doesn't take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.
SELECT syntax
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
[DISTINCT DISTINCTROW ALL]
select_expression,...
[INTO {OUTFILE DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer col_name formula} [ASC DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
SELECT is used to retrieve rows selected from one or more tables. select_expression indicates the columns you want to retrieve. SELECT may also be used to retrieve rows computed without reference to any table. For example:
SELECT 1 + 1;
-> 2
All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause.
· A SELECT expression may be given an alias using AS. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses. For example:
mysql> select concat(last_name,', ',first_name) AS full_name
from mytable ORDER BY full_name;
· The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see JOIN syntax.
· You can refer to a column as col_name, tbl_name.col_name or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a SELECT statement unless the reference would be ambiguous. See Database, table, index, column and alias names for examples of ambiguity that require the more explicit column reference forms.
· A table reference may be aliased using tbl_name [AS] alias_name.
select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
select t1.name, t2.salary from employee t1, info t2 where t1.name = t2.name;
· Columns selected for output may be referred to in ORDER BY and GROUP BY clauses using column names, column aliases or column positions. Column positions begin with 1.
select college, region, seed from tournament ORDER BY region, seed;
select college, region AS r, seed AS s from tournament ORDER BY r, s;
select college, region, seed from tournament ORDER BY 2, 3;
To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this may be specified explicitly using the ASC keyword.
· The HAVING clause can refer to any column or alias named in the select_expression. It is applied last, just before items are sent to the client, with no optimization. Don't use HAVING for items that should be in the WHERE clause. For example, do not write this:
mysql> select col_name from tbl_name HAVING col_name > 0;
Write this instead:
mysql> select col_name from tbl_name WHERE col_name > 0;
You can also write queries like this:
mysql> select user,max(salary) from users
group by user HAVING max(salary)>10;
· SQL_SMALL_RESULT, SQL_BIG_RESULT, STRAIGHT_JOIN and HIGH_PRIORITY are MySQL extensions to ANSI SQL92.
· STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in non-optimal order. See EXPLAIN syntax (Get information about a SELECT).
· SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will be small. In this case, MySQL will use fast temporary tables to store the resulting table instead of using sorting. SQL_SMALL_RESULT is a MySQL
· SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will have many rows. In this case, MySQL will directly use disk based temporary tables if needed. MySQL in this case will prefer to do a sort instead doing a temporary table with a key on the GROUP BY elements.
· HIGH_PRIORITY will give the SELECT higher priority than a statement that updates a table. You should only use this for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query will run if the table is locked for read even if there is an update statement that is waiting for the table to be free.
· The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
If one argument is given, it indicates the maximum number of rows to return.
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.
· The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, and cannot already exist (among other things, this prevents database tables and files such as `/etc/passwd' from being destroyed). You must have the file privilege on the server host to use this form of SELECT. SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See LOAD DATA INFILE syntax. In the resulting text file, only the following characters are escaped by the ESCAPED BY character:
· The ESCAPED BY character
· The first character in FIELDS TERMINATED BY
· The first character in LINES TERMINATED BY
Additionally, ASCII 0 is converted to ESCAPED BY followed by 0 (ASCII 48). The reason for the above is that you MUST escape any FIELDS TERMINATED BY, ESCAPED BY or LINES TERMINATED BY characters to reliably be able to read the file back. ASCII 0 is escaped to make it easier to view with some pagers. As the resulting file doesn't have to conform to the SQL syntax, nothing else need be escaped.
If you use INTO DUMPFILE instead of INTO OUTFILE MySQL will only write one row into the file, without any column or line terminations and without any escaping. This is useful if you want to store a blob in a file.
JOIN syntax
MySQL supports the following JOIN syntaxes for use in SELECT statements:
table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
The last LEFT OUTER JOIN syntax shown above exists only for compatibility with ODBC.
· A table reference may be aliased using tbl_name AS alias_name or tbl_name alias_name.
select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
· INNER JOIN and , (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition.
· The ON conditional is any conditional of the form that may be used in a WHERE clause.
· If there is no matching record for the right table in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:
select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;
This example finds all rows in table1 with an id value that is not present in table2 (i.e., all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course.
· The USING (column_list) clause names a list of columns that must exist in both tables. A USING clause such as:
A LEFT JOIN B USING (C1,C2,C3,...)
is defined to be semantically identical to an ON expression like this:
A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
· The NATURAL LEFT JOIN of two tables is defined to be semantically equivalent to a LEFT JOIN with a USING clause that names all columns that exist in both tables.
· STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases where the join optimizer puts the tables in the wrong order.
Some examples:
select * from table1,table2 where table1.id=table2.id;
select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
select * from table1 LEFT JOIN table2 USING (id);
select * from table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;

INSERT syntax
INSERT [LOW_PRIORITY DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or INSERT [LOW_PRIORITY DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
INSERT inserts new rows into an existing table. The INSERT ... VALUES form of the statement inserts rows based on explicitly-specified values. The INSERT ... SELECT form inserts rows selected from another table or tables.
tbl_name is the table into which rows should be inserted. The column name list or the SET clause indicates which columns the statement specifies values for.
· If you specify no column list for INSERT ... VALUES or INSERT ... SELECT, values for all columns must be provided in the VALUES() list or by the SELECT. If you don't know the order of the columns in the table, use DESCRIBE tbl_name to find out.
· Any column not explicitly given a value is set to its default value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in CREATE TABLE syntax.
· An expression may refer to any column that was set earlier in a value list. For example, you can say this:
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
But not this:
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
· If you specify the keyword LOW_PRIORITY, execution of the INSERT is delayed until no other clients are reading from the table. In this case the client has to wait until the insert statement is completed, which may take a long time if the table is in heavy use. This is in contrast to INSERT DELAYED which lets the client continue at once.
· If you specify the keyword IGNORE in an INSERT with many value rows, any rows which duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted. If you do not specify IGNORE, the insert is aborted if there is any row that duplicates an existing key value. You can check with the C API function mysql_info() how many rows were inserted into the table.
· If MySQL was configured using the DONT_USE_DEFAULT_FIELDS option, INSERT statements generate an error unless you explicitly specify values for all columns that require a non-NULL value.
· The following conditions hold for a INSERT INTO ... SELECT statement:
· The query cannot contain an ORDER BY clause.
· The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query, because it's forbidden in ANSI SQL to SELECT from the same table into which you are INSERTing. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using sub-select clauses, the situation could easily be very confusing!)
· AUTO_INCREMENT columns work as usual.
If you use INSERT ... SELECT or a INSERT ... VALUES statement with multiple value lists, you can use the C API function mysql_info() to get information about the query. The format of the information string is shown below:
Records: 100 Duplicates: 0 Warnings: 0
Duplicates indicates the number of rows that couldn't be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:
· Inserting NULL into a column that has been declared NOT NULL. The column is set to its default value.
· Setting a numeric column to a value that lies outside the column's range. The value is clipped to the appropriate endpoint of the range.
· Setting a numeric column to a value such as '10.34 a'. The trailing garbage is stripped and the remaining numeric part is inserted. If the value doesn't make sense as a number at all, the column is set to 0.
· Inserting a string into a CHAR, VARCHAR, TEXT or BLOB column that exceeds the column's maximum length. The value is truncated to the column's maximum length.
· Inserting a value into a date or time column that is illegal for the column type. The column is set to the appropriate ``zero'' value for the type.
The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT statements that take a long time to complete. DELAYED is a MySQL extension to ANSI SQL92.
When you use INSERT DELAYED, the client will get an ok at once and the row will be inserted when the table is not in use by any other thread.
Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.
The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the ``thread'' is the thread that received an INSERT DELAYED command and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.
· When a thread executes a DELAYED statement for a table, a handler thread is created to process all DELAYED statements for the table, if no such handler already exists.
· The thread checks whether or not the handler has acquired a DELAYED lock already; if not, it tells the handler thread to do so. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler will wait for all ALTER TABLE locks or FLUSH TABLES to ensure that the table structure is up to date.
· The thread executes the INSERT statement but instead of writing the row to the table it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.
· The client can't report the number of duplicates or the AUTO_INCREMENT value for the resulting row; it can't obtain them from the server, because the INSERT returns before the insert operation has been completed. If you use the C API, the mysql_info() function doesn't return anything meaningful, for the same reason.
· The update log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the update log is updated when the first row is inserted.
· After every delayed_insert_limit rows are written, the handler checks whether or not any SELECT statements are still pending. If so, it allows these to execute before continuing.
· When the handler has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED commands are received within delayed_insert_timeout seconds, the handler terminates.
· If more than delayed_queue_size rows are pending already in a specific handler queue, the thread waits until there is room in the queue. This is useful to ensure that the mysqld server doesn't use all memory for the delayed memory queue.
· The handler thread will show up in the MySQL process list with delayed_insert in the Command column. It will be killed if you execute a FLUSH TABLES command or kill it with KILL thread_id. However, it will first store all queued rows into the table before exiting. During this time it will not accept any new INSERT commands from another thread. If you execute an INSERT DELAYED command after this, a new handler thread will be created.
· Note that the above means that INSERT DELAYED commands have higher priority than normal INSERT commands if there is an INSERT DELAYED handler already running! Other update commands will have to wait until the INSERT DELAY queue is empty, someone kills the handler thread (with KILL thread_id) or someone executes FLUSH TABLES.
· The following status variables provide information about INSERT DELAYED commands:
Delayed_insert_threads
Number of handler threads
Delayed_writes
Number of rows written with INSERT DELAYED
Not_flushed_delayed_rows
Number of rows waiting to be written
You can view these variables by issuing a SHOW STATUS statement or by executing a mysqladmin extended-status command.
Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!
REPLACE syntax
REPLACE [LOW_PRIORITY DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...
REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. See INSERT syntax.
UPDATE syntax
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...
[WHERE where_definition] [LIMIT #]
UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise all rows are updated.
If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.
If you access a column from tbl_name in an expression, UPDATE uses the current value of the column. For example, the following statement sets the age column to one more than its current value:
UPDATE persondata SET age=age+1;
UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:
UPDATE persondata SET age=age*2, age=age+1;
If you set a column to the value it currently has, MySQL notices this and doesn't update it.
UPDATE returns the number of rows that were actually changed.

LEARN MYSQL CLASS(3)

Some basic / useful commands are as follows :

While NOT yet connected to MySQL :

- connect to MySQL

mysql -uUsername -pPassword

- connect to MySQL , directly to a database

mysql -uUsername -pPassword DbName

- upload a MySQL schema into my Database

mysql -uUsername -pPassword DbName < schema.sql

- dump a DB (copy DB for backup)

mysql -uUsername -pPassword DbName > contents-of-db.sql

While connected to MySQL :

- display all databases

show databases;

- connect to a Database

use DbName;

- view tables of a Database (must be connected to the Database)

show tables;

- view contents of a table (must be connected to the Database)

select * from TableName;

Thoughts related to the above

- If you want to be prompted for your password rather then type
it in the command line directly, just use -p vs -pPassword

- If you are not using MySQL as your local host, use the -h option so :

mysql -uUsername -p -hHostname

- Keep in mind that there are many ways to do everything in
most programs, languages ... MySQL included.

- A useful Alias is as follows. You'd put this in your .bashrc
file which is usually located in your user root directory (~username/)

alias my='mysql -uUsername -p'

that way it cuts down on typing. for example, to connect to a
db you just do :

my DbName

and then enter your password (DON'T store your MySQL
password in .bashrc!!!)

- Using a ' ; ' after a command while in MySQL executes that command

---

This should get you going ... ;)

LEARN MYSQL(CLASS 2)

Asit Kumar Mahapatra
Software Trainee
BAY AREA INFOTECH ISyntax for Query Commands
CREATE Command
The Create command is used to create a table by specifying the tablename, fieldnames and constraints as shown below:
Syntax:
$createSQL=("CREATE TABLE tblName");
Example:
$createSQL=("CREATE TABLE tblstudent(fldstudid int(10) NOTNULL AUTO_INCREMENT PRIMARY KEY,fldstudName VARCHAR(250) NOTNULL,fldstudentmark int(4) DEFAULT '0' ");
SELECT Command
The Select command is used to select the records from a table using its field names. To select all the fields in a table, '*' is used in the command. The result is assigned to a variable name as shown below:
Syntax:
$selectSQL=("SELECT field_names FROM tablename");
Example:
$selectSQL=("SELECT * FROM tblstudent");
DELETE Command
The Delete command is used to delete the records from a table using conditions as shown below:
Syntax:
$deleteSQL=("DELETE * FROM tablename WHERE condition");
Example:
$deleteSQL=("DELETE * FROM tblstudent WHERE fldstudid=2");
INSERT Command
The Insert command is used to insert records into a table. The values are assigned to the field names as shown below:
Syntax:
$insertSQL=("INSERT INTO tblname(fieldname1,fieldname2..) VALUES(value1,value2,...) ");
Example:
$insertSQL=("INSERT INTO Tblstudent(fldstudName,fldstudmark)VALUES(Baskar,75) ");
UPDATE Command
The Update command is used to update the field values using conditions. This is done using 'SET' and the fieldnames to assign new values to them.
Syntax:
$updateSQL=("UPDATE Tblname SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber");
Example:
$updateSQL=("UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2");
DROP Command
The Drop command is used to delete all the records in a table using the table name as shown below:
Syntax:
$dropSQL=("DROP tblName");
Example:
$dropSQL=("DROP tblstudent");
NDIA PVT. LTD.

LEARN MYSQL(CLASS1)

MySQL Tutorial
1. Accessing MySQL (back to top)
Accessing MySQL on the command line
log into your workstation
edit .alias in vi
% vi .alias
enter the following in vi:
alias mysql /users/science/wilfred/local/mysql/bin/mysql
save and quit vi editor
:wq
(The last three steps need to be performed only once.)
Type
% source .alias
% mysql -h multivac
Or
log into multivac (used to be spirit)
ssh multivac
and type:
% source .alias
% mysql
The database that you can use is the same one as your username, plus samp_db (currently, more may be granted later).
You may create tables, drop tables, etc. inside your own database. samp_db or others which you may browse may not be deleted, or dropped.
Accessing MySQL from any machine
Your_Shell% mysql -h multivac.sdsc.edu -u wbluhm --password="" wbluhm
(This assumes that you have the client installed on your machine and the PATH set properly.)
Executing MySQL scripts on the command line (outside of MySQL)
% mysql wbluhm < myscript.sql

% mysql -t wbluhm < myscript.sql
Accessing MySQL from Perl or Java APIs

Perl Example: JavaBooks.pl
# queries the books table in database wbluhm on multivac

use DBI;
use strict;

my ($dsn) = "DBI:mysql:wbluhm:multivac.sdsc.edu"; # data source name
my ($user_name) = "wbluhm"; # user name
my ($password) = ""; # password
my ($dbh, $sth); # database and statement handles
my (@ary); # array for rows returned by query

# connect to database
$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 });

# issue query
my $query = qq{ SELECT title, author, comment
FROM books
WHERE topic="Java" };
$sth = $dbh->prepare ( $query );
$sth->execute ();

# read results of query, then clean up
while (@ary = $sth->fetchrow_array ())
{
print join (" ", @ary), "\n";
}
$sth->finish ();

$dbh->disconnect ();
exit (0);

Java Example: JavaBooks.java
// queries the books table in database wbluhm on multivac

import java.sql.*;
public class JavaBooks {
public static void main(String[] args) {

try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}
catch (Exception E) {
System.err.println("Unable to load driver");
E.printStackTrace();
}

try {
Connection C = DriverManager.getConnection(
"jdbc:mysql://multivac.sdsc.edu/wbluhm","wbluhm","");
Statement Stmt = C.createStatement();

ResultSet RS = Stmt.executeQuery
("SELECT title, author, comment " +
" FROM books WHERE topic=\"Java\"");

while (RS.next()) {
System.out.print("\"" + RS.getString(1) + "\"");
System.out.print(" by " + RS.getString(2));
System.out.println(": " + RS.getString(3));
}
C.close();
RS.close();
Stmt.close();
}
catch (SQLException E) {
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}
}
}

2. Creating Tables (back to top)
Basic commands on mysql command line:
% mysql

mysql> SHOW DATABASES;

mysql> USE wbluhm;

mysql> SHOW TABLES;

mysql> CREATE TABLE dummy ( first_name CHAR(20) , last_name CHAR(20) );

mysql> SHOW TABLES;

mysql> DESCRIBE dummy;

mysql> DROP TABLE dummy;

mysql> SHOW TABLES;

mysql> exit;

%
Creating tables from .sql scripts

SQL script example: create_books.sql

-- creates the table books in database wbluhm

CREATE TABLE books
(
title CHAR(50) NOT NULL,
author CHAR(30) NOT NULL,
publisher CHAR(30),
topic CHAR(20),
comment CHAR(100),
price FLOAT
)

% mysql wbluhm < create_books.sql
For more examples, see some of the scripts from the samp_db database in
/users/science/wbluhm/mysql/create_*.sql
Column types
Some basic column types: INTEGER, FLOAT, CHAR, DATE, TIME, BLOB
Complete table of all column types on mysql.com
3. Inserting Data (back to top)
Basic commands on mysql command line:
% mysql

mysql> USE wbluhm;

mysql> SELECT * FROM books;

mysql> INSERT INTO books
-> VALUES ("My Life", "Mickey Mouse", "Disney",
-> "Biography", "What can I say?", 9.95);

mysql> SELECT * FROM books;

mysql> DELETE FROM books WHERE title="My Life";

mysql> SELECT * FROM books;

mysql> exit;

%
Inserting data with .sql scripts:

SQL script example: populate_books.sql

-- populates the books table

INSERT INTO books
VALUES ("MySQL", "Paul DuBois", "New Riders", "MySQL",
"Excellent book, but doesn't cover Java API", 49.99);

INSERT INTO books
VALUES ("Beginning XML", "David Hunter", "Wrox", "XML",
"Well recommended, fairly comprehensive", 39.99);

INSERT INTO books
VALUES ("Java How to Program", "Paul Deitel", "Prentice Hall", "Java",
"Good textbook, extremely detailed", 68.00);

INSERT INTO books
VALUES ("Thinking in Java", "Bruce Eckel", "Prentice Hall", "Java",
"Well written, free on the web", 0.00);

INSERT INTO books
VALUES ("The Java Programming Language", "Ken Arnold", "Addison Wesley", "Java",
"Considered to be from the source", 37.95);

INSERT INTO books
VALUES ("Learning Perl", "Randal Schwartz", "O'Reilly", "Perl",
"Not a bad start", 29.95);

INSERT INTO books
VALUES ("Programming Perl", "Larry Wall", "O'Reilly", "Perl",
"Usually considered THE reference", 44.95);

INSERT INTO books
VALUES ("Effective Perl Programming", "Joseph Hall", "Addison Wesley", "Perl",
"Great tips, not for beginners", 34.95);

% mysql wbluhm < populate_books.sql

% mysql wbluhm

mysql> SELECT * FROM books;

mysql> DELETE FROM books;

mysql> SELECT * FROM books;

mysql> exit;

%
Inserting data from files

data file: books.data

MySQL Paul DuBois New Riders MySQL ... 49.99
Beginning XML David Hunter Wrox XML ... 39.99
...
Effective Perl Programming Joseph Hall Perl ... 34.95

% mysql

mysql> USE wbluhm

mysql> LOAD DATA INFILE '/users/science/wbluhm/mysql/books.data'
-> INTO TABLE books
-> FIELDS TERMINATED BY '\t'
-> LINES TERMINATED BY '\n'
-> IGNORE 0 LINES
-> (title, author, publisher, topic, comment, price);

mysql> SELECT * FROM books;

mysql> exit;

%
4. Querying Data (back to top)
Basic commands on mysql command line:
% mysql

mysql> USE wbluhm;

mysql> SELECT * FROM books;

mysql> SELECT title, price
-> FROM books
-> ;

mysql> SELECT title, author
-> FROM books
-> WHERE topic="Java";

mysql> SELECT title, author, price
-> FROM books
-> WHERE topic="Java"
-> ORDER BY price;

mysql> SELECT title, author, price
-> FROM books
-> WHERE topic="Java"
-> ORDER BY price DESC;

mysql> USE samp_db;

mysql> SHOW TABLES;

mysql> DESCRIBE president;

mysql> SELECT last_name, first_name, birth
-> FROM president
-> WHERE death IS NULL
-> ORDER BY last_name;

mysql> DESCRIBE student;

mysql> DESCRIBE absence;

mysql> SELECT student.name, absence.date
-> FROM student JOIN absence
-> WHERE student.student_id = absence.student_id;

mysql> exit;

%
Querying data with .sql scripts:

SQL script example: select_java_books.sql

SELECT title, author, price FROM books
WHERE topic="Java"
ORDER BY price;

% mysql wbluhm < select_java_books.sql

% mysql -t wbluhm < select_java_books.sql

BASIC LINUX COMMANDS

Asit Kumar Mahapatra
Software Trainee
BAY AREA INFOTECH INDIA PVT. LTD.
An A-Z Index of the Linux BASH command line
alias Create an alias
apropos Search Help manual pages (man -k)
awk Find and Replace text, database sort/validate/indexbreak Exit from a loop
builtin Run a shell builtin
bzip2 Compress or decompress named file(s)

cal Display a calendar
case Conditionally perform a commandcat Display the contents of a file
cd Change Directory
cfdisk Partition table manipulator for Linuxchgrp Change group ownershipchmod Change access permissionschown Change file owner and group
chroot Run a command with a different root directorycksum Print CRC checksum and byte counts
clear Clear terminal screen
cmp Compare two files
comm Compare two sorted files line by line
command Run a command - ignoring shell functions
continue Resume the next iteration of a loopcp Copy one or more files to another locationcron Daemon to execute scheduled commands
crontab Schedule a command to run at a later timecsplit Split a file into context-determined pieces
cut Divide a file into several parts

date Display or change the date & time
dc Desk Calculator
dd Data Dump - Convert and copy a file
ddrescue Data recovery tool
declare Declare variables and give them attributesdf Display free disk spacediff Display the differences between two filesdiff3 Show differences among three files
dig DNS lookup
dir Briefly list directory contents
dircolors Colour setup for `ls'dirname Convert a full pathname to just a path
dirs Display list of remembered directories
du Estimate file space usage

echo Display message on screen
egrep Search file(s) for lines that match an extended expression
eject Eject removable media
enable Enable and disable builtin shell commands
env Environment variables
ethtool Ethernet card settings
eval Evaluate several commands/arguments
exec Execute a command
exit Exit the shell
expand Convert tabs to spaces
export Set an environment variable
expr Evaluate expressions

false Do nothing, unsuccessfully
fdformat Low-level format a floppy disk
fdisk Partition table manipulator for Linux
fgrep Search file(s) for lines that match a fixed string
file Determine file type
find Search for files that meet a desired criteria
fmt Reformat paragraph text
fold Wrap text to fit a specified width.
for Expand words, and execute commands
format Format disks or tapes
free Display memory usage
fsck File system consistency check and repair
ftp File Transfer Protocol
function Define Function Macros

gawk Find and Replace text within file(s)
getopts Parse positional parameters
grep Search file(s) for lines that match a given pattern
groups Print group names a user is in
gzip Compress or decompress named file(s)

hash Remember the full pathname of a name argument
head Output the first part of file(s)history Command History
hostname Print or set system name

id Print user and group id's
if Conditionally perform a command
ifconfig Configure a network interface
import Capture an X server screen and save the image to file
install Copy files and set attributes

join Join lines on a common field

kill Stop a process from running

less Display output one screen at a time
let Perform arithmetic on shell variables
ln Make links between files
local Create variables
locate Find files
logname Print current login name
logout Exit a login shell
look Display lines beginning with a given string
lpc Line printer control program
lpr Off line print
lprint Print a file
lprintd Abort a print job
lprintq List the print queue
lprm Remove jobs from the print queue
ls List information about file(s)
lsof List open files

make Recompile a group of programs
man Help manual
mkdir Create new folder(s)
mkfifo Make FIFOs (named pipes)
mkisofs Create an hybrid ISO9660/JOLIET/HFS filesystem
mknod Make block or character special files
more Display output one screen at a time
mount Mount a file system
mtools Manipulate MS-DOS files
mv Move or rename files or directories

netstat Networking information
nice Set the priority of a command or job
nl Number lines and write files
nohup Run a command immune to hangups
nslookup Query Internet name servers interactively

passwd Modify a user password
paste Merge lines of files
pathchk Check file name portability
ping Test a network connection
popd Restore the previous value of the current directory
pr Prepare files for printing
printcap Printer capability database
printenv Print environment variables
printf Format and print data
ps Process status
pushd Save and then change the current directory
pwd Print Working Directory

quota Display disk usage and limits
quotacheck Scan a file system for disk usage
quotactl Set disk quotas

ram ram disk device
rcp Copy files between two machines.
read read a line from standard input
readonly Mark variables/functions as readonly
remsync Synchronize remote files via email
return Exit a shell function
rm Remove files
rmdir Remove folder(s)
rsync Remote file copy (Synchronize file trees)

screen Terminal window manager
scp Secure copy (remote file copy)
sdiff Merge two files interactively
sed Stream Editor
select Accept keyboard input
seq Print numeric sequences
set Manipulate shell variables and functions
sftp Secure File Transfer Program
shift Shift positional parameters
shopt Shell Options
shutdown Shutdown or restart linux
sleep Delay for a specified time
sort Sort text files
source Run commands from a file `.'
split Split a file into fixed-size pieces
ssh Secure Shell client (remote login program)
strace Trace system calls and signals
su Substitute user identity
sum Print a checksum for a file
symlink Make a new name for a file
sync Synchronize data on disk with memory

tail Output the last part of files
tar Tape ARchiver
tee Redirect output to multiple files
test Evaluate a conditional expression
time Measure Program running time
times User and system times
touch Change file timestamps
top List processes running on the system
traceroute Trace Route to Host
trap Run a command when a signal is set(bourne)
tr Translate, squeeze, and/or delete characters
true Do nothing, successfully
tsort Topological sort
tty Print filename of terminal on stdin
type Describe a command

ulimit Limit user resources
umask Users file creation mask
umount Unmount a device
unalias Remove an alias
uname Print system information
unexpand Convert spaces to tabs
uniq Uniquify files
units Convert units from one scale to another
unset Remove variable or function names
unshar Unpack shell archive scripts
until Execute commands (until error)
useradd Create new user account
usermod Modify user account
users List users currently logged in
uuencode Encode a binary file uudecode Decode a file created by uuencode

v Verbosely list directory contents (`ls -l -b')
vdir Verbosely list directory contents (`ls -l -b')
vi Text Editor

watch Execute/display a program periodically
wc Print byte, word, and line counts
whereis Report all known instances of a command
which Locate a program file in the user's path.
while Execute commands
who Print all usernames currently logged in
whoami Print the current user id and name (`id -un')
Wget Retrieve web pages or files via HTTP, HTTPS or FTP

xargs Execute utility, passing constructed argument list(s)
yes Print a string until interrupted