菜鸟教程学习，基本满足测试SQL需要

菜鸟教程地址：https://www.runoob.com/sql/sql-tutorial.html

测试SQL

查询自服务用户所属的客户经理：

SELECT idUser,user FROM user WHERE idUser = (SELECT creator FROM customer_info LEFT JOIN user ON customer_info.id = user.customer WHERE user = 'xmsy20')

查询客户经理下的客户：

SELECT name FROM customer_info WHERE creator = (SELECT idUser FROM user LEFT JOIN customer_info ON user.customer = customer_info.id WHERE user = 'xmdls20')

查询客户折扣/产品名称：

SELECT user,prod,discount FROM discount LEFT JOIN user ON discount.customer = user.customer WHERE user = 'jztest'

SELECT * FROM prod WHERE id = '9'

查询账户地区：

SELECT city.city FROM city RIGHT JOIN user ON user.city = city.id WHERE user = 'xmsy20'

查询地区的所有用户：

SELECT user.user FROM user RIGHT JOIN city ON user.city = city.id WHERE city.city = '武汉市'

修改镜像cloudinit类型：

update virtualimage set cloudinit = true where name = 'centos7.6-cloudinit6';

select idImage,name,pathName,type,cloudinit from virtualimage

设置账号为试用:

update user set customerType='TRIAL' where user='yyf(登录账号)';

设置账号为商用:

update user set customerType='COMMERCIAL' where user='yyf(登录账号)';

修改账户为立即退订：

update user set fortest = true where user = 'xll20'

查询用户下的所有定时任务：

SELECT id,excuteTime,expirationOperate,idList,user.user,TaskAction,taskState,type FROM timerTask LEFT JOIN user ON timerTask.operatorId = user.idUser WHERE user.user = 'xmsy20'

查询产品规格：

SELECT * FROM `spec` WHERE id in ('338','339','340','341')

查询用户下的虚拟机数量：

SELECT user.user,COUNT(virtualmachine.idVM) AS num FROM user LEFT JOIN virtualmachine ON user.idUser = virtualmachine.idUser WHERE user.user = 'xmsy20'

查询用户所有服务器下的虚拟机列表：

SELECT user.user,virtualmachine.name,vdrpPort,vdrpIP,openStatus FROM user LEFT JOIN virtualmachine ON user.idUser = virtualmachine.idUser WHERE user.user = 'xmsy20'

查看用户指定服务器下的虚拟机：

SELECT user.user,virtualmachine.name,vdrpPort,vdrpIP,openStatus FROM user LEFT JOIN virtualmachine ON user.idUser = virtualmachine.idUser WHERE user.user = 'xmsy20' HAVING virtualmachine.vdrpIP = '192.168.2.242'

修改用户云主机开通状态：

UPDATE virtualmachine set openStatus = 'AVAILABLE' WHERE name = 'wdnmd1-c9d5c9b0-46c3-4e19-bf04-930c9c8f6504'

查询服务器下的所有网卡：

SELECT id,physicalmachine.name AS servername,network_interface.name,speed FROM network_interface LEFT JOIN physicalmachine ON network_interface.physicalmachine_id = physicalmachine.idPhysicalMachine WHERE physicalmachine.name = 'xm_node150'

按服务器网卡名称查询/修改网速：

SELECT id,name,physicalmachine_id,speed FROM network_interface where name in ('eth1','eth2','eth3','eth4') HAVING physicalmachine_id = '25'

UPDATE network_interface set speed = '10000' where name in ('eth3','eth4') and physicalmachine_id = '25'

查询操作日志：

SELECT * from metering ORDER BY idMeter DESC

查询用户下的裸金属服务器信息：

SELECT bareMetal.idBareMetal,expirationTime,orderTime, openStatus,bareMetal.name AS servername,user.user,spec FROM bareMetal LEFT JOIN user ON bareMetal.idUser = user.idUser WHERE bareMetal.name = 'test'

UPDATE bareMetal set openStatus = 'AVAILABLE' WHERE name = 'wdnmd'

查询用户下的专属宿主机信息：

SELECT idPhysicalMachine,idState,expirationTime,orderTime,openStatus,physicalmachine.name,user.user,spec FROM physicalmachine RIGHT JOIN user on physicalmachine.idUser = user.idUser WHERE physicalmachine.name = 'node2'

SELECT * FROM spec WHERE id = '194'