mysql消费记录表

mysql消费记录表"/>

mysql消费记录表

insert into,delete,update,select

“增、删、改、查”

insert 增 (向表中插入新纪录)

一次插入1记录给所有记录赋值 [1]

一次插入多条记录给所有记录赋值 [2]

一次插入1记录给指定记录赋值 [3]

一次插入多条记录给指定记录赋值 [4]

[1] insert into ku.biao values(字段值列表);

– insert into userdb.user values()

[2] insert into ku.biao values(字段值列表),(字段值列表)…;

[3] insert into ku.biao(字段名列表) values(字段值记录);

[4] insert into ku.biao(字段名列表) values(字段值列表),(字段值列表)…;

delete 删 (删除表记录)

删除所有行[ delete from ku.biao ;]

删除特定行[ delete from ku.biao where … ;]

update 改(修改记录字段的值)

修改所有记录指定字段的值 [5]

修改指定记录指定字段的值 [6]

[5] update ku.biao set 字段名=值

[6] update ku.biao set 字段名=值,字段名=值 where 条件；

select 查 (单表查询 嵌套查询 多表查询 连接查询)

select * from db.tb ;

select * from db.tb where数值比较 [ 字段名 符号 数字]

[ >、>=、

slelect * from db.tb where id<=10;

字符比较

select from db.tb where user=”root”;

select from db.tb where shell!=”/bin/bash”;

范围内匹配 [ in between..and ]select * from db.tb where id between 1 and 2; (with 1&2)

select * from db.tb where name in (“root”,”apache”…);

select * from db.tb where id not in (100,200,300);

四则运算 [ + - * / % ]select name,uid,gid,uid+gid sum from db.tb;

select name,age,year(now())-age syear from db.tb where name=”root”;

select name,uid,gid,uid+gid sum,(uid+gid)/2 avg from db.tb where name=”bin”;

update userdb.user set age=age+1 where name=tony”;

模糊匹配 [ like %零个或多个null不匹配 _一个字符 ]select name from db.tb where name like ‘____’;//四个字符

select name from db.tb where name like ‘_ a _’;//三个字符a中间

select name from db.tb where name like ‘a%’;//a开头

select name from db.tb where name like “_ % _”;//两个字符

正则匹配 [ ^ $ . [ ] * ]select name from db.tb where name regexp ‘[0-9]’; //含数字

select name from db.tb where name regexp ‘[0-9]$’; //数字结尾

select name from db.tb where name regexp ‘^ [0-9]’; //数字开头

select name from db.tb where name regexp ‘….’; //含四个以上字符

select name from db.tb where name regexp ‘^….$’; //只含四个字符

select name from db.tb where name regexp ‘^r.*t$’; //r开头，t结尾，中间任意

匹配空 [ is null ]select * from db.tb where shell is null;

匹配非空 [ is not null ]

逻辑匹配 [ or and ! ]select * from db.tb where uid=>1000 and shell!=”/bin/bash”;

select * from db.tb where uid=>1000 or shell!=”/bin/bash” ;

查询分组

查询排序[ order by desc/asc]SELECT column_name,column_name

FROM table_name

ORDER BY column_name,column_name ASC | DESC;

限制查询显示的行数 [ limit ]select * from db.tb where uid>10 limit 10 ; //显示结果的前十行

select * from db.tb where uid<10 limit 2,3 ; //显示结果2nd行后开始，显示3行

查询时不显示字段的重复 [ distinct ]select distinct shell from db.tb where uid<10;

查询时可以做数据统计(聚集函数)sum(字段名) //求和select sum(id) from db.tb;

avg(字段名) //平均值select avg(age) from db.tb;

max(字段名) //最大值select min(uid) from db.tb;

min(字段名) //最小值select min(gid) from db.tb;

count(字段名) //计数select count(*) from db.tb where shell=”/bin/bash”;