mysql数据库中的各种查询操作

查询

语法:

select 列名1,列名2 # 查询列

from 表名 # 从 某张表中

#可选

where 条件表达式

group by 列名

having 条件表达式

order by 列名

limit ?,? 分页

说明：

我们想查询该表的那些列 可以直接写列名 ，顺序可以自定义

SELECT `name`,age,address,sex from student; 

可以使用 select * 查询所有列。 *号代表所有列。但是这种查询效率低。

SELECT * from student; 

列和表都可以起别名

SELECT s.`name`,s.age+3 a,s.address from student s; 

查询中列支持运算

SELECT s.`name`,s.age+3 a,s.address from student s; 

去重查询

去除查询结果中的重复数据

select distinct 列名 from 表名;

条件查询where语句 

SQL语句中的条件表达式：查询条件、判断条件、修改条件

条件运算符：=    > < >= <= 不等：<> 和  !=

算术运算符：+ - * /     %（mod)   不支持：++  -- 复合赋值 += -=

逻辑运算符：and(&&) , not(!) , or(||)

1.多条件查询

#带条件查询

#条件表达式:

#关系运算符: >,>=,<,<=,=,!=,<>

#逻辑运算符: and(&&),or(||),not(!) 

 

#查询价格大于5元的商品

SELECT                # 1. 什么操作

goodsId,goodsName,price   # 4. 查询什么数据

from goods            # 2. 针对那个表

where price>5        # 3. 条件

 

#查询价格大于10元的并且产地是广州的商品

SELECT                            #1.什么操作

*                                 #4.查什么结果

from goods                          #2.针对哪个表

where price>10 and address = '广州'  #3.条件

#where price > 10 && address ='广州'

 

#查询商品类型是食品，或者价格小于5元的商品

SELECT

*

from goods

where categoryId = 1 or price<5

 

#查询价格不小于5的商品

SELECT

*

from goods

where not price < 5

 

#查询产地不是广州的

select

*

from goods

where not address = '广州'

#where address <> '广州'

#where address != '广州'

 

#带条件修改

#1. 将产地是郑州的并且是食品的商品都上涨1元钱,并且生产日期改为当前日期

update goods                          # 1.修改哪个表

set price = price+1,                   #3.修改哪些列

produceDate = now()

where address = '郑州' and categoryId = 1  # 2.修改哪些记录

 

select * from goods where address = '郑州' and categoryId = 1

 

#2. 将价格小于5的并且是饮品的商品产地改为新乡

update goods

set address = '新乡'

where price<5 and categoryId = 2

 

select * from goods where price<5  and categoryId = 2

 

#带条件删除

#1. 删除生产日期小于2005-01-01的商品

delete from goods   #1.删除哪个表的数据

where produceDate< '2005-01-01' #2.删除哪些记录

 

select * from goods where produceDate< '2005-01-01'

2.范围查询: between and

#3.多条件查询，使用between AND(查询某个连续期间的序列数据)

#查询商品编号是2-9之间的数据

SELECT

*

from goods

where goodsId between 2 and 9  #包含开始和结束

#where goodsId>=2 and goodsId<=9

 

#查询生产日期在2010-01-01 和2022-01-01之间的商品

 

3.范围查询: in 的使用

#4.多条件查询，使用in(也是范围条件查询，可以用于不连续的条件)

#查询商品是面包和鸡蛋的记录

SELECT

*

from goods

where goodsName in ('面包','鸡蛋')

#where goodsName = '面包' or goodsName = '鸡蛋'

 

#查询商品编号是1，3，5的商品

#查询产地是广州和郑州的商品

 

模糊查询 

正则通配符

 

正则模式REGEXP操作符

“.” 匹配任意单个的字符。

“[ ]” 匹配在[]内的任意一个字符

［^］ 匹配不在[]内的任意一个字符

“ * ” 匹配零个或多个在它前面的字符

“+” 匹配+前面的字符1次或多次

“{n}” 匹配前面的字符至少n次

“^” 匹配文本的开始字符

“$” 匹配文本的结尾字符

//匹配名字以a开头 c结尾的名字

select * from student where `name` REGEXP'^a.c$';

模糊查询like

 

'%a' //以a结尾的数据

'a%' //以a开头的数据

'%a%' //含有a的数据,三位且中间字母是a的

'_a' //两位且结尾字母是a的

'a_' //两位且开头字母是a的

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两

个百分号（%%）表示。

_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

排序操作

order by 列名 asc 默认升序 desc 降序

分页查询limit 

每次我们直接 select * from t_people 会将数据全部查询出来，如果一张表数据量比较大 ，此时 容易爆炸，所以真正开发的时候，都是只查一部分数据，所以都是进行分页操作的。 

#分页必须和排序一起使用

#按照价格升序排序进行分页

select * from goods

order by price asc

limit 10,5

聚合函数(汇总函数) 

#汇总函数(聚合函数)：针对多条数据计算出一个结果

# count(*/列名): 统计记录数，统计非空值，统计唯一值

# max(列名): 查询某列中的最大值

# min(列名): 查询某列中的最小值

# sum(列名): 针对列所有值求和

# avg(列名): 针对列所有值求平均

# 聚合函数可以单独使用，也可以与分组一起使用(重点)

 

分组查询 

group by 列名    是将 某一列 相同数据的行当成一组

-- 分组查询

-- 求出每个班级的人数  按照班级分组

SELECT

classId,         #分组字段

count(*) as 人数, #分组汇总

from studentinfo

group by classId

-- 求每个班级的男生和女生人数（多字段分组）

SELECT

classId,sex,count(*) as 人数

from studentinfo

group by classId,sex

order by classId,sex desc

 

having   对分组后的数据做过滤，注意分组查询添加条件  group by  列名  having  条件；

where 用在分组之前的条件过滤

 

-- 查询出所有男生人数大于2的班级

SELECT

classId,sex,count(*)

from studentinfo

group by classId,sex

having count(*)>=2 and sex='男' #having 分组之后条件过滤

#第二种写法??

SELECT

classId,count(*)  #每个班级的男生

from studentinfo

where sex = '男'  #先查询男生

group by classId  #再按班级分组

having count(*)>=2

多表查询（重难点）

内连接（等值连接:where,[inner] join）

内连接和外连接一样都是最常用的连接查询，它根据表中共同的列进行匹配，特别是两个表存在主外键关系时，通常会使用到内连接查询 内连接也称为等值连接，返回的结果集是两个表中所有相匹配的数据，舍弃不匹配的数据。

1）where等值连接（隐式内连接）：简单，但是碰到问题不好调。

select 列名......

from 表1，表2，表3，表4....

where 关联条件、还可以过滤条件

 

#多表查询:查询学生姓名，性别，班级名称

#内连接(等值连接): 1.where连接  2.inner join 连接

#内连接的特点:多表之间能完全匹配的数据才会查询出来

#1.where连接查多表数据

SELECT

#*

s.name,s.sex,c.className

from studentInfo s , classInfo c  #从两个表查询

where s.classId = c.classId  #两个表的关联条件

2）inner join内连接（显式内连接）on ，扩展方便 

select 列名,......

from 表1

[inner] join 表2 on 关联条件

[inner] join 表3 on 关联条件

[inner] join 表4 on 关联条件

where 过滤条件;

 

外连接 

外连接是至少返回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录 外连接就是在满足表连接关系的情况下不但可以查找出匹配的数据，而且还可以包含左表，右表或是两表中的所有的数据行

• 左外连接 

左外保证左表完整、如果不匹配null值填充

SELECT * from 表1 LEFT JOIN 表2 ON 关联条件; 

• 右外连接

右外保证右表完整、如果不匹配null值填充

 SELECT * from 表1 right JOIN 表2 ON 条件;

交叉连接 cross join 

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行再一一组合，相当于两个表“相乘”左表中每一行的数据都和右表组合一次，相当于两表的排列组合，返回的条数其实就是两表的成绩交叉连接没有关联条件

#交叉连接(笛卡尔积): 两表相乘，没有关联条件

select 

*

from studentInfo s

cross join classInfo c

 

#一般在考勤业务中使用

#生成一个学生的31天记录