一、查询
限定查询
SELECT 列名 FROM 表名 LIMIT 起始行下标,查询行数
查询前五行记录
--限定行查询,查询前 5条记录
SELECT *FROM student LIMIT 5;
查询范围记录
#查询表中从第四条开始,查询5行,
#起始行是从0开始代表了第一行,第二个参数代表的是从指定行开始查询几条记录
SELECT * FROM studentLIMIT 3,5;
二、查询总结
SQL语句编写顺序
SELECT 列名
FROM 表名
WHERE 条件
GROUP BY 分组
HAVING 过滤条件
LIMIT 起始行,总条数
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 ='王五')
四、表间映射关系
一对一
数据库中存在两张表,其中一张表的主键是另外一张表的外键,即两张表的主键关联
外键所在的表叫从表,另外一张表叫主表
添加数据的时候需要先添加主表数据,后添加从表数据
删除数据的时候需要先删除从表数据,后删除主表数据
一对多
在两个表中,一张是多方表,一张是一方表,通常设置外键在多方表中,
需要单独设置一个字段来作为外键,外键和一方表的主键进行关联。
外键所在的表叫从表,外键关联的表叫主表
增加数据的时候需要先增加主表数据,后增加从表数据
删除数据时先删除从表的数据,再删除主表的数据
-- 一对多
#创建部门表
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;
多对多
有两张表,还需要一张中间表,中间表用来存其他两张表的外键
多对多关系中,中间表是从表,其他两个表是主表
每一张表与中间表之间都构成一个一对多关系
增加数据的时候需要先增加主表,后增加中间表数据
删除先删除从表也就是中间表数据,在删除主表数据
五、表连接查询
通过一定的*连接条件*将*多个表*中数据查询出来
SELECT 列名
FROM 表 1连接方式 表2
ON 连接条件
#查询所有有班级的学生信息
SELECT FROM grade INNER JOIN student
ON grade.gid'= student.'gid';
内连接查询 (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 ='男';
左外连接(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
右外连接(RIGHT JOIN ON)
右外连接:以右表为基表,返回右表中所有记录以及左表中符合条件的记录,
如果左表中没有匹配的数据是用null填充。
语法:
select 列名1,列名2.....(列来自于多张表)
from A right join B on A.id = Bid
[where 查询条件]
六、事务
概念
事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成,
在同一个事务当中,所有的SQL语句都成功执行时,整个事务成功,
有一个SQL语句执行失败,整个事务都执行失败。
commit 提交
rollback 回滚
事务的分类
自动提交事务:默认为自动事务,当执行DML语句时,事务是自动提交的
手动提交事务:如果事务手动提交,做DML操作时,事务不会自动提交,此时数据处于临界状态,数据易失性。
查看事务命令
SHOW VARIABLES LIKE %autocommit%;
开启手动事务命令
SET SESSION autocommit = off/0;
事务的原理
数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行结果都会缓存在回滚段中,只有当事务中所有SQL语句均正常结束(commit),才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败,整个事务将回滚 (rollback) 。
事务的特性ACID
Atomicity(原子性)
表示一个事务内的所有操作是一个整体,要么全部成功,要么全部失败
Consistency(一致性)
表示一个事务内有一个操作失败时,所有的更改过的数据都必须回滚到修改前状态
lsolation(隔离性)
并发性。多个事务之间相互独立,互不干扰。
Durability(持久性)
持久性事务完成之后,它对于系统的影响是永久性
的
七、视图
概念
视图,虚拟表,从一个表或多个表中查询出来的表,作用和真实表一样,包含一系列带有行和列的数据。视图中,用户可以使用SELECT语句查询数据,也可以使用INSERT,UPDATE,DELETE修改记录,视图可以使用户操作方便,并保障数据库系统安全。
特点
优点
简单化,数据所见即所得
安全性,用户只能查询或修改他们所能见到的数据逻辑独立性,可以屏蔽真实表结构变化带来的影响
缺点
性能相对较差,简单的查询也会变得稍显复杂
修改不方便,特变是复杂的聚合视图基本无法修改。
视图的创建
语法: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';
视图的修改
方式一:CREATE OR REPLACE VIEW 视图名 AS 查询语
方式二:ALTER VIEW 视图名 AS 查询语句
删除视图
DROP VIEW 视图名
注意:删除视图不会影响原表