基本数据类型
整数：可选择unsigned修饰
intyint 8位 （-128 – 127）
smallint 16位 （-32768 – 32767）
mediumint 24位 （-8388608 – 8388607）
int 32位 大约正负21亿
bigint 64位
实数（带有小数点）：使用标准的浮点运算进行近似计算
float 4个字节
double 8个字节
decimal 最多允许65个数字
示例：decimal(5,2)，说明：5位长度，2位小数精度，如果整数部分+2位小数超长，则报错，如果只是小数部分超出2两位，则四舍五入到两位小数
字符串
char：定长：msql根据定义字符串的长度一次分配足够的空间
适用场景：较短的字符串，且所有值接近同一长度
varchar 变长字符串
- 比定长类型节约空间
- 但是ROW_FOMAT=FIXED每行使用定长
- 适用场景：字符串的最大长度比评估长度大很多，列的更新较少
- 缺点：频繁修改，且字符串的长度变化大时，可能出现页分 裂
- 不要盲目的给过大的长度
- 在临时表或排序时可能遭遇最大长度分配内存问题
Text、Blob
1.都为存放很大的数据而设计
2.与其他数据不同，都作为独立的对象存储
3.当值太大时，使用外部存储区存储，每行只要使用1-4字节存放一个指针
text存储字符数据：
- tinytext
- smalltext
- text
- mediumtext
- longtext
Blob存储二进制数据：
- tinyblob
- smallblob
- blob
- mediumblob
- longblob
日期时间
datetime
- 精度：秒
- 与时区无关，8个字节存储空间
- 范围：1001 至 9999 年
timestamp
- 保存1970年1月1日午夜以来的秒数
- 占用4个字节存储空间
- 范围：1970年 至 2038年
- 与时区有关
- 默认为NOT NULL
- 通常尽量使用timestamp
- 精度：秒
date
time
选择标识符
- 用来进行关联操作
- 在其他表中作为外键
- 整型通常是标识列的最好选择
- 相关的表中使用相同的数据类型
- 尽量避免字符串作为标识列，尤其是随机生成的字符串，（如：uuid）导致insert与select都很慢
- 插入值被随机写到索引的不同位置，insert慢，容易导致页分 裂，磁盘随机读取
- 逻辑上相邻的行被分布在磁盘和内存的不同地方，select慢
- 使mysql查询缓存失效
- 如果需要存储uuid，则应将“-”去除
（插入值被随机写到索引的不同位置，insert慢，容易导致页分 裂，磁盘随机读取
逻辑上相邻的行被分布在磁盘和内存的不同地方，select慢 使mysql查询缓存失效 如果需要存储uuid，则应将“-”去除）
数据库命令
创建数据库：
![]()
t_course课程表 t_score成绩表
![]()
1）查询" 1 "课程比" 2 "课程成绩高的学生的信息及课程分数
SELECT stu.sid,stu.sname,stu.ssex,c1.cid, c1.score, c2.cid, c2.score
FROM t_student stu
INNER JOIN(SELECT t1.sid, t1.cid, t1.score FROM t_score t1 WHERE t1.cid = 1 ) c1 ON stu.sid = c1.sid
INNER JOIN(SELECT t2.sid, t2.cid, t2.score FROM t_score t2 WHERE t2.cid = 2) c2 ON stu.sid = c2.sid
WHERE c1.score > c2.score
2）查询同时选修" 1 "课程和" 2 "课程的学生信息
//方法一
SELECT stu.sid,stu.sname,stu.ssex,c1.cid, c1.score, c2.cid, c2.score
FROM t_student stu
INNER JOIN(SELECT t1.sid, t1.cid, t1.score FROM t_score t1 WHERE t1.cid = 1 ) c1 ON stu.sid = c1.sid
INNER JOIN(SELECT t2.sid, t2.cid, t2.score FROM t_score t2 WHERE t2.cid = 2) c2 ON stu.sid = c2.sid
//方法二
SELECT stu.`sid`,stu.`sname`, stu.`ssex`, tmp.c1num, tmp.c2num FROM t_student stu INNER JOIN
(
SELECT t.`sid`,
SUM(CASE WHEN t.cid = 1 THEN t.`score` ELSE 0 END) c1num,
SUM(CASE WHEN t.cid = 2 THEN t.`score` ELSE 0 END) c2num FROM t_score t GROUP BY t.`sid`
) tmp ON stu.sid = tmp.sid AND tmp.c1num > 0 AND tmp.c2num > 0;
3）查询选修" 1 "课程但没有选修" 2 "课程的情况
SELECT stu.* FROM t_student stu
WHERE stu.sid IN(SELECT t1.sid FROM t_score t1 WHERE t1.cid = 1)
AND stu.sid NOT IN (SELECT t1.sid FROM t_score t1 WHERE t1.cid = 2)
SELECT stu.`sid`,stu.`sname`, stu.`ssex`, tmp.c1num, tmp.c2num FROM t_student stu INNER JOIN
(
SELECT t.`sid`,
SUM(CASE WHEN t.cid = 1 THEN t.`score` ELSE 0 END) c1num,
SUM(CASE WHEN t.cid = 2 THEN t.`score` ELSE 0 END) c2num FROM t_score t GROUP BY t.`sid`
) tmp ON stu.sid = tmp.sid AND tmp.c1num > 0 AND tmp.c2num = 0;
4）查询不存在" 1 "课程但存在" 2 "课程的情况
SELECT t1.sid,t1.cid,t1.score
FROM t_score t1
WHERE t1.cid = 2 AND t1.sid NOT IN (SELECT t2.sid FROM t_score t2 WHERE t2.cid = 1);
查询各科成绩最高分、最低分和平均分：
1）显示列：课程ID，课程名称，最高分，最低分，平均分，选修人数，及格率，中等率，优良率 2）优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 3）要求查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT t2.cid ‘课程ID’,
t2.cname ‘课程名称’,
MAX(t1.score) ‘最高分’,
MIN(t1.score) ‘最低分’,
ROUND(AVG(t1.score), 2) ‘平均分’,
COUNT(t1.sid) ‘选修人数’,
ROUND(SUM(CASE WHEN t1.score >= 60 THEN 1 ELSE 0 END) / COUNT(t1.sid), 2) ‘及格率’,
ROUND(SUM(CASE WHEN t1.score >=70 AND t1.score < 80 THEN 1 ELSE 0 END)/COUNT(t1.sid),2) ‘中等率’,
ROUND(SUM(CASE WHEN t1.score >=80 AND t1.score < 90 THEN 1 ELSE 0 END)/COUNT(t1.sid),2) ‘优良率’,
ROUND(SUM(CASE WHEN t1.score >= 90 THEN 1 ELSE 0 END)/COUNT(t1.sid), 2) ‘优秀率’
FROM t_score t1
INNER JOIN t_course t2 ON t1.cid = t2.cid
GROUP BY t2.cid, t2.cname
ORDER BY COUNT(t1.sid) DESC, t2.cid ASC;
到此这篇关于Mysql数据类型与CRUD的文章就介绍到这了,更多相关Mysql数据类型内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
