DQL 操作单表
排序查询
/*
排序
使用order by子句
语法结构: select 字段名 from 表名 [where 字段名=值] order by 字段名称 [ASC/DESC]
ASC 升序排序(默认升序)
DESC 降序排序
*/
-- 单列排序 按照某一个字段进行排序
-- 使用salary 字段对emp表进行排序
SELECT * FROM emp ORDER BY salary;-- 默认升序
SELECT * FROM emp ORDER BY salary DESC;
-- 组合排序 同时对多个字段进行排序
-- 在salary的排序基础上,再去使用id字段进行排序
SELECT * FROM emp ORDER BY salary DESC , eid DESC;
-- 组合排序特点:如果第一个字段 值相同,就按照第二个字段进行排序
聚合函数
/*
聚合函数
作用:将一列数据作为一个整体,进行纵向的计算
常用的聚合函数:
count(字段) 统计记录数
sum(字段) 求和操作
max(字段) 求最大值
min(字段) 求最小值
avg(字段) 求平均值
语法格式
select 聚合函数(字段名) from 表名 [where 条件]
*/
#1 查询员工的总数
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;
SELECT COUNT(eid) FROM emp;
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
SELECT
SUM(salary) '总薪水',
MAX(salary) '最高薪水',
MIN(salary) AS '最小薪水',
AVG(salary) '薪水平均值'
FROM emp;
#3 查询薪水大于4000员工的个数
SELECT COUNT(*) FROM emp WHERE salary > 4000;
#4 查询部门为'教学部'的所有员工的个数
SELECT COUNT(*) FROM emp WHERE dept_name = '教学部';
#5 查询部门为'市场部'的所有员工的平均薪水
SELECT AVG(salary) FROM emp WHERE dept_name = '市场部';
注意COUNT函数 在统计时会忽略空值
所以不要用带空值的列 进行COUNT
结果
- #1 查询员工的总数
- #2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
- #3 查询薪水大于4000员工的个数
分组查询
分组目的是为了统计操作(进行统计操作才有意义),所以一般分组会和聚合函数一起使用
另外 查询的时候 要查询分组字段
特别注意分组的格式
/*
分组查询 使用 group by 子句
语法格式
select 分组字段/聚合函数 from 表名 group by 分组字段
*/
-- 通过性别字段进行分组,求各组的平均薪资
SELECT sex, AVG(salary) FROM emp GROUP BY sex;
# 查询所有部门信息
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;
# 查询每个部门的平均薪资
SELECT dept_name AS '部门名称',AVG(salary) AS '平均薪资' FROM emp GROUP BY dept_name;
# 查询每个部门的平均薪资,部门名称不能为null
SELECT
dept_name AS '部门名称',
AVG(salary) AS '部门平均薪资'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;
结果
having的使用
查询平均薪资大于6000的部门
# 查询平均薪资大于6000的部门
-- 1.首先分组求出平均薪资
-- 2.求出 平均薪资大于6000的部门
-- 在分组之后 进行条件过滤 使用: having 判断条件
SELECT
dept_name AS '部门名称',
AVG(salary) AS '部门平均薪资'
FROM emp
WHERE dept_name IS NOT NULL GROUP BY dept_name
HAVING AVG(salary>6000);
WHERE与HAVING的区别
WHERE:
- 在分组前进行过滤
- WHERE后不能跟聚合函数
HAVING:
- HAVING在分组后进行条件过滤
- having后面可以写聚合函数
limit关键字
/*
limit 通过limit 指定要查询的数据条数 行数
语法格式
select 字段 from 表名 limit offset,length;
参数说明:
offset:起始行数 默认从0开始计数
length: 返回得行数(要查询几条行数)
*/
# 查询emp表的前5条数据
SELECT * FROM emp LIMIT 0, 5;
SELECT * FROM emp LIMIT 5;
# 查询emp表中 从从四条开始,查询6条
SELECT * FROM emp LIMIT 3, 6;
-- limit 分页操作,每页显示3条
SELECT * FROM emp LIMIT 0, 3;-- 第一页
SELECT * FROM emp LIMIT 3, 3;-- 第二页
SELECT * FROM emp LIMIT 6, 3;-- 第三页
SQL约束
约束是指对数据进行一定的限制,保证数据的完整性、有效性、正确性
常见的约束:
- 主键约束 primary key
- 唯一约束 unique
- 非空约束 not null
- 外键约束 foreign key
主键约束primary key
/*
主键约束
特点 不可重复 唯一 非空
作用 用来表示数据库中的每一条记录
语法格式
字段名 字段类型 primary key
*/
-- 方式一 创建一个带有主键的表
CREATE TABLE emp2 (
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);
-- 方式二
DROP TABLE emp2;
CREATE TABLE emp2(
eid INT,
ename VARCHAR(20),
sex CHAR(1),
PRIMARY KEY(eid)-- 指定eid为主键
);
-- 方式三 创建表之后再添加主键
DROP TABLE emp2;
CREATE TABLE emp2(
eid INT,
ename VARCHAR(20),
sex CHAR(1)
);
-- 添加主键元素
ALTER TABLE emp2 ADD PRIMARY KEY(eid);
-- 删除主键
ALTER TABLE emp2 DROP PRIMARY KEY;
这样我们的eid就唯一且不为空
主键自增
/*
主键自增
关键字: auto_increment 主键的自动增长(字段类型必须是INT)
*/
-- 创建主键自增的表
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
);
-- 插入数据,观察主键的起始值
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');
-- 修改自增的起始值
DROP TABLE emp2;
-- 重新创建自增主键的表,自定义自增的起始位置
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');
DELETE和TRUNCATE对自增的影响
delete 删除表中所有数据,是将表中数据逐条删除
truncate 删除表中所有数据,将整个表删除,再创建一个结构相同的表
DELETE
-- delete 方式删除所有数据
DELETE FROM emp2;
-- 插入数据,观察主键的起始值
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');
可以见到eid还在沿着之前的表从101开始自增,不是从1开始
说明delete删除对自增没有影响
TRUNCATE
-- truncate 方式删除所有数据
TRUNCATE TABLE emp2;
-- 插入数据,观察主键的起始值
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');
TRUNCATE会刷新掉自增
非空约束
特点:某一列不允许为空
语法格式:
字段名 字段类型 not null
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);
唯一约束
特点:表中的某一列不能够重复(对null值 不做唯一判断)
语法格式:
字段名 字段类型 unique
CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);
主键约束和唯一约束的区别
- 主键约束 是唯一且不能为空
- 唯一约束 可以为空
- 一个表中只能有一个主键,但可以有多个唯一约束
默认值
特点:用来指定某一列的默认值
语法格式:
字段名 字段类型 default 默认值
-- 创建emp4表,指定sex默认值为 nv
CREATE TABLE emp4(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1) DEFAULT '女'
);
INSERT INTO emp4(eid,ename) VALUES(1,'张三');
数据库的事务
什么是事务
事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败
比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中 间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失
又比如:想保存三张表到数据库里,在一个事务里,如果程序中间哪里异常,回滚之后都不会执行,就不出现三个表只有一张或者两张有数据的情况。但是回滚还要看回滚的时机还有回滚方式,特别是在分布式系统里边
回滚
即在事务运行的过程中发生了某种故障,事务不能继续执行,系统将事务中对数据库的所有已完成的操作全部撤销,滚回到事务开始时的状态。(在提交之前执行)
MySql事务操作
- 手动提交事务
- 开启事务 start transaction; 或者 begin;
- 提交事务 commit;
- 回滚事务 rollback;
对于上表进行操作:
成功案例
START TRANSACTION;
UPDATE account SET money = money -500 WHERE NAME ='tom';
UPDATE account SET money = money +500 WHERE NAME ='jack';
COMMIT;
失败案例
如果事务中,有某条sql语句执行时报错了,我们没有手动的commit,那整个事务会自动回滚
开启事务,插入数据, 不去提交事务 直接关闭窗口,发生回滚操作,数据没有改变
- 自动提交事务
MySql默认的提交方式 自动提交事务
每执行一条DML(增删改查)语句,都是一个单独的事务
Mysql 事务的四大特性
- 原子性:每个事务都是一个整体,不可以再拆分,事务中所有SQL要么都执行成功,要么都执行失败
- 一致性:事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前2个人的 总金额是 2000,转账后 2 个人总金额也是 2000.
- 隔离性:事务与事务之间不应该相互影响,执行时保持隔离的状态
- 持久性:一旦事务执行成功,对数据库的修改是持久的。就算关机,数据也是要保存下来的.
Mysql 事务的隔离级别
一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性