βΆ NULL μ°μ°μ
β
μμ 9-17) ν΄λν°μ κ°μ§κ³ μλ νμμ νλ²κ³Ό μ΄λ¦, ν΄λν° λ²νΈλ₯Ό λνλ΄μ΄λΌ.
A)
mysql> select stu_no, stu_name, mobile
-> from student
-> where mobile is not null;
ββ
β
μμ 9-18) ν΄λν°μ κ°μ§κ³ μμ§ μμ νμμ νλ²κ³Ό μ΄λ¦, ν΄λν° λ²νΈκ° NULLμΈ κ²½μ°μλ "ν΄λν° μμ"μ λνλ΄μ΄λΌ.
A)
mysql> select stu_no, stu_name, ifnull(mobile, 'ν΄λν°μμ')
-> from student
-> where mobile is null;
β
ββ
μμ 9-19) νμμ ν΄λν°λ²νΈ(MOBILE)κ° 010μ΄ μλ λͺ¨λ νμμ νλ²κ³Ό μ΄λ¦, ν΄λν°λ²νΈλ₯Ό μΆλ ₯νλΌ. (λ¨, ν΄λν°μ΄ μλ νμλ ν¬ν¨λμ΄ μΆλ ₯λμ΄μΌ νλ€)β
A)
mysql> select stu_no, stu_name, mobile
-> from student
-> where substring(mobile, 1, 3) <> '010'
-> or mobile is null;
β
ββ
β
βΆ λΆμμ§μμ΄μμ IN μ°μ°μβ
β
μμ 9-20) λ±λ‘μ ν κ° νμμ νλ², μ΄λ¦μ μΆλ ₯νλΌ. (inμ°μ°μλ₯Ό μ΄μ©)
A)
mysql> select stu_no, stu_name
-> from student
-> where stu_no in (20141001, 20161001, 20191004, 20191005,
-> 20191006, 20191007, 20191008, 20201002);
ββ
β
μμ 9-21) λΆμμ§μμ΄λ₯Ό μ΄μ©νμ¬ λ±λ‘μ ν κ° νμμ νλ², μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from student
-> where stu_no in
-> (select stu_no from fee);
β
ββ
μμ 9-22) μ μ΄λ ν λ²μ μ₯νκΈμ λ°μλ νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from student
-> where stu_no in
-> (select stu_no from fee where jang_total > 0);
ββ
β
μμ 9-23) "20191009"μΈ νμμ΄ κ°μ ν λμ리λ₯Ό μ μΈν λ€λ₯Έ λμ리μ μ μ΄λ ν λ² κ°μ μ ν νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from student
-> where stu_no in
-> (select stu_no from circle where cir_name not in
-> (select cir_name from circle where stu_no = '20191009'));
ββ
β
μμ 9-24) ν΄λν°μ κ°μ§κ³ μλ νμμ μΆλ ₯νλΌ. (λ¨, ν΄λν°μ΄ μμ΄λ μΌκ°μΈ νμμ μ μΈνλ€)
A)
mysql> select stu_no, stu_name, mobile
-> from student
-> where mobile not in
-> (select mobile from student where juya='μΌ');
β
β
ββ
βΆ λΆμ μ§μμ΄μμ κ΄κ³ μ°μ°μ
ββ
μμ 9-25) μκ°μ μ²μ ν νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from student
-> where stu_no in
-> (select stu_no from attend where att_div = 'y');
ββ
β
μμ 9-26) μ₯μμΈ(1999λ μ) λ³΄λ€ λμ΄κ° λ λ§μ κ° νμμ νλ²κ³Ό μ΄λ¦, μλ μμΌμ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name, birthday
-> from student
-> where substring(birthday, 1, 4) <
-> (select substring(birthday, 1, 4)
-> fromβ student
-> where stu_name = 'μ₯μμΈ');
β
β
β
βΆ ALLκ³Ό ANY μ°μ°μ
β
-ANY μ°μ°μ
> ANY : μ΅μκ° λ³΄λ€ ν¬λ©΄
>= ANY : μ΅μκ°λ³΄λ€ ν¬κ±°λ κ°μΌλ©΄
< ANY : μ΅λκ°λ³΄λ€ μμΌλ©΄
<= ANY : μ΅λκ°λ³΄λ€ μκ±°λ κ°μΌλ©΄
= ANY : INκ³Ό κ°μ ν¨κ³Ό
!= ANY : NOT INκ³Ό κ°μ ν¨κ³Ό
β
-ALL μ°μ°μ
> ALL : μ΅λκ° λ³΄λ€ ν¬λ©΄
>= ALL : μ΅λκ°λ³΄λ€ ν¬κ±°λ κ°μΌλ©΄
< ALL : μ΅μκ°λ³΄λ€ μμΌλ©΄
<= ALL : μ΅μκ°λ³΄λ€ μκ±°λ κ°μΌλ©΄
= ALL : SUBSELECTμ κ²°κ³Όκ° 1건μ΄λ©΄ μκ΄μμ§λ§ μ¬λ¬ 건μ΄λ©΄ μ€λ₯κ° λ°μνλ€.
!= ALL : μμ λ§μ°¬κ°μ§λ‘ SUBSELECTμ κ²°κ³Όκ° μ¬λ¬ 건μ΄λ©΄ μ€λ₯κ° λ°μνλ€.
ββ
β
μμ 9-27) κ°μ₯ λμ΄κ° λ§μ νμμ νλ², μ΄λ¦, μλ μμΌμ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name, birthday
-> from student
-> where birthday <= all
-> (select birthday from student);
β
β
μμ 9-28) κ°μ₯ λμ΄κ° λ§μ νμ(λ°λμ)μ μ μΈν λλ¨Έμ§ λͺ¨λ νμμ νλ², μ΄λ¦, μλ μμΌμ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name, birthday
-> from student
-> where birthday > any
-> (select birthday from student);
β
β
μμ 9-29) νλ² 20191004μΈ νμμ΄ λ±λ‘ν λ±λ‘κΈμ λ©λΆμ΄μ‘(1,000,000)λ³΄λ€ λ λ§μ λ±λ‘κΈμ λΈ νμμ νλ²κ³Ό λ©λΆμ΄μ‘μ μΆλ ₯νλΌ. μ΄λ 20191004λ κ²°κ³Όμμ μ μΈνλ€.
A)
mysql> select distinct stu_no, fee_pay
-> from fee
-> where stu_no <> '20191004'
-> and fee_pay > any
-> (select fee_pay from fee where stu_no = '20191004');
β
β
β
βΆ EXISTS μ°μ°μβ
β
μμ 9-30) λ±λ‘μ ν νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from student
-> where stu_no in
-> (select stu_no from fee);
ββ
β
μμ 9-31) λ±λ‘νμ§ μμ νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from student
-> where not exists
-> (select * from fee where stu_no = student.stu_no);
β
β
μμ 9-32) νμ ν μ΄λΈμμ νλ², μ΄λ¦, ν΄λν°λ²νΈ, μ°νΈλ²νΈλ₯Ό μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name, mobile, post_no from student;
β
β
μμ 9-33) κ°κ°μ λμμ κ±°μ£Όνλ λͺ¨λ νμμ λνμ¬ ν΄λν°μ κ°μ§κ³ μλ νμμ νλ²κ³Ό μ΄λ¦, μ°νΈλ²νΈ, ν΄λν°λ²νΈλ₯Ό λνλ΄μ΄λΌ. (λ¨, ν΄λν°μ΄ μλ νμκ³Ό ν΄λν°μ΄ μλ νμμ μ°νΈλ²νΈ μ 1μλ¦¬κ° λμΌν νμμ μ μΈμν¨λ€.)
A)
mysql> select stu_no, stu_name, post_no, mobile
-> from student s1
-> where not mobile in
-> (select mobile from student s2
-> where substring(s1.post_no, 1, 1) = substring(s2.post_no, 1, 1)
-> and mobile is null);
β
β
β
βΆ λΆμ 쑰건
β
μμ 9-34) "JavaκΈΈλΌμ‘μ΄" λμ리μ κ°μ ν νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from circle
-> where cir_name = 'JavaκΈΈλΌμ‘μ΄';
β
β
μμ 9-35) "JavaκΈΈλΌμ‘μ΄" λμ리μ κ°μ νμ§ μμ νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name, cir_name
-> from circle
-> where cir_name <> 'JavaκΈΈλΌμ‘μ΄';
β
β
μμ 9-36) λ±λ‘ ν μ΄λΈμμ μ₯ν μ½λκ° '11' νμμ νλ²κ³Ό μ₯νμ½λ, μ₯νκΈ μ΄μ‘μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, jang_code, jang_total
-> from fee
-> where jang_code = 11;
β
β
μμ 9-37) λ±λ‘ν μ΄λΈμμ μ₯νμ½λκ° '11'μ΄ μλ νμμ νλ²κ³Ό μ₯νμ½λ, μ₯νκΈ μ΄μ‘μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, jang_code, jang_total
-> from fee
-> where jang_code <> 11;
β
β
μμ 9-38) λ±λ‘ν μ΄λΈμμ μ₯νμ½λκ° '11'μ΄ μλ νμμ νλ²κ³Ό, μ₯νμ½λ, μ₯νκΈ μ΄μ‘μ μΆλ ₯νλΌ. (NOT IN μ΄μ©)
A)
mysql> select stu_no, jang_code, jang_total
-> from fee
-> where jang_code not in
-> (select jang_code from fee where jang_code in (11));
β
β
μμ 9-39) λ±λ‘ν μ΄λΈμμ μ₯νμ½λκ° '11'μ΄ μλ νμμ νλ²κ³Ό μ₯νμ½λ, μ₯νκΈ μ΄μ‘μ μΆλ ₯νλΌ.
(λ¨, NOT IN μ΄μ©νκ³ μ₯νμ½λκ° NULLμΈ νμλ ν¬ν¨νμ¬ μΆλ ₯νλΌ.)
A)
mysql> select stu_no, jang_code, jang_total
-> from fee
-> where jang_code not in
-> (select jang_code from fee where jang_code in (11))
-> or jang_code is null;
β
β
ββ
<μ°μ΅λ¬Έμ >
9-1) 2000λ μ΄νμ μΆμν κ° νμμ νλ²μ λνλ΄μ΄λΌ.
A)
mysql> select stu_no, birthday from student where substring(birthday, 1, 4) > 2000;
β
β
9-2) νλ² 20141001λ²μ΄ λ±λ‘νμλ€λ©΄ νμμ μ΄λ¦μ λνλ΄μ΄λΌ.
A)
mysql> select stu_name from student where stu_no = '20141001';
β
β
9-4) μ μ΄λ ν λ² μ΄μ μ₯νκΈμ λ°μ νμμ νλ²μ μΆλ ₯νλΌ.
A)
mysql> select stu_no
-> from student
-> where stu_no in
-> (select stu_no from fee where jang_total > 0);
β
β
9-5) 1,500,000μ μ΄μ μ₯νκΈμ λ°μ νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select distinct s.stu_no, stu_name, jang_total
-> from student s, fee f
-> where s.stu_no = f.stu_no and jang_total >= 1500000;
β
β
9-6) μ±λ³μ΄ λ¨μκ° μλ νμμ νλ²κ³Ό μ΄λ¦μ μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name
-> from student
-> where (gender % 2 = 0);
β
β
9-7) 1998λ λΆν° 2001λ μ¬μ΄μ μΆμν νμμ νλ²κ³Ό μ΄λ¦, μΆμλ λλ₯Ό μΆλ ₯νλΌ.
A)
mysql> select stu_no, stu_name, substring(birthday, 1, 4) μΆμλ λ
-> from student
-> where substring(birthday, 1, 4) between 1998 and 2001;
β
β
9-8) μ μ΄λ ν λ²μ μ₯νκΈμ λ°κ³ 1999λ μ΄νμ μΆμν νμμ νλ², μ΄λ¦, μ₯νκΈ μ΄μ‘μ μΆλ ₯νλΌ.
A)
mysql> select s.stu_no, stu_name, jang_total
-> from student s, fee f
-> where s.stu_no = f.stu_no and jang_total is not null
-> and substring(birthday, 1, 4) > 1999;
β
β
9-10) μ₯νκΈ μλ Ή μ΄μ‘μ΄ 500,000μμμ 2,000,000μ μ¬μ΄μ ν¬ν¨λλ κ° νμμ νλ²μ μΆλ ₯νλΌ.
A)
mysql> select distinct stu_no from fee where jang_total between 500000 and 2000000;
'Data Base > MySQL' μΉ΄ν κ³ λ¦¬μ λ€λ₯Έ κΈ
MySQL 19λ²μ§Έμμ (0) | 2021.02.08 |
---|---|
MySQL 18λ²μ§Έμμ (0) | 2021.02.08 |
MySQL 16λ²μ§Έμμ (0) | 2021.02.08 |
MySQL 15λ²μ§Έμμ (0) | 2021.02.08 |
MySQL 14λ²μ§Έμμ (0) | 2021.02.08 |
λκΈ