-
Notifications
You must be signed in to change notification settings - Fork 0
/
MySQL_d09_class01
339 lines (267 loc) · 14 KB
/
MySQL_d09_class01
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
*****************
#20210514 오전수업
*****************
#코딩 배우는 곳
https://www.w3schools.com/sql/default.asp
#mysql실행
mysql> create database aadb;
Query OK, 1 row affected (0.02 sec)
mysql> drop database aadb;
==========
계정(account)
id : ace
pw : 1234
db : ace db
==========
mysql> use mysql;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
35 rows in set (0.01 sec)
mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.01 sec)
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.01 sec)
mysql> select user from user;
+------------------+
| user |
+------------------+
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
4 rows in set (0.00 sec)
mysql> select user from db;
+---------------+
| user |
+---------------+
| mysql.session |
| mysql.sys |
+---------------+
2 rows in set (0.01 sec)
============유저생성===========
mysql> create user ace@'%' identified by '1234';
Query OK, 0 rows affected (0.02 sec)
==============등록유저확인============
mysql> select user from user;
+------------------+
| user |
+------------------+
| ace |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
5 rows in set (0.00 sec)
========유저에게 acedb에 대한 접근권한&사용권한?을 주는것====
mysql> grant all privileges on acedb.* to ace@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges; <===========위 내용(권한을 준것)을 바로 적용해라??
==================================================================
==================================================================
flush 해야 권한이 적용된다. 안하면 아무 의미가 없다
==================================================================
==================================================================
========cmd에서 mysql접근하는 방법===========
====경로연결하고, ace로 접속해서, user 정보 확인까지===
C:\Users\CPB06GameN>mysql
'mysql'은(는) 내부 또는 외부 명령, 실행할 수 있는 프로그램, 또는
배치 파일이 아닙니다.
C:\Users\CPB06GameN>cd C:\Program Files\MySQL\MySQL Server 8.0\bin
(폴더내 bin까지 접근하고 경로 복사)
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u ace -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 44
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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> select user();
+---------------+
| user() |
+---------------+
| ace@localhost |
+---------------+
1 row in set (0.00 sec)
================================================
시스템환경변수 클릭
mysql을 어디서든 실행하기 위해
>클릭
> 고급 > 환경변수 >
> 시스템변수 > 변수 Path 클릭
>C:\Program Files\MySQL\MySQL Shell 8.0\bin\
>C:\Program Files\MySQL\MySQL Server 8.0\bin
경로추가하기.
그러면 cmd에서
C:\Users\CPB06GameN>mysql -u ace -p
Enter password: ****
위와같이 바로 접속이 된다.
=============================================
=============================================
=============================================
=============================================
=============== 오전 2교시 ===============
=============================================
=============================================
=============================================
=========mysql workbench 접속================
#퍼포먼스>대쉬보드 : 현재 db상태 확인 가능
# Ctrl + Enter = 한줄씩 확인가능
# Ctrl + Shift + Enter = 여러줄 확인가능
# -- sql은 이게 주석임 --
create database rootdb;
use rootdb;
show databases;
create table book(
title char(30), -- char 30 은 고정폭 30칸
(속도는 이게 빠르다)
author varchar(30), -- varchar 30은 가변폭 30칸을 쓸수 있지만 남는 공간은 안쓴다
(이건 속도 느림)
price int);
# title = 필드명
# char = 자료형
## insert 여러번하면 중복데이터 발생함
## 데이터 중복 막기 위해 설정해주는게 primary 개념
desc book;
insert into book values('코로나이후의 세계', '미쿡사람', 16000);
insert into book values('MySQL이다', '우재남', 30000);
insert into book values('코로나이후의 세계', '미쿡사람', 16000);
select * from book;
============ 책 238p =============
mysql 자료형
데이터 낭비를 막기위해
자료형을 잘 설정해줘야한다.
** ENUM **
설문조사시 선택지(EX 5개)가
문자열로 보이지만, 컴퓨터 내부적으로는
0, 1, 2, 3, 4 와 같은 데이터로 처리하여
분석속도를 높히는 개념
========== table 이름 바꾸기 =======
========== 필드 추가 =======
show tables;
alter table book rename b2;
alter table b2 add press char(20);
desc b2;
==================================
# drop = 테이블을 지우는것
# delete = 테이블의 내용을 지우는것(테이블은 남아있음)
===============================
data 세부자료 검색
==============================
use world;
show tables;
desc city;
select count(*) from city;
select * from city; (위에 리미트 걸려있다)
'''
select * from city limit 100; (100개만 출력)
select * from city limit 100, 10; (100번쨰부터 10개만 출력)
#인터넷 게시판 한번에 몇개씩 보기를 설정할때 쓰일수 있다.
'''
=======================================
desc country;
select count(*) from country; -- 239
select count(*) from countrylanguage; -- 984