SQL语法

select * from sl_project where corpid='2' and name='qweererererr'; select distinct corpid,item_coding from sl_project;where 子句中的运算符 =,<>不等于,>,<,>=,<=,between and在某个范围内,like模糊搜搜,in指定针对某个列的多个可能值逻辑运算的优先级 () not and or空值判断: is null
select * from sl_project where id between 1 and 5;
select * from sl_project where id in (1,3,4);
select * from sl_project where name like '%qwee%';
%表示多个字值， 下划线表示一个字符
M%：为能配符，正则表达式，表示的意思为模糊查询信息为M开头的
%M 表示查询以M在倒数第二位的所有内容

select * from sl_project where name like '%ww__';
select * from sl_project where corpid='2' and item_coding like '%P%';

################ ORDER BY ################

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。如果需要降序记录进行排序DESC|ASC
select column_name, column_name from table_name order by column_name,column_name ASC | DESC;
order by A desc,B   这个时候 A 降序，B 升序排列

select * from sl_project order by id desc limit 10;

insert into table_name values (column1,column2,column3,...) values (value1,value2,value3,...);
insert into table_name values (value1, value2, value3, ...);



插入一行，要求表sl_project_check必须存在
insert into sl_project_check select *from sl_project where item_coding = '1111111';

--也是插入一行,要求表scorebak 不存在
select *  into scorebak from score  where neza='neza'



##-- update 语句用于更新表中已存在的记录。
update table_name sel column=value,column2=values,... where some_column = some_value;

update sl_project set name='我是小二郎呀' where id = '15663';
select * from sl_project order by id desc limit 10;

delete 语句用于删除表中的行

<***!>WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！
delete from table_name where some_column = some_value;

select *from sl_project_check; delete from sl_project_check where name='323'; select * from sl_project_check where name='1' order by create_time asc ;

select top 语句用于规定要返回的记录的数目，对于拥有数千条记录的大型表来说，非常有用的。

select top number|percent column_name(s) from table_name;




sql语法
select top number|percent column_name(s) from table_name;

MySql 语法
select column_name (s) from table_name limit number;
eg: select * from person limit 5;

oracle语法
select column_name(s) from table_name where rownum <= number;
eg: select * from person where rownum <= 5;

select * from sl_project_check limit 2;//选取前2条数据

select top 10 percent *from sl_project_check;

select * from sl_project limit 10;//取前10条数据

select * from sl_project order by id desc limit 10;//取后10条

##通配符
    [charlist]   字符列中的任何单一字符
    [^charlist]或[!charlist]   不在字符列中的任何单一字符

mysql使用regexp 或 not regexp运算符（或rlike和 not rlike）来操作正则表达式

select * from sl_project_check where name like '%1%';
select * from sl_project where name regexp '^[g]';//选取以g开始的所有项目名称
select * from sl_project where name regexp '^[a-c]';//选取以a-c字母开头的项目名称
select * from sl_project where name regexp '^[a-y]';//选取 name 不以 A 到 H 字母开头的项目
select * from sl_project where name = '小二郎';

in 操作符
in 操作符允许您在where子句中规定多个值

select column_name(s) from table_name where column_name in (value,value,...);

select * from sl_project where name in ('AAA','CEMS');
SELECT *FROM sl_project WHERE id between 1 and 5;

not between 操作符实例 不在范围内的项目

select * from sl_project where id not between 1 and 100;

带有in的between 操作符实例
select * from table_name where (id between 1 and 10) and name not in ('','');

select * from sl_project where (id between 1 and 5) and name not in ('百度项目','用友政务项目');
select * from sl_project where (id between 1 and 10) and name not like '%R%';

select * from group__field_groups where entity_id = 28;//查询项目分组信息
select group_content_field_data.id,
group_content__field_nickname.field_nickname_value,
group_content__field_group.field_group_value,
group__field_groups.field_groups_value,
group__field_groups.delta
from group_content_field_data
LEFT JOIN group_content__field_nickname on group_content__field_nickname.entity_id=group_content_field_data.id
LEFT JOIN group_content__field_group on group_content__field_nickname.entity_id=group_content__field_group.entity_id
LEFT JOIN group__field_groups on group__field_groups.entity_id=28
where gid = 28;//获取项目的所有人员

SELECT group__field_groups.delta,
group__field_groups.field_groups_value,
group_content_field_data.id
FROM group__field_groups
left JOIN group_content_field_data on group__field_groups.entity_id = group_content_field_data.gid;

-- SELECT group__field_groups.delta,group__field_groups.field_groups_value FROM group__field_groups UNION SELECT group_content_field_data.id FROM group_content_field_data;

-- SELECT group_content_field_data.id FROM group_content_field_data 项目ID
-- UNION SELECT group_content__field_nickname.group_content__field_nickname FROM group_content__field_nickname //人物姓名
-- UNION SELECT group_content__field_avatar.field_avatar_uri //人物头像
-- UNION SELECT group_content__field_group.field_group_value FROM group_content__field_group; //所在分组

-- 查询某个字段在表中出现的次数

-- group by，即以其中一个字段的值来分组
-- select 的字段只能是分组的字段类别以及使l聚合函数如，max(),min(),count()的字段。
-- where在前，group by在后，注意group by紧跟在where最后一个限制条件后面，不能被夹在where限制条件之间。
-- where在前，group by在后的原因：要先用where过滤掉不进行分组的数据，然后在对剩下满足条件的数据进行分组。
-- having是在分好组后找出特定的分组，通常是以筛选聚合函数的结果，如sum(a) > 100等，且having必须在group by 后面，
-- 使用了having必须使用group by，但是使用group by 不一定使用having。
SELECT lesson_id,COUNT(lesson_id) as count FROM sl_lesson_order GROUP BY lesson_id ORDER BY count DESC LIMIT 0,5