Web开发之MySQL知识点总结

数据库就是一个文件系统，通过SQL语句来获取数据，MySQL是最常用的关系型数据库管理系统，实体与实体之间通过关系连在一起，每个实体都有自己的属性。比较常用的关系型数据库是MySQL和Oracle，SQLServer，本次主要介绍MySQL的相关知识。在数据库服务器内部通常通过数据库存储数据，一般一个应用创建一个数据库。在数据库中使用表存储数据，一般一个实体创建一张表，每个表中往往会有多条记录，一个实体的实例会创建一条新的记录。

对于我们常说的SQL语言，主要分为四种，分别如下：

1.数据定义语言(DDL):create drop alter

2.数据控制语言(DCL):grant if

3.数据操纵语言(DML):insert delete update

4.数据查询语言(DQL):select

目录

1、对数据库的基本操作

2、表的创建与基本操作

3、SQL添加表的记录

4、SQL修改表的记录

5、SQL删除表的记录

6、SQL查看表的记录

7、多表设计之外键约束

8、多表分析之实体之间的关系

9、多表查询操作

10、事务的处理

---

1、对数据库的基本操作

包括数据库的创建，查看，使用数据库，删除具体如下：

create database db1 ;
show databases ;
use db1 ;
drop database db1 ;

2、表的创建与基本操作

创建表，并添加字段，字段类型以及约束条件，其中包括主键约束，唯一约束和非空约束，具体如下：

create database web_test1;
use web_test1;
create table user(
	id int primary key ,
	username varchar(20) unique,
	password varchar(20) not null,
	age int,
	birthday date
);

对表的基本操作包括查看表，对表中的每一列进行增，删，改，对表重命名以及删除表等。

use web_test1 ;
show tables ;
desc user ;
alter table user modify username varchar(30) not null ;
alter table user change username name varchar(25) ;
alter table user add hobby varchar(20) ;
alter table user drop hobby ;
rename table user to user1 ;
drop table user1 ;

3、SQL添加表的记录

向全部列插入数据，如下所示：

insert into user values(null,'张而','123',28,'2022-01-01') ;
insert into user values(null,'李四','123',18,'2023-01-01') ;
insert into user values(null,'张无','123',28,'2024-01-01') ;

向部分列插入数据，如下所示：

insert into user (id,username,password) values (null,'aaa','123');
insert into user (id,username,password) values (null,'bbb','123');
insert into user (id,username,password) values (null,'ccc','123');

4、SQL修改表的记录

根据条件修改表中的记录，如下所示：

update user set password = 'xyz' where username = 'bbb';
update user set password = '143',age=18 where username = 'aaa' ;

5、SQL删除表的记录

根据where条件删除表中的记录，不加条件即删除表中所有的记录。

delete from user where username = 'bbb' ;
delete from user ;

注意：

1. 删除表中的记录有两种做法:
   1. truncate table user;
      1. 删除所有记录，属于DDL语句，将表删除，然后重新创建一个结构一样的表。事务不能控制DDL的
   2. delete from user;
      1. 删除所有记录，属于DML语句，一条记录一条记录删除。事务可以作用在DML语句上的

6、SQL查看表的记录

对于查询一句话总结：

1. 1. S(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);

先创建一张表exam，并向表中插入一些记录，然后再演示查询过程：

create table if not exists exam(
	id int primary key ,
	name varchar(20),
	english int,
	chinese int,
	math	int
);

insert into exam values (null,'张时',85,74,91);
insert into exam values (null,'李四',95,90,83);
insert into exam values (null,'王五',85,84,59);
insert into exam values (null,'赵六',75,79,76);
insert into exam values (null,'田七',69,63,98);
insert into exam values (null,'李老八',89,90,83);

查询包括：基础查询，条件查询，模糊查询，排序查询，分组查询等

select * from exam;
select name,english from exam;
select distinct english from exam;
select * from exam where name = '李四';
select * from exam where name = '李四' and english > 90;
select * from exam where name like '李%';
select * from exam where english in (69,75,89);
select * from exam order by chinese;
select * from exam order by chinese desc;
select sum(english) from exam;
select count(*) from exam;
select max(math) from exam;
select min(chinese) from exam;
select avg(chinese) from exam;

7、多表设计之外键约束

我们创建一个部门表，再创建一个员工表，因为部门和员工实体之间是一对多的关系，所以需要再员工表添加外键约束。

防止发生：插入一个没有部门的员工和删除一个含有员工的部门。

创建部门表：

create table if not exists dept(
	did int primary key auto_increment,
	dname varchar(20)
);
insert into dept values (null,'市场部');
insert into dept values (null,'人事部');
insert into dept values (null,'教研部');

创建员工表并添加外键约束：

create table if not exists employee(
	eid int primary key ,
	ename varchar(20),
	salary double,
	birthday date,
	sex varchar(10),
	dno int,
	foreign key (dno) references dept(did)
);

insert into employee values (1,'张三',8000,'1988-09-01','男',3);

insert into employee values (2,'李四',9000,'1988-09-01','男',1);

insert into employee values (3,'王五',6000,'1988-09-01','男',2);

insert into employee values (4,'赵六',10000,'1988-09-01','男',3);

insert into employee values (5,'孙七',10000,'1988-09-01','男',1);

8、多表分析之实体之间的关系

实体与实体之间三种关系：一对一，一对多，多对多。

对于一对多的关系，建表原则：在多的一方创建外键指向一的一方的主键。

对于多的关系的建表原则：建立中间表，使用两个字段作为外键，分别指向两张表的主键。

 一对一的关系，在多的一方创建外键指向一的一方的主键，将主键设置为unique。

9、多表查询操作

主要分为连接查询和子查询，连接查询包括：交叉连接，内连接和外连接，外连接又分为左外连接和右外连接，一个查询语句条件需要依赖另一个查询语句的结果。

在进行查询之前需要进行数据准备，首先创建学生表，课程表，班级表，学生选课表(中间表)

create table if not exists class(
	cid int primary key ,
	cname varchar(20),
	cnum int 
);

create table if not exists student(
	sid int primary key ,
	sname varchar(20),
	sex varchar(10),
	birthday date,
	cno int,
	foreign key (cno) references class(cid)
);

create table if not exists course(
	cid int primary key ,
	cname varchar(20)
);


create table if not exists select_course(
	scid int(10) primary key,
	sno int(20) ,
	cno int(20) ,
	score int(20),
	foreign key (sno) references student(sid),
	foreign key (cno) references course(cid)
);

向各表中分别插入一些数据，具体如下：
 

insert into class VALUES(1,'01班',20) ;
insert into class VALUES(2,'02班',30) ;
insert into class VALUES(3,'03班',32) ;
insert into class VALUES(4,'01班',41) ;

insert into student VALUES(1,'张三','男','1990-9-1',1) ;
insert into student VALUES(2,'李四','女','1990-2-1',1) ;
insert into student VALUES(3,'王五','男','1990-3-1',1) ;
insert into student VALUES(4,'赵六','男','1990-4-1',2) ;
insert into student VALUES(5,'田七','男','1990-4-1',2) ;
insert into student VALUES(6,'张五','女','1990-5-1',2) ;
insert into student VALUES(7,'张老七','女','1995-5-1',3) ;
insert into student VALUES(8,'王老四','女','1992-9-1',3) ;
insert into student VALUES(9,'李老六','男','1995-9-1',3) ;

insert into course VALUES(1,'Java') ;
insert into course VALUES(2,'python') ;
insert into course VALUES(3,'c++') ;

insert into select_course VALUES(1,1,1,85) ;
insert into select_course VALUES(2,1,3,72) ;
insert into select_course VALUES(3,2,2,82) ;
insert into select_course VALUES(4,2,3,65) ;
insert into select_course VALUES(5,3,1,71) ;
insert into select_course VALUES(6,3,2,75) ;
insert into select_course VALUES(7,3,3,68) ;
insert into select_course VALUES(8,4,1,72) ;
insert into select_course VALUES(9,4,2,64) ;
insert into select_course VALUES(10,5,2,91) ;
insert into select_course VALUES(11,5,3,82) ;
insert into select_course VALUES(12,6,1,74) ;
insert into select_course VALUES(13,6,2,78) ;
insert into select_course VALUES(14,7,2,73) ;
insert into select_course VALUES(15,7,3,72) ;
insert into select_course VALUES(16,8,1,65) ;
insert into select_course VALUES(17,8,2,80) ;
insert into select_course VALUES(18,9,1,81) ;
insert into select_course VALUES(19,9,2,85) ;

下面的查询包括交叉连接，内连接和外连接：

left join （左连接，左外连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

right join （右连接，右外连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

select * from class cross join student;
select * from class c inner join student s on c.cid = s.cno;
SELECT * FROM class c,student s WHERE c.cid = s.cno;
SELECT * FROM class c LEFT OUTER JOIN student s ON c.cid = s.cno;
select * from class c right outer join student s on c.cid = s.cno;

下面看一下子查询，主要是带in,exists,all,any的查询，具体如下：

select * from class where cid in (SELECT cno FROM student WHERE birthday > '1991-01-01');
select * from class where exists (SELECT cno FROM student WHERE birthday > '1991-01-01');
SELECT * FROM class WHERE cid > ANY (SELECT cno FROM student );
SELECT * FROM class WHERE cid > ALL (SELECT cno FROM student)

10、事务的处理

事务：指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么全都成功，要么全都失败。

事务有如下四个基本性质：

原子性：事务的不可分割，组成事务的各个逻辑单元不可分割。

一致性：事务执行的前后，数据完整性保持一致。

隔离性：事务执行不应该受到其他事务的干扰。

持久性：事务一旦结束，数据就持久化到数据库中。

隔离性：一个事务的执行，不应该受到其他事务的干扰。

如果不考虑隔离性（一个事务执行受到其他的事务的干扰），引发一些安全问题，主要体现在读取数据上：

1. 脏读：一个事务读到了另一个事务未提交的数据，导致查询结果不一致。
2. 不可重复读：事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。
3. 虚读/幻读：一个事务读到了另一个事务已经提交的insert的数据，导致多次查询结果不一致。

为了解决安全性问题，一般通过设置事务的隔离级别实现，具体如下：

1. read uncommitted  ：脏读，不可重复读，虚读都有可能发生
2. read committed      ：避免脏读。但是不可重复读和虚读是有可能发生
3. repeatable read      ：避免脏读和不可重复读，但是虚读有可能发生。
4. serializable             ：避免脏读，不可重复读，虚读。