delete from tableName where columnName = columnNameValue
逻辑删除
alter table tableName add is_delete bit default 0; – 在加上一个字段代表是否删除(0未删除,1已删除)
数据表去重
distinct
模糊查询 关键字like
%代替0+个字符; _ 代替一个字符
模糊查询关键字rlike
这个使用的是正则表达式
范围查询 关键字in
select * from tableName where columnName2 in (a,b,c);
select columnName from tableName where columnName2 not in (a,b,c);
范围查询 关键字between …and…
select * from tableName where columnName2 between m and n;
select * from tableName where columnName2 not between m and n;
从小到大排序 asc (默认为这个)
select from tableName order by columnName asc
从大到小排序 desc
select * from tableName order by columnName desc
columnName1Value相同,则比较第二行
select * from tableName order by columnName1, columnName2
聚合函数
count 统计满足条件的个数
select count( * ) from tableName where columnName = columnNameValue
max 取出满足条件的最大值
select count( columnName1 ) from tableName where columnName2 = columnName2Value
min 取出满足条件的最小值
sum 计算满足条件的值的总和
avg 计算满足条件值的平均值
round(m,n) 其中m位数字,n为m的小数部分的保留位数
group by分组
select columnName from tableName group by columnName
分组中不和聚合函数将毫无意义
select columnName1 , count(columName2) from tableName group by columnName1
条件筛选 having 和 where
having
where
是对select出来的结果进行筛选
是对原表格进行筛选
select columnName1, group_concat(columnName2), agv(columnName3) from tableName having avg(columnName) < columnName
分页
limit m
输出符合条件的前m个数据,若不到m个数据则全部输出
limit m, n
输出符合条件的第(m+1)个数据到(m+n+1)个数据
链接查询
inner join … on 内链接(取交集)
select * form tableName1 inner join tableName2 on tableName1.tableName2_id = tableName2.id
left join … on 左链接(将左边的表全取出来,右边表的属性没有则显示为null)
select * form tableName1 left join tableName2 on tableName1.tableName2_id = tableName2.id
right join … on 右链接(将右边的表全取出来,左边表的属性没有则显示为null)
select * form tableName1 right join tableName2 on tableName1.tableName2_id = tableName2.id
-- 示例一
create table students(
id int unsigned primary key not null auto_increment,
name varchar(30),
age tinyint unsigned default 0,
gender enum("男", "女", "保密") defalut "保密",
high decimal(5, 2)
);