loading
본문 바로가기
MY SQL

SQL ) 1일차

by 원쿤짱쿤 2022. 2. 20.
반응형

-SQL 하는법-
0. C:\Bitnami\wampstack-8.0.15-0 들어가서  manager-windows 열기
1. 윈도우 +R   -> cmd 입력
2. cd c:\Bitnami\wampstack-8.0.15-0\mariadb\bin 엔터
3. dir 엔터
4. mysql -uroot -p 엔터

 

새로운 비밀번호 설정하기

SET PASSWORD = PASSWORD(' 변경할 비밀번호 ' );

 

데이터베이스 생성.

CREATE DATABASE opentutorials;

 

데이터베이스 보기.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| opentutorials      |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.001 sec)

 

데이터베이스 사용하기

MariaDB [(none)]> use opentutorials;
Database changed
MariaDB [opentutorials]>

 

테이블 칼럼별 속성 생성하기

    NULL = 값이 없는것을 허용한다.

MariaDB [(none)]> use opentutorials;
MariaDB [opentutorials]> create table topic(
    -> id INT(11) NOT NULL AUTO_INCREMENT,
    -> TITLE VARCHAR(100) NOT NULL,
    -> DESCRIPTION TEXT NULL,
    -> created DATETIME NOT NULL,
    -> author VARCHAR(30) NULL,
    -> profile VARCHAR(100) NULL
    -> , primary key(id));
Query OK, 0 rows affected (0.043 sec)

 

테이블 무엇이 있는지 보기

MariaDB [opentutorials]> show tables;
+-------------------------+
| Tables_in_opentutorials |
+-------------------------+
| topic                   |
+-------------------------+
1 row in set (0.001 sec)

 

테이블 값 생성하기

MariaDB INSERT INTO topic (title,description,created,author,profile) VALUES('MYSQL','MYSQL IS...' ,NO(),'egoing','developer');
MariaDB INSERT INTO topic (title,description,created,author,profile) VALUES('oracle',' oracle is...' ,NO(),'egoing','developer');
MariaDB INSERT INTO topic (title,description,created,author,profile) VALUES('sql server','sql server IS...' ,NO(),'duru','data adminnistrator');
MariaDB INSERT INTO topic (title,description,created,author,profile) VALUES('postgreSQL','postgreSQLS...' ,NO(),'taeho','data scientist, develope');
MariaDB INSERT INTO topic (title,description,created,author,profile) VALUES('mongoDB','mongoDB is...' ,NO(),'egoing','developer');

 

 

테이블(topic) 데이터 보기

MariaDB [opentutorials]> select*from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | TITLE      | DESCRIPTION       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MySQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is         | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.000 sec)

 

 

테이블 이름 바꾸기.

MariaDB [opentutorials]> ALTER TABLE topic CHANGE DESCRIPTION description text NULL;

MariaDB [opentutorials]> ALTER TABLE 테이블명 CHANGE 기존이름 바꿀이름 text NULL;

MariaDB [opentutorials]> select*from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | DESCRIPTION       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MYSQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is         | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.000 sec)

MariaDB [opentutorials]> ALTER TABLE topic CHANGE DESCRIPTION description text NULL;
Query OK, 0 rows affected (0.016 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [opentutorials]> select*from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MYSQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is         | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.000 sec)

 

 

테이블 값 변경하기

UPDATE 테이블명 SET 컬럼1=컬럼1의 값, 컬럼2=컬럼2의 값 WHERE 대상이 될 컬럼명=컬럼의 값

MariaDB [opentutorials]> UPDATE topic SET title='good' WHERE id='1';

-해석 : topic 테이블에서 title부분을 good으로 바꿔줄것이다 ,  id가 1인 부분에.

  where이 없을경우 title의 모든 값이 good으로 바뀜.

<기존값>
MariaDB [opentutorials]> select*from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | MYSQL      | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is         | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.000 sec)

<적용값>
MariaDB [opentutorials]> UPDATE topic SET title='good' WHERE id='1';


Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

<변경값>
MariaDB [opentutorials]> select*from topic;
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | good       | mysql is ...      | 2022-02-20 16:59:56 | egoing | developer                 |
|  2 | oracle     | oracle is         | 2022-02-20 17:28:03 | egoing | developer                 |
|  3 | sql server | sql server is ... | 2022-02-20 17:29:14 | duru   | data adminnistrator       |
|  4 | postgreSQL | postgreSQL is ... | 2022-02-20 17:30:04 | taeho  | data scientist, developer |
|  5 | mongoDB    | mongoDB is ...    | 2022-02-20 17:31:23 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
5 rows in set (0.000 sec)

 

SELECT문  (검색엔진 : mysql select syntax)

MariaDB [opentutorials]> SELECT id,title,created,author FROM topic;
+----+------------+---------------------+--------+
| id | title      | created             | author |
+----+------------+---------------------+--------+
|  1 | MYSQL      | 2022-02-20 16:59:56 | egoing |
|  2 | oracle     | 2022-02-20 17:28:03 | egoing |
|  3 | sql server | 2022-02-20 17:29:14 | duru   |
|  4 | postgreSQL | 2022-02-20 17:30:04 | taeho  |
|  5 | mongoDB    | 2022-02-20 17:31:23 | egoing |
+----+------------+---------------------+--------+
5 rows in set (0.000 sec)

MariaDB [opentutorials]> SELECT id,title,created,author FROM topic WHERE author='egoing';
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  1 | MYSQL   | 2022-02-20 16:59:56 | egoing |
|  2 | oracle  | 2022-02-20 17:28:03 | egoing |
|  5 | mongoDB | 2022-02-20 17:31:23 | egoing |
+----+---------+---------------------+--------+
3 rows in set (0.001 sec)

MariaDB [opentutorials]> SELECT id,title,created,author FROM topic WHERE author='egoing' ORDER BY id DESC;
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  5 | mongoDB | 2022-02-20 17:31:23 | egoing |
|  2 | oracle  | 2022-02-20 17:28:03 | egoing |
|  1 | MYSQL   | 2022-02-20 16:59:56 | egoing |
+----+---------+---------------------+--------+
3 rows in set (0.000 sec)

MariaDB [opentutorials]> SELECT id,title,created,author FROM topic WHERE author='egoing' ORDER BY id DESC LIMIT 2;
+----+---------+---------------------+--------+
| id | title   | created             | author |
+----+---------+---------------------+--------+
|  5 | mongoDB | 2022-02-20 17:31:23 | egoing |
|  2 | oracle  | 2022-02-20 17:28:03 | egoing |
+----+---------+---------------------+--------+
2 rows in set (0.000 sec)

MariaDB [opentutorials]>

 

 

TIP

언어에대한 정리된 내용을 보려고 한다면 -> 검색엔진 : xxxxx cheatsheet

문법 관련 검색 (syntax=문법) ->   xxxxx syntax

'MY SQL' 카테고리의 다른 글

[SQL] 트랜잭션(Transaction)이란? / 그림으로 쉽게 이해하기  (2) 2024.04.17
3일차  (2) 2022.02.23
SQL)2일차  (0) 2022.02.21

관련글

티스토리툴바