(万字文章)搞懂数据库看这一篇就够了
(万字文章)搞懂数据库看这一篇就够了
- (万字文章)搞懂数据库看这一篇就够了
-
- 1. 为什么要学习数据库?
-
- 什么是数据库?
- 常见的数据库有哪些呢?
- 2. SQL语句
-
- 什么是SQL?
- SQL语句的分类?
-
- DDL?
-
- 1. 针对数据库结构的操作
- 2. 针对表结构的操作
- DML?
-
- 1. 添加操作:insert into ...
- 2. 修改操作:update ... set ...
- 3. 删除操作:delete from ...
- DQL?
-
- 1. 简单的查询
- 2. 条件查询:
- 3. 复杂的条件查询
-
- a. 聚合函数:
- b. 排序查询
- c. 分组查询
- d.分页查询
- 3. 约束
-
- 1. 非空约束:not null
- 2. 唯一约束:unique
- 3. 主键约束:primary key
- 4. 自动增长:auto_increment
- 5.外键约束
- 4.表与表之间的关系
-
- 1. 一对一
- 2. 一对多(多对一)
- 3. 多对多
- 5.多表查询
-
- a. 笛卡尔积查询
- b. 内连接查询
- c.外连接查询
- d.子查询
- 5.事务
-
- 什么是事务?
- 事务处理:
- 事务的四大特性
- 6. 视图(view)
-
- 什么是视图?
- 视图有什么用?
- 如何创建视图?
- 修改视图的内容?
- 修改视图的结构?
- 删除视图?
- 7. 索引
-
- 什么是索引?
- 索引的分类?
- 索引的操作?
(万字文章)搞懂数据库看这一篇就够了
1. 为什么要学习数据库?
-
为什么要学习容器?
数据一多,就不方便管理,为了方便管理这些数据,要将数据都存储到容器,方便管理和操作 -
为什么学习IO流?
如果很多数据需要持久性,永久性的进行存储的话,使用数组容器和集合容器是无法实现的,因为他们在运行一次之后就会在内存中消失,就会导致数据的丢失。
使用IO流,就可以实现将数据从内存存储到本地磁盘,从而实现了永久存储 -
为什么学习数据库?
使用IO流技术虽然可以将很多数据进行永久性的存储,但是未来操作这些数据是非常的不方便。
使用数据库,就可以很简单的修改里面的任何一条记录信息
什么是数据库?
- 数据库是存放数据的仓库,全称叫做数据库管理系统,就是一款软件
常见的数据库有哪些呢?
-
关系型数据库:
- Oracle数据库:Oracle公司的,大型数据库,是收费的
- *MySql数据库:Oracle公司的,中小型数据库
- DB2数据库:IBM公司的,银行用的多
- SQL Server:微软公司的
…
-
非关系型数据库:
- Redise
- HBase
- MongoDB
- Neo4J
…
-
MySql的服务的开启和关闭?
-
怎么找到服务?
1.在搜索窗口中搜索服务,打开即可
2.在运行窗口输入services.msc,回车即可 -
如何开启服务?
1.找到MySql服务之后,鼠标点击启动即可
2.在dos窗口(管理员)中,输入:net start mysql -
如何关闭服务?
1.找到MySql服务之后,鼠标点击停止即可
2.在dos窗口(管理员)中,输入:net stop mysql
-
-
MySql的登录和退出?
-
登录:
mysql -uroot -p密码 -
退出:
exit
-
-
了解几个概念?
数据库管理系统:数据库软件,下载安装即可
数据库:安装好数据库软件之后,会生成专门用来存储数据的文件夹
表:是数据库中的存放数据的文件
2. SQL语句
什么是SQL?
- Structured Query Language: 结构化 查询 语言
- SQL的工业化标准: 一套规范,按照这套规范制作出来的sql语句,是可以操作任何数据库的,是通用的。
方言: 针对不同的数据库厂商,会提供不会的只能操作自己公司研发的数据库的sql语句,就称为方言
SQL语句的分类?
- DDL:数据库定义语言,这里面的sql语句,主要是用来操作数据库和表的结构的
- *DML:数据库操作语言,这里面的sql语句,主要是用来操作数据库中的表中的数据的增删改的
- *DQL:数据库查询语言,这里面的sql语句,主要是用来操作数据库中的表中的数据的查询的
- DCL:数据库控制语言,这里面的sql语句,主要是用来操作数据库的用户的账号和密码,权限,访问级别等等
DDL?
- 数据库定义语言,操作数据库和表的结构的
1. 针对数据库结构的操作
- 查看有哪些数据库?
show databases; - 创建一个新的数据库?
create database 数据库的名字; - 查看数据库的创建细节(查看创建数据库的sql语句长什么样)?
show create database 数据库的名字; - 删除数据库?
drop database 数据库名字; - 指定使用的数据库?
use 数据库名字;
2. 针对表结构的操作
- 查看数据库中有哪些表?
show tables;
- 创建一张表?
create table 表名 (字段名 数据类型,字段名 数据类型,... ...);create table person (name varchar(20),age int);
- 常见的数据类型?
intdecimal(5,2)date:精确到年月日datetime:精确到年月日时分秒timestamp:精确到年月日时分秒,如果没有赋值,会按照当前系统时间自动填充varchar(20)
- 查看表的创建细节(创建表使用的sql语句是什么)?
show create table 表名;
- 查看表结构?
desc 表名;
- 在原表的基础之上,增加一个新的字段?
alter table 表名 add 字段名 数据类型;alter table person add sex varchar(20);
- 在原表的基础之上,修改字段的名字?
alter table 表名 change 原字段名 新字段名 数据类型;alter table person change sex gender varchar(20);
- 在原表的基础之上,修改字段的数据类型?
alter table 表名 change 原字段名 新字段名 数据类型;alter table person change gender gender int;alter table 表名 modify 字段名 数据类型;alter table person modify gender varchar(20);
- 在原表的基础之上,删除某个字段?
alter table 表名 drop 字段名;alter table person drop gender;
- 修改表的名字?
rename table 原表名 to 新表名;rename table person to user;
- 删除指定的表?
drop table 表名;drop table student;
DML?
- 数据库操作语言,主要对表中的数据的增加,修改和删除操作
1. 添加操作:insert into …
1.添加一条记录a. insert into 表名 (字段名, 字段名, ...) values (数据1, 数据2, ...); insert into user (name, age) values ('tom', 18); insert into user (name) values ('jerry'); insert into user (age) values (19);b. insert into 表名 values (数据1, 数据2, ...); insert into user values ('rose', 19); 注意:有多少个字段,就要给多少个值 注意:如果添加中文内容,在添加之前设置编码:set names gbk;2.添加多条记录a. insert into 表名 (字段名, 字段名, ...) values (数据1, 数据2, ...), (数据1, 数据2, ...),..; insert into user (name, age) values ('jack', 20), ('tony', 21);b. insert into 表名 values (数据1, 数据2, ...), (数据1, 数据2, ...),...;
2. 修改操作:update … set …
1.修改所有记录a. update 表名 set 字段名=值; update user set age = 18; update user set name = 'tom'; update user set name = 'jerry', age = 19;2.修改一条记录a. update 表名 set 字段名=值 where 字段名=值;
- 将名字为jerry的年龄改为20
update person set age=20 where name='jerry';
3. 删除操作:delete from …
1.删除所有记录a. delete from 表名; delete from person; b. truncate 表名; truncate person; 注意:这条sql语句属于DDL里的 a和b有什么区别?a的删除所有是逐行删除b的删除所有是先将整张表给删除,然后再重新创建一张新的表2.删除一条记录a. delete from 表名 where 字段名=值;
- 删除名字为tom的那条记录
delete from person where name = 'tom';
- 删除年龄为20的那条记录
delete from person where age = 20;
- 删除年龄为18,名字为jerry的那条记录
delete from person where name = 'jerry' and age = 18;
DQL?
- 数据库查询语言,主要是操作表中的查询的
1. 简单的查询
select * from table
2. 条件查询:
常用的条件:=><>=<=!=, between...and...in ()is nullis not nulland, &&or, ||distinctlike:模糊查询as:起别名注意,使用条件查询,一定要用到关键字where,where的后面写条件
- 搭建环境:
create table user (id int,name varchar(20),age int,sex varchar(20));insert into user values(1, 'tom', 18, '男');insert into user values(2, 'jerry', 20, '男');insert into user values(3, 'rose', 17, '女');insert into user values(4, 'jack', 21, '男');insert into user values(5, 'tony', 22, '男');insert into user values(6, 'mary', 28, '女');
- 查询表中的名字为jack的那条记录信息
select * from user where name = 'jack';
- 查询表中年龄为18岁的记录信息
select * from user where age = 18;
- 查询表中年龄大于20岁的记录信息
select * from user where age > 20;
- 查询表中年龄小于20岁的记录信息
select * from user where age < 20;
- 查询表中年龄大于等于20的记录信息
select * from user where age >= 20;
- 查询表中年龄小于等于20的记录信息
select * from user where age <= 20;
- 询表中年龄不等于20岁的记录信息
select * from user where age != 20;select * from user where age 20;
- 查询表中姓名不为tom记录信息
select * from user where name != 'tom';
- 查询表中年龄在20岁到25岁之间的记录信息
select * from user where age between 20 and 25;select * from user where age >= 20 and age <= 25;
- 查询表中年龄为18,21,28的记录信息
select * from user where age in (18, 21, 28);select * from user where age = 18 or age = 21 or age = 28;
- 查询表中姓名为tom,rose,tony的记录信息
select * from user where name in ('tom', 'rose', 'tony');
- 查询表中性别为null的记录信息
select * from user where sex is null;注意:select * from user where sex = null; --错误写法
- 查询表中性别不为null的记录信息
select * from user where sex is not null;
- 查询表中姓名为tom,年龄为20岁的记录信息
select * from user where name = 'tom' and age = 20;select * from user where name = 'tom' && age = 20;
- 查询表中姓名为tom,或者年龄为20岁的记录信息
select * from user where name = 'tom' or age = 20;select * from user where name = 'tom' || age = 20;
- 查询表中看一看有哪些年龄段
select distinct age from user;注意:distinct是去重的意思select distinct age, name from user; --针对age和name相同的去重
- 查询表中名字中第二个字带’三’字的记录信息
select * from user where name like '_三%';_:代表任意一个字符%:代表任意0到多个的字符
- 查询表中名字为三个字,并且第二个字带’三’的记录信息
select * from user where name like '_三_';
- 查询表中名为带’三’的记录信息
select * from user where name like '%三%';
- 查询表中名字姓’张’的记录信息
select * from user where name like '张%';
- 查询表中所有的记录信息(给字段起别名)
select * from user;select id, name, age, sex from user;select id as 编号, name as 姓名, age as 年龄, sex as 性别 from user;select id 编号, name 姓名, age 年龄, sex 性别 from user;
- 查询表中所有的记录信息(给表起别名)
select * from user;select user.id, user.name, user.age, user.sex from user;select * from user as u;select u.id, u.name, u.age, u.sex from user as u;
3. 复杂的条件查询
a. 聚合函数:
- count():统计
- 查询user表中有多少个用户
select count(*) from user;select count(id) from user;select count(1) from user;
- sum():求和
- 查询表中所有的年龄总和为多少
select sum(age) from user;
- max():最大值
- 查询表中年龄最大的记录信息
select max(age) from user;
- min():最小值
- 查询表中年龄最小的记录信息
select min(age) from user;
- avg():平均值
- 查询表中年龄平均值为多少
select avg(age) from user;
b. 排序查询
order by 字段名 排序方式排序方式:升序:asc降序:desc注意:默认是升序的
- 根据年龄进行升序排列
select * from user order by age;select * from user order by age asc;
- 查询年龄为17,18,28的信息,并且按照年龄升序排列
select * from user where age in (17, 18, 28) order by age asc;
- 根据年龄进行降序排列
select * from user order by age desc;
- 根据姓名字典顺序进行排序
select * from user order by name asc;
- 根据年龄由大到小,如果年龄相同,再根据姓名字典顺序排序
select * from user order by age desc, name asc;
c. 分组查询
group by 分组的字段
- 按照性别进行分组,查询出每组的人数是多少
select count(*) from user group by sex;select sex, count(*) from user group by sex;select id, name, age, sex, count(*) from user group by sex; --这么写没有意义注意:一般使用分组查询,查询出来的是聚合函数,或者根据分组字段的字段信息
- 按照年龄进行分组,查询出每组的人数是多少
select count(*) from user group by age;select age, count(*) from user group by age;
- 按照年龄进行分组得到每组的人数,要求每组的每个人的年龄要大于20岁
(对20岁以上的人按照年龄进行分组,得到每组的人数)?
提示:先筛选,后分组
select age, count(*) from user where age > 20 group by age;
- 按照年龄进行分组得到每组的人数,要求每组的人数必须大于等于2
提示:先分组,后筛选
select age, count(*) from user group by age having count(*) >= 2;
- 按照年龄进行分组得到每组人数,要求每组的每个人的年龄大于18,并且分组之后每组的人数大于等于2
提示: 先筛选,然后再分组,最后再筛选
select age, count(*) from user where age > 18 group by age having count(*) >= 2;
- where和having的区别?
- where是在分组之前进行条件判断,having是在分组之后进行条件判断
- where只能写在group by的前面,having只能写在group by的后面
- where后面不能写聚合函数,having后面可以写聚合函数
d.分页查询
limit 起始个数, 每页显示的条数;
- 查询第一页的记录
select * from user limit 0, 3;
- 查询第二页的记录
select * from user limit 3, 3;
- 查询第三页的记录
select * from user limit 6, 3;
- 查询第N页的记录
select * from user limit (N-1) * 每页显示的条数, 每页显示的条数;
3. 约束
1. 非空约束:not null
-
加与不加有什么区别?
加了非空约束,值就不能为NULL
-
在创建表时候添加非空约束?
create table user (name varchar(20) not null,age int);
- 删除非空约束?
alter table user modify name varchar(20);
- 在创建表之后再去添加非空约束?
alter table user modify name varchar(20) not null;
2. 唯一约束:unique
-
加与不加有什么区别?
加了唯一约束之后,这一列的值不能有重复的 -
在创建表的时候添加唯一约束?
create table user (name varchar(20) unique,age int);
- 删除唯一约束?
alter table user drop index name;
- 在创建表之后再添加唯一约束?
alter table user modify name varchar(20) unique;
3. 主键约束:primary key
-
加与不加有什么区别?
加了主键约束,就代表非空且唯一
每张表中只能有一个主键 -
在创建表的时候添加主键约束?
create table user (id int primary key,name varchar(20));
- 删除主键约束?
alter table user drop pirmary key;
- 在创建表之后再去添加主键约束?
alter table user modify id int primary key;
4. 自动增长:auto_increment
-
加与不加有什么区别?
加了自动增长,会自动+1 -
在创建表的时候添加自动增长?
create table user (id int primary key auto_increment,name varchar(20));
- 删除自动增长?
alter table user modify id int;
- 在创建表之后再去添加自动增长?
alter table user modify id int auto_increment;
- 删除表中的所有记录?
1.delete from user;2.truncate user;
5.外键约束
外键约束是建立在有两张表的基础之上的
- 格式:
constraint 外键的名字 foreign key (外键字段) references 另外一张表名(字段);
- 简化格式:
foreign key (外键字段) references 表名(字段);
- 在创建表的时候添加外键约束?
create table user (id int primary key,name varchar(20));create table computer (id int primary key,name varchar(20),uid int,constraint c_u_key foreign key (uid) references user(id));
- 删除外键约束?
alter table computer drop foreign key c_u_key;
- 在创建表之后再去添加外键约束?
alter table computer add constraint c_u_key foreign key (uid) references user(id);
4.表与表之间的关系
1. 一对一
- 例如:person表和ID表(身份证号)
一个person只能有一个ID,一个ID是能所属一个person
如果一对一的关系,会将两张表合并成一张表
2. 一对多(多对一)
- 例如:用户表和电脑表
一个用户有多台电脑
一个电脑只能属于一个用户
设计原则:在多的一方增加一个新的字段,关联一的一方的主键id,并且设置外键关联。
3. 多对多
-
例如:学生表和老师表
一个学生可以被多个老师教
一个老师也可以教多个学生
设计原则:要额外创建一张新的表,去分别关联另外的两张表 -
练习:
已知有三个表,分别为用户表(user),商品表(goods),分类表(category),
通过现实的分析,去设计这三张表,并创建。- 分析:
用户表和商品表的关系
多对多的关系
用户表和分类表的关系
没有什么直接的关系
分类表和商品表的关系
一对多的关系 - 创建:
- 分析:
用户表:create table user (id int primary key auto_increment,name varchar(20));商品表:create table goods (id int primary key auto_increment,name varchar(20),cid int,constraint g_c_key foreign key (cid) references category(id));分类表:create table category (id int primary key auto_increment,name varchar(20));关联表:create table u_g (uid int,gid int,primary key(uid, gid),constraint ug_u_key foreign key (uid) references user(id),constraint ug_g_key foreign key (gid) references goods(id));
5.多表查询
- 环境搭建:
create table a (id int,name varchar(20));insert into a values(1,'tom');insert into a values(2,'jerry');insert into a values(3,'rose');create table b (id int,name varchar(20));insert into b values(1,'张三');insert into b values(2,'李四');insert into b values(4,'王五');
a. 笛卡尔积查询
- 是所有多表查询的最基础的查询,其他的多表查询都是建立在笛卡尔积查询的基础之上的,但是笛卡尔积查询本身没有任何的意义,所以以后不会写它。
select * from a, b;
b. 内连接查询
- 隐式内连接查询:
select * from a, b where a.id = b.id;
- 显示内连接查询:
select * from a inner join b on a.id = b.id;
注意:显示内连接和隐式内连接的查询结果是一样的,只是写法不同
c.外连接查询
- 左外连接查询:
select * from a left join b on a.id = b.id;
- 右外连接查询:
select * from a right join b on a.id = b.id;
d.子查询
- 嵌套查询:查询语句里面还有查询语句
- 里面查询语句查询出来的结果是一个数据
- 查询出a表中id值等于b表中名字为李四的id值的记录信息
select * from a where id = (select id from b where name = '李四');
- 里面查询语句查询出来的结果是多个数据
- 查询出a表中id值等于b表中名字为张三,李四的id值的记录信息
select * from a where id in (select id from b where name = '张三' or name = '李四');
- 里面查询语句查询出来的结果是一张表
- 查询a表中的记录信息(按照id值降序查询)(使用子查询来实现)
select * from a order by id desc;select * from (select * from a order by id desc) newA;select * from newA;体验出了表的重构思想
5.事务
什么是事务?
一般是指要做的或所做的事情
-
不需要事务处理
只需要用一条sql语句就可以完成的操作基本不需要事务处理 -
需要事务处理
至少需要2条或者以上的sql语句才可以完成的操作,需要事务处理
例如转账:tom给jerry转了100元钱
create table user (id int primary key auto_increment,name varchar(20),money int);insert into user values(1,'tom',1000);insert into user values(2,'jerry',1000);start transaction;开启事务update user set money = 900 where name = 'tom';update user set money = 1100 where name = 'jerry';commit; 提交事务
事务处理:
- 方法一:
start transaction; 开启事务commit; 提交事务rollback; 回滚事务常见的组合:start transaction;commit;start transaction;rollback
- 方法二:
select @@autocommit; 查看当前的提交方式是自动还是手动1:自动提交0:手动提交set @@autocommit = 0;
事务的四大特性
- 原子性:原子是化学反应中最小的粒子,不能再分割了。
- 持久性:就是指通过事务操作完的数据,也是永久的存储,永久的改变。
- 一致性:在使用事务操作的时候,操作前和操作后的数据是一致的。
- 隔离性:事务与事务之间是没有关系,互相隔离,互相不干扰的,但是,有的时候还是会打破隔离性,当多个事务并发操作同一张表的时候,就会打破隔离性。
- 隔离打破会产生的问题?
- 脏读: 事务A 读到了 事务B 的未提交的数据
- 不可重复读:事务A 读到了 事务B 已经提交后的修改(update)的数据
- 虚读(幻读):事务A 读到了 事务B 已经提交后的增加(insert)或者减少的数据
- 事务的隔离级别?
- read uncommitted:设置这个隔离级别,上面的三个问题都会发生
- read committed:设置这个隔离级别,能够解决脏读,但是会产生幻读和不可重复读
Oracle数据库的默认隔离级别 - repeatable read:设置这个隔离级别,能够解决脏读,不可重复读,但是x会产生幻读
MySql数据库的默认隔离级别 - serializable:设置这个隔离级别,都能够解决
查看当前的隔离级别:
select @@tx_isolation;x
修改隔离级别:
set global transaction isolation level ???;
6. 视图(view)
什么是视图?
- 视图是一张虚拟的表,在数据库中不是真实存在的
视图有什么用?
- 可以将一些复杂的查询语句通过封装成视图,方便我们后续的查询操作
一般是查询次数比较频繁,同样的需求经常出现,就可以封装为视图 - 例子:用户表和电脑表
- 查询名字叫tom的用户拥有哪些电脑,将用户名和所拥有的电脑名给展示出来。
select u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';
如何创建视图?
create view 视图名 (列名, ...) as 复杂的查询语句;create view user_computer (user_name, computer_name) asselect u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';
修改视图的内容?
update user_computer set computer_name = 'lenovo' where computer_name = 'xiaomi';
修改视图的结构?
alter view user_computer (user_name, cname) asselect u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';
删除视图?
drop view user_computer;
7. 索引
什么是索引?
- 是帮助MySql数据库高效获取数据的一种数据结构,所以说索引的本质就是数据结构,数据库管理系统平时除了要管理表数据的各种操作,要还维护索引,因为索引是数据结构,里面涉及到了
- 一些高级算法,这些高级算法通过某些方式指向了数据表中的某些列
索引的分类?
- 按照功能分类?
- 普通索引
- 主键索引(一张表的主键自动添加主键索引)
- 外键索引(一张表的外键自动添加外键索引)
- 联合索引
- 全文索引
- 唯一索引
- 按照结构分类?
- BTree索引
底层是B + Tree数据结构 - Hash索引
索引的操作?
- 查看索引
show index from user;
- 创建索引
create index idx_name on user(name);
- 删除索引
drop index idx_name on user;