11 Mar 09 Simple MySQL Developer Intro

MySQL is one of the most powerful and widely used databases available. Here is a really quick guide to creating a database, creating a table, inserting, selecting and deleting the data, then table, then database. This will not go into too much depth as there are plenty of resources out there already that can provide more information on each step.

I’ll be using the MySQL command line tool on a Linux (Debian!) platform. Assuming I already have mysql running and a passworded root user account:

ns3:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 168753
Server version: 5.0.51a-24-log (Debian)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>


Now we’re ready to go. First we need to create a database:

mysql> create database `test_database`;
Query OK, 1 row affected (0.06 sec)

Now we list all databases:

mysql> show databases;
+———————+
| Database            |
+———————+
| information_schema  |
| mysql               |
| test_database       |
+———————+
3 rows in set (0.00 sec)

mysql>

Now we need to specify which database we’re working with:

mysql> use test_database;
Database changed
mysql>

I’ll add a user called ‘test_users’ with a password ‘test_password’ and allow him full access to test_database:

mysql> GRANT ALL ON test_database.* TO test_users@’%’ IDENTIFIED BY “test_password”;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> \q
Bye
ns3:~#

Now I’ll relog as ‘test_users’

ns3:~# mysql -u test_users -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 168783
Server version: 5.0.51a-24-log (Debian)

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

mysql> use test_database;
Database changed
mysql>

Here I create a new table called ‘test_table’. I’ve created three fields, test_id, test_firstname and test_lastname. There are many different field types and table settings that you can apply, these are beyond the scope of this article.

mysql> CREATE TABLE test_table (
-> test_id int unsigned not null auto_increment primary key,
-> test_firstname varchar (30),
-> test_lastname varchar (30)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+————————-+
| Tables_in_test_database |
+————————-+
| test_table              |
+————————-+
1 row in set (0.00 sec)

mysql> describe test_table;
+—————-+——————+——+—–+———+—————-+
| Field          | Type             | Null | Key | Default | Extra          |
+—————-+——————+——+—–+———+—————-+
| test_id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test_firstname | varchar(30)      | YES  |     | NULL    |                |
| test_lastname  | varchar(30)      | YES  |     | NULL    |                |
+—————-+——————+——+—–+———+—————-+
3 rows in set (0.01 sec)

mysql>

Inserting records is this easy..

mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES (’Adam’, ‘Palmer’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES (’Fred’, ‘Flintstone’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES (’Foo’, ‘Bar’);
Query OK, 1 row affected (0.00 sec)

mysql>

Pulling out data is even easier:

mysql> SELECT test_firstname FROM test_table;
+—————-+
| test_firstname |
+—————-+
| Adam           |
| Fred           |
| Foo            |
+—————-+
3 rows in set (0.00 sec)

mysql> SELECT test_lastname FROM test_table
-> ;
+—————+
| test_lastname |
+—————+
| Palmer        |
| Flintstone    |
| Bar           |
+—————+
3 rows in set (0.00 sec)

mysql> SELECT test_firstname, test_lastname FROM test_table ORDER BY test_lastname ASC;
+—————-+—————+
| test_firstname | test_lastname |
+—————-+—————+
| Foo            | Bar           |
| Fred           | Flintstone    |
| Adam           | Palmer        |
+—————-+—————+
3 rows in set (0.00 sec)

mysql> SELECT test_firstname, test_lastname FROM test_table ORDER BY test_lastname ASC LIMIT 2;
+—————-+—————+
| test_firstname | test_lastname |
+—————-+—————+
| Foo            | Bar           |
| Fred           | Flintstone    |
+—————-+—————+
2 rows in set (0.00 sec)

Now to delete the data. Note that DELETE FROM will delete ALL data in the table unless you constrain it with WHERE conditions. We use the ‘%’ character as a wildcard. %blah% will match ‘helloblah’ and ‘blahhello’ and ‘helloblahhello’

mysql> DELETE FROM test_table WHERE test_firstname=’Foo’;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM test_table WHERE test_lastname LIKE ‘%e%’;
Query OK, 2 rows affected (0.00 sec)

Delete the table:

mysql> DROP TABLE test_table;
Query OK, 0 rows affected (0.01 sec)

mysql>

Delete the database:

mysql> DROP DATABASE test_database;
Query OK, 0 rows affected (0.14 sec)

mysql> \q
Bye

We’re done. I hope that this has helped. The purpose of the article is to provide a really quick guide to getting started. This blog as a whole is geared towards the user that can take the information above and work the rest out, by trial and error and by google. For that reason I haven’t elaborated on each step or gone into much explaination.


cialis how does it work cheap cialis india viagra recipe viagra cialis difference tadalafil soft tablets generic cialis overnight viagra buy on line buy viagra low cost viagra shopping levitra prescription viagra naturale levitra alternatives cialis soft tab vigora 50mg cialis trial tadalafil 20 cialis order vardenafil 20 mg levitra tabs viagra no prescription needed price cialis viagra prescription uk sell viagra online cheap generic viagra sildenafil soft tabs viagra in india kamagra india cialis low price cheap cialis genuine buy viagra pill online generic viagra paypal viagra how it works viagra online in uk viagra cialis sample indian viagra generic cialis lowest price levitra in canada viagra performance cialis best prices sildenafil tablet sildenafil and nitrates cialis order online cheap viagra canada cheap viagra cialis prescription viagra viagra sales in india kamagra jelly 100mg viagra medicare cheap tadalafil viagra street value free viagra viagra from canada viagra doctors cialis prescription cost generic vardenafil viagra ukraine gel viagra kamagra gel uk viagra tokyo generic viagra online cheap vardenafil viagra erowid kamagra shop cialis viagra comparison cialis discount vardenafil and pharmacy levitra buy online sildenafil 50 mg tadalafil 20 mg cialis uk online viagra no script cialis no rx viagra prescription drug viagra free shipping buy viagra in india cialis pill levitra cost discount cialis over the counter sildenafil buy viagra in sydney cialis versus viagra viagra treatment cialis mastercard sildenafil synthesis online viagra australia levitra sample viagra online rx viagra us pharmacy viagra generic drug levitra mexico generic viagra uk cheap cialis online us pfizer viagra order cialis indian version of viagra levitra for sale vigara viagra pharmacology cheapest viagra in australia online cialis indian viagra cost levitra lasts sildenafil tablets discount online viagra levitra medication viagra splitter viagra for sale without prescription viagra generic names cialis testimonials kamagra sale viagra natural alternatives viagra suppliers in uk buy vardenafil tadalafil citrate buy cialis generic online cheapest tadalafil cialis female buy kamagra jelly viagra clones caverta india viagra canadian pharmacy viagra indian version cialis super active reviews viagra glasgow tadalafil soft tablets 20mg levitra or cialis viagra 25 mg sialis cheap apcalis viagra online uk viagra homemade viagra price canada sildenafil australia levitra shelf life buying viagra online levitra sales cialis toronto indian viagra brands viagra pros and cons oral jelly 100 mg viagra 25mg levitra versus cialis generic viagra safety impotence viagra cialis compare viagra viagra ajantha buy tadalafil no prescription generic viagra from india buy viagra super active viagra free pill cialis no prescription cialis instructions singapore viagra woman viagra generic viagra blue pill 25mg silagra tablets viagra without ed kamagra st 100 order levitra without prescription free kamagra viagra buy online viagra instructions canada viagra online viagra otc cheapest viagra in canada viagra online legal buy sildenafil viagra cheap levitra no prescription generic indian viagra cialis maker cialis online sales kamagra jellies cialis 5 mg daily cialis new zealand free viagra order online viagra online no prescription levitra now online levitra professional online viagra liquid buy viagra in malaysia herbal viagra reviews viagra capsules levitra vs viagra vs cialis buy cialis now liquid tadalafil viagra mailing lists viagra vietnam generic viagra samples cialis manufacturer female viagra pill viagra sales 2006 viagra equivalent viagra online pharmacy viagra questions generic viagra cialis buy cheap cialis cialis vs viagra vs levitra viagra comparison canada cialis viagra ingredient buy kamagra cialis online shopping generic tadalafil canada viagra indian buy viagra in italy cheap generic cialis levitra information buy viagra woman green kamagra viagra non prescription viagra gels kamagra next day cialis uk supplier cheap viagra order online viagra fedex order cialis online no prescription cialis uk prescription indian viagra reviews cialis jelly order viagra viagra 100 viagra value card buy viagra in europe vardenafil tablets sildenafil uk cialis price uk viagra purchase online levitra online cheap generic kamagra levitra or viagra generic cialis mastercard cialis 20 viagra kgr 100 cialis price comparison buy apcalis revatio coupon viagra san diego viagra professional review trial cialis viagra in uk kamagra bestellen levitra 10 mg order viagra air travel generic caverta cheap viagra no prescription viagra online safely viagra online order 25mg viagra generic viagra for sale levitra 5 mg free viagra online viagra ebay viagra street price cheap online viagra purchase viagra australia viagra kopen viagra alternative buy viagra in ireland 20mg cialis generic women viagra sildenafil india kaufen viagra viagra with no prescription generic cialis next day cheap cialis free shipping apcalis sx viagra manchester cialis generic name online viagra sale cialis over the counter cealis levitra overnight delivery viagra pharmacy online buy viagra in nz cialis philippines liquid sildenafil purchase viagra buying viagra in australia online generic viagra buy silagra online viagra from india levitra viagra compared viagra cost per pill buy viagra cheap tadalafil buy free cialis pills buy viagra cialis viagra drug prices cialis coupons buy cialis usa viagra naturel levitra dosages viagra cost uk viagra pills online tadalafil vs viagra sildenafil buy buy viagra in england viagra soft tabs 100mg cialis costs viagra tabs buy viagra cod sildenafil msds viagra generic name liquid tadalafil citrate tadalafil 20mg buy cialis soft tabs levitra without prescription buy viagra online cheap cialis fedex viagra pills uk cialis st revatio drug generic levitra vardenafil levitra online order cheap cialis online cialis generic viagra cialis no prescription needed viagra cheap prescription suhagra 100 levitra online no prescription cialis without a prescription generic viagra fast shipping buy tadalafil liquid cialis levitra faq buying viagra online legal oral jelly sildenafil viagra oysters cialis sales canada cheap tadalafil online canadian viagra online viagra en espanol viagra retail price cialis generic pill viagra mexico viagra vs generic cialis for sale in uk viagra clock tadalafil india cheap lovegra buy viagra over the counter buy viagra in montreal suhagra 50 viagra costs cialis 30 mg buy levitra without prescription levitra results viagra pill costume 10mg levitra caverta sildenafil citrate buy levitra 20 mg drug prescription viagra cialis online discount kamagra from india cialis 50 mg viagra info generic viagra fda cialis how long does it last cialis sample pack kamagra online kamagra on line cheap generic levitra cialis fast delivery ebay viagra mexico viagra cialis generic tadalafil buy cialis in uk cialis medication viagra kaufen vardenafil india daily cialis discount viagra viagra money order viagra products buy viagra without a prescription order viagra prescription viagra sale sildenafil citrate soft tabs buy cialis super active pakistan viagra viagra package viagra forums viagra uk delivery cialis 100 mg viagra 50mg price female viagra pills viagra buy viagra generics viagra mechanism of action buy viagra in dubai cheap cialis generic cialis fda levitra prices cialis prescription viagra sale online sildenafil citrate online levitra experiences viagra buying buy generic cialis viagra supplement tadalafil best price viagra australia no prescription sildenafil wikipedia viagra paypal caverta 100mg tablets viagra caddis buy vigora apcalis gel sildenafil citrate price cialis where to buy viagra vs. cialis buy viagra online australia viagra for sale in ireland viagra generic online viagra ingredients levitra online prescription cialis drug prescription viagra england buy viagra with no prescription natural viagra cheap levitra india buy cialis cheap sale cialis buy viagra usa online order viagra cialis online order generic revatio cialis online buy viagra alternatives viagra nicknames cheap viagra prices buy viagra in singapore generic viagra caverta generic online viagra generic cialis price cialis 20 mg drug cialis discount coupon generic viagra sale viagra sell generic viagra 100mg levitra generic sildenafil citrate 50mg buy cialis professional buy cialis pill revatio for ed cialis gels revatio generic generic viagra online uk viagra 50 kamagra viagra levitra india viagra singapore viagra by intas pharma tadalafil cheap buy tadalafil india viagra sydney viagra price in india buy viagra now online cialis overnight delivery uk cialis viagra supplier vardenafil otc viagra drug information viagra soft generic revatio price sildenafil tadalafil vardenafil buying viagra in canada buy sildenafil citrate cialis canada buy online viagra securely tadalafil soft buy viagra 25mg buy viagra online in the uk viagra ohne rezept viagra toronto viagra italia viagra europe viagra discounts cialus levitra online pharmacy buy cheap kamagra viagra canada pharmacy viagra newsletters low cost cialis buying viagra viagra online reviews viarga viagra generico jelly viagra cialis and viagra together tadalafil manufacturer viagra card revatio prescribing information india viagra compare levitra cialis generic cialis tadalafil buy viagra soft tabs online buy viagra 50 mg viagra super active+ viagra 6 free samples generic viagra overnight cialis online cheap viagra generic generic viagra no prescription viagra internet cialis next day cheap cialis professional order viagra without prescription online price viagra cialis soft tablets ordering viagra online viagra for sale in australia kamagra wholesale bestellen online viagra buy viagra from uk cialis online pill generic viagra india free viagra samples online viagra manufacturer levitra order viagra to buy cheap cialis online purchase cialis cost viagra jellys viagra buy uk viagra impotence pill tadalafil drug viagra ontario overnight cialis viagra gel buy cialis in canada indian equivalent of viagra levitra directions levitra 20 expired viagra sildenafil citrate prescription cialis silagra 50 levitra 10mg price generic viagra soft tabs buy viagra ireland prescriptions viagra viagra 100mg kamagra in uk cialis soft cheap levitra online buy viagra in britain viagra generic cheap cheapest viagra in uk silagra uk buy viagra in uk levitra generic name viagra equivalents viagra men viagra frequency cialis under tongue purchase viagra on line viagra jelly cialis trial pack buy viagra in japan viagra discount tadalafil price viagra uk law free viagra pills buy cialis soft vardenafil generic viagra 50 mg counterfeit viagra cialis split cialis cheapest viagra purchase australia viagra jersey viagra expired cialias erectalis 20 buy viagra in los angeles calis tadalafil soft tabs cialis samples sample viagra cialis prescribing viagra brands online viagra canada sildenafil for sale generic viagra does it work cialis kaufen