MySql 数据库操作SQL练习（带答案）

最新推荐文章于 2024-07-05 22:42:05 发布

八宝粥大朋友

最新推荐文章于 2024-07-05 22:42:05 发布

阅读量1.8k

点赞数

分类专栏： mysql 文章标签： mysql sql

本文链接：https://blog.csdn.net/long_24k_os/article/details/109574048

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySql 数据库操作SQL练习（带答案）

I. 数据初始化

--  创建 employee 表
CREATE TABLE `employee` (
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `NAME` varchar(15)  DEFAULT NULL COMMENT '名字',
    `gender` varchar(20)  DEFAULT NULL COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '生日',
    `emtry_date` TIMESTAMP COMMENT '入职时间',
    `job` varchar(20)  DEFAULT NULL COMMENT '岗位',
    `salary` DOUBLE  DEFAULT NULL COMMENT '薪水',
    `RESUME` TEXT  DEFAULT NULL COMMENT '个人简历'
)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='员工表';

-- 添加数据
INSERT INTO employee VALUES(NULL,'zs',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO employee VALUES(NULL,'ls',NULL,NULL,NULL,NULL,NULL,NULL); 
INSERT INTO employee VALUES(NULL,'wangwu',NULL,NULL,NULL,NULL,NULL,NULL);

--  创建 exam 考试成绩 表
CREATE TABLE exam(
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    `NAME` VARCHAR(20) NOT NULL COMMENT '姓名',
    `chinese` DOUBLE COMMENT '语文',
    `math` DOUBLE COMMENT '数学',
    `english` DOUBLE COMMENT '英语'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='考试成绩';

-- 添加数据
INSERT INTO `exam` VALUES(NULL,'关羽',85,76,70);
INSERT INTO `exam` VALUES(NULL,'张飞',70,75,70);
INSERT INTO `exam` VALUES(NULL,'赵云',90,65,95);
INSERT INTO `exam` VALUES(NULL,'刘备',97,50,50);
INSERT INTO `exam` VALUES(NULL,'曹操',90,89,80);
INSERT INTO `exam` VALUES(NULL,'司马懿',90,67,65);

-- 创建 orders 订单表
CREATE TABLE orders(
    id INT COMMENT '主键ID',
    product VARCHAR(20) COMMENT '产品',
    price FLOAT COMMENT '价格'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='订单表';

-- 添加数据
INSERT INTO orders(id,product,price) VALUES(1,'电视',900);
INSERT INTO orders(id,product,price) VALUES(2,'洗衣机',100);
INSERT INTO orders(id,product,price) VALUES(3,'洗衣粉',90);
INSERT INTO orders(id,product,price) VALUES(4,'桔子',9);
INSERT INTO orders(id,product,price) VALUES(5,'洗衣粉',90);

A. 数据库操作的SQL

一.数据库的创建：

创建一个名称为 mydb1 的数据库 :
```
CREATE DATABASE mydb1;
```
创建一个使用 utf8 字符集的 mydb2 数据库。
```
 CREATE DATABASE mydb2 CHARACTER SET utf8;
```
创建一个使用 utf8 字符集，并带比较规则的 mydb3 数据库。
```
 CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_bin;
```

二.数据库的修改：

修改 mydb2 字符集为 gbk;

ALTER DATABASE mydb2 CHARACTER SET gbk;

三.数据库的删除：

删除数据库 mydb3。
```
 DROP DATABASE mydb3;
```

四.数据库查看：

查看所有数据库。
```
SHOW DATABASES;
```
查看数据库 mydb1 的字符集
```
SHOW CREATE DATABASE mydb1; 
```

B. 数据库中表操作的SQL练习

一.创建表

创建一张员工表 employee 字段类型 id 整形, NAME 字符型, gender 字符型, birthday 日期型, entry_date 日期型, job 字符型 ,salary 小数型, RESUME 文本:

CREATE TABLE employee(id INT ,
                      NAME VARCHAR(10),gender VARCHAR(20),
                      birthday DATETIME, enty_date TIMESTAMP,
                      job VARCHAR(10),salary DOUBLE,RESUME TEXT);

创建一张员工表 employee2 字段类型 id 整形, NAME 字符型, gender 字符型, birthday 日期型, entry_date 日期型, job 字符型, salary 小数型, RESUME 文本, 要求：把id 设置成主键，并且自动增长。NAME不允许为空。
```
CREATE TABLE employee2(id INT PRIMARY KEY AUTO_INCREMENT,
                       NAME VARCHAR(15),gender VARCHAR(20),birthday DATETIME,
                       entry_date TIMESTAMP,job VARCHAR(20),salary DOUBLE 
                       ,RESUME TEXT);
```

二.删除表

删除employee2表
```
DROP TABLE employee2;
```

三.数据表的结构的修改：

在上面员工表的基本上增加一个 image 列。
```
ALTER TABLE employee ADD image VARCHAR(20);
```

修改 job 列，使其长度为60。

ALTER TABLE employee MODIFY job VARCHAR(60);

删除 gender 列。
```
ALTER TABLE employee DROP gender;
```
表名改为 USER。
```
RENAME TABLE employee TO USER;
```
修改表的字符集为 utf8
```
 ALTER TABLE USER CHARACTER SET utf8;
```

列名 NAME 修改为 username

ALTER TABLE USER CHANGE NAME username VARCHAR(20);

表名改为 employee。
```
RENAME TABLE USER TO employee;
```

四.查看表结构

查看数据库内的所有表
```
SHOW TABLES;
```
查看 employee 的建表语句
```
 SHOW CREATE TABLE employee;
```
查看 employee 的表结构
```
DESC employee;
```

C. 练习题 sql练习题

一.数据库的创建：

创建一个名称为 mydb1 的数据库
```
 CREATE DATABASE mydb1;
```
创建一个使用 utf8 字符集的 mydb2 数据库。
```
 CREATE DATABASE mtdb2 CHARACTER SET utf8;
```
创建一个使用 utf8 字符集，并带比较规则的 mydb3 数据库。
```
 CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_bin;
```

二.数据库的修改：

修改 mydb2 字符集为 gbk;

ALTER DATABASE mydb2 CHARACTER SET gbk;

三.数据库的删除：

删除数据库 mydb3。
```
DROP DATABASE mydb3;
```

四.数据库查看：

查看所有数据库。
```
SHOW DATABASES;
```
查看数据库 mydb1 的字符集
```
SHOW CREATE DATABASE mydb1;
```

D. 数据库中表操作的SQL练习

一.创建表

创建一张员工表 employee 字段类型 id 整形, NAME 字符型, gender 字符型, birthday 日期型, entry_date 日期型, job 字符型 ,salary 小数型, RESUME 文本

CREATE TABLE employee(
    `id` INT,
    `NAME` VARCHAR(15),
    `gender` VARCHAR(20),
    `birthday` DATETIME,
    `emtry_date` TIMESTAMP,
    `job` VARCHAR(20),
    `salary` DOUBLE,
    `RESUME` TEXT
);

创建一张员工表employee2 字段类型 id 整形, NAME 字符型, gender 字符型, birthday 日期型, entry_date 日期型, job 字符型, salary 小数型, RESUME 文本要求：把id 设置成主键，并且自动增长。NAME不允许为空。

CREATE TABLE employee2(
    `id` INT AUTO_INCREMENT,
    `NAME` VARCHAR(15) NOT NULL,
    `gender` VARCHAR(20),
    `birthday` DATETIME,
    `emtry_date` TIMESTAMP,
    `job` VARCHAR(20),
    `salary` DOUBLE,
    `RESUME` TEXT,
	PRIMARY KEY (`id`)
)

二.删除表

删除 employee2 表
```
DROP TABLE enployee2;
```

三.数据表的结构的修改：

在上面员工表的基本上增加一个 image2 列。然后删除 image2

 ALTER TABLE employee ADD image2 VARCHAR(20); 
 ALTER TABLE employee DROP image2;

修改 job 列，使其长度为 80。

ALTER TABLE employee MODIFY job VARCHAR(80);

删除 gender 列。
```
 ALTER TABLE employee DROP gender;
```

表名改为 USER。然后改回来

 RENAME TABLE employee TO USER; 
 RENAME TABLE USER TO employee;

修改表的字符集为 utf8

 ALTER TABLE employee CHARACTER SET utf8;

列名 NAME 修改为 username

ALTER TABLE employee CHANGE NAME username VARCHAR(20);

四.查看表结构

查看数据库内的所有表
```
SHOW TABLES;
```
查看 employee 的建表语句
```
SHOW CREATE TABLE employee;
```
查看 employee 的表结构
```
DESC employee;
```

五. 表记录的操作

一. 插入语句 INSERT

向 employee 中插入三个员工信息，要求员工姓名分别是 zs, ls, wangwu

INSERT INTO employee VALUES(NULL,‘zs‘,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO employee VALUES(NULL,‘ls‘,NULL,NULL,NULL,NULL,NULL,NULL); 
INSERT INTO employee VALUES(NULL,‘wangwu‘,NULL,NULL,NULL,NULL,NULL,NULL);

二.更新语句 UPDATE

将所有员工薪水修改为5000元。

 UPDATE employee SET salary=5000; 
 UPDATE employee SET salary=5000 WHERE NAME=‘wangwu‘;

将姓名为 ’zs’ 的员工薪水修改为3000元。
```
UPDATE employee SET salary=3000 WHERE NAME=‘zs‘;
```
将姓名为 ’ls’ 的员工薪水修改为4000元, job 改为 ccc。
```
UPDATE employee SET salary=4000, job=‘ccc‘ WHERE NAME=‘ls‘;
```

将 wangwu 的薪水在原有基础上增加1000元。

UPDATE employee SET salary=salary+1000 WHERE NAME=‘wangwu‘;

三.删除语句 DELETE

删除表中名称为 ’zs’ 的记录。

DELETE FROM employee WHERE NAME=‘zs‘;

删除表中所有记录。
```
DELETE FROM employee;
```

四.查询语句 SELECT

E. 练习

查询表中所有学生的信息。
```
 SELECT * FROM exam;
```
查询表中所有学生的姓名和对应的英语成绩。
```
SELECT NAME,english FROM exam;
```
过滤表中重复数据。
```
SELECT DISTINCT * FROM exam GROUP BY ;
```
在所有学生分数上加10分特长分。

统计每个学生的总分。

 SELECT * ,(chinese+math+english) AS 总分 FROM exam;

使用别名表示学生分数。

SELECT id,NAME 名字, chinese 语文,math 数学, english 英语 FROM exam;

查询姓名为刘备的学生成绩, 使用WHERE子句

SELECT NAME,chinese,math,english FROM exam WHERE NAME=‘刘备‘;

查询英语成绩大于90分的同学
```
SELECT * FROM exam WHERE english > 90;
```

查询总分大于200分的所有同学

SELECT * FROM exam WHERE (chinese+math+english) > 200;

查询英语分数在 80－90之间的同学。

SELECT * FROM exam WHERE english BETWEEN 80 AND 90;

查询数学分数为89,75,91的同学。

 SELECT * FROM exam WHERE math=85 OR math=75 OR math=91; 
 SELECT * FROM exam WHERE math IN(85,75,91);

查询所有姓刘的学生成绩。

SELECT * FROM exam WHERE NAME LIKE ‘刘%‘;

查询所有姓刘两个字的学生成绩。

SELECT * FROM exam WHERE NAME LIKE ‘刘_‘;

查询数学分>80并且语文分>80的同学。

SELECT * FROM exam WHERE math >80 OR chinese > 80;

查询数学分>80 或者语文分>80的同学。

 SELECT * FROM exam WHERE math>80 AND chinese>80;

对数学成绩排序后输出, 使用 ORDER BY 排序
```
SELECT * FROM exam ORDER BY math; 
```
对总分排序按从高到低的顺序输出, 使用 ORDER BY 排序
```
SELECT * FROM exam ORDER BY (chinese+math+english) DESC;
```

对姓刘的学生成绩排序输出 , 使用 ORDER BY 排序

SELECT * FROM exam WHERE NAME LIKE ‘刘%‘ ORDER BY chinese;

统计一个班级共有多少学生？使用COUNT（函数）
```
SELECT COUNT(*) AS 总人数 FROM exam;  
```
统计数学成绩大于或等于90的学生有多少个？ 使用COUNT（函数）
```
SELECT COUNT(*) FROM exam WHERE math > 90 OR math = 90; 
```
21.统计总分大于250的人数有多少？ 使用COUNT（函数）
```
SELECT COUNT(*) FROM exam WHERE (chinese+math+english) > 250;
```
统计一个班级数学总成绩？ 使用SUM函数
```
SELECT SUM(math) FROM exam; 
```
统计一个班级语文.英语.数学各科的总成绩 ,使用SUM函数
```
SELECT SUM(chinese), SUM(math), SUM(english) FROM exam; 
```
统计一个班级语文.英语.数学的成绩总和, 使用SUM函数
```
 SELECT SUM(chinese+math+english) FROM exam; 
```
统计一个班级语文成绩平均分, 使用SUM函数
```
 SELECT AVG(chinese) 语文平均分 FROM exam;
```
求一个班级数学平均分？使用 AVG 函数
```
SELECT AVG(math) FROM exam;
```
求一个班级总分平均分 , 使用 AVG 函数
```
SELECT AVG(chinese+math+english) FROM exam;
```
求班级最高分和最低分（数值范围在统计中特别有用), 使用 MAX，MIN 函数
```
SELECT MAX(chinese+math+english) FROM exam;
SELECT MIN(chinese+math+english) FROM exam;
```

查询购买了几类商品，并且每类总价大于100的商品

SELECT product,SUM(price) FROM orders WHERE price>100 GROUP BY product;

八宝粥大朋友

关注

0
点赞
踩
23

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录