USE test;
/*1.创建账务表 id name mony*/
CREATE TABLE zhangwu(id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(200),
money DOUBLE);
/*2.插入数据*/
INSERT INTO zhangwu VALUES(1,'吃饭支出',666);
INSERT INTO zhangwu VALUES(2,'睡觉支出',666);
INSERT INTO zhangwu VALUES(3,'衣服支出',666);
INSERT INTO zhangwu VALUES(4,'吃饭支出',666);
INSERT INTO zhangwu VALUES(5,'吃饭支出',666);
UPDATE zhangwu SET money=888 WHERE id=2;
UPDATE zhangwu SET money=766 WHERE id=3;
/*3.从zhangwu表中选出id sname */
SELECT id FROM zhangwu ;
SELECT sname FROM zhangwu ;
/*4.把zhangwu表中的sname去重 */
SELECT DISTINCT sname FROM zhangwu ;
/*5.别名 列名查询 使用关键字as*/
/*查询money列,定义新的列名m*/
SELECT money AS m FROM zhangwu ;
SELECT money AS ling FROM zhangwu ;
/*6.对列进行运算*/
/*从zhangwu表中挑选sname ，所有money+1000*/
SELECT sname ,money+1000 FROM zhangwu ;
/*7.数据条件的查询 where， 格式：select 字段 from 表名 where 条件*/
/*从zhangwu中挑选出sname为吃饭支出的记录*/
SELECT * FROM zhangwu WHERE sname='吃饭支出';
/*从zhangwu中挑选出money大于等于100的记录*/
SELECT * FROM zhangwu WHERE money>=100;
/*从zhangwu中挑选出money在600到700之间的记录*/
SELECT * FROM zhangwu WHERE money BETWEEN 600 AND 700;
SELECT * FROM zhangwu WHERE money>=600 AND money<700;
/*8.模糊查询*/
/*从zhangwu表中挑选出sname中有支出的记录*/
SELECT * FROM zhangwu WHERE sname LIKE '%支出%';
/*从zhangwu表中挑选出sname中有五个字符的记录*/
SELECT * FROM zhangwu WHERE sname LIKE '_____';/*五个下划线*/
/*从zhangwu表中挑选出sname中不为空的记录*/
SELECT * FROM zhangwu WHERE sname IS NOT NULL;
/*9.排序查询 格式：oderby 列名 asc升序 desc降序*/
SELECT * FROM zhangwu ORDER BY money ASC;
SELECT * FROM zhangwu ORDER BY money DESC;
/*10.聚合函数 count 对表中的数据的个数求和*/
SELECT COUNT(*) AS '总数' FROM zhangwu ;
/*11.IN关键字 ，在zhangwu的money里挑选出66,666，700记录*/
SELECT * FROM zhangwu WHERE money IN (66,666,700);