mysql查询、函数、约束、事务（没有废话，全是例子）

生夏夏夏

已于 2023-08-19 16:11:54 修改

阅读量145

点赞数

文章标签： mysql 数据库 sql

于 2023-06-04 15:23:24 首次发布

本文链接：https://blog.csdn.net/qq_52589631/article/details/131032597

版权

mysql查询

普通查询

SELECT  * FROM t_blog; # 查询所有字段
SELECT blogId,blogContent,createTime from t_blog; # 查询单个字段
SELECT blogContent as "博客内容" from t_blog; # 起别名，as可以省略
SELECT DISTINCT blogContent from t_blog;  # 使用distinct去重

条件查询

#条件查询
# 语法： select 字段列表 from 表名 where 条件;
SELECT * from t_blog;
-- 查询userid为3的博客内容
SELECT * from t_blog where userId=3;
-- 查询userid大于3的博客内容
SELECT * from t_blog where userId>3;
-- 查询没有图片的博客内容
SELECT * from t_blog where coverImage is null;
-- 查询有图片的博客内容
SELECT * from t_blog where coverImage is not null;
-- 查询userid为3到5之间的博客内容
SELECT * from t_blog where userId>=3 && userId<=5;
SELECT * from t_blog where userId BETWEEN 3 AND 5;
-- 查询userid为3或5或7的博客内容】、
SELECT * from t_blog where userId in (3,5,7);
-- 模糊匹配like
-- 匹配标题有2个字符的
SELECT * from t_blog where blogTitle LIKE '__';
-- 匹配以哈结尾的 %表示匹配任意字符
SELECT * from t_blog where blogTitle LIKE '%哈';

-- 聚合函数 将一列数据作为一个整体,进行纵向计算,作用于某一列
-- 常见聚合函数 count max min avg sum 
-- 统计博客数量
SELECT COUNT(*) from t_blog;
SELECT COUNT(blogId) from t_blog;
-- 统计最多博客数量某个用户
SELECT userId,COUNT(*) AS blog_count 
from t_blog
GROUP BY userId
ORDER BY blog_count DESC;
SELECT MAX(userId) from t_blog;
-- 统计最少博客数量某个用户的
SELECT MIN(userId) from t_blog;
SELECT * FROM t_blog WHERE userId=1;
-- 统计用户的平均博客数量
SELECT userId,AVG(blog_count) as avg_blog_count from (
SELECT COUNT(*) as blog_count ,userId
from t_blog
GROUP BY userId
)as sub_query GROUP BY userId;

顺序查询

SELECT * FROM `t_blog`
-- 排序查询
-- order by 其中ASC表示升序 DESC表示降序,多字段排序,只有当第一个字段的值相同时,才会根据第二个字段进行排序
-- 根据userId进行排序
SELECT * from t_blog ORDER BY userId ASC;
-- 根据创建时间进行排序
SELECT * from t_blog ORDER BY createTime DESC;
-- 根据userId进行排序,如果userId相同,根据创建时间进行排序
SELECT * from t_blog ORDER BY userId ASC, createTime DESC;

分页查询

SELECT * FROM `t_blog`
-- 分页查询
-- LIMIT 起始索引,查询记录数
-- 起始索引=(查询页码-1) * 每页显示记录数
SELECT * FROM t_blog LIMIT 0,5; # 第一页
SELECT * FROM t_blog LIMIT 5,5; # 第二页

分组查询

SELECT * FROM `t_blog`
-- 分组查询
-- where 和 having 的区别
-- where 是在分组之前进行过滤,不满足where条件,不参与分组,而having是分组之后对结果进行过滤
-- where不能对聚合函数进行判断,而having可以
-- 根据userId进行分组
-- 统计每个用户的博客数量
SELECT userId,count(*) from t_blog GROUP BY userId;
-- 统计每个用户的平均创建时间
SELECT userId,AVG(createTime) from t_blog GROUP BY userId;
-- 统计userid大于2,用户博客数量等于4的userId有哪些
SELECT userId,COUNT(*) as blog_count from t_blog where userId >2 GROUP BY userId HAVING blog_count =4;

用户管理

-- 用户管理
SELECT * FROM `user`
-- 创建用户shengxia 只能在当前主机localhost访问，密码是123456
CREATE user 'shengxia'@'localhost' IDENTIFIED by '123456';
-- 创建用户xia 可以在任意主机访问，密码是123456
CREATE user 'xia'@'%' IDENTIFIED by '123456';
-- 修改用户shengxia 的密码为12345678  myql 5.7版本可用
SET PASSWORD FOR 'shengxia'@'localhost' = PASSWORD('12345678');
-- mysql8.0版本可用
ALTER user 'shengxia'@'localhost' WITH mysql_native_password by '12345678';
-- 删除用户
DROP user 'xia'@'%';

权限控制

-- 权限控制  新建用户后，能登录mysql，但是不能访问其它数据库，是因为没有权限
-- 查询某个用户的权限
SHOW GRANTS for 'shengxia'@'localhost';
-- 授予某个用户权限
GRANT all ON  bus.* to 'shengxia'@'localhost';
-- 撤销权限
REVOKE ALL ON bus.* FROM 'shengxia'@'localhost';

执行顺序

在这里插入图片描述

mysql函数

字符串函数

-- CONCAT
SELECT CONCAT('Hello',' World');
-- upper和lower
SELECT UPPER('shengxia');
SELECT LOWER('SHENGXIA');
-- LPAD 
SELECT LPAD('hello',7,'-');
-- RPAD
SELECT RPAD('hello',7,'-');
-- trim 
SELECT TRIM(' This is test ');
-- SUBSTRING() 索引从1开始
SELECT SUBSTRING('This is test',1,4);

数值函数

在这里插入图片描述

-- 数值函数
-- ceil 向上取整
SELECT CEIL(1.1); -- =>2
-- floor 向下取整
SELECT FLOOR(1.8); -- =>1
-- mod 取余
SELECT MOD(7,4); -- =>3
-- rand 随机生成0-1的数
SELECT RAND();
-- round 四舍五入
SELECT ROUND(1.27,1); -- =>1.3
-- 案例：生成一个6位数的随机验证码
-- 生成一个随机数，将其扩大
SELECT RAND()*1000000;
-- 去除小数部分
SELECT ROUND(RAND()*1000000,0); -- => 会产生一个5位数
-- 如果产生5位数，用0补齐
SELECT LPAD(ROUND(RAND()*1000000,0),5,0);

日期函数

在这里插入图片描述

-- 日期函数
-- CURDATE()
SELECT CURDATE();
-- CURTIME()
SELECT CURTIME();
-- NOW()
SELECT NOW();
-- YEAR、MONTH、DAY
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
-- DATE_ADD()
SELECT DATE_ADD(NOW(),INTERVAL 70 YEAR);
-- DATEDIFF()
SELECT DATEDIFF('2022-1-1','2024-2-2');
-- 案例 查询所有博客的创建了多长时间，并根据博客时间进行升序排序
SELECT userId,DATEDIFF(CURTIME(),createTime) as 'bolgdays' FROM t_blog ORDER BY bolgdays ASC;

流程函数

在这里插入图片描述

-- 流程函数
-- if 
SELECT IF(TRUE,"返回true","否则返回false");
-- ifnull 
SELECT IFNULL("不为null则返回这个","为空则返回这个");
-- case when then else end 
SELECT
userId,
(CASE blogStatus
	WHEN 1 THEN
		'博客已经发布'
	ELSE
		'博客没有发布'
  END 
) as '博客状态'
from t_blog;

mysql约束

在这里插入图片描述

普通约束

-- 约束
CREATE TABLE student(
	id INT PRIMARY KEY auto_increment COMMENT '主键',
	name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
	age INT  COMMENT '年龄',
	status CHAR(1) DEFAULT '1' COMMENT '状态',
	gender CHAR(1) COMMENT '性别'
) COMMENT '学生表';
SELECT * FROM student;
-- DELIMITER // mysql5.7不支持check函数，创建一个触发器
CREATE TRIGGER trig_student_age
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
  IF NEW.age <= 0 OR NEW.age > 120 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在 1 到 120 岁之间';
  END IF;
END 
DELIMITER ;

-- 插入语句进行测试
insert into student (name,age,status,gender) VALUES ('tom1',18,'1','男'),('tom2',18,'0','男');
-- 测试都比较简单，主要看一下年龄的测试
insert into student (name,age,status,gender) VALUES ('tom3',-1,'0','男');  -- 1644 - 年龄必须在 1 到 120 岁之间
insert into student (name,age,status,gender) VALUES ('tom4',111,'1','男');

外键约束

在这里插入图片描述

student表

在这里插入图片描述

-- 外键约束
SELECT * from student;
-- 新建一个class表
CREATE TABLE `class` (
  `id` varchar(5) NOT NULL COMMENT '班级编号',
  `type` varchar(10) DEFAULT NULL COMMENT '班级类别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这里插入图片描述

添加约束和删除约束

ALTER TABLE student ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class(id);  -- 添加外键约束成功
ALTER TABLE student DROP FOREIGN KEY fk_class_id; -- 删除外键约束成功

mysql多表查询

-- 多表查询 创建一个部门和员工表，并且建立外键关系
CREATE TABLE employee (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  department_id INT NOT NULL,
  FOREIGN KEY (department_id) REFERENCES department(id)
);
CREATE TABLE department (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
SELECT * from employee;
SELECT * from department;

-- 插入数据
INSERT INTO department (name) VALUES ('Sales');
INSERT INTO department (name) VALUES ('Marketing');
INSERT INTO department (name) VALUES ('Finance');

INSERT INTO employee (name, age, department_id) VALUES ('John', 30, 1);
INSERT INTO employee (name, age, department_id) VALUES ('Alice', 25, 1);
INSERT INTO employee (name, age, department_id) VALUES ('Bob', 35, 2);
INSERT INTO employee (name, age, department_id) VALUES ('Charlie', 28, 3);
-- 多表查询
-- 当员工的department_id和department的id相等时，就可以查出员工所在的部门
SELECT * from employee,department WHERE department_id=department.id;

查询分类

在这里插入图片描述

内连接

在这里插入图片描述

-- 隐式内连接
-- 查询每一个员工的姓名及关联的部门的名称
SELECT employee.name , department.name from employee,department where employee.department_id=department.id;
--  可以为表指定别名，简化sql编写
SELECT e.name , d.name from employee e,department d where e.department_id=d.id;
-- 显示内连接
SELECT employee.name ,department.name from employee join department on employee.department_id=department.id;

外连接

在这里插入图片描述

-- 外连接
-- 查询员工表对应的数据，和对应的部门信息
SELECT e.*,d.name from employee e LEFT JOIN department d on e.department_id=d.id; 
-- 查询部门表对应的数据，和对应的员工信息
SELECT d.*,e.* from employee e RIGHT JOIN department d on e.department_id=d.id;

自连接

在这里插入图片描述

-- 自连接
-- 创建员工表
CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    manager_id INT
);
-- 创建部门表
CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);
-- 插入数据
INSERT INTO employee VALUES 
    (1, 'Alice', 1, NULL),
    (2, 'Bob', 2, 1),
    (3, 'Charlie', 3, 2),
    (4, 'David', 2, 1),
    (5, 'Eve', 1, 3),
    (6, 'Frank', 2, 1),
    (7, 'Grace', 3, 2),
    (8, 'Henry', 2, 1);
		
INSERT INTO department VALUES 
    (1, 'Sales', 5),
    (2, 'Engineering', 2),
    (3, 'Marketing', 7);
-- 查看数据 
SELECT * from employee;
SELECT * from department;
-- 查询每个员工的上级领导
SELECT e.name AS '员工姓名', m.name AS '领导姓名'
FROM employee e 
LEFT JOIN employee m ON e.manager_id = m.id;
-- 查询每个部门的领导
SELECT d.name AS '部门', m.name AS '领导姓名' 
FROM department d 
LEFT JOIN employee m ON d.manager_id = m.id;

联合查询

在这里插入图片描述

SELECT * from t_blog;
-- 查询userid为3的博客
SELECT * from t_blog WHERE userId=3;
-- UNION ALL 合并不去重
UNION -- 合并去重
-- 查询发布状态为0的博客
SELECT * from t_blog WHERE blogStatus=0;

mysql事务

在这里插入图片描述

事务操作

在这里插入图片描述

-- 事务
-- 创建用户转账表
CREATE TABLE bank_account (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    balance DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id)
);
-- 插入数据
INSERT INTO bank_account (name, balance)
VALUES ('张三', 1000.00),
       ('李四', 2000.00),
       ('王五', 3000.00),
       ('赵六', 4000.00);
-- 开启转账事务 张三向李四转账1000元
SELECT * FROM bank_account;
START TRANSACTION;
UPDATE bank_account SET balance = balance - 1000 WHERE name = "张三";
hello -- 设置出错才能看到回滚现象
UPDATE bank_account SET balance = balance + 1000 WHERE name = "李四";
-- 提交
COMMIT;
-- 回滚
ROLLBACK;

事务四大特性

事务的四大特性是ACID，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性（Atomicity）：事务是一个原子操作，要么全部执行成功，要么全部执行失败。如果在事务执行过程中发生错误或者异常，事务会回滚到执行前的状态，使得数据一致性得到保障。
一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。在事务执行的过程中，如果有任何错误发生，事务会回滚到执行前的状态，保证数据的一致性。
隔离性（Isolation）：事务的执行是相互隔离的，即一个事务在执行过程中不会被其他事务干扰。在多个事务同时执行的情况下，事务之间应该互相隔离，防止数据出现异常。
持久性（Durability）：事务执行成功后，对数据库的修改应该永久保存。如果数据库发生故障，事务提交后的数据也应该能够恢复。

并发事务出现的问题

并发事务是指多个事务同时对数据库进行读写操作的情况。并发事务可能会出现以下问题：

假设有两个用户，分别是A和B，他们要同时对一个银行账户进行转账操作。现在假设账户中有100元钱，A要向B转账50元，而B要向A转账20元。这时候如果两个用户同时进行转账操作，就会出现并发事务的情况。

在不同的隔离级别下，可能会出现以下问题：

脏读（Dirty Read）：一个事务读取了另一个事务尚未提交的数据。如果尚未提交的事务最终回滚，那么读取到的数据就是无效的。
不可重复读（Non-repeatable Read）：一个事务在同一个查询中多次读取同一行数据，但在此期间其他事务修改了该行数据，导致每次读取的结果不一致。
幻读（Phantom Read）：一个事务在同一个查询中多次读取符合某个条件的数据，但在此期间其他事务插入或删除了符合该条件的数据，导致每次读取的结果不一致。
丢失更新（Lost Update）：两个事务同时读取同一行数据，然后分别修改该行数据，最后一个提交的事务会覆盖前一个事务的修改，导致前一个事务的修改丢失。
死锁（Deadlock）：两个或多个事务相互等待对方释放资源，导致所有事务无法继续执行。这种情况下，必须通过中断其中一个事务或回滚其中一个事务来解决死锁。

# 首先，我们创建一个名为"bank_account"的表，用于存储用户的银行账户信息：

CREATE TABLE bank_account (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);
# 然后，我们向表中插入一些数据：

INSERT INTO bank_account (id, name, balance) VALUES
       ('张三', 1000.00),
       ('李四', 2000.00),
       ('王五', 3000.00),
       ('赵六', 4000.00);
# 接下来，我们将打开两个不同的数据库连接，模拟两个并发事务。

# 事务1：

BEGIN;
UPDATE bank_account SET balance = balance - 500 WHERE id = 1;
-- 等待5秒钟
COMMIT;
# 事务2：

BEGIN;
UPDATE bank_account SET balance = balance + 500 WHERE id = 1;
COMMIT;
# 这里我们使用了两个事务来更新同一个账户的余额，事务1将余额减少500元，而事务2将余额增加500元。

# 现在，我们来演示不同的隔离级别对并发事务的影响。
# 将隔离级别设置为"READ UNCOMMITTED"
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 在"READ UNCOMMITTED"级别下，如果事务2进行了更新操作但没有提交，事务1会读取到事务2更新的数据，出现脏读。

# 将隔离级别设置为"READ COMMITTED"，这是大多数数据库系统的默认隔离级别，解决脏读问题：

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 在"READ COMMITTED"隔离级别下，事务2在更新前会等待事务1提交，因此事务2会一直等待，直到事务1提交，但是会出现不可重复读的问题。

# 接下来，我们将隔离级别设置为"REPEATABLE READ"，这是MySQL默认的隔离级别，解决重复读的问题：

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# 在"REPEATABLE READ"隔离级别下，事务2在更新前会先读取一次账户余额，然后等待事务1提交。如果事务1提交前没有再次更新账户余额，事务2将会更新成功。否则，事务2将会失败。

# 最后，我们将隔离级别设置为"SERIALIZABLE"，这是最严格的隔离级别：

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
# 在"SERIALIZABLE"隔离级别下，事务2在更新前会等待事务1提交，并且任何其他事务都不能对账户余额进行更新，直到事务2提交。