1 删除数据
例如: 把所有性别为空的数据删除
mysql> delete from student where Gender is null;
例如: 把 ID 为 2,4 的数据删除
mysql> delete from student where ID in (2,4);
2 清空数据表, 只留表结构
mysql> truncate table student;
3 修改数据
mysql> update student set Score=80 where Name="肖清华";
4 统计字符串的字节数 length(), 字符数 char_length()
- mysql> select * from student where char_length(Name)=3; // 查询 Name 是三个字的人, 也可以使用以下方法
- mysql> select * from student where Name like "___";
5 查询指定时间内的数据
- mysql> create table member(
- ID int unsigned not null auto_increment,
- Account varchar(30) not null unique,
- Pass varchar(32),
- Regtime datetime,
- primary key(ID)); // 新创建一个测试表
- mysql> insert into member values(null,"李四","123456","2018-03-08");
- mysql> insert into member values(null,"王五","1234567","2018-02-08");
- mysql> insert into member values(null,"赵六","1234557","2017-02-08");
- mysql> insert into member values(null,"夏明","123457","2017-12-08"); // 插入测试数据
要求: 统计近五天内注册的会员人数
- mysql> select count(*) from member where Regtime between date_add(now(),interval -5 day) and now();
- mysql> select * from member where Regtime between date_add(now(),interval -5 day) and now();
6 格式化输出
mysql> select Account 账号, date_format(Regtime,"%Y 年 %m 月 %d 日") 注册日期 from member;
7 查询当前系统的时间戳, 然后转换成年月日格式
mysql> select unix_timestamp() 系统时间戳;
- mysql> select from_unixtime(1520494242);
- mysql> select date_format(from_unixtime(1520494242),"%Y 年 %m 月 %d 日");
8 将字符串连接
- mysql> select concat("年龄:",Age) from student;
- mysql> select concat_ws("-","年龄",Age) from student; // 也是字符的拼接, 只不过在字符中间加上了 -
9 重复输出, 比如输出十个 "#"
- mysql> select repeat("#",10);
- mysql> select space(10); // 输出十个空格
10 大小写转换
- mysql> select upper(Name) from student where Name like "w%"; //upper 把小写字母转换为大写字母
- mysql> select lower(Name) from student where Name like "w%"; //lower 把大写字母转换为小写字母
11 字符截取
- mysql> select left(Name,1) from student; //left 截取左边第一个字符
- mysql> select right(Address,3) from student; //right 截取右边的字符
- mysql> select mid(Address,2) from student; //mid 从第几个字符开始截取, 这里的首字符是 1
- mysql> select mid(Address,1,3) from student; // 从第几个开始到第几个结束
12if 函数使用
等级: 90 优秀, 70 良好, 60 及格, 以下补考
mysql> select Name 姓名, Gender 性别, Score 成绩, if(Score>=90,"优秀",if(Score>=70,"良好",if(Score>=60,"及格","补考"))) 等级 from student;
13uuid() 生成唯一字符串
mysql> select uuid(),length(uuid()); // 并查看长度
14 格式化数字
mysql> select format(1000,2); // 将显示 1000.00
15 将 IP 转换为数字
mysql> select inet_aton("192.168.200.101");
16 将数字转换为 IP
mysql> select inet_ntoa("3232286821");
来源: http://www.bubuko.com/infodetail-2520906.html