1 / 37

Enhancing Database Skills: Understanding Built-in Functions and Queries in MySQL

This course provides an in-depth exploration of essential database concepts and tools, focusing on creating and managing databases using MySQL. Participants will learn about database architecture, data flow diagrams (DFDs), entity-relationship diagrams (ERDs), query formulation using relational algebra, and schema implementation. Key topics include built-in functions (mathematical, string, date, and system), data extraction, normalization, and performance improvement techniques. By the end of the course, learners will confidently work with joins and advanced database functionalities.

kacy
Télécharger la présentation

Enhancing Database Skills: Understanding Built-in Functions and Queries in MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. M Taimoor Khan taimoorkhan@ciit-attock.edu.pk builtin functions

  2. Course Objectives • Basic Concepts • Tools • Database architecture and design • Flow of data (DFDs) • Mappings (ERDs) • Formulating queries (Relational algebra) • Implementing Schema • Built-in Functions • Extracting data • Working with Joins • Normalization • Improving performance • Advanced topics

  3. Built-in Functions • Mathematical • String • Date • System • Aggregate

  4. Built-in Functions • System • Math • Date

  5. Categories of Functions • Depending on the arguments and the return value, categorized • Mathematical (ABS, ROUND, SIN, SQRT) • String (LOWER, UPPER, SUBSTRING, LEN) • Date (DATEDIFF, DATEPART, GETDATE()) • System (USER, DATALENGTH, HOST_NAME) • Conversion (CAST, CONVERT)

  6. System Functions

  7. DB server version, Current database

  8. Current user logged in

  9. Current CHARSET, COLLATION

  10. Math Functions

  11. Random function

  12. Absolute value, PI, sin

  13. Binary, octal, hexa-decimal

  14. Ceiling, floor, rounding off

  15. Power, square-root

  16. degrees

  17. Aggregate functions

  18. Minimum, Maximum, Average

  19. Sum, Count, Standard deviation, variance

  20. Counting Rows • Databases are often used to answer the question, "How often does a certain type of data occur in a table?" • For example, you might want to know how many pets you have, or how many pets each owner has. • Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet. • The COUNT() function counts the number of non-NULL results.

  21. Counting Rows Example • A query to determine total number of pets: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+

  22. String functions

  23. Length, Upper case, Lower case

  24. Reverse order, repeat characters

  25. String compare, concatenation

  26. replace

  27. Date functions

  28. Day name, year, Month name

  29. Now – date and time

  30. Current time, date

  31. Date difference

  32. Day name, month name

  33. Week of year, week day

  34. Formatting date

  35. Add into date, subtracting from date

  36. Built-in Functions • Mathematical • String • Date • System • Aggregation

  37. Next Lecture • MySQL JOINS

More Related