数据库初级中级练习题

数据库初级中级练习题

部门表（dept），包含部门编号（id）和部门名称（name）字段，主键为部门编号。
职位表（job），包含职位编号（id）和职位名称（title）字段，主键为职位编号。
员工表（emp），包含员工编号（id）、员工姓名（name）、性别（sex）、年龄（age）、入职日期（hiredate）、月薪（salary）、奖金（bonus）以及电子邮箱（email）、部门编号（did）、职位编号（jid）、上级领导编号（mid）。主键为员工编号，部门编号字段是引用部门表的外键，职位编号字段是引用职位表的外键，上级领导编号字段是引用员工表自身的外键。
create database one 
use one
--建员工表
CREATE table emp(
id int,
name varchar(20),
sex varchar(10),
age int,
hiredate DATE,
salary int,
bonus int,
email varchar(50),
did int,
jid int,
mid int
)
-- 如何查看员工表中的姓名和性别？
SELECT name,sex from emp
--如何查看员工表中的所有字段？
SELECT * from emp
-- 如何知道每个员工一年的总收入？
SELECT name,salary*12 FROM emp
--如何为查询结果指定别名(不会)
SELECT name AS "姓名",salary*12 AS "年薪" FROM emp
--怎么查看女员工的信息？
SELECT * from emp WHERE sex = "女"
--如何查看月薪范围位于 8000 到 12000 之间的员工？
SELECT * from emp WHERE salary BETWEEN 800 and 12000
--查询员工中叫做“张三”、“李四” 或“张飞”的信息。
SELECT * FROM emp WHERE name in("张三"，"李四","张飞")
--查询员工的姓名里有个“云”字的员工信息？
SELECT * from emp WHERE NAME like "%云%"
--有些员工有奖金（bonus），另一些没有。怎么查看哪些员工有奖金？(不会)
SELECT name,bonus from emp WHERE bonus is not null;
--如何查看 2010 年 1 月 1 日之后入职的女员工呢？(不会)
SELECT name,sex,hirdate FROM emp where sex = "女" and hirdate >date "2010-01-01"
--查看员工信息的时候，按照薪水从高到低显示？
SELECT * FROM emp ORDER BY salary desc;
--薪水一样多的员工，希望再按照奖金的多少进行排序？
SELECT * FROM emp ORDER BY salary desc,bonus desc
--员工的姓名是中文，如何按照姓名的拼音顺序进行排序？
SELECT name FROM ORDER BY CONVERT(name USING GBK)
--薪水最高的 3 位员工都有谁？
SELECT name,salary FROM emp ORDER BY salary desc limit 0,3;
--工资信息比较敏感，不宜直接显示。按照范围显示收入水平，小于 10000 显示为“低收入”，大于等于 10000 并且小于 20000 显示为“中等收入”，大于 20000 显示为“高收入”。如何使用 SQL 实现？
SELECT name
    case when salary < 10000 then "低收入"
    case when salary <20000 then "中等收入"
    else "高收入"
 end as "薪水等级"
 from emp;
 --如何统计员工的数量、平均月薪、最高月薪、最低月薪以及月薪的总和？
SELECT count(*),avg(salary),max(salary),min(salary),sum(salary) from emp;
--以下两个 COUNT 函数返回的结果是否相同？
SELECT count(*),count(bouns) from emp;
--统计每个部门的员工的数量、平均月薪？
SELECT id,count(*),avg(salary) from emp GROUP BY did;
--如果只想查看平均月薪大于 10000 的部门，怎么实现？
SELECT id,avg(salary) from emp GROUP BY id HAVING avg(salary) >10000;
--查询月薪超过 5000 的，员工数量大于 5的部门？
SELECT did ,count(*) AS 员工数量 from emp where salary >5000 GROUP BY did HAVING count(*) >5;
--SQL 高级查询
--什么是连接查询？SQL 中有哪些连接查询？
join;
inner join:内连接  显示公共的数据
left join：左连接  以左表数据为主右边没有数据显示为null
right join：右连接 以右表数据为主左边没有数据显示为null
--通过内连接查询员工名称及其所在的部门名称？’
SELECT d.id,d.name,e.name from emp e inner join dept d on (e.id = d.id);
SELECT d.id,d.name,e.name from emp e,dept d where e.id = d.id;
--统计每个部门的员工数量，同时显示部门名称信息。如何使用连接查询实现？
SELECT d.name,count(*) from dept d LEFT join emp e on e.did = d.id; --统计每个部门的员工数量，同时显示部门名称信息。如何使用连接查询实现？
SELECT d.name,count(e.name) from dept d LEFT JOIN emp e on e.did = d.id GROUP BY d.id;
--如何知道每个员工领导的姓名（mid）
SELECT e.name as "员工姓名"，m.name AS "领导姓名" from emp e LEFT JOIN emp m on e.mid = m.id
--子查询
--查询出月薪大于员工平均月薪的员工？
SELECT name,salary from emp where salary >(swelect avg(salary) from emp);
--26. 哪些员工的月薪高于本部门的平均值？
SELECT e.name,e.salary,d.salary from emp e (select did,avg(salary) a.salary from emp GROUP BY did) d where e.salary > d.salary and e.did = d.did;
--27.显示员工信息时，增加一列，用于显示该员工所在部门的人数
select name,(SELECT count(*) from emp where did = e.did) as
--28. 找出哪些部门中有女性员工？
SELECT * from dept d where EXISTS (SELECT 1 from emp e where e.sex ="女" AND e.id = d.id );
--char 和varchar的区别
char的长度不可变，varchar长度可变
--DROP TABLE 和 TRUNCATE TABLE 的区别？
drop table 用于从数据库中删除表，包括表中的数据和表结构本身。同时还会删除与表相关的所有对象，包括索引，约束以及访问该表的授权。truncate table 只是快速删除表中的所有数据，回收表中占用的空间，但会保留表的结构。
--81. MySQL 中的 InnoDB 和 MyISAM 存储引擎有什么区别？
主要的存储引擎包括 InnoDB 和 MyISAM。自从 MySQL 5.5 版本之后，默认使用 InnoDB 存储引擎。
InnoDB 存储引擎支持事务（ACID），提供了事务提交、回滚以及故障恢复能力，能够确保数据不会丢失。InnoDB 支持行级锁和多版本一致性的非锁定读取，能够提高并发访问和性能。InnoDB 使用聚集索引存储数据，能够减少使用主键查找时的磁盘 I/O。另外，InnoDB 还支持外键约束，能够维护数据的完整性。
MyISAM 存储引擎数据文件占用的空间更小。MyISAM 采用表级锁，限制了同时读写的性能，通常用于只读或者以读为主的应用。
--删除数据时，DELETE 和 TRUNCATE 语句的区别？
DELETE    TRUNCATE
用于从表中删除指定的数据行。    用于删除表中的所有行，并释放包含该表的存储空间。
删除数据后，可以提交或者回滚。    操作无法回滚。
属于数据操作语言（DML）。    属于数据定义语言（DDL）。
删除数据较多时比较慢。    执行速度很快。
通常来说，使用 DELETE 语句删除数据时需要指定一个 WHERE 条件，否则会删除表中所有的数据；使用 TRUNCATE 语句需要小心，因为它会直接清空数据。