额,这个主要是今天回忆的MySQL语法,学了Oracle就忘记了MySQL,之前有一位面试官发SQL Server的测试题,直接gg。尴尬......
/*建立一个入住客人的表*/
CREATE TABLE guestStayInfo(
gsiID INT PRIMARY KEY AUTO_INCREMENT, /*入住信息的编号,唯一主键约束自增长*/
guestSource VARCHAR(50) NOT NULL, /*客户来源*/
CheckIn VARCHAR(20), /*入住时间*/
CheckOut VARCHAR(20), /*退房时间*/
linkman VARCHAR(20), /*联系人*/
orderamount INT, /*订单金额*/
needCash INT, /*需补金额*/
OperatorID INT, /*操作人,外键对应相应的员工*/
CONSTRAINT FK_emp FOREIGN KEY(OperatorID) REFERENCES employee(empID)
)AUTO_INCREMENT=1;
/*尝试插入数据*/
INSERT INTO guestStayInfo(guestSource, CheckIn, CheckOut, linkman, orderamount, needCash, OperatorID)
VALUES ('巴乐兔', '2018/01/02', '2018/04/02', '王先生', 300, 45, 1);
INSERT INTO guestStayInfo(guestSource, CheckIn, CheckOut, linkman, orderamount, needCash, OperatorID)
VALUES ('58同城', '2018/02/03', '2018/04/04', '张三先生', 260, 20, 2);
/*查询所有的数据*/
SELECT * FROM guestStayInfo;
/*添加外链接查看操作人*/
SELECT * FROM guestStayInfo g INNER JOIN employee e WHERE g.OperatorId=e.empId;
SELECT gsiId AS '客人记录编号', guestSource AS '客人来源', CheckIn AS '入住时间', CheckOut AS '退房时间', linkman AS '联系人',
orderamount AS '需付押金', needCash AS '补付押金', empName AS '操作人姓名' FROM guestStayInfo g INNER JOIN employee e WHERE g.OperatorId=e.empId;
/*查看所有来自58同城的客人*/
SELECT * FROM guestStayInfo WHERE guestSource='58同城';
/*将所有的客人信息根据补付押金由低到高输出*/
SELECT * FROM guestStayInfo ORDER BY needCash DESC ;
/*将所有客人信息根据需付押金由高到低输出*/
SELECT * FROM guestStayInfo ORDER BY orderamount ASC;
/*根据分组将需付押金和补付押金之和由低到高输出*/
SELECT gsiId AS '客人记录编号', guestSource AS '客人来源', CheckIn AS '入住时间', CheckOut AS '退房时间', linkman AS '联系人',
orderamount AS '需付押金', needCash AS '补付押金',(orderamount+needCash) '两者之和' FROM guestStayInfo ORDER BY (orderamount+needCash) DESC;
/*查询在2018/01/01之前入住的客人*/
SELECT * FROM guestStayInfo WHERE CheckIn<'2018/01/01';
/*查询在2018/04/05之后退房的客人*/
SELECT * FROM guestStayInfo WHERE CheckOut>'2018/04/05';
/*查看需付押金的平均数*/
SELECT AVG(orderamount) AS '需付押金平均数' FROM guestStayInfo;
/*查看一共所需要的押金总和*/
SELECT SUM(orderamount) AS '押金总和' FROM guestStayInfo;
/*查看最大补付押金*/
SELECT MAX(orderamount) AS '最大补付押金' FROM guestStayInfo;
/*查看最小补付押金*/
SELECT MIN(orderamount) AS '最小补付押金' FROM guestStayInfo;
/*统计一共有多少行的数量*/
SELECT COUNT(gsiId) FROM guestStayInfo;
额,就是最简单的操作。增删改QAQ没有了╮(╯_╰)╭