03_SQL

最新推荐文章于 2023-02-16 10:54:29 发布
最新推荐文章于 2023-02-16 10:54:29 发布
阅读量184 收藏
点赞数
分类专栏： java后端 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42430409/article/details/126609473
版权

1.基本Select

列别名

使用AS或者空格间隔

一般别名使用双引号包起

SELECT last_name AS name, commission_pct comm
FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;

去重

SELECT DISTINCT department_id
FROM employees;

添加常数列

SELECT '尚硅谷' as corporation, last_name FROM employees;

2.显示列结构

DESCRIBE employees;
或
DESC employees;

3.特殊运算符

在这里插入图片描述

4.排序与分页

排序

使用 ORDER BY 子句排序

ASC（ascend）: 升序

DESC（descend）:降序

ORDER BY 子句在SELECT语句的结尾。

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
多列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

可以使用不在SELECT列表中的列排序。

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第 一列数据中所有值都是唯一的，将不再对第二列进行排序。

分页

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。 MySQL中使用 LIMIT 实现分页

LIMIT [位置偏移量,] 行数
--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;

注意：LIMIT 子句必须放在整个SELECT语句的最后！

不同DBMS关键字不同

在不同的 DBMS 中使用的关键字可能不同。

在这里插入图片描述

5.多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个 关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进 行关联。

笛卡尔积

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;#输出2889行
SELECT COUNT(employee_id) FROM employees;
#输出107行
SELECT COUNT(department_id)FROM departments;
#输出27行
SELECT 107*27 FROM dual;
#2889 = 27*107

在这里插入图片描述

SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交 叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡 尔积：

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

笛卡尔积的错误会在下面条件下产生：

  1. 省略多个表的连接条件（或关联条件）
  2. 连接条件（或关联条件）无效
  3. 所有表中的所有行互相连接

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件
#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

多表查询分类

等值连接

在这里插入图片描述

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。

非等值连接

在这里插入图片描述

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j #别名
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
自连接

在这里插入图片描述

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两 个表再进行内连接，外连接等查询。

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager #别名
WHERE worker.manager_id = manager.employee_id ;

非自连接的其他都是外连接

内外连接

在这里插入图片描述

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

使用JOIN…ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
  1. 可以使用 ON 子句指定额外的连接条件。
  2. 这个连接条件是与其它条件分开的。
  3. ON 子句使语句具有更高的易读性。
  4. 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
内连接
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
外连接
#左外连接(LEFT OUTER JOIN)
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

# 右外连接(RIGHT OUTER JOIN)
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

#满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
自然连接

它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
Using连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

#与上方结果相同
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
七种sql join实现

在这里插入图片描述

Union

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

  • UNION 操作符返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重

执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效 率。

SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

表连接约束方式区别

表连接的约束条件可以有三种方式：

  1. WHERE, ON, USING WHERE：适用于所有关联查询
  2. ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起 写，但分开写可读性更好。
  3. USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字 段值相等
#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

6.单行函数

  1. 操作数据对象 接受参数返回一个结果 只对一行进行变换 每行返回一个结果 可以嵌套 参数可以是一列或一个值

常用数学函数

在这里插入图片描述

在这里插入图片描述

进制转换函数

在这里插入图片描述

字符串函数

注意：MySQL中，字符串的位置是从1开始的

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

日期与时间函数

获取日期、时间

在这里插入图片描述

日期与时间戳的转换

在这里插入图片描述

获取月份、星期、星期数、天数等函数

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;
时间和秒钟转换的函数

在这里插入图片描述

计算日期和时间的函数

在这里插入图片描述

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,
DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

在这里插入图片描述

SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;
日期的格式化与解析

在这里插入图片描述

在这里插入图片描述

流程控制函数

在这里插入图片描述

加密与解密函数

在这里插入图片描述

在这里插入图片描述

7.聚合函数(多行函数)

聚合函数不能使用在WHERE中

聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

聚合函数类型

  • AVG()
  • SUM()
#可以对数值型数据使用AVG 和 SUM 函数。
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
  • MAX()
  • MIN()
#可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
  • COUNT()

注：count(*)会统计值为 NULL 的行，而 count(列名)不会统计列为 NULL 值的行。

#COUNT(*)返回表中记录总数，适用于任意数据类型。
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

8.GROUP BY

在这里插入图片描述

可以使用GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

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

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

多列分组

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

在这里插入图片描述

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所 有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP#添加一条整个公司的工资

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

HAVING

在这里插入图片描述

过滤分组：HAVING子句

  1. 行已经被分组。
  2. 使用了聚合函数。
  3. 满足HAVING 子句中条件的分组将被显示。
  4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

在这里插入图片描述

在这里插入图片描述

9.SELECT的执行过程

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

10.子查询

在这里插入图片描述

SELECT last_name,salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id

#实现方式1：不成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
    (SELECT manager_id
     FROM employees
     WHERE employee_id IN (174,141))
AND department_id IN
    (SELECT department_id
     FROM employees
     WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
#实现方式2：成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
    (SELECT manager_id, department_id
     FROM employees
     WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

分类

我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。

我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。

单行子查询

在这里插入图片描述

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);


# HAVING 中的子查询
#查询最低工资大于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);
#CASE中的子查询
#显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
SELECT employee_id, last_name,
    (CASE department_id
    WHEN
        (SELECT department_id FROM departments
        WHERE location_id = 1800)
    THEN 'Canada' ELSE 'USA' END) location
FROM employees;
#此处case语句表示当department_id的值与子查询查出来的department_id相等时，location为Canada，否则为USA
多行子查询

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件 关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

在这里插入图片描述

在这里插入图片描述

#方式二：在 FROM 中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;
#from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟的表”来使用。

#在ORDER BY 中使用子查询：
#题目：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
    SELECT department_name
    FROM departments d
    WHERE e.`department_id` = d.`department_id`
);
EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

  • 如果在子查询中不存在满足条件的行： 条件返回 FALSE 继续在子查询中查找
  • 如果在子查询中存在满足条件的行： 不在子查询中继续查找 条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式一：
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
              FROM employees e2
              WHERE e2.manager_id =
              e1.employee_id);

#方式二:自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

#方式三:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees
);


#题目：查询departments表中，不存在于employees表中的部门department_id和department_name
#此处子查询SELECT 随便写什么都可以
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X' 
                  FROM employees
                  WHERE department_id = d.department_id);
相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

#题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2）
UPDATE employees e
SET department_name = (SELECT department_name
                       FROM departments d
                       WHERE e.department_id = d.department_id);
相关删除
#题目：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
 FROM emp_history
 WHERE employee_id = e.employee_id);

11.创建与管理表

数据库操作

#创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
#删除数据库
DROP DATABASE IF EXISTS 数据库名;

表操作

创建表
CREATE TABLE [IF NOT EXISTS] 表名(
    字段1, 数据类型 [约束条件] [默认值],
    字段2, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件]
);

相关约束：

  • AUTO_INCREMENT 自增
  • PRIMARY KEY 主键
CREATE TABLE dept(
    -- int类型，自增
    deptno INT(2) AUTO_INCREMENT,
    dname VARCHAR(14),
    loc VARCHAR(13),
    -- 主键
    PRIMARY KEY (deptno)
);

使用 AS subquery 选项，将创建表和插入数据结合起来

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

#使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。
SHOW CREATE TABLE 表名\G
修改表

使用 ALTER TABLE 语句可以实现：

  • 向已有的表中添加列
  • 修改现有表中的列
  • 删除现有表中的列
  • 重命名现有表中的列

添加列

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

ALTER TABLE dept80 ADD job_id varchar(15);

修改列

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

ALTER TABLE dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

重命名列

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

删除列

ALTER TABLE 表名 DROP 【COLUMN】字段名

ALTER TABLE dept80 DROP COLUMN job_id;

重命名表

#方式一
RENAME TABLE emp TO myemp;
#方式二
ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略
删除表
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

DROP TABLE dept80;
清空表
TRUNCATE TABLE detail_dep;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

12.数据处理

插入

#整体插入
INSERT INTO 表名 VALUES (value1,value2,....);
INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);

#局部插入
INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);
INSERT INTO departments(department_id, department_name) VALUES (80, 'IT');

#多条整体插入
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

#多条局部插入
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

#通过查询插入
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

更新数据

可以一次更新多条数据。

如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

删除数据

DELETE FROM table_name [WHERE <condition>];

DELETE FROM departments
WHERE department_name = 'Finance';

计算列

a列值为1、b列值为2，c列 不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

CREATE TABLE tb1(
    id INT,
    a INT,
    b INT,
    c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);
INSERT INTO tb1(a,b) VALUES (100,200);

13.数据类型

ZEROFILL

0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指 定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

DECIMAL

在这里插入图片描述

定点数

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。

定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。

**由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型 外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能 差。 **

日期时间

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

14.约束

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

  • 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录
  • 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”
  • 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门
  • 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门 经理的工资不得高于本部门职工的平均工资的5倍。

约束是表级的强制规定。 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定 约束。

约束的分类

  • 根据约束数据列的限制，约束可分为：
    • 单列约束：每个约束只约束一列
    • 多列约束：每个约束可约束多列数据
  • 根据约束的作用范围，约束可分为：
    • 列级约束：只能作用在一个列上，跟在列的定义后面
    • 表级约束：可以作用在多个列上，不与列一起，而是单独定义

在这里插入图片描述

  • 根据约束起的作用，约束可分为：
    • NOT NULL 非空约束，规定某个字段不能为空
    • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的 PRIMARY KEY 主键(非空且唯一)约束
    • FOREIGN KEY 外键约束
    • CHECK 检查约束
    • DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

#查看约束
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名称';

非空约束

  • 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
  • 一个表可以有很多列都分别限定了非空
  • 空字符串’'不等于NULL，0也不等于NULL
CREATE TABLE 表名称(
    字段名 数据类型,
    字段名 数据类型 NOT NULL,
    字段名 数据类型 NOT NULL
);

alter table 表名称 modify 字段名 数据类型 not null

唯一性约束

用来限制某个字段/某列的值不能重复。

  • 同一个表可以有多个唯一约束。
  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
  • 唯一性约束允许列值为空。 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
    MySQL会给唯一约束的列上默认创建一个唯一索引。
#建表时
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);

create table student(
sid int,
sname varchar(20),
tel char(11) unique,
    #后面的key可写可不写
cardid char(18) unique key
);

CREATE TABLE t_course(
cid INT UNIQUE,
cname VARCHAR(100) UNIQUE,
description VARCHAR(200)
);

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法,表示用户名和密码组合不能重复
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

#建表后
#方式一
alter table 表名称 add unique key(字段列表)
ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);
#方式二
alter table 表名称 modify 字段名 字段类型 unique;
ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;
关于复合唯一约束
create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);
删除唯一约束
  • 添加唯一性约束的列上也会自动创建唯一索引。
  • 删除唯一约束只能通过删除唯一索引的方式删除。
  • 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
  • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和() 中排在第一个的列名相同。也可以自定义唯一性约束名。
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪些约束
#：可以通过 show index from 表名称; 查看表的索引

ALTER TABLE USER DROP INDEX uk_name_pwd;

主键约束

用来唯一标识表中的一行记录。

  • 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
  • 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
  • 主键约束对应着表中的一列或者多列（复合主键）
  • 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
  • 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询 的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。

MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。

添加主键
#建表时
create table 表名称(
    字段名 数据类型 primary key, #列级模式
    字段名 数据类型,
    字段名 数据类型
);
create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    [constraint 约束名] primary key(字段名) #表级模式
);

#建表后
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键
删除主键
#说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。
alter table 表名称 drop primary key;
自增
  1. 一个表最多只能有一个自增长列
  2. 当需要产生唯一标识符或顺序值时，可设置自增长
  3. 自增长列约束的列必须是键列（主键列，唯一键列）
  4. 自增约束的列的数据类型必须是整数类型
  5. 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接 赋值为具体值。
#建表时
create table 表名称(
    字段名 数据类型 primary key auto_increment,
    字段名 数据类型 unique key not null,
    字段名 数据类型 unique key,
    字段名 数据类型 not null default 默认值,
);
create table 表名称(
    字段名 数据类型 default 默认值 ,
    字段名 数据类型 unique key auto_increment,
    字段名 数据类型 not null default 默认值,,
    primary key(字段名)
);
#建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;

#删除
#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

外键约束

限定某个表的某个字段的引用完整性。

在这里插入图片描述

主从表

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是 主表，选课表是从表。

特点
  1. 从表的外键列，必须引用/参考主表的主键或唯一约束的列 为什么？因为被依赖/被参考的值必须是唯一的
  2. 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
  3. 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
  4. 删表时，先删从表（或先删除外键约束），再删除主表
  5. 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖 该记录的数据，然后才可以删除主表的数据
  6. 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
  7. 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类 型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t createtable’database.tablename’(errno: 150)”。
  8. **当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。**但是索引名是外键的约束 名。（根据外键查询效率很高）
  9. 删除外键约束后，必须 手动 删除对应的索引
添加
#建表时
create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段));
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

#建表后
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];
约束等级
  • Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
  • Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子 表的外键列不能为not null
  • No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
  • Restrict方式 ：同no action， 都是立即检查外键约束
  • Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置 成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

#on update cascade on delete cascade
create table dept(
    did int primary key, #部门编号
    dname varchar(50) #部门名称
);
create table emp(
    eid int primary key, #员工编号
    ename varchar(5), #员工姓名
    deptid int, #员工所在的部门
    foreign key (deptid) references dept(did) on update cascade on delete cascade
    #把修改操作设置为级联修改等级，把删除操作也设置为级联删除等级
);
删除
#(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
#（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

在这里插入图片描述

检查约束

检查某个字段的值是否符号xx要求，一般指的是值的范围

create table employee(
    eid int primary key,
    ename varchar(5),
    gender char check ('男' or '女')
);

默认值约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默 认值。

#建表时
create table 表名称(
    字段名 数据类型 primary key,
    字段名 数据类型 unique key not null,
    字段名 数据类型 unique key,
    字段名 数据类型 not null default 默认值,
);
#说明：默认值约束一般不在唯一键和主键列上加

#建表后
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;


#删除
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

15.视图

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查 询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的 价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他 人的查询视图中则不提供这个字段。

视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。

视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句

在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增加、删 除和修改操作时，数据表中的数据会相应地发生变化；反之亦然。

创建

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

#单表视图
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
#查询视图
SELECT * FROM salvu80;

当我们创建好一张视图之后，还可以在它的基础上继续创建视图。

不可更新视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如 下情况时，视图不支持更新操作：

  • 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
  • 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
  • 在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
  • 在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
  • 在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持INSERT、UPDATE、DELETE；
  • 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持 INSERT、UPDATE、DELETE；
  • 视图定义基于一个 不可更新视图 ；
  • 常量视图。

虽然可以更新视图数据，但总的来说，视图作为 虚拟表 ，主要用于 方便查询 ，不建议更新视图的 数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

修改视图

#方式一
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;
#方式二
ALTER VIEW 视图名称
AS
查询语句

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

DROP VIEW IF EXISTS 视图名称;

16.存储过程与存储函数

存储过程

含义：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句 的封装。

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1、没有参数（无参数无返回） 2、仅仅带 IN 类型（有参数无返回） 3、仅仅带 OUT 类型（无参数有返 回） 4、既带 IN 又带 OUT（有参数有返回） 5、带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

使用格式
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
    存储过程体
END

# characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使
用SQL语句的限制。
CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
如果没有设置相关的值，则MySQL默认指定值为DEFINER。
COMMENT 'string' ：注释信息，可以用来描述存储过程。

存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END 编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
结束符修改

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用 DELIMITER改变存储过程的结束符。

DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
    sql语句1;
    sql语句2;
END $

#例一
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
END $
DELIMITER ;
#例二
DELIMITER //
CREATE PROCEDURE avg_employee_salary ()
BEGIN
	SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER ;
#例三
DELIMITER //
CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查看最高薪资'
BEGIN
	SELECT MAX(salary) FROM emps;
END //
DELIMITER ;
输出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO ms FROM emps;
END //
DELIMITER ;

#调用查看
CALL show_min_salary(@ms);
SELECT @ms;
输入
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
	SELECT salary FROM emps WHERE ename = empname;
END //
DELIMITER ;

#调用查看
CALL show_someone_salary('Abel');
调用
CALL 存储过程名(实参列表)

存储函数

RETURNS
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END

DELIMITER //
CREATE FUNCTION email_by_name()#可带参
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
#调用
SELECT email_by_name();

在这里插入图片描述

查看存储过程及存储函数

#1
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
#2
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
#3
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
#：如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

修改存储过程及存储函数

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

在这里插入图片描述

删除存储过程及存储函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

在这里插入图片描述

17.变量

系统变量

系统变量分为全局系统变量（需要添加 global 关键字）以及会话系统变量（需要添加 session 关键 字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认 会话级别。静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系 统变量。

在这里插入图片描述

会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修 改，不会影响其他会话同一个会话系统变量的值。

在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些系 统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些系 统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。

查看
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;
修改

有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、 特征。具体方法：

方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）

方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;

用户变量

定义
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
查看
SELECT @用户变量
#查看某个未声明的变量时，将得到NULL值
局部变量

定义：可以使用 DECLARE 语句定义一个局部变量 作用域：仅仅在定义它的 BEGIN … END 中有效 位置：只能放在 BEGIN … END 中，而且只能放在第一句

BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END

DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL

在这里插入图片描述

18.流程控制

在这里插入图片描述

IF

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

CASE

#1
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
#2
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

LOOP

LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子 句），跳出循环过程。

[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
#其中，loop_label表示LOOP语句的标注名称，该参数可以省略。

DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id +1;
IF id >= 10 THEN LEAVE add_loop;
END IF;
END LOOP add_loop;

WHILE

[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];

REPEAT

REPEAT 循环首先会执行一次循 环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出

[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

LEAVE

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出 程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。

LEAVE 标记名

ITERATE

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序 转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意 思为“再次循环”。

ITERATE label

19.游标

DECLARE cursor_name CURSOR FOR select_statement;

#创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
DECLARE emp_count INT DEFAULT 0; #记录循环个数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标（从游标中获取数据）
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;

20.触发器

触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指 用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生 了，就会 自动 激发触发器执行相应的操作。

创建

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('before_insert');
END //
DELIMITER ;

查看

#查看当前数据库的所有触发器的定义
SHOW TRIGGERS
#查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
#从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;

删除

DROP TRIGGER IF EXISTS 触发器名称;

21.窗口函数

类似于不聚合的group by

#语法
#1
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;

#2 window...写在最后别名声明
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price
DESC);

在这里插入图片描述

按照category_id分区，对每个区域进行排序。如果是group by 则会聚合，而窗口函数不会

在这里插入图片描述

ROW_NUMBER()函数

在这里插入图片描述

RANK()函数

在这里插入图片描述

DENSE_RANK()函数

在这里插入图片描述

PERCENT_RANK()函数

PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算

(rank - 1) / (rows - 1)
#其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。

在这里插入图片描述

rows = 6-1。 rank-/5。

CUME_DIST()函数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

在这里插入图片描述

小于等于价格的比例，比如小于等于1399的只有他自己一个所以比例是1

LAG(expr,n)函数

LAG(expr,n)函数返回当前行的前n行的expr的值。

在这里插入图片描述

LEAD(expr,n)函数

LEAD(expr,n)函数返回当前行的后n行的expr的值

在这里插入图片描述

FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值

在这里插入图片描述

LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值。和上面相反

NTH_VALUE(expr,n)函数

NTH_VALUE(expr,n)函数返回第n个expr的值。

在这里插入图片描述

NTILE(n)函数

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号

在这里插入图片描述

Flandre_rokned
关注
专栏目录
MySQL|基本的SELECT语句
saufmoi的博客
04-12 163
SQLselect查询
SQL时间函数
代码生活，敲出程序人生
08-30 425
昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1 7天 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名) 近30天 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名) 本月 SEL...
SQL赋值为当前日期
liangzhuangdongtou的专栏
10-23 8860
declare @time datetime --定义变量 select @time=getdate() --获取当前日期时间并赋值给变量 UPDATE tablename SET time= @time
SQL获取两个日期之间的天数
shan1774965666的专栏
08-16 1万+
SQL获取两个日期之间的天数
SQL Server无法连接服务器
qq_46104221的博客
02-16 1万+
1、检查系统服务是否正常2、检查SQL Server网络配置是否启用3、双击TCP/IP检查端口是否启用都检查完了，用telnet 127.0.0.1 1433测试一下如需在其他机器上访问本机再做如下检查：1、系统防火墙设置打开1433端口具体步骤：1，控制面板—>防火墙—>打开或关闭防火墙—>关闭防火墙；2，控制面板—>防火墙—>高级设置—>入站规则—>新建规则—>选择端口—>选择特定本地端口；如图所示，添加完后重复上诉步骤直到上图所示端口打开成功。
Pemrograman SQL 03_sql_MSSQLServer_programming_
09-30
在本课程“Pemrograman SQL 03_sql_MSSQLServer_programming_”中，我们将深入探讨SQL编程，特别是与Microsoft SQL Server相关的编程技术。这是一份Bahan Kuliah，即大学课堂的教学材料，专注于SQL的第三次会议，...
03_SQL文件.sql
04-22
https://blog.csdn.net/MixedSignals/article/details/105676917配套使用
oracle_sql_03_view.sql
最新发布
05-29
SQL语句学习汇总
03练习问题_sql_
10-02
在本套"03练习问题_sql_"的视频教程中，我们将会深入学习SQL数据库的基础知识，这是一门针对初学者的课程，旨在帮助你掌握SQL语言的核心概念和操作技巧。SQL，全称Structured Query Language（结构化查询语言），是...
用数据实例解说SQL连接.rar_SQL_Server_SQL__SQL_Server_SQL_
08-09
用数据实例解说SQL内连接/外连接/全连接/交叉连接的运用与区别--表O的内容:select O.orderid 001 U01 item001 002 U02 item002 007 U07 item007 --表U的内容:select U.userID U01 张三 U02 李四 U03 周五 --外连接1:...
SQL2000赋值日期型字段
03-26
用SQL2000语法写的一个游标,用于修改某个日期型字段的日期.
mysql数据库操作之------查的各种小细节，排序，分组，筛选，分页；
L同学的博客
10-03 1938
作业： 一、单表查询 素材： 表名：worker-- 表中字段均为中文，比如 部门号 工资 职工号 参加工作 等 CREATE TABLE worker ( 部门号 int(11) NOT NULL, 职工号 int(11) NOT NULL, 工作时间 date NOT NULL, 工资 float(8,2) NOT NULL, 政治面貌 varchar(10) NOT NULL DEFAULT ‘群众’, 姓名 varchar(20) NOT NULL, 出生日期 date NOT NULL, PRIM
关于在SQL语句中记录添加时间和修改时间的简单用法：invalid default value for create_time
热门推荐
种一棵树最好的时间是十年前，其次是现在。
07-29 5万+
最近看到别人的项目中，数据库中加入了创建时间和修改时间，感觉这个挺实用，就想创建一个demo试试怎么用，查询之后发现修改时间使用的是timestamp字段格式，数据库会根据当前时间，自动处理，不需要在SQL语句中设置，创建时间为datetime，默认值是CURRENT_TIMESTAMP，使用时直接在SQL语句中使用now()来获取当前的时间，但是动手之后，问题就来了！！！！ 首先是在创建数据库...
mysql获取当前日期赋值_一天一点MySQL复习——获取数据库系统时间、变量赋值、变量比较...
weixin_29050829的博客
01-27 1969
一、SQL获取系统时间mysql> select now() from dual;+---------------------+| now() |+---------------------+| 2016-05-24 08:34:12 |+---------------------+1 row in set (0.00 sec)mysql> select c...
PL/SQL 变量赋值
一个情绪猿的脖克...
08-08 2万+
2.6   变量赋值 在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：    variable := expression ;    variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式. 2.6.1  字符及数字运算特点 空值加数字仍是空值：NULL + = NULL 空值加（连接）字符，结果为字符：NULL ||
大数据技术之_19_spark学习_03_spark sql 应用解析 + spark sql 概述、解析 、数据源、实战 + 执行 spark ...
06-06
大数据技术之Spark学习第三篇主要介绍了Spark SQL的应用解析，包括Spark SQL的概述、解析、数据源和实战。Spark SQL是Spark生态系统中的一个组件，它提供了一种基于结构化数据的编程接口，可以使用SQL语言进行数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值