MySQL学习笔记2

一、查询

1. 限定查询

SELECT 列名 FROM 表名 LIMIT 起始行下标，查询行数

查询前五行记录

--限定行查询，查询前 5条记录
SELECT *FROM student LIMIT 5;

查询范围记录

#查询表中从第四条开始，查询5行，
#起始行是从0开始代表了第一行，第二个参数代表的是从指定行开始查询几条记录
SELECT * FROM studentLIMIT 3，5;

二、查询总结

1. SQL语句编写顺序

SELECT 列名

FROM 表名

WHERE 条件

GROUP BY 分组

HAVING 过滤条件

LIMIT 起始行，总条数

2. SQL语句执行顺序

FROM：指定数据来源表

WHERE：对查询数据做第一次过滤

GROUP BY：分组

HAVING:对分组后的数据第二次过滤

SELECT:查询各字段的值

ORDER BY：排序

LIMIT:限定查询结果

三、子查询

一个查询语句中内嵌另一个完整的查询语句，被嵌套的语句称为子查询或者内查询

外面的语句称为主查询或者父查询

语法：SELECT 列名 FROM 表名 WHERE （子查询结果）

特点：

• 子查询必须写在小括号中

• 子查询优先执行，父查询需要得到子查询的结果

• 子查询结果

• 单行子查询：子查询结果只有一个值，搭配的符号 = > <>=

• 多行子查询：子查询结果多个值，搭配的符号in /not in/any/all

#查询和王五年龄相同的学生信息
SELECT * FROM student WHERE age =(SELECT age FROM student WHERE sname ='王五');
#4.查询比王五年龄大的同学的信息
SELECT * FROM student WHERE age >(SELECT age FROM studentWHERE sname ='王五')

四、表间映射关系

1. 一对一

数据库中存在两张表，其中一张表的主键是另外一张表的外键，即两张表的主键关联

外键所在的表叫从表，另外一张表叫主表

添加数据的时候需要先添加主表数据，后添加从表数据

删除数据的时候需要先删除从表数据，后删除主表数据

2. 一对多

• 在两个表中，一张是多方表，一张是一方表，通常设置外键在多方表中，

需要单独设置一个字段来作为外键，外键和一方表的主键进行关联。

• 外键所在的表叫从表，外键关联的表叫主表

• 增加数据的时候需要先增加主表数据，后增加从表数据

• 删除数据时先删除从表的数据，再删除主表的数据

-- 一对多
#创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY,
dname VARCHAR(30)，
dcount INT
#创建员工表
CREATE TABLE emp(
eid INT PRIMARY KEY
ename VARCHAR(20)
age INT，
hiredate DATE
did INT，#部门编号，与部门表主键关联
FOREIGN KEY(did) REFERENCES dept(did)
);
#创建外键约束
ALTER TABLE eMP ADD CONSTRAINT FOREIGN KEY (did) REFERENCES
dept(did);
#删除表，一定先删除从表，再删除主表
DROP TABLE emp;
DROP TABLE dept;

3. 多对多

• 有两张表，还需要一张中间表，中间表用来存其他两张表的外键

• 多对多关系中，中间表是从表，其他两个表是主表

• 每一张表与中间表之间都构成一个一对多关系

• 增加数据的时候需要先增加主表，后增加中间表数据

• 删除先删除从表也就是中间表数据，在删除主表数据

五、表连接查询

通过一定的*连接条件*将*多个表*中数据查询出来

SELECT 列名

FROM 表 1连接方式 表2

ON 连接条件

#查询所有有班级的学生信息 
SELECT  FROM grade INNER JOIN student
ON grade.gid'= student.'gid';

1. 内连接查询 (INNER JOIN ON)

语法:

select 列名1，列名2.....(列来自于多张表)

from A inner join B

on A.id = B.id

[where 查询条件]

#查询所有男同学的姓名，年龄，性别及所在班级名称
SELECT sname,age,sex,gname
FROM grade g INNER JOIN student s
ON g.gid= s.gid
WHERE sex ='男';

2. 左外连接（LEFT JOIN ON）

左外连接:以左表为基表，返回左表中所有记录以及右表中符合条件的记录，

如果右表中没有匹配的数据是用null填充。

语法:

select 列名1，列名2.....(列来自于多张表)

from A left join B on A.id = B.id

[where 查询条件]

#查询学号，姓名，所在班级名称，没有学生的班级也要显示，学生信息用null填充
SELECT sid,sname,gname
FROM grade g LEFT JOIN student s
ON g. gid= s.gid

3. 右外连接（RIGHT JOIN ON）

右外连接:以右表为基表，返回右表中所有记录以及左表中符合条件的记录，

如果左表中没有匹配的数据是用null填充。

语法:

select 列名1，列名2.....(列来自于多张表)

from A right join B on A.id = Bid

[where 查询条件]

六、事务

1. 概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，

在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，

有一个SQL语句执行失败，整个事务都执行失败。

commit 提交

rollback 回滚

2. 事务的分类

• 自动提交事务：默认为自动事务，当执行DML语句时，事务是自动提交的

• 手动提交事务：如果事务手动提交，做DML操作时，事务不会自动提交，此时数据处于临界状态，数据易失性。

查看事务命令

SHOW VARIABLES LIKE %autocommit%;

开启手动事务命令

SET SESSION autocommit = off/0;

3. 事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束(commit)，才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚 (rollback) 。

4. 事务的特性ACID

• Atomicity(原子性)

表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

• Consistency(一致性)

表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

• lsolation(隔离性)

并发性。多个事务之间相互独立，互不干扰。

• Durability(持久性)

持久性事务完成之后，它对于系统的影响是永久性

的

七、视图

1. 概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

2. 特点

优点

• 简单化，数据所见即所得

• 安全性，用户只能查询或修改他们所能见到的数据逻辑独立性，可以屏蔽真实表结构变化带来的影响

缺点

• 性能相对较差，简单的查询也会变得稍显复杂

• 修改不方便，特变是复杂的聚合视图基本无法修改。

3. 视图的创建

语法:CREATE VIEW 视图名 AS 查询数据源表语句;

视图中不存储真实数据，只保存一条查询语句。视图中数据来源于原始表。

视图中建立要对表查询基础上的，所以创建视图时只能写查询语句，不能写增删改。

对视图的操作主要是查询，增删改也可以，但存在局限性，一般不使用。

#基于学生表创建视图
CREATE VIEW v_stu
AS
SELECT sid,sname,age,sex,phone
FROM student;
-- 查询视图
SELECT *FROM v_stu;
SELECT * FROM v_stu WHERE sid ='sy001';

4. 视图的修改

方式一:CREATE OR REPLACE VIEW 视图名 AS 查询语

方式二:ALTER VIEW 视图名 AS 查询语句

5. 删除视图

DROP VIEW 视图名

注意：删除视图不会影响原表