Important enough to most questions in the database course that pass into the area of computer literacy test to determine whether the SQL is SQL. Actual chulje SQL syntax vary, but the basic pattern is fixed, so you can learn this, you will easily be able to fit more than 2/3.
1) SQL features
Divided on multiple lines, you can enter.
Sentence is fitted with a semicolon (;) at the end.
keyword are not case-sensitive.
2) The basic SELECT syntax
● SELECT field FROM table;
SELECT * FROM student -> search for all fields in a table of students.
SELECT student ID, name FROM student -> search for the student number, the name of the student table field.
SELECT student's student ID, student name FROM student -> search for the student number, the name of the student table field.
SELECT Student. * FROM Students Department (X) -> comma (,) to two tables must be connected.
3) WHERE clause condition, using
● SELECT field FROM table WHERE condition;
When you enter a string in the condition, use single quotation marks ('') or double quotation marks ("").
SELECT * FROM student WHERE average> = 80 -> the average student in the table more than 80 points in all field search
SELECT name, department FROM Temple WHERE age> = 30 AND <= 35; -> employee table at least 30 years of age 35 years of age or less, the Name field of the temple and the department field search
4) specify the conditions with operators
IN to retrieve all the values contained in the syntax to perform OR operations.
Statement to search for records that are included in the scope BETWEEN A AND B: perform an AND operation.
LIKE: * or% are used as specified character or string that contains only the records search.
Syntax for NOT condition is false to retrieve records.
WHERE department = 'SALES' OR department = 'General Affairs' department WHERE IN ('SALES', 'General Affairs'), such as the meaning is.
SELECT * FROM employees WHERE address LIKE "Seoul *" OR address LIKE "game *" Search -> Seoul and address that begins with the employee record.
SELECT employee number, name, rank FROM employee WHERE NOT age <20 AND NOT age> 50 -> age 20 50 is between the employee's employee number, name, rank, search.
FROM books WHERE Title LIKE '% access%' -> 'access' the word that contains the information about your book.
5) ORDER BY clauses using the sort
Ascending (ASC) and descending (DESC) statement can be used.
● If you do not specify how to sort in ascending order (ASC).
90 ORDER BY grade DESC ASC half average 90 or more students in descending order of grade, SELECT name FROM average grades WHERE average = half ascending sort.
SELECT * FROM staff ORDER BY job duration DESC, Age DESC number of years of work from a lot of people, and start to sort First, older people, such as the number of working years to come to sort.
6) group function
Function
Meaning
Function
Meaning
SUM
Total
MIN
The minimum
COUNT
Number of
MAX
The maximum value
AVG
Average
StDev
The standard deviation
Var
Dispersion
7) groups using the GROUP BY clause specifies the
Grouping particular field after the record is retrieved.
GROUP BY clause after grouping, use the HAVING clause to specify the conditions.
SELECT statement, AVG (score) FROM scores GROUP BY statement -> grouped in a statement after the average is calculated.
SELECT department name FROM employees GROUP BY department name HAVING COUNT (*)> = 3; -> department in more than the department name is searched.
SELECT rank, AVG (salary) AS salary average, SUM (salary) AS salary amount FROM employees GROUP BY rank; -> salary for each position the average and the sum.
SELECT customer number, COUNT (order number), SUM (Quantity) FROM order GROUP BY Customer HAVING COUNT (customer number)> number = 2 -> ordered two or more times for customers 'customer number' specific customer number, order number of search, the amount of the order quantity.
SELECT, COUNT (Class of) half FROM sexual WHERE (Korean + English + Math) / 3> = 95 GROUP BY half the number of languages, the average of English, math field of 95 or more students per class to search.
8) AS using the Add Field
Field or by changing the name of the table using the AS clause.
Any function or formula used ● If you do not specify a separate name to start with Expr field to the output.
SELECT COUNT (*) AS members FROM Members -> Members (membership number, name, age, address) from the table several members name is searched.
Members -> Members Search SELECT COUNT (*) AS members FROM.
Using Deduplication 9) DISTINCT
Duplicate records only once.
SELECT DISTINCT credits FROM course -> once after removing duplicate records in the credit field.
10) IN subquery
Perform queries against a specific table returns the value in the WHERE clause of another table using a query.
Format is used as the basic conditions of the query result in a subquery.
SELECT field A FROM Table A
WHERE field IN (SELECT field B FROM Table B);
SELECT name, job duration FROM employee WHERE department code
IN (SELECT department FROM department code
WHERE department name = 'SALES');
-> The name of the department, "SALES" to retrieve the name of the employee and job duration.
SELECT student number, course, score FROM scores WHERE student ID
IN (SELECT Student ID FROM student
WHERE grade = 1 AND half = 1);
-> 1 grade 1 students of student number, course, score lookup.
FROM products WHERE SELECT Product Code Product Code
FROM NOT IN (SELECT sales of merchandise sales
WHERE Account Number BETWEEN 30 AND 40);
-> Account number of products that are not 30-40 Product Code Search.
Specify the condition to 11), using the function
Function
Explanation
NOW
Display the current date and time.
DATE
That displays the current date
TIME
Current time display.
WEEKDAY
Display the date of the day of the week as a number
DATEADD
Add a number to a date
DATEDIFF
The difference between the start and end dates
DATEPART
To display the date units specified
DATEVALUE
Convert date to date data.
YEAR / MONTH / DAY
Display year / month / ten thousand.
HOUR / MINUTE / SECOND
Specified time hours / minutes / seconds are shown
SELECT LCASE (trade name) FROM factory -> brand in uppercase letters changed to lowercase.
SELECT DATEDIFF ("Y", 5, DATE ()) FROM factory -> chulgoil field to today's date has elapsed (日), the number is displayed.
SELECT DATEADD ("YYYY", 5, DATE ()) FROM factory -> date plus five years from today's date.
SELECT COUNT (*) FROM factory GROUP BY DATEPART ("m", wearing one]) -> wearing grouped into one monthly displays the number of records.
YEAR ([Date of Birth])> = 1980 -> your birthday person since 1980 to find.
FROM sexual SELECT average, IF (average> = 90, "pass", "fail") AS acceptance -> average and the average score is 90 points more than otherwise "pass", "fail" to display the fields' acceptance 'brings to the field.
12) Cartesian product
This is a query that retrieves all the fields of the two tables.
Number of records: the output is multiplied by the value of the records of the two tables.
Field: the output is the sum of the number of fields in the two tables.
No comments:
Post a Comment