表操作

  1. -- 创建
  2. CREATE TABLE mytable (
  3.   id INT NOT NULL AUTO_INCREMENT,
  4.   col1 INT NOT NULL DEFAULT 1,// 默认值1
  5.   col2 VARCHAR(45) NULL,
  6.   col3 DATE NULL,
  7.   PRIMARY KEY (`id`));
  8. -- 修改表
  9. ALTER TABLE mytable add col CHAR(20); -- 添加列
  10. ALTER TABLE mytable DROP COLUMN col; -- 删除列
  11. DROP TABLE mytable; -- 删除表

增删改查

  • 插入

    1. INSERT INTO mytable(col1, col2) VALUES(val1, val2);
    2. -- 插入检索出来的数据
    3. INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2; 
    4. -- 将一个表的内容插入到一个新表
    5. CREATE TABLE newtable AS SELECT * FROM mytable;

  • 更新

    1. UPDATE mytable SET col = val WHERE id = 1;

  • 删除

    1. DELETE FROM mytable WHERE id = 1;
    2. -- 可以清空表，也就是删除所有行。
    3. TRUNCATE TABLE mytable;

  • 查询

    • DISTINCT 所有列的值都相同的值只会出现一次。它作用于所有列

      1. SELECT DISTINCT col1, col2 FROM mytable;

    • limit 限制返回的行数。有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数

      1. SELECT * FROM mytable LIMIT 5; -- 返回前 5 行
      2. SELECT * FROM mytable LIMIT 0, 5; -- 返回第 1 ~ 5 行
      3. SELECT * FROM mytable LIMIT 2, 3; -- 返回第 3 ~ 5 行

    • case when

      1. SELECT s.s_sex,CASE s.s_sex WHEN '1' THEN '男' ELSE '女' END as sex FROM t_b_student s;

    • 排序 ASC ：升序（默认）, DESC ：降序

      1. -- 可以按多个列进行排序，并且为每个列指定不同的排序方式
      2. SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;

    • 过滤 WHERE子句

      1. SELECT * FROM mytable WHERE col IS NULL;
      2. -- 不以 A 和 B 开头的任意文本
      3. SELECT * FROM mytable WHERE col LIKE '[^AB]%';
操作符 说明
= 等于
< 小于
> 大于
<> != 不等于
<= !> 小于等于
>= !< 大于等于
BETWEEN 在两个值之间
IS NULL 为 NULL 值
AND 和 OR 连接多个过滤条件
IN 操作符用于匹配一组值
NOT 否定一个条件
% 匹配 >=0 个任意字符
_ 匹配 ==1 个任意字符
[ ] 可以匹配集合内的字符

  • 分组 GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

    • GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前
    • 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出
    • NULL 的行会单独分为一组
    • 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型
      1. SELECT col, COUNT(*) AS num FROM mytable GROUP BY col HAVING num >= 2;

  • UNION

    • 每个查询必须包含相同的列、表达式和聚集函数
    • 默认会去除相同行，如果需要保留相同行，使用 UNION ALL
    • 只能包含一个 ORDER BY 子句，并且必须位于语句的最后
      1. SELECT col FROM mytable WHERE col = 1
      2. UNION
      3. SELECT col FROM mytable WHERE col =2;

  • 行转列

PIVOT 后跟一个聚合函数来拿到结果，FOR 后面跟的科目是我们要转换的列，这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。
SQL - 图1SQL - 图2

  1. SELECT *
  2. FROM student
  3. PIVOT (
  4.     SUM(score) FOR subject IN (语文, 数学, 英语)
  5. )
  • 列转行

SQL - 图3SQL - 图4

  1. SELECT *
  2. FROM student1
  3. UNPIVOT (
  4.     score FOR subject IN ("语文","数学","英语")
  5. )

连接

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

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

  1. SELECT A.value, B.value FROM tablea AS A INNER JOIN tableb AS B ON A.key = B.key;
  2. -- 可以不明确使用 INNER JOIN，而使用WHERE 中将两个表中要连接的列用等值方法连接起来。
  3. SELECT A.value, B.value FROM tablea AS A, tableb AS B WHERE A.key = B.key;

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

  1. -- 一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
  2. -- 子查询
  3. SELECT name FROM employee WHERE department = 
  4. (SELECT department FROM employee WHERE name = "Jim");
  5. -- 自连接
  6. SELECT e1.name FROM employee AS e1 INNER JOIN employee AS e2
  7. ON e1.department = e2.department AND e2.name = "Jim";

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。
内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

  1. SELECT A.value, B.value FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

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

  1. -- 检索所有顾客的订单信息，包括还没有订单信息的顾客。
  2. SELECT Customers.cust_id, Orders.order_num
  3. FROM Customers LEFT OUTER JOIN Orders
  4. ON Customers.cust_id = Orders.cust_id;

customers 表

cust_id cust_name
1 a
2 b
3 c

orders 表

order_id cust_id
1 1
2 1
3 3
4 3

结果

cust_id cust_name order_id
1 a 1
1 a 2
3 c 3
3 c 4
2 b Null

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。
对视图的操作和对普通表的操作一样。
视图具有如下好处：

  • 简化复杂的 SQL 操作，比如复杂的连接；
  • 只使用实际表的一部分数据；
  • 通过只给用户访问视图的权限，保证数据的安全性；
  • 更改数据格式和表示。
  1. CREATE VIEW myview AS
  2. SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
  3. FROM mytable WHERE col5 = val;

存储过程

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

使用存储过程的好处：

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

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
包含 in、out 和 inout 三种参数。
给变量赋值都需要用 select into 语句。
每次只能给一个变量赋值，不支持集合的操作。

  1. delimiter //
  2. create procedure myprocedure( out ret int )
  3.     begin
  4.         declare y int;
  5.         select sum(col1)
  6.         from mytable
  7.         into y;
  8.         select y*y into ret;
  9.     end //
  10. delimiter ;
  12. call myprocedure(@ret);
  13. select @ret;

游标

在存储过程中使用游标可以对一个结果集进行移动遍历。
游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

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

  4. 关闭游标；

    1. delimiter //
    2. create procedure myprocedure(out ret int)
    3.  begin
    4.      declare done boolean default 0;
    6.      declare mycursor cursor for
    7.      select col1 from mytable;
    8.      # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
    9.      declare continue handler for sqlstate '02000' set done = 1;
    11.      open mycursor;
    13.      repeat
    14.          fetch mycursor into ret;
    15.          select ret;
    16.      until done end repeat;
    18.      close mycursor;
    19.  end //
    20. delimiter ;

触发器

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

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

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

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

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

事务管理

基本术语：

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

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

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

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

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

  1. START TRANSACTION
  2. // ...
  3. SAVEPOINT delete1
  4. // ...
  5. ROLLBACK TO delete1
  6. // ...
  7. COMMIT

权限管理

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

  1. USE mysql;
  2. SELECT user FROM user;
  1. -- 创建账户，新创建的账户没有任何权限。
  2. CREATE USER myuser IDENTIFIED BY 'mypassword';
  3. -- 修改账户名
  4. RENAME myuser TO newuser;
  5. -- 删除账户
  6. DROP USER myuser;
  7. -- 查看权限
  8. SHOW GRANTS FOR myuser;
  9. -- 授予权限
  10. -- 账户用 username@host  的形式定义，username@% 使用的是默认主机名。
  11. GRANT SELECT, INSERT ON mydatabase.* TO myuser;
  12. -- 更改密码，必须使用 Password() 函数
  13. SET PASSWROD FOR myuser = Password('new_password');

删除权限
GRANT 和 REVOKE 可在几个层次上控制访问权限：

  • 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
  • 整个数据库，使用 ON database.*；
  • 特定的表，使用 ON database.table；
  • 特定的列；
  • 特定的存储过程。
    1. REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;