头像

cyb-包子

重庆邮电大学




离线:3小时前



第六章 事务和索引

什么是事务,要么都成功,要么都失败

1. sql  A 给 B 转账      A  1000 -- > 200 B: 200
2. sql  B 收到 A 的 钱 A 800  B 400

将一组SQL放在一个批次中执行

事务(Transaction)原则

ACID原则

原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)

脏读、幻读

原子性

要么都成功,要么都失败

一致性

事务前后的数据要保证完整性,1000

隔离性

无法被其他事务干扰

持久性

事务一旦提交不可逆转,被持久化到数据库中

隔离性中,如果无法保证,存在干扰

  • 脏读
  • 一个事务读取了另一个事务未提交的数据
  • 不可重复读
  • 多次读取的数据不一致
  • 幻读
  • 一般有人插入了新的数据,多了新的一行

6.1 事务

mysql 是默认开启事务自动提交的

SET autocommit = 0 -- 关闭
SET autocommit = 1  --开启 默认值



-- 手动处理事务
-- 事务开启 关闭自动提交
SET autocommit = 0

SET TRANSACTION  -- 标记一个事务的开始
INSERT xxx
INSERT xxx

-- 提交 持久化(成功)
COMMIT

-- 回滚 失败
ROLLBACK

-- 事务结束 开启自动提交
SET autocommit = 1 


SAVEPOINT   -- 设置一个事务的保存点
SAVEPOINT 存档1 
ROLLBACK TO 存档1 --回滚到保存点
/*
课堂测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交

6.2 索引

  • 主键索引 (Primary Key)

  • 唯一表示,主键不能重复,只能有一个列作为主键

  • 唯一索引 (Unique)

  • 避免重复的列出现,唯一索引可以重复,多个列都可以表示为唯一索引

CREATE TABLE `Grade`( `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY, `GradeName` VARCHAR(32) NOT NULL UNIQUE -- 或 UNIQUE KEY `GradeID` (`GradeID`) )

  • 常规索引 (Index)

  • 默认。index,key关键字设置

`` CREATE TABLEresult( -- 省略一些代码 INDEX/KEYind(studentNo,subjectNo`) – 创建表时添加
)

– 创建后添加
ALTER TABLE result ADD INDEX ind(studentNo,subjectNo);
```

  • 全文索引 (FullText)

  • 在特点数据库才有 MyISAM

  • 快速定位数据
  • 只能用于MyISAM类型的数据表
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集
SHOW INDEX FROM `student`
/*
#方法一:创建表时
    CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );


#方法二:CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;


#方法三:ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;


#删除索引:DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前,先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

测试索引

建表app_user:

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据:100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

image-20201128160554307.png

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

image-20201128160651539.png

索引准则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

索引的数据结构

-- 我们可以在创建上述索引的时候,为其指定索引类型,分两类
hash类型的索引:查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;



第五章 Mysql函数

5.1 常用函数

-- 数学运算
abs
ceiling 向上取整
floor 向下取整
rand()
sign()  返回符号


-- 字符串函数
CHAR_LENGTH  字符串长度
CONCAT 拼接字符串
SELECT INSERT('12345',1,2,'cc')
-- cc345

LOWER 
UPPER

SELECT NOW()
SELECT LOCALTIME

5.2 聚合函数(常用)

函数名 功能
COUNT() 想查询一个表中有多少记录
AVG() 均值
SUM() 总和
MAX() 最大值
MIN() 最小值
-- 想查询一个表中有多少记录
SELECT COUNT(`studentname`) FROM student -- 会忽略所有的null值  最快  指定字段

-- 不会忽略null值
SELECT COUNT(*) FROM student  -- 计算行数
SELECT COUNT(1) FROM student  -- 有主键情况下 本质也是计算行数

SELECT SUM(`studentresult`) FROM result
SELECT AVG(`studentresult`) FROM result



SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno`

补充1. 分组和过滤

SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno`
HAVING 平均分 > 70

补充2. MD5加密

MD5不可逆

主要增强算法复杂度和不可逆性

image-20201126221259382.png



活动打卡代码 LeetCode 93. 复原IP地址

class Solution {

    static List<String> ans = new ArrayList<String>();

    static void dfs(String s, int u, int k, String path){
        if(u == s.length())
        {
            if(k == 4)
            {
                ans.add(path.substring(0,path.length() - 1));//去掉"."号
            }
            return ;
        }


        if(k == 4) return;
        for(int i = u,t = 0;i < s.length();i ++){
            if(i > u && s.charAt(u) == '0') break;//前导0
            t = t * 10 + s.charAt(i) - '0';
            if(t <= 255) dfs(s,i + 1,k + 1,path + t + ".");
            else break;
        }
    }

    public List<String> restoreIpAddresses(String s) {
        ans.clear();
        dfs(s,0,0,"");
        return ans;
    }
}


活动打卡代码 LeetCode 92. 反转链表 II

class Solution {
    public int numDecodings(String s) {
        int n = s.length();
        int[] f = new int[n+10];
        f[0] = 1;
        for(int i = 1; i <= n; i ++){
            if(s.charAt(i-1) != '0') f[i] += f[i-1];
            if(i >= 2){
                int num = (s.charAt(i-2) - '0')*10 + (s.charAt(i-1) - '0');
                if(num>=10&&num<=26) f[i] += f[i-2];
            }
        }

        return f[n];
    }
}



LeetCode92 反转链表

题目描述

反转从位置 mn 的链表。请使用一趟扫描完成反转。

样例

输入: 1->2->3->4->5->NULL, m = 2, n = 4
输出: 1->4->3->2->5->NULL

算法分析

推荐小呆呆的题解,写的不错

LeetCode 92. 反转链表 II - AcWing

时间复杂度

Java代码

/**
 * Definition for singly-linked list.
 * public class ListNode {
 *     int val;
 *     ListNode next;
 *     ListNode(int x) { val = x; }
 * }
 */
class Solution {
    public ListNode reverseBetween(ListNode head, int m, int n) {
        ListNode dummy = new ListNode(0);
        dummy.next = head;
        ListNode p = dummy;
        ListNode q = dummy;

        for(int i = 0;i < m - 1;i ++) p = p.next;
        for(int i = 0;i < n ;i ++) q = q.next;

        ListNode a = p.next;
        ListNode b = q.next;
        for(ListNode c = p.next,d = c.next;d != b;){
            ListNode e = d.next;
            d.next = c;
            c = d;
            d = e;
        }

        a.next = b;
        p.next = q;

        return dummy.next;

    }
}


活动打卡代码 LeetCode 91. 解码方法

class Solution {
    public int numDecodings(String s) {
        int n = s.length();
        int[] f = new int[n+10];
        f[0] = 1;
        for(int i = 1; i <= n; i ++){
            if(s.charAt(i-1) != '0') f[i] += f[i-1];
            if(i >= 2){
                int num = (s.charAt(i-2) - '0')*10 + (s.charAt(i-1) - '0');
                if(num>=10&&num<=26) f[i] += f[i-2];
            }
        }

        return f[n];
    }
}



LeetCode91 解码方法

题目描述

一条包含字母 A-Z 的消息通过以下方式进行了编码:

'A' -> 1
'B' -> 2
...
'Z' -> 26

给定一个只包含数字的非空字符串,请计算解码方法的总数。

题目数据保证答案肯定是一个 32 位的整数。

样例

输入:s = "12"
输出:2
解释:它可以解码为 "AB"(1 2)或者 "L"(12)。
输入:s = "226"
输出:3
解释:它可以解码为 "BZ" (2 26), "VF" (22 6), 或者 "BBF" (2 2 6) 。
输入:s = "0"
输出:0
输入:s = "1"
输出:1

算法分析

  • dp问题

image-20201127164325587.png

时间复杂度

$O(n)$

Java代码

class Solution {
    public int numDecodings(String s) {
        int n = s.length();
        int[] f = new int[n+10];
        f[0] = 1;
        for(int i = 1; i <= n; i ++){
            if(s.charAt(i-1) != '0') f[i] += f[i-1];
            if(i >= 2){
                int num = (s.charAt(i-2) - '0')*10 + (s.charAt(i-1) - '0');
                if(num>=10&&num<=26) f[i] += f[i-2];
            }
        }

        return f[n];
    }
}



第四章_DQL查询数据

重点

4.1、DQL

Data Query Language: 数据查询语言

  • 所有查询都用它 Select
  • 简单、复杂的查询都可以用它
  • 最核心的语言
  • 使用频率最高

建立数据库

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;


-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
    `gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
    `subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;


-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);



-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');


-- 插入成绩数据  这里仅插入了一组,其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

4.2、指定查询字段

SELECT * FROM student;  --查询所有学生
SELECT * FROM result; --查询所有成绩

-- 查询指定字段
SELECT `studentno`, `studentname` FROM student;

studentno   studentname
1000    张伟
1001    赵强

-- 使用别名  给结果起一个名字 列名换 -- 也可以给表起别名
SELECT `studentno` AS  学号, `studentname` AS 学生姓名 FROM student as s;

学号  学生姓名
1000    张伟
1001    赵强


-- 函数 concat(a,b)
SELECT CONCAT('姓名:', `studentname`) AS 新名字 FROM student;

新名字
姓名:张伟
姓名:赵强

去重复

distinct

-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM result;
-- 有重复数据  需要去重
SELECT DISTINCT `studentno` FROM result;

去除select 查询出来的结果中重复的数据,只显示一条

select 表达式 from 表

表达式可以为:文本值,列,Null,函数,计算表达式,系统变量

4.3、where条件字句

作用:检索数据中符合条件的

逻辑运算符

运算符 语法 描述
and && a and b a &&b
or || a or b a || b
Not ! not a ! a
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 95 AND 100;

模糊查询

比较运算符

运算符 语法 描述
is null a is null 操作符null,结果真
is not null a is not null
between and
Like a like b SQL匹配,如果匹配,真
in a in (a1,a2,…) 假设a在a1,或者a2,…其中一个返回真

like

  • % 0 到任意个字符

  • _一个字符

-- 以张开头
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '张%';

-- 中间有某个字  %张%

in

SELECT `studentno`,`studentname` FROM student
WHERE `address` IN ('广东深圳');

4.4、连表查询

JOIN

简单的jon理论

image-20201122154454436.png

-- 查询参加了考试的同学(学号,姓名,科目编号,分数)

SELECT * FROM student;
SELECT * FROM result;



-- 1. 查那些字段,来自那个表
-- 2. 确定使用的查询
-- 3. 交集的确定
-- 4. 判断的条件, 表1 xx = 表2 xx


SELECT s.`studentno`, s.`studentname`, r.`subjectno`, r.`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE   s.`studentno` = r.`studentno`;


-- right join
SELECT s.`studentno`, s.`studentname`, r.`subjectno`, r.`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`;


-- left join
SELECT s.`studentno`, s.`studentname`, r.`subjectno`, r.`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.`studentno` = r.`studentno`;

操作 描述
Inner join 如果表中至少有一个匹配,就返回
left join 会从左表返回所有的值,即使右表没有匹配
right join 会从右表返回所有的值,即使左表没有匹配

左右表?

FROM student AS s
RIGHT JOIN result AS r   
-- 左表  s, 右表 r, 返回右表所有,即r的所有

FROM student AS s
LEFT JOIN result AS r
-- 左表s,右表r,返回左表所有的,即学生的信息,即使他没参加考试

那么如何查询缺考的学生呢?

-- 查询缺考的学生
SELECT s.`studentno`, s.`studentname`, r.`subjectno`, r.`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` IS NULL;
  • 查询的是学生
  • left join
  • where条件

join on和where

  • join (连接的表) on 连接查询
  • where 等值查询

查询:参考考试的同学信息:学号,学生姓名,科目名,分数

SELECT s.`studentno`,s.`studentname`,sb.`subjectname`,r.`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sb
ON r.`subjectno` = sb.`subjectno`

image-20201122162308170.png

套路:

  1. 查询啥 select …
  2. 那几个表 …
  3. 那个表基准。Left,right,inner
  4. 交叉条件
  5. 一张一张表连

a left join b 以a为基准

a right join b以b为基准

自连接查询

自己的表和自己的表连接,核心:一张表拆为两张一样的表

建立测试表数据

CREATE TABLE `school`.`category`( `categoryid` INT(3) NOT NULL COMMENT 'id', `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', PRIMARY KEY (`categoryid`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

image-20201122163049600.png
image-20201122163518448.png

查询父类对应的子类关系

父类 子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术
SELECT p.`categoryname` AS '父栏目',s.`categoryname` AS '子栏目'
FROM `category` AS p, `category` AS s
WHERE p.`categoryid` = s.`pid`;

image-20201122163940831.png

核心:把一张表拆为两张表,别名是关键

4.5 分页和排序

分类limit和排序order by

排序:升序asc,降序desc

SELECT s.`studentno`,s.`studentname`,sb.`subjectname`,r.`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sb
ON r.`subjectno` = sb.`subjectno`
ORDER BY studentresult DESC

分页limit

为啥要分页呢?缓解数据库压力

SELECT s.`studentno`,s.`studentname`,sb.`subjectname`,r.`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sb
ON r.`subjectno` = sb.`subjectno`
ORDER BY studentresult DESC
LIMIT 0,3

第1~3条数据

limit 起始值 页面大小

-- 第一页
limit 0,5
-- 第二页
limit 5,5
-- 第三页 
limit 10,5

-- 第n页
limit (n-1)*5,5

limit (n-1)*pagesize,pagesize 
-- n代表当前页,pagesize代表页面大小
-- (n-1)*pagesize 起始值
-- 数据总数/页面大小 = 总页数

4.6 子查询

在where语句中嵌套一个子查询语句

where( select xxx)

关联查询的效率更高



活动打卡代码 LeetCode 90. 子集 II

class Solution {
    static List<List<Integer>> ans = new ArrayList<List<Integer>>();
    static List<Integer> path = new ArrayList<Integer>();
    static boolean[] st;
    static void dfs(int[] nums,int u)
    {
        if(u == nums.length)
        {
            ans.add(new ArrayList<Integer>(path));
            return ;
        }

        dfs(nums,u + 1);
        //保证一个顺序来选择
        if(u > 0 && nums[u] == nums[u - 1] && !st[u - 1]) return ;
        st[u] = true;
        path.add(nums[u]);
        dfs(nums,u + 1);
        path.remove(path.size() - 1);
        st[u] = false;

    }
    public List<List<Integer>> subsetsWithDup(int[] nums) {
        ans.clear();
        Arrays.sort(nums);
        st = new boolean[nums.length + 10];
        dfs(nums,0);
        return ans;
    }
}



LeetCode90 子集II

题目描述

给定一个可能包含重复元素的整数数组 nums,返回该数组所有可能的子集(幂集)。

说明:解集不能包含重复的子集。

样例

输入: [1,2,2]
输出:
[
  [2],
  [1],
  [1,2,2],
  [2,2],
  [1,2],
  []
]

算法分析

小呆呆思路

先对数组从小到大排序,每个数有选和不选两种情况,若选的话,假设上一个数与当前数一致,且上一个数没有选,则当前数一定不能选,否则会产生重复情况

时间复杂度

Java代码

class Solution {
    static List<List<Integer>> ans = new ArrayList<List<Integer>>();
    static List<Integer> path = new ArrayList<Integer>();
    static boolean[] st;
    static void dfs(int[] nums,int u)
    {
        if(u == nums.length)
        {
            ans.add(new ArrayList<Integer>(path));
            return ;
        }

        dfs(nums,u + 1);
        //保证一个顺序来选择
        if(u > 0 && nums[u] == nums[u - 1] && !st[u - 1]) return ;
        st[u] = true;
        path.add(nums[u]);
        dfs(nums,u + 1);
        path.remove(path.size() - 1);
        st[u] = false;

    }
    public List<List<Integer>> subsetsWithDup(int[] nums) {
        ans.clear();
        Arrays.sort(nums);
        st = new boolean[nums.length + 10];
        dfs(nums,0);
        return ans;
    }
}