Transcription
MySQL Tutorial
MYSQL TUTORIALSimply Easy Learning by tutorialspoint.comtutorialspoint.comi
ABOUT THE TUTORIALMySQL TutorialMySQL is the most popular Open Source Relational SQL database management system. MySQL is one ofthe best RDBMS being used for developing web-based software applications.This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming.AudienceThis reference has been prepared for the beginners to help them understand the basics to advancedconcepts related to MySQL languages.PrerequisitesBefore you start doing practice with various types of examples given in this reference, I'm making anassumption that you are already aware about what is database, especially RDBMS and what is acomputer programming language.Copyright & Disclaimer Notice Allthe content and graphics on this tutorial are the property of tutorialspoint.com. Any content fromtutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape or formwithout the written permission of tutorialspoint.com. Failure to do so is a violation of copyright laws.This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding theaccuracy of the site or its contents including this tutorial. If you discover that the tutorialspoint.com siteor this tutorial content contains some errors, please contact us at webmaster@tutorialspoint.comTUTORIALS POINTSimply Easy Learning
Table of ContentMySQL Tutorial . 2Audience . 2Prerequisites . 2Copyright & Disclaimer Notice. 2Introduction . 12RDBMS Terminology:. 12MySQL Database: . 13Before You Begin: . 13Installation. 14Installing MySQL on Linux/UNIX . 14Installing MySQL on Windows: . 15Verifying MySQL Installation: . 15Use the mysqladmin Utility to Obtain Server Status: . 15Execute simple SQL commands using MySQL Client: . 15Post-installation Steps: . 16Running MySQL at boot time: . 16Administration . 17The /etc/my.cnf File Configuration: . 19Administrative MySQL Command: . 19PHP Syntax . 20Connection. 21Example: . 21MySQL Connection using PHP Script: . 21Syntax: . 21Syntax: . 22Example: . 22Create Database . 24Example: . 24Create Database using PHP Script: . 24Syntax: . 24Example: . 24Drop Database . 26Drop Database using PHP Script: . 26Syntax: . 26Example: . 27Select Database. 28Selecting MySQL Database from Command Prompt: . 28TUTORIALS POINTSimply Easy Learning
Example: . 28Selecting MySQL Database Using PHP Script: . 28Syntax: . 28Example: . 29Data Types . 30Numeric Data Types:. 30Date and Time Types: . 31String Types: . 31Create Tables . 32Syntax: . 32Creating Tables from Command Prompt: . 32Example: . 33Creating Tables Using PHP Script: . 33Example: . 33Drop Tables . 35Syntax: . 35Dropping Tables from Command Prompt: . 35Example: . 35Dropping Tables Using PHP Script: . 35Example: . 35Insert Query . 37Syntax: . 37Inserting Data from Command Prompt: . 37Example: . 37Inserting Data Using PHP Script: . 38Example: . 38Select Query . 40Syntax: . 40Fetching Data from Command Prompt: . 40Example: . 40Fetching Data Using PHP Script: . 41Example: . 41Example: . 42Example: . 42Releasing Memory: . 43Example: . 43Where Clause . 44Syntax: . 44Fetching Data from Command Prompt: . 45TUTORIALS POINTSimply Easy Learning
Example: . 45Fetching Data Using PHP Script: . 45Example: . 46Update Query. 47Syntax: . 47Updating Data from Command Prompt: . 47Example: . 47Updating Data Using PHP Script: . 48Example: . 48Delete Query . 49Syntax: . 49Deleting Data from Command Prompt: . 49Example: . 49Deleting Data Using PHP Script: . 50Example: . 50Like Clause . 51Syntax: . 51Using LIKE clause at Command Prompt: . 51Example: . 52Using LIKE clause inside PHP Script: . 52Example: . 52Sorting Results. 54Syntax: . 54Using ORDER BY clause at Command Prompt: . 54Example: . 54Using ORDER BY clause inside PHP Script: . 55Example: . 55Using Join . 56Using Joins at Command Prompt: . 56Example: . 56Using Joins in PHP Script: . 57Example: . 57MySQL LEFT JOIN: . 58Example: . 58NULL Values . 59Using NULL values at Command Prompt:. 59Example: . 59Handling NULL Values in PHP Script: . 60Example: . 60TUTORIALS POINTSimply Easy Learning
Regexps. 62Examples: . 62Transactions . 64Properties of Transactions: . 64COMMIT and ROLLBACK:. 64Generic Example on Transaction . 65Transaction-Safe Table Types in MySQL:. 65Alter Command . 66Dropping, Adding or Repositioning a Column: . 66Changing a Column Definition or Name: . 67The Effect of ALTER TABLE on Null and Default Value Attributes: . 67Changing a Column's Default Value:. 68Changing a Table Type: . 68Renaming a Table: . 68Indexes . 70Simple and Unique Index: . 70ALTER command to add and drop INDEX: . 71ALTER Command to add and drop PRIMARY KEY: . 71Displaying INDEX Information: . 71Temporary Tables . 72Example: . 72Dropping Temporary Tables:. 73Clone Tables . 74Example: . 74Step 1: . 74Step 2: . 75Step 3: . 75Database Info. 76Obtaining the Number of Rows Affected by a Query: . 76PERL Example: . 76PHP Example: . 76Listing Tables and Databases: . 77PERL Example: . 77PHP Example: . 77Getting Server Metadata: . 77Using Sequences . 78Using AUTO INCREMENT column: . 78Example: . 78Obtain AUTO INCREMENT Values: . 79TUTORIALS POINTSimply Easy Learning
PERL Example: . 79PHP Example: . 79Renumbering an Existing Sequence: . 79Starting a Sequence at a Particular Value:. 79Handling Duplicates . 81Preventing Duplicates from Occurring in a Table: . 81Counting and Identifying Duplicates: . 82Eliminating Duplicates from a Query Result: . 82Removing Duplicates Using Table Replacement: . 83MySQL SQL Injection. 84Preventing SQL Injection:. 85The LIKE Quandary: . 85Database Export . 86Exporting Data with the SELECT . INTO OUTFILE Statement: . 86Exporting Tables as Raw Data: . 87Exporting Table Contents or Definitions in SQL Format: . 87Copying Tables or Databases to Another Host: . 88Database Import . 89Importing Data with LOAD DATA: . 89Importing Data with mysqlimport . 89Handling Quotes and Special Characters: . 90MySQL Useful Functions . 91ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days) . 105ADDTIME(expr1,expr2) . 105CONVERT TZ(dt,from tz,to tz) . 105CURDATE() . 106CURRENT DATE and CURRENT DATE() . 106CURTIME() . 106CURRENT TIME and CURRENT TIME() . 107CURRENT TIMESTAMP and CURRENT TIMESTAMP() . 107DATE(expr) . 107DATEDIFF(expr1,expr2) . 107DATE ADD(date,INTERVAL expr unit),. 107DATE SUB(date,INTERVAL expr unit) . 107DATE FORMAT(date,format) . 109DATE SUB(date,INTERVAL expr unit) . 110DAY(date) . 110DAYNAME(date) . 110DAYOFMONTH(date) . 111TUTORIALS POINTSimply Easy Learning
DAYOFWEEK(date) . 111DAYOFYEAR(date) . 111EXTRACT(unit FROM date) . 111FROM DAYS(N) . 112FROM UNIXTIME(unix timestamp) . 112FROM UNIXTIME(unix timestamp,format) . 112HOUR(time) . 112LAST DAY(date) . 112LOCALTIME and LOCALTIME() . 113LOCALTIMESTAMP and LOCALTIMESTAMP() . 113MAKEDATE(year,dayofyear) . 113MAKETIME(hour,minute,second) . 113MICROSECOND(expr). 113MINUTE(time) . 113MONTH(date). 114MONTHNAME(date) . 114NOW() . 114PERIOD ADD(P,N) . 114PERIOD DIFF(P1,P2) . 115QUARTER(date) . 115SECOND(time) . 115SEC TO TIME(seconds). 115STR TO DATE(str,format) . 116SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days) . 116SUBTIME(expr1,expr2) . 116SYSDATE() . 116TIME(expr) . 117TIMEDIFF(expr1,expr2) . 117TIMESTAMP(expr), TIMESTAMP(expr1,expr2) . 117TIMESTAMPADD(unit,interval,datetime expr) . 117TIMESTAMPDIFF(unit,datetime expr1,datetime expr2) . 118TIME FORMAT(time,format) . 118TIME TO SEC(time) . 118TO DAYS(date) . 118UNIX TIMESTAMP(), UNIX TIMESTAMP(date) . 119UTC DATE, UTC DATE() . 119UTC TIME, UTC TIME() . 119UTC TIMESTAMP, UTC TIMESTAMP() . 120WEEK(date[,mode]) . 120TUTORIALS POINTSimply Easy Learning
WEEKDAY(date) . 120WEEKOFYEAR(date). 121YEAR(date) . 121YEARWEEK(date), YEARWEEK(date,mode) . 121ACOS(X) . 123ASIN(X) . 124ATAN(X) . 124ATAN2(Y,X) . 124BIT AND(expression) . 124BIT COUNT(numeric value) . 125BIT OR(expression). 125CEIL(X) . 125CEILING(X) . 125CONV(N,from base,to base) . 126COS(X). 126COT(X) . 126DEGREES(X) . 126EXP(X) . 127FLOOR(X) . 127FORMAT(X,D) . 127GREATEST(n1,n2,n3,.). 127INTERVAL(N,N1,N2,N3,.) . 128INTERVAL(N,N1,N2,N3,.) . 128LEAST(N1,N2,N3,N4,.) . 128LOG(X) . 129LOG(B,X) . 129LOG10(X) . 129MOD(N,M) . 129OCT(N). 129PI() . 130POW(X,Y) . 130POWER(X,Y) . 130RADIANS(X) . 130ROUND(X) . 130ROUND(X,D) . 130SIGN(X). 131SIN(X) . 131SQRT(X) . 132STD(expression) . 132TUTORIALS POINTSimply Easy Learning
STDDEV(expression) . 132TAN(X) . 132TRUNCATE(X,D) . 132ASCII(str) . 134BIN(N) . 135BIT LENGTH(str) . 135CHAR(N,. [USING charset name]) . 135CHAR LENGTH(str) . 135CHARACTER LENGTH(str) . 136CONCAT(str1,str2,.) . 136CONCAT WS(separator,str1,str2,.) . 136CONV(N,from base,to base) . 136ELT(N,str1,str2,str3,.) . 137EXPORT SET(bits,on,off[,separator[,number of bits]]) . 137FIELD(str,str1,str2,str3,.) . 137FIND IN SET(str,strlist). 137FORMAT(X,D) .
Simply Easy Learning ABOUT THE TUTORIAL MySQL Tutorial MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications. This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming. Audience
