SQL

---

创建数据库
CREATE DATABASE guyu;


进入数据库
USE guyu;


创建表格名:emp
CREATE TABLE emp (
    empno INT PRIMARY KEY auto_increment,
    ename VARCHAR (20),
    job VARCHAR (30),
    salary NUMERIC (7, 2),
    bonus NUMERIC (7, 2),
    mgr INT,
    hiredate date,
    deptno INT
);


--查询该表字段信息
DESC emp;


导入数据
INSERT INTO emp VALUES(1001,'rose','analyst',15000,5000,null,'2010-2-2',10);
INSERT INTO emp VALUES(1002,'tom','analyst',10000,3000,1001,'2010-3-5',10);
INSERT INTO emp VALUES(1003,'jerry','analyst',10000,3000,null,'2010-3-5',10);
INSERT INTO emp VALUES(1004,'smith','sales',8000,2000,null,'2010-5-5',10);
INSERT INTO emp VALUES(1005,'blake','programmer',9000,null,null,'2010-10-7',30);
INSERT INTO emp VALUES(1006,'zhangsan','sales',11000,null,null,'2009-12-15',30);
INSERT INTO emp VALUES(1007,'lisi','clerk',2000,500,null,'2008-3-5',10);
INSERT INTO emp VALUES(1008,'king','boss',20000,null,null,'2008-1-6',10);
INSERT INTO emp VALUES(1009,'allen','clerk',8500,2000,null,'2009-3-5',30);
INSERT INTO emp VALUES(1010,'dawson','sales',7400,3000,null,'2010-5-5',30);


显示所有信息
SELECT * FROM emp;




1.查询2010年入职的员工（三种方法）
方法一：
SELECT
    ename,
    hiredate
FROM
    emp 
WHERE
    hiredate LIKE '2010%';

方法二：
SELECT
    ename,
    hiredate
FROM
    emp
WHERE
    YEAR(hiredate)=2010;

方法三：
SELECT
    ename,
    hiredate
FROM
    emp
WHERE
    hiredate>='2010-01-01'  AND hiredate<='2010-12-31';



2.查询薪资在10000~20000之间的员工
方法一：
SELECT
    ename,
    salary
FROM
    emp
WHERE
    salary BETWEEN 10000 AND 20000;

方法二：
SELECT
    ename,
    salary
FROM
    emp
WHERE
    salary >= '10000' AND salary <= '20000';



3。查询薪资不在10000~20000之间的员工
方法一：
SELECT
    ename,
    salary
FROM
    emp
WHERE
    salary NOT BETWEEN 10000 AND 20000;

方法二：
SELECT
    ename,
    salary
FROM
    emp
WHERE
    NOT salary >= '10000' AND salary <= '20000';



4.查询姓名中含有a的名字的员工信息
SELECT
    ename,
    empno,
    job,
    hiredate
FROM
    emp
WHERE
    ename LIKE '%a%';



5.查询姓名中第二个字母含有a的员工信息
SELECT
    ename,
    empno,
    job,
    hiredate
FROM
    emp
WHERE
    ename LIKE '_a%';



6.查询所有员工的年薪是多少
SELECT
    ename,
    salary,
    bonus,
    salary * 12 + ifnull(bonus, 0) year_sal
FROM
    emp;



7.显示所有员工的姓名,要求首字母大写(特殊难点)
-- 方法一:
SELECT
    CONCAT(
        upper(substr(ename, 1, 1)),
        substr(ename, 2)
    )
FROM
    emp;

方法二:
SELECT
    REPLACE (
        ename,
        substr(ename, 1, 1),
        upper(substr(ename, 1, 1))
    )
FROM
    emp;



8.修改1002, 1003, 1004的领导为1001
UPDATE emp
SET mgr = 1001
WHERE
    empno IN (1002, 1003, 1004);



9.修改1001, 1005的领导设置为1008
UPDATE emp
    SET mgr = 1008
WHERE
--     empno IN (1001,1005);



10.修改表格名称, 将emp改为employee
    alter table emp rename to employee;



11.修改1001, 1002 的入职时间为2019-07-30
UPDATE employee
SET hiredate = '2019-7-30'
WHERE
    empno IN (1001, 1002);


    
12.修改1006, 1007的入职时间为2019-4-15
UPDATE employee
SET hiredate = '2019-4-15'
WHERE
    empno IN (1006, 1007);



13.修改1002, 1007的入职时间为2020-8-26
UPDATE employee
SET hiredate = '2020-8-26'
WHERE
    empno IN (1002, 1007);



14.查询今年入职的员工有哪些
SELECT
    ename,
    hiredate
FROM
    employee
WHERE
    YEAR (hiredate) = YEAR (CURDATE());



15.查询当月入职的员工有哪些
SELECT
    ename,
    hiredate
FROM
    employee
WHERE
    MONTH (hiredate) = MONTH (now());
AND YEAR (hiredate) = YEAR (curdate());



16.查询薪资大于10000且部门号为10的员工年薪
SELECT
    ename,
    salary,
    bonus,
    deptno,
    salary * 12 + IFNULL(bonus, 0) year_sal
FROM
    employee
WHERE
    salary > 10000
AND deptno = 10;



17.查询薪资大于10000且奖金大于1000的员工信息
SELECT
    *
FROM
    employee
WHERE
    salary > 10000
AND bonus > 1000;



18.查询当前员工中哪些员工已经超过了3年
SELECT
    ename,hiredate
FROM
    employee
WHERE
    hiredate < DATE_SUB(CURDATE(), INTERVAL 3 YEAR);



-- 19.查询employee表所有数据
SELECT * from employee;

　　 -- 20.修改表格原先名称, 将employee改为emp
    alter table employee rename to emp;

                  故屿γ