常用语句
概念:客户端CMD链接服务器,操作mysql服务器里的很多数据库里的数据
SQL语句
SQL:
structured query language 结构化查询语言,操作任意一个DBMS,
1.1: DDL data defination language 数据定义语言
-- 创建/删除库。表 CRETAE/DROP
--ALRER
--TRUNCATE
1.2: DML 数据操作语言(更新操作)
-- INSERT
-- DELETE
--UPDATE
1.3: DQL 数据查询语句
--SELECT
1.4: DCL 数据控制语言
--GRANT
--事务管理
--BEGIN COMMIT ROLLBACK
接下来让我们看一下DDL
DDL
DDL.操作库
-- 1.创建新的数据库-- 语法:CREATE DATABASE [IF NOT EXISTS] 数据库名称;CREATE DATABASE 数据库名称; -- 数据库名称一旦创建 无法更改-- mysql> CREATE DATABASE mydb;mysql> SHOW CREATE DATABASE mydb; -- 查询创建库的完整信息-- 编码格式: utf8mb4 存储中文-- 2.删除指定数据库(所有的表+全部删除)mysql> DROP DATABASE mydb;-- 3.修改指定数据库的编码格式-- mysql> ALTER DATABASE mydb CHARACTER SET utf8mb4;
操作表
-- 1.创建新的表-- 1.1 表名规范(等价于程序类名) 表名全部小写,有多个单词, _-- t_user tb_user user sys_user sys_role-- 列名1: 自定义、-- MySQL服务器数据类型CREATE TABLE [IF NOT EXISTS] 表名(列名1 数据类型 [约束],列名2 数据类型 [约束],....列名n 数据类型 [约束]);-- 需求: 使用DDL语句 创建student表 指定特定列以及列对应的数据类型-- 规则: 必不可少的3个字段 id create_time update_timeCREATE TABLE student(id INT,stu_name VARCHAR(20),gender TINYINT(1),age TINYINT(2) unsigned,score FLOAT(4,1),stu_image VARCHAR(200),create_time DATETIME,update_time DATETIME);-- 2.删除指定表mysql> DROP TABLE a;-- 删除表 表的数据也会被删除 无法回滚
-- 3.修改表结构 ALTER
-- 3.1 新增新的列
ALTER TABLE 表名 ADD 新的列名 数据类型 [约束];
mysql> ALTER TABLE student ADD money DECIMAL(4,1);
-- 3.2 删除指定的列
ALTER TABLE 表名 DROP 列名;
mysql> ALTER TABLE student DROP MONEY;
-- 3.3 修改列的数据类型
mysql> ALTER TABLE student MODIFY gender char(1);
mysql> ALTER TABLE student CHANGE gender gender varchar(2);
-- 3.4 修改列的名称
mysql> ALTER TABLE student CHANGE gender stu_gender char(2);-- 3.5 修改表名
mysql> ALTER TABLE student RENAME tb_student;
7. 数据类型
-- java程序里面提供的数据类型 -- 1.1 基本数据类型 -- 四类八种 -- 整数类型 byte short int long -- 浮点类型 float double -----> java.math.BigDecimal -- 字符类型 char -- 布尔类型 boolean -- 1.2 引用数据类型
MySQL服务器常用的数据类型:
7.1 整数类型
维护任意一个整数数据。
-- n: 限定查询时候的列宽。随意编写 int(5) 1 100 10000000
-- 1. tinyint(n) "byte/Byte"
-- tinyint(1) 列的数据 0 1 “boolean”
-- 2. int(n) "int/Integer"
-- 3. bigint(n) "long/Long"-- 要求数据不能为负数: mysql里面提供的属性 unsigned
tinyint(n) unsigned 0-255-- 场景:
-- tinyint(n) 年龄
-- int(n) 年龄 库存 id
-- bigint(n) 库存 id 时间的毫秒数
7.2 浮点类型
- 1. float(m,n) "float/Float"
-- 120.5 float(4,2)
-- 2. double(m,n) “double/Double”-- m: 规定浮点数值总长度(小数点之前+小数点之后的数字的个数总和)
-- n: 小数点之后的数字的个数总和-- 使用场景: 成绩 单价
-- 3. decimal(m,n) 实现浮点数据数据进行算术运算 “BigDecimal”
-- 使用场景: 余额 购物车总金额
7.3 字符类型
-- char String -- 1. char(n) 255个字符 -- 2. varchar(n) 65535个字符 -- n: 限定存储字符个数。 char(5) hello char(3) VS varchar(3) -- 最多存储3个字符数据。 -- 'a' char(3)-----> 存储内存还是存储3个字符。定长。’a__‘ 查询的时候 先trim 再展示 -- 'a' varchar(3)----> 存储内存还是存储1个字符 可变长度 使用场景: char(3) 性别 手机号 身份证 varchar(3) 姓名 简介 路径 ....
7.4 日期时间类型
-- java.util.Date jdk1.8- -- java.time.LocalDate '年月日' -- java.time.LocalTime '时分秒' -- java.time.LocalDateTime '年月日 时分秒' -- 1. date '年月日' "等价于java.util.Date/java.time.LocalDate" -- 2. time '时分秒' "等价于java.util.Date/java.time.LocalTime" -- 3. datetime '年月日 时分秒' "等价于java.util.Date/java.time.LocalDateTime -- 4. timestamp 2038年 不建议使用 '年月日 时分秒' -- 时间很重要
==9. DML==
数据操作语言。 INSERT UPDATE DELETE
以student表为例,实现对表数据进行增删改。
9.1 INSERT
-- 一次新增一个 -- 1.语法1 INSERT INTO 库名.表名 VALUES (数据1,....,数据n);-- 对指定表的所有的列赋值 实现新增 -- 表名有n个列,就需要再()新增n个数据 INSERT INTO student VALUES (1,'张三','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL); -- INSERT INTO student VALUES (2,'李四','男',20,100,'/user/a.jpg',SYSDATE(),NULL); -- INSERT INTO student VALUES (3,'wangwu','男',20,100,'/user/a.jpg',SYSDATE(),NULL); SELECT NOW(),SYSDATE();-- 2.语法2 INSERT INTO 库名.表名 (列名1,....,列名n) VALUES (数据1,....,数据n); -- 指定列新增新的记录 INSERT into student (id,stu_name,create_time) VALUES (4,'jim',NOW());-- 3.语法3 批处理 INSERT INTO student VALUES (11,'张三1','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL), (12,'张三2','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL), (13,'张三3','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL), (14,'张三4','男',20,100,'/user/a.jpg','2023-09-11 15:42:30',NULL); INSERT into student (id,stu_name,create_time) VALUES (41,'jim1',NOW()), (42,'jim2',NOW()), (43,'jim3',NOW()), (44,'jim4',NOW());
9.2 DELETE
- 一次删除一个 (唯一匹配 根据id删除) -- 批量删除 -- 语法1: DELETE FROM 表名; -- 清空表数据。 -- 语法2:(在满足一定条件下删除) DELETE FROM 表名 WHERE 条件1 AND/OR 条件2.... -- 条件1: 表里面的列是要参与 DELETE FROM student; -- DELETE: 底层都是遍历式删除 一行行删除 -- 优点: DELETE 属于DML语句 在开启事务前提下 数据可以回滚的 -- 缺点: 性能低 -- -- TRUNCATE TABLE student; -- TRUNCATE: 清空表记录 -- 属于DDL语句。 在开启事务前提下 数据也不可以回滚 -- 性能高。不是遍历式删除。先DROP TABLE 然后CREATE TABLE
SQL | 事务 | 操作 | 性能 | |
---|---|---|---|---|
delete from a; | DML | 与事务有关,可以回滚 | 遍历式删除 | 低 |
truncate table a; | DDL | 完全无关 | 先drop table, 再create table | 高 |
## 9.3 UPDATE
```mysql
-- 一次修改一个/行(根据id修改),修改的某些列的数据。
-- 语法1:
UPDATE 表名 SET 列名1=新的数据1,....,列名n=新的数据n;
-- 修改表里面所有的记录
-- UPDATE student SET stu_gender='女',update_time=NOW();-- 语法2:
UPDATE 表名 SET 列名1=新的数据1,....,列名n=新的数据n WHERE 条件1 AND/OR 条件2....;-- UPDATE
-- UPDATE student SET stu_gender='女',update_time=NOW();
-- UPDATE student SET stu_gender='男',update_time=NOW() WHERE stu_name='jim';-- 修改age=18 条件: score为null的数据
-- UPDATE student SET age=18 WHERE score IS NOT NULL;-- SELECT * FROM student WHERE score IS NULL;
```
10. 约束
就是一些规则。限制列的数据。
如果使用一些约束修饰列,列的数据不能随意赋值。
-- 1. 行级约束(对某个列修饰) 1.1 非空约束 NOT NULL 1.2 唯一性约束 UNQIUE 1.3 默认约束 DEFAULT -- 以上3个约束,可以在一张表里面使用多次修饰多个列。 1.4 主键约束 PRIMARY KEY -- 一张表,主键约束只能修饰一个列(主键列),只能使用1次。 -- 2. 表级约束(维护多表关系) 2.1 主键约束 PRIMARY KEY -- 位置上区分。联合主键。 2.2 外键约束 FOREIGN KEY -- 维护多表数据。
10.1 NOT NULL
非空约束。使用非空约束修饰列,列的数据不能为null。
-- 1. NOT NULL -- CREATE TABLE a( -- id INT, -- name VARCHAR(20), -- age INT -- ); -- DESC a; -- INSERT INTO a (id) VALUES (1); -- INSERT INTO a (id,name) VALUES (1,'aaa'); -- 需求: id,name必须不能为null -- 在创建表成功之后,修改指定列的约束。 -- ALTER TABLE a MODIFY id INT NOT NULL; -- CREATE TABLE b( -- id INT NOT NULL, -- 行级约束 -- name VARCHAR(20) NOT NULL, -- age INT -- );
10.1 NOT NULL
非空约束。使用非空约束修饰列,列的数据不能为null。
-- 1. NOT NULL -- CREATE TABLE a( -- id INT, -- name VARCHAR(20), -- age INT -- ); -- DESC a; -- INSERT INTO a (id) VALUES (1); -- INSERT INTO a (id,name) VALUES (1,'aaa'); -- 需求: id,name必须不能为null -- 在创建表成功之后,修改指定列的约束。 -- ALTER TABLE a MODIFY id INT NOT NULL; -- CREATE TABLE b( -- id INT NOT NULL, -- 行级约束 -- name VARCHAR(20) NOT NULL, -- age INT -- );
10.3 DEFAULT
默认约束。使用默认约束修饰列,必须给列设置==默认数据==
CREATE TABLE a( id INT NOT NULL UNIQUE, name VARCHAR(20) NOT NULL, gender CHAR(1) DEFAULT '男' ); INSERT INTO a (id,name) VALUES (2,'张三1'); SELECT * FROM a; -- 需求: 用户注册 提交数据 未设置性别 设置默认值 '男' ALTER TABLE a MODIFY gender CHAR(1) DEFAULT '男';
==10.4 PRIMARY KEY==
主键约束。行级约束/表级约束
-- 主键约束自带index(Btree)。 -- 使用主键约束修饰的列,称为"主键列"。 -- 在一张表中,主键列有且只有1个。 -- 在一张表中,只能使用1次主键约束。 -- 理论上说,任意类型的1个列都可以使用PRIMARY KEY修饰。 -- 正常开发中,一般使用的列的数据类型: int bigint varchar -- 使用PRIMARY KEY修饰列,这个列的数据"不能为null,且唯一" -- 在开发中,只使用not null+unique修饰id 理论上也可以 -- 整数类型: id的值必须要唯一。id要自增的---> auto_increment --> id 必须是主键列 -- 从表设计的3大范式: -- 第1范式: 保证列的原子性。列不可再分的原则。 -- sys_user: address '河南省郑州市高新区' 这个地址信息可以拆分的。 -- provinceCode cityCode areaCode -- 第2范式: 满足第一范式,保证每行记录唯一性。 id 必须要用“主键约束”。 -- 第3范式: 满足第2范式,避免列、数据冗余的问题。外键列的数据除外。 -- order ----> buy_num * prod_price -- id buy_num prod_price money 这样设计有问题的 1 10 5.0 10*5 -- product -- id prod_name prod_store prod_price ....
==1. auto_increment==
CREATE TABLE b( id INT PRIMARY KEY , -- 行级约束 name VARCHAR(20), age int ); -- DESC b; -- INSERT INTO b (id,name) VALUES (1,'a'); -- INSERT INTO b (id,name) VALUES (2,'a'); -- id主键列 唯一不能null -- id是整数类型: 每次累增+1 -- 使用自增的特性。----> 列必须是主键列,整数类型 ALTER TABLE b MODIFY id INT auto_increment; -- id设置了自增的特性 对与程序员 不用管id -- 自增初始值1 步长+1 -- INSERT INTO b (name) VALUES ('f'); -- 上一次的id+1 -- 获得上一次的自增的id的数据 SELECT LAST_INSERT_ID(); ALTER TABLE b auto_increment 1001; SET GLOBAL auto_increment_increment=1;
2. uuid
-- PRIMARY KEY -- CREATE TABLE a( -- id VARCHAR(255), -- name VARCHAR(20), -- age int, -- PRIMARY KEY(id) -- 表级约束 -- ); -- 保证字符数据的唯一性。 -- 获得唯一的字符串数据 -- 1. 获得当前时间戳 -- long mills = System.currentTimeMills(); -- long nanos = System.nanoTime(); -- 2. UUID -- String uuidStr = UUID.randomUUID().toString(); -- 3. mysql服务器 UUID() UUID_SHORT() -- INSERT INTO a (id,name) VALUES (UUID(),'f'); -- INSERT INTO a (id,name) VALUES (UUID_SHORT(),'f'); -- SELECT * FROM A WHERE ID='df852482-5115-11ee-b1cf-581122cc10a3'
3. 联合主键
-- 联合主键(中间表: 维护多表关系的) -- 多个列联合起来 作为一个主键列体现。 -- 必须使用表级约束 -- CREATE TABLE b( -- id VARCHAR(255), -- name VARCHAR(20), -- age int, -- PRIMARY KEY(id,name) -- ); INSERT INTO b (id,name) VALUES ('1','f'); INSERT INTO b (id,name) VALUES ('1','a'); INSERT INTO b (id,name) VALUES ('2','a');
10.5 外键约束 FOREIGN KEY
表级约束。外键约束。
-- 核心作用: 保证数据安全。(存储引擎是InnoDB) -- 弊端: 性能低。 -- 1.使用FOREIGN KEY修饰的列 称为"外键列" -- 2.一张表里面可以使用多次FOREIGN KEY修饰多个列 -- 3.外键列在的表一般称为 “从表/子表” -- 4.外键列的数据严格参照"主表/基表"的主键列的数据。 -- 5.外键列的数据类型与主表的主键列类型一致。 -- 6.外键列服务/维护多表之间的关系。-- 创建外键约束 -- 对cart表里面uid新增外键约束 保证数据的安全性 -- 创建表之后的情况下: -- ALTER TABLE cart ADD CONSTRAINT FOREIGN KEY(uid) REFERENCES sys_user(id);-- 外键约束的特性: RESTRICT -- 性能低: -- 操作主表/子表 -- 新增/查询/修改主表记录 ok 删除主表记录 子表没有关联这行记录 ok 反之: 无法删除 -- 查询/删除子表记录 ok 新增/修改可能有问题 外键列的数据要严格参照主表的数据-- 外键约束的特性: CASCADE (级联) -- 性能低: -- 操作主表/子表 -- 新增/查询/修改主表记录 ok -- 删除主表记录 先查询子表是否关联使用这行记录 有 遍历删除子表记录 再删除主表的记录 -- 查询/删除子表记录 ok 新增/修改可能有问题 外键列的数据要严格参照主表的数据-- 外键约束的特性: SET NULL(外键列可以为null) -- 性能低: -- 操作主表/子表 -- 新增/查询/修改主表记录 ok -- 删除主表记录 先查询子表是否关联使用这行记录 有 将外键列的数据都设置为null -- 查询/删除子表记录 ok 新增/修改可能有问题 外键列的数据要严格参照主表的数据
在正常的开发中,禁止使用外键约束。一切表的数据维护需要再业务层面(页面/后端代码)上解决。==外键列正常创建,这些列不新增外建约束。仅仅是一个普通列作为外键列体现。==
毕业设计,练习,无所谓的。正常的使用外键约束。
-- 查询所有的存储引擎 -- SHOW ENGINES; -- CREATE TABLE cart( -- id INT PRIMARY KEY auto_increment, -- total_money DECIMAL(20,2), -- uid INT , -- FOREIGN KEY (uid) REFERENCES sys_user(id) -- ); -- 自带索引 -- SHOW INDEX FROM cart;
==12. 表设计==
1. 用户信息表 sys_user
2. 购物车表 cart
3. 购物项表 cart_item
4. 商品表 product_info
5. 类型表 type-- 关系: 一对一 一对多
-- 一对一
-- 用户表与购物车 一个用户一个购物方
-- 购物项与商品 一个购物项项 对一个商品。 一个商品对应很多购物项。-- 一对多
-- 购物车 与 购物项 一个购物车有很多购物项
-- 类型与商品 一个类型下有很多商品 一个商品对一个类型-- 学生与老师(多对多)
12.1 一对一
用户表与购物车
CREATE TABLE `sys_user` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '用户id', `username` varchar(20) DEFAULT NULL COMMENT '用户名称', `age` tinyint unsigned DEFAULT NULL, `password` varchar(100) DEFAULT NULL COMMENT '用户加密之后密码', `phone` char(11) DEFAULT NULL, `email` varchar(30) DEFAULT NULL, `balance` decimal(20,3) DEFAULT NULL, `image` varchar(255) DEFAULT NULL COMMENT '存储服务器资源路径', `status` int DEFAULT NULL COMMENT '用户状态 0 禁用 1 可用 2 删除', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `cart` ( `id` int NOT NULL AUTO_INCREMENT, `total_money` decimal(20,2) DEFAULT NULL COMMENT '购物车金额', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 创建外键约束
-- 对cart表里面uid新增外键约束 保证数据的安全性
-- 创建表之后的情况下:
-- ALTER TABLE cart ADD CONSTRAINT FOREIGN KEY(uid) REFERENCES sys_user(id);
12.2 一对多
购物车与购物项
CREATE TABLE `cart` (
`id` int NOT NULL AUTO_INCREMENT,
`total_money` decimal(20,2) DEFAULT NULL,
`uid` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `cart_item` (
`id` int NOT NULL AUTO_INCREMENT,
`buy_num` int DEFAULT NULL,
`money` decimal(10,2) DEFAULT NULL COMMENT '小计',
`cid` int DEFAULT NULL COMMENT '外键列 严格参照cart表里面的id数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
购物项与商品
CREATE TABLE `cart` (
`id` int NOT NULL AUTO_INCREMENT,
`total_money` decimal(20,2) DEFAULT NULL,
`uid` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `cart_item` (
`id` int NOT NULL AUTO_INCREMENT,
`buy_num` int DEFAULT NULL,
`money` decimal(10,2) DEFAULT NULL COMMENT '小计',
`cid` int DEFAULT NULL COMMENT '外键列 严格参照cart表里面的id数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `cart_item` (
`id` int NOT NULL AUTO_INCREMENT,
`buy_num` int DEFAULT NULL,
`money` decimal(10,2) DEFAULT NULL COMMENT '小计',
`cid` int DEFAULT NULL COMMENT '外键列 严格参照cart表里面的id数据',
`pid` int DEFAULT NULL COMMENT '外键列 严格参照product_info里面的id的数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `product_info` (
`id` int NOT NULL AUTO_INCREMENT,
`prod_name` varchar(255) DEFAULT NULL,
`prod_price` decimal(10,2) DEFAULT NULL,
`prod_store` bigint DEFAULT NULL,
`prod_image` varchar(255) DEFAULT NULL,
`prod_desc` varchar(255) DEFAULT NULL,
`prod_status` int DEFAULT NULL COMMENT '1 在售 0 下架 ',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
类型与类型
-- 一个父类型有很多子级类型 CREATE TABLE `type` ( `id` int NOT NULL AUTO_INCREMENT, `type_name` varchar(255) DEFAULT NULL COMMENT '类型名称', `parent_type_id` int DEFAULT NULL COMMENT '外键列 数据要严格参照type表里面的id 0 一级类型', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
商品与类型
CREATE TABLE `product_info` ( `id` int NOT NULL AUTO_INCREMENT, `prod_name` varchar(255) DEFAULT NULL, `prod_price` decimal(10,2) DEFAULT NULL, `prod_store` bigint DEFAULT NULL, `prod_image` varchar(255) DEFAULT NULL, `prod_desc` varchar(255) DEFAULT NULL, `prod_status` int DEFAULT NULL COMMENT '1 在售 0 下架 ', `type_id` int DEFAULT NULL COMMENT '外键列 严格参照type的id的数据', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
12.3 多对多
-- 创建student -- 创建teacher -- 经典的使用场景: 用户 角色 菜单
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) DEFAULT NULL,
`stu_gender` char(2) DEFAULT NULL,
`age` tinyint unsigned DEFAULT NULL,
`score` float(4,1) DEFAULT NULL,
`stu_image` varchar(200) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_stu_name` (`stu_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `teacher` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `teacher_student` (
`tid` int DEFAULT NULL COMMENT '外键列 严格参照teacher表的id的数据',
`sid` int DEFAULT NULL COMMENT '外键列 严格参照student表的id的数据'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
==13. DQL==
SELECT 都是有结果。查询的结果都在一张==虚拟表==中。
1. 语法
SELECT [DISTINCT]列1,...,列n -- 这些列可能是多张表的列 FROM 表1,...,表n [WHERE 条件1 OR/AND....] -- 根据指定的条件对记录过滤 [GROUP BY 列1] -- 根据指定的列分组查询 [HAVING 条件1 OR/AND....] -- 对分组之后的记录进行过滤 [ORDER BY 列 ASC/DESC] -- 根据指定的列进行升序(默认 ASC)或者降序排序 [LIMIT ?/?,?] -- 限定查询的结果集。分页查询
2. 建库建表
CREATE TABLE stu ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) ); INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male'); INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female'); INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male'); INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female'); INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male'); INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female'); INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male'); INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female'); INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male'); INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female'); INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);CREATE TABLE emp( empno INT, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), comm decimal(7,2), deptno INT ) ; INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20); INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20); INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);CREATE TABLE dept( deptno INT, dname varchar(14), loc varchar(13) ); INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept values(20, 'RESEARCH', 'DALLAS'); INSERT INTO dept values(30, 'SALES', 'CHICAGO'); INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');CREATE TABLE `salgrade` ( `GRADE` int(11) NOT NULL DEFAULT '0' COMMENT '⼯资的等级', `LowSAL` decimal(7,2) DEFAULT NULL COMMENT '此等级的最低⼯资', `HISAL` decimal(7,2) DEFAULT NULL COMMENT '此等级的最⾼⼯资' ) ; INSERT INTO `salgrade` VALUES (1,700.00,1200.00),(2,1201.00,1400.00), (3,1401.00,2000.00),(4,2001.00,3000.00),(5,3001.00,9999.00);
3. 基础查询
-- 1. 基础查询 -- * 是一个通配符 统配的是所有的列 -- sql优化角度: 一张表里面列的数量>=10 不要使用* -- 指定列名去查询 -- SELECT sid,sname,age,gender FROM stu; -- SELECT sid,sname FROM stu;
4. WHERE
-- 过滤记录 条件查询就是在查询时给出WHERE⼦句,在WHERE⼦句中可以使⽤如下运算符及关键字: =、!=、<>不等于、<、<=、>、>=; BETWEEN…AND;是否满⾜⼀个区间范围 >= <= IN(set);条件的集合 IS NULL; AND; 连接多个条件的查询 OR;or 满⾜其中⼀个条件就可以 NOT; -- 1. 查询学生性别为女,并且年龄50的记录 -- SELECT * FROM stu WHERE (gender='female' AND age=15); -- SELECT * FROM stu WHERE (gender='female') OR age=50; -- 2、查询学号为S_1001,S_1002,S_1003的记录 -- SELECT * FROM stu WHERE sid='s_1001' OR sid='s_1003' OR sid='s_1004'; -- SELECT * FROM stu WHERE sid IN ('s_1001','s_1003','s_1004'); -- 3、查询学号不是S_1001,S_1002,S_1003的记录 -- SELECT * FROM stu WHERE sid!='s_1001' AND sid<>'s_1003' AND sid<>'s_1002'; -- SELECT * FROM stu WHERE sid NOT IN ('s_1001','s_1003','s_1004'); -- 4、查询年龄为null的记录 -- SELECT * FROM stu WHERE age IS NULL; -- SELECT * FROM stu WHERE age IS NOT NULL; -- -- 5、查询年龄在20到40之间的学生记录 -- SELECT * FROM stu WHERE age>=20 AND age<=40; -- SELECT * FROM stu WHERE age BETWEEN 20 AND 40; -- 6、查询性别非男的学生记录 -- SELECT * FROM stu WHERE gender!='male' OR gender IS NULL;
5. 模糊查询 LIKE
与where一起使用。模糊查询/搜索。
-- 任意一个字符: _ -- 任意数量的字符: % -- 1、查询姓名由5个字符构成的学生记录 -- SELECT * FROM stu WHERE sname LIKE '_____'; -- 2、查询姓名以“z”开头的学生记录 -- SELECT * FROM stu WHERE sname LIKE 'z%'; -- 3、查询姓名中第2个字符为“i”的学生记录 -- SELECT * FROM stu WHERE sname LIKE '_i%'; -- 4、查询姓名中包含“a”字符的学生记录 -- SELECT * FROM stu WHERE sname LIKE '%a%';
6. 字段控制查询
针对于某个列/某些列而言的,来做一些特殊的处理。
DISTINCT
-- DISTINCT 去除重复的行记录 -- 查询stu表里面的性别的数据 SELECT DISTINCT sname, gender FROM stu; -- 去重
IFNULL函数
-- ifnull(列名,默认的数据); -- 查询emp的信息 展示员工薪金之和(薪水+奖金) -- 列的数据如果是null,与null值做运算,结果都是null -- IFNULL(列名,默认数据) -- 列的数据为null 使用默认数据参与运算 否则还是使用自身的数据 SELECT empno,ename,sal,comm, sal+ IFNULL(comm,0) FROM emp;
AS 可以省略。不建议省略。 起别名(起一个新的列名)
-- 对复杂的表名,列名都有起别名的需求 -- 尤其后面的多表的关联查询中 建议起别名 -- 别名: 可以是中文 建议英文单词 SELECT empno,ename,sal,comm, sal+ IFNULL(comm,0) AS '薪金之和' FROM emp; SELECT empno,ename,sal,comm, sal+ IFNULL(comm,0) AS money FROM emp AS e;
7. 聚合函数
- 还可以称为组函数、分组函数 - ⽤作统计使⽤,⼜称为聚合函数或者统计函数或者组函数。 - 聚合函数是⽤来做纵向运算的函数: - COUNT(字段/列):统计指定列不为NULL的记录⾏数;⼀般使⽤count(*)统计⾏数 -- 统计表的记录数。 - MAX(字段/列):计算指定列的最⼤值,如果指定列是字符串类型,那么使⽤字符串排序运算; - MIN(字段/列):计算指定列的最⼩值,如果指定列是字符串类型,那么使⽤字符串排序运算; - SUM(字段/列):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0; - AVG(字段/列):计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0; - SUM、AVG⼀般处理数值型 - MAX、MIN、COUNT可以处理任意数据类型 分组函数都忽略了NULL值,可以和DISTINCT搭配使⽤ 注意点:组函数可以出现多个,但是不能嵌套;如果没有GROUP BY ⼦句,结果集中所有⾏数作为⼀组1、查询emp表中记录数: 2、查询emp表中有佣⾦的⼈数:注意,因为count()函数中给出的是comm列,那么只统计comm列⾮NULL的⾏数。 3、查询emp表中⽉薪⼤于2500的⼈数: 4、统计⽉薪与佣⾦之和⼤于2500元的⼈数: 5、查询有佣⾦的⼈数,以及有领导的⼈数: 6、查询所有雇员⽉薪和: 7、查询所有雇员⽉薪和,以及所有雇员佣⾦和: 8、查询所有雇员⽉薪+佣⾦和: 9、统计所有员⼯平均⼯资: 10、查询最⾼⼯资和最低⼯资 -- COUNT(列名) -- 1. 统计stu表里面所有的学生数量(表里面所有的记录数量 主键列) -- SELECT COUNT(sid) FROM stu; -- stu的记录分成一组 统计 -- SELECT COUNT(*) FROM stu; -- 2. 统计stu表里面男生/女生学生数量 -- SELECT COUNT(*) FROM stu WHERE gender='male'; -- 3. 统计emp表里面有奖金的人数 -- SELECT COUNT(comm) FROM emp; -- 4. 统计emp最高薪水,最低薪水,所有的员工薪水总和,平均薪水 -- SELECT MAX(sal) AS '最高薪水', MIN(sal), SUM(sal),AVG(sal),SUM(sal)/COUNT(*) FROM emp; -- SELECT * FROM emp WHERE sal = (SELECT MAX(sal) AS '最高薪水' FROM emp);
8. 分组查询GROUP BY
1、查询每个部⻔的部⻔编号和每个部⻔的⼯资和: 2、查询每个部⻔的部⻔编号以及每个部⻔的⼈数: 3、查询每个部⻔的部⻔编号以及每个部⻔员⼯⼯资⼤于1500的⼈数: 4、查询每个性别的学⽣的数量,根据gender统计学⽣的数量 -- 分组查询 GROUP BY -- 查询stu 展示gender以及不同性别的人数 -- SELECT COUNT(*) FROM stu WHERE gender='male'; -- SELECT COUNT(*) FROM stu WHERE gender='female'; -- 使用1条sql实现 SELECT gender, COUNT(*) FROM stu WHERE gender IS NOT NULL GROUP BY gender; -- 查询emp 统计不同部门的人数。以及展示不同部门的员工薪资总和 SELECT deptno,COUNT(*),SUM(sal) FROM emp GROUP BY deptno; -- GROUP BY 自带去重
9. 分组过滤HAVING
1.查询⼯资总和⼤于9000的部⻔编号以及⼯资和 SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000; -- HAVING -- 功能与where一致。 -- 查询sal=1500的员工信息 -- SELECT * FROM emp WHERE sal=1500; -- SELECT * FROM emp HAVING sal=1500; -- 需求: 对分组之后的数据进行过滤 -- 查询每个部门的员工人数 展示员工人数>=5 SELECT deptno,COUNT(*) AS cou FROM emp GROUP BY deptno HAVING COUNT(*)>=5 ; -- WHERE不能与分组函数一起使用 HAVING 可以一起使用 -- WHERE 必须在group by之前 HAVING 必须在group by 之后
10.排序 ORDER BY
-- 默认升序 ASC 可以省略 -- 降序: DESC -- 查询员工信息 根据sal进行升序/降序排序 -- 遇见了sal相同 根据empno进行降序排序 SELECT * FROM emp ORDER BY sal ASC; SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC , empno DESC; SELECT deptno,sum(sal) AS sum FROM emp GROUP BY deptno ORDER BY sum ASC;
11. 分页查询LIMIT
-- LIMIT 限定查询的结果集(分页查询) -- 一张表里面肯定有很多行记录 不可能查询所有的记录进行展示 -- 除非这张表里面的数据 不怎么改变 数量也不多 (查询所有) -- 分页查询emp表的信息 -- 每一页展示5条记录 pageSize=5 -- 一共有多少页 -- totalCount=19 -- SELECT COUNT(*) FROM emp; -- totalPage = totalCount/pageSize; -- totalPage = totalCount%pageSize==0 ?totalPage:totalPage+1 ; -- totalPage=4 -- LIMIT pageSize; 从第一行记录查询 查询pageSize条 查询第一页 -- LIMIT startRowIndex,pageSize; -- startRowIndex: 从指定的行记录的index开始查询 0 查询pageSize条 -- 获得用户请求查询的第page页 -- 查询第1页的数据 SELECT * FROM emp LIMIT 0,5; -- 查询第2页的数据 SELECT * FROM emp LIMIT 5,5; -- 查询第3页的数据 SELECT * FROM emp LIMIT 10,5; -- 查询第4页的数据 SELECT * FROM emp LIMIT 15,5; -- 通用的分页查询的模板sql -- 在java程序请求的时候 SELECT * FROM emp LIMIT (page-1)*pageSize,pageSize;
12. 多表查询
12.1 关联查询
-- 1、查询员工信息,要求显示: 员工号,姓名,月薪,部门名称 -- 2、查询员工信息,要求显示:员工号,姓名,月薪,薪资的级别 -- 3、查询员工信息,要求显示:员工号,姓名,月薪,部门名称,薪资的级别 -- 1.1 关联查询 -- 1、查询员工信息,要求显示: 员工号,姓名,月薪,部门名称 -- 14行记录 -- 56= 14*4 x*y 笛卡尔积里面有一部分数据是有误的 -- 过滤不符合要求的数据 找表的关系: 外键列 -- 规则: 多表连接查询 2 至少1个 3 至少2 SELECT e.empno,e.ename,e.sal,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno; -- 等值连接 -- 2、查询员工信息,要求显示:员工号,姓名,月薪,薪资的级别 SELECT e.empno,e.ename,e.sal,s.GRADE FROM emp AS e,salgrade AS s WHERE e.sal BETWEEN s.LowSAL AND s.HISAL -- 不等值连接 ORDER BY e.sal; -- 3、查询员工信息,要求显示:员工号,姓名,月薪,部门名称, 薪资的级别 SELECT e.empno,e.ename,e.sal,d.dname,s.GRADE FROM emp AS e, dept AS d,salgrade AS s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL; -- 查询指定用户购物车信息: 用户id=1006 -- 显示: 商品的图片 名称 单价 购买数量 小计 商品类型 -- SELECT -- p.id,p.prod_name,p.prod_image,p.prod_price, ci.buy_num, ci.money,t.type_name -- FROM -- mydb.product_info AS p, -- mydb.cart_item AS ci, -- mydb.type AS t, -- mydb.cart AS c -- WHERE -- c.uid = 1006 AND c.id=ci.cid AND ci.pid=p.id AND p.type_id=t.id;
12.2 连接查询
-- 1、查询员工信息,要求显示员工号,姓名,月薪,部门名称 使内连接和关联查询 -- 2、查询所有部门的名称以及每个部门的员工数量 -- 1.2 连接查询 -- 1. 内连接 表1 INNER JOIN 表2 ON 条件1 INNER JOIN 表3 ON 条件2...... -- 表1 INNER JOIN 表2 ON/WHERE 条件1 -- 内连接的查询的结果与普通的关联查询语法结果是完全一致的。 -- 内连接几乎不用 -- 2. 外连接 -- 2.1 左外连接 表1 LEFT JOIN 表2 ON 条件1 LEFT JOIN 表3 ON 条件2.... -- 以左边的表为基准表,右边的表没有的数据使用null/0填充 -- 2.2 右外连接 表1 RIGHT JOIN 表2 ON 条件1 RIGHT JOIN 表3 ON 条件2.... -- 以右边的表为基准表,左边的表没有的数据使用null/0填充 -- 1. 查询员工信息,要求显示员工号,姓名,月薪,部门名称 使内连接和关联查询 -- SELECT -- e.empno,e.ename,e.sal,d.dname -- FROM -- emp AS e,dept AS d -- WHERE e.deptno=d.deptno; -- SELECT -- e.empno,e.ename,e.sal,d.dname -- FROM -- emp AS e INNER JOIN dept AS d -- ON e.deptno=d.deptno WHERE 1=1; -- 2. 查询所有部门的名称以及每个部门的员工数量 -- 显示: 部门编号 部门名称 部门里面的员工数量 count() -- 4行记录 -- SELECT -- d.deptno,d.dname, COUNT(*) AS '每个部门的员工数量' -- FROM -- dept AS d,emp AS e -- WHERE d.deptno=e.deptno -- GROUP BY d.deptno -- ORDER BY d.deptno; -- 普通关联/内连接不能满足需求 d.deptno=e.deptno 将40部门筛掉 SELECT d.deptno,d.dname, COUNT(e.empno) AS '每个部门的员工数量' FROM emp AS e RIGHT JOIN dept AS d ON d.deptno=e.deptno GROUP BY d.deptno ORDER BY d.deptno;
12.3 自连接
-- 1、查询员工信息,员工的上级的名称 -- 2、查询员工信息,展示部门名称 薪资级别 上级领导名 -- 1.3 自连接 -- 本表与本表进行关联查询。把一张表看成多张表操作, 核心: "对表起别名"。 -- 1、 查询员工信息,员工的上级的名称 -- 显示员工基本信息,还要显示员工上级的领导的名称 -- 14行记录 -- SELECT -- e.empno,e.ename,e.sal,e.mgr, e1.empno, e1.ename AS '领导的名称' -- FROM -- emp AS e,emp AS e1 -- WHERE e.mgr=e1.empno; -- SELECT -- e.empno,e.ename,e.sal,e.mgr, e1.empno, e1.ename AS '领导的名称' -- FROM -- emp AS e LEFT JOIN emp AS e1 ON e.mgr=e1.empno; -- 2. 查询员工信息,展示部门名称 薪资级别 上级领导名 -- 外连接可以与普通关联混合使用 -- SELECT -- e.empno,e.ename,e.sal,e.mgr, d.dname,s.GRADE, e1.empno, e1.ename AS '领导的名称' -- FROM -- emp AS e,emp AS e1,dept AS d,salgrade AS s -- WHERE e.mgr=e1.empno AND e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL; -- SELECT -- e.empno,e.ename,e.sal,e.mgr,d.dname,s.GRADE, e1.empno, e1.ename AS '领导的名称' -- FROM -- emp AS e LEFT JOIN emp AS e1 ON e.mgr=e1.empno -- LEFT JOIN dept AS d ON e.deptno=d.deptno -- LEFT JOIN salgrade AS s ON e.sal BETWEEN s.LowSAL AND s.HISAL ; -- SELECT -- e.empno,e.ename,e.sal,e.mgr,d.dname,s.GRADE, e1.empno, e1.ename AS '领导的名称' -- FROM -- emp AS e LEFT JOIN emp AS e1 ON e.mgr=e1.empno,dept AS d,salgrade AS s -- WHERE -- e.deptno=d.deptno AND e.sal BETWEEN s.LowSAL AND s.HISAL; --
12.4 集合查询
-- UNION VS UNION ALL -- 1.4 集合查询 -- 语法:将多个结果集合并成一个结果集 -- 要求: 查询的列的数量必须一致 -- SELECT empno,ename FROM emp -- UNION ALL -- SELECT deptno,dname FROM dept -- UNION ALL -- SELECT * FROM stu; -- 场景: 分表的时候 -- 把学生信息拆分成很多表维护 -- CREATE TABLE stu_1 AS SELECT * FROM stu; -- stu stu_1 stu_3 -- 查询所有学生的记录 -- SELECT * FROM stu; -- SELECT * FROM stu_1; -- SELECT * FROM stu_3; -- UNION ALL 把所有的结果全部合并到一起 不会去重 -- UNION 把所有的结果全部合并到一起 会对行记录去重 -- SELECT * FROM stu -- UNION -- SELECT * FROM stu_1 -- UNION -- SELECT * FROM stu_3; -- MySQL里面实现去重的方式: -- 1. DISTINCT -- 2. UNION -- 3. GROUP BY
12.5 子查询
-- 子查询 -- SELECT语句中嵌套多个SELECT -- 原因: 查询的条件是未知的 -- 查询员工的sal=30号部门平均薪资的员工信息 -- SELECT * FROM emp WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno=30)
14. 数据库事务
数据库的事务(序列): transaction. 概念: 在功能实现中,需要一系列sql语句实现一个功能,这个功能里面的一些列sql要么是全部成功的,要么是全部失败的。不能出现一些sql成功了,一些sql失败了。 这个序列就是一个一个不可分割的工作单位。 事务由事务开始与事务结束之间执行的全部数据库操作组成。 目的: "保证数据的一致性"。
==14.1 四大特性 ACID==
-- 1. A atomic 原子性 -- 不可分割的工作单位。要么是全部成功的,要么是全部失败的。 -- 转账: 张三 balance 5000 给李四 1000 转账 转1000 -- UPDATE sys_user SET balance=balance-1000 WHERE id = 张三的id -- UPDATE sys_user SET balance=balance+1000 WHERE id = 李四的id -- 2. C consistentency 一致性 -- 事务开始之前 以及事务提交之后 数据正确的。 -- 事务开始之前: 张三的balance=5000 李四的balance=1000 -- 事务提交之后: 张三的balance=5000-1000 李四的balance=1000+1000 -- 3. I isolation 隔离性 -- 张三给李四转账 王五也可以给李四转账 -- 2个客户端同时操作 2个事务 事务与事务之间是相互隔离的。 -- 不同的DBMS软件使用不同的隔离级别机制,在事务并发的环境下,可能会出现数据安全的问题。 -- 问题: 脏读 不可重复读 幻读 -- 4. D durability 持久性 -- 事务提交之后 数据可以持久化保存。
14.2 DCL
-- 事务 -- 实现转账功能 BEGIN; -- 手动开启事务 SELECT * FROM sys_user WHERE id=1007; SELECT * FROM sys_user WHERE id=1008; -- 张三 id=1007转账1000给李四 id=1008 UPDATE sys_user SET balance=balance-1000 WHERE id=1007; -- 还有一系列逻辑需要执行 -- 只要更新sys_user记录 都需要再user_log新增一行记录 INSERT INTO user_log (type,remark) VALUES ('修改','更新了1007的余额-1000'); UPDATE sys_user SET balance=balance+1000 WHERE id=1008; INSERT INTO user_log (type,remark) VALUES ('修改','更新了1008的余额+1000'); ROLLBACK;-- 事务回滚 回滚到事务开始之前的数据上 COMMIT; -- 提交事务 将虚拟表中数据 更新到物理表 满足事务的D -- 以上的操作属于一个"转账"的功能 -- 问题: 没有满足事务的要求 -- 需求: 一系列的sql 全部成功或者全部失败 -- 根本原因: 没有在一个事务内执行 mysql的事务自动提交 -- 实现方式: 关闭mysql事务的自动的提交(手动控制事务) 保证在一个事务内执行 -- 查询mysql的事务提交 SHOW VARIABLES LIKE '%autocommit%'; SET autocommit = on; -- 关闭事务的自动提交
==14.3 隔离级别机制==
并发的环境下,DBMS使用不同的隔离级别机制,会对数据造成不同的问题。
-- 1. 读未提交(未提交读) read UNCOMMITTED -- 2. 读已提交 READ COMMITTED -- 3. 可重复读 REPEATABLE READ -- 4. 串行化 SERIALIZABLE -- SQLServer Oracle使用的隔离级别机制是: 读已提交 -- MySQL使用的隔离级别机制是: 可重复读 -- 查询MySQL的隔离级别机制 SHOW VARIABLES LIKE '%isolation%'; -- 产生的问题: 2个事务 -- 1. 脏读 -- A事务会读取B事务未提交的数据。 -- 2. 不可重复读 -- 在事务提交之后,先后多次读取的数据不是一致的。 update insert delete -- 3. 幻读 -- 在事务提交之后, 出现了很多新的记录。 insert
机制 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | y | y | y |
读已提交 | n | y | y |
可重复读 | n | n | y |
串行化 | n | n | n |
14.4 演示
演示MySQL在不同的隔离级别机制下产生的问题。
略。
-- 修改MySQL的隔离级别机制(学习期间 开发中 不会修改的) SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
15. 索引 INDEX
15.1 概念
索引就是MySQL软件提供的数据结构。用来提高查询/检索数据的效率。(SELECT) 索引依赖于物理表的。
15.2 索引结构
根据MySQL软件使用的存储引擎的不同,支持的数据结构也是不一样。 BTREE HASH RTree FULLTEXT
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分存储引擎都支持B+树索引。 |
Hash索引 | 底层数据结构是用hash实现,只有精确匹配索引的列才会生效,不支持范围查询。 |
R-tree(空间索引) | 是MyIsam引擎的一个特殊索引类型,主要用户地理空间数据类型,使用很少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文件的方式。类型后面学习的solr,es |
索引结构类型 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+tree索引 | Y | Y | Y |
hash索引 | N | N | Y |
R-tree索引 | N | Y | N |
Full-text索引 | 5.6+ Y | Y | N |
15.3 索引分类
-- 1. 唯一性索引 新增unique -- 2. 主键索引 primary key -- 3. 外键索引 foreign key -- 4. 普通索引 index -- 5. 全文索引 fulltext
15.4 优缺点
-- 优点: 提高查询的性能。 -- 缺点: -- 索引也是占用空间的 -- 更新操作是不友好的(insert update)
15.5 语法
-- 索引 INDEX -- SHOW INDEX FROM a; -- 操作index的语法 -- 1. 创建索引 -- CREATE INDEX 索引名 ON 表名(列1,列2....); -- CREATE UNIQUE INDEX 索引名 ON 表名(列1,列2....); -- CREATE FULLTEXT INDEX 索引名 ON 表名(列1,列2....); -- 创建index CREATE UNIQUE INDEX idx_sys_user_2 ON sys_user (email); -- 删除index DROP INDEX idx_sys_user ON sys_user; SELECT * FROM sys_user WHERE username like 'a%'; -- 查询index SHOW INDEX FROM sys_user; SHOW INDEX FROM product_info;
15.6 性能优化
-- SQL性能优化 -- 哪些sql执行的时间是比较久? -- 需要对哪些sql创建index进行优化,提升性能? -- 1. sql的执行频率 -- insert delete update select -- SHOW GLOBAL STATUS LIKE 'COM_______'; -- 2. 以上指定 得到 SELECT语句执行的频率比较高的 -- 肯定会出现某些select语句比较耗时的。 -- 知道哪些select查询语句比较耗时。 -- 2.1 慢查询日志 -- 记录了这些操作超过指定时间的select语句(long_query_time 10s) SHOW VARIABLES LIKE '%slow_query_log%'; SHOW VARIABLES LIKE '%long_query_time%'; -- SET long_query_time=1; -- SELECT * FROM sys_user WHERE username LIKE '%a%'; -- 2.2 PROFILE -- 记录了sql的耗时时间的具体的位置 -- 保证PROFILE开启的 -- SHOW VARIABLES LIKE '%PROFILE%'; -- SELECT @@HAVE_PROFILING; -- SHOW PROFILE FOR QUERY 292; -- SELECT * FROM sys_user; -- SHOW PROFILES; -- 2.3 EXPLAIN EXPLAIN SELECT * FROM sys_user WHERE id BETWEEN 1000 AND 3000; EXPLAIN SELECT * FROM sys_user WHERE SUBSTRING(username,0,3) ='abc'; EXPLAIN SELECT * FROM sys_user WHERE phone='110'; CREATE INDEX username_idx ON sys_user (username); CREATE UNIQUE INDEX phone_idx ON sys_user (phone); SHOW INDEX FROM sys_user;
==16. 数据备份==
在开发中,项目是要迭代升级。 问题: 在上线的过程中,新版本有bug,长时间都没有成功解决。 回退版本。----> 数据要回退。 -- 1. 物理备份 -- C:\ProgramData\MySQL\MySQL Server 8.0\Data -- 2. 命令行备份 -- 备份mydb库里面所有的表,索引,数据等。 -- 导出 mysqldump -uroot -proot mydb > d:\\a.sql mysqldump -uroot -proot mydb sys_user product_info > d:\\b.sql -- 导入 在连接成功前提下 -- source sql的文件路径 -- source d:\\a.sql -- 3. 可视化客户端工具 -- 导出: 转储sql文件(表结构,表数据) -- 导入: 运行指定位置的sql文件 -- 4. navicat----> 备份 -- 4.1 新建备份 -- 4.2 还原备份