docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root1! --name mysql57 mysql:5.7
-e
는 환경변수. os가 쓰는 환경변수인거임. mysql 최고관리자의 password, 네이밍, 버전붙이기(중요)
docker exec -it mysql57 bash
들어가기
mysql -u root -p
mysql 세계로 들어감
-create database <db-name>
show databases
use <db-name>
host는 어디에서의 접근 열어주는지. localhost, %(everywhere), 특정 주소(ip)
User 생성
create user <user-name>@'<host>' identified by '<password>';
ex) create user jbwoo@'%' identified by 'sol1!';
권한 부여
ㄴ grant all privileges on *.* to 'user-name'@'<host>';
*.*
이렇게하면 모든 db에 모든 권한을 다 주는거기에 이렇게 하진 않음
ㄴ grant all privileges on <DB>.* to 'user-name'@'%';
이렇게 특정 DB 권한 부여
적용하기
flush privileges
User 삭제
drop user '<사용자>'@'<host>';
접속
mysql -u <userID> -p
권한 확인
show grants from '<user-name>'@'<host>';
권한 삭제(취소)
revoke all privileges on <db-name>.* from <user-name>@'<host>';
User 삭제
drop user '<사용자>'@'<host>';
show create table test_table;
생성했던 table 보여줌
update Student set tel='010-2222-3333' where id = 4;
student table에 id 4 인 data의 tel을 바꿈
create table t_student like Student;
insert into t_student select * from Student
테이블 복사해서 하나 더 생성하고 데이터들도 같은걸 넣어줌
truncate t_student
delete from t_student whrere id> 0
;truncate
로 지워야 `auto increment' 도 초기화됨 0부터 다시시작.%
, _
%
는 글자가 뭐가와도, n개가 와도 됨. _
는 한글자
select * from Student where name like '김%'
;
student 테이블에서 '김'으로 시작하는 name으로 해당하는 걸 찾아옴
select * from Student where name like '김%현'
;
김으로 시작해서 현으로 끝나는 사람
select * from Student where name like '%현'
;
현으로 끝나는 사람
select * from Student where id in (10, 20, 30)
;
select * from Student where id = 10 or id = 20 or id =30
;
select * from Student where id >= 10 and id <= 30
;
select * from Student where id between 10 and 30
;
email이 a로 시작하고, tel의 가운데 숫자가 9000 이상인 학생 추출
select * from Student where email like 'a%' and tel like '010-9%';
distinct
: 중복 제거용
select distinct(birth) from Student s where birth='700601';
select birth from Student s where birth='700601';
밑에꺼는 중복된 데이터를 다 주는데 distinct
는 중복을 제거할 때 사용함.
order by
, rand()
, desc
: 정렬 기준
select * from Student order by addr, name desc;
order by
는 by 뒤에 오는 순서대로 정렬해줌. desc
으로 name은 역순 걸어주었음
select * from Student order by rand();
랜덤 정렬
limit
: 몇 개 줄까? 어디부터?
몇개까지만 줄까?
limit num
: num 개수만 달라.
limit (start_index, num)
: start_index 갯수 다음부터, num 갯수만큼.
limit (10, 10)
: 2페이지 조회할 때 처럼 11~20 까지 줌
강원 지역 학생 중 어린 순서로 11번째부터 5명 추출
select * from Student where addr = '강원' and ordey by birth desc limit 10, 5;
group by (colum name)
: 컬럼 기준으로 그룹으로 묶자
지역별 학생 수
select addr, count(*) from Student group by addr;
지역별 학생 수. 학생 순서 많은 순, 적은 순
select addr, count(*) as cnt from Student group by addr order by cnt;
select addr, count(*) as cnt from Student group by addr order by cnt desc;
having
: group by 해놓고 조건을 걸고 싶을 때 쓰는 친구지역별 학생 수가 250명 이상인 지역들만 추출
select addr, count(*) as cnt from Student group by addr where cnt >= 250
: 오답
그룹핑 한거에서 where 못씀
select addr, count(*) as cnt from Student group by addr having cnt >= 250
case when ~ else ~ end
select name, birth, (case when birth like '7%' then '70년대생' when birth like '8%' then '80년대생' else '그 이하 출생년생' end) lfrom Student limit 10;
위에꺼는 == 이라 like가 필요없었음
성별 추가하기
위에꺼는 safe를 위해 where id > 0
을 추가해야함.
update Student set gender = (case when name like '%혜%' or name like '%솔%' or name like '%효%' or name like '%숙%' or name like '%민%' or name like '%현%' or name like '%희%' or name like '%영%' or name like '%주%' then 0 else 1 end) where id > 0;
Relation
CREATE TABLE.. (
…
[Constraint] foreign key <idx-name> (col1) references tbl2(col2)
ON [DELETE | UPDATE] [RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]
);
student가 지워졌을 때, on DELETE
를 쓰고 default인
RESTRICT
는 leader가 있으면 안지워짐. 안전
CASCADE
는 둘 다 지워짐(주의!!)
SET NULL
은 leader 를 NULL 로. 이거도 많이 씀
NO ACTION
아무것도 안하겠다.
SET DEFAULT
는 Leader 의 DEFAULT
값을 설정했었다면 해당 default 값으로 변경
여기서는 뭐가 잘못되었는지 맞춰보시오.
create table Subject(
id smallint unsigned not null auto_increment primary key,
name varchar(31) not null,
prof smallint unsigned not null,
constraint foreign key fk_prof_prof (prof) reference Prof(id)
on delete set null
prof를 not null 로 설정해놓고 교수가 삭제되면 prof를 null로 설정하려함
연습 - 테이블생성
create table Club(
id smallint unsigned not null auto_increment primary key,
name varchar(31) not null,
createdate timestamp not null default current_timestamp,
leader int unsigned,
constraint foreign key fk_leader_student(leader) references Student(id)
);
create table Prof(
id smallint unsigned not null auto_increment primary key,
name varchar(31) not null,
likecnt int not null default 0
);
create table `Subject` (
id smallint unsigned not null auto_increment primary key,
name varchar(31) not null,
prof smallint unsigned,
constraint foreign key fk_prof_prof (prof) references Prof(id) on delete set null
);
create table Enroll(
id int unsigned not null auto_increment primary key,
subject smallint unsigned not null,
student int unsigned not null
);
alter table Enroll add constraint foreign key fk_subject (subject) references Subject(id) on delete cascade;
alter table Enroll add constraint foreign key fk_student (student) references Student(id) on delete cascade;
데이터 추가 및 수정
select c.*, s.name as 'student name' from Club c inner join Student s on c.leader = s.id;
select rand() from dual;
: 난수 생성
100명의 교수 생성하기
insert into Prof(name, likecnt) select name, ceil(rand() * 100) from Student order by rand() limit 100;
과목을 전부 국어로 생성한 뒤 다른 과목으로 바꿔주는 방법
insert into Subject(name, prof) select '국어', id from Prof order by rand() limit 10;
update Subject set name='물리' where id = 3;
update Subject set name='정치' where id = 4;
update Subject set name='수학' where id = 5;
update Subject set name='과학' where id = 6;
아래가 확실히 더 괜찮다.
update Subject set name='역사' where name="국어" and id <> 10 limit 1;
<> or !=
create table Club(
id smallint unsigned not null auto_increment primary key,
name varchar(31) not null,
createdate timestamp not null default current_timestamp,
leader int unsigned,
constraint foreign key fk_leader_student(leader) references Student(id)
);
create table Prof(
id smallint unsigned not null auto_increment primary key,
name varchar(31) not null,
likecnt int not null default 0
);
create table `Subject` (
id smallint unsigned not null auto_increment primary key,
name varchar(31) not null,
prof smallint unsigned,
constraint foreign key fk_prof_prof (prof) references Prof(id) on delete set null
);
create table Enroll(
id int unsigned not null auto_increment primary key,
subject smallint unsigned not null,
student int unsigned not null
);
alter table Enroll add constraint foreign key fk_subject (subject) references Subject(id) on delete cascade;
alter table Enroll add constraint foreign key fk_student (student) references Student(id) on delete cascade;
insert into Subject(name, prof) select '국어', id from Prof order by rand() limit 10;
update Subject set name='물리' where id = 3;
update Subject set name='정치' where id = 4;
update Subject set name='수학' where id = 5;
update Subject set name='과학' where id = 6;
update Subject set name='사문 ' where name="국어" and id <> 10 limit 1;
select * from Subject;
select c.*, s.name as 'student name' from Club c inner join Student s on c.leader = s.id;
select rand() from dual;
insert into Prof(name, likecnt) select name, ceil(rand() * 100) from Student order by rand() limit 100;
select * from Prof;
CREATE TABLE `Club` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(31) CHARACTER SET latin1 NOT NULL,
`createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`leader` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_leader_student` (`leader`),
CONSTRAINT `Club_ibfk_1` FOREIGN KEY (`leader`) REFERENCES `Student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
select count(*) from user where identify = ? and pwd = password(?);
desc Club;
create table Test1(
id tinyint unsigned not null auto_increment,
name char(5) not null,
primary key(id)
);
CREATE TABLE `test3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ttt` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
desc Test1;
show create table Test1;
insert into Test1(name) values('김일수');
select * from Test1;
CREATE TABLE `doodb`.`User` (
`id` mediumint unsigned NOT NULL AUTO_INCREMENT,
`email` VARCHAR(45) NOT NULL,
`password` CHAR(128) NOT NULL,
`name` VARCHAR(40) NOT NULL,
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
UNIQUE INDEX `email_UNIQUE` (`email` ASC),
PRIMARY KEY (`email`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci
COMMENT = 'user_table';
CREATE TABLE if not exists `User` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'user_id',
`email` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'user_email',
`password` char(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'user_password',
`name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'user_name',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='user_table';
CREATE TABLE if not exists `Accountlist` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'accountlist_id',
`name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'default_account',
`user_id` int unsigned NOT NULL COMMENT 'link_for_user_and_accountlist\n',
PRIMARY KEY (`id`),
KEY `Accountlist_ibfk_1` (`user_id`),
CONSTRAINT `Accountlist_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
create table if not exists `Tradeitem` (
`id` bigint unsigned not null auto_increment primary key comment 'trade item id',
`trade_date` timestamp not null default current_timestamp comment 'trade item timestamp',
`item_name` varchar(40) not null default 'default: 거래내역 1건' comment 'trade item name',
`item_memo` varchar(100) not null default '-' comment 'about trade memo',
`trade_value` bigint not null default 0 comment 'value of trade item',
`for_ref_list` int unsigned comment 'for link with account list',
constraint foreign key fk_trade_list(for_ref_list) references Accountlist(id) on delete no action
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
create table if not exists `Deletedlist` (
`id` int not null primary key auto_increment COMMENT 'Deletedlist id',
user_id int unsigned not null COMMENT 'user_id at Deletedlist',
constraint foreign key fk_del_user_id(user_id) references User(id) on delete no action
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
create table if not exists `Deleteditem` (
`id` bigint unsigned not null auto_increment primary key COMMENT 'deleted item id',
`recode_date` timestamp not null default current_timestamp COMMENT 'deleted item timestamp',
`item_name` varchar(40) not null default 'deafult: 거래내역 1건' COMMENT 'default deal name' ,
`value` bigint not null default 0 COMMENT 'value of deleted item',
`original_list` varchar(40) COMMENT 'come from which list',
del_table_id int COMMENT 'for link with delete table',
constraint foreign key fk_del_item_id(del_table_id) references Deletedlist(id) on delete no action
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
desc User;
insert into User (email, password, name) values ("jerry@naver.com", password('wjdqja12'), 'jbwoo');
insert into Accountlist (user_id) values ((select u.id from User u where email = "jerry@naver.com"));
insert into Deletedlist (user_id) values ((select u.id from User u where email = "jerry@naver.com"));
insert into Accountlist (name, user_id) values ("home", (select u.id from User u where email = "jerry@naver.com"));
select * from Accountlist;
select * from Deletedlist;
select *, count(*) from User where email = "jerry@naver.com" and password = password('wjdqja12');
select a.id from Accountlist a where name="default_account" and a.user_id = (select u.id from User u where email="jerry@naver.com");
insert into Tradeitem (item_name, item_memo, trade_value, for_ref_list) values ("광어", "싱싱하게 뜬 광어를 먹음", 30000, (select a.id from Accountlist a where name="default_account" and a.user_id = (select u.id from User u where email="jerry@naver.com")));
파이썬 깔끔한 request_mock
#!/usr/bin/env python
"""
mocking requests calls
"""
import mock
import unittest
import requests
from requests.exceptions import HTTPError
def google_query(query):
"""
trivial function that does a GET request
against google, checks the status of the
result and returns the raw content
"""
url = "https://www.google.com"
params = {'q': query}
resp = requests.get(url, params=params)
resp.raise_for_status()
return resp.content
class TestRequestsCall(unittest.TestCase):
"""
example text that mocks requests.get and
returns a mock Response object
"""
def _mock_response(
self,
status=200,
content="CONTENT",
json_data=None,
raise_for_status=None):
"""
since we typically test a bunch of different
requests calls for a service, we are going to do
a lot of mock responses, so its usually a good idea
to have a helper function that builds these things
"""
mock_resp = mock.Mock()
# mock raise_for_status call w/optional error
mock_resp.raise_for_status = mock.Mock()
if raise_for_status:
mock_resp.raise_for_status.side_effect = raise_for_status
# set status code and content
mock_resp.status_code = status
mock_resp.content = content
# add json data if provided
if json_data:
mock_resp.json = mock.Mock(
return_value=json_data
)
return mock_resp
@mock.patch('requests.get')
def test_google_query(self, mock_get):
"""test google query method"""
mock_resp = self._mock_response(content="ELEPHANTS")
mock_get.return_value = mock_resp
result = google_query('elephants')
self.assertEqual(result, 'ELEPHANTS')
self.assertTrue(mock_resp.raise_for_status.called)
@mock.patch('requests.get')
def test_failed_query(self, mock_get):
"""test case where google is down"""
mock_resp = self._mock_response(status=500, raise_for_status=HTTPError("google is down"))
mock_get.return_value = mock_resp
self.assertRaises(HTTPError, google_query, 'elephants')
if __name__ == '__main__':
unittest.main()