MySQL | SQL 基本语句

管理表记录

"增、删、改、查"

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

一次插入1记录给所有记录赋值 [1]
一次插入多条记录给所有记录赋值 [2]
一次插入1记录给指定记录赋值 [3]
一次插入多条记录给指定记录赋值 [4]

[1] insert into db.tb values(字段值列表);
eg: -- insert into userdb.user values()
[2] insert into db.tb values(字段值列表),(字段值列表)...;
[3] insert into db.tb(字段名列表) values(字段值记录);
[4] insert into db.tb(字段名列表) values(字段值列表),(字段值列表)...;

delete 删 (删除表记录)

删除所有行[ delete from db.tb ;]
删除特定行[ delete from db.tb where ... ;]

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

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

[1] update db.tb set 字段名=值
[2] update db.tb set 字段名=值,字段名=值 where 条件;

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

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" ;

- 查询分组 [ group by 字段名]

    - select shell from db.tb where name like "%r" group by shell;

- 查询排序 [ order by 字段名 asc | desc ] 默认升序asc

    - select name, id, from db.tb where uid between 1 and 10 order by uid asc;

- 限制查询显示的行数 [ 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";

where嵌套查询

select 字段名列表 from db.tb where 条件 (select 字段名列表 from db.tb where 条件);

本文链接:

https://blog.zhigu34.cn/archives/12.html
1 + 2 =
快来做第一个评论的人吧~