mySQL查询操作
1 基础知识
1.1 数据库模型
- 层次模型
- 网状模型
- 关系模型
- 二维表格,行号+列号
- 最常用
1.2 安装mySQL
Linux系统上:
sudo apt-get update
sudo apt-get install mysql-server
安装时会自动创建一个root用户,输入用户密码即可。
进入、退出mySQL:
# 之后输入root用户口令(密码)
mysql -u root -p
# 利用 SHOW 查看已有数据库
SHOW DATABASES;
# 退出
exit;
1.3 数据类型
# 整型
INT 整型 4字节整数类型,范围约+/-21亿
BIGINT 长整型 8字节整数类型,范围约+/-922亿亿
# 浮点型
REAL 浮点型 4字节浮点数,范围约+/-1038
DOUBLE 浮点型 8字节浮点数,范围约+/-10308
DECIMAL(M,N) 高精度小数 由用户指定精度的小数,
例如,DECIMAL(20,10)表示一共20位,其中小数10位,通常用于财务计算。
# 字符串型
CHAR(N) 定长字符串 存储指定长度的字符串,
例如,CHAR(100)总是存储100个字符的字符串。
VARCHAR(N) 变长字符串 存储可变长度的字符串,
例如,VARCHAR(100)可以存储0~100个字符的字符串。
# 布尔型
BOOLEAN 布尔类型 存储True或者False
# 时间、日期
DATE 日期类型 存储日期,例如,2018-06-22
TIME 时间类型 存储时间,例如,12:20:59
DATETIME 日期和时间类型 存储日期+时间,例如,2018-06-22 12:20:59
1.4 关系模型
- 主键:
- 能唯一确定一条记录的字段;
- 确定之后最好不要修改;
- 主键选取基本原则:不使用任何业务相关的字段(身份证号,邮箱等都不行)
- 常见主键(id)字段:
- 自增整数类型(BIGINT NOT NULL AUTO_INCREMENT)
- 全局唯一GUID类型(字符串)
- 联合主键:
- 多个字段能唯一确定一条记录,称之为联合主键;
- 外键:
- 可以把数据与另一张表关联起来,这种称之为外键;
- 在students 表中 class_id 就是一个外键;
- 索引:
- 是关系数据库中对某一列或多个列的值进行预排序的数据结构;
- 索引目的是加快查询速度;
- 索引搜索出的记录越少,其效率越高;
- 索引越多,增加、删除记录的速度越慢;
1.5 语法特点!!
- SQL语法关键字不区分大小写;
- 一条语句以 “;” 结尾,不以此符号结尾的都不会被执行;
- 数据库名、表名、列名、索引名区分大小写(有些类型数据库会不区分);
- 所以在SQL中可以用以下规范:
- 关键字总是大写;
- 表名、列名均用小写;
1.6 命名规范
2 查询操作
2.1 SELECT * FROM <表>
- " * " 代表查询输出所有列;
- SELECT相当于输出命令, SELECT 1 可以用来测试数据库是否连接;
- 查询students表的所有数据
SELECT *
FROM students;
- 查询students表的name和score数据
SELECT name, score
FROM students;
- 查询students表的id,score,name并将score重命名成points
SELECT name, score points
FROM students;
2.2 WHERE <条件>
NOT、 AND、 OR优先级递减
- 查询成绩大于等于80的人
SELECT * FROM students
WHERE score >= 80;
- 查询成绩[80,90]的女生
SELECT * FROM students
WHERE gender = 'F' AND
score BETWEEN 80 AND 90;
2.3 ORDER BY <索引> (不能用 " * " )
使用索引时注意事项:(SELECT 后面要加上排序的列名称)
// 这种方式不会排序!!
SELECT *
FROM students
WHERE gender = 'F'
ORDER BY score;
// 这种方式可以排序,但是会输出两列成绩!!
SELECT *, score
FROM students
WHERE gender = 'F'
ORDER BY score;
结果如下:
id class_id name gender score score
4 1 小米 F 73 73
5 2 小白 F 81 81
10 3 小丽 F 88 88
8 3 小新 F 91 91
2 1 小红 F 95 95
- 查询女生的成绩,从低到高排序
SELECT id, class_id, name, gender, score
FROM students
WHERE gender = 'F'
ORDER BY score;
- 查询1班的成绩,从高到低排列
SELECT id, class_id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
- 查询所有人成绩,每个班级成绩按照降序排序
SELECT id, class_id, name, gender, score
FROM students
ORDER BY class_id, score DESC;
结果如下:
id class_id name gender score
2 1 小红 F 95
1 1 小明 M 90
3 1 小军 M 88
4 1 小米 F 73
7 2 小林 M 85
5 2 小白 F 81
6 2 小兵 M 55
8 3 小新 F 91
9 3 小王 M 89
10 3 小丽 F 88
2.4 LIMIT n OFFSET m
- 查询所有人成绩,分页查询,一页放5个数据,从第四名开始显示;
SELECT * FROM students
LIMIT 5 OFFSET 3;
2.5 COUNT、SUM、AVG、MAX、MIN
- 查询成绩大于等于90分的人数
SELECT COUNT(*)
FROM students
WHERE score >= 90;
- 查询1班的总分、平均分、最高分、最低分
SELECT SUM(score) sum_1,
AVG(score) avg_1,
MAX(score) max_1,
MIN(score) min_1
FROM students
WHERE class_id = 1;
结果如下:
sum_1 avg_1 max_1 min_1
346 86.5 95 73
2.6 GROUP BY <索引>
- 查询每个班的总分、平均分向下 / 向上取整、最高分、最低分,并输出班级名;
SELECT class_id,
SUM(score),
// 向下取整; 向上取整为 CEILING(...)
FLOOR(AVG(score)),
MAX(score),
MIN(score)
FROM students
GROUP BY class_id;
结果如下:
class_id SUM(score) AVG(score) MAX(score) MIN(score)
1 346 86 95 73
2 221 73 85 55
3 268 89 91 88
- 查询每个班男生女生的平均分,并按照班级、男女排序
SELECT class_id, gender, AVG(score)
FROM students
GROUP BY class_id, gender
ORDER BY class_id, gender;
结果如下:
class_id gender AVG(score)
1 F 84
1 M 89
2 F 81
2 M 70
3 F 89.5
3 M 89
2.7 SELECT * FROM <表1>, <表2>(多表查询)
多表查询又称笛卡尔查询
查询的结果是<表1><表2>每一行两两拼接在一起的结果。
所以两个100条记录的表,查询后有1w条记录;
主要学习如何给表起别名
- 查询students的id、name、gender、score和classes的id、name并重命名
// 为了节省空间,这里只显示90分以上的
SELECT s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name
FROM students s, classes c
WHERE score > 90;
输出如下:
sid name gender score cid
2 一班 F 95 1
2 二班 F 95 2
2 三班 F 95 3
2 四班 F 95 4
8 一班 F 91 1
8 二班 F 91 2
8 三班 F 91 3
8 四班 F 91 4
2.8 INNER JOIN <表> ON <条件> (OUTER、FULL连接查询)
连接查询作用是把两个表有选择的连接到一起
eg:students表中班级是1、2、3;但是我们想显示classes表中1、2、3对应的班级一、班级二、班级三,就可以用到连接查询;
连接查询分四类:(左表 = 右表)
-
INNER JOIN:
- 只显示两个表公共的部分(交集)
-
LEFT OUTER JOIN:
- 显示左表所有的内容,在右表中无得部分用NULL填充;
-
RIGHT OUTER JOIN:
- 显示右表所有的内容,左表无的用NULL填充;
-
FULL OUTER JOIN:
- 显示两个表的并集,分共有记录部分用NULL填充;
-
查询上面的例子,用class的name替代students的class_id。
注意两个表是如何连接的!!
SELECT s.id, s.name, c.name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id
WHERE score > 80;
结果如下:
id name gender score
1 一班 M 90
2 一班 F 95
3 一班 M 88
5 二班 F 81
7 二班 M 85
8 三班 F 91
9 三班 M 89
10 三班 F 88
- 看一下full outer join的结果(注意最后的where条件)
SELECT s.id, s.name, c.name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id
WHERE c.id > 2;
结果如下:
id name gender score
8 三班 F 91
9 三班 M 89
10 三班 F 88
NULL 四班 NULL NULL