MySQL数据库常用知识（2）

目录

一、MySQL函数：

二、.表连接

三、内连接：

四、外部连接：

五、子查询：

六、视图：

七、索引：

---

一、MySQL函数：

1.字符串函数

length(字符串)  求的是字符串的字节长度

MySQL数据库中一个汉字占3个字节，一个字母占1个字节

char_length(字符串)  求的是字符串的长度

mid(原始字符串,截取的开始位置,截取的长度)

2.数值函数：

round(原始数据,保留的小数位)

round(原始数据)   只保留整数部分

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

greatest(值1,值2...)  求最大的数字

学员操作

对45.8793 保留3位小数

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

3，日期时间函数

now()  获取日期和时间

current_date()  获取日期

current_time()  获取时间

to_days(日期值)  将日期转换成总天数

dayofyear(日期值) 求该年已过的天数

week(日期值)  已过的周数

4.控制函数：

if(布尔表达式,参数1,参数2)

if(null,参数1,参数2)

ifnull(参数1,参数2)

ifnull(null,参数2)

二、.表连接

前提：有时候我们所需要的数据不止在一张表中，需要多个表做结合的查询，就可以用表连接实现。

分类：

1.内部连接(提供了两种表与表之间的连接方式)

表与表之间建立起关联的列，要求列名可以不一样，但是这两个列的数据类型和内容得保持一致

内连接查询：只关联表与表中能够匹配到的数据信息，才能有对应的查询结果

1.1 表连接的实现是通过where关键字来进行的关联

格式一：

select  表名1.列名1,表名1.列名2,表名2.列名1,表名2.列名2...

from 表名1,表名2

where  表名1.列名1=表名2.列名1;

1.2通过inner join on 实现表与表之间的连接

格式二：

select  表名1.列名1,表名1.列名2,表名2.列名1,表名2.列名2...

from 表名1  [inner] join  表名2

on  表名1.列名1=表名2.列名1;

inner join on 又划分为两个分类：

1.等值连接  指的是条件中只包含等号"="，没有其它符号在里面

2.非等值连接  指的是条件中，除了等号之前，还有其他的符号 >    <    >=

多表连接：

格式一：

select  表名1.列名1,表名1.列名2,表名2.列名1,表名2.列名2,表名3.列名1,表名4.列名1...

from 表名1,表名2,表名3,表名4....

where  表名1.列名1=表名2.列名1  and  表名2.列名1=表名3.列名1  and 表名3.列名1=表名4.列名1....

三、内连接：

只会显示两个表中匹配到的数值信息

多个表查询（表数量>2）

1.where

select  表名.列名

from  表名1,表名2,表名3

where 表名1.列=表名2.列  and 表名2.列=表名3.列;

2.on

select  表名.列名

from 表名1  inner join 表名2

on 表名1.列=表名2.列  inner join 表3

on 表名2.列=表名3.列

四、外部连接：

特征：至少会返回出一个表的所有内容

分类：左外部连接    右外部连接

左外部连接：对左边的表不加限制，返回左表的所有行

右外部连接：对右边的表不加限制，返回右表的所有行

在from之后，先写的表是左表，后写的表是右表

外部连接  on

左外部连接的格式：

select   表名.列名

from 表名1  left outer join  表名2

on  表名1.列=表名2.列

会返回左表的所有内容，如果在右表中没有匹配到，在对应位置上就显示为null

右外部连接格式：

select  表名.列名

from  表名1 right outer join 表名2

on  表名1.列=表名2.列;

会返回右表的所有内容，如果在左表中没有匹配到，在对应位置上就显示为null

五、子查询：

指的就是查询语句里面嵌套查询语句

格式：

select 列名 from 表名 where  列名 in(select查询语句);

子查询的注意事项：

1.where后面的条件要什么，我的子查询就查什么

例：条件要学号，子查询就查学号

2.什么情况优先考虑子查询？

2.1 题上给出的条件没有明确的提示结果

例. 查询计算机系学生选修了哪些课程？问：计算机系学生有哪些？

2.2 where后面要用到聚合函数当条件

聚合函数不能直接写，就可以借助查询语句把聚合函数的结果给查出来就OK

成绩大于平均成绩

where grade>(select avg(grade) from sc)

==>where grade>78

例子：

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

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

例：select student.sno,sname,grade        （把所有要查询的全部写到select后 
from sc,student                                          然后 where后面关联  然后看题中条件where sc.sno=student.sno and sc.grade in     写                                                                      到括号里面）and  in 连接
(select grade from sc where grade>90);

子查询：指的是子查询中还包含其他的子查询

select(select(select(select)))

相关子查询：指的是子查询的结果有且只有一个值

当insert语句和select语句做结合，就是把查询的结果插入到某一张表中。

格式：

insert into 表名 select查询语句

update语句

update 表名

set 列名

where 列名 in(select语句)

删除语句

delete from 表名 where 列名 in(select查询语句)

例：删除计算机系学生的成绩

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

六、视图：

指的是根据某个实际的表(实表)查询出来，生成的一个虚表。

创建视图

格式：

create view 视图的名字 as select语句;

注意：

1.视图既然作为一张虚表存在，那么对实表的增删改查操作，视图同样成立

2.视图既然是根据实表得到的，那么对视图的增删改操作，也会影响实表。

 

修改视图（结构）格式：

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

插入数据

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

修改数据：

update 视图的名字

set 列名=值

where 条件;

删除视图数据

delete from 视图的名字 where 条件;

删除视图

drop view 视图的名字;

对于表的增删改查操作，在视图中同样成立。

七、索引：

  类似于书本中的目录

指的是在数据库表中的一个列或多个列的设置，帮助快速的定位所查询的数据。

索引的作用(优点)

1.加快查询速度   (定位)

2.保证数据的唯一性  （唯一约束）

3.实现表与表之间的参照完整性  （进行外键约束参照的列）

4.可以减少group by,order by，分组和排序的时间   （根据某个列来进行分作或者排序）

索引的缺点：

1.创建索引会需要一定的时间和数据空间

2.虽然查询的速度加快了，但是减慢了增删改的速度  (书本中插入内容)

优先考虑建立索引的列：    加快   节约  

1.定义有主键或者外键的列

2.在查询过程中频繁使用的列

3.连接过程中频繁使用的列

4.在排序，分组过程中使用的列

索引的分类：

1.普通索引

2.唯一索引

创建格式：

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

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

设计索引，要考虑到的数据库准则。

1.一个表中如果有大量的索引，会影响insert，update，delete语句的性能。

2.避免对经常更新的表进行过多的索引，索引要保持较窄。

3.建立索引，可以提高更新少，数据量大的查询性能。

4.对小表建立索引，可能不会产生优化的效果。

删除索引：

drop index 索引的名字 on 表名;

练习题：

4.

select cname

from sc,c

where  sc.c#=c.c#

group by c#

having  avg(grade)>80;

5.

select s#,avg(grade)

from sc

group by s#;

3.1.先把课程总数给统计出来

all 所有,代表的是把所有的条件匹配成功的情况下，才会有查询结果

select sname from s where s# in(select s# from sc where c#=all(select c# from c));

select sname

from s,sc

where s.s#=sc.s#

group by s#

having count(sc.c#)=(select count(*) from c);

1.先查询出刘老师所较课程的课程号,根据课程号找出对应的选课学生学号,-->姓名

select sname from s where s# in(

select s# from sc where c# in(

select c# from c where tname like '刘%'));

2.先分别查看C2和C4修课的学生有哪些？ 我只要既选修了C2，又选修了C4

select sname from s where  s# in(select s# from sc where c#='c2') and s# in(select s# from sc where c#='c4');

c#=c2 and  c#=c4   c5 c6 c7

3.1.先把课程总数给统计出来

all 所有,代表的是把所有的条件匹配成功的情况下，才会有查询结果

select sname from s where s# in(select s# from sc where c#=all(select c# from c));