sql.txt

Last login: Tue Oct 20 18:15:27 on ttys000
 rsh@rsh  ~
 mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.6-MariaDB Homebrew

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

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

MariaDB [(none)]> exit
Bye
 rsh@rsh  ~
 brew services start mariadb
Service `mariadb` already started, use `brew services restart mariadb` to restart.
 rsh@rsh  ~
 mysql -u root -p           
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.6-MariaDB Homebrew

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

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

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

MariaDB [(none)]> CREATE USER 'study'@'localhost' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.019 sec)

MariaDB [(none)]> CREATE USER 'study'@'localhost' IDENTIFIED BY '1111';
ERROR 1396 (HY000): Operation CREATE USER failed for 'study'@'localhost'
MariaDB [(none)]> desc mysql.user;
+------------------------+---------------------+------+-----+----------+-------+
| Field                  | Type                | Null | Key | Default  | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host                   | char(60)            | NO   |     |          |       |
| User                   | char(80)            | NO   |     |          |       |
| Password               | longtext            | YES  |     | NULL     |       |
| Select_priv            | varchar(1)          | YES  |     | NULL     |       |
| Insert_priv            | varchar(1)          | YES  |     | NULL     |       |
| Update_priv            | varchar(1)          | YES  |     | NULL     |       |
| Delete_priv            | varchar(1)          | YES  |     | NULL     |       |
| Create_priv            | varchar(1)          | YES  |     | NULL     |       |
| Drop_priv              | varchar(1)          | YES  |     | NULL     |       |
| Reload_priv            | varchar(1)          | YES  |     | NULL     |       |
| Shutdown_priv          | varchar(1)          | YES  |     | NULL     |       |
| Process_priv           | varchar(1)          | YES  |     | NULL     |       |
| File_priv              | varchar(1)          | YES  |     | NULL     |       |
| Grant_priv             | varchar(1)          | YES  |     | NULL     |       |
| References_priv        | varchar(1)          | YES  |     | NULL     |       |
| Index_priv             | varchar(1)          | YES  |     | NULL     |       |
| Alter_priv             | varchar(1)          | YES  |     | NULL     |       |
| Show_db_priv           | varchar(1)          | YES  |     | NULL     |       |
| Super_priv             | varchar(1)          | YES  |     | NULL     |       |
| Create_tmp_table_priv  | varchar(1)          | YES  |     | NULL     |       |
| Lock_tables_priv       | varchar(1)          | YES  |     | NULL     |       |
| Execute_priv           | varchar(1)          | YES  |     | NULL     |       |
| Repl_slave_priv        | varchar(1)          | YES  |     | NULL     |       |
| Repl_client_priv       | varchar(1)          | YES  |     | NULL     |       |
| Create_view_priv       | varchar(1)          | YES  |     | NULL     |       |
| Show_view_priv         | varchar(1)          | YES  |     | NULL     |       |
| Create_routine_priv    | varchar(1)          | YES  |     | NULL     |       |
| Alter_routine_priv     | varchar(1)          | YES  |     | NULL     |       |
| Create_user_priv       | varchar(1)          | YES  |     | NULL     |       |
| Event_priv             | varchar(1)          | YES  |     | NULL     |       |
| Trigger_priv           | varchar(1)          | YES  |     | NULL     |       |
| Create_tablespace_priv | varchar(1)          | YES  |     | NULL     |       |
| Delete_history_priv    | varchar(1)          | YES  |     | NULL     |       |
| ssl_type               | varchar(9)          | YES  |     | NULL     |       |
| ssl_cipher             | longtext            | NO   |     |          |       |
| x509_issuer            | longtext            | NO   |     |          |       |
| x509_subject           | longtext            | NO   |     |          |       |
| max_questions          | bigint(20) unsigned | NO   |     | 0        |       |
| max_updates            | bigint(20) unsigned | NO   |     | 0        |       |
| max_connections        | bigint(20) unsigned | NO   |     | 0        |       |
| max_user_connections   | bigint(21)          | NO   |     | 0        |       |
| plugin                 | longtext            | NO   |     |          |       |
| authentication_string  | longtext            | NO   |     |          |       |
| password_expired       | varchar(1)          | NO   |     |          |       |
| is_role                | varchar(1)          | YES  |     | NULL     |       |
| default_role           | longtext            | NO   |     |          |       |
| max_statement_time     | decimal(12,6)       | NO   |     | 0.000000 |       |
+------------------------+---------------------+------+-----+----------+-------+
47 rows in set (0.017 sec)

MariaDB [(none)]> CREATE USER 'study'@'localhost' IDENTIFIED BY '1111';
ERROR 1396 (HY000): Operation CREATE USER failed for 'study'@'localhost'
MariaDB [(none)]> CREATE USER 'study'@'%' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]> CREATE DATABASE studydb
    ->   DEFAULT CHARACTER SET utf8
    ->   DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.005 sec)

MariaDB [(none)]> GRANT ALL ON studydb.* TO 'study'@'localhost';
Query OK, 0 rows affected (0.014 sec)

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

MariaDB [(none)]> select Host, User from mysql.user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| %         | study       |
| localhost | mariadb.sys |
| localhost | root        |
| localhost | rsh         |
| localhost | study       |
+-----------+-------------+
5 rows in set (0.007 sec)

MariaDB [(none)]> exit
Bye
 rsh@rsh  ~
 mysql -u study -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.5.6-MariaDB Homebrew

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| studydb            |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> exit
Bye
 rsh@rsh  ~
 mysql -u root -p           
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.6-MariaDB Homebrew

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

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

MariaDB [(none)]> CREATE USER 'user1'@'localhost' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.015 sec)

MariaDB [(none)]> CREATE USER 'user1'@'%' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]> CREATE USER 'user2'@'%' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]> CREATE USER 'user3'@'%' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.010 sec)

MariaDB [(none)]> CREATE USER 'user4'@'%' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> CREATE USER 'user5'@'%' IDENTIFIED BY '1111';
Query OK, 0 rows affected (0.014 sec)

MariaDB [(none)]> CREATE DATABASE studydb
    ->   DEFAULT CHARACTER SET utf8
    ->   DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
ERROR 1007 (HY000): Can't create database 'studydb'; database exists
MariaDB [(none)]> CREATE DATABASE user1db
    ->   DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> CREATE DATABASE user2db
    ->   DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> CREATE DATABASE user3db
    ->   DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> CREATE DATABASE user4db
    ->   DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> CREATE DATABASE user5db
    ->   DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> GRANT ALL ON userdb1.* TO 'user1'@'%';
Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> GRANT ALL ON userdb1.* TO 'user1'@'localhost';
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]> GRANT ALL ON userdb2.* TO 'user2'@'localhost';
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [(none)]> GRANT ALL ON user2db.* TO 'user2'@'localhost';
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [(none)]> GRANT ALL ON user2db.* TO 'user2'@'%';
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]> GRANT ALL ON user3db.* TO 'user3'@'%';
Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> GRANT ALL ON user4db.* TO 'user4'@'%';
Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> GRANT ALL ON user5db.* TO 'user5'@'%';
Query OK, 0 rows affected (0.014 sec)

MariaDB [(none)]> GRANT ALL ON userdb5.* TO 'user5'@'%';
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studydb            |
| user1db            |
| user2db            |
| user3db            |
| user4db            |
| user5db            |
+--------------------+
9 rows in set (0.001 sec)

MariaDB [(none)]> select user from mysql.user;
+-------------+
| User        |
+-------------+
| study       |
| user1       |
| user2       |
| user3       |
| user4       |
| user5       |
| mariadb.sys |
| root        |
| rsh         |
| study       |
| user1       |
+-------------+
11 rows in set (0.001 sec)

MariaDB [(none)]> select Host, User from mysql.user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| %         | study       |
| %         | user1       |
| %         | user2       |
| %         | user3       |
| %         | user4       |
| %         | user5       |
| localhost | mariadb.sys |
| localhost | root        |
| localhost | rsh         |
| localhost | study       |
| localhost | user1       |
+-----------+-------------+
11 rows in set (0.001 sec)

MariaDB [(none)]> drop user user1@localhost;
Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> select Host, User from mysql.user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| %         | study       |
| %         | user1       |
| %         | user2       |
| %         | user3       |
| %         | user4       |
| %         | user5       |
| localhost | mariadb.sys |
| localhost | root        |
| localhost | rsh         |
| localhost | study       |
+-----------+-------------+
10 rows in set (0.001 sec)

MariaDB [(none)]> drop user user1@%;
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 '%' at line 1
MariaDB [(none)]> drop user user1;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> select Host, User from mysql.user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| %         | study       |
| %         | user2       |
| %         | user3       |
| %         | user4       |
| %         | user5       |
| localhost | mariadb.sys |
| localhost | root        |
| localhost | rsh         |
| localhost | study       |
+-----------+-------------+
9 rows in set (0.001 sec)

MariaDB [(none)]> drop user user2;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> drop user user3;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> drop user user4;
Query OK, 0 rows affected (0.016 sec)

MariaDB [(none)]> drop user user5;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> select Host, User from mysql.user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| %         | study       |
| localhost | mariadb.sys |
| localhost | root        |
| localhost | rsh         |
| localhost | study       |
+-----------+-------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studydb            |
| user1db            |
| user2db            |
| user3db            |
| user4db            |
| user5db            |
+--------------------+
9 rows in set (0.002 sec)

MariaDB [(none)]> DROP DATABASE user1db
    -> ;
Query OK, 0 rows affected (0.013 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studydb            |
| user2db            |
| user3db            |
| user4db            |
| user5db            |
+--------------------+
8 rows in set (0.000 sec)

MariaDB [(none)]> DROP DATABASE user2db;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> DROP DATABASE user3db;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> DROP DATABASE user4db;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> DROP DATABASE user5db;
Query OK, 0 rows affected (0.001 sec)

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

MariaDB [(none)]> create table test01 (
    ->     name varchar(50) not null,
    ->     kor int not null,
    ->     eng int not null,
    ->     math int not null,
    ->     sum int not null,
    ->     aver float not null
    ->   );
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studydb            |
+--------------------+
4 rows in set (0.002 sec)

MariaDB [(none)]> create table test01 (
    ->     name varchar(50) not null,
    ->     kor int not null,
    ->     eng int not null,
    ->     math int not null,
    ->     sum int not null,
    ->     aver float not null
    ->   );
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> create table studydb.test01 (
    ->     name varchar(50) not null,
    ->     kor int not null,
    ->     eng int not null,
    ->     math int not null,
    ->     sum int not null,
    ->     aver float not null
    ->   );
Query OK, 0 rows affected (0.031 sec)

MariaDB [(none)]> drop table studydb.test01;
Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> use studydb;
Database changed
MariaDB [studydb]> create table test01 (
    ->     name varchar(50) not null,
    ->     kor int not null,
    ->     eng int not null,
    ->     math int not null,
    ->     sum int not null,
    ->     aver float not null
    ->   );
Query OK, 0 rows affected (0.018 sec)

MariaDB [studydb]> show tables;
+-------------------+
| Tables_in_studydb |
+-------------------+
| test01            |
+-------------------+
1 row in set (0.000 sec)

MariaDB [studydb]> describe test01
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | NO   |     | NULL    |       |
| kor   | int(11)     | NO   |     | NULL    |       |
| eng   | int(11)     | NO   |     | NULL    |       |
| math  | int(11)     | NO   |     | NULL    |       |
| sum   | int(11)     | NO   |     | NULL    |       |
| aver  | float       | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.003 sec)

MariaDB [studydb]> desc table test01;
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 'table test01' at line 1
MariaDB [studydb]> desc test01
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | NO   |     | NULL    |       |
| kor   | int(11)     | NO   |     | NULL    |       |
| eng   | int(11)     | NO   |     | NULL    |       |
| math  | int(11)     | NO   |     | NULL    |       |
| sum   | int(11)     | NO   |     | NULL    |       |
| aver  | float       | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.003 sec)

MariaDB [studydb]> drop table test01;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> show tables;
Empty set (0.000 sec)

MariaDB [studydb]> create table test1(
    ->     no int not null, 
    ->     name varchar(20)
    ->   );
Query OK, 0 rows affected (0.019 sec)

MariaDB [studydb]> desc test01
    -> ;
ERROR 1146 (42S02): Table 'studydb.test01' doesn't exist
MariaDB [studydb]> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.003 sec)

MariaDB [studydb]> insert into test1(no, name) values(1, 'aaa');
Query OK, 1 row affected (0.003 sec)

MariaDB [studydb]> select * from test1;
+----+------+
| no | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.001 sec)

MariaDB [studydb]> insert into test1(no2, name) values(1, 'aaa');
ERROR 1054 (42S22): Unknown column 'no2' in 'field list'
MariaDB [studydb]> insert into test1(no, name) values(1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [studydb]> insert into test1(no, name) values('aaa', 1);
ERROR 1366 (22007): Incorrect integer value: 'aaa' for column `studydb`.`test1`.`no` at row 1
MariaDB [studydb]> insert into test1(no, name) values(null, 'bbb');
ERROR 1048 (23000): Column 'no' cannot be null
MariaDB [studydb]> insert into test1(no, name) values(3, null);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+----+------+
| no | name |
+----+------+
|  1 | aaa  |
|  3 | NULL |
+----+------+
2 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(no, name) values(null, 'bbb');
ERROR 1048 (23000): Column 'no' cannot be null
MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> show tables;
Empty set (0.000 sec)

MariaDB [studydb]> create table test1 (
    ->     no int,
    ->     name varchar(20)
    ->   );
Query OK, 0 rows affected (0.016 sec)

MariaDB [studydb]> insert into test1(no, name) values(1, 'aaa');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(no, name) values(null, 'bbb');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(no, name) values(3, null);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(no, name) values(null, null);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+------+
| no   | name |
+------+------+
|    1 | aaa  |
| NULL | bbb  |
|    3 | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(no, name) values(1, 'aaa');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+------+
| no   | name |
+------+------+
|    1 | aaa  |
| NULL | bbb  |
|    3 | NULL |
| NULL | NULL |
|    1 | aaa  |
+------+------+
5 rows in set (0.000 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.004 sec)

MariaDB [studydb]> create table test1(
    ->     no int not null, 
    ->     name varchar(20)
    ->   );
Query OK, 0 rows affected (0.021 sec)

MariaDB [studydb]> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.003 sec)

MariaDB [studydb]> insert into test1(no, name) values(1, 'aaa');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(no, name) values(null, 'bbb');
ERROR 1048 (23000): Column 'no' cannot be null
MariaDB [studydb]> insert into test1(no, name) values(3, null);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.003 sec)

MariaDB [studydb]> select * from test1;
+----+------+
| no | name |
+----+------+
|  1 | aaa  |
|  3 | NULL |
+----+------+
2 rows in set (0.000 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> create table test1(
    ->     no int not null,
    ->     name varchar(20) default 'noname',
    ->     age int default 20
    ->   );
Query OK, 0 rows affected (0.018 sec)

MariaDB [studydb]> insert into test1(no, name, age) values(1, 'aaa', 30);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+----+------+------+
| no | name | age  |
+----+------+------+
|  1 | aaa  |   30 |
+----+------+------+
1 row in set (0.000 sec)

MariaDB [studydb]> insert into test1(name, age) values('aaa', 30);
ERROR 1364 (HY000): Field 'no' doesn't have a default value
MariaDB [studydb]> insert into test1(no, age) values(3, 30);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(no, name) values(4, 'ddd');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+----+--------+------+
| no | name   | age  |
+----+--------+------+
|  1 | aaa    |   30 |
|  3 | noname |   30 |
|  4 | ddd    |   20 |
+----+--------+------+
3 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(no) values(5);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+----+--------+------+
| no | name   | age  |
+----+--------+------+
|  1 | aaa    |   30 |
|  3 | noname |   30 |
|  4 | ddd    |   20 |
|  5 | noname |   20 |
+----+--------+------+
4 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(no, age, name) values(6, null, null);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+----+--------+------+
| no | name   | age  |
+----+--------+------+
|  1 | aaa    |   30 |
|  3 | noname |   30 |
|  4 | ddd    |   20 |
|  5 | noname |   20 |
|  6 | NULL   | NULL |
+----+--------+------+
5 rows in set (0.000 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> create table test1(
    ->   c1 int, 
    ->   c2 float, 
    ->   c3 numeric(6,2), /* 소수점 자릿수를 지정하면 부동소수점으로 사용 */
    ->   c4 numeric /* int와 같다 */
    ->   );
Query OK, 0 rows affected (0.016 sec)

MariaDB [studydb]> desc test1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| c1    | int(11)       | YES  |     | NULL    |       |
| c2    | float         | YES  |     | NULL    |       |
| c3    | decimal(6,2)  | YES  |     | NULL    |       |
| c4    | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.004 sec)

MariaDB [studydb]> insert into test1(c1) values(100);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
|  100 | NULL | NULL | NULL |
+------+------+------+------+
1 row in set (0.000 sec)

MariaDB [studydb]> insert into test1(c1) values(3.14);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
|  100 | NULL | NULL | NULL |
|    3 | NULL | NULL | NULL |
+------+------+------+------+
2 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c1) values(100.98);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
|  100 | NULL | NULL | NULL |
|    3 | NULL | NULL | NULL |
|  101 | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c2) values(100);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(c2) values(3.14);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(c2) values(3.14159);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+---------+------+------+
| c1   | c2      | c3   | c4   |
+------+---------+------+------+
|  100 |    NULL | NULL | NULL |
|    3 |    NULL | NULL | NULL |
|  101 |    NULL | NULL | NULL |
| NULL |     100 | NULL | NULL |
| NULL |    3.14 | NULL | NULL |
| NULL | 3.14159 | NULL | NULL |
+------+---------+------+------+
6 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c3) values(100);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(c3) values(123456789); /* 입력 오류. 5자리 초과 */
ERROR 1264 (22003): Out of range value for column 'c3' at row 1
MariaDB [studydb]> insert into test1(c3) values(12345); /* 입력 오류. 1자리 초과 */
ERROR 1264 (22003): Out of range value for column 'c3' at row 1
MariaDB [studydb]> insert into test1(c3) values(1234.12);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(c3) values(1234.123);
Query OK, 1 row affected, 1 warning (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+---------+---------+------+
| c1   | c2      | c3      | c4   |
+------+---------+---------+------+
|  100 |    NULL |    NULL | NULL |
|    3 |    NULL |    NULL | NULL |
|  101 |    NULL |    NULL | NULL |
| NULL |     100 |    NULL | NULL |
| NULL |    3.14 |    NULL | NULL |
| NULL | 3.14159 |    NULL | NULL |
| NULL |    NULL |  100.00 | NULL |
| NULL |    NULL | 1234.12 | NULL |
| NULL |    NULL | 1234.12 | NULL |
+------+---------+---------+------+
9 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c3) values(1234.129);
Query OK, 1 row affected, 1 warning (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+---------+---------+------+
| c1   | c2      | c3      | c4   |
+------+---------+---------+------+
|  100 |    NULL |    NULL | NULL |
|    3 |    NULL |    NULL | NULL |
|  101 |    NULL |    NULL | NULL |
| NULL |     100 |    NULL | NULL |
| NULL |    3.14 |    NULL | NULL |
| NULL | 3.14159 |    NULL | NULL |
| NULL |    NULL |  100.00 | NULL |
| NULL |    NULL | 1234.12 | NULL |
| NULL |    NULL | 1234.12 | NULL |
| NULL |    NULL | 1234.13 | NULL |
+------+---------+---------+------+
10 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c4) values(1234567890);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(c4) values(12.34567890); /* 소수점은 반올림 처리됨 */
Query OK, 1 row affected, 1 warning (0.001 sec)

MariaDB [studydb]> insert into test1(c4) values(12345678.90); /* 소수점은 반올림 처리됨 */
Query OK, 1 row affected, 1 warning (0.001 sec)

MariaDB [studydb]> insert into test1(c4) values(12345678901);
ERROR 1264 (22003): Out of range value for column 'c4' at row 1
MariaDB [studydb]> select * from test1;
+------+---------+---------+------------+
| c1   | c2      | c3      | c4         |
+------+---------+---------+------------+
|  100 |    NULL |    NULL |       NULL |
|    3 |    NULL |    NULL |       NULL |
|  101 |    NULL |    NULL |       NULL |
| NULL |     100 |    NULL |       NULL |
| NULL |    3.14 |    NULL |       NULL |
| NULL | 3.14159 |    NULL |       NULL |
| NULL |    NULL |  100.00 |       NULL |
| NULL |    NULL | 1234.12 |       NULL |
| NULL |    NULL | 1234.12 |       NULL |
| NULL |    NULL | 1234.13 |       NULL |
| NULL |    NULL |    NULL | 1234567890 |
| NULL |    NULL |    NULL |         12 |
| NULL |    NULL |    NULL |   12345679 |
+------+---------+---------+------------+
13 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c4) values(2234567890);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1;
+------+---------+---------+------------+
| c1   | c2      | c3      | c4         |
+------+---------+---------+------------+
|  100 |    NULL |    NULL |       NULL |
|    3 |    NULL |    NULL |       NULL |
|  101 |    NULL |    NULL |       NULL |
| NULL |     100 |    NULL |       NULL |
| NULL |    3.14 |    NULL |       NULL |
| NULL | 3.14159 |    NULL |       NULL |
| NULL |    NULL |  100.00 |       NULL |
| NULL |    NULL | 1234.12 |       NULL |
| NULL |    NULL | 1234.12 |       NULL |
| NULL |    NULL | 1234.13 |       NULL |
| NULL |    NULL |    NULL | 1234567890 |
| NULL |    NULL |    NULL |         12 |
| NULL |    NULL |    NULL |   12345679 |
| NULL |    NULL |    NULL | 2234567890 |
+------+---------+---------+------------+
14 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c4) values(9234567890);
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+------+---------+---------+------------+
| c1   | c2      | c3      | c4         |
+------+---------+---------+------------+
|  100 |    NULL |    NULL |       NULL |
|    3 |    NULL |    NULL |       NULL |
|  101 |    NULL |    NULL |       NULL |
| NULL |     100 |    NULL |       NULL |
| NULL |    3.14 |    NULL |       NULL |
| NULL | 3.14159 |    NULL |       NULL |
| NULL |    NULL |  100.00 |       NULL |
| NULL |    NULL | 1234.12 |       NULL |
| NULL |    NULL | 1234.12 |       NULL |
| NULL |    NULL | 1234.13 |       NULL |
| NULL |    NULL |    NULL | 1234567890 |
| NULL |    NULL |    NULL |         12 |
| NULL |    NULL |    NULL |   12345679 |
| NULL |    NULL |    NULL | 2234567890 |
| NULL |    NULL |    NULL | 9234567890 |
+------+---------+---------+------------+
15 rows in set (0.000 sec)

MariaDB [studydb]> insert into test1(c4) values(19234567890);
ERROR 1264 (22003): Out of range value for column 'c4' at row 1
MariaDB [studydb]> insert into test1(c1) values(1234567890);
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(c1) values(2234567890);
ERROR 1264 (22003): Out of range value for column 'c1' at row 1
MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> create table test1(
    ->   c1 char(5),
    ->   c2 varchar(5),
    ->   c3 varchar(21000) 
    ->   );
Query OK, 0 rows affected (0.017 sec)

MariaDB [studydb]> insert into test1(c1) values('');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|      | NULL | NULL |
+------+------+------+
1 row in set (0.000 sec)

MariaDB [studydb]> insert into test1(c1) values('abcde');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> insert into test1(c1) values('가나다라마');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(c1) values('가나다라마1');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
MariaDB [studydb]> insert into test1(c1) values('abcde1');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
MariaDB [studydb]> insert into test1(c2) values('');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(c2) values('abcde');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> insert into test1(c2) values('abcdefghi');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
MariaDB [studydb]> select * from test1;
+-----------------+-------+------+
| c1              | c2    | c3   |
+-----------------+-------+------+
|                 | NULL  | NULL |
| abcde           | NULL  | NULL |
| 가나다라마      | NULL  | NULL |
| NULL            |       | NULL |
| NULL            | abcde | NULL |
+-----------------+-------+------+
5 rows in set (0.000 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.002 sec)

MariaDB [studydb]> create table test1(
    ->   c1 char(5),
    ->   c2 varchar(5),
    ->   c3 varchar(23000) 
    -> );
ERROR 1074 (42000): Column length too big for column 'c3' (max = 21844); use BLOB or TEXT instead
MariaDB [studydb]> create table test1(
    ->   c1 char(5),
    ->   c2 varchar(5),
    ->   c3 varchar(21844) 
    -> );
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [studydb]> create table test1(
    ->   c1 char(5),
    ->   c2 varchar(5),
    ->   c3 varchar(21000) 
    -> );
Query OK, 0 rows affected (0.019 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.004 sec)

MariaDB [studydb]> create table test1(
    ->   c1 char(5),
    ->   c2 varchar(5),
    ->   c3 varchar(21000) 
    -> );
Query OK, 0 rows affected (0.015 sec)

MariaDB [studydb]> insert into test1(c1) values('abc');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
| abc  | NULL | NULL |
+------+------+------+
1 row in set (0.000 sec)

MariaDB [studydb]> select * from test1 where c1='abc'; 
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
| abc  | NULL | NULL |
+------+------+------+
1 row in set (0.003 sec)

MariaDB [studydb]> select * from test1 where c2='abc'; 
Empty set (0.000 sec)

MariaDB [studydb]> insert into test1(c2) values('abc');
Query OK, 1 row affected (0.001 sec)

MariaDB [studydb]> select * from test1 where c2='abc'; 
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
| NULL | abc  | NULL |
+------+------+------+
1 row in set (0.001 sec)

MariaDB [studydb]> drop table test1;
Query OK, 0 rows affected (0.003 sec)

MariaDB [studydb]> create table test1(
    ->   c1 date,
    ->   c2 time,
    ->   c3 datetime
    ->   ); 
Query OK, 0 rows affected (0.016 sec)

MariaDB [studydb]> insert into test1(c1) values('2017-11-21');
Query OK, 1 row affected (0.003 sec)

MariaDB [studydb]> select * from test1; 
+------------+------+------+
| c1         | c2   | c3   |
+------------+------+------+
| 2017-11-21 | NULL | NULL |
+------------+------+------+
1 row in set (0.000 sec)

MariaDB [studydb]> insert into test1(c2) values('16:12:35');
Query OK, 1 row affected (0.002 sec)

MariaDB [studydb]> select * from test1; 
+------------+----------+------+
| c1         | c2       | c3   |
+------------+----------+------+
| 2017-11-21 | NULL     | NULL |
| NULL       | 16:12:35 | NULL |
+------------+----------+------+
2 rows in set (0.000 sec)

MariaDB [studydb]> > insert into test1(c3) values('2017-11-21 16:13:33');
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 '> insert into test1(c3) values('2017-11-21 16:13:33')' at line 1
MariaDB [studydb]> > insert into test1(c3) values('2017-9-21 16:5:3');
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 '> insert into test1(c3) values('2017-9-21 16:5:3')' at line 1
MariaDB [studydb]> insert into test1(c3) values('2017-9-21 16:5:3')'
    '> ;
    '> ;
    '> );
    '> at line 1;
    '> quit
    '> exit
    '>