delete 和 truncate的区别

delete from table和truncate table 都能删除表中的所有数据。如果主键自增，那么

• delete数据之后自动增长从断点开始
• truncate数据之后自动增长从默认起始值开始（也可以理解为，直接删除表再重新创建）

运算符

算数运算符：

通配符：

通配符%：

ABC%，选取开头为ABC的字符串

%ABC，选取结尾为ABC的字符串

%ABC%，字符串中必须包含ABC

通配符_：

ABC_：选取最后一个字符之前三个字符为ABC的字符串

_ABC：选取第一个字符后面三个字符为ABC的字符串

_A\_B：选取相邻四个字符中第二个和第三个字符为A和B的字符串

通配符[charlist]：

[ABC]%：获取以A，B或者C开头的字符串

[!ABC]%：获取不以A，B或者C开头的字符串

比较运算符：

逻辑运算符：

位运算符：

排序

DESC：降序

ASC：升序

例如：一张表（EMP）里有员工编号（ENO）和薪资（PRICE）

按照PRICE降序排序：

 SELECT * FROM EMP ORDER BY PRICE DESC;

先按照PRICE降序排序，当PRICE相等的时候，员工编号大的排在前面：

SELECT * FROM EMP ORDER BY PRICE DESC,ENO ASC;

当PRICE相等的时候，后面的字段排序才会执行

分组查询

分组之后的条件筛选-having

分组之后对分组的结果进行筛选必须使用having，不能使用where

格式：

SELECT 字段1,字段2 FROM 表名 WHERE ... GROUP BY 分组字段 HAVING 分组条件

注：select的字段除了已经分组的字段和聚合函数，不能查询其他字段，WHERE筛选不能使用聚合函数，但是HAVING可以使用

分页查询

例：查询表中前五条记录

SELECT * FROM TABLES LIMIT 5

从第四条开始，查询五条记录

SELECT * FROM TABLES LIMIT 3,5

分页查询，page代表页数，size代表每页的数量，page的初始值为0，如果规定第一页的page为1，那么就应该减去1，如果规定第一页page为0，则不需要减一

SELECT * FROM TABLES LIMIT (PAGE-1)*SIZE,SIZE

insert_into_select语句

将一个表中的所有数据或者部分数据插入到另外一个表中，将TABLE2中的NAME和AGE，插入到TABLE1中，SELECT后面可以使用分组，聚合函数等等，前提是查询的值必须与INSERT的字段相同

INSERT INTO TABLE1(NAME,AGE) SELECT NAME,AGE FROM TABLE2;

多表联合子查询

子查询的结果如果为多个可以当做一个临时表，如果为单个也可以当做一个值

或者：(sql练习 - z’blog (sikns.com))

子查询关键字：

ALL：

SELECT ... FROM ... WHERE C > ALL(1,2,3,4...)
等于
SELECT ... FROM ... WHERE C > 1 AND C > 2 AND C > 3 AND C > 4

ANY / SOME

SELECT ... FROM ... WHERE C > ANY(1,2,3,4...)
等于
SELECT ... FROM ... WHERE C > 1 OR C > 2 OR C > 3 OR C > 4

EXITS：

SELECT ... FROM ... WHERE EXISTS(查询语句)

聚合函数

GROUP_CONCAT：

将EMP表中的ENAME放入一行
SELECT GROUP_CONCAT(ENAME) FROM EMP
将EMP表中的ENAME放入一行，并用-分割
SELECT GROUP_CONCAT(ENAME SEPARATOR '-') FROM EMP
将EMP表种的ENAME放入一行，并用-分割，并且按照EMPNO员工号排序
SELECT GROUP_CONCAT(ENAME ORDER BY EMPNO DESC SEPARATOR '-') FROM EMP
将各个部门的员工分别放入一行
SELECT GROUP_CONCAT(ENAME) FROM EMP E GROUP BY E.DEPTNO

ABS(X)：返回X的绝对值

CEIL(X)：返回大于或等于X的最小整数（向上取整）

FLOOR(X)：返回小于或等于X的最大整数（向下取整）

GREATEST(X1,X2,X3,X4,....)：返回列表中的最大值

LEAST(X1,X2,X3,X4,....)：返回列表中的最小值

ROUND(X,N)：将小数按照指定位数四舍五入

TRUNCATE(X,N)：直接截取小数，不进行四舍五入

视图

介绍：

• 视图（view）是一个虚拟表，并非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获得数据集，并可以将其当作表来使用
• 数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中
• 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据源是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变

作用：

• 简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
• 安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图

创建视图：

查询EMP中的ENAME,JOB字段作为VIEW_EMP视图的数据

-- 创建视图
CREATE OR REPLACE VIEW VIEW_EMP
AS
SELECT ENAME,JOB FROM EMP;

查询视图：

查询视图与普通表一致

修改视图：

ALTER VIEW VIEW_EMP AS SELECT 
AS
SELECT ENO,ENAME,JOB FROM EMP;

更新视图：

UPDATE VIEW_EMP SET ENAME = '张三' WHERE ENAME='blake'
DELETE FROM VIEW_EMP WHERE ENAME = 'clark'
INSERT INTO VIEW_EMP (ENO,ENAME,JOB) VALUES (100,'李四',banzhuan);

对视图中数据的更新操作实际上就是直接操作原表的数据

Mysql存储过程

什么是存储过程：

• 存储过程就是一组SQL语句集，可以实现一些比较复杂的逻辑功能，类似于其他语言中的函数，方法，简单来说，就是可以通过调用方法，执行方法里面的sql语句
• 存储过程就是数据库SQL语言层面的代码封装与重用

特性：

• 有输入输出函数，可以声明变量，有if/else，case，while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能
• 函数的普遍特性：模块化，封装，代码复用
• 速度快，只有首次需经过编译和优化步骤，后续被调用可以直接执行

创建存储过程：

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE FUNC1()
BEGIN
    SELECT EMPNO,ENAME FROM EMP;
END $$
DELIMITER;
--调用存储过程
CALL FUNC1();

变量的定义和赋值：

DELIMITER $$
CREATE PROCEDURE FUNC2()
BEGIN
    DECLARE VAR_NAME VARCHAR(20) DEFAULT 'AAA';
    SET VAR_NAME = 'BBB';
    SELECT VAR_NAME;
END $$
DELIMITER;

CALL FUNC2();

将查询的结果赋值给变量：

DELIMITER $$
CREATE PROCEDURE FUNC3()
BEGIN
    DECLARE VAR_NAME VARCHAR(20);
    SELECT ENAME INTO VAR_NAME FROM EMP WHERE EMPNO = 7369;
    SELECT VAR_NAME;
END $$
DELIMITER;

CALL FUNC3();

用户变量：

用户变量在存储过程中和存储过程外都可以使用

DELIMITER $$
CREATE PROCEDURE FUNC4()
BEGIN
    SET @VAR_NAME = 'ZHANGSAN';
END $$
DELIMITER;

CALL FUNC4();
SELECT @VAR_NAME;

存储过程传参-IN：

DELIMITER $$
# 定义传入参数，格式：IN 参数名 参数类型,...
CREATE PROCEDURE FUNC5(IN EMPNO INT)
BEGIN
    SELECT * FROM EMP E WHERE E.EMPNO = EMPNO;
END $$
DELIMITER;
# 调用传入参数
CALL FUNC5(7369);

存储过程传参-OUT：

DELIMITER $$
# IN为传入参数,OUT为返回参数
CREATE PROCEDURE FUNC6(IN EMPNO INT,OUT OUT_NAME VARCHAR(50),OUT OUT_DEPTNO INT)
BEGIN
# OUT_NAME,OUT_DEPTNO接收查询的ENAME,DEPTNO
    SELECT ENAME,DEPTNO INTO OUT_NAME,OUT_DEPTNO FROM EMP E WHERE E.EMPNO = EMPNO;
END $$
DELIMITER;
# 定义用户变量接收
CALL FUNC6(7369,@STATIC_OUT_NAME,@STATIC_OUT_DEPTNO);
# 查询用户变量
SELECT @STATIC_OUT_NAME,@STATIC_OUT_DEPTNO