SQL重点语法

已于 2025-04-10 12:57:00 修改
阅读量1.7k 收藏 2
点赞数
文章标签： sql 数据库 mysql
于 2022-12-06 17:59:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_45322869/article/details/128202048
版权

目录

1.MySql增量同步

2.PostgreSQL增量同步

3.SQL窗口函数

3.1 聚合窗口函数

3.2 排名窗口函数

3.3 取值函数窗口

3.4连续7天登录用户查询

3.5求各区域下商品销售top3

附建表语句

1.MySql增量同步

当MySql数据同步以源库为准时，如果插入的数据与现有的唯一索引或者主键有冲突，这个会把旧记录删掉，插入当前的新记录。

Replace into 目的表名 select * from 源表名 where +过滤条件

当Mysql数据同步以目的库为准时，如果插入的数据与现有的唯一索引或者主键有冲突，就会忽略，新的记录不插入

insert IGNORE into 目的表名SQ select * from 源表名 where +过滤条件

2.PostgreSQL增量同步

PgSql增量同步语法如下：

INSERT INTO 
	table_name(column_list) 	-- 需要插入的字段
VALUES (value_list)				-- 插入的数据
ON CONFLICT (conflict_target)	-- conflict_target 存在冲突的字段，可以是主键、复合主键、唯一键、复合唯一键、多字段组成的唯一值等;例 CONFLICT(id,name,sex,create_time)
conflict_action	
/* 				
conflict_action	字段冲突时执行的操作
DO NOTHING: 如果存在冲突，不采取任何动作。
DO UPDATE: 如果存在冲突，使用 DO UPDATE SET column_1 = value_1, .. WHERE condition 更新表中的字段
*/
[RETURNING {* | column_names}];--返回值

Pgsql实现数据存在则修改，不存在则插入功能的语法，
将 test2 表中查询出来的数据插入 test3 表中，若存在则更新，若不存在则插入

INSERT INTO 
	test3(id, name, sex, create_time)
SELECT	
	id,
	name,
	sex,
	now() create_time
FROM
	test2 
ON CONFLICT(id)  --目的表 test3 表中确定唯一值的字段，可用多个字段确定 CONFLICT(id,name,sex,create_time)
DO UPDATE SET    --若存在检查字段冲突，则执行将test3表中字段更新为test2查询出来的字段
		name   = EXCLUDED.name,
		sex = EXCLUDED.sex,
		create_time = EXCLUDED.create_time;

Pgsql实现数据存在则忽略，不存在则插入功能的语法，
将 test2 表中查询出来的数据插入 test3 表中，若存在则忽略，若不存在则插入 

INSERT INTO 
	test3(id, name, sex, create_time)
SELECT	
	id,
	name,
	sex,
	now() create_time
FROM
	test2 
ON CONFLICT(id)  --目的表 test3 表中确定唯一值的字段，可用多个字段确定 CONFLICT(id,name,sex,create_time)
DO NOTHING;    --若存在检查字段冲突，则执行忽略

3.SQL窗口函数

3.1 聚合窗口函数

SUM/COUNT/AVG/MAX/MIN (expression) OVER(PARTITION BY COLUNM_A,COLUNM_B,COLUNM_C......   ORDER BY  COLUNM_A........   ROW/RANGE  BETWEEN frame_start AND frame end)

SUM/COUNT/AVG/MAX/MIN (expression) OVER(PARTITION BY COLUNM_A,COLUNM_B,COLUNM_C......   ORDER BY  COLUNM_A........   ROW/RANGE  BETWEEN frame_start AND frame end)

 其中 SUM/COUNT/AVG/MAX/MIN 是窗口函数的名称，expression是可选的分析对象（字段名或者表达式），OVER子句包含分区（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）3个选项。

窗口函数OVER子句中的PARTITION BY选项用于定义分区，其作用类似于查询语句中的GROUP BY子句。如果我们指定了分区选项，窗口函数将会分别针对每个分区单独进行分析。

窗口函数OVER子句中的frame_clause选项用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。在指定了分析窗口之后，窗口函数不再基于分区进行分析，而是基于窗口内的数据进行分析。

其中，ROWS表示窗口的偏移量，RANGE表示以数值（例如10天、5km等）为单位计算窗口的偏移量。
frame_start选项用于定义窗口的起始位置，可以指定以下内容之一：
●UNBOUNDED PRECEDING——表示窗口从分区的第一行开始。
●N PRECEDING——表示窗口从当前行之前的第N行开始。
●CURRENT ROW——表示窗口从当前行开始。


frame_end选项用于定义窗口的结束位置，可以指定以下内容之一：
●CURRENT ROW——表示窗口到当前行结束。
●M FOLLOWING——表示窗口到当前行之后的第M行结束。
●UNBOUNDED FOLLOWING——表示窗口到分区的最后一行结束。

下面语句表示分析窗口从当前分区的第一行开始，直到当前行结束。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

查找短期之内（3天）累计转账超过100万元的账户：

SELECT
	*
FROM
	(
		SELECT
			LOG_ID,--ID
			LOG_TS,--时间
			FROM_USER,--转账人
			AMOUNT,
			SUM(AMOUNT) OVER(PARTITION BY FROM_USER ORDER BY LOG_TS RANGE INTERVAL '3' DAY PRECEDING) as HJ--转账合计
		FROM
			C_TRANSFER_LOG
		WHERE
			TYPE = '转账'
	)
WHERE
	HJ > 1000000

3.2 排名窗口函数

排名窗口函数可以用于获取数据的分类排名，排名窗口函数不支持动态的窗口大小选项，而是以整个分区作为分析的窗口。常见的排名窗口函数如下：

1. ROW_NUMBER函数可以为分区中的每行数据分配一个序列号，序列号从1开始。
2. RANK函数返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
3. DENSE_RANK函数返回当前行在分区中的名次。即使存在名次相同的数据，后续的排名也是连续值。
4. PERCENT_RANK函数以百分比的形式返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。
5. CUME_DIST函数计算当前行在分区内的累积分布。
6. NTILE函数将分区内的数据分为N等份，并返回当前行所在的分片位置。

SELECT
	A.DEPT_ID,
	B.DEPT_NAME,
	EMP_NAME,
	SEX,
	HIRE_DATE,
	SALARY,
	ROW_NUMBER() OVER(PARTITION BY A.DEPT_ID ORDER BY SALARY DESC ) AS ROW_NUMBER,
	RANK() OVER(PARTITION BY A.DEPT_ID ORDER BY SALARY DESC ) AS 	RANK,
	DENSE_RANK() OVER(PARTITION BY A.DEPT_ID ORDER BY SALARY DESC ) AS 	DENSE_RANK,
	PERCENT_RANK() OVER(PARTITION BY A.DEPT_ID ORDER BY SALARY DESC ) AS 	PERCENT_RANK,
	CUME_DIST() OVER(PARTITION BY A.DEPT_ID ORDER BY SALARY DESC ) AS 	CUME_DIST,
	NTILE(5) OVER(PARTITION BY A.DEPT_ID ORDER BY SALARY DESC ) AS 	NTILE
FROM
	EMPLOYEE A
INNER JOIN 
	DEPARTMENT B 
ON A .DEPT_ID = B.DEPT_ID

3.3 取值函数窗口

取值窗口函数可以用于返回窗口内指定位置的数据行，其中，LAG函数和LEAD函数不支持动态的窗口大小，它们以整个分区作为分析的窗口。。常见的取值窗口函数如下：

LAG函数可以返回窗口内当前行之前的第N行数据。
LEAD函数可以返回窗口内当前行之后的第N行数据。
FIRST_VALUE函数可以返回窗口内第一行数据。
LAST_VALUE函数可以返回窗口内最后一行数据。
NTH_VALUE函数可以返回窗口内第N行数据。

1.环比分析、同比分析
环比增长指的是本期数据与上期数据相比的增长，例如，产品2019年6月的销售额与2019年5月的销售额相比增加的部分。


同比增长指的是本期数据与上一年度或历史同期相比的增长，例如，产品2019年6月的销售额与2018年6月的销售额相比增加的部分。
以下语句统计了各种产品每个月的环比增长率、同比增长率：

SELECT
	PRODUCT,
	YM,
	AMOUNT,
	LEAD(AMOUNT,1) OVER(PARTITION BY PRODUCT ORDER BY YM) AS AMOUNT_LEAD,--获取下条数据金额
	LAG(AMOUNT,1) OVER(PARTITION BY PRODUCT ORDER BY YM) AS AMOUNT_LAG,--获取上条数据金额
	ROUND(
				(AMOUNT-LAG(AMOUNT,1) OVER(PARTITION BY PRODUCT ORDER BY YM))
					/
				LAG(AMOUNT,1) OVER(PARTITION BY PRODUCT ORDER BY YM)*100,2
				) AS AMOUNT_HB,--同上一月环比
	LAG(AMOUNT,12) OVER(PARTITION BY PRODUCT ORDER BY YM)AS AMOUNT_QN,--获取上12条数据金额
	ROUND(
				(AMOUNT-LAG(AMOUNT,12) OVER(PARTITION BY PRODUCT ORDER BY YM))
					/
				LAG(AMOUNT,12) OVER(PARTITION BY PRODUCT ORDER BY YM)*100,2
				) AS AMOUNT_TB--同去年同比
FROM
	SALES_MONTHLY

2.复合增长率
复合增长率是第N期的数据除以第一期的基准数据，然后开N-1次方再减去1得到的结果。

假如2018年的产品销售额为10000，2019年的产品销售额为12500，2020年的产品销售额为15000。那么这两年的复合增长率的计算方式如下：

以年度为单位计算的复合增长率被称为年均复合增长率，以月度为单位计算的复合增长率被称为月均复合增长率。

以下查询统计了自2018年1月以来不同产品的月均销售额复合增长率:

SELECT 
	PRODUCT,
	YM,
	AMOUNT,
	FIRST_AMOUNT,
	ROW_NUMBER,
	ROUND((POWER(AMOUNT/FIRST_AMOUNT,1/NULLIF(ROW_NUMBER-1,0))-1)*100,2) AS FHZZL--复合增长率
FROM 
(

	SELECT
		PRODUCT,
		YM,
		AMOUNT,
		FIRST_VALUE(AMOUNT) OVER (PARTITION BY PRODUCT ORDER BY YM) AS FIRST_AMOUNT,--获取窗口内的第一个值
		ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY YM)AS ROW_NUMBER  --ROW_NUMBER排序
	FROM
		SALES_MONTHLY 
)

3.最高，最低，第二高金额及月份 

SELECT
		PRODUCT,
		YM,
		AMOUNT,
		FIRST_VALUE(YM) OVER (PARTITION BY PRODUCT ORDER BY AMOUNT DESC) AS FIRST_YM,--获取窗口内的第一个值
		FIRST_VALUE(AMOUNT) OVER (PARTITION BY PRODUCT ORDER BY AMOUNT DESC) AS FIRST_AMOUNT,--获取窗口内的第一个值
		LAST_VALUE(YM) OVER (PARTITION BY PRODUCT ORDER BY AMOUNT DESC) AS LAST_YM,--获取窗口内的第一个值
		LAST_VALUE(AMOUNT) OVER (PARTITION BY PRODUCT ORDER BY AMOUNT DESC) AS LAST_AMOUNT,--获取窗口内的第一个值
		NTH_VALUE(YM,2) OVER (PARTITION BY PRODUCT ORDER BY AMOUNT DESC) AS NTH_YM,--获取窗口内的第2个值
		NTH_VALUE(AMOUNT,2) OVER (PARTITION BY PRODUCT ORDER BY AMOUNT DESC) AS NTH_AMOUNT--获取窗口内的第2个值
FROM SALES_MONTHLY

3.4连续7天登录用户查询

需求：现有用户登录日志表，请查找出最近30天内连续7天登录的用户。

CREATE TABLE `user_login` (
  `name` varchar(64)  COMMENT '姓名',
  `login_time` datetime  COMMENT '登录时间'
) ;

分析 ：

1.首先按照姓名和登录日期进行去重，保证每个用户每天只有一条记录。

SELECT 
	NAME,
	DATE ( login_time ) AS login_date 
FROM
	user_info 
WHERE
	login_time >= CURDATE() - INTERVAL 30 DAY 
GROUP BY
	NAME,
	login_date

2.然后使用row_number()函数进行窗口排名，使用 登录日期-排名序号进行操作，当返回时间一致时，代表此数据连续登录。

SELECT 
	NAME,
	login_date,
	DATE_SUB( login_date, INTERVAL ROW_NUMBER() over ( PARTITION BY NAME ORDER BY login_date ASC ) DAY ) AS login_date_deff,
	ROW_NUMBER() over ( PARTITION BY NAME ORDER BY login_date ASC ) rank1 
FROM 
    第一步临时表

3.对姓名和处理后时间进行分组，当分组后计数>=7时，代表连续七天登录

SELECT 
	NAME,
	login_date_deff,
	count( login_date_deff ) num 
FROM
    第二步临时表 
GROUP BY
	NAME,
	login_date_deff 
HAVING
	num >=7

汇总如下：

-- 对姓名和处理后时间进行分组，当分组后计数>=7时，代表连续七天登录
SELECT 	
	NAME,-- 姓名
	login_date_deff,-- 日期差
	count( login_date_deff ) num, -- 连续登录天数
	min(login_date) start_date,  -- 开始登录日期
	max(login_date) end_date -- 结束登录日期
FROM
	(-- 使用row_number()函数进行窗口排名，使用 登录日期-排名序号进行操作，当返回时间一致时，代表此数据连续登录
	SELECT
		NAME,
        login_date, 
		DATE_SUB( login_date, INTERVAL ROW_NUMBER() over ( PARTITION BY NAME ORDER BY login_date ASC ) DAY ) AS login_date_deff,
		ROW_NUMBER() over ( PARTITION BY NAME ORDER BY login_date ASC ) rank1 
	FROM
		( -- 按姓名和登录日期进行去重，保证每个用户每天只有一条记录
		SELECT 
			NAME, 
			DATE ( login_time ) AS login_date 
		FROM 
			user_info 
		WHERE 
			login_time >= CURDATE() - INTERVAL 30 DAY 
		GROUP BY 
			NAME, 
			login_date 
		) a 
	) b
GROUP BY
	NAME,
	login_date_deff 
HAVING
	num >=3

3.5求各区域下商品销售top3

 求各区域下商品销售top3，备注显示并且额外显示出每个产品的第一热销城市及销售百分比，第二热销城市及销售百分比，超过两个城市显示其他及销售百分比，数据格式如下:
  Region1    Product12    584    1     City20：29.72% ,City1：28.17% ,其他：42.11%
  Region1    Product27    426    2     City12：33.52% ,City3：21.59% ,其他：44.89%
  Region1    Product34    408    3     City7：50.00% ,City9：50.00% ,其他：0.00%

/* 求各区域下商品销售top3，备注显示并且额外显示出每个产品的第一热销城市及销售百分比，
 第二热销城市及销售百分比，超过两个城市显示其他及销售百分比，数据格式如下
  Region1	Product12	584	1	 City20：29.72% ,City1：28.17% ,其他：42.11%
  Region1	Product27	426	2	 City12：33.52% ,City3：21.59% ,其他：44.89%
  Region1	Product34	408	3	 City7：50.00% ,City9：50.00% ,其他：0.00%
*/
-- 判断是否为空并进行拼接操作
SELECT
	region,
	product_name,
	total_sales,
	ROW_NUMBER() over ( PARTITION BY region ORDER BY total_sales DESC ) AS sales_rank, 
	CONCAT(
		IFNULL( first_city, '' ),
	IF
		( second_city IS NOT NULL, CONCAT( ' ,', second_city ), '' ),
	IF
		( other_percentage > 0, CONCAT( ' ,其他：', FORMAT( other_percentage, 2 ), '%' ), '' ) 
	) AS city_percentage_info 
FROM
	(-- 判断是否为空并进行拼接操作，并计算其他城市销售额
	SELECT
		region,
		product_name,
		total_sales,
		MAX( CASE WHEN city_rank = 1 THEN CONCAT( city_name, '：', FORMAT( percentage, 2 ), '%' ) END ) AS first_city,
		MAX( CASE WHEN city_rank = 2 THEN CONCAT( city_name, '：', FORMAT( percentage, 2 ), '%' ) END ) AS second_city,
		SUM( CASE WHEN city_rank > 2 THEN percentage ELSE 0 END ) AS other_percentage 
	FROM
		(-- 根据区域内商品top3过滤城市并计算每个城市商品占比
		SELECT
			c.region,
			c.product_name,
			c.total_sales,
			d.city_name,
			d.sales_quantity,
		IF
			( c.total_sales = 0, 0,( D.sales_quantity / C.total_sales ) * 100 ) AS percentage,
			ROW_NUMBER() OVER ( PARTITION BY c.region, c.product_name ORDER BY d.sales_quantity DESC ) AS city_rank 
		FROM
			(-- 获取区域内商品top3
			SELECT
				* 
			FROM
				(-- 按照区域进行row_number分组按照销售进进行排序
				SELECT
					region,
					product_name,
					total_sales,
					ROW_NUMBER() over ( PARTITION BY region ORDER BY total_sales DESC ) AS sales_rank 
				FROM
					( -- 按照区域商品进行分组计算总销售额
					SELECT 
						region, 
						product_name, 
						sum( sales_quantity ) AS total_sales 
					FROM 
						sales_data 
					GROUP BY 
						region, 
						product_name 
					) a 
				) b 
			WHERE
				sales_rank <= 3 
			) C
			JOIN sales_data d ON c.region = d.region 
			AND c.product_name = d.product_name 
		) E 
	GROUP BY
		region,
		product_name,
	total_sales 
	)F;



CREATE TABLE if not EXISTS `sales_data` (
  `region` varchar(255) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `city_name` varchar(255) DEFAULT NULL,
  `sales_quantity` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

附建表语句

--员工信息表
CREATE TABLE employee
    ( emp_id    NUMBER
    , emp_name  VARCHAR2(50) NOT NULL
    , sex       VARCHAR2(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR2(100) NOT NULL
  , comments  VARCHAR2(500)
  , create_by VARCHAR2(50) NOT NULL
  , create_ts TIMESTAMP NOT NULL
  , update_by VARCHAR2(50) 
  , update_ts TIMESTAMP
    ) ;
COMMENT ON TABLE employee IS '员工信息表';
COMMENT ON COLUMN employee.emp_id IS '员工编号，自增主键';
COMMENT ON COLUMN employee.emp_name IS '员工姓名';
COMMENT ON COLUMN employee.sex IS '性别';
COMMENT ON COLUMN employee.dept_id IS '部门编号';
COMMENT ON COLUMN employee.manager IS '上级经理';
COMMENT ON COLUMN employee.hire_date IS '入职日期';
COMMENT ON COLUMN employee.job_id IS '职位编号';
COMMENT ON COLUMN employee.salary IS '月薪';
COMMENT ON COLUMN employee.bonus IS '年终奖金';
COMMENT ON COLUMN employee.email IS '电子邮箱';
COMMENT ON COLUMN employee.comments IS '备注信息';
COMMENT ON COLUMN employee.create_by IS '创建者';
COMMENT ON COLUMN employee.create_ts IS '创建时间';
COMMENT ON COLUMN employee.update_by IS '修改者';
COMMENT ON COLUMN employee.update_ts IS '修改时间';
 
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (1,'刘备', '男', 1, NULL, DATE '2000-01-01', 1, 30000, 10000, 'liubei@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (2,'关羽', '男', 1, 1, DATE '2000-01-01', 2, 26000, 10000, 'guanyu@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (3,'张飞', '男', 1, 1, DATE '2000-01-01', 2, 24000, 10000, 'zhangfei@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (4,'诸葛亮', '男', 2, 1, DATE '2006-03-15', 3, 24000, 8000, 'zhugeliang@shuguo.com', NULL, 'Admin', TIMESTAMP '2006-03-15 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (5,'黄忠', '男', 2, 4, DATE '2008-10-25', 4, 8000, NULL, 'huangzhong@shuguo.com', NULL, 'Admin', TIMESTAMP '2008-10-25 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (6,'魏延', '男', 2, 4, DATE '2007-04-01', 4, 7500, NULL, 'weiyan@shuguo.com', NULL, 'Admin', TIMESTAMP '2007-04-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (7,'孙尚香', '女', 3, 1, DATE '2002-08-08', 5, 12000, 5000, 'sunshangxiang@shuguo.com', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (8,'孙丫鬟', '女', 3, 7, DATE '2002-08-08', 6, 6000, NULL, 'sunyahuan@shuguo.com', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (9,'赵云', '男', 4, 1, DATE '2005-12-19', 7, 15000, 6000, 'zhaoyun@shuguo.com', NULL, 'Admin', TIMESTAMP '2005-12-19 10:00:00', 'Admin', TIMESTAMP '2006-12-31 10:00:00');
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (10,'廖化', '男', 4, 9, DATE '2009-02-17', 8, 6500, NULL, 'liaohua@shuguo.com', NULL, 'Admin', TIMESTAMP '2009-02-17 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (11,'关平', '男', 4, 9, DATE '2011-07-24', 8, 6800, NULL, 'guanping@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-07-24 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (12,'赵氏', '女', 4, 9, DATE '2011-11-10', 8, 6600, NULL, 'zhaoshi@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-11-10 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (13,'关兴', '男', 4, 9, DATE '2011-07-30', 8, 7000, NULL, 'guanxing@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-07-30 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (14,'张苞', '男', 4, 9, DATE '2012-05-31', 8, 6500, NULL, 'zhangbao@shuguo.com', NULL, 'Admin', TIMESTAMP '2012-05-31 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (15,'赵统', '男', 4, 9, DATE '2012-05-03', 8, 6000, NULL, 'zhaotong@shuguo.com', NULL, 'Admin', TIMESTAMP '2012-05-03 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (16,'周仓', '男', 4, 9, DATE '2010-02-20', 8, 8000, NULL, 'zhoucang@shuguo.com', NULL, 'Admin', TIMESTAMP '2010-02-20 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (17,'马岱', '男', 4, 9, DATE '2014-09-16', 8, 5800, NULL, 'madai@shuguo.com', NULL, 'Admin', TIMESTAMP '2014-09-16 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (18,'法正', '男', 5, 2, DATE '2017-04-09', 9, 10000, 5000, 'fazheng@shuguo.com', NULL, 'Admin', TIMESTAMP '2017-04-09 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (19,'庞统', '男', 5, 18, DATE '2017-06-06', 10, 4100, 2000, 'pangtong@shuguo.com', NULL, 'Admin', TIMESTAMP '2017-06-06 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (20,'蒋琬', '男', 5, 18, DATE '2018-01-28', 10, 4000, 1500, 'jiangwan@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-01-28 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (21,'黄权', '男', 5, 18, DATE '2018-03-14', 10, 4200, NULL, 'huangquan@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-03-14 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (22,'糜竺', '男', 5, 18, DATE '2018-03-27', 10, 4300, NULL, 'mizhu@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-03-27 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (23,'邓芝', '男', 5, 18, DATE '2018-11-11', 10, 4000, NULL, 'dengzhi@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-11-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (24,'简雍', '男', 5, 18, DATE '2019-05-11', 10, 4800, NULL, 'jianyong@shuguo.com', NULL, 'Admin', TIMESTAMP '2019-05-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (25,'孙乾', '男', 5, 18, DATE '2018-10-09', 10, 4700, NULL, 'sunqian@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-10-09 10:00:00', NULL, NULL);






-- 创建销量表sales_monthly
-- product表示产品名称，ym表示年月，amount表示销售金额（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);
 
 
 
 
-- 创建银行交易日志表transfer_log
-- Oracle、MySQL、PostgreSQL以及SQLite
CREATE TABLE transfer_log
( log_id    INTEGER NOT NULL PRIMARY KEY, -- 交易日志编号
  log_ts    TIMESTAMP NOT NULL, -- 交易时间
  from_user VARCHAR(50) NOT NULL, -- 交易发起账号
  to_user   VARCHAR(50), -- 交易接收账号
  type      VARCHAR(10) NOT NULL, -- 交易类型
  amount    NUMERIC(10) NOT NULL -- 交易金额（元）
);
 
-- 生成测试数据
-- Oracle 需要执行以下ALTER语句
ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (1,'2021-01-02 10:31:40','62221234567890',NULL,'存款',50000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (2,'2021-01-02 10:32:15','62221234567890',NULL,'存款',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (3,'2021-01-03 08:14:29','62221234567890','62226666666666','转账',200000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (4,'2021-01-05 13:55:38','62221234567890','62226666666666','转账',150000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (5,'2021-01-07 20:00:31','62221234567890','62227777777777','转账',300000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (6,'2021-01-09 17:28:07','62221234567890','62227777777777','转账',500000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (7,'2021-01-10 07:46:02','62221234567890','62227777777777','转账',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (8,'2021-01-11 09:36:53','62221234567890',NULL,'存款',40000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (9,'2021-01-12 07:10:01','62221234567890','62228888888881','转账',10000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (10,'2021-01-12 07:11:12','62221234567890','62228888888882','转账',8000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (11,'2021-01-12 07:12:36','62221234567890','62228888888883','转账',5000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (12,'2021-01-12 07:13:55','62221234567890','62228888888884','转账',6000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (13,'2021-01-12 07:14:24','62221234567890','62228888888885','转账',7000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (14,'2021-01-21 12:11:16','62221234567890','62228888888885','转账',70000);




--部门表
CREATE TABLE department
    ( dept_id    NUMBER
    , dept_name  VARCHAR2(50) NOT NULL
    ) ;
COMMENT ON TABLE department IS '部门信息表';
COMMENT ON COLUMN department.dept_id IS '部门编号，自增主键';
COMMENT ON COLUMN department.dept_name IS '部门名称';
 
 
-- 生成测试数据
INSERT INTO department(DEPT_ID,dept_name) VALUES (1,'行政管理部');
INSERT INTO department(DEPT_ID,dept_name) VALUES (2,'人力资源部');
INSERT INTO department(DEPT_ID,dept_name) VALUES (3,'财务部');
INSERT INTO department(DEPT_ID,dept_name) VALUES (4,'研发部');
INSERT INTO department(DEPT_ID,dept_name) VALUES (5,'销售部');
INSERT INTO department(DEPT_ID,dept_name) VALUES (6,'保卫部');
 
 
 
 
 
--员工信息表
CREATE TABLE employee
    ( emp_id    NUMBER
    , emp_name  VARCHAR2(50) NOT NULL
    , sex       VARCHAR2(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR2(100) NOT NULL
  , comments  VARCHAR2(500)
  , create_by VARCHAR2(50) NOT NULL
  , create_ts TIMESTAMP NOT NULL
  , update_by VARCHAR2(50) 
  , update_ts TIMESTAMP
    ) ;
COMMENT ON TABLE employee IS '员工信息表';
COMMENT ON COLUMN employee.emp_id IS '员工编号，自增主键';
COMMENT ON COLUMN employee.emp_name IS '员工姓名';
COMMENT ON COLUMN employee.sex IS '性别';
COMMENT ON COLUMN employee.dept_id IS '部门编号';
COMMENT ON COLUMN employee.manager IS '上级经理';
COMMENT ON COLUMN employee.hire_date IS '入职日期';
COMMENT ON COLUMN employee.job_id IS '职位编号';
COMMENT ON COLUMN employee.salary IS '月薪';
COMMENT ON COLUMN employee.bonus IS '年终奖金';
COMMENT ON COLUMN employee.email IS '电子邮箱';
COMMENT ON COLUMN employee.comments IS '备注信息';
COMMENT ON COLUMN employee.create_by IS '创建者';
COMMENT ON COLUMN employee.create_ts IS '创建时间';
COMMENT ON COLUMN employee.update_by IS '修改者';
COMMENT ON COLUMN employee.update_ts IS '修改时间';
 
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (1,'刘备', '男', 1, NULL, DATE '2000-01-01', 1, 30000, 10000, 'liubei@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (2,'关羽', '男', 1, 1, DATE '2000-01-01', 2, 26000, 10000, 'guanyu@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (3,'张飞', '男', 1, 1, DATE '2000-01-01', 2, 24000, 10000, 'zhangfei@shuguo.com', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (4,'诸葛亮', '男', 2, 1, DATE '2006-03-15', 3, 24000, 8000, 'zhugeliang@shuguo.com', NULL, 'Admin', TIMESTAMP '2006-03-15 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (5,'黄忠', '男', 2, 4, DATE '2008-10-25', 4, 8000, NULL, 'huangzhong@shuguo.com', NULL, 'Admin', TIMESTAMP '2008-10-25 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (6,'魏延', '男', 2, 4, DATE '2007-04-01', 4, 7500, NULL, 'weiyan@shuguo.com', NULL, 'Admin', TIMESTAMP '2007-04-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (7,'孙尚香', '女', 3, 1, DATE '2002-08-08', 5, 12000, 5000, 'sunshangxiang@shuguo.com', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (8,'孙丫鬟', '女', 3, 7, DATE '2002-08-08', 6, 6000, NULL, 'sunyahuan@shuguo.com', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (9,'赵云', '男', 4, 1, DATE '2005-12-19', 7, 15000, 6000, 'zhaoyun@shuguo.com', NULL, 'Admin', TIMESTAMP '2005-12-19 10:00:00', 'Admin', TIMESTAMP '2006-12-31 10:00:00');
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (10,'廖化', '男', 4, 9, DATE '2009-02-17', 8, 6500, NULL, 'liaohua@shuguo.com', NULL, 'Admin', TIMESTAMP '2009-02-17 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (11,'关平', '男', 4, 9, DATE '2011-07-24', 8, 6800, NULL, 'guanping@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-07-24 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (12,'赵氏', '女', 4, 9, DATE '2011-11-10', 8, 6600, NULL, 'zhaoshi@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-11-10 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (13,'关兴', '男', 4, 9, DATE '2011-07-30', 8, 7000, NULL, 'guanxing@shuguo.com', NULL, 'Admin', TIMESTAMP '2011-07-30 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (14,'张苞', '男', 4, 9, DATE '2012-05-31', 8, 6500, NULL, 'zhangbao@shuguo.com', NULL, 'Admin', TIMESTAMP '2012-05-31 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (15,'赵统', '男', 4, 9, DATE '2012-05-03', 8, 6000, NULL, 'zhaotong@shuguo.com', NULL, 'Admin', TIMESTAMP '2012-05-03 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (16,'周仓', '男', 4, 9, DATE '2010-02-20', 8, 8000, NULL, 'zhoucang@shuguo.com', NULL, 'Admin', TIMESTAMP '2010-02-20 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (17,'马岱', '男', 4, 9, DATE '2014-09-16', 8, 5800, NULL, 'madai@shuguo.com', NULL, 'Admin', TIMESTAMP '2014-09-16 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (18,'法正', '男', 5, 2, DATE '2017-04-09', 9, 10000, 5000, 'fazheng@shuguo.com', NULL, 'Admin', TIMESTAMP '2017-04-09 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (19,'庞统', '男', 5, 18, DATE '2017-06-06', 10, 4100, 2000, 'pangtong@shuguo.com', NULL, 'Admin', TIMESTAMP '2017-06-06 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (20,'蒋琬', '男', 5, 18, DATE '2018-01-28', 10, 4000, 1500, 'jiangwan@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-01-28 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (21,'黄权', '男', 5, 18, DATE '2018-03-14', 10, 4200, NULL, 'huangquan@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-03-14 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (22,'糜竺', '男', 5, 18, DATE '2018-03-27', 10, 4300, NULL, 'mizhu@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-03-27 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (23,'邓芝', '男', 5, 18, DATE '2018-11-11', 10, 4000, NULL, 'dengzhi@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-11-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (24,'简雍', '男', 5, 18, DATE '2019-05-11', 10, 4800, NULL, 'jianyong@shuguo.com', NULL, 'Admin', TIMESTAMP '2019-05-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (25,'孙乾', '男', 5, 18, DATE '2018-10-09', 10, 4700, NULL, 'sunqian@shuguo.com', NULL, 'Admin', TIMESTAMP '2018-10-09 10:00:00', NULL, NULL);


-- 创建销量表sales_monthly
-- product表示产品名称，ym表示年月，amount表示销售金额（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);

深入解析PostgreSQL中的PL/pgSQL语法
谦谦均的博客
11-20 1630
在数据库管理系统中，PostgreSQL因其强大的功能和稳定性而受到广泛欢迎。其中，PL/pgSQL作为PostgreSQL的过程化语言，为用户提供了更为灵活和强大的编程能力。本文将深入解析PL/pgSQL的语法，帮助读者更好地掌握这门语言，从而在实际开发中更加得心应手。
32.flink sql 语法大全
笔生花的博客
10-09 4643
flink table api 之 time Attributes, which supports standard ANSI SQL(ANSI SQL是sql语言标准).Calcite 是什么？如果用一句话形容 Calcite，Calcite 是一个用于优化异构数据源的查询处理的基础框架。最近十几年来，出现了很多专门的数据处理引擎。例如列式存储 (HBase)、流处理引擎 (Flink)、文档搜索引擎 (Elasticsearch) 等等。
PostgreSQL中 类似于Mysql的replace into的功能
qq_34471241的博客
04-24 1942
使用 `INSERT INTO ... ON CONFLICT` 语法实现插入新记录或更新现有记录。
PL/pgSQL 语法
hyoka的博客
07-05 690
函数体：函数体就是简单的一个字符串。在写函数体时，使用美元符号引用（使用$xxx$包括起来）通常比使用普通单引号语法更有帮助。如果没有美元引用，函数体中的任何单引号或者反斜线必须通过双写来转义。 块结构:PL/pgSQL函数体的外部有一个隐藏的“外层块”包围着。这个块提供了该函数参数（如果有）的声明,其参数和特殊变量可以用该函数的名称限定。 [ <<label>> ] [ DECLARE declarations ] BEGIN statements END [ la
pgsql基础语法
griefed的博客
12-12 2900
postgres 基础语法 1.创建表 例如：使用Create Table 语句创建表 student , sc, course CREATE TABLE student( Sno char(6) PRIMARY KEY, Sname VARCHAR(8), Ssex CHAR(2), Sage INTEGER, Sdept VARCHAR(15) ); CREATE TABLE COURSE(...
PGSQL语法整理
tswddd的博客
01-06 222
注意alias在表格前面。会自动识别数据类型。注意：只适用于PGSQL。
PGSQL（一）SQL语法介绍
zyl042的博客
10-24 1万+
SQL一般分为DQL，DML，DDL： DQL：数据查询语句，SELECT DML：数据操作语言，INSERT，UPDATE，DELETE DDL：数据定义语言，主要用于创建 删除，以及修改表 索引等数据库对象语言 DDL： 创建 CREATE TABLE table_name{ col01_name data_type, col02_name data_type, col...
DB2数据库SQL语法参考手册中文最新版本
11-22
DB2数据库SQL语法参考手册中文最新版本是数据库管理领域中一本重要的参考资料，它详细介绍了DB2数据库系统中SQL语言的使用方法和语法结构。SQL语言是一种标准化的查询和程序设计语言，广泛应用于各种关系型数据库...
利用Java理解sql的语法(实例讲解)
08-28
在本文中，我们将深入探讨如何利用Java来理解SQL语法，特别是通过实例来解析SQL的查询结构。SQL（Structured Query Language）是一种用于管理关系数据库的标准语言，而Java则是一种广泛使用的编程语言，它允许我们与...
数据分析领域SQL核心语法详解：从创建数据库到聚合函数的实战指南
最新发布
03-19
为了提高在数据分析中的SQL技能，应首先掌握基础语法。几乎所有的数据库的SQL语法都是相 似的，甚至现在做大数据分析用到的Hive SQL，其语法也基本和SQL一样，所以学会SQL基本 上就掌握了所有数据库的取数方法。 ...
Sql Server 语法
04-10
本文主要讨论的是关于`Sql Server`的语法，重点集中在创建、修改和删除表结构的操作上。 首先，我们来看创建表结构的`CREATE TABLE`语句。创建表是数据库设计的基础，它定义了表的列名、数据类型以及列的属性。基本...
PL/pgSQL 基本使用语法
aganliang的专栏
11-28 889
参考官网 介绍： PL/pgSQL is a loadable procedural language for the PostgreSQL database system. The design goals of PL/pgSQL were to create a loadable procedural language that can be used to create functions, procedures, and triggers, adds control structures t
mysql pgsql 语法_PostgreSQL数据库的sql语法（整理版）
weixin_39540934的博客
01-19 507
操作命令创建数据库create database [db_name];删除数据库drop database [db_name];查询所有数据库select datname from PG_DATABASE; /select * from PG_DATABASE;查询某一数据库的所有表select table_name FROM information_schema.tables where ta...
pgsql入门&工作中常用语法
I am your sun ❤
04-08 1666
【代码】pgsql入门&工作中常用语法。
postgresql 基本语法
热门推荐
大牛在路上的博客
12-05 1万+
postgresql 基本语法 postgresql数据库创建/修改/删除等写入类代码语法总结： 1，创建库 2，创建/删除表 　　2.1 创建表  create table myTableName 　　2.2 如果表不存在则创建表 create table if not exists myTableName 　　2.3 删除表 drop table if exists m...
postgresql_基本语法
前行
11-05 2217
1.创建数据库： CREATE DATABASE "db001" WITH ENCODING='UTF8' CONNECTION LIMIT=100 TABLESPACE=pg_default; 2.创建数据表： create table "db001.table001"(id numeric(12) PRIMARY KEY, phone numeric(12) UNIQUE NOT NUL...
PostgreSQL基础语法
五宝茶四娃的博客
05-19 7106
关联两张表查询 SELECT * FROM res_quota inner join 入库1 on res_quota.code=入库1.定额编号 where res_quota.status='0'; 关联两张表更新 update res_quota_condition a set remark ='0' from res_quota b where a.quota_id=b.id and b.status='0' 关联两张表插入 insert into res_quota_condition(id
postgresql PL/pgsql语法介绍
魂醉的一亩二分地
07-24 3984
一、概述：PL/pgSQL函数在第一次被调用时，其函数内的源代码(文本)将被解析为二进制指令树，但是函数内的表达式和SQL命令只有在首次用到它们的时候，PL/pgSQL解释器才会为其创建一个准备好的执行规划，随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中，有部分SQL命令或表达式没有被用到，那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划，这样的好处是可以有效地减少
postgreSQL语法
H_0521的博客
05-22 1729
(2) 字符串类型的数据原则上按字典的顺序排序，不能与数字的大小顺序混淆，如’1-3‘
深入掌握SQL：语法与高级进阶测验指南
而标签“SQL语法测验”进一步强调了文档内容的重点是提供SQL语法相关的练习和测试，可能涉及到各种类型的SQL查询和操作，例如基本的SELECT语句、JOIN连接查询、子查询、聚合函数、事务处理以及数据修改命令等。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值