SQLyog中创建数据表及相关方法
时间：2021-07-01 10:21:17
帮助过：515人阅读
/*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
;
/*8.模糊查询*/
/*从zhangwu表中挑选出sname中有支出的记录*/
SELECT * FROM zhangwu WHERE sname LIKE ‘%支出%‘
;
/*从zhangwu表中挑选出sname中有五个字符的记录*/
SELECT * FROM zhangwu WHERE sname LIKE ‘_____‘;
/*五个下划线*/
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 ;
SQLyog中创建数据表及相关方法
标签：color zha 划线 incr 排序 使用 聚合 set val
