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…]

字段值相同的分到一组
注意:
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 – 对结果限制