Window function

The window function performs calculations across a set of table rows that are somehow related to the current row. This is similar to the form of calculation performed by aggregate functions. However, unlike regular aggregation functions, the use of window functions does not group rows into a single output row. Each row maintains its own identity.

Windows functions perform aggregation processing(ranking, etc) on acquired(SELECT) data. It can be said that it is an essential function in the recent trend of big data and OLAP.

SELECT
  Start_station_name,
  Duration,
  SUM(Duration) OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS running_total
 FROM
  trip_histories
 WHERE
  Start_time < '2019-01-30'
 LIMIT 10;

SELECT
 Start_station_name,
 Duration,
 SUM(Duration) OVER (PARTITION BY Start_station_name) AS running_total,
 COUNT(Duration) OVER (PARTITION BY Start_station_name) AS running_count,
 AVG(Duration) OVER (PARTITION BY Start_station_name) AS running_avg
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

SELECT
 ROW_NUMBER() OVER (order by Start_time DESC) AS row_num,
 Start_station_name,
 Start_time,
 Duration
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

なるほどー、これは数こなさないとな~

What is CTE?

What is CTE?
– CTE is a so-called “recursive query”
– The WOTH clause has become available
 → Implemented on other RDMS(PostgreSQL, Oracle etc)
– Hierarchical queries can be written very easily
 → In the past, it had to be nested using subueries.

WITH RECURSIVE Hatoyama AS
(
 select id, last_name, first_name from family
   where last_name= "鳩山" and first_name="和夫"
 union all
   select child.id, child.last_name, child.first_name
   from family as child, Hatoyama
    where Hatoyama.id = child.parent_id
)
select * from Hatoyama;

WITH Yoshida as
(
	select id, last_name, first_name from family
	  where last_name="吉田" and parent id is null
	union all
	  select child.id, child.last_name, child.first_name
	  from family as child, Yoshida
	  where yoshida.id= child.parent_id
)
select * from Yoshida;

select文が階層化しないようにできるわけね。

mysqlのindexって何?

大量のレコードが入っているテーブルから1行のレコードを検索するのに
頭から順番に検索したら時間がかかります。
INDEXを作成すると、データテーブルとは別に検索用に最適化された状態でデータが保存されます。
このINDEXを使うことで、目的のレコードを迅速に見つけて取り出すことが可能になります。

なるほど~ データ参照か~

実際に試してみたいですね。

mysqlをwindows10に入れる

C:\mysql-56\bin>mysqld –install mysq56
Service successfully installed.

C:\mysql-56\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

きた。
eclipseでどうしても必要だと思ったが、半日かかった。

mysql varbinary

BINARY および VARBINARY 型は、CHAR および VARCHAR 型に似ていますが、非バイナリ文字列ではなく、バイナリ文字列を格納します。つまり、それらには文字の文字列ではなく、バイトの文字列が含まれています。これは、それらに文字セットがなく、ソートおよび比較は値の中のバイトの数値に基づいていることを意味します。

可変長バイナリバイト文字列
送られてきたデータをバイナリデータとして格納

CHAR型の特徴的な点は,値を格納された時に,もし文字列がテーブル作成時に指定された文字数よりも短かった場合,文字列の右側の末尾にスペースで補完

mysqlのpasswordを忘れた時

passwordが違うと表示
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

さっぱり覚えていない
ec2の初期パスワードか?
[ec2-user@ ~]$ cat /var/log/mysqld.log

[ec2-user@ ~]$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
違うらしい。。

mysqlを止めて、/etc/my.cnfを編集
[ec2-user@ ~]$ sudo service mysqld stop
Stopping mysqld: [ OK ]
[ec2-user@]$ sudo vi /etc/my.cnf

skip-grant-tables を追加

再起動して、-pなしでログイン
[ec2-user@~]$ sudo service mysqld start
Starting mysqld: [ OK ]
[ec2-user@ ~]$ mysql -u root

> UPDATE mysql.user SET authentication_string = PASSWORD(‘new password’) WHERE User = ‘root’ AND Host = ‘localhost’;
> FLUSH PRIVILEGES;

[ec2-user@]$ sudo vi /etc/my.cnf
# skip-grant-tables //コメントアウト

$ sudo service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[ec2-user@ ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

OK!ふう、焦った~

Laravelのカラム作成で使用できるデータ型

increment(‘id’)
binary(‘column’)
boolean(”)
char(”)
date(”)
time(”)
dateTime(”)
double(”)
enum(”)
integer(”)
json(”)
timestamp(”)
timestamps(”)
nullableTimestamps(”)
string(”) …varchar
string(”, )
text(”)

ということは、mysqlのid int primary key auto_incrementはincrement(‘id’)
varcharはstring(”)

あ、つまり、laravelはvarchar(‘n’)は表示する必要ないのね。
update_at、create_atはtimestamps()

tinyintはbooleanのようです。

create table profiles(
 id int primary key auto_increment,
 login_id varchar(30),
 role varchar(50),
 name varchar(20),
 password varchar(30),
 mail varchar(255),
 updated_at datetime,
 updated_person varchar(50)
);

これで行けるかな?
increment(‘id’)
string(‘login_id’)
string(‘role’)
string(‘name’)
string(‘password’)
string(‘mail’)
string(‘test_mail’)
timestamps()
string(‘updated_person’)

create tables

sqlでtableをつくっていく。いずれはconcatで結合する。

mysql> show tables;
+—————–+
| Tables_in_addb |
+—————–+
| account_lists |
| companies_lists |
| documents_lists |
| profiles |
| registers |
+—————–+
5 rows in set (0.05 sec)

– laravelで複数テーブルでやるところまで
– ER図を作れないといけない。
– mysqlだけでなく、oracleも
– concatも

mysqlの日付・時刻型

DATE: YYYY-MM-DD
TIME: HH:MM:SS
DATETIME: YYYY-MM-DD HH:MM:SS
TIMESTAMP: YYYY-MM-DD HH:MM:SS
YEAR: YYYY

なるほど、格納する値によって型も変わってくるのね♪

updated_at(更新日時)などはYYY-MM-DD HH:MM:SSで入力、
ラジオボタンなど選択肢が2択の場合は、tinyint(1)とする。
名前は日本人ならvarchar(20)で十分? まーmiddle nameがありませんからね。
会社名などもvarchar(50)で十分でしょうね。

あら、こう考えると、varchar(255)も殆ど必要なさそうな。。

emailは?
RFC 5321(Simple Mail Transfer Protocol)によると、
-ローカル部(@の前)64オクテット
-ドメイン部(@の後)255オクテット
-全体 256オクテット

よって、varchar(255)あれば十分だそうだ。

ああ、こりゃER図マスターしないとあかんな。。
ところで、 id int primary key auto_incrementって、すべてのテーブルに入れるのか?
例えば、profiles と accountsというtableがあった場合、両方 id int primary key auto_incrementとするのか、profile_id int primary key auto_increment, account_id int primary key auto_increment とした方が望ましいのか…

悩ましい。とりあえず、まずはid int primary key auto_incrementで行こう。