SQL - CURD示例 详解大全

---

-- 创建数据库
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;

---

没有个性 哪来的签名！
详情请关注点我：https://www.cnblogs.com/guyu-/
持续更新中··· ···