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;

SELECT
ta.id,ta.`name`,ta.age,tb.source
FROM
tableA ta, tableB tb
where ta.id = tb.id;


SELECT 
ta.id,ta.`name`,ta.age,tb.source
FROM tableA ta
LEFT JOIN tableB tb
ON ta.id = tb.id;

SELECT 
ta.id,ta.`name`,ta.age,tb.source
FROM tableA ta
RIGHT JOIN tableB tb
ON 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, SAL
FROM EMP
WHERE SAL < 1000;


/* 条件查询 */
SELECT EMPNO, ENAME, JOB,SAL
FROM EMP
WHERE SAL = 5000;

/* 查询职位为MANAGER的员工 */
SELECT EMPNO,ENAME,JOB
FROM EMP
WHERE JOB = 'MANAGER';

SELECT *
FROM EMP
WHERE SAL != 5000;

SELECT *
FROM EMP
WHERE SAL != '5000';

SELECT *
FROM EMP
WHERE SAL < '3000';

/* 以下显示1981-02-20以后的记录*/
SELECT *
FROM EMP
WHERE HIREDATE > '1981-02-20';

/* 以下显示1981-02-20以前的记录*/
SELECT *
FROM EMP
WHERE HIREDATE < '1981-02-20';

/* 查询薪水1600到3000之间的员工 */
SELECT * 
FROM EMP
WHERE SAL>1600 AND SAL < 3000;

SELECT *
FROM EMP
WHERE SAL BETWEEN 1600 AND 3000;

SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME BETWEEN 'A' AND 'D';

SHOW TABLES;

DESC EMP;

/* 查询员工津贴为null的记录 */
/* 因为null类型比较特殊，必须使用 is 来比较 */
SELECT *
FROM EMP
WHERE COMM IS NULL;


/* 查询员工津贴不为null的记录 */
SELECT * 
FROM EMP
WHERE COMM IS NOT NULL;

/* 查询工作岗位为“MANAGER”并且薪水大于2500的员工 */
SELECT * 
FROM EMP
WHERE JOB BETWEEN 'MANAGER' AND SAL < 2500;

/* 查询出 job 为 SALESMAN 和 job为MANAGER的员工 */
SELECT * 
FROM EMP
WHERE JOB = 'MANAGER' OR JOB = 'CLERK';

/* 查询薪水大于1800，并且部门编号为20或30的员工 */
SELECT 
    EMPNO,ENAME,DEPTNO,SAL
FROM 
    EMP
WHERE 
    SAL > '1800'
AND
    (DEPTNO = '20' OR DEPTNO = '30');


SELECT
    EMPNO,ENAME,DEPTNO,SAL
FROM 
    EMP
WHERE
    SAL > '1800'
HAVING
    DEPTNO
IN
    ('20','30');


/* 查询出Job为 SALESMAN 和 Job为 MANAGER 的员工 */
/* in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些 */
SELECT
    *
FROM
    EMP
WHERE 
    JOB 
IN 
    ('SALESMAN','MANAGER');

/* 查询出薪水为1600和3000的员工 */
SELECT
    EMPNO, ENAME, SAL
FROM
    EMP
WHERE
    SAL
IN
    ('1600','3000');

SELECT
    EMPNO, ENAME, SAL
FROM
    EMP
WHERE
    SAL = '1600' OR SAL = '3000';


/* 查询出薪水在1600和3000的之间员工 */
SELECT
    EMPNO, ENAME, SAL
FROM
    EMP
WHERE
    SAL BETWEEN '1600' AND '3000';

/* 内连接 */
SELECT
    E.EMPNO,E.ENAME,D.DNAME,E.COMM
FROM
    EMP E ,DEPT D
WHERE
    E.DEPTNO = D.DEPTNO
HAVING
    E.COMM IS NULL;


/* 模糊查询like */
SELECT
    *
FROM
    EMP
WHERE
    ENAME LIKE '%S%';

/* 查询姓名中第二个字符为A的所有员工 */
SELECT 
    EMPNO, ENAME
FROM
    EMP
WHERE
    ENAME LIKE '_A%';

/* 查询ENAME所有倒数第二个字母为E的 */
SELECT
    EMPNO, ENAME
FROM 
    EMP
WHERE
    ENAME LIKE '%E_';

/* 查询姓名中第三个字符为N的所有员工姓名 */
SELECT
    EMPNO,ENAME
FROM 
    EMP
WHERE
    ENAME LIKE '__N%';

UPDATE
    EMP
SET
    ENAME ='KI_NG'
WHERE
    EMPNO = '7839';

UPDATE
    EMP
SET
    ENAME = 'JO%NN'
WHERE
    EMPNO = '7566';

/* 转义查询包含通配符 ESCAPE */
SELECT
    EMPNO,ENAME,SAL
FROM 
    EMP
WHERE
    ENAME LIKE '%/_%'
ESCAPE "/";

SELECT
    EMPNO,ENAME,SAL
FROM 
    EMP
WHERE 
    ENAME LIKE '%/%%'
ESCAPE '/';

/* 按照薪水由小到大排序（系统默认由小到大） */
SELECT
    EMPNO,ENAME,SAL
FROM
    EMP
WHERE
    JOB = 'MANAGER'
ORDER BY 
    SAL ASC;


/* 多个字段排序 */
/* 按照 job 和薪水倒序排序,首先按照job降序排列.然后在按照sal降序排列 */
SELECT 
    E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAME
FROM
    EMP E, DEPT D
WHERE
    D.DEPTNO = '20'
ORDER BY
    E.JOB DESC,E.SAL ASC;

/* 将大写转换成小写 */
SELECT
    EMPNO,lower(ENAME) AS ENAME,SAL
FROM
    EMP;


/* 查询员工姓名中第二个字母为A的所有员工 */
SELECT
    EMPNO,ENAME
FROM 
    EMP
WHERE
    substr(ENAME,'2','1') = 'A';


SELECT
    EMPNO,ENAME,SAL,MGR
FROM 
    EMP
WHERE
    SUBSTR(MGR,'2','2') = '69';

SELECT
    EMPNO,ENAME,SAL,MGR
FROM
    EMP
WHERE
    MGR LIKE '_69%';

/* 取得员工姓名长度 */
SELECT
    EMPNO,ENAME,length(ENAME) AS ENAMELENGTH
FROM 
    EMP;

/* 取得工作岗位为manager的所有员工 */
/* 使用trim()函数,去除"  manager  "前后的空格,可以取得查询结果 */
SELECT
    EMPNO,ENAME,SAL,JOB
FROM
    EMP
WHERE
    JOB = trim('MANAGER');

SELECT rand();

SELECT
    EMPNO,ENAME,SAL,JOB
FROM 
    EMP
ORDER BY SAL DESC;


SELECT
    EMPNO,ENAME,SAL,JOB
FROM
    EMP
ORDER BY SAL ASC;


SELECT * FROM EMP;

SELECT
    DEPTNO,sum(SAL) as sal_total
FROM 
    EMP
GROUP BY
    DEPTNO
ORDER BY
    sum(SAL) ASC;

SELECT
    DEPTNO,ENAME,SAL
FROM 
    EMP
GROUP BY
    DEPTNO,ENAME,SAL
ORDER BY
    DEPTNO,SAL ASC;

/* 去重关键字 */
SELECT DISTINCT JOB FROM EMP;

SELECT
    *
FROM
    EMP 
WHERE 
    FALSE;

SELECT
    *
FROM
    EMP
WHERE 
    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 
    EMP
WHERE
    HIREDATE = '1981-02-20';


/* where过滤 年月日 Y要大写 d m 小写 */
SELECT
    ENAME,HIREDATE
FROM 
    EMP
WHERE
    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_STUDENT
VALUES
    ('2','ZHANGSAN',str_to_date('12-31-2001','%m-%d-%Y'));

INSERT INTO
    T_STUDENT
VALUES
    ('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) NEWDEPTNO
FROM 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
    EMP
WHERE
    HIREDATE = (
    SELECT
    max(HIREDATE)
FROM
    EMP
    );


SELECT 
    DISTINCT 
        E.DEPTNO, D.DNAME, E.JOB
FROM
    EMP E, DEPT D
WHERE
    E.DEPTNO = D.DEPTNO
ORDER BY
    E.DEPTNO;

/* 查询该公司有几个工作岗位 */
SELECT 
    count(DISTINCT JOB) AS JOBNUM
FROM    
    EMP;

/* 按照工作岗位分组，然后对每一组求最大值。SQL语句中肯定包含 group by、max(sal)； */
SELECT 
    JOB,
    MAX(SAL)
FROM
    EMP
GROUP BY
    JOB;

/* 找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的； */