SQL常用语句初体验

MySQL Workbench

官方出品，业界有不同的声音，对于只是简单的使用，完全够了。
下载，傻瓜式安装即可。
技术发展到这一步，一切从简，我觉得最好不过了。
爱因斯坦说，如果想做好一件事，那么就把它简单到不能再简单。

输入 hostname port username password 就可以开启 MySQL 之旅了。

SQL语句

SQL（ Structured Query Language），结构化查询语言。

1. 使用数据库

   use dataName;

   注意：表名最好不要使用符号

假设数据

假设表名为 repository，字段如下：

id	name	star	fork	languages	uId
1	antiquity	99	22	JavaScript	1
2	todolist	16	12	Vue	1

假设表名为 user，字段如下：

id	userName
1	yiluyanxia

查询

基本查询

-- 基本查询
SELECT * FROM tableName


-- 条件查询
SELECT * FROM tableName WHERE condition;
SELECT * FROM <表名> WHERE <条件表达式>
-- 示例
SELECT * FROM repository WHERE star >= 100;


-- 多个条件
SELECT * FROM tableName WHERE condition1 AND condition2;
SELECT * FROM tableName WHERE condition1 OR condition2;
SELECT * FROM tableName WHERE NOT condition;
-- 示例，查询大于等于100个star和大于等于100个人fork
SELECT * FROM repository WHERE star >= 100 AND fork >= 100;


-- 也可以同时多个
SELECT * FROM repository WHERE (star < 100 OR star > 90) AND languages = 'JavaScript';

投影查询

-- 投影查询
SELECT column1, column2, column3 FROM tableName
-- 示例
SELECT id, star, fork FROM repository


-- 投影查询，并重命名
SELECT column1 alias1, column2 alias2, column3 alias3 FROM tableName
-- 示例
SELECT id, star, fork fans FROM repository


-- 投影查询，并添加条件
SELECT column1, column2, column3 FROM tableName WHERE condition1;

排序

-- 排序，正序，从小到大，ASC 可省
SELECT column1, column2, column3 FROM tableName ORDER BY column1 ASC;


-- 排序，倒序，从大到小
SELECT column1, column2, column3 FROM tableName ORDER BY column1 DESC;


-- 按多个选项排序
SELECT column1, column2, column3 FROM tableName ORDER BY column1 DESC, column2;

分页

-- 分页
SELECT column1, column2, column3 FROM tableName LIMIT m OFFSET n;
-- 示例，第一页
SELECT id, star, fork FROM repository LIMIT 10 OFFSET 0;
-- 示例，第二页
SELECT id, star, fork FROM repository LIMIT 10 OFFSET 10;
-- 示例，第三页
SELECT id, star, fork FROM repository LIMIT 10 OFFSET 20;

聚合查询

-- 统计总数
SELECT COUNT(*) FROM tableName;
-- 统计总数，并重命名
SELECT COUNT(*) alias1 FROM tableName;
-- 示例，统计repository表中有多少行
SELECT COUNT(*) num FROM repository;

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

-- 求平均值，该列为数值类型
SELECT AVG(column1) FROM tableName;
-- 示例，计算平均star数
SELECT AVG(star) average FROM repository;


-- 求和，该列为数值类型
SELECT SUM(column1) FROM tableName;
-- 示例，计算总的star数量
SELECT SUM(star) summation FROM repository;


-- 计算某一列的最大值
SELECT MAX(column1) FROM tableName;


-- 计算某一列的最小值
SELECT MIN(column1) FROM tableName;


-- 分组
SELECT COUNT(*) FROM tableName GROUP BY column1;
-- 示例，统计每种语言下的数目
SELECT languages COUNT(*) num FROM repository GROUP BY languages;

笛卡尔查询（多表查询）

注意：结果集是目标表的行数乘积，每个表中各有10条记录，那么将返回10*10=100条记录。当添加条件后，结果集数量会减少。

-- 多表查询
SELECT * FROM tableName, tableName1;


-- 多表查询，并重命名列
SELECT 
    tableName1.column1 t1column1,
    tableName2.column1 t2column1
FROM tableName1, tableName2;


-- 多表查询，并重命名表名
SELECT
    t1.column1 t1column1,
    t2.column1 t2column1
FROM tableName1 t1, tableName2 t2;


-- 多表查询，并添加条件
SELECT
    t1.column1 t1column1,
    t2.column1 t2column1
FROM tableName1 t1, tableName2 t2
WHERE condition;

连接查询

-- 连接查询，INNER JOIN，交集
SELECT t1.column1, t1.column2
FROM tableName1 t1
INNER JOIN tableName2 t2
ON t1.column1 = t2.column2;
-- 示例,获取仓库中的所有记录，并返回对应的用户名。
SELECT r.id, r.name, r.star, r.uId, u.userName
FROM repository r
INNER JOIN user u
ON r.uId = u.id;


-- 连接查询，RIGHT OUTER JOIN，右表所有的记录
SELECT t1.column1, t1.column2
FROM tableName1 t1
RIGHT OUTER JOIN tableName2 t2
ON t1.column1 = t2.column2;


-- 连接查询，LEFT OUTER JOIN，左表所有的记录
SELECT t1.column1, t1.column2
FROM tableName1 t1
LEFT OUTER JOIN tableName2 t2
ON t1.column1 = t2.column2;


-- 连接查询，FULL OUTER JOIN，两张表的全集
SELECT t1.column1, t1.column2
FROM tableName1 t1
FULL OUTER JOIN tableName2 t2
ON t1.column1 = t2.column2;

插入

-- 插入
INSERT INTO tableName (column1, column2, column3) VALUES (value1, value2, value3);
-- 示例，插入一条
INSERT INTO repository (name, star, fork, languages) VALUES ('sqlTest', 0, 0, 'SQL');
-- 示例，插入多条
INSERT INTO repository (name, star, fork, languages) VALUES
    ('sqlTest', 0, 0, 'SQL'),
    ('cms', 0, 0, 'JavaScript');


-- 插入或更新，插入一条新记录（INSERT），但记录已经存在，则更新该记录
INSERT INTO tableName (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE column1=value1, column2=value2, column3=value3;
-- 示例
INSERT INTO repository (id, name, star, fork, languages) VALUES (3, 'sqlTest', 0, 0, 'SQL') ON DUPLICATE KEY UPDATE name='sqlTest', star=0, fork=0, languages='SQL';


-- 插入或忽略，插入一条新记录（INSERT），但记录已经存在，则直接忽略
INSERT IGNORE INTO tableName (column1, column2, column3) VALUES (value1, value2, value3);
-- 示例
INSERT IGNORE INTO repository (id, name, star, fork, languages) VALUES (3, 'sqlTest', 0, 0, 'SQL');

更新

注意：没有条件的时候，整个表都会更新。

-- 更新
UPDATE tableName SET column1=value1, column2=value2 WHERE condition;
-- 示例，更新一条
UPDATE repository SET star=2, languages='java' WHERE id=4;
-- 示例，修改条件更新多条
UPDATE repository SET star=2, languages='java' WHERE id>2;


-- 更新，字段可以使用表达式
UPDATE tableName SET column1=column1+value1 WHERE condition;
-- 示例
UPDATE repository SET star=star+1 WHERE id=4;

删除

注意：没有条件的时候，整个表的记录都会被删除。

-- 删除
DELETE FROM tableName WHERE condition;
-- 示例
DELETE FROM repository WHERE id=4;

替换

-- 替换，若id=id1的记录不存在，REPLACE语句将插入新记录，否则，当前id=id1的记录将被删除，然后再插入新记录。
REPLACE INTO tableName (id, column1, column2, column3) VALUES (id1, value1, value2, value3);
-- 示例
REPLACE INTO repository (id, name, star, fork) VALUES (3, 'sqlTest', 0, 0);

快照

--快照
CREATE TABLE tableName_new SELECT * FROM tableName WHERE condition;
-- 示例
CREATE TABLE repository_of_user1 SELECT * FROM repository WHERE uId=1;

事务

显示事务

几条SQL语句必须全部执行，若其中一条语句失败，就必须全部撤销。
这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。

-- 显示事务
BEGIN;
UPDATE tableName SET column1=value1, column2=value2 WHERE condition;
INSERT INTO tableName (column1, column2, column3) VALUES (value1, value2, value3);
COMMIT;

回滚事务

-- 回滚事务
BEGIN;
UPDATE tableName SET column1=value1, column2=value2 WHERE condition;
INSERT INTO tableName (column1, column2, column3) VALUES (value1, value2, value3);
ROLLBACK;