IntroductiontoMySQLLanguage
WhatisMySQL?
MySQLisafastandeasytouseRDBMSwhichcanbeusedinsmalltolargescaleorganizations.
Itisrobustandflexible,therebymakingitsusageeasier.
SomeoftheadvantagesofusingMySQLis:
MySQLisopensourceandisfreetouse
[Link].
MySQLworksonmultipleOperatingSystemsandcanbeusedwithmanylanguagessuchas
Java,C,C++,PHP,PERL
MySQLusesstandardformofpopularlyknownSQLlanguage
Caneasilysupportlargedatasetswithoutbringingdowntheperformance
[Link],onecanmodifytheMySQL
softwaretofittheirenvironmentrequirements
Isextremelylightweightsoftwareandcomeswitheasytoinstallfeature
MySQLiscloselyintegratedwithPHP,thusmakingitidealforWebApplicationdevelopment
MySQLInstallation:
ToinstallMYSQL,[Link]/downloads/.Oncedownloaded,extractthe
[Link],then
youcanremoveallsubfoldersexceptdata,bin,[Link]
belowforWindowsInstallationforMySQL5.7.16
1. Choosesetuptype
2. CheckRequirements
3. SelectInstallation
4. ProductConfiguration
5. SelectNetworkDetailsandPortnumber
6. ProvideAccountRootPassword
7. ChooseWindowsServiceoptions
8. CompleteInstallation
Onceinstallationiscomplete,youcanaccessMySQLthroughCommandlineinterfaceorMySQL
[Link]
below
OnopeningtheCLI,itwillpromptforpasswordandonenteringthecorrectpassword,youwillbe
abletoaccessMySQLcommandprompttofirequeries.
FromAllPrograms,[Link],youcanselect
[Link],youwillbeabletoseethe
Workbenchinterfaceasshownbelow
QUERIESINMySQL:
OncompletionofMySQLinstallationasshownabove,youcanstartusingMySQLWorkbenchorCLI
toquerythedatabase.
1. CreateUserBelowquerycreatesausermySQLUserinlocalhostwithpasswordTest123
CREATEUSER'mySQLUser'@'localhost'IDENTIFIEDBY'Test123';
2. GrantPrivilegesThesyntaxforgrantprivilegeis
GRANT[typeofpermission]ON[databasename].[tablename]TO[username]@'localhost;
E.g.:GRANTALLPRIVILEGESON*.*TO'mySQLUser'@'localhost';
Theabovecommandistoprovideread,edit,executetoalltablesanddatabases.
Afterfinalizingthepermissionsforanewuser,reloadalltheprivilegetoreflectthenewuser
using
FLUSHPRIVILEGES;
Thedifferentpermissionspossibleare
ALLPRIVILEGESgivesallaccesstoadatabase
CREATEgivesaccesstocreatetableordatabase
DROPgivesaccesstodroptableordatabase
DELETEgivesaccesstodeleterowsfromtable
INSERTgivesaccesstoinsertrowsintotables
SELECTallowstouseselectcommand
UPDATEallowstoupdaterows
GRANTOPTIONallowstograntorremoveotherusersprivileges
3. DropUserBelowquerycanbeusedtodropusermySQLUser
DROPUSER'mySQLUser'@'localhost';
4. RevokeUserThesyntaxtorevokeprivilegesis
REVOKE[typeofpermission]ON[databasename].[tablename]FROM
[username]@localhost;
5. ShowDatabaseCommandtolistalldatabasesis
SHOWDATABASES;
6. CreateDatabaseCommandtocreateanewdatabaseis
CREATEDATABASE[databasename];
7. UseDatabaseUsecommandisusedtotellMySQLtousethedatabase_nameasthe
default/currentdatabaseforallsubsequentstatements
USE[database_name];
8. ShowTablesShowcommandisusedtolistalltablesinthedatabase
SHOWTABLES;
9. DescribeTableDescribecommandisusedtoshowthestructureofthetable
DESCRIBE[table_name];
10. ShowColumnsAllowstoshowallthecolumnsinatable
SHOWCOLUMNSFROM[table_name];
11. DropDatabaseAllowstodropthespecifieddatabase
DROPDATABASE[database_name];
12. CreatetableCreateTablesyntaxis
CREATETABLE[table_name](
Column1Type1,
Column2Type2,
);
Thedifferentdatatypeavailableareint,float,double,timestamp,char,varchar,blob,enum
13. InsertInsertsyntaxis
INSERTINTO[table_name]VALUES(list_of_values);
OR
INSERTINTO[table_name]SET
Column1=Value1,
Column2=Value2.
14. UpdateUpdatesyntaxis
UPDATE[table_name]SET
Column1=Value1,
Column2=Value2,
WHERE[conditions];
15. MySQLMathematicalFunctionsThedifferenttypesofmathematicalfunctionsavailable
areCOUNT,AVG,MIN,MAX,SUB,ABS,ROUND,FLOOW,CEILING,SQRT,POW,RANS,SIN,
COS.
16. MySQLStringFunctionsThedifferenttypesofStringfunctionsavailableareSTRCMP,
LOWER,UPPER,LTRIM,SUBSTRING,PASSWORD,ENCODE,DECODE,CURDATE,CURTIME,
DAYNAME,DAYOFWEEK,MONTHNAME.
CONCLUSION:
MySQLhasmanyotherfeaturessuchasdistinct,joins,grouping,alias,[Link]
builtfunctionsmakesSQLqueriesinMySQLeasier.
More informations about MySQL language on [Link] .