mysql query statements in java: 1. Simple query; 2. Simple query; 3. Sorting query; 4. Group query, the code is [group by grouped field.[Having condition]]; 5. For paging query, the code is [select * from table name limit x;].

mysql query statement in java:

1. Simple query

–Query all fields:

select * from table name;

- - Query specified fields:

select field 1, field 2... from table name;

- - Table alias: If there are special symbols or spaces in the alias, it needs to be enclosed in quotation marks

select * from table name [as] Table alias

- - Column Alias: as can be omitted.

select field 1 [as] alias, field 2 [as] alias from table name;

- - Remove duplicate values: If there are multiple fields, they must be Repeat.

select distinct field from table name;

- - Operation query:

select (math english) total score from table name;

2. Conditional query:

Comparison operators: > , < , = , >= , <= , <>(!=)

Logical operators:

between…and… : Values ??displayed in a certain interval (including head and tail)

in (multiple conditions) : or (or) relationship

like: Fuzzy query

% represents zero or more arbitrary characters.

_ represents one character.

is null: Determine whether it is empty.

3. Sorting query: Write at the end of the sql statement.

select * from table name order by sorting field ASC (ascending order - default)/DESC (descending order)

If there are multiple fields to be sorted, sort them by the first one first, and then sort by the following ones

4. Aggregation function: after select, before from.

sum (sum): The specified column is not a numeric type, and the calculation result is 0;

count (statistical number): does not include null; generally use *;

max (maximum value ): If it is a string type, use string sorting;

min(minimum value) :

avg(average): The specified column is not a numeric type, and the calculation result is 0;

5. Group query

group by 被分組的字段.[Having 條件]

• where: Filter before group query.

• having: Group query Post-filtering.

Note; Grouped fields are generally written after select as query conditions for easy viewing

6. Paging query (understand)

Use keyword limit

Format one: only the first x data

select * from 表名 limit x;

Format two: paging query

 select * from 表名 limit m,n;

• m: The starting number of rows of data per page, changing

• n: The number displayed on each page, fixed

Note:

The index of the row in the database starts from 0

The index of the column starts from 1

Single table case:

-- 創(chuàng)建數據庫
create database day03; 
-- 員工表
USE day03; 
CREATE TABLE emp(
     -- 員工編號
     empno   INT,
     -- 員工姓名
     ename   VARCHAR(50),
     -- 工作
     job  VARCHAR(50),
     -- 管理者
     mgr   INT,
     -- 雇用時間 
     hiredate   DATE,
     -- 工資
     sal  DECIMAL(7,2),
     -- 獎金
     comm  DECIMAL(7,2),
     -- 部門
     deptno  INT
) ;
-- 部門表
CREATE TABLE dept(
   -- 部門
   deptno  INT,
   -- 部門名稱
   dname  VARCHAR(14),
   -- 部門位置
   loc   VARCHAR(13)
   );
-- 向員工表中添加數據.
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
-- 向部門表添加數據 , 采用批量插入數據, 用 , 號隔開 .
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK')
,(20, 'RESEARCH', 'DALLAS'),(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
-- 1.  查詢工資大于1200的員工姓名和工資
SELECT ename 員工姓名 , sal 工資 FROM emp  WHERE sal > 1200;
-- 2.   查詢員工號為7698的員工的姓名和部門號
SELECT ename 員工姓名 , deptno 部門號 FROM emp WHERE empno = 7698;
-- 3.   選擇工資不在500到1200的員工的姓名和工資
SELECT ename 員工姓名 ,sal 工資 FROM emp WHERE sal<=1200 && sal >= 500;
-- 4.   選擇雇用時間在1981-02-01到1987-05-01之間的員工姓名，job_id和雇用時間
SELECT ename 員工姓名,empno , hiredate FROM emp WHERE hiredate BETWEEN &#39;1981-02-01&#39; AND &#39;1987-05-01&#39;;
-- 5.   選擇在20或30號部門工作的員工姓名和部門號
SELECT ename 員工姓名,deptno 部門號 FROM emp WHERE deptno IN(20 , 30 );
-- 6.   選擇在1981年雇用的員工的姓名和雇用時間
SELECT ename 員工姓名,hiredate 雇傭時間 FROM emp WHERE hiredate LIKE(&#39;1981-__-__&#39;);
-- 7.   選擇公司中沒有管理者的員工姓名及job_id
SELECT ename 員工姓名,empno FROM emp WHERE mgr IS NULL;
-- 8.   選擇公司中有獎金的員工姓名，工資和獎金級別
SELECT ename 員工姓名,sal 工資,comm 獎金 FROM emp WHERE comm IS NOT NULL OR;
-- 9.   選擇員工姓名的第三個字母是a的員工姓名
SELECT ename 員工姓名 FROM emp WHERE ename LIKE &#39;__A%&#39;;
-- 10.  選擇姓名中有字母a和e的員工姓名
SELECT ename 員工姓名 FROM emp WHERE ename LIKE &#39;%A%&#39; OR &#39;%E%&#39;
-- 11.  查詢員工號，姓名，工資，以及工資提高百分之20%后的結果（new salary）
SELECT empno 員工號, ename 姓名,sal+(sal*0.2) 工資 FROM emp;
-- 12.  將員工的姓名按首字母排序
SELECT ename FROM emp ORDER BY ename ASC; -- 升序
SELECT ename FROM emp ORDER BY ename DESC; -- 降序
-- 13.  查詢公司員工工資的最大值，最小值，平均值，總和
SELECT MAX(sal) 最大值,MIN(sal) 最小值 , AVG(sal) 平均值, SUM(sal) 總和 FROM emp;   
-- 14.  查詢各deptno的員工工資的最大值，最小值，平均值，總和
SELECT deptno 部門,MAX(sal) 最大值,MIN(sal) 最小值 , AVG(sal) 平均值, SUM(sal) 總和 FROM emp GROUP BY deptno;   
-- 15.  選擇具有各個deptno的員工人數
SELECT deptno , COUNT(empno) FROM emp GROUP BY deptno;

Related free learning recommendations: java basic tutorial, mysql video tutorial

The above is the detailed content of What are the mysql query statements in java. For more information, please follow other related articles on the PHP Chinese website!