Data Query Language (Oracle VS MySQL)

Data Query Language (Oracle VS MySQL)

Simple Query
Suppose there is such a table students:
STUDENT_ID MONITOR_ID NAME SEX DOB SPECIALTY
------------------------------------------------------------
10101 Wang xiaofang female-88 computer
10205 Li qiufeng male 25-11 month-90 Automation
10102 10101 Liu chunping female 12-8-91 computer
10301 mountain male-90 Mechanical and Electrical Engineering
10207 10205 Wang Gang male-87 Automation
10112 Zhang Chunyu male 10101-89 computer
10318 Zhang Dongyun female 26-12-89 Mechanical and Electrical Engineering
10103 10101 Wang Tianyi male 26-12-89 computers
10201 10205 Zhao wind and rain man-90 Automation
10105 10101 Han Liu male 03-8 months-91 Computers
10311 Zhang Yang male 10301-90 Mechanical and Electrical Engineering
10213 10205 master male 11-3 month-87 Automation
10212 10205 Ouyang chunlan female 12-3-89 Automation
10314 Zhao difan Male 22-9-89 Mechanical and Electrical Engineering
10312 Bai Feifei female 10301-88 Mechanical and Electrical Engineering
10328 Zeng Chengnan Mechanical and Electrical Engineering
10128 10101 Bai Xin Male Computer
10228 Lin zihan female Automation

Query all information in the table
Select * from [tableName];
Eg: select * from students;
Display the information of the specified column:
Select [columnName1 <as otherName1>, columnName2 <as otherName2>,...] from [tableName];
ColumnName, columnName <as otherName> indicates that the columnName is changed to the alias otherName. If the alias otherName has different sizes or contains special characters or spaces, the alias must be taken; if you do not change the name, you can omit <as otherName>;
Eg: select student_id, monitor_id as banner ID, name as name from students;

Null Value Processing
NULL indicates an unknown or unknown value in the database. For any type of column, NULL may occur as long as the non-NULL (not null) or primary key integrity restriction is NOT used ).
A null value has no data type. It indicates an unknown or unknown value in the database. It can be expressed by a unified NULL value, whether it is a number, text, or date type.
1. If a column in the table does NOT use non-NULL (not null) or primary key integrity restrictions, its default value is NULL. If the value of this column is not specified when data is inserted using the insert statement, the value is NULL. If the value of the specified column is NULL, it can be expressed as NULL; use the UPDATE statement to change the data of a column in the table to a NULL value, which can be expressed as NULL.
2. When a NULL value is involved in an operation, if the result in an arithmetic expression is a NULL value, no value is displayed in Oracle, and NULL is displayed in MySQL. If it is in a string connection expression, Oracle treats it as an empty string;
MySQL does not seem to have a string connection operation, but it is NULL in the search results. Oracle provides three functions to solve the preceding problems due to NULL calculation. The usage of the three functions is as follows:
(1). NVL (expr1, expr2 );
Expr1 and expr2 are function parameter expressions, which can be any internal Oracle data type, but expr1 and expr2 data types must match;
Function function: If expr1 is NULL, the value of expr2 is returned. Otherwise, if not NULL, the value of expr1 is returned.
For example, tables with teachers are as follows:
TEACHER_ID name title HIRE_DATE bonus wage DEPARTMENT_ID
-------------------------------------------------------------------
10210 Professor Yang culture 03-10 months-89 1000 3100 102
10206 Cui Tian ta 500-00 1900 102
10209 sun qingbi lecturer 11-5 months-98 600 2500 102
10207 instructor Zhang Ke 16-8 months-97 700 2700 102
10308 Qi Shenyang Senior Engineer 03-10 months-89 1000 3100
10306 October 500-01 1900 103
10309 Ma Haitao engineer 29-6 month-99 600 2400 103
10307 Zhao Kun lecturer 18-2 month-96 800 2700 103
10128 WANG Xiao 1000-07 101
10328 Zhang Xiaoying 29-9 month-07 1000 103
10228 Zhao Tianyu 18-9 month-07 1000 102

SQL> SELECT name AS "name", NVL (bonus, 0) + wage AS "total monthly income" FROM teachers;
Total Monthly name income
------------------
Yang culture 4100
Cui Tian 2400
Sun qingbi 3100
Zhang Ke 3400
Qi Shenyang 4100
July 2400
Tao Haitao 3000
Zhao Kun 3500
WANG Xiao 1000
Zhang Xiao 1000
Zhao Tianyu 1000
(2). NVL2 (expr1, expr2, expr3 );
Expr1 and expr2 are function parameter expressions, which take the internal data type of Oracle, but the data types of expr2 and expr3 must match the data type of expr1;
Function function: if the value of expr1 is null, the value of expr3 is returned. If the value of expr1 is not null, the value of expr2 is returned.

SQL> SELECT name AS "name", NVL2 (bonus, bonus + wage, wage) AS "total monthly income" FROM teachers;
Total Monthly name income
------------------
Yang culture 4100
Cui Tian 2400
Sun qingbi 3100
Zhang Ke 3400
Qi Shenyang 4100
July 2400
Tao Haitao 3000
Zhao Kun 3500
WANG Xiao 1000
Zhang Xiao 1000
Zhao Tianyu 1000
(3). COALESCE (expr, [, expr]...);
Here, expr1, expr2,... is the function parameter expression, take the internal data type of Oracle; function: return the first non-null value in the parameter list. If all expressions are null, a null value is returned.

Conditional Query
Select * from [tableName] where [condition];
Select [columnName1 <as otherName1>, columnName2 <as otherName2>,...] from [tableName] where [condition]; The where clause is used to specify query conditions. condition is a specific condition expression. When the value of condition is true, the corresponding data is retrieved. Comparison Condition
Function Description
Example
Arithmetic comparison Condition
=
Equal
Name = 'zhang san'
>
Greater
Bonus & gt; 500
> =
Greater than or equal
Bonus> = 500
<
Less
Bonus <1, 1000
<=
Less than or equal
Hire_date <'06-February-2001'
<>,
Not equal
Bonus> = 1000
Include test
IN
In the specified collection
Student_id in (10101, 10102)
NOT IN
Not in the specified collection
Student_id not in (10101, 10102)
Range Test
BETWEEN... AND
Within the specified range
Age between 10 and 40
Not between... AND
Not in the specified range
Age between 0 and 15
Matching Test
LIKE
Match with the specified mode
Name like 'Luo %'
NOT LIKE
Does not match the specified mode
Name not lik 'Li %'
NULL Test
IS NULL
Is a NULL value.
Name is null
IS NOT NULL
Not a NULL value
Name is not null
Logical operators
AND
Logic and Operation
Bouns> 500 and bounus <= 1000
OR
Logic or operation
Bouns> 500 or name like 'wang %'
NOT
Logical non-operation
Not bonus = 500 single condition query in condition, the value uses a comparison character to form a condition query condition. For example:

SQL> select * from students where student_id <= 10128;

STUDENT_ID MONITOR_ID NAME SEX DOB SPECIALTY
------------------------------------------------------------
10101 Wang xiaofang female-88 computer
10102 10101 Liu chunping female 12-8-91 computer
10103 10101 Wang Tianyi male 26-12-89 computers
10105 10101 Han Liu male 03-8 months-91 Computers
10112 Zhang Chunyu male 10101-89 computer
10128 10101 Bai Xin Male Computer

Compound condition Query
Compound condition query uses logical operators and, or, and not to connect multiple conditions as query conditions. For example:
SQL> select * from students where student_id <= 10128 and sex = 'femal ';

STUDENT_ID MONITOR_ID NAME SEX DOB SPECIALTY
------------------------------------------------------------
10101 Wang xiaofang female-88 computer
10102 10101 Liu chunping female 12-8-91 computer

Record sorting
If the order of query results is not specified when the select statement is executed, data rows are displayed in the order of data insertion in the table. To display the searched data in a certain order, you can use the select statement of the order by clause to achieve this purpose. The format is as follows:
Select * from [tableName] from [tableName] <where [condition]> order by [columnName] <ASC | DESC>;
Select [columnName1 <as otherName1>, columnName2 <as otherName2>,...] from [tableName] <where [condition]> order by [columnName] <ASC | DESC>;
Condition indicates the column or expression used for sorting. ASC indicates sorting in ascending order (default), and DESC indicates sorting in descending order. <ASC | DESC> values can be set by default, by default, ASC is sorted in ascending order.

Sort by Single Column
Sorting by a single column means that the columnName of the order by clause only refers to one column or expression. For example, the orders table

DEPARTMENT_ID when me ADDRESS
---------------------------------
101 teaching building no. 1 of Information Engineering
102 teaching building no. 2 of Electrical Engineering
103 teaching building no. 3 of Mechanical and Electrical Engineering
104 teaching building No. 4 of Business Administration
Select * from orders ments order by department_name asc;
DEPARTMENT_ID when me ADDRESS
--------------------------------
102 teaching building no. 2 of Electrical Engineering
104 teaching building No. 4 of Business Administration
103 teaching building no. 3 of Mechanical and Electrical Engineering
101 teaching building no. 1 of Information Engineering
Sort by multiple columns
Sorting by multiple columns means that the columnName of the order by clause only refers to one or more columns or expressions. The data in the query results is first sorted by the first column specified by columnName, and then
Sort the third column, and so on.

SQL> select student_id, monitor_id, sex, name from
Students order by sex, monitor_id;

STUDENT_ID MONITOR_ID SEX NAME
------------------------------------
10128 10101 male Bai Xin
10105 10101 male Han Liu
10103 10101 male Wang Tianyi
10112 10101 male Zhang Chunyu
10207 10205 male Wang Gang
10201 Zhao wind and rain, 10205 male
10213 10205 male High School
10314 10301 male Zhao difan
10311 10301 male Zhang Yang
10328 10301 male Zeng Cheng
10205 male Li qiufeng
10301 male Mountain
10102 10101 female Liu chunping
10228 10205 female Lin zihan
10212 10205 female Ouyang chunlan
10318 10301 female Zhang Dongyun
10312 million female Bai Feifei
10101 female Wang xiaofang

Group Query
Column functions and Their Applications
Grouping query is mainly used to query statistics in the entire table. This type of operation is done by using the aggregate function, group by clause, and having clause.
The format of the split query:
Select [columnName1 <as otherName1>, columnName2 <as otherName2>,...] from [tableName] <where [condition]> group by [columnName1, columnName2,...]
<Having condition>; column function overview column (Aggregate) Function
Function Description
Column functions for character, value, and date data
MAX (column)
Maximum value in a column
MIN (column)
Minimum value in a column
COUNT (*)
Total number of rows in the table
COUNT (column)
Number of rows whose column is not NULL
COUNT (distinct column)
Column specifies the number of distinct values in the Column
Column functions for numeric data only
SUM (column)
Total of all values in a column
AVG (column)
Average of all values in a column
STDDEV (column)
Column Standard Deviation
VARIANCE (column)
Column variance eg: calculate the number of students in the students table
SQL> select count (*) from students;
COUNT (*)
----------
18

Mysql> select count (*) from students;
+ ---------- +
| Count (*) |
+ ---------- +
| 7 |
+ ---------- +
1 row in set (0.00 sec)

Group by clause
The group by clause can be used to group data in a table. If the table rows are divided into groups, the rows in these groups are not repeated. Then, the column functions form each level of poetry, so that each group has a statistical value.
In the group by clause, columnName is used to specify the column or expression of the group. One or more expressions can be specified as the group basis. When grouping based on a single column, a statistical result is generated based on each different value of the column;

When grouping multiple columns, statistical results are generated based on different values of multiple columns. MySQL does not seem to support statistics based on multiple columns.
Eg. Sort the students table by the monitor ID.
SQL> select monitor_id from students group by monitor_id;
MONITOR_ID
----------

10101
10301
10205

Mysql> select monitor_id from students group by monitor_id;
+ ------------ +
| Monitor_id |
+ ------------ +
| NULL |
| 1, 10101 |
| 1, 10205 |
| 1, 10301 |
+ ------------ +
4 rows in set (0.20 sec)

Eg: group students tables by major and shift leader ID
SQL> select specialty, monitor_id from students group by specialty, monitor_id;
SPECIALTY MONITOR_ID
--------------------
Computer
Mechanical and Electrical Engineering
Automation
Automation 10205
Electromechanical engineering 10301
Computer 10101

Eg: calculate the number of boys and girls in the students table.
SQL> select sex, count (sex) from students group by sex;

Sex count (SEX)
----------------
Male 12
Female 6

Having clause
The group by clause is used to specify the basis for the group, while the having clause specifies the conditions to limit the results displayed by the group. The condition in the having clause is used to specify the conditions for limiting grouping.
Having clauses must be used with group by clauses, while group by clauses are generally used independently.
Eg: Search for the department whose average salary is higher than 2000, showing the Department number and average salary.

SQL> select department_id, avg (wage) from teachers group by department_id having
Avg (wage)> 2000;
DEPARTMENT_ID AVG (WAGE)
-----------------------
102 2240
103 2220