mysql之DML语言(查)

1. DML语言2

select 查询

1.1 查询指定字段

1. 语法:

select 字段名 from 表名

2. 示例

-- 查询全部的学生
select * from student

-- 查询指定字段
select `name`,`id` from `student`

3. 起别名

   select `name` as 学生 from `student`
   

4. concat 拼接

   select concat('姓名:',name) as 新名字 from student
   

5. 去重 distinct

   作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条

   -- 查询一下有那些同学参加了考试,成绩
   select distinct `studentNo` from result
   

6. 数据库的列(表达式)

   select version() -- 查询系统版本(函数)
   select (8888-888)/8 as 计算结果  -- 用来计算
   select @@auto_increment_increment -- 查询自增步长(变量)
   
   
   -- 学员考试成绩 +1分查看
   select `studentNo` , `studentResult`+1 as '提分后' from result
   

1.2 where 条件子句

1. 作用: 检索数据中符合条件的值

   搜索的条件由一个或者多个表达式组成! 结果 布尔值

   逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与,两个都为真,结果为真
or ||	a or b a || b	逻辑或,其中一个为真,结果为真
not !	not a !a	逻辑非,真为假,假为真

​ 尽量使用英文字母

2. 示例:

   -- 查询考试成绩在 95~100 分之间
   select `studentNo`,`studentResult` from result
   where studentResult>=95 && studentResult<=100
   
   select `studentNo`,`studentResult` from result
   where studentResult between 95 and 100
   
   -- 除了1000号学生之外
   select `studentNo`,`studentResult` from result
   where studentNo != 1000
   

1.3 模糊查询

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL,结果为真
IS NOT NULL	a is not null	如果操作符不为NULL,结果为真
BETWEEN	a between b and c	若在a和b之间,则结果为真
LIKE	a like b	SQL匹配,如果a匹配b,则结果为真
IN	a in (a1,a2,a3…)	假设a在a1,a2.a3…其中的某一个值中,结果为真

1. 示例:

   -- like结合  %(代表0到任意个字符) _(一个字符)
   select `studentNo`,`studentName` from student
   where studentName like '刘%'
   
   -- 查询姓刘的同学,名字后面只有一个字
   select `studentNo`,`studentName` from student
   where studentName like '刘_'
   
   -- 查询姓刘的同学,名字后面只有两个字
   select `studentNo`,`studentName` from student
   where studentName like '刘__'
   
   -- 查询中间有嘉的同学
   select `studentNo`,`studentName` from student
   where studentName like '%嘉%'
   
   
   -- in(具体的一个或多个值)
   
   -- 查询 1001,1002,1003号学员
   select `studentNo`,`studentName` from student
   where studentNo in (1001,1002,1003)
   
   -- 查询来自北京的学员
   select `studentNo`,`studentName` from student
   where `address` in ('北京')
   
   -- null not null
   -- 查询地址为空的学生'' null
   select `studentNo`,`studentName` from student
   where `address`='' or adress is null
   
   -- 查询有出生日期的学生
   select `studentNo`,`studentName` from student
   where `borthday` is not null
   

1.4 连接查询

1. 语法

   join (连接的表) on (判断条件) where 等值查询
   

2. 示例

   -- 两表连接
   select s.studentNo,studentName,subjectNO,studentResult
   from student as s
   inner join result as r
   where s.student = r.studentNo
   
   
   select s.studentNo,studentName,subjectNO,studentResult
   from student s
   inner join result r
   on s.student = r.studentNo
   
   
   -- right join 
   select s.studentNo,studentName,subjectNO,studentResult
   from student s
   right join result r
   on s.student = r.studentNo
   
   
   -- left join
   select s.studentNo,studentName,subjectNO,studentResult
   from student s
   left join result  r
   on s.student = r.studentNo
   
   -- 查缺考的同学
   select s.studentNo,studentName,subjectNO,studentResult
   from student  s
   inner join result  r
   on s.student = r.studentNo
   where studentResult is null
   
   -- 三表查询
   select s.studentNo,studentName,subjectNO,studentResult
   from student  s
   right join result r
   on s.student = r.studentNo
   inner join subject sub
   on r.subjectNo = sub.subjectNo
   

1.5 自连接

解释:自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

例子:

总表

pid	categoryid	categoryName
1	2	信息技术
1	3	软件开发
1	5	美术信息
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

操作:查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息:把一张表看为两个一模一样的表
select a.`categoryName` as '父栏目',b.`categoryName` as '子栏目'
from `category` as a, `category` as b
where a.`categoryid` = b.`pid`

1.6 排序 order by

-- 排序: 升序asc, 降序desc
-- order by 通过那个字段排序,怎么排
-- 查询的结果根据 成绩降序 排序
select s.`studentNo`,`studenName`,`subjectName`,`studentResult`
from student s
inner join `result` r
on s.studenNo = r.studentNo
inner s. `subject` sub
on r.`subjectNo` = sub.`subjectNo`
where subjectName = '数据结构-1'
order by studentResult asc

**分页:**limit

语法:limit(查询起始下标,pagesize(页面大小))

-- 为什么要分页
-- 缓解数据库压力,给人的更好,瀑布流
-- 分页:没页只显示五条数据
-- 语法:limit 起始值,页面的大小
-- 网页应用:当前,总的页数,页面的大小
-- limit 0,5  1~5
select s.`studentNo`,`studenName`,`subjectName`,`studentResult`
from student s
inner join `result` r
on s.studenNo = r.studentNo
inner s. `subject` sub
on r.`subjectNo` = sub.`subjectNo`
where subjectName = '数据结构-1'
order by studentResult asc
limit 0,5

-- 第一页: limit 0 ,5   (1-1)*5
-- 第二页: limit 5 ,5   (1-1)*5
-- 第三页: limit 10 ,5   (1-1)*5
-- 第n页: limit 0 ,5   (n-1)*pagesize,pagesize
-- [pagesize:页面大小]
-- [(n-1)*pagesize:起始值]
-- [n:当前页]
-- [数据总数/页面大小 = 总页数]

1.7 函数

-- 时间日期的函数
select current_date() -- 获取当前日期
select curdate()  -- 获取当前日期
select now() -- 获取当前的时间
select localtime() -- 本地时间
select sysdate() -- 系统时间

select year(now())
select month(now())
select day(now())
select hoer(now())
select minute(now())
select second(now())

-- 系统
select system_user()
select user()
select version()

-- 常用函数 --
-- 数学函数
select abs(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand()  -- 返回一个0~1 之间的随机数
select sign(10) -- 判断一个数的符号

-- 字符串函数
select char_length() -- 字符串长度
select concat()  -- 拼接字符串
select insert()  -- 查询,从某个位置开始替换某个长度
select lower() -- 小写字母
select upper() -- 大写字母
select instr() -- 返回第一次出现的子串的索引
select replace() -- 替换出现的指定字符串

-- 聚合函数
avg() 平均分
max() 最大值
min() 最小值
sum() 和

1.8 分组和过滤

分组 group by

-- 查询不同课程的平均分,最高分,最低分,
-- 核心: (根据不同的课程分组)
select subjectName, avg(studentResult) as 平均分,max(studentResult) as 最高分,min(studentResult) as 最低分
from result r
inner join `subject` sub
on r.`subjectNo` = sub.`subjectNo`
group by r.subjectNo

过滤 having

-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心: (根据不同的课程分组)
select subjectName, avg(studentResult) as 平均分,max(studentResult) as 最高分,min(studentResult) as 最低分
from result r
inner join `subject` sub
on r.`subjectNo` = sub.`subjectNo`
group by r.subjectNo
having 平均分>80

测试md5加密

-- 明文密码
insert into testmd5 values(1, 'zhangshan','12346'),(2, 'lisi','12346'),(3, 'wangwu','12346')

-- 加密
update testmd5 set pwd=md5(pwd) where id = 1

update testmd5 set pwd=md5(pwd) -- 全文加密

-- 插入时加密
insert into testmd5 values(4, 'zhaoliu',md5('123456')) 

-- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
select * from testmd5 where `name`='zhaoliu' and pwd=md5('123456')

1.9 mdl语言使用的顺序(很重要)

select distinct 字段 from 表名 (注意:表名和字段可以取别名)
inner[right,left] join 要连接的表  on 等值判断
where(具体的值,子查询语句)
group by (通过哪个字段来分组)
having (过滤分组后的信息,条件和where是一样的,位置不同)
order by (通过哪个字段排序)[asc(升) /desc(降)]
limit startlndex,pagesize

',md5(‘123456’))

– 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
select * from testmd5 where name=‘zhaoliu’ and pwd=md5(‘123456’)

## 1.9 mdl语言使用的顺序(很重要)

```mysql
select distinct 字段 from 表名 (注意:表名和字段可以取别名)
inner[right,left] join 要连接的表  on 等值判断
where(具体的值,子查询语句)
group by (通过哪个字段来分组)
having (过滤分组后的信息,条件和where是一样的,位置不同)
order by (通过哪个字段排序)[asc(升) /desc(降)]
limit startlndex,pagesize