sql语句练习，oracle与mysql不同

迈不过友情╰ 2021-09-13 16:18 485阅读 0赞

## sql语句，oracle与mysql不同![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70][] ##

![在这里插入图片描述][20200604164144728.png]  
**两表联查练习**  
– 查询财务部女员工的信息

SELECT
    	*
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    WHERE
    	d.dept_name = '财务部'
    AND e.emp_sex = 0

– 查询财务部工资大于2000的男员工的信息

SELECT
    	*
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    WHERE
    	d.dept_name = '财务部'
    AND e.emp_sex = 1
    AND e.emp_wage > 2000

**order by的用法**  
– 查询所有员工的姓名、工资、部门名称，并以工资倒序排列

SELECT
    	e.emp_name,
    	e.emp_wage,
    	d.dept_name
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    ORDER BY
    	e.emp_wage DESC

– 查出工资大于5000的员工id、员工姓名、部门名称、工资

SELECT
    	e.emp_id,
    	e.emp_name,
    	e.emp_wage,
    	d.dept_name
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    WHERE
    	e.emp_wage > 5000

– 查询年龄大于20的李姓男员工的员工姓名、部门名称

SELECT
    	e.emp_name,
    	d.dept_name
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    WHERE
    	e.emp_age > 20
    AND e.emp_sex = 1
    AND e.emp_name LIKE '李%'

**聚合函数max的用法**  
– 查询工资大于男员工最大工资的女员工的员工id、员工姓名、部门名称

SELECT
    	e.emp_id,
    	e.emp_name,
    	d.dept_name
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    WHERE
    	e.emp_wage > (
    		SELECT
    			max(emp_wage)
    		FROM
    			t_emp
    		WHERE
    			emp_sex = 1
    	)
    AND e.emp_sex = 0

**group by的用法**  
– 查询至少有三个员工的部门名称

select 
    d.dept_name
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    GROUP BY e.dept_id having count(e.emp_id)>=3

聚合函数与group by共同使用语法：group by后边不能跟where语句，只能用having  
– 查询最大工资大于5000的部门名称

SELECT
    	d.dept_name
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    GROUP BY e.dept_id HAVING MAX(e.emp_wage) > 5000

– 查询工资最高的张姓女员工的员工id和部门名称

SELECT
    	e.emp_id,
    	d.dept_name
    FROM
    	t_emp e
    LEFT JOIN t_dept d ON e.dept_id = d.dept_id
    WHERE
    	e.emp_wage = (
    		SELECT
    			max(emp_wage)
    		FROM
    			t_emp
    		WHERE
    			emp_name LIKE '张%' and emp_sex = 0
    	)
    AND 	emp_name LIKE '张%' and emp_sex = 0

\*\*

## 分页：oracle与mysql的区别 ##

\*\*

**– 分页查询员工表的信息，假设每页展示5条，查询第3页的数据  
– oracle**

SELECT
    	*
    FROM
    	(
    		SELECT
    			rownum r,
    			e.*
    		FROM
    			(SELECT * FROM t_emp) e
    		WHERE
    			rownum <= 3 * 5
    	)
    WHERE
    	r > (3 - 1) * 5

**– mysql**  
– mysql识别不了后边进行运算，需要计算好了在拿出来

select * from t_emp LIMIT 4 , 2;
    //错误的
    select * from t_emp LIMIT (3-1)*2 , 2;
    select * from  t_emp limit  (3-1)*5 , 5 ;

– 将财务部工资大于2000的员工的工资在原有基础上减100

UPDATE t_emp
    SET emp_wage = emp_wage + 100
    WHERE
    	emp_wage > 2000
    AND dept_id = (
    	SELECT
    		dept_id
    	FROM
    		t_dept
    	WHERE
    		dept_name = '财务部'
    )

– 将财务部年龄最大的员工的工资改为20000

– oracle

UPDATE t_emp
    SET emp_wage = 20000
    WHERE
    	emp_age = (
    		SELECT
    			max(emp_age)
    		FROM
    			t_emp
    		WHERE
    			dept_id = (
    				SELECT
    					dept_id
    				FROM
    					t_dept
    				WHERE
    					dept_name = '财务部'
    			)
    	)

– mysql，对于一些语句执行不了，会报错，就比如这个，它识别不了oracle这种，必须也要把后边的条件当作一张表才可以继续执行

UPDATE t_emp
    SET emp_wage = 20000
    WHERE
    	emp_age = (
    		SELECT
    			*
    		FROM
    			(
    				SELECT
    					max(emp_age)
    				FROM
    					t_emp
    				WHERE
    					dept_id = (
    						SELECT
    							dept_id
    						FROM
    							t_dept
    						WHERE
    							dept_name = '财务部'
    					)
    			) AS emp
    	)

– 删除员工表的重复信息，认为姓名和部门一样是重复的

– oracle

DELETE
    FROM
    	t_emp
    WHERE
    	emp_id NOT IN (
    		SELECT
    			emp_id
    		FROM
    			t_emp
    		GROUP BY
    			emp_name,
    			dept_id
    	);

– mysql

DELETE
    FROM
    	t_emp
    WHERE
    	emp_id NOT IN (
    		SELECT
    			*
    		FROM
    			(
    				SELECT
    					emp_id
    				FROM
    					t_emp
    				GROUP BY
    					emp_name,
    					dept_id
    			) AS emp
    	);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70]: /images/20210913/203bff5e60d240ec9e120e5cf17c1053.png
[20200604164144728.png]: /images/20210913/fe82df4a9ad044808276965f33a5985a.png