際際滷

際際滷Share a Scribd company logo

MySql

Download as PPTX, PDF
Sukrit Gupta
Sukrit Gupta

MySQL is a relational database management system written in C and C++. It works across many platforms and is designed to be fully multi-threaded to take advantage of multiple CPUs. It supports large databases with flexible security and has over a dozen data types including numeric, date/time, and string types. MySQL uses storage engines like MyISAM and InnoDB to store and retrieve data, with InnoDB being the default and most widely used as it supports transactions and row-level locking. MySQL also includes many built-in mathematical, aggregate, string, and date/time functions to manipulate data.

1 of 32
MySQL at a Glance Prepared by: Sahil Goel
Features of MySql Written in C and C++. Works on many different platforms. Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available. A privilege and password system that is very flexible and secure. Support for large databases. e.g.: dev.mysql.com use MySQL Server with databases that contain 50 million records. Some users use MySQL Server with 200,000 tables and about 5,000,000,000 rows. Support for upto 64 indexes per table.
Data Types Properly defining the fields in a table is important to the overall optimization of our database. We should use only the type and size of field we really need to use; don't define a field as 10 characters wide if we know we're only going to use 2 characters. MySQL uses many different data types, broken into three categories: -numeric, -date and time, and - string types.
Numeric Data Types: INT - A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -214,74,83,648 to 214,74,83,647. If unsigned, the allowable range is from 0 to 429,49,67,295. TINYINT - A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. SMALLINT - A small integer that can be signed or unsigned. If signed, the allowable range is from -32,768 to 32,767. If unsigned, the allowable range is from 0 to 65,535. MEDIUMINT - A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -83,88,608 to 83,88,607. If unsigned, the allowable range is from 0 to 16777215. BIGINT - A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615.
Numeric Data Types(Cont.): FLOAT(M,D) - A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT. DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE. DECIMAL(M,D) - Values for DECIMAL columns in MySQL 5.6 are stored using a binary format that packs nine decimal digits into 4 bytes and any remaining digits left over require some fraction of 4 bytes.
Date and Time Types: DATE - A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30. DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00. TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ). TIME - Stores the time in HH:MM:SS format. YEAR(M) - Stores a year in 2-digit or 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be 1970 to 2069 (70 to 69). If the length is specified as 4, YEAR can be 1901 to 2155. The default length is 4
String Types CHAR(M) - A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1. VARCHAR(M) - A variable-length string between 1 and 65535 characters in length; for example VARCHAR(25). You must define a length when creating a VARCHAR field. BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.
String Types(Cont.): TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT. MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length of 167,77,215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT. LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of 429,49,67,295 characters. You do not specify a length with LONGBLOB or LONGTEXT. ENUM - An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain "A" or "B" or "C", you would define your ENUM as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that field.
Storage Engine A storage engine is a software module that a database List of storage engines management system uses to create, read, update data MyISAM from a database. There are InnoDB two types of storage engines Memory in MySQL. Transactional and CSV non-transactional. Merge Archive The default storage engine for Blackhole MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB. Choosing the right storage engine is an important strategic decision.
Storage Engines MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used most in Web, data warehousing. InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row- level locking, crash recovery and multi-version concurrency control Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted. CSV stores data in csv files. It provides great flexibility, because data in this format is easily integrated into other applications.
Storage Engines(Cont.): Merge operates on underlying MyISAM tables. Merge tables help manage large volumes of data more easily. It logically groups a series of identical MyISAM tables, and references them as one object. Good for data warehousing environments. Archive storage engine is optimized for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing, retrieving large amounts of seldom referenced historical, archived data. The Blackhole storage engine accepts but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally. This storage engine can be used to perform performance tests or other testing.
MySQL functions MySQL built-in functions can be categorized into several groups: Mathematical functions Aggregate functions String functions Date and time functions System Functions
Mathematical functions
Mathematical functions(Cont.)
Mathematical functions(Cont.)
Aggregate functions We have the Cars table.
Aggregate functions(Cont.)
String functions
String functions(Cont.)
String functions(Cont.)
Date & time functions
Date & time functions(Cont.)
Date & time functions(Cont.)
Date & time functions(Cont.)
System Functions
MySQL Charset/Collate A "character" is composed of one or more bytes, and represents what we think of when reading. A byte can represent only 256 different values. There are over 11,000 Korean characters and over 40,000 Chinese characters -- no way to squeeze such a character into a single byte. Charset vs. collation. These are different things! 'Charset' refers to the bits used to represent 'characters'. 'Collation' refers to how those bits could be compare for inequality (WHERE) and sorting (ORDER BY). GROUP BY and FOREIGN KEY CONSTRAINTS can also involve collation. And it even can involve deciding whether two different bit strings compare 'equal'. We can specify character sets at the server, database, table, and column level.
Collations have these general characteristics Two different character sets cannot have the same collation. Each character set has one collation that is the default collation. There is a convention for collation names: They start with the name of the character set with which they are associated, they usually include a language name, and they end with _ci (case insensitive), _cs (case sensitive), or _bin (binary).
Few Design Tips If we choose our Numeric Data Type as: INT(1) BIGINT AUTO_INCREMENT No UNSIGNED used DECIMAL(31,0)
Few Design Tips(Cont.) Choosing our Numeric Data Type INT(1) - 1 does not mean 1 digit (1) represents client output display format only INT is 4 Bytes, TINYINT is 1 Byte TINYINT UNSIGNED can store from 0 255 BIT is even better when values are 0 - 1 BIGINT is not needed for AUTO_INCREMENT INT UNSIGNED stores 4.3 billion values Best Practice- All integer columns UNSIGNED unless there is a reason otherwise Using Codes or ENUM- e.g. 'M', 'F' instead of 'Male', 'Female e.g. 'A', 'I' instead of 'Active', 'Inactive NOT NULL- Saves up to a byte per column per row of data Double bene鍖t for indexed columns
Few Design Tips(Cont.) Know about character sets Can be de鍖ned at database, schema, table or column level (if using UTF8) Only de鍖ne columns that need UTF8 e.g. Codes, MD5 Value, web address e.g. VARCHAR(255) utf8 is 765 bytes to store just 1 byte Using SELECT * MySQL Internal Temporary table will force Temp Disk Table Know Every SQL Statement Developers don't write proper SQL statements SQL statements will directly affect our performance For Example : Repeating SQL statements for no bene鍖t 1000 very quick small unnecessary queries is worse then 1 slow query
Few Design Tips(Cont.) Naming Standards Name all Primary Key兵s Uniquely e.g. customer_id, order_id not id Be Descriptive e.g. invoice_date not just date Avoid Reserved Words e.g. date, time
Thank You

More Related Content

MySql

  • 1. MySQL at a Glance Prepared by: Sahil Goel
  • 2. Features of MySql Written in C and C++. Works on many different platforms. Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available. A privilege and password system that is very flexible and secure. Support for large databases. e.g.: dev.mysql.com use MySQL Server with databases that contain 50 million records. Some users use MySQL Server with 200,000 tables and about 5,000,000,000 rows. Support for upto 64 indexes per table.
  • 3. Data Types Properly defining the fields in a table is important to the overall optimization of our database. We should use only the type and size of field we really need to use; don't define a field as 10 characters wide if we know we're only going to use 2 characters. MySQL uses many different data types, broken into three categories: -numeric, -date and time, and - string types.
  • 4. Numeric Data Types: INT - A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -214,74,83,648 to 214,74,83,647. If unsigned, the allowable range is from 0 to 429,49,67,295. TINYINT - A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. SMALLINT - A small integer that can be signed or unsigned. If signed, the allowable range is from -32,768 to 32,767. If unsigned, the allowable range is from 0 to 65,535. MEDIUMINT - A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -83,88,608 to 83,88,607. If unsigned, the allowable range is from 0 to 16777215. BIGINT - A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615.
  • 5. Numeric Data Types(Cont.): FLOAT(M,D) - A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT. DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE. DECIMAL(M,D) - Values for DECIMAL columns in MySQL 5.6 are stored using a binary format that packs nine decimal digits into 4 bytes and any remaining digits left over require some fraction of 4 bytes.
  • 6. Date and Time Types: DATE - A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30. DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973-12-30 15:30:00. TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ). TIME - Stores the time in HH:MM:SS format. YEAR(M) - Stores a year in 2-digit or 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be 1970 to 2069 (70 to 69). If the length is specified as 4, YEAR can be 1901 to 2155. The default length is 4
  • 7. String Types CHAR(M) - A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1. VARCHAR(M) - A variable-length string between 1 and 65535 characters in length; for example VARCHAR(25). You must define a length when creating a VARCHAR field. BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.
  • 8. String Types(Cont.): TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT. MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length of 167,77,215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT. LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of 429,49,67,295 characters. You do not specify a length with LONGBLOB or LONGTEXT. ENUM - An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain "A" or "B" or "C", you would define your ENUM as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that field.
  • 9. Storage Engine A storage engine is a software module that a database List of storage engines management system uses to create, read, update data MyISAM from a database. There are InnoDB two types of storage engines Memory in MySQL. Transactional and CSV non-transactional. Merge Archive The default storage engine for Blackhole MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB. Choosing the right storage engine is an important strategic decision.
  • 10. Storage Engines MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used most in Web, data warehousing. InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row- level locking, crash recovery and multi-version concurrency control Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted. CSV stores data in csv files. It provides great flexibility, because data in this format is easily integrated into other applications.
  • 11. Storage Engines(Cont.): Merge operates on underlying MyISAM tables. Merge tables help manage large volumes of data more easily. It logically groups a series of identical MyISAM tables, and references them as one object. Good for data warehousing environments. Archive storage engine is optimized for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing, retrieving large amounts of seldom referenced historical, archived data. The Blackhole storage engine accepts but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally. This storage engine can be used to perform performance tests or other testing.
  • 12. MySQL functions MySQL built-in functions can be categorized into several groups: Mathematical functions Aggregate functions String functions Date and time functions System Functions
  • 16. Aggregate functions We have the Cars table.
  • 21. Date & time functions
  • 22. Date & time functions(Cont.)
  • 23. Date & time functions(Cont.)
  • 24. Date & time functions(Cont.)
  • 26. MySQL Charset/Collate A "character" is composed of one or more bytes, and represents what we think of when reading. A byte can represent only 256 different values. There are over 11,000 Korean characters and over 40,000 Chinese characters -- no way to squeeze such a character into a single byte. Charset vs. collation. These are different things! 'Charset' refers to the bits used to represent 'characters'. 'Collation' refers to how those bits could be compare for inequality (WHERE) and sorting (ORDER BY). GROUP BY and FOREIGN KEY CONSTRAINTS can also involve collation. And it even can involve deciding whether two different bit strings compare 'equal'. We can specify character sets at the server, database, table, and column level.
  • 27. Collations have these general characteristics Two different character sets cannot have the same collation. Each character set has one collation that is the default collation. There is a convention for collation names: They start with the name of the character set with which they are associated, they usually include a language name, and they end with _ci (case insensitive), _cs (case sensitive), or _bin (binary).
  • 28. Few Design Tips If we choose our Numeric Data Type as: INT(1) BIGINT AUTO_INCREMENT No UNSIGNED used DECIMAL(31,0)
  • 29. Few Design Tips(Cont.) Choosing our Numeric Data Type INT(1) - 1 does not mean 1 digit (1) represents client output display format only INT is 4 Bytes, TINYINT is 1 Byte TINYINT UNSIGNED can store from 0 255 BIT is even better when values are 0 - 1 BIGINT is not needed for AUTO_INCREMENT INT UNSIGNED stores 4.3 billion values Best Practice- All integer columns UNSIGNED unless there is a reason otherwise Using Codes or ENUM- e.g. 'M', 'F' instead of 'Male', 'Female e.g. 'A', 'I' instead of 'Active', 'Inactive NOT NULL- Saves up to a byte per column per row of data Double bene鍖t for indexed columns
  • 30. Few Design Tips(Cont.) Know about character sets Can be de鍖ned at database, schema, table or column level (if using UTF8) Only de鍖ne columns that need UTF8 e.g. Codes, MD5 Value, web address e.g. VARCHAR(255) utf8 is 765 bytes to store just 1 byte Using SELECT * MySQL Internal Temporary table will force Temp Disk Table Know Every SQL Statement Developers don't write proper SQL statements SQL statements will directly affect our performance For Example : Repeating SQL statements for no bene鍖t 1000 very quick small unnecessary queries is worse then 1 slow query
  • 31. Few Design Tips(Cont.) Naming Standards Name all Primary Key兵s Uniquely e.g. customer_id, order_id not id Be Descriptive e.g. invoice_date not just date Avoid Reserved Words e.g. date, time

Editor's Notes

  1. 1)The RAND() function returns a random number from the <0, 1> interval.2) The ABS() function returns the absolute value of a number. The PI() function gives the value of PI. And the SIN() function computes the sine of an argument.
  2. 1)The CEIL() function rounds the value to the smallest following integer. The FLOOR() function rounds the value to the largest previous integer. The ROUND() returns a number rounded to a specified number of decimal places. 2) We use functions to give binary, octal and hexadecimal representation of decimal 22.
  3. pow(base, exponent)Converts radians to degrees
  4. 1)We use the MIN(), MAX() and AVG() aggregate functions to compute the minimal price, maximal price and the average price of cars in the table.2) We use the SUM() function to get the sum of all values in the Cost column. We count the number of cars in the table with the COUNT() function. Finally, we get the standard deviance and variance using the STD() and VARIANCE() functions.
  5. The LENGTH() function returns the length of a string. The UPPER() function converts characters into upper-case letters. The LOWER() function converts characters into lower-case letters.We use the LPAD() and RPAD() functions to append and prepend characters to a specified string. The "ZetCode" string has 7 characters. The RPAD() function appends 3 '*' characters to the string, which will be now 10 characters long.
  6. The REVERSE() function reverses the characters in a string. The REPEAT() function repeats a string specified number of times.The LEFT() function returns 3 leftmost characters, the RIGHT() function returns 3 characters from the right. The SUBSTRING() function returns three characters from the third position of the string.
  7. The STRCMP() compares two strings and returns 0, if they are the same. The CONCAT() function concatenates two strings.The REPLACE() function returns a string, in which we have replaced some text. The first parameter is the original string. The second parameter is a string, we want to replace. And the last parameter is the new replacing string.
  8. The NOW() function returns the current date and time. In MySQL, date is written in the format YYYY-MM-DD. Year is followed by month and day. They can be separated by slash or by hyphen. MySQL also supports a shortened date format, without separators. Time is written in a standard form, HH:MM:SS. Hours followed by minutes and seconds.
  9. The CURTIME() returns the current time and the CURDATE() returns the current date. With the DATEDIFF() we get the number of days between two dates.
  10. The DAYNAME() function returns the day name of a date. The MONTHNAME() function returns a month name of a date.January 23, 2011 can be written in a shortened date format, 110123. We use the WEEKOFYEAR() to find out the week of the year. The WEEKDAY() returns 6, which is Sunday. And the QUARTER() function returns the quarter of the year.
  11. To display date in a different format, we use the DATE_FORMAT(). We can use DATE_ADD() to add time intervals to a date and SUBDATE() to subtract time intervals from a date.
  12. We get the version of the MySQL database and the current database name.The USER() function returns the user name and the host name provided by the client.The CHARSET() function returns the character set of the argument. The COLLATION() returns the collation of the current string argument. They depend on the charset and collation of the client in use.