mysql学习笔记(基础)

原创 王明昌  2018-08-16 19:28 
  • 修改编码方式 ALERT DATABASE aaa DEFAULT CHARACTER SET=UTF8;
  • 使用数据库 use aaa
  • 查看当前数据库 select database()
  • 删除数据库 drop database aaa

in / not in
select *from u where id in(1,2,4,5)

like / not like
select *from u where user like 'aa%'
select *from u where user like '_aa%'
select *from u where user like '___' (_表示一位)

and / or

group by
select *from u group by pid (直接分组,只显示每个组的第一条数据)
select *from u group by pid,aid (多字段分组)
select id,sex,GROUP_CONCAT(username) from u group by sex (将username放到一起逗号隔开,)

count 不统计null的值
max
min
avg
sum
with rollup 统计上面查询的数值的总和(不能统计的为null)

having 要配合这group by才有意义
select sex group_concat(user) as user,
count(),
max(age),
sum(age)
from u
group by sex 一次筛选
having count(
)>2 and max(age)>60 having二次筛选

order by
select *from u order by sort desc,id desc

limit
select *from u limit 0,1
select *from u limit 1

更新前三条数据(不能设置偏移量)
update u set age=age+10 limit 3;
update u set age=age+10 order by sort desc limit 3;

删除
delete from u where sex='男' order by age desc limit 1;

连接查询
内连接(相当于where)
select * from * from uu INNER JOIN bb ON uu.id=bb.id
select * from uu JOIN bb ON uu.id=bb.id where ....
外链接(什么连接,以什么为主)
select * from * from uu LEFT JOIN bb ON uu.id=bb.id

联合查询union union all
union去掉相同的记录

子查询
IN
select id ,user from employee where depid in(select id from uu)
比较运算符
[not] exists
any/some/all
正则查询
REGEXP
select * from uu where username REGEXP "t"
开始

$结束
.一个任意字符
运算符
算术运算符
比较运算符
逻辑运算符
select id,user is null from uu

数学函数库

字符串函数
字符串的起始点从1开始

日期时间函数

条件判断 和系统函数

其他常用函数

历史上的今天:

本文地址:https://www.wangmingchang.com/4151.html
关注我们:请关注一下我们的微信公众号:扫描二维码王明昌博客的公众号,公众号:wmc365
版权声明:本文为原创文章,版权归 王明昌 所有,欢迎分享本文,转载请保留出处!

发表评论


表情