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开始   日期时间函数   条件判断 和系统函数 

select id ,user ,IF(score>=60,'及格','不及格') from user
select id ,user ,CASE WHEN score>60 THEN '及格' WHEN score=60 THEN '刚及格' ELSE '不及格' END from user

 其他常用函数