SQL

關聯模式的條件 關聯模式可以比實體關係模式(ERM)更精準的描述資料，他有幾個條件必須滿足： 定義域限制: 指資料庫的關聯中的每個屬性質，必須符合該屬性的定義，例如產品名稱必須是字串，薪水必須是整數數字等。 關聯鍵限制: 指資料庫的關聯中必須有關聯鍵的定義，也就是Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key。這些定義我們稍後再來解釋。 實體完整限制: 如果關聯存在主鍵(Primary Key)，則不能為空。因為如果為空值，無法得知其相關的屬性值到底是描述哪一個實體。 參考完整限制: 如果關聯存在外鍵(Foreign Key)為非空值，必須有可以參考的主鍵(Primary Key)。因為如果外鍵存在，而無法關連到其他表格的主鍵，這個關聯存在就沒有意義。 語意完整限制: 這個限制不是必須的，但是可以更完備的描述實體世界的資料。例如交易金額高於100元才可以使用信用卡付款等。 關聯模式的五大鍵 Super key 超鍵: 符合唯一性的關聯鍵。 Candidate Key 候選鍵: 符合唯一性以及最小性的關聯鍵。 Primary Key 主鍵: 從候選鍵中，挑選出其中一個關聯鍵，也就是最具識別意義的關聯鍵。 Alternate Key 次要鍵: 沒有被選為主鍵的其他候選鍵。 Foreign Key 外鍵/外部鍵: 關聯中被用來參考到其他表格主鍵的關聯鍵，就是外鍵。 例如學生資料表(student_id, student_no, student_name, student_depid) student_id student_no student_name student_depid A123454321 00001 Rain Hu MSE A123123123 00002 Mike Hu IM A221232134 00003 Eva Hsu ECE A223124125 00004 Dudu Liu ECE A124124512 00005 Gober Wei IT student_id表示學生身分證字號 student_no表示學生學號 student_name表示學生姓名 student_depid表示學生的科系代號 Super key 就可以是 {student_id}、{student_no}、 {student_id, student_no}、{student_id, student_name}、{student_id, student_depid} … 等等，都符合唯一性的條件。 Candidate Key 就可以是 {student_id}、{student_no}，都符合唯一性及最小性的條件。 Primary Key 就可以從Candidate Key挑選一個，至於挑選哪一個，就看你的系統特性。如果你的學校是多學制的話，就可能不適合挑選學生身分證字號當主鍵，因為可能某個學生原本是國中部，畢業後再進入高中部，如果系統沒有考慮清楚，這個畢業後再變新生的個體，就可能出錯。 Alternate Key 就是沒被挑中當成Primary Key的其他Candidate Key，例如，如果挑選 {student_id}當成主鍵，Alternate Key 就是{student_no}。 如果存在科系資料表 (depid, dep_name)，而且depid當成科系資料表的主鍵，學生資料表的 student_depid就是Foreign Key。 資料來源：https://www.mysql.tw/2015/04/super-keycandidate-keyprimary.html ...

String Functions ASCII Return the ASCII value for the specific character CHAR_LENGTH or CHARACTER_LENGTH Return the length of string (in characters) CONCAT Adds two or more expressions together CONCAT_WS Adds two or more expressions together with a separator FIELD Returns the index position of a value in a list of values FIND_IN_SET Returns the position of a string within a list of strings FORMAT Return sFormats a number to a format like “#,###,###.##”, rounded to a specified number of decimal places INSERT Inserts a string within a string at the specified position and for a certain number of characters INSTR Returns the position of the first occurrence of a string in another string LCASE Converts a string to lower-case LEFT Extracts a number of characters from a string (starting from left) LENGTH Returns the length of a string (in bytes) LOCATE Returns the position of the first occurrence of a substring in a string LOWER Converts a string to lower-case LPAD Left-pads a string with another string, to a certain length LTRIM Removes leading spaces from a string MID Extracts a substring from a string (starting at any position) POSITION Returns the position of the first occurrence of a substring in a string REPEAT Repeats a string as many times as specified REPLACE Replaces all occurrences of a substring within a string, with a new substring REVERSE Reverses a string and returns the result RIGHT Extracts a number of characters from a string (starting from right) RPAD Right-pads a string with another string, to a certain length RTRIM Removes trailing spaces from a string SPACE Returns a string of the specified number of space characters STRCMP Compares two strings SUBSTR Extracts a substring from a string (starting at any position) SUBSTRING Extracts a substring from a string (starting at any position) SUBSTRING_INDEX Returns a substring of a string before a specified number of delimiter occurs TRIM Removes leading and trailing spaces from a string UCASE Converts a string to upper-case UPPER Converts a string to upper-case Numeric Functions ABS Returns the absolute value of a number ACOS Returns the arc cosine of a number ASIN Returns the arc sine of a number ATAN Returns the arc tangent of one or two numbers ATAN2 Returns the arc tangent of two numbers AVG Returns the average value of an expression CEIL Returns the smallest integer value that is >= to a number CEILING Returns the smallest integer value that is >= to a number COS Returns the cosine of a number COT Returns the cotangent of a number COUNT Returns the number of records returned by a select query DEGREES Converts a value in radians to degrees DIV Used for integer division EXP Returns e raised to the power of a specified number FLOOR Returns the largest integer value that is <= to a number GREATEST Returns the greatest value of the list of arguments LEAST Returns the smallest value of the list of arguments LN Returns the natural logarithm of a number LOG Returns the natural logarithm of a number, or the logarithm of a number to a specified base LOG10 Returns the natural logarithm of a number to base 10 LOG2 Returns the natural logarithm of a number to base 2 MAX Returns the maximum value in a set of values MIN Returns the minimum value in a set of values MOD Returns the remainder of a number divided by another number PI Returns the value of PI POW Returns the value of a number raised to the power of another number POWER Returns the value of a number raised to the power of another number RADIANS Converts a degree value into radians RAND Returns a random number ROUND Rounds a number to a specified number of decimal places SIGN Returns the sign of a number SIN Returns the sine of a number SQRT Returns the square root of a number SUM Calculates the sum of a set of values TAN Returns the tangent of a number TRUNCATE Truncates a number to the specified number of decimal places Date Functions ADDDATE Adds a time/date interval to a date and then returns the date ADDTIME Adds a time interval to a time/datetime and then returns the time/datetime CURDATE Returns the current date CURRENT_DATE Returns the current date CURRENT_TIME Returns the current time CURRENT_TIMESTAMP Returns the current date and time CURTIME Returns the current time DATE Extracts the date part from a datetime expression DATEDIFF Returns the number of days between two date values DATE_ADD Adds a time/date interval to a date and then returns the date DATE_FORMAT Formats a date DATE_SUB Subtracts a time/date interval from a date and then returns the date DAY Returns the day of the month for a given date DAYNAME Returns the weekday name for a given date DAYOFMONTH Returns the day of the month for a given date DAYOFWEEK Returns the weekday index for a given date DAYOFYEAR Returns the day of the year for a given date EXTRACT Extracts a part from a given date FROM_DAYS Returns a date from a numeric datevalue HOUR Returns the hour part for a given date LAST_DAY Extracts the last day of the month for a given date LOCALTIME Returns the current date and time LOCALTIMESTAMP Returns the current date and time MAKEDATE Creates and returns a date based on a year and a number of days value MAKETIME Creates and returns a time based on an hour, minute, and second value MICROSECOND Returns the microsecond part of a time/datetime MINUTE Returns the minute part of a time/datetime MONTH Returns the month part for a given date MONTHNAME Returns the name of the month for a given date NOW Returns the current date and time PERIOD_ADD Adds a specified number of months to a period PERIOD_DIFF Returns the difference between two periods QUARTER Returns the quarter of the year for a given date value SECOND Returns the seconds part of a time/datetime SEC_TO_TIME Returns a time value based on the specified seconds STR_TO_DATE Returns a date based on a string and a format SUBDATE Subtracts a time/date interval from a date and then returns the date SUBTIME Subtracts a time interval from a datetime and then returns the time/datetime SYSDATE Returns the current date and time TIME Extracts the time part from a given time/datetime TIME_FORMAT Formats a time by a specified format TIME_TO_SEC Converts a time value into seconds TIMEDIFF Returns the difference between two time/datetime expressions TIMESTAMP Returns a datetime value based on a date or datetime value TO_DAYS Returns the number of days between a date and date “0000-00-00” WEEK Returns the week number for a given date WEEKDAY Returns the weekday number for a given date WEEKOFYEAR Returns the week number for a given date YEAR Returns the year part for a given date YEARWEEK Returns the year and week number for a given date Advanced Functions BIN Returns a binary representation of a number BINARY Converts a value to a binary string CASE Goes through conditions and return a value when the first condition is met CAST Converts a value (of any type) into a specified datatype COALESCE Returns the first non-null value in a list CONNECTION_ID Returns the unique connection ID for the current connection CONV Converts a number from one numeric base system to another CONVERT Converts a value into the specified datatype or character set CURRENT_USER Returns the user name and host name for the MySQL account that the server used to authenticate the current client DATABASE Returns the name of the current database IF Returns a value if a condition is TRUE, or another value if a condition is FALSE IFNULL Return a specified value if the expression is NULL, otherwise return the expression ISNULL Returns 1 or 0 depending on whether an expression is NULL LAST_INSERT_ID Returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table NULLIF Compares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned SESSION_USER Returns the current MySQL user name and host name SYSTEM_USER Returns the current MySQL user name and host name USER Returns the current MySQL user name and host name VERSION Returns the current version of the MySQL database

Introduction What is SQL? SQL stands for Structured Query Language SQL lets you access and manipualate databases SQL becaome a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987 RDBMS RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. Syntax SELECT * FROM Customers; SQL keywords are NOT case sensitive. Some database systems requires a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Most Important SQL Commands SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index SELECT The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. SELECT column1, column2, ... FROM table_name; SELECT DISTINCT The SELECT DINSTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT column1, column2, ... FROM table_name; WHERE The WHERE clause is used to filter records. It is used to extract only those records that fultill a specified condition. SELECT column1, column2, ... FROM table_name WHERE condition; Operators can be used in the WHERE clause: \(\begin{array}{|c|l|}\hline \text{Operator}&\text{Description}\\\hline \text{=}&\text{Equal}\\\hline \text{>}&\text{Greater than}\\\hline \text{<}&\text{Less than}\\\hline \text{>=}&\text{Greater than or equal}\\\hline \text{<=}&\text{Less than or equal}\\\hline \text{<>, !=}&\text{Not equal}\\\hline \text{BETWEEN}&\text{Between a certain range}\\\hline \text{LIKE}&\text{Search for a pattern}\\\hline \text{IN}&\text{To specify multiple possible values for a column}\\\hline \end{array}\) AND, OR, NOT THE WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditons separated by AND are TRUE. The OR operator displays a record if any of the conditons separated by OR is TRUE. THE NOT operator display a record if the condition(s) it NOT TRUE. SELECT column1, column2, ... FROM table_name; WHERE NOT condition1 AND condition2 OR condition3 ...; ORDER BY The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, used DESC keyword. SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; INSERT INTO The INSERT INTO statement is used to insert new records in a table. Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows: INSERT INTO table_name VALUES (value1, value2, value3, ...); IS NULL, IS NOT NULL What is a NULL Value? ...