MySQL数据库---函数概览

九.MySQL的函数应用(了解)

Ⅰ.字符串函数

1.length()   统计一个字符串的字节长度

2.char_length()   统计一个字符串的长

3.mid()    进行字符串的截取操作

格式：mid(字符串,截取开始的位置,截取的字符个数)

Ⅱ.数学函数

1.round()   四舍五入

round(数据,保留的小数位)     保留指定的小数位

round(数据)     保留整数位

2.least(值1,值2,值3...)    求最小值

3.greatest(值1,值2,值3...)   求最大值   

Ⅲ.日期时间函数

1.now()   获取日期时间

2.current_date()   获取日期

3.current_time()   获取时间

4.to_days()   统计总天数

5.dayofyear()  统计该年已过的天数

6.week()   查看该年的周数

Ⅳ.控制函数

1.if()函数

格式一：if(布尔表达式,参数1,参数2)   成立，看参数1，否则看参数2

格式二：if(null,参数1,参数2)    如果第一个位置为空，直接看参数2

2.ifnull(参数1,参数2)   如果参数1有值，直接返回;如果参数1是null，返回参数2

-- 1.length()   统计一个字符串的字节长度

--  MySQL，一个汉字3占多少个字节？一个字母1占多少个字节？

-- 统计系别的名称长度(字节)

select  sdept,length(sdept)  '字节长度'   from  student;

-- 统计学生姓名字节长度

select  姓名,length(姓名)  '字节长度'   from   学生信息;

-- 2.char_length()   统计一个字符串的长度

select  *   from  student;

-- 统计系别的名称长度

select  sdept,char_length(sdept)  '系别长度'  from  student

-- 从学生信息表中对籍贯的数据进行截取：从第二字符开始截取，截取长度为3

select  籍贯,mid(籍贯,2,3)  '截取后的数据'   from  学生信息;

-- 1.round()   四舍五入

-- round(数据,保留的小数位)     保留指定的小数位

-- round(数据)     保留整数位

-- 从sc表中查看学生的平均成绩，结果保留两位小数   79.9091

select  round(avg(grade),2)  '平均成绩'   from  sc;

-- 从sc表中查看选了c02这门课学生的平均成绩，结果保留整数部分

select  round(avg(grade))  '平均成绩'   from  sc  where   cno='c02';

--  把3.1415926，保留两位小数进行查询

select  round(3.1415926,2);

select round(3.1415926);

-- 2.least(值1,值2,值3...)    求最小值

select  least(45,98,21,36,10,49)  '最小值';

-- 3.greatest(值1,值2,值3...)   求最大值   

select  greatest(70,95,13,78,345,71)  '最大值';

-- 对45.8793 保留3位小数

-- 求45,34,45,43,322 中的最大值，最小值

-- 1.now()   获取日期时间

select  now()  '当前日期时间';

-- 2.current_date()   获取日期

select current_date()  '当前日期';

-- 3.current_time()   获取时间

select  current_time()  '当前时间';

-- 4.to_days()   统计总天数

-- 截止到今天，来统计天数的总值

select  to_days(now()) '总天数';

-- 5.dayofyear()  统计该年已过的天数

-- 截止到今天，已经过的天数

select  dayofyear(now())  '已过的天数';

-- 6.week()   查看该年的周数

-- 截止到今天，该周是属于该年的第几周

select week(now())  '当前周数';

-- 格式一：if(布尔表达式,参数1,参数2)   成立，看参数1，否则看参数2

select  if(5>6,'test','testing')  '结果';

select  if(5<6,'test','testing')  '结果';

-- 格式二：if(null,参数1,参数2)    如果第一个位置为空，直接看参数2

select  if(null,'T','F')  '结果';

-- 2.ifnull(参数1,参数2)   如果参数1有值，直接返回;如果参数1是null，返回参数2

select  ifnull('test','testing')  '结果';

select  ifnull(null,'testing')  '结果';

十.子查询的使用(重点)

1.子查询：指的是查询语句，嵌套查询语句---->一般是放在where子句中，表示条件。

2.子查询的应用使用场景：

2.1查询过程中，虽然给了条件，但是条件的结果并不明确，这个时候就需要借助于查询语句，把条件的结果查出来，参与操作。

例：从sc表中查看计算机系学生的选课记录.

解析：select  选课记录*   from   sc   where  计算机系学生(需要查询);

2.2如果聚合函数当条件，是不能直接放在where子句中，那么就可以借助于查询语句，把聚合函数的结果查询出来，参与操作。

例：从sc表中查看所修课程的成绩大于平均成绩的学生信息

解析：select  *  from  sc  where  grade>(select  avg(grade)  from  sc);

3.子查询语法格式：

格式一：  in 来匹配子查询的结果值    嵌套子查询

select  列名  from  表名  where   列名   in(select  列名   from  表名   where  条件...);

注意1：条件要什么，子查询就查什么

格式二：= 来匹配子查询的结果值   相关子查询(单值子查询)

select  列名  from  表名  where  列名  =(select  列名   from  表名   where  条件...);

注意2：如果是用=匹配子查询，那么就要求子查询的结果只能有一个值；如果不确定子查询结果的个数，推荐使用in；子查询都是用小括号来包裹的。

select  列名  from  表名  where   列名   in(select  列名   from  表名   where  列名  in  (select  列名   from  表名   where 条件...));

延伸一种写法：

select  列名   from  表名  where  列名  比较运算符(select  列名  from  表名  where  条件....);

-- 例：从sc表中查看计算机系学生的选课记录.

select  *

from  sc

where  sno  in(select  sno  from student  where  sdept='计算机系');   --  要求:学生--->计算机系

-- 查询出计算机系的学生

select  sno  from student  where  sdept='计算机系'

-- 例：从sc表中查看修了VB课程的学生选课情况

select  *

from  sc  

where  cno =(select  cno  from  course  where cname='VB');  -- 学生修了VB课程

-- 查看VB课程的课程号

select  cno  from  course  where cname='VB';

-- 例.查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。

select  sno,grade

from  sc

where  cno='c02'   and   grade>(select  avg(grade)  from  sc  where  cno='c02');

-- 此课程c02的平均成绩  79.6667

select  avg(grade)  from  sc  where  cno='c02';  

-- 查看籍贯是beijing的学生所在的班级名称

select  班级名称

from  班级信息

where  班级编号  in(select  班级编号   from   学生信息   where   籍贯='beijing');

--  查看籍贯是beijing的学生所在的班级编号---名称

select  班级编号   from   学生信息   where   籍贯='beijing';

-- 查询修了c02这门课的学生的姓名，性别，专业

select sname,ssex,sdept

from  student

where  sno  in(select  sno  from  sc  where  cno='c02');

-- 可以从sc表中根据课程号查询出对应选修学生学号

select  sno  from  sc  where  cno='c02';

-- 案例：查询和'ff'在同一个班的学生的信息(学号，姓名，性别，籍贯，班级名称，年级)。

select 学号,姓名,性别,籍贯,班级名称,年级

from  学生信息 s,班级信息 c

where  s.班级编号=c.班级编号  and  s.班级编号=(select  班级编号  from  学生信息   where  姓名='ff');

-- 查看ff这个学生所在的班级编号

select  班级编号  from  学生信息   where  姓名='ff';

-- 查看修了数据库基础课程的学生的姓名，所在的系别

-- 分析：数据库基础cname---->cno--->sc表中查询学生sno--->在student表查询姓名，系别

select sname,sdept  from  student  where  sno  in(

select  sno  from  sc  where  cno  in(select  cno  from  course  where cname='数据库基础'));

十一.除了select语句中可以使用子查询，在insert，update，delete语句中也可以嵌套子查询语句

1.insert语句和select语句做结合

思想：把select查询出来的数据，结合insert插入到指定的表中

格式：insert  into   表名    select查询语句;

-- 创建一个表stu(学号，姓名，年龄，专业)

create  table  stu(

学号  varchar(10),

姓名  varchar(10),

年龄  int,

专业  varchar(10)

);

--  向stu表中插入数据(只保存计算机系和信息系的学生记录)

insert  into  stu  select  sno,sname,sage,sdept  from  student  where  sdept  in('计算机系','信息系');

select  *  from  stu;

-- 创建一个学生表(学号,姓名，性别,课程号，成绩)

create  table  学生表(

学号   varchar(10),

姓名   varchar(10),

性别   varchar(10),

课程号  varchar(10),

成绩  int

);

--  向学生表中插入数据(修了c02,c04,且成绩大于等于60)

insert into  学生表   select  student.sno,sname,ssex,cno,grade   from   student,sc  where  student.sno=sc.sno  and  cno in('c02','c04')  and  grade>=60;

select  *   from  学生表;

2.update语句和select语句结合

格式：update  表名  set  列名=值  where  条件;

select查询语句还是放在where子句中表示条件

-- 将计算机系学生的选课成绩添加10分

update  sc

set  grade=grade+10

where  sno  in(select  sno  from  student  where  sdept='计算机系');

3.delete语句和select语句结合

格式：delete   from  表名  where  条件;

select查询语句还是放在where子句中表示条件

--  从sc表中删除计算机系学生的选课记录

delete  from  sc where  sno  in(select  sno  from  student  where  sdept='计算机系');

-- 把修了VB课程学生的成绩减去10分

update  sc   set  grade=grade-10  where  cno  in(select  cno  from course  where cname='VB');

-- 删除信息系男生的选课记录

delete  from  sc  where  sno  in(select sno  from  student  where sdept='信息系'  and  ssex='男');

=========================================================

一.视图概述

1.视图是基于某个查询结果的虚表。(根据实际存在的表，经过查询之后，创建出来的一个虚表，被称为视图)

2.视图如同真实的表一样，对视图进行增删改(insert,update,delete)操作，原表数据会受影响，同样的道理，对原表进行增删改操作，视图也会受影响。

3.视图的作用：方便用户对数据进行操作

二.创建视图

格式：create   view   视图的名字   as    select查询语句;

-- 创建一个视图view_student,包含计算机系和数学系学生的信息

create  view  view_student  as   select  *   from   student  where  sdept  in('计算机系','数学系');

-- 查询view_student视图中的内容

select *  from  view_student;

-- 创建一个视图view_sc,包含修了c02课程的学生学号，姓名，年龄，课程号，成绩

create  view  view_sc  as  select  student.sno,sname,sage,cno,grade  from student,sc  where  student.sno=sc.sno  and  cno='c02';

select  *   from  view_sc;

三.修改视图

1.修改视图的结构

alter  view  视图的名字   as    select查询语句;

2.修改视图中的数据

格式：参考修改表数据的语法格式，把表名改成视图名

update  视图名字   set  列名=值   where  条件;

-- 修改view_student视图结构(学号,姓名,专业)

alter  view  view_student  as   select  sno,sname,sdept   from  student  where  sdept in ('计算机系','数学系');

-- 把视图view_student中，9512101学生的专业改为软件测试

update  view_student   set  sdept='软件测试'   where  sno='9512101';

select  *  from  student;

-- 在view_student,把9512102学生姓名改成刘晨晨

update  view_student  set sname='刘晨晨'  where  sno='9512102';

-- 查询view_student视图中的内容

select *  from  view_student;

四.删除视图

1.删除视图中的数据

格式：参考删除表数据的语法格式，把表名改成视图名

delete  from  视图的名字   where   条件;

2.insert语句向视图中插入数据

格式：参考表插入数据的格式

insert  into  视图名字  values(值1,值2...);

3.删除整个视图

格式：drop   view    视图名字;

-- 创建一个视图view_student,包含计算机系和数学系学生的信息

create  view  view_student  as   select  *   from   student  where  sdept  in('计算机系','数学系');

-- 创建一个视图view_sc,包含修了c02课程的学生学号，姓名，年龄，课程号，成绩

create  view  view_sc  as  select  student.sno,sname,sage,cno,grade  from student,sc  where  student.sno=sc.sno  and  cno='c02';

select  *   from  view_sc;

-- 修改view_student视图结构(学号,姓名,专业)

alter  view  view_student  as   select  sno,sname,sdept   from  student  where  sdept in ('计算机系','数学系');

-- 把视图view_student中，9512101学生的专业改为软件测试

update  view_student   set  sdept='软件测试'   where  sno='9512101';

select  *  from  student;

-- 在view_student,把9512102学生姓名改成刘晨晨

update  view_student  set sname='刘晨晨'  where  sno='9512102';

-- 查询view_student视图中的内容

select *  from  view_student;

-- 向view_student表中插入两条数据

insert into  view_student  values('9512106','张三','计算机系'),('9512107','李四','计算机系');

--  从view_student视图中，删除张三学生记录

delete  from  view_student  where  sname='张三';

-- 删除view_student视图

drop  view  view_student;

三.视图

视图---->对实表进行查询得到的一张虚表

今日内容：

一.索引概述

1.索引的定义：索引是针对表中的列来进行设置的，能够快速的查询数据。

例：表(书)     索引(目录)    定位的操作

一个表中索引的设置，不会受到个数限制。

2.索引的作用(优点)---->查询

2.1加快数据的检索(查询)---->最根本的作用

2.2保证数据的唯一性

2.3实现表与表之间的参照完整性

2.4利用索引设置，可以减少分组和排序的时间

二.索引的优缺点

a.索引的缺点：

1.创建索引需要消耗数据空间，并花费一定的时间

2.查询的速度是快了，但是索引会减慢增删改的操作

3.索引的创建也是需要消耗系统性能

4.索引的优势一定在于表中数据越多，查询的速度提升就越明显，对于小表来说，索引可能不会产生优化的效果

b.优先考虑建立索引的列：

1.建立有主键或者外键约束的列

2.查询中频繁使用的列

3.连接过程中频繁使用的列(表连接，子查询)

4.分组和排序用到的列

三.创建索引

1.普通索引的创建：

格式：create   index    索引的名字  on  表名(列名);

2.唯一索引的创建

格式：create  unique   index   索引的名字   on   表名(列名);

四.删除索引

格式：drop   index   索引的名字   on  表名;

-- 给student表中sno的列创建一个普通的索引index_sno

create  index  index_sno  on  student(sno);

select  sno  from student;  -- 0.063s   0.060s

-- 给course表的cno列设置唯一索引 index_cno

create  unique  index  index_cno   on  course(cno);

-- 删除course表中的唯一索引

drop  index  index_cno  on  course;