Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      DECIMAL

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       DECIMAL

      The MySQL Decimal Data Type

      The MySQL DECIMAL data type is used to store numeric values with decimal points. It allows for precise calculations and can be configured to store a specified number of digits before and after the decimal point.
      We often use this datatype for the columns that require exact precision such as salaries of employees, employee PF balances, etc.
      Internally, MySQL stores DECIMAL values using a binary format that allocates storage for the integer and fractional parts of the number separately. This binary format efficiently packs 9 digits into 4 bytes of storage.

      Syntax

      Following is the syntax to define a column whose data type is DECIMAL −
      column_name  DECIMAL(P,D);
      
      Where,
      • P is called precision which specifies the total number of significant digits that can be stored in the column, both to the left and right of the decimal point. The range of P is 1 to 65.
      • D is a scale that specifies the maximum number of digits that can be stored after the decimal point. The range of D should be between 0 and 30 and D is less than or equal to (<=) P.
      For instance, if we define a column as DECIMAL(10,2), it can store numbers with up to 10 digits, and up to 2 digits to the right of the decimal point.
      In MySQL, instead of the DECIMAL keyword, we can also use the "DEC", "FIXED" and "NUMERIC" keywords because they are synonyms for DECIMAL.

      Attributes

      The DECIMAL keyword has two attributes: UNSIGNED and ZEROFILL.
      • UNSIGNED − When used, it indicates that the column does not accept negative values.
      • ZEROFILL − If used, it pads the number with zeros to the specified width.

      Precision and Scale

      In the following query, we define a SALARY column with the DECIMAL data type, specifying a precision of 5 and a scale of 3 −
      SALARY decimal(5,3)
      
      This definition means that the SALARY column can store values with up to 5 digits in total, including 3 digits to the right of the decimal point. The range for this column would be from 99.999 to -99.999.

      No Decimal Places

      In here, the SALARY column contains no fractional part or decimal point. The following two queries are same −
      SALARY DECIMAL(5);
      SALARY DECIMAL(5,0);
      
      Both declarations indicate that the SALARY column can store values as whole numbers without decimal places.

      Default Precision

      If you omit the precision value, the default precision P will be 10 −
      SALARY DECIMAL;
      

      MySQL DECIMAL Storage

      MySQL stores values of the "DECIMAL" data type using a binary format that optimizes storage. Specifically, MySQL packs 9 digits into 4 bytes. Storage is allocated separately for the integer and fractional parts, with 4 bytes used for each set of 9 digits. Any remaining digits require additional storage.
      The storage required for remaining (leftover) digits is demonstrated in the following table:
      Leftover Digits
      Bytes
      0
      0
      1-2
      1
      3-4
      2
      5-6
      3
      7-9
      4
      Consider a DECIMAL(30,9) column, which has 9 digits for the fractional part and 30 - 9 = 21 digits for the integer part. In this case, the fractional part takes 4 bytes. The integer part takes 8 bytes for the first 18 digits, and for the leftover 3 digits, it requires an additional 2 bytes. Therefore, the DECIMAL(30,9) column requires a total of 14 bytes.

      Example

      To further understand this, let us create a table named EMPLOYEES using the following query −
      CREATE TABLE EMPLOYEES (
         ID int NOT NULL AUTO_INCREMENT,
         NAME varchar(30) NOT NULL,
         SALARY decimal(14,4) NOT NULL,
         PRIMARY KEY (ID)
       );
      
      Using the following query, we are inserting some records into the above created table −
      INSERT INTO EMPLOYEES (NAME, SALARY) VALUES 
      ("Krishna", 150050.34), 
      ("Kalyan", 100000.65);
      
      The EMPLOYEES table obtained is as follows −
      ID
      NAME
      SALARY
      1
      Krishna
      150050.3400
      2
      Kalyan
      100000.6500
      Using the following query, we are including the ZEROFILL attribute in the "SALARY" column −
      ALTER TABLE EMPLOYEES
      MODIFY SALARY decimal(14, 4) zerofill;
      
      Following is the output of the above query −
      Query OK, 2 rows affected, 1 warning (0.03 sec)
      Records: 2  Duplicates: 0  Warnings: 1
      
      Here, we are trying to fetch all the records from the EMPLOYEES tables after including the ZEROFILL attribute on SALARY column −
      SELECT * FROM EMPLOYEES;
      
      The records will display zeros padded based on the range specified in the "SALARY" column −
      ID
      NAME
      SALARY
      1
      Krishna
      0000150050.3400
      2
      Kalyan
      0000100000.6500

      Decimal Datatype Using a Client Program

      We can also create column of the decimal datatype using the client program.

      Syntax

      PHPNodeJSJavaPython
      To create a column of decimal datatype through a PHP program, we need to execute the "CREATE TABLE" statement using the mysqli function query() as follows −
      $sql = 'CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )';
      $mysqli->query($sql);
      

      Example

      Following are the programs −
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      //printf('Connected successfully.');
      
      //create table with boolean column    
      $sql = 'CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )';
      $result = $mysqli->query($sql);
      if ($result) {
          printf("Table created successfully...!\n");
      }
      //insert data into created table    
      $q = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES ('Krishna', 150050.34), ('Kalyan', 100000.65)";
      if ($res = $mysqli->query($q)) {
          printf("Data inserted successfully...!\n");
      }
      //now display the table records    
      $s = "SELECT ID, Salary FROM EMPLOYEES";
      if ($r = $mysqli->query($s)) {
          printf("Table Records: Where Salary is decimal type! \n");
          while ($row = $r->fetch_assoc()) {
              printf(" ID: %d, Salary: %s", $row["ID"], $row["Salary"]);
              printf("\n");
          }
      } else {
          printf('Failed');
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Table created successfully...!
      Data inserted successfully...!
      Table Records: Where Salary is decimal type!
       ID: 1, Salary: 150050.3400
       ID: 2, Salary: 100000.6500          
      
      P