SQL

CREATE TABLE

至少要包含表名和列的定义，不能存在同名表，所以可以使用 IF NOT EXISTS 判断
字段默认为 NULL，如果为 NOT NULL ，插入的时候不能省略该字段名
PRIMARY KEY可以有多个列组成，使用逗号分隔开，设置为主键的列只能为NOT NULL

• 表中只能拥有一个AUTO_INCREMENT的列，且必须被设置为PRIMARY KEY
• 使用SELECT last_insert_id()获取最后一个AUTO_INCREMENT的值

foreign key不能跨引擎

CREATE TABLE  IF NOT EXISTS Customers
(
    cust_id       int        NOT NULL AUTO_INCREMENT,
  cust_key      int        NOT NULL,
  cust_name     char(50)   NOT NULL DEFAULT "ANONYMOUS",
  PRIMARY KEY  (cust_id, cust_key)
) ENGINE = InnoDB;

主要引擎

• InnoDB 支持事务管理，不支持全文搜索
• MyISAM 性能极高，支持全文索引，不支持事务管理
• MEMORY 功能等同于MyISAM，存储在内存中

ALTER/DROP/RENAME TABLE

可以更改表结构，设置外键等

ALTER TABLE vendors
ADD vender_phone CHAR(50);
ALTER TABLE vendors
DROP vend_phone;
RENAME TABLE customersasdasd TO customers,
                   vendorsasdasd   TO vendors;

TYPES

字符串

数值

除了BIT/BOOLEAN外，都可以使用UNSIGNED关键字

日期

二进制

INSERT

插入行，行的一部分，多行，或者某些SELECT查询出的结果
默认不指定列名的情况下表示所有列，可以在表名后面的括号内指定特定的某些列名

• 只能省略 允许NULL的列或者设置了默认值的列，否则省略后会报错
• VALUES内顺序要与指定的列名顺序一致
• 在不指定列名的情况下，对于自增的列(primary key)，不能省略，指定为NULL，mysql会自动赋值，当然也可以指定一个尚不存在的key
• 明确指定列的一个好处是即使表结构发生了改变，sql代码一般仍有效

插入多行只需要将多行用逗号隔开即可，多行插入比多次INSERT快
插入由SELECT产生的数据的时候，不在乎SELECT的结果的列名/字段名，只是按照对应的顺序进行插入
MYSQL服务端允许以任意的顺序执行客户端的请求，INSERT, UPDATE, DELETE运行加上LOW_PRIORITY 声明该请求优先级低

INSERT INTO Customers
    VALUES("asd", NULL, 15);
INSERT INTO Customers(name, age, address)
    VALUES('asdas', 11, 'asdasd');
INSERT INTO Customers(name, age, address)
    VALUES('asdas', 11, 'asdasd'),
              ('asdas', 11, 'asdasd'),
        ('asdas', 11, 'asdasd');
INSERT INTO Customers(name, age, address)
SELECT cust_name, cust_age, cast_addr
FROM newcustomer;
WHERE Date(date) > Date('2000-01-01');
INSERT LOW_PRIORITY INTO Customers VALUES(1, 2, 3);

UPDATE

更新某些行的部分字段
不需要在表名后面指定列名，而是在在SET中指定多个 column_name = value pair
可以使用SELECT语句查询出来的数据更新列数据

UPDATE Customers
SET cust_email = 'asdasd@qq.com',
        cust_addr = "asdas"
WHERE cust_id = 1005;

DELETE

删除某些行

• DELETE是逐行删除，要删除所有行，使用 TRUNCATE TABLE 速度更快，其内部为删除整个表，并重新创建一个

  DELETE FROM Customers
  WHERE cust_id < 1000;

SELECT

DISTINCT只能作用于列名/字段，不能用于计算/表达式

foreign key: 某一个表中的一列，包含了另一个表的主键值

别名

AS alias_name

可以对列/字段和表使用别名

计算字段

可以经过函数调用，算数计算(+, -, *, /)将多个列的数据转换成新的列/字段

计算字段默认是匿名的，可以使用 AS 设置别名alias

SELECT Concat(name, '(', age, ')') AS name_age
FROM vendors
ORDER BY name

函数

移植性不高
可用于计算字段，WHERE子语句中

• 文本处理
• 日期和时间
• 数值处理
• Aggregate 将某一列/字段的数据转换为一个数值
  
  • AVG/COUNT/MAX/MIN/SUM
    • COUNT(*) 统计行数，而 COUNT(column) 仅仅统计非NULL的行数
  • 可以在列/字段前使用 DISTINCT 关键字，表示先进行DISTINCT操作

SELECT AVG(DISTINCT price) AS avg_cost,
             COUNT(*) AS total_num
FROM products
WHERE vent_id = 1003;

子查询

执行顺序为从内到外

• 用于WHERE语句中
• 作为计算字段
  ```sql SELECT name, contact FROM customers WHERE id IN (SELECT id

         FROM orders
         WHERE order_num IN (SELECT order_num
                             FROM order_items
                             WHERE prod_id = 'TNT2'));

SELECT name, state, (SELECT COUNT(*) FROM orders WHERE orders.id = customers.id) AS num_orders FROM customers ORDER BY cust_name; // 会对每一个customers.id调用一次子查询

<a name="pZGV6"></a>
### GROUP BY
出现在WHERE之后，ORDER BY之前<br />除了Aggregate语句外，SELECT语句中的每个列/字段(允许是表达式)都必须出现在GROUP BY中
-  如果SELECT语句中的字段是表达式，在GROUP中同样要以表达式的形式出现，而不能使用别名
NULL会被单独作为一组
```sql
SELECT num * price AS total,
       COUNT(*) AS nums
FROM orders
WHERE Date(date) = "2020-01-01"
GROUP BY num * price
ORDER BY total DESC;

HAVING

对分组进行过滤，WHERE是用于过滤行，在分组前进行，而HAVING用于过滤分组，在分组后进行，且HAVING后面可以可以接聚合函数
HAVING子句中的条件和WHERE中一致

SELECT num * price AS total,
       COUNT(*) AS nums
FROM orders
WHERE Date(date) = "2020-01-01"
GROUP BY num * price
HAVING nums > 1000
ORDER BY total DESC
LIMIT 5 offset 0; # or LIMIT 0,5;  or LIMIT 5;

JOIN

WHERE指示了联结条件，如果没有WHERE语句，那么产生的结果为笛卡尔积，包含m * n行

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name
// 使用联结替代子查询
SELECT name, contact
FROM customers, orders, orderitems
WHERE customers.id = orders.id 
  AND orders.order_num = order_items.order_num 
  AND order_itms.prod_id = 'TNT2';

内联结

上述的联结基于两个表中列的相等测试，称为等值联结，其实是内联结
推荐使用 INNER JOIN 的语法

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

自联结

可用于代替从相同表中检索数据时使用的子查询语句

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');
// 替换为自联结
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.prod_id = 'DTNTR'
    AND p1.vend_id = p2.vend_id;

自然联结

是指的联结后的表不会出现相同的列

外连接

内连接和自联结都是返回在两个表中通过某一列相互关联的行，外连接还会返回没有关联的行
取决于没有关联的行取左边的表还是右边的表，分别2种

• LEFT OUTER JOIN
• RIGHT OUTHER JOIN

SELECT cust_id, order_num
FROM customers LEFT OUTER JOIN orders
    ON customers.cust_id = orders.cust_id;
// 该sql语句还会返回没有orders的customers

分组

可以在联结的同时使用分组

SELECT c.cust_id, c.cust_name, COUNT(orders.order_num) AS num_ord
FROM customers AS c INNER JOIN orders
GROUP BY c.cust_id
ORDER BY num_ord
LIMIT 5;

UNION

将多个SELECT查询(可以是同一个表，也可以是不同的表)的结果取并集起来，等价于多个WHERE条件(但性能不一样)
UNION的多个查询必须包含相同的列/字段，但是他们的顺序可以不一致，且数据类型要兼容(可以隐式转换)
UNION的默认行为和WHERE一样，结果不会有重复行，使用UNION ALL可以取消这一行为
ORDER BY只能出现在最后一条SELECT语句后面，且是对UNION后的结果进行排序，而不是最后一个SELECT返回的结果

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
WHERE  vend_id IN (1001, 1002);
// 等价于
SELECT vend_id, prod_id, prod_price
FROM prioducts
WHERE prod_price <= 5 OR vend_id IN (1001, 1002);

全文本搜索

搜索的单位是词，而不是substring

MyISAM支持而InnoDB不支持
有些词会被忽略，且不支持不含词分隔符的语言(日语，汉语)

FULLTEXT

需要指定进行索引的列，之后MYSQL会自动维护该索引
使用的时候在WHERE语句使用Match(column)指定列，Against(‘pattern’)指定搜索表达式

• 除非使用BINARY模式，否则搜索的时候不区分大小写
• pattern中可以包含多个词，以空格隔开
  

全文搜索返回的结果是按照匹配等级进行排序的

• Match() Again()返回的结果可以作为计算字段，返回的是匹配好坏等级
  ```sql CREATE TABLE productnotes ( node_id int NOT NULL AUTO_INCREMENT, note_text text NULL, note_abstract text NULL, PRIMARY_KEY(note_id), FULLTEXT(note_text, note_abstract) ) ENGINE = MyISAM;

SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit’);

SELECT note_text, Match(note_text) Against(‘rabbit’) AS rank FROM productnotes; // 没有where子句，会返回所有行

<a name="keibe"></a>
##### WITH QUERY EXPANSION
会进行两次全文搜索，第二次使用第一次搜索到的行中的部分单词进行搜索<br />
```sql
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);

Boolean Mode

不需要FULLTEXT索引也可以使用，性能非常低

WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE); // 同时包含rabbit bait词
WHERE Match(note_text) Against('+rabbit -bait' IN BOOLEAN MODE); // 不能包含bait且包含rabbit词
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE); // 包含两个中至少一个词
WHERE Match(note_text) Against('-bait*' IN BOOLEAN MODE); // 不能包含任意以bait开头的词

VIEW

不包含任何数据，类似于虚拟的表，一般用于检索而不是更改
视图不能与其他视图/表重名，可以嵌套视图
并不是所有VIEW都可以进行UPDATE/INSERT/DELETE
视图可以和表一起使用，比如连接一个视图和一个表

• 查询视图时使用ORDER BY子句的时候，如果视图内部的SELECT语句也有ORDER BY， 则视图内部的ORDER BY将会被该ORDERBY所覆盖
• 查询视图的时候如果使用WHERE子句查询，会将WHERE的条件合并到VIEW内已有的WHERE语句内

基本操作

• CREATE VIEW/ CREATE OR REPLATE VIEW 后者可以用于已存在同名VIEW的时候自动覆盖
  
• SHOW CREATE VIEW view_name 展示VIEW的语句
  
• DROP VIEW view_name ```sql CREATE VIEW cust_view AS SELECT name, price FROM Customers;

SELECT cust_name FROM cust_view WHERE price > 100;

<a name="LbQC7"></a>
### Procedure
类似于函数，允许指定输入参数，输出参数，函数内可以定义变量，使用循环，判断语句，调用其他procedure。<br />SHOW CREATE PROCEDURE pro_name 获取函数的定义<br />
```sql
CREATE PROCEDURE total(             -- 定义函数
    IN onumber INT,                   -- 输入
  OUT ototal INT                    -- 输出
) COMMENT 'asdasdasdas'
BEGIN
    DECLARE num DECIMAL(8, 2) DEFAULT 6.5; - 指定默认值
  DECLARE total DOUBLE;
  SELECT SUM(price * count)
  FROM orders
  WHERE order_num = onumber
  INTO ototal;
  IF bool_variable THEN
      SELECT ..... INTO total;
  ELSE IF
      ....
  ELSE
      ....
  END IF;
  SELECT total INTO ototal;
END;
CALL total(1000, @total_var);       -- 调用函数
SELECT @total_var;                  -- 获取输出变量

Cursor

允许一行一行/多行的读

Trigger

可以对INSERT/UPDATE/DELETE定义AFTER或者BEFORE的触发器
INERT/UPDATE触发器中都可以使用NEW这个虚拟表，获取要更新的数据
UPDATE/DELETE触发器中都可以使用OLD这个虚拟表，获取之前的数据
BEFORE通常用于数据的验证和处理，而AFTER通常做一些善后的统计，备份工作

CREATE TRIGGER trigger_name AFTER INSERT ON table_name
    FOR EACH ROW SELECT NEW.order_name;
CREATE TRIGGER update_tb BEFORE UPDATE ON table_name
    FOR EACH ROW SET NEW.name = Upper(NEW.name);
DROP TRIGGER trigger_name;

Transaction

Priviledges

账户信息存储于mysql database中，一般位于user中
user的权限针对的是user和host_name的组合user@host_name，不选择host_name则为user@%
GRANT/REVOKE时，用户必须存在，但是涉及的databse/table可以不存在

CREATE USER user_name IDENTIFIED BY 'password';   // 默认无任何权限
SET PASSWORD FOR user_name = Password('password');
SET PASSWORD = Password('password');              // 更改自己密码
RENAME USER old_name TO new_name;
DROP USER user_name;
SHOW GRANTS FOR user_name;
SHOW GRANTS FOR user_name@host_name;
GRANT SELECT ON dbname.* TO user_name;
GRANT SELECT, INSERT ON dbname.* TO user_name;
REVOKE SELECT ON dbname.* FROM user_name;

日志

位于data目录下

错误日志，hostname.err
查询日志，hostname.log 包括了所有mysql活动

二进制日志，hostname-bin 包括了更新过数据的所有日志

缓慢查询日志，hostname-slow.log 记录了执行缓慢的任何查询，可以用于后续的优化

索引

• 优点：除了字符串搜索相关的索引外，都是基于btree，搜索是可以二分查找加速
• 代价：索引是二进制文件占空间，DML操作会更新索引，影响性能
• 场景：
  • 在频繁使用的查询字段上建索引，比如WHERE语句中使用的
  • 也不能是重复性太高的字段，比如GENDER字段
  • 经常更新的字段不应该作为索引，原因是改动一次就得更新索引
• 主键索引，索引值必须唯一(不允许值为NULL)，一般建表时创建
• 普通索引，允许值重复
• 唯一索引，索引值必须唯一(允许值为NULL)，如果是组合索引，则列组合必须唯一
• 组合索引，涉及多列，比较时按前缀比较
• 全文索引，用于字符串搜索匹配 ```sql

  主键索引

  CREATE TABLE tb (id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))

  还能改变主键索引

普通索引

CREATE TABLE tb (name VARCHAR(32), INDEX tb_index(name(11))) CREATE INDEX tb_index on tb(name(11)) # 字符串可以指定索引使用的前缀长度 ALTER TABLE tb ADD INDEX tb_index(name(11))

唯一索引

INDEX -> UNIQUE INDEX

组合索引

CREATE TABLE tb (name VARCHAR(32), id int(12), INDEX tb_index(name, id)) CREATE INDEX tb_index on tb(name, id) ALTER TABLE tb ADD INDEX tb_index(name, id)

全文索引

INDEX -> FULLTEXT

```