mysql 메모

mysql

• 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 세계로 들어감

---

DB 생성

-create database <db-name>

• show databases

• use <db-name>

User + Host 생성

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()