Mysql数据类型与CRUD操作详细讲解

目录

基本数据类型

整数:可选择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) '选修人数',
       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;
原文地址:https://blog.csdn.net/qq_64001795/article/details/125939431
22人参与, 0条评论 登录后显示评论回复

你需要登录后才能评论 登录/ 注册