SQL使用教程

关系模型

主键

不可重复性，采用于任何业务无关的字段为主键 如：数值id（1，2，3…） BIGINT NOT NULL AUTO_INCREMENT类型，主键也不应该允许NULL

1.自增整数类型：数据库会在插入数据自动为每一个记录分配一个自增整数，无需自己预习生成主键

2.全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57

GUID算法通过网卡MAC地址，时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分语言内置了GUID算法，可自己预算出主键

联合主键

同时拥有两或更多字段设置为主键，联合主键允许一列有重复，只要不是所有主键列重复即可

id_num	id_type	other columns…
1	A	…
2	A	…
2	B	…

如把上述的id_num和id_type作为联合主键，上面的三条记录都是允许的

外键

本表通过xxx字段，可以把数据与另一张表关联起来，这种列称为外键

外键通过定义外键约束实现

# 添加外键
ALTER TABLE students #打开students表
ADD CONSTRAINT fk_class_id #外键约束的名称`fk_class_id`可以任意
FOREIGN KEY (class_id) #指定了`class_id`列作为外键
REFERENCES classes (id);#指定了这个外键将关联到`classes`表的`id`列（即`classes`表的主键）

# 删除外键
ALTER TABLE students #打开students表
DROP FOREIGN KEY fk_class_id; #删除fk_class_id 外键（并没有删除外键列，仅删除关联）
DROP COLUMN XXX #删除列

多对多

指通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，形成多对多关系

teachers表：

id	name
1	张老师
2	王老师
3	李老师
4	赵老师

classes表：

id	name
1	一班
2	二班

中间表teacher_class关联两个一对多关系：

id	teacher_id	class_id
1	1	1
2	1	2
3	2	1
4	2	2
5	3	1
6	4	2

通过中间表teacher_class可知teachers到classes的关系：

• id=1的张老师对应id=1,2的一班和二班；
• id=2的王老师对应id=1,2的一班和二班；
• id=3的李老师对应id=1的一班；
• id=4的赵老师对应id=2的二班。

同理可知classes到teachers的关系：

• id=1的一班对应id=1,2,3的张老师、王老师和李老师；
• id=2的二班对应id=1,2,4的张老师、王老师和赵老师；

因此，通过中间表，我们就定义了一个“多对多”关系

一对一

指一个表的记录对应另一个表的唯一一个记录

如students表的学生的联系方式填入另一个contacts，可得到一对一关系

id	student_id	mobile
1	1	135xxxx6300
2	2	138xxxx2209
3	5	139xxxx8086

索引

索引可以在使用数据库的过程中逐步优化，加快查询速度，对于用户，应用程序是否创建索引使用关系数据库没有任何区别

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小红	F	95

如经常根据score列进行查询，可对score列创建索引

ALTER TABLE students # 打开students表
ADD INDEX idx_score (score); #创建score列索引为idx_score(索引名称任意)
ADD INDEX idx_name_score (name, score); #如索引有多列，可在括号里依次写入

[]: 索引的效率取决于索引列的值是否散列，即该列的值越互不相同，索引效率越高，反之，如上图gender列 “，大约一半是m，另一半是f，该列索引没有意义”

唯一索引

假设students表的name不能重复（唯一性)

ALTER TABLE students # 打开students表
ADD UNIQUE INDEX uni_name (name); # 添加唯一索引，并创建索引
ADD CONSTRAINT uni_name UNIQUE (name); # 添加唯一索引，不创建唯一索引（name列没有索引，但具有唯一性保证）

查询数据

基本查询

打开数据库

USE <数据库名>;

查看数据表

SELECT * FROM <表名>;

条件查询

查看分数在80分以上的学生

SELECT * FROM <表名> WHERE <列名> >= 80;

查看分数80分而且是男生, AND表达同时满足条件1和条件2

SELECT * FROM <表名> WHERE <列名> >= 80 AND <l列名> = 'M';（<条件表达式>）

查看满足条件1或满足条件2 (OR 或者)

SELECT * FROM <表名> WHERE <列名> >= 80 OR <列名> = ‘M’;

查看不满足该条件的记录 （NOT(条件)）

SELECT * FROM <表名> WHERE NOT <列名> = 2;

查看满足多个条件的记录(分数小于80或者大于90，而且是男)

SELECT * FROM <> WHERE (<列名> < 80 OR <列名> > 90) AND <列名> = 'M';

常用的条件表达式

条件	表达式举例1	表达式举例2	说明
使用=判断相等	score = 80	name = ‘abc’	字符串需要用单引号括起来
使用>判断大于	score > 80	name > ‘abc’	字符串比较根据ASCII码，中文字符比较根据数据库设置
使用>=判断大于或相等	score >= 80	name >= ‘abc’
使用<判断小于	score < 80	name <= ‘abc’
使用<=判断小于或相等	score <= 80	name <= ‘abc’
使用<>判断不相等	score <> 80	name <> ‘abc’
使用LIKE判断相似	name LIKE ‘ab%’	name LIKE ‘%bc%’	%表示任意字符，例如’ab%‘将匹配’ab’，‘abc’，‘abcd’

投影查询

^: 只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询，
^: 使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。SELECT语句可以对结果集的列进行重命名

从students表返回id，score，name

SELECT id, score, name, FROM students;

从students表返回id，score重命名为points，name

SELECT id, score points, name FROM students;

投影查询并WHERE条件 (从students表返回id，score重命名为points，name,并限制只有男的)

SELECT id， score points， name FROM students WHERE gender = ‘M’;

排序

默认排序规则是ASC ‘升序’，从小到大

按score(分数)从低到高(ORDER BY 从低到高)

SELECT id，name，gender，score FROM students ORDER BY score

按score从高到低(ORDER BY <列名> DESC;)

SELECT id, name, gender, score FROM students ORDER BY score DESC;

如score有相同的数据(使用ORDER BY s