MySQL关键字用法

关键词的使用

GROUP BY，对查询结果集进行分组

根据某个列或多个列对结果集进行分组（即，select 后得到的结果按照给定列进行分组），group by子句后面的字段作为依据，在表中去分组。

# 执行该语句时会将where条件下的数据，以column_name为依据进行分组(唯一形式)，得到一个结果集
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

如下例子：

# 执行如下sql得到以所有名字出现的次数
SELECT name, COUNT(*) FROM tb GROUP BY name;
# 将以上的数据表按名字进行分组，再统计每个人登录的次数
SELECT name, SUM(singin) as singin_count FROM tb GROUP BY name WITH ROLLUP;
# 使用coalesce(name, '总数')将name为null时代替为‘总数’
SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM tb GROUP BY name WITH ROLLUP;
#使用having在分组上对结果集再进行条件筛选
SELECT name,sum(singin) As singin_sum From tb Group By name Having singin_sum>2;

具体建表SQL请点击这里
补充：

• HAVING是设置分组条件的语句，其后面的字段必须是group by中的或是聚合函数。select后出现的字段必须是聚合函数字段或group by中有的，MySQL中受sql_mode=only_full_group_by的影响，如果需要设置该条件需要配置MySQL
• where负责记录的筛选，having负责分组的筛选。where先于having执行

连接join

MySQL INNER JOIN(等同于JOIN、cross join)获取两个表中字段匹配关系的记录。
MySQL LEFT JOIN (属于out join)会读取左边数据表的全部数据，即便右边表无对应数据。
MySQL RIGHT JOIN (属于out join)会读取右边数据表的全部数据，即便左边边表无对应数据。
注：
一般连接的条件使用ON（但是事实where也是同效的）
多表查询时，在一个join后继续添加join，如子表 left join 父1表 on… left join 父2表 on…
尽可能inner join（内连）

NULL值处理(值得注意)

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。这些都是无效的，虽然不会报错，但是查询结果为空。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。

如下例子：

执行!=null结果为空：

使用IS NOT NULL 或 IS NULL结果显示正常：

正则

除了LIKE [pattern]模糊查询外，MySQL还支持REGEXP [pattern]来实现正则查询。

事务

MySQL事务只有InnoDB支持，且开启方式有两种：
关于回滚，如果不是指定回滚到某个savepoint，那么就是直接回滚到begin之前的状态。

# 第一种
BEGIN # 开始一个事务
ROLLBACK # 事务回滚
SAVEPOINT identifier # SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier # 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常
ROLLBACK TO identifier # 把事务回滚到标记点；
COMMIT # 事务确认

# 第二种
SET AUTOCOMMIT=0 # 禁止自动提交，及相当于begin
ROLLBACK # 事务回滚和上面相同
SET AUTOCOMMIT=1 # 开启自动提交，及相当于commit;

ALTER语句

对应不同情况需要使用不同的关键词，如下：

• 删除表字段：

DROP COLUMN column_name

• 添加表字段：

ADD COLUMN column_name datatype...
# 如果添加字段需要到到指定位置，那么需要使用关键字
FIRST # 放在语句最后，该新增字段会放到表的首部
AFTER other_column # 放到语句后面，该新增字段会放到指定字段的后面

• 修改表字段2中方式，差异较大modify和change：
  总体来说区别是modify不可改变字段名，change可以改变字段名（因此修改字段名时使用change但是应该先记得改列的原来的属性设置,可以通过show create table tb_name 先进行查看）

MODIFY COLUMN column_name datatype...
CHANGE old_column_name new_column_name datatype...;

• 修改表名

ALTER TABLE old_tb_name RENAME TO new_tb_name;

• 修改字段默认值

ALTER TABLE tb_name ALTER column_name SET DEFAULT 1000;

索引INDEX

索引是一个非常重要的数据库概念，同时实际开发中需要使用索引来提升检索速度，就像是一本字典的目录一样，通过索引快速找到目的组数据。当然也有些情况下索引是失效的，依旧会全扫描（FULL SCAN），具体情况请移步或这里。
提示：explain可以分析是否使用了索引

创建索引：（3种方式）

# 建表时
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  
# 建好表后
CREATE INDEX indexName ON mytable(columnName(length)); 
# 在建好表后通过ALTER命令进行建索引
ALTER table tableName ADD INDEX indexName(columnName(length))
**注：**
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

删除索引：（2种方式）

DROP INDEX indexName ON tb; # 注意此处使用ON
ALTER TABLE tb_name DROP INDEX ind_name;

查看索引：

SHOW INDEX FROM tb_name;

外键foreign key(2种方式)

当从表（子表）中字段使用到主表（父表）的主键字段时，这个时候需要外键foreigin key将两个表联系起来。
注意：
只有InnoDB才支持外键

# 创建表时最后加上
foreign key(child_column) references Parents_tb(parents_column);
# 创建好表后
alter table Child_tb add foreign key(child_column) references Parents_tb(parents_column);
# 如果想自己建立有名的外键
ALTER TABLE Child_tb ADD CONSTRAINT fk_customer
FOREIGN KEY (child_column) REFERENCES Parents_tb(parents_column);
# 如果需要撤销外键，需要先找到系统生成的外键名通过show create table tb可见，然后执行
alter table Child_tb DROP foreign key fk_name

LIMIT

LIMIT用于限制查询数量：
LIMIT [数据数量];
LIMIT [start:从0开始][nums:数据数量];