- SQL就是访问和处理关系数据库的计算机标准语言
- NoSQL数据库,也就是非SQL的数据库,包括MongoDB、Cassandra、Dynamo等等,它们都不是关系数据库
- 数据库一共有三种模型:层次模型网状模型关系模型
- REAL 即FLOAT24
- 主流的关系数据库主要分为以下几类:
商用数据库,例如:Oracle,SQL Server,DB2等;
开源数据库,例如:MySQL,PostgreSQL等;
桌面数据库,以微软Access为代表,适合桌面应用程序使用;
嵌入式数据库,以Sqlite为代表,适合手机应用和桌面程序。
- SQL语言定义了这么几种操作数据库的能力:
DDL:Data Definition Language
DDL允许用户定义数据,也就是创建表、删除表、修改表结构这些操作。通常,DDL由数据库管理员执行。
DML:Data Manipulation Language
DML为用户提供添加、删除、更新数据的能力,这些是应用程序对数据库的日常操作。
DQL:Data Query Language
DQL允许用户查询数据,这也是通常最频繁的数据库日常操作。
- 不区分大小写
- 关系模型
- 关系数据库是建立在关系模型上的,关系模型本质上就是若干个存储数据的二维表
- 表的每一行称为记录(Record),记录是一个逻辑意义上的数据。
- 表的每一列称为字段(Column),同一个表的每一行记录都拥有相同的若干字段。
- 关系数据库的表和表之间需要建立“一对多”,“多对一”和“一对一”的关系
- 主键
- 关系表任意两条记录不能重复。不是指两条记录不完全相同,而是指能够通过某个字段唯一区分出不同的记录,这个字段被称为主键。
-
不使用任何业务相关的字段作为主键。因此,身份证号、手机号、邮箱地址这些看上去可以唯一的字段,均不可用作主键
-
可以使用多个列作为联合主键,但联合主键并不常用
- 外键
- 在
students
表中,通过class_id
的字段,可以把数据与另一张表关联起来,这种列称为外键。
外键并不是通过列名实现的,而是通过定义外键约束实现的 -
ALTER TABLE students ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes (id);
-
ALTER TABLE students DROP FOREIGN KEY fk_class_id;删除外键约束并没有删除外键这一列。删除列是通过
DROP COLUMN ...
实现的。 -
外键既可以通过数据库来约束,也可以不设置约束,仅依靠应用程序的逻辑来保证。
- 在
- 索引
ALTER TABLE students
ADD INDEX idx_name_score (name, score);- 索引的效率取决于索引列的值是否散列,即该列的值如果越互不相同,那么索引效率越高。
- 使用主键索引的效率是最高的,因为主键会保证绝对唯一。
- 唯一索引
-
ALTER TABLE students ADD UNIQUE INDEX uni_name (name);
-
- 数据库索引对于用户和应用程序来说都是透明的
- 查询数据
- 虽然
SELECT
可以用作计算,但它并不是SQL的强项。但是,不带FROM
子句的SELECT
语句有一个有用的用途,就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;
来测试数据库连接。 -
条件查询
-
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
-
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
- name LIKE 'ab%'
- WHERE score BETWEEN 60 AND 90 即 WHERE score >= 60 AND score <= 90
-
- 投影查询
- 使用
SELECT 列1, 列2, 列3
则可以仅返回指定列,这种操作称为投影。 - SELECT语句可以对结果集的列进行重命名。
SELECT id, score points, name FROM students WHERE gender = 'M
- 使用
- 排序
- SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
- 分页查询
- 使用SELECT查询时,如果结果集数据量很大,比如几万行数据,放在一个页面显示的话数据量太大,不如分页显示,每次显示100条。要实现分页功能,实际上就是从结果集中显示第1~100条记录作为第1页,显示第101~200条记录作为第2页,以此类推。
- SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;把结果集分页,每页3条记录。要获取第1页的记录,可以使用
LIMIT 3 OFFSET 0,对结果集从0号记录开始,最多取3条
- SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;跳过第一页
- OFFSET计算公式为
pageSize * (pageIndex - 1)
- OFFSET超过了查询的最大数量并不会报错,而是得到一个空的结果集。
-
OFFSET
是可选的,如果只写LIMIT 15
,那么相当于LIMIT 15 OFFSET 0
。在MySQL中,
LIMIT 15 OFFSET 30
还可以简写成LIMIT 30, 15
。使用
LIMIT <M> OFFSET <N>
分页时,随着N
越来越大,查询效率也会越来越低。
- 聚合查询
- 对于统计总数、平均数这类计算,SQL提供了专门的聚合函数,使用聚合函数进行查询,就是聚合查询
-
SELECT COUNT(*) FROM students; SELECT COUNT(*) num FROM students;~重命名COUNT(*)
表示查询所有列的行数,要注意聚合的计算结果虽然是一个数字,但查询的结果仍然是一个二维表,只是这个二维表只有一行一列,并且列名是COUNT(*)
。通常,使用聚合查询时,我们应该给列名设置一个别名,便于处理结果: - 如果是字符类型,
MAX()
和MIN()
会返回排序最后和排序最前的字符 - 分组聚合
- 如果我们要统计一班的学生数量可以用
SELECT COUNT(*) num FROM students WHERE class_id = 1;
。如果要继续统计二班、三班的学生数量,难道必须不断修改WHERE
条件来执行SELECT
语句吗?SELECT COUNT(*) num FROM students GROUP BY class_id;
- SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
- 如果我们要统计一班的学生数量可以用
- 多表查询(笛卡尔查询)
- SELECT * FROM students, classes; 是
students
表和classes
表的“乘积”,即students
表的每一行与classes
表的每一行都两两拼在一起返回。结果集的列数是students
表和classes
表的列数之和,行数是students
表和classes
表的行数之积。 - 避免列名重复,利用投影查询的“设置列的别名” SELECT students.id sid, students.name, students.gender, students.score, classes.id cid, classes.name cnameFROM students, classes;
- SQL还允许给表设置一个别名,让我们在投影查询中引用起来稍微简洁一点: 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 * FROM students, classes; 是
- 连接查询
- JOIN查询需要先确定主表,然后把另一个表的数据“附加”到结果集上; 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; - right/left/full outer join、inner join,如SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;
- JOIN查询需要先确定主表,然后把另一个表的数据“附加”到结果集上; SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
- 虽然
- 修改数据
- 增 INSERT INTO students (class_id, name, gender, score) VALUES (1, '大宝', 'M', 87),(2, '二宝', 'M', 81);SELECT * FROM students;
- 更新
- UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7; SELECT * FROM students;
- UPDATE students SET score=score+10 WHERE score<80;
- 删除
- DELETE FROM students WHERE id=999;
- 其它实用语句
-
创建数据库mysql> CREATE DATABASE test;
删除数据库mysql> DROP DATABASE test;
切换数据库mysql> USE test;
列出当前数据库的所有表mysql> SHOW TABLES;
查看表结构mysql> DESC students;删除表mysql> DROP TABLE students;
修改表
增加列ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
修改列ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
删除列ALTER TABLE students DROP COLUMN birthday;EXIT仅仅断开了客户端和服务器的连接,MySQL服务器仍然继续运行。 -
插入或替换:
若id=1的记录不存在,REPLACE语句将插入新记录,否则,当前id=1的记录将被删除,然后再插入新记录。
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99); -
插入或更新:
若id=1的记录不存在,INSERT语句将插入新记录,否则,当前id=1的记录将被更新,更新的字段由UPDATE指定。
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99; -
插入或忽略:
若id=1的记录不存在,INSERT语句将插入新记录,否则,不执行任何操作。
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99); -
快照:
即复制一份当前表的数据到一个新表
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;
-
-
事务
-
ACID
-