PowerPoint 프레젠테이션
Download
Report
Transcript PowerPoint 프레젠테이션
Database
- mysql
장은영
[email protected]
Contents
Database
– DBMS
– Relational model
SQL
– MySQL
Database
Database
– a collection of data
– Entity, relationship
DBMS
– Database management system
DBMS
Why use a DBMS?
–
–
–
–
–
efficient access
reduced application development time
data integrity and security
concurrent access
recovery from system crash
Relational model
Most widely used data model
Relation
– A set of records
– Schema
• Name of a relation, name of each fields
– Instance
• A table with rows and columns
Students(sid:string, sname:string, snum:integer)
Fields (attributes, columns)
Field names
Tuples
(records, rows)
sid
sname
snum
euny
Chang Eun Young
99
cavalist Ryu Han Seung
99
netj
00
Jaeho Shin
SQL
Structured query language
Standard language for interacting
with a DBMS
Data definition
Manipulation
MySQL
Connect
– mysql [–h host] [–u user] [–p[password]]
[dbname]
>mysql -u euny -p test
Enter password: ********
Welcome to the MySQL monitor. Commands end with ;
or \g.
Your MySQL connection id is 14 to server version:
3.23.34a
Type 'help;' or '\h' for help. Type '\c' to clear
the buffer
mysql>
MySQL
Data definition
– CREATE/DROP DATABASE dbname;
– SHOW DATABASES;
– USE dbname;
– CREATE TABLE table_name (field_name type,..,
constraints,..);
– SHOW TABLES;
– SHOW COLUMNS FROM table_name;
– DROP TABLE table_name;
MySQL
Data manipulation
– INSERT INTO table_name [(field_name,..)]
VALUES (value,..);
– DELETE FROM table_name
WHERE condition;
– UPDATE table_name
SET field_name=value,..
[WHERE condition];
MySQL
Data manipulation(2)
– SELECT field_name [as field_name],..
FROM table_name
[WHERE condition]
[ORDER BY field_name];
– =, <, >, AND, OR, NOT
(field_name LIKE “_%….”)
MySQL
ALTER TABLE
– ALTER TABLE table_name
[RENAME new_table_name]/
[ADD field_name type]/
[DROP field_name]/
[CHANGE name new_name new_type];
mysql> show databases;
+----------+
| Database |
+----------+
| mysql
|
| test
|
+----------+
2 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE Students(
-> sid VARCHAR(8) NOT NULL,
-> sname VARCHAR(20),
-> snum INT,
-> PRIMARY KEY(sid));
Query OK, 0 rows affected (0.01 sec)
•Data Type :
•CHAR, VARCHAR, INT [Unsigned], FLOAT
•DATE, TEXT/BLOB, SET, ENUM
•Constraints :
•PRIMARY/FOREIGN KEY, NOT NULL
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students
|
+----------------+
1 row in set (0.00 sec)
mysql> show columns from students;
+-------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid
| varchar(8) |
| PRI |
|
|
| sname | varchar(20) | YES |
| NULL
|
|
| snum | int(11)
| YES |
| NULL
|
|
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> INSERT INTO Students
-> VALUES ('euny', 'Chang Eun-young', 99);
Query OK, 1 row affected (0.00 sec)
…
mysql> SELECT * FROM Students;
+----------+-----------------+------+
| sid
| sname
| snum |
+----------+-----------------+------+
| euny
| Chang Eun-young |
99 |
| cavalist | Ryu Han Seung
|
99 |
| netj
| Jaeho Shin
| 2000 |
+----------+-----------------+------+
3 rows in set (0.00 sec)
mysql> DELETE FROM Students
-> WHERE sid='euny';
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE Students
-> SET sid='asdf'
-> WHERE sid='euny';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM Students;
+----------+-----------------+------+
| sid
| sname
| snum |
+----------+-----------------+------+
| asdf
| Chang Eun-young |
99 |
| cavalist | Ryu Han Seung
|
99 |
| netj
| Jaeho Shin
| 2000 |
+----------+-----------------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Students ORDER BY sname;
+----------+-----------------+------+
| sid
| sname
| snum |
+----------+-----------------+------+
| asdf
| Chang Eun-young |
99 |
| netj
| Jaeho Shin
| 2000 |
| cavalist | Ryu Han Seung
|
99 |
+----------+-----------------+------+
3 rows in set (0.02 sec)
mysql> SELECT sname FROM Students WHERE snum=99;
+-----------------+
| sname
|
+-----------------+
| Chang Eun-young |
| Ryu Han Seung
|
+-----------------+
1 row in set (0.00 sec)