MySQL多表查询和事务安全
目录
一、DQL语句
1.条件查询
准备数据
CREATE TABLE student3 (
id INT,
NAME VARCHAR(20),
age INT,
sex VARCHAR(5),
address VARCHAR(100),
math INT,
english INT
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭
州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳
岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
(1)比较运算符
大于 > 小于 < 小于等于 <=
等于 = 不等于<> 、!=
①查询math分数大于80分的学生
SELECT * FROM student3 WHERE math>80;
②查询english分数小于或等于80分的学生
SELECT * FROM student3 WHERE english<=80
③查询age等于20岁的学生
SELECT * FROM student3 WHERE age=20
④查询age不等于20岁的学生
SELECT * FROM student3 WHERE age!=20
(2)逻辑运算符
and:多个条件同时满足
or:多个条件满足其中一个
not(!)不满足
①查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student3 WHERE age>35 AND sex='男'
②查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student3 WHERE age>35 OR sex='男'
In关键字: select 字段名 from 表名 where 字段 in(数据1,数据2)
in里面的每个数据会作为一次条件,只要满足条件就会显示
In列表的值类型必须一致或兼容
In列表里的值不能使用模糊查询
③查询id是1或3或5的学生
SELECT * FROM student3 WHERE id IN(1,3,5)
④查询id不是1或3或5的学生
SELECT * FROM student3 WHERE id NOT IN(1,3,5)
(3)范围
BETWEEN 值1 AND 值2
①查询english成绩大于等于75,且小于等于90的学生
SELECT * FROM student3 WHERE english BETWEEN 75 AND 90
(4)模糊查询
LIKE表示模糊查询。
SELECT * FROM 表名 WHERE 字段名 LIKE ‘通配符字符串’
通配符字符串有两个:
%:表示0个或多个字符
_:表示一个字符
①查询姓马的学生
SELECT * FROM student3 WHERE NAME LIKE '马%'
②查询姓名中包含'德'字的学生
SELECT * FROM student3 WHERE NAME LIKE'%德%'
③查询姓马,且姓名有三个字的学生
SELECT * FROM student3 WHERE NAME LIKE'马__'
(5)排序
ORDER BY
ASC:升序
DESC:降序
①单列排序
查询所有数据,使用年龄降序排序
SELECT * FROM student3 ORDER BY age DESC
②组合排序
查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序
SELECT * FROM student3 ORDER BY age DESC,age ASC
(6)聚合函数
count():统计指定列记录数,NULL的不统计
sum():计算指定列的数值和,如果不是数值类型,结果为0
max():计算指定列的最大值
min():计算指定列的最小值
avg():计算指定列的平均值
①查询学生总数
SELECT COUNT(*) FROM student3
②查询年龄大于40的总数
SELECT COUNT(*) FROM student3 WHERE age>40
③查询数学成绩总分
SELECT SUM(math) FROM student3
④查询数学成绩平均分
SELECT AVG(math) FROM student3
⑤查询数学成绩最高分
SELECT MAX(math) FROM student3
⑥查询数学成绩最低分
SELECT MIN(math) FROM student3
(7)分组
GROUP BY
SELECT 字段1,字段2... FROM 表名
GROUP BY 分组字段 [HAVING 条件];
①按性别分组
SELECT sex FROM student3 GROUP BY sex
②查询男女各多少人
SELECT sex,COUNT(*) FROM student3 GROUP BY sex
③查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT sex,COUNT(*) FROM student3 WHERE age>25 GROUP BY sex
④查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex,COUNT(*) FROM student3 WHERE age>25 GROUP BY sex HAVING COUNT(*)>2
having与where的区别:
①having是在分组后对数据进行过滤,可以使用聚合函数
②where是在分组前对数据进行过滤,不可以使用聚合函数
(8)limit语句
限制查询记录的条数
LIMIT offset,length
offset是指偏移量,默认为0
length是指需要显示的总记录数
①查询学生表中数据,从第三条开始显示,显示6条
SELECT * FROM student3 LIMIT 2,6
二、数据库的约束
1.主键(PRIMARY KEY)
(1)主键的作用
唯一标识一条记录
(2)主键的特点
①唯一
②不能包含NULL值
(3)创建主键
①在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY
②在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
(4)删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
(5)主键自增
字段名 字段类型 PRIMARY KEY AUTO_INCREMENT
DELETE和TRUNCATE的区别:
①DELETE 删除表中的数据,但不重置AUTO_INCREMENT的值。
②TRUNCATE 摧毁表,重建表,AUTO_INCREMENT重置为1。
2.唯一(UNIQUE)
字段名 字段类型 UNIQUE
3.非空(NOT NULL)
字段名 字段类型 NOT NULL
4.默认(DEFAULT)
字段名 字段类型 DEFAULT 默认值
5.外键(FOREIGN KEY)
(1)新建表时增加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
(2)已有表增加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
(3)删除外键
ALTER TABLE 从表 drop foreign key 外键名称;
(4)外键的级联
在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作。
ON UPDATE CASCADE:级联更新
ON DELETE CASCADE:级联删除
三、多表查询
1.多表查询的分类
2.内连接
(1)隐式内连接
看不到JOIN 关键字,条件使用WHERE 指定 SELECT 字段名 FROM 左表, 右表 WHERE 条件;
(2)显示内连接
使用INNER JOIN ... ON 语句, 可以省略INNER SELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件;
3.外连接
(1)左外连接
使用LEFT OUTER JOIN ... ON,OUTER 可以省略
在内连接的基础上保证左表的数据全部显示
(2)右外连接
使用RIGHT OUTER JOIN ... ON , OUTER 可以省略
在内连接的基础上保证右表的数据全部显示
4.子查询
(1)子查询的结果是一个值
(2)子查询的结果是单列多行
(3)子查询的结果是多列多行
总结:子查询结果只要是单列,肯定在WHERE 后面作为条件
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
子查询结果只要是多列,肯定在FROM 后面作为表
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
四、事务安全
1.操作事务
(1)手动提交事务
start transaction:开启事务
commit:提交事务
rollback:回滚事务
(2)自动提交事务
2.事务原理
事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)。
3.回滚点
(1)作用
可以供后续操作返回到该位置,而不是返回所有操作,这个点称为回滚点。
(2)设置回滚点语法
savepoint 回滚点名字;
(3)回到回滚点语法
rollback to 回滚点名字;
4.事务的四大特性
5.事务的隔离级别
事务在操作时的理想状态:多个事务之间互不影响,如果隔离级别设置不当就可能引发并发访问问题。