简单查询

下面的语句是否可以执行成功
可以

SELECT last_name , job_id , salary AS sal
FROM employees;

下面的语句是否可以执行成功
可以

SELECT
*
FROM employees;

表达式可以直接用到select之后

SELECT employee_id , last_name,
salary * 12 AS "ANNUAL
SALARY"
FROM employees;

显示表departments的结构,并查询其中的全部数据

DESC departments;
SELECT * FROM `departments`;

显示出表employees中的全部job_id（不能重复

distinct连接字段的时候没有括号

SELECT DISTINCT job_id FROM employees;

显示出表employees的全部列, 各个列之间用逗号连接, 列头显示成OUT_PUT
先使用ifnull函数把null的转到0, 不然所有commission_pct为null的都会变null

SELECT
IFNULL(commission_pct,0) AS 奖金率,
commission_pct
FROM
employees;
SELECT
CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
FROM
employees;

单行函数

显示系统时间(注: 日期+时间)

SELECT NOW();

查询员工号, 姓名, 工资, 以及工资提高百分之20%后的结果（new salary

SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM employees;

将员工的姓名按首字母排序, 并写出姓名的长度(length)

SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name
FROM employees
ORDER BY 首字符;

做一个查询,产生下面的结果
<last_name> earns monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary"
FROM employees
WHERE salary=24000;

使用case-when, 按照下面的条件:

产生下面的结果
Last_name Job_id Grade
king AD_PRES A

SELECT last_name,job_id AS
job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_PRE' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees
WHERE job_id = 'AD_PRES';

分组函数

查询公司员工工资的最大值, 最小值, 平均值, 总和

SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和
FROM employees;

查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE)

SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM employees;
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;
SELECT DATEDIFF('1995-2-7','1995-2-6');

查询部门编号为90的员工个数

SELECT COUNT(*) FROM employees WHERE department_id = 90;

排序查询

查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;

选择工资不在8000到17000的员工的姓名和工资, 按工资降序

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

查询邮箱中包含e的员工信息, 并先按邮箱的字节数降序, 再按部门号升序

SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

分组查询

查询各job_id的员工工资的最大值, 最小值, 平均值, 总和, 并按job_id升序.

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

查询员工最高工资和最低工资的差距（DIFFERENCE

SELECT MAX(salary)-MIN(salary) DIFFRENCE
FROM employees;

查询各个管理者手下员工的最低工资, 其中最低工资不能低于6000, 没有管理者的员工不计算在内

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

查询所有部门的编号, 员工数量和工资平均值, 并按平均工资降序

SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;

选择具有各个job_id的员工人数

SELECT COUNT(*) 个数,job_id
FROM employees
GROUP BY job_id;

连接查询

显示所有员工的姓名, 部门号和部门名称

USE myemployees;
SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;

查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;

选择所有有奖金的员工的
last_name, department_name, location_id, city

SELECT last_name, department_name, l.location_id , 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;

选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name

SELECT last_name , job_id , d.department_id , department_name
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';

查询每个工种及每个部门的部门名, 工种名和最低工资

SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;

查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;

选择指定员工的姓名, 员工号, 以及他的管理者的姓名和员工号, 结果类似于下面的格式:
employees Emp# manager Mgr#
kochhar 101 king 100

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';

查询和Zlotkey相同部门的员工姓名和工资

#①查询Zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
#②查询部门号=①的姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
)

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

#①查询平均工资
SELECT AVG(salary)
FROM employees
#②查询工资>①的员工号，姓名和工资。
SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
);

查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

#1. 查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2. 连接1st结果集和employees表, 进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;

查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

#1. 查询姓名中包含字母u的员工的部门
SELECT
DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
#2. 查询部门号=1st中的任意一个的员工号和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
SELECT
DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);

查询在部门的location_id为1700的部门工作的员工的员工号

#①查询location_id为1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id
= 1700
#②查询部门号=①中的任意一个的员工号
SELECT employee_id
FROM employees
WHERE department_id =ANY(
SELECT DISTINCT department_id
FROM departments
WHERE location_id
= 1700
);

查询管理者是King的员工姓名和工资

#①查询姓名为king的员工编号
SELECT employee_id
FROM employees
WHERE last_name
= 'K_ing'
#②查询哪个员工的manager_id = ①
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name
= 'K_ing'
);

查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为 姓.名

#1. 查询最高工资
SELECT MAX(salary)
FROM employees
#2. 查询工资=①的姓.名
SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees
);

查询综合

查询工资最低的员工信息: last_name, salary

#①查询最低的工资
SELECT MIN(salary)
FROM employees
#②查询last_name,salary，要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);

查询平均工资最低的部门信息
方式一

#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
#③查询哪个部门的平均工资=②
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
);
#④查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
)
);

方式二

#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);

自己写了一个

SELECT AVG(e.salary) AS dmean, d.*
FROM employees AS e
INNER JOIN departments AS d
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_id`
ORDER BY dmean ASC
LIMIT 1;

查询平均工资最低的部门信息和该部门的平均工资

#1.各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2. 求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;
#3. 查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.department_id;

查询平均工资最高的 job 信息

#①查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
#②查询job信息
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);

查询平均工资高于公司平均工资的部门有哪些?

#1. 查询平均工资
SELECT AVG(salary)
FROM employees
#2. 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#3. 筛选2nd结果集，满足平均工资>①
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
);

查询出公司中所有 manager 的详细信息.

#①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees
#②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id =ANY(
SELECT DISTINCT manager_id
FROM employees
);

各个部门中 最高工资中最低的那个部门的 最低工资是多少

#①查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
#②查询①结果的那个部门的最低工资
SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);

查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

#1. 查询平均工资最高的部门编号
SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
#2. 将employees和departments连接查询，筛选条件是①
SELECT
last_name, d.department_id, email, salary
FROM
employees e
INNER JOIN departments d
ON d.manager_id = e.employee_id
WHERE d.department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1) ;

查询每个专业的学生人数

SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;

查询参加考试的学生中, 每个学生的平均分, 最高分

SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

查询姓张的每个学生的最低分大于60的学号, 姓名

SELECT s.studentno,s.`studentname`,MIN(score)
FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.`studentno`
HAVING MIN(score)>60;

查询每个专业生日在“1988-1-1”后的学生姓名, 专业名称

SELECT m.`majorname`,s.`studentname`
FROM student s
JOIN major m
ON m.`majorid`=s.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1')>0
GROUP BY m.`majorid`;

查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;

查询专业和张翠山一样的学生的最低分

#1. 查询张翠山的专业编号
SELECT majorid
FROM student
WHERE studentname = '张翠山'
#2. 查询编号=1st的所有学生编号
SELECT studentno
FROM student
WHERE majorid=(
SELECT majorid
FROM student
WHERE studentname = '张翠山'
)
#3. 查询最低分
SELECT MIN(score)
FROM result
WHERE studentno IN(
SELECT studentno
FROM student
WHERE majorid=(
SELECT majorid
FROM student
WHERE studentname = '张翠山'
)
);

查询大于60分的学生的姓名, 密码, 专业名

SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid=
m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;

按邮箱位数分组, 查询每组的学生个数

SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);

查询学生名, 专业名, 分数

SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid=
m.majorid
LEFT JOIN result r ON s.studentno=r.studentno

查询哪个专业没有学生, 分别用左连接和右连接实现
左

SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM major m
LEFT JOIN student s ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

右

SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM student s
RIGHT JOIN
major m ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

查询没有成绩的学生人数

SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL

数据增删改

创建表my_employees

USE myemployees;
CREATE TABLE my_employees(
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);

显示表my_employees的结构
DESC my_employees;

向my_employees表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550

方式一

INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
DELETE FROM my_employees;

方式二

INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;

向users表中插入数据
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40

INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20);

将3号员工的last_name修改为"drelxer"

UPDATE my_employees SET last_name='drelxer' WHERE id = 3;

将所有工资少于900的员工的工资修改为1000

UPDATE my_employees SET salary=1000 WHERE salary<900;

将userid 为Bbiri的user表和my_employees表的记录全部删除

DELETE u,e
FROM users u
JOIN my_employees e ON u.`userid`=e.`Userid`
WHERE u.`userid`='Bbiri';

删除所有数据

DELETE FROM my_employees;
DELETE FROM users;

检查所作的修正

SELECT * FROM my_employees;
SELECT * FROM users;

清空表my_employees

TRUNCATE TABLE my_employees;

库和表的管理

创建表dept1
NAME NULL? TYPE
id INT(7)
NAME VARCHAR(25)

USE test;
CREATE TABLE dept1(
id INT(7),
NAME VARCHAR(25)
);

将表departments中的数据插入新表dept2中

CREATE TABLE dept2
SELECT department_id,department_name
FROM myemployees.departments;

创建表emp5
NAME NULL? TYPE
id INT(7)
First_name VARCHAR (25)
Last_name VARCHAR(25)
Dept_id INT(7)

CREATE TABLE emp5(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

将列Last_name的长度增加到50

ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);

根据表employees创建employees2

只复制结构, 没有数据

CREATE TABLE employees2 LIKE myemployees.employees;

删除表emp5

DROP TABLE IF EXISTS emp5;

将表employees2重命名为emp5

ALTER TABLE employees2 RENAME TO emp5;

在表dept和emp5中添加新列test_column，并检查所作的操作

ALTER TABLE emp5 ADD COLUMN test_column INT;``

直接删除表emp5中的列 dept_id

DESC emp5;
ALTER TABLE emp5 DROP COLUMN test_column;

存储过程

创建存储过程实现传入用户名和密码, 插入到admin表中

CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,PASSWORD)
VALUES(username,loginpwd);
END $

创建存储过程实现传入女神编号, 返回女神名称和女神电话

CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
SELECT b.name ,b.phone INTO NAME,phone
FROM beauty b
WHERE b.id = id;
END $

创建存储存储过程或函数实现传入两个女神生日, 返回大小

CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(birth1,birth2) INTO result;
END $

创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回

CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $
CALL test_pro4(NOW(),@str)$
SELECT @str $

创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串
如 传入 ：小昭
返回： 小昭 AND 张无忌

DROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName;
END $
CALL test_pro5('柳岩',@str)$
SELECT @str $

创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录

DROP PROCEDURE test_pro6$
CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startIndex,size;
END $
CALL test_pro6(3,5)$

生成一百个随机数

DELIMITER $
CREATE PROCEDURE ttt(IN num INT)
BEGIN
DECLARE
i INT DEFAULT 1;
WHILE i <= num DO
INSERT INTO testloop(threshold) VALUES(RAND());
SET i=i+1;
END WHILE ;
END$
CALL ttt(100);
SELECT * FROM testloop;

最后

以上就是花痴刺猬为你收集整理的MySQL案例集锦简单查询单行函数分组函数排序查询分组查询连接查询查询综合数据增删改库和表的管理存储过程生成一百个随机数的全部内容，希望文章能够帮你解决MySQL案例集锦简单查询单行函数分组函数排序查询分组查询连接查询查询综合数据增删改库和表的管理存储过程生成一百个随机数所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。