关系模型:表中的每一行数据被称为一条记录,一条记录由多个字段组成。
- 一对多
- 多对一
- 多对多 (通过中间表,关联两个一对多的表)
- 一对一
CMD进入数据库
mysql -hlocalhost -uroot -proot
其中**-h表示服务器名,localhost表示本地;-u为数据库用户名,root是mysql
默认用户名;-p为密码,如果设置了密码,可直接在-p**后链接输入,如:-p123456
主键
关系表中不能存在两条完全相同的记录,必须要能通过某个字段(即主键)提取出唯一的记录。
主键具有唯一性,且(最好)不可变,主键也不应该允许NULL
。同时主键的字段选择不能与业务有任何的关联,一般会自增整数类型(即id)来作为主键。
外键
外键是把数据与另一张表关联起来的列。
外键通过定义外键约束实现:
ALTERTSBLE students // 找到对应表
ADD CONSTRAINT fk_class_id // 添加约束,fk_class_id 约束名称任意,
FOREIGN KEY (class_id) // 指定class_id为外键
REFERENCES classes (id) // 将外键关联到classes 表的id列
引入外键的意义在于关系数据库可以保证无法插入无效的数据,但会降低数据库的性能
删除外键:
ALTERTABLE students
DROPFOREIGNKEY fk_class_id
索引
索引是关系数据库中对某一列或多个列的值进行预排序的数据结构,通过索引,可以让数据系统不必扫描整个表,而是直接定位到符合条件的记录。
id |
class_id |
name |
gender |
score |
1 |
1 |
小明 |
M |
90 |
2 |
1 |
小红 |
F |
67 |
3 |
1 |
小兰 |
M |
89 |
索引的效率取决于索引值是否散列,即该列的值如果越不相同效率越高,从这个角度来说,gender
列毫无意义,score
是相对合适的,我们以score
来创建索引:
ALTER TABLE students
ADD INDEX idx_score (score) // 创建了名称为idx_score,使用score列的索引,索引可以有多列,即多个搜索关键字,倘若二人分数一致,第二个索引会加以区分。
一张表可以创建多个索引,但索引会导致插入、更新、删除记录时速度变慢,因为其需要同时修改索引值。
对于主键,关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的,因为主键会保证绝对唯一。
唯一索引可以对某列形成约束,使某列的值具有唯一性:
ALTER TABLE students
ADD UNIQUE uni_name (name)
也可以只对某一列添加一个唯一约束而不创建唯一索引:
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name)
注意结尾的分号以及字符串字段的''
数据查询
基本查询
条件查询
通过WHERE
来添加筛选条件
SELECT * FROM student WHERE score >=80
SELECT * FROM <表名> WHERE <条件表达式>
SELECT * FROM students WHERE score>= 80 AND gender = "M"
SELECT * FROM students WHERE score>= 80 OR gender = "M"
SELECT * FROM students WHERE score>= 80 AND NOT gender = "M"
SELECT * FROM students WHERE score BETWEEN 60 AND 90
投影查询
条件查询的结果都是和表结构一致的,使用投影查询可以自定义返回所需要的列:
SELECT id,score FROM student
SELECT 列1, 列2, 列3 FROM ...
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ... // 取别名
排序查询
通过关键字ORDER BY
来正序,通过追加DESC
来倒序:
SELECT id,name changeName,score FROM students ORDER BY score
SELECT id,name,score FROM students ORDER BY score DESC
分页查询
分页是将结果集中“截取”出第M~N条记录,其通过LIMIT <M> OFFSET <N>
实现:
SELECT id,name,score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;
我们每pageSize
为3,上述代码为第一页。LIMIT
和OFFSET
的计算:
LIMIT:pageSize
OFFSET: pageSize * (pageIndex - 1)
获得总页数:
SELECT CEILNG(COUNT(*)/3) FROM students;
聚合查询
SQL提供了一些内置的计算方法,比如某个表共有多少条记录:
SELECT COUNT(*) FROM students;
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
返回的查询结果依旧还是个二维表,虽然其只有一行一列,且列名为COUNT(*)、boys
.
除了COUNT()
,还内置了SUM AVG MAX MIN
等
SELECT SUM(score) boys FROM students WHERE gender = 'M'
如果聚合查询的WHERE
条件没有匹配到任何行,COUNT()
会返回0,而SUM()
、AVG()
、MAX()
和MIN()
会返回NULL
;
分组
SELECT COUNT(*) num FROM students WHERE class_id = 1
上述语句可以得出一般总人数,如果想同时获取每个班的人数:
SELECT class_id,COUNT(*) num FROM students GROUP BY class_id
也可以使用多个列进行分组:
SELECT class_id,gender,COUNT(*) num FROM students GROUP BY class_id,gender
多表查询
SQL可以从多张表同时查询数据,语法为SELECT * FROM <biao1> <biao2>
。
多表查询的记录是两个表列和字段量互乘的结果;
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;
连接查询
多表查询时对多个表进行JOIN
运算,即先确定一个主表作为结果集,首先是内连接:
语法:SELECT <字段> ... FROM <主表> INNER JOIN <关联表> ON <关联关系,即某个字段相同>
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
与之对应的是外连接:
SELECT s.id,s.name,s.class_id,c.name class_name,s.gender,s.score FROM students s RIGHT OUTER JOIN classes c ON s.class_id = c.id
其中的RIGHT
关键字类似于集合的概念,即关联表全部;如果是LEFT OUTER JOIN
则结果是主表的全部数据;INNER JOIN
是二者交集,FULL OUTER JOIN
是二者全部数据。