MySQL Chapter 3-Querying Data

This is for test on 12.02 and I alse want to review the MySQL.By the way,I want to try to write my first English blog.

 

Retrieving Data

1.Retrieving Specific Attributes

select *from cusomer;

select customer_id,store_id,first_name,last_name from customer;

select title as 'film name' from film;/*将查询到的电影标题显示在名为‘电影名的用户定义列中’*/

2.Retrieving Selected Rows

select title,length,rental_rate from film where rental_rate=4.99;
/*show the title... of those films that have rental rate of $4.99*/

You can use Logical operators in the select to retrieve records based on the result of one or more conditions.

OR

select *from customer where last_name='smith' or last name='jones';

AND

select *from customer where first_name='elizabeth' and last_name='brown';

NOT

select *from customer where not first_name='mary';

There are some Comparison Operators you can use to compare the column values with a given value

Operators	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than equal to
<>,!=	Not equal to

select film_id,title,rental_rate from film where rental_rate<1;

In addition to the operators listed in the preceding table,comparsion operators provide the following operators to refine query results:

BETWEEN

select title,rental_rate from film where rental_rate between 0.99 and 2.99;

LIKE

LIKE is used for retrieving those records from a table which match a pattern  specified in a select query.For example,you may need to display a list of those films whose name's the second character in the title is F.

select title from film where title like '_F%'

Wildcard Character	Description
%	number
_	a single character

IN

This operator is used to specify multiple values of a specific field while retrieving records from a table select statement.

select film_id title,description,rental_rate from film 
where title in('AFRICAN EGG','AGENT TRUMAN','ALONE TRIP');

Arithmetic Opreators are used to perform the mathematical calculations.

Operators	Description	Example
DIV	division return quotient	7 DIV 2 3
/	division return exact quotient value	7 / 2 3.5000
-	substraction	7-2 5
*	multipication	7*2 14
% or MOD	division return remainder	7 % 2 1
+	addition	7+2 9

select 2*(rental_rate) from film where title='ACADEMY DINSAUR';

Using the LIMIT Clause to limit the query results that fall within a specified range.The limit clause can take one or two arguments.

LIMIT [offset,] row_count

notes:offset's default value is zero,

          row_count specifies the maximum number of rows to be returned in the result set.

select film_id,title from film limit 5;
select film_id,title from film limit 5,10;/*from 6 to 15*/

 

Using Functions to Customize Result Set

We will learn six functions:String, Date, Mathematical, Information, Cast , Aggregate

 

1.String Functions

Function name	Parameters	Example	Description
ASCII	(character_expression)	select ascii('ABC')	return 65. the leftmost character
LEFT	(character_expression, integer_expression)	select left ('DAVID',4)	return 'DAVI' the number of character from left
REVERSE	(character_expression)	select reverse ('RAM')	return 'MAR' the reversr of
RIGHT	(character_expression, integer_expression)	select right ('LAWSON',4)	return 'WSON'
SUBSTRING	(expression,start,length)	select  substring('Whether',2,2)	return 'he' from the start position the number of characters specified the length
UPPER	(character_expression)	select upper('lawson')	return  'LAWSON'
LOWER	(character_expression)	select lower('RICHARD')	return 'richard'
CONCAT	(character_expression1, character_expression2, ......)	select concat('Mary','Smith')	return 'Mary Smith'
REGEXP	(No Paramerters)	select 'John Peter'  REGEXP 'Peter'	return 1 find this part

for example,you can use concat to display full name:

select concat(first_name,' ',last_name) as 'customer name' from customer;

some of the special characters and constructs used with the REGEXP operator.

Special character/construct	Description
.	a single character
p*	zero or any number of occurrences of the p character
p+	one or any number of occurrences of the p character
^p	the string(s) that begin with a p character
p$	the string(s) that end with a p character
pqr|abc	the string(s) having either the sequence of pqr or abc

2.Date Functions

select adddate('2008-8-24 15:25:52',interval '8:20' HOUR_MINUTE);
/*return 2008-8-24 23:45:52*/

select date('2008-08-16 22:55:56');
/*2008-08-16*/

select month('2007-08-16 16:40:45');
/*8*/

select monthname('2008-07-24 20:40:45');
/*July*/

select year('2005-06-16 20:45:30');
/*2005*/

select datediff('2005-08-24 20:50:59','2004-05-20 16:40:50');
/*461 days*/

select timediff('2005-08-30 23:59:59','2005-08-29 23:59:59');
/*24:00:00*/

select day('2008-08-24 22:40:40');
/*24*/

select extract(year from '2009-05-20 22:50:40');
/*2009*/

select date_format('2005-06-18 13:33:59','%M');
/*June*/

format value	Description
%a	names of weekday
%b	names of months(3 words)
%c	the number of months
%H	the number of hour from 00 to 23
%i	the number of minute from 00 to 59
%j	the number of the day of the year from 001 to366
%M	the name of month
%p	the time in AM or PM
%S	the number of seconds from 00 to 59

3.Mathematical Funcations

select floor(12.30);
/*12      less than or equal to*/

select cot(3.5);
/*2.669616484968866          the cotangent of the specified angle in radians*/

select pi();
/*3.141593*/

select pow(5,2);
/*25*/

select round(12.789,2);
/*12.79*/

select sqrt(81);
/*9*/

 

 

4.Information Funcations

select current_user();
/*the user name and the host name of user*/

select connection_id();
/*the current_id*/

select database();
/*the name of database*/

select version();
/*the current version of the MySQL server*/

select charset('ANDRE');
/**/

5.Cast Functions

to be continued......

6.Aggregate Functions

select avg(rental_rate) from film;

select sum(rental_rate) from film;

select min(rental_rate) from film;

select max(rental_rate) from film;

select count(title) from film;

Sorting and Grouping Data

1.Sorting Data

select rental_id,amount,payment_date from payment 
order by amount desc;/*> > > */

select *from payment order by amount asc;
/*< < <*/

2.Grouping Data

select customer_id as 'customer_id',sum(amount) as 'total amount' from payment
group by customer_id having customer_id<5;