• SQL动态分区、用户管理以及流程控制

    1、表的增删改查

    1.1 表操作

    0）表注释

    # 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
   注释2 */

    • 1
    • 2
    • 3
    • 4
    • 5

    1）创建表

    CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT'密码' ,
`sex` VARCHAR(2) NOT NULL DEFAULT '男'COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address`VARCHAR (100)DEFAULT NULL COMMENT '家庭住址',
`email`VARCHAR(50)DEFAULT NULL COMMENT '邮箱',
-- 添加主键操作
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8;

# 查看数据库的定义
SHOW CREATE DATABASE school;
# 查看数据表的定义
SHOW CREATE TABLE student;
# 显示表结构
DESC student;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    2） 修改表

    • 添加列
    ALTER TABLE db_name.tableName
ADD col_name CHAR(20);

    • 1
    • 2
    • 删除列
    ALTER TABLE db_name.tableName
DROP COLUMN col_name;

    • 1
    • 2
    • 删除表
    DROP TABLE db_name.tableName;

    • 1

    3）插入数据

    desc  db_test.student

    • 1

    在这里插入图片描述

    • 普通插入
    INSERT INTO db_name.tableName(col1, col2)
VALUES(val1, val2);

    • 1
    • 2
    INSERT INTO db_test.student(name, remark)
VALUES("alex", "test one person");

    • 1
    • 2
    • 插入其他表检索出来的数据 – 表维度(into,直接追加到表中数据的尾部)
    -- 插入表，指定插入字段，不固定值
INSERT into db_name.tableName(col1, col2)

-- 检索内容
SELECT col1, col2 FROM mytable2;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 插入其他表检索出来的数据 – 分区维度(overwrite,先进行删除，再写入。如果存在分区的情况，insert overwrite会只重写当前分区数据)
    1）静态插入数据：要求插入数据时指定与建表时相同的分区字段，如：
insert overwrite tablename （year='2017', month='03'） select a, b from tablename2;

2）动静混合分区插入：要求指定部分分区字段的值，如：

insert overwrite tablename （year='2017', month） select a, b from tablename2;

3）动态分区插入：只指定分区字段，不用指定值，如：

insert overwrite tablename （year, month） select a, b from tablename2;

4)动态分区插入：使用每日调度参数

insert overwrite table du_data_analysis.soc_push_data_details partition (pt='${bizdate}')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 将一个表的内容插入到一个新表
    CREATE TABLE newtable AS
SELECT * FROM mytable;

    • 1
    • 2
    CREATE TABLE newtable AS
SELECT * FROM db_test.student;

    • 1
    • 2

    4）更新数据

    UPDATE db_test.student
SET name = "andy"
WHERE id = 1;

    • 1
    • 2
    • 3

    5）删除数据

    DELETE FROM db_test.student
WHERE id = 1;

    • 1
    • 2

    注意：

    • TRUNCATE TABLE 可以清空表，也就是删除所有行。

    • 使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

    1.2 用户管理

    1）创建用户

    mysql>  CREATE USER 'test1'@'localhost' identified by 'Test@001';
Query OK, 0 rows affected (0.00 sec)

    • 1
    • 2

    在这里插入图片描述

    2）查询用户

    mysql> SELECT USER FROM mysql.user;       查询所有用户
mysql> SHOW GRANTS For test1@'localhost'; 查询具体 test1 用户

    • 1
    • 2

    在这里插入图片描述
    在这里插入图片描述

    3）grant 用户授权

    a. 授权用户所有权限

    mysql> GRANT ALL ON *.* TO 'test1'@'192.127.12.16' IDENTIFIED BY 'Test@001';
Query OK, 0 rows affected, 1 warning (0.01 sec)

    • 1
    • 2
    • 限定IP地址 192.127.12.16 登录操作
    • *.* 第一个表示所有数据库，第二个表示所有表
    • 最后是远程密码

    b. 具体指定用户可用的语句，限制test1只能用SELECT语句

    mysql> GRANT SELECT ON *.* TO 'test1'@'localhost' identified BY 'Test@001';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; 刷新权限
Query OK, 0 rows affected (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5

    c. 查询是否授权成功

    mysql> SELECT * FROM USER WHERE HOST='192.168.12.16'\G;

    • 1

    4）删除用户

    mysql> DROP USER 'test1'@'localhost';
Query OK, 0 rows affected (0.01 sec)

    • 1
    • 2

    2、流程控制

    2.1 CASE语句

    类似的，SQL支持case条件语句，两种格式如下：

    • 1

    1）指定 case_value 匹配

    case_value是一个表达式，这个值与when_value相比较，如果相等，则进入相应的statement_list，statement_list就是一个语句列表，可以包含多条语句。如果在when_value中没有和case_value相等的值，则进入ELSE里面的statement_list语句列表。

    CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END 

    • 1
    • 2
    • 3
    • 4
    • 5

    2）指定 条件 匹配 【推荐用这种】

    每一个WHEN后面的search_condition表达式都会被判断，直到一个表达式的结果为真，然后执行相应的THEN后面的语句序列。如果没有匹配的，则执行ELSE后面的语句列表。

    CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 举例【字符模糊匹配】
    SELECT 
	name,
	remark,
CASE
	    WHEN NAME LIKE "李%" THEN "老李家的" 
		WHEN NAME LIKE "孙%" THEN "老孙家的" 
		WHEN NAME LIKE "林%" THEN "老林家的" 
		WHEN NAME LIKE "郭%" THEN "老郭家的" 
		ELSE "其他姓氏的" 
	END AS "firstName" 
FROM
	db_test.student

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    在这里插入图片描述

    • 举例【数字大小条件匹配】
    SELECT 
	name,
	age,
	CASE  
		when age > 65  THEN "退休"
		when age > 25  THEN "上班"
		when age > 3   THEN "上学"
		ELSE "小宝宝"
	END AS "work"
FROM db_test.student

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述

    3）以上两种，都需 else，假如不写 else兜底，则可以用：

    每一个WHEN后面的search_condition表达式都会被判断，直到一个表达式的结果为真，然后执行相应的THEN后面的语句序列。如果没有匹配的，则执行ELSE后面的语句列表。

    DELIMITER |
CREATE PROCEDURE p()
  BEGIN
    DECLARE v INT DEFAULT 1;
    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END ;
  END;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    3、关键字

    3.1 and 【与】

    -- 过滤姓李的，年龄在 18岁 到 35岁 之间
SELECT 
name,
age
FROM db_test.student
WHERE 
name LIKE "李%" 
AND age >= 18 
AND age <= 35

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    3.2 or 【或】

    -- 过滤姓李的或姓王的
SELECT 
name,
age
FROM db_test.student
WHERE 
name LIKE "李%" OR  name LIKE "王%" 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    3.3 not 【非】

    -- 过滤 非 王姓的
SELECT 
name,
age
FROM db_test.student
WHERE 
name NOT LIKE "王%"

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    3.4 and 与 or 高级过滤，需要添加 圆括号

    () 圆括号具有比AND，OR 更高的操作计算顺序。

    需求：过滤出 李姓或王姓的，且年龄 大于 100岁

    错误示范：
    在这里插入图片描述
    正确做法：

    SELECT 
name,
age
FROM db_test.student
WHERE 
(name LIKE "李%" OR  name LIKE "王%" )
AND age > 100

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述
    注意: 使用AND 和OR操作WHERE句子，都应该用圆括号明确分组操作。

    3.5 in 【范围内的列表值】

    IN操作符，用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。

    -- 过滤出 年龄分别为18, 30, 35, 60, 80, 100, 120的记录
SELECT
name,
age
FROM db_test.student
WHERE age in (18, 30, 35, 60, 80, 100, 120)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    使用IN的优点：

    • 语法清晰，特别是语法较长时
    • 操作符少，计算次序容易管理
    • IN比OR执行速度快
    • 最大的优点，可以包含其他SELECT语句，能够更加动态的建立WHERE子句

    4、模糊匹配

    通配符是SQL的WHERE子句中的特殊含义字符，子句中使用通配符必须使用LIKE操作符。

    4.1 百分号%通配符

    • 匹配开头，区分大小写
    SELECT
name,
age,
address
FROM db_test.student
WHERE name like "An%"

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    • 匹配 A开头，z结尾，区分大小写
    SELECT
name,
age,
address
FROM db_test.student
WHERE name like "A%z" 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    • 匹配任意位置包含 Willi 的值
    SELECT
name,
age,
address
FROM db_test.student
WHERE name like "%Willi%" 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    4.2 下划线 _ 通配符

    下划线与%不同的是匹配单个字符，而不是多个字符。即：一个下划线代表匹配一个字符

    -- 匹配姓名： 卢X明  
SELECT
name,
age,
address
FROM db_test.student
WHERE name like "卢_明" 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    -- 匹配姓名： XX明  
SELECT
name,
age,
address
FROM db_test.student
WHERE name like "__明" 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    总结： 一个 _ 匹配 一个字符串。

    4.3 不区分 大小写 过滤

    • 将查询条件用binary()括起来
    -- 匹配 以 an 开头的 name，不区分大小写
SELECT
name,
age,
address
FROM db_test.student
WHERE binary (name like 'an%')

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    4.4 通配符总结

    • 其他操作如果能达到相同的效果，就不要用通配符
    • 使用通配符尽量，缩小检索范围

    5、内置函数

    6、联合查询

  • 相关阅读:
    一、BurpSuite基本界面学习
    Redis与Mybatis
    聚观早报 | 极越07正式上市；宝骏云海正式上市
    矩阵分析学习笔记（五）：数域上矩阵的特征矩阵
    dpdk tap设备不能转发大于1500报文问题分析
    《向量数据库》——都有哪些向量数据库，都有什么特点？
    【python基础】format格式化函数的使用
    vscode - 添加新项目到远程仓库(gitee)
    深度学习在文档矫正中的应用
    【类和对象+this引用】
  • 原文地址：https://blog.csdn.net/Sunny_Future/article/details/126334834