Mysql语句实例+练习分享

1. 
   
2. 
   
3. mysql> select 10;
   
4. +----+
   
5. | 10 |
   
6. +----+
   
7. | 10 |
   
8. +----+
   
9. 1 row in set (0.00 sec)
   
10. 
    
11. mysql> select 10+20+30;
    
12. +----------+
    
13. | 10+20+30 |
    
14. +----------+
    
15. |       60 |
    
16. +----------+
    
17. 1 row in set (0.02 sec)
    
18. 
    
19. mysql> select "aa"+"bb";
    
20. +-----------+
    
21. | "aa"+"bb" |
    
22. +-----------+
    
23. |         0 |
    
24. +-----------+
    
25. 1 row in set, 2 warnings (0.00 sec)
    
26. 
    
27. mysql> select "10a"+"bb";
    
28. +------------+
    
29. | "10a"+"bb" |
    
30. +------------+
    
31. |         10 |
    
32. +------------+
    
33. 1 row in set, 2 warnings (0.00 sec)
    
34. 
    
35. mysql> select "10a"+"bb20";
    
36. +--------------+
    
37. | "10a"+"bb20" |
    
38. +--------------+
    
39. |           10 |
    
40. +--------------+
    
41. 1 row in set, 2 warnings (0.00 sec)
    
42. 
    
43. mysql>
    
44. mysql> select 10*"2a";
    
45. +---------+
    
46. | 10*"2a" |
    
47. +---------+
    
48. |      20 |
    
49. +---------+
    
50. 1 row in set, 1 warning (0.00 sec)
    
51. 
    
52. mysql> select "3a"+"4b";
    
53. +-----------+
    
54. | "3a"+"4b" |
    
55. +-----------+
    
56. |         7 |
    
57. +-----------+
    
58. 1 row in set, 2 warnings (0.00 sec)
    
59. 
    
60. mysql> select "3a"+"4e2b";
    
61. +-------------+
    
62. | "3a"+"4e2b" |
    
63. +-------------+
    
64. |         403 |
    
65. +-------------+
    
66. 1 row in set, 2 warnings (0.00 sec)
    
67. 
    
68. mysql> select "3a"+"4.23b";
    
69. +--------------+
    
70. | "3a"+"4.23b" |
    
71. +--------------+
    
72. |         7.23 |
    
73. +--------------+
    
74. 1 row in set, 2 warnings (0.00 sec)
    
75. 
    
76. mysql> select 12=20;
    
77. +-------+
    
78. | 12=20 |
    
79. +-------+
    
80. |     0 |
    
81. +-------+
    
82. 1 row in set (0.00 sec)
    
83. 
    
84. mysql> select 0="abc";
    
85. +---------+
    
86. | 0="abc" |
    
87. +---------+
    
88. |       1 |
    
89. +---------+
    
90. 1 row in set, 1 warning (0.00 sec)
    
91. 
    
92. mysql> select "10"="10a";
    
93. +------------+
    
94. | "10"="10a" |
    
95. +------------+
    
96. |          0 |
    
97. +------------+
    
98. 1 row in set (0.00 sec)
    
99. 
    
100. mysql> select 10>=1 and 10<=20;
     
101. +------------------+
     
102. | 10>=1 and 10<=20 |
     
103. +------------------+
     
104. |                1 |
     
105. +------------------+
     
106. 1 row in set (0.00 sec)
     
107. 
     
108. mysql> select 10 between 1 and 20;
     
109. +---------------------+
     
110. | 10 between 1 and 20 |
     
111. +---------------------+
     
112. |                   1 |
     
113. +---------------------+
     
114. 1 row in set (0.00 sec)
     

复制代码

1. 
   
2. mysql> select 1<<3;
   
3. +------+
   
4. | 1<<3 |
   
5. +------+
   
6. |    8 |
   
7. +------+
   
8. 1 row in set (0.02 sec)
   
9. 
   
10. mysql> select 1<<10;
    
11. +-------+
    
12. | 1<<10 |
    
13. +-------+
    
14. |  1024 |
    
15. +-------+
    
16. 1 row in set (0.00 sec)
    
17. 
    
18. mysql> select 1<<20;
    
19. +---------+
    
20. | 1<<20   |
    
21. +---------+
    
22. | 1048576 |
    
23. +---------+
    
24. 1 row in set (0.00 sec)
    
25. 
    
26. mysql> select 1|2;
    
27. +-----+
    
28. | 1|2 |
    
29. +-----+
    
30. |   3 |
    
31. +-----+
    
32. 1 row in set (0.00 sec)
    
33. 
    
34. mysql> select 1&2;
    
35. +-----+
    
36. | 1&2 |
    
37. +-----+
    
38. |   0 |
    
39. +-----+
    
40. 1 row in set (0.00 sec)
    
41. 
    
42. mysql> select 5&6;
    
43. +-----+
    
44. | 5&6 |
    
45. +-----+
    
46. |   4 |
    
47. +-----+
    
48. 1 row in set (0.00 sec)
    
49. 
    
50. 
    
51. 
    
52. --创建数据库
    
53. mysql> create database fooknet;
    
54. Query OK, 1 row affected (0.00 sec)
    
55. 
    
56. --查看当前用户可以看到所有数据库
    
57. mysql> show databases;
    
58. +----------------------+
    
59. | Database             |
    
60. +----------------------+
    
61. | information_schema   |
    
62. | test                 |
    
63. | mysql                |
    
64. | fooknet               |
    
65. +----------------------+
    
66. 83 rows in set (0.75 sec)
    
67. 
    
68. --删除数据库
    
69. mysql> drop database fooknet;
    
70. Query OK, 0 rows affected (0.03 sec)
    
71. 
    
72. mysql> create database fooknet;
    
73. Query OK, 1 row affected (0.00 sec)
    
74. 
    
75. mysql>
    
76. 
    
77. -- 选择进入fooknet数据库
    
78. mysql> use fooknet;
    
79. Database changed
    
80. 
    
81. --查看当前你在哪个数据库下
    
82. mysql> select database();
    
83. +------------+
    
84. | database() |
    
85. +------------+
    
86. | fooknet     |
    
87. +------------+
    
88. 1 row in set (0.00 sec)
    
89. 
    
90. --查看当前数据库中的所有表格。
    
91. mysql> show tables;
    
92. Empty set (0.02 sec)
    
93. 
    
94. mysql>
    
95. --创建留言表格
    
96. mysql> create table liuyan(
    
97.     -> id int unsigned not null auto_increment primary key,
    
98.     -> title varchar(32) not null,
    
99.     -> author varchar(32) not null default 'wu',
    
100.     -> addtime datetime,
     
101.     -> content text not null);
     
102. Query OK, 0 rows affected (0.08 sec)
     
103. --id 整型 无负号 非空 自增 主键（非空，唯一性、索引列）
     
104. --title 字串（长度32位） 非空
     
105. --author 字串（长度32位） 非空 默认值 “wu”
     
106. 
     
107. 
     
108. --查看留言表结构
     
109. mysql> desc liuyan;
     
110. +---------+------------------+------+-----+---------+----------------+
     
111. | Field   | Type             | Null | Key | Default | Extra          |
     
112. +---------+------------------+------+-----+---------+----------------+
     
113. | id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
114. | title   | varchar(32)      | NO   |     | NULL    |                |
     
115. | author  | varchar(32)      | NO   |     | wu      |                |
     
116. | addtime | datetime         | YES  |     | NULL    |                |
     
117. | content | text             | NO   |     | NULL    |                |
     
118. +---------+------------------+------+-----+---------+----------------+
     
119. 5 rows in set (0.02 sec)
     
120. 
     
121. --以文本方式输出留言表的建表语句
     
122. mysql> show create table liuyan \G;
     
123. *************************** 1. row ***************************
     
124.        Table: liuyan
     
125. Create Table: CREATE TABLE `liuyan` (
     
126.   `id` int(10) unsigned NOT NULL auto_increment,
     
127.   `title` varchar(32) NOT NULL,
     
128.   `author` varchar(32) NOT NULL default 'wu',
     
129.   `addtime` datetime default NULL,
     
130.   `content` text NOT NULL,
     
131.   PRIMARY KEY  (`id`)
     
132. ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
133. 1 row in set (0.00 sec)
     
134. 
     
135. ERROR:
     
136. No query specified
     
137. 
     
138. mysql>
     
139. mysql>
     
140. --查看当前数据库中表格
     
141. mysql> show tables;
     
142. +------------------+
     
143. | Tables_in_fooknet |
     
144. +------------------+
     
145. | liuyan           |
     
146. +------------------+
     
147. 1 row in set (0.00 sec)
     
148. 
     
149. mysql>
     
150. --删除表格liuyan
     
151. mysql> drop table liuyan;
     
152. Query OK, 0 rows affected (0.00 sec)
     
153. 
     
154. mysql>  CREATE TABLE `liuyan` (
     
155.     ->   `id` int(10) unsigned NOT NULL auto_increment,
     
156.     ->   `title` varchar(32) NOT NULL,
     
157.     ->   `author` varchar(32) NOT NULL default 'wu',
     
158.     ->   `addtime` datetime default NULL,
     
159.     ->   `content` text NOT NULL,
     
160.     ->   PRIMARY KEY  (`id`)
     
161.     -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     
162. Query OK, 0 rows affected (0.05 sec)
     
163. 
     
164. mysql>
     
165. 
     
166. --添加一个测试表格
     
167. mysql> create table aa(id int not null);
     
168. Query OK, 0 rows affected (0.05 sec)
     
169. 
     
170. --查看当前数据库下的表格
     
171. mysql> show tables;
     
172. +------------------+
     
173. | Tables_in_fooknet |
     
174. +------------------+
     
175. | aa               |
     
176. | liuyan           |
     
177. +------------------+
     
178. 2 rows in set (0.00 sec)
     
179. 
     
180. --删除一个不存在的表格
     
181. mysql> drop table bb;
     
182. ERROR 1051 (42S02): Unknown table 'bb'
     
183. --删除不存在的bb表，不报错
     
184. mysql> drop table if exists bb;
     
185. Query OK, 0 rows affected, 1 warning (0.00 sec)
     
186. 
     
187. mysql> drop table if exists aa;
     
188. Query OK, 0 rows affected (0.00 sec)
     
189. 
     
190. mysql> drop table if exists aa;
     
191. Query OK, 0 rows affected, 1 warning (0.00 sec)
     
192. 
     
193. mysql>
     
194. mysql>
     
195. --查看表结构
     
196. mysql> desc liuyan;
     
197. +---------+------------------+------+-----+---------+----------------+
     
198. | Field   | Type             | Null | Key | Default | Extra          |
     
199. +---------+------------------+------+-----+---------+----------------+
     
200. | id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
201. | title   | varchar(32)      | NO   |     | NULL    |                |
     
202. | author  | varchar(32)      | NO   |     | wu      |                |
     
203. | addtime | datetime         | YES  |     | NULL    |                |
     
204. | content | text             | NO   |     | NULL    |                |
     
205. +---------+------------------+------+-----+---------+----------------+
     
206. 5 rows in set (0.02 sec)
     
207. 
     
208. --采用标准方式添加一条数据
     
209. mysql> insert into liuyan(id,title,author,addtime,content)
     
210.     -> values(1,'news','zhangsan','2012-05-22 12:32:50','hello mysql');
     
211. Query OK, 1 row affected (0.01 sec)
     
212. 
     
213. --查看留言表中的所有数据。（*表示所有列）
     
214. mysql> select * from liuyan;
     
215. +----+-------+----------+---------------------+-------------+
     
216. | id | title | author   | addtime             | content     |
     
217. +----+-------+----------+---------------------+-------------+
     
218. |  1 | news  | zhangsan | 2012-05-22 12:32:50 | hello mysql |
     
219. +----+-------+----------+---------------------+-------------+
     
220. 1 row in set (0.00 sec)
     
221. 
     
222. mysql>
     
223. --添加数据（字段名的顺序可以颠倒）
     
224. mysql> insert into liuyan(id,content,addtime,title,author)
     
225.     -> values(2,'aaaaa','2012-12-24 12:12:12','info','lisi');
     
226. Query OK, 1 row affected (0.00 sec)
     
227. 
     
228. --只指定部分字段名来添加数据
     
229. mysql> insert into liuyan(title,addtime,content)
     
230.     -> values('test','2012-12-22','hello');
     
231. Query OK, 1 row affected (0.00 sec)
     
232. 
     
233. mysql> desc liuyan;
     
234. +---------+------------------+------+-----+---------+----------------+
     
235. | Field   | Type             | Null | Key | Default | Extra          |
     
236. +---------+------------------+------+-----+---------+----------------+
     
237. | id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
238. | title   | varchar(32)      | NO   |     | NULL    |                |
     
239. | author  | varchar(32)      | NO   |     | wu      |                |
     
240. | addtime | datetime         | YES  |     | NULL    |                |
     
241. | content | text             | NO   |     | NULL    |                |
     
242. +---------+------------------+------+-----+---------+----------------+
     
243. 5 rows in set (0.00 sec)
     
244. 
     
245. --不指定字段名添加数据(values中要给所有字段赋值，并顺序一致)
     
246. mysql> insert into liuyan
     
247.         -> values(null,'demo','wangwu','2012-09-10 12:34:56','ddddd');
     
248. Query OK, 1 row affected (0.00 sec)
     
249. 
     
250. mysql> select * from liuyan;
     
251. +----+-------+----------+---------------------+-------------+
     
252. | id | title | author   | addtime             | content     |
     
253. +----+-------+----------+---------------------+-------------+
     
254. |  1 | news  | zhangsan | 2012-05-22 12:32:50 | hello mysql |
     
255. |  2 | info  | lisi     | 2012-12-24 12:12:12 | aaaaa       |
     
256. |  3 | test  | wu       | 2012-12-22 00:00:00 | hello       |
     
257. |  4 | demo  | wangwu   | 2012-09-10 12:34:56 | ddddd       |
     
258. +----+-------+----------+---------------------+-------------+
     
259. 4 rows in set (0.00 sec)
     
260. 
     
261. mysql>
     
262. ---批量添加3条数据
     
263. mysql> insert into liuyan
     
264.     -> values(null,'tt1','lisi1','2012-02-03 12:21:34','dddd'),
     
265.     -> (null,'tt2','lisi2','2012-02-03 12:21:34','eeee'),
     
266.     -> (null,'tt3','lisi3','2012-02-03 12:21:34','ffff');
     
267. Query OK, 3 rows affected (0.00 sec)
     
268. Records: 3  Duplicates: 0  Warnings: 0
     
269. 
     
270. mysql> select * from liuyan;
     
271. +----+-------+----------+---------------------+-------------+
     
272. | id | title | author   | addtime             | content     |
     
273. +----+-------+----------+---------------------+-------------+
     
274. |  1 | news  | zhangsan | 2012-05-22 12:32:50 | hello mysql |
     
275. |  2 | info  | lisi     | 2012-12-24 12:12:12 | aaaaa       |
     
276. |  3 | test  | wu       | 2012-12-22 00:00:00 | hello       |
     
277. |  4 | demo  | wangwu   | 2012-09-10 12:34:56 | ddddd       |
     
278. |  5 | tt1   | lisi1    | 2012-02-03 12:21:34 | dddd        |
     
279. |  6 | tt2   | lisi2    | 2012-02-03 12:21:34 | eeee        |
     
280. |  7 | tt3   | lisi3    | 2012-02-03 12:21:34 | ffff        |
     
281. +----+-------+----------+---------------------+-------------+
     
282. 7 rows in set (0.00 sec)
     
283. 
     
284. mysql>
     
285. 
     
286. 
     
287. mysql> create table demo(
     
288.     -> d1 tinyint not null,
     
289.     -> d2 int(4) zerofill not null,
     
290.     -> d3 double(4,2) not null,
     
291.     -> d4 varchar(4) not null);
     
292. Query OK, 0 rows affected (0.05 sec)
     
293. 
     
294. mysql> desc demo;
     
295. +-------+--------------------------+------+-----+---------+-------+
     
296. | Field | Type                     | Null | Key | Default | Extra |
     
297. +-------+--------------------------+------+-----+---------+-------+
     
298. | d1    | tinyint(4)               | NO   |     | NULL    |       |
     
299. | d2    | int(4) unsigned zerofill | NO   |     | NULL    |       |
     
300. | d3    | double(4,2)              | NO   |     | NULL    |       |
     
301. | d4    | varchar(4)               | NO   |     | NULL    |       |
     
302. +-------+--------------------------+------+-----+---------+-------+
     
303. 4 rows in set (0.00 sec)
     
304. 
     
305. mysql> show create table demo \G;
     
306. *************************** 1. row ***************************
     
307.        Table: demo
     
308. Create Table: CREATE TABLE `demo` (
     
309.   `d1` tinyint(4) NOT NULL,
     
310.   `d2` int(4) unsigned zerofill NOT NULL,
     
311.   `d3` double(4,2) NOT NULL,
     
312.   `d4` varchar(4) NOT NULL
     
313. ) ENGINE=MyISAM DEFAULT CHARSET=utf8
     
314. 1 row in set (0.00 sec)
     
315. 
     
316. ERROR:
     
317. No query specified
     
318. 
     
319. mysql>mysql> desc demo;
     
320. +-------+--------------------------+------+-----+---------+-------+
     
321. | Field | Type                     | Null | Key | Default | Extra |
     
322. +-------+--------------------------+------+-----+---------+-------+
     
323. | d1    | tinyint(4)               | NO   |     | NULL    |       |
     
324. | d2    | int(4) unsigned zerofill | NO   |     | NULL    |       |
     
325. | d3    | double(4,2)              | NO   |     | NULL    |       |
     
326. | d4    | varchar(4)               | NO   |     | NULL    |       |
     
327. +-------+--------------------------+------+-----+---------+-------+
     
328. 4 rows in set (0.00 sec)
     
329. 
     
330. mysql> insert into demo values(11,22,123.456,"abcdef");
     
331. Query OK, 1 row affected, 2 warnings (0.00 sec)
     
332. 
     
333. mysql> select * from demo;
     
334. +----+------+-------+------+
     
335. | d1 | d2   | d3    | d4   |
     
336. +----+------+-------+------+
     
337. | 11 | 0022 | 99.99 | abcd |
     
338. +----+------+-------+------+
     
339. 1 row in set (0.00 sec)
     
340. 
     
341. mysql> insert into demo values(11,12345,12.3456,"abc");
     
342. Query OK, 1 row affected (0.00 sec)
     
343. 
     
344. mysql> select * from demo;
     
345. +----+-------+-------+------+
     
346. | d1 | d2    | d3    | d4   |
     
347. +----+-------+-------+------+
     
348. | 11 |  0022 | 99.99 | abcd |
     
349. | 11 | 12345 | 12.35 | abc  |
     
350. +----+-------+-------+------+
     
351. 2 rows in set (0.00 sec)
     
352. 
     
353. mysql> insert into demo values(128,22,123.456,"abcdef");
     
354. Query OK, 1 row affected, 3 warnings (0.00 sec)
     
355. 
     
356. mysql> select * from demo;
     
357. +-----+-------+-------+------+
     
358. | d1  | d2    | d3    | d4   |
     
359. +-----+-------+-------+------+
     
360. |  11 |  0022 | 99.99 | abcd |
     
361. |  11 | 12345 | 12.35 | abc  |
     
362. | 127 |  0022 | 99.99 | abcd |
     
363. +-----+-------+-------+------+
     
364. 3 rows in set (0.00 sec)
     
365. 
     
366. mysql>
     
367. 
     
368. ================================================================
     
369.         表结构的修改
     
370. ================================================================
     
371. --创建一个测试表格
     
372. mysql> create table news(
     
373.     -> title varchar(32) not null);
     
374. Query OK, 0 rows affected (0.08 sec)
     
375. 
     
376. --显示表结构
     
377. mysql> desc news;
     
378. +-------+-------------+------+-----+---------+-------+
     
379. | Field | Type        | Null | Key | Default | Extra |
     
380. +-------+-------------+------+-----+---------+-------+
     
381. | title | varchar(32) | NO   |     | NULL    |       |
     
382. +-------+-------------+------+-----+---------+-------+
     
383. 1 row in set (0.00 sec)
     
384. 
     
385. --为表添加一个主键列
     
386. mysql> alter table news add id int unsigned not null auto_increment primary key
     
387. first;
     
388. Query OK, 0 rows affected (0.09 sec)
     
389. Records: 0  Duplicates: 0  Warnings: 0
     
390. 
     
391. --显示表结构
     
392. mysql> desc news;
     
393. +-------+------------------+------+-----+---------+----------------+
     
394. | Field | Type             | Null | Key | Default | Extra          |
     
395. +-------+------------------+------+-----+---------+----------------+
     
396. | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
397. | title | varchar(32)      | NO   |     | NULL    |                |
     
398. +-------+------------------+------+-----+---------+----------------+
     
399. 2 rows in set (0.01 sec)
     
400. 
     
401. mysql> alter table news add content text not null;
     
402. Query OK, 0 rows affected (0.06 sec)
     
403. Records: 0  Duplicates: 0  Warnings: 0
     
404. 
     
405. --显示表结构
     
406. mysql> desc news;
     
407. +---------+------------------+------+-----+---------+----------------+
     
408. | Field   | Type             | Null | Key | Default | Extra          |
     
409. +---------+------------------+------+-----+---------+----------------+
     
410. | id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
411. | title   | varchar(32)      | NO   |     | NULL    |                |
     
412. | content | text             | NO   |     | NULL    |                |
     
413. +---------+------------------+------+-----+---------+----------------+
     
414. 3 rows in set (0.02 sec)
     
415. 
     
416. --为表添加一个作者字段 varchar32位，在标题后面
     
417. mysql> alter table news add author varchar(32) not null after title;
     
418. Query OK, 0 rows affected (0.08 sec)
     
419. Records: 0  Duplicates: 0  Warnings: 0
     
420. 
     
421. --显示表结构
     
422. mysql> desc news;
     
423. +---------+------------------+------+-----+---------+----------------+
     
424. | Field   | Type             | Null | Key | Default | Extra          |
     
425. +---------+------------------+------+-----+---------+----------------+
     
426. | id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
427. | title   | varchar(32)      | NO   |     | NULL    |                |
     
428. | author  | varchar(32)      | NO   |     | NULL    |                |
     
429. | content | text             | NO   |     | NULL    |                |
     
430. +---------+------------------+------+-----+---------+----------------+
     
431. 4 rows in set (0.02 sec)
     
432. 
     
433. --将字段author修改为username类型varchar(16)
     
434. mysql> alter table news change author username varchar(16) not null;
     
435. Query OK, 0 rows affected (0.08 sec)
     
436. Records: 0  Duplicates: 0  Warnings: 0
     
437. 
     
438. --显示表结构
     
439. mysql> desc news;
     
440. +----------+------------------+------+-----+---------+----------------+
     
441. | Field    | Type             | Null | Key | Default | Extra          |
     
442. +----------+------------------+------+-----+---------+----------------+
     
443. | id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
444. | title    | varchar(32)      | NO   |     | NULL    |                |
     
445. | username | varchar(16)      | NO   |     | NULL    |                |
     
446. | content  | text             | NO   |     | NULL    |                |
     
447. +----------+------------------+------+-----+---------+----------------+
     
448. 4 rows in set (0.01 sec)
     
449. 
     
450. --删除一个字段
     
451. mysql> alter table news drop content;
     
452. Query OK, 0 rows affected (0.08 sec)
     
453. Records: 0  Duplicates: 0  Warnings: 0
     
454. 
     
455. --显示表结构
     
456. mysql> desc news;
     
457. +----------+------------------+------+-----+---------+----------------+
     
458. | Field    | Type             | Null | Key | Default | Extra          |
     
459. +----------+------------------+------+-----+---------+----------------+
     
460. | id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
     
461. | title    | varchar(32)      | NO   |     | NULL    |                |
     
462. | username | varchar(16)      | NO   |     | NULL    |                |
     
463. +----------+------------------+------+-----+---------+----------------+
     
464. 3 rows in set (0.01 sec)
     
465. 
     
466. mysql>
     
467. 
     
468. 
     
469. ================================================================
     
470.         修改表数据
     
471. ================================================================
     
472. 
     
473. mysql>
     
474. mysql> select * from liuyan;
     
475. +----+-------+----------+---------------------+-------------+
     
476. | id | title | author   | addtime             | content     |
     
477. +----+-------+----------+---------------------+-------------+
     
478. |  1 | news  | zhangsan | 2012-05-22 12:32:50 | hello mysql |
     
479. |  2 | info  | lisi     | 2012-12-24 12:12:12 | aaaaa       |
     
480. |  3 | test  | wu       | 2012-12-22 00:00:00 | hello       |
     
481. |  4 | demo  | wangwu   | 2012-09-10 12:34:56 | ddddd       |
     
482. |  5 | tt1   | lisi1    | 2012-02-03 12:21:34 | dddd        |
     
483. |  6 | tt2   | lisi2    | 2012-02-03 12:21:34 | eeee        |
     
484. |  7 | tt3   | lisi3    | 2012-02-03 12:21:34 | ffff        |
     
485. +----+-------+----------+---------------------+-------------+
     
486. 7 rows in set (0.00 sec)
     
487. 
     
488. --修改留言表中author字段值为qq（所有数据）
     
489. mysql> update liuyan set author='qq';
     
490. Query OK, 7 rows affected (0.00 sec)
     
491. Rows matched: 7  Changed: 7  Warnings: 0
     
492. 
     
493. mysql> select * from liuyan;
     
494. +----+-------+--------+---------------------+-------------+
     
495. | id | title | author | addtime             | content     |
     
496. +----+-------+--------+---------------------+-------------+
     
497. |  1 | news  | qq     | 2012-05-22 12:32:50 | hello mysql |
     
498. |  2 | info  | qq     | 2012-12-24 12:12:12 | aaaaa       |
     
499. |  3 | test  | qq     | 2012-12-22 00:00:00 | hello       |
     
500. |  4 | demo  | qq     | 2012-09-10 12:34:56 | ddddd       |
     
501. |  5 | tt1   | qq     | 2012-02-03 12:21:34 | dddd        |
     
502. |  6 | tt2   | qq     | 2012-02-03 12:21:34 | eeee        |
     
503. |  7 | tt3   | qq     | 2012-02-03 12:21:34 | ffff        |
     
504. +----+-------+--------+---------------------+-------------+
     
505. 7 rows in set (0.00 sec)
     
506. 
     
507. --修改留言表中某列的某几条数据。
     
508. mysql> update liuyan set author='zhangsan' where  id in(1,3,5);
     
509. Query OK, 3 rows affected (0.03 sec)
     
510. Rows matched: 3  Changed: 3  Warnings: 0
     
511. 
     
512. mysql> select * from liuyan;
     
513. +----+-------+----------+---------------------+-------------+
     
514. | id | title | author   | addtime             | content     |
     
515. +----+-------+----------+---------------------+-------------+
     
516. |  1 | news  | zhangsan | 2012-05-22 12:32:50 | hello mysql |
     
517. |  2 | info  | qq       | 2012-12-24 12:12:12 | aaaaa       |
     
518. |  3 | test  | zhangsan | 2012-12-22 00:00:00 | hello       |
     
519. |  4 | demo  | qq       | 2012-09-10 12:34:56 | ddddd       |
     
520. |  5 | tt1   | zhangsan | 2012-02-03 12:21:34 | dddd        |
     
521. |  6 | tt2   | qq       | 2012-02-03 12:21:34 | eeee        |
     
522. |  7 | tt3   | qq       | 2012-02-03 12:21:34 | ffff        |
     
523. +----+-------+----------+---------------------+-------------+
     
524. 7 rows in set (0.00 sec)
     
525. 
     
526. --修改某条数据的某几列。
     
527. mysql> update liuyan set title='llllll',author="lisi",content="qwertyuio"
     
528.     -> where id=7;
     
529. Query OK, 1 row affected (0.00 sec)
     
530. Rows matched: 1  Changed: 1  Warnings: 0
     
531. 
     
532. mysql> select * from liuyan;
     
533. +----+--------+----------+---------------------+-------------+
     
534. | id | title  | author   | addtime             | content     |
     
535. +----+--------+----------+---------------------+-------------+
     
536. |  1 | news   | zhangsan | 2012-05-22 12:32:50 | hello mysql |
     
537. |  2 | info   | qq       | 2012-12-24 12:12:12 | aaaaa       |
     
538. |  3 | test   | zhangsan | 2012-12-22 00:00:00 | hello       |
     
539. |  4 | demo   | qq       | 2012-09-10 12:34:56 | ddddd       |
     
540. |  5 | tt1    | zhangsan | 2012-02-03 12:21:34 | dddd        |
     
541. |  6 | tt2    | qq       | 2012-02-03 12:21:34 | eeee        |
     
542. |  7 | llllll | lisi     | 2012-02-03 12:21:34 | qwertyuio   |
     
543. +----+--------+----------+---------------------+-------------+
     
544. 7 rows in set (0.00 sec)
     
545. 
     
546. --将6号信息中的作者改为王五
     
547. mysql>update liuyan set author="wangwu" where id=6;
     
548. 
     
549. 
     
550. mysql> select * from demo;
     
551. +-----+-------+-------+------+
     
552. | d1  | d2    | d3    | d4   |
     
553. +-----+-------+-------+------+
     
554. |  11 |  0022 | 99.99 | abcd |
     
555. |  11 | 12345 | 12.35 | abc  |
     
556. | 127 |  0022 | 99.99 | abcd |
     
557. +-----+-------+-------+------+
     
558. 3 rows in set (0.00 sec)
     
559. --将demo表中d1字段的所有值都减一。
     
560. mysql> update demo set d1=d1-1;
     
561. Query OK, 3 rows affected (0.00 sec)
     
562. Rows matched: 3  Changed: 3  Warnings: 0
     
563. 
     
564. mysql> select * from demo;
     
565. +-----+-------+-------+------+
     
566. | d1  | d2    | d3    | d4   |
     
567. +-----+-------+-------+------+
     
568. |  10 |  0022 | 99.99 | abcd |
     
569. |  10 | 12345 | 12.35 | abc  |
     
570. | 126 |  0022 | 99.99 | abcd |
     
571. +-----+-------+-------+------+
     
572. 3 rows in set (0.00 sec)
     
573. 
     
574. 
     
575. 
     
576. 
     
577. 
     
578. 
     
579. 
     
580. 
     

复制代码

1. 
   
2. 
   
3. mysql> use fooknet
   
4. Database changed
   
5. mysql> select database();
   
6. +------------+
   
7. | database() |
   
8. +------------+
   
9. | fooknet     |
   
10. +------------+
    
11. 1 row in set (0.00 sec)
    
12. 
    
13. mysql> show tables;
    
14. +------------------+
    
15. | Tables_in_fooknet |
    
16. +------------------+
    
17. | demo             |
    
18. | liuyan           |
    
19. | news             |
    
20. +------------------+
    
21. 3 rows in set (0.06 sec)
    
22. 
    
23. mysql> create table stu(
    
24.     -> id int unsigned not null auto_increment primary key,
    
25.     -> name varchar(16) unique not null,
    
26.     -> sex enum('m','w') not null default 'm',
    
27.     -> age tinyint unsigned ,
    
28.     -> classid char(6) not null);
    
29. Query OK, 0 rows affected (0.13 sec)
    
30. 
    
31. mysql> create table grade(
    
32.     -> id int unsigned not null auto_increment primary key,
    
33.     -> sid int unsigned not null,
    
34.     -> php tinyint unsigned not null,
    
35.     -> mysql tinyint unsigned not null);
    
36. Query OK, 0 rows affected (0.06 sec)
    
37. 
    
38. mysql> desc stu;
    
39. +---------+---------------------+------+-----+---------+----------------+
    
40. | Field   | Type                | Null | Key | Default | Extra          |
    
41. +---------+---------------------+------+-----+---------+----------------+
    
42. | id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    
43. | name    | varchar(16)         | NO   | UNI | NULL    |                |
    
44. | sex     | enum('m','w')       | NO   |     | m       |                |
    
45. | age     | tinyint(3) unsigned | YES  |     | NULL    |                |
    
46. | classid | char(6)             | NO   |     | NULL    |                |
    
47. +---------+---------------------+------+-----+---------+----------------+
    
48. 5 rows in set (0.08 sec)
    
49. 
    
50. mysql> desc grade;
    
51. +-------+---------------------+------+-----+---------+----------------+
    
52. | Field | Type                | Null | Key | Default | Extra          |
    
53. +-------+---------------------+------+-----+---------+----------------+
    
54. | id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    
55. | sid   | int(10) unsigned    | NO   |     | NULL    |                |
    
56. | php   | tinyint(3) unsigned | NO   |     | NULL    |                |
    
57. | mysql | tinyint(3) unsigned | NO   |     | NULL    |                |
    
58. +-------+---------------------+------+-----+---------+----------------+
    
59. 4 rows in set (0.00 sec)
    
60. 
    
61. mysql> insert into stu values(null,'zhangsan','m',20,'fooknet');
    
62. Query OK, 1 row affected (0.03 sec)
    
63. 
    
64. mysql> insert into stu values(null,'lisi','w',21,'fooknet'),
    
65.     -> (null,'wangwu','m',20,'lamp46'),
    
66.     -> (null,'zhaoliu','w',19,'lamp47'),
    
67.     -> (null,'tianqi','m',22,'lamp47'),
    
68.     -> (null,'xiaowang','w',24,'lamp46'),
    
69.     -> (null,'aa','w',25,'fooknet'),
    
70.     -> (null,'qq','w',27,'fooknet'),
    
71.     -> (null,'dd','m',20,'lamp46');
    
72. Query OK, 8 rows affected (0.00 sec)
    
73. Records: 8  Duplicates: 0  Warnings: 0
    
74. 
    
75. mysql> insert into stu values(null,'pp','w',25,'fooknet');
    
76. Query OK, 1 row affected (0.00 sec)
    
77. 
    
78. mysql> select * from stu;
    
79. +----+----------+-----+------+---------+
    
80. | id | name     | sex | age  | classid |
    
81. +----+----------+-----+------+---------+
    
82. |  1 | zhangsan | m   |   20 | fooknet  |
    
83. |  2 | lisi     | w   |   21 | fooknet  |
    
84. |  3 | wangwu   | m   |   20 | lamp46  |
    
85. |  4 | zhaoliu  | w   |   19 | lamp47  |
    
86. |  5 | tianqi   | m   |   22 | lamp47  |
    
87. |  6 | xiaowang | w   |   24 | lamp46  |
    
88. |  7 | aa       | w   |   25 | fooknet  |
    
89. |  8 | qq       | w   |   27 | fooknet  |
    
90. |  9 | dd       | m   |   20 | lamp46  |
    
91. | 10 | pp       | w   |   25 | fooknet  |
    
92. +----+----------+-----+------+---------+
    
93. 10 rows in set (0.00 sec)
    
94. 
    
95. mysql> desc grade;
    
96. +-------+---------------------+------+-----+---------+----------------+
    
97. | Field | Type                | Null | Key | Default | Extra          |
    
98. +-------+---------------------+------+-----+---------+----------------+
    
99. | id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    
100. | sid   | int(10) unsigned    | NO   |     | NULL    |                |
     
101. | php   | tinyint(3) unsigned | NO   |     | NULL    |                |
     
102. | mysql | tinyint(3) unsigned | NO   |     | NULL    |                |
     
103. +-------+---------------------+------+-----+---------+----------------+
     
104. 4 rows in set (0.00 sec)
     
105. 
     
106. mysql> insert into grade values(null,5,80,86),
     
107.     -> (null,6,58,62),(null,4,90,98),
     
108.     -> (null,1,87,90),(null,2,90,69),
     
109.     -> (null,7,52,58),(null,3,67,86);
     
110. Query OK, 7 rows affected (0.00 sec)
     
111. Records: 7  Duplicates: 0  Warnings: 0
     
112. 
     
113. mysql> select * from stu;
     
114. +----+----------+-----+------+---------+
     
115. | id | name     | sex | age  | classid |
     
116. +----+----------+-----+------+---------+
     
117. |  1 | zhangsan | m   |   20 | fooknet  |
     
118. |  2 | lisi     | w   |   21 | fooknet  |
     
119. |  3 | wangwu   | m   |   20 | lamp46  |
     
120. |  4 | zhaoliu  | w   |   19 | lamp47  |
     
121. |  5 | tianqi   | m   |   22 | lamp47  |
     
122. |  6 | xiaowang | w   |   24 | lamp46  |
     
123. |  7 | aa       | w   |   25 | fooknet  |
     
124. |  8 | qq       | w   |   27 | fooknet  |
     
125. |  9 | dd       | m   |   20 | lamp46  |
     
126. | 10 | pp       | w   |   25 | fooknet  |
     
127. +----+----------+-----+------+---------+
     
128. 10 rows in set (0.00 sec)
     
129. 
     
130. mysql> select * from grade;
     
131. +----+-----+-----+-------+
     
132. | id | sid | php | mysql |
     
133. +----+-----+-----+-------+
     
134. |  1 |   5 |  80 |    86 |
     
135. |  2 |   6 |  58 |    62 |
     
136. |  3 |   4 |  90 |    98 |
     
137. |  4 |   1 |  87 |    90 |
     
138. |  5 |   2 |  90 |    69 |
     
139. |  6 |   7 |  52 |    58 |
     
140. |  7 |   3 |  67 |    86 |
     
141. +----+-----+-----+-------+
     
142. 7 rows in set (0.00 sec)
     
143. 
     
144. mysql> update grade set mysql=mysql+1;
     
145. Query OK, 7 rows affected (0.05 sec)
     
146. Rows matched: 7  Changed: 7  Warnings: 0
     
147. 
     
148. mysql> select * from grade;
     
149. +----+-----+-----+-------+
     
150. | id | sid | php | mysql |
     
151. +----+-----+-----+-------+
     
152. |  1 |   5 |  80 |    87 |
     
153. |  2 |   6 |  58 |    63 |
     
154. |  3 |   4 |  90 |    99 |
     
155. |  4 |   1 |  87 |    91 |
     
156. |  5 |   2 |  90 |    70 |
     
157. |  6 |   7 |  52 |    59 |
     
158. |  7 |   3 |  67 |    87 |
     
159. +----+-----+-----+-------+
     
160. 7 rows in set (0.00 sec)
     
161. 
     
162. mysql> quit
     
163. Bye
     
164. 
     
165. C:\Documents and Settings\admin>mysqldump
     
166. Usage: mysqldump [OPTIONS] database [tables]
     
167. OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
     
168. OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
     
169. For more options, use mysqldump --help
     
170. 
     
171. C:\Documents and Settings\admin>d:
     
172. 
     
173. D:\>cd D:\AppServ\MySQL\bin
     
174. 
     
175. D:\AppServ\MySQL\bin>dir
     
176. 驱动器 D 中的卷没有标签。
     
177. 卷的序列号是 000E-9EA3
     
178. 
     
179. D:\AppServ\MySQL\bin 的目录
     
180. 
     
181. 2012-04-10  11:13    <DIR>          .
     
182. 2012-04-10  11:13    <DIR>          ..
     
183. 2008-04-17  20:13         2,052,736 mysql.exe
     
184. 2005-10-25  18:23               894 mysql.ico
     
185. 2008-04-17  20:13         2,003,584 mysqladmin.exe
     
186. 2008-04-17  20:13         5,750,784 mysqld-nt.exe
     
187. 2008-04-17  20:13         2,048,640 mysqldump.exe
     
188.                5 个文件     11,856,638 字节
     
189.                2 个目录  7,268,278,272 可用字节
     
190. 
     
191.     数据的备份与恢复
     
192. =====================================================
     
193. 切记是在DOS命令行下执行：
     
194. --备份(导出)数据库fooknet
     
195. D:\AppServ\MySQL\bin>mysqldump -u root -p fooknet>fooknet_20120523.sql
     
196. Enter password: ****
     
197. 
     
198. --备份(导出)数据库fooknet中的一个stu表。
     
199. D:\AppServ\MySQL\bin>mysqldump -u root -p fooknet stu>fooknet_stu_20120523.sql
     
200. Enter password: ****
     
201. 
     
202. --数据的恢复（导入）
     
203. D:\AppServ\www\fooknet\mysql>mysql -u root -p fooknet <fooknet_20120523.sql
     
204. Enter password: ****
     
205. 
     

复制代码