230530_SQL

Active code page: 65001

C:\Users\SAMSUNG>mysql -u root -pzerobase
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

C:\Users\SAMSUNG>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use zerobase;
Database changed
mysql> creat table person
-> (
-> pid int NOT NULL,
-> name varchar(16),
-> age int,
-> sex char,
-> primary key (pid)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat table person
(
pid int NOT NULL,
name varchar(16),
age int,
' at line 1
mysql> create table person
-> (
-> pid int NOT NULL,
-> name varchar(16),
-> age int,
-> sex char,
-> primary key (pid)
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

mysql> create table animal
-> (
-> name varchar(16) NOT NULL,
-> type varchar(16) NOT NULL,
-> age int,
-> primary key (name, type)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc animal:
-> ^C
mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| type | varchar(16) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table person
-> drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc person
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| type | varchar(16) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table animal
-> drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | | NULL | |
| type | varchar(16) | NO | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table person
-> add primary key (pid);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | | NULL | |
| type | varchar(16) | NO | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table animal
-> add constraint PK_animal
-> primary key (name, type);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| type | varchar(16) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table orders
-> (
-> oid int NOT NULL,
-> order_no varchar(16),
-> pid int,
-> primary key(oid),
-> constraint FK_person foreign key (pid) references person (pid)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table job
-> (
-> jid int not null,
-> name varchar(16),
-> pid int,
-> primary key(jid),
-> foreign key (pid) references person(pid)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc job;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table job;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| job | CREATE TABLE job (
jid int NOT NULL,
name varchar(16) DEFAULT NULL,
pid int DEFAULT NULL,
PRIMARY KEY (jid),
KEY pid (pid),
CONSTRAINT job_ibfk_1 FOREIGN KEY (pid) REFERENCES person (pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table orders
-> drop foreign key FK_person;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table orders;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE orders (
oid int NOT NULL,
order_no varchar(16) DEFAULT NULL,
pid int DEFAULT NULL,
PRIMARY KEY (oid),
KEY FK_person (pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table orders
-> add foreign key (pid) references person(pid);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> create table orders;
ERROR 4028 (HY000): A table must have at least one visible column.
mysql> show create table orders;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE orders (
oid int NOT NULL,
order_no varchar(16) DEFAULT NULL,
pid int DEFAULT NULL,
PRIMARY KEY (oid),
KEY pid (pid),
CONSTRAINT orders_ibfk_1 FOREIGN KEY (pid) REFERENCES person (pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc job;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show crate table job;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'crate table job' at line 1
mysql> show create table job;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| job | CREATE TABLE job (
jid int NOT NULL,
name varchar(16) DEFAULT NULL,
pid int DEFAULT NULL,
PRIMARY KEY (jid),
KEY pid (pid),
CONSTRAINT job_ibfk_1 FOREIGN KEY (pid) REFERENCES person (pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ^C
mysql> exit
Bye

C:\Users\SAMSUNG>mysql -h database-1.cp8s2edt9q42.us-east-1.rds.amazonaws.com -P 3306 -u admin -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.32 Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zerobase |
| zerodb |
+--------------------+
6 rows in set (0.27 sec)

mysql> use zerobase;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| cctv |
| celeb |
| crime_status |
| police_station |
| refueling |
| snl_show |
| sql_file |
| test1 |
| test2 |
+--------------------+
9 rows in set (0.54 sec)

mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| address | varchar(128) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.26 sec)

mysql> select count(distinct name) from police_staion;
ERROR 1146 (42S02): Table 'zerobase.police_staion' doesn't exist
mysql> select count (distinct name) from police_station;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct name) from police_station' at line 1
mysql> select * from police_station;
+--------------------------+----------------------------------------------------------------------------------+
| name | address |
+--------------------------+----------------------------------------------------------------------------------+
| 서울중부경찰서 | 서울특별시 중구 수표로 27 |
| 서울종로경찰서 | 서울특별시 종로구 율곡로 46 |
| 서울남대문경찰서 | 서울특별시 중구 한강대로 410 |
| 서울서대문경찰서 | 서울특별시 서대문구 통일로 113 |
| 서울혜화경찰서 | 서울특별시 종로구 창경궁로 112-16 |
| 서울용산경찰서 | 서울특별시 용산구 원효로89길 24 |
| 서울성북경찰서 | 서울특별시 성북구 보문로 170 |
| 서울동대문경찰서 | 서울특별시 동대문구 약령시로 21길 29 |
| 서울마포경찰서 | 서울특별시 마포구 마포대로 183 |
| 서울영등포경찰서 | 서울특별시 영등포구 국회대로 608 |
| 서울성동경찰서 | 서울특별시 성동구 왕십리광장로 9 |
| 서울동작경찰서 | 서울특별시 동작구 노량진로 148 |
| 서울광진경찰서 | 서울특별시 광진구 광나루로 447 광진소방서 임시청사 (능동) |
| 서울서부경찰서 | 서울특별시 은평구 은평로9길 15 |
| 서울강북경찰서 | 서울특별시 강북구 오패산로 406 |
| 서울금천경찰서 | 서울특별시 관악구 남부순환로 1435 |
| 서울중랑경찰서 | 서울특별시 중랑구 중랑역로 137 |
| 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 |
| 서울관악경찰서 | 서울특별시 관악구 관악로5길 33 |
| 서울강서경찰서 | 서울특별시 양천구 화곡로 73 |
| 서울강동경찰서 | 서울특별시 강동구 성내로 33 |
| 서울종암경찰서 | 서울특별시 성북구 종암로 135 |
| 서울구로경찰서 | 서울특별시 구로구 가마산로 235 |
| 서울서초경찰서 | 서울특별시 서초구 반포대로 179 |
| 서울양천경찰서 | 서울특별시 양천구 목동동로 99 |
| 서울송파경찰서 | 서울특별시 송파구 중대로 221 |
| 서울노원경찰서 | 서울특별시 노원구 노원로 283 |
| 서울방배경찰서 | 서울특별시 서초구 방배천로 54 |
| 서울도봉경찰서 | 서울특별시 도봉구 노해로 403 |
| 서울수서경찰서 | 서울특별시 강남구 개포로 617 |
| 서울은평경찰서 | 서울특별시 은평구 연서로 365 |
+--------------------------+----------------------------------------------------------------------------------+
31 rows in set (0.56 sec)

mysql> select count(distinct name) from police_station;
+----------------------+
| count(distinct name) |
+----------------------+
| 31 |
+----------------------+
1 row in set (0.23 sec)

mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year | year | YES | | NULL | |
| police_station | varchar(8) | YES | | NULL | |
| crime_type | varchar(16) | YES | | NULL | |
| status_type | char(2) | YES | | NULL | |
| case_number | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.25 sec)

mysql> select count (distinct police_station) from crime_status;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct police_station) from crime_status' at line 1
mysql> select count(distinct police_station) from crime_status;
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
| 31 |
+--------------------------------+
1 row in set (0.33 sec)

mysql> select distinct name from police_station limit 3;
+--------------------------+
| name |
+--------------------------+
| 서울중부경찰서 |
| 서울종로경찰서 |
| 서울남대문경찰서 |
+--------------------------+
3 rows in set (0.36 sec)

mysql> select distinct police_station from crime_status limit 3;
+----------------+
| police_station |
+----------------+
| 중부 |
| 종로 |
| 남대문 |
+----------------+
3 rows in set (0.37 sec)

mysql> select c.police_station, p.name
-> from crime_status as c, police_station as p
-> wherer p.name like concat('서울', c.police_station, '경찰서')
-> group by c.police_station, p.name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wherer p.name like concat('서울', c.police_station, '경찰서')
group by c.po' at line 3
mysql> select c.police_station, p.name
-> from crime_status c, police_station p
-> where p.name like concat ('서울', c.police_station, '경찰서')
-> group by c.police_station, p.name;
+----------------+--------------------------+
| police_station | name |
+----------------+--------------------------+
| 중부 | 서울중부경찰서 |
| 종로 | 서울종로경찰서 |
| 남대문 | 서울남대문경찰서 |
| 서대문 | 서울서대문경찰서 |
| 혜화 | 서울혜화경찰서 |
| 용산 | 서울용산경찰서 |
| 성북 | 서울성북경찰서 |
| 동대문 | 서울동대문경찰서 |
| 마포 | 서울마포경찰서 |
| 영등포 | 서울영등포경찰서 |
| 성동 | 서울성동경찰서 |
| 동작 | 서울동작경찰서 |
| 광진 | 서울광진경찰서 |
| 서부 | 서울서부경찰서 |
| 강북 | 서울강북경찰서 |
| 금천 | 서울금천경찰서 |
| 중랑 | 서울중랑경찰서 |
| 강남 | 서울강남경찰서 |
| 관악 | 서울관악경찰서 |
| 강서 | 서울강서경찰서 |
| 강동 | 서울강동경찰서 |
| 종암 | 서울종암경찰서 |
| 구로 | 서울구로경찰서 |
| 서초 | 서울서초경찰서 |
| 양천 | 서울양천경찰서 |
| 송파 | 서울송파경찰서 |
| 노원 | 서울노원경찰서 |
| 방배 | 서울방배경찰서 |
| 은평 | 서울은평경찰서 |
| 도봉 | 서울도봉경찰서 |
| 수서 | 서울수서경찰서 |
+----------------+--------------------------+
31 rows in set (0.30 sec)

mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| address | varchar(128) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.52 sec)

mysql> alter table police_station
-> add primary key (name);
Query OK, 31 rows affected (0.38 sec)
Records: 31 Duplicates: 0 Warnings: 0

mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| address | varchar(128) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.35 sec)

mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year | year | YES | | NULL | |
| police_station | varchar(8) | YES | | NULL | |
| crime_type | varchar(16) | YES | | NULL | |
| status_type | char(2) | YES | | NULL | |
| case_number | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.28 sec)

mysql> alter table crime_status
-> add column reference varchar(16);
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year | year | YES | | NULL | |
| police_station | varchar(8) | YES | | NULL | |
| crime_type | varchar(16) | YES | | NULL | |
| status_type | char(2) | YES | | NULL | |
| case_number | int | YES | | NULL | |
| reference | varchar(16) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.43 sec)

mysql> alter table crime_status
-> add foreign key (reference) references police_station(name);
Query OK, 366 rows affected (0.46 sec)
Records: 366 Duplicates: 0 Warnings: 0

mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year | year | YES | | NULL | |
| police_station | varchar(8) | YES | | NULL | |
| crime_type | varchar(16) | YES | | NULL | |
| status_type | char(2) | YES | | NULL | |
| case_number | int | YES | | NULL | |
| reference | varchar(16) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.25 sec)

mysql> update crime_status c, police_station p
-> set c.reference = p.name
-> where p.name like concat('서울', c.police_station, '경찰서');
Query OK, 366 rows affected (0.40 sec)
Rows matched: 366 Changed: 366 Warnings: 0

mysql> select distinct police_station, reference from crime_status;
+----------------+--------------------------+
| police_station | reference |
+----------------+--------------------------+
| 중부 | 서울중부경찰서 |
| 종로 | 서울종로경찰서 |
| 남대문 | 서울남대문경찰서 |
| 서대문 | 서울서대문경찰서 |
| 혜화 | 서울혜화경찰서 |
| 용산 | 서울용산경찰서 |
| 성북 | 서울성북경찰서 |
| 동대문 | 서울동대문경찰서 |
| 마포 | 서울마포경찰서 |
| 영등포 | 서울영등포경찰서 |
| 성동 | 서울성동경찰서 |
| 동작 | 서울동작경찰서 |
| 광진 | 서울광진경찰서 |
| 서부 | 서울서부경찰서 |
| 강북 | 서울강북경찰서 |
| 금천 | 서울금천경찰서 |
| 중랑 | 서울중랑경찰서 |
| 강남 | 서울강남경찰서 |
| 관악 | 서울관악경찰서 |
| 강서 | 서울강서경찰서 |
| 강동 | 서울강동경찰서 |
| 종암 | 서울종암경찰서 |
| 구로 | 서울구로경찰서 |
| 서초 | 서울서초경찰서 |
| 양천 | 서울양천경찰서 |
| 송파 | 서울송파경찰서 |
| 노원 | 서울노원경찰서 |
| 방배 | 서울방배경찰서 |
| 은평 | 서울은평경찰서 |
| 도봉 | 서울도봉경찰서 |
| 수서 | 서울수서경찰서 |
+----------------+--------------------------+
31 rows in set (0.43 sec)

mysql> select c.police_station, p.address
-> from crime_status as c, police_station as p
-> where c.reference = p.name
-> group by c.police_station;
+----------------+----------------------------------------------------------------------------------+
| police_station | address |
+----------------+----------------------------------------------------------------------------------+
| 강남 | 서울특별시 강남구 테헤란로 114길 11 |
| 강동 | 서울특별시 강동구 성내로 33 |
| 강북 | 서울특별시 강북구 오패산로 406 |
| 강서 | 서울특별시 양천구 화곡로 73 |
| 관악 | 서울특별시 관악구 관악로5길 33 |
| 광진 | 서울특별시 광진구 광나루로 447 광진소방서 임시청사 (능동) |
| 구로 | 서울특별시 구로구 가마산로 235 |
| 금천 | 서울특별시 관악구 남부순환로 1435 |
| 남대문 | 서울특별시 중구 한강대로 410 |
| 노원 | 서울특별시 노원구 노원로 283 |
| 도봉 | 서울특별시 도봉구 노해로 403 |
| 동대문 | 서울특별시 동대문구 약령시로 21길 29 |
| 동작 | 서울특별시 동작구 노량진로 148 |
| 마포 | 서울특별시 마포구 마포대로 183 |
| 방배 | 서울특별시 서초구 방배천로 54 |
| 서대문 | 서울특별시 서대문구 통일로 113 |
| 서부 | 서울특별시 은평구 은평로9길 15 |
| 서초 | 서울특별시 서초구 반포대로 179 |
| 성동 | 서울특별시 성동구 왕십리광장로 9 |
| 성북 | 서울특별시 성북구 보문로 170 |
| 송파 | 서울특별시 송파구 중대로 221 |
| 수서 | 서울특별시 강남구 개포로 617 |
| 양천 | 서울특별시 양천구 목동동로 99 |
| 영등포 | 서울특별시 영등포구 국회대로 608 |
| 용산 | 서울특별시 용산구 원효로89길 24 |
| 은평 | 서울특별시 은평구 연서로 365 |
| 종로 | 서울특별시 종로구 율곡로 46 |
| 종암 | 서울특별시 성북구 종암로 135 |
| 중랑 | 서울특별시 중랑구 중랑역로 137 |
| 중부 | 서울특별시 중구 수표로 27 |
| 혜화 | 서울특별시 종로구 창경궁로 112-16 |
+----------------+----------------------------------------------------------------------------------+
31 rows in set (0.46 sec)

mysql> show tables;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 61
Current database: zerobase

+--------------------+
| Tables_in_zerobase |
+--------------------+
| cctv |
| celeb |
| crime_status |
| police_station |
| refueling |
| snl_show |
| sql_file |
| test1 |
| test2 |
+--------------------+
9 rows in set (1.40 sec)

mysql> exit
Bye

C:\Users\SAMSUNG>cd Documents/sql_ws

C:\Users\SAMSUNG\Documents\sql_ws>mysql -u root -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use zerobase;
Database changed
mysql> show talbes;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'talbes' at line 1
mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| animal |
| celeb |
| crime_status |
| job |
| orders |
| person |
| police_station |
| refueling |
| snl_show |
| test1 |
| test2 |
+--------------------+
11 rows in set (0.01 sec)

mysql> create table study
-> (
-> study_id int not null,
-> study_date date,
-> study_time time,
-> patient_id int,
-> primary key (study_id),
-> constraint FK_study foreign key (patient_id) reference person (pid)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'reference person (pid)
)' at line 8
mysql> create table study
-> (
-> study_id int not null,
-> study_date date,
-> study_time time,
-> patient_id int,
-> primary key (study_id),
-> constraint FK_study foreign key (patient_id) references person (pid)
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show create table study'
'> ;
'> ^C
mysql> show create table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show create table study;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| study | CREATE TABLE study (
study_id int NOT NULL,
study_date date DEFAULT NULL,
study_time time DEFAULT NULL,
patient_id int DEFAULT NULL,
PRIMARY KEY (study_id),
KEY FK_study (patient_id),
CONSTRAINT FK_study FOREIGN KEY (patient_id) REFERENCES person (pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table study
-> drop primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table study
-> drop foreign key FK_study;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table study;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| study | CREATE TABLE study (
study_id int NOT NULL,
study_date date DEFAULT NULL,
study_time time DEFAULT NULL,
patient_id int DEFAULT NULL,
KEY FK_study (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table study
-> add foreign key (patient_id) references person(pid);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table study;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| study | CREATE TABLE study (
study_id int NOT NULL,
study_date date DEFAULT NULL,
study_time time DEFAULT NULL,
patient_id int DEFAULT NULL,
KEY patient_id (patient_id),
CONSTRAINT study_ibfk_1 FOREIGN KEY (patient_id) REFERENCES person (pid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table study
-> ^C
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table study
-> add primary key (study_id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>