mysql学习笔记(基础)

• 修改编码方式 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开始




日期时间函数



条件判断 和系统函数


<code>select id ,user ,IF(score&gt;=60,'及格','不及格') from user

select id ,user ,CASE WHEN score&gt;60 THEN '及格' WHEN score=60 THEN '刚及格' ELSE '不及格' END from user

</code>



其他常用函数