Mysql数据库 （4）创建表与增删改和数据库事务

-- 数据准备：创建工资等级表CREATE TABLE salgrade_test (    grade INT (2),    lowsal FLOAT (7, 2),    hisal FLOAT (7, 2));-- 查看工资等级表结构DESC salgrade_test;-- 查看工资等级表数据SELECT    *FROM    salgrade_test;-- 插入数据INSERT INTO salgrade_testVALUES    (1, 10001, 99999);INSERT INTO salgrade_testVALUES    (2, 8001, 10000);INSERT INTO salgrade_testVALUES    (3, 6001, 8000);INSERT INTO salgrade_testVALUES    (4, 4001, 6000);INSERT INTO salgrade_testVALUES    (5, 1, 4000);-- 计算员工的薪水等级SELECT    NAME,    salary,    gradeFROM    emp_test,    salgrade_testWHERE    emp_test.salary BETWEEN salgrade_test.lowsalAND salgrade_test.hisal;-- -----------------------------------------------SELECT    NAME,    salary,    gradeFROM    emp_testLEFT JOIN salgrade_test ON emp_test.salary BETWEEN salgrade_test.lowsalAND salgrade_test.hisal;-- 复制表：只复制结构 , 不复制数据CREATE TABLE salgrade_copy AS (    SELECT        *    FROM        salgrade_test    WHERE        1 <> 1);SELECT    *FROM    salgrade_copy;-- 复制表：复制一部分数据 ----通过设置别名的方式 , 指定新表中的列名(year_sal)CREATE TABLE emp_test_copy AS (    SELECT        emp_id,        NAME,        salary * 12 year_sal    FROM        emp_test    WHERE        dept_test_id = 10);SELECT    *FROM    emp_test_copy;-- 复制表：复制一部分数据  -- 新表中的列名CREATE TABLE emp_count AS (    SELECT        dept_test_id did,        count(*) emp_num    FROM        emp_test    GROUP BY        dept_test_id);SELECT    *FROM    emp_count;-- 创建一个同 emp 表结构相同的空表 , 将部门号为 10 的员工信息放入该表-- 如果有一张表 emp 的数据量为一百万条 , 此时需要建立 1 张测试表只放入少量测试数据( 如 100条 ) , 执行步骤如下所示：-- 第 1 步  创建一个空表CREATE TABLE emp_copy AS (    SELECT        *    FROM        emp_test    WHERE        1 <> 1);-- 第 2 步  揑入少量测试数据INSERT INTO emp_copy (    SELECT        *    FROM        emp_test    WHERE        dept_test_id = 10);SELECT    *FROM    emp_copy;-- 把表中的数据换为部门 20 和 30 的员工记录DELETEFROM    emp_copy;INSERT INTO emp_copy (    SELECT        *    FROM        emp_test    WHERE        dept_test_id IN (20, 30));SELECT    *FROM    emp_copy;-- 向新表中揑入指定记录数的数据,比如前 8 条 DELETEFROM    emp_copy;INSERT INTO emp_copy (SELECT * FROM emp_test LIMIT 8);SELECT    *FROM    emp_copy;-- update( 更新数据 ) **-- 语法结构：-- update 表名 set 列名 = 新的列值 ,-- 列名 = 新的列值.-- ….-- where 条件;-- 注意：--   更新( update )数据表时 , 注意条件 , 如果丌加条件 , 修改的是全部表记录--   rollback 回退 , commit 确认-- 将员工号为 1012 的员工薪水改为 3500 , 职位改为 ProgrammerUPDATE emp_testSET salary = 3500, job = 'Programmer'WHERE    emp_id = 1012;SELECT    *FROM    emp_test;-- 部门 10 的员工薪水+1000UPDATE emp_testSET salary = salary + 3500WHERE    dept_test_id = 10;SELECT    *FROM    emp_test;-- delete( 删除数据 ) **-- 语法结构：-- delete [from] 表名 where 条件 ;-- 注意：--   如果删除语句中丌加 where 条件 , 将删掉表中的全部记录--   rollback 回退 , commit 确认--   drop table 会删除表结构和数据 ;truncate 删除表数据 , 保留表结构。Drop 和 truncate 都-- 不可以回退。 delete 仅删除数据 , 可以回退-- 创建表 emp_bak2 , 只存放不重复的记录CREATE TABLE emp_copy2 AS (    SELECT        *    FROM        emp_test    WHERE        1 <> 1);-- 如下语句执行 3 遍 , 揑入 3 条重复数据INSERT INTO emp_copy2 (emp_id, NAME, salary)VALUES    (1015, 'amy', 4000);-- 如下语句执行 2 遍 , 揑入 2 条重复数据INSERT INTO emp_copy2 (emp_id, NAME, salary)VALUES    (1016, 'rory', 5000);-- 如下语句执行 1 遍 , 揑入 1 条数据INSERT INTO emp_copy2 (emp_id, NAME, salary)VALUES    (1017, 'river', 6000);SELECT    *FROM    emp_copy2;-- 创建表 emp_copy3 , 只存放不重复的记录 , 利用 distinct 关键字CREATE TABLE emp_copy3 AS SELECT DISTINCT    *FROM    emp_copy2;SELECT    *FROM    emp_copy3;-- 将表改名语法区别：Oracle:RENAME  tablename TO newtablename;; mysql：RENAME TABLE tablename TO newtablename;-- 把 emp_copy3 改名为 emp_bak4RENAME TABLE emp_copy3 TO emp_bak4;-- 把 emp_bak4 改名为 emp_copy3RENAME TABLE emp_bak4 TO emp_copy3;-- Transaction( 事务 ) **-- 1)  事务是一组 DML 操作的逻辑单元 , 用来保证数据的一致性。-- 2)  在一个事务内 , 组成事务的这组 DML 操作 , 或者一起成功提交 , 或者一起被撤销。-- 3)  事务控制语言 TCL( Transaction Control Language )--   commit 事务提交 将所有的数据改劢提交--   rollback 事务回滚 回退到事务之初 , 数据的状态和事务开始之前完全一-- 致--   savepoint  事务保存点( 较丌常用 )-- 3.1. 事务的开始和终止( 事务边界 )-- 1)  事务开始-- 事务开始于上一个事务的终止或者第一条 DML 语句-- 2)  事务终止--   事务终止于 commit/rollback 显式操作( 即控制台输入 commit/rollback )--   如果连接关闭 , 事务( Transaction )将隐式提交--   DDL 操作( 比如 create ) , 事务将隐式提交--   如果出现异常 , 事务将隐式回滚-- 1)  事务内部的数据改变只有在自己的会话中能够看到-- 2)  事务会对操作的数据加锁 , 丌允许其它事务操作-- 3)  如果提交( commit )后 , 数据的改变被确认 , 则--   所有的会话都能看到被改变的结果 ;--   数据上的锁被释放 ;--   保存数据的临时空间被释放-- 4)  如果回滚( rollback ) , 则--   数据的改变被取消 ;--   数据上的锁被释放 ;--   临时空间被释放SELECT    *FROM    emp_copy3;UPDATE emp_copy3SET emp_id = 1014WHERE    emp_id = 1015;CREATE TABLE mytemp_test (id INT(4));INSERT INTO mytemp_testVALUES    (3);SAVEPOINT A;-- 设置保存点 , 名为 AINSERT INTO mytemp_testVALUES    (4);SAVEPOINT B;-- 设置保存点 , 名为 BINSERT INTO mytemp_testVALUES    (5);ROLLBACK TO A;-- 回滚到保存点 A , 注意：A 之后的保存点全部被取消SELECT    *FROM    mytemp_test;-- 3 被揑入数据库,-- 4、5 没有被揑入-- truncate 保留表结构 , 删除表中所有数据truncate 操作丌需提交( commit ) , 没有回退( rollback )的机会-- 语法结构： truncate table 表名 ;TRUNCATE TABLE mytemp_test;-- 增加列( 只能增加在最后一列 )ALTER TABLE mytemp_test ADD (NAME VARCHAR(10));ALTER TABLE mytemp_test ADD (PASSWORD VARCHAR(10));-- 修改列名 password 为 pwd 和Oracle有区别：-- Oracle:ALTER TABLE table_name RENAME COLUMN old_column_name  TO new_column_name ;-- Mysql:ALTER TABLE table_name CHANGE COLUMN old_column_name  new_column_name typedefinition;ALTER TABLE mytemp_test CHANGE COLUMN PASSWORD pwd VARCHAR (10);-- 修改列的数据类型为 pwd char(8)-- MySQL:alter table 表名 modify column 字段名 类型;-- Oracle:alter table 表名 modify (字段名 类型);ALTER TABLE mytemp_test MODIFY COLUMN pwd VARCHAR (8);-- 删除列 pwdALTER TABLE mytemp_test DROP COLUMN pwd;DESC mytemp_test;
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
336
337
338
339
340
341
342
343
344
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
336
337
338
339
340
341
342
343
344