MySQL数据库语法及CRUD:
目录
一、去重
二、where条件
三、联表查询
四、自连接
五、排序和分页
六、子查询
七、常用函数
八、group by
一、去重
去除select查询出来的语句重复的部分(关键字 distinct );
语句:select [distinct] 字段名 form 表名
例:select distinct name from people;
二、where条件
作用:根据查询条件返回指定的结果
1、逻辑运算符
运算符 | 语法 | 描述 |
and 和 && | result >1 and result <4 === > result >1 && result <4 | 逻辑与,两个条件都满足,才为true |
or 和 || | result >1 or result <4 ===> result >1 || result <4 | 逻辑或,满足一个条件,为true |
not 和 ! | not result = 2 ===> ! (result = 2) | 取反,result不等于2,则为true |
与运算:必须满足所有的条件
或运算:只需要满足一个条件即可
取反:当满足条件时,返回不满足条件的部分( 使用!记得加上括号() )
2、比较运算符
运算符 | 语法 | 描述 |
is null | name is null | 查询name为null的数据 |
is not null | name is not null | 查询name不为null的数据 |
between…and… | id between 2 and 4 | 查询id在2到4之间的数据 |
like(模糊查询) | name like b(有 % 和 _ 两种修饰符) | 匹配,name中是否存在b数据,使用修饰符可以部分匹配 |
in | name in (a1,a2,a3,a4…)(完全匹配) | 匹配,name中存在a1到a4数据返回true,完全匹配 |
is null和is not null:
between…and…: 查询年龄在20到40岁之间的数据
like: %(表示0到任何个字符)
只要数据带有%,查询时不会看其长度,如 ‘%2%’ ,只要带有2都会被查出。
like: _(表示一个字符)数据带有_,查询时只会忽略一个字符,超出一个字符就不会匹配,如(王 _),只要往后面有两个字符,就不会匹配。
in: 完全匹配多个数据,相当于多个查询语句。
三、联表查询
JOIN
join联表操作
操作 | 描述 |
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 以左表为基准,就算右表没有匹配到,左表有,也会输出 |
right join | 以右表为基准,就算左表没有匹配到,右表有,也会输出 |
inner join:(根据条件获取两张表数据相等的行)
left join:已左表为基准,获取值,就算两张表数据不相等,以左表数据为准。
right join:已右表为基准,获取值,就算两张表数据不相等,以右表数据为准。
join on:连接查询
where:等值查询
on和where结合使用(可以有效的剔除不符合条件的数据):
- 1、where是在临时表创建完成,进行联表处理进行操作
- 2、但临时表已经完成,where再对数据进行过滤,导致出现的情况非常理想。
但当on和and,or进行结合使用时,得到的效果去不尽如人意(不支持在join中使用or):
- 1、and是在join临时表进行过滤,对数据进行限制
- 2、当join之后,限制后的数据和on连接处理,但是因为数据已经被处理,不能全部匹配导致产生为null的数据。
四、自连接
核心:一张表拆成两张表。
拆分: 根据子科目id为1,获取父科目,其他的为子科目,进行父类id = 子类id。
五、排序和分页
1、排序语法:order by 字段 (ASC(升序) 或 DESC(降序))
例:对查询出来的数据进行降序。
select p.pid,s.subjectname as ‘父科目',p.subjectname as ‘子科目’
from subject as s inner join subject as p
on s.subjectid = p.pid order by p.pid desc;
2、分页 limit 在 SQL语句最后
mysql下标默认从0开始,oracle从1开始。
语法:limit 查询下标(对应页面的当前页),显示数据(展示的页面数据个数);
- 网页分页查询一般的计算方式(网页分页显示索引从0开始):查询下标(当前页) * 显示数据 ,显示数据;
- 网页分页查询一般的计算方式(网页分页显示索引从1开始):(查询下标(当前页) -1)* 显示数据 ,显示数据;
例:对降序完之后的数据进行分页,每页5条数据
select p.pid,s.subjectname as ‘父科目',p.subjectname as ‘子科目’
from subject as s inner join subject as p
on s.subjectid = p.pid order by p.pid desc limit 0,5;
六、子查询
本质:在where中在嵌套一个select语句。
1、子查询可以通过少量的表,查询更多的表的数据(数据必须在查询的表内存在)。
2、表中存在其他表的字段(如:id)就可以实现,但只能获取本表对应字段(如:id),不能其他表的其他字段。
例:
select peopleid,name,roleId(其他表的ID)from people(只能查询people存在的数据)
where roleId in (select roleId from role where roleId >0);
通过people表的roleId和studentid进行子查询
people表数据:
查询职务为普通员工,性别为女的数据:
子查询虽然简便,但没有联表查询那样可以查询其他表的数据(但使用了join我个人感觉和联表查询差不多),各有千秋。
七、常用函数
1、时间
函数 | 描述 |
select current_date() | 获取当前时间(年月日) |
select curdate() | 获取当前时间 (年月日) |
select now() | 获取当前时间(年月日 时分秒) |
select localdate() | 获取本地时间(本地时间 年月日 时分秒) |
select sysdate() | 获取系统时间(系统时间 年月日 时分秒) |
select year(now) | 获取当前年份 |
select month(now) | 获取当前月份 |
select day(now) | 获取当前日期 |
select hour(now) | 获取当前小时 |
select minute(now) | 获取当前分钟 |
select second(now) | 获取当前秒数 |
2、系统
函数 | 描述 |
select system_user() | 获取当前用户 |
select user() | 获取当前用户 |
select version() | 获取当前版本号 |
3、常用聚合函数
函数 | 描述 |
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
count执行效率:count(列名) > count(1)>count(*)
count(列名):只查询指定列,忽略null。
count(1):只查询第一列,不忽略null。
count(*):每一列都会查询,不忽略null。
select sum(列名) [as 总分] from 表名
select avg(列名) [as 平均分] from 表名
select max(列名) [as 最高分] from 表名
select min(列名) [as 最低分] from 表名
4、MD5加密
md5加密是一种加密手段,在数据插入时对数据进行加密,用加密的数据进行验证(加密数据不会变动,每一次加密相同的数据,加密后数据的值相同),Java一般在插入中进行加密。
insert into user(userid,username,password) values('2','wangwu',mad5('13579'));
八、Group by
1、group by 列名:通过列名进行分组,在where下面。
- group by 会将指定列中相同的数据合并,但多个id在一个单元格中,显然使用问题的,所以直接查询id会报错。
group by和 聚合函数的操作:
2、having :次要过滤条件,和group by一起,过滤group by返回的数据,弥补where不能和聚合函数联合使用的不足。
select完整语句:
selete [all(默认) | ditinct(去重)]
(*(全部),指定列名(可以有多个)[as 别名]) from (表名 [as 别名])
[(inner | left | right ) join (表名 [as 别名]) [on ...条件]] ===>联合查询
[where ...条件]
[group by 列名] ===> 根据列名进行分组,数据相同为一组进行计数)
[having ...次要条件] ===>group by一起,过滤group by返回的数据,弥补where不能和聚合函数联合使用的不足
[order by ( asc (升序) | desc (降序) )]
[limit 查询页码(当前页),展示数据个数]
回到顶部