您现在的位置是：网站首页> 编程资料编程资料
Mysql数据类型与CRUD操作详细讲解_Mysql_
2023-05-26
356人已围观
简介 Mysql数据类型与CRUD操作详细讲解_Mysql_
基本数据类型
整数：可选择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
- yyyy-MM-dd
time
- HH:mm:ss
选择标识符
- 用来进行关联操作
- 在其他表中作为外键
- 整型通常是标识列的最好选择
- 相关的表中使用相同的数据类型
- 尽量避免字符串作为标识列，尤其是随机生成的字符串，（如：uuid）导致insert与select都很慢
- 插入值被随机写到索引的不同位置，insert慢，容易导致页分 裂，磁盘随机读取
- 逻辑上相邻的行被分布在磁盘和内存的不同地方，select慢
- 使mysql查询缓存失效
- 如果需要存储uuid，则应将“-”去除
（插入值被随机写到索引的不同位置，insert慢，容易导致页分 裂，磁盘随机读取
逻辑上相邻的行被分布在磁盘和内存的不同地方，select慢 使mysql查询缓存失效 如果需要存储uuid，则应将“-”去除）
数据库命令
创建数据库：
create database 数据库名 create database if not exists 数据库名 default charset utf8 collate utf8_general_ci; //默认的数据库编码集：utf8 //collate表示校验规则为utf8_general_ci //常用排序类型 //utf8_general_cs（区分大小写） //utf8_genera_ci（不区分大小写）
查看所有数据库：
show databases
删除数据库：
drop database 数据库名
注意：删除数据库是一个危险操作，如要删除建议先备份
建表与约束
建表
命令格式：
create table 表名称(
列名称1 数据类型not null,
列名称2 数据类型,
列名称3 数据类型,
unique(列名称1[,列名称2,...,列名称N])
)
示例：
create table t_student ( sid int not null comment '学号', sname varchar(60) not null comment '姓名', sex tinyint not null default 1 comment '性别：1男， 2女', age tinyint not null comment ' 年龄', icard varchar(18) not null comment '身份证，唯一约束', primary key (sid), unique key AK_Key_2 (icard) ) comment '学生信息表';
约束
主键约束：
primarykey
增加主键（alter table 表名 add primary key(主键名称)）
删除主键（alerttable 表名dropprimarykey） 非空约束：
sid int not null comment'学号',
外键约束：
create table t_score ( id int not null comment'记录流水号', sid int not null comment'学号', cid int not null comment'课程ID', score float comment'成绩', primary key(id), foreign key(sid) references t_student (sid) on delete restrict on update redtrict , unique key ak_key_2(sid, cid) ); //说明： sid为本表的外键，关联t_student表中的的sid主键，on delete restrict on update redtrict说明在本表有数据的情况下，主表的关联键不能删除或更新。
增加主键（alerttable 表名 add foreign key(外键名称) references 主表名称(主键名称)）
删除主键（alerttable 表名drop foreign key约束名）
唯一约束：uniquekey约束名 (字段)
创建唯一约束：alert table 表名 add unique(列名称1[,列名称2,..])
create unique index UserNameIndex on 't_user' ('username')
删除唯一约束：alerttable 表名dropindex 唯一约束缩影名称
默认值约束：default
基本数据操作（CRUD）
数据准备
create database db_t281 use db_t281 -- 1.学生表-t_student -- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别 create table t_student ( sid int not null auto_increment comment '学号', sname varchar(40) not null comment '名称', birthday date not null comment '年龄', ssex tinyint not null default 1 comment '1男，2女', primary key (sid) ); INSERT INTO t_student VALUES(1, '赵雷' , '1990-01-01' , 1); INSERT INTO t_student VALUES(2 , '钱电' , '1990-12-21' , 1); INSERT INTO t_student VALUES(3 , '孙风' , '1990-12-20' , 1); INSERT INTO t_student VALUES(4 , '李云' , '1990-12-06' , 1); INSERT INTO t_student VALUES(5 , '周梅' , '1991-12-01' , 2); INSERT INTO t_student VALUES(6 , '吴兰' , '1992-01-01' , 2); INSERT INTO t_student VALUES(7 , '郑竹' , '1989-01-01' , 2); INSERT INTO t_student VALUES(9 , '张三' , '2017-12-20' , 2); INSERT INTO t_student VALUES(10 , '李四' , '2017-12-25' , 2); INSERT INTO t_student VALUES(11 , '李四' , '2012-06-06' , 2); INSERT INTO t_student VALUES(12 , '赵六' , '2013-06-13' , 2); INSERT INTO t_student VALUES(13 , '孙七' , '2014-06-01' , 2); -- 2.教师表-t_teacher -- tid 教师编号,tname 教师名称 CREATE TABLE t_teacher ( tid INT NOT NULL AUTO_INCREMENT COMMENT '教师ID', tname VARCHAR(40) NOT NULL COMMENT '教师名称', PRIMARY KEY (tid) ); INSERT INTO t_teacher VALUES(1 , '张五哥'); INSERT INTO t_teacher VALUES(2 , '李卫'); INSERT INTO t_teacher VALUES(3 , '年羹尧'); -- 3.课程表-t_course -- cid 课程编号,cname 课程名称,tid 教师名称 CREATE TABLE t_course ( cid INT NOT NULL COMMENT '课程ID', cname VARCHAR(50) COMMENT '课程名称', tid INT COMMENT '教师id', PRIMARY KEY (cid) ); INSERT INTO t_course VALUES(1 , '语文' , 2); INSERT INTO t_course VALUES(2 , '数学' , 1); INSERT INTO t_course VALUES(3 , '英语' , 3); -- 4.成绩表-t_score -- sid 学生编号,cid 课程编号,score 成绩 CREATE TABLE t_score ( sid INT NOT NULL COMMENT '学号，外键', cid INT NOT NULL COMMENT '课程id', score decimal(5,2) COMMENT '成绩', UNIQUE KEY ak_key_sid_cid (sid, cid) ); INSERT INTO t_score VALUES(1 , 1 , 80); INSERT INTO t_score VALUES(1 , 2 , 90); INSERT INTO t_score VALUES(1 , 3 , 99); INSERT INTO t_score VALUES(2 , 1 , 70); INSERT INTO t_score VALUES(2 , 2 , 60); INSERT INTO t_score VALUES(2 , 3 , 80); INSERT INTO t_score VALUES(3 , 1 , 80); INSERT INTO t_score VALUES(3 , 2 , 80); INSERT INTO t_score VALUES(3 , 3 , 80); INSERT INTO t_score VALUES(4 , 1 , 50); INSERT INTO t_score VALUES(4 , 2 , 30); INSERT INTO t_score VALUES(4 , 3 , 20); INSERT INTO t_score VALUES(5 , 1 , 76); INSERT INTO t_score VALUES(5 , 2 , 87); INSERT INTO t_score VALUES(6 , 1 , 31); INSERT INTO t_score VALUES(6 , 3 , 34); INSERT INTO t_score VALUES(7 , 2 , 89); INSERT INTO t_score VALUES(7 , 3 , 98); select * from t_student; select * from t_teacher; select * from t_course; select * from t_score;
数据表如下：
t_student学生表 t_teacher教师表
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
相关内容
- Windows下mysql 5.7 设置区分大小写(敏感),设置默认编码 utf8mb4_Mysql_
- 深入了解MySQL中的慢查询_Mysql_
- MySQL+Redis缓存+Gearman共同构建数据库缓存的方法_Mysql_
- MySQL Workbench安装及使用详解_Mysql_
- MySQL实现分词搜索(FULLTEXT)的方法_Mysql_
- Windows中Mysql启动失败的完美解决方案_Mysql_
- windows下安装MySQL详细教程_Mysql_
- MySQL登录、访问及退出操作实战指南_Mysql_
- MySQL中explain使用快速查询手册_Mysql_
- mysql sql字符串截取函数详解_Mysql_
点击排行
本栏推荐
