catalogue
(1) Select all data
select * from pet;
Return:
(2) Modify table contents
Method 1
Delete first
DELETE FROM pet;
Modify the contents in txt and import again
load data local infile 'D:/desktop/pet.txt' into table pet lines terminated by '\r\n';
Method 2
(this is simpler)
For example, if you want to change Yangyang under owners to stupid, execute the following code
update pet set owner='Stupid' where name='11';
Return:
(3) Select a row individually
Select the following by name
Here, 11 is a numeric type. If it is a string type, you need to add quotation marks: '11'
SELECT * FROM pet WHERE name = 11;
Return:
You can specify conditions on any column, not just name. For example, if you want to know which animal was born after January 1, 2021, test the birth column:
SELECT * FROM pet WHERE birth > '2021-1-1';
Return:
You can combine conditions, such as finding Yangyang's dog:
select * from pet where owner='Yangyang' and species='dog';
Return:
AND and AND OR can be mixed, but AND has higher priority than OR. If you use two operators, it's a good idea to use parentheses to indicate how to group conditions:
select * from pet where owner='Yangyang' and species='dog' or ( owner='Yangyang' and species='Loong');
return:
(4) Select special columns
select name,birth from pet;
return:
Find out who owns pets and use this query
select owner from pet;
Return:
Note that the query simply retrieves the owner column of each record, and some of them appear multiple times. To minimize the output, add the keyword DISTINCT to retrieve each unique output record:
select distinct owner from pet;
Return:
You can use a WHERE clause to combine row selection with column selection.
For example, to query the date of birth of dogs and cats, use this query:
select name,species,birth from pet where species = 'pig' or species = 'Loong';
return:
(5) Classification line
To sort the results, use the ORDER BY clause. Here are animal birthdays, sorted by date:
select name, birth from pet order by birth;
Return:
The default sorting is ascending, with the smallest value first. To sort in descending order, add the DESC keyword to the column name you are sorting:
SELECT name, birth FROM pet ORDER BY birth DESC;
return:
Sort the animal species in ascending order, and then sort the animal species in descending order according to the birthday (the youngest animal is at the top). Use the following query:
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
return:
(6) Date calculation
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 date of birth, subtract one year. The following query displays the number of years for each pet's birth date, current date, and age values.
Although the query is feasible, it is easier to browse the results if the rows are arranged in a certain order. Add the ORDER BY name clause to sort the output by name to achieve:
SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age from pet;
If the code is too long, you can write it separately, as follows
Sort the output by age instead of name. Just use another ORDER BY clause:
SELECT name, birth, CURDATE(),(YEAR(CURDATE())-YEAR(birth))- (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age FROM pet ORDER BY age;
return:
Know which animal has a birthday in which month:
SELECT name, birth, MONTH(birth) FROM pet;
return:
It's also easy to find the animal whose birthday is next month. Assuming that the current month is February, then the monthly value is 2. You can find the animals born in March (March). The method is:
SELECT name, birth FROM pet WHERE MONTH(birth) = 3;
Return:
(7) NULL value operation
Using the IS NULL and IS NOT NULL operators, 0 or {NULL means false and other values mean true. The default truth value of Boolean operation is 1.
To determine which animal is no longer alive, use death IS NOT NULL instead of death= NULL
(8) Pattern matching
To find a name that begins with "1":
SELECT * FROM pet WHERE name LIKE '1%' ;
Return:
Similarly:
To find a name that ends with "1":
select * from pet where name like '%1';
Return:
Similarly:
To find a name that contains "1":
SELECT * FROM pet WHERE name LIKE '%1%';
Return:
To find a name that contains exactly 2 characters, use "" Mode character:
SELECT * FROM pet WHERE name LIKE '__';
return:
