2-SQLの使用

48974 ワード
Database sql mysql テキストリンク
SQLの使用
列の作成
入力
CREATE TABLE topic(  # 테이블 이름을 topic으로 지정
	id INT(11) NOT NULL AUTO_INCREMENT, # column 이름을 id로 지정. INT(11): column의 데이터 타입을 숫자로 강제, 노출되는 길이를 11글자로 지정. NOT NULL: 값이 없는 것(공백)을 허용하지 않음. AUTO_INCREMENT: id 값이 자동으로 1씩 증가.
    	title VARCHAR(100) NOT NULL, # 2nd column. VARCHAR(100): 글자 수를 100자로 지정.
	description TEXT NULL, # 3rd column. NULL: 값이 있는 것(공백)을 허용.
    	created DATATIME NOT NULL, # 4th column.
    	author VARCHAR(30) NUll, # 5th column, 글자 수를 30자로 지정.
    	profile VARCHAR(100) NUll, # 6th column, 글자 수를 100자로 지정.
    	PRIMARY KEY(id)); # PRIMARY KEY: 각각의 값이 고유하며 중복되면 안 된다고 알리는 것. 첫 줄에서 안 닫긴 괄호가 있으므로 (topic 뒤) 소괄호를 두번 써줌.
しゅつりょく
mysql> DESC topic; # description의 약자, 테이블의 구조를 보여준다.
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100) | NO   |     | NULL    |                |
| description | text         | YES  |     | NULL    |                |
| created     | datetime     | NO   |     | NULL    |                |
| author      | varchar(30)  | YES  |     | NULL    |                |
| profile     | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
SQLのINSERT構文
入力
INSERT INTO topic (title,description,created,author,profile) VALUES('MySQL', 'MySQL is ...', NOW(), 'jacob', 'developer'); 
# Insert INTO topic: 데이터(행)를 'topic'이란 테이블에 삽입함. id 컬럼은 값을 지정하지 않으면 자동으로 AUTO_INCREMENT가 됨. VALUES() 안에 title 값을 적어줌. NOW(): 현재 시간이 자동으로 입력되는 함수.
しゅつりょく
SELECT *FROM topic; #topic에서 가져오고 싶은 데이터를 가져옴. 특별한 언급이 없으면 모든 데이터를 가져온다.
+----+-------+--------------+---------------------+--------+-----------+
| id | title | description  | created             | author | profile   |
+----+-------+--------------+---------------------+--------+-----------+
|  1 | MySQL | MySQL is ... | 2020-10-23 20:46:21 | jacob  | developer |
+----+-------+--------------+---------------------+--------+-----------+
このように行を追加し続けます.
入力
# 두 번째 행
INSERT INTO topic (title,description,created,author,profile) VALUES('ORACLE', 'ORACLE is ...', NOW(), 'jacob', 'developer');

# 세 번째 행
INSERT INTO topic (title,description,created,author,profile) VALUES('SQL Server', 'SQL Server is ...', NOW(), 'duru', 'data administrator');

# 네 번째 행
INSERT INTO topic (title,description,created,author,profile) VALUES('PostgreSQL', 'PostgreSQL is ...', NOW(), 'taeho', 'data scientist, developer');

# 다섯 번째 행
INSERT INTO topic (title,description,created,author,profile) VALUES('MongoDB', 'MongoDB is ...', NOW(), 'jacob', 'developer');
しゅつりょく
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2020-10-23 20:46:21 | jacob  | developer                 |
|  2 | ORACLE     | ORACLE is ...     | 2020-10-23 21:21:51 | jacob  | developer                 |
|  3 | SQL Server | SQL Server is ... | 2020-10-23 21:22:19 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2020-10-23 21:24:25 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2020-10-23 21:24:29 | jacob  | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
SQLのSELECTの文法
# id, title, created, author만 출력하기
SELECT id,title,created,author FROM topic;
+----+------------+---------------------+--------+
| id | title      | created             | author |
+----+------------+---------------------+--------+
|  1 | MySQL      | 2020-10-23 20:46:21 | jacob  |
|  2 | ORACLE     | 2020-10-23 21:21:51 | jacob  |
|  3 | SQL Server | 2020-10-23 21:22:19 | duru   |
|  4 | PostgreSQL | 2020-10-23 21:24:25 | taeho  |
|  5 | MongoDB    | 2020-10-23 21:24:29 | jacob  |
+----+------------+---------------------+--------+

# 위에서 author가 jacob인 데이터만 가져오기
SELECT id,title,created,author FROM topic WHERE author='jacob'; 
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  1 | MySQL   | 2020-10-23 20:46:21 | jacob  |
|  2 | ORACLE  | 2020-10-23 21:21:51 | jacob  |
|  5 | MongoDB | 2020-10-23 21:24:29 | jacob  |
+----+---------+---------------------+--------+

# id 정렬 순서를 내림차순으로 바꾸기
SELECT id,title,created,author FROM topic WHERE author='jacob' ORDER BY id DESC;
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  5 | MongoDB | 2020-10-23 21:24:29 | jacob  |
|  2 | ORACLE  | 2020-10-23 21:21:51 | jacob  |
|  1 | MySQL   | 2020-10-23 20:46:21 | jacob  |
+----+---------+---------------------+--------+

# 데이터를 두 건만 보기
SELECT id,title,created,author FROM topic WHERE author='jacob' ORDER BY id DESC LIMIT 2;
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  5 | MongoDB | 2020-10-23 21:24:29 | jacob  |
|  2 | ORACLE  | 2020-10-23 21:21:51 | jacob  |
+----+---------+---------------------+--------+
SQLのUPDATE構文
# 두 번째 줄 내용을 업데이트 하기
UPDATE topic SET description='ORACLE is...', title='Oracle' WHERE id=2;
SQLのDELETE構文
# 다섯번째 줄 삭제하기
DELETE FROM topic WHERE id=5;

+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | MySQL is ...      | 2020-10-23 20:46:21 | jacob  | developer                 |
|  2 | Oracle     | ORACLE is...      | 2020-10-23 21:21:51 | jacob  | developer                 |
|  3 | SQL Server | SQL Server is ... | 2020-10-23 21:22:19 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is ... | 2020-10-23 21:24:25 | taeho  | data scientist, developer |
+----+------------+-------------------+---------------------+--------+---------------------------+
JOIN(リレーショナルデータベースの花)
2つのテーブルのマージ
# topic이라는 테이블과 author이라는 테이블 합치기
SELECT *FROM topic LEFT JOIN author ON topic.author_id=author.id;
# 합칠 때 topic 테이블의 'author_id'값과 author 테이블의 'id'값을 기준으로 합성하라고 요청함

+----+------------+-------------------+---------------------+-----------+------+-------+---------------------------+
| id | title      | description       | created             | author_id | id   | name  | profile                   |
+----+------------+-------------------+---------------------+-----------+------+-------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 |         1 |    1 | jacob | developer                 |
|  2 | Oracle     | Oracle is ...     | 2018-01-03 13:01:10 |         1 |    1 | jacob | developer                 |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 |         2 |    2 | duru  | database administrator    |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 |         3 |    3 | taeho | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 |         1 |    1 | jacob | developer                 |
+----+------------+-------------------+---------------------+-----------+------+-------+---------------------------+

# 여기에 나와있지는 않지만 author 테이블은 id 값이 1, 2, 3으로 3줄 뿐이였는데 topic 테이블의 author_id 값을 기준으로 합쳐졌기 때문에 id가 같은 값들끼리 행에 정렬했다.
選択的連結テーブル
SELECT topic.id,title,description,created,name,profile FROM topic LEFT JOIN author ON topic.author_id=author.id;

+----+------------+-------------------+---------------------+-------+---------------------------+
| id | title      | description       | created             | name  | profile                   |
+----+------------+-------------------+---------------------+-------+---------------------------+
|  1 | MySQL      | MySQL is...       | 2018-01-01 12:10:11 | jacob | developer                 |
|  2 | Oracle     | Oracle is ...     | 2018-01-03 13:01:10 | jacob | developer                 |
|  3 | SQL Server | SQL Server is ... | 2018-01-20 11:01:10 | duru  | database administrator    |
|  4 | PostgreSQL | PostgreSQL is ... | 2018-01-23 01:03:03 | taeho | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2018-01-30 12:31:03 | jacob | developer                 |
+----+------------+-------------------+---------------------+-------+---------------------------+

# 이제 합쳐진 두 테이블은 한 테이블이 되었기 때문에 topic. 이후 원하는 값만 입력해서 출력해줄 수 있다.
AnnotationとReflection
ASP.NET Forms権限検証