本頁面是使用 Elementor 來編輯的。
主要是記錄以 wordpress 為主題之網頁設計之學習心得或筆記；日記型。
Elementor 的目前使用感想，後台編輯上的反應速度較於古籐堡慢，然而風格與功能上是有所不同的故有其存在的價值。
而一般的 CSS 外掛應是不相容於它，且它的 pro 版才提供 CSS 自訂的功能。
另外第一次用它就遇到了 BUGXD，在它的左側文本編輯器視窗中使用未格式化文字，
但下次點進來，若未格式化文字是 HTML 語句，就會被自動轉譯掉很惱人XD。
只能一次性編輯來避免。
古籐堡與 Elementor 也沒辦法在同一篇文章或頁面中混用。
所以筆者預估整個網站中只在此頁面使用 Elementor。畢竟古籐堡是 wordpress 預設，考量到相容性上及尚未過度包裝。
2020／0206 媒體檔案之存取
- 在任一台電腦上都可登入後台使用，那麼開啟檔案時是從您正在使用的電腦中找尋檔案，這會是最常見的狀況。並且檔案會上傳儲存至 wordpress 目錄下的 ./uploads 底下。
- 筆者文章也提過另一做法，上傳至後台的伺服器上，再手動加入 wordpress 媒體庫。
- 設想另一個狀況是媒體檔案並不想置入 wordpress 目錄內因這樣備份出來的檔案大小會日益大增（註：可指定不備份某目錄）；那麼檔案就要放置在 wordpress 可以存取得到的／非 wordpress 備份得到，的地方。筆者的做法是會在 ./uploads 底下建立軟連結，連到另一處存放媒體檔的地方。但衍生的問題在於後台路徑與前台路徑各為何？及無統一管理而有失連風險。
做法例如：在 ./uploads 底下建立（不對，它會被我們的 caddy 規則排除），在 ./wp-content 底下建立一軟連結，例如 DATA，連結到其它新建目錄，記得權限要設定正確。那麼內部的存取路徑將會是例如：url(“/wp-content/DATA/myimage.png”)，外部就用完整的 URL 即可：https://waterfalls.ddns.net/wp-content/DATA/myimage.png - 簡之，以上三種各有適用情境。衡輕下還是交由 wordpress 管理為佳。
2020／0209 更換外觀主題
2020／0209 CSS 的學習筆記
W3Schools
本段落是從這個網站的 CSS 教學內容攫取出來的，就是筆者的筆記。。。將會持續地追加。
由於它這個編輯視窗用起來並不順手，所以筆者的做法是使用另一份 wordpress 草稿，貼在該處，學畢便複製再貼過來。
此目的：藉由每種語法的範例用法來快速查詢與取用。
/******************general*/
Inline styles, Embedded styles, External styles.
The inline styles have the highest priority, and the external style sheets have the lowest.
<element style=”attrib1:val1; attrib2:val2;”>
But it needs to be no line break between quotes.
You can attach external style sheets in two ways — linking and importing.
<head><link rel=”stylesheet” href=”css/style.css”></head>
<style>@import url(“css/style.css”);</style>
you can use the @import rule to import a style sheet within another style sheet.
All @import rules must occur at the start of the style sheet.
However, importing a style sheet within another style sheet is not recommended due to performance issue.
selector(i.e., to matched elements; case sensitive) {
/* This is a CSS comment */
property1: value;
property2: value;
…
}
universal selector: *
element-type selector: element-name
id selector: #id
class selector: .class-name or element-name.class-name
.class-name: elements declare it taken effect
element-name.class-name: elements declare it where only the element-name taken effect
descendant selector:
element-name <with descendant element-name>[ <with descendant element-name>[…]]the first element-name can also be element-name.class-name
child selector:
element-name > <with child element-name>[ > <with child element-name>[…]]the first element-name can also be element-name.class-name
child applies to any adjacent levels(e.g., l4/l5) of pattern that has the parent-child relationship(e.g., no other non-child element between them otherwise would become l4/l6)
descendant applies to any specified levels of pattern(unnecessary adjacent) that has the parent-child-or-descendant relationship
if both matched, the later declared applied.
therefore:
ul > li {
list-style: square;
}
ul li {
list-style: circle;
}
ul li covers all patterns matched.
ul li {
list-style: square;
}
ul > li {
list-style: circle;
}
ul li covers all patterns which ul > li can’t matched.
adjacent sibling selector: E1 + E2
E2 is applied where E1 and E2 are right adjacent to each other at the same level with the declared order.
general sibling selector: E1 ~ E2
E2 is applied where elements at the same level of and order come after E1
grouping selectors: E1, E2, E3, …
all listed together by comma share the same settings.
/******************color*/
<h1 style=”color:Tomato;”>Hello World</h1>
<h1 style=”color:rgb(100%, 0%, 0%);”>Hello World</h1>
<p style=”background-color:Tomato;”>Lorem ipsum…</p>
<h1 style=”border:2px solid Tomato;”>Hello World</h1>
<h1 style=”background-color:rgb(255, 99, 71);”>…</h1>
<h1 style=”background-color:#ff6347;”>…</h1>
<h1 style=”background-color:hsl(9, 100%, 64%);”>…</h1>
<h1 style=”background-color:rgba(255, 99, 71, 0.5);”>…</h1>
<h1 style=”background-color:hsla(9, 100%, 64%, 0.5);”>…</h1>
Shades of gray are often defined using equal values for all the 3 light sources.
A color can be specified using hue, saturation, and lightness (HSL) in the form:hsl(hue, saturation, lightness).
Hue is a degree on the color wheel from 0 to 360. 0 is red, 120 is green, and 240 is blue. Saturation is a percentage value, 0% means a shade of gray, and 100% is the full color. Lightness is also a percentage, 0% is black, 50% is neither light or dark, 100% is white. Shades of gray are often defined by setting the hue and saturation to 0, and adjust the lightness from 0% to 100% to get darker/lighter shades.
/******************background*/
body {
background-image: url(“paper.gif”);
}
By default, the background-image property repeats an image both horizontally and vertically.
body {
background-image: url(“gradient_bg.png”);
background-repeat: repeat-x;
}
body {
background-image: url(“img_tree.png”);
background-repeat: no-repeat;
}
body {
background-image: url(“img_tree.png”);
background-repeat: no-repeat;
background-position: right top; /*default left top*/
}
body {
background-image: url(“img_tree.png”);
background-repeat: no-repeat;
background-position: right top;
margin-right: 200px;
}
In this example we have also added a margin on the right side, so the background image will never disturb the text.
body {
background-image: url(“img_tree.png”);
background-repeat: no-repeat;
background-position: right top;
background-attachment: fixed;
}
The background-attachment property specifies whether the background image should scroll or be fixed (will not scroll with the rest of the page) against viewport.
body {
background-image: url(“img_tree.png”);
background-repeat: no-repeat;
background-position: right top;
background-attachment: scroll;
}
The shorthand property for background is background
body {
background: #ffffff url(“img_tree.png”) no-repeat right top;
}
When using the shorthand property the order of the property values is:
background-color
background-image
background-repeat
background-attachment
background-position
/******************margin*/
p {
margin-top: 100px;
margin-bottom: 100px;
margin-right: 150px;
margin-left: 80px;
}
The margin property is a shorthand property
/******************padding*/
div {
padding-top: 50px;
padding-right: 30px;
padding-bottom: 50px;
padding-left: 80px;
}
The padding property is a shorthand property
So, if an element has a specified width, the padding added to that element will be added to the total width of the element. This is often an undesirable result.
[fixed box:]To keep the width at 300px, no matter the amount of padding, you can use the box-sizing property. This causes the element to maintain its width; if you increase the padding, the available content space will decrease.
Use the box-sizing property to keep the width at 300px, no matter the amount of padding:
div {
width: 300px;
padding: 25px;
box-sizing: border-box;
}
div {
height: 200px;
width: 50%;
background-color: powderblue;
}
auto – This is default. The browser calculates the height and width
length – Defines the height/width in px, cm etc.
% – Defines the height/width in percent of the containing block
initial – Sets the height/width to its default value
inherit – The height/width will be inherited from its parent value
Remember that the height and width properties do not include padding, borders, or margins! They set the height/width of the area inside the padding, border, and margin of the element!
The max-width property is used to set the maximum width of an element.(means it can be auto-smaller)
/******************text*/
The commonly used text properties are: text-align, text-decoration, text-transform, text-indent, line-height, letter-spacing, word-spacing, and more which control over the visual appearance of the characters, words, spaces, and so on.
body {
color: blue;
}
h1 {
text-align: center; /*left, right, center, justify*/
}
A text can be left or right aligned, centered, or justified.
a {
text-decoration: none; /*underline, overline, line-through, none*/
}
h1 {
text-decoration: overline;
}
h2 {
text-decoration: line-through;
}
h3 {
text-decoration: underline;
}
a {
text-decoration: none;
border-bottom: 1px dotted;
}
a:hover {
border-bottom: none;
}
p.uppercase {
text-transform: uppercase;
}
p.lowercase {
text-transform: lowercase;
}
p.capitalize {
text-transform: capitalize;
}
p {
text-indent: 50px; /*set the indentation of the first line of text within a block of text*/
}
h1 {
letter-spacing: 3px; /*a length value indicates spacing in addition to the default inter-character space*/
}
h2 {
letter-spacing: -3px;
}
h1 {
word-spacing: 10px;
}
h2 {
word-spacing: -5px;
}
p.three {
word-spacing: -20px;
white-space: pre; /*preserve white space*/
/*white space still affected even preserved*/
}
p.small {
line-height: 0.8;
/*The line-height property sets the height between lines of text. When the value is a number, the line height is calculated by multiplying the element’s font size by the number. While, percentage values are relative to the element’s font size.*/
/*If the value of the line-height property is greater than the value of the font-size for a text, their difference(called the “leading”) is cut in half(called the “half-leading”) and distributed evenly on the top and bottom around the text(i.e., half over text-top and half under text-bottom)*/
}
p {
direction: rtl;
}
h1 {
text-shadow: 3px 2px red;
}
the position of the horizontal shadow (3px), the position of the vertical shadow (2px) and the color of the shadow (red)
/******************font*/
The font properties are: font-family, font-style, font-weight, font-size, and font-variant.
You should end the list with a generic font family which are five — serif, sans-serif, monospace, cursive and fantasy.
p {
font-family: “Times New Roman”, Times, serif;
}
p.normal {
font-style: normal;
}
p.italic {
font-style: italic;
}
p {
font-size: 40px;
}
h1 {
font-size: 2.5em; /* 40px/16=2.5em */
}
Defining the font sizes in pixel is not considered very accessible, because the user cannot change the font size from the browser settings.
In the example above, the text size in em is the same as the previous example in pixels. However, with the em size, it is possible to adjust the text size in all browsers.
The em unit refers to the font size of the parent element. When defining the font-size property, 1em is equal to the size of the font that applies to the parent of the element. So, if you set a font-size of 20px on the body element, then 1em = 20px and 2em = 40px. However, if you haven’t set the font size anywhere on the page, then it is the browser default, which is normally 16px and equals 100%. To simplify this, a popular technique is to set the font-size for the body element to 62.5% (that is 62.5% of the default 16px), This way 10px = 1em, 12px = 1.2em, 14px = 1.4em, 16px = 1.6em, and so on.
Another solution that works in all browsers, is to set a default font-size in percent for the <body> element:
body {
font-size: 100%;
}
h1 {
font-size: 2.5em;
}
h2 {
font-size: 1.875em;
}
p {
font-size: 0.875em;
}
To make things even more simpler CSS3 has introduced rem unit (short for “root em”) which is always relative to the font-size of the root element (html).
Viewport is the browser window size. 1vw = 1% of viewport width. If the viewport is 50cm wide, 1vw is 0.5cm.
The font sizes can be specified using viewport units such as vw or vh.
Viewport units refer to a percentage of the browser’s viewport dimensions, where 1vw = 1% of viewport width, and 1vh = 1% of viewport height. Hence, if the viewport is 1600px wide, 1vw is 16px.
However, there is a problem with the viewport units. On small screens fonts become so small that they are hardly readable. To prevent this you can utilize CSS calc() function, like this:
html {
font-size: calc(1em + 1vw);
}
h1 {
font-size: 3rem;
}
In this example, even if the viewport width becomes 0, the font-size would be at least 1em or 16px. And benifits larger/versatile viewport width as well.
The font-weight property can take one of the following values: normal, bold, bolder, lighter, 100, 200, 300, 400, 500, 600, 700, 800, 900 and inherit. The numeric values 100-900 specify the font weights, where each number represents a weight greater than its predecessor. 400 is same as normal & 700 is same as bold. The bolder and lighter values are relative to the inherited font weight, while the other values such as normal and bold are absolute font weights.
p.normal {
font-weight: normal;
}
p.thick {
font-weight: bold;
}
p.normal {
font-variant: normal;
}
p.small {
font-variant: small-caps;
}
/******************link*/
/*These four states of a link can be styled differently through using the following anchor pseudo-class selectors*/
/* unvisited link */
a:link {
color: red;
}
/* visited link */
a:visited {
color: green;
}
/* mouse over link */
a:hover {
color: hotpink;
}
/* selected link */
a:active { /*define styles for links when they are being clicked*/
color: blue;
}
You can specify any CSS property you’d like to customize the style of links, just like you do with the normal text.
When setting the style for several link states, there are some order rules:
a:hover MUST come after a:link and a:visited
a:active MUST come after a:hover
/******************list*/
ul.a {
list-style-type: circle;
}
ol {
list-style-type: upper-roman;
}
ul {
list-style-image: url(‘sqpurple.gif’);
}
ul.a { /*By default, markers of each list items are positioned outside of their display boxes.*/
list-style-position: outside;
}
ul.b {
list-style-position: inside;
}
ul {
list-style-type: none;
margin: 0;
padding: 0;
}
The list-style property is a shorthand property. It is used to set all the list properties in one declaration:
ul {
list-style: square inside url(“sqpurple.gif”);
}
Anything added to the <ol> or <ul> tag, affects the entire list, while properties added to the <li> tag will affect the individual list items
li {
background: #ffe5e5;
padding: 5px;
margin-left: 35px;
}
ul li {
background: #cce5ff;
margin: 5px;
}
https://www.tutorialrepublic.com/css-tutorial/css-tables.php
/******************table*/
table {
border-collapse: collapse;
}
table, th, td {
border: 1px solid black;
}
table {
width: 100%;
}
th {
height: 50px;
}
th {
text-align: left;
}
By default, the content of <th> elements are center-aligned and the content of <td> elements are left-aligned.
td {
height: 50px;
vertical-align: bottom;
}
By default, the vertical alignment of the content in a table is middle (for both <th> and <td> elements).
To control the space between the border and the content in a table, use the padding property on <td> and <th> elements
Add the border-bottom property to <th> and <td> for horizontal dividers:
th, td {
border-bottom: 1px solid #ddd;
}
Use the :hover selector on <tr> to highlight table rows on mouse over:
tr:hover {background-color: #f5f5f5;}
For zebra-striped tables, use the nth-child() selector and add a background-color to all even (or odd) table rows:
tr:nth-child(even) {background-color: #f2f2f2;}
th {
background-color: #4CAF50;
color: white;
}
Add a container element (like <div>) with overflow-x:auto around the <table> element to make it responsive:
<div style=”overflow-x:auto;”>
<table>
… table content …
</table>
</div>
Examples of block-level elements:
<div>
<h1> – <h6>
<p>
<form>
<header>
<footer>
<section>
Examples of inline elements:
<span>
<a>
<img>
The <script> element uses display: none; as default.
A common example is making inline <li> elements for horizontal menus:
li {
display: inline;
}
Setting the display property of an element only changes how the element is displayed, NOT what kind of element it is. So, an inline element with display: block; is not allowed to have other block elements inside it.
Hiding an element can be done by setting the display property to none.
visibility:hidden; also hides an element.
However, the element will still take up the same space as before. The element will be hidden, but still affect the layout.
Setting the width of a block-level element will prevent it from stretching out to the edges of its container. Then, you can set the margins to auto, to horizontally center the element within its container. The element will take up the specified width, and the remaining space will be split equally between the two margins.
There are five different position values:
static
HTML elements are positioned static by default.
Static positioned elements are not affected by the top, bottom, left, and right properties.
relative
An element with position: relative; is positioned relative to its normal position.
Setting the top, right, bottom, and left properties of a relatively-positioned element will cause it to be adjusted away from its normal position.
fixed
An element with position: fixed; is positioned relative to the viewport, which means it always stays in the same place even if the page is scrolled. The top, right, bottom, and left properties are used to position the element.
absolute
An element with position: absolute; is positioned relative to the nearest positioned ancestor (instead of positioned relative to the viewport, like fixed).
However; if an absolute positioned element has no positioned ancestors, it uses the document body, and moves along with page scrolling.
sticky
An element with position: sticky; is positioned based on the user’s scroll position.
A sticky element toggles between relative and fixed, depending on the scroll position. It is positioned relative until a given offset position is met in the viewport – then it “sticks” in place (like position:fixed).
When elements are positioned, they can overlap other elements.
The z-index property specifies the stack order of an element (which element should be placed in front of, or behind, the others).
An element can have a positive or negative stack order.
An element with greater stack order is always in front of an element with a lower stack order.
If two positioned elements overlap without a z-index specified, the element positioned last in the HTML code will be shown on top.
The overflow property has the following values:
visible – Default. The overflow is not clipped. The content renders outside the element’s box
hidden – The overflow is clipped, and the rest of the content will be invisible
scroll – The overflow is clipped, and a scrollbar is added to see the rest of the content
auto – Similar to scroll, but it adds scrollbars only when necessary
The overflow property only works for block elements with a specified height.
The overflow-x and overflow-y properties specifies whether to change the overflow of content just horizontally or vertically (or both)
https://www.w3schools.com/css/css_float.asp
<a href=”https://www.freepik.com/free-photos-vectors/business”>Business vector created by makyzz – www.freepik.com</a>
https://developer.mozilla.org/en-US/docs/Web/CSS
https://developer.mozilla.org/en-US/docs/Learn/Getting_started_with_the_web/CSS_basics
https://developer.mozilla.org/en-US/docs/Web/CSS/Reference
https://developer.mozilla.org/en-US/docs/Learn/CSS
https://www.codeinwp.com/blog/wordpress-cheat-sheets-web-development-design/#CSS
- https://www.w3schools.com/colors/colors_names.asp
- https://www.w3schools.com/css/css_border.asp
- 列表項目 #3
2020／0216 關閉 wordpress 維護模式
當使用 wordpress 的更新選項，所有可更新的項目都會集中在此讓您選擇更新項目；進行更新時 wordpress 會進入維護模式。萬一更新失敗有可能導致一直卡在維護模式。故筆者建議欲更新 plugin，盡量在外掛選項內手動逐項更新。若上述問題發生，在 wordpress 的主／根目錄下，會有一個檔案 “.maintanence“，將它刪除便可脫離維護模式了。
2020／0308 SQL 語法
column, field, row, record
The data returned is stored in a result table, called the result-set.
SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes.
1.
SELECT <column-name1>[ AS <alias-name1>][, <column-name2>[ AS <alias-name2>]]...
FROM <table>|(<select-subset clause>)
[WHERE <specifying-conditions>][ORDER BY <sort-conditions{ |ASC|DESC}>][<surfix>];
1.1. column-name:
1.1.1. [name]1.1.2. [*]1.1.3. [column-names of operations]
1.2. specific-conditions clause: names' logical operations
1.2.1. relational ops: =, <, >, <=, >=, !=, <>
1.2.2. logical ops: AND, OR, NOT （優先權低於上列）
1.2.3. LIKE op:
1.2.3.1. % 任意長度字串包含""
1.2.3.2. _ 任意單個字元
1.2.3.3. [...] 此列舉中的某一字元
1.2.3.4. [^...] 皆非此列舉中的任一字元
1.2.3.5. [!...] 皆非此列舉中的任一字元
1.2.3.6. [x-y] 此指定連續範圍內的某一字元
1.2.4. IN op: IN (a, b, c, ...) 在所列舉中
1.2.5. IN op: IN (SELECT clause)
1.2.6. BETWEEN:BETWEEN a AND b 在此區間中
1.2.7. BETWEEN #01/07/1996# AND #31/07/1996#
1.2.8. BETWEEN '1996-07-01' AND '1996-07-31'
1.2.9. EXISTS 子句: 若子句結果不為空
1.2.10. ANY 子句: 須為邏輯判斷的運算元且置於後，例如 WHERE A = ANY (...)
1.2.11. ALL 子句: 須為邏輯判斷的運算元且置於後，例如 WHERE A = ALL (...)
1.3. ORDER BY a [ |ASC|DESC], b [ |ASC|DESC], ...
1.3.1. 預設不指定是 ASC。若指定多個欄位，則是先從後面指定的欄位排起，依序排到最前面一個，亦即最前面的欄位有最高的排序顯現；先排序的欄位，會被後來才排序的欄位給打亂（不過最後結果，在前面欄位是相同的前題下後面的欄位仍保持有序）對吧。
1.3.2. result table，結果表格，各 column 的前後關係是由 SELECT 所決定的。
1.3.3. ORDER BY，GROUP BY 是獨立敍述子句，在 select 中的別名將作用不到。
1.4. surfix:
1.4.1. LIMIT
1.4.2. LIMIT 5 回傳五筆
1.4.3. LIMIT 3, 5 略過三筆回傳五筆
1.5.
SELECT DISTINCT <column-names clause>
DISTINCT 影響之後接的所有 column names，即相異兩行整行相同才算相同
1.6. 回傳數量
1.6.1. SELECT COUNT() // return a count-record without a field name
1.6.2. SELECT COUNT() AS <field-name>
1.6.3. for a subset, which specified in FROM clause: SELECT COUNT(*) FROM <subset>
1.7. SELECT AVG() // 平均值
1.8. SELECT SUM() // 總和
1.9. SELECT MIN(), SELECT MAX()：極值
1.10. SELECT CONCAT(<column-name1> + 'string' + <column-name2>) AS <alias-name>
2.
INSERT INTO <table>
(<column-name>[, column-name[, ...]])
VALUES (a[, b, c, ...]);
2.1. 未指定的欄位會被填入 null
2.2. 若已知 table 的各 column 排列順序，則新增所有欄位的前題下可省略列舉 column name
2.3. 判斷是否為 NULL：IS NULL, IS NOT NULL, =NULL, !=NULL. 結果是布林值.
3.
UPDATE <table>
SET <column-name>=a[, <column-name>=b[, ...]][WHERE clause];
4.
DELETE FROM <table>
[WHERE clause];
5. table alias: SELECT <column-names> FROM <table1> AS <alias-name>
5.1. [name contains space] or "name contains space"
5.2. SELECT a.<column-name>, b.<column-name> FROM <table1> AS a, <table2> AS b
6. inner join：A 和 B 的交集
SELECT A.a, A.b, B.c
FROM A
INNER JOIN B ON A.d=B.e;
6.1. A 和 B 和 C 的交集（交集的屬性可以不同）
SELECT A.a, A.b, B.c, C.d, C.e
FROM ((A INNER JOIN B ON A.f=B.g)
INNER JOIN C ON C.h=B.i);
7. left join: A 或 （A 和 B 的交集）；將 INNER 換成 LEFT；A 沒有交集的記錄會 NULL
8. right join: B 或 （A 和 B 的交集）；將 INNER 換成 RIGHT；B 沒有交集的記錄會 NULL
9. full join: A 或 B 或 （A 和 B 的交集）；將 INNER 換成 FULL；A，B 中沒有交集的記錄會 NULL
10. union:
SELECT <column-names> FROM <table1> [WHERE clause]UNION
SELECT <column-names> FROM <table2> [WHERE clause];
10.1. 前後 select 出來的行數必須相等，前後各行需對應有相容的資料型別與屬性與排列次序
10.2. 結果表會使用第一個 select 的 column-names
10.3. 結果表的各記錄會是相異的，即重覆的不列出。若重覆的亦需列出則使用 UNION ALL
10.4. 承 10.2，新增一行常數欄位用以做後續區分，例如：
SELECT 'Customer' As Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers
那麼會新增頭一行 Type，從 Customers 出來的標為 'Customer'，Suppliers 的標為 'Supplier'
11. group by：會先將相同的值分群，之後再按照其他指示對各群操作
12. having：主要用於合計函數。用於 select/where 之後結果的再附加條件
13. select into：複製行到另一 table
13.1. SELECT * INTO <newtable> [IN externaldb] FROM <oldtable> WHERE <condition>;
13.2. SELECT * INTO <newtable> FROM <oldtable> WHERE 1 = 0; 建立新表格
14. insert into select：複製列到另一 table
14.1. 追加；各欄位的資料型態必須相容
14.2. INSERT INTO <target-table> (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM <source-table>
15. case：
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
15.1. 若沒有 else，且無 matched 則回傳 NULL
16. NULL functions: IFNULL(<column-name>, <alternative-value>)
17. Procedure
17.1. CREATE PROCEDURE <procedure_name>
AS <sql_statement>
GO;
17.2. EXEC <procedure_name>;
17.3. CREATE PROCEDURE <procedure-name> @<param-name> <data-type>(<size>)
AS <sql-statements with> @<param-name>
GO;
17.4. EXEC <procedure_name> @<param-name> = <some-string>;
17.5.
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
EXEC SelectAllCustomers @City = "London", @PostalCode = "WA1 1DP";
18. 註解：
18.1. 從 -- 開始至行尾
18.2. /**/ 之間
19. CREATE DATABASE <database-name>;
20. SHOW DATABASES;
21. DROP DATABASE <database-name>;
22. BACKUP DATABASE <database-name> TO DISK = <'file-path'> [WITH DIFFERENTIAL];
23. create table
23.1.
CREATE TABLE <table-name> (
column1 datatype,
column2 datatype,
column3 datatype,
...
);
23.2.
CREATE TABLE <new_table_name> AS
SELECT <column1>, <column2>, ...
FROM <existing_table_name>
WHERE ....;
24. DROP TABLE <table_name>;
25. TRUNCATE TABLE <table_name>; /* delete the data inside a table, but not the table itself.*/
26. alter table
26.1. ALTER TABLE <table_name> ADD <column_name> <datatype>;
26.2. ALTER TABLE <table_name> DROP COLUMN <column_name>;
26.3. ALTER TABLE <table_name> MODIFY COLUMN <column_name> <datatype>;
27. constraints
27.1. table level or column level
27.2. specified when CREATE TABLE or ALTER TABLE
27.3.
CREATE TABLE <table-name> (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
...
);
27.4.
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
FOREIGN KEY - Uniquely identifies a row/record in another table; used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
FOREIGN KEY (<foreign-key-name>) REFERENCES <parent-table-name>(<primary-key-name>)
CHECK - Ensures that all values in a column satisfies a specific condition
CHECK (conditions)
DEFAULT - Sets a default value for a column when no value is specified. The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE().
INDEX - Used to create and retrieve data from the database very quickly. Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
28. auto increment: MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement: ALTER TABLE <tab-name> AUTO_INCREMENT=<value>;
29.
MySQL comes with the following data types for storing a date or a date/time value in the database:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
30. view
30.1.
CREATE VIEW <view_name> AS
SELECT <column1>, <column2>, ...
FROM <table_name>
WHERE <condition>;
30.2. create or replace
replace "CREATE VIEW" with "CREATE OR REPLACE VIEW"
30.3. DROP VIEW <view_name>;
31. references
https://www.w3schools.com/sql/sql_datatypes.asp
https://www.w3schools.com/sql/sql_injection.asp
https://www.w3schools.com/sql/sql_ref_keywords.asp
https://www.w3schools.com/sql/sql_ref_mysql.asp
https://www.w3schools.com/sql/sql_ref_sqlserver.asp
https://www.w3schools.com/sql/sql_operators.asp