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
      Database Export

      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.

      Database Export

      MySQL is one of the most popular relational database systems used to store and manage data. It structures data in the form of tables and views so that data handling becomes easier making organizations prefer using MySQL to manage their company's confidential data. Since their data is highly confidential, it becomes necessary to back up the database and restore it whenever necessary. Hence we perform database export.
      Exporting a database in MySQL is commonly used for backup purposes or transferring data between servers. You can export entire database or just a portion of it. The simplest way of exporting a database is by using the mysqldump command-line tool.

      Exporting Database using mysqldump

      The mysqldump command-line tool is used in MySQL to create backups of databases. It can be used to back up an entire database, specific tables, or even specific rows based of a table.
      Following is the syntax of mysqldump command to export a database 
      $ mysqldump -u username -p database_name > output_file_path
      Where,
      • username: It is the MySQL username to use when connecting to the database.
      • database_name: It is the name of the database to be exported.
      • output_file_path: It is the path of the backup file. This is where the backup data will be stored.
      • >: This symbol exports the output of the mysqldump command into a file named output_file_path.

      Example

      First of all, create a database named TUTORIALS using the following query 
      Create database TUTORIALS;
      Execute the below query to select the current database as TUTORIALS 
      USE TUTORIALS;
      Let us also create a table named CUSTOMERS in the above-created database 
      CREATE TABLE CUSTOMERS (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      The following query inserts 7 records into the CUSTOMERS table 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      Here, we are using the mysqldump command-line statement to export the TUTORIALS database to the 'datadump.sql' file. Once we execute the below statement, we need to enter our MySQL server password.
      
      $ mysqldump -u root -p TUTORIALS > data-dump.sql
      The above command will not produce any visual output. Instead, the 'data-dump.sql' file will be saved in the current working directory of the command prompt or terminal where you executed the command.

      Exporting only Specific Tables in Database

      We can also export only specific tables in a database using the mysqldump command-line tool. To do so, we use the following syntax 
      mysqldump -u username -p database_name table1 table2 ... > output_file.sql

      Example

      Before exporting, let us create two new tables (STUDENTS and EMPLOYEES) in the above-created TUTORIALS database using the following query 
      CREATE TABLE STUDENTS (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         FEES DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Here, we are creating the EMPLOYEES table 
      CREATE TABLE EMPLOYEES (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARIES DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Let us fetch the list of tables in the TUTORIALS database using the below query 
      Show Tables;
      As we can in the output below, we have three tables present in TUTORIALS database
      Tables_in_tutorials
      customers
      employees
      students
      Now, let us export two tables named EMPLOYEES and STUDENTS into the 'datadump2.sql' as follows 
      $ mysqldump -u root -p TUTORIALS employees students > output_file.sql
      Once we executed the above query, it wont display any ouptut. Instead, it exports the data of both the tables into the specified file.

      Exporting all Databases in a Host

      For instance, you have multiple databases in your host and you want to export all of them in a single query. In such scenario, we can use the "--all-databases" option of mysqldump command.

      Example

      Following is the query to export all the databases in a host using the --all-databases option 
      $ mysqldump -u root -p --all-databases > database_dump.sql
      The above command won't show any visible output on the screen. Instead, the 'database_dump.sql' file will be saved in the current working directory of the command prompt or terminal where you ran the command."