操作表数据，增删改查

查询

select * from table;
select id,name,age from table;
select * from table where id = '2323';
select * from table order by id asc; -- 排序默认 a-z
select * from table order by id desc;
select * from table where name like '%xu%';
select * from table where index  != 0;
select * from table where sex = 'man' and age = 18;
select * from table where age = 18 or age = 20;
select * from table where id in (10,11);
select * from table where id not in (10,11);
select * from table where name REGEXP 'xu|zhao'; --正则
select * from table where phone REGEXP '[0-9]{11}'; --正则

新增

-- 新增单行
INSERT INTO table(age, name, sex) VALUES(18, 'tom', 'man'); 
-- 新增多行
INSERT INTO table(age, name, sex) VALUES(18, 'tom', 'man'),(18, 'may', 'man');
-- selected 其他表数据插入
INSERT INTO table(age, name, sex) select age,name,sex from otherTable;

更新

update tabel set age = 30 where id=123;
update tabel set age = 30,sex = 'women' where id=123;
update IGNORE tabel set age = 30,sex = 'women' where id=123; -- 忽略错误

删除

delete from table  where id=123;
-- 删除表中所有行
TRUNCATE TABLE;

更快的删除如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

操作表结构，增删改查

一般使用数据库管理工具创建表，更便捷；并谨慎更改表结构

新增/创建表

CREATE TABLE tableName (age int null,name var(50) null, sex var(50) null);

更新表

ALTER TABLE table add score init; --新增列
ALTER TABLE table drop column age; --删除列

删除表

DROP TABLE table;  --谨慎使用

重命名表

RENAME TABLE table TO otherTableName;

操作视图，增删改查

视图是虚拟的表，理解为公共函数。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的数据。视图的最常见的应用之一是隐藏复杂的SQL,例如连表查询。

查询视图

直接当作普通表来使用，一般是查询；因为视图主要用于数据检索

select * from viewName;

新增视图

CREATE VIEW viewName AS select * from table;

更新视图

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）; 更新视图意义不大，不如直接创建一个新的视图。

删除视图

DROP VIEW viewName;

连表查询

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作.

笛卡儿积

select * from table1,table2; 
-- 查询出的结果行数量是：table1.length * table2.length;

inner join on 内连接

select * from table1 INNER JOIN table2 ON table1.id = table2.id;

outer join on 外链接

select * from table1 OUTER JOIN table2 ON table1.id = table2.id;

联结多个表

考虑性能，连接的表不应该过多，超过3个表时，就应该考虑表结构设计是否合理

select * from table1,table2,table3 where talbe1.id = table2.id AND talbe2.id = table3.id;

使用表别名

select * from table1 AS a,table2 AS b where a.id = b.id;

自联结

select * from table1 where name = (select name from table1 where id=123);
-- 第二种写法
select * from table1 AS t1, table1 AS t2 where t1.id = t2.id and t1.id = 123;

数据分组 group by

一般配合聚合函数来处理分组，例如：求平均数、最大值、最小值

image.png

-- 求男生的平均年龄
selecte *,avg(age) age avgAge from table group by sex HAVING sex = 'man';
-- HAVING HAVING非常类似于WHERE。事实上，目前为止所 学过的所有类型的WHERE子句都可以用HAVING来替代.
-- 例如求男生中分数及格的平均年龄：
group by sex HAVING sex = 'man' and score > 60;

使用子查询

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

使用计算属性

SELECT 
    *,
    (SELECT 
            COUNT(*)
        FROM
            apago_top.ariticle AS t1,
            apago_top.ariticle_preview AS t2
        WHERE
            t1.id = t2.id AND t2.acttype = '阅读') AS previewNum,
             (SELECT 
            COUNT(*)
        FROM
            apago_top.ariticle AS t1,
            apago_top.ariticle_preview AS t2
        WHERE
            t1.id = t2.id AND t2.acttype = '点赞') AS likeNum,
             (SELECT 
            COUNT(*)
        FROM
            apago_top.ariticle AS t1,
            apago_top.ariticle_preview AS t2
        WHERE
            t1.id = t2.id AND t2.acttype = '评论') AS commentNum
FROM
    apago_top.ariticle;

使用子查询数据作过滤

-- 普通写法
select * from table where age in [16,17,18];
-- 当[16,17,18] 值在其他表时，就需要使用子查询
select * from table where age in (select age from otherTable);

组合查询 UNION

MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回.这些组合查询通常称为并（ union ）或复合查询（compound query）;UNION从查询结果集中自动去除了重复的行;

-- 多个where and 转为组合查询
select * from table where sex = 'man' and age = 18;
-- 组合查询. 
select * from table where sex = 'man'
UNION
select * from table where age = 18;

数据处理函数

字符串处理函数
date 日期类型处理函数
数值处理函数

具体使用时，再查文档

触发器，增删

触发器(参考勾子函数)是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：DELETE、INSERT、UPDATE；其他MySQL语句不支持触发器；配合 AFTER/BEFORE 一个表最多6种触发器。

触发器中虚拟表

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行
在UPDATE触发器代码中
-- 你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，
-- 引用一个名为NEW的虚拟表访问新更新的值

新增触发器

-- 关键词 CREATE TRIGGER；新建触发器名称 newproduct; 修改另一个表数据
CREATE TRIGGER newproduct AFTER INSERT ON tableName FOR EACH ROW 
BEGIN
update tabel set age = 30 where id=123;
END；
-- BEGIN END 非必需，当需要执行多条时，必不可少。

删除触发器

DROP TRIGGER newproduct;

使用触发器格式化数据

CREATE TRIGGER newproduct BEFORE INSERT ON tableName FOR EACH ROW 
SET NEW.name = Upper(NEW.name)

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

存储过程

存储过程(参考函数)简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

BEGIN和END语句用来限定存储过程体;

创建存储过程

CREATE PROCEDURE proname() 
BEGIN
select * from table;
END;

执行存储过程

CALL proname();

删除存储过程

已经存在的重名的存储过程，需要删除后重新创建，无法修改。

DROP PROCEDURE proname;

事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。INSERT、UPDATE和DELETE语句。你不能回退SELECT语句

事务（transaction）指一组SQL语句；

回退（rollback）指撤销指定SQL语句的过程；

提交（commit）指将未存储的SQL语句结果写入数据库表；

保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）

使用ROLLBACK

START TRANSACTION
delete from table  where id=123; --删除的数据 ROLLBACK 后将会还原
ROLLBACK;

使用COMMIT

START TRANSACTION
delete from table  where id=123;
delete from table  where id=123;
COMMIT; 
-- COMMIT语句仅在不出错时写出更改，只要有一条语句错误 ，所有语句将会回退。

使用保留点 SAVEPOINT

-- 使用保留点处理部分提交或回退
SAVEPOINT pointName; -- 定义一个保留点
ROLLBACK TO pointName; -- 回退到指定保留点
RELEASE SAVEPOINT pointName; --明确地释放保留点

全文本搜索

全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回

启用全文本搜索 FULLTEXT

CREATE TABLE tableName (age int null,name var(50) null, fulltext(marker));

进行全文本搜索Match()和Against()

-- 使用两个函数Match()和Against()执行全文本搜索
select marker from tableName where Match(marker) Aginst('keyword');

使用查询扩展

-- 查询扩展用来设法放宽所返回的全文本搜索结果的范围,查询扩展极大地增加了返回的行数
select marker from tableName 
where Match(marker) Aginst('keyword' WITH QUERY EXPANSION);

不要在导入数据时使用FULLTEXT 更新索引要花时间

SELECT子句顺序

SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

mysql 常用语句一览表

mysql 常用语句一览表

操作表数据，增删改查

操作表结构，增删改查

操作视图，增删改查

连表查询

数据分组 group by

使用子查询

组合查询 UNION

数据处理函数

触发器，增删

存储过程

事务处理

全文本搜索

SELECT子句顺序

推荐阅读更多精彩内容