sql语句

SELECT NAME,sex FROM emp;
SELECT * FROM emp;
SELECT NAME,salary * 12 + bonus FROM emp;
SELECT NAME,sex 性别 FROM emp;     -- sex 性别   中间有个as  as可以省略
SELECT * FROM emp WHERE sex ='女';
SELECT * FROM emp WHERE salary BETWEEN 8000 AND 12000;
SELECT * FROM emp WHERE NAME IN ("张三","李四","张飞");
SELECT * FROM emp WHERE NAME LIKE '%云%';
SELECT * FROM emp WHERE bonus IS NOT NULL;
SELECT * FROM emp WHERE sex='女' AND hiredate> DATE'2010-1-1';
SELECT * FROM emp ORDER BY salary DESC;
SELECT * FROM emp ORDER BY salary DESC,bonus DESC;
SELECT NAME FROM emp ORDER BY CONVERT(NAME USING GBK);-- name是utf-8格式   USING GBK 编码支持拼音排序。
SELECT NAME FROM emp ORDER BY salary DESC LIMIT 0,3;
SELECT NAME, CASE WHEN salary < 10000 THEN '低收入' WHEN salary < 20000 THEN '中等收入' ELSE '高收入' END AS "薪水等级" FROM emp;
SELECT COUNT(*),AVG(salary),MAX(salary),MIN(salary),SUM(salary) FROM emp;
不相同 因为COUNT（*）只要期中哟偶一个字段有数据就记录COUNT（bouns）奖金  乳沟没有奖金的就不会记录他只会记录这个字段有数据的为NULL不会记录
-- 统计每个部门的员工的数量、平均月薪？
SELECT d.name,COUNT(*),AVG(salary) FROM emp e,dept d WHERE e.did=d.id GROUP BY d.name;
SELECT d.name,AVG(salary) FROM emp e,dept d WHERE e.did=d.id GROUP BY d.name HAVING AVG(salary)>10000;
SELECT d.name,COUNT(*) FROM emp e,dept d WHERE e.did=d.id GROUP BY d.name HAVING COUNT(e.did)>5
连接  内连接 外连接 
外连接分为左外连接 右外连接
SELECT e.name,d.name FROM emp e,dept d WHERE e.did=d.id
SELECT d.name,COUNT(e.name) FROM emp e,dept d WHERE e.did=d.id GROUP BY d.name
SELECT e.name 员工名字,m.name 领导名字 FROM emp e,job m WHERE e.mid=m.id;
SSELECT d.name FROM emp e,dept d WHERE e.did=d.id AND d.name IN(SELECT * FROM emp  WHERE sex ='女');