SQL - 4과. 데이터 선택 - SELECT 문
따라서 포럼 데이터베이스에는 사용자(사용자), 주제(주제) 및 게시물(메시지)의 세 가지 테이블이 있습니다. 그리고 우리는 그들이 어떤 데이터를 담고 있는지 알고 싶습니다. 이를 위해 SQL에 연산자가 있습니다. 선택하다. 이를 사용하는 구문은 다음과 같습니다.select_from에서 select_what을 선택합니다.
"what_to_select" 대신에 우리가 보고자 하는 값을 가진 열의 이름이나 쉼표로 구분된 여러 열의 이름 또는 모든 열의 선택을 의미하는 별표 문자(*)를 지정해야 합니다. 탁자. "from_choose" 대신 테이블 이름을 지정해야 합니다.
먼저 users 테이블의 모든 열을 살펴보겠습니다.
사용자로부터 * 선택;
이것이 우리가 이 테이블에 입력한 모든 데이터입니다. 하지만 id_user 열만 보고 싶다고 가정해 보겠습니다(예를 들어, 지난 강의에서 주제 테이블을 채우기 위해 사용자 테이블에 있는 id_users가 무엇인지 알아야 했습니다). 이를 위해 쿼리에서 이 열의 이름을 지정합니다.
사용자로부터 id_user 선택;
예를 들어 사용자의 이름과 전자 메일을 보려면 관심 있는 열을 쉼표로 구분하여 나열합니다.
사용자로부터 이름, 이메일을 선택하십시오.
마찬가지로 다른 테이블에 어떤 데이터가 포함되어 있는지 확인할 수 있습니다. 어떤 주제가 있는지 살펴보겠습니다.
선택 * 주제에서;
이제 주제가 4개뿐인데 100개가 있다면? 예를 들어 알파벳순으로 표시하고 싶습니다. SQL에는 이에 대한 키워드가 있습니다. 주문다음에 정렬이 발생할 열의 이름이 옵니다. 구문은 다음과 같습니다.
SELECT column_name FROM table_name ORDER BY sort_column_name;
기본 정렬은 오름차순이지만 키워드를 추가하여 변경할 수 있습니다. 설명
이제 데이터가 내림차순으로 정렬됩니다.
한 번에 여러 열을 기준으로 정렬할 수 있습니다. 예를 들어 다음 쿼리는 topic_name 열을 기준으로 데이터를 정렬하고 이 열에 동일한 행이 여러 개 있는 경우 id_author 열을 내림차순으로 정렬합니다.
결과를 이전 쿼리의 결과와 비교합니다.
매우 자주 우리는 테이블의 모든 정보가 필요하지 않습니다. 예를 들어 사용자 sveta(id=4)가 만든 주제를 알고 싶습니다. SQL에는 이에 대한 키워드가 있습니다. 어디, 이러한 요청의 구문은 다음과 같습니다.
이 예에서 조건은 사용자 ID입니다. id_author 열(사용자 ID sveta)에 4가 있는 행만 필요합니다.
또는 "자전거" 테마를 만든 사람을 알고 싶습니다.
물론 작성자의 아이디 대신에 작성자의 이름을 표시하는 것이 더 편리하겠지만 이름은 다른 테이블에 저장됩니다. 이후 단원에서는 여러 테이블에서 데이터를 선택하는 방법을 배웁니다. 그동안 WHERE 키워드를 사용하여 어떤 조건을 지정할 수 있는지 알아보겠습니다.
| 운영자 | 설명 |
| = (같음) | 지정된 값과 동일한 선택된 값 예: SELECT * FROM 주제 WHERE id_author=4; 결과:
|
| > (더) | 지정된 것보다 큰 값이 선택됩니다. 예: SELECT * FROM 주제 WHERE id_author>2; 결과:
|
| < (меньше) | 지정된 값보다 작은 값이 선택됩니다. 예: SELECT * FROM 주제 WHERE id_author
|
| >=(크거나 같음) | 지정된 값보다 크거나 같은 값이 선택됩니다. 예: SELECT * FROM 주제 WHERE id_author>=2; 결과:
|
| <= (меньше или равно) | 지정된 값보다 작거나 같은 값이 선택됩니다. 예: SELECT * FROM 주제 WHERE id_author
|
| != (같지 않음) | 지정된 것과 같지 않은 값이 선택됩니다. 예: SELECT * FROM 주제 WHERE id_author!=1; 결과:
|
| NULL이 아님 | 지정된 필드에 값이 있는 행이 선택됩니다. 예: id_author가 NULL이 아닌 주제에서 * 선택; 결과:
|
| NULL | 지정된 필드에 값이 없는 행이 선택됩니다. 예: id_author가 NULL인 주제에서 * 선택; 결과:
|
| BETWEEN (사이) | 지정된 값 사이의 값이 선택됩니다. 예: 1과 3 사이의 id_author가 있는 주제에서 * 선택; 결과:
|
| IN(포함된 값) | 지정된 값에 해당하는 값 예: SELECT * FROM 주제 WHERE id_author IN (1, 4); 결과:
|
| NOT IN(값이 포함되지 않음) | 지정된 값 이외의 선택된 값 예: id_author가 (1, 4)가 아닌 주제에서 * 선택; 결과:
|
| 좋아요(일치) | 샘플 값이 선택되었습니다. 예: SELECT * FROM 주제 WHERE topic_name LIKE "vel%"; 결과:
|
| 같지 않은 | 샘플과 일치하지 않는 값이 선택됨 예: SELECT * FROM topic WHERE topic_name이 "vel%"와 같지 않음; 결과:
|
빈 세트 - 해당 문자열이 없습니다.
LIKE 연산자의 가능한 메타문자는 아래에서 설명합니다.
LIKE 연산자 메타문자
메타문자 검색은 텍스트 필드에서만 수행할 수 있습니다.가장 일반적인 메타 문자는 다음과 같습니다. % . 모든 문자를 의미합니다. 예를 들어 "vel"로 시작하는 단어를 찾으려면 LIKE "vel%"라고 쓰고 "club"이라는 문자가 포함된 단어를 찾으려면 LIKE "%"라고 씁니다. 클럽%". 예를 들어:
일반적으로 사용되는 또 다른 메타 문자는 다음과 같습니다. _ . 문자가 거의 없거나 전혀 없음을 나타내는 %와 달리 밑줄은 정확히 한 문자를 나타냅니다. 예를 들어:
메타 문자와 "물고기" 사이의 공백에 주의하세요. 건너뛰면 요청이 작동하지 않습니다. 메타문자 _ 정확히 하나의 문자를 나타내며 공백도 문자입니다.
오늘은 충분합니다. 다음 단원에서는 두 개 이상의 테이블을 쿼리하는 방법을 배웁니다. 그동안 게시물 테이블(메시지)에 대해 고유한 쿼리를 만들어 보십시오.
이 강의는 SQL 쿼리의 데이터베이스에 VBA 액세스. 데이터베이스에 대한 VBA 쿼리 INSERT, UPDATE, DELETE가 수행되는 방법을 살펴보고 SELECT 쿼리에서 특정 값을 가져오는 방법도 알아봅니다.
프로그래밍 하시는 분들 VBA 액세스그리고 SQL 서버 데이터베이스로 작업하는 동안 INSERT, UPDATE 또는 간단한 SQL SELECT 쿼리와 같은 SQL 쿼리를 데이터베이스로 보내는 것과 같은 간단하고 필요한 작업에 직면하는 경우가 많습니다. 그리고 우리는 초보 프로그래머이기 때문에 이것도 할 수 있어야 하므로 오늘은 그렇게 하겠습니다.
예를 들어 MSSql 2008에서 텍스트 파일로 데이터 업로드에 대한 기사에서 이 데이터를 얻기 위해 VBA에서 코드를 작성했거나 자료에서 약간 다루었습니다. Access에서 Word 및 Excel 템플릿으로 데이터 업로드 그러나 어떤 식 으로든 우리는 그것을 피상적으로 고려했으며 오늘은 이에 대해 좀 더 자세히 이야기 할 것을 제안합니다.
메모! 아래의 모든 예는 Access 2003 ADP 프로젝트와 MSSql 2008 데이터베이스를 사용하여 설명합니다.
예를 위한 초기 데이터
1년 중 월의 숫자와 이름을 포함하는 test_table 테이블이 있다고 가정해 보겠습니다. 관리 스튜디오)
테이블 만들기 .(NULL 아님, (50) NULL) 진행 중
내가 말했듯이 MS SQL 2008과 함께 작동하도록 구성된 ADP 프로젝트를 사용할 것입니다. 테스트 양식을 만들고 캡션이 있는 시작 버튼을 추가했습니다. "달리다", 코드를 테스트하는 데 필요합니다. 이벤트 핸들러에 모든 코드를 작성할 것입니다." 버튼 누르기».
VBA에서 데이터베이스 쿼리 INSERT, UPDATE, DELETE
오랫동안 드래그하지 않으려면 시작하겠습니다. 테스트 테이블에 행을 추가해야 한다고 가정해 보겠습니다( 코드가 주석 처리됨)/
Private Sub start_Click() "쿼리 문자열을 저장할 변수를 선언합니다. Dim sql_query As String "필요한 쿼리를 작성합니다. sql_query = "INSERT INTO test_table(id, name_mon) VALUES("6", "June")" "실행 DoCmd.RunSQL sql_query End Sub
이 경우 쿼리는 현재 데이터베이스 연결 설정을 사용하여 실행됩니다. 데이터가 추가되었는지 여부를 확인할 수 있습니다.
보시다시피 데이터가 삽입되었습니다.
한 줄을 삭제하기 위해 다음 코드를 작성합니다.
Private Sub start_Click() "쿼리 문자열을 담을 변수 선언 Dim sql_query As String "삭제 쿼리를 여기에 작성 sql_query = "DELETE test_table WHERE id = 6" "실행 DoCmd.RunSQL sql_query End Sub
확인하면 원하는 줄이 삭제되었음을 알 수 있습니다.
데이터를 업데이트하기 위해 sql_query 변수에 업데이트 쿼리를 작성합니다. 의미가 명확했으면 합니다.
VBA에서 데이터베이스에 대한 SELECT 쿼리
여기서는 다른 SQL 구조보다 조금 더 흥미롭습니다.
먼저 테이블에서 모든 데이터를 가져와야 한다고 가정해 보겠습니다. 예를 들어 데이터를 처리하여 메시지에 표시하고 물론 다른 용도로 사용할 수 있습니다. 이를 위해 다음과 같이 작성합니다. 암호
Private Sub start_Click() "Declaring variables" For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for display total data in the message Dim str As String "Create a new object for records set 세트 RS = New ADODB .Recordset "Query string sql_query = "SELECT id, name_mon FROM test_table" "현재 프로젝트 연결 설정을 사용하여 쿼리를 실행하여 str = str & RS.Fields("id") & "-" & RS 메시지를 표시합니다. Fields("name_mon") & vbnewline "다음 레코드로 이동 RS.MoveNext Wend "출력 메시지 msgbox str End Sub
여기에서는 이미 VBA 액세스 루프를 사용하여 레코드세트의 모든 값을 반복하고 있습니다.
그러나 종종 레코드 세트에서 모든 값을 가져오는 것이 아니라 코드로 월 이름과 같은 하나만 가져와야 합니다. 이를 위해 루프를 사용하는 것은 다소 비용이 많이 들기 때문에 하나의 값만 반환하고 참조하는 쿼리를 작성할 수 있습니다. 예를 들어 코드 5로 월 이름을 얻을 수 있습니다.
Private Sub start_Click() "Declaring variables "For a record set from the database Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String for display the final value Dim str As String "Create a new object for records set RS = New ADODB.Recordset "Query string sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "현재 프로젝트 연결 설정을 사용하여 쿼리 실행 RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Get our value str = RS.Fields(0 ) msgbox str end sub
보편성을 위해 여기서 우리는 이미 셀 이름이 아니라 인덱스, 즉 0은 첫 번째 값입니다. 레코드세트, 결국 우리는 값을 얻었다 "5월".
보시다시피 모든 것이 아주 간단합니다. 종종 데이터베이스에서 특정 값을 가져와야 하는 경우( 마지막 예에서와 같이) 그런 다음 하나의 입력 매개변수(예: 월 코드( 우리의 예를 고려) 간단하게 이 값을 표시해야 하는 경우 필요한 매개변수를 사용하여 필요한 함수를 호출하면 VBA 코드가 크게 줄어들고 프로그램 인식이 향상됩니다.
오늘은 그게 다야. 행운을 빌어요!
교육 프로젝트 "상점"에 대한 설명테이블 링크 체계
테이블 설명
m_category - 제품 범주
m_income - 상품 수령
m_outcome - 상품 소비
m_product - 디렉토리, 제품 설명
m_supplier - 디렉토리; 공급업체 정보
m_unit - 디렉토리; 단위
이 자습서에 제공된 예제를 실제로 테스트하려면 다음 소프트웨어를 사용할 수 있어야 합니다.
마이크로소프트 액세스 2003 이상.
MS Access의 SQL 쿼리. 시작
테이블의 내용을 보려면 왼쪽 창에서 테이블 이름을 두 번 클릭합니다.
테이블 필드 편집 모드로 전환하려면 상단 패널에서 디자인 모드를 선택합니다.
SQL 쿼리 결과를 표시하려면 왼쪽 창에서 쿼리 이름을 두 번 클릭합니다.
SQL 쿼리 편집 모드로 전환하려면 상단 패널에서 SQL 모드를 선택합니다.
SQL 쿼리. MS 액세스의 예. 선택: 1-10
SQL 쿼리에서 SELECT 문은 데이터베이스 테이블에서 선택하는 데 사용됩니다.SQL 쿼리 Q001.원하는 순서로 필요한 필드만 가져오는 SQL 쿼리의 예:
SELECT dt, product_id, 금액
FROM m_income;
SQL 쿼리 Q002.이 SQL 쿼리 예에서 별표 문자(*)는 m_product 테이블의 모든 열을 표시하는 데 사용됩니다. 즉, m_product 관계의 모든 필드를 가져오는 데 사용됩니다.
선택하다*
FROM m_product;
요구SQLQ003. DISTINCT 문은 중복 레코드를 제거하고 많은 고유 레코드를 얻는 데 사용됩니다.
고유한 product_id 선택
FROM m_income;
SQL 쿼리 Q004. ORDER BY 문은 특정 필드의 값을 기준으로 레코드를 정렬(순서)하는 데 사용됩니다. 필드 이름은 ORDER BY 절 다음에 옵니다.
선택하다*
m_income에서
가격으로 주문;
SQL 쿼리 Q005. ASC 문은 ORDER BY 문과 함께 사용되며 오름차순 정렬을 정의하는 데 사용됩니다. DESC 문은 ORDER BY 문과 함께 사용되며 내림차순 정렬을 정의하는 데 사용됩니다. ASC와 DESC가 모두 지정되지 않은 경우 ASC(기본값)가 있다고 가정합니다.
선택하다*
m_income에서
ORDER BY dt DESC , 가격;
SQL 쿼리 Q006.테이블에서 필요한 레코드를 선택하기 위해 선택 조건을 나타내는 다양한 논리식이 사용됩니다. 부울 식은 WHERE 절 뒤에 옵니다. 금액 값이 200보다 큰 모든 레코드를 m_income 테이블에서 가져오는 예:
선택하다*
m_income에서
WHERE 금액>200;
SQL 쿼리 Q007.복잡한 조건을 표현하기 위해 논리 연산 AND(접속사), OR(접합사), NOT(논리적 부정)을 사용합니다. m_outcome 테이블에서 금액 값이 20이고 가격 값이 10보다 크거나 같은 모든 레코드를 가져오는 예:
가격
m_outcome에서
WHERE 금액=20 AND 가격>=10;
SQL 쿼리 Q008.둘 이상의 테이블에서 데이터를 조인하려면 INNER JOIN, LEFT JOIN, RIGHT JOIN 문을 사용합니다. 다음 예제는 m_income 테이블에서 dt, product_id, amount, price 필드를 검색하고 m_product 테이블에서 title 필드를 검색합니다. m_income 테이블의 레코드는 m_income.product_id의 값이 m_product.id의 값과 같을 때 m_product 테이블의 레코드에 연결됩니다.
ON m_income.product_id=m_product.id;
SQL 쿼리 Q009.이 SQL 쿼리에서 주의해야 할 두 가지 사항이 있습니다. 1) 검색 텍스트는 작은따옴표(")로 묶여 있습니다. 2) 날짜는 MS에 적합한 #Month/Day/Year# 형식입니다. 액세스.다른 시스템에서는 날짜 형식이 다를 수 있습니다.2011년 6월 12일에 우유 수령에 대한 정보를 표시하는 예입니다. 날짜 형식 #6/12/2011#에 주의하십시오.
SELECT dt, product_id, 제목, 수량, 가격
FROM m_income INNER JOIN m_product
WHERE 제목="우유" And dt=#6/12/2011#; !}
SQL 쿼리 Q010. BETWEEN 명령은 값 범위가 속하는지 테스트하는 데 사용됩니다. 2011년 6월 1일과 6월 30일 사이에 받은 상품에 대한 정보를 표시하는 SQL 쿼리의 예:
선택하다*
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
#2011년 6월 1일#과 #2011년 6월 30일# 사이에 dt가 있는 경우;
SQL 쿼리. MS 액세스의 예. 선택: 11-20
하나의 SQL 쿼리는 다른 쿼리 안에 중첩될 수 있습니다. 하위 쿼리는 쿼리 내의 쿼리에 지나지 않습니다. 일반적으로 하위 쿼리는 WHERE 절에서 사용됩니다. 그러나 하위 쿼리를 사용하는 다른 방법이 있습니다.Q011을 요청합니다. m_product 테이블의 제품에 대한 정보를 표시하며 해당 코드는 m_income 테이블에도 있습니다.
선택하다*
m_product에서
WHERE id IN (m_income에서 product_id 선택);
Q012를 요청합니다. m_product 테이블의 제품 목록이 표시되며 해당 코드는 m_outcome 테이블에 없습니다.
선택하다*
m_product에서
WHERE id NOT IN(m_outcome에서 product_id 선택);
Q013을 요청합니다.이 SQL 쿼리는 m_income 테이블에는 코드가 있지만 m_outcome 테이블에는 없는 코드 및 제품 이름의 고유한 목록을 반환합니다.
SELECT DISTINCT product_id, 제목
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN(m_outcome에서 product_id 선택);
Q014를 요청하십시오.고유한 범주 목록이 m_category 테이블에서 표시되며 이름은 문자 M으로 시작합니다.
뚜렷한 제목 선택
m_product에서
WHERE 제목 LIKE "M*";
Q015를 요청하십시오.쿼리의 필드에 대해 산술 연산을 수행하고 쿼리의 필드 이름을 바꾸는 예(별칭). 이 예에서는 이익이 매출의 7%라고 가정하고 각 항목 소비 레코드에 대한 비용 = 수량*가격 및 이익을 계산합니다.
금액*가격/100*7 AS 이익
FROM m_outcom;
Q016을 요청합니다.산술 연산을 분석하고 단순화하여 쿼리 실행 속도를 높일 수 있습니다.
SELECT dt, product_id, amount, price, amount*price AS output_sum,
result_sum*0.07 AS 이익
FROM m_outcom;
Q017을 요청합니다. INNER JOIN 문을 사용하여 여러 테이블의 데이터를 결합할 수 있습니다. 다음 예에서는 ctgry_id 값에 따라 m_income 테이블의 각 항목이 제품이 속한 m_category 테이블의 카테고리 이름과 일치합니다.
SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
내부 조인 m_category AS c ON b.ctgry_id=c.id
ORDER BY c.제목, b.제목;
Q018을 요청합니다. SUM - 합계, COUNT - 수량, AVG - 산술 평균, MAX - 최대값, MIN - 최소값과 같은 함수를 집계 함수라고 합니다. 여러 값을 취하고 처리할 때 단일 값을 반환합니다. SUM 집계 함수를 사용하여 금액 필드와 가격 필드의 곱 합계를 계산하는 예:
SELECT SUM(금액*가격) AS Total_Sum
FROM m_income;
Q019를 요청하십시오.여러 집계 함수를 사용하는 예:
SELECT Sum(금액) AS Amount_Sum, AVG(금액) AS Amount_AVG,
MAX(금액) AS 금액_최대, 최소(금액) AS 금액_최소,
개수(*) AS Total_Number
FROM m_income;
Q020을 요청합니다.이 예에서는 2011년 6월에 받은 코드 1이 있는 모든 항목의 합계가 계산됩니다.
SELECT 합계(금액*가격) AS income_sum
m_income에서
WHERE product_id=1 AND dt BETWEEN #6/1/2011#과 #6/30/2011#;.
Q021을 요청합니다.다음 SQL 쿼리는 코드 4 또는 6이 있는 상품이 판매된 금액을 계산합니다.
SELECT Sum(금액*가격) as output_sum
m_outcome에서
WHERE product_id=4 OR product_id=6;
Q022를 요청하십시오. 2011년 6월 12일에 코드 4 또는 6이 있는 상품이 판매된 금액에 대해 계산됩니다.
SELECT 합계(금액*가격) AS output_sum
m_outcome에서
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;
Q023을 요청합니다.과제는 이것입니다. "구운 제품" 범주의 상품이 적립된 총 금액을 계산합니다.
이 문제를 해결하려면 다음과 같은 이유로 m_income, m_product 및 m_category의 세 테이블에서 작업해야 합니다.
- 적립된 상품의 수량과 가격은 m_income 테이블에 저장됩니다.
- 각 제품의 카테고리 코드는 m_product 테이블에 저장됩니다.
- 카테고리 제목의 이름은 m_category 테이블에 저장됩니다.
이 문제를 해결하기 위해 다음 알고리즘을 사용합니다.
- 하위 쿼리를 통해 테이블 m_category에서 카테고리 코드 "베이킹 제품" 결정
- m_income 및 m_product 테이블을 조인하여 적립된 각 제품의 범주를 결정합니다.
- 카테고리 코드가 위 하위 쿼리에서 정의한 코드와 동일한 상품에 대한 입고 금액(= 수량 * 가격) 계산.
선택하다
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (m_category에서 ID 선택 WHERE title="구운 제품"); !}
Q024를 요청하십시오."베이킹된 제품" 범주의 크레딧 상품 총액을 계산하는 문제는 다음 알고리즘으로 해결됩니다.
- m_category 테이블의 product_id 값에 따라 m_income 테이블의 각 레코드는 카테고리 이름과 일치합니다.
- 범주가 "베이킹된 제품"과 동일한 레코드를 선택합니다.
- 소득 금액 = 수량 * 가격을 계산합니다.
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="구운 과자";
!}
Q025를 요청하십시오.이 예에서는 소비된 항목 수를 계산합니다.
SELECT COUNT(product_id) AS product_cnt
FROM (m_outcome에서 고유한 product_id 선택) AS t;
Q026을 요청하십시오. GROUP BY 절은 레코드를 그룹화하는 데 사용됩니다. 일반적으로 레코드는 하나 이상의 필드 값으로 그룹화되며 각 그룹에 집계 작업이 적용됩니다. 예를 들어 다음 쿼리는 상품 판매에 대한 보고서를 생성합니다. 즉, 상품 이름과 판매 금액이 포함된 테이블이 생성됩니다.
제목 선택, SUM(금액*가격) AS output_sum
FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
제목으로 그룹화;
Q027을 요청합니다.범주별 판매 보고서입니다. 즉, 제품 범주의 이름, 이러한 범주의 상품이 판매된 총 금액 및 평균 판매 금액을 포함하는 테이블이 생성됩니다. ROUND 함수는 평균값을 가장 가까운 100분의 1로 반올림하는 데 사용됩니다(소수 구분 기호 다음의 두 번째 소수점 자리).
SELECT c.제목, SUM(금액*가격) AS output_sum,
ROUND(AVG(금액*가격),2) AS output_sum_avg
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
내부 조인 m_category AS c ON a.ctgry_id=c.id
GROUP BY c.제목;
Q028을 요청합니다.각 제품에 대해 총 영수증 수와 평균 수량이 계산되고 상품에 대한 정보가 표시되며 총 수취 수는 500 이상입니다.
SELECT product_id, SUM(금액) AS amount_sum,
Round(Avg(금액),2) AS amount_avg
m_income에서
product_id로 그룹화
HAVING 합계(금액)>=500;
Q029를 요청하십시오.이 쿼리는 각 항목에 대해 2011년 2분기에 이루어진 영수증의 합계와 평균을 계산합니다. 상품 수령 총액이 1000 이상인 경우 이 상품에 대한 정보가 표시됩니다.
제목 선택, SUM(금액*가격) AS income_sum
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
#2011년 4월 1일#과 #2011년 6월 30일# 사이 dt
제목으로 그룹화
HAVING SUM(금액*가격)>=1000;
Q030을 요청합니다.경우에 따라 일부 테이블의 각 레코드를 다른 테이블의 각 레코드와 일치시켜야 합니다. 직교 곱이라고하는 것. 이러한 조인의 결과 테이블을 Descartes 테이블이라고 합니다. 예를 들어 어떤 테이블 A에 100개의 항목이 있고 테이블 B에 15개의 항목이 있는 경우 데카르트 테이블은 100*15=150개의 항목으로 구성됩니다. 다음 쿼리는 m_income 테이블의 각 항목을 m_outcome 테이블의 각 항목과 조인합니다.
FROM m_income, m_outcome;
Q031을 요청하십시오.두 개의 필드로 레코드를 그룹화하는 예입니다. 다음 SQL 쿼리는 각 공급업체에 대해 공급업체로부터 수령한 상품의 양과 수량을 계산합니다.
SUM(금액*가격) AS income_sum
Q032를 요청하십시오.두 개의 필드로 레코드를 그룹화하는 예입니다. 다음 쿼리는 각 공급자에 대해 우리가 판매한 제품의 양과 수량을 계산합니다.
SELECT supplier_id, product_id, SUM(금액) AS amount_sum,
GROUP BY supplier_id, product_id;
Q033을 요청합니다.이 예에서는 위의 두 쿼리(q031 및 q032)가 하위 쿼리로 사용됩니다. 이러한 쿼리의 결과는 LEFT JOIN 메서드를 사용하여 하나의 보고서로 병합됩니다. 다음 쿼리는 각 공급자에 대해 입고 및 판매된 제품의 수와 양에 대한 보고서를 표시합니다. 일부 제품이 이미 도착했지만 아직 판매되지 않은 경우 이 레코드의 output_sum 셀이 비어 있다는 사실에 주의해야 합니다. 이 쿼리는 상대적으로 복잡한 쿼리를 하위 쿼리로 사용하는 예일 뿐입니다. 많은 양의 데이터가 포함된 이 SQL 쿼리의 성능이 의심스럽습니다.
선택하다*
에서
SUM(금액*가격) AS income_sum
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
왼쪽 조인
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(금액*가격) AS output_sum
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);
Q034를 요청하십시오.이 예에서는 위의 두 쿼리(q031 및 q032)가 하위 쿼리로 사용됩니다. 이러한 쿼리의 결과는 RIGTH JOIN 방법을 사용하여 하나의 보고서로 결합됩니다. 다음 쿼리는 사용한 결제 시스템에 대한 각 고객의 결제 금액과 투자 금액에 대한 보고서를 인쇄합니다. 다음 쿼리는 각 공급자에 대해 입고 및 판매된 제품의 수와 양에 대한 보고서를 표시합니다. 제품이 이미 판매되었지만 아직 수령하지 않은 경우 이 항목의 income_sum 셀은 비어 있습니다. 이러한 빈 셀의 존재는 판매 전에 해당 제품이 먼저 도착해야 하기 때문에 판매 회계 오류의 지표입니다.
선택하다*
에서
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(금액*가격) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
오른쪽 조인
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(금액*가격) AS output_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);
Q035를 요청하십시오.상품별 수입 및 지출 금액에 대한 보고서가 표시됩니다. 이를 위해 m_income 및 m_outcome 테이블에 따라 제품 목록이 생성된 다음 이 목록의 각 제품에 대해 m_income 테이블에 따른 영수증 합계와 m_outcome 테이블에 따른 비용 합계가 계산됩니다.
SELECT product_id, SUM(in_amount) AS income_amount,
SUM(out_amount) AS output_amount
에서
(SELECT product_id, amount AS in_amount, 0 AS out_amount
m_income에서
유니온 올
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
product_id로 그룹화;
Q036을 요청하십시오. EXISTS 함수는 전달된 집합에 요소가 포함된 경우 TRUE를 반환합니다. EXISTS 함수는 전달된 집합이 비어 있는 경우, 즉 요소가 없는 경우 FALSE를 반환합니다. 다음 쿼리는 m_income 테이블과 m_outcome 테이블 모두에 포함된 제품 코드를 반환합니다.
고유한 product_id 선택
FROM m_income AS
WHERE EXISTS(m_outcome AS b에서 product_id 선택
Q037을 요청합니다. m_income 테이블과 m_outcome 테이블 모두에 포함된 제품 코드가 표시됩니다.
고유한 product_id 선택
FROM m_income AS
WHERE product_id IN (m_outcome에서 product_id 선택)
Q038을 요청하십시오. m_income 테이블에 포함되어 있지만 m_outcome 테이블에는 포함되지 않은 제품 코드가 표시됩니다.
고유한 product_id 선택
FROM m_income AS
존재하지 않는 곳(m_outcome AS b에서 product_id 선택
WHERE b.product_id=a.product_id);
Q039를 요청하십시오.판매 금액이 가장 높은 제품 목록이 표시됩니다. 알고리즘은 이것입니다. 각 제품에 대해 판매액 합계가 계산됩니다. 그런 다음 이러한 합계의 최대값이 결정됩니다. 그런 다음 각 제품에 대해 판매 합계가 다시 계산되고 코드와 판매 합계가 최대 값과 같은 상품 판매 합계가 표시됩니다.
SELECT product_id, SUM(금액*가격) AS amount_sum
m_outcome에서
product_id로 그룹화
HAVING SUM(금액*가격) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id));
Q040을 요청합니다.예약어 IIF(조건 연산자)는 논리식을 평가하고 결과(TRUE 또는 FALSE)에 따라 작업을 수행하는 데 사용됩니다. 다음 예에서 품목의 배송은 수량이 500개 미만인 경우 "소량"으로 간주됩니다. 그렇지 않은 경우, 즉 입고 수량이 500개 이상인 경우 배송은 "대량"으로 간주됩니다.
SELECT dt, product_id, 금액,
IIF(m_income에서 금액;
SQL 쿼리 Q041. IIF 문을 여러 번 사용하는 경우에는 SWITCH 문으로 대체하는 것이 더 편리합니다. SWITCH 연산자(다중 선택 연산자)는 논리식을 평가하고 결과에 따라 작업을 수행하는 데 사용됩니다. 다음 예에서 배송된 로트는 로트의 상품 수량이 500개 미만인 경우 "소"로 간주됩니다. 그렇지 않고, 즉 상품 수량이 500보다 크거나 같으면 해당 로트는 "대"로 간주됩니다. ":
SELECT dt, product_id, 금액,
SWITCH(금액=500,"큰") AS마크
FROM m_income;
Q042를 요청하십시오.다음 쿼리에서 들어오는 배치의 상품 수량이 300개 미만이면 배치가 "소량"으로 간주됩니다. 그렇지 않으면, 즉 조건 amount SELECT dt, product_id, amount,
IIF(금액 IIF(금액 FROM m_income;
SQL 쿼리 Q043.다음 쿼리에서 들어오는 배치의 상품 수량이 300개 미만이면 배치가 "소량"으로 간주됩니다. 그렇지 않으면, 즉 조건 amount SELECT dt, product_id, amount,
SWITCH(금액금액금액>=1000,"큰") AS마크
FROM m_income;
SQL 쿼리 Q044.다음 쿼리에서 매출은 소규모(최대 150개), 중간(150~300개), 대규모(300개 이상)의 세 그룹으로 나뉩니다. 다음으로 각 그룹에 대해 총 금액이 계산됩니다.
범주 선택, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT 금액*가격 AS output_sum,
IIf(금액*가격 IIf(금액*가격 FROM m_outcome) AS t
범주별 그룹화;
SQL 쿼리 Q045. DateAdd 함수는 주어진 날짜에 일, 월 또는 연도를 추가하고 새 날짜를 얻는 데 사용됩니다. 다음 요청:
1) dt 필드의 날짜에 30일을 더하고 dt_plus_30d 필드에 새 날짜를 표시합니다.
2) dt 필드의 날짜에 1개월을 추가하고 dt_plus_1m 필드에 새 날짜를 표시합니다.
SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
FROM m_income;
SQL 쿼리 Q046. DateDiff 함수는 서로 다른 단위(일, 월 또는 연도)의 두 날짜 간의 차이를 계산하도록 설계되었습니다. 다음 쿼리는 dt 필드의 날짜와 현재 날짜(일, 월 및 연)의 차이를 계산합니다.
SELECT dt, DateDiff("d",dt,Date()) AS last_day,
DateDiff("m",dt,Date()) AS last_months,
DateDiff("yyyy",dt,Date()) as last_years
FROM m_income;
SQL 쿼리 Q047.상품을 받은 날(테이블 m_income)부터 현재 날짜까지의 일수는 DateDiff 함수를 사용하여 계산하고 만료 날짜를 비교합니다(테이블 m_product).
DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id;
SQL 쿼리 Q048.상품 수령일로부터 현재 날짜까지의 일수를 계산한 다음 이 숫자가 만료일을 초과하는지 확인합니다.
SELECT a.id, product_id, dt, 수명,
DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;
SQL 쿼리 Q049.상품 수령일로부터 현재 날짜까지의 개월 수를 계산합니다. month_last1 열은 월의 절대 수를 계산하고 month_last2 열은 전체 월 수를 계산합니다.
SELECT dt, DateDiff("m",dt,Date()) AS 월_마지막1,
DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;
SQL 쿼리 Q050. 2011년 입고된 상품의 수량 및 금액에 대한 분기별 보고서가 표시됩니다.
SELECT kvartal, SUM(outcome_sum) AS 합계
FROM (SELECT 금액*가격 AS output_sum, 월(dt) AS m,
SWITCH(m =10.4) AS kvartal
FROM m_income WHERE 연도(dt)=2011) AS t
GROUP BY 블록;
Q051을 요청합니다.다음 쿼리는 사용자가 상품 수령 금액보다 많은 금액의 상품 소비에 대한 정보를 시스템에 입력했는지 확인하는 데 도움이 됩니다.
SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS output_sum
FROM (SELECT product_id, 금액*가격을 in_sum으로, 0을 out_sum으로
m_income에서
유니온 올
SELECT product_id, 0을 in_sum으로, amount*price를 out_sum으로
m_outcome에서) AS t
product_id로 그룹화
HAVING SUM(in_sum)
Q052를 요청하십시오.쿼리에서 반환된 줄의 번호 매기기는 다양한 방식으로 구현됩니다. 예를 들어 MS Access 자체를 사용하여 MS Access에서 준비된 보고서의 줄 번호를 다시 매길 수 있습니다. VBA 또는 PHP와 같은 프로그래밍 언어를 사용하여 번호를 다시 매길 수도 있습니다. 그러나 때로는 SQL 쿼리 자체에서 수행해야 합니다. 따라서 다음 쿼리는 ID 필드 값의 오름차순에 따라 m_income 테이블의 행 번호를 매깁니다.
SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price
FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;
Q053을 요청합니다.판매액 기준으로 상위 5개 제품이 표시됩니다. 처음 5개 레코드의 출력은 TOP 명령을 사용하여 수행됩니다.
SELECT TOP 5, product_id, sum(금액*가격) AS summa
m_outcome에서
product_id로 그룹화
ORDER BY 합계(금액*가격) DESC;
Q054를 요청하십시오.판매량 기준 상위 5개 제품이 표시되고 그 결과 라인에 번호가 매겨집니다.
SELECT COUNT(*) AS N, b.product_id, b.summa
에서
FROM m_outcome GROUP BY product_id) AS
내부 조인
(SELECT product_id, sum(amount*price) AS summa,
합계*10000000+product_id AS ID
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)ORDER BY COUNT(*);
Q055를 요청하십시오.다음 SQL 쿼리는 MS Access SQL에서 수학 함수 COS, SIN, TAN, SQRT, ^ 및 ABS의 사용을 보여줍니다.
SELECT (m_income에서 count(*) 선택) N, 3.1415926 as pi, k,
x로 2*pi*(k-1)/N, COS_로 COS(x), SIN_로 SIN(x), TAN_로 TAN(x),
SQR(x)는 SQRT_로, x^3은 "x^3"으로, ABS(x)는 ABS_로
FROM (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.idGROUP BY b.id) t;
SQL 쿼리. MS 액세스의 예. 업데이트: 1-10
U001을 요청합니다.다음 SQL 변경 쿼리는 m_income 테이블에서 코드 3이 있는 항목의 가격을 10% 인상합니다.업데이트 m_income SET 가격 = 가격*1.1
WHERE product_id=3;
U002를 요청하십시오.다음 SQL 업데이트 쿼리는 이름이 "Butter"라는 단어로 시작하는 m_income 테이블의 모든 제품 수량을 22 단위로 늘립니다.
UPDATE m_income SET 금액 = 금액+22
WHERE product_id IN (m_product WHERE 제목에서 "Oil*"과 같은 id 선택);
U003을 요청합니다. m_outcome 테이블의 다음 SQL 변경 쿼리는 OOO Sladkoe가 생산하는 모든 상품의 가격을 2% 인하합니다.
UPDATE m_outcome SET 가격 = 가격*0.98
WHERE product_id 입력
(m_product a INNER JOIN m_supplier b에서 a.id 선택
ON a.supplier_id=b.id WHERE b.title="OOO"Сладкое"");. !}
실습 #1
SQL: 데이터 검색 - 명령선택하다
작업의 목표:
- SQL 문에 익숙해지기
- SELECT 명령을 사용하여 Access에서 간단한 SQL 쿼리를 만드는 방법을 배웁니다.
· IN, BETWEEN, LIKE, IS NULL 연산자 사용.
운동№1. SQL 모드에서 STUDENTS 테이블의 FIRST NAME 및 LAST NAME 필드의 모든 값을 선택하는 쿼리를 작성하십시오.
이름, 성 선택
학생으로부터;
운동№2 . STUDENTS 테이블의 모든 열에 대해 SQL 모드에서 선택 쿼리를 만듭니다.
선택하다*
학생으로부터;
작업 번호 3. PERSONAL DATA 테이블에 있는 학생이 거주하는 도시의 이름을 SQL 모드에서 선택하는 쿼리를 생성합니다.
뚜렷한 도시 선택
[개인 데이터]에서;
작업 번호 4.정보가 STUDENTS 테이블에 있는 Ivanov라는 성을 가진 모든 학생의 이름을 선택하는 SQL 모드에서 선택 쿼리를 만듭니다.
성, 이름 선택
학생으로부터
WHERE SURNAME="이바노프";
작업 번호 5. 예산 지원 교육 형태로 UIT-22 그룹에서 공부하는 학생의 이름과 성을 얻기 위해 SQL 모드에서 선택 항목에 대한 쿼리를 생성합니다.
성, 이름 선택
학생으로부터
WHERE GROUP="HIT-22" AND BUDGET=true;
작업 번호 6. SQL 모드에서 쿼리를 생성합니다. EXAM PASSING 테이블의 샘플은 성적이 4와 5인 학생에 대한 정보입니다.
선택하다*
에서[변화시험]
어디등급안으로 (4.5);
작업 번호 7. IOSU 과목에서 시험 성적이 3점인 학생들에 대한 정보 샘플에 대한 zanpoc 및 SQL 모드를 만듭니다.
선택하다*
에서[변화시험]
어디안건=" ISSU" 그리고등급포함되지 않음(4.5);
작업 번호 8.시간이 100에서 130 사이인 항목에 대한 레코드를 선택하려면 SQL 모드에서 쿼리를 생성합니다.
선택하다*
에서항목
어디보다100에서 130 사이;
작업 번호 9. SQL 모드에서 쿼리를 생성하여 STUDENTS 테이블에서 성이 "C"로 시작하는 학생에 대한 정보를 선택합니다.
선택하다*
에서재학생
어디성좋다"와 함께*";
결론:실험실 작업 중에 우리는 SQL 문에 익숙해졌고 IN, BETWEEN, LIKE 연산자를 사용하여 SELECT 명령을 사용하여 Access에서 간단한 SQL 쿼리를 만드는 방법을 배웠습니다.
SQL 쿼리 예제는 MS Access에서 SQL 쿼리 작성을 배우고 연습하는 데 사용할 수 있습니다.
하나의 SQL 쿼리는 다른 쿼리 안에 중첩될 수 있습니다. 하위 쿼리는 쿼리 내의 쿼리에 지나지 않습니다. 일반적으로 하위 쿼리는 WHERE 절에서 사용됩니다. 그러나 하위 쿼리를 사용하는 다른 방법이 있습니다.
Q011을 요청합니다. m_product 테이블의 제품에 대한 정보를 표시하며 해당 코드는 m_income 테이블에도 있습니다.
선택하다*
m_product에서
WHERE id IN (m_income에서 product_id 선택);
Q012를 요청합니다. m_product 테이블의 제품 목록이 표시되며 해당 코드는 m_outcome 테이블에 없습니다.
선택하다*
m_product에서
WHERE id NOT IN(m_outcome에서 product_id 선택);
Q013을 요청합니다.이 SQL 쿼리는 m_income 테이블에는 코드가 있지만 m_outcome 테이블에는 없는 코드 및 제품 이름의 고유한 목록을 반환합니다.
SELECT DISTINCT product_id, 제목
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN(m_outcome에서 product_id 선택);
Q014를 요청하십시오.고유한 범주 목록이 m_category 테이블에서 표시되며 이름은 문자 M으로 시작합니다.
뚜렷한 제목 선택
m_product에서
WHERE 제목 LIKE "M*";
Q015를 요청하십시오.쿼리의 필드에 대해 산술 연산을 수행하고 쿼리의 필드 이름을 바꾸는 예(별칭). 이 예에서는 이익이 매출의 7%라고 가정하고 각 항목 소비 레코드에 대한 비용 = 수량*가격 및 이익을 계산합니다.
금액*가격/100*7 AS 이익
FROM m_outcom;
Q016을 요청합니다.산술 연산을 분석하고 단순화하여 쿼리 실행 속도를 높일 수 있습니다.
SELECT dt, product_id, amount, price, amount*price AS output_sum,
result_sum*0.07 AS 이익
FROM m_outcom;
Q017을 요청합니다. INNER JOIN 문을 사용하여 여러 테이블의 데이터를 결합할 수 있습니다. 다음 예에서는 ctgry_id 값에 따라 m_income 테이블의 각 항목이 제품이 속한 m_category 테이블의 카테고리 이름과 일치합니다.
SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
내부 조인 m_category AS c ON b.ctgry_id=c.id
ORDER BY c.제목, b.제목;
Q018을 요청합니다. SUM - 합계, COUNT - 수량, AVG - 산술 평균, MAX - 최대값, MIN - 최소값과 같은 함수를 집계 함수라고 합니다. 여러 값을 취하고 처리할 때 단일 값을 반환합니다. SUM 집계 함수를 사용하여 금액 필드와 가격 필드의 곱 합계를 계산하는 예입니다.