-- 查询表中所有数据
SELECT * FROM tb_user;
-- 给全部列添加数据(可以把所有列名省略不写)
INSERT into tb_user (id,username,password,address,gender) VALUES (1,"张三",123456,"河南","男");
INSERT into tb_user (id,username,password,address,gender) VALUES (2,"李四",147852,"山东","男");
INSERT into tb_user (id,username,password,address,gender) VALUES (3,"王五",258963,"浙江","男");
INSERT into tb_user (id,username,password,address,gender) VALUES (6,"小红",147632,"北京","女");
-- 给部分列添加数据
INSERT into tb_user (id,username) VALUES (4,"赵六");
-- 给所有列添加数据
INSERT into tb_user (id,username,password,address,gender) VALUES (1,"张三",123456,"河南","男"),(2,"李四",147852,"山东","男"), (3,"王五",258963,"浙江","男");
-- 修改表中数据
UPDATE tb_user SET password=123123 WHERE username="王五";
-- 删除数据
DELETE FROM tb_user WHERE id=4;
-- 查询多个列
SELECT username, gender FROM tb_user;
-- 去重
SELECT DISTINCT gender FROM tb_user;
-- 起别名
SELECT username as 用户名, gender as 性别 FROM tb_user;
-- 查询id=3的人员信息
SELECT * FROM tb_user WHERE id=3;
-- 查询id在1-3之间的人员信息
SELECT * FROM tb_user WHERE id BETWEEN 1 AND 3 ;
-- 查询id不等于1的人员信息
SELECT * FROM tb_user WHERE id !=1;
-- 查询gender为null的人员信息
SELECT * FROM tb_user WHERE gender is null;
-- 查询gender不为null的人员信息
SELECT * FROM tb_user WHERE gender is not null;
-- 查询姓名为张的人员信息
SELECT * FROM tb_user WHERE username LIKE "张%";
-- 查询第二个字为三的人员信息
SELECT * FROM tb_user WHERE username LIKE "_三%";
-- 查询名字中含有六的人员信息
SELECT * FROM tb_user WHERE username LIKE "%六%";
-- 按照id升序排列
SELECT * FROM tb_user ORDER BY id ASC;
-- 按照id降序排列
SELECT * FROM tb_user ORDER BY id DESC;
-- 查询人员数量
SELECT COUNT(*) FROM tb_user;
-- 查询id最大值
SELECT max(id) FROM tb_user;
-- 求按照男女分组求各自id(必须大于等于一)平均值以及各组的人数，留下人数大于一的
SELECT gender, AVG(id),count() FROM tb_user WHERE id>=1 GROUP BY gender HAVING count()>1;
-- 每页显示一条数据，查询第二页
SELECT * FROM tb_user LIMIT 1,1;
