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
      Create Tables

      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.

      CREATE TABLE Statement

      MySQL CREATE TABLE Statement

      The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column.

      Syntax

      Following is the syntax to create a table in MySQL 
      CREATE TABLE [IF NOT EXISTS] table_name(
         column1 datatype,
         column2 datatype,
         column3 datatype,
         .....
         columnN datatype,
      );
      Where, table_name is the name of the table you need to create, column1, column2, column3, ……..… columnN are the names of the columns and datatype is the name of the datatypes of the respective columns.

      Example

      Following query creates a table with name Employee 
      CREATE TABLE Employee(
         Name VARCHAR(255),
         Salary INT NOT NULL,
         Location VARCHAR(255)
      );
      The SHOW TABLES statements gives you the list of tables in the current database, if the creation is successful, you can see the name of the above created table in it.
      show tables;

      Output

      The above query produces the following output
      Tables_in_sample
      employee

      The IF NOT EXISTS clause

      If you try to create a table with an existing name an error will be generated 
      CREATE TABLE Employee(Name VARCHAR(255));
      ERROR 1050 (42S01): Table 'employee' already exists
      If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new table will be created and if a table with the given name, already exists the query will be ignored.
      CREATE TABLE Test(Name VARCHAR(255));

      Creating a table using an existing one

      You can also create a table using the existing table (with same definition), following is the syntax to do so 
      CREATE TABLE [IF NOT EXISTS] table_name {LIKE old_table_name}
      Where, table_name is the name of the table you need to create and old_table_name is the name of the table from which you need to create the new one.

      Example

      Following query create a new table sample same as the table Employee
      CREATE TABLE sample LIKE Employee;
      You can verify the above created table using the DESC statement too.
      DESC sample;

      Output

      Following is the output of the above program
      Field
      Type
      Null
      Key
      Default
      Extra
      Name
      varchar(255)
      YES
      
      NULL
      
      Salary
      int
      NO
      
      NULL
      
      Location
      varchar(255)
      YES
      
      NULL