接上 上篇文章继续
查询
# 比较运算# 根据WHERE条件查找数据: = > < >= <= !=select * from t_hero where age < 30;# 逻辑判断# AND : 指定多个条件,所有条件必须满足select * from t_hero where age > 20 and state='吴';# OR : 指定多个条件,满足任意一个条件即可select * from t_hero where age > 20 or state='吴';# NOT:不满足指定条件,select * from t_hero where not age > 30;select 列1,列2,... from 表名;例:select id,name from t_hero;说明:主键列是自动增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准全列插入:值的顺序与表中字段的顺序对应insert into 表名 values(...)例:insert into students values(0,'郭靖',1,'蒙古','2015-1-2');部分列插入:值的顺序与给出的列顺序对应insert into 表名(列1,...) values(值1,...)例:insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2015-3-2');上面的语句一次可以向表中插入一行数据,还可以一次性插入多行数据,这样可以减少与数据库的通信全列多行插入:值的顺序与给出的列顺序对应insert into 表名 values(...),(...)...;例:insert into classes values(0,'python'),(0,'linux'),(0,'mysql'),(0,'js');insert into 表名(列1,...) values(值1,...),(值1,...)...;例:insert into students(name) values('杨康'),('杨过'),('小龙女');
删除
delete from 表名 where 条件例:delete from students where id=5;
修改
update 表名 set 列1=值1,列2=值2... where 条件例:update students set gender=0,hometown='古墓' where id=5;
模糊查询
# 模糊查询 like# % : 代表任意多个的任意字符select * from t_hero where name like "%张飞%";# _ : 代表一个任意字符,如果多个_ 则表示多个任意字符select *from t_hero where name like "_张飞_";
固定范围查询:
# 范围查询: IN 表示查询的值指定的集合内;select *from t_hero where age in (30, 45);# 范围查询:NOT IN 表示查询的字符不在指定的集合内select *from t_hero where age not in (30, 45);
空值查询
# IS NULL :表示查找数据为空的数据select *from t_hero where state is null;# IS NOT NULL: 表示查找非空的数据select *from t_hero where state is not null;
聚合函数
# count :统计个数,select count(*) as "年龄大于30的人数"from t_hero where age > 30;# max:统计某个列里的最大值,比如ageselect max(age) as "蜀国的最大年龄"from t_hero where state = "蜀";# min:统计某个列里的最小值select min(age) as "魏国的最小年龄" from t_hero where state = "魏";# sum: 统计某一列的数据之和,as 可以给查出的数据列起别名select sum(age) as '魏国年龄总和' from t_hero where state = "魏";# avg:统计某一列数据的平均值select avg(age) as '魏国年龄平均数' from t_hero where state = "魏";
GROUP BY 分组操作
1. 单独使用:直接对数据进行分组(单独使用没有任何意义)select * from t_hero group by state;2. GROUP_CONCAT:对数据进行分组,并通过GOURP_CONCAT() 将指定列数据拼接到一起select state, group_concat(name) from t_hero GROUP BY state;3. 聚合函数:对数据进行分组,分组后的数据根据查询语句的聚合函数来统计指定的数据SELECT gender, sum(age) FROM t_hero GROUP BY gender;4. HAVING : 限制输出结果(对select 查询的结果进行限制输出):# having是对最后select 的结果进行限制输出,having的字段必须在select查询的字段里SELECT state, count(*) as num FROM t_hero GROUP BY state HAVING num >= 3;SELECT state, count(*) as num FROM t_hero GROUP BY state HAVING state = '蜀';
排序:ORDER BY
# order by 对指定的列进行排序,# 默认是升序(从小到大), ASC (ascend)select * from t_hero ORDER BY id;select * from t_hero ORDER BY id ASC;# DESC 指定为降序(从大到小)select * from t_hero ORDER BY id DESC;# 配合where条件进行排序select * from t_hero where state='蜀' and gender='男' ORDER BY age DESC;