use table_name ==切换表
插入数据
insert into table_name (field1,field2..)values(value1,value2...);
insert into table_name set field1=value1,field2=value2...;(键值对赋值)
插入多条数据
insert into table_name values
(value1,value2...),
(value11,value22...),
(value111,value211...);
修改表记录
update table_name set field1=value1,field2=value2... where 语句;
删除表记录
delete from table_name where 语句;--如不加where 则删除整张表;
delete 是一行一行的删除记录
truncate table table_name;--是删除整张表再创建新表
查询表记录
select distinct(去重) field1 as 别名,field2 别名 ,field3.... from table_name where 语句 > < <= >= <> != 不等于;
between .. and ..
in ()
field like "value%"--like "tom%" 模糊匹配
field like "value_"--模糊匹配一个字符
order by 指定排序的列 --可以是表中的列名，也可以是select 语句后指定的别名
先过虑再排序
select * field1,field2... from table_name where 语句 order by field desc/ asc
group by分组查询 与having 子句
--注意，按分组条件分组后每一组只会显示第一条记录
group by 2 按字段位置来分
es: select name,sum(js) from table_name group by name having sum(js)>150;
having 与 where 两者都是对查询结果进行进一步的过滤，差别有：
1、where 语句只能用在分组之前 的筛选，having可以用在分组之后筛选
2、使用where语句的都可以用having 进行替换
3、having中可以使用聚合函数(如：sum,max，count,min,AVG)，where中就不行
ifnull(js ,0)--ifnull 将null置为0
limit 1,4 ---只查看1-4行
select [distinct] *|field ,field1... from table_name
where 条件
group by field
having 筛选
order by field
limit 限制条数
REGXP---使用正则查询
select * from table_name where emp_name REGXP ‘^yu‘; --以yu开头
‘yun$‘;--以yun结尾
‘m(2)‘;--有两个m
select from where group by having order by
执行顺序：
from where select group by having order by