Day4/7：2021-1-28：剑指offer的哈希表部分+MySQL必知必会-CSDN博客

本文链接：https://blog.csdn.net/qq_34318539/article/details/113360352

文章目录

第3章使用MySQL
第4章检索数据
第5章排序检索数据

第3章使用MySQL

3.0创建数据库

执行下列语句。10 queries executed, 10 success, 0 errors, 0 warnings。代码可以正常执行。你在这之前需要把数据库删干净，以防止有同名的数据库。

--------------------------------------------------------------------------------
-- MySQL Crash Course
-- http://www.forta.com/books/0672327120/
-- Example table creation scripts
--------------------------------------------------------------------------------


------------------------------------------------
-- Create customers table
------------------------------------------------
CREATE TABLE customers
(
  cust_id      INT       NOT NULL AUTO_INCREMENT,
  cust_name    CHAR(50)  NOT NULL ,
  cust_address CHAR(50)  NULL ,
  cust_city    CHAR(50)  NULL ,
  cust_state   CHAR(5)   NULL ,
  cust_zip     CHAR(10)  NULL ,
  cust_country CHAR(50)  NULL ,
  cust_contact CHAR(50)  NULL ,
  cust_email   CHAR(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=INNODB;

--------------------------------------------------
-- Create orderitems table
--------------------------------------------------
CREATE TABLE orderitems
(
  order_num  INT          NOT NULL ,
  order_item INT          NOT NULL ,
  prod_id    CHAR(10)     NOT NULL ,
  quantity   INT          NOT NULL ,
  item_price DECIMAL(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=INNODB;


------------------------------------------
-- Create orders table
------------------------------------------
CREATE TABLE orders
(
  order_num  INT      NOT NULL AUTO_INCREMENT,
  order_date DATETIME NOT NULL ,
  cust_id    INT      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=INNODB;

----------------------------------------------
-- Create products table
----------------------------------------------
CREATE TABLE products
(
  prod_id    CHAR(10)      NOT NULL,
  vend_id    INT           NOT NULL ,
  prod_name  CHAR(255)     NOT NULL ,
  prod_price DECIMAL(8,2)  NOT NULL ,
  prod_desc  TEXT          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=INNODB;

--------------------------------------------
-- Create vendors table
--------------------------------------------
CREATE TABLE vendors
(
  vend_id      INT      NOT NULL AUTO_INCREMENT,
  vend_name    CHAR(50) NOT NULL ,
  vend_address CHAR(50) NULL ,
  vend_city    CHAR(50) NULL ,
  vend_state   CHAR(5)  NULL ,
  vend_zip     CHAR(10) NULL ,
  vend_country CHAR(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=INNODB;

------------------------------------------------------
-- Create productnotes table
------------------------------------------------------
CREATE TABLE productnotes
(
  note_id    INT           NOT NULL AUTO_INCREMENT,
  prod_id    CHAR(10)      NOT NULL,
  note_date DATETIME       NOT NULL,
  note_text  TEXT          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MYISAM;


------------------------------------------
-- Define foreign keys
------------------------------------------
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

再执行这个，插入数据。55 queries executed, 55 success, 0 errors, 0 warnings

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

3.1连接数据库

操作win+r–>cmd–>net start mysql 去启动服务

-- 操作win+r-->cmd-->net start mysql 去启动服务
-- 在这里需要先去启动sql【net start mysql 去启动服务】，再去连接。
net stop mysql --去停止服务，
net start mysql --去启动服务，
mysql -uroot -p123456 --连接数据库，密码是 123456

现在是进入了sql系统

show databases; -- 查看所有的表 需要加上分号哦。语句以分号结尾。

3.3了解数据库和表

0.先去创建了数据库，首先创建crashcourse数据库，在SQLyog中运行创建数据库的程序，就可以创建了数据库。我不确定创建成功了没有，先继续进行吧。

1.显示所有可用的数据库列表。

show databases; -- 查看所有的表 需要加上分号哦。语句以分号结尾。

show tables; --获得一个数据库内的表的列表

show columns from customers; -- 查看customers表中的所有列设置
describe customers;  -- 同上，查看customers表中的所有列设置

软件中选择改变表，查看表中的列设置，也就是数据类型吧

第4章检索数据

#############################

4.2select检索单列

select prod_name from products;  -- 从products表中检索prod_name 单列。抛出一列数据。

4.3select检索多列

select prod_id,prod_name,prod_price from products;  -- 从products表中检索prod_name，prod_name,prod_price 多列

4.4select检索所有列

select * from products;  -- 从products表中检索所有列，通常情况下，检索不需要的列会降低检索和应用程序的效率。*代表所有元素。

4.5使用distinct 去重，这个作用还是挺重要的吧

select distinct vend_id from products;  # 使用distinct关键字去重，distinc只能放在列名的前面

select distinct prod_price from products;  # 使用distinct关键字去重，distinc只能放在列名的前面

select distinct vend_id,prod_price from products;  # distinct不仅对前置它的列vend_id起作用，同时也作用于prod_price，两列值有重复，才去重

注意哦，是两列值有重复，才去重。相当于返回的结果=A*B，这个意思也不对。。。

4.6限制结果的查询，使用limit检索部分行，开始位置为行索引值，索引从0开始

select prod_name from products limit 5; #从第 0 行开始，返回前 5 行
select prod_name from products limit 0,5; #从第 0 行开始，返回前 5 行

select prod_name from products limit 5,5; #从第 5 行开始，检索 5 行

# 另一种写法,推荐使用offset，这样不容易混乱。offset表示从多少行开始。
select prod_name from products limit 4 OFFSET 3; #从第 3 行开始，检索 4 行
select prod_name from products limit 3,4; #，同上，从第 3 行开始，检索 4 行

## 行数不够时，mysql只返回它能返回的那么多行

select count(prod_name) from products; # prod_name 共14行，索引为0-13
select prod_name from products limit 10,5; #从第 10 行开始，检索 5 行，行索引10-14，超出范围，只返回10-13共4行数据

# 使用完全限定的表名

select products.prod_name from products;
select products.prod_name from crashcourse.products; -- 也就是products.prod_name和crashcourse.products进行了限定。

第5章排序检索数据

关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义

5.1使用order by 子句对输出排序

# 按单列排序 

select prod_name from products order by prod_name;  # 以字母顺序排序prod_name列 
select prod_name from products order by prod_id;  # 使用非检索的列排序数据也是合法的，如使用prod_id顺序排列prod_name

# 按多列排序 

select prod_id, prod_price,prod_name from products order by prod_price, prod_name; #先按价格，再按产品名排序

# 降序排列 desc，desc只作用于直接位于其前面的列名

select prod_id, prod_price,prod_name from products order by prod_price desc; # 按价格降序排列
select prod_id, prod_price,prod_name from products order by prod_price desc, prod_name; #先按价格降序排列，再按产品名升序排列 
select prod_id, prod_price,prod_name from products order by prod_price desc, prod_name desc; #先按价格降序排列，再按产品名降序排列

6.哈希表

HashMap.getOrDefault(Object key, V defaultValue) 方法要记住。使用的次数还挺多。

剑指 Offer 50 .第一个只出现一次的字符

思考：不要理解错题意了。

在字符串 s 中找出第一个只出现一次的字符。对于aba，返回的结果应该为b。所以应该先遍历整个字符串s，统计每种字符的个数，然后再去遍历字符串，根据HashMap的结果去返回第一个只出现一次的字符。

我感觉计算字符出现的次数的思路还是挺常规的思路吧。

java.util.HashMap.getOrDefault(Object key, V defaultValue) 方法用于获取使用指定键映射的值。如果没有使用提供的键映射任何值，则返回默认值。

import java.util.HashMap;

public class Offer50 {
    public static void main(String[] args) {
        String s = new String("abaccdeff");
        System.out.println(firstUniqChar(s));
    }

    // 在字符串 s 中找出第一个只出现一次的字符。
    // 如果没有，返回一个单空格。 s 只包含小写字母。

    public static char firstUniqChar(String s) {
        // 输入条件判断，字符串s为空，则返回空格。
        if (s == null || s.length() == 0) {
            return ' ';
        }
        HashMap<Character, Integer> hashMap = new HashMap<>();
        for (int i = 0; i < s.length(); i++) {
            int count = hashMap.getOrDefault(s.charAt(i), 0) + 1;
            hashMap.put(s.charAt(i), count);
        }
        char res = ' ';
        for (int i = 0; i < s.length(); i++) {
            if (hashMap.get(s.charAt(i)) == 1) {
                res = s.charAt(i);
                break;
            }
        }
        return res;
    }
}

剑指 Offer 56 - I 数组中数字出现的次数

思考：

（1）普通解法，但是面试可能不能写这个做法

import java.util.HashMap;

public class Offer56_1 {
    public static void main(String[] args) {
        int[] nums = new int[]{1, 2, 10, 4, 1, 4, 3, 3};
        System.out.println(singleNumbers(nums));
    }

    // 一个整型数组 nums 里除两个数字之外，其他数字都出现了两次。
    // 请写程序找出这两个只出现一次的数字。
    public static int[] singleNumbers(int[] nums) {
        HashMap<Integer, Integer> map = new HashMap<>();
        for (int i = 0; i < nums.length; i++) {
            int count = map.getOrDefault(nums[i], 0) + 1;
            map.put(nums[i], count);
        }
        int[] res = new int[2];
        int index = 0;
        for (int i = 0; i < nums.length; i++) {
            if (map.get(nums[i]) == 1) {
                res[index++] = nums[i];
                if (index >= 2) {
                    break;
                }
            }
        }
        return res;
    }
}

（2）位运算的做法

位运算的做法，见题解，需要背诵。

剑指 Offer 56 - II 数组中数字出现的次数 II

思考：

（1）简单做法 HashMap

import java.util.HashMap;

public class Offer56_2 {
    public static void main(String[] args) {
        int[] nums = new int[]{9, 1, 7, 9, 7, 9, 7};
        System.out.println(singleNumber(nums));
    }

    // 在一个数组 nums 中除一个数字只出现一次之外，其他数字都出现了三次。请找出那个只出现一次的数字。
    public static int singleNumber(int[] nums) {
        HashMap<Integer, Integer> map = new HashMap<>();
        for (int i = 0; i < nums.length; i++) {
            int count = map.getOrDefault(nums[i], 0) + 1;
            map.put(nums[i], count);
        }
        // 遍历 map 的 values
        int res = -1;
        for (Integer key : map.keySet()) {
            if (map.get(key) == 1) {
                res = key;
                break;
            }
        }
        return res;
    }
}

（2）

剑指 Offer 57 - I 和为s的两个数字

思考：类型简单。根据字眼递增数组，这样就简单了。

输入一个递增排序的数组和一个数字s，在数组中查找两个数，使得它们的和正好是s。如果有多对数字的和等于s，则输出任意一对即可。

import java.util.Arrays;

public class Offer57 {
    public static void main(String[] args) {
        int[] nums = new int[]{2, 7, 11, 15};
        int target = 9;
        // Arrays.toString是为了将数组变为字符串去输出。
        System.out.println(Arrays.toString(twoSum(nums, target)));
    }

    // 输入一个递增排序的数组和一个数字s，在数组中查找两个数，使得它们的和正好是s。
    // 如果有多对数字的和等于s，则输出任意一对即可。
    public static int[] twoSum(int[] nums, int target) {
        // 1.扣字眼：递增排序的数组。
        int i = 0, j = nums.length - 1;
        while (i < j) {
            if (nums[i] + nums[j] == target) {
                break;
            } else if (nums[i] + nums[j] > target) {
                // j-- 会使得数据和 减少
                j--;
            } else if (nums[i] + nums[j] < target) {
                // i++ 会使得数据和 增加
                i++;
            }
        }
        return new int[]{nums[i], nums[j]};
    }
}