Exploring MySQL

Before jumping on to project, i decided to do some data manipulation in MySQL so that i have better understanding of it syntax. This turn out to be good decision as i have work on any Database software for about 6 month, after a long hit and trial i learn to following things about MySQL database:-

• MySQL Data Types
  
  
  
  • Numeric data types
    
    
    
    • INT
      
    • TINYINT
      
    • SMALLINT
      
    • MEDIUMINT
      
    • BIGINT
      
    • FLOAT(M,D)
      
    • DOUBLE(M,D)
      
    • DECIMAL(M,D)
      
      Note: term signed and unsigned can be used in numeric data types
      
    
    
  • Date and Time types
    
    
    
    • DATE
      
    • DATETIME
      
    • TIMESTAMP
      
    • TIME
      
    • YEAR(M)
      
    
    
  • String Types
    
    
    
    • CHAR(M)
      
    • VARCHAR (M)
      
    • BLOB or TEXT
      
    • TINYBLOB or TINYTEXT
      
    • MEDIUMBLOB or MEDIUMTEXT
      
    • LONGBLOB or LONGTEXT
      
    • ENUM
      
    
    
    
  
  
• First thing is to create our database which can be done by executing following command:-
  CREATE DATABASE [IF NOT EXISTS] "database name"
  [[default] CHARACTER SET "character set name"]
  [[default] COLLATE "collation name"]
  (Sheldon, Robert and Moes Geoff 2005,"Beginning MySQL",JohnWiley and sons,pg 140).
  
  or simply:-
  
  CREATE DATABASE "database name";
  if CHARACTER SET AND COLLATE are not define that MySQL will use the default value.
  
  
• use this command is used to change database.The syntax of this is:-
  mysql> use datbase_name
  .
  Note: this command does not followed by semi colon(;) like other commands
  
  
  
• For creating table the command is:-
  mysql>CREATE TABLE table_name (column name column type);
  
  Note: table would be created in the selected database,make sure the right database is selected.
  
• For entering data in to table the command is:-
  
  mysql>INSERT INTO table_name (column list) VALUES (column values);
  
  
• SELECT command is used to retrieve data from the tables.
  
  mysql>SELECT expressions_and_columns FROM table_name
  [WHERE some_condition_is_true]
  [ORDER BY some_column [ASC | DESC]
  [LIMIT offset, rows]
  
  ORDERBY by column_name can be used with select to display result in specified sorted order.
  
  
• Selecting from a Multiple Tables can be done with the following command:-
  mysql> (select column list) from (table name) where condition;
  
  
• For UPDATING the records is done by:-
  
  
  
  • mysql> update table_name set Column_name = Value;
    
    
  • For conditional Updates the syntax is:-
    
    mysql> update Table_name set column_name where condition;
    
    
  
  
• Replacing a RECORD can be done by:-
  
  mysql> replace into table_name values ();
  
  
• Deleting a record :-
  mysql> DELETE from table_name [where some_condition_is_true][Limit rows]( Meloni , Julie C. 2005," Sams teach yourself PHP,MysQL and Apache all in one", Sams , ed:2.)
  
  
• Length and concatenation function
  
  
  
  • length(), octet_length().char_length(), and character_length() all calculate number of characters in string. syntax is:-
    
    mysql> select length ('string';
    
    
    
  • To concatenate two string, MySQL have concat() function,
    To concatenate a string with space the function is concat_ws();
    
    
  • Trimming and padding
    rtrim() and ltrim() remove the white spaces from the right and left of the string respectively. rpad() and lpad() adds a string to the right and left of the string respectively.
    
    
  • To locate a part of string in another string the MySQL has Locate(), syntax is:-
    
    mysql> select locate( 'string to be find', ' from this string', position from where to start searching);
    
    
    
  • To extract a substring from a target string, mysql has:-
    Substring(), Left() and Right() functions:-
    
    
    mysql> select substring("target string", starting position, end position);
    
    mysql> select left("target string", till this position from left);
    
    mysql> select right("target string", till this position from right);
    
    
  • String modification functions
    For transforming a string into lower and upper case lcase() and ucase() are available:-
    mysql> select lcase('string' or condition)
    
    mysql> select ucase('string' or condition)
    
    MySQL have repeat() for repeating a string number of time and replace() from replacing a occurrence of given string from target string.
    
    mysql> select repeat(" string", number of time);
    
    mysql> select replace ( ' given string', ' string to be replace',' string with which to replace');
    
    
  
  
  
  
  

( Meloni , Julie C. 2005," Sams teach yourself PHP,MysQL and Apache all in one", Sams , ed:2.)
(Sheldon, Robert and Moes Geoff 2005,"Beginning MySQL",JohnWiley and sons).