What are the mysql query statements in java
- coldplay.xixiOriginal
- 2020-11-02 15:36:244555browse
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: > , b9931771cfc46c359af3973b0cab18ca= , 1da22f42f617d92256b26ab0e936e813(!=)
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:
-- 创建数据库
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 '1981-02-01' AND '1987-05-01';
-- 5. 选择在20或30号部门工作的员工姓名和部门号
SELECT ename 员工姓名,deptno 部门号 FROM emp WHERE deptno IN(20 , 30 );
-- 6. 选择在1981年雇用的员工的姓名和雇用时间
SELECT ename 员工姓名,hiredate 雇佣时间 FROM emp WHERE hiredate LIKE('1981-__-__');
-- 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 '__A%';
-- 10. 选择姓名中有字母a和e的员工姓名
SELECT ename 员工姓名 FROM emp WHERE ename LIKE '%A%' OR '%E%'
-- 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!