![7 Pattern Description * Zero or more instances of string preceding it + One or more instances of strings preceding it . Any single character ? Match zero or one instances of the strings preceding it. ^ caret(^) matches Beginning of string $ End of string [abc] Any character listed between the square brackets [^abc] Any character not listed between the square brackets [A-Z] match any upper case letter. [a-z] match any lower case letter [0-9] match any digit from 0 through to 9. [:class:] matches a character class i.e. [:alpha:] to match letters, [:space:] to match white space, [:punct:] is match punctuations and [:upper:] for upper class letters. p1|p2|p3 Alternation;matches any of the patterns p1, p2, or p3 {n} n instances of preceding element {m,n} m through n instances of preceding element](https://image.slidesharecdn.com/dee431introductiontomysqlslide6-200409062316/75/DEE-431-Introduction-to-MySql-Slide-6-7-2048.jpg)
![8 Class Keyword Description of Matches [[:alnum:]] Alphanumeric - any number or letter. Equivalent to [a-z], [A-Z] and [0-9] [[:alpha:]] Alpha - any letter. Equivalent to [a-z] and [A-Z] [[:blank:]] Space or Tab. Equivalent to [t] and [ ] [[:cntrl:]] ASCII Control Character [[:digit:]] Numeric. Equivalent to [0-9] [[:graph:]] Any character with the exception of space [[:lower:]] Lower case letters. Equivalent to [a-z] [[:print:]] Any printable character [[:punct:]] Characters that are neither control characters, nor alphanumeric (i.e punctuation characters) [[:space:]] Any whitespace character (tab, new line, form feed, space etc) [[:upper:]] Upper case letters. Equivalent to [A-Z] [[:xdigit:]] Any hexadecimal digit. Equivalent to [A-F], [a-f] and [0-9]](https://image.slidesharecdn.com/dee431introductiontomysqlslide6-200409062316/75/DEE-431-Introduction-to-MySql-Slide-6-8-2048.jpg)
![CONTINUE… Matches any of the patterns p1, p2, or p3(p1|p2|p3): Gives all the names containing ‘an’ or ‘ha’. Example – Charles, Sumiran. SELECT sname FROM tc WHERE sname REGEXP ‘an|ha' ; Matches any character listed between the square brackets([abc]): Gives all the names containing ‘t’ or ‘z’. Example –Tessa, Natasha. SELECT sname FROM tc WHERE sname REGEXP '[tz]' ; Matches any lower case letter between ‘a’ to ‘z’- ([a-z]) ([a-z] and (.)): Retrieve all names that contain a letter in the range of ‘m’ and ‘s’, followed by any character, followed by the letter ‘a’. Example – Thomas, carnage. Matches any single character(.) SELECT sname FROM tc WHERE sname REGEXP '[m-s].[a]' ; 10](https://image.slidesharecdn.com/dee431introductiontomysqlslide6-200409062316/75/DEE-431-Introduction-to-MySql-Slide-6-10-2048.jpg)
![CONTINUE… Matches any character pattern which have n instance of preceding element. ({n}): Example – Luna, John. SELECT * FROM tc WHERE sname REGEXP ‘^.{4}$' ; Matches any character not listed between the square brackets.([^abc]): Gives all the names not containing ‘m’ or ‘o’. Example – Kevin, John. SELECT * FROM tc WHERE sname REGEXP ‘^[^mo]' ; Matches a character class[:class:]: i.e [:lower:]- lowercase character , [:digit:] – digit characters etc. Gives all the name containing alphabetic character only. SELECT sname FROM tc WHERE sname REGEXP '[:alpha:]' ; 11](https://image.slidesharecdn.com/dee431introductiontomysqlslide6-200409062316/75/DEE-431-Introduction-to-MySql-Slide-6-11-2048.jpg)
DEE 431 Introduction to MySql Slide 6
- 1. D.E.I TECHNICAL COLLEGE COURSETITLE:DATABASE PROGRAMMINGWITH SQL COURSE CODE: DEE431 SLIDE: 6 CLASS: DIPLOMA IN INFORMATIONTECHNOLOGYVOCATIONAL SEMESTER: FOURTH SESSION: 2019-20
- 2. TOPICS COVER REGULAREXPRESSIONS DATE/TIME FUNCTIONS INDEX 2
- 3. WHAT ARE REGULAREXPRESSIONS? As we know that, Databases are huge dumps of data where the data is stored in an organized manner. But many a time we come across situations where we need to retrieve some data but don’t have sufficient information to filter it out. For such cases, SQL provides an amazing feature called Regular Expressions. A regular expression is a special string that describes a search pattern. It is a powerful tool that gives you a concise and flexible way to identify strings of text e.g., characters, and words, based on patterns. A regular expression uses its own syntax that can be interpreted by a regular expression processor. A regular expression is widely used in almost platforms from programming languages to databases including MySQL. The abbreviation of regular expressions is regex or regexp. 3 Topic
- 4. WHY REGULAR EXPRESSIONWHEN WILDCARDS ALREADY EXIST? Regular Expressions help search data matching complex criteria. If you have worked with wildcards before, you may be asking why learn regular expressions when you can get similar results using the wildcards. Because, compared to wildcards, regular expressions allow us to search data matching even more complex criterion. The advantage of using regular expression is that you are not limited to search for a string based on a fixed pattern with the percent sign (%) and underscore (_) in the LIKE operator. The regular expressions have more meta-characters to construct flexible patterns. 4
- 5. SYNTAX FOR USINGSQL REGEX SELECT statements... WHERE field_name REGEXP 'my_pattern'; Explanation SELECT – Select is the standard SQL keyword to retrieve data from the table statements – This specifies the rows to be retrieved WHERE – WHERE clause is used to specify a condition while fetching the data field_name – It represents the name of a column on which the regular expression needs to be applied on. REGEXP – It is the keyword that precedes the RegEx pattern. my_pattern – It is the user-defined RegEx pattern to search data. 5
- 6. FEATURES OF REGULAREXPRESSIONS MySQL provides pattern matching operation based on the regular expressions and the REGEXP operator. 1. It provide a powerful and flexible pattern match that can help us implement power search utilities for our database systems. 2. REGEXP is the operator used when performing regular expression pattern matches. RLIKE is the synonym. 3. It also supports a number of metacharacter which allow more flexibility and control when performing pattern matching. 4. The backslash is used as an escape character. It’s only considered in the pattern match if double backslashes have used. 5. Not case sensitive. 6
- 7. 7 Pattern Description * Zeroor more instances of string preceding it + One or more instances of strings preceding it . Any single character ? Match zero or one instances of the strings preceding it. ^ caret(^) matches Beginning of string $ End of string [abc] Any character listed between the square brackets [^abc] Any character not listed between the square brackets [A-Z] match any upper case letter. [a-z] match any lower case letter [0-9] match any digit from 0 through to 9. [:class:] matches a character class i.e. [:alpha:] to match letters, [:space:] to match white space, [:punct:] is match punctuations and [:upper:] for upper class letters. p1|p2|p3 Alternation;matches any of the patterns p1, p2, or p3 {n} n instances of preceding element {m,n} m through n instances of preceding element
- 8. 8 Class Keyword Descriptionof Matches [[:alnum:]] Alphanumeric - any number or letter. Equivalent to [a-z], [A-Z] and [0-9] [[:alpha:]] Alpha - any letter. Equivalent to [a-z] and [A-Z] [[:blank:]] Space or Tab. Equivalent to [t] and [ ] [[:cntrl:]] ASCII Control Character [[:digit:]] Numeric. Equivalent to [0-9] [[:graph:]] Any character with the exception of space [[:lower:]] Lower case letters. Equivalent to [a-z] [[:print:]] Any printable character [[:punct:]] Characters that are neither control characters, nor alphanumeric (i.e punctuation characters) [[:space:]] Any whitespace character (tab, new line, form feed, space etc) [[:upper:]] Upper case letters. Equivalent to [A-Z] [[:xdigit:]] Any hexadecimal digit. Equivalent to [A-F], [a-f] and [0-9]
- 9. EXAMPLES Match beginningof string(^): Gives all the names starting with ‘al’. Example- allen, alice. SELECT sname FROM tc WHERE sname REGEXP '^al'; Match the end of a string($): Gives all the names ending with ‘er’. Example – Silver Surfer, Punisher. SELECT sname FROM tc WHERE sname REGEXP ‘er$'; Match zero or one instance of the strings preceding it(?): Gives all the sname containing ‘son’. Example – Johnson , Jason. SELECT sname FROM tc WHERE sname REGEXP ‘son?'; 9
- 10. CONTINUE… Matches anyof the patterns p1, p2, or p3(p1|p2|p3): Gives all the names containing ‘an’ or ‘ha’. Example – Charles, Sumiran. SELECT sname FROM tc WHERE sname REGEXP ‘an|ha' ; Matches any character listed between the square brackets([abc]): Gives all the names containing ‘t’ or ‘z’. Example –Tessa, Natasha. SELECT sname FROM tc WHERE sname REGEXP '[tz]' ; Matches any lower case letter between ‘a’ to ‘z’- ([a-z]) ([a-z] and (.)): Retrieve all names that contain a letter in the range of ‘m’ and ‘s’, followed by any character, followed by the letter ‘a’. Example – Thomas, carnage. Matches any single character(.) SELECT sname FROM tc WHERE sname REGEXP '[m-s].[a]' ; 10
- 11. CONTINUE… Matches anycharacter pattern which have n instance of preceding element. ({n}): Example – Luna, John. SELECT * FROM tc WHERE sname REGEXP ‘^.{4}$' ; Matches any character not listed between the square brackets.([^abc]): Gives all the names not containing ‘m’ or ‘o’. Example – Kevin, John. SELECT * FROM tc WHERE sname REGEXP ‘^[^mo]' ; Matches a character class[:class:]: i.e [:lower:]- lowercase character , [:digit:] – digit characters etc. Gives all the name containing alphabetic character only. SELECT sname FROM tc WHERE sname REGEXP '[:alpha:]' ; 11
- 12. DATE/TIME FUNCTION InSQL, dates are complicated for newbies, since while working with a database, the format of the date in the table must be matched with the input date in order to insert. In various scenarios instead of date, datetime (time is also involved with date) is used. 12 EXAMPLE FORMAT '2018-10-18' 'YYYY-MM-DD' '20181018' 'YYYYMMDD' 20181018 YYYYMMDD '18-10-18' 'YY-MM-DD' 181018 YYMMDD '2018-10-18 5:25:20' 'YYYY-MM-DD HH:MM:SS' '2018101852520' 'YYYYMMDDHHMMSS' 2018101852520 YYYYMMDDHHMMSS '18-10-18 5:25:20' 'YY-MM-DD HH:MM:SS' '18101852520' 'YYMMDDHHMMSS' 18101852520 YYMMDDHHMMSS Topic
- 13. VARIOUS DATE FORMAT Date format DayMonth Year Week 13 %d (01 to 31) %e (0 to 31) %D (st, nd, rd or th) %j (001 to 366) %Y (2020) %y (20) %a (Mon) %W (Monday) %w (0 to 6) %M (January) %b (Jan to Dec) %m (00 to 12) %c (0 to 12)
- 14. VARIOUSTIME FORMAT Time format Hour MinutesSeconds Microseconds Meridiem 14 %h (01 to 12) %H (00 to 23) %g (1 to 12) %G (0 to 23) %i (00 to 59) %s (00 to 59) %f (000000 to 999999) %p (AM or PM) 06:45:30:00 PM
- 15. DATE/TIME FUNCTIONSWITH SYNTAXAND EXAMPLES ADDDATE(): It returns a date after a certain time/date interval has been added. Syntax: SELECT ADDDATE("2020-04-09 07:54:50","8"); ADDTIME(): It returns a time / date time after a certain time interval has been added. Syntax: SELECT ADDTIME("2020-04-09 07:54:50", "8"); CURDATE(): It returns the current date. Syntax: SELECT CURDATE(); CURRENT_DATE(): It returns the current date. Syntax: SELECT CURRENT_DATE(); CURRENT_TIME(): It returns the current time. Syntax: SELECT CURRENT_TIME(); 15 OUTPUT: 2020-04-17 07:54:50 OUTPUT: 2020-04-09 07:54:58 OUTPUT: 2020-04-09 OUTPUT: 2020-04-09 OUTPUT: 09:06:25
- 16. 16 CURRENT_TIMESTAMP(): Itreturns the current date and time. Syntax: SELECT CURRENT_TIMESTAMP(); CURTIME(): It returns the current time. Syntax: SELECT CURTIME(); DATE(): It extracts the date value from a date or date time expression. Syntax: SELECT DATE("2020-04-09"); DATEDIFF(): It returns the difference in days between two date values. Syntax: SELECT DATEDIFF("2020-06-25","2020-06-10"); DATE_ADD(): It returns a date after a certain time/date interval has been added. Syntax: SELECT DATE_ADD("2020-04-09",INTERVAL 20 DAY); OUTPUT: 2020-04-09 09:10:49 OUTPUT: 09:11:34 OUTPUT: 2020-04-09 OUTPUT: 2020-04-29 OUTPUT: 15
- 17. 17 DATE_FORMAT(): Itformats a date as specified by a format mask. Syntax: SELECT DATE_FORMAT("2020-04-09","%Y"); DATE_SUB(): It returns a date after a certain time/date interval has been subtracted. Syntax: SELECT DATE_SUB("2020-04-09",INTERVAL 10 DAY); DAY(): It returns the day portion of a date value. Syntax: SELECT DAY("2020-04-09"); DAYNAME(): It returns the weekday name for a date. Syntax: SELECT DAYNAME('2020-04-09'); DAYOFMONTH(): It returns the day portion of a date value. Syntax: SELECT DAYOFMONTH('2020-04-09'); DAYWEEK(): It returns the weekday index for a date value. Syntax: SELECT WEEKDAY("2020-04-09"); OUTPUT: 2020 OUTPUT: 2020-03-30 OUTPUT: 9 OUTPUT: Thursday OUTPUT: 9 OUTPUT: 3
- 18. 18 DAYOFYEAR(): Itreturns the day of the year for a date value. Syntax: SELECT DAYOFYEAR("2020-04-09"); EXTRACT(): It extracts parts from a date. Syntax: SELECT EXTRACT(MONTH FROM "2020-04-09"); FROM_DAYS(): It returns a date value from a numeric representation of the day. Syntax: SELECT FROM_DAYS(685467); HOUR(): It returns the hour portion of a date value. Syntax: SELECT HOUR("2020-04-09 09:54:00"); LAST_DAY(): It returns the last day of the month for a given date. Syntax: SELECT LAST_DAY('2020-04-09'); LOCALTIME(): It returns the current date and time. Syntax: SELECT LOCALTIME(); OUTPUT: 100 OUTPUT: 4 OUTPUT: 1876-09-29 OUTPUT: 9 OUTPUT: 2020-04-30 OUTPUT: 2020-04-09 09:59:45
- 19. 19 LOCALTIMESTAMP(): Itreturns the current date and time. Syntax: SELECT LOCALTIMESTAMP(); MAKEDATE(): It returns the date for a certain year and day-of-year value. Syntax: SELECT MAKEDATE(2009,138); MAKETIME(): It returns the time for a certain hour, minute, second combination. Syntax: SELECT MAKETIME(11, 35, 4); OUTPUT: 2020-04-09 10:05:25 OUTPUT: 11:35:04 OUTPUT: 2009-05-18 NextTime Functions
- 20. 20 MICROSECOND(): Itreturns the microsecond portion of a date value. Syntax: SELECT MICROSECOND("2020-04-09 10:16:00.000345"); MINUTE(): It returns the minute portion of a date value. Syntax: SELECT MINUTE("2020-04-09 10:16:00.000345"); MONTH(): It returns the month portion of a date value. Syntax: SELECT MONTH ('2020/04/09')AS MONTH; MONTHNAME(): It returns the full month name for a date. Syntax: SELECT MONTHNAME("2020/04/09"); NOW(): It returns the current date and time. Syntax: SELECT NOW(); PERIOD_ADD(): It takes a period and adds a specified number of months to it. Syntax: SELECT PERIOD_ADD(202005,6); OUTPUT: 345 OUTPUT: 16 OUTPUT: 4 OUTPUT: April OUTPUT: 202011 OUTPUT: 2020-04-09 10:19:27
- 21. 21 PERIOD_DIFF(): Itreturns the difference in months between two periods. Syntax: SELECT PERIOD_DIFF(202010,202002); QUARTER(): It returns the quarter portion of a date value. Syntax: SELECT QUARTER("2020/04/09"); SECOND(): It returns the second portion of a date value. Syntax: SELECT SECOND(“10:29:00:00036"); SEC_TO_TIME(): It converts numeric seconds into a time value. Syntax: SELECT SEC_TO_TIME(5); STR_TO_DATE(): It takes a string and returns a date specified by a format mask. Syntax: SELECT STR_TO_DATE(“April 20 2020", "%M %D %Y"); SUBDATE(): It returns a date after which a certain time/date interval has been subtracted. Syntax: SELECT SUBDATE("2020-04-09",INTERVAL 10 DAY); OUTPUT: 8 OUTPUT: 2 OUTPUT: 0 OUTPUT: 00:00:05 OUTPUT: 0020-04-20 OUTPUT: 2020-03-30
- 22. 22 SYSDATE(): Itreturns the current date and time. Syntax: SELECT SYSDATE(); TIME(): It extracts the time value from a time/date time expression. Syntax: SELECT TIME(“10:42:10"); TIME_FORMAT(): It formats the time as specified by a format mask. Syntax: SELECT TIME_FORMAT(“10:42:10","%H %I %S"); TIME_TO_SEC(): It converts a time value into numeric seconds. Syntax: SELECT TIME_TO_SEC(“10:42:10"); TIMEDIFF(): It returns the difference between two time/datetime values. Syntax: SELECT TIMEDIFF("09:16:10", "09:16:04"); TIMESTAMP(): It converts an expression to a date time value and if specified adds an optional time interval to the value. Syntax: SELECT TIMESTAMP("2020-04-09","09:16:10"); OUTPUT: 2020-04-09 10:44:39 OUTPUT: 10:42:10 OUTPUT: 10 10 10 OUTPUT: 38530 OUTPUT: 00:00:06 OUTPUT: 2020-04-09 09:16:10
- 23. 23 TO_DAYS(): Itconverts a date into numeric days. Syntax: SELECT TO_DAYS("2020-04-09"); WEEK(): It returns the week portion of a date value. Syntax: SELECT WEEK("2020-04-09"); WEEKDAY(): It returns the weekday index for a date value. Syntax: SELECT WEEKDAY("2020-04-09"); WEEKOFYEAR(): It returns the week of the year for a date value. Syntax: SELECT WEEKOFYEAR("2020-04-09"); YEAR(): It returns the year portion of a date value. Syntax: SELECT YEAR("2020-04-09"); YEARWEEK(): It returns the year and week for a date value. Syntax: SELECT YEARWEEK("2020-04-20"); OUTPUT: 737889 OUTPUT: 14 OUTPUT: 202016 OUTPUT: 15 OUTPUT: 3 OUTPUT: 2020
- 24. INDEX PERFORMANCETURNING METHOD ALLOWS FASTERRETRIEVAL OF RECORDS FROMTHETABLE CREATES AN ENTRY FOR EACHVALUE 24 Topic
- 25. WHY INDEXES AREUSED? The slowness in the response time is usually due to the records being stored randomly in database tables. This results in poor performance databases when it comes to retrieving data from large tables Indexes come in handy in such situations. Indexes sort data in an organized sequential way. Think of an index as an alphabetically sorted list. It is easier to lookup names that have been sorted in alphabetical order than ones that are not sorted. INDEX's are created on the column(s) that will be used to filter the data. Using indexes on tables that are frequently updated can result in poor performance. This is because MySQL creates a new index block every time that data is added or updated in the table. Generally, indexes should be used on tables whose data does not change frequently but is used a lot in select search queries. 25
- 26. TYPES OF INDEX 26 unique •This index does not allow the field to have duplicate values if the column is unique indexed. • If a primary key is defined, a unique index can be applied automatically. Clustered • This index records the physical order of the table and searches based on the basis of key values. • Each table can only have one clustered index. Non- Clustered • Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the data. • Each table can have many non-clustered indexes.
- 27. DIFFERENCE BETWEEN 27 Clustered IndexNon-Clustered index Clustered index is used for easy retrieval of data from the database and is faster. Non-Clustered index is used for easy retrieval of data from the database and is slower. Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index. Non Clustered index does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching. One table can only have one clustered index. One table can have many non-clustered indexes.
- 28. SOME FACTS RELATEDTOINDEXES Index only those columns that you frequently use to retrieve the data. Index columns that are used for joins to improve join performance. Need to avoid creation of indexes for small tables. Also avoid those columns that contain too many NULL values. Indexes are automatically creates the for PRIMARY KEY and UNIQUE columns. 28
- 29. SQL CREATE INDEXSYNTAX The CREATE INDEX statement is used to create indexes in tables. Syntax Creates an index on a table. Duplicate values are allowed: CREATE INDEX index_name ON table_name (column1, column2, ...); Syntax Creates a unique index on a table. Duplicate values are not allowed: CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...); 29
- 30. EXAMPLE The statementbelow creates an index named “<SDBIN>" on the “DOB" column in the “Student" table: If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas: 30 CREATE INDEX SDBIN ON Student (DOB); CREATE INDEX DBB ON Student (DOB, Branch);
- 31. DROP AND SHOWINDEX The DROP INDEX statement is used to delete an index in a table. Syntax: SHOW INDEX command to list out all the indexes associated with a table. 31 ALTERTABLE table_name DROP INDEX index_name; SHOW INDEX FROM table_name;
- 32.