Mysql

查询某个字段信息

select id ,stu_name from stu_name;

指定别名使用 as 也可以省略 as 后面跟着的就是别名

select id as 'id名' ,stu_name from stu_info a;

as省略写法

select a.id , a.stu_name drom stu_info a
select id 'id名',stu_name '姓名' from stu_info

模糊查询

select * from stu_info where stu_grade link '%-%'
通配符%：表示0个或多个任意字符
通配符_ ：表示一个任意字符

通配符[] ：正则表达式匹配模式

select * from student where sname rlinke '陈["六六"]';
rlike 同义：regxp

正则匹配

select * from student where sname regexp '陈[六六]'

in关键字：可以查询多条信息；in后边是一个范围，多个信息用逗号分割

select * from stu_info where id in (1,3,5);

distinct:关键字用来去重，返回唯一的值

select disinct stu_sub ject from stu_info;

排序：order by 字段名 排序方法

ASC 升序 desc 降序
默认排序方式：升序 ASC
select * from stu_info order by stu_age desc

多个字段排序原理

先按照第一个字段去排序，如果里面有相同的值，则这些相同的值的信息再次按照第二个字段进行排序，多个字段进行排序，中间用逗号分割
select * feom stu_info order by stu_fee,stu_age;

分组group by

having:通常和group by 结合使用，对筛选的结果进行进一步的筛选；having 后边要使用聚合函数；

select sno as '学号',cno as '课程编号',avg(degree) as '平均成绩',max(degree) as '最高成绩',min(degree) as '最低成绩',count(*) as '记录数',sum(degree) as '总成绩' from score group by cno having 平均成绩>80;
//如果使用的别名,不加引号 : 平均成绩>80;

'#'select sno as '学号',cno as '课程编号',avg(degree) as '平均成绩',max(degree) as '最高成绩',min(degree) as '最低成绩',count(*) as '记录数',sum(degree) as '总成绩' from score group by cno having avg(degree)>80;

多表查询

等值连接 左连接 右连接

自查询

#select * from student a, student b where a.sno > b.sno

单行子查询

#select * from teacher where tname=(select sname from student where sno=123);

多行子查询

select * from student where sno in(108,109);
select * from student where sno not in(108,109);