### 数据表名称：**users** *****  ***** ### 基本命令 ***** ``` show databases; -- 查询数据库 use mynode; show tables; -- 数据表 ``` ### 插入数据 ***** ``` insert into users(username,realname,`password`,createtime,state,age,sex) values('zhangsan','张三2','224455',1625624088234,1,23,'男'); -- 插入数据 ``` ### 查询 ***** ``` select * from users; -- users表全部查询 select age from users; -- users表某列查询 select * from users where state <> 0; -- 不等于0 select * from users where age > 20; -- 大于20 select * from users where age between 21 and 22; -- 查询21-22之间 select * from users where userid in (1,3,5); -- 查询指定userid集合 select * from users where userid not in (2,3); -- 查询非2,3的userid集合 select * from users where username='zhangsan'; -- 查询名称为zhangsan select * from users where username like '%zhang%'; -- 模糊查询 select * from users where username like '%zhang%' order by userid desc; -- 模糊查询，且根据userid倒叙 select * from users order by age desc limit 3; -- 倒叙前三行记录 select * from users where username='zhangsan' and realname='张三'; -- 同时成立 select * from users where username='zhangsan' or realname='张三'; -- 某个成立即可 select count(*) from users where age=22; -- 统计age=22的数量 select state,count(*) from users group by state; -- 分组,统计 select * from users limit 0,2; -- 查询下标0，查询两行，分页 ``` ### 更新 ***** 重点：`SET SQL_SAFE_UPDATES = 0;` ``` SET SQL_SAFE_UPDATES = 0; -- 执行更新操作，如果更新失败，需要执行本命令 update users set sex = '男',state=1 where age = 22; -- 更新 ``` ### 删除 ***** 删除技术`delete from users`比较危险；可以采取**软删除**，如添加特殊字段`state=0`则表示改数据为无效，进行查询时过滤掉`state=0`的数据。 ``` delete from users where username='zhangsan'; -- 删除 ```