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.
Specialcharacter/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;