1. 创建表的三种方式
方式一:直接创建
create table [if not exists] 表名{
字段名1 类型1,
字段名2 类型2,
......
字段名n 类型n
}character set '编码集'
方式二:基于查询结果创建一张新表
create table test as(as可有可无)
select name,id
from student;
方式三:基于现有的表结构创建一张新表(只复制结构不复制内容)
creat table test2 like 库名.表名;
2. case when then end 嵌套用法
!!!以前的身份证是15位,这里我都放进去了
!!!建表语句放在最后边啦
2.1 身份证性别提取
SELECT
id,
case when xb is null then
case
when length(id)=18 then
case when substr(id,17,1)%2=0 then '女' else '男' end
when length(id)=15 then
case when substr(id,15,1)%2=0 then '女' else '男' end
else '身份证格式待确认'
end
else xb end '性别'
FROM student2
2.2 身份证出生日期提取
select
id,
case when csrq is null then
case
when length(id) = 18 then CONCAT(substr(id,7,4),'-',substr(id,11,2),'-',substr(id,13,2))
when length(id) = 15 then CONCAT('19',substr(id,7,2),'-',substr(id,9,2),'-',substr(id,11,2))
else '身份证格式待确认'
end
else csrq end '出生日期'
FROM student2
2.3 身份证年龄提取
select
id,
case when nl is null then
case
when length(id) = 18 then TIMESTAMPDIFF(year,STR_TO_DATE(SUBSTR(id,7,8),'%Y%m%d'),now())
when length(id) = 15 then TIMESTAMPDIFF(year,STR_TO_DATE(CONCAT('19', SUBSTR(id, 7, 6)), '%Y%m%d'),now())
else '身份证格式待确认'
end
else nl end '年龄'
from student2
2.4 知识点说明
2.4.1 TIMESTAMPDIFF函数
语法
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
解释
unit 是指定计算的时间单位。可以是以下之一:YEAR、QUARTER、MONTH、DAY、HOUR、MINUTE、SECOND 等。
datetime_expr1 是第一个日期时间表达式,通常是较早的日期时间。
datetime_expr2 是第二个日期时间表达式,通常是较晚的日期时间。
TIMESTAMPDIFF 返回一个整数,表示 datetime_expr2 相对于 datetime_expr1 的时间差,以 unit 指定的单位为基准。
2.4.2 STR_TO_DATE函数
STR_TO_DATE 是一个 MySQL
中的函数,用于将一个字符串表示的日期或时间转换为日期时间值,以便在查询中进行日期操作。
语法
STR_TO_DATE(string, format)
解释
string 是要转换的字符串,它包含日期或时间信息。
format 是一个格式化字符串,用于指定 string 中日期或时间的格式。
2.4.3 format
“format” 是在日期和时间处理中用于指定日期时间字符串的格式的重要概念。
根据需求组合搭配
日期格式:
%Y:四位年份,如 '2023'。
%y:两位年份,通常是后两位年份,如 '23'。
%m:月份,以两位数表示,如 '08' 表示八月。
%d:日期,以两位数表示,如 '19' 表示19号。
时间格式:
%H:小时,以两位数表示,24小时制,如 '14' 表示下午2点。
%h:小时,以两位数表示,12小时制,如 '02' 或 '2' 表示下午2点。
%i:分钟,以两位数表示,如 '05' 表示5分钟。
%s:秒,以两位数表示,如 '09' 表示9秒。
%p:AM/PM 指示符,通常与 %h 配合使用,如 'AM' 或 'PM'。
日期时间格式:
%Y-%m-%d:年-月-日,如 '2023-08-19'。
%y-%m-%d:年-月-日,如 '23-08-19'。
%Y/%m/%d:年/月/日,如 '2023/08/19'。
%d/%m/%Y:日/月/年,如 '19/08/2023'。
月份和星期格式:
%b:月份的缩写,如 'Aug' 表示八月。
%M:月份的全名,如 'August' 表示八月。
%a:星期几的缩写,如 'Mon' 表示星期一。
%W:星期几的全名,如 'Monday' 表示星期一。
2.4.4 substr 和 substring 区别
SUBSTR 和 SUBSTRING 都是用于从字符串中提取子字符串的函数,但它们的具体可用性和语法会因数据库类型而异。以下是这两个函数在一些常见数据库系统中的支持情况:
MySQL:
SUBSTR 和 SUBSTRING 都是支持的。
语法:SUBSTR(string, start, length) 或 SUBSTRING(string, start, length)。
Oracle:
SUBSTR 是 Oracle 中支持的标准函数。
SUBSTRING 通常不是 Oracle 的标准函数,而是使用 SUBSTR。
SQL Server:
SUBSTRING 是 SQL Server 中支持的标准函数。
SUBSTR 通常不是 SQL Server 的标准函数。
PostgreSQL:
SUBSTRING 是 PostgreSQL 中支持的标准函数。
SUBSTR 通常不是 PostgreSQL 的标准函数。
SQLite:
SQLite 支持 SUBSTR。
SUBSTRING 通常不是 SQLite 的标准函数。
Hive:
SUBSTR 和 SUBSTRING 都是支持的。
语法:SUBSTR(string, start, length) 或 SUBSTRING(string, start, length)。
常见的数据库一般最少支持一种,具体使用有的略有差异,大家了解一下即可
建表语句
DROP TABLE IF EXISTS `student2`;
CREATE TABLE `student2` (
`name` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
`id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`xb` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`csrq` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`nl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;
INSERT INTO `student2` VALUES ('a', '140601198706285716', NULL, NULL, NULL);
INSERT INTO `student2` VALUES ('王五', '140601197706285727', NULL, NULL, NULL);
INSERT INTO `student2` VALUES ('赵六', '140601500628571', NULL, NULL, NULL);
INSERT INTO `student2` VALUES ('李二', '1406015506285', NULL, NULL, NULL);
INSERT INTO `student2` VALUES ('七小', '140601199706285727', '女', '1997-06-28', '26');
INSERT INTO `student2` VALUES ('九七', '140601199710285727', NULL, NULL, NULL);
你以为结束了吗
不
还有
2.5 拉取库表信息(表名,表注释,字段名,字段注释)
SELECT distinct --根据需要可去重 t.TABLE_NAME,t.TABLE_COMMENT,c.Columns_NAME,c.Columns_COMMENT,t.table_rows
FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c
WHERE c.TABLE_NAME=t.TABLE_NAME AND t.TABLE_SCHEMA='表名'
注意:这里t.table_rows为粗略计算值,count()计算更为准确
有错误欢迎大家批评指证~