728x90
반응형
MySQL 사용법 (요약) 2000.1.26
시작하기 형식 : mysql -h hostname -u user_name -p DBname hostname 의 default : localhost user_name의 default : login name 예1) mysql -u guest # user가 guest이고 password가 없을 경우 (localhost) 예1) mysql -h sr.kwangwoon.ac.kr -u guest # user가 guest이고 password가 없을 경우 예2) mysql -u guest -p # password를 입력해야 함 Enter password:******** 예3) mysql -u guest pythonDB 참고) 컴퓨터 sr에서 mysql을 이용하여 연습을 하려면 여기서 사용하는 모든 데이터베이스 명 대신에 test를 사용하라. 사용자는 누구라도 되고, 테이블을 만들고 검색하고 삭제할 수 있다. 암호는 주어져 있지 않다. 실행예: mysql -u guest test 끝내기 mysql> QUIT mysql> Ctrl-D *mysql> 은 프롬프트임
변경: mysql -u root -p
Enter password:*****
mysql> use mysql
mysql> update user set Password=PASSWORD('password') where user='userid';
mysql> flush privileges;
버전과 날짜 보기: * 명령에 대소문자 구분은 없다 (case insensitive). mysql> 은 프롬프트임. mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | version() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 1999-03-19 | +--------------+--------------+ 1 row in set (0.01 sec) mysql> 계산기로 사용하기: mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+ 여러 명령 한줄에 입력하기: mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | version() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 1999-03-19 00:15:33 | +---------------------+ 여려줄에 한 명령 입력하기: mysql> SELECT -> USER() -> , -> CURRENT_DATE; +--------------------+--------------+ | USER() | CURRENT_DATE | +--------------------+--------------+ | joesmith@localhost | 1999-03-18 | +--------------------+--------------+
데이터베이스 보기 : SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | python | | test | +----------+ 사용할 데이터베이스 선택하기 : USE test 관리자로 하여금 지정된 사용자만 DB를 접근하도록 하기 : GRANT ALL ON dbname.* TO your_mysql_name; 새로운 데이터베이스 만들기: CREATE DATABASE menagerie; 관리자가 해주던지 아니면 본인이 해야한다(허가권이 있다면). 사용할 데이터베이스 선택 : USE menagerie mysql을 호출할 때 db선택하기 : mysql -h host -u user -p menagerie 데이터베이스 없애기: (한번에 모든 것을 날려 버리므로 조심해서 사용할 것) DROP DATABASE database_name
테이블 보기 : SHOW TABLES; Empty set (0.00 sec) 테이블 만들기 : CREATE TABLE pet ( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, -> death DATE); 테이블 다시 보기: mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | pet | +---------------------+ 테이블 확인하기: mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 테이블 없애기: mysql> DROP table table_name;
테이블에 입력할 정보가 다음과 같다고 하자:
텍스트 파일에 정보가 있다면 쉽게 일괄적으로 테이블에 입력할 수 있다. 각 레코드는 한 라인에 입력되며, 각 필드는 탭으로 구분되어야 하고, NULL인 필드는 \N이 입력되어야 한다. 예:
이와 같은 정보가 'pet.txt' 파일에 저장되어 있다고 한다면, 다음의 명령으로 일괄적으로 읽어들일 수 있다. mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet; 한번에 한 레코드씩 입력하려면 다음과 같은 코맨드라인 명령을 쓴다. mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
이 경우 값이 없으면 NULL을 사용한다. (\N는 아님)
SELECT 문을 이용한다: SELECT what_to_select FROM which_table WHERE conditions_to_satisfy 전체 데이터 선택하기 mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 정보 갱신하기: Bowser의 생일을 1998년에서 1989로 수정하려면, mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser"; 행 선택하여 출력하기: mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ mysql> SELECT * FROM pet WHERE birth >= "1998-1-1"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+-------+---------+------+------------+-------+ mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m") -> OR (species = "dog" AND sex = "f"); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 열 선택하여 출력하기 mysql> SELECT name, birth FROM pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Puffball | 1999-03-30 | +----------+------------+ mysql> SELECT owner FROM pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | | Diane | +--------+ mysql> SELECT DISTINCT owner FROM pet; +--------+ | owner | +--------+ | Benny | | Diane | | Gwen | | Harold | +--------+ mysql> SELECT name, species, birth FROM pet -> WHERE species = "dog" OR species = "cat"; +--------+---------+------------+ | name | species | birth | +--------+---------+------------+ | Fluffy | cat | 1993-02-04 | | Claws | cat | 1994-03-17 | | Buffy | dog | 1989-05-13 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | +--------+---------+------------+ 정렬하기 mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Bowser | 1989-08-31 | | Fang | 1990-08-27 | | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Chirpy | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+ mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Puffball | 1999-03-30 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Fluffy | 1993-02-04 | | Fang | 1990-08-27 | | Bowser | 1989-08-31 | | Buffy | 1989-05-13 | +----------+------------+ mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | 1998-09-11 | | Whistler | bird | 1997-12-09 | | Claws | cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy | dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +----------+---------+------------+ 날짜계산하기 mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet; +----------+-------------------------------------+ | name | (TO_DAYS(NOW())-TO_DAYS(birth))/365 | +----------+-------------------------------------+ | Fluffy | 6.15 | | Claws | 5.04 | | Buffy | 9.88 | | Fang | 8.59 | | Bowser | 9.58 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Puffball | 0.00 | +----------+-------------------------------------+ mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age -> FROM pet ORDER BY name; +----------+------+ | name | age | +----------+------+ | Bowser | 9.58 | | Buffy | 9.88 | | Chirpy | 0.55 | | Claws | 5.04 | | Fang | 8.59 | | Fluffy | 6.15 | | Puffball | 0.00 | | Slim | 2.92 | | Whistler | 1.30 | +----------+------+ mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age -> FROM pet ORDER BY age; +----------+------+ | name | age | +----------+------+ | Puffball | 0.00 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Claws | 5.04 | | Fluffy | 6.15 | | Fang | 8.59 | | Bowser | 9.58 | | Buffy | 9.88 | +----------+------+ mysql> SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age; +--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | Bowser | 1989-08-31 | 1995-07-29 | 5.91 | +--------+------------+------------+------+ DATE에서 해, 달, 일의 정보를 꺼내려면, YEAR(), MONTH(), DAYOFMONTH()를 이용한다. mysql> SELECT name, birth, MONTH(birth) FROM pet; +----------+------------+--------------+ | name | birth | MONTH(birth) | +----------+------------+--------------+ | Fluffy | 1993-02-04 | 2 | | Claws | 1994-03-17 | 3 | | Buffy | 1989-05-13 | 5 | | Fang | 1990-08-27 | 8 | | Bowser | 1989-08-31 | 8 | | Chirpy | 1998-09-11 | 9 | | Whistler | 1997-12-09 | 12 | | Slim | 1996-04-29 | 4 | | Puffball | 1999-03-30 | 3 | +----------+------------+--------------+ 5월에 태어난 동물을 출력하려면: mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | 1989-05-13 | +-------+------------+ 패턴 매칭 '%'는 (0을 포함한) 임의의 수의 문자와 매칭한다. 'b'로 시작하는 이름의 동물을 찾으려면, mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 'fy'로 끝나는 이름의 동물은, mysql> SELECT * FROM pet WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 'w'가 있는 이름의 동물은, mysql> SELECT * FROM pet WHERE name LIKE "%w%"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 꼭 다섯자로 구성된 이름의 동물은, mysql> SELECT * FROM pet WHERE name LIKE "_____"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 확장된 정규식
'b'혹은 'B'로 시작하는 이름: mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]"; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 마지막에 `fy' 로 끝나는 이름: mysql> SELECT * FROM pet WHERE name REGEXP "fy$"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ `w'를 포함하는 이름: mysql> SELECT * FROM pet WHERE name REGEXP "[wW]"; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 다섯 문자로 이루어진 이름: mysql> SELECT * FROM pet WHERE name REGEXP "^.....$"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ `{n}' 연산자 (repeat-n-times)를 사용하여 아래와 같이 쓸 수도 있음: mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
행의 수 세기 전체 엔트리 수 세기: mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ 주인이 가지고 있는 동물의 수 세기: mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ 종별 수: mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ | species | COUNT(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ 성별 수: mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ | sex | COUNT(*) | +------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +------+----------+ 종 및 성별 수: mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ 개와 고양이에 대해서만 적용: mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE species = "dog" OR species = "cat" -> GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | +---------+------+----------+ 알려진 성별에 대해서만 성별 동물의 수: mysql> SELECT species, sex, COUNT(*) FROM pet -> WHERE sex IS NOT NULL -> GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+
테이블 하나 더 만들기: mysql> CREATE TABLE event ( -> name VARCHAR(20), -> date DATE, -> type VARCHAR(15), -> remark VARCHAR(255)); 파일 'event.txt'
데이터 읽어들이기: mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event; 출산 때의 동물의 나이를 계산 (JOIN): mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark -> FROM pet, event -> WHERE pet.name = event.name AND type = "litter"; +--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2.27 | 4 kittens, 3 female, 1 male | | Buffy | 4.12 | 5 puppies, 2 female, 3 male | | Buffy | 5.10 | 3 puppies, 3 female | +--------+------+-----------------------------+ 짝찾아보기 (JOINing itself): mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+
CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69), (3,'D',1.25),(4,'D',19.95); 전체 데이터 예는: SELECT * FROM shop +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 3.45 | | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | B | 1.45 | | 0003 | C | 1.69 | | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+ 최대 아이템 수는? SELECT MAX(article) AS article FROM shop +---------+ | article | +---------+ | 4 | +---------+ 가장 비싼 항목의 번호, 판매자, 가격? SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop) 각 항목의 최고 가격은? SELECT article, MAX(price) AS price FROM shop GROUP BY article +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+ 각 항목에 대하여 가장 비싼 가격의 딜러를 찾아라! SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) 항목 삭제하기 (DELETE) 문법 : DELETE FROM tbl_name [WHERE where_condition] [LIMIT rows]
CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirts (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id)
);
INSERT INTO persons VALUES (NULL, 'Antonio Paz');
INSERT INTO shirts VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirts VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
SELECT * FROM persons;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirts;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM persons p, shirts s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
테이블 이름 바꾸기 t1에서 t2로 이름 바꾸기 mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10)); mysql> ALTER TABLE t1 RENAME t2; 컬럼의 형 바꾸기 테이블 t2의 컬럼 a를 INTEGER에서 TINYINT NOT NULL 형으로 전환하기 mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL; 컬럼의 길이 바꾸기 테이블 t2의 컬럼 b CHAR(10)을 c CHAR(20)으로 길이와 이름 바꾸기 mysql> ALTER TABLE t2 CHANGE b c CHAR(20); 새 컬럼 추가하기 timestamp란 컬럼을 t2에 추가하기 mysql> ALTER TABLE t2 ADD d TIMESTAMP; 인덱스 컬럼 만들기 / primary key 설정하기 t2에 컬럼 d의 인덱스를 추가하기, 컬럼 a를 주 키로 설정한다. mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); 컬럼 없애기 테이블 t2에서 컬럼 c를 없앤다. mysql> ALTER TABLE t2 DROP COLUMN c; 새 컬럼 추가하기 테이블 t2에 INT UNSIGNED NOT NULL AUTO_INCREMENT 형 컬럼 c를 새로 만들기 mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT; 테이블 없애기 mysql> DROP TABLE table_name
언어 참조 스트링 'a string' "another string" Escape sequences \0 An ASCII 0 (NUL) character. \n A newline character. \t A tab character. \r A carriage return character. \b A backspace character. \' A single quote (`'') character. \" A double quote (`"') character. \\ A backslash (`\') character. \% A `%' character. This is used to search for literal instances of `%' in contexts where `%' would otherwise be interpreted as a wildcard character. \_ A `_' character. This is used to search for literal instances of `_' in contexts where `_' would otherwise be interpreted as a wildcard character. mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello'; +-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello"; +-------+---------+-----------+--------+--------+ | hello | 'hello' | ''hello'' | hel"lo | "hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "This\nIs\nFour\nlines"; +--------------------+ | This Is Four lines | +--------------------+ 숫자 정수형 (1221, 0, -32), 부동소숫점형 (3.4, -23.45e+10, 148.00), 16진수(0x23fc) Column Types Numeric types
Date and time types
date, time이 값의 범위를 벋어날 경우는 zero value를 갖는다.
Timestamp format
String types
기억 용량 비교
* BLOB 는 case-sensitive, TEXT는 case-insensitive 인덱스 적절한 인덱스의 사용은 SELECT문의 성능을 크게 향상시켜준다. 최대 16개까지의 인덱스를 갖는다. 최대 인덱스 길이는 256바이트이다. CHAR과 VARCHAR에 대해서 앞 문자열을 대상으로 인덱스 할 수 있다. BLOB와 TEXT에 대해서 앞문자열을 사용하는 것은 인덱스 할 때 필수이다. mysql> CREATE TABLE test ( name CHAR(200) NOT NULL, KEY index_name (name(10))); 복수개 컬럼에 대해서 인덱스를 만들 수 있다. 최대 15개 컬럼을 사용할 수 있다. mysql> CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name)); name인덱스의 사용예: mysql> SELECT * FROM test WHERE last_name="Widenius"; mysql> SELECT * FROM test WHERE last_name="Widenius" AND first_name="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" AND (first_name="Michael" OR first_name="Monty"); mysql> SELECT * FROM test WHERE last_name="Widenius" AND first_name >="M" AND first_name < "N"; 연산자 다음의 연산자는 select문에서 사용가능하다. 산술 연산자 +, -, *, /, MOD(a,b) : a를 b로 나눈 나머지 비트 연산자 |, &, <<, >>, ~, BIT_COUNT(N) : N의 설정된 비트 수 논리 연산자 !, ||, &&, 비교 연산자 =, >, <, >=, <=, <> (!=), <=> (Null safe equal), IS NULL, IS NOT NULL, between a and b expr IN (value, ...), expr NOT IN (value, ...) ISNULL(expr), COALESCE(list), INTERVAL(N,N1,N2,N3,...) mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL; -> 1 1 0 mysql> select 1 BETWEEN 2 AND 3; -> 0 mysql> select 'b' BETWEEN 'a' AND 'c'; -> 1 mysql> select 2 BETWEEN 2 AND '3'; -> 1 mysql> select 2 BETWEEN 2 AND 'x-3'; -> 0 mysql> select 2 IN (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' IN (0,3,5,'wefwf'); -> 1 ....... |
728x90
반응형
