一、做题之前先配置好自己的数据库
安装数据库:MySQL8.0安装教程(很详细)_优柔寡断的阿坤~的博客-CSDN博客
二、使用SQLyog连接数据库:
中间报错报错:Client does not support authentication protocol requested by server; consider upgrading MySQL cli
三、导入所需要的库文件如下链接:https://pan.baidu.com/s/1M4YrBqO6KErErmV8la3ysQ?pwd=6666
将做题用到的三个表导进去
第三个表的代码如下
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
开始做题吧~
表中每个字段的含义
第1关、数据库基础
1、MySQL和SQLyog安装
2、MySQL服务器开启
3、将sql语句导入sqlyog创建做题需要的数据库
第2关、基础查询
1、查询员工名和姓连接成一个字段,并显示为姓名
第3关、条件查询和排序查询
1、查询员工名中包含字符a的员工信息
2、查询员工名中第三个字符为a,第五个字符为e的员工名信息
3、查询员工名中第二个字符为_的员工名(使用转义字符\)escape关键字
4、查询员工编号在100到200之间的员工信息
5、查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
6、查询没有奖金的员工名和奖金率
7、查询有奖金的员工名和奖金率
8、查询部门编号>=90的员工信息,按照入职时间的先后顺序进行排序
9、查询员工信息,要求工资从高到低排序
10、按年薪的高低显示员工信息和年薪【按表达式排序】
11、按年薪的高低显示员工信息和年薪【按别名排序】
12、按姓名长度显示员工的姓名和工资
13、查询员工信息,要求先按工资排序【升序】,再按照员工编号排序【降序】【按多个字段排序】
第4关、常见函数
1、姓中首字母大写,其他字母小写然后用‘_’符号拼接起来
2、查询入职日期为1992-4-3的员工信息
3、查询有奖金的员工名和入职日期(**月/**日 **年)
4、
查询员工的工资,要求
部门编号=30,显示的工资为1.1倍
部门编号=40,显示的工资为1.2倍
部门编号=50,显示的工资为1.3倍
其他部门显示工资为原工资
5、
查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
Else 显示D级别
6、显示系统时间(注意:日期+时间)
7、查询员工号,姓名以及工资提高20%后的结果(new salary)
8、将员工的姓名按照其首字母降序,并写出姓名的长度
9、做一个查询,产生下面的结果:
<last_name> earns <salary> monthly but wants <salary*3>
10、使用CASE-WHEN,按照下面的条件:
job grade
AD_PRES A
ST_MAN B
IT_PROG C
第5关、分组函数
1、查询公司员工工资最大值、最小值、平均值、综合
2、查询员工表中的最大入职时间和最小入职时间的相差天数
3、查询部门编号为90的员工个数
第6关、分组查询
1、查询每个工种的最高工资
2、查询每个位置撒谎给你上的部门个数
3、查询邮箱中包含a字符的每个部门的平均工资
4、查询每个领导手下有奖金的员工的最高工资
5、查询哪个部门的员工个数大于2
6、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
7、查询领导编号>120的每个领导手下的最低工资大于5000的领导编号是哪个,以及其最低工资
8、按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数大于5的有哪些
9、查询每个部门每个工种的员工的平均工资(先按照部门分组,在部门里再按照工种分组)
10、查询每个部门(部门编号不为零)每个工种的员工的平均工资,再按照平均工资的高低进行排序
11、查询员工最高工资和最低工资的差距(DIFFERENCE)
12、查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
13、.查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
14、选择具有各个job_id的员工人数
第7关、连接查询(导入第二个sql语句集)
1、查询女神名对应的男神名
2、查询员工名对应的部门名
3、查询员工名、工种号、工种名
4、查询有奖金的员工名和部门名
5、查询城市名中第二个字符为o的部门名和城市名
6、查询每个城市的部门个数
7、查询有奖金得每个部门得部门名和部门得领导编号和该部门得最低工资(一个部门多个领导)
8、查询每个工种得公众名和员工得个数,并按员工得个数降序
9、查询员工名、部门名和所在得城市
10、查询员工的工资和工资级别
11、查询员工名和上级的名称
12、显示所有员工的姓名,部门号和部门名称。
13、查询 90 号部门员工的 job_id 和 90 号部门的 location_id
14、选择所有有奖金的员工的 last_name , department_name , location_id , city
15、选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
16、查询每个工种、每个部门的部门名、工种名和最低工资
17、查询每个国家下的部门个数大于 2 的国家编号
18、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,
其结果类似于下面的格式:
employees Emp manager Mgr
kochhar 101 king 100
第8关、SQL99语法
1、查询员工名、部门名
2、查询名字中包含e的员工名和工种名(添加筛选)
3、查询部门个数>3城市名和部门个数(添加分组和筛选)
4、查询部门的员工个数大于3的部门名和员工个数,并按个数降序
5、查询员工名、部门名、工种名,并按部门名降序(第一个表要和后两个表有连接条件)
6、查询员工的工资级别
7、查询每个工资级别员工的个数>20的级别及员工个数,并且按工资级别降序
8、查询员工名字和上级的名字
9、查询男朋友不在男神表中的女神名
10、查询哪个部门没有员工(部门表主表,员工表从表)
11、查询编号>3 的女神的男朋友信息,如果有则列出详细,如果没有,用 null 填充
12、查询哪个城市没有部门
13、查询部门名为 SAL 或 IT 的员工信息
第9关、子查询
1、谁的工资比Abel高?
2、返回job_id与141号员工相同,salary比143号员工多的员工的姓名,job_id和工资
3、查询员工姓名,job_id和salary 满足job_id与141号员工相同,salary比143号员工多
4、返回公司工资最少的员工的last_name,job_id,salary
5、查询最低工资大于50号部门最低工资的部门id和其最低工资
6、返回location_id是1400或1700的部门中的所有员工姓名
7、返回其他工种中比job_id为‘IT_PROG’的工种任一员工工资低的员工的员工号、姓名、job_id以及salary。
8、返回其他工种中比job_id为‘IT_PROG’的工种所有员工工资低的员工的员工号、姓名、job_id以及salary
9、查询员工编号最小并且工资最高的员工信息
10、查询每个部门的员工个数
11、查询员工号为102的部门名
12、查询每个部门的平均工资的工资等级
13、查询有员工的部门名
14、查询没有女朋友的男神信息
15、查询和Zlotkey相同的员工姓名和工资
16、查询工资比公司平均工资高的员工号、姓名和工资
17、查询各部门中工资比本部门平均工资高的员工号、姓名和工资
18、查询和姓名中包含u的员工在相同部门的员工的员工号和姓名
19、查询在部门的location_id 为1700的部门工作的员工的员工号
20、查询管理者是King的员工姓名和工资
21、查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为姓.名
22、查询工资最低的员工信息: last_name, salary
23、查询平均工资最低的部门信息
24、 查询平均工资最低的部门信息和该部门的平均工资
25、查询平均工资最高的 job 信息
26、查询平均工资高于公司平均工资的部门有哪些?
27、查询出公司中所有 manager 的详细信息.
28各个部门中 最高工资中最低的那个部门的 最低工资是多少
29、查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email,
salary
第10关、分页查询
1、查询前五条员工信息
2、查询第11条至第25条数据
3、有奖金的员工信息,并且工资较高的前10名显示出来
第11关、union联合查询
1、查询部门编号>90或邮箱包含a的员工信息
2、查询中国用户中男性的信息以及外国用户中男性的信息
第12关、数据操纵语言
1、修改beauty表中姓唐的女神电话为‘1988888’
2、修改boys表中的id号为2的名称为张飞,魅力值为250
3、修改张无忌的女朋友的手机号为199
4、修改没有男朋友的男朋友编号都为2
5、删除手机号以9结尾的女神信息
6、删除张无忌的女朋友的信息
7、删除黄晓明的信息和他女朋友的信息
8、运行以下脚本创建表 my_employees
Create table my_employees(
Id int(10),
First_name varchar(10),
Last_name varchar(10),
Userid varchar(10),
Salary double(10,2)
)
create table users(
id int,
userid varchar(10),
department_id int
)
9、显示表 my_employees 的结构
10、
向 my_employees 表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
11、
向 users 表中插入数据
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
12、将 3 号员工的 last_name 修改为“drelxer
13、将所有工资少于 900 的员工的工资修改为 1000
14、将 userid 为 Bbiri 的 user 表和 my_employees 表的记录全部删除
15、删除所有数据
16、检查所作的修正
17、清空表 my_employees
第13关、数据库定义语言
1、创建数据库test1(后面表操作基于此库)
2、创建表book;(
id INT,#编号
bName VARCHAR(20),#书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期)
3、创建表 auther
( id INT,
au_name VARCHAR(20),
nation VARCHAR(10));
4、创建表 dept1
name Null? type
id int(7)
name varchar(25)
5、将表 departments 中的数据插入新表 dept2 中
6、创建表 emp5
name Null? type
id int(7)
First_name Varchar (25)
Last_name Varchar(25)
Dept_id int(7)
7、将列 Last_name 的长度增加到 50
8、根据表 employees 创建 employees2
9、删除表 emp5
10、将表 employees2 重命名为 emp5
11、在表 dept 和 emp5 中添加新列 test_column,并检查所作的操作
12、直接删除表 emp5 中的列 dept_id
13、向表 emp2 的 id 列中添加 PRIMARY KEY 约束(my_emp_id_pk)
14、向表 dept2 的 id 列中添加 PRIMARY KEY 约束(my_dept_id_pk)
15、向表 emp2 中添加列 dept_id,并在其中定义 FOREIGN KEY 约束,与之相关联的列是
dept2 表中的 id 列。
16、创建表 dept
name Null? type
id int(7)
name varchar(25)
17、
将表 departments 中的数据插入新表 dept2 中
18、创建表 emp5
name Null? type
id int(7)
First_name Varchar (25)
Last_name Varchar(25)
Dept_id int(7)
19、将列 Last_name 的长度增加到 50
20、根据表 employees 创建 employees2
21、删除表 emp5
22、将表 employees2 重命名为 emp5
23、在表 dept 和 emp5 中添加新列 test_column,并检查所作的操作
24、直接删除表 emp5 中的列 dept_id
第14关、数据类型
第15关、事务控制语言
1、创建一个表,里面有 id 为主键,stuname 唯一键,seat 座位号,要求将 id 设置成自增
2、要求用事务的方式插入 3 行数据
3、要求用事务的方式删除数据,并回滚
第16关、视图
1、查询邮箱中包含a的员工名,部门名和工种信息
2、查询各部门的平均工资级别
3、查询平均工资最低的部门信息
4、查询工资最低的部门名和工资
5、创建视图 emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
6、要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员
工姓名和邮箱、电话号码
7、向 emp_v1 插入一条记录,是否可以?
8、修改刚才记录中的电话号码为‘0119’
9、删除刚才记录
10、创建视图 emp_v2,要求查询部门的最高工资高于 12000 的部门信息
11、向 emp_v2 中插入一条记录,是否可以?
12、删除刚才的 emp_v2 和 emp_v1
第17关、变量
1、查看所有的全局变量
2、查看部分全局变量
3、查看指定的全局变量的值autocommit(是否自动提交)、tx_isolation(数据库隔离级别)
4、设置全局变量不自动提交(即为autocommit赋值)
5、设置数据库隔离级别为read committed
6、查看所有的会话变量
7、查看部分会话变量(包含char)
8、查看指定的会话变量的值autocommit、tx_isolation
9、创建用户变量@name='li'
10、创建一个局部变量
第18关、存储过程
1、创建存储过程 插入到admin表中五条记录
2、创建存储过程实现根据女神名查询对应的男神信息
3、创建存储过程实现,用户是否登陆成功
4、根据女神名,返回对应的男神名
5、根据女神名返回对应的男神名和男神魅力值
6、传入a和b两个值,最终a和b都翻倍并返回
7、创建存储过程或函数实现传入用户名和密码,插入到 admin 表中
8、创建存储过程或函数实现传入女神编号,返回女神名称和女神电话
9、创建存储存储过程或函数实现歘人两个女神生日,返回大小
10、创建存储过程或函数实现传入一个日期,格式化成 xx 年 xx 月 xx 日并返回
11、创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
如 传入 :小昭 返回: 小昭 and 张无忌
12、创建存储过程或函数,根据传入的条目数和起始索引,查询 beauty 表的记录
第19关、函数
1、返回公司的员工个数
2、根据员工名,返回他的工资。
3、根据部门名,返回该部门的平均工资。
4、创建函数,实现传入两个 float,返回二者之和
5、创建函数,实现传入工种名,返回该工种的员工人数
6、创建函数,实现传入员工名,返回该员工的领导名
第20关、流程控制结构
1、很有难度~~~
已知表 stringcontent
其中字段:
id 自增长
content varchar(20)
向该表插入指定个数的,随机的字符串
答案:
第1关、数据库基础
1、MySQL和SQLyog安装
2、MySQL服务器开启
3、将sql语句导入sqlyog创建做题需要的数据库
第2关、基础查询
1、查询员工名和姓连接成一个字段,并显示为姓名
SELECT CONCAT(last_name,first_name) 姓名
FROM employees;
第3关、条件查询和排序查询
1、查询员工名中包含字符a的员工信息
SELECT * FROM employees
WHERE last_name LIKE '%a%';
2、查询员工名中第三个字符为a,第五个字符为e的员工名信息
SELECT * FROM employees
WHERE last_name LIKE '__a_e%';
3、查询员工名中第二个字符为_的员工名(使用转义字符\)escape关键字(明确\为转义字符)
SELECT DISTINCT last_name
FROM employees
WHERE last_name LIKE '_\_%' ESCAPE '\';
4、查询员工编号在100到200之间的员工信息
#方式一
SELECT * FROM employees
WHERE employee_id BETWEEN 100 AND 200;
#方式二
SELECT * FROM employees
WHERE employee_id>=100 AND employee_id<=200;
5、查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
SELECT * FROM employees
WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
6、查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
7、查询有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
8、查询部门编号>=90的员工信息,按照入职时间的先后顺序进行排序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate DESC;
9、查询员工信息,要求工资从高到低排序
SELECT *
FROM employees
ORDER BY salary;
10、按年薪的高低显示员工信息和年薪【按表达式排序】
SELECT *,salary*(1+IFNULL(commission_pct,0))*12 annual
FROM employees
ORDER BY salary*(1+IFNULL(commission_pct,0))*12 DESC;
11、按年薪的高低显示员工信息和年薪【按别名排序】
SELECT *,salary*(1+IFNULL(commission_pct,0))*12 annual
FROM employees
ORDER BY annual DESC;
12、按姓名长度显示员工的姓名和工资
SELECT CONCAT(last_name,first_name)'name',salary
FROM employees
ORDER BY LENGTH('name');
13、查询员工信息,要求先按工资排序【升序】,再按照员工编号排序【降序】【按多个字段排序】
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;
第4关、常见函数
1、姓中首字母大写,其他字母小写然后用‘_’符号拼接起来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)))
FROM employees;
2、查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees
WHERE hiredate = DATE_FORMAT('1992-4-3','%Y-%m-%d');
3、查询有奖金的员工名和入职日期(**月/**日 **年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月\%d日 %Y年')
FROM employees
WHERE commission_pct IS NOT NULL;
4、
查询员工的工资,要求
部门编号=30,显示的工资为1.1倍
部门编号=40,显示的工资为1.2倍
部门编号=50,显示的工资为1.3倍
其他部门显示工资为原工资
SELECT department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 30 THEN salary*1.1
WHEN 30 THEN salary*1.1
ELSE salary
END AS 新工资
FROM employees;
5、
查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
Else 显示D级别
SELECT salary,
CASE
WHEN salary>2000 THEN 'A'
WHEN salary>2000 THEN 'A'
WHEN salary>2000 THEN 'A'
ELSE 'D'
END AS 工资级别
FROM employees
6、显示系统时间(注意:日期+时间)
SELECT NOW();
7、查询员工号,姓名以及工资提高20%后的结果(new salary)
SELECT employee_id,salary*1.2 'new salary'
FROM employees;
8、将员工的姓名按照其首字母降序,并写出姓名的长度
SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY SUBSTR(last_name,1,1) DESC;
9、做一个查询,产生下面的结果:
<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3)
FROM employees;
10、使用CASE-WHEN,按照下面的条件:
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SELECT job_id job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN ' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
END AS grade
FROM employees;
第5关、分组函数
1、查询公司员工工资最大值、最小值、平均值、综合
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
2、查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT MAX(hiredate),MIN(hiredate),DATEDIFF(MAX(hiredate),MIN(hiredate))
FROM employees;
3、查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id=90;
第6关、分组查询
1、查询每个工种的最高工资
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id;
2、查询每个位置上的部门个数
SELECT location_id,COUNT(*)
FROM departments
GROUP BY location_id
3、查询邮箱中包含a字符的每个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
4、查询每个领导手下有奖金的员工的最高工资
SELECT manager_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
5、查询哪个部门的员工个数大于2
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
6、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT department_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY department_id
HAVING MAX(salary)>12000;
7、查询领导编号>120的每个领导手下的最低工资大于5000的领导编号是哪个,以及其最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>120
GROUP BY manager_id
HAVING MIN(salary)>5000;
8、按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数大于5的有哪些
SELECT LENGTH(last_name),COUNT(*)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
9、查询每个部门每个工种的员工的平均工资(先按照部门分组,在部门里再按照工种分组)
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
10、查询每个部门(部门编号不为null)每个工种的员工的平均工资,再按照平均工资的高低进行排序
SELECT department_id,job_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
ORDER BY AVG(salary);
11、查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT MAX(salary)-MIN(salary) DIFFERENCE FROM employees;
12、查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>6000;
13、.查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id;
14、选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
第7关、连接查询(导入第二个sql语句集)
1、查询女神名对应的男神名
SELECT boyName
FROM boys bo,beauty b
WHERE b.`boyfriend_id`=b.`id`;
2、查询员工名对应的部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;
3、查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
4、查询有奖金的员工名和部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
5、查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`;
6、查询每个城市的部门个数
SELECT city,COUNT(*)
FROM departments d,locations l
WHERE d.location_id=l.`location_id`
GROUP BY l.`city`;
7、查询有奖金得每个部门得部门名和部门的领导编号和该部门得最低工资(一个部门多个领导)
SELECT e.department_id,e.manager_id,MIN(e.salary)
FROM employees e,employees em
WHERE e.`employee_id`=em.`manager_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY e.department_id;
8、查询每个工种得工种名和员工的个数,并按员工得个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs s
WHERE e.`job_id`=s.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
9、查询员工名、部门名和所在得城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;
10、查询员工的工资和工资级别
SELECT salary,`grade_level`
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
11、查询员工名和上级的名称
SELECT e.last_name,em.last_name
FROM employees e,employees em
WHERE e.`employee_id`=em.`manager_id`;
12、显示所有员工的姓名,部门号和部门名称。
SELECT last_name,e.department_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
13、查询 90 号部门员工的 job_id 和 90 号部门的 location_id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.department_id=90;
14、选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.`last_name`,d.`department_name`,d.`location_id`,l.`city`
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND e.`commission_pct`IS NOT NULL;
15、选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT last_name,e.`job_id`,e.`department_id`,d.`department_name`
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND l.`city` = Toronto;
16、查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.department_name,job_title,MIN(salary)
FROM employees e,jobs j,departments d
WHERE e.`job_id`=j.`job_id` AND e.`department_id`=d.`department_id`
GROUP BY e.department_id,j.`job_id`;
17、查询每个国家下的部门个数大于 2 的国家编号
SELECT country_id,COUNT(*)
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING COUNT(*)>2;
18、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,
其结果类似于下面的格式:
employees Emp manager Mgr
kochhar 101 king 100
SELECT e.`last_name` employees,e.`employee_id` EMP,em.`last_name` manager,em.`employee_id` MGR
FROM employees e,employees em
WHERE e.`employee_id`=em.`employee_id`;
第8关、SQL99语法
1、查询员工名、部门名
SELECT e.`last_name`,j.`job_title`
FROM employees e
JOIN jobs j
ON e.`job_id`=j.`job_id`;
2、查询名字中包含e的员工名和工种名(添加筛选)
SELECT e.`last_name`,j.`job_title`
FROM employees e
JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name`LIKE '%e%';
3、查询部门个数>3城市名和部门个数(添加分组和筛选)
SELECT city,COUNT(*)
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`location_id`
HAVING COUNT(*)>3;
4、查询部门的员工个数大于3的部门名和员工个数,并按个数降序
SELECT department_name,COUNT(*)
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING COUNT(*)>3;
5、查询员工名、部门名、工种名,并按部门名降序(第一个表要和后两个表有连接条件)
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
JOIN jobs j ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;
6、查询员工的工资级别
SELECT j.`grade_level`,salary
FROM employees e
JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
7、查询每个工资级别员工的个数>20的级别及员工个数,并且按工资级别降序
SELECT j.`grade_level` ,COUNT(*)
FROM employees e
JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY j.`grade_level`
ORDER BY j.`grade_level`;
8、查询员工名和其上级的名字
SELECT e.last_name,em.last_name
FROM employees e
JOIN employees em
ON e.`manager_id`=em.`employee_id`;
9、查询男朋友不在男神表中的女神名
SELECT b.`name`
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
10、查询哪个部门没有员工(部门表主表,员工表从表)
SELECT d.`department_id`,e.*
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
11、查询编号>3 的女神的男朋友信息,如果有则列出详细,如果没有则用 null 填充
SELECT b.`id`,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`=3;
12、查询哪个城市没有部门
SELECT l.`city`
FROM locations l
LEFT JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL;
13、查询部门名为 SAL 或 IT 的员工信息
SELECT e.*
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE department_name IN('SAL','IT');
第9关、子查询
1、谁的工资比Abel高?
SELECT last_name
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel');
2、返回job_id与141号员工相同,salary比143号员工多的员工的姓名,job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143);
3、查询员工姓名,job_id和salary 满足job_id与141号员工相同,salary比143号员工多
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143);
4、返回公司工资最少的员工的last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees);
5、查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50);
6、返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700));
7、返回其他工种中比job_id为‘IT_PROG’的工种任一员工工资低的员工的员工号、姓名、job_id以及salary。
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN(salary)
FROM employees
WHERE job_id='IT_PROG')
AND job_id<>'IT_PROG';
9、查询员工编号最小并且工资最高的员工信息
SELECT * FROM employees
WHERE
employee_id=(SELECT MIN(employee_id) FROM employees)
AND
salary=(SELECT MAX(salary) FROM employees);
10、查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
11、查询员工号为102的部门名
SELECT department_name
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
WHERE employee_id=102);
12、查询每个部门的平均工资的工资等级
SELECT department_id,'avg',grade_level
FROM
(SELECT AVG(salary) 'avg',department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id) AS em_avg
JOIN job_grades j
ON em_avg.avg BETWEEN j.`lowest_sal`AND j.`highest_sal`;
13、查询有员工的部门名
#方法一
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`;
#方法二
SELECT last_name,department_name
FROM (SELECT last_name,department_id FROM employees) em
JOIN departments d
ON d.`department_id`=em.department_id;
14、查询没有女朋友的男神信息
SELECT bo.*
FROM beauty b
RIGHT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`name` IS NULL;
15、查询和Zlotkey工资相同的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT salary
FROM employees
WHERE last_name='Zlotkey')
16、查询工资比公司平均工资高的员工号、姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees);
17、查询各部门中工资比本部门平均工资高的员工号、姓名和工资
SELECT employee_id,last_name,salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees em
WHERE e.`department_id`=em.`department_id`
GROUP BY department_id);
18、查询和姓名中包含u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM employees
WHERE last_name LIKE'%u%');
19、查询在部门的location_id 为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE l.location_id=1700
);
20、查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name='K_ing')
21、查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为姓.名
SELECT CONCAT(first_name,last_name)姓·名
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees);
22、查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees);
23、查询平均工资最低的部门信息
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1);
24、 查询平均工资最低的部门信息和该部门的平均工资
SELECT d.*,avg_de.avg
FROM departments d
JOIN (
SELECT department_id,AVG(salary) 'avg'
FROM employees e
WHERE e.department_id IS NOT NULL
GROUP BY e.department_id
ORDER BY AVG(salary)
LIMIT 1) avg_de
WHERE d.department_id=avg_de.department_id;
25、查询平均工资最高的 job 信息
SELECT * FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary)
LIMIT 1);
26、查询平均工资高于公司平均工资的部门有哪些?
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees);
27、查询出公司中所有 manager 的详细信息.
SELECT * FROM employees
WHERE employee_id IN(
SELECT e.manager_id
FROM employees e
JOIN employees em
ON e.`manager_id`=em.`employee_id`);
28、各个部门中 最高工资中最低的那个部门的 最低工资是多少
SELECT MIN(salary)
FROM employees
WHERE department_id =(
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1)
29、查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email,
salary
SELECT * FROM employees
WHERE employee_id=(
SELECT DISTINCT manager_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1)
AND manager_id IS NOT NULL);
第10关、分页查询
1、查询前五条员工信息
SELECT * FROM employees LIMIT 5;
2、查询第11条至第25条数据
SELECT * FROM employees LIMIT 10,15;
3、有奖金的员工信息,并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
第11关、union联合查询
1、查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE'%a%';
第12关、数据操纵语言
1、修改beauty表中姓唐的女神电话为‘1988888’
UPDATE beauty
SET phone=1988888
WHERE NAME LIKE '唐%';
2、修改boys表中的id号为2的名称为张飞,魅力值为250
UPDATE boys
SET userCP=250
WHERE id=2;
3、修改张无忌的女朋友的手机号为199
UPDATE beauty b
JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
SET phone=199
WHERE bo.`boyName`='张无忌';
4、修改没有男朋友的男朋友编号都为2
UPDATE beauty
SET boyfriend_id =2
WHERE boyfriend_id IS NULL;
5、删除手机号以9结尾的女神信息
DELETE FROM beauty
WHERE phone LIKE'%9';
6、删除张无忌的女朋友的信息
DELETE b FROM beauty b JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.boyname='张无忌';
7、删除黄晓明的信息和他女朋友的信息
DELETE b,bo FROM beauty b JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';
8、运行以下脚本创建表 my_employees
Create table my_employees(
Id int(10),
First_name varchar(10),
Last_name varchar(10),
Userid varchar(10),
Salary double(10,2)
)
create table users(
id int,
userid varchar(10),
department_id int
)
9、显示表 my_employees 的结构
DESC my_employees;
10、
向 my_employees 表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Birl','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
11、
向 users 表中插入数据
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40)
12、将 3 号员工的 last_name 修改为“drelxer
UPDATE my_employees
SET Last_name = 'drelxer'
WHERE id=3;
13、将所有工资少于 900 的员工的工资修改为 1000
UPDATE my_employees
SET Salary=1000
WHERE Salary<900;
14、将 userid 为 Bbiri 的 user 表和 my_employees 表的记录全部删除
DELETE my,u FROM my_employees my
JOIN users u
ON my.`Userid`=u.`userid`
WHERE my.`Userid`='Bbiri';
15、删除所有数据
DELETE FROM my_employees;
16、检查所作的修正
SELECT * FROM my_employees;
17、清空表 my_employees
TRUNCATE my_employees;
第13关、数据库定义语言
1、创建数据库test1(后面表操作基于此库)
CREATE DATABASE test1;
2、创建表book;(
id INT,#编号
bName VARCHAR(20),#书名
price DOUBLE,#价格
authorId INT,#作者编号
publishDate DATETIME#出版日期)
USE test1;
CREATE TABLE book(
id INT,
bName VARCHAR(20),
price DOUBLE,
authorId INT,
publishDate DATETIME);
3、创建表 auther
( id INT,
au_name VARCHAR(20),
nation VARCHAR(10));
CREATE TABLE auther(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10));
4、创建表 dept1
name Null? type
id int(7)
name varchar(25)
CREATE TABLE dep1(
id INT(7),
NAME VARCHAR(25));
5、将表 departments 中的数据插入新表 dept2 中
CREATE TABLE dept2 SELECT * FROM departments;
6、创建表 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));
7、将列 Last_name 的长度增加到 50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);
8、根据表 employees 创建 employees2
CREATE TABLE employees2 LIKE employees;
9、删除表 emp5
DROP TABLE emp5;
10、将表 employees2 重命名为 emp5
ALTER employees2 RENAME TO emp5;
11、在表 dept 和 emp5 中添加新列 test_column,并检查所作的操作
ALTER TABLE dept ADD COLUMN test_column
12、直接删除表 emp5 中的列 dept_id
ALTER TABLE emp5 DROP COLUMN dep_id;
13、向表 emp2 的 id 列中添加 PRIMARY KEY 约束(my_emp_id_pk)
ALTER TABLE emp2 MODIFY id PRIMARY KEY my_emp_id_pk;
14、向表 dept2 的 id 列中添加 PRIMARY KEY 约束(my_dept_id_pk)
15、向表 emp2 中添加列 dept_id,并在其中定义 FOREIGN KEY 约束,与之相关联的列是
dept2 表中的 id 列。
ALTER TABLE ADD COLUMN dept_id INT REFERENCES dept2(id);
第14关、数据类型
第15关、事务控制语言
1、创建一个表,里面有 id 为主键,stuname 唯一键,seat 座位号,要求将 id 设置成自增
CREATE TABLE li_1(
id INT PRIMARY KEY AUTO_INCREMENT,
stuname VARCHAR(20) UNIQUE
);
2、要求用事务的方式插入 3 行数据
SET autocommit=0;
START TRANSACTION;
INSERT INTO li_1 VALUES('','张山');
INSERT INTO li_1 VALUES('','李四');
INSERT INTO li_1 VALUES('','王五');
COMMIT;
3、要求用事务的方式删除数据,并回滚
SET autocommit=0;
START TRANSACTION;
DELETE FROM li_1;
ROLLBACK;
第16关、视图
1、查询邮箱中包含a的员工名,部门名和工种信息
①创建视图
CREATE VIEW myemp1
AS
SELECT last_name,d.department_name,j.*
FROM employees e
JOIN departments d
ON e.department_id=d.department_id
JOIN jobs j ON e.job_id=j.job_id ;
②使用视图
SELECT * FROM myemp1;
2、查询各部门的平均工资级别
①创建每个部门平均工资的视图
CREATE VIEW myemp2
AS
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
②使用视图
SELECT grade_level,department_id
FROM myemp2 my
JOIN job_grades j
ON my.`avg(salary)` BETWEEN j.`lowest_sal`AND j.`highest_sal`;
3、查询平均工资最低的部门信息
使用上边创建的视图
SELECT * FROM departments d
WHERE department_id=(
SELECT department_id
FROM myemp2 my
ORDER BY my.`avg(salary)`
LIMIT 1);
4、查询平均工资最低的部门名和工资
使用myemp2视图
SELECT d.`department_name`,my.`avg(salary)`
FROM departments d
JOIN myemp2 my
ON d.`department_id`=my.`department_id`
ORDER BY my.`avg(salary)`
LIMIT 1;
5、创建视图 emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
6、要求将视图 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%';
7、向 emp_v1 插入一条记录,是否可以?
可以原始表中也修改了但是只修改插入的三个字段
8、修改刚才记录中的电话号码为‘0119’
UPDATE emp_v1 SET phone_number=0119;
9、删除刚才记录
DROP VIEW emp_v1;
10、创建视图 emp_v2,要求查询部门的最高工资高于 12000 的部门信息
CREATE VIEW emp_v2
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
11、向 emp_v2 中插入一条记录,是否可以?
不可以
12、删除刚才的 emp_v2 和 emp_v1
DROP VIEW emp_v1,emp_v2
第17关、变量
1、查看所有的全局变量
SHOW GLOBAL VARIABLES;
2、查看部分全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
3、查看指定的全局变量的值autocommit(是否自动提交)、tx_isolation(数据库隔离级别)
SELECT @@global.autocommit;
SELECT @@transaction_isolation;
4、设置全局变量不自动提交(即为autocommit赋值)
SET @@global.autocommit=0;
5、设置数据库隔离级别为read committed
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
6、查看所有的会话变量
SHOW SESSION VARIABLES;
7、查看部分会话变量(包含char)
SHOW SESSION VARIABLES LIKE '%char%';
8、查看指定的会话变量的值autocommit、tx_isolation
SELECT @@session.autocommit;
9、创建用户变量@name='li'
SET @name='li';
10、创建一个局部变量
SET name='li';
第18关、存储过程
1、创建存储过程 插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin
VALUES(1,'张三'),(2,'李四'),(3,'王五'),(4,'麻六'),(5,'徐七');
END $
CALL myp1()$
2、创建存储过程实现根据女神名查询对应的男神信息
CREATE PROCEDOURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id=bo.id
WHERE b.name=beautyName;
END $
CALL myp2(‘柳岩’)$
3、创建存储过程实现,用户是否登陆成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),PASSWORD VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT'';
SELECT COUNT(*) INTO result
FROM `admin`
WHERE admin.username=username
AND admin.`password`=PASSWORD;
SELECT IF(result>0,'成功','失败')是否登陆成功;
END $
CALL myp3('张飞','123456')$
4、根据女神名,返回对应的男神名
CREATE PROCEDURE myp4(IN girlname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
SELECT bo.`boyName`INTO boyname
FROM boys bo
JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE b.`name`=girlname;
END $
call myp4('小昭',@boyname)
select @voyname$
5、根据女神名返回对应的男神名和男神魅力值
CREATE PROCEDURE myp5(IN girlname VARCHAR(20),OUT boyname,OUT userCP INT)
BEGIN
SELECT bo.userCP INTO userCp,bo.boyName INTO boyname
FROM boys bo
JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE b.`name`=girlname;
END$
CALL myp5('小昭',@boyname,@userCP)$
SELECT @boyname,@userCP$
6、传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m = 10$
SET @n = 20$
CALL myp6(@m,@n)$
SELECT @m,@n$
7、创建存储过程或函数实现传入用户名和密码,插入到 admin 表中
CREATE PROCEDURE myp7(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
INSERT INTO admin(`username`,`password`) VALUES(username,PASSWORD);
END $
CALL myp7('lishao','666666')$
8、创建存储过程或函数实现传入女神编号,返回女神名称和女神电话
DELIMITER $
CREATE PROCEDURE myp8(IN id INT, OUT bName VARCHAR(20), OUT bphone INT)
BEGIN
SELECT b.name, b.phone INTO bName, bphone
FROM beauty b
WHERE b.id = id;
END$
DELIMITER ;
call myp8(1,@bName,@bphone);
select @bName,@bphone;
9、创建存储存储过程或函数实现比较两个女神生日,返回大小
DELIMITER $
CREATE PROCEDURE myp9(IN name1 VARCHAR(20), IN name2 VARCHAR(20))
BEGIN
DECLARE birthday1 DATE;
DECLARE birthday2 DATE;
DECLARE result VARCHAR(20);
SELECT borndate INTO birthday1 FROM beauty WHERE beauty.`name` = name1;
SELECT borndate INTO birthday2 FROM beauty WHERE beauty.`name` = name2;
IF birthday1 > birthday2 THEN
SET result = 'younger';
ELSEIF birthday2 > birthday1 THEN
SET result = 'older';
ELSE
SET result = 'equal';
END IF;
-- 这里可以添加一个输出结果的语句,例如使用 SELECT 来返回 result 变量的值
SELECT result;
END $
DELIMITER ;
10、创建存储过程或函数实现传入一个日期,格式化成 xx 年 xx 月 xx 日并返回
DELIMITER $$
CREATE PROCEDURE FormatDateToChineseProc(IN dateInput DATE, OUT formattedDate VARCHAR(255))
BEGIN
SET formattedDate = DATE_FORMAT(dateInput, '%Y年%m月%d日');
END$$
DELIMITER ;
11、创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
如 传入 :小昭 返回: 小昭 and 张无忌
DELIMITER $$
CREATE PROCEDURE GetGoddessAndGodProc(IN goddessName VARCHAR(255), OUT result VARCHAR(255))
BEGIN
DECLARE godName VARCHAR(255);
SELECT bo.boyName INTO godName
FROM boys bo
JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE b.name = goddessName;
-- 设置格式化的字符串结果
SET result = CONCAT(goddessName, ' and ', godName);
END$$
12、创建存储过程或函数,根据传入的条目数和起始索引,查询 beauty 表的记录
CREATE PROCEDURE myp10(IN a INT,IN b INT)
BEGIN
SELECT *
FROM employees
LIMIT a,b;
END$
第19关、函数
1、返回公司的员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURN INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM employees;
RETURN c;
END $
DELIMITER ;
2、根据员工名,返回他的工资。
DELIMITER $
CREATE FUNCTION myf2(empName varchar(20)) RETURN DOUBLE
BEGIN
SET@sal = 0;
SELECT salary INTO @sal
FROM employees
WHERE last_name=empName;
RETURN @sal;
END $
DELIMITER ;
3、根据部门名,返回该部门的平均工资。
DELIMITER $
CREATE FUNCTION myf3( depName VARCHAR(20)) RETURN DOUBLE
BEGIN
DECLARE avg_sal DOUBLE;
SELECT AVG(salary) INTO avg_sal
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name=depName;
RETURN avg_sal;
END $
DELIMITER;
4、创建函数,实现传入两个 float,返回二者之和
DELIMITER $
CREATE FUNCTION myf4(a FLOAT,b FLOAT) RETURN FLOAT
BEGIN
SET@sum = NONE;
SELECT a+b INTO @sum;
RETURN @sum
END $
DELIMITER;
5、创建函数,实现传入工种名,返回该工种的员工人数
DELIMITER $
CREATE FUNCTION myf5(job_title VARCHAR(20)) RETURN INT
BEGIN
DECLARE sum_p = 0;
SELECT COUNT(*)
FROM employees e
JOIN jobs j
ON e.job_id = j.job_id
WHERE j.job_title = job_title;
END $
DELIMITER;
6、创建函数,实现传入员工名,返回该员工的领导名
DELIMITER $
CREATE FUNCTION myf5(NAME VARCHAR(20)) RETURN VARCHAR(20)
BEGIN
DECLARE mana_name VARCHAR(20);
SELECT em.lastname INTO mana_name
FROM employees e
JOIN employees em
ON e.manager_id=em.employee_id
WHERE e.last_name = NAME;
END $
DELIMITER;
第20关、流程控制结构
1、很有难度~~~
已知表 stringcontent
其中字段:
id 自增长
content varchar(20)
向该表插入指定个数的,随机的字符串