简单学习SQL语言
SQL(Structured Query Language)就是一个语言,操作数据库的语言,可以实现增删改查(库、表、列、数据)。
- 表的每一行称为记录(Record),记录是一个逻辑意义上的数据。
- 表的每一列称为字段(Column),同一个表的每一行记录都拥有相同的若干字段。
- 字段定义了数据类型(整型、浮点型、字符串、日期等),以及是否允许为 NULL。注意 NULL 表示字段数据不存在。一个整型字段如果为 NULL 不表示它的值为 0,同样的,一个字符串型字段为 NULL 也不表示它的值为空串''
- 主键是表里记录的唯一标识,现在一般采用自增,与业务逻辑无关
MySQL
MySQL 是应用最光泛的数据库,本文以此为例。
- Ubuntu 用户 - 可通过命令
apt-get install mysql-server
安装最新的 MySQL 版本。 - Mac用户 - 可通过命令
brew install mysql
安装,mac详细安装教程
安装完MySQL
后,以下两个同时具备了
- 一个是
MySQL Server
,即真正的 MySQL 服务器, - 还附赠一个
MySQL Client
程序。其是一个命令行客户端,可登录 MySQL,然后,输入 SQL 语句并执行。
MySQL Client
命令行客户端输入命令,mysql -u root -p
,输入正确的密码,即可连接MySQL Server
,输入exit
,退出连接模式。
EXIT 仅仅断开了客户端和服务器的连接,MySQL 服务器仍然继续运行。
- 在
MySQL Client
中输入的 SQL 语句通过TCP
连接发送到MySQL Server
。默认端口号是3306
, - 如果发送到
本机MySQL Server
,地址就是127.0.0.1:3306
- 如果连接远程,需要
指定IP
或者域名
,使用命令mysql -h 10.0.1.99 -u root -p
管理库、表、列
库:
- 列出所有数据库 -
SHOW DATABASES
- 创建一个新数据库 -
CREATE DATABASE <数据库名字>
- 删除一个数据库 -
DROP DATABASE <数据库名字>
,删除一个数据库将导致该数据库的所有表全部被删除 - 切换数据库 -
SHOW TABLES
,对一个数据库进行操作时,要首先将其切换为当前数据库
表:
- 列出当前数据库的所有表 -
USE <数据库名字>
- 查看一个表的结构 -
DESC <数据库名字>
- 查看创建表的 SQL 语句 -
SHOW CREATE TABLE <数据库名字>
- 创建一个新表 -
CREATE TABLE <表名字>
- 删除一个表 -
DROP TABLE <表名字>
列:
- 给表增加新列 -
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
,给 students 表新增一列 birth - 修改列名 -
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
,列名改为 birthday,类型改为 VARCHAR(20) - 删除列 -
ALTER TABLE students DROP COLUMN birthday;
查询数据
基础查询
-- SELECT * FROM students; SELECT * FROM <表名>
SELECT
是关键字,表示将要执行一个查询*
表示“所有列”FROM
表示将要从哪个表查询,本例中是 students 表。- 该 SQL 将查询出 students 表的所有数据。注意:查询结果也是一个
二维表
,它包含列名和每一行的数据 - 许多检测工具会执行一条
SELECT 1;
来测试数据库连接
条件查询
很多时候,我们并不需要获得所有记录,而是获取指定条件的记录
-- SELECT * FROM students WHERE score >= 80; SELECT * FROM <表名> WHERE <条件表达式>
- 条件 1:根据 score 列的数据判断:
score >= 80
- 条件 2:根据 gender 列的数据判断:
60 <= score <= 90
- AND:
score >= 80 AND gender = 'M'
- OR:
score >= 80 OR gender = 'M'
- NOT:
NOT class_id = 2
,其实等价于class_id <> 2
,所以不常用NOT
- LIKE:
name LIKE 'ab%'
,LIKE 相似,%是任意字符
投影查询
希望返回某些列的数据,而不是所有列的数据
-- SELECT id, score, name FROM students; SELECT 列1, 列2, 列3 FROM ... -- 也可别名 SELECT id, score points, name FROM students; SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...
排序查询
默认是主键排序,其他排序的话用ORDER BY
,不写的话,默认ASC
SELECT * FROM students ORDER BY score; SELECT * FROM students ORDER BY score DESC;
- 分数相同按 gender 排序的话,
ORDER BY score DESC, gender
分页查询
数据量庞大的话,就需要分页展示,可以设置每页 100 条
SELECT id, name, gender, score FROM students ORDER BY score DESC -- 也可简写为 LIMIT 3,0 LIMIT 3 OFFSET 0;
LIMIT 3 OFFSET 0
表示,对结果集从 0 号记录开始,最多取 3 条。注意 SQL 记录集的索引从 0 开始LIMIT
总是设定为pageSize
,OFFSET
计算公式为pageSize * (pageIndex - 1)
聚合查询
统计一张表的数据量用COUNT()
,表示查询所有列的行数,就是记录的数量
SELECT COUNT(*) FROM students;
- 一般给列名设置一个别名,便于处理结果:
SELECT COUNT(*) num FROM students;
,没有记录返回0 - 其他聚合函数,可以计算某列的总体值:SUM、AVG、MAX、MIN,
SELECT AVG(score) average FROM students
,找不到列返回NULL - 获取总页数
SELECT CEILING(COUNT(*) / <pageSize>) FROM students
分组
学生列表里,想要分别统计一班、二班、三班的学生数量,就是用分组,省的一个个查询
SELECT class_id,COUNT(*) num FROM students GROUP BY class_id; 复制代码
执行该 SELECT 语句时,会把class_id
相同的列先分组,再分别计算,因此,得到了多行结果。
- 查询各班的男女生人数:
SELECT class_id,gender,count(*) num FROM students GROUP BY class_id,gender
- 查询各班的平均分:
SELECT class_id,AVG(score) average FROM students GROUP BY class_id;
多表查询
多表查询其实就是FROM <表名1>, <表名2>
比如联合查询学生和班级表
SELECT s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c; WHERE s.gender = 'M' AND c.id = 1;
- 两个表有相同字段的时候,需要使用别名
- 表也可以使用别名
- 多表查询,记录可能会很多,所以尽量带条件
连接查询
主表想增加别的表的字段
比如学生表想增加班级名称字段,需要班级表配合
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s INNER JOIN classes c ON s.class_id = c.id;
- 先确定主表,仍然使用
FROM <表1>
的语法; - 再确定需要连接的表,使用
INNER JOIN <表2>
的语法; - 然后确定连接条件,使用
ON <条件...>
,这里的条件是s.class_id = c.id
,表示 students 表的 class_id 列与 classes 表的 id 列相同的行需要连接; - 可选:加上 WHERE 子句、ORDER BY 等子句。
不同的 join:
- INNER JOIN 只返回同时存在于两张表的行数据
- RIGHT OUTER JOIN 返回右表都存在的行
- LEFT OUTER JOIN 则返回左表都存在的行
- FULL OUTER JOIN,它会把两张表的所有记录全部选择出来,并且,自动把对方不存在的列填充为 NULL
修改数据
关系数据库的基本操作就是增删改查,即 CRUD:Create、Retrieve、Update、Delete。其中,对于查询,我们已经详细讲述了 SELECT 语句的详细用法。
而对于增、删、改,对应的 SQL 语句分别是:
- INSERT:插入新记录;
- UPDATE:更新已有记录;
- DELETE:删除已有记录。
INSERT:插入新记录
-- INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80); -- 查询并观察结果: -- SELECT * FROM students; INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
可以添加多条记录:
INSERT INTO students (class_id, name, gender, score) VALUES (1, '大宝', 'M', 87), (2, '二宝', 'M', 81);
UPDATE
-- UPDATE students SET name='大牛', score=66 WHERE id=1; -- 查询并观察结果: -- SELECT * FROM students WHERE id=1; UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
更新字段,可以使用表达式UPDATE students SET score=score+10 WHERE score<80;
UPDATE
语句可以没有WHERE
条件,整个表的所有记录都会被更新。所以,在执行 UPDATE 语句时要非常小心 最好先用SELECT
语句来测试 WHERE 条件是否筛选出了期望的记录集,然后再用UPDATE
更新
DELETE
DELETE FROM <表名> WHERE ...;
同样也应该SELECT
提前测试
SQL 的常用语句
插入一条新记录(INSERT)
- 若记录已存在则删除,再插入新记录,否则直接插入新纪录。可以使用 REPLACE 语句,省去查询步骤(记录存在与否通过主键判断)
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
- 若记录已存在则更新,再插入新记录,否则直接插入新纪录。
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
- 若记录已存在则停止操作,否则直接插入新纪录。
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
复制表
复制一份当前表的数据到一个新表,专业名词快照
,新创建的表结构和原表结构完全一致。
-- 对class_id=1的记录进行快照,并存储为新表students_of_class1: CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
查询结果集写入到指定表中
一般先创建表
CREATE TABLE statistics ( id BIGINT NOT NULL AUTO_INCREMENT, class_id BIGINT NOT NULL, average DOUBLE NOT NULL, PRIMARY KEY (id) );
用一条语句写入各班的平均成绩
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
强制使用指定索引
在查询的时候,数据库默认选择最合适的索引。如果我们知道如何选择索引,可以使用FORCE INDEX
强制查询使用指定的索引。例如:
-- 指定索引的前提是索引idx_class_id必须存在 SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
事务
事务,其实是说一系列的操作,都执行成功才成功,否则等于没执行。
一系列的操作作为整体,需要用关键词括起来:
BEGIN; -- 第一步:将id=1的A账户余额减去100 UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 第二步:将id=2的B账户余额加上100 UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
有些时候,我们希望主动让事务失败,这时,可以用ROLLBACK
回滚事务,整个事务会失败:
BEGIN; -- 第一步:将id=1的A账户余额减去100 UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 第二步:将id=2的B账户余额加上100 UPDATE accounts SET balance = balance + 100 WHERE id = 2; ROLLBACK;
引用
本文主要参考和大量使用廖大神的原句,简单构成手册使用,感谢廖大神