[SQL] MariaDB 연습1

| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| st_db              |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| st_db              |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [(none)]> use st_db
Database changed
MariaDB [st_db]>
MariaDB [st_db]>
MariaDB [st_db]>
MariaDB [st_db]> show tables;
Empty set (0.00 sec)

MariaDB [st_db]> create table st_info (ST_ID int, NAME varchar(20), DEPT varchar(25)) default charset=utf8;
Query OK, 0 rows affected (0.31 sec)

MariaDB [st_db]> create table st_grade (ST_ID int, Linux int, DB int);
Query OK, 0 rows affected (0.53 sec)

MariaDB [st_db]> show tables;
+-----------------+
| Tables_in_st_db |
+-----------------+
| st_grade        |
| st_info         |
+-----------------+
2 rows in set (0.00 sec)

MariaDB [st_db]>
MariaDB [st_db]>
MariaDB [st_db]>
MariaDB [st_db]> explain st_info
    ->
    -> l
    -> ;
Empty set (0.00 sec)

MariaDB [st_db]> explain st_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ST_ID | int(11)     | YES  |     | NULL    |       |
| NAME  | varchar(20) | YES  |     | NULL    |       |
| DEPT  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [st_db]> explain st_grade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ST_ID | int(11) | YES  |     | NULL    |       |
| Linux | int(11) | YES  |     | NULL    |       |
| DB    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [st_db]> alter table st_info modify ST_ID int not Null;
Query OK, 0 rows affected (0.60 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [st_db]> alter table st_grade modify ST_ID int not Null;
Query OK, 0 rows affected (1.30 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [st_db]> alter table st_info add constraint pk_stinfo primary key(ST_ID);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [st_db]> alter table st_grade add constraint pk_stgrade primary key(ST_ID);
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [st_db]> explain st_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ST_ID | int(11)     | NO   | PRI | NULL    |       |
| NAME  | varchar(20) | YES  |     | NULL    |       |
| DEPT  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [st_db]> explain st_grade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ST_ID | int(11) | NO   | PRI | NULL    |       |
| Linux | int(11) | YES  |     | NULL    |       |
| DB    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [st_db]> insert into st_info values(201401, '이길동', 'Game');
Query OK, 1 row affected (0.09 sec)

MariaDB [st_db]> insert into st_info values(201402, '김길동', 'Computer');
Query OK, 1 row affected (0.04 sec)

MariaDB [st_db]> insert into st_info values(201403, '홍길동', 'Game');
Query OK, 1 row affected (0.05 sec)

MariaDB [st_db]> insert into st_grade values(201401, 90, 80);
Query OK, 1 row affected (0.04 sec)

MariaDB [st_db]> insert into st_grade values(201401, 70, 95);
ERROR 1062 (23000): Duplicate entry '201401' for key 'PRIMARY'
MariaDB [st_db]> insert into st_grade values(201402, 70, 95);
Query OK, 1 row affected (0.05 sec)

MariaDB [st_db]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| st_db              |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [st_db]> show tables
    -> ;
+-----------------+
| Tables_in_st_db |
+-----------------+
| st_grade        |
| st_info         |
+-----------------+
2 rows in set (0.01 sec)

MariaDB [st_db]> select * from st_info;
+--------+-----------+----------+
| ST_ID  | NAME      | DEPT     |
+--------+-----------+----------+
| 201401 | 이길동    | Game     |
| 201402 | 김길동    | Computer |
| 201403 | 홍길동    | Game     |
+--------+-----------+----------+
3 rows in set (0.03 sec)

MariaDB [st_db]> select NAME, DEPT from st_info where ST_ID=201401;
+-----------+------+
| NAME      | DEPT |
+-----------+------+
| 이길동    | Game |
+-----------+------+
1 row in set (0.03 sec)

MariaDB [st_db]> select NAME from st_info where ST_ID=201401;
+-----------+
| NAME      |
+-----------+
| 이길동    |
+-----------+
1 row in set (0.00 sec)

MariaDB [st_db]> select Linux from st_grade where ST_ID=201401;
+-------+
| Linux |
+-------+
|    90 |
+-------+
1 row in set (0.00 sec)

MariaDB [st_db]> select st_info.NAME, st_info.DEPT, st_grade.DB
    -> from st_info, st_grade
    -> where st_info.ST_ID=201401 and st_grade.ST_ID=201401;
+-----------+------+------+
| NAME      | DEPT | DB   |
+-----------+------+------+
| 이길동    | Game |   80 |
+-----------+------+------+
1 row in set (0.00 sec)

MariaDB [st_db]> select st_info.NAME, st_info.DEPT, st_grade.DB from st_info, st_grade where st_info.ST_ID=201401;
+-----------+------+------+
| NAME      | DEPT | DB   |
+-----------+------+------+
| 이길동    | Game |   80 |
| 이길동    | Game |   95 |
+-----------+------+------+
2 rows in set (0.00 sec)

MariaDB [st_db]> select * from st_info;
+--------+-----------+----------+
| ST_ID  | NAME      | DEPT     |
+--------+-----------+----------+
| 201401 | 이길동    | Game     |
| 201402 | 김길동    | Computer |
| 201403 | 홍길동    | Game     |
+--------+-----------+----------+
3 rows in set (0.00 sec)

MariaDB [st_db]> select * from st_grade;
+--------+-------+------+
| ST_ID  | Linux | DB   |
+--------+-------+------+
| 201401 |    90 |   80 |
| 201402 |    70 |   95 |
+--------+-------+------+
2 rows in set (0.01 sec)

MariaDB [st_db]> select st_info.NAME, st_info.DEPT, st_grade.DB from st_info, st_grade where st_info.ST_ID=201401 and st_grade.ST_ID=201401;
+-----------+------+------+
| NAME      | DEPT | DB   |
+-----------+------+------+
| 이길동    | Game |   80 |
+-----------+------+------+
1 row in set (0.00 sec)

MariaDB [st_db]> update st_grade set DB=90 where ST_ID=201401;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [st_db]> select * from st_grade where ST_ID=201401;
+--------+-------+------+
| ST_ID  | Linux | DB   |
+--------+-------+------+
| 201401 |    90 |   90 |
+--------+-------+------+
1 row in set (0.00 sec)

MariaDB [st_db]> update st_grade set DB=80 where ST_ID=201401;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [st_db]> select * from st_grade where ST_ID=201401;
+--------+-------+------+
| ST_ID  | Linux | DB   |
+--------+-------+------+
| 201401 |    90 |   80 |
+--------+-------+------+
1 row in set (0.00 sec)

MariaDB [st_db]> update st_grade set DB=90 where ST_ID=201401;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [st_db]> select * from st_grade where ST_ID=201401;
+--------+-------+------+
| ST_ID  | Linux | DB   |
+--------+-------+------+
| 201401 |    90 |   90 |
+--------+-------+------+
1 row in set (0.00 sec)

MariaDB [st_db]> bye
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'bye' at line 1
MariaDB [st_db]> exit
Bye
sjw@sjw-HP-Mini-110-3500:~$ mysqladmin status
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'sjw'@'localhost' (using password: NO)'
sjw@sjw-HP-Mini-110-3500:~$ mysqladmin -u root -p status
Enter password:
Uptime: 23332  Threads: 1  Questions: 143  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 80  Queries per second avg: 0.006
sjw@sjw-HP-Mini-110-3500:~$ mysqladmin -u root -p version
Enter password:
mysqladmin  Ver 9.1 Distrib 10.0.17-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Server version        10.0.17-MariaDB-1~trusty-log
Protocol version    10
Connection        Localhost via UNIX socket
UNIX socket        /var/run/mysqld/mysqld.sock
Uptime:            6 hours 35 min 7 sec

Threads: 1  Questions: 144  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 80  Queries per second avg: 0.006
sjw@sjw-HP-Mini-110-3500:~$ mysqladmin -u root -p password '123456'
Enter password:
sjw@sjw-HP-Mini-110-3500:~$ mysqladmin -u root -p password 'sjh213897'
Enter password:
sjw@sjw-HP-Mini-110-3500:~$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.0.17-MariaDB-1~trusty-log mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>