SELECT @@basedir AS basePath FROM DUAL -- 查找mysql安装目录
USE test;-- 引用数据库
CREATE TABLE zhangwu (
id INT PRIMARY KEY AUTO_INCREMENT, -- 账务ID
NAME VARCHAR(200), -- 账务名称
money DOUBLE -- 金额
);-- 创建结构表
INSERT INTO zhangwu(id,NAME,money) VALUES (1,'吃饭支出',247);
INSERT INTO zhangwu(id,NAME,money) VALUES (2,'工资收入',12345);
INSERT INTO zhangwu(id,NAME,money) VALUES (3,'服装支出',1000);
INSERT INTO zhangwu(id,NAME,money) VALUES (4,'吃饭支出',325);
INSERT INTO zhangwu(id,NAME,money) VALUES (5,'股票收入',8000);
INSERT INTO zhangwu(id,NAME,money) VALUES (6,'打麻将支出',8000);
INSERT INTO zhangwu(id,NAME,money) VALUES (7,NULL,5000);-- 插入数据
SELECT * FROM zhangwu -- 查询全部内容
SELECT NAME,money+10000 AS SUM FROM zhangwu-- 直接查询金额加10000的
SELECT * FROM zhangwu WHERE money BETWEEN 2000 AND 11000 -- 查询出金额在2000到11000之间的数据
SELECT * FROM zhangwu WHERE NAME LIKE '%支出' -- 查询出账务名称中带支出的数据
SELECT * FROM zhangwu WHERE NAME LIKE '_____' -- 查询出账务名称中5个字符的
SELECT * FROM zhangwu ORDER BY money ASC -- 以金额升序状态查询出所有数据
SELECT COUNT(*) AS '行数'FROM zhangwu -- 查询出表的行数
SELECT SUM(money) AS '支出总金额' FROM zhangwu WHERE NAME LIKE '%支出'; -- 以总金额字段名称查出所有账务名称中带支出的金额合计
SELECT MAX(money) FROM zhangwu;-- 查出金额合计
SELECT NAME,SUM(money)AS zmoney FROM zhangwu WHERE NAME LIKE '%支出' GROUP BY NAME HAVING zmoney>666 ORDER BY zmoney DESC ;-- 按账务名称中带支出分组查出以降序排列的金额合计
