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
      Administration

      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.

      Administration

      MySQL Server is the program that mainly executes the SQL queries in the database system. Hence it becomes necessary to optimize the working of this server. The general MySQL administration usually includes concepts like:
      • Starting and Stopping the Server
      • User Security
      • Database Maintenance
      • Backup & Restore

      Start MySQL Server

      We need to first start the MySQL server on the device in order to use it. One way to do so, is by executing the following command on the command prompt (run as an administrator) 
      mysqld
      We can also start the server by going through the services provided by the Windows and follow the steps below −
      • Open the 'Run' Window using the 'Windows+R' shortcut and run 'services.msc' through it.
      • 
      • Then, select the "MySQL80" service click "start" to start the server.
      • 

      Stop, Pause, Restart MySQL Server

      Now, if you want to pause, stop or restart an already running MySQL server, then you can do it by opening the Windows Services and selecting the desired action
      To stop the MySQL Server, select the 'stop' option as shown in the image below
      
      To pause the MySQL Server, select the 'pause' option as shown in the image below
      
      We can also restart the MySQL server as needed, by selecting the 'restart' option as shown below
      

      Setting Up a MySQL User Account

      For adding a new user to MySQL, you just need to add a new entry to the user table in the database mysql.
      In the following example, we are creating a new user guest with the password guest123 on the 'localhost'. We are also granting all privileges required to executing SQL queries 
      CREATE USER 'guest'@'localhost' IDENTIFIED BY 'guest123';
      Now, execute the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don't use it, then you won't be able to connect to MySQL using the new user account at least until the server is rebooted.
      FLUSH PRIVILEGES;
      Finally, you need to grant all privileges to this new user to execute SQL queries.
      GRANT ALL PRIVILEGES ON * . * TO 'sample'@'localhost';
      You can also specify other privileges to a new user by setting the values of following columns in 'user' table to 'Y' using the UPDATE query.
      • Select_priv
      • Insert_priv
      • Update_priv
      • Delete_priv
      • Create_priv
      • Drop_priv
      • Reload_priv
      • Shutdown_priv
      • Process_priv
      • File_priv
      • Grant_priv
      • References_priv
      • Index_priv
      • Alter_priv
      NOTE − MySQL does not terminate a command until you give a semi colon (;) at the end of the SQL command.

      The /etc/my.cnf File Configuration

      In most of the cases, you should not touch this file. By default, it will have the following entries 
      [mysqld]
      datadir = /var/lib/mysql
      socket = /var/lib/mysql/mysql.sock
      
      [mysql.server]
      user = mysql
      basedir = /var/lib
      
      [safe_mysqld]
      err-log = /var/log/mysqld.log
      pid-file = /var/run/mysqld/mysqld.pid
      Here, you can specify a different directory for the error log, otherwise you should not change any entry in this table.

      Administrative MySQL Commands

      Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database
      • USE database_name − This will be used to select a database in the MySQL.
      • SHOW DATABASES − Lists out the databases that are accessible by the MySQL DBMS.
      • SHOW TABLES − Displays the list of the tables in the current database.
      • SHOW COLUMNS FROM table_name: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
      • SHOW INDEX FROM table_name − Presents the details of all indexes on the table, including the PRIMARY KEY.
      • SHOW TABLE STATUS LIKE table_name\G − Reports details of the MySQL DBMS performance and statistics.