『学习』MySQL实践

# 设置编码
CREATE DATABASE samp_db CHARACTER SET gbk ;

DROP DATABASE samp_db ;

DROP TABLE IF EXISTS test_1;
DROP TABLE IF EXISTS tmp;

# 创建表
CREATE TABLE test_1 (
                     a INT ,
                     b INT , 
                     c INT  
                     )
;

# 插入数据
# 插入一行数据
INSERT INTO test_1 (a, b, c) VALUES (1, 11, 111);
# 一次性插入多行数据
INSERT INTO test_1 (a, b, c) VALUES (2, 22, 222), 
                                    (3, 33, 333), 
                                    (4, 44, 444);

# 插入一列数据
# 只能先添加一列，然后再更新数据
ALTER TABLE test_1 ADD d INT ;

SELECT * FROM test_1;


# 每次只能更新一行
UPDATE test_1 SET d = 1111 WHERE a = 1;
UPDATE test_1 SET d = 2222 WHERE a = 2;
UPDATE test_1 SET d = 3333 WHERE a = 3;
UPDATE test_1 SET d = 4444 WHERE a = 4;

ALTER TABLE test_1 ADD e INT;
# 批量插入一列数据
REPLACE INTO test_1 (e) VALUES (11111), (22222), (33333), (44444), (55555);

ALTER TABLE test_1 ADD f INT;
INSERT INTO test_1 (f) VALUES (11111), (22222), (33333), (44444), (55555) ;

# 通过建立临时表的方式来更新一列数据
CREATE TEMPORARY TABLE tmp (temp_a INT, temp_e INT);
INSERT INTO tmp VALUES (1, 11111), (2, 22222), (3, 33333), (4, 44444), (5, 55555);
UPDATE test_1, tmp SET test_1.e = tmp.temp_e WHERE test_1.a = tmp.temp_a;

# 利用 when 语句批量更新一列数据
UPDATE test_1 
      SET f = 
          CASE a
						WHEN 1 THEN 111111
						WHEN 2 THEN 222222
						WHEN 3 THEN 333333
						WHEN 4 THEN 444444
          END
WHERE a IN (1, 2, 3, 4);

# 删除数据
DELETE FROM test_1 WHERE ISNULL(a) ;

# 两列相加
ALTER TABLE test_1 ADD g INT;
UPDATE test_1 SET g = a + b;

# 两列相减
ALTER TABLE test_1 ADD h INT;
UPDATE test_1 SET h = a - b;

# 一列除以同一个数
ALTER TABLE test_1 ADD i DOUBLE;
UPDATE test_1 SET i = a / 10;

# 一列求和再减一个数
SELECT (SUM(a) - 10000) AS temp FROM test_1;

-- SUM(investmoney) 
--      / (SELECT SUM(investmoney)
--             FROM product
--                 WHERE valuedate <= now() AND valuedate > '2016-05-05' AND repaymentdate >= now() AND delflag=0)
--          AS fact_rate;

# 一列的值除以该列的总和
ALTER TABLE test_1 ADD i_per DOUBLE;
INSERT INTO test_1 (i_per) SELECT SUM(a) / (SELECT SUM(a) FROM test_1) AS temp_2 FROM test_1 WHERE a IN (1, 2, 2);

SELECT * FROM test_1;

# 删除一列
ALTER TABLE test_1 DROP i;

# 部分匹配查询
SELECT a FROM test_1 WHERE a LIKE '%2%';    # % 代表 0 个或多个字符；
SELECT a FROM test_1 WHERE a LIKE '_2';     # _ 代表一个字符，
SELECT a FROM test_1 WHERE a REGEXP '[1-4]';    # [] 代表在某一个范围的字符，注：只能在正则表达式中用
SELECT a FROM test_1 WHERE a REGEXP '[^1-3]';    # [^] 代表不在某一范围的字符，注：同样也只能在正则表达式中用

# 空值查询
SELECT a FROM test_1 WHERE a IS NULL;

# 常用库函数及统计汇总查询
SELECT SUM(a) AS sum_a, AVG(a) AS avg_a FROM test_1;
SELECT MAX(a) AS max_a, MIN(a) AS min_a FROM test_1;
SELECT COUNT(DISTINCT a) AS a_num FROM test_1;    # 按列值统计个数，加入关键字 DISTINCT 后表示消去重复行，且对空值不计算，但对 0 计算

# 分组查询
SELECT a, COUNT(*) AS count_ FROM test_1 GROUP BY a;

# 若在分组后还要按照一定的条件进行筛选，则需要使用 HAVING 子句，且必须在 GROUP BY 子句后面
SELECT a, COUNT(*) AS count_ FROM test_1 GROUP BY a HAVING (count_ > 2); # HAVING 子句不使用中括号也可以

# 查询的排序
SELECT a FROM test_1 ORDER BY a DESC;    # ORDER BY 子句必须出现在其他子句之后，DESC 为降序，ASC 为升序，默认为升序，其中 a 也可以为数字，代表的是查询结果的第 i 列

# 数据表连接及连接查询
CREATE TABLE test_2 (
                      aa INT,
                      bb INT,
                      cc INT,
                      dd INT
                     ) ;

INSERT INTO test_2 VALUES (4, 3, 2, 1), 
                          (44, 33, 22, 11), 
                          (444, 333, 222, 111);

# INNER JOIN（内连接）显示符合条件的记录，此为默认值
SELECT test_1.a, test_2.bb
    FROM test_1, test_2
        WHERE test_1.a = test_2.aa;

# 此列子与上面的相同
SELECT test_1.a, test_2.bb
    FROM test_1 INNER JOIN test_2
        ON test_1.a = test_2.aa;

# LEFT OUTER JOIN （左(外)连接）用于显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边的数据行会以 NULL 来显示，如果是 BIT 型数据则以 0 来显示
SELECT test_1.a, test_2.aa
    FROM test_1 LEFT OUTER JOIN test_2
        ON test_1.a = test_2.aa;

# RIGHT OUTER JOIN （右（外）连接）跟左连接相反
SELECT test_1.a, test_2.aa
    FROM test_1 RIGHT OUTER JOIN test_2
        ON test_1.a = test_2.aa;