Database - mysql

1. Database- mysql 장은영 euny@sparcs.kaist.ac.kr

2. Contents • Database • DBMS • Relational model • SQL • MySQL

3. Database • Database • a collection of data • Entity, relationship • DBMS • Database management system

4. DBMS • Why use a DBMS? • efficient access • reduced application development time • data integrity and security • concurrent access • recovery from system crash

5. 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

6. Students(sid:string, sname:string, snum:integer) Fields (attributes, columns) Field names Tuples (records, rows)

7. SQL • Structured query language • Standard language for interacting with a DBMS • Data definition • Manipulation

8. 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>

9. 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;

10. 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];

11. 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 “_%….”)

12. MySQL • ALTER TABLE • ALTER TABLE table_name [RENAME new_table_name]/ [ADD field_name type]/ [DROP field_name]/ [CHANGE name new_name new_type];

13. 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)

14. 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

15. 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)

16. 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)

17. 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)

18. 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)