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
      FLOAT

      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.

      FLOAT

      The FLOAT data type is a part of the numeric data type. Numeric data types are used to store numbers, and they can be categorized into various subtypes based on their characteristics, such as storage size and precision.

      The MySQL FLOAT Data Type

      The MySQL FLOAT datatype is a floating-point number type that stores approximate numeric values. It stores approximate numeric values in 4 bytes and represents single-precision values.
      FLOAT is suitable for a wide range of numeric values but stores them in an approximate manner due to the IEEE 754 standard limitations.
      FLOAT data type can represent both signed and unsigned attributes of a data value in versions prior to MySQL 8.0.17, but the unsigned FLOAT is deprecated in MySQL 8.0.17 and later versions.

      Syntax

      Following is the basic syntax to set the datatype of a field as FLOAT −
      CREATE TABLE (column_name FLOAT, ...);
      

      Example

      In this example, let us create a new database table named 'datatype_demo' using CREATE TABLE statement with columns representing FLOAT values −
      CREATE TABLE datatype_demo(
         ID INT, 
         NAME VARCHAR(50), 
         HEIGHT FLOAT, 
         WEIGHT FLOAT
      );
      
      Following is the output obtained −
      Query OK, 0 rows affected (0.03 sec)
      

      Verification

      Once the table is created, we can verify the data types of the 'HEIGHT' and 'WEIGHT' fields by retrieving the table's definition as shown below −
      DESC datatype_demo;
      
      The result of the DESC command will show that the 'HEIGHT' and 'WEIGHT' fields have the FLOAT data type −
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      YES
      
      NULL
      
      NAME
      varchar(50)
      YES
      
      NULL
      
      HEIGHT
      float
      YES
      
      NULL
      
      WEIGHT
      float
      YES
      
      NULL
      
      To verify further, let us insert some values into the table using the following INSERT statement −
      INSERT INTO datatype_demo VALUES
      (1, 'John', 171.3, 65.7),
      (2, 'Rob', 45, 75),
      (3, 'Salman', 12.74839, 54.262),
      (4, 'Arush', NULL, NULL),
      (5, 'James', 'h', 'w');
      
      Following is the output obtained −
      ERROR 1265 (01000): Data truncated for column 'HEIGHT' at row 1
      
      As expected, the FLOAT fields accept single precision floating-point numbers without any issues. However, when attempting to insert non-numeric values into these fields, such as 'h' and 'w,' MySQL raises an error, indicating data truncation.
      Finally, to view the data that has been inserted into the table, we can use the SELECT statement as shown below −
      SELECT * FROM datatype_demo;
      
      The resultant table is as follows −
      ID
      NAME
      HEIGHT
      WEIGHT
      1
      John
      171.3
      65.7
      2
      Rob
      45
      75
      3
      Salman
      12.7484
      54.262
      4
      Arush
      NULL
      NULL

      Other Representations of MySQL FLOAT

      MySQL has a provision to specify the range of precision (not the exponent) for the FLOAT datatype in the form of bits. These bits are specified within the parenthesis following the keyword FLOAT, i.e. FLOAT(p).
      However, this precision value is only used to determine the storage size and only holds up to 7 decimal places, with the range from 0 to 23 bits. If the precision bit exceeds 23, the data type becomes DOUBLE.

      Example

      First, we will drop the existing 'datatype_demo' table −
      DROP TABLE datatype_demo;
      
      The output obtained is as follows −
      Query OK, 0 rows affected (0.01 sec)
      
      Then, we will create a new table 'datatype_demo' specifying a precision of 20 bits for the 'HEIGHT' column −
      CREATE TABLE datatype_demo(
         ID INT,
         NAME VARCHAR(50),
         HEIGHT FLOAT(20)
      );
      
      Following is the output of the above code −
      Query OK, 0 rows affected (0.02 sec)
      

      Verification

      Even though we specified a precision of 20 bits, the 'HEIGHT' column will still store float values within the single-precision range, holding up to 7 decimal places. To verify the table's definition, we can use the DESC command as shown below −
      DESC datatype_demo;
      
      The table produced is as follows −
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      YES
      
      NULL
      
      NAME
      varchar(50)
      YES
      
      NULL
      
      HEIGHT
      float
      YES
      
      NULL
      
      If the precision bit exceeds 23, the datatype becomes DOUBLE. Look at the query below −
      CREATE TABLE datatype_demo1(
         ID INT,
         NAME VARCHAR(50),
         HEIGHT FLOAT(30)
      );
      
      we get the following output −
      Query OK, 0 rows affected (0.02 sec)
      
      Again, we can verify the table's definition using the DESC command −
      DESC datatype_demo1;
      
      Following is the table obtained −
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      YES
      
      NULL
      
      NAME
      varchar(50)
      YES
      
      NULL
      
      HEIGHT
      double
      YES
      
      NULL
      

      Float Datatype Using a Client Program

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

      Syntax

      PHPNodeJSJavaPython
      To create a column of Float datatype through a PHP program, we need to execute the "CREATE TABLE" statement using the mysqli function query() as follows −
      $sql = 'CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, 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.');
      
      //creating a table temp where we are inserting the celsius and Fahrenheit values in float
      $sql = 'CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))';
      $result = $mysqli->query($sql);
      if ($result) {
          printf("Table created successfully...!\n");
      }
      // insert data into created table    
      $q = " INSERT INTO temp(Celsius, Fahrenheit) VALUES ( 36.2, 97.16), ( 35.8, 96.44), ( 37.32, 99.17), ( 35.89, 96.602);";
      if ($res = $mysqli->query($q)) {
          printf("Data inserted successfully...!\n");
      }
      //now display the table records    
      $s = "SELECT * FROM temp";
      if ($r = $mysqli->query($s)) {
          printf("Table Records: \n");
          while ($row = $r->fetch_assoc()) {
              printf(" ID: %d, Celsius: %f, Fahrenheit: %f", $row["Id"], $row["Celsius"], $row["Fahrenheit"]);
              printf("\n");
          }
      } else {
          printf('Failed');
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Table created successfully...!
      Data inserted successfully...!
      Table Records:
       ID: 1, Celsius: 36.200000, Fahrenheit: 97.160000
       ID: 2, Celsius: 35.800000, Fahrenheit: 96.440000
       ID: 3, Celsius: 37.320000, Fahrenheit: 99.170000
       ID: 4, Celsius: 35.890000, Fahrenheit: 96.602000