SQL

SELECT

查询列表可以是：表中的字段、常量值、表达式、函数
查询结果是一个虚拟的表格

select name from student;     //检索单个列
select * from student;        //检索整个表格
select name, id from student; //选择多行

select distinct city from student; //只返回city中不同的行
select name from student limit 5;  //返回从头开始的5行
select name from student limit 5,3;//返回从第5行开始的3行

select student.name from student;  //使用完全限定的表名

ORDER

select name from student order by name;   //以name列的字母排序
select id, name from student order by name, id desc; //选择多列，首先按照name，再按照id排序

select name from student order by name desc;   //以name列的字母降序排列
select id, name from student order by name desc, id; // 对name列降序，id升序

select id, name from student order by name desc, id
limit 1;//配合limit筛选最大值和最小值

降序 desc，升序asc。

order by一般是最后的，除了limit

WHERE

select * from student where city = "杭州"; //注意杭州是字符串需要加引号

select * from student where id > 5; //检测多个值

select * from student where city != '杭州';

select * from student where city is NULL;

Where子句的位置应该在order之前，否则会出错。

select * from student where city != '河南' and city != '安徽';

select * from student where city = '河南' or city = '安徽';

select * from student where city in ('河南','安徽'); # 列表的值统一或者兼容

select * from student where city not in ('河南','安徽');

select * from student where id between 100 and 200;

and or同时使用时，加括号突出优先级。
列表的值统一或者兼容

select * from student where city like '杭%'; #匹配前面

select * from student where address like '%隔壁'; #匹配后面

select * from student where address like '___隔壁';

select * from student where address like '_\__隔壁';

通过%匹配不限字符数，通过_匹配，限定单个字符数。
为了避免尾空格，应该在末尾加一个%，避免其影响。
escape $或者\$转义符号。
like不能和NULL匹配。
通配符的效率低，能使用其他的就不要使用通配符，尽量不要从搜索模式的开头使用，（我猜从头开始搜索，所以效率低）。

select last_name from employee where commission_pct IS / IS NOT NULL;

= > < 不能判断NULL值
IS只能判断NULL
IFNULL(commission, 0)如果奖金为NULL，用0替代

AND OR IN NOT IN

IS NULL

expression IS NULL
expression IS NOT NULL

EXISTS

CONCAT

 select concat(city,' ',address) from student;

RTrim和LTrim处理所选列的左右空格。

mysql中的+号
- 两个操作数都为数值型，则做加法运算
- 其中一个为字符型，试图将字符型转换为数值型，转换失败的话，字符型数值变成0进行运算
有一方为null，结果就是null，所以实际拼接要进行判断，ISNULL(exp1,exp2)。

AS

select concat(city,' ',RTrim(address)) as newtitle from student;

as给拼接字段的列起一个新的名字。

LIMIT

LIMIT row_count OFFSET offset;

一般放在最后
一般结合orderby，输出指定行数的数据

DISTINCT

SELECT DISTINCT department_id FROM employees;

可以针对多个字段
当 DISTINCT 遇到 NULL 值时，只保留一个 NULL 值。因为 DISTINCT 认为所有的 NULL 值都是相同的，这与字段的类型无关。

LIKE

正则表达式是用来匹配文本的特殊的串（字符集合），用正则表达式语言来建立。

所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。

MySQL仅支持多数正则表达式实现的一个很小的子集。

select * from student where city regexp '州';
select * from student where city like '州';
# 对比like和正则表达式，regexp匹配的是包含关系
select * from student where city regexp '10|20';
select * from student where city regexp '河|安|州';
select * from student where city regexp [123]; # '1|2|3'

select * from student where city regexp '[1-5] Ton'; # 匹配 1 Ton, 2 Ton,3 Ton...
select * from student where city regexp '[a-c] Ton'; # 匹配 a Ton, b Ton,c Ton...

匹配特殊字符

select * from student where city regexp '\\.'
select * from student where city regexp '\\f'
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
\\ 转义符号

匹配字符类

[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和制表（同[\\t]）
[:cntr1:] ASCII控制字符（ASCII到31和127）
[:digit:] 任意数字（同[0-9]）
[:graph:] 与[：print:]相同，但不包括空格
[:lower:  任意小写字母（同[a-z]）
[:print:] 任意可打印字符
[:punct:] 既不在[：alnum:]又不在[：cntr1:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\\fl\nl\rlltllv]）
[:upper:] 任意大写字母（同[A-Z]）
[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）

USING

等值查询，必须满足关键字相同

JOIN

用于将数据库中的两个表或者多个表组合起来。

交叉连接：全匹配，m * n，做型号和颜色匹配
内连接：有条件的，不满足连接条件的不会显示
左连接：左表匹配右表，如果匹配成功则将左表和右表的行组合成新的数据行返回；如果匹配不成功则将左表的行和 NULL 值组合成新的数据行返回
右连接：与左连接相反
自连接：要给相同表起不同的名字，避免冲突
自然连接：让数据库自己筛选

一般用左连接，显示一些右表NULL值的情况

UNION

两端查询中间加一个UNION关键词，合并两次结果值。

两次查询的列数、列顺序必须相同，否则mysql不知道如何合并。
UNION 运算包括 UNION DISTINCT 和 UNION ALL 两种算法，其中 UNION DISTINCT 可以简写为 UNION。
多个表UNION，实际上是合成一个，再与下一个运算得到
当需要对 UNION 运算的结果进行排序时，最需要在 SQL 语句的最后添加 ORDER BY 子句。
参与 UNION 运算的结果集只要列数一样就可以。返回结果集的列名采用第一个结果集的列名。

INSERT

INSERT 语句用于将一行或者多行数据插入到数据表中。

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_1, value_2, ...);

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...)
       ...;

如果想忽略插入过程中一些错误行，可使用 IGNORE 标识符。
插入分层行，多个表之间的插入问题。
复制新表时，主键和自动增减的属性会被忽略。

insert into invoices_archive
select *
from invoices
where payment_date IS NOT NULL;

# 直接筛选好合适的数据后，新建表格
create table invoices_archive as
select 
        c.name,
    ia.payment_date
from invoices_archive ia
join clients c
        using (client_id)

UPDATE

update invoices
set payment_total = 10, payment_date = '2019-03-01'
where invoice_id = 1

update customers
set points = points + 50
where birth_date < '1990-01-01'
# 更新多行数据，需要关闭安全模式

# 使用子查询
update orders
set comments = '金牌客户'
where customer_id IN (
        select customer_id
    from customers
    where points > 3000
)

LOW_PRIORITY、IGNORE修饰降低更新的优先级，忽略操作期间的错误。

Delete

delete from orders # 删除全部
where invoice_id = 1

配合order排序，以及limit删除指定的行
配合JOIN实现多表删除，多表删除语句中不能使用 LIMIT 子句和 ORDER BY 子句
配合修饰符，会降低删除操作的优先级

GROUP

对整个表中数据进行分组，分组之后再聚集
计算聚集时必须使用
结合聚合函数进行分类，结合聚合函数时无法使用where分类聚合函数的条件
- where在分组之前筛选数据
- having在分组之后筛选数据
一般结合ORDER BY，保证正确的排序

子查询

select
        customer_id,
    first_name,
    last_name
from customers
where customer_id IN(
        select o.customer_id
    from order_items oi
    JOIN orders o using(order_id)
    where product_id = 3
);

select
        DISTINCT customer_id,
    first_name,
    last_name
from customers c
join orders o
        using (customer_id)
join order_items oi
        using (order_id)
where oi.product_id = 3

ANY

使用ALL关键字，比较子查询返回的多个值

EXISTS

如果IN运算符后写的子查询生成了大量的结果集，EXISTS效率更高，他没有生成大量的结果集。

select *
from products p
where not EXISTS(
        select product_id
    from order_items o
    where p.product_id = o.product_id
)

select *
from products
where product_id NOT IN(
        select product_id
    from order_items
)

SELECT子查询

select 
        client_id,
    name,
    (select sum(invoice_total)
                from invoices i
                where c.client_id = i.client_id) as total_sales,
    (select AVG(invoice_total) from invoices) as avg,
    (select total_sales - avg) as difference
from clients c

FROM子查询

select *
from(
        select 
                client_id,
                name,
                (select sum(invoice_total)
                        from invoices i
                        where c.client_id = i.client_id) as total_sales,
                (select AVG(invoice_total) from invoices) as avg,
                (select total_sales - avg) as difference
        from clients c
) as sales
where total_sales IS NOT NULL

视图

创建视图

create view  sales_by_client as
select 
        concat(first_name, ' ',last_name) as name,
        points,
    case
                when points > 3000 then 'gold'
        when points < 3000 and points > 2000 then 'silver'
        else  'bronze'
        end as member
from customers

保存视图后，在View可以看到，可以当作一个表来使用
可以用来复用数据，视图不存储数据，相当于一张虚拟表

更改或删除视图

drop view clients_balance

尽可能放在sql文件，并加入源码控制

可更新视图

往往我们没有对表修改的权限，所以通过视图插入、删除更改
会更新原来表中数据吗

WITH OPTION CHECK

在创建视图结尾加入with option check
在后续对视图进行操作时，如果进行操作设计删除等操作，会收到错误提醒

存储过程

存储过程的主要作用如下：

代码重用：存储过程可以将常用的SQL操作封装起来，可以在多个地方重复调用，提高代码的重用性和可维护性。
执行复杂操作：存储过程可以执行一系列的SQL语句，包括条件判断、循环和事务处理等。这样可以在数据库端实现复杂的业务逻辑，减少数据传输和网络开销。
数据安全性：通过存储过程，可以限制用户对数据库的直接访问，只允许通过存储过程进行数据操作。这样可以提高数据的安全性，防止非授权用户对数据库进行恶意操作。
提高性能：存储过程可以在数据库端进行编译和优化，执行速度通常比客户端的逐条SQL执行更快。此外，存储过程还可以减少与数据库的通信次数，降低网络延迟。

创建存储过程

delimiter $$
create procedure get_invpoces(id int)
begin
    select * from invoices as i
    where i.client_id = id;
end $$

调用存储过程

call get_clients_by_state('CA')

触发器

创建触发器

DELIMITER $$  
CREATE TRIGGER payments_after_delete  
AFTER DELETE ON payments  
FOR EACH ROW  
BEGIN  
    UPDATE invoices SET payment_total = payment_total - old.amount 
    WHERE invoice_id = old.invoice_id;  
END $$  
DELIMITER ;

其他操作

show triggers
show triggers like 'payments%'

-- 一般的命名方式：table_after_insert

-- delete
drop triggers if exists table_after_insert

事件

show variables
set global event_scheduler = OFF

创建事件

DELIMITER $$  
create event yearly_delete_stale_audit_rows
on schedule
        -- AT '2019-05-01'
    every 1 year starts '2019-01-01' ENDs '2029-01-01'

DO begin 
        delete from payments_audit
    where action_date < now - INTERVAL 1 YEAR;
END $$ 
DELIMITER ;

其他操作

show events;
show events like 'yearly%';

drop event if exists ___;

-- modifiy
alter event year_delete_stale_audit_row enable;

事务

创建事务

START TRANSACTION;

insert into orders (customers_id, order_date, status)
values(1, '2019-01-01',1);


INSERT INTO order_items
VALUES(last_insert_id(), 1, 1, 1);

COMMIT;
-- rollback;

当我们涉及delete、insert等操作时，mysql默认会放到事务当中处理，show variables like 'autocommit'

并发和锁定

如果一个事务视图修改一行或者多行，会给这些行加上锁，防止其他事务对这些行修改，所以默认情况下不用考虑并发问题
事务的隔离级别越高，会存在越重的性能和可拓展性问题，因为会涉及更多的锁，防止并发问题的前提，是限制性能。

SHOW VARIABLES LIKE 'transaction_isolation';

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

READ UNCOMMITTED

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customer_id = 1;


USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
COMMIT;

当在第二个客户端执行第二段SQL语句时，未COMMIT，但是第一个客户端已经读取到了20，这是dirty data
READ UNCOMMITTED是最低级别的隔离级别，可能会发生各种问题

READ COMMITTED

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;


USE sql_store;
START TRANSACTION;
UPDATE customers
SET points 30
WHERE customer id 1;
COMMIT

解决上述中读取dirty data的问题，但是会出现不一致读问题
读取第一段points时未提交时20，但是在第二段读时提交了变成30，造成前后数据不同问题

REPEATABLE READ

USE sql_store;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;



USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 30
WHERE customer_id = 1;
COMMIT;

这是默认的隔离级别，可以解决大部分问题，会保证读取的一致性
但是这部分会出现另一个问题幻读，为了保证读取一致性，但是后续操作要对之前新增的数据操作，由于一致性造成了忽略

SERIALIZABLE

USE sql_store;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
COMMIT;

USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 4;
COMMIT

在读取VA时，但是第二段未提交，但是应该算在内，所以当执行select时，会先等待其他提交相关的操作，再执行select，避免了幻读问题
但是由此引发效率低下问题

死锁

死锁就是当不同事务均因握住订别的事务需要的“锁”，而无法完成的情况，导致双方都在等待锁，无法释放问题
为了尽可能减少死锁，在更新多条记录的时候可以遵照相同的顺序，尽可能缩小事务和其运行时间，调整不同事务的执行时间避免活跃阶段，造成死锁问题

数据类型

尽可能使用合适的数据类型，这样会使得数据库大小更小，查询速度也会更快

Strings

CHAR(X)	fixed-length
VARCHAR(X)	max：65535
MEDIUMTEXT	max：16MB
LONGTEXT	max：4GB

英文字母占用1个字节，亚洲文字占用3个字节，其他占用2个字节
CHAR使用时指定一个长度，不足长度时补充空格，查询时去除多余的空格
- 由于 CHAR 数据类型采用固定的长度进行存储，因此 CHAR 的性能要比 VARCHAR 更好
- 字符串原有的空格，如果是前缀空格，那么会保留，后缀空格会被抹除
- 超出长度，出错
VARCHAR相比CHAR是一个可变长的字符串
- 使用VARCHAR时需要指定一个最大的长度值X，默认255
- 超过长度时，会截断，后续是空格直接去除，如果是字符会error出错

Integers

TINYINT	[-128,127]
UNSIGNED TINYINT	[0, 255]
ZEROFILL	INT(4), 1 -> 0001

INT 数据类型列用来存储整数，比如年龄，数量等，它也可以结合AUTO_INCREMENT作为主键列。

DECIMAL

DECIMAL(p, s)

精度p，1~65
DECIMAL(9, 2)，一共九位数，小数点后2位
等价于关键词， DEC、NUMERIC、FIXED

BOOLEANS

BOOL, BOOLEANS

ENUMS

枚举类型并不是特别好用，更改枚举成员需要使用ALTER TABLE 语句重建整个表，这在资源和时间方面都是昂贵的。
ENUM 它不是 SQL 标准的，因此，移植到其他 RDBMS 可能是一个问题。

枚举列表是不可重用的。例如，上面 orders 表中的状态枚举值，不能重用到新建的其他表上。

DATE / TIME

要格式化日期值，请使用 DATE_FORMAT() 函数
要计算两个日期值之间的天数，请 DATEDIFF() 按如下方式使用该函数
DATE_ADD() 函数用于将天数、周数、月数、年数等添加到给定的日期值
同样，DATE_SUB() 被用于从日期中减去一个时间间隔

DATETIME

相比前者日期 + 时间

BIT

BIT(M) 允许存储 M 位值。 M 的取值范围是 1 到 64。
如果不指定 M，那么它的默认值为 1。 BIT 等效于 BIT(1)。
BIT 列非常适合用来存储状态值。
插入的格式，b'01' B'01' 0b01

BLOBS

存储大型的二进制数据

TINYBLOB	255b
BLOB	65KB
MEDIUMBLOB	16MB
LONGBLOB	4GB

JSON

-- 添加JSON
UPDATE products
SET properties = '
{
    "dimensions": [1, 2, 3],
    "weight": 10,
    "manufacturer": {"name": "sony"}
}
'
where product_id = 1

SET properties = JSON_OBJECT(
    'weight', 10,
    'dimensions', JSON_ARRAY(1,2,3),
    'manufacturer', JSON_OBJECT('name', 'sony')
)

-- 查询JSON
SELECT product_id, JSON_EXTRACT(properties, '$.weight') AS weight
FROM products
WHERE product_id = 1;

SELECT product_id, properties -> '$.weight'

-- 更新JSON属性，或者添加新的属性
UPDATE products
SET properties = JSON_SET（
    properties,
    '$.weight', 20
    '$.age', 10
）
WHERE product_id = 1;

-- 删除JSON
UPDATE products
SET properties = JSON_REMOVE（
    properties,
    '$.age'
）
WHERE product_id = 1;

是一种通过网络存储和传输数据的轻量级格式
$表示当前的JSON文件

主键和外键

主键

主键要短，可唯一标识记录，且永不改变。比如在student表中新建一个student_id作为主键。
主键不可以为空，另外也可以设置Auto Incremental。

外键

MySQL里可以通过一对一或一对多两种连线表达这种先后关系/因果关系并自动建立外键
- 其中学生和课程被称作父表或主键表
- 注册事件被称作子表或外键表，外键是子表里对父表主键的引用
- student_id 和 course_id 两个外键分别是students和courses两个表的主键
- 作为衍生表可以设置这两个外键作为联合主键，或者是另外设置一个单独的主键 enrollment_id
  - 好处是可以避免重复的注册记录，即可以防止同一个学生重复注册同一门课程，因为主键（这里是联合主键）是唯一不可重复的，这可以防止一些不合理的数据输入
  - 坏处是如果 enrollments 未来有新的子表，就需要复制两个字段（而不是 enrollment_id 一个字段）作为外键，这也不一定是很大的麻烦，要根据数据量以及子表是否还有子表等情况来考虑，在一定情况下可能会造成不必要冗余和麻烦

外键约束

有外键时，需要设置约束以防止数据损坏/污染（不一致）
在 enrollements 表设计模式里，打开 Foreign Keys 标签页，可以看到两个外键，以 fk_子表_父表 的方式命名，名称后可能有数字，是MySQL为了防止外键与其他外键重名自动添加的，这里没必要，可去掉。
右边 Foreign Key Options 可分别选择当父表里的主键被修改或删除（Update / Delete）时，子表里的外键如何反应，有三种选项：
- CASCADE: 瀑布/串联/级联，表示随着主键改变而改变，如主键某学生的 student_id 从1变成2，则该学生的所有注册课程记录的 student_id 也会全部变为2 （注意主键一般也最好是永远不要变的，这里讨论的是特殊情况）
- RESTRICT / NO ACTION: 两者等效，作用都是禁止更改或删除主键。如：对于有过注册记录的课程，除非先删除该课程的注册购买记录，不然不能在 courses表里删除该课程的信息
- SET NULL: 就是当主键更改或删除时，使得相应的外键变为空，这样的子表记录就没有对应的主键和对应的父表记录了（no parent），被称为孤儿记录（orphan record），这是垃圾数据，让我们不知道是谁注册的课程或不知道注册的是什么课程，一般不用，只在极其特殊的情况可能有用。

索引

使用索引来加快检索速度，索引就像是字典中的目录。
索引是一种数据结构，例如、B-Tree，它提高了从表中检索数据行的速度，但需要额外的写入和存储来维护它。
当使用主键或唯一键创建表时，MySQL 会自动创建一个名为 PRIMARY 的索引。该索引称为聚集索引。
- PRIMARY 索引是特殊的，因为索引本身与数据一起存储在同一个表中。聚集索引强制执行表中行的顺序。
- PRIMARY 索引以外的其他索引称为二级索引或非聚集索引。
- 每个 InnoDB 表都有一个聚集索引。
- 聚集索引决定了表中的行的物理顺序。
聚簇索引和非聚簇索引
- 聚簇索引：逻辑上连续且物理空间上连续
  - 索引数据和表数据在空间中位置是连续的
- 非聚簇索引：逻辑上连续，物理空间上不连续
  - 索引节点和表数据之间用物理地址的方式维护二者之间的联系

创建索引

create index idx_state on customers (state);
explain select customer_id from customers where state = 'CA'

查看索引

show indexes in customers;
analyze table customers;

drop index index_name on table_name

当创建了错误的索引，或者为了更快插入和更新数据，先删除现有的索引

USE INDEX

MySQL查询优化器根据实际，不一定使用索引，会做出他认为最优选择，可以使用USE INDEX，建议优化器去使用指定的命名索引。
如果想要优化器必须使用指定的索引，应当使用FORCE INDEX。
一般USE INDEX放在FROM语句之后。

索引基数

MySQL SHOW INDEXES 语句返回的 Cardinality 列中值是索引基数。索引基数是 MySQL 查询优化器决定是否使用索引的一个重要依据。

每一个索引都有对应的索引基础，决定了优化器使用这个索引的优先级。

唯一索引

比如每个表中自动创建的PRIMARY索引，可以加快从表中检索数据的速度，还可以防止在指定的一个或者多个列中出现重复值。

create unique index on table_name(index_)//创建唯一索引

前缀索引

create index idx_lastname on customers (last_name(20))

SELECT
    COUNT (DISTINCT LEFT (Last name,1)),
    COUNT(DISTINCT LEFT(last_name,5)),
    COUNT(DISTINCT LEFT (Last_name,10))
FROM customers;

确定前缀长度时，应该尽量让字符串前缀保持唯一性。唯一性越强，则索引的效率越高。
使用前缀索引，可以减少内存的消耗。

全局索引

CREATE FULLTEXT INDEX idx_title_body ON posts (title,body);

CREATE FULLTEXT INDEX idx_title_body ON posts (title,body);

会计算一个相关性得分

复合索引

create index idx_state_points on customers (state, points)

复合多个索引的顺序也很重要
- 频繁使用的列排在最前面，否则优化器可能不使用索引。
- 基数更高的列在最前面，或者说是唯一值，比如性别就是2，州有48个，那么根据性别索引降低到500K，而经过48个州，更加有效。
常常使用的是复合索引，而不是对单个列使用索引，单个索引浪费空间
MySQL会自动将主键包含在每个二级索引当中

使用索引排序

设计好的索引，可以用于筛选数据和排序数据。

使用建议

避免创建重复索引

其他函数

文本处理函数

select concat(city,' ',RTrim(address)) as newtitle from student;
# RTrim() /  LTrim() 去除左边右边空格
select trim('a' from 'aaabbaa') as out put;
# 去除前后的字符

select city, upper(address) as newaddress from student;
# Upper() / Lower()文本大小写转换

Left() /  Right()
# 返回串左边 / 右边的字符

Length()
# 返回串的字节个数,需要考虑不同的字符集
 
Locate()
# 找出串的一个字串

Soundex()
# 返回串的SOUNDEX值
# 将任何文本串转换为描述其语音表示的字母数字模式的算法

SubString() / substr()
# 返回子串的字符

concat(str1, str2,str3)
# 拼接字符串

instr()
# 返回子串第一次出现的位置

lpad() / rpad()
# 用指定的字符，左填充到指定数量的字符数
 
replace()
# 字符串替换

日期和时间处理函数

AddDate()     增加一个日期（天、周等）
AddTime()     增加一个时间（时、分等）
CurDate()     返回当前日期
CurTime()     返回当前时间
Date()        返回日期时间的日期部分
DateDiff()    计算两个日期之差
Date_Add()    高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day()         返回一个日期的天数部分
Dayofweek()   对于一个日期，返回对应的星期几
Hour()        返回一个时间的小时部分
Minute()      返回一个时间的分钟部分
Month()       返回一个日期的月份部分
NowC()        返回当前日期和时间
Second()      返回一个时间的秒部分
Time()        返回一个日期时间的时间部分
Year()        返回一个日期的年份部分

str_to_date   将日期格式的字符转换成指定格式的日期
str_to_date('9-13-1999','%m-%d-%Y')

date_format  将日期转换为字符
date_format(now(), '%y年%m月%d日') as out_put

对于datatime的数据类型存储日期以及时间值，样例表中的值全具有时间值00::00::00，当我们直接使用where order_date = '2005-09-01';，与实际存储的where order_data = '2005-09-01 11::30::14冲突，应该用where date(order_date) = '2005-09-01'。

where date(order_date) between '2005-09-01' and '2005-10-01';

数值处理函数

round(X,2) 四舍五入
ceil() 向上取整
floor()向下取整
truncate() 截断
mod()  取余 a -a/b*b
Abs()  返回一个数的绝对值
Cos()  返回一个角度的余弦
Exp()  返回一个数的指数值
Mod()  返回除操作的余数
Pi()   返回圆周率
Rand() 返回一个随机数
Sin()  返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan()  返回一个角度的正切

聚集函数

运行在行组上，计算和返回单个值得函数。

select AVG(id) as avg_id from student;
select AVG(id) as avg_id from student where city = '杭州';
select count(*) as count_id from student;
select count(city) from student;
select max(id) from student;
select min(id) from student;

如果有多行结果时，MAX返回最后一行的，MIN返回最前面的行。

select sum(id) from student;

sum、avg一般用于处理数值型
max、min、count可以处理任何类型
默认都会有重复值，需要加DISTINCT去重，在括号内
都会忽略值为NULL的行，如果没有忽略NULL值，与NULL值计算会全部变成NULL。

IF函数

IF(expression, first, second)

select 
        concat(first_name, ' ',last_name) as name,
        points,
    case
        when points > 3000 then 'gold'
        when points < 3000 and points > 2000 then 'silver'
        else  'bronze'
        end as member
from customers

其他函数

select version();
select datebase();
select user();

SQL

SELECT

ORDER

WHERE

AND OR IN NOT IN

IS NULL

EXISTS

CONCAT

AS

LIMIT

DISTINCT

LIKE

匹配特殊字符

匹配字符类

USING

JOIN

UNION

INSERT

UPDATE

Delete

GROUP

子查询

ANY

相关子查询

EXISTS

SELECT子查询

FROM子查询

视图

创建视图

更改或删除视图

可更新视图

WITH OPTION CHECK

存储过程

创建存储过程

调用存储过程

触发器

创建触发器

其他操作

事件

创建事件

其他操作

事务

创建事务

并发和锁定

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

死锁

数据类型

Strings

Integers

DECIMAL

BOOLEANS

ENUMS

DATE / TIME

DATETIME

BIT

BLOBS

JSON

主键和外键

主键

外键

外键约束

索引

创建索引

查看索引

USE INDEX

索引基数

唯一索引

前缀索引

全局索引

复合索引

使用索引排序

使用建议

其他函数

文本处理函数

日期和时间处理函数

数值处理函数

聚集函数