GAME203 QUERIES & TRANSACTIONS

GAME203QUERIES & TRANSACTIONS

Specifying the Date Format mysql> select date_format(date_joined, '%m/%d/%Y') -> from sales_rep where employee_number=1; +--------------------------------------+ | date_format(date_joined, '%m/%d/%Y') | +--------------------------------------+ | 02/15/2000 | +--------------------------------------+ 1 row in set (0.00 sec)

Specifying the Date Format mysql> select date_format(date_joined, '%m/%d/%y') -> from sales_rep where employee_number=1; +--------------------------------------+ | date_format(date_joined, '%m/%d/%y') | +--------------------------------------+ | 02/15/00 | +--------------------------------------+ 1 row in set (0.00 sec)

Specifying the Date Format mysql> select date_format(date_joined, '%W %M %e %y') -> from sales_rep where employee_number=1; +-----------------------------------------+ | date_format(date_joined, '%W %M %e %y') | +-----------------------------------------+ | Tuesday February 15 00 | +-----------------------------------------+ 1 row in set (0.00 sec)

mysql> select date_format(date_joined, '%W %M %e, %Y') -> from sales_rep where employee_number=1; +------------------------------------------+ | date_format(date_joined, '%W %M %e, %Y') | +------------------------------------------+ | Tuesday February 15, 2000 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_format(date_joined, '%a %D %b, %Y') -> from sales_rep where employee_number=1; +------------------------------------------+ | date_format(date_joined, '%a %D %b, %Y') | +------------------------------------------+ | Tue 15th Feb, 2000 | +------------------------------------------+ 1 row in set (0.00 sec)

Returning the Current Date and Time mysql> SELECT NOW(), CURRENT_DATE(); +---------------------+----------------+ | now() | current_date() | +---------------------+----------------+ | 2005-01-17 10:15:33 | 2005-01-17 | +---------------------+----------------+ 1 row in set (0.00 sec)

Date and Time Functions mysql> SELECT YEAR(birthday) FROM sales_rep; +----------------+ | year(birthday) | +----------------+ | 1976 | | 1958 | | 1971 | | 1982 | | 1949 | | 1950 | | 1965 | +----------------+ 7 rows in set (0.03 sec) Here the YEAR function returns the year of each persons birthday from a field that contains data of type DATE.

Year and Date Functions mysql> SELECT MONTH(birthday), DAYOFMONTH(birthday)-> FROM sales_rep; +-----------------+----------------------+ | month(birthday) | dayofmonth(birthday) | +-----------------+----------------------+ | 3 | 18 | | 11 | 30 | | 6 | 18 | | 1 | 4 | | 1 | 8 | | 3 | 7 | | 4 | 7 | +-----------------+----------------------+ 7 rows in set (0.00 sec) Here the MONTH and DAYOFMONTH are returned from the birthday field which is of type DATE.

Advanced QueriesGiving Columns a New Heading mysql> SELECT surname, first_name, month(birthday) -> AS month, dayofmonth(birthday) AS day FROM sales_rep -> ORDER BY month; +----------+------------+-------+------+ | surname | first_name | month | day | +----------+------------+-------+------+ | Rive | Mongane | 1 | 4 | | Smith | Mike | 1 | 8 | | Rive | Sol | 3 | 18 | | Parisien | Joe | 3 | 7 | | Mackay | Gord | 4 | 7 | | Serote | Mike | 6 | 18 | | Gordimer | Charlene | 11 | 30 | +----------+------------+-------+------+ 7 rows in set (0.02 sec) Here we use AS to give columns a new Heading.

Joining Columns with CONCAT mysql> SELECT CONCAT(first_name,' ',surname) -> AS name, MONTH(birthday) AS month, DAYOFMONTH(birthday) -> AS day FROM sales_rep ORDER BY month; +-------------------+-------+------+ | name | month | day | +-------------------+-------+------+ | Mongane Rive | 1 | 4 | | Mike Smith | 1 | 8 | | Sol Rive | 3 | 18 | | Joe Parisien | 3 | 7 | | Gord Mackay | 4 | 7 | | Mike Serote | 6 | 18 | | Charlene Gordimer | 11 | 30 | +-------------------+-------+------+ 7 rows in set (0.00 sec) Here we concatenate (join together) two columns into one (first_name and surname).

Finding the Day of the Year mysql> SELECT DAYOFYEAR(date_joined) FROM sales_rep -> WHERE employee_number=1; +------------------------+ | DAYOFYEAR(date_joined) | +------------------------+ | 46 | +------------------------+ 1 row in set (0.00 sec) This function will return a value from 1-366.

Date Calculations mysql> SELECT YEAR(NOW()) – YEAR(birthday) FROM sales_rep; +--------------------------------+ | YEAR(NOW()) – YEAR(birthday) | +--------------------------------+ | 26 | | 44 | | 31 | | 20 | +--------------------------------+ 4 rows in set (0.00sec) Returns the difference in years. Note: CURRENT_DATE() could be used instead of NOW().

Date Calculations • The previous example does not take into account days and months. • You need to subtract the years as was done previously, but then subtract a further year if a full year has not passed. Someone born on the 10th of December in 2001 is not one year old in January 2002, but only after the 10th of December 2002. • Take the MM-DD part components of the two date fields (the current date and the birth date) and compare them. If the current one is larger, a full year has passed. If the current MM-DD part is less than the birth date one, less than a full year has passed, and you must subtract one from the calculation.

Date Calculations mysql> SELECT YEAR(NOW()) > YEAR(birthday) FROM sales_rep where employee_number=1; +--------------------------------+ | YEAR(NOW()) > YEAR(birthday) | +--------------------------------+ | 1 | +--------------------------------+ 1 rows in set (0.00sec) Mysql evaluatesa true expression to 1 and a false expression to 0. The current year is greater than the birth date of employee 1. That is true and evaluates to 1. Note: CURRENT_DATE() could be used instead of NOW().

Date Calculations mysql> SELECT YEAR(NOW()) < YEAR(birthday) FROM sales_rep where employee_number=1; +--------------------------------+ | YEAR(NOW()) < YEAR(birthday) | +--------------------------------+ | 0 | +--------------------------------+ 1 rows in set (0.00sec) Mysql evaluatesa true expression to 1 and a false expression to 0. The current year is not less than the birth date year employee 1. That is false and evaluates to 0. Note: CURRENT_DATE() could be used instead of NOW().

Date Calculations • To return just the MM-DD component of the date: mysql> SELECT RIGHT(CURRENT_DATE,5), RIGHT(birthday,5) FROM sales_rep; +-----------------------+----------------------+ | RIGHT(CURRENT_DATE,5) | RIGHT(birthday,5) | +-----------------------+----------------------+ | 04-06 | 03-18 | | 04-06 | 11-30 | | 04-06 | 01-04 | | 04-06 | 06-18 | +-----------------------+----------------------+ 4 rows in set (0.00sec) The 5 inside the RIGHT( ) function refers to the number of characters from the right side of the string that the function returns. The full string 2004-04-06 returns 04-06.

Date Calculations Mysql> SELECT surname,first_name,(YEAR(CURRENT_DATE)- -> YEAR(birthday)) – (RIGHT(CURRENT_DATE,5) < RIGHT(birthday),5)) -> AS age FROM sales_rep; +----------+------------+-------+ | surname | first_name | age | +----------+------------+-------+ | Rive | Sol | 26 | +----------+------------+-------+ | Gordimer | Charlene | 43 | +----------+------------+-------+ | Rive | Mongane | 20 | +----------+------------+-------+ | Serote | Mike | 30 | +----------+------------+-------+ 4 row in set (0.00 sec)

Topics • Logical, arithmetic, comparison, and bit operators • Advanced joins (inner, outer, left, right, and natural joins) • Joining results with UNION • Rewriting sub-selects as joins • Removing records with DELETE and TRUNCATE • User variables • Running MySQL in batch mode • Performing transactions with BEGIN and COMMIT • Consistent reads • Table locks • Read locks for updating and for sharing

Operators • Logical operators (AND, OR, XOR, NOT) • Arithmetic operators (+, -, * /) • Comparison operators ( >, <, <=, >=, etc) • Bit operators

Logical Operators Operator Syntax Description AND, && C1 AND C2, C1 && C2 Only true if both conditions are true (C1 and C2) OR, || C1 OR C2, C1 || C2 True if either C1 or C2 is true !, NOT !C1, NOT C1 True if C1 is false, False if C1 is true

mysql> SELECT 1 AND 0; +---------+ | 1 AND 0 | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> SELECT NOT(1 AND 0); +--------------+ | NOT(1 AND 0) | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT !((1 OR 0) AND (0 OR 1)); +--------------------------+ | !((1 OR 0) AND (0 OR 1)) | +--------------------------+ | 0 | +--------------------------+ 1 row in set (0.00 sec)

Operator Syntax Description + a + b Addition - a – b Subtraction * a * b Multiplication / a / b Division Quotient returned % a % b a Modulus b Remainder returned after a/b Arithmetic Operators

mysql> SELECT 2+1; +-----+ | 2+1 | +-----+ | 3 | +-----+ 1 row in set (0.00 sec) mysql> SELECT 4-2/4; +-------+ | 4-2/4 | +-------+ | 3.50 | +-------+ 1 row in set (0.00 sec) mysql> SELECT 4-(2/4); +---------+ | 4-(2/4) | +---------+ | 3.50 | +---------+ 1 row in set (0.00 sec) mysql> SELECT (4-2)/4; +---------+ | (4-2)/4 | +---------+ | 0.50 | +---------+ 1 row in set (0.00 sec) mysql> SELECT 5 % 3; +-------+ | 5 % 3 | +-------+ | 2 | +-------+ 1 row in set (0.00 sec)

Operator Syntax Description = a = b True if Equal !=, <> a != b, a <> b True if Not Equal > a > b True if a greater than b < a < b True if a less than b >= a >= b True if a is greater or equal to b <= a <= b True if a less or equal to b <=> a<=> b True if a and b are equal including NULL IS NULL a is NULL True if a is NULL IS NOT NULL a IS NOT NULL True if a is not NULL Comparison Operators

Operator Syntax Description True if: BETWEEN a BETWEEN b and c a Between values b and c NOT BETWEEN a NOT BETWEEN b and c a Not Between values b and c LIKE a LIKE b a matches b NOT LIKE a NOT LIKE a does not match b IN a IN (b1,b2,b3..) a equals any in list NOT IN a NOT IN (b1,b2,b3..) a does not match any in list REGEXP, RLIKE a REGEXP b,a RLIKE b a matches b with a regular expression NOT REGEXP, NOT RLIKE a NOT REGEXP b,a NOT RLIKE b a does not matches b with a regular expression

mysql> SELECT 13=11; +-------+ | 13=11 | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) mysql> SELECT '4200' = 4200.0; +-----------------+ | '4200' = 4200.0 | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT '4200' = '4200.0'; +-------------------+ | '4200' = '4200.0' | +-------------------+ | 0 | +-------------------+ 1 row in set (0.00 sec)

mysql> SELECT 4.5 BETWEEN 4 and 5; +---------------------+ | 4.5 BETWEEN 4 and 5 | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT 5 BETWEEN 6 and 4; +-------------------+ | 5 BETWEEN 6 and 4 | +-------------------+ | 0 | +-------------------+ 1 row in set (0.00 sec)

mysql> SELECT 'abc' < 'b'; +-------------+ | 'abc' < 'b' | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> SELECT 'bbc' <= 'b'; +--------------+ | 'bbc' <= 'b' | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT 'a' IN('b','c','a'); +---------------------+ | 'a' IN('b','c','a') | +---------------------+ | 1 | +---------------------+ 1 row in set (0.02 sec)

mysql> SELECT 'abcd' LIKE '%b; +--------------------+ | 'abcd' LIKE '%b;' | +--------------------+ | 0 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT 'abcd' LIKE '%bc%'; % matches zero or more chars. +--------------------+ | 'abcd' LIKE '%bc%' | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT 'abcd' LIKE '%b%'; +-------------------+ | 'abcd' LIKE '%b%' | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec)

Character Description [a-z] Match any lower case letter [0-9] Matches any digit ^ Anchors match to beginning $ Anchors match to the end | Separates strings [n, m] String must occur at least n times, but no more the m {n} Strings must occur exactly n times {n,} String must occur at least n times Regular Expressions

Character Description . Matches any character (including carriage return and newline). ‘a*’ Matches any sequence of zero or more à' characters. ‘a+’ Matches any sequence of one or more à' characters. ‘a?’ Matches either zero or one à' character. ‘de|abc’ Matches either of the sequences ‘de’ or ‘abc’. ‘(abc)*’ Match zero or more instances of the sequence àbc'. ‘{1}’, ‘{2,3}’ To be more precise, à{n}' matches exactly `n' instances of à'. à{n,}' matches `n' or more instances of à'. à{m,n}' matches `m' through `n' instances of à', inclusive. ‘[a-dX]’, ‘[â-dX]’ Matches any character that is (or is not, if ^ is used) either ‘a’, ‘b’, ‘c’, ‘d’ or ‘X’.

Advanced Joins create table customer ( id int(11) default NULL, first_name varchar(30) default NULL, surname varchar(40) default NULL ) engine=MyISAM; insert customer values (1,'Yvonne', 'Clegg'); insert customer values (2,'Johnny', 'Waston'); insert customer values (3,'Winston', 'Powers'); insert customer values (4,'Patrica', 'Manning');

Advanced Joins create table sales ( code int(11) default NULL, sales_rep int(11) default NULL, customer int(11) default NULL, value int(11) default NULL ) ENGINE=MySQL; insert into sales_rep values (1,1,1,2000); insert into sales_rep values (2,4,3,250); insert into sales_rep values (3,2,3,500; insert into sales_rep values (4,1,4,450); insert into sales_rep values (5,3,1,3800); insert into sales_rep values (6,1,2,500);

Advanced Joins create table sales_rep ( employee_number int(11) default NULL, surname varchar(40) default NULL, first_name varchar(30) default NULL, commission tinyint(4) default NULL, date_joined date default NULL, birthday date default NULL ) ENGINE=MyISAM; insert into sales_rep values (1,'Rive','Sol',10,'2000-02-15','1976-03-18'); insert into sales_rep values (2,'Gordimer','Clarlene',15,'1998-07-09','1958-11-30'); insert into sales_rep values (3,'Serote','Mike',10,'2001-05-14','1971-16-18'); insert into sales_rep values (4,'Rive','Mongane','10,'2002-11-23'.'1982-01-04');

sales; +------+-----------+----------+-------+ | code | sales_rep | customer | value | +------+-----------+----------+-------+ | 1 | 1 | 1 | 2000 | | 2 | 4 | 3 | 250 | | 3 | 2 | 3 | 500 | | 4 | 1 | 4 | 450 | | 5 | 3 | 1 | 3800 | | 6 | 1 | 2 | 500 | +------+-----------+----------+-------+ sales_rep; +-----------------+----------+------------+------------+-------------+------------+ | employee_number | surname | first_name | commission | date_joined | birthday | +-----------------+----------+------------+------------+-------------+------------+ | 1 | Rive | Sol | 10 | 2000-02-15 | 1976-03-18 | | 2 | Gordimer | Charlene | 15 | 1998-07-09 | 1958-11-30 | | 3 | Serote | Mike | 10 | 2001-05-04 | 1971-06-18 | | 4 | Rive | Mongane | 10 | 2002-11-23 | 1982-01-04 | +-----------------+----------+------------+------------+-------------+------------+ customer; +------+------------+---------+ | id | first_name | surname | +------+------------+---------+ | 1 | Yvonne | Clegg | | 2 | Johnny | Jones | | 3 | Winston | Powers | | 4 | Patricia | Clark | +------+------------+---------+

LEFT Joins (Left Outer Joins) If a sale was made, where the customer paid cash, and the customer left before you could capture their details, would this cause a problem? No, there’s no problem because you can still add the data to the sales table using a NULL value for customer. mysql> INSERT INTO sales(code,sales_rep,customer,value) VALUES -> (7, 2, NULL, 670);

GAME203 QUERIES & TRANSACTIONS