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
      TINYINT

      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.

      TINYINT

      The MySQL TINYINT Data Type

      The MySQL TINYINT data type is used to store integer values within a very small range. It occupies just 1 byte (8 bits) of storage and can hold values from -128 to 127 for signed TINYINT or 0 to 255 for unsigned TINYINT.
      When you define a TINYINT column in MySQL, by default it is considered as SIGNED. This means it can hold both positive and negative numbers within a specific range. Additionally, you can use either "TINYINT" or "INT1" to define such a column because they work the same way.

      Syntax

      Following is the syntax of the MySQL TINYINT data type −
      TINYINT(M) [SIGNED | UNSIGNED | ZEROFILL]
      

      Example

      First, let us create a table with the name tinyint_table using the below query −
      CREATE TABLE tinyint_table (
         col1 TINYINT,
         col2 TINYINT UNSIGNED,
         col3 TINYINT ZEROFILL
      );
      
      Following is the output obtained −
      Query OK, 0 rows affected, 1 warning (0.03 sec)
      
      Now, let us try to insert some values (128, 128, 128) into these columns as shown below −
      INSERT INTO tinyint_table VALUES (128, 128, 128);
      
      An error is generated for the value in col1 because the value we inserted is out of range −
      ERROR 1264 (22003): Out of range value for column 'col1' at row 1
      
      Next, if we try to insert a negative value into the TINYINT UNSIGNED column ("col2"), it will result in an error because UNSIGNED values cannot be negative −
      INSERT INTO tinyint_table VALUES (127, -120, 128);
      
      The error message displayed is as follows −
      ERROR 1264 (22003): Out of range value for column 'col2' at row 1
      
      Similarly, if we insert -128 into the TINYINT ZEROFILL column ("col3"), an error will be generated −
      INSERT INTO tinyint_table VALUES (127, 128, -128);
      
      The output is as shown below −
      ERROR 1264 (22003): Out of range value for column 'col3' at row 1
      
      However, if we insert values within the valid range, the insertion will succeed as shown below −
      INSERT INTO tinyint_table VALUES (127, 128, 128);
      
      Following is the output of the above code −
      Query OK, 1 row affected (0.01 sec)
      
      Finally, we can retrieve all the records present in the table using the following SELECT query −
      SELECT * FROM tinyint_table;
      
      This query will display the following result −
      col1
      col2
      col3
      127
      128
      128

      TINYINT Datatype Using a Client Program

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

      Syntax

      PHPNodeJSJavaPython
      To create a column of TINYINT datatype through a PHP program, we need to execute the "CREATE TABLE" statement using the mysqli function query() as follows −
      $sql = 'CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )';
      $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.');
      
      $sql = 'CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )';
      $result = $mysqli->query($sql);
      if ($result) {
          printf("Table created successfully...!\n");
      }
      // insert data into created table    
      $q = " INSERT INTO tinyint_table (col1, col2, col3) VALUES (100, 105, 110)";
      if ($res = $mysqli->query($q)) {
          printf("Data inserted successfully...!\n");
      }
      //now display the table records    
      $s = "SELECT * FROM tinyint_table";
      if ($r = $mysqli->query($s)) {
          printf("Table Records: \n");
          while ($row = $r->fetch_assoc()) {
              printf(" Col_1: %s, Col_2: %s, Col_3: %s", $row["col1"], $row["col2"], $row["col3"]);
              printf("\n");
          }
      } else {
          printf('Failed');
      }
      $mysqli->close();
      

      Output

      The output obtained is as follows −
      Table created successfully...!
      Data inserted successfully...!
      Table Records:
       Col_1: 100, Col_2: 105, Col_3: 110