[DeMon Designs]

MySQL TOUR

• MySQL is a free, open source Relational Database Management System.
  
• A MySQL database system consists of a MySQL server and a MySQL database.
  
• The DDL commands are used to define and redefine schema objects.
  
• The DML commands are used to manipulate data in existing schema objects.
  
• The TCL commands are used to manage transactions.
  
• A transaction is one complete unit of work.
  
• The basic elements MySQL SQL are : literals, datatypes, nulls and comments.
  
• Literals are fixed data values.
  
• Data types of MySQL SQL includes :
  • NUMERIC (INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, FLOAT(M, D), DOUBLE(M, D) and DECIMAL(M, D)).
    
  • DATE and TIME (DATE, DATETIME, TIMESTAMP, TIME and YEAR(M)).
    
  • STRINGS (CHAR(M), VARCHAR(M), BLOB or TEXT, TINYBLOB or TINYTEXT, MEDIUMBLOB or MEDIUMTEXT and ENUM).
    
• Column having no value is said to have NULL value.
  
• The SELECT command of SQL lets you make queries on the database.
  
• The DISTINCT keyword eliminates redundant data.
  
• To perform calculations, the expressions can be written along with SELECT without specifying any table name.
  
• A small work table Dummy can be used for making simple calculations.
  
• The curdate pseudo-column returns the current system date.
  
• The WHERE clause is used to select specific rows.
  
• The logical operators OR(||), AND(&&) and NOT(!) are used to connect search conditions in the WHERE clause.
  
• The BETWEEN operator is used for making range checks in queries.
  
• The IN operator is for selecting values from a list of values.
  
• The LIKE operator is used for making character comparisons using strings.
  
• The null value in a column can be searched for in a table using IS NULL in a WHERE clause.
  
• The ORDER BY clause is used to sort the result of a query.
  
• A function is a special type of predefined command set that performs some operations and returns a single value.
  • Major Character functions are Lower/LCASE(), Upper/UCASE(), Concat(), Instr(), Length(), RTrim(), LTrim(), Substr().
    
  • Major Numeric functions are Round(), Truncate(), Mod(), Sign().
    
  • Major Date functions are CurDate(), Date(), Month(), Year(), DayName(), DayofMonth(), DayofWeek(), DayofYear(), Now(), SysDate().
    
• CREATE TABLE command is used to create tables in database.
  
• INSERT INTO command is used to insert data into the table.
  
• To insert data from other tables, sub-query can be used inside INSERT INTO command.
  
• Existing data in tables can be changed with UPDATE command.
  
• Tuples in a table can be deleted using DELETE command.
  
• ALTER TABLE command is used to alter the definition of already created tables.
  
• With ALTER TABLE, new columns can be added, existing columns can be redefined.
  
• DROP TABLE command drops a table from a database.
  

DATABASE TRANSACTIONS

• A database transaction is a logical unit of work (LUW) that must succeed or fail in its entirety.
  
• A user views a transaction in terms of work carried out by it.
  
• The system views a transaction as a logical sequence of read and write operations.
  
• COMMITing a transaction means all the steps of a transaction are carried out successfully and all data changes are made permanent in the database.
  
• Transaction ROLLBACK means transaction has not been finished completely and hence all data changes made by the transaction in the database are undone. And the database returns to the same state as it was before this transaction execution started.
  
• COMMIT command permanently saves the changes made during the transaction execution.
  
• ROLLBACK command undoes the changes made during the transaction execution.
  
• SAVEPOINT is a point in a transaction, uptil which all changes have been saved permanently.
  

GROUPING RECORDS AND TABLE JOINS

• The GROUP BY clause combines all those records that have identical value in a particular field or a group of fields.
  
• GROUP BY clause is used to divide the result in groups.
  
• A group within another another group is called a NESTED GROUP.
  
• Nested grouping can be done by providing multiple fields in the GROUP BY expression.
  
• All fields containing a NULL value are considered to have a value and are grouped with the fields containing non-NULL values.
  
• The HAVING clause is used to specify filtering condition for groups.
  
• The difference between WHERE and HAVING clause is that WHERE conditions are applicable on individual rows whereas HAVING conditions are applicable on groups as formed by GROUP BY clause.
  
• A join is a query that combines rows from two or more tables.
  
• Column having same name in more than one tables being joined, must have qualified names i.e., they must be specified along with their table name or table alias.
  
• A table alias is a temporary label given along with table name in FROM clause.
  
• Equi-joins are joins based on equality conditions.
  
• Non-equi joins are those where column being joined are compared for non-equality.
  
• When one of the two identical columns of joined tables is removed in join query, this is called neutral join.
  

TABLE AND INTEGRITY CONSTRAINTS

• Integrity constraints (or constraints) are the rules that a database must comply at all times. Integrity constraints determine what all changes are permissible to a database.
  
• Commonly used integrity constraints are NOT NULL, PRIMARY KEY and FOREIGN KEY.
  
• Integrity constraints can be defined with CREATE TABLE as well as with ALTER TABLE commands.
  
• With DROP TABLE command, integrity constraints also get dropped.
  
• Primary Key constraint cannot be disabled, it can only be dropped.
  
• Foreign Key constraint can be disabled by setting system variable FOREIGN_CHECKS to zero.
  

NOTE

I have been writing this for last two hours so their maybe some mistakes in this. All the corrections are appreciated. If you have any query related to this please feel free to leave a comment and or PM me. I will be HAPPY to help you.

Thank You for reading this.

[T0ucaNSaM]

Thanks for taking the time to write this out for us i am sure there are many viewing and just do not want to say thanks 🤝

[DeMon Designs]

Thanks for taking the time to write this out for us i am sure there are many viewing and just do not want to say thanks 🤝

Surely this took some time and efforts. But when people like you say thanks, its justified. If some response comes, sure I will write many more.

[Haxalot]

Surely this took some time and efforts. But when people like you say thanks, its justified. If some response comes, sure I will write many more.

There's a good reason why this thread has had little attention, and that's because you've just thrown knowledge at people with no explanation nor examples. Nobody is going to be able to read through a bullet-point-based article and find it an interesting read, and they certainly won't learn or remember a lot from it.

[DeMon Designs]

There's a good reason why this thread has had little attention, and that's because you've just thrown knowledge at people with no explanation nor examples. Nobody is going to be able to read through a bullet-point-based article and find it an interesting read, and they certainly won't learn or remember a lot from it.

Thank you for your valuable suggestion. I surely will improve my upcoming threads.

[Han.]

Kind of pointless. You've basically just re-written the MySQL docs.