Mysql知识点整合

目录

1.库的操作

2.表的操作

3.基础函数

4.聚合函数

5.条件查询

6.关联查询

7.事务

8.索引

9.视图

10.总结

---

1.库的操作

 1.1建库（建demo）

create database demo default character set utf-8;

1.2删库（删demo）

drop database demo;

1.3查库

show databases;

2.表的操作

2.1创建表

格式：

create table 表明 （字段名  字段类型 字段约束）；

创建user表，表内有字段id（自增、主键）、姓名、年龄、性别

create table user (
id int primary key auto_increment,
name varchar(20),
age int,
sex varchar(10)
);

2.2修改表

格式：

添加列：alter table 表名 add column 列字段（字段类型 约束条件）；

向user表中添加列tel

alter table user add column tel int(30);

2.3查看所有表

格式：show tables；

2.4查看表结构

格式：desc 表名；

2.5向表中插入数据

格式：

insert into 表名  values(值1，值2，值3，....值n)；

向user表中添加小明的数据：

insert into user values (
null,
'小明',
18,
'男',
187633
);

向user中插入小红的数据：

insert into user values (
null,
'小红',
18,
'女',
187632
);

注：1.因为id设置了自增，所以id可以赋值为null

       2.因为tel字段的约束设为了30，可能不足，可自行调节

2.6查询记录

格式：

select  要查询的字段  from  表名   条件;

查询所有信息

select * from user;

查询性别为男的所有信息

select * from user where sex='男';

查询性别为男的名字

select name from user where sex='男';

2.7修改记录

格式：

update 表名 set 字段  条件；

将user表中id为1的用户姓名改为张三

update user set name='张三' where id=1;

2.8删除记录

格式：

delete from 表名 条件；

删除user表中id为1的记录

delete from user where id=1;

3.基础函数

数据准备

创建课程表

create table class 
(id int primary key auto_increment,
teacher_id int,
class_name varchar(50)
);

创建教师表

create table teacher (
id int primary key auto_increment,
teacher_id int,
teacher_name varchar(20)
dept_no int
);

插入课程数据

insert into class values(
null,
1001,
'java基础'
);
insert into class values(
null,
1002,
'数据库'
);
insert into class values(
null,
1003,
'spring框架'
);

插入教师表

insert into teacher values(
null,
1001,
'JACK',
1
);
insert into teacher values(
null,
1002,
'tony',
1
);
insert into teacher values(
null,
1003,
'王五',
1
);
insert into teacher values(
null,
null,
'张三',
2
);

3.1 lower（数据转小写）

将教师姓名JACK转为小写并打印

select lower(teacher_name)from teacher where teacher_name='JACK';

3.2 upper(数据转大写)

将教师姓名tony转为大写并打印

select upper(teacher_name) from teacher where teacher_name='tony';

3.3 length(查询数据的长度)

查询teacher表中id为3的教师姓名数据长度

select length(teacher_name) from teacher where id=3;

3.4 substr

截取class表中 id为1的课程名称，从第一个开始，截取三个字。

select substr(class_name,1,3) from class where id=1;

注：（1，3）1表示从第一个开始截取，3表示截取三个

3.5 contat（拼接数据）

在class表中将“java基础”拼接“大全”并打印

select concat(class_name,'大全') from class where id=1;

3.6 replace（替换）

将教师姓名“jack”中的“j‘替换为c；

select replace(teacher_name,'J','c') from teacher where id=1;

3.7 ifnull(判断是否为null ，若为null 则用***替换)

判断教师表中id为4的教师id是否为空，若为空则以1004替换

select ifnull(teacher_id,1004) from teacher where id=4;

3.8 round（四舍五入）、ceil（向上取整）、floor（向下取整）

3.9 时间

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

year & month & day

–hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

4.聚合函数

4.1 count（数量）

查询class记录的数量

select count(*) from class;  //方法一：底层优化过
select count(1) from class;  //方法二：与方法一效果一样
select count(id) from class; //方法三：慢，只能统计非null的

4.2max（最大值）、min（最小值）

求教师id的最大值与最小值

select max(teacher_id) max,min(teacher_id) min from class;

4.3 sum(总和)、avg（平均数）

求教师id的总和和平均数

select sum(teacher_id) sum ,avg(teacher_id) avg from teacher;

5.条件查询

5.1 distinct去重

查询教师的部门，对于相同部门数据不显示

select distinct dept_no from teacher;

5.2 where(条件)

查询教师表中id为1的教师名

select teacher_name from teacher where id=1;

注：where中不能用别名！！

5.3 like（模糊查询）

select * from teacher where teacher_name like 'J%'; --以J开头的

select * from teacher where teacher_name like '%y'; --以y结束的

select * from teacher where teacher_name like '%A%'; --中间包含A的

select * from teacher where teacher_name like 'o__'; --o后面有两个字符的 _代表一个字符位置

5.4 null

select * from teacher where teacher_id is null; --过滤字段值为空的

select * from teacher where teacher_id is not null; --过滤字段值不为空的

5.5 between and

select * from teacher where teacher_id <1002 and teacher_id>1000;

select * from teacher where teacher_id<=1002 and teacher_id>=1000;

select * from teacher where teacher_id between 1000 and 1002;

注：between and包括首尾

5.6 limit （分页）

select * from teacher limit 2 ;--列出前两条

select * from teacher limit 1,2; --从第二条开始,展示2条记录

select * from teacher limit 0,3; --从第一条开始,展示3条记录--前三条

5.7 order by（排序）

SELECT * FROM teacher order by teacher_id ;#默认升序

SELECT * FROM teacher order by teacher_id desc; #降序

注：null值升序排在第一位

5.8 group by（分组）

从教师表中，根据部门分组查询教师id最大的教师id，打印每个部门最大的教师id及部门编号

select max(teacher_id) max,dept_no from teacher
group by dept_no;

5.9 having（条件）

having与where类似，但是group by只能跟having连用，且having用于聚合函数；

按部门分组，查询每个部门教师id最大值大于1000的记录

select max(teacher_id) max ,dept_no
from teacher 
group by dept_no
having max>1000;

6.关联查询(多表联查)

6.1笛卡尔积

select * from teacher,class;

笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，先在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

6.2 join

内连接  join（inner join）

打印教师姓名及教师对应的课程

select c.class_name,t.teacher_name 
from class c join teacher t
on c.teacher_id=t.teacher_id;

左连接 left join

打印教师姓名及教师对应的课程

select c.class_name,t.teacher_name 
from class c left join teacher t
on c.teacher_id=t.teacher_id;

右连接 right join

打印教师姓名及教师对应的课程

select c.class_name,t.teacher_name 
from class c right join teacher t
on c.teacher_id=t.teacher_id;

 总结：

1.使用内连接，不会打印null值，而使用外连接会填充null值。

2.使用内连接，是取两张表的交集，同理三表联查的话就是三张表的交集；

使用左连接，是取左表的全集及右表的交集部分；

使用又连接，是取右表的全集及左表的交集部分；

6.3 子查询

打印教师为王五课程名

select class_name from class
where teacher_id in (
select teacher_id from teacher
where teacher_name='王五'
);

附：sql的执行顺序：

(1) FROM [left_table] 选择表
 
(2) ON <join_condition> 链接条件
 
(3) <join_type> JOIN <right_table> 链接
 
(4) WHERE <where_condition> 条件过滤
 
(5) GROUP BY <group_by_list> 分组
 
(6) AGG_FUNC(column or expression),... 聚合
 
(7) HAVING <having_condition> 分组过滤
 
(8) SELECT (9) DISTINCT column,... 选择字段、去重
 
(9) ORDER BY <order_by_list> 排序
 
(10) LIMIT count OFFSET count; 分页

7.事务

7.1四个特性

原子性：

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性：

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

7.2隔离级别

读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
读已提交（read committed） Oracle默认的隔离级别
可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

8.索引

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。
 

8.1分类

• 单值索引：一个索引只包括一个列，一个表可以有多个列
• 唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
• 复合索引：一个索引同时包括多列

8.2 创建索引

查看索引

show index from class;

创建普通索引

格式：

create index 索引名字 on 表名(字段名);

create index cla_index on class(teacher_id);

创建唯一索引

格式：

CREATE UNIQUE INDEX 索引名 ON 表名(字段名);

create unique index class_index on class(teacher_id);

创建复合索引

格式：

CREATE INDEX 索引名 ON 表名 (字段1, 字段2)

create index tea_index on teacher(teacher_id,teacher_name);

删除索引

alter table teacher drop index tea_index;

索引的扫描类型

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描，其次慢的方式
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
null MySQL不访问任何表或索引，直接返回结果
 

最左特性

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

索引优缺点

优点:

1.索引是数据库优化
2.表的主键会默认自动创建索引
3.每个字段都可以被索引
4.大量降低数据库的IO磁盘读写成本，极大提高了检索速度
5.索引事先对数据进行了排序，大大提高了查询效率

缺点：

1.索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
2.索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
3.虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
4.随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

9.视图

          可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。
下次还要发起相同的sql，直接查视图。

创建、使用视图

格式：

create view 视图名 as SQL语句;

select * from 视图名;

//创建 视图
create view viewdemo as
select *from class ;

//使用 视图
sele* from viewdemo;

#视图：就是一个特殊的表，缓存上次的查询结果

#好处是提高了SQL的复用率，坏处是占内存无法被优化