Category Archives: My SQL

phpMyAdmin

phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL with the use of a web browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions.

Install phpmyadmin

1) Execute sudo apt-get install phpmyadmin
2) Edit the file /etc/apache2/apache2.conf. and include the line Include /etc/phpmyadmin/apache.conf
3) Restart Apache sudo /etc/init.d/apache2 reload

Features

  • Web interface
  • MySQL database management
  • Import data from CSV and SQL
  • Export data to various formats: CSV, SQL, XML, PDF (via the TCPDF library), ISO/IEC 26300 – OpenDocument Text and Spreadsheet, Word, Excel, LaTeX and others
  • Administering multiple servers
  • Creating PDF graphics of the database layout
  • Creating complex queries using Query-by-Example (QBE)
  • Searching globally in a database or a subset of it
  • Transforming stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link
  • Live charts to monitor MySQL server activity like connections, processes, CPU/Memory usage, etc.
  • Working with different operating systems.

 

 

MySQL SET

A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (“,”). A consequence of this is that SET member values should not themselves contain commas.

Example

mysql> CREATE TABLE myset (col SET(‘a’, ‘b’, ‘c’, ‘d’));
Query OK, 0 rows affected (0.43 sec)

mysql> select * from myset;
Empty set (0.20 sec)

mysql> insert into myset value(‘p’);
Query OK, 1 row affected, 1 warning (0.23 sec)

mysql> select * from myset;
+——+
| col |
+——+
| |
+——+
1 row in set (0.10 sec)

mysql> insert into myset value(‘a’);
Query OK, 1 row affected (0.18 sec)

mysql> select * from myset;
+——+
| col |
+——+
| |
| a |
+——+
2 rows in set (0.00 sec)

mysql> insert into myset value(‘p,a’);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> select * from myset;
+——+
| col |
+——+
| |
| a |
| a |
+——+
3 rows in set (0.00 sec)

MySQL CHAR And VARCHAR

Following are the differences between CHAR and VARCHAR:

  • CHAR and VARCHAR types differ in storage and retrieval
  • CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255
  • When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.