mapengpeng1999@163.com SQL练习

数据库不区分大小写，但必须全是英文标点符号。要用换行和缩进来让代码更美观，语句以;结束。

基础查询：

#1. 显示表 departments 的结构，并查询其中的全部数据 （显示表结构DESC）  
DESC departments;
SELECT * FROM departments;

#2. 显示出表 employees 中的全部 job_id（不能重复）（去掉重复数据DISTINCT）
SELECT DISTINCT job_id FROM employees;
#3. 显示系统当前时间(注：日期+时间)
SELECT NOW();
#4. 查询员工号为 176 的员工的姓名和部门号和年薪   (取别名用AS和双引号，不用也行。)（WHERE条件筛选）
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees WHERE employee_id = 176;

#5. 选择工资不在 5000 到 12000 的员工的姓名和工资  （NOT BETWEEN AND的使用（包含区间端口））
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

#6. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位  （ BETWEEN AND的使用（包含区间端口））
SELECT last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

#7. 选择在 20 或 50 号部门工作的员工姓名和部门号   （IN（符合其中任一条件就行）的使用）
SELECT last_name,department_id
FROM employees
WHERE department_id IN(20,50);

#8. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位  
（IN（符合其中任一条件就行）的使用）
SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id IN(100,101,110);

#9. 选择公司中没有管理者的员工姓名及 job_id       (IS NULL和IS NOT NULL的使用）
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;

#6. 选择公司中有奖金的员工姓名，工资和奖金级别    (IS NULL和IS NOT NULL的使用）
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#10. 选择员工姓名的第三个字母是 a 的员工姓名   （模糊查询LIKE和下划线任意一个占位符的使用）
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
#11. 选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%'
OR last_name LIKE '%e%a%';
#12. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT *
FROM employees
WHERE first_name LIKE '%e';

/*
%多个字符，'%kk%'   'kk%'   '%kk'
_一个字符，_占一个位置。 '_k'第二个字符为k   'k_'第一个字符为k
*/

#1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name;
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
#4. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY last_name DESC;

#1. 查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序    
（每，每个，每组，各，各个，要用GROUP BY分组）
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;
#5. 选择具有各个 job_id 的员工人数
SELECT COUNT(*)
FROM employees
GROUP BY job_id;
#4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(employee_id),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;
#2. 查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
（求天数差只要知道具体某日就行不用具体到时分秒）
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) 'DIFFRENCE'
FROM employees;
#2. 查询员工最高工资和最低工资的差距
SELECT (MAX(salary) - MIN(salary)) 工资差
FROM employees;

#3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
SELECT MIN(salary) m,employee_id,last_name,manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING m>=6000;
#连接字符串 CONCAT(`last_name` ,`first_name`)

/* 5. 使用 case-when，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
*/
SELECT last_name,job_id,CASE job_id
WHEN 'AD_PRES' THEN  'A'
WHEN 'ST_MAN' THEN  'B'
WHEN 'IT_PROG' THEN  'C'
WHEN 'SA_REP' THEN  'D'
WHEN 'ST_CLERK' THEN  'E'
ELSE 'F'
END Grade
FROM employees;

SQL92连接查询（只支持内连接）：

SQL92连接查询（只支持内连接），表的连接条件放在WHERE中，查询的筛选条件放在AND中。
分组后筛选用HAVING，聚合分组函数只能和HAVING一起使用，不能和WHERE一起使用。
#1. 显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,d.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

#2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id
SELECT e.job_id,d.`location_id`
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND d.`department_id`=90;

#3. 选择所有有奖金的员工的last_name , department_name , location_id , city
SELECT e.last_name , d.department_name , d.location_id , l.city
FROM employees e,departments d,locations l
WHERE e.department_id = d.`department_id`
AND d.`location_id` = l.location_id
AND e.`commission_pct` IS NOT NULL;

#4. 选择city在Toronto工作的员工的last_name , job_id , department_id , department_name
SELECT e.last_name , e.job_id , d.department_id , d.department_name
FROM employees e,departments d,locations l
WHERE e.department_id = d.`department_id`
AND d.`location_id` = l.location_id
AND l.city = 'Toronto';

#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.department_name,j.job_title,j.`min_salary`
FROM departments d,jobs j,employees e
WHERE d.department_id = e.`department_id`
AND e.job_id = j.`job_id`
GROUP BY e.job_id,d.department_id;

#6.查询每个国家下的部门个数大于 2 的国家编号
SELECT l.`country_id`,COUNT(*)
FROM locations l,departments d
WHERE l.`location_id` = d.`location_id`
GROUP BY country_id
HAVING COUNT(*) > 2;

SQL99连接查询：

#一、查询编号>3 的女神的男朋友信息
SELECT bo.*,b.id
FROM boys bo
RIGHT OUTER JOIN beauty b
ON bo.id = b.`boyfriend_id`
WHERE b.id > 3;

#二、查询哪个城市没有部门
SELECT l.city
FROM locations l
LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.department_id IS NULL;

#三、查询部门名为 SAL 或 IT 的员工信息
SELECT e.*
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN('SAL','IT');

子查询：

#1. 查询和 Zlotkey 相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name='Zlotkey'
);

#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

#3. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
	SELECT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

#4. 查询在部门 location_id 为 1700 的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN(  #IN和=ANY是一样的效果
	SELECT department_id
	FROM departments
	WHERE location_id=1700
);

#5. 查询管理者是 King 的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN( 
	SELECT employee_id
	FROM employees
	WHERE last_name = 'K_ing'
);

#6. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名
SELECT CONCAT(last_name,first_name) '姓.名'
FROM employees
WHERE salary = (
	SELECT MAX(salary)
	FROM employees
);

#7. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT e.employee_id,e.last_name,e.salary,av.a
FROM employees e
JOIN (
SELECT AVG(salary) a,department_id
FROM employees
GROUP BY department_id) av
ON e.department_id = av.department_id
WHERE e.salary > av.a;

#4. 向 users 表中插入数据
INSERT INTO users VALUES(1,'patel',895),(2,'Dancs',860);
#5. 将 2 号员工的 last_name 修改为“drelxer”
UPDATE my_employees SET last_name = 'drelxer' WHERE Id=2;
#6. 将所有工资少于 900 的员工的工资修改为 1000
UPDATE my_employees SET Salary=1000 WHERE Salary<900;
#7. 将 userid 为 Bbiri 的 user 表和 my_employees 表的记录全部删除
DELETE FROM users WHERE userid = 'Dancs';
DELETE FROM my_employees WHERE Userid = 'Bdancs';

#1. 向表 dept1 的 id 列中添加 PRIMARY KEY 约束（my_emp_id_pk）
ALTER TABLE dept1 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

#2. 将表 departments 中的数据插入新表 dept2 中
CREATE TABLE dept2 AS
SELECT * FROM myemployees.departments;

#4. 将列 Last_name 的长度增加到 50
ALTER TABLE emp5 MODIFY Last_name VARCHAR(50);

#7. 将表 employees2 重命名为 emp5
ALTER TABLE employees2 RENAME TO emp5;

#9.直接删除表 emp5 中的列 job_id
ALTER TABLE emp5 DROP COLUMN job_id;

#一、创建视图 emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE VIEW emp_v1 AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';

#二、要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员工姓名和邮箱、电话号码
CREATE OR REPLACE VIEW emp_v1 AS
SELECT last_name,salary,email,phone_number
FROM employees
WHERE phone_number LIKE '011%'
AND email LIKE '%e%';

#存储过程
#1、创建存储过程或函数实现传入用户名和密码，插入到 admin 表中
DELIMITER $
CREATE PROCEDURE inste(IN uName VARCHAR(10),IN uPwd VARCHAR(10))
BEGIN
	INSERT INTO admin VALUES(NULL,uName,uPwd);
END$
CALL inste('李明','111')$
SELECT * FROM admin$
#2、创建存储过程或函数实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE gir(IN girlid INT,OUT girlName VARCHAR(50),OUT girlPhone VARCHAR(11))
BEGIN
	SELECT NAME,phone INTO girlName,girlPhone
	FROM beauty
	WHERE id = girlid;
END$
CALL gir(2,@gName,@gPhone)$
SELECT @gName,@gPhone$
#3、创建存储程或函数实现传入两个女神生日，返回大小
CREATE PROCEDURE dif(IN girlOne DATETIME,IN girlTwo DATETIME,OUT com VARCHAR(3))
BEGIN
	IF DATEDIFF(girlOne,girlTwo)>0 THEN SELECT '前者大' INTO com;
	ELSEIF DATEDIFF(girlOne,girlTwo)<0 THEN SELECT '后者大' INTO com;
	ELSE SELECT '相等' INTO com;
	END IF;
END$	
#4、创建存储过程或函数实现传入一个日期，格式化成 xx 年 xx 月 xx 日并返回
CREATE PROCEDURE geshihua(IN dat DATETIME,OUT datStr VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(dat,'%Y年%m月%d日') INTO datStr;
END$
CALL geshihua(NOW(),@current)$
SELECT @current$
/*5、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串
如 传入 ：小昭
返回： 小昭 and 张无忌
*/
CREATE PROCEDURE shuchu(IN girlNam VARCHAR(50),OUT girlBoy VARCHAR(100))
BEGIN
	SELECT CONCAT(NAME,' and ',boyNAME) INTO girlBoy
	FROM beauty b
	INNER JOIN boys bo
	ON b.boyfriend_id = bo.id
	WHERE b.name = girlNam;
END$
CALL shuchu('Angelababy',@hebing)$
SELECT @hebing$
#6、创建存储过程或函数，根据传入的条目数和起始索引，查询 beauty 表的记录
CREATE PROCEDURE record(IN numCount INT,IN numStart INT)
BEGIN
	SELECT *
	FROM beauty
	LIMIT numStart,numCount;
END$
CALL record(5,2)$

#函数
#1、创建函数，实现传入两个 float，返回二者之和
CREATE FUNCTION totalSum(strNum1 FLOAT,strNum2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE totSum FLOAT DEFAULT 0;
	SET totSum = strNum1 + strNum2;
	RETURN totSum;
END$
SELECT totalSum(230,435)$
#2、创建函数，实现传入工种名，返回该工种的员工人数
CREATE FUNCTION countNuma(jobName VARCHAR(10)) RETURNS INT
BEGIN
	DECLARE resultNum INT;
	SELECT COUNT(*) INTO resultNum
	FROM employees
	WHERE job_id = jobName;
	RETURN resultNum;
END$
SELECT countNuma('IT_PROG')$
#3、创建函数，实现传入员工名，返回该员工的领导名
CREATE FUNCTION leader(employeeName VARCHAR(25)) RETURNS VARCHAR(25)
BEGIN
	DECLARE insLeader VARCHAR(25);
	SELECT last_name INTO insLeader
	FROM employees
	WHERE employee_id = (
		SELECT manager_id
		FROM employees
		WHERE last_name = employeeName
	);
	RETURN insLeader;	
END$
SELECT leader('Kochhar')$

#控制语句
/* 1、已知表 stringcontent
其中字段：
id 自增长
content varchar(20)
向该表插入指定个数的，随机的字符串*/
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);
DESC stringcontent;
DROP PROCEDURE randomInsert$
CREATE PROCEDURE randomInsert(IN rNum INT)
BEGIN
	DECLARE str CHAR(26);
	DECLARE i INT DEFAULT 1;
	DECLARE startSubStr INT DEFAULT 1;
	DECLARE getLen INT DEFAULT 1;
	SET str = 'abcdefghigklmnopqrstuwvxyz';
	WHILE i<=rNum DO
	SET startSubStr = CEIL(RAND()*26);
	SET getLen = 26 - startSubStr + 1;
	SET getLen = IF(getLen>20,20,getLen);
	INSERT INTO stringcontent VALUES(NULL,SUBSTR(str,startSubStr,getLen));
	SET i = i + 1;
	END WHILE;
END$