728x90
📎 MySQL
MySQL은 데이터베이스 소프트웨어입니다.
일반적으로 데이터를 추가하거나 검색, 추출하는 기능을 모두 포함해서 데이터베이스라고 부릅니다.
MySQL은 세계에서 가장 많이 쓰는 오픈 소스의 관계형 데이터베이스 관리시스템(RDBMS)입니다.
MySQL은 PHP 스크립트 언어와 상호 연동이 잘 되면서 오픈소스를 개발된 무료 프로그램입니다.
➰ MySQL 설치
MAMP란 웹사이트를 개발할 때 쓰이는 기술 스택인 macOS, Apache, MySQL, PHP 의 약어이자 솔루션 스택이다.
http://www.mamp.info/en/downlads/
➰ MySQL 실행
윈도우 : cs MAMP/bin/mysql/bin
로그인 : ./mysql -uroot -proot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
맥 : cs Application/mamp/Library/bin
로그인 : ./mysql -uroot -proot
hyehyeon@gwonhyehyeon-ui-MacBookAir bin % ./mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 213
Server version: 5.7.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
📎 데이터베이스
➰ 데이터베이스 만들기
create database 데이터베이스 이름;
mysql> create database sample02;
Query OK, 1 row affected (0.00 sec)
➰ 데이터베이스 보기
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sample01 |
| sample02 |
| sys |
+--------------------+
6 rows in set (0.00 sec)
➰ 데이터베이스 사용
use 데이터베이스 이름;
use sample01;
Database changed
➰ 데이터베이스 삭제
drop database 데이터베이스 이름;
mysql> drop database sample02;
Query OK, 0 rows affected (0.00 sec)
📎 테이블
➰ 테이블 만들기
create table 테이블 이름
create table member (
myMemberID int(10) unsigned auto_increment,
youEmail varchar(40) NOT NULL,
youName varchar(20) NOT NULL,
youPass varchar(20) NOT NULL,
youBirth int(20) NOT NULL,
youAge int(5) NOT NULL,
regTime int(20) NOT NULL,
PRIMARY KEY (myMemberID)
) charset=utf8;
➰ 테이블 전체보기
show tables;
mysql> show tables;
+--------------------+
| Tables_in_sample01 |
+--------------------+
| member |
+--------------------+
1 row in set (0.00 sec)
➰ 테이블 보기
desc 테이블 이름;
mysql> desc member;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| myMemberID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| youEmail | varchar(40) | NO | | NULL | |
| youName | varchar(20) | NO | | NULL | |
| youPass | varchar(20) | NO | | NULL | |
| youBirth | int(20) | NO | | NULL | |
| regTime | int(20) | NO | | NULL | |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
➰ 테이블 삭제
drop table 테이블 이름;
drop table member;
Query OK, 0 rows affected (0.01 sec)
📎 테이블 데이터
➰ 데이터 입력하기
INSERT INTO 테이블이름(필드명) VALUE(데이터)
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge regTime) VALUES('webstoryboy@naver.com','황지락','1234','19990303','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('thdtjdgml415@naver.com', '송지락', '1234', '19970415','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('mo0647@naver.com', '김지락', '1234', '19970530','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('tjrwnsrkdtj@naver.com', '김지락', '1234', '19941009','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('jwor124@naver.com', '정지락', '1234', '19990303','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('leesh3432@naver.com','이지락','1234','19970205','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('sshin4882@naver.com','박지락','1234','19990303','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('to_before@naver.com', '김지락', '1234', '19970809','20', '04');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('kkb7528@naver.com','권지락','1234','19990303','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('piowm123@gmail.com', '문지락', '1234', '19990303','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('ghkddbwls96@gmail.com', '황지락', '1234', '19990303','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('eodnjs9605@naver.com','김지락','1234','19960530','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('wjsqhdus971007@gmail.com','전지락','1234','19971007','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('noeyheyh@gmail.com','권지락','1234','19960331','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('hjkang306@gmail.com','강지락','1234','19990303','20','1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('sunhey9810@gmail.com', '박지락', '1234', '19981010','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUE('kde66034@gmail.com', '김지락', '1234', '19700101','20', '1234567');
INSERT INTO member(youEmail, youName, youPass, youBirth, youAge, regTime) VALUES('praise1109@gmail.com', '이지락', '1234', '19990303','20', '1234567');
➰ 데이터 불러오기
SELECT 필드명 FROM 테이블명 WHERE 조건명
➿ 전체 데이터 불러오기
mysql> SELECT * FROM member;
+------------+--------------------------+------------+---------+----------+---------+
| myMemberID | youEmail | youName | youPass | youBirth | regTime |
+------------+--------------------------+------------+---------+----------+---------+
| 1 | noeyheyh@gmail.com | 권지락 | 1234 | 19960331 | 1234567 |
| 2 | webstoryboy@naver.com | 황지락 | 1234 | 19990303 | 1234567 |
| 3 | thdtjdgml415@naver.com | 송지락 | 1234 | 19970415 | 1234567 |
| 4 | mo0647@naver.com | 김지락 | 1234 | 19970530 | 1234567 |
| 5 | tjrwnsrkdtj@naver.com | 김지락 | 1234 | 19941009 | 1234567 |
| 6 | jwor124@naver.com | 정지락 | 1234 | 19990303 | 1234567 |
| 7 | leesh3432@naver.com | 이지락 | 1234 | 19970205 | 1234567 |
| 8 | kkk5993@naver.com | 김지락 | 1234 | 19960617 | 1234567 |
| 9 | sshin4882@naver.com | 박지락 | 1234 | 19990303 | 1234567 |
| 10 | to_before@naver.com | 김지락 | 1234 | 19970809 | 4 |
| 11 | kkb7528@naver.com | 권지락 | 1234 | 19990303 | 1234567 |
| 12 | piowm123@gmail.com | 문지락 | 1234 | 19990303 | 1234567 |
| 13 | ghkddbwls96@gmail.com | 황지락 | 1234 | 19990303 | 1234567 |
| 14 | eodnjs9605@naver.com | 김지락 | 1234 | 19960530 | 1234567 |
| 15 | wjsqhdus971007@gmail.com | 전지락 | 1234 | 19971007 | 1234567 |
| 16 | hjkang306@gmail.com | 강지락 | 1234 | 19990303 | 1234567 |
| 17 | sunhey9810@gmail.com | 박지락 | 1234 | 19981010 | 1234567 |
| 18 | kde66034@gmail.com | 김지락 | 1234 | 19700101 | 1234567 |
| 19 | praise1109@gmail.com | 이지락 | 1234 | 19990303 | 1234567 |
| 20 | oranssy@naver.com | 최지락 | 3950 | 20010415 | 1234567 |
+------------+--------------------------+------------+---------+----------+---------+
20 rows in set (0.00 sec)
➿ myMemberID가 6번인 경우
mysql> SELECT * FROM member WHERE myMemberID = 6;
+------------+-------------------+------------+---------+----------+---------+
| myMemberID | youEmail | youName | youPass | youBirth | regTime |
+------------+-------------------+------------+---------+----------+---------+
| 6 | jwor124@naver.com | 정장첸 | 1234 | 19990303 | 1234567 |
+------------+-------------------+------------+---------+----------+---------+
1 row in set (0.00 sec)
➿ email 중에 naver 텍스트를 포함하고 있는 경우
mysql> SELECT * FROM member WHERE youEmail LIKE '%naver%';
+------------+------------------------+------------+---------+----------+---------+
| myMemberID | youEmail | youName | youPass | youBirth | regTime |
+------------+------------------------+------------+---------+----------+---------+
| 2 | webstoryboy@naver.com | 황장첸 | 1234 | 19990303 | 1234567 |
| 3 | thdtjdgml415@naver.com | 송장첸 | 1234 | 19970415 | 1234567 |
| 4 | mo0647@naver.com | 김장첸 | 1234 | 19970530 | 1234567 |
| 5 | tjrwnsrkdtj@naver.com | 김장첸 | 1234 | 19941009 | 1234567 |
| 6 | jwor124@naver.com | 정장첸 | 1234 | 19990303 | 1234567 |
| 7 | leesh3432@naver.com | 이장첸 | 1234 | 19970205 | 1234567 |
| 8 | kkk5993@naver.com | 김장첸 | 1234 | 19960617 | 1234567 |
| 9 | sshin4882@naver.com | 박장첸 | 1234 | 19990303 | 1234567 |
| 10 | to_before@naver.com | 김장첸 | 1234 | 19970809 | 4 |
| 11 | kkb7528@naver.com | 권장첸 | 1234 | 19990303 | 1234567 |
| 14 | eodnjs9605@naver.com | 김장첸 | 1234 | 19960530 | 1234567 |
| 20 | oranssy@naver.com | 최장첸 | 3950 | 20010415 | 1234567 |
+------------+------------------------+------------+---------+----------+---------+
12 rows in set (0.00 sec)
댓글