Tag Archives: MySQL

MySQLi

MySQLi is an improved version of the older PHP MySQL driver, offering various benefits.

The developers of the PHP programming language recommend using MySQLi when dealing with MySQL server versions 4.1.3 and newer

The MySQLi extension provides various benefits with respect to its predecessor, the most prominent of which  are:

  • An object-oriented interface
  • Object Mapping
  • Support for prepared statements
  • Support for multiple statements
  • Support for transactions
  • Enhanced debugging support
  • Embedded server support
  • More powerful Functionality

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.

MySQL REGEXP

A regular expression is a powerful way of specifying a pattern for a complex search.

MySQL uses Henry Spencer’s implementation of regular expressions, which is aimed at conformance with POSIX 1003.2. MySQL uses the extended version to support pattern-matching operations performed with the REGEXP operator in SQL statements.