MySQL aggregate functions, operator operations, constraints

4. Aggregation function

1, category
avg (field name): Find the field average
sum (field name): Sum
Max (field name): Max value 　　
min (field name): Min
Count (field name): Counts the number of records in this field
2, example
1, The strongest value of the attack is how much
select Max (Gongji) from Moshou.sanguo; 　　　
2, statistical ID, name two fields have a few records
Select count (ID), count (name) from Sanguo;
# # null value NULL is not counted, "" will be counted

3, calculate the total attack power of Shu hero
Select SUM (Gongji) from Moshou.sanguo
where country= "Shu";
4. Count the number of heroes who attack more than 200 in Shu heroes
　　Select COUNT (*) from Moshou.sanguo
where gongji>200 and country= "Shu";

4. Operator operation
1. Numeric comparison/character comparison
1. Numerical comparison: = = = > >= < <=
2. Character comparison: = =!
3. Practice
1. Find the hero's name and attack value above 150 damage
Select Name,gongji from Sanguo where gongji>150;
2, set the attack power of the Zhao Yun to 360, the Defense Force is set to 68
Update Sanguo Set gongji=360,fangyu=68
where Name= "Zhao Yun";

5, the query table records when doing mathematical operations
1. Operators
+-*/%
2. Example
1, Search all Heroes attack double
Select Id,name,gongji*2 as GJ from Sanguo;
2. Logical comparison
1, and (two or more conditions set up at the same time)
2, or (any one condition can be established)
3. Practice
1, find out the attack value above 200 Shu Hero's name, attack
select name as N,gongji as G from Sanguo
where gongji>200 and country= "Shu";
2, the hero of the Wu hero attack value of 110 attack value to 100, the defense Force changed to
Update Sanguo Set gongji=100,fangyu=60
where country= "WU" and gongji=110;
3. Find the hero information of Shu and Wei
SELECT * from Sanguo
where country= "Shu" or country= "Wei";
3. In-range comparison
1, between value 1 and value 2
2, where field name in (value 1, value 2,...)
3, where field name not in (value 1, value 2,...)
4. Practice
1, find the attack value 100-200 Shu Hero information
SELECT * from Sanguo
where Gongji between and
country= "Shu";
2. Find the information of the heroine of the country other than Shu and Wu
SELECT * from Sanguo
where country not in ("Shu", "WU")
and sex= "female";
3. Find the information of Shu hero Shing ID 1, 3 or 5
SELECT * from Sanguo
where
(ID in (1,3,5) and country= "Shu") or name= "Marten cicada";
4, matching empty, non-empty
1, empty: where name is null
2, non-null: where name is NOT NULL
3. Example
1. Shu heroine information with a null value
SELECT * from Sanguo
where
name is null and country= "Shu" and sex= "female";
2, the name of "" The Hero Information
SELECT * from Sanguo where name= "";
4. Attention
1, null: null value, can only be matched with IS or not
2, "": empty string, with = or! = to match
5. Fuzzy comparison
1. Where field name like Expression
2. Expression
1, _: Match a single character
2,%: match 0 to more characters
3. Example
select name from Sanguo where name is like "_%_";
select name from Sanguo where name is like "%";
# # NULL will not be counted, can only be matched with is
select name from Sanguo where name is like "___";
select name from Sanguo where name is like "Zhao%";

2. Constraints
1, the role: to ensure the integrity of data, consistency, effectiveness
2. Constraint classification
1. Default constraint
1, insert the record, do not assign a value to the field, then use the default value
2, non-empty constraint (NOT null)
1. The value of the field is not allowed to have a null record
Sex enum ("M", "F", "s") not null Defalut "s"

MySQL aggregate functions, operator operations, constraints