Sql语句总结

SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。

1. 注释

# 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
   注释2 */

2. 创建表

CREATE TABLE mytable (
  # int 类型，不为空，自增
  id INT NOT NULL AUTO_INCREMENT,
  # int 类型，不可为空，默认值为 1，不为空
  col1 INT NOT NULL DEFAULT 1,
  # 变长字符串类型，最长为 45 个字符，可以为空
  col2 VARCHAR(45) NULL,
  # 日期类型，可为空
  col3 DATE NULL,
  # 设置主键为 id
  PRIMARY KEY (`id`));

3. 修改表

添加列

ALTER TABLE mytable ADD col CHAR(20);

删除列

ALTER TABLE mytable DROP COLUMN col;

删除表

DROP TABLE mytable;

4. 插入

普通插入

INSERT INTO mytable(col1, col2) VALUES(val1, val2);

插入检索出来的数据

INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2;

将一个表的内容插入到一个新表

CREATE TABLE newtable AS SELECT * FROM mytable;

5. 更新

UPDATE mytable SET col = val WHERE id = 1;

6. 删除

DELETE FROM mytable WHERE id = 1;

TRUNCATE TABLE 可以清空表，也就是删除所有行。

TRUNCATE TABLE mytable;

7. 查询

DISTINCT:消除重复行

SELECT DISTINCT col1, col2 FROM mytable;

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

SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;

10. 排序
    ASC ：升序（默认）
    DESC ：降序

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

11. 过滤

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

SELECT *
FROM mytable
WHERE col IS NULL;

NULL 与 0、空字符串都不同

12. 通配符

通配符也是用在过滤语句中，但它只能用于文本字段；

% 匹配 >=0 个任意字符；

_ 匹配 ==1 个任意字符；；

[ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

SELECT * FROM mytable WHERE col LIKE '[^AB]%'; 
-- 不以 A 和 B 开头的任意文本
select a from A where name like '刘%' --以刘开头的

13. 计算字段

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

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

SELECT col1 * col2 AS alias FROM mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

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

14. 函数

各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。

只能用于select 和having语句，不能再where中使用

SELECT AVGcol1) AS avg_col FROM mytable;

15. 分组

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

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

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

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

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

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

分组规定:

1. GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
2. 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
3. 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

16. 连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

(连接可以替换子查询，并且比子查询的效率一般会更快。)

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接 又称等值连接

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

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

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

自然连接 把同名列通过等值测试连接起来的，同名列可以有多个。

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

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

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

17. 视图

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

视图具有如下好处：

简化复杂的 SQL 操作，比如复杂的连接；

只使用实际表的一部分数据；

通过只给用户访问视图的权限，保证数据的安全性；

更改数据格式和表示。

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

18. 存储过程

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

使用存储过程的好处：

代码封装，保证了一定的安全性；

代码复用；

由于是预先编译，因此具有很高的性能。

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;

19. 游标

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

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

使用游标的四个步骤：

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

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 ;

20. 权限管理

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

USE mysql; 
SELECT user FROM user;

创建账户

CREATE USER myuser IDENTIFIED BY 'mypassword';

修改账户名

RENAME myuser TO newuser;

查看权限

SHOW GRANTS FOR myuser;

授予权限

GRANT SELECT, INSERT ON mydatabase.* TO myuser;

删除权限

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