Python————Mysql练习

question1 数据库软件安装与配置

1. 数据库软件安装与配置
   server 主机安装 mariadb 服务器软件并实现远程登录数据

yum install mariadb-server -y

systemctl start mariadb
systemctl enable mariadb

mysql_secure_installation	# 设置本地密码
systemctl stop firewalld.service		# 关火墙
systemctl disable firewalld.service
mysql -uroot -p		   # 进入数据库
grant all privileges on *.* to root@'172.25.254.68' 
identified by 'westos';	
# 允许172.25.254.68使用root用户登陆数据库，密码为westos

question2 第 N 高的薪水

2. 第 N 高的薪水: 此处要求获取薪水第 2 高的薪水
   

select
(
    select DISTINCT
        Salary 
    from 
        Employee
    order by 
        Salary desc
    limit 1,1 )
as 
    SecondHighestSalary
;

question3 分数排序

3. 分数排序
   编写一个 SQL 查询来实现分数排名。如果两个分数相同，则按照 id 由大到小进行
   排序.

select Id, Score from Score order by Score desc, Id asc;

question4 查找重复的电子邮箱

4. 查找重复的电子邮箱

select Email from Email group by Email having count(*)> 1;

question5 超过经理收入的员工

5. 超过经理收入的员工
   

select a.Name from StaffSalare as a,StaffSalare as b 
where a.ManageId = b.Id and a.Salary > b.Salary;

question6 从不订购的客户

6. 从不订购的客户
   
   

select Name as Customers from Customers 
where not Customers.Id in 
(select Customers from Orders);