mysql了基本语句_mysql基本语句

1.增

use myblog;

show tables;

insert into users(username, `password`, realname) values ('zhangsan', '123', '张三');

2.查

use myblogs;

show tables;

select * from users; --查询所有

select id,username form users; --查询id和username

select * from users where username='zhangsan'; --条件查询

select * from users where username='zhangsan' and `password`='123'; --并且

select * from users where username='zhangsan' or `password`='123'; --或者

select * from users where username like '%zhang%'; --模糊查询

select * from users where password like '%1%' order by id desc; --desc表示倒序，不加默认正序

select * form users where state <> '0'; -- <>表示不等于0

select count(id) as `count` from blogs; -- 查询总数

select * from blogs order by id desc limit 2; -- 查询第一页的两条数据

select * from blogs order by id desc limit 2 offset 2; -- 查询第二页的两条数据

3.改(更新)

如果遇到update users set realname...报错的话，先执行 SET SQL_SAFE_UPDATES = 0; 然后删掉再执行更新操作

update users set realname='李四2' where username='lisi';

4.删

delete from users where username='lisi';

-- 日常开发中我们通常是采用软删除

update users set state='0' where username='lisi';

5. 多表联查

select blogs.*, users.username, users.nickname

from blogs inner join users on users.id = blogs.userid

where users.username = 'lisi'