SQL

1 CREATE

1.1 创建数据库

CREATE DATABASE

CREATE DATABASE 数据库名;
USE 数据库名;

1.2 创建表

1）全新创建

CREATE TABLE

CREATE TABLE 表名 (
  列名 列类型 列属性,
  ...);

2）从已有表创建

CREATE TABLE … AS

CREATE TABLE newtable AS
SELECT * FROM mytable;

2 DROP

2.1 删除数据库

DROP DATABASE

2.2 删除表

DROP TABLE

3 ALTER

3.1 添加列

ALTER TABLE … ADD

ALTER TABLE 表名 ADD 列名 列类型;

3.2 删除列

ALTER TABLE … DROP COLUMN

ALTER TABLE 表名 DROP COLUMN 列名;

4 INSERT

4.1 普通插入

INSERT INTO 表名(列名, 列名) VALUES(列值, 列值);

4.2 插入检索出来的数据

• INSERT INTO SELECT

Insert into Table2(field1,field2,...) select value1,value2,... from Table1

• SELECT INTO FROM

SELECT vale1, value2 into Table2 from Table1

4.3 避免重复插入

• INSERT IGNORE
  重复忽略
• REPLACE INTO
  除旧迎新
• INSERT … ON DUPLICATE KEY UPDATE
  重复更新

5 DELETE

5.1 条件删除

DELETE FROM

DELETE FROM 表名称 WHERE 列名称 = 值

使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

5.2 清空表

TRUNCATE TABLE

TRUNCATE TABLE 表名称;

6 UPDATE

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

7 SELECT

7.1 DISTINCT

相同值只会出现一次。所有列的值都相同才算相同。

SELECT DISTINCT …;

8 FROM

8.1 JOIN

• 连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
• 连接可以替换子查询，并且比子查询的效率一般会更快。
• 可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

1）INNER JOIN

• 内连接又称等值连接
• 可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

2）NATURAL JOIN

• 自连接可以看成内连接的一种，只是连接的表是自身而已。
• 自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。
• 内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

-- 子查询版本
SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

-- 自连接版本
SELECT e1.name
FROM employee AS e1 NATURAL JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

3）OUTER JOIN

• 外连接保留了没有关联的那些行。
• 分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

8.2 UNION

使用 UNION 来组合两个查询，每个查询必须包含相同的列、表达式和聚集函数。

1）区别

• UNION
  • 去重且排序
• UNION ALL
  • 不去重不排序

从效率上说，UNION ALL 要比 UNION 快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用 UNION ALL。

2）排序

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

可使用子查询避开语法的问题

SELECT * from
(SELECT name FROM student
ORDER BY age limit 0, 1000) t1
UNION (all)
SELECT * from
(SELECT grade FROM student
ORDER BY age limit 0, 1000);

9 WHERE

9.1 操作符

9.2 IN

• IN 是把外表和内表作 hash 连接
• EXISTS 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。
• 如果查询的两个表大小相当，那么用 in 和 exists 差别不大；如果两个表中一个较小一个较大，则子查询表大的用 exists，子查询表小的用 in；

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

9.3 LIKE

Like 只能用于文本字段

• %：匹配 >=0 个任意字符；
• _：匹配 ==1 个任意字符；
• [ ]：匹配集合内的字符， ^ 否定。

不要滥用通配符，通配符位于开头处匹配会非常慢。

9.4 ORDER BY

• ASC 升序（默认），DESC 降序
• 可多列排序，每列指定排序方式

ORDER BY col1 DESC, col2 ASC;

9.5 LIMIT

限制返回的行数。可以有两个参数(起始行，返回行数)。

-- 返回前 5 行：
LIMIT 5;
-- 返回第 3 ~ 5 行：
LIMIT 2, 3;

10 GROUP BY

10.1 GROUP BY

把具有相同的数据值的行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

10.2 HAVING

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

10.3 分组规定：

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
• NULL 的行会单独分为一组；
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

11 函数

11.1 连接合并

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

• AS 别名
• CONCAT() 连接两个字段，不支持"+"
• GROUP_CONCAT() 将多行结果聚合，","连接
• TRIM() 去除首尾空格。

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

11.2 汇总

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以汇总不同的值。

11.3 文本处理

其中，SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

11.4 日期和时间处理

• 日期格式：YYYY-MM-DD
• 时间格式：HH:MM:SS

--加减指定
SELECT now()-interval 60 second;
select date_sub(’1998-01-01 00:00:00′, interval ’1 1:1:1′ day_second);
select now()+interval 24 hour;
select date_add(@dt, interval ’01:15:30′ hour_second);

SELECT date_format(NOW(),'%Y-%m-%d');

11.5 数值处理

12 视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

13 存储过程

13.1 PROCEDURE

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

• 代码封装，保证了一定的安全性；
• 代码复用；
• 由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;

call myprocedure(@ret);
select @ret;

13.2 CURSOR

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

1. 声明游标，这个过程没有实际检索出数据；
2. 打开游标；
3. 取出数据；
4. 关闭游标；

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

14 触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

• INSERT
  INSERT 触发器包含一个名为 NEW 的虚拟表。

  CREATE TRIGGER mytrigger AFTER INSERT ON mytable
  FOR EACH ROW SELECT NEW.col into @result;
  
  SELECT @result; -- 获取结果
  

• DELETE
  DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。
• UPDATE
  UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

15 事务管理

基本术语：

• 事务（transaction）指一组 SQL 语句；
• 回退（rollback）指撤销指定 SQL 语句的过程；
• 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

16 字符集

基本术语：

• 字符集为字母和符号的集合；
• 编码为某个字符集成员的内部表示；
• 校对字符指定如何比较，主要用于排序和分组。

除了给表指定字符集和校对外，也可以给列指定：

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分组时指定校对：

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

17 权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

USE mysql;
SELECT user FROM user;

• 创建账户
  新创建的账户没有任何权限。

  CREATE USER myuser IDENTIFIED BY 'mypassword';
  

• 修改账户名

  RENAME myuser TO newuser;
  

• 删除账户

  DROP USER myuser;
  

• 查看权限

  SHOW GRANTS FOR myuser;
  

• 授予权限
  账户用 username@host 的形式定义，username@% 使用的是默认主机名。

  GRANT SELECT, INSERT ON mydatabase.* TO myuser;
  

• 删除权限
  GRANT 和 REVOKE 可在几个层次上控制访问权限：
  • 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
  • 整个数据库，使用 ON database.*；
  • 特定的表，使用 ON database.table；
  • 特定的列；
  • 特定的存储过程。

  REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
  

• 更改密码
  必须使用 Password() 函数

  SET PASSWROD FOR myuser = Password('new_password');
  

18 条件选择

case 
    when b.customer_type ='L' 
        then 
    when customer_main_type='1' 
        then (select t2.org_name from tz_cus_organ t2 where t2.ecif_id=b.ecif_id)
    else
end