### 數據表名稱：**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'; -- 刪除 ```