SQL :: Standard Query Language :: Basic commands and Advances

Login & Change / Set password to MySQL :

$ mysql -h hostname -u username -p

$ mysql -u username -p

$ mysql -u username

$ mysqladmin -u root password
Set password for root
SQL : Standard Query Language :

:: Show / Delete / Create Databases ::

mysql> SHOW DATABASES;
show list database in mysql

mysql> DROP DATABASE database_name;
delete database in mysql

mysql> CREATE DATABASE database_name;
create new database


:: Cancel command typing ::

mysql> .....\c
cancel the command while type

:: Logout mysql ::

mysql> quit
to logout of mysql

:: Using Database & Create / Show / Delete Table ::

mysql> USE database_name;
choose database to use

mysql> CREATE TABLE table_name(
-> field_name1 field_type,
-> field_name2 field_type,
-> ........ );
Create a table with fields,
field_type : CHAR(N),VARCHAR(N),INT,DOUBLE,FLOAT,DATE....

mysql> SHOW TABLES;
To show all table in database you selected.

mysql> DROP TABLE table_name;
To delete table in database you selected.





:: Inserting Data to Table ::

mysql> INSERT INTO table_name SET
-> field_name1 = value1,
-> field_name2 = value2,
-> .....
-> ;
OR

mysql> INSERT INTO table_name
-> ( field_name1 , field_name2 , .... )
-> VALUES ( value1 , value2 , ....);

OR

mysql> INSERT INTO table_name VALUES
-> ( field_name1 = value1 ,
-> field_name2 = value2 ,
-> .....
-> );
Ex.
mysql> INSERT INTO person VALUES
-> ( birth = "1987-06-12" ,
-> name = "MR.KEOVESSNA" ,
-> sex = 'm',
-> id = 53160083 );

:: Viewing Stored Data ::

mysql> SELECT * FROM person;
show all fields' values in table person.

mysql> SELECT * FROM person WHERE name = "MR.KEOVESSNA";
show all field's value of table person which name = "MR.KEOVESSNA".

mysql> SELECT name FORM person WHERE sex = 'm';
show all name value of table person which sex = 'm'.

mysql> SELECT name,sex,birth FROM person;
show all name,sex,birth values of table person.

mysql> SELECT ID, LEFT(name,5) FROM person;
show id number and 5 characters from name field at from left of person.
Ex. name = "123456789" => LEFT(name,5) = "12345" .

mysql> SELECT COUNT(*) FORM person;
To count the number of result returned.

mysql> SELECT name FORM person WHERE name LIKE "%EO%";
Show name which are contented ..EO.. .

mysql> SELECT name FORM person WHERE sex = 'f' AND birth < "1980-1-1" ; Show name which are sex = 'f' and birth under year 1980. Logical operator : AND , OR Operator : = , < , > , <= , >= , !=

mysql> SELECT name FORM person WHERE
-> name IN (SELECT name FROM person WHERE sex = 'f')
-> AND name IN (SELECT name FROM person WHERE sex = 'm');
Find name from set of 2 sets who are sex = 'm' and 'f' to Intersection.
IN use to make as Set.



mysql> SELECT * FROM pet
> ORDER BY date;
To show all data in table pet by order by field date

:::: Cartesian Product :::
 
Ex. we have a table pet in manageries database.
if want to show the who fet both bird and dog

mysql> SELECT p1.owner FROM pet AS p1 , pet As p2
// pet AS p1 , pet AS p2 : p1 x p2 ( cartesian product which p1,p2 // from pet table ).