-- show command 로 정보 확인
show databases;
show tables;
show variables;
show processlist;
show variables like 'log_output%';
show variables like '%timeout%';
show variables like 'event%';
-- slow 쿼리 로깅 활성화, 상태 확인
-- slow_query_log_file 위치의 파일을 확인하면 된다.
set global slow_query_log = 'on';
show global variables like 'slow_query_log%';
-- event 활성
show variables like 'event%';
set global event_scheduler = on;
-- event 생성
create event delete_history
on schedule every 1 day
comment '매일 히스토리 정리'
do
delete from history1 where created_at < (now() - interval 6 month);
delete from history2 where start_at < (now() - interval 6 month);
show events;
drop event delete_history;
-- db 선택
use ysoftman;
-- mysql 버전확인
select version();
-- create
create table `test_info` (
`id` bigint(20) unsigned not null auto_increment,
`age` int(11) default null,
`name` varchar(45) default null,
`nickname` varchar(45) default null,
`enable` tinyint(1) default 1,
`created_at` datetime default current_timestamp,
`updated_at` datetime default current_timestamp,
`deleted_at` datetime default current_timestamp,
`last_date` datetime default null,
primary key (`id`)
) engine=innodb default charset=utf8;
-- show create table
show create table test_info;
-- alter
alter table test_info default character set utf8mb4 default collate utf8mb4_bin;
-- delete
delete from test_info where name='ysoftamn';
-- update
update ysoftman.test_info set nickname = 'xelloss' where name = 'bill';
update ysoftman.test_info set nickname = 'xelloss' where name in ('bill', 'yoon', 'naruto');
-- select
select * from user;
select * from user limit 10;
select * from user where name like '%ysoftman%' or name like '%bill%';
select * from user order by id desc;
select * from user where created_at >= (now() - interval 1 month);
-- BETWEEN min AND max 으로 사용해야 한다.
select * from user where created_at between (now() - interval 1 month) and (now() - interval 1 day);
-- 3개의 테이블 left, right join
select
svc1.name as svc1_name,
svc1.description as svc1_desc,
ifnull(svc2.aaa,'') as svc2_aaa,
ifnull(svc3.aaa,'') as svc3_aaa,
from
service1 svc1
right join service2 svc2 on
svc1.service2_id = svc2.id
left join service3 svc3 on
svc1.service3_id = svc3.id
where
s.name = 'bill'
order by
name asc;
-- major, minor 별 path 가장 높은 버전으로 조회
select
*
from
versions as t1
join (
select
version_major,
version_minor,
max(version_patch) as patch
from
versions
where
stable = 1
and version_major >= 0
and version_minor >= 1
group by
version_major,
version_minor
) as t2 on
t1.version_major = t2.version_major
and t1.version_minor = t2.version_minor
and t1.version_patch = t2.patch;
/*
mssql 저장 프로시져 테스트
*/
use [TestSP]
create proc SPQuery(
@a varchar(20),
@b varchar(20),
@c int,
@d float,
@e bigint,
@f int)
as
declare @idx int;
set @idx = 0;
while (@idx < 10)
begin
insert into mytable(col1, col2, col3, col4, col5, col6)
values(@a, @b, @c, @d, @e, @f)
set @idx = @idx + 1
end
/*
저장 프로시져 실행
*/
exec SPQuery 'ysoftman', yoon, 123, 1.0, 12345, 123
#####
# mysql 접속해서 쿼리 실행하기
# -h host
# -u user
# -p passowrd (-p뒤에 공백없이 패스워드 명시)
# -N, --skip-column-names 결과 출력에서 컬럼명 제외
# -B, --batch 결과 출력시 dash(-) 표시 제외
# -e statement
# key같이 예약어가 있을 수 있어 ``로 감싸주는게 좋다.
mysql -h 127.0.0.1 -u ${mysql_user} -p${mysql_password}" mydb -NB -e 'select `key` from ysoftman;' 2> /dev/null
