MySQL_小さな練習

1）        
SELECT
	dname 
FROM
	dept

2）          （  +  ） ，      “   ”
SELECT
	ename,
	( sal + ifnull( comm, 0 ) ) * 12 "   " 
FROM
	emp

3）          
SELECT DISTINCT
	deptno 
FROM
	emp;
	
1）    2850        
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal > 2850

2）    1500 2850             
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal NOT BETWEEN 1500 
	AND 2850

3）   7566             
SELECT
	ename,
	deptno 
FROM
	emp 
WHERE
	empno = 7566
 
4）  10 30     1500        
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	deptno IN ( 10, 30 )

5）             
SELECT
	ename,
	job 
FROM
	emp 
WHERE
	mgr IS NULL;

6)               
SELECT
	ename,
	job 
FROM
	emp 
WHERE
	comm IS NULL;

1） 1981 2 1 ~~1981 5 1         、       ，             
SELECT
	ename,
	job,
	hiredate 
FROM
	emp 
WHERE
	hiredate BETWEEN "1981-2-1" 
	AND "1981-5-1" 
ORDER BY
	hiredate ASC;
	
2）          ，       ，            
SELECT
	ename,
	sal,
	comm 
FROM
	emp 
WHERE
	comm IS NOT NULL 
ORDER BY
	sal DESC,
	comm DESC;

1）         、    、    、      
SELECT
	avg( sal ),
	sum( sal ),
	max( sal ),
	min( sal ) 
FROM
	emp;
	
2）         、      
SELECT
	count( empno ),
	avg( sal ) 
FROM
	emp 
GROUP BY
	job;

3）               
SELECT
	count( empno ),
	count( comm ) 
FROM
	emp
 
4）         
SELECT
	count( DISTINCT mgr ) 
FROM
	emp;
	
1）  20               、       
SELECT
	dname,
	ename,
	sal,
	job 
FROM
	emp,
	dept 
WHERE
	emp.deptno = 20 
	AND emp.deptno = dept.deptno;

2）          、          
SELECT
	ename,
	comm,
	dname 
FROM
	emp,
	dept 
WHERE
	comm IS NOT NULL 
	AND emp.deptno = dept.deptno;

3） “DALLAS”        、          
SELECT
	ename,
	sal,
	dname 
FROM
	emp,
	dept 
WHERE
	loc = "DALLAS"
4）  SCOTT     
SELECT
	e2.ename 
FROM
	emp e1,
	emp e2 
WHERE
	e1.ename = "SCOTT" 
	AND e1.mgr = e2.empno;

5）  EMP  SALGRADE ，    20    、        
SELECT
	ename,
	sal,
	grade 
FROM
	emp,
	salgrade 
WHERE
	deptno = 20 
	AND sal BETWEEN losal 
	AND hisal;

6）  10      、          
SELECT
	ename,
	dname 
FROM
	emp
	RIGHT JOIN dept ON emp.deptno = 10 
	AND emp.deptno = dept.deptno;
7）  10      、          
SELECT
	ename,
	dname 
FROM
	emp
	LEFT JOIN dept ON emp.deptno = dept.deptno 
	AND emp.deptno = 10;
--        ：
-- 1）BLAKE        ，    BLAKE
SELECT
	ename 
FROM
	emp 
WHERE
	deptno = ( SELECT deptno FROM emp WHERE ename = "BLAKE" ) 
	AND ename <> "BLAKE";
-- 2）            、      
SELECT
	ename,
	sal,
	deptno 
FROM
	emp 
WHERE
	sal > ( SELECT avg( sal ) FROM emp ) 
-- 3）              、      

SELECT
	ename,
	sal,
	e.deptno 
FROM
	emp e,
	( SELECT deptno, avg( sal ) avg FROM emp GROUP BY deptno ) t2 
WHERE
	e.deptno = t2.deptno 
	AND sal > avg;

-- 4）  CLERK              、      
SELECT
	ename,
	sal,
	comm 
FROM
	emp 
WHERE
	sal > ALL ( SELECT sal FROM emp WHERE job = "CLERK" )

-- 5）  、    SCOTT          、      
SELECT
	ename,
	e.sal,
	e.comm 
FROM
	emp e,
	( SELECT sal, comm FROM emp WHERE ename = "SCOTT" ) t2 
WHERE
	e.sal = t2.sal 
	AND e.comm <=> t2.comm 
	AND ename <> "SCOTT";
-- 6）         .                    
SELECT
	dname,
	deptsum 
FROM
	dept,
	( SELECT deptno, sum( sal ) deptsum FROM emp GROUP BY deptno ) t1 
WHERE
	dept.deptno = t1.deptno 
	AND deptsum > ( SELECT sum( sal ) / 3 sum FROM emp )
	
--   emp  45-50   
SELECT
	* 
FROM
	emp 
	LIMIT 44,
	6;

SELECT
	* 
FROM
	( SELECT * FROM emp, rownum r FROM emp WHERE rownum <= 50 ) 
WHERE
	r >= 45;
--             
SELECT
	dname,
	ifnull( num, 0 ) 
FROM
	dept
	LEFT JOIN ( SELECT deptno, count( * ) num FROM emp GROUP BY deptno ) t1 ON dept.deptno = t1.deptno

--                       

SELECT NAME
	,
	modelname 
FROM
	scmuser su,
	systemmodel sm,
	usermodel um 
WHERE
	createdate > ( SELECT createdate FROM scmuser WHERE NAME = "lisi" ) 
	AND su.account = um.account 
	AND sm.modelcode = um.modelcode;
	
--             
SELECT
	couid,
	max( score ),
	min( score ) 
FROM
	score 
GROUP BY
	couid;
--         3   id、       id、       
SELECT
	stuid,
	stuname,
	stu.classid 
FROM
	student stu,
	( SELECT classid, count( * ) num FROM student GROUP BY classid ) t1 
WHERE
	t1.classid = stu.classid 
	AND t1.num >3
--         60      、       
SELECT
	stu.stuid,
	stuname,
	avg 
FROM
	student stu,
	( SELECT stuid, avg( score ) avg FROM score GROUP BY stuid ) t1 
WHERE
	stu.stuid = t1.stuid 
	AND avg > 60;
	
--                          

SELECT
	stuname,
	s1.score math,
	s2.score chinese 
FROM
	student stu,
	score s1,
	score s2 
WHERE
	stu.stuid = s1.stuid 
	AND s1.stuid = s2.stuid 
	AND s1.couid = ( SELECT couid FROM course WHERE cname = "  " ) 
	AND s2.couid = ( SELECT couid FROM course WHERE cname = "  " ) 
	AND s1.score > s2.score