DATABASE DBMS/MYSQL – Basic Learning

Add to favorites

Basic Learning (DBMS / MySQL)

DBMS:대량의 데이터를 효율적으로 관리하고 운영하기 위한 환경 (Database management system), 데이터베이스를 운영하기 위한 시스템 또는 소프트웨어

DB Modeling Software :
CA-ERwin, MS Visio, MySQL workbench, SQL Power Architect, exERD

DBMS: An environment for efficiently managing and operating large amounts of data (Database management system), a system or software for operating a database

MySQL: Oracle에서 제작, 오픈소스로서 제공한 DBMS
– 유료: Standard, Enterprise, Cluster CGE
– 무료: Community

MySQL: DBMS produced by Oracle and provided as open-source
-Paid: Standard, Enterprise, Cluster CGE
-Free: Community

MySQL workbench support for :
Database connection/Modeling
Instance management (+start/stop)
MySQL management through the wizard (Integrated function for SQL editor)
Database forward/reverse engineering
Database export/import/account management

Installation 

Where to download installation files :

http://dev.mysql.com/downloads/mysql/
MySQL Community 8.0 – MySQL-installer-community-8.0.17.0 393.4MB (MySQL Utility Workbench)

---

SQL administration

Index Table :

i.e. : create table indexTBL (first varchar(10), last varchar(10), birth date);
insert into indexTBL select first, last, birth from family.family limit 500;
select * from indexTBL;

select * from indexTBL where first = ‘kim’;

View Table: Virtual Table

i.e. : create view uv_familyTBL as select first, last from familyTBL;

select * from uv_familyTBL;

MySQL Server Version Check Commands: 

mysql> show variables like ‘%version%’;
+————————–+——————————-+
| Variable_name | Value |
+————————–+——————————-+
| version | 8.0.20 |
| version_comment | MySQL Community Server – GPL |
+————————–+——————————-+
11 rows in set, 1 warning (0.00 sec)

VisualStudio 2019 download: Go Click

MySQL Connector/ODBC download: Go Click

---

SQL administration (advanced)

Stored Procedure: function to bundle and use SQL statements conveniently

i.e. SQL Statements :
DELIMITER //
create procedure proc1()
begin
select * from familyTBL where last = ‘홍’;
select * from familyTBL where first = ‘길동’;
end
DELIMITER;

call proc1();

Trigger : Run codes if insert/update/delete job is executed

Cursor: Method for processing a set of rows that are the result of a query one row after querying multiple rows in a table

테이블에서 여러 개의 행을 쿼리한 후에 쿼리의 결과인 행 집합을 한행씩 처리하기 위한 방식