Category: mariadb

MariaDB – Transactions ”; Previous Next Transactions are sequential group operations. They function as a single unit, and do not terminate until all operations within the group execute successfully. A single failure in the group causes the entire transaction to fail, and causes it to have no impact on the database. Transactions conform to ACID (Atomicity, Consistency, Isolation, and Durability) − Atomicity − It ensures the success of all operations by aborting on failures and rolling back changes. Consistency − It ensures the database applies changes on a successful transaction. Isolation − It enables independent transactions operation of transactions. Durability − It ensures the persistence of a successful transaction in the event of system failure. At the head of a transaction statement is the START TRANSACTION statement followed by COMMIT and ROLLBACK statements − START TRANSACTION begins the transaction. COMMIT saves changes to data. ROLLBACK ends the transaction, destroying any changes. On a successful transaction, COMMIT acts. On a failure, ROLLBACK acts. Note − Some statements cause an implicit commit, and they also cause an error when used within transactions. Examples of such statements include, but are not limited to CREATE, ALTER, and DROP. MariaDB transactions also include options like SAVEPOINT and LOCK TABLES. SAVEPOINT sets a restore point to utilize with ROLLBACK. LOCK TABLES allows controlling access to tables during sessions to prevent modifications during certain time periods. The AUTOCOMMIT variable provides control over transactions. A setting of 1 forces all operations to be considered successful transactions, and a setting of 0 causes persistence of changes to only occur on an explicit COMMIT statement. Structure of a Transaction The general structure of a transaction statement consists of beginning with START TRANSACTION. The next step is inserting one or more commands/operations, inserting statements that check for errors, inserting ROLLBACK statements to manage any errors discovered and finally inserting a COMMIT statement to apply changes on successful operations. Review the example given below − START TRANSACTION; SELECT name FROM products WHERE manufacturer = ”XYZ Corp”; UPDATE spring_products SET item = name; COMMIT; Print Page Previous Next Advertisements ”;

MariaDB – Temporary Tables ”; Previous Next Some operations can benefit from temporary tables due to speed or disposable data. The life of a temporary table ends at the termination of a session whether you employ them from the command prompt, with a PHP script, or through a client program. It also does not appear in the system in a typical fashion. The SHOW TABLES command will not reveal a list containing temporary tables. Create a Temporary Table The TEMPORARY keyword within a CREATE TABLE statement spawns a temporary table. Review an example given below − mysql>CREATE TEMPORARY TABLE order ( item_name VARCHAR(50) NOT NULL , price DECIMAL(7,2) NOT NULL DEFAULT 0.00 , quantity INT UNSIGNED NOT NULL DEFAULT 0 ); In creating a temporary table, you can clone existing tables, meaning all their general characteristics, with the LIKE clause. The CREATE TABLE statement used to spawn the temporary table will not commit transactions as a result of the TEMPORARY keyword. Though temporary tables stand apart from non-temporary and drop at the end of a session, they may have certain conflicts − They sometimes conflict with ghost temporary tables from expired sessions. They sometimes conflict with shadow names of non-temporary tables. Note − Temporary tables are permitted to have the same name as an existing non-temporary table because MariaDB views it as a difference reference. Administration MariaDB requires granting privileges to users for creating temporary tables. Utilize a GRANT statement to give this privilege to non-admin users. GRANT CREATE TEMPORARY TABLES ON orders TO ”machine122”@”localhost”; Drop a Temporary Table Though temporary tables are essentially removed at the end of sessions, you have the option to delete them. Dropping a temporary table requires the use of the TEMPORARY keyword, and best practices suggest dropping temporary tables before any non-temporary. mysql> DROP TABLE order; Print Page Previous Next Advertisements ”;

MariaDB – Indexes & Statistics Tables ”; Previous Next Indexes are tools for accelerating record retrieval. An index spawns an entry for each value within an indexed column. There are four types of indexes − Primary (one record represents all records) Unique (one record represents multiple records) Plain Full-Text (permits many options in text searches). The terms “key” and “index” are identical in this usage. Indexes associate with one or more columns, and support rapid searches and efficient record organization. When creating an index, consider which columns are frequently used in your queries. Then create one or multiple indexes on them. In addition, view indexes as essentially tables of primary keys. Though indexes accelerate searches or SELECT statements, they make insertions and updates drag due to performing the operations on both the tables and the indexes. Create an Index You can create an index through a CREATE TABLE…INDEX statement or a CREATE INDEX statement. The best option supporting readability, maintenance, and best practices is CREATE INDEX. Review the general syntax of Index given below − CREATE [UNIQUE or FULLTEXT or…] INDEX index_name ON table_name column; Review an example of its use − CREATE UNIQUE INDEX top_sellers ON products_tbl product; Drop an Index You can drop an index with DROP INDEX or ALTER TABLE…DROP. The best option supporting readability, maintenance, and best practices is DROP INDEX. Review the general syntax of Drop Index given below − DROP INDEX index_name ON table_name; Review an example of its use − DROP INDEX top_sellers ON product_tbl; Rename an Index Rename an index with the ALTER TABLE statement. Review its general syntax given below − ALTER TABLE table_name DROP INDEX index_name, ADD INDEX new_index_name; Review an example of its use − ALTER TABLE products_tbl DROP INDEX top_sellers, ADD INDEX top_2016sellers; Managing Indexes You will need to examine and track all indexes. Use SHOW INDEX to list all existing indexes associated with a given table. You can set the format of the displayed content by using an option such as “G”, which specifies a vertical format. Review the following example − mysql > SHOW INDEX FROM products_tblG Table Statistics Indexes are used heavily to optimize queries given the faster access to records, and the statistics provided. However, many users find index maintenance cumbersome. MariaDB 10.0 made storage engine independent statistics tables available, which calculate data statistics for every table in every storage engine, and even statistics for columns that are not indexed. Print Page Previous Next Advertisements ”;

MariaDB – Alter Command ”; Previous Next The ALTER command provides a way to change an existing table”s structure, meaning modifications like removing or adding columns, modifying indices, changing data types, or changing names. ALTER also waits to apply changes when a metadata lock is active. Using ALTER to Modify Columns ALTER paired with DROP removes an existing column. However, it fails if the column is the only remaining column. Review the example given below − mysql> ALTER TABLE products_tbl DROP version_num; Use an ALTER…ADD statement to add columns − mysql> ALTER TABLE products_tbl ADD discontinued CHAR(1); Use the keywords FIRST and AFTER to specify placement of the column − ALTER TABLE products_tbl ADD discontinued CHAR(1) FIRST; ALTER TABLE products_tbl ADD discontinued CHAR(1) AFTER quantity; Note the FIRST and AFTER keywords only apply to ALTER…ADD statements. Furthermore, you must drop a table and then add it in order to reposition it. Change a column definition or name by using the MODIFY or CHANGE clause in an ALTER statement. The clauses have similar effects, but utilize substantially different syntax. Review a CHANGE example given below − mysql> ALTER TABLE products_tbl CHANGE discontinued status CHAR(4); In a statement using CHANGE, specify the original column and then the new column that will replace it. Review a MODIFY example below − mysql> ALTER TABLE products_tbl MODIFY discontinued CHAR(4); The ALTER command also allows for changing default values. Review an example − mysql> ALTER TABLE products_tbl ALTER discontinued SET DEFAULT N; You can also use it to remove default constraints by pairing it with a DROP clause − mysql> ALTER TABLE products_tbl ALTER discontinued DROP DEFAULT; Using ALTER to Modify Tables Change table type with the TYPE clause − mysql> ALTER TABLE products_tbl TYPE = INNODB; Rename a table with the RENAME keyword − mysql> ALTER TABLE products_tbl RENAME TO products2016_tbl; Print Page Previous Next Advertisements ”;

MariaDB – Order By Clause ”; Previous Next The ORDER BY clause, as mentioned in previous discussions, sorts the results of a statement. It specifies the order of the data operated on, and includes the option to sort in ascending (ASC) or descending (DESC) order. On omission of order specification, the default order is ascending. ORDER BY clauses appear in a wide variety of statements such as DELETE and UPDATE. They always appear at the end of a statement, not in a subquery or before a set function, because they operate on the final resulting table. You also cannot use an integer to identify a column. Review the general syntax of the ORDER BY clause given below − SELECT field, field2,… [or column] FROM table_name, table_name2,… ORDER BY field, field2,… ASC[or DESC] Use an ORDER BY clause either at the command prompt or within a PHP script. The Command Prompt At the command prompt, simply use a standard command − root@ host# mysql -u root -p password; Enter password:******* mysql> use PRODUCTS; Database changed mysql> SELECT * from products_tbl ORDER BY product_manufacturer ASC +————-+—————-+———————-+ | ID_number | Nomenclature | product_manufacturer | +————-+—————-+———————-+ | 56789 | SuperBlast 400 | LMN Corp | +————-+—————-+———————-+ | 67891 | Zoomzoom 5000 | QFT Corp | +————-+—————-+———————-+ | 12347 | Orbitron 1000 | XYZ Corp | +————-+—————-+———————-+ PHP Script Using Order By Clause Utilize the mysql_query() function, once again, in statements employing the ORDER BY clause − <?php $dbhost = ”localhost:3036”; $dbuser = ”root”; $dbpass = ”rootpassword”; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(”Could not connect: ” . mysql_error()); } $sql = ”SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl ORDER BY product_manufacturer DESC”; mysql_select_db(”PRODUCTS”); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die(”Could not get data: ” . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) { echo “Product ID :{$row[”product_id”]} <br> “. “Name: {$row[”product_name”]} <br> “. “Manufacturer: {$row[”product_manufacturer”]} <br> “. “Ship Date : {$row[”ship_date”]} <br> “. “——————————–<br>”; } echo “Fetched data successfullyn”; mysql_close($conn); ?> On successful data retrieval, you will see the following output − Product ID: 12347 Nomenclature: Orbitron 1000 Manufacturer: XYZ Corp Ship Date: 01/01/17 ———————————————- Product ID: 67891 Nomenclature: Zoomzoom 5000 Manufacturer: QFT Corp Ship Date: 01/01/17 ———————————————- Product ID: 56789 Nomenclature: SuperBlast 400 Manufacturer: LMN Corp Ship Date: 01/04/17 ———————————————- mysql> Fetched data successfully Print Page Previous Next Advertisements ”;

MariaDB – Join ”; Previous Next In previous discussions and examples, we examined retrieving from a single table, or retrieving multiple values from multiple sources. Most real-world data operations are much more complex, requiring aggregation, comparison, and retrieval from multiple tables. JOINs allow merging of two or more tables into a single object. They are employed through SELECT, UPDATE, and DELETE statements. Review the general syntax of a statement employing a JOIN as shown below − SELECT column FROM table_name1 INNER JOIN table_name2 ON table_name1.column = table_name2.column; Note the old syntax for JOINS used implicit joins and no keywords. It is possible to use a WHERE clause to achieve a join, but keywords work best for readability, maintenance, and best practices. JOINs come in many forms such as a left join, right join, or inner join. Various join types offer different types of aggregation based on shared values or characteristics. Employ a JOIN either at the command prompt or with a PHP script. The Command Prompt At the command prompt, simply use a standard statement − root@host# mysql -u root -p password; Enter password:******* mysql> use PRODUCTS; Database changed mysql> SELECT products.ID_number, products.Nomenclature, inventory.inventory_ct FROM products INNER JOIN inventory ON products.ID_numbeer = inventory.ID_number; +————-+—————-+—————–+ | ID_number | Nomenclature | Inventory Count | +————-+—————-+—————–+ | 12345 | Orbitron 4000 | 150 | +————-+—————-+—————–+ | 12346 | Orbitron 3000 | 200 | +————-+—————-+—————–+ | 12347 | Orbitron 1000 | 0 | +————-+—————-+—————–+ PHP Script Using JOIN Use the mysql_query() function to perform a join operation − <?php $dbhost = ”localhost:3036”; $dbuser = ”root”; $dbpass = ”rootpassword”; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(”Could not connect: ” . mysql_error()); } $sql = ”SELECT a.product_id, a.product_manufacturer, b.product_count FROM products_tbl a, pcount_tbl b WHERE a.product_manufacturer = b.product_manufacturer”; mysql_select_db(”PRODUCTS”); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die(”Could not get data: ” . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) { echo “Manufacturer:{$row[”product_manufacturer”]} <br&gt “. “Count: {$row[”product_count”]} <br&gt “. “Product ID: {$row[”product_id”]} <br&gt “. “——————————–<br&gt”; } echo “Fetched data successfullyn”; mysql_close($conn); ?> On successful data retrieval, you will see the following output − ID Number: 12345 Nomenclature: Orbitron 4000 Inventory Count: 150 ————————————– ID Number: 12346 Nomenclature: Orbitron 3000 Inventory Count: 200 ————————————– ID Number: 12347 Nomenclature: Orbitron 1000 Inventory Count: 0 ————————————– mysql> Fetched data successfully Print Page Previous Next Advertisements ”;

MariaDB – Select Query ”; Previous Next In this chapter, we will learn how to select data from a table. SELECT statements retrieve selected rows. They can include UNION statements, an ordering clause, a LIMIT clause, a WHERE clause, a GROUP BY…HAVING clause, and subqueries. Review the following general syntax − SELECT field, field2,… FROM table_name, table_name2,… WHERE… A SELECT statement provides multiple options for specifying the table used − database_name.table_name table_name.column_name database_name.table_name.column_name All select statements must contain one or more select expressions. Select expressions consist of one of the following options − A column name. An expression employing operators and functions. The specification “table_name.*” to select all columns within the given table. The character “*” to select all columns from all tables specified in the FROM clause. The command prompt or a PHP script can be employed in executing a select statement. The Command Prompt At the command prompt, execute statements as follows − root@host# mysql -u root -p password; Enter password:******* mysql> use PRODUCTS; Database changed mysql> SELECT * from products_tbl +————-+—————+ | ID_number | Nomenclature | +————-+—————+ | 12345 | Orbitron 4000 | +————-+—————+ PHP Select Script Employ the same SELECT statement(s) within a PHP function to perform the operation. You will use the mysql_query() function once again. Review an example given below − <?php $dbhost = ”localhost:3036”; $dbuser = ”root”; $dbpass = ”rootpassword”; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(”Could not connect: ” . mysql_error()); } $sql = ”SELECT product_id, product_name,product_manufacturer, ship_date FROM products_tbl”; mysql_select_db(”PRODUCTS”); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die(”Could not get data: ” . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) { echo “Product ID :{$row[”product_id”]} <br> “. “Name: {$row[”product_name”]} <br> “. “Manufacturer: {$row[”product_manufacturer”]} <br> “. “Ship Date : {$row[”ship_date”]} <br>”. “——————————–<br>”; } echo “Fetched data successfullyn”; mysql_close($conn); ?> On successful data retrieval, you will see the following output − Product ID: 12345 Nomenclature: Orbitron 4000 Manufacturer: XYZ Corp Ship Date: 01/01/17 ———————————————- Product ID: 12346 Nomenclature: Orbitron 3000 Manufacturer: XYZ Corp Ship Date: 01/02/17 ———————————————- mysql> Fetched data successfully Best practices suggest releasing cursor memory after every SELECT statement. PHP provides the mysql_free_result() function for this purpose. Review its use as shown below − <?php $dbhost = ”localhost:3036”; $dbuser = ”root”; $dbpass = ”rootpassword”; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(”Could not connect: ” . mysql_error()); } $sql = ”SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl”; mysql_select_db(”PRODUCTS”); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die(”Could not get data: ” . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_NUM)) { echo “Product ID :{$row[0]} <br> “. “Name: {$row[1]} <br> “. “Manufacturer: {$row[2]} <br> “. “Ship Date : {$row[3]} <br> “. “——————————–<br>”; } mysql_free_result($retval); echo “Fetched data successfullyn”; mysql_close($conn); ?> Print Page Previous Next Advertisements ”;

MariaDB – Null Values ”; Previous Next When working with NULL values, remember they are unknown values. They are not empty strings or zero, which are valid values. In table creation, column specifications allow for setting them to accept null values, or reject them. Simply utilize a NULL or NOT NULL clause. This has applications in cases of missing record information like an ID number. User-defined variables have a value of NULL until explicit assignment. Stored routine parameters and local variables allow setting a value of NULL. When a local variable has no default value, it has a value of NULL. NULL is case-insensitive, and has the following aliases − UNKNOWN (a boolean value) N NULL Operators Standard comparison operators cannot be used with NULL (e.g., =, >, >=, <=, <, or !=) because all comparisons with a NULL value return NULL, not true or false. Comparisons with NULL or possibly containing it must use the “<=>” (NULL-SAFE) operator. Other available operators are − IS NULL − It tests for a NULL value. IS NOT NULL − It confirms the absence of a NULL value. ISNULL − It returns a value of 1 on discovery of a NULL value, and 0 in its absence. COALESCE − It returns the first non-NULL value of a list, or it returns a NULL value in the absence of one. Sorting NULL Values In sorting operations, NULL values have the lowest value, so DESC order results in NULL values at the bottom. MariaDB allows for setting a higher value for NULL values. There are two ways to do this as shown below − SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1; The other way − SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC; NULL Functions Functions generally output NULL when any parameters are NULL. However, there are functions specifically designed for managing NULL values. They are − IFNULL() − If the first expression is not NULL it returns it. When it evaluates to NULL, it returns the second expression. NULLIF() − It returns NULL when the compared expressions are equal, if not, it returns the first expression. Functions like SUM and AVG ignore NULL values. Inserting NULL Values On insertion of a NULL value in a column declared NOT NULL, an error occurs. In default SQL mode, a NOT NULL column will instead insert a default value based on data type. When a field is a TIMESTAMP, AUTO_INCREMENT, or virtual column, MariaDB manages NULL values differently. Insertion in an AUTO_INCREMENT column causes the next number in the sequence to insert in its place. In a TIMESTAMP field, MariaDB assigns the current timestamp instead. In virtual columns, a topic discussed later in this tutorial, the default value is assigned. UNIQUE indices can hold many NULL values, however, primary keys cannot be NULL. NULL Values and the Alter Command When you use the ALTER command to modify a column, in the absence of NULL specifications, MariaDB automatically assigns values. Print Page Previous Next Advertisements ”;

MariaDB – Regular Expression ”; Previous Next Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP operator. The operator performs pattern matching for a string expression based on a given pattern. MariaDB 10.0.5 introduced PCRE Regular Expressions, which dramatically increases the scope of matching into areas like recursive patterns, look-ahead assertions, and more. Review the use of standard REGEXP operator syntax given below − SELECT column FROM table_name WHERE column REGEXP ”[PATTERN]”; REGEXP returns 1 for a pattern match or 0 in the absence of one. An option for the opposite exists in the form of NOT REGEXP. MariaDB also offers synonyms for REGEXP and NOT REGEXP, RLIKE and NOT RLIKE, which were created for compatibility reasons. The pattern compared can be a literal string or something else such as a table column. In strings, it uses C escape syntax, so double any “” characters. REGEXP is also case-insensitive, with the exception of binary strings. A table of possible patterns, which can be used are given below − Sr.No Pattern & Description 1 ^ It matches the start of the string. 2 $ It matches the string”s end. 3 . It matches a single character. 4 […] It matches any character in the brackets. 5 [^…] It matches any character not listed in the brackets. 6 p1|p2|p3 It matches any of the patterns. 7 * It matches 0 or more instances of the preceding element. 8 + It matches 1 or more instances of the preceding element. 9 {n} It matches n instances of the preceding element. 10 {m,n} It matches m to n instances of the preceding element. Review the pattern matching examples given below − Products starting with “pr” − SELECT name FROM product_tbl WHERE name REGEXP ”^pr”; Products ending with “na” − SELECT name FROM product_tbl WHERE name REGEXP ”na$”; Products starting with a vowel − SELECT name FROM product_tbl WHERE name REGEXP ”^[aeiou]”; Print Page Previous Next Advertisements ”;

MariaDB – Where Clause ”; Previous Next WHERE clauses filter various statements such as SELECT, UPDATE, DELETE, and INSERT. They present criteria used to specify action. They typically appear after a table name in a statement, and their condition follows. The WHERE clause essentially functions like an if statement. Review the general syntax of WHERE clause given below − [COMMAND] field,field2,… FROM table_name,table_name2,… WHERE [CONDITION] Note the following qualities of the WHERE clause − It is optional. It allows any condition to be specified. It allows for the specification of multiple conditions through using an AND or OR operator. Case sensitivity only applies to statements using LIKE comparisons. The WHERE clause permits the use of the following operators − Operator = != > < >= <= WHERE clauses can be utilized at the command prompt or within a PHP script. The Command Prompt At the command prompt, simply use a standard command − root@host# mysql -u root -p password; Enter password:******* mysql> use PRODUCTS; Database changed mysql> SELECT * from products_tbl WHERE product_manufacturer = ”XYZ Corp”; +————-+—————-+———————-+ | ID_number | Nomenclature | product_manufacturer | +————-+—————-+———————-+ | 12345 | Orbitron 4000 | XYZ Corp | +————-+—————-+———————-+ | 12346 | Orbitron 3000 | XYZ Corp | +————-+—————-+———————-+ | 12347 | Orbitron 1000 | XYZ Corp | +————-+—————-+———————-+ Review an example using the AND condition − SELECT * FROM products_tbl WHERE product_name = ”Bun Janshu 3000”; AND product_id <= 344; This example combines both AND and OR conditions SELECT * FROM products_tbl WHERE (product_name = ”Bun Janshu 3000” AND product_id < 344) OR (product_name = ”Bun Janshu 3000”); PHP Scripts Using Where Clause Employ the mysql_query() function in operations using a WHERE clause − <?php $dbhost = ”localhost:3036”; $dbuser = ”root”; $dbpass = ”rootpassword”; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(”Could not connect: ” . mysql_error()); } $sql = ”SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl WHERE product_manufacturer = “XYZ Corp””; mysql_select_db(”PRODUCTS”); $retval = mysql_query( $sql, $conn ); if(! $retval ) { die(”Could not get data: ” . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) { echo “Product ID :{$row[”product_id”]} <br> “. “Name: {$row[”product_name”]} <br> “. “Manufacturer: {$row[”product_manufacturer”]} <br> “. “Ship Date: {$row[”ship_date”]} <br> “. “——————————–<br>”; } echo “Fetched data successfullyn”; mysql_close($conn); ?> On successful data retrieval, you will see the following output − Product ID: 12345 Nomenclature: Orbitron 4000 Manufacturer: XYZ Corp Ship Date: 01/01/17 ———————————————- Product ID: 12346 Nomenclature: Orbitron 3000 Manufacturer: XYZ Corp Ship Date: 01/02/17 ———————————————- Product ID: 12347 Nomenclature: Orbitron 1000 Manufacturer: XYZ Corp Ship Date: 01/02/17 ———————————————- mysql> Fetched data successfully Print Page Previous Next Advertisements ”;