[数据库笔记] SQL练习11 - 18

最新推荐文章于 2021-11-25 15:22:15 发布
最新推荐文章于 2021-11-25 15:22:15 发布
阅读量1.9k 收藏 16
点赞数 4
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wong_faye/article/details/106273082
版权

11.创建和管理表

（1）创建表dept1

name 			Null? 			type
id 								int(7)
name 							varchar(25)

CREATE TABLE dept1(
	id INT(7),
	NAME VARCHAR(25)
);

（2）将表 departments 中的数据插入新表 dept2 中

CREATE TABLE dept2 SELECT * FROM departments;

（3）创建表 emp5

name 		Null? 		type
id 						int(7)
First_name 				Varchar (25)
Last_name 				Varchar(25)
Dept_id 				int(7)

CREATE TABLE emp5(
	id INT(7),
	first_name VARCHAR(25),
	last_name VARCHAR(25),
	dept_id INT(7)
);

（4）将列 Last_name 的长度增加到 50

ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);

（5）根据表 employees 创建 employees2

CREATE TABLE employees2 SELECT * FROM employees;

（6）删除表 emp5

DROP TABLE emp5;

（7）将表 employees2 重命名为 emp5

ALTER TABLE employees2 RENAME emp5;

（8）在表 dept 和 emp5 中添加新列 test_column，并检查所作的操作

ALTER TABLE dept1 ADD COLUMN test_column VARCHAR(25);
ALTER TABLE emp5 ADD COLUMN test_column VARCHAR(25);
SELECT * FROM dept1;
SELECT * FROM emp5;

（9）直接删除表 emp5 中的列 dept_id

ALTER TABLE emp5 DROP COLUMN department_id;

12.约束

（1）向表 emp2 的 id 列中添加 PRIMARY KEY 约束（my_emp_id_pk）

ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

（2）向表 dept2 的 id 列中添加 PRIMARY KEY 约束（my_dept_id_pk）

ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);

（3）向表 emp2 中添加列 dept_id，并在其中定义 FOREIGN KEY 约束，与之相关联的列是
dept2 表中的 id 列。

ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

13.事务

（1）创建一个表，里面有 id 为主键，stuname 唯一键，seat 座位号，要求将 id 设置成自增

（2）要求用事务的方式插入 3 行数据

（3）要求用事务的方式删除数据，并回滚

14.视图

（1）创建视图 emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

CREATE OR REPLACE VIEW emp_v1
AS 
SELECT last_name, salary, email
FROM employees
WHERE phone_number LIKE "011%";

在这里插入图片描述
（2）要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员
工姓名和邮箱、电话号码

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name, email, phone_number
FROM employees
WHERE phone_number LIKE "011%" AND email LIKE "%e%";

（3）向 emp_v1 插入一条记录，是否可以？

可以，插入的记录会插到真实的表中

INSERT INTO emp_v1 VALUES('Trump', 'TTT', '011.44.465.4555');

但在以下几种情况不能插入记录

1.包含分组函数、group by、distinct、having、union、
2.join
3.常量视图
4.where后的子查询用到了from中的表
5.用到了不可更新的视图

（4）创建视图 emp_v2，要求查询部门的最高工资高于 12000 的部门信息

CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 12000;

在这里插入图片描述
（5）向 emp_v2 中插入一条记录，是否可以？

不可以

（6）删除刚才的 emp_v2 和 emp_v1

DROP VIEW emp_v1, emp_v2;

15.存储过程

（1）创建存储过程或函数实现传入用户名和密码，插入到 admin 表中

CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))
BEGIN
	INSERT INTO admin(admin.username,PASSWORD)
	VALUES(username,loginpwd);
END $

（2）创建存储过程或函数实现传入女神编号，返回女神名称和女神电话

CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))

BEGIN
	SELECT b.name ,b.phone INTO NAME,phone
	FROM beauty b
	WHERE b.id = id;

END $

（3）创建存储存储过程或函数实现输入两个女神生日，返回大小

CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
	SELECT DATEDIFF(birth1,birth2) INTO result;
END $

（4）创建存储过程或函数实现传入一个日期，格式化成 xx 年 xx 月 xx 日并返回

CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
	SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $

CALL test_pro4(NOW(),@str)$
SELECT @str $

（5）创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串。如 传入 ：小昭，返回： 小昭 and 张无忌

DROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
	SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str
	FROM boys bo
	RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName;
	
	
	SET str=
END $

CALL test_pro5('柳岩',@str)$
SELECT @str $

（6）创建存储过程或函数，根据传入的条目数和起始索引，查询 beauty 表的记录

DROP PROCEDURE test_pro6$
CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT)
BEGIN
	SELECT * FROM beauty LIMIT startIndex,size;
END $

CALL test_pro6(3,5)$

16.函数

（1）创建函数，实现传入两个 float，返回二者之和

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$

17.流程控制结构

（1）已知表 stringcontent
其中字段：

id 			自增长
content 	varchar(20)

向该表插入指定个数的，随机的字符串

DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
	
);
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT;#代表初始索引
	DECLARE len INT;#代表截取的字符长度
	WHILE i<=insertcount DO
		SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26
		SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1-（20-startIndex+1）
		INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i=i+1;
	END WHILE;

END $

CALL test_randstr_insert(10)$

18.子查询经典题目

（1）查询工资最低的员工信息: last_name, salary

SELECT last_name, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

在这里插入图片描述
（2）查询平均工资最低的部门信息

-- 方法1

-- 1.查询各部门的平均工资
SELECT AVG(salary)ag, department_id
FROM employees
GROUP BY department_id;

-- 2.在1结果上查询最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
) ag_dep
;

-- 3.查询哪个部门的平均工资 = 2.
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
	HAVING AVG(salary) = (
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag, department_id
		FROM employees
		GROUP BY department_id
	) ag_dep
);

SELECT *
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
		HAVING AVG(salary) = (
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag, department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
	)
)

-- 方法2

-- 1.查询各部门的平均工资
SELECT AVG(salary)ag, department_id
FROM employees
GROUP BY department_id;

-- 2.求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

-- 3.查询部门信息
SELECT *
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
);

在这里插入图片描述
（3）查询平均工资最低的部门信息和该部门的平均工资

-- 1.查询各部门的平均工资
SELECT AVG(salary)ag, department_id
FROM employees
GROUP BY department_id;

-- 2.求出最低平均工资的部门编号
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;


-- 3.查询部门信息
SELECT d.*, ag
FROM departments d
JOIN (
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
) ag_dep
ON d.`department_id` = ag_dep.department_id;

在这里插入图片描述
（4）查询平均工资最高的 job 信息

-- 1.
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;

-- 2.
SELECT *
FROM jobs
WHERE job_id = (
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

在这里插入图片描述
（5）查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary) FROM employees
)
;

在这里插入图片描述
（6）查询出公司中所有 manager 的详细信息

SELECT *
FROM employees
WHERE employee_id = ANY (
	SELECT DISTINCT manager_id
	FROM employees
);

在这里插入图片描述
（7）各个部门中 最高工资中最低的那个部门的 最低工资是多少

SELECT MIN(salary)
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

在这里插入图片描述
（8）查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email,
salary

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = (

	SELECT DISTINCT manager_id -- 查询manage_id
	FROM employees
	WHERE department_id = (
		SELECT department_id -- 查询部门id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
	) AND manager_id IS NOT NULL
);

在这里插入图片描述

wong_faye
关注
专栏目录
Mysql基础篇（创建、管理、增删改表）
全网粉丝10W+、全栈领域优质创作者、掘金、阿里云等社区博客专家、专注于全栈领域和毕业项目实战
07-27 3764
MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货。...
第二阶段模块一作业第二题
crckerfang的博客
03-17 590
数据准备 1. 创建部门表(id,name) create table dept( id int primary key auto_increment, name varchar(20) ) insert into dept (name) values (‘开发部’),(‘市场部’),(‘财务部’); 2.员工表，员工表(id, 姓名，性别，工资，入职日期，部门ID) 创建员工表 create table employee ( id int primary key auto_increment, name
oracle部门平均工资视图,在Oracle中创建一个显示每个部门平均工资的视图？(Creating a view in Oracle that will show the average salar...
weixin_35131425的博客
04-06 1062
在Oracle中创建一个显示每个部门平均工资的视图？(Creating a view in Oracle that will show the average salary of each department?)我想在Oracle中查看将显示每个部门的平均薪水。 我使用的表是Employee，它有empno ， salary和deptname字段。我试着这样做：create view v_emp...
MySQL学习（12）|数据定义语言
积一时之步，臻千里之遥程
02-05 459
DDL语言（数据定义语言） 库和表的管理 一、库的管理 创建、修改、删除 二、表的管理 创建、修改、删除 关键词 创建： CREATE 修改： ALTER 删除： DROP #这是对数据进行付定义操作，区分DELETE、TRUNCATE 一、库的管理 1、库的创建 语法： create database [if not exists]库名; #if not exists 如果不存在，就创建 案例：...
mysql数据库-库与表的管理案例与操作练习【复习系列】
~本特利~的博客
10-21 411
1.案例学习 DDL（数据定义语言） 库和表的管理 一、库的管理 创建、修改、删除 二、表的管理 创建、修改、删除 创建： create 修改： alter 删除： drop 一、库的管理 1、库的创建 语法：create database [if not exists]库名; 案例：创建库Books CREATE DATABASE IF NOT EXISTS books ; 2、库的修改 语法： RENAME DATABASE books TO 新库名; 更改库的字符集 ALT
sqli-labs靶场练习笔记
11-09
### SQLi-Labs靶场练习笔记知识点概览 #### 一、SQL注入基本概念与原理 - **定义**：SQL注入是一种常见的应用层攻击方式，它利用编程中的漏洞，通过在应用程序的输入框中插入恶意SQL语句来控制后端数据库服务器。 -...
sql学习笔记-轻松掌握sql
最新发布
02-21
这份“sql学习笔记”涵盖了SQL的基础概念、语法和实用技巧，帮助初学者轻松掌握SQL的核心知识。 一、SQL基础 1. 数据库与表：SQL中的数据库是一个组织数据的集合，由一个或多个表组成。表是由行和列构成的数据结构...
数据库实验1 MYSQL数据库SQL语句练习实验.doc
12-10
在本实验"数据库实验1 MYSQL数据库SQL语句练习实验"中，主要目标是理解和掌握数据库管理系统(DBMS)的基本操作，特别是通过SQL语句来定义、操纵数据库以及理解视图的概念。实验主要分为以下几个部分： 首先，实验...
数据库复习笔记及mooc上的题库
12-30
数据库是信息技术领域中的核心部分，它负责存储、管理和检索数据，是各类信息系统的基础。这篇复习笔记结合了Mooc上的题库，将全面...通过学习和练习，你可以提升在数据库领域的专业素养，更好地应对各种数据管理挑战。
数据库练习-教程与笔记习题
05-22
本节将详细介绍如何通过SQL Server Management Studio (SSMS) 图形化界面及T-SQL语句两种方式来创建一个名为“xuesheng”的数据库。 #### 二、使用SSMS创建数据库 1. **启动SSMS**：首先打开SQL Server Management...
07、SQL--创建和管理表（含习题）
qq_39044046的博客
08-03 1984
一、数据库表的一些操作： 1、Oracle数据库中的表： （1）、查看用户创建了哪些表。 select * from user_tables；//查询用户自己创建的表的所有属性 select table_name from user_tables；//只查看用户创建的表的名字 （2）、查看数据库中表的一些操作。 二、关于创建数据...
oracle之创建和管理表之练习题
歌谣的博客
07-04 639
51. 利用子查询创建表 myemp, 该表中包含 employees 表的 employee_id(id), last_name(name), salary(sal), email 字段 1). 创建表的同时复制 employees 对应的记录 create table myemp as select employee_id id, last_name name, salary sal, email from employees 2). 创建表的同时不包含 employees .
「MySql高级查询与编程」练习：企业员工管理
热门推荐
杨明金的博客
11-13 5万+
题目：企业员工管理 一、语言和环境 1.实现语言：SQL。 2.开发环境：MySQL，navicat for mysql。 二、题目（100分）： 1、创建数据库及数据表： （1）创建数据库，名称为db_emp； （2）创建数据表t_emp（员工表）、t_dept（部门表）、t_salary（薪资表）。 表1 t_emp（员工表） 字段列名 含义 数据类型 长度 允许为空 约束 empId 员工编号 int - no 主键、自增长 empName 员工姓名 Varchar 30 No
MySQL第10章_创建和管理表
LeungKiChen
11-25 519
MySQL第10章_创建和管理表 #第10章_创建和管理表 SELECT * FROM `order`; #1. 创建和管理数据库 #1.1 如何创建数据库 #方式1： CREATE DATABASE mytest1; # 创建的此数据库使用的是默认的字符集 #查看创建数据库的结构 SHOW CREATE DATABASE mytest1; #方式2：显式了指名了要创建的数据库的字符集 CREATE DATABASE mytest2 CHARACTER SET 'gbk'; # SHOW.
mysql 部分习题---存储过程
ZhaoQQing的博客
06-30 934
#一、创建存储过程实现传入用户名和密码，插入到admin表中 #二、创建存储过程实现传入女神编号，返回女神名称和女神电话 #三、创建存储存储过程或函数实现传入两个女神生日，返回她们相隔的天数 #四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回 #五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串 #六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录 #一、创建存储过程实现传入用户名和密码，插入到admin表中 DELIMIT.
MySQL学习（17）|存储过程
积一时之步，臻千里之遥程
02-15 535
存储过程 存储过程：类似python中class类，封装 含义：一组预先编译好的SQL语句的集合，理解成批处理语句 优点： 1、提高代码的重用性 2、简化操作 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 一、创建语法 CREATE PROCEDURE 存储过程名(IN输入/OUT输出/INOUT即入又出 参数名 参数类型) BEGIN 存储过程体（一组合法的SQL语句...
mysql存储过程实例_mysql存储过程案例讲解
weixin_33419842的博客
01-18 196
一、创建存储过程实现传入用户名和密码，插入到admin表中CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))BEGININSERT INTO admin(admin.username,PASSWORD)VALUES(username,loginpwd);END $二、创建存储过程实现传入女神编号，返...
关于mysql存储过程
AnlaGodness的博客
07-29 288
存储过程和函数：类似于java中的方法 好处： 1、提高代码的重用性 2、简化操作 存储过程 含义：一组预先编译好的SQL语句的集合，理解成批处理语句 1、提高代码的重用性 2、简化操作 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。 一、创建语法 create procedure 存储过程名（参数列表） begin 存储过程体（一组合法的SQL语句） end 注意： 1、参数列表包含三部分：参数模式、参数名、参数类型 举例：IN stuname VARCHAR(20)
黑猴子的家：mysql 存储过程练习
黑猴子的博客
02-28 660
1、创建存储过程或函数实现传入用户名和密码，插入到admin表中 USE girls; DELIMITER $ CREATE PROCEDURE mypro1(IN username VARCHAR(20),IN PASSWORD VARCHAR(20)) BEGIN INSERT INTO admin VALUES(NULL,username,PASSWORD); END $ C...
SQL数据库基础教程及练习笔记
资源摘要信息:"本资源为SQL数据库基础的视频教程配套笔记及课后练习文档。它涵盖了SQL（Structured Query Language，结构化查询语言）的基本知识，这是一门用于管理和操纵关系数据库管理系统（RDBMS）的标准编程语言...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值