SQL Statement                        

Connecting to and Disconnecting from the Server

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 4.0.14-log

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

mysql>

Loading Data into a Table

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
     -> LINES TERMINATED BY '\r\n';

SQL Statement

1. mysql>create database db_name;   // create database;

2. mysql>create table t_name(id int , name char(20),birth DATE , owner VARCHAR(20));
 							    //varchar is string 1-255 length
 							    //Date= yyyy-mm-dd	

3. mysql>create table t_name( 
	id int primary key,
	name varchar(n),         //create table
	email varchar(n),
	birthday text,
        );


4. mysql>insert into tabb values (1,"memphis" ) ,(2, " Atlanta");    

5. mysql> SELECT
    -> USER()
    -> \c                   //  \c means cancel and return to mysql>
   mysql>

6. mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
   '> '\c
   mysql>   

7. mysql> UPDATE table_name SET birth = '1989-08-31' WHERE name = 'Bowser';  //update data

8. mysql> select  distinct name from tabb;   //delete duplicate record 

9. mysql> select * from tabb ORDER BY name; // record order ascending by name

10. mysql> select * from tabb ORDER BY name DESC; //descending record by name

11. mysql> SELECT * FROM pet WHERE name LIKE 'b%';  //pattern matching starting b

12. mysql> SELECT * FROM pet WHERE name LIKE '%fy';  //pattern matching ending fy

13. mysql> SELECT * FROM pet WHERE name LIKE '%w%';  //pattern matching containing w

14. mysql> SELECT * FROM pet WHERE name LIKE '_____';  //pattern matching name has ----- (5 characters)

15. mysql> SELECT * FROM pet WHERE name REGEXP '^b';  // name start at b

13. mysql> SELECT * FROM pet WHERE name REGEXP 'fy$'; // name ends at fy

14. mysql> SELECT COUNT(*) FROM pet grouped by species; // how may rows in pet table

15. mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;   //group all record for each owner
 
	+--------+----------+
	| owner  | COUNT(*) |
	+--------+----------+
	| Benny  |        2 |
	| Diane  |        2 |
	| Gwen   |        3 |
	| Harold |        2 |
	+--------+----------+

16. mysql>select * from table_name;   //view table