mysql 史上最详细表单案例

青旅半醒 2023-02-20 07:37 6阅读 0赞

# mysql 表单案例 #

## 建表 ##

CREATE TABLE student2(  
SID INT(2)AUTO\_INCREMENT PRIMARY KEY NOT NULL,  
Sname VARCHAR(10)NOT NULL,  
Sage DATETIME NOT NULL,  
Ssex ENUM(‘男’,‘女’)NOT NULL COMMENT’性别’  
)DEFAULT CHARSET=utf8

## 添加数据 ##

INSERT INTO student2(SID,Sname,Sage,Ssex)VALUES(01,“赵雷”,‘1990-01-01 00:00:00’,‘男’),(02,“钱电”,‘1990-12-21 00:00:00’,‘男’),(03,“孙风”,‘1990-05-20 00:00:00’,‘男’),(04,“李云”,‘1990-08-06 00:00:00’,‘男’),(05,“周梅”,‘1991-12-01 00:00:00’,‘女’),(06,“吴兰”,‘1992-03-01 00:00:00’,‘女’),(07,“郑竹”,‘1989-07-01 00:00:00’,‘女’),(08,“王橘”,‘1990-01-20 00:00:00’,‘女’);

#### 查询表单中所有数据 看看是否创建添加成功 ####

SELECT \* FROM student3;  
![在这里插入图片描述][20200624214910186.png]

## 二、修改数据 ##

### 修改需求1： 把sid=01的人名字修改为 “韩梅梅” ###

UPDATE student2 SET Sname = ‘韩梅梅’ WHERE SID = 1;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70]

### 把出生日期在90年之前的，性别全部改为 “男” ###

UPDATE student2 SET Ssex= ‘男’ WHERE Sage<‘1990-01-01 00:00:00’  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 1]

## 三、删除数据 ##

– 1.删除需求： 把出生日期在90年之前的删除  
DELETE FROM student2 WHERE CAST(Sage AS DATETIME)<‘1990-01-01 00:00:00’  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 2]

# 四、简单查询student2student2student2 #

#### – 1）查询所有的学生数据？ ####

SELECT \* FROM student2;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 3]

#### – 2）查询学生性别，且使用distinct去重 ####

SELECT DISTINCT Ssex FROM student2;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 4]

#### – 3）查询出在1990年之后的出生的学生？ ####

SELECT \* FROM student2 WHERE Sage>‘1990’;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 5]

#### – 4）查询出在1990年之后的出生且性别为男的学生？ ####

SELECT \* FROM student2 WHERE Sage>‘1990’ AND Ssex=‘男’;  
![在这里插入图片描述][20200624215235286.png]

#### – 5）查询出在1990年之后的出生或性别为男的学生？ ####

SELECT \* FROM student2 WHERE Sage>‘1990’ || Ssex=‘男’;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 6]

#### – 6）查询出在1990年之后的出生且性别为男的学生，并根据出生日期从小到大显示？ ####

SELECT \* FROM student2 WHERE Ssex = ‘男’ ORDER BY Sage;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 7]

#### – 7）查询学生总数量？ ####

SELECT sid, COUNT(\*) FROM student2;  
![在这里插入图片描述][20200624215310903.png]

#### – 8）统计学生性别为男的学生数量？ ####

SELECT SID, COUNT(\*) FROM student2 WHERE Ssex=‘男’;  
![在这里插入图片描述][20200624215322960.png]

#### – 9）查询姓赵的学生？ ####

SELECT \* FROM student2 WHERE Sname LIKE ‘赵\*’;  
![在这里插入图片描述][2020062421533794.png]

#### – 10）查询sid=01、06、03 的学生？ ####

SELECT \* FROM student2 WHERE SID IN(01,03,06);  
![在这里插入图片描述][20200624215347199.png]

#### – 11）查询出生日期在 90年 到 91年之间的学生？ ####

SELECT \* FROM student2 WHERE Sage BETWEEN ‘1990-01-00’ AND ‘1991-01-00’;  
![在这里插入图片描述][20200624215359926.png]  
[点击查看 上一篇表单查询比这篇更基础哦][Link 1]  
内容就到这里拉~~我是Yama 再见啦

[20200624214910186.png]: https://img-blog.csdnimg.cn/20200624214910186.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200624215004374.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200624215031284.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200624215103980.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200624215127818.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020062421514967.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200624215222504.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[20200624215235286.png]: https://img-blog.csdnimg.cn/20200624215235286.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200624215247492.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200624215300259.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NzU2NDczNw==,size_16,color_FFFFFF,t_70
[20200624215310903.png]: https://img-blog.csdnimg.cn/20200624215310903.png
[20200624215322960.png]: https://img-blog.csdnimg.cn/20200624215322960.png
[2020062421533794.png]: https://img-blog.csdnimg.cn/2020062421533794.png
[20200624215347199.png]: https://img-blog.csdnimg.cn/20200624215347199.png
[20200624215359926.png]: https://img-blog.csdnimg.cn/20200624215359926.png
[Link 1]: https://blog.csdn.net/weixin_47564737/article/details/106932911