SQL 语句(一)

show DATABASES;USE esupermarket;show TABLES;SELECT * from tbl_product LIMIT 2,5;select id,productNo,title,price,sellPoint,status,image1,image2,image3,image4,image5 from tbl_product LIMIT 5,5;select count(id) from tbl_product;SELECTta.id,ta.`name`,ta.age,tb.sourceFROMtableA ta, tableB tbwhere ta.id = tb.id;SELECT ta.id,ta.`name`,ta.age,tb.sourceFROM tableA taLEFT JOIN tableB tbON ta.id = tb.id;SELECT ta.id,ta.`name`,ta.age,tb.sourceFROM tableA taRIGHT JOIN tableB tbON ta.id = tb.id;/* 查询当前使用数据库版本, */SELECT database();/* 显示当前库有哪些表 */SHOW TABLES;show DATABASES;USE test2;SHOW TABLES FROM esupermarket;DESC tableA;SHOW CREATE TABLE tableA;SELECT database();USE bjpowernode;show TABLES;DESC DEPT;show TABLES;SELECT EMPNO, ENAME FROM EMP;SELECT * FROM EMP;/* 计算员工年薪 */SELECT EMPNO, ENAME, JOB, SAL*12 AS '年薪'FROM EMP;/* 过滤掉大于1000的员工 *//* 列出员工的编号，姓名和年薪 */SELECT EMPNO, ENAME, JOB, SALFROM EMPWHERE SAL < 1000;/* 条件查询 */SELECT EMPNO, ENAME, JOB,SALFROM EMPWHERE SAL = 5000;/* 查询职位为MANAGER的员工 */SELECT EMPNO,ENAME,JOBFROM EMPWHERE JOB = 'MANAGER';SELECT *FROM EMPWHERE SAL != 5000;SELECT *FROM EMPWHERE SAL != '5000';SELECT *FROM EMPWHERE SAL < '3000';/* 以下显示1981-02-20以后的记录*/SELECT *FROM EMPWHERE HIREDATE > '1981-02-20';/* 以下显示1981-02-20以前的记录*/SELECT *FROM EMPWHERE HIREDATE < '1981-02-20';/* 查询薪水1600到3000之间的员工 */SELECT * FROM EMPWHERE SAL>1600 AND SAL < 3000;SELECT *FROM EMPWHERE SAL BETWEEN 1600 AND 3000;SELECT EMPNO, ENAMEFROM EMPWHERE ENAME BETWEEN 'A' AND 'D';SHOW TABLES;DESC EMP;/* 查询员工津贴为null的记录 *//* 因为null类型比较特殊，必须使用 is 来比较 */SELECT *FROM EMPWHERE COMM IS NULL;/* 查询员工津贴不为null的记录 */SELECT * FROM EMPWHERE COMM IS NOT NULL;/* 查询工作岗位为“MANAGER”并且薪水大于2500的员工 */SELECT * FROM EMPWHERE JOB BETWEEN 'MANAGER' AND SAL < 2500;/* 查询出 job 为 SALESMAN 和 job为MANAGER的员工 */SELECT * FROM EMPWHERE JOB = 'MANAGER' OR JOB = 'CLERK';/* 查询薪水大于1800，并且部门编号为20或30的员工 */SELECT     EMPNO,ENAME,DEPTNO,SALFROM     EMPWHERE     SAL > '1800'AND    (DEPTNO = '20' OR DEPTNO = '30');SELECT    EMPNO,ENAME,DEPTNO,SALFROM     EMPWHERE    SAL > '1800'HAVING    DEPTNOIN    ('20','30');/* 查询出Job为 SALESMAN 和 Job为 MANAGER 的员工 *//* in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些 */SELECT    *FROM    EMPWHERE     JOB IN     ('SALESMAN','MANAGER');/* 查询出薪水为1600和3000的员工 */SELECT    EMPNO, ENAME, SALFROM    EMPWHERE    SALIN    ('1600','3000');SELECT    EMPNO, ENAME, SALFROM    EMPWHERE    SAL = '1600' OR SAL = '3000';/* 查询出薪水在1600和3000的之间员工 */SELECT    EMPNO, ENAME, SALFROM    EMPWHERE    SAL BETWEEN '1600' AND '3000';/* 内连接 */SELECT    E.EMPNO,E.ENAME,D.DNAME,E.COMMFROM    EMP E ,DEPT DWHERE    E.DEPTNO = D.DEPTNOHAVING    E.COMM IS NULL;/* 模糊查询like */SELECT    *FROM    EMPWHERE    ENAME LIKE '%S%';/* 查询姓名中第二个字符为A的所有员工 */SELECT     EMPNO, ENAMEFROM    EMPWHERE    ENAME LIKE '_A%';/* 查询ENAME所有倒数第二个字母为E的 */SELECT    EMPNO, ENAMEFROM     EMPWHERE    ENAME LIKE '%E_';/* 查询姓名中第三个字符为N的所有员工姓名 */SELECT    EMPNO,ENAMEFROM     EMPWHERE    ENAME LIKE '__N%';UPDATE    EMPSET    ENAME ='KI_NG'WHERE    EMPNO = '7839';UPDATE    EMPSET    ENAME = 'JO%NN'WHERE    EMPNO = '7566';/* 转义查询包含通配符 ESCAPE */SELECT    EMPNO,ENAME,SALFROM     EMPWHERE    ENAME LIKE '%/_%'ESCAPE "/";SELECT    EMPNO,ENAME,SALFROM     EMPWHERE     ENAME LIKE '%/%%'ESCAPE '/';/* 按照薪水由小到大排序（系统默认由小到大） */SELECT    EMPNO,ENAME,SALFROM    EMPWHERE    JOB = 'MANAGER'ORDER BY     SAL ASC;/* 多个字段排序 *//* 按照 job 和薪水倒序排序,首先按照job降序排列.然后在按照sal降序排列 */SELECT     E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAMEFROM    EMP E, DEPT DWHERE    D.DEPTNO = '20'ORDER BY    E.JOB DESC,E.SAL ASC;/* 将大写转换成小写 */SELECT    EMPNO,lower(ENAME) AS ENAME,SALFROM    EMP;/* 查询员工姓名中第二个字母为A的所有员工 */SELECT    EMPNO,ENAMEFROM     EMPWHERE    substr(ENAME,'2','1') = 'A';SELECT    EMPNO,ENAME,SAL,MGRFROM     EMPWHERE    SUBSTR(MGR,'2','2') = '69';SELECT    EMPNO,ENAME,SAL,MGRFROM    EMPWHERE    MGR LIKE '_69%';/* 取得员工姓名长度 */SELECT    EMPNO,ENAME,length(ENAME) AS ENAMELENGTHFROM     EMP;/* 取得工作岗位为manager的所有员工 *//* 使用trim()函数,去除"  manager  "前后的空格,可以取得查询结果 */SELECT    EMPNO,ENAME,SAL,JOBFROM    EMPWHERE    JOB = trim('MANAGER');SELECT rand();SELECT    EMPNO,ENAME,SAL,JOBFROM     EMPORDER BY SAL DESC;SELECT    EMPNO,ENAME,SAL,JOBFROM    EMPORDER BY SAL ASC;SELECT * FROM EMP;SELECT    DEPTNO,sum(SAL) as sal_totalFROM     EMPGROUP BY    DEPTNOORDER BY    sum(SAL) ASC;SELECT    DEPTNO,ENAME,SALFROM     EMPGROUP BY    DEPTNO,ENAME,SALORDER BY    DEPTNO,SAL ASC;/* 去重关键字 */SELECT DISTINCT JOB FROM EMP;SELECT    *FROM    EMP WHERE     FALSE;SELECT    *FROM    EMPWHERE     TRUE;show DATABASES;USE bjpowernode;SHOW TABLES;/* IFNULL  空值处理函数   */SELECT    ENAME,SAL,COMM,(SAL + IFNULL(COMM,0))*12 AS '年收入'FROM    EMP;/* 没有补助的员工 将补助100 计算年薪 */SELECT    ENAME,SAL,COMM,IFNULL(COMM,100) AS '补助',(SAL + IFNULL(COMM,100))*12 AS '年薪'FROM     EMP;SELECT    *FROM     EMPWHERE    HIREDATE = '1981-02-20';/* where过滤 年月日 Y要大写 d m 小写 */SELECT    ENAME,HIREDATEFROM     EMPWHERE    hiredate = str_to_date('17-12-1980','%d-%m-%Y');/* 创建表格语句 */CREATE TABLE T_STUDENT(ID  INT(4),NAME VARCHAR(32),BIRTH  DATE);SELECT * FROM T_STUDENT;INSERT into     T_STUDENT VALUES    ('1','LISI','1925-12-22');INSERT INTO    T_STUDENTVALUES    ('2','ZHANGSAN',str_to_date('12-31-2001','%m-%d-%Y'));INSERT INTO    T_STUDENTVALUES    ('4','WANGWU',str_to_date('31-12-2010','%d-%m-%Y'));/* data_format函数主要用在数据库查询操作中。实际工作中，客户需要日期以特定格式展示的时候，需要使用该函数 *//* 匹配工作岗位，当为MANAGER时，薪水上调10%，当为SALESMAN时，薪水上调50%，其它岗位薪水不变 */SELECT    EMPNO, ENAME, JOB, SAL, (CASE JOB WHEN 'MANAGER' THEN SAL*1.1 WHEN 'SALESMAN' THEN SAL*1.5 ELSE SAL END) AS 'NEWSAL'FROM EMP;SELECT * FROM EMP;/* 匹配部门列表 当部门编号为10 去20部门 20部门去30部门  其他不变 */SELECT    EMPNO,ENAME,JOB,SAL,DEPTNO,(CASE DEPTNO WHEN '10' THEN DEPTNO+10 WHEN '20' THEN DEPTNO+10 ELSE DEPTNO END) NEWDEPTNOFROM EMP;SELECT    COUNT(COMM)FROM    EMP;SELECT    SUM(SAL+COMM)FROM    EMP;SELECT    sum(SAL + ifnull(COMM,0)) AS 'SAL+COMM'FROM    EMP;/* 取得最晚入职的员工 */SELECT    *FROM    EMPWHERE    HIREDATE = (    SELECT    max(HIREDATE)FROM    EMP    );SELECT     DISTINCT         E.DEPTNO, D.DNAME, E.JOBFROM    EMP E, DEPT DWHERE    E.DEPTNO = D.DEPTNOORDER BY    E.DEPTNO;/* 查询该公司有几个工作岗位 */SELECT     count(DISTINCT JOB) AS JOBNUMFROM        EMP;/* 按照工作岗位分组，然后对每一组求最大值。SQL语句中肯定包含 group by、max(sal)； */SELECT     JOB,    MAX(SAL)FROM    EMPGROUP BY    JOB;/* 找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的； */