MySQL基础（二）- MySql单表&约束&事务

炎影
147 阅读8分钟

DQL操作单表

CREATE DATABASE db2 CHARACTER SET utf8;

排序

语法结构

-- select 字段名 from 表名 [where 字段名 = 值] order by 字段名称 [ASC/DESC]

--  升序排序 (默认升序)
--  降序排序
单列排序
-- 单列排序 按照某一个字段进行排序
-- 使用salary 字段 对emp表进行排序
SELECT * FROM emp ORDER BY salary; -- 默认升序
SELECT * FROM emp ORDER BY salary DESC; -- 降序排序
组合排序
-- 组合排序 同时对多个字段进行排序
-- 在薪资的排序基础上,再去使用 id字段进行排序
SELECT * FROM emp ORDER BY salary DESC ,eid DESC;

-- 组合排序的特点: 如果第一个字段 值相同,就按照第二个字段进行排序.

聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向 查询，它是对某一列的值进行计算，然后返回一个单一的值(另外聚合函数会忽略null空值。)；

语法结构

SELECT 聚合函数(字段名) FROM 表名;
聚合函数作用
count(字段)统计指定列不为NULL的记录行数
sum(字段)计算指定列的数值和 求和操作
max(字段)计算指定列的最大值 求最大值
min(字段)计算指定列的最小值 求最小值
avg(字段)计算指定列的平均值 求平均值

查询员工的总数

SELECT COUNT(*) FROM emp; 
SELECT COUNT(1) FROM emp;
SELECT COUNT(eid) FROM emp;

注意: 不要使用带空值的列 进行 count

| dept_name |
|   null    |

-- count函数 在统计的时候回忽略空值
-- 注意 不要使用带空值的列 进行 count
SELECT COUNT(dept_name) FROM emp;

查看员工总薪水、最高薪水、最小薪水、薪水的平均值

SELECT 
    SUM(salary) AS '总薪水',
    MAX(salary) '最高薪水',
    MIN(salary) '最小薪水',
    AVG(salary) '平均薪水'
FROM emp

查询薪水大于4000员工的个数

SELECT COUNT(*) FROM emp WHERE salary > 4000;

查询部门为'教学部'的所有员工的个数

SELECT COUNT(*) FROM emp WHERE dept_name = '教学部';

查询部门为'市场部'所有员工的平均薪水

SELECT AVG(salary) FROM emp WHERE dept_name = '市场部';

分组

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

语法格式

SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

通过性别字段 进行分组 (错误写法)

-- 按照性别进行分组操作
SELECT * FROM emp GROUP BY sex; -- 注意 这样写没有意义

注意:

分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作.
* 查询其他字段没有意义

通过性别字段 进行分组,求各组的平均薪资

SELECT sex, AVG(salary) FROM emp GROUP BY sex;

查询所有部门信息

SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;

查询每个部门的平均薪资

SELECT dept_name,AVG(salary) 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;

查询平均薪资大于6000的部门.

-- 1. 首先要分组求平均薪资
-- 2. 求出 平均薪资大于 6000的部门

-- 在分组之后 进行条件过滤 使用:  having 判断条件

SELECT 
	dept_name,
	AVG(salary)
FROM emp 
WHERE dept_name IS NOT NULL GROUP BY dept_name 
HAVING	AVG(salary) > 6000;

where 与 having的区别

/*
    where 与 having的区别
    where 
            1.在分组前进行过滤
            2.where后面不能跟 聚合函数
    having
            1.是在分组后进行条件过滤
            2.having 后面可以写 聚合函数

*/

limit关键字

limit 关键字的作用

  • limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)

  • limit 语法是 MySql的方言,用来完成分页

语法结构

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

参数说明

limit offset , length;

offset 起始行数, 从0开始记数, 如果省略 则默认为 0.

length 返回的行数 (要查询几条数据)

查询emp表中的前 5条数据

SELECT * FROM emp LIMIT 0,5;
SELECT * FROM emp LIMIT 5;

查询emp表中 从第4条开始,查询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; -- 第三页 3-1=2 2*3=6

limit 分页操作, 分页公式

-- 分页公式 起始行数 = (当前页码 - 1) * 每页显示条数

SQL约束

约束的作用: 对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中

常见的约束

约束名约束关键字
主键primary key
唯一unique
非空not null
外键foreign key

主键约束

特点: 不可重复 唯一 非空

作用: 用来表示数据库中的每一条记录

添加主键约束

语法格式

字段名 字段类型 primary key

方式1 创建一个带有主键的表

CREATE TABLE emp2(
    eid INT PRIMARY KEY,
    ename VARCHAR(20),
    sex CHAR(1)
);

DESC emp2;

方式2 创建

CREATE TABLE emp2(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1),
    PRIMARY KEY(eid) -- 指定eid为主键
);

方式3 创建表之后 再添加主键

CREATE TABLE emp2(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1)
);

通过DDL语句 添加主键约束 (创建的时候不指定主键,然后通过 DDL语句进行设置)

ALTER TABLE emp2 ADD PRIMARY KEY(eid);

DESC 查看表结构

-- 查看表的详细信息
DESC emp2;

image.png

测试主键的唯一性 非空性

# 正常插入一条数据
INSERT INTO emp2 VALUES(1,'宋江','男');

# 插入一条数据,主键为空
-- Column 'eid' cannot be null 主键不能为空
INSERT INTO emp2 VALUES(NULL,'李逵','男');

# 插入一条数据,主键为 1
-- Duplicate entry '1' for key 'PRIMARY' 主键不能重复
INSERT INTO emp2 VALUES(1,'孙二娘','女');

哪些字段可以作为主键 ?

通常针对业务去设计主键,每张表都设计一个主键id

主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复

比如 身份证就可以作为主键.

删除主键约束
-- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;
主键的自增

注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值.

关键字: AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

创建主键自增的表
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('李四','男');
INSERT INTO emp2 VALUES(NULL,'翠花','女');
INSERT INTO emp2 VALUES(NULL,'艳秋','女');

image.png




修改主键自增的起始值

默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

-- 创建主键自增的表,自定义自增其实值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;
-- 插入数据,观察主键的起始值
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

image.png




DELETE和TRUNCATE对自增长的影响
  • 删除表中所有数据有两种方式
清空表数据的方式特点
DELETE只是删除表中所有数据,对自增没有影响
TRUNCATEtruncate 是将整个表删除掉,然后创建一个新的表自增的主键,重新从 1开始

delete 方式删除所有数据

-- 目前最后的主键值是 101
SELECT * FROM emp2;
-- delete 删除表中数据,对自增没有影响
DELETE FROM emp2;
-- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

image.png

truncate删除 表中数据

-- 使用 truncate 删除表中所有数据,
TRUNCATE TABLE emp2;
-- 插入数据 查看主键
INSERT INTO emp2(ename,sex) VALUES('张百万','男');
INSERT INTO emp2(ename,sex) VALUES('艳秋','女');

image.png

非空约束

非空约束的特点: 某一列不予许为空

语法格式

字段名 字段类型 not null

为 ename 字段添加非空约束

# 非空约束
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 添加非空约束, ename字段不能为空
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);

image.png

唯一约束

唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )

语法格式

字段名 字段类型 unique

添加唯一约束

#创建emp3表 为ename 字段添加唯一约束
CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);

测试唯一约束

-- 测试唯一约束 添加一条数据
INSERT INTO emp3 (ename,sex) VALUES('张百万','男');
-- 添加一条 ename重复的 数据
-- Duplicate entry '张百万' for key 'ename' ename不能重复
INSERT INTO emp3 (ename,sex) VALUES('张百万','女');

image.png

主键约束与唯一约束的区别:

  1. 主键约束 唯一且不能够为空
  2. 唯一约束,唯一 但是可以为空
  3. 一个表中只能有一个主键 , 但是可以有多个唯一约束
avatar
文章
阅读
粉丝