DAY1DROP DATABASE IF EXISTS mysql_function_course;CREATE DATABASE mysql_function_courseDEFAULT CHARACTER SET utf8mb4COLLATE utf8mb4_unicode_ci;USE mysql_function_course;-- -- 1. users 用户表-- CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID,username VARCHAR(50) NOT NULL COMMENT 用户名,first_name VARCHAR(50) NOT NULL COMMENT 名,last_name VARCHAR(50) NOT NULL COMMENT 姓,email VARCHAR(100) NOT NULL COMMENT 邮箱,register_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间,total_spent DECIMAL(10,2) DEFAULT 0.00 COMMENT 总消费金额) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;INSERT INTO users (username, first_name, last_name, email, register_date, total_spent)VALUES(john_doe, john, DOE, JOHNexample.com, 2025-01-15 10:30:00, 28000.00),(alice_smith, alice, Smith, AliceTEST.com, 2025-03-20 14:20:00, 5000.00),(bob_wang, bob, Wang, BOB.WANGcompany.net, 2025-06-01 09:00:00, 12000.00),(zhao_liu, zhao, Liu, ZHAOLIUtest.org, 2024-11-10 16:45:00, 300.00),(emma_chen, emma, Chen, EmmaChenemail.com, 2026-01-05 11:30:00, 35000.00),(mike_zhang, mike, Zhang, MIKE.ZHANGwork.com, 2025-08-15 08:15:00, 1500.00),(sara_lin, sara, Lin, sara.lincompany.com, 2026-02-28 13:40:00, 8500.00),(tom_wu, tom, Wu, tom.wutest.com, 2024-09-01 10:00:00, 6800.00);-- -- 2. products 商品表-- CREATE TABLE products (product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 商品ID,product_name VARCHAR(100) NOT NULL COMMENT 商品名称,category VARCHAR(50) NOT NULL COMMENT 商品分类,description TEXT COMMENT 商品描述,price DECIMAL(10,2) NOT NULL COMMENT 原价,discount_rate DECIMAL(3,2) DEFAULT 0.00 COMMENT 折扣率,stock_quantity INT NOT NULL DEFAULT 0 COMMENT 库存数量) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表;INSERT INTO products (product_name, category, description, price, discount_rate,stock_quantity) VALUES(笔记本电脑 Pro, 电子产品, 高性能商务笔记本电脑16GB内存, 5999.99, 0.15, 45),(无线蓝牙鼠标, 电子产品, 人体工学设计静音按键蓝牙5.0, 89.50, 0.10, 120),(机械键盘 RGB, 电子产品, 青轴机械键盘RGB背光, 399.00, 0.20, 78),(4K 显示器 27寸, 电子产品, IPS面板4K分辨率HDR400, 2499.00, 0.12, 32),(电竞耳机, 电子产品, 7.1环绕声降噪麦克风, 299.00, 0.15, 56),(手机支架, 配件, 铝合金材质可调节角度, 29.90, 0.00, 200),(充电宝 20000mAh, 配件, 快充支持双USB输出, 159.00, 0.05, 88),(USB-C 扩展坞, 配件, 7合1多功能扩展坞, 189.00, 0.08, 43),(智能手表 Pro, 穿戴设备, AMOLED屏幕心率监测, 1299.00, 0.10, 25),(运动手环, 穿戴设备, IP68防水血氧检测, 199.00, 0.20, 67);-- -- 3. orders 订单表-- CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID,user_id INT NOT NULL COMMENT 用户ID,order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间,shipped_date DATETIME DEFAULT NULL COMMENT 发货时间,total_amount DECIMAL(10,2) NOT NULL COMMENT 订单总金额,status VARCHAR(20) NOT NULL DEFAULT 待支付 COMMENT 订单状态,FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;INSERT INTO orders (user_id, order_date, shipped_date, total_amount, status) VALUES(1, 2026-08-01 10:00:00, 2026-08-03 14:30:00, 6100.00, 已送达),(1, 2026-07-20 09:30:00, 2026-07-21 11:20:00, 3299.00, 已送达),(1, 2026-08-08 14:20:00, NULL, 4599.00, 待发货),(2, 2026-08-05 09:15:00, 2026-08-07 08:30:00, 89.50, 已送达),(2, 2026-05-15 11:30:00, NULL, 399.00, 待发货),(3, 2026-08-09 08:00:00, NULL, 2499.00, 待付款),(3, 2026-07-28 16:45:00, 2026-07-30 10:20:00, 8999.00, 已送达),(4, 2026-01-20 10:30:00, 2026-01-22 14:00:00, 300.00, 已送达),(5, 2026-08-06 08:45:00, 2026-08-08 10:00:00, 1299.00, 已送达),(5, 2026-08-10 11:30:00, NULL, 3200.00, 待发货);-- -- 4. order_items 订单明细表-- CREATE TABLE order_items (item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 明细ID,order_id INT NOT NULL COMMENT 订单ID,product_id INT NOT NULL COMMENT 商品ID,quantity INT NOT NULL COMMENT 购买数量,unit_price DECIMAL(10,2) NOT NULL COMMENT 成交单价,subtotal DECIMAL(10,2) NOT NULL COMMENT 小计金额,FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;INSERT INTO order_items (order_id, product_id, quantity, unit_price, subtotal) VALUES(1, 1, 1, 5999.99, 5999.99),(1, 2, 1, 89.50, 89.50),(2, 3, 1, 399.00, 399.00),(2, 1, 1, 2900.00, 2900.00),(3, 4, 1, 2499.00, 2499.00),(3, 5, 1, 299.00, 299.00),(3, 1, 1, 1800.00, 1800.00),(4, 2, 1, 89.50, 89.50),(5, 3, 1, 399.00, 399.00),(6, 4, 1, 2499.00, 2499.00),(7, 1, 1, 8999.00, 8999.00),(8, 6, 10, 29.90, 299.00),(9, 9, 1, 1299.00, 1299.00),(10, 4, 1, 2499.00, 2499.00),(10, 8, 1, 189.00, 189.00);-- 一、字符串函数#1、concat 连接多个字段select concat(hello,--,mysql);#2、concat_ws(分隔符字段1字段2...)按照指定分隔符连接多个字段select concat_ws(-,product_id,product_name,category) from products;#3、length()返回字符串长度select length(product_name) from products where product_id 1;#4、char_length(str) 返回字符个数select CHAR_LENGTH(category),category from products where product_id 1;#5、upper() lower() 全部转大写、小写select upper(HrtRw),lower(HrtRw);#6、trim() 去掉前后空格 ltrim rtrimselect trim( mysql );#7、substring(str ,pos,len) len 是位置 从迫使位置开始第几个往后截取len个字符 省略len 截取到末尾。select SUBSTRING(我是中国人,2,2);#8、left(str,len) right(str,len)select left(我是中国人,2);select left(我是中国人,3);#9、instr(str,substr) substr 在str中第一次出现的位置select instr(中国我是中国人,中国);#10、replace(str,old,new) 右边替换左边select replace(HElloll,l,L);-- 二、数学函数#1、round(小数n) 四舍五入n为小数select round(98.78351,3)#2、ceil() 向上取整比当前数大的最小整数 floor()向下取整 比当前数小的最大整数select ceil(12.56),floor(-3.14);#3、truncate(小数n) 截取n位小数 不四舍五入截取select truncate(12.5678,3);#4、mod(m,n) m%n 求余数select mod(3,10);#5、 abs() 求绝对值 去符号select abs(-100);#6、rand() 获取0~1之间的随机小数 前闭后开[01#获取0~10之间的随机整数select round(rand()*10,0);select FLOOR(rand()*10);#floor(rand()*m)n mend-start1 nstart#获取5~10的随机整数select FLOOR(rand()*6)5;#7、greatest() 一组数的最大值select GREATEST(12,56,3);#8、least() 一组数的最小值select least(12,56,3);select greatest(stock_quantity,120) from products;-- 三、日期时间函数#1、now()获取当前系统的日期时间select now();# 2026-08-11 16:05:21# 2、 curdate()获取当前系统的日期# 3、curtime()获取当前系统的时间select curdate(),curtime();#4、year(日期对象) month(date)select year(now()),month(now()),day(now());select hour(now()),minute(now()),second(now());#5、date_format(date,format) 将日期按照指定格式输出为字符串select DATE_FORMAT(NOW(),%Y年%m月%d日 %h-%i-%s);#6、datediff(d1,d2) d1-d2的天数select datediff(now(),2026-8-15);#-4#7、date_add(date,days) date几天后的日期select date_add(now(),interval 10 day);select date_add(now(),interval -10 day);#8、unix_timestamp() 获取当前日期时间戳select UNIX_TIMESTAMP();#在orders 表中统计今年八月份所有的订单SELECT * FROM ordersWHERE YEAR(order_date) YEAR(NOW())AND MONTH(order_date) 8;#在orders表中统计今年8月份所有的订单数SELECT count(*) FROM ordersWHERE YEAR(order_date) YEAR(NOW())AND MONTH(order_date) MONTH(NOW());#在orders表中统计每一年当前月份(现在月份)所有的订单数 查询结果中满足条件的行数select year(order_date),month(current_date()),count(*) from orderswhere MONTH(order_date) MONTH(NOW())GROUP BY year(order_date),month(current_date());SELECT YEAR(order_date),COUNT(*) FROM ordersWHERE MONTH(order_date) MONTH(NOW())GROUP BY YEAR(order_date)ORDER BY YEAR(order_date);#查询2026年所有的订单 日期可以当作字符串、数字、日期格式来用select *from orders where year(order_date) 2026;select *from orders where order_date between 2026-01-01and 2026-12-31:23:59:59;select *from orders where substring(oeder_date,1,4)2026;select *from orders where oeder_date like 2026;-- 【了解】 流程控制语句 选择结构#if(条件表达式值1满足值2不满足)#产品库存量大于零的显示为有货否则为缺货select product_name, stock_quantity, IF(stock_quantity0,有货,缺货) as 库存状态 from#ifnull 如果值为nul1显示默认值否则显示本身-- IFNULL(表达式1, 表达式2)-- 如果表达式1不为NULL返回表达式1-- 如果表达式1为NULL返回表达式2SELECT IFNULL(NULL, 默认值); -- 默认值SELECT IFNULL(有值, 默认值); -- 有值SELECT IFNULL(1 2, 0); -- 3SELECT IFNULL(NULL 5, 0); -- 0#查询订单信息发货日期为空的显示为未发货select product_name, stock_quantity,IF(stock_quantity0, 有货,缺货)as库存状态from products;#nullif-- ULLIF(表达式1, 表达式2)-- 如果表达式1 表达式2返回NULL-- 如果表达式1 ! 表达式2返回表达式1SELECT NULLIF(1, 1); -- NULL相等SELECT NULLIF(1, 2); -- 1不相等SELECT NULLIF(abc, abc); -- NULLSELECT NULLIF(abc, xyz); -- abc#case when/*-- 语法1简单CASE等值判断CASE 表达式WHEN 值1 THEN 结果1WHEN 值2 THEN 结果2...ELSE 默认结果END-- 语法2搜索CASE条件判断CASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2...ELSE 默认结果END*/DAY2-- 事务、视图#视图 view/*概念每个视图是一张虚拟表他不存储数据本身而是保存了一条dql查询语句当你查询视图时只是执行了保存的这条查询语句将查询结果返回。特点逻辑存在物理上不会保存不占用数据库空间 可以向操作表一样来使用理解某张表的部分数据某些列或满足条件的某些数据多张表的数据连接查询结果避免重复编写sql语句将相关信息整合一张虚拟表中用来查询相当于对表数据进行了保护给数据加权限只能看到部分数据view 可以进行增删改查哪些操作呢一般视图只做查询用如果对视图进行了修改操作原始表也会发生变化视图作用1、降低维护成本提高维护性2、提升安全性视图可以隐藏某些字段例如用户表中的密码、手机号、身份证等信息*/create table if not exists emp(id int auto_increment primary key,name varchar(20) not null,age int,sex char(2),did int,create_date datetime,address varchar(255));insert into emp values(null,tom,22,男,1,now(),北京沙河),(null,lili,12,女,2,now(),北京昌平),(null,李白,22,男,1,now(),北京西城),(null,杜甫,25,男,3,now(),湖南),(null,蔡文姬,19,女,1,now(),安徽);#如何创建视图 类比创建表create table 表名 as select ... from 旧表create view v_emp1 as select id ,name,address from emp;create view v_emp2 as select id ,name,address from emp where name like __;#修改原始表update emp set name 黧黑 where id 4;#使用视图select *from v_emp2;#修改视图从视图修改表数据update v_emp1 set nametomcat where id 2;select * from emp;#集中显示多张表的相关信息create table dept (did int AUTO_INCREMENT PRIMARY key ,dname varchar(30) not null,location varchar(200));insert into dept (dname ,location) values(武装部,三层),(外交部,1层1-12),(后勤部,3层3-11);create view v_emp_dept as select id ,name,dname,location from emp inner join dept on emp.diddept.did ;select *from v_emp_dept;#删除视图drop view if exists v_emp1;-- 事务/*| 特性 | 英文 | 含义 | 类比 || :------ | :---------- | :--------- | :------------------------ || **原子性** | Atomicity | 要么全做要么全不做 | 转账扣款和加款是一个整体 || **一致性** | Consistency | 操作前后数据始终合法 | 转账前后两人总余额不变 || **隔离性** | Isolation | 多个事务互不干扰 | 你转账时别人查余额不会看到扣了还没加的中间态 || **持久性** | Durability | 提交后数据永久保存 | 转账成功后即使服务器炸了记录也不会丢 |*//*一组DML操作insert/update/delete组合到一起执行要么都执行成功 要么都不执行失败只有创建表的时候指定引擎为innodb才支持事务默认引擎特点事务一旦开启必须手动提交关闭commit都执行/rollback都不执行平常写的DML语句MySQL默认自动提交永久写入磁盘例子转账业务、订单业务生成一个订单减少一个库存事务的四大特性ACID【面试题必考题目】1、原子性Atomicity每个事务不可再分包含的所有操作一起执行要么都成功要么都失败2、一致性Consistency:事务开始前和完成后数据应该是保持一致的例如转账业务中转账前后张三和李四的账户总金额不变3、隔离性Isolation :多个用户并发访问数据库时数据库为每个用户开启一个事务。多个并发宏观上像同时事务相互独立互不干扰。4、持久性(Durability ):事务一旦提交它对于数据库中数据的改变是永久性的(即便时数据库系统遇到故障问题也不会丢失提交事务的操作)不在服务器上写在磁盘内无法回复到提交前状态*/-- 使用事务#平时未开启事务每一行增删改的sql命令都是一个事务#执行完自动提交持久化操作#如何关闭自动提交 使用begin 或 start transaction开启事务自动关闭#事务一旦提交无法回滚恢复到上一个提交点#提交有两种方式rollback错误的提交/commit成功的提交create table account (id int PRIMARY KEY,name varchar(20) not null ,balance decimal(6,2));insert into account values(1,张三,2000);rollback ;#提交(错误的提交/回滚)SELECT *from account;#1、开始事务关闭自动提交start transaction;begin ;#2、执行sql操作insert into account values(2,李四,5000);select *from account;#3、提交事务持久性commit;#4、回滚事务撤销所有更改rollback;begin;update account set balance balance-1000 where id1;update account set balance balance1000 where id2;rollback ;-- 隔离性的四个级别-- WALWrite-Ahead Logging原则-- 先写日志再写磁盘。/*脏读(Dirty Read)脏读指一个事务读取了另一个事务尚未提交的修改数据如果该事务随后回滚则读取的数据是无效的。例如事务A修改了一条记录但未提交事务B读取了这条数据随后事务A回滚事务B读取的数据就成了脏数据”可能导致错误操作或业务逻辑异常。脏读的重点在于读取了尚未提交的数据数据可能最终不存在。幻读 (Phantom Read)幻读发生在同一事务中当执行相同查询条件的操作时结果集出现了新增或删除的记录。例如事务A查询某范围内的记录事务B在该范围内插入新记录并提交事务A再次查询时发现多了一条记录这种现象就称为幻读。幻读的重点在于新增或删除操作导致查询结果集变化而不是单条数据的修改。*/-- 隔离性的四个级别 mvcc多版本并发控制锁/* 问题脏读脏数据、不可重复读、幻读 、并发性能read uncommittedread committedoracle的默认隔离等级repeatable read mysql的默认隔离等级serializable从低到高读未提交读提交可重复读串行化理论上是4个。实践中可能会使用三个:读提交、可重复读、串行化。读未提交等级太低等于没有隔离。可以读取到其他事务未提交的数据读提交能够读取到其他事务提交后的数据。Oracle数据库的默认隔离级别可重复读只要当前事务不结束(只要还在当前事务中)读取同一行数据这一行数据永远都一样。mysql的默认隔离级别串行化隔离的最高等级 不支持并发 效率极低 一般不使用。*/#演示四个隔离级别及出现的问题-- 查看当前的隔离级别select transaction_isolation;-- 设置全局隔离级别-- SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ*/#演示四个隔离级别及出现的问题-- 查看当前的隔离级别-- 1、查看当前会话的隔离级别-- 会话多次请求询问与响应回答--多轮对话select transaction_isolation;select session.transaction_isolation;select global.transaction_isolation;-- 设置全局隔离级别-- SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ-- SET session TRANSACTION ISOLATION LEVEL REPEATABLE READ/*并发事务产生的数据问题现象 三类事务为了解决这些问题1、脏读并发时指的是一个事务读取到了另一个事务未提交的数据即读取到了另一个事务过程中的脏数据。此情况下如果另一个事务回滚或修改这个数据你读取到的数据不准确2、不可重复读指的是在同一个事务中多次两次读取同一行数据期间被其他已提交的事务修改导致两次读取到的同一行数据结果不一致。3、幻读指在事务执行过程中前后两次相同的查询条件得到的结果不一致可能变多或变少其实是其他事务执行并提交了插入或删除命令*/