dtoppin@dtoppin-desktop:~/Desktop/mysql/zipcodes$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bebo | | callerid | | mysql | | testdate | | text | +--------------------+ 6 rows in set (0.00 sec) mysql> create database zipcodes; Query OK, 1 row affected (0.02 sec) mysql> use zipcodes Database changed mysql> create table zips ( -> fips char(2), -> zipcode integer, -> state char(2). -> zipname char, -> longitude double, -> latitude double, -> population integer, -> allocfactor double); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. zipname char, longitude double, latitude double, population integer, allocfact' at line 4 mysql> mysql> mysql> create table zips ( fips char(2), zipcode integer, state char(2). zipname char(20), longitude double, latitude double, population integer, allocfacto r double); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. zipname char(20), longitude double, latitude double, population integer, alloc' at line 1 mysql> mysql> create table zips ( fips char(2), zipcode integer, state char(2), zipname char(20), longitude double, latitude double, population integer, allocfactor double); Query OK, 0 rows affected (0.01 sec) mysql> load data local infile "/home/dtoppin/Desktop/mysql/zipcodes/list.csv" into table zips fields terminated by ","; ERROR 2 (HY000): File '/home/dtoppin/Desktop/mysql/zipcodes/list.csv' not found (Errcode: 2) mysql> load data local infile "/home/dtoppin/Desktop/mysql/zipcodes/list1.csv" into table zips fields terminated by ","; Query OK, 29470 rows affected, 65535 warnings (0.09 sec) Records: 29470 Deleted: 0 Skipped: 0 Warnings: 88410 mysql> select * from zips limit 10; +------+---------+-------+--------------+-----------+-----------+------------+-------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | +------+---------+-------+--------------+-----------+-----------+------------+-------------+ | "0 | 0 | "A | "ACMAR" | 86.51557 | 33.584132 | 6055 | 0.001499 | | "0 | 0 | "A | "ADAMSVILLE" | 86.959727 | 33.588437 | 10616 | 0.002627 | | "0 | 0 | "A | "ADGER" | 87.167455 | 33.434277 | 3205 | 0.000793 | | "0 | 0 | "A | "KEYSTONE" | 86.812861 | 33.236868 | 14218 | 0.003519 | | "0 | 0 | "A | "NEW SITE" | 85.951086 | 32.941445 | 19942 | 0.004935 | | "0 | 0 | "A | "ALPINE" | 86.208934 | 33.331165 | 3062 | 0.000758 | | "0 | 0 | "A | "ARAB" | 86.489638 | 34.328339 | 13650 | 0.003378 | | "0 | 0 | "A | "BAILEYTON" | 86.621299 | 34.268298 | 1781 | 0.000441 | | "0 | 0 | "A | "BESSEMER" | 86.947547 | 33.409002 | 40549 | 0.010035 | | "0 | 0 | "A | "HUEYTOWN" | 86.999607 | 33.414625 | 39677 | 0.00982 | +------+---------+-------+--------------+-----------+-----------+------------+-------------+ 10 rows in set (0.00 sec) mysql> select * from zips where state = "AL"; Empty set (0.00 sec) mysql> trucate table zips; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trucate table zips' at line 1 mysql> help truncate Name: 'TRUNCATE' Description: Syntax: TRUNCATE(X,D) Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero. URL: http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html Examples: mysql> SELECT TRUNCATE(1.223,1); -> 1.2 mysql> SELECT TRUNCATE(1.999,1); -> 1.9 mysql> SELECT TRUNCATE(1.999,0); -> 1 mysql> SELECT TRUNCATE(-1.999,1); -> -1.9 mysql> SELECT TRUNCATE(122,-2); -> 100 mysql> SELECT TRUNCATE(10.28*100,0); -> 1028 mysql> truncate zips; Query OK, 0 rows affected (0.00 sec) mysql> select * from zips where state = "AL"; Empty set (0.00 sec) mysql> mysql> mysql> load data local infile "/home/dtoppin/Desktop/mysql/zipcodes/list1.csv" into table zips fields terminated by ","; Query OK, 29470 rows affected, 29470 warnings (0.06 sec) Records: 29470 Deleted: 0 Skipped: 0 Warnings: 29470 mysql> show warnings -> ; +---------+------+---------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------+ | Warning | 1265 | Data truncated for column 'allocfactor' at row 1 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 2 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 3 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 4 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 5 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 6 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 7 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 8 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 9 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 10 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 11 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 12 | | Warning | 1265 | Data truncated for column 'allocfactor' at row 64 | ... +---------+------+---------------------------------------------------+ 64 rows in set (0.00 sec) mysql> mysql> mysql> select * from zips where state = "AL"; +------+---------+-------+------------------+-----------+-----------+------------+-------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | +------+---------+-------+------------------+-----------+-----------+------------+-------------+ | 01 | 35004 | AL | ACMAR | 86.51557 | 33.584132 | 6055 | 0.001499 | | 01 | 35005 | AL | ADAMSVILLE | 86.959727 | 33.588437 | 10616 | 0.002627 | | 01 | 35006 | AL | ADGER | 87.167455 | 33.434277 | 3205 | 0.000793 | | 01 | 35007 | AL | KEYSTONE | 86.812861 | 33.236868 | 14218 | 0.003519 | | 01 | 35010 | AL | NEW SITE | 85.951086 | 32.941445 | 19942 | 0.004935 | | 01 | 35014 | AL | ALPINE | 86.208934 | 33.331165 | 3062 | 0.000758 | | 01 | 35016 | AL | ARAB | 86.489638 | 34.328339 | 13650 | 0.003378 | | 01 | 35019 | AL | BAILEYTON | 86.621299 | 34.268298 | 1781 | 0.000441 | | 01 | 35020 | AL | BESSEMER | 86.947547 | 33.409002 | 40549 | 0.010035 | | 01 | 35023 | AL | HUEYTOWN | 86.999607 | 33.414625 | 39677 | 0.00982 | | 01 | 35031 | AL | BLOUNTSVILLE | 86.568628 | 34.092937 | 9058 | 0.002242 | ... | 01 | 36915 | AL | NEEDHAM | 88.345798 | 32.012165 | 618 | 0.000153 | | 01 | 36916 | AL | PENNINGTON | 88.0931 | 32.226245 | 1459 | 0.000361 | | 01 | 36919 | AL | SILAS | 88.309149 | 31.766754 | 2600 | 0.000643 | | 01 | 36921 | AL | TOXEY | 88.31794 | 31.93153 | 1980 | 0.00049 | | 01 | 36922 | AL | WARD | 88.297114 | 32.334063 | 274 | 6.8e-05 | | 01 | 36925 | AL | YORK | 88.268304 | 32.472765 | 5728 | 0.001418 | +------+---------+-------+------------------+-----------+-----------+------------+-------------+ 567 rows in set (0.01 sec) mysql> mysql> mysql> show warnings -> ; Empty set (0.00 sec) mysql> select * from zips where state ="TX"; +------+---------+-------+------------------+------------+-----------+------------+-------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | +------+---------+-------+------------------+------------+-----------+------------+-------------+ | 48 | 75002 | TX | ALLEN | 96.645433 | 33.093383 | 24151 | 0.001422 | | 48 | 75006 | TX | CARROLLTON | 96.882464 | 32.965736 | 37699 | 0.002219 | | 48 | 75007 | TX | CARROLLTON | 96.881988 | 33.003294 | 54796 | 0.003226 | | 48 | 75008 | TX | CARROLLTON | 96.923197 | 33.03524 | 1482 | 8.7e-05 | | 48 | 75009 | TX | CELINA | 96.767325 | 33.310316 | 3373 | 0.000199 | | 48 | 75010 | TX | CARROLLTON | 96.877746 | 33.030414 | 4379 | 0.000258 | | 48 | 75019 | TX | COPPELL | 96.980516 | 32.96727 | 16862 | 0.000993 | | 48 | 75020 | TX | DENISON | 96.549574 | 33.745009 | 27172 | 0.0016 | | 48 | 75023 | TX | PLANO | 96.736454 | 33.054972 | 40832 | 0.002404 | | 48 | 75024 | TX | PLANO | 96.784307 | 33.075211 | 1439 | 8.5e-05 | | 48 | 75025 | TX | PLANO | 96.729142 | 33.078377 | 8562 | 0.000504 | | 48 | 75028 | TX | FLOWER MOUND | 97.074501 | 33.038268 | 16825 | 0.00099 | | 48 | 75034 | TX | FRISCO | 96.824105 | 33.149901 | 8045 | 0.000474 | | 48 | 75038 | TX | IRVING | 96.990503 | 32.865309 | 20152 | 0.001186 | | 48 | 75039 | TX | IRVING | 96.938876 | 32.869669 | 598 | 3.5e-05 | | 48 | 75040 | TX | GARLAND | 96.624804 | 32.922744 | 45359 | 0.00267 | | 48 | 75041 | TX | GARLAND | 96.641115 | 32.87937 | 26212 | 0.001543 | | 48 | 75042 | TX | GARLAND | 96.677545 | 32.918486 | 31807 | 0.001872 | | 48 | 75043 | TX | GARLAND | 96.599882 | 32.856502 | 46620 | 0.002745 | ... | 48 | 79932 | TX | EL PASO | 106.593186 | 31.862334 | 14909 | 0.000878 | | 48 | 79934 | TX | EL PASO | 106.407328 | 31.938585 | 2983 | 0.000176 | | 48 | 79935 | TX | EL PASO | 106.330258 | 31.771847 | 20465 | 0.001205 | | 48 | 79936 | TX | EL PASO | 106.30159 | 31.767655 | 52031 | 0.003063 | +------+---------+-------+------------------+------------+-----------+------------+-------------+ 1676 rows in set (0.01 sec) mysql> mysql> count(select * from zips where state ="TX"); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(select * from zips where state ="TX")' at line 1 mysql> count * from zips where state ="TX"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count * from zips where state ="TX"' at line 1 mysql> mysql> mysql> count(*) from zips where state= "TX"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(*) from zips where state= "TX"' at line 1 mysql> describe zips; +-------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-------+ | fips | char(2) | YES | | NULL | | | zipcode | int(11) | YES | | NULL | | | state | char(2) | YES | | NULL | | | zipname | char(20) | YES | | NULL | | | longitude | double | YES | | NULL | | | latitude | double | YES | | NULL | | | population | int(11) | YES | | NULL | | | allocfactor | double | YES | | NULL | | +-------------+----------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> mysql> select count(*) from zips where state= "TX"; +----------+ | count(*) | +----------+ | 1676 | +----------+ 1 row in set (0.01 sec) mysql> mysql> select count(*) from zips where zipname="HOUSTON"; +----------+ | count(*) | +----------+ | 101 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from zips where zipname="houston"; +----------+ | count(*) | +----------+ | 101 | +----------+ 1 row in set (0.01 sec) mysql> select * from zips where zipname="houston" AND population > 40000; +------+---------+-------+---------+-----------+-----------+------------+-------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | +------+---------+-------+---------+-----------+-----------+------------+-------------+ | 48 | 77009 | TX | HOUSTON | 95.367481 | 29.793558 | 42521 | 0.002503 | | 48 | 77015 | TX | HOUSTON | 95.185189 | 29.785287 | 42008 | 0.002473 | | 48 | 77036 | TX | HOUSTON | 95.540464 | 29.698447 | 55414 | 0.003262 | | 48 | 77072 | TX | HOUSTON | 95.586155 | 29.699026 | 41808 | 0.002461 | | 48 | 77084 | TX | HOUSTON | 95.662329 | 29.844022 | 45204 | 0.002661 | | 48 | 77088 | TX | HOUSTON | 95.453877 | 29.881694 | 44595 | 0.002625 | | 48 | 77099 | TX | HOUSTON | 95.586613 | 29.670869 | 40503 | 0.002384 | +------+---------+-------+---------+-----------+-----------+------------+-------------+ 7 rows in set (0.00 sec) mysql> select count(*) from zips where zipname="houston" AND population > 40000; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) mysql> select * from zips where zipname="houston" AND population > 40000 into outfile "/tmp/queryresults.txt";; Query OK, 7 rows affected (0.01 sec) $ cat /tmp/queryresults.txt 48 77009 TX HOUSTON 95.367481 29.793558 42521 0.002503 48 77015 TX HOUSTON 95.185189 29.785287 42008 0.002473 48 77036 TX HOUSTON 95.540464 29.698447 55414 0.003262 48 77072 TX HOUSTON 95.586155 29.699026 41808 0.002461 48 77084 TX HOUSTON 95.662329 29.844022 45204 0.002661 48 77088 TX HOUSTON 95.453877 29.881694 44595 0.002625 48 77099 TX HOUSTON 95.586613 29.670869 40503 0.002384 mysql> select * from zips where zipname="houston" AND population > 40000 into outfile "/tmp/queryresults.txt";; ERROR 1086 (HY000): File '/tmp/queryresults.txt' already exists mysql> show tables; +--------------------+ | Tables_in_zipcodes | +--------------------+ | zips | +--------------------+ 1 row in set (0.01 sec) mysql> describe zips; +-------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-------+ | fips | char(2) | YES | | NULL | | | zipcode | int(11) | YES | | NULL | | | state | char(2) | YES | | NULL | | | zipname | char(20) | YES | | NULL | | | longitude | double | YES | | NULL | | | latitude | double | YES | | NULL | | | population | int(11) | YES | | NULL | | | allocfactor | double | YES | | NULL | | +-------------+----------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> alter table zips add updated TIMESTAMP; Query OK, 29470 rows affected (0.01 sec) Records: 29470 Duplicates: 0 Warnings: 0 mysql> describe zips; +-------------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------+------+-----+-------------------+-------+ | fips | char(2) | YES | | NULL | | | zipcode | int(11) | YES | | NULL | | | state | char(2) | YES | | NULL | | | zipname | char(20) | YES | | NULL | | | longitude | double | YES | | NULL | | | latitude | double | YES | | NULL | | | population | int(11) | YES | | NULL | | | allocfactor | double | YES | | NULL | | | updated | timestamp | NO | | CURRENT_TIMESTAMP | | +-------------+-----------+------+-----+-------------------+-------+ 9 rows in set (0.00 sec) mysql> select * from zips limit 10; +------+---------+-------+------------+-----------+-----------+------------+-------------+---------------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | updated | +------+---------+-------+------------+-----------+-----------+------------+-------------+---------------------+ | 01 | 35004 | AL | ACMAR | 86.51557 | 33.584132 | 6055 | 0.001499 | 0000-00-00 00:00:00 | | 01 | 35005 | AL | ADAMSVILLE | 86.959727 | 33.588437 | 10616 | 0.002627 | 0000-00-00 00:00:00 | | 01 | 35006 | AL | ADGER | 87.167455 | 33.434277 | 3205 | 0.000793 | 0000-00-00 00:00:00 | | 01 | 35007 | AL | KEYSTONE | 86.812861 | 33.236868 | 14218 | 0.003519 | 0000-00-00 00:00:00 | | 01 | 35010 | AL | NEW SITE | 85.951086 | 32.941445 | 19942 | 0.004935 | 0000-00-00 00:00:00 | | 01 | 35014 | AL | ALPINE | 86.208934 | 33.331165 | 3062 | 0.000758 | 0000-00-00 00:00:00 | | 01 | 35016 | AL | ARAB | 86.489638 | 34.328339 | 13650 | 0.003378 | 0000-00-00 00:00:00 | | 01 | 35019 | AL | BAILEYTON | 86.621299 | 34.268298 | 1781 | 0.000441 | 0000-00-00 00:00:00 | | 01 | 35020 | AL | BESSEMER | 86.947547 | 33.409002 | 40549 | 0.010035 | 0000-00-00 00:00:00 | | 01 | 35023 | AL | HUEYTOWN | 86.999607 | 33.414625 | 39677 | 0.00982 | 0000-00-00 00:00:00 | +------+---------+-------+------------+-----------+-----------+------------+-------------+---------------------+ 10 rows in set (0.00 sec) mysql> update zips SET population="7000" where zipname="ACMAR" and zipcode="35004"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from zips limit 10; +------+---------+-------+------------+-----------+-----------+------------+-------------+---------------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | updated | +------+---------+-------+------------+-----------+-----------+------------+-------------+---------------------+ | 01 | 35004 | AL | ACMAR | 86.51557 | 33.584132 | 7000 | 0.001499 | 2008-09-28 21:22:35 | | 01 | 35005 | AL | ADAMSVILLE | 86.959727 | 33.588437 | 10616 | 0.002627 | 0000-00-00 00:00:00 | | 01 | 35006 | AL | ADGER | 87.167455 | 33.434277 | 3205 | 0.000793 | 0000-00-00 00:00:00 | | 01 | 35007 | AL | KEYSTONE | 86.812861 | 33.236868 | 14218 | 0.003519 | 0000-00-00 00:00:00 | | 01 | 35010 | AL | NEW SITE | 85.951086 | 32.941445 | 19942 | 0.004935 | 0000-00-00 00:00:00 | | 01 | 35014 | AL | ALPINE | 86.208934 | 33.331165 | 3062 | 0.000758 | 0000-00-00 00:00:00 | | 01 | 35016 | AL | ARAB | 86.489638 | 34.328339 | 13650 | 0.003378 | 0000-00-00 00:00:00 | | 01 | 35019 | AL | BAILEYTON | 86.621299 | 34.268298 | 1781 | 0.000441 | 0000-00-00 00:00:00 | | 01 | 35020 | AL | BESSEMER | 86.947547 | 33.409002 | 40549 | 0.010035 | 0000-00-00 00:00:00 | | 01 | 35023 | AL | HUEYTOWN | 86.999607 | 33.414625 | 39677 | 0.00982 | 0000-00-00 00:00:00 | +------+---------+-------+------------+-----------+-----------+------------+-------------+---------------------+ 10 rows in set (0.00 sec) mysql> update zips SET updated="1998-05-06" where zipname="ALPINE"; mysql> select * from zips where updated > "0000-00-00"; +------+---------+-------+---------+------------+-----------+------------+-------------+---------------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | updated | +------+---------+-------+---------+------------+-----------+------------+-------------+---------------------+ | 01 | 35004 | AL | ACMAR | 86.51557 | 33.584132 | 7000 | 0.001499 | 2008-10-31 20:27:08 | | 01 | 35014 | AL | ALPINE | 86.208934 | 33.331165 | 3062 | 0.000758 | 1998-05-06 00:00:00 | | 04 | 85920 | AZ | ALPINE | 109.12829 | 33.827878 | 243 | 6.6e-05 | 1998-05-06 00:00:00 | | 06 | 91901 | CA | ALPINE | 116.754328 | 32.828161 | 12566 | 0.000422 | 1998-05-06 00:00:00 | | 34 | 7620 | NJ | ALPINE | 73.930842 | 40.951097 | 1716 | 0.000222 | 1998-05-06 00:00:00 | | 36 | 14805 | NY | ALPINE | 76.734775 | 42.351014 | 1098 | 6.1e-05 | 1998-05-06 00:00:00 | | 47 | 38543 | TN | ALPINE | 85.152153 | 36.380324 | 441 | 9e-05 | 1999-05-06 00:00:00 | | 48 | 79830 | TX | ALPINE | 103.654089 | 30.263111 | 7648 | 0.00045 | 1998-05-06 00:00:00 | | 49 | 84004 | UT | ALPINE | 111.768861 | 40.461591 | 3665 | 0.002127 | 1998-05-06 00:00:00 | +------+---------+-------+---------+------------+-----------+------------+-------------+---------------------+ 9 rows in set (0.00 sec) mysql> select * from zips where updated = "1998-05-06"; mysql> select * from zips where updated = "1998-05-06"; +------+---------+-------+---------+------------+-----------+------------+-------------+---------------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | updated | +------+---------+-------+---------+------------+-----------+------------+-------------+---------------------+ | 01 | 35014 | AL | ALPINE | 86.208934 | 33.331165 | 3062 | 0.000758 | 1998-05-06 00:00:00 | | 04 | 85920 | AZ | ALPINE | 109.12829 | 33.827878 | 243 | 6.6e-05 | 1998-05-06 00:00:00 | | 06 | 91901 | CA | ALPINE | 116.754328 | 32.828161 | 12566 | 0.000422 | 1998-05-06 00:00:00 | | 34 | 7620 | NJ | ALPINE | 73.930842 | 40.951097 | 1716 | 0.000222 | 1998-05-06 00:00:00 | | 36 | 14805 | NY | ALPINE | 76.734775 | 42.351014 | 1098 | 6.1e-05 | 1998-05-06 00:00:00 | | 48 | 79830 | TX | ALPINE | 103.654089 | 30.263111 | 7648 | 0.00045 | 1998-05-06 00:00:00 | | 49 | 84004 | UT | ALPINE | 111.768861 | 40.461591 | 3665 | 0.002127 | 1998-05-06 00:00:00 | +------+---------+-------+---------+------------+-----------+------------+-------------+---------------------+ 7 rows in set (0.01 sec) mysql> select * from zips where updated > "1998-05-06"; +------+---------+-------+---------+-----------+-----------+------------+-------------+---------------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | updated | +------+---------+-------+---------+-----------+-----------+------------+-------------+---------------------+ | 01 | 35004 | AL | ACMAR | 86.51557 | 33.584132 | 7000 | 0.001499 | 2008-10-31 20:27:08 | | 47 | 38543 | TN | ALPINE | 85.152153 | 36.380324 | 441 | 9e-05 | 1999-05-06 00:00:00 | +------+---------+-------+---------+-----------+-----------+------------+-------------+---------------------+ 2 rows in set (0.01 sec) mysql> update zips SET updated="1999-05-06" where zipname="ALPINE" and STATE="TN"; mysql> select * from zips where updated > "1998-05-06" and updated < "2008-10-31"; +------+---------+-------+---------+-----------+-----------+------------+-------------+---------------------+ | fips | zipcode | state | zipname | longitude | latitude | population | allocfactor | updated | +------+---------+-------+---------+-----------+-----------+------------+-------------+---------------------+ | 47 | 38543 | TN | ALPINE | 85.152153 | 36.380324 | 441 | 9e-05 | 1999-05-06 00:00:00 | +------+---------+-------+---------+-----------+-----------+------------+-------------+---------------------+ 1 row in set (0.01 sec) mysql> show status; +-----------------------------------+-------------+ | Variable_name | Value | +-----------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 789 | | Bytes_sent | 13092 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_call_procedure | 0 | | Com_change_db | 1 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_create_user | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 0 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 7 | | Com_set_option | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 0 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 0 | | Com_show_databases | 1 | | Com_show_errors | 0 | | Com_show_fields | 1 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_ndb_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 2 | | Com_show_storage_engines | 0 | | Com_show_tables | 1 | | Com_show_triggers | 0 | | Com_show_variables | 0 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 1 | | Com_update_multi | 0 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 11 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 5 | | Created_tmp_tables | 4 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 147641 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 390 | | Innodb_buffer_pool_pages_data | 19 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 0 | | Innodb_buffer_pool_pages_free | 493 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 512 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 77 | | Innodb_buffer_pool_reads | 12 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 0 | | Innodb_data_fsyncs | 3 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 2494464 | | Innodb_data_reads | 25 | | Innodb_data_writes | 3 | | Innodb_data_written | 1536 | | Innodb_dblwr_pages_written | 0 | | Innodb_dblwr_writes | 0 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 0 | | Innodb_log_writes | 1 | | Innodb_os_log_fsyncs | 3 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 512 | | Innodb_page_size | 16384 | | Innodb_pages_created | 0 | | Innodb_pages_read | 19 | | Innodb_pages_written | 0 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 0 | | Innodb_rows_read | 0 | | Innodb_rows_updated | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 14494 | | Key_blocks_used | 3 | | Key_read_requests | 6 | | Key_reads | 3 | | Key_write_requests | 0 | | Key_writes | 0 | | Last_query_cost | 6342.723512 | | Max_used_connections | 1 | | Ndb_cluster_node_id | 0 | | Ndb_config_from_host | | | Ndb_config_from_port | 0 | | Ndb_number_of_data_nodes | 0 | | Not_flushed_delayed_rows | 0 | | Open_files | 40 | | Open_streams | 0 | | Open_tables | 20 | | Opened_tables | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 1 | | Qcache_free_memory | 16760928 | | Qcache_hits | 1 | | Qcache_inserts | 5 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 15 | | Qcache_queries_in_cache | 5 | | Qcache_total_blocks | 12 | | Questions | 58 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 9 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 39 | | Table_locks_waited | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 1 | | Threads_running | 1 | | Uptime | 4161 | | Uptime_since_flush_status | 4161 | +-----------------------------------+-------------+ 253 rows in set (0.00 sec) mysql> show variables; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 5 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | keep_files_on_create | OFF | | engine_condition_pushdown | OFF | | expire_logs_days | 10 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | YES | | have_bdb | NO | | have_blackhole_engine | YES | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_example_engine | NO | | have_federated_engine | YES | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_merge_engine | YES | | have_ndbcluster | DISABLED | | have_openssl | DISABLED | | have_ssl | DISABLED | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | hostname | dtoppin-laptop2 | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 16777216 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 16776192 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 104857600 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | ndb_autoincrement_prefetch_sz | 32 | | ndb_force_send | ON | | ndb_use_exact_count | ON | | ndb_use_transactions | ON | | ndb_cache_check_time | 0 | | ndb_connectstring | | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | OFF | | open_files_limit | 1024 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | pid_file | /var/run/mysqld/mysqld.pid | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log_purge | ON | | relay_log_space_limit | 0 | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 0 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_load_tmpdir | /tmp/ | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slow_launch_time | 2 | | socket | /var/run/mysqld/mysqld.sock | | sort_buffer_size | 2097144 | | sql_big_selects | ON | | sql_mode | | | sql_notes | ON | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | EDT | | table_cache | 64 | | table_lock_wait_timeout | 50 | | table_type | MyISAM | | thread_cache_size | 8 | | thread_stack | 131072 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 33554432 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | updatable_views_with_limit | YES | | version | 5.0.51a-3ubuntu5.1 | | version_comment | (Ubuntu) | | version_compile_machine | i486 | | version_compile_os | debian-linux-gnu | | wait_timeout | 28800 | +---------------------------------+-----------------------------+ 232 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+----------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----------+---------+------+-------+------------------+ | 10 | root | localhost | zipcodes | Query | 0 | NULL | show processlist | +----+------+-----------+----------+---------+------+-------+------------------+ 1 row in set (0.00 sec) mysql> exit Bye $ mysqldump --user=root --password=xxxx --result-file=/tmp/mysqlzips.sql zipcodes $ ls -l /tmp/mysqlzips.sql -rw-r--r-- 1 dtoppin dtoppin 2508427 2008-11-01 08:27 /tmp/mysqlzips.sql $ head -20 /tmp/mysqlzips.sql -- MySQL dump 10.11 -- -- Host: localhost Database: zipcodes -- ------------------------------------------------------ -- Server version 5.0.51a-3ubuntu5.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;