sql基础语法
1.注释语句:
#单行注释 /*多行注释 注释 */ -- 单行注释,注意有注释符号和输入中间有空格
2.数据型语句:
新增数据:
insert into t2 values(007,22),(008,33); #全字段插入
语法:insert into 表名 values(字段1值,字段2值,……),(字段1值,字段2值,……); insert into t2(id,name) values(10,82); # #个别字段插入
语法:insert inton 表名(字段名) values(值一),(值二);
insert inton 表名(字段名,字段名) values(值一),(值二);
删除数据:
delete from t2 where id=4; 语法:delete from 表名 where 条件;(不加条件时:删除整张表数据)
清除表数据(可使表自增id从1开始):
truncate table scheduling_plan_week; 语法:truncate table 表名
修改数据:
update t2 set score=69 where id=2;
语法:update 表名 set 更改的字段名=值 where 条件;
查询数据:
单表查询:
select * from t2; #单表查询
语法:select * from 表名; select id from t1; #个别字段查询
语法:select 字段一,字段二 from 表名;
多表查询:
select name from t3 where id=(select id from t2 where score=55); #嵌套查询 语法:select 字段一,字段二…… from 表名 where 条件(查询);
(select id from t3 )union(select id from t2); #并查询:union前后查询语句返回的数据数量必须一致
select id from t3 where id in (select id from t2); # 交查询
select * from t3 left jion t2 on t3.id = t2.code_id where score = '55' #左连接查询 语法:select 字段 from 表1 left jion 表2 on 链接方式 where 条件
常用函数:
select sum(score) from t2; #求和 select avg(score) from t2; #求平均值 select count(*) from t2; #计数(不包含null) select max(score) from t2; #最大值 select min(score) from t2; #最小值 # REGEXP('M60|M70')模糊匹配,查询结果与一致:SELECT * FROM tt_production_lou_package WHERE (sub_package like "%M60%" OR ub_package like "%M70%") SELECT * FROM tt_production_lou_package WHERE sub_package REGEXP ('M60|M70') # REGEXP('^M60|^M70')模糊匹配,查询结果与一致:SELECT * FROM tt_production_lou_package WHERE (sub_package like "M60%" OR ub_package like "M70%") SELECT * FROM tt_production_lou_package WHERE sub_package REGEXP ('^M60|^M70')
select sum(score) from t2; #求和
select avg(score) from t2; #求平均值
select count(*) from t2; #计数(不包含null)
select max(score) from t2; #最大值
select min(score) from t2; #最小值
常用修饰符:
select distinct score from t2; #distinct 字段中值唯一 select * from t2 limit 2; #结果数限制 select * from t2 order by score; #排序(默认升序) select * from t2 order by score desc; #降序排序 select * from t2 order by score asc; #升序排序 select score from t2 group by score; #分组