-- 修改数据 update `user` set password='haojin'where name='haojin';
-- 删除数据 deletefrom `user` where name='haojin';
5.5. 查询数据
1 2 3 4 5 6 7 8 9
-- 查询数据 select*from `user` where name='haojin'and password='voidking'; select*from `user` where name='haojin'or password='haojin'; select*from `user` where name<>'haojin0'; select*from `user` where name like'%ao%'; select*from `user` orderby id asc; select*from `user` orderby id desc; select*from `user` orderby id desc limit 2; select*from `user` where unix_timestamp(update_time) < unix_timestamp('2020-03-01 10:14:51');
5.6. 查看表是否被锁
1 2 3 4 5
-- 查看表是否被锁 SHOWOPEN TABLES; SHOWOPEN TABLES WHERE In_use >0; SHOW PROCESSLIST; SHOWTABLE STATUS LIKE'your_table_name';
select user.name, user_detail.telephone fromuserinnerjoin user_detail on user.id=user_detail.user_id where user.id=1; -- or select U1.name, U2.telephone fromuseras U1 innerjoin user_detail as U2 on U1.id=U2.user_id where U1.id=1;
select user.name, user_detail.telephone, role.name fromuser leftjoin role on user.role_id=role.id leftjoin user_detail on user.id=user_detail.user_id where user.id=1;
9.3. 查询重复记录
需求:查询user表中的重名记录
1 2 3
select * from user where name in (select name from user group by name having count(name) > 1);
9.4. 替换字符串
需求:用户表(user)的个性签名字段(info)是字符串,字符串中都包含“my name is haojin”,现在需要把“my name is haojin”都替换成“my name is voidking”。
1
updateuserset info=replace(info,"my name is haojin","my name is voidking") where info like "%my name is haojin%";
#!/bin/bash grep -v "^$" mobiles.txt | whileread line do old=`echo$line | awk '{print $1}'` new=`echo$line | awk '{print $2}'` echo"select CONCAT('update user set mobiles=\"',mobiles,',${new}\" where name=\"',name,'\";') from user where mobiles like \"%${old}%\";" done