SQL语句(2)

最新推荐文章于 2023-03-22 10:31:41 发布

Patient365

最新推荐文章于 2023-03-22 10:31:41 发布

阅读量409

点赞数

分类专栏： sql语句文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_51750759/article/details/125773495

版权

sql语句专栏收录该内容

2 篇文章 0 订阅

订阅专栏

– 分组函数（多行函数）

-- MAX MIN COUNT AVG SUM
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary), COUNT(salary)
FROM employees;

-- 字符型
SELECT MAX(last_name), MIN(last_name), MAX(LENGTH(last_name)) , COUNT(last_name)
FROM employees;

SELECT COUNT(employee_id)
FROM employees;

SELECT COUNT(*)
FROM employees;

SELECT COUNT(commission_pct)
FROM employees;

SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

-- 组函数不计算空值
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct)
FROM employees;

– GROUP BY 分组(重点、难点)

SELECT AVG(salary)
FROM employees;

#求各个部门的平均工资

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

#****出现在 SELECT 子句后的非分组函数，一定要出现在 GROUP BY 子句后
SELECT employee_id, department_id, AVG(salary)
FROM employees
GROUP BY department_id, employee_id; -- 多列分组

#各个部门中每个工种(job_id)的平均工资
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

#WHERE 不能过滤组函数，若过滤组函数需要使用 HAVING
SELECT AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 9000;

-- MySQL 中组函数不能嵌套，但是 Oracle 可以,但是不能嵌套太深
/*SELECT max(AVG(salary))
FROM employees
GROUP BY department_id;*/

SELECT MAX(avg_sal)
FROM (
	SELECT AVG(salary) avg_sal
	FROM employees
	GROUP BY department_id
) e;

– GROUP BY 分组练习

-- 1.where子句可否使用组函数进行过滤? 

-- 2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;

-- 3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id,MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;

-- 4.选择具有各个job_id的员工数
SELECT job_id, COUNT(employee_id)
FROM employees
GROUP BY job_id;

-- 5.查询员工 最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;

-- 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;

-- 7.查询所有部门的名字，location_id，员工数量和工资平均值
SELECT department_name, location_id, COUNT(employee_id), AVG(salary)
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
GROUP BY department_name, location_id;

– 子查询

#谁的工资比 Abel 高?

SELECT salary
FROM employees
WHERE last_name = "Abel";

SELECT *
FROM employees
WHERE salary > 11000;

-- 子查询

-- 主查询（外查询）
SELECT *
FROM employees
WHERE salary > (
	-- 子查询（内查询）
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

-- 题目：返回job_id与141号员工相同，salary比143号员工多的 员工姓名，job_id 和工资

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

-- 题目：返回公司 工资最少 的员工的 last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

-- 题目：查询最低工资  大于  50号部门最低工资的   部门id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

-- 非法子查询
SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);


-- 空值问题
SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');


-- 题目：返回其它部门中比 job_id为‘IT_PROG’部门 任一 工资低的员工的 员工号、姓名、job_id 以及salary

-- any : 满足条件的任意一个
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY(
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

-- 题目：返回其它部门中比job_id为‘IT_PROG’部门 所有 工资都低的员工的员工号、姓名、job_id 以及salary
-- all : 满足所有的条件
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary IN (
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

– 子查询练习

-- 1.查询和Zlotkey相同部门的员工姓名
SELECT last_name
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.查询各部门中工资 比 本 部门平均工资高的员工的员工号, 姓名和工资
SELECT employee_id, last_name, salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees e2
	WHERE e2.`department_id` = e1.`department_id`
);

SELECT employee_id, last_name, salary
FROM employees e1, (
	SELECT department_id, AVG(salary) avg_sal
	FROM employees 
	GROUP BY department_id
) e2
WHERE e1.`department_id` = e2.department_id
AND e1.`salary` > e2.avg_sal;


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

-- 5.查询在部门的  location_id为1700的部门  工作的员工的 员工号
SELECT employee_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND location_id = 1700;

SELECT employee_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = ANY(
	SELECT department_id
	FROM departments d
	WHERE location_id = 1700
);

SELECT employee_id
FROM employees 
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id = 1700
);

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

创建和管理表

-- DDL : 数据定义语言

CREATE DATABASE atguigu;
USE atguigu;

# 新建表 CREATE TABLE
CREATE TABLE emp1(
	id INT(10),
	`name` VARCHAR(20),
	salary DOUBLE(10, 2),
	hire_date DATE
);

CREATE TABLE emp3(
	id INT(10) AUTO_INCREMENT,
	NAME VARCHAR(20),
	PRIMARY KEY(id)
);

SELECT * FROM emp3;

# 基于现有表创建新表(相当于表的复制)

CREATE TABLE emp2
AS
SELECT * FROM employees;

SELECT * FROM emp2;

DESC employees;
DESC emp2;

# 基于现有表创建新表，但是不导入数据

CREATE TABLE emp4
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE 1=2;

SELECT * FROM emp4;

CREATE TABLE emp5
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 90;

SELECT * FROM employees5;

# 修改表 ALTER TABLE

#①添加列
ALTER TABLE emp5
ADD gender VARCHAR(2);

ALTER TABLE emp5
ADD age INT(4) DEFAULT 0;

DESC emp5;

#②修改列
ALTER TABLE emp5
MODIFY gender VARCHAR(20);

#③重命名列
ALTER TABLE emp5
CHANGE gender gender222 VARCHAR(2);

#④删除列
ALTER TABLE emp5
DROP COLUMN gender222;

# 重命名表

ALTER TABLE emp5
RENAME employees5;

# 清空表

TRUNCATE TABLE emp5;

# 删除表

DROP TABLE employees5;

-- DML : 数据操纵语言
SELECT * FROM emp5;

DELETE FROM emp5;

-- DCL : 数据控制语言

COMMIT; -- 提交，对数据的操作永久生效

-- 一个 commit 和 一个 rollback 之间的一个或多个的 DML 称为一个事务
-- DDL 不能回滚，自动commit。

ROLLBACK; -- 回滚，默认回滚到上一次 commit

SET autocommit = FALSE; -- 取消自动提交

数据处理之增删改

-- DDL : 数据定义语言
CREATE TABLE emp6(
	id INT(10),
	NAME VARCHAR(20),
	salary DOUBLE(10, 2),
	hire_date DATE
);

-- DML : 数据操纵语言

#添加数据 INSERT INTO ... VALUES...
INSERT INTO emp6
VALUES(101, '张三', 9999.99, '1999-9-9');

INSERT INTO emp6(id, NAME, hire_date)
VALUES(102, '李四', NOW());

#基于现有表导入数据
INSERT INTO emp6(id, NAME, salary)
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 90;

#修改数据 UPDATE ... SET ...
-- 注意：不要忘记 where 条件
UPDATE emp6
SET salary = 1000
WHERE id = 100;

#同时修改多列
UPDATE emp6
SET salary = 20000, hire_date = '2000-10-10'
WHERE id = 100;

#删除数据 DELETE FROM ...
DELETE FROM emp6
WHERE id = 100;

#查询数据
SELECT * FROM emp6;

约束

-- NOT NULL 非空约束，规定某个字段不能为空
CREATE TABLE emp1(
	id INT(10),
	`name` VARCHAR(20) NOT NULL
);

INSERT INTO emp1
VALUES(101, NULL);

INSERT INTO emp1(id)
VALUES(NULL);

SELECT * FROM emp1;

CREATE TABLE emp2(
	id INT(10),
	NAME VARCHAR(20)
);

SELECT * FROM emp2;

INSERT INTO emp2
VALUES(101, NULL);

#对现有表添加非空约束
ALTER TABLE emp2
MODIFY NAME VARCHAR(20) NOT NULL;

#删除非空约束
ALTER TABLE emp2
MODIFY NAME VARCHAR(20) NULL;

-- UNIQUE  唯一约束，规定某个字段在整个表中是唯一的
CREATE TABLE emp3(
	id INT(10),
	NAME VARCHAR(20),
	phone VARCHAR(30),
	CONSTRAINT emp3_phone_un UNIQUE(phone)
);

DESC emp3;

INSERT INTO emp3
VALUES(102, '张三666', '123');

SELECT * FROM emp3;

#添加唯一约束
ALTER TABLE emp3
ADD CONSTRAINT emp3_name_un UNIQUE(NAME);

#删除唯一约束
ALTER TABLE emp3
DROP INDEX emp3_name_un;

-- PRIMARY KEY  主键(非空且唯一),通常利用逐渐锁定一条数据
CREATE TABLE emp4(
	id INT(10) PRIMARY KEY,
	NAME VARCHAR(20)
);

SELECT * FROM emp4;

INSERT INTO emp4
VALUES(101, 'Tom222');

CREATE TABLE emp5(
	id INT(10),
	NAME VARCHAR(20),
	CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

SELECT * FROM emp5;

CREATE TABLE emp6(
	id INT(10),
	NAME VARCHAR(20)
);

#添加主键约束
ALTER TABLE emp6
ADD CONSTRAINT emp6_id_pk PRIMARY KEY(id);

#删除主键约束
ALTER TABLE emp6
DROP PRIMARY KEY;

INSERT INTO emp6
VALUES(NULL, 'Tom');

SELECT * FROM emp6;

-- FOREIGN KEY 外键:通常关联另一个表的主键，出现在外键表中的数据，一定出现在主键表中 
CREATE TABLE dept1(
	dept_id INT(10) PRIMARY KEY,
	dept_name VARCHAR(20)
);

CREATE TABLE emp7(
	id INT(10) PRIMARY KEY,
	NAME VARCHAR(20),
	depart_id INT(10),
	CONSTRAINT emp7_departId_fk FOREIGN KEY(depart_id) REFERENCES dept1(dept_id)
	-- ON DELETE CASCADE -- (级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
	ON DELETE SET NULL -- (级联置空): 子表中相应的列置空
);

SELECT * FROM dept1;

SELECT * FROM emp7;

INSERT INTO dept1
VALUES(10, 'IT');

INSERT INTO emp7
VALUES(101, '张三', 10);

DELETE FROM emp7
WHERE id = 101;

DELETE FROM dept1
WHERE dept_id = 10;

#添加外键约束
ALTER TABLE emp7
ADD CONSTRAINT emp7_depart_id_fk FOREIGN KEY(depart_id) REFERENCES dept1(dept_id);

#删除外键约束
ALTER TABLE emp7
DROP FOREIGN KEY emp7_departId_fk;

-- CHECK  检查约束  MySQL 8.0

CREATE TABLE emp8(
	id INT(10),
	NAME VARCHAR(20),
	salary DOUBLE(10, 2),
	CONSTRAINT emp8_salary_ck CHECK(salary > 3000)
);

SELECT * FROM emp8;

INSERT INTO emp8
VALUES(101, '张三', 1000);

MySQL 分页（重要）

-- LIMIT 用于 MySQL 分页，使用在最末尾
-- 公式：（当前页数-1）*每页条数, 每页条数

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 30, 10;


-- 窗口函数 ROW_NUMBER()
SELECT last_name, salary
FROM (
	SELECT row_number() over(PARTITION BY department_id ORDER BY salary DESC) rownum, department_id, last_name, salary
	FROM employees
) e
WHERE e.rownum = 1;

SELECT *
FROM (
	SELECT row_number() over(PARTITION BY salary) rownum, department_id, last_name, salary
	FROM employees
) e
WHERE e.rownum = 1;