常用sql

 

    1.查询并创建表

create table abc
COMMENT='学生表'
SELECT * from def
where id<20

   

    2.查询包含_%等关键字符的字符串，使用反斜杠，字段是关键词的使用键盘1前面那个键``引起来

select * from student
where sname like '%\%%'--包含%

select * from student
where sname like '%\_%'--包含_

select `key` from studeng 

 

 3.聚合连接函数

select group_concat(distinct sname order by sname desc separator ';') from student
group by age

 

4.自定义排序:取所有学生的成绩，有第3次成绩，按第3次算，否则按第1次，如果没有，再按第2次

select a.* from (select * from student
where banji='一班'  and xueke in (3,1,2)
order by FIELD(xueke,3,1,2)
) a
group by a.student_name
order by a.id

 

 5.trim 去掉两头的逗号

 

-- TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
select TRIM(BOTH  ','  FROM sname) sname2 from student