Introduction to My SQL Lab no 9 Advance

Introduction to My. SQL Lab no. 9 Advance Database Management System

Lab Outline • My. SQL PHP

What is My. SQL? • My. SQL is the most popular open-source (relational) database system.

My. SQL • My. SQL can be scaled down to support embedded database applications. Perhaps because of this reputation many people believe that My. SQL can only handle small to medium-sized systems. • The truth is that My. SQL is the de-facto standard database for web sites that support huge volumes of both data and end users (like Friendster, Yahoo, Google).

Starting My. SQL Console • Left click WAMP server icon on task bar • Click My. SQL console

My. SQL Console • • • The console will ask for password. Press Enter key You will see mysql prompt You can now start typing commands… Try the following: mysql> show databases;

Create a Database • The CREATE DATABASE statement is used to create a database in My. SQL. • Syntax CREATE DATABASE database_name • Example mysql> CREATE DATABASE guestbook;

Creating a Table • Syntax CREATE TABLE table_name ( column_name 1 data_type, column_name 2 data_type, column_name 3 data_type, . . ); • Example mysql>CREATE TABLE profile ( ID int, Name varchar(30) ); Note: you must specify database on prompt before creating a table mysql> use database_name;

Column Data Types • String types – – – – Char Varchar tinytext/tinyblob text/blob mediumtext/mediumblob longtext/longblob enum set

Column Data Types • Numeric types – int/integer – tinyint – mediumint – bigint – float – double/double precision/real – decimal/numeric

Column Data Types • Date Time Data Types – datetime – timestamp – time – year

Column Characteristics • Unique – when this parameter is turned on, My. SQL makes sure that absolutely no duplicates exist for a particular field. – it can be used with any field • Auto Increment – automatically increases column value by one whenever a new record is added. – You don’t have to worry about what the last ID number was; the field automatically keeps track for you. • null/not null – whether or not the field can be empty – If a field has been defined as not null and nothing is entered by the user, My. SQL will enter a “ 0” in the field instead of producing an error

Example CREATE TABLE grocery_inventory ( id int not null primary key auto_increment, item_name varchar (50) not null, item_desc text, item_price float not null, curr_qty int not null );

Detailed Description of Data Types

char Usage: char (length) Description: – Max length supported 255 characters – Fixed Length Type (Spaces are padded from the right). Spaces are removed on data retrieval. – Defining a length is not required. Default is 1. – Automatic Truncation for more than 255 Characters

Var. Char • Usage: varchar(length) • Description: – Max length supported 255 characters – Varible Length Type (Value is adjusted as per value) – Automatic Truncation for more than 255 Characters • Note: If you define a column as varchar with a column length of less than four, My. SQL will automatically change the column to the char type. Similarly, if you try to mix chars and varchars with a column length of more than four, they all become varchars.

Text / Blob • Usage: text/blob – 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.

Tiny Medium and Long text /blob • 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 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT. • LONGBLOB or LONGTEXT – A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.

enum - An enumeration 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 either "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. - ENUMs can have 65535 different values. - Example: create table my_table ( id int auto_increment primary key, answer enum (‘yes’, ‘no’) default ‘no’ );

set • Syntax: set (‘value 1’, ‘value 2’, ‘value 3’ ? ) [default ‘value’] • This column type defines a superset of values. It allows for zero or more values from the list you specify to be included in a field.

Numeric Types • • int/integer tinyint mediumint bigint float double/double precision/real decimal/numeric

Int/integer • Syntax: int(display size) [unsigned] [zerofill] – With unsigned flag, 0 to 4, 294, 967, 295. – With signed flag, 2, 147, 483, 648 to 2, 147, 483, 647. – int will often be used with auto_increment to define the primary key of a table. • Usage: create table my_table ( table_id int unsigned auto_increment primary key, next_column text );

tinyint • Syntax: tinyint(display size) [unsigned] [zerofill] – If unsigned, tinyint stores integers between 0 and 255. – If signed, the range is from -128 to 127. – Zerofill is optional like unsigned. • Usage: create table my_table 2 ( table_id tinyint(150) unsigned zerofill, next_column text );

mediumint • Syntax: mediumint(display size) [unsigned] [zerofill] – With unsigned flag, mediumint stores integers between 8, 388, 608 and 8, 388, 607. – With signed flag, the range is from 0 to 1677215. • Usage: create table my_table 2 ( table_id mediumint(150) unsigned zerofill, next_column text );

Float • Float has two distinct usages. • Syntax: float(precision) [zerofill] – In this usage, float stores a floating-point number and cannot be unsigned. – The precision attribute can be ≤ 24 for a single-precision floating-point number, and between 25 and 53 for a double-precision floating-point number. • Syntax: float[(M, D)] [zerofill] – This is a small (single-precision) floating-point number and cannot be – unsigned. Allowable values are -3. 402823466 E+38 to -1. 175494351 E-38, zero, and 1. 175494351 E-38 to 3. 402823466 E+38. – M is the display width and D is the number of decimals. If the float attribute is used without an argument or with an argument of ≤ 24, the column will store a single-precision floating-point number.

double/double precision/real • Syntax: double[(M, D)] [zerofill] – This column stores a double-precision floating-point number and cannot be unsigned. – Allowable values are – 1. 7976931348623157 E+308 to 2. 2250738585072014 E-308, zero, and 2. 2250738585072014 E 308 to 1. 7976931348623157 E+308. – M is the display width and D is the number of decimals.

decimal • Syntax: decimal[(M[, D])] [zerofill] • Numbers in a decimal column are stored as characters. Each number is stored as a string, with one character for each digit of the value. • M is the display width, and D is the number of decimals. If M is left out, it’s set to 10. If D is 0, values will have no decimal point. The maximum range of decimal values is the same as for double. • Remember, though, that decimal, like all real types, can cause rounding errors.

Date Time Data Type • • • datetimestamp time year

Example & sample usage • create table date_test( id int unsigned auto_increment primary key, a_date ); • The following insert statements are all interpreted correctly by My. SQL: – – insert into date_test (a_date) values (‘ 00 -06 -01’); insert into date_test (a_date) values (‘ 20000601’); insert into test 6 (a_date) values (000601);

Usage of Date Time • date – Usage: date – The date column type stores values in the format YYYY-MM-DD. It will allow values between 1000 -01 -01 and 9999 -12 -31. • datetime – Usage: datetime [null | not null] [default] – The datetime type stores values in the format YYYY-MM-DD HH: MM: SS. It will allow values between 1000 -01 -01 00: 00 and 9999 -12 -31 23: 59. • timestamp – Usage: timestamp(size) – This is a handy column type that will automatically record the time of the most recent change to a row, whether from an insert or an update. Size can be defined as any number between 2 and 14.

Timestamp formats

• Use this command to view all tables in your database: • mysql> show tables;

Insert Command • A statement with all columns named: insert into grocery_inventory (id, item_name, item_desc, item_price, curr_qty) values ('1', 'Apples', 'Beautiful, ripe apples. ', '0. 25', 1000); • A statement that uses all columns but does not explicitly name them: insert into grocery_inventory values ('2', 'Bunches of Grapes', 'Seedless grapes. ', '2. 99', 500); • Read Sams e-book to understand auto increment fields

Select Command • Syntax: SELECT expressions_and_columns FROM table_name [WHERE some_condition_is_true] [ORDER BY some_column [ASC | DESC]] [LIMIT offset, rows] • Example: select * from grocery_inventory; LIMIT clause is used to return only a certain number of records in your SELECT query result. The offset is the starting position of row

My. SQL Tables • Two types of tables: – transaction-safe tables (TSTs) – non-transaction-safe tables (NTSTs). • TSTs – Transaction-safe tables allow lost data to be recovered, or a rollback of data to revert changes recently made. • NTSTs – Non-transaction-safe tables are much faster and require much less memory to process updates – changes are permanent

My. SQL Storage Engines • Current version of My. SQL uses five main types of storage engines to store and update data in these tables (TST, NTST): – My. ISAM – MERGE – MEMORY (formerly known as HEAP) – Inno. DB – BDB

Storage Engines (contd. . ) • My. ISAM – default storage engine – Usually sufficient for the average user’s needs. – supports all the field types, parameters, and functions. – supports NTSTs – replaces the ISAM storage engine from long ago.

Storage Engines (contd. . ) • MERGE – can manipulate several identical My. ISAM tables as one entity. – supports NTSTs.

Storage Engines (contd. . ) • MEMORY – mostly used for temporary tables because of their incredible speed – they don’t support a lot of the common features of the My. ISAM table, such as auto_increment and blob/text columns. – This type should be used in unique circumstances only. • for example, when working with user logs, if you wanted to store the information in a temporary table. – supports NTSTs.

Storage Engines (contd. . ) • Inno. DB – This type, along with the BDB type, supports TSTs. – meant for extremely large and frequently accessed applications. – “row-locking” mechanism to prevent different users from attempting to change or add the same row to the table. – According to the source Web site, one instance of this type of table has been shown to support 800 inserts and updates per second! – You can also read more about this type at its – own Web site: www. innodb. com.

Storage Engines (contd. . ) • BDB – BDB, or Berkeley. DB, is the other type of table that supports TSTs. – It is actually its own entity that works closely with the My. SQL server and can be downloaded from www. sleepycat. com. – Like Inno. DB tables, • it is meant to support very large applications with literally thousands of users attempting to insert and update the same data at the same time. – There is a complete reference manual available at its source Web site.

• mysql> SHOW ENGINESG ************** 1. row ************** Engine: My. ISAM Support: DEFAULT Comment: Default engine as of My. SQL 3. 23 with great performance ************** 2. row ************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables ************** 3. row ************** Engine: Inno. DB Support: YES Comment: Supports transactions, row-level locking, and foreign keys ************** 4. row ************** Engine: Berkeley. DB Support: NO Comment: Supports transactions and pagelevel locking ************** 5. row ************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears). . .

Introduction to php. My. Admin • Left click on WAMP server icon and select php. My. Admin • Your action will open php. My. Admin in your browser window

php. My. Admin • While exploring this GUI based utility, we will learn how to – Create a database – Create/design tables – Set table column/field characteristics – Modify existing table design options – Insert, select, update, delete data rows – Dropping tables and databases