简单的sql语句

ALTER TABLE f ADD COLUMN address VARCHAR(100) DEFAULT '广州';
SELECT ename FROM emp;
SELECT ename,UPPER(ename),LOWER(ename) FROM emp;
SELECT LENGTH(ename),ename,LENGTH(job) FROM emp;
SELECT ename,CONCAT(ename,'hello',100) FROM emp;

SELECT dname FROM dept;
SELECT dname,SUBSTR(dname,2,3) FROM dept;
SELECT dname,REPLACE(dname,'o','666') FROM dept;


SELECT ename,comm FROM emp;
SELECT comm,IFNULL(comm,0) FROM emp;
SELECT comm,sal+IFNULL(comm,0) FROM emp;

SELECT comm FROM emp;
SELECT comm,ROUND(comm),CEIL(comm),FLOOR(comm) FROM emp;
SELECT NOW() ,CURDATE() , CURTIME() ;

SELECT loc FROM dept;
SELECT DISTINCT loc FROM dept;
SELECT * FROM dept WHERE deptno=1;
SELECT * FROM dept WHERE loc='一区';

SELECT * FROM dept WHERE dname='research';

SELECT * FROM dept WHERE loc='二区' OR dname='research';

SELECT * FROM emp WHERE sal=8000 OR sal=3000;

SELECT * FROM emp WHERE empno IN (100,200,300);

SELECT * FROM emp WHERE ename LIKE '%a%';
	
SELECT * FROM emp WHERE mgr IS NULL;

SELECT * FROM emp WHERE mgr IS NOT NULL;

SELECT * FROM emp WHERE sal >=3000 AND sal<=10000;

SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000;

SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2017 AND 2019;

SELECT * FROM emp LIMIT 2,2;

SELECT * FROM emp ORDER BY sal ASC;
SELECT * FROM emp ORDER BY sal DESC;


SELECT *FROM  emp ORDER BY hiredate ;
SELECT * FROM emp ORDER BY job;

SELECT * FROM emp WHERE hiredate>'2019-1-1' AND hiredate <'2019-12-12';

SELECT * FROM emp WHERE YEAR(hiredate)<2017;
SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2015 AND 2017;
SELECT *,YEAR(NOW())-YEAR(hiredate) FROM emp;


SELECT (sal+IFNULL(comm,0))*16 AS salary FROM emp;

SELECT sal FROM emp ORDER BY sal DESC LIMIT 1;
SELECT MAX(sal) FROM emp;
SELECT MIN(sal) FROM emp;

SELECT SUM(sal) FROM emp;
SELECT sal,AVG(sal) FROM emp;

SELECT COUNT(1) FROM emp;

SELECT  COUNT(1) FROM emp WHERE sal>8000;
SELECT COUNT(1) FROM emp WHERE YEAR(hiredate)=2019;


SELECT deptno,MAX(sal),AVG(sal) FROM emp GROUP BY deptno;
SELECT job,MAX(sal) FROM emp GROUP BY job;
SELECT COUNT(1),YEAR(hiredate) FROM emp GROUP BY YEAR(hiredate);

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>10000;

SELECT job,MAX(sal) FROM emp GROUP BY job HAVING MAX(sal)>8000 ORDER BY MAX(sal) DESC;

SELECT COUNT(1),YEAR(hiredate) FROM emp GROUP BY YEAR(hiredate) HAVING COUNT(1)>1;

SELECT COUNT(1),YEAR(hiredate) FROM emp WHERE YEAR(hiredate)>2017 GROUP BY YEAR(hiredate);