mysql 명령어

 

테이블, 필드 생성 확인 수정 삭제

mysql
create database [name]; # db 생성
drop database [name]; # db 삭제
show databases; # db 확인
use [name]; # db 사용
select database(); # 현재 사용중인 db 확인
show tables; # 사용중인 db의 table 확인
create table [name] (field option); # 테이블생성 괄호안은 필드
desc [name]; # 테이블의 필드 확인
drop table [name]; # 테이블 삭제
alter table [name] rename [name2]; # 테이블 이름 변경
alter table [table name] add column [field option]; # 필드 추가
alter table [name] drop column [field]; # 필드 삭제
alter table [name] change column [field] [field2 option]; # 필드 수정
alter table [name] change column [field] [field option]; # 같은 이름 두번써서 타입만 변경
alter table [name] modify column [field option]; # 타입변경 위와 같은 기능

ex)

create table testT ( num int(4), user char(10), email char(20))

alter table testT rename testT1; 
alter table testT1 add column homepage char(50); 
alter table testT1 drop column email; 
alter table testT1 change column homepage home char(80); 
alter table testT1 change column home home char(100);
alter table testT1 modify column home char(100); 

 

필드 내용 생성 확인 수정 삭제

insert into [table](field) value([value]); # 필드 내용 입력
insert into [table] value([value]); # field 생략시 field 순서대로 입력
select * from [table]; # 전체 필드 내용 확인
select [value] from [table] where [con]; # 조건에 맞는 값을 확인
update [table] set [field]=[value] where [con]; # 조건에 맞는 값을 수정
delete from [table] where [con]; # 조건에 맞는 값을 삭제 / 조건이 없으면 전부 삭제

ex)

insert into testT2(uid,name,email,homepage) value(1,'admin','admin@abc.com','www.admin.com'); 

insert into testT2 value(1,'admin','admin@abc.com','www.admin.com'); 

select * from testT2; 

select name,email from testT2 where uid=1;

update testT2 set email='www.naver.com' where uid=1;

delete from testT2 where uid=7;

 

테이블 상세 검색

select * from [table] limit [int]; # int값만큼만 출력
select [field1],[field2]... from [table]; # 지정 필드 내용만 출력
select distinct [field] from [table] # 중복 제거후 출력
select [field] as [name] from [table]; # 출력되는 field명 변경
select [field], '[content]', [field] from [table]; # 필드 사이에 내용 추가
select [field] + [int] from [table]; # int만큼 더해서 출력 사칙연산 모두 가능
select [filed1] from [table] where [field2] [ex] # filed2에 ex 조건에 맞는 filed1 내용 출력
[ex]
like '%[value]%' value 값이 포함된것 / [value]% value로 시작되는것 / %[value] value로 끝나는것
is null 내용이 빈것 / is not null 내용이 비어있지 않은것
[field] >,< [num] num 보다 field 값이 큰것,작은것 
order by [field] asc/desc field값으로 정렬 오름/내림차순 default는 오름차순
in [con] 조건이 포함된 내용 출력 / not in [con] 조건이 포함되지 않은 내용 출력
between [con1] and [con2] 조건1과 2사이 내용이 포함된 내용 출력


select [field] from [table] group by [field2] # field2를 그룹으로 묶어서 출력
select [field] from [table] having [con2] # con2에 맞는 내용을 출력 단, field에 조건필드가 있어야함
ex) select uid,name from testT3 having name='aaa'

select upper,ucase([field]) from [table]; # field값을 모두 대문자로 변경
select lower,lcase([field]) from [table]; # field값을 모두 소문자로 변경
select concat([field1],[field2],...) from [table]; # field값을 묶어서 출력
select concat_ws('v',[field1],[field2],...) from [table]; # field값들 사이에 v값을 넣고 출력
select substr([field],n1,n2) from [table]; # field값을 n1자리부터 n2만큼 잘라서 출력
select left,right([field],n) from [table]; # field값을 왼쪽/오른쪽에서 부터 n자리 잘라서 출력
select length([field]) from [table]; # field값의 길이를 출력
select trim,ltrim,rtrim([field]) from [table]; # field값의 좌우공백,왼쪽공백,오른쪽공백을 잘라서 출력
select lpad,rpad([field],n,v) from [table]; # field값의 좌/우에 n자리가 되도록 v값을 넣어서 출력
select replace([field],v1,v2) from [table]; # field값에서 v1을 v2로 변환하여 출력
select instr([field],v) from [table];   field값에서 v의 첫 위치 반환, 없을 시 0


cast(v as [type]), convert(v,[type]) : v값을 type자료형으로 변경

% : 와일드카드, %자리에 값이 있다면 자리수에 상관없이 출력
ex) select 'hello' like 'hel%'; = true

_ : 한 자리 와일드카드, _의 개수만큼 있다면 출력
ex ) select 'hello' like 'h_o'; = false / select 'hello' like 'h___o'; = true

 

예제

create table testT3 (uid int(4), name char(10), email char(40), age int(4),
sex char(4), save int(10), milage int(10), level int(4));

insert into testT3 value (1,'aaa','aaa@abc.com',11,'M',1111,1000,11);
insert into testT3 value (2,'aaa','aaa@abc.com',33,'M',1111,1000,11);
insert into testT3 value (3,'bbb','bbb@abc.com',33,'F',2111,2000,21);
insert into testT3 value (4,'ccc','ccc@abc.com',33,'M',3111,3000,31);
insert into testT3 value (5,'ddd','ddd@abc.com',44,'F',4111,4000,41);
insert into testT3 value (6,'eee','eee@abc.com',55,'M',5111,5000,51);
insert into testT3 value (7,'bbb','bbb@abc.com',11,'F',2111,2000,21);
insert into testT3 value (8,'ccc','ccc@abc.com',22,'M',3111,3000,31);
insert into testT3 value (9,'ddd','ddd@abc.com',22,'F',4111,4000,41);
insert into testT3 value (10,'eee','eee@abc.com',22,'M',5111,5000,51);

+------+------+-------------+------+------+------+--------+-------+
| uid  | name | email       | age  | sex  | save | milage | level |
+------+------+-------------+------+------+------+--------+-------+
|    1 | aaa  | aaa@abc.com |   11 | M    | 1111 |   1000 |    11 |
|    2 | aaa  | aaa@abc.com |   33 | M    | 1111 |   1000 |    11 |
|    3 | bbb  | bbb@abc.com |   33 | F    | 2111 |   2000 |    21 |
|    4 | ccc  | ccc@abc.com |   33 | M    | 3111 |   3000 |    31 |
|    5 | ddd  | ddd@abc.com |   44 | F    | 4111 |   4000 |    41 |
|    6 | eee  | eee@abc.com |   55 | M    | 5111 |   5000 |    51 |
|    7 | bbb  | bbb@abc.com |   11 | F    | 2111 |   2000 |    21 |
|    8 | ccc  | ccc@abc.com |   22 | M    | 3111 |   3000 |    31 |
|    9 | ddd  | ddd@abc.com |   22 | F    | 4111 |   4000 |    41 |
|   10 | eee  | eee@abc.com |   22 | M    | 5111 |   5000 |    51 |
+------+------+-------------+------+------+------+--------+-------+

select name, 'milage is' , milage+300 as bonus from testT3 where milage > 2000 order by milage desc;

+------+-----------+-------+
| name | milage is | bonus |
+------+-----------+-------+
| eee  | milage is |  5300 |
| eee  | milage is |  5300 |
| ddd  | milage is |  4300 |
| ddd  | milage is |  4300 |
| ccc  | milage is |  3300 |
| ccc  | milage is |  3300 |
+------+-----------+-------+

milage가 2000 보다 큰 값들중에 name과 milage 사이에 milage is 를 추가하고 milage에 300을 더한 후 milage를 bonus로 바꿔서 내림차순으로 출력한다

 

테이블 연산

select count(*) from [table] where [con]; # 조건에 맞는것의 개수
select avg([field]) from [table] # field값의 평균
select sum([field]) from [table] # field값의 합
select max([field]) from [table] # field값중 가장 큰것
select min([field]) from [table] # field값중 가장 작은것
select round,ceil,floor([field]) from [table]; # field값을 반올림,올림,내림
select abs([field]) from [table]; # field값의 절대값
select greatest,least([field1],[field2],...) from [table] # field값들중 가장 큰것/작은것
select pow([field],n) from [table]; # field값의 n제곱
select sqrt([field]) from [table]; # field값의 root, 1/2제곱
select truncate([field],n) from [table]; # field값을 소수점 n자리까지 표시 음수면 정수자리를 0으로

조건문

case when [con] then [result] # when 뒤에는 조건 then 뒤에는 결과
if([con],t,f) # 조건이 true면 t값을 출력 false면 f값을 출력
ifnull(a,b) # a값이 null이면 b값을 출력

ex)

select productname,categoryid,

case when categoryid <= 3 then '소비재'

when categoryid between 4 and 5 then '사치재'

else 'etc' end as 분류

from products order by 분류

 

JOIN

select [field] from [table1] join [table2] # 두 테이블의 교집합만 출력 = inner join

select [field] from [table1] left/right join [table2] on [table1.field] = [table2.field] 
# 왼쪽/오른쪽은 모두 출력 오른쪽/왼쪽은 교집합만 출력 없으면 null

select [field] from [table1] outer join [table2] on [table1.field] = [table2.field]
# 왼쪽과 오른쪽 모두 출력

select [field] from [table1] cross join [table2] on [table1.field] = [table2.field]
# table1 * table2 / 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인

select [field] from [table] [alias1] self join [table] [alias2] on [alias1.field] = [alias2.field]
# 자기 자신에게 조인 테이블 1개만 사용

UNION # 중복을 제거한 집합
UNION ALL # 중복을 제거하지 않은 집합

True, False

True면 1 False면 0을 반환

SELECT TRUE, FALSE;

+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+

SELECT 3 between 1 and 5;

+-------------------+
| 3 between 1 and 5 |
+-------------------+
|                 1 |
+-------------------+

!,not을 붙이면 반대의미
!true,not true = false / !false, not false = true

날짜, 시간

CURRENT_DATE, CURDATE()		# 현재 날짜 반환
CURRENT_TIME, CURTIME()		# 현재 시간 반환
CURRENT_TIMESTAMP, NOW()	# 현재 시간과 날짜 반환
DATE([field])	# 문자열에 따라 날짜 생성
TIME([field])	# 문자열에 따라 시간 생성
YEAR	        # 주어진 DATETIME값의 년도 반환
MONTHNAME		# 주어진 DATETIME값의 월(영문) 반환
MONTH	        # 주어진 DATETIME값의 월 반환 
WEEKDAY			# 주어진 DATETIME값의 요일값 반환(월요일: 0)  숫자로 반환
DAYNAME			# 주어진 DATETIME값의 요일명 반환
DAYOFMONTH, DAY	# 주어진 DATETIME값의 날짜(일) 반환
HOUR	        # 주어진 DATETIME의 시 반환
MINUTE	        # 주어진 DATETIME의 분 반환
SECOND			# 주어진 DATETIME의 초 반환
ADDDATE, DATE_ADD	# 시간/날짜 더하기
SUBDATE, DATE_SUB	# 시간/날짜 빼기
ex)
ADDDATE('2023-08-03', INTERVAL 1 YEAR) 
ADDDATE('2023-08-03', INTERVAL -2 MONTH) 
ADDDATE('2023-08-03', INTERVAL 3 WEEK)

DATEDIFF(D1,D2)	# 두 시간/날짜 간 일수차
TIMEDIFF(D1,D2)	# 두 시간/날짜 간 시간차
LAST_DAY	#해당 달의 마지막 날짜

DATE_FORMAT(D,[fromat])	# 시간/날짜를 지정한 형식으로 반환
%Y	년도 4자리
%y	년도 2자리  -> 끝의 2자리만 표시 
%M	월 영문
%m	월 숫자
%D	일 영문(1st, 2nd, 3rd...)
%d, %e	일 숫자 (01 ~ 31)
%T	hh:mm:ss
%r	hh:mm:ss AM/PM
%H, %k	시 (~23)
%h, %l	시 (~12)
%i	분
%S, %s	초
%p	AM/PM
ex) DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초')

 

 

참고 링크

https://dev.mysql.com/

MySQL :: Developer Zone

https://dev.mysql.com/doc/refman/8.0/en/mysql.html

MySQL :: MySQL 8.0 Reference Manual :: 4.5.1 mysql — The MySQL Command-Line Client

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_select_all

MySQL Tryit Editor v1.0