Looping progressive learning MySQL (1)

Create Database Menagerie;
show databases;
Use Menagerie;
Show tables;
CREATE TABLE Pet (name varchar, owner varchar), sepcies varchar, sex char (1), birth date,death date);
Describe pet;
INSERT into Pet
VALUES (' puffball ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Cat ', ' dd ', ' hamster ', ' d ', ' 1949-03-30 ', NULL);
INSERT into Pet
VALUES (' Jon ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' dog ', ' de ', ' cc ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Cat ', ' ge ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Bird ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Niao ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Wangba ', ' gh ', ' de ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Zhu ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Peg ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Houzi ', ' Diane ', ' hamster ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' file ', ' Diane ', ' hamster ', ' u ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' She ', ' Diane ', ' GG ', ' f ', ' 1999-03-30 ', NULL);
INSERT into Pet
VALUES (' Ji ', ' oo ', ' kk ', ' f ', ' 1999-03-30 ', NULL);
SELECT * FROM Pet ORDER BY name Desc;

/**select What_to_select
From which_table
WHERE conditions_to_satisfy;
What_to_select points out what you want to see, which can be a table for a column, or * denotes "all columns." Which_table indicates the table from which you want to retrieve data.
The WHERE clause is optional, and if selected, conditions_to_satisfy specifies the search criteria that the row must satisfy.

**/
UPDATE Pet SET birth = ' 1959-08-31 ' WHERE name = ' Wangba ';

SELECT * FROM pet where name= ' cat ';
SELECT * FROM pet where name= ' cat ' and owner= ' GE ';
SELECT owner from Pet; --This place is going to add an owner field
Select Name,owner,sex from pet where name= ' she ' or owner= ' GH ';
SELECT * FROM Pet
ORDER by birth DESC;


/***
MySQL provides several functions that can be used to calculate dates, for example, calculating age or extracting date parts.

To determine how big each pet is, you can calculate the difference between the year of the current date and the date of birth. If the calendar year of the current date is earlier than the birth date,
Minus one year. The following query shows the year numbers for each pet's date of birth, current date, and age value.

*****/
SELECT name, birth, Curdate (),
(Year (Curdate ())-year (birth)),
(Right (Curdate (), 5) <right (birth,5))
As age
from Pet;



/***
Here, year () extracts the date's part, right () extracts the date's MM-DD (calendar year) portion of the rightmost 5 characters. Expressions that compare MM-DD values
The value of the section is typically 1 or 0, and if the Year of Curdate () is earlier than birth, the year should be subtracted by 1. The entire expression is somewhat difficult to understand, using alias (age)
To make the output column markers more meaningful. Although the query is feasible, it is easier to explore the results if the rows are arranged in a certain order. Add ORDER by name child
It is possible to sort the output by name.

****/
SELECT name, birth, Curdate (),
(Year (Curdate ())-year (birth)),
(Right (Curdate (), 5) <right (birth,5))
As age
From pet ORDER by name;


/**
To sort the output by age rather than by name, just use an ORDER BY clause again:

**/
SELECT name, birth, Curdate (),
(Year (Curdate ())-year (birth))
-(Right (Curdate (), 5) <right (birth,5))
As age
From pet ORDER by age;

/***
A similar query can be used to determine the age of death of an animal that has died. You determine which animals are by checking if the death value is null, and then, for those animals that are not null values,
The difference between the death and birth values needs to be calculated:

**/
SELECT name, birth, death,
(Year (death)-year (birth)) – (Right (death,5) <right (birth,5))
As age
From pet WHERE death are not NULL for ORDER by age;

SELECT name, birth, MONTH (birth) from pet; --View Birthdays


SELECT name, birth from pet WHERE MONTH (birth) = 3; --View birthdays in March with bird names and birthday dates

This article is from "Ghost" blog, be sure to keep this source http://caizi.blog.51cto.com/5234706/1537342