Java training notes (8)-mysql

Java training notes (8) MYSQL: functions are explained first today. functions are mainly divided into: 1. Aggregate functions 2. String functions 3. Date functions 4. mathematical functions Aggregate functions: A value is generated after a group of values are calculated. Common functions: function name function meaning Count () counts the maximum number of non-empty rows calculated by Max () and min,

Java training notes (8) MYSQL: functions are explained first today. functions are mainly divided into: 1. Aggregate functions 2. String functions 3. Date functions 4. mathematical functions Aggregate functions: A value is generated after a group of values are calculated. Common functions: function name function meaning Count () counts the maximum number of non-empty rows calculated by Max () and min,

Java training notes (8)-MYSQL

Today, we will first explain the functions, which are mainly divided:

1. Aggregate functions

2. String Functions

3. Date Functions

4. mathematical functions

Aggregate Function: A value is generated after a group of values are calculated.

Common functions include:

Function Name	Function meaning
Count ()	Count non-empty rows
Max (), min ()	Max and min
Avg ()	Average
Sum ()	Sum

In the function, you can also use distinct to represent non-duplicated data.

Problem:Calculate the number of students per course? (Assume that score indicates the score table. In this table, sid indicates the student ID, cid indicates the course number, and grade indicates the score)

Solution:

1. count is used to count the number of people;

2. Use group by for each course)

Answer:

Select count (*) from score where grade is not null group by cid;

String function: the number of the first character in the mysql string starts from 1.

Common functions include:

Function Name	Function meaning
Length (), bit_length ()	Evaluate the length of the string and the length of the binary string
Concat ()	String connection
Ascii ()	Average
Char	Sum
Ltrim ()	Remove spaces on the left
Rtrim ()	Remove spaces on the right
Left ()	Take characters from the left
Right ()	Take characters from the right
Instr ()	Locate
Substring ()	Substring
Rerverse ()	String Inversion
Bin ()	Binary
Oct ()	Octal
Hex ()	Hexadecimal
Locate ()	Position of the expected character
Repeat ()	Repeated characters
Format ()	Format String

Date functions: functions used to process dates

1. ADDDATE (Date, INTERVALExpr type) ADDDATE (Expr,Days) (ADDDATE () is the synonym of DATE_ADD)

Type Value	Type Value
MINUTE_SECOND	MICROSECOND
HOUR_MICROSECOND	SECOND
HOUR_SECOND	MINUTE
HOUR_MINUTE	HOUR
DAY_MICROSECOND	DAY
DAY_SECOND	WEEK
DAY_MINUTE	MONTH
DAY_HOUR	QUARTER
YEAR_MONTH	YEAR
MINUTE_MICROSECOND	SECOND_MICROSECOND

For example, postpone a month.

Select adddate (curdate (), interval 1 month); (curdate () Get the current date, now () Get the current date and time, curtime () Get the current time)

2. Datediff: The difference between the two dates

3. DAY () or dayofmonth ()

4. Dayname ():

5. Dayofweek

6. Dayofyear

7. Extract (): used to Extract a part of a date;

Problem:Count the turnover of the week. (sale table sale (sid, sname, sdate, count, number ));

Solution:The extract function is used to retrieve the week number from the date.

Code:Select sum (count * number) from sale

Where extract (week from curdate () = extract (week from sdate );

Mathematical functions

Random (): used to generate Random numbers.

Multi-Table query:

Multi-table queries are implemented through connections and subqueries.

Connection Methods: internal connections and external connections are divided into external connections and right outer connections.

:(In the student information table and student ID table, two tables have a public field called student ID)

Sample Code:

Structure of the three example tables: Student Information table stdinfo (sid pk, sname, sex, address), score (sid, cid, grade, pk (sid, cid )), indicates the primary key in course (cid pk, cname ).

Problem:Query students' test scores.

Solution:Based on the above information, you need to use several tables, and then check whether the obtained information is a common value of the two tables.

Code:Select sname, cid, grade from stdinfo s inner join score SC on s. sid = SC. sid;

Problem:Display the corresponding Course name.

Problem:Displays exam information for all students.

Solution:The internal connection and left outer connection are required here.

Subquery: A statement contains a select query, which is called a subquery. An external query is called a subquery. A subquery is calculated first and then an external query is calculated.

Syntax: select column name from table name where .. (Subquery)

External queries can be update or delete. They all have a common feature: they all contain where clauses, which are connected to subqueries through operators in the where clause. The available operators are comparison operators and in.

Question: Find the author of HeChuan publishing house in the same city.

Solution: First, find the city where Sichuan publishing house is located, and then find the author based on the city.

Table Structure: author (aid, author_name, sex, birth, city)

Publisher (pid, publisher_name, city)

Code: select author_name, sex birth, city from author where

City in (select city from publisher where publisher_name = 'chuan da press ');

Connection implementation method:

Select author_name, sex, birth, city from author inner join (select city from publisher where publisher_name = 'sichuan publishing put') c on author. city = c. city;