mysql function 输出语句_mysql常用语句即函数

mysql数据库，常用查询语句大全

下文记录了SQL常用DQL语句； 基础查询，条件查询，模糊查询，排序查询，聚合函数，去重，分页查询，限制输出，多表查询…

基本语法：

增：

添加数据：inset into 表名 (列名1，列名2，...)values(值1，值2，...)

添加列：alter table 表名 add 列名 数据类型(长度)；

删:delete from 表名

删除列：alter table 表名 drop 列名

可根据需要加上条件，否则删除全表

改：update 表名 set 字段1=值，字段2=值 ...

可根据需要加上条件，否则更新全表

更新列名：alter table 表名 change 旧列名 新列名 数据类型(长度)

查：select 查询列名(多个列名用，隔开)from 表名

一、基础查询

语法：select 查询列名(多个列名用，隔开)from 表名

*PS:查询所有列可以使用*代替所有列名，*的时间复杂度较高，在写项目时要列出所有需要查询的列

例：

– 查询指定 列

– 查询学生学号,姓名,年龄

二 条件查询

理解

条件查询就是查询时在基础查询的后面使用

where语句,where后使用运算符将符合条件的数据保留

条件符号

= 相等

!= <> 不等

大于 <=小于等于 >=大于等于

and 和/与

-----------两边的条件同时成立才行

or 或

------------- 两边的条件有一个成立即可

in(set) 范围 在set集合范围内

between 值1 and 值2

------------在值1之值2间 (包含临界值,从小到大)

not in 取反

例：

– 查询学生年龄在20到40之间的学生记录

– 查询学号不是1,2,3的记录

三 模糊查询

模糊查询需要放在where后使用like关键字

： 通配符：

% ,可以匹配任何个数任意字符

_ ,可以匹配一个任意字符

例·：-- 查询姓名以“张”开头的学生记录

select * from stu where sname like '张%';

-- 查询姓名中包含“三”的学生记录

select * from stu where sname like '%三%'

四 排序查询

将查询到的的结果按照一定的顺序排序

order by 字段 [desc|asc] ==> desc 降序,asc升序

例：

-- 不写排序类型,默认是升序

select * from stu order by age

-- 查询所有学生记录，按年龄升序排序,如果年龄相同时，按编号降序排序

select * from stu order by age asc,sid desc

五 聚合函数

聚合函数：

count(列名) 统计指定列不为null的行数

max(列名) 获得指定列中最大值,如果是字符串,按照字符顺序

min(列名) 获得最小

sum(列名) 计算指定列的和值,计算非数字,结果是0

avg(列名) 计算指定列的平均值,计算非数字,结果是0

语法:

select 聚合函数 from 表名 [where.. ]

例：

-- 查询最高成绩和最低成绩：

select max(score) '最高分',min(score) '最低分' from stu;

-- 统计stu表中成绩大于60的最高成绩和最低成绩：

select max(score) '最高分',min(score) '最低分' from stu where score > 60;

ps:

与聚合函数一同出现的列名,必须出现在group by后

反之,如果出现了一个没有在groupby后出现的字段,那么查询结果不正常!

六 去重

去重函数 distinct(列)

将指定列的数据去重

语法：

selectdistinct(列名) from 表名 [where ...]

例：

-- 不重复的年龄

select distinct(age) from stu;

-- 查询年龄不重复的共有多少人

select count(distinct age) from stu;

七 分组查询

当需要分组查询时,需要使用group by语句.

语法:

select * from stu [where ...] group by 字段 [order by..]

ps:

字段值相同的分到一组

注意:

1 一般分组之后的操作都是聚合操作

2 分成几组,虚拟表结果就是几行数据

3 聚合函数,是对每组进行单独聚合操作

分组后再过滤,需要使用having子句

hacing与where的区别:

where是分组前过滤,having是分组后过滤

where后不能使用聚合函数,having可以使用聚合函数

例：

-- 查询男生多少人,女生多少人

select sex,count(*) '人数' from stu group by sex;

-- 查询每个班级的班级编号和每个班级的成绩和：

select cid,sum(score) from stu group by cid;

-- 查询每个班级的班级编号以及每个班级的人数：

select cid '班级',count(sid) '人数' from stu group by cid;

-- 查询成绩总和大于200的班级编号以及成绩和

select cid,sum(score) from stu group by cid having sum(score) > 200

-- having可以使用前面定义的别名

select cid,sum(score) 'sum' from stu group by cid having sum > 200

-- 查询成绩总和大于200的班级编号以及成绩和并根据成绩总和降序

select cid,sum(score) 'sum' from stu group by cid having sum > 200 order by sum asc;

八 限制输出

可以将查询好的结果,限制输出.

可以限制输出几条,从哪里输出.

语法:

在sql语句最后写 limit offset,row_count

limit 开始下标,行数;

例如: limit 0,2; ==> 从第一条输出,输出2条

例：

-- 输出前两条

select * from stu limit 0,2;

-- 从第四条开始,输出三条

select * from stu limit 3,3;

九 函数

流程函数

IF(expr1,expr2,expr3)

如果expr1为真，则返回expr2，否则返回expr3

-- isnull()函数,判断是否为空

-- isnull(字段) 如果是null,返回1 不是null返回0

例：

-- 查询学生id,姓名,成绩,如果成绩为null,显示缺考

select sid,sname,if(isnull(score)=1,'缺考',score) from stu;

CASE WHEN [expr1] THEN [result1]… ELSE [default] END如果expr是真, 返回result1,否则返回default

case

when 条件 then 执行语句

when 条件 then 执行语句

...

else 执行语句

end

执行第一个when后的条件,如果为true,执行then后的语句,

如果when后的条件为false,执行第二when后的条件

如果都是false,执行else后语句

例：

-- 查询学生id,姓名,成绩,以及等级

-- (0-59 不及格,60-69中,70-89良,90-100优 )

select sid,sname,score,

case

when score < 60 then '不及格'

when score < 70 then '中'

when score < 90 then '良'

when isnull(score)=1 then '缺考'

else '优'

end as '等级'

from stu

十 多表查询

** 联合查询-合并结果集**

将两表的查询结果纵向拼接在一起

union / union all

拼接的两个表的字段个数,类型,顺序保证一致.

union 在拼接时会去重

union all将两表的全部数据都拼接

例如：

select sid,sname from stu

union all

select cid,cname from class;

连接查询：

表与表之间的关系有三种：一对一，一对多，多对多

一对一关系：在任何一张表添加关联字段都可以

一对多关系：只能在多的一端添加关联字段

多对多关系：需要新建一张表，用第三方来表示关联关系

连接查询就是将多个表多行数据相乘. 也就会产生 笛卡尔积

内连接：

内连接语法:

select * from 表1 inner join 表2 on 关联条件

内连接只会保留完全符合关联条件的数据

例：

-- 查询学生名称、学生成绩、班级名称、班级地址

select sname,score,cname,caddress from stu inner join class on stu.cid = class.cid

-- 内连接可以简写

select * from stu inner join class on stu.cid = class.cid

-- 简写成

select * from stu,class where stu.cid = class.cid

外连接

外连接: 会保留不满足条件的数据

左外连接-left outer join on

会保留左表中不符合的条件的数据

右外连接 - right outer join on

会保留右表中不符合的条件的数据

一般outer关键字可以省略

例：

-- 查询全部学生信息,如果有班级信息,一并查出.

select * from stu s left outer join class c on s.cid = c.cid

-- 查询全部班级信息,如果有学生,将学生信息查出

select * from stu s right outer join class c on s.cid = c.cid

子查询

子查询就是嵌套查询.

一般子查询出现在:

from后, 当做一张表使用

where后, 当做条件使用

例：

-- 查询与张三同一个班级的学生。

-- 1查出张三的班级

select cid from stu where sname = '张三'

-- 2查出1班的学生

select * from stu where cid = 1

-- 合并成子查询

select * from stu where cid = (select cid from stu where sname = '张三')

-- 成绩高于3号班级所有人的学生信息

-- 1 找到3班最高分

select max(score) from stu where cid = 3

-- 2 找成绩比96高的学生

select * from stu where score > 96

-- 合并

select * from stu where score > (select max(score) from stu where cid = 3)

自连接

自连接就是自己连接自己，一个表当两个表用

s1做学生表 s2做组长表

-- s1的组长是s2中的学生

select s1.sid,s1.sname,s2.sid,s2.sname from stu s1,stu s2 where s1.groupLeaderId = s2.sid and s1.sid = 1008

实例的表：

创建学生表：

DROP TABLE IF EXISTS `stu`;

CREATE TABLE `stu` (

`sid` int(11) DEFAULT NULL,

`sname` varchar(25) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`sex` char(6) DEFAULT NULL,

`score` int(11) DEFAULT NULL,

`cid` int(11) DEFAULT NULL,

`groupLeaderId` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 添加数据

INSERT INTO `stu` VALUES ('1001', '张三', '20', '男', '72', '1', '1003');

INSERT INTO `stu` VALUES ('1002', '李四', '15', '女', '78', '1', '1003');

INSERT INTO `stu` VALUES ('1003', '王五', '95', '男', '99', '1', '1010');

INSERT INTO `stu` VALUES ('1004', '赵六张', '65', '女', '60', '1', '1007');

INSERT INTO `stu` VALUES ('1005', '周七', '55', '男', '78', '3', '1007');

INSERT INTO `stu` VALUES ('1006', '茅十八', '75', '女', '96', '3', '1007');

INSERT INTO `stu` VALUES ('1007', '张三丰', '40', '男', '85', '3', '1010');

INSERT INTO `stu` VALUES ('1008', '李四方', '45', '女', '90', '2', '1010');

INSERT INTO `stu` VALUES ('1009', '艾三弗森', '45', '', '35', '4', '1008');

INSERT INTO `stu` VALUES ('1010', '三欧文', '35', '女', '49', '2', '1008');

创建课程表：

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (

`cid` int(11) DEFAULT NULL,

`cname` varchar(255) COLLATE utf8_bin NOT NULL,

`caddress` varchar(255) COLLATE utf8_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------

-- 添加数据

-- ----------------------------

INSERT INTO `class` VALUES ('1', 'BigData', '102');

INSERT INTO `class` VALUES ('2', 'HTML', '103');

INSERT INTO `class` VALUES ('3', 'VR', '104');

INSERT INTO `class` VALUES ('4', 'Java', '105');

总结：

书写语法:

select

selection_list --要查询的列

from

table_name -- 要查询的表名

where condition -- 过滤行条件

group by grouping_clumns -- 对结果按照列进行分组

having condition -- 分组后再过滤

order by sort_column -- 排序

limit offset,row_count -- 对结果限制