- Database Connection Example:
123456789$dbhost = 'localhost:3306';$dbuser = 'guest';$dbpass = 'guest123';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ) {die('Could not connect: ' . mysql_error());}echo 'Connected successfully';mysql_close($conn);
- SQL:
- Required − SQL query to create or delete a MySQL database
- Required − SQL query to create or delete a MySQL database
- Data Type:
- Properly defining the fields in a table is important to the overall optimization of your database. You should use only the type and size of field you really need to use. For example, do not define a field 10 characters wide, if you know you are only going to use 2 characters. These type of fields (or columns) are also referred to as data types, after the type of data you will be storing in those fields
- MySQL uses many different data types broken into three categories −
- Numeric:
- INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, FLOAT(M,D), DOUBLE(M,D), DECIMAL(M,D)
- Date and Time:
- 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
- Numeric:
- Sorting Result:
- We have seen the SQL SELECT command to fetch data from a MySQL table. When you select rows, the MySQL server is free to return them in any order, unless you instruct it otherwise by saying how to sort the result. But, you sort a result set by adding an ORDER BY clause that names the column or columns which you want to sort
- SELECT field1, field2,…fieldN table_name1, table_name2… ORDER BY field1, [field2…] [ASC [DESC]]
- You can sort the returned result on any field, if that field is being listed out.
- You can sort the result on more than one field.
- You can use the keyword ASC or DESC to get result in ascending or descending order. By default, it’s the ascending order.
- You can use the WHERE…LIKE clause in the usual way to put a condition
- Joins:
- You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table. You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables
- “SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b WHERE a.tutorial_author = b.tutorial_author”;
- MySQL LEFT JOIN:
- A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives some extra consideration to the table that is on the left.
- If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join: thus ensuring (in my example) that every AUTHOR gets a mention.
- SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author;
- Pattern Matching – REGEXP:
- MySQL supports another type of pattern matching operation based on the regular expressions and the REGEXP operator
- Following is the table of pattern, which can be used along with the REGEXP Operator:
- ^ – Beginning of string
- $ – End of string
- . – Any single character
- […] – Any character listed between the square brackets
- [^…] – Any character not listed between the square brackets
- p1|p2|p3 – Alternation; matches any of the patterns p1, p2, or p3
- * – Zero or more instances of preceding element
- + – One or more instances of preceding element
- {n} – n instances of preceding element
- {m,n} – m through n instances of preceding element
- Query to find all the names starting with ‘st’ – SELECT name FROM person_tbl WHERE name REGEXP ‘^st‘
- Query to find all the names ending with ‘ok’ – SELECT name FROM person_tbl WHERE name REGEXP “ok$”
- Query to find all the names, which contain ‘mar’ – SELECT name FROM person_tbl WHERE name REGEXP “mar”
- Query to find all the names starting with a vowel and ending with ‘ok’ – SELECT FirstName FROM intque.person_tbl WHERE FirstName REGEXP “^[aeiou].*ok$”
- Transaction:
- A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.
- Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction
- Properties of Transactions:
- Transactions have the following four standard properties, usually referred to by the acronym ACID −
- Atomicity − This ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.
- Consistency − This ensures that the database properly changes states upon a successfully committed transaction.
- Isolation − This enables transactions to operate independently on and transparent to each other.
- Durability − This ensures that the result or effect of a committed transaction persists in case of a system failure.
- In MySQL, the transactions begin with the statement BEGIN WORK and end with either a COMMIT or a ROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction
- Commit and Rollback:
- These two keywords Commit and Rollback are mainly used for MySQL Transactions.
- When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect.
- If a failure occurs, a ROLLBACK command should be issued to return every table referenced in the transaction to its previous state.
- AUTOCOMMIT:
- You can control the behavior of a transaction by setting session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes.
- When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT = 0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued
- Command Matrix Table:
| OPERATIONS | QUERIES |
| Insert User | INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES (‘localhost’, ‘guest’, PASSWORD(‘guest123’), ‘Y’, ‘Y’, ‘Y’) |
| Grant Privilages | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON TUTORIALS.* TO ‘zara’@’localhost’ IDENTIFIED BY ‘zara123’ |
| MySql Connection | connection mysql_connect(server,user,passwd,new_link,client_flag) |
| Create Database | CREATE DATABASE TUTORIALS |
| Drop Database | DROP DATABASE TUTORIALS |
| Select Database | USE TUTORIALS |
| Create Table |
CREATE TABLE table_name (column_name column_type) CREAT TABLE tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( tutorial_id ) ) |
| Drop Table | DROP TABLE table_name |
| Insert Row | INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN ) |
| Select | SELECT field1, field2,…fieldN FROM table_name1, table_name2… [WHERE Clause] [OFFSET M ] [LIMIT N |
| Update | UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE Clause] |
| Delete | DELETE FROM table_name [WHERE Clause] |
| Like | SELECT field1, field2,…fieldN table_name1, table_name2… WHERE field1 LIKE condition1 [AND [OR]] filed2 = ‘somevalue’ |
| Sorting Result | SELECT field1, field2,…fieldN table_name1, table_name2… ORDER BY field1, [field2…] [ASC [DESC]] |
| Joins | SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b WHERE a.tutorial_author = b.tutorial_author |
| Left Joins | SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author |
| Is Null | SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL |
| Is Not Null | SELECT * FROM tcount_tbl WHERE tutorial_count IS NOT NULL |
| ALTER | ALTER TABLE testalter_tbl |
| Drop Column | ALTER TABLE testalter_tbl DROP column_name |
| Add Column | ALTER TABLE testalter_tbl ADD column_name INT |
| Add In First Column | ALTER TABLE testalter_tbl ADD column_name INT FIRST |
| Add After Column | ALTER TABLE testalter_tbl ADD column_name INT AFTER column_one |
| Modify OR Change |
ALTER TABLE testalter_tbl MODIFY c CHAR(10) ALTER TABLE testalter_tbl CHANGE i j BIGINT ALTER TABLE testalter_tbl CHANGE j j INT ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100 |
| Unique Index |
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,…); CREATE UNIQUE INDEX author_index ON tutorials_tbl (tutorial_author) CREATE UNIQUE INDEX author_index ON tutorials_tbl (tutorial_author DESC) |
| Temporary Tables | CREATE TEMPORARY TABLE SalesSummary (product_name VARCHAR(50) NOT NULL, total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00, avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 |
| Get Table Table Details | SHOW CREATE TABLE tutorials_tbl \G |
