MYSQL Explorations....

To create a database:

mysql> CREATE DATABASE smrpnm;

--> database name is case sensitive, unlike SQL keywords.

We should use the db...to make a database the current one...

mysql> USE smrpnm;

Alrernate at shell level, # mysql -h host -u user -p smrpnm
password: *********** (or)
# mysql -h host -u user -pmypassword smrpnm // not recommended
---------------------
No tables are present, so the following will not return anything...

mysql> SHOW TABLES;

So time to create a table now,

mysql> CREATE TABLE members (name VARCHAR(20), dob DATE, email VARCHAR(30), bg VARCHAR(20), mobile VARCHAR(14), locality VARCHAR(20), bagh VARCHAR(20), phone VARCHAR(14));

--> Character length: 1-255 char.

---------------------
But now, give,

mysql> SHOW TABLES;

To verify that the table was created as expected..

mysql> DESCRIBE members;

---------------------
Loading data:

mysql follows yyyy-mm--dd format

A single record at a time...

mysql> INSERT INTO members VALUES ('Sample',NULL,'sample@sample.com',NULL,NULL,'Vadapalani','Vadapalani','2222222');

Load data provides chances to get bulk data to the table..

mysql> LOAD DATA LOCAL INFILE '/root/Desktop/loadtosql.txt' INTO TABLE members;

--> In loadtosql.txt each value of a record seperated by tab witn \N representing NULL. In window '\n\r' has to be used as line seperator.
---------------------
SELECT statement:

Syntax:

SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

mysql> SELECT * FROM members;

To update a record, which has got erroneous value in table....or replace a NULL value....

mysql> UPDATE members SET name = 'Seshadri' Where locality = 'Ashok Nagar';
---------------------

To DELETE record from a table....

mysql> DELETE FROM members;

---------------------

More SELECT staements examples:

Option: All columns

mysql> SELECT * from members WHERE locality = 'T Nagar';

mysql> SELECT * from members WHERE bagh = 'Medavakkam' AND locality = 'Medavakkam';

mysql> SELECT * from members WHERE bagh = 'Medavakkam' OR bagh = "Mylapore";

mysql> SELECT * from members WHERE (bagh = 'Medavakkam' OR bagh = "Mylapore") AND (bg = 'B+ve');
------
Option: Few columns

mysql> SELECT name, mobile from members WHERE locality = 'T Nagar';

Selecting distinct value to get displayed when there is a repitition,'

mysql> SELECT DISTINCT bg FROM members;

mysql> SELECT name from members WHERE (bagh = 'Medavakkam' OR bagh = "Mylapore") AND (bg = 'B+ve');
------
Sorting:

mysql> SELECT locality, name, mobile from members ORDER BY locality;

For desecding order sort..

mysql> SELECT locality, name, mobile from members ORDER BY locality DESC;

Multi-column sort,

mysql> SELECT locality, name, mobile from members ORDER BY locality, name;
------