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.
ENUM is a string object used to specify set of predefined values and that can be used during table creation.
CREATE table phpcodez(name ENUM(‘Average’,’Good’,’Excellent’));
CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.
The default port for MySQL server is 3306.
The following list shows the most important properties of MySQL.
- Clients and Tools
- Scalability and Limits
- Data Types
- Client/Server Architecture
- Stored procedures
- SQL compatibility
- User interface
- Full-text search
- Foreign key constraints
- GIS functions
- Programming languages
- Platform independence
- Relational Database System
We can delete a database using DROP DATABASE statement.
DROP DATABASE DATABASE_NAME
mysql> drop database mage;
Query OK, 459 rows affected (36.63 sec)
An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like users not being able to connect.
MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.
CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.
FORMAT(X, D) – Formats the number X to D significant digits.
CURRDATE(), CURRTIME() – Returns the current date or time.
NOW() – Returns the current date and time as one value.
MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.
HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.
DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age
SUBTIMES(A, B) – Determines the difference between two times.
FROMDAYS(INT) – Converts an integer number of days into a date value.
Following are Non-Standard string types:
SIX triggers are allowed in MySql table. They are as follows:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE and
- AFTER DELETE