The next day, export file sql, query, views view, aggregate function, anti-pattern, string handler function

Export the database to a script file
mysqldump-uroot-p1234--databases ABC > D:/a/abc.sql

CREATE TABLE Stud (
ID INT PRIMARY KEY,
NAME VARCHAR (+) is not NULL,
Score NUMERIC (4,1)
);
Set all the names to "Mike."
UPDATE stud SET name= "Mike"

Set the name of the record for the score >=70 only
UPDATE stud SET name= "EXC" WHERE score>=70

Modify multiple fields at the same time (the following two ways are available.) Field values can be quoted even if they are not character types---Note that the value of the character type must be quoted. Therefore, for security reasons, it is best to enclose all the values in quotation marks-both single and quotation marks.
UPDATE stud SET name= "idiot", id=5 WHERE score<60
UPDATE stud SET name= "idiot", id= ' 5 ' WHERE score<60

The sentence pattern of the ※where clause
Select field list from table name where condition and condition or condition
Update Table TableName Set: Where condition
Delete from TableName where ....

Query scores between [60,80] Students
SELECT * FROM stud WHERE score>=60 and score<=80;
SELECT Name,score from stud WHERE score between and 80; The between clause is a boundary-containing

Query classification is exactly 65 and 85 of the students
SELECT * from stud WHERE score=65 OR score=85;
SELECT * from stud WHERE score in (65,85); The values in the IN clause are discrete

Fuzzy query: Like parameter:% (any match) _ (match single-character)
SELECT * FROM stud WHERE NAME is like ' Wang% '; A student surnamed Wang
SELECT * FROM stud WHERE NAME is like ' king _ '; A student surnamed Wang who is called a word
SELECT * FROM stud WHERE NAME is like ' King __ '; A student surnamed Wang with two words
SELECT * FROM stud WHERE name like '% King ';//students with "King" in their names

Query a field value is empty
SELECT * FROM stud WHERE score is NULL; Note that the "=" number cannot be used

View views---Operations on the map are updated to physical tables at all times, except that the operation scope is only for the view (more efficient)
CREATE VIEW STUDV as SELECT * from stud WHERE score<60;
SELECT NAME from STUDV;
UPDATE STUDV SET score = score*1.1; After the sentence is executed: 1) If the score passes, the data in the STUDV view is no longer included in the 2) the data in the physical table stud has changed.

Aggregation functions
SELECT COUNT (*) as num from stud; Number of rows of data in the statistics table
SELECT COUNT (score) as Num from stud; Count the number of non-null data rows in the score column
SELECT AVG (Score) as Avgg the average of the statistical score from stud;//(does not contain records with a score value of NULL)
SELECT AVG (Score) as Avgg from stud WHERE score are not NULL;

SELECT ROUND (AVG (Score)) as Avgg from stud;//rounding, rounding
SELECT ROUND (AVG (Score), 2) as Avgg from stud;//rounded, reserved two decimal places

SELECT sum (score) as Avgg from stud;//summation
SELECT MAX (Score) as Avgg from stud; Maximum (highest score)
Find out about the highest scores of students
SELECT * FROM stud WHERE score = (SELECT MAX (score) as Avgg from stud);
SELECT * FROM stud WHERE score in (SELECT MAX (score) as Avgg from stud);

What age groups are queried (show what age values, i.e. only one for the same age value)---later show which professions and which departments are used distinct
SELECT DISTINCT age from stud;
SELECT DISTINCT age from stud ORDER by age ASC; Ascending
SELECT DISTINCT age from stud ORDER by age DESC; Descending

Anti-pattern-The following statement either displays all records or does not appear either. Because exists () returns the same result
SELECT * FROM stud where EXISTS (SELECT * from stud where age=25);

GROUP BY ...---to show the average of students of all ages
SELECT avg (Score) average, age from stud GROUP by;
SELECT avg (Score) average, age-from-stud GROUP by-age has age>20;
SELECT avg (Score) average, age-stud GROUP by-age has AVG (score) >=70;

String handling functions
Length (str)-to find the length of the string
Ltrim (str)-Remove the left space
Rtrim (str)-Remove the space on the right
Left (str,n); -Remove n characters from the left
Right (Str,n); -Remove n characters from the right
Substring (Str,begin,len)-return substring, begin sequence number starting from 1 ※
Reverse (str) – returns the inverted string
Lower (str)-Turn lowercase
Upper (str)-turn into uppercase
Concat (Str,str ...) Concatenates strings.
Instr (Str,s) – returns the position of S in str, no then returns 0

SELECT * FROM stud WHERE LENGTH (NAME) >3;
SELECT * FROM stud WHERE LENGTH (LTRIM (NAME)) >3;

SELECT SUBSTRING (name,1,2) from stud;
SELECT * FROM stud WHERE SUBSTRING (name,1,1) = ' King ';//show students surnamed Wang

※ objects between the database and the Java class
list<stud>---Form
Stud class object (Value object, domain object)---table record (one row in the table)
Stud attributes (member variables) in a Class object-----table field

The next day, export file sql, query, views view, aggregate function, anti-pattern, string handler function