MySQL Computed Column and windows function

 

MySQL 8 Cookbook 原文書我在Internet下載不到
只能找到中文版
今天測到一個書中的錯誤
描述到computed column
其實應該跟sql server 的computed column是相同功能
我手上的書的語法是這樣的
ALTER TABLE employees ADD hire_date_year YEAR AS (YEAR(hire_date )) VIRTUAL ;
只好上網找compute column的語法
修改為
alter table employees add hire_date_year char(4) GENERATED ALWAYS as (year(hire_date)) virtual;
怪了~~ 寫書的人,怎麼會語法差這多~~

書中提了一個windows function的語法
怎麼建怎麼失敗!!
後來仔細看了一下我抄的語法
我猜可能rank這個字是保留字
select hire_date_year, salary,
row_number() over( partition by hire_date_year order by salary desc) as rank,
employees join salaries
salaries.emp_no = employees.emp_no
order by salary desc limit 10;
果然....
我調整了語法如下:
select hire_date_year, salary,
row_number() over( partition by hire_date_year order by salary desc) as "rank"
from employees join salaries
on salaries.emp_no = employees.emp_no
order by salary desc limit 10;
+----------------+--------+------+
| hire_date_year | salary | rank |
+----------------+--------+------+
| 1985 | 158220 | 1 |
| 1985 | 157821 | 2 |
| 1986 | 156286 | 1 |
| 1985 | 155709 | 3 |
| 1987 | 155513 | 1 |
| 1985 | 155377 | 4 |
| 1985 | 155190 | 5 |
| 1985 | 154888 | 6 |
| 1985 | 154885 | 7 |
| 1985 | 154459 | 8 |
+----------------+--------+------+
10 rows in set (5.96 sec)
回看了書上的內容,它是用single qoute
不過我看看它最後的結論
用CTE改了一下語法
把各年度最高年薪排在最上頭
with salary_rank as (select hire_date_year, salary,
row_number() over( partition by hire_date_year order by salary desc) as salrank
from employees join salaries
on salaries.emp_no = employees.emp_no
order by salary desc limit 10)
select * from salary_rank order by salrank ;
+----------------+--------+---------+
| hire_date_year | salary | salrank |
+----------------+--------+---------+
| 1985 | 158220 | 1 |
| 1986 | 156286 | 1 |
| 1987 | 155513 | 1 |
| 1985 | 157821 | 2 |
| 1985 | 155709 | 3 |
| 1985 | 155377 | 4 |
| 1985 | 155190 | 5 |
| 1985 | 154888 | 6 |
| 1985 | 154885 | 7 |
| 1985 | 154459 | 8 |
+----------------+--------+---------+
10 rows in set (5.86 sec)
windows function好像是8 的new feature
不過保留字的問題
應該在其他的資料庫也一樣
只是我很少用到~~

第一个、最后一个和第n 个值
照書上的語法,first_value的產出值我看得懂
但其他的,我就一頭霧水
後來我用CTE改成我的語法之後
看了之後,就恍然大悟
之所以有partition 語法
就是為了要分群
with sal_rank as (
SELECT hire_date_year , salary , RANK() OVER w AS 'Rank',
FIRST_VALUE(salary) OVER w AS 'first',
NTH_VALUE(salary, 3) OVER w AS 'third ',
LAST_VALUE(salary) OVER w AS 'last'
FROM employees join salaries ON salaries.emp_no=employees.emp_no
WINDOW w AS (PARTITION BY hire_date_year ORDER BY salary DESC)
ORDER BY salary DESC LIMIT 10 )
select * from sal_rank order by 3;
+----------------+--------+------+--------+--------+--------+
| hire_date_year | salary | Rank | first | third | last |
+----------------+--------+------+--------+--------+--------+
| 1985 | 158220 | 1 | 158220 | NULL | 158220 |
| 1986 | 156286 | 1 | 156286 | NULL | 156286 |
| 1987 | 155513 | 1 | 155513 | NULL | 155513 |
| 1985 | 157821 | 2 | 158220 | NULL | 157821 |
| 1985 | 155709 | 3 | 158220 | 155709 | 155709 |
| 1985 | 155377 | 4 | 158220 | 155709 | 155377 |
| 1985 | 155190 | 5 | 158220 | 155709 | 155190 |
| 1985 | 154888 | 6 | 158220 | 155709 | 154888 |
| 1985 | 154885 | 7 | 158220 | 155709 | 154885 |
| 1985 | 154459 | 8 | 158220 | 155709 | 154459 |
+----------------+--------+------+--------+--------+--------+
10 rows in set (9.92 sec)

Read More

SQL Server non-Partition 表格轉換至分割表

 

最近剛好在處理複寫建置
Snapshot 初始化好像不會主動把表原分割的定義帶到訂閱端
剛好我就拿訂閱端來做測試
進行未分割表轉換為分割表的測試
我手上是有兩個情境
primary key是非叢集或叢集
對叢集PK表格
重建PK直接帶入partition scheme就OK了
而且重建的字眼是用CREATE
我那時候看到都傻眼了
想說我一定要來測看看
只是CREATE 一定得帶上DROP_EXISTING
底下這是我的語法
CREATE UNIQUE CLUSTERED INDEX PK_DEL_STAT ON [dbo].[DEL_STAT] (
[BILL] ASC,
[SEQ] ASC,
[STATUS] ASC,
[EMP] ASC,
[CREATE_DATE] ASC )
WITH (DROP_EXISTING = ON)
ON [PS_DS]([BILL]);
PS_DS 當然就是Partition Scheme
BILL是分割欄位

2.非叢集PK 表格
這就比較麻煩了
他需要三個步驟
a. 如同PK叢集一樣
先建出clustered pk 分割表
其實clustered pk本身就是表格
指定他套用分割,他就是分割表格
b. drop pk contraint
這就是把clutered 分割表轉為heap 分割表
c. 依partition scheme 建出uniqe key 非叢集index 分割
這是為了要Align 資料表的分割
指令步驟如下 :
a. 建出clustered pk 分割表
CREATE UNIQUE CLUSTERED INDEX PK_DEL_STAT ON [dbo].[DEL_STAT] (
[BILL] ASC,
[SEQ] ASC,
[STATUS] ASC,
[EMP] ASC,
[CREATE_DATE] ASC )
WITH (DROP_EXISTING = ON)
ON [PS_DS]([BILL]);
b.drop pk contraint
ALTER TABLE [dbo].[DEL_STAT] DROP CONSTRAINT PK_DEL_STAT
go
c.Align 索引與資料表的分割
CREATE UNIQUE NONCLUSTERED INDEX PK_DEL_STAT ON [dbo].[DEL_STAT] (
[BILL] ASC,
[SEQ] ASC,
[STATUS] ASC,
[EMP] ASC,
[CREATE_DATE] ASC )
WITH (DROP_EXISTING = ON)
ON [PS_DS]([BILL]);

Read More

MySQL 8 在Ubuntu 上的安裝

 

現在我連ubuntu都懶得安裝了
所以在internet上找看看
有沒有現成的vm image .......................
還真有喔~~
Ubuntu 20.04 Focal Fossa

去看info那個頁簽找帳密
osboxes/osboxes.org
切換root
sudo su -
apt-get install openssh-server
apt-get install libaio1
apt-get install libncurses5
下載MySQL 8 tar ball

為什麼用tar ball?
MySQL 8 cookbook建議使用

我也覺得它最簡便
最適合機房系統使用
因為不允許對internet的連線

root@osboxes:/usr/local# tar xvf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz
mysql-8.0.22-linux-glibc2.12-x86_64/bin/
mysql-8.0.22-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-8.0.22-linux-glibc2.12-x86_64/bin/myisamchk
mysql-8.0.22-linux-glibc2.12-x86_64/bin/myisamlog
mysql-8.0.22-linux-glibc2.12-x86_64/bin/myisampack
.

資料庫初始指令如下: 1.groupadd mysql
2.useradd -r -g mysql -s /bin/false mysql
3.ln -s mysql-8.0.22-linux-glibc2.12-x86_64 mysql
4.cd mysql
5.root@osboxes:/usr/local/mysql# mkdir mysql-files
6.root@osboxes:/usr/local/mysql# chmod 750 mysql-files
7.root@osboxes:/usr/local/mysql# chown -R mysql .
8.root@osboxes:/usr/local/mysql# chgrp -R mysql .
9.root@osboxes:/usr/local/mysql# bin/mysqld --initialize --user=mysql
2020-11-10T07:57:42.769064Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.22-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.22) initializing of server in progress as process 7113
2020-11-10T07:57:42.777843Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-10T07:57:43.402843Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-11-10T07:57:44.323808Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: r#S8h&DeiyQf

紅色字眼是初始密碼
得記起來
等一下起了instance
要透過root登入時
它會要求這個密碼

等一下起了instance
要透過root登入時
它會要求這個密碼
10. root@osboxes:/usr/local/mysql# bin/mysql_ssl_rsa_setup
11. root@osboxes:/usr/local/mysql# chown -R root .
12. root@osboxes:/usr/local/mysql# chown -R mysql data mysql-files
13. root@osboxes:/usr/local/mysql# cp support-files/mysql.server /etc/init.d/mysql
14. root@osboxes:/usr/local/mysql# export PATH=$PATH:/usr/local/mysql/bin
15. root@osboxes:/usr/local/mysql# /etc/init.d/mysql start
Starting mysql (via systemctl): mysql.service.
16.root@osboxes:/usr/local/mysql# ps -ef |grep mysql
root 7336 1 0 03:07 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/osboxes.pid
mysql 7427 7336 14 03:07 ? 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=osboxes.err --pid-file=/usr/local/mysql/data/osboxes.pid
17.root@osboxes:/usr/local/mysql# mysql -u root -p
Enter password: 輸入那組亂碼
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql; 在你還沒變更密碼前,不會讓你進行任何作業
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
18.mysql> alter user 'root'@'localhost' identified by 'xxxx';
Query OK, 0 rows affected (0.02 sec)
19.root@osboxes:/usr/local/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Read More