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;
------
posted by ஆமாச்சு at 8:20 PM
|
1 comments
