Top

NSD DBA DAY04

1. 案例1：表管理
2. 案例2：數據類型
3. 案例3：數據批量處理
4. 案例4：表頭基本約束

1 案例1：表管理

1.1 問題

1. 建庫練習
2. 建表練習
3. 修改表練習

1.2 方案

在MySQL50主機完成練習。

1.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：建庫練習

庫名命名規則：

僅可以使用數字、字母、下劃線、不能純數字

區分字母大小寫，

具有唯一性

不可使用MySQL命令或特殊字符

命令操作如下所示：

1. //庫名區分字母大小寫
2. mysql> create database gamedb ;
3. Query OK, 1 row affected (0.14 sec)
4. mysql> create database GAMEDB ;
5. Query OK, 1 row affected (0.08 sec)
6. mysql> create database GAMEDB ;
7. ERROR 1007 (HY000): Can't create database 'GAMEDB'; database exists //重名報錯
8. //加if not exists 命令避免重名報錯
9. mysql> create database if not exists gamedb ;
10. Query OK, 1 row affected, 1 warning (0.03 sec) //正常
11. mysql> show databases; //查看創建的庫
12. +--------------------+
13. | Database |
14. +--------------------+
15. | GAMEDB |
16. | gamedb |
17. | information_schema |
18. | mysql |
19. | performance_schema |
20. | sys |
21. | tarena |
22. +--------------------+
23. 7 rows in set (0.00 sec)
24. mysql> drop database gamedb; //刪除庫
25. Query OK, 0 rows affected (0.11 sec)
26. mysql> drop database gamedb; // 刪除沒有的庫報錯
27. ERROR 1008 (HY000): Can’t drop database ‘gamedb’; database doesn’t exist
28. //加if exists 刪除沒有的庫，也不報錯
29. mysql> drop database if exists gamedb;
30. Query OK, 0 rows affected, 1 warning (0.00 sec)

步驟二：建表練習

命令操作如下所示：

1. mysql> create database 學生庫; //建庫
2. Query OK, 1 row affected (0.11 sec)
3. mysql> create table 學生庫.學生信息表( //建表
4. -> 姓名 char(10),
5. -> 班級 char(9),
6. -> 性別 char(4),
7. -> 年齡 int
8. -> );
9. Query OK, 0 rows affected (0.47 sec)
10. mysql> use 學生庫; //進入庫
11. Reading table information for completion of table and column names
12. You can turn off this feature to get a quicker startup with -A
13. Database changed
14. mysql> show tables; //查看表
15. +---------------------+
16. | Tables_in_學生庫 |
17. +---------------------+
18. | 學生信息表 |
19. +---------------------+
20. 1 row in set (0.00 sec)
21. mysql> desc 學生信息表; //查看表頭
22. +--------+----------+------+-----+---------+-------+
23. | Field | Type | Null | Key | Default | Extra |
24. +--------+----------+------+-----+---------+-------+
25. | 姓名 | char(10) | YES | | NULL | |
26. | 班級 | char(9) | YES | | NULL | |
27. | 性別 | char(4) | YES | | NULL | |
28. | 年齡 | int | YES | | NULL | |
29. +--------+----------+------+-----+---------+-------+
30. 4 rows in set (0.00 sec)
31. //刪除表
32. mysql> drop table 學生庫.學生信息表;
33. //刪除庫
34. mysql> drop database 學生庫;

使用英文命名，重新建庫、建表

1. mysql> create database studb; //建庫
2. Query OK, 1 row affected (0.11 sec)
3. mysql> create table studb.stu( //建表
4. -> name char(10),
5. -> class char(9),
6. -> gender char(4),
7. -> age int
8. -> );
9. Query OK, 0 rows affected (1.17 sec)
10. mysql> desc studb.stu; //查看表頭
11. +--------+----------+------+-----+---------+-------+
12. | Field | Type | Null | Key | Default | Extra |
13. +--------+----------+------+-----+---------+-------+
14. | name | char(10) | YES | | NULL | |
15. | class | char(9) | YES | | NULL | |
16. | gender | char(4) | YES | | NULL | |
17. | age | int | YES | | NULL | |
18. +--------+----------+------+-----+---------+-------+
19. 4 rows in set (0.00 sec)

步驟三：修改表練習

命令操作如下所示：

1. mysql> alter table studb.stu rename studb.stuinfo; //修改表名
2. Query OK, 0 rows affected (0.28 sec)
3. mysql> use studb; //進入庫
4. Reading table information for completion of table and column names
5. You can turn off this feature to get a quicker startup with -A
6. Database changed
7. mysql> show tables; //查看表
8. +-----------------+
9. | Tables_in_studb |
10. +-----------------+
11. | stuinfo |
12. +-----------------+
13. 1 row in set (0.00 sec)
14. mysql> alter table studb.stuinfo drop age ; //刪除age表頭
15. Query OK, 0 rows affected (0.52 sec)
16. Records: 0 Duplicates: 0 Warnings: 0
17. mysql> desc stuinfo; //查看表頭
18. +--------+----------+------+-----+---------+-------+
19. | Field | Type | Null | Key | Default | Extra |
20. +--------+----------+------+-----+---------+-------+
21. | name | char(10) | YES | | NULL | |
22. | class | char(9) | YES | | NULL | |
23. | gender | char(4) | YES | | NULL | |
24. +--------+----------+------+-----+---------+-------+
25. 3 rows in set (0.00 sec)
26. //添加表頭，默認添加在末尾
27. mysql> alter table studb.stuinfo add mail char(30) ;
28. Query OK, 0 rows affected (0.24 sec)
29. Records: 0 Duplicates: 0 Warnings: 0
30. //查看表頭
31. mysql> desc studb.stuinfo;
32. +--------+----------+------+-----+---------+-------+
33. | Field | Type | Null | Key | Default | Extra |
34. +--------+----------+------+-----+---------+-------+
35. | name | char(10) | YES | | NULL | |
36. | class | char(9) | YES | | NULL | |
37. | gender | char(4) | YES | | NULL | |
38. | mail | char(30) | YES | | NULL | |
39. +--------+----------+------+-----+---------+-------+
40. 4 rows in set (0.00 sec)
41. //first 把表頭添加首位
42. //after 添加在指定表頭名的下方
43. mysql> alter table studb.stuinfo add number char(9) first , add school char(10) after name;
44. Query OK, 0 rows affected (0.48 sec)
45. Records: 0 Duplicates: 0 Warnings: 0
46. //查看表結構
47. mysql> desc studb.stuinfo; //查看表頭
48. +--------+----------+------+-----+---------+-------+
49. | Field | Type | Null | Key | Default | Extra |
50. +--------+----------+------+-----+---------+-------+
51. | number | char(9) | YES | | NULL | |
52. | name | char(10) | YES | | NULL | |
53. | school | char(10) | YES | | NULL | |
54. | class | char(9) | YES | | NULL | |
55. | gender | char(4) | YES | | NULL | |
56. | mail | char(30) | YES | | NULL | |
57. +--------+----------+------+-----+---------+-------+
58. 6 rows in set (0.00 sec)
59. //修改表頭數據類型
60. mysql> alter table studb.stuinfo modify mail varchar(50);
61. Query OK, 0 rows affected (1.17 sec)
62. Records: 0 Duplicates: 0 Warnings: 0
64. mysql> desc studb.stuinfo;
65. +--------+-------------+------+-----+---------+-------+
66. | Field | Type | Null | Key | Default | Extra |
67. +--------+-------------+------+-----+---------+-------+
68. | number | char(9) | YES | | NULL | |
69. | name | char(10) | YES | | NULL | |
70. | school | char(10) | YES | | NULL | |
71. | class | char(9) | YES | | NULL | |
72. | gender | char(4) | YES | | NULL | |
73. | mail | varchar(50) | YES | | NULL | |
74. +--------+-------------+------+-----+---------+-------+
75. 6 rows in set (0.01 sec)
76. //修改表頭名
77. mysql> alter table studb.stuinfo change class 班級 char(9) ;
78. Query OK, 0 rows affected (0.12 sec)
79. Records: 0 Duplicates: 0 Warnings: 0
80. //查看表頭
81. mysql> desc studb.stuinfo;
82. +--------+-------------+------+-----+---------+-------+
83. | Field | Type | Null | Key | Default | Extra |
84. +--------+-------------+------+-----+---------+-------+
85. | number | char(9) | YES | | NULL | |
86. | name | char(10) | YES | | NULL | |
87. | school | char(10) | YES | | NULL | |
88. | 班級 | char(9) | YES | | NULL | |
89. | gender | char(4) | YES | | NULL | |
90. | mail | varchar(50) | YES | | NULL | |
91. +--------+-------------+------+-----+---------+-------+
92. 6 rows in set (0.00 sec)
93. //一起刪除多個表頭
94. mysql> alter table studb.stuinfo drop school , drop 班級 ,drop mail ;
95. Query OK, 0 rows affected (0.73 sec)
96. Records: 0 Duplicates: 0 Warnings: 0
97. //查看表頭
98. mysql> desc studb.stuinfo;
99. +--------+----------+------+-----+---------+-------+
100. | Field | Type | Null | Key | Default | Extra |
101. +--------+----------+------+-----+---------+-------+
102. | number | char(9) | YES | | NULL | |
103. | name | char(10) | YES | | NULL | |
104. | gender | char(4) | YES | | NULL | |
105. +--------+----------+------+-----+---------+-------+
106. 3 rows in set (0.00 sec)
107. mysql>
108. //使用modify 修改表頭的位置
109. mysql> alter table studb.stuinfo modify gender char(4) after number;
110. Query OK, 0 rows affected (0.77 sec)
111. Records: 0 Duplicates: 0 Warnings: 0
112. //查看表頭
113. mysql> desc studb.stuinfo;
114. +--------+----------+------+-----+---------+-------+
115. | Field | Type | Null | Key | Default | Extra |
116. +--------+----------+------+-----+---------+-------+
117. | number | char(9) | YES | | NULL | |
118. | gender | char(4) | YES | | NULL | |
119. | name | char(10) | YES | | NULL | |
120. +--------+----------+------+-----+---------+-------+
121. 3 rows in set (0.00 sec)
122. //再修改回原來位置
123. mysql> alter table studb.stuinfo modify gender char(4) after name;
124. Query OK, 0 rows affected (0.50 sec)
125. Records: 0 Duplicates: 0 Warnings: 0
126. //查看表頭
127. mysql> desc studb.stuinfo;
128. +--------+----------+------+-----+---------+-------+
129. | Field | Type | Null | Key | Default | Extra |
130. +--------+----------+------+-----+---------+-------+
131. | number | char(9) | YES | | NULL | |
132. | name | char(10) | YES | | NULL | |
133. | gender | char(4) | YES | | NULL | |
134. +--------+----------+------+-----+---------+-------+
135. 3 rows in set (0.01 sec)

復制表 （拷貝已有的表 和系統命令 cp 的功能一樣 ）

1. //復制tarena庫salary表到 studb庫 表名不變
2. mysql> create table studb.salary select * from tarena.salary;
3. Query OK, 8055 rows affected (2.66 sec)
4. Records: 8055 Duplicates: 0 Warnings: 0
5. //查看表頭，源表的key 不會被復制
6. mysql> desc studb.salary;
7. +-------------+------+------+-----+---------+-------+
8. | Field | Type | Null | Key | Default | Extra |
9. +-------------+------+------+-----+---------+-------+
10. | id | int | NO | | 0 | |
11. | date | date | YES | | NULL | |
12. | employee_id | int | YES | | NULL | |
13. | basic | int | YES | | NULL | |
14. | bonus | int | YES | | NULL | |
15. +-------------+------+------+-----+---------+-------+
16. 5 rows in set (0.00 sec)
17. //查看表行數
18. mysql> select count(*) from studb.salary;
19. +----------+
20. | count(*) |
21. +----------+
22. | 8055 |
23. +----------+
24. 1 row in set (0.00 sec)
25. //僅僅復制表頭
26. mysql> create table studb.salary2 like tarena.salary;
27. Query OK, 0 rows affected (0.95 sec)
28. //查看表頭
29. mysql> desc studb.salary2;
30. +-------------+------+------+-----+---------+----------------+
31. | Field | Type | Null | Key | Default | Extra |
32. +-------------+------+------+-----+---------+----------------+
33. | id | int | NO | PRI | NULL | auto_increment |
34. | date | date | YES | | NULL | |
35. | employee_id | int | YES | MUL | NULL | |
36. | basic | int | YES | | NULL | |
37. | bonus | int | YES | | NULL | |
38. +-------------+------+------+-----+---------+----------------+
39. 5 rows in set (0.00 sec)
40. //查看表行數
41. mysql> select count(*) from studb.salary2;
42. +----------+
43. | count(*) |
44. +----------+
45. | 0 |
46. +----------+
47. 1 row in set (0.00 sec)
48. mysql>

2 案例2：數據類型

2.1 問題

1. 練習字符類型的使用
2. 練習數值類型的使用
3. 練習枚舉類型的使用
4. 練習日期時間類型的使用

2.2 方案

常用數據類型：數值類型、字符類型、日期時間類型、枚舉類型,每種類型都有對應的命令表示、有具體的存儲范圍。

• 比如存儲： 身高、體重、工資、獎金，適合使用數值類型。
• 比如存儲： 姓名、家庭地址、收貨地址，適合使用字符類型。
• 比如存儲： 生日、出生年份、入職時間、下班時間、注冊時間，適合使用日期時間。
• 比如存儲： 愛好、性別、社保醫院，適合使用枚舉類型。

2.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：練習字符類型的使用

命令操作如下所示：

1. //建表
2. mysql> create table studb.t2(name char(3) , address varchar(5) );
3. Query OK, 0 rows affected (0.30 sec)
4. //查看表頭
5. mysql> desc studb.t2;
6. +---------+------------+------+-----+---------+-------+
7. | Field | Type | Null | Key | Default | Extra |
8. +---------+------------+------+-----+---------+-------+
9. | name | char(3) | YES | | NULL | |
10. | address | varchar(5) | YES | | NULL | |
11. +---------+------------+------+-----+---------+-------+
12. 2 rows in set (0.00 sec)
13. //插入記錄
14. mysql> insert into studb.t2 values ("a","a"); //正常
15. Query OK, 1 row affected (0.05 sec)
16. mysql> insert into studb.t2 values ("ab","ab"); //正常
17. Query OK, 1 row affected (0.08 sec)
18. mysql> insert into studb.t2 values ("abc","abc");//正常
19. Query OK, 1 row affected (0.04 sec)
20. mysql> insert into studb.t2 values ("abcd","abcd"); //超出字符個數報錯
21. ERROR 1406 (22001): Data too long for column 'name' at row 1
22. mysql>

mysql8 建表默認支持中文字符集

1. //查看字符集
2. mysql> show create table studb.t2 \G
3. *************************** 1. row ***************************
4. Table: t2
5. Create Table: CREATE TABLE `t2` (
6. `name` char(3) DEFAULT NULL,
7. `address` varchar(5) DEFAULT NULL
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
9. 1 row in set (0.00 sec)
10. 說明 ：
11. ENGINE=InnoDB 定義存儲引擎（存儲引擎課程里講）
12. DEFAULT CHARSET=定義表使用的字符集
13. //插入記錄
14. mysql> insert into studb.t2 values ("張翠山","武當山");
15. Query OK, 1 row affected (0.07 sec)
16. //查看表記錄
17. mysql> SELECT * FROM studb.t2;
18. +-----------+-----------+
19. | name | address |
20. +-----------+-----------+
21. | a | a |
22. | ab | ab |
23. | abc | abc |
24. | 張翠山 | 武當山 |
25. +-----------+-----------+
26. 4 rows in set (0.00 sec)

步驟二：練習數值類型的使用

命令操作如下所示：

1. name 姓名
2. level 游戲級別
3. money 游戲幣
4. //建表
5. mysql> create table studb.t1(name char(10) , level tinyint unsigned , money double );
6. Query OK, 0 rows affected (0.72 sec)
7. //查看表頭
8. mysql> desc studb.t1;
9. +-------+------------------+------+-----+---------+-------+
10. | Field | Type | Null | Key | Default | Extra |
11. +-------+------------------+------+-----+---------+-------+
12. | name | char(10) | YES | | NULL | |
13. | level | tinyint unsigned | YES | | NULL | |
14. | money | double | YES | | NULL | |
15. +-------+------------------+------+-----+---------+-------+
16. 3 rows in set (0.00 sec)
17. //插入數據
18. mysql> insert into studb.t1 values("法師",80,88);
19. Query OK, 1 row affected (0.04 sec)
20. //超出范圍報錯
21. mysql> insert into studb.t1 values("戰士",301,1.292);
22. ERROR 1264 (22003): Out of range value for column 'level' at row 1
23. mysql>
24. mysql> insert into studb.t1 values("獵人",255,1.292);
25. Query OK, 1 row affected (0.06 sec)
26. //整數類型 不存儲小數位
27. mysql> insert into studb.t1 values ("英雄",1.292,6.78);
28. Query OK, 1 row affected (0.07 sec)
29. //查看表記錄
30. mysql> select * from studb.t1 ;
31. +--------+-------+-------+
32. | name | level | money |
33. +--------+-------+-------+
34. | 法師 | 80 | 88 |
35. | 獵人 | 255 | 1.292 |
36. | 英雄 | 1 | 6.78 |
37. +--------+-------+-------+
38. 3 rows in set (0.00 sec)

步驟三：練習枚舉類型的使用

1. //建表
2. mysql> create table studb.t8(
3. -> 姓名 char(10),
4. -> 性別 enum("男","女","保密"),
5. -> 愛好 set("帥哥","金錢","吃","睡")
6. -> );
7. Query OK, 0 rows affected (0.29 sec)
8. //查看表頭
9. mysql> desc studb.t8 ;
10. +--------+------------------------------------+------+-----+---------+-------+
11. | Field | Type | Null | Key | Default | Extra |
12. +--------+------------------------------------+------+-----+---------+-------+
13. | 姓名 | char(10) | YES | | NULL | |
14. | 性別 | enum('男','女','保密') | YES | | NULL | |
15. | 愛好 | set('帥哥','金錢','吃','睡') | YES | | NULL | |
16. +--------+------------------------------------+------+-----+---------+-------+
17. 3 rows in set (0.01 sec)
19. //插入記錄超出范圍報錯
20. mysql> insert into studb.t8 values ("小包總","男人","帥哥,睡,金錢");
21. ERROR 1265 (01000): Data truncated for column '性別' at row 1
22. mysql> insert into studb.t8 values ("小包總","男","美女,睡,金錢");
23. ERROR 1265 (01000): Data truncated for column '愛好' at row 1
24. mysql>
25. //在范圍內插入成功
26. mysql> insert into studb.t8 values ("丫丫","女","帥哥,吃");
27. Query OK, 1 row affected (0.09 sec)
28. mysql> select * from studb.t8;
29. +--------+--------+------------+
30. | 姓名 | 性別 | 愛好 |
31. +--------+--------+------------+
32. | 丫丫 | 女 | 帥哥,吃 |
33. +--------+--------+------------+
34. 1 row in set (0.00 sec)

步驟四：練習日期時間類型的使用

命令操作如下所示：

1. //建表
2. mysql> create table studb.t6(
3. -> 姓名 char(10),
4. -> 生日 date ,
5. -> 出生年份 year ,
6. -> 家庭聚會 datetime ,
7. -> 聚會地點 varchar(15),
8. -> 上班時間 time
9. -> );
10. Query OK, 0 rows affected (0.25 sec)
11. //查看表頭
12. mysql> desc studb.t6 ;
13. +--------------+-------------+------+-----+---------+-------+
14. | Field | Type | Null | Key | Default | Extra |
15. +--------------+-------------+------+-----+---------+-------+
16. | 姓名 | char(10) | YES | | NULL | |
17. | 生日 | date | YES | | NULL | |
18. | 出生年份 | year | YES | | NULL | |
19. | 家庭聚會 | datetime | YES | | NULL | |
20. | 聚會地點 | varchar(15) | YES | | NULL | |
21. | 上班時間 | time | YES | | NULL | |
22. +--------------+-------------+------+-----+---------+-------+
23. 6 rows in set (0.00 sec)
24. //插入表頭
25. mysql> insert into studb.t6
26. -> values ("翠花",20211120,1990,20220101183000,"天壇校區",090000);
27. Query OK, 1 row affected (0.05 sec)
28. //查看表記錄
29. mysql> select * from studb.t6;
30. +--------+------------+--------------+---------------------+--------------+--------------+
31. | 姓名 | 生日 | 出生年份 | 家庭聚會 | 聚會地點 | 上班時間 |
32. +--------+------------+--------------+---------------------+--------------+--------------+
33. | 翠花 | 2021-11-20 | 1990 | 2022-01-01 18:30:00 | 天壇校區 | 09:00:00 |
34. +--------+------------+--------------+---------------------+--------------+--------------+
35. 1 row in set (0.00 sec)

3 案例3：數據批量處理

3.1 問題

1. 修改檢索目錄為/myload。
2. 將/etc/passwd文件導入db1庫的user3表里，并添加行號字段。
3. 將db1庫user3表所有記錄導出, 存到/myload/user.txt文件里。

3.2 方案

在mysql50主機完成練習。

3.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：修改檢索目錄為/myload。

檢查目錄存放導入導出數據時存放數據的文件

1. [root@mysql50 ~]# mysql -uroot -pNSD2023...a
2. mysql> show variables like "%file%"; 查看與文件相關的配置項
3. +---------------------------------------+---------------------------------+
4. | Variable_name | Value |
5. +---------------------------------------+---------------------------------+
6. | character_set_filesystem | binary |
7. | core_file | OFF |
8. | ft_stopword_file | (built-in) |
9. | general_log_file | /var/lib/mysql/mysql50.log |
10. | init_file | |
11. | innodb_buffer_pool_filename | ib_buffer_pool |
12. | innodb_buffer_pool_in_core_file | ON |
13. | innodb_data_file_path | ibdata1:12M:autoextend |
14. | innodb_disable_sort_file_cache | OFF |
15. | innodb_doublewrite_files | 2 |
16. | innodb_file_per_table | ON |
17. | innodb_log_file_size | 50331648 |
18. | innodb_log_files_in_group | 2 |
19. | innodb_open_files | 4000 |
20. | innodb_temp_data_file_path | ibtmp1:12M:autoextend |
21. | keep_files_on_create | OFF |
22. | large_files_support | ON |
23. | local_infile | OFF |
24. | lower_case_file_system | OFF |
25. | myisam_max_sort_file_size | 9223372036853727232 |
26. | open_files_limit | 10000 |
27. | performance_schema_max_file_classes | 80 |
28. | performance_schema_max_file_handles | 32768 |
29. | performance_schema_max_file_instances | -1 |
30. | pid_file | /run/mysqld/mysqld.pid |
31. | relay_log_info_file | relay-log.info |
32. | secure_file_priv | /var/lib/mysql-files/ |
33. | slow_query_log_file | /var/lib/mysql/mysql50-slow.log |
34. +---------------------------------------+---------------------------------+
35. 28 rows in set (0.00 sec)
37. 查看默認檢索目錄
38. mysql> show variables like "secure_file_priv";
39. +------------------+-----------------------+
40. | Variable_name | Value |
41. +------------------+-----------------------+
42. | secure_file_priv | /var/lib/mysql-files/ |
43. +------------------+-----------------------+
44. 1 row in set (0.00 sec)
45. mysql> exit
47. 安裝MySQL服務軟件時自動創建
48. [root@mysql50 ~]# ls -ld /var/lib/mysql-files/
49. drwxr-x--- 2 mysql mysql 6 Sep 22 2021 /var/lib/mysql-files/
50. [root@mysql50 ~]#
51. 修改主配置文件
52. [root@mysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf
53. [mysqld]
54. secure_file_priv=/myload 添加此行
55. :wq
57. 創建目錄并修改所有者為mysql用戶 ,并保證mysql用戶對父目錄有rx
58. [root@mysql50 ~]# mkdir /myload
59. [root@mysql50 ~]# chown mysql /myload
60. 關閉selinux
61. root@mysql50 ~]# setenforce 0
62. setenforce: SELinux is disabled
63. 重啟服務
64. [root@mysql50 ~]# systemctl restart mysqld
65. 管理員員登陸查看目錄
66. [root@mysql50 ~]# mysql -uroot -pNSD2023...a
67. mysql> show variables like "secure_file_priv";
68. +------------------+----------+
69. | Variable_name | Value |
70. +------------------+----------+
71. | secure_file_priv | /myload/ |
72. +------------------+----------+
73. 1 row in set (0.01 sec)

步驟二：將/etc/passwd文件導入db1庫的user3表里。

命令操作如下所示：

1. 建庫
3. [root@mysql50 ~]# mysql -uroot -pNSD2023...a
4. mysql> create database db1;
6. 建表（ 根據導入的文件內容 創建表頭）
7. mysql> create table db1.user3(name varchar(30),password char(1),uid int , gid int , comment varchar(200),homedir varchar(50),shell varchar(30));
8. Query OK, 0 rows affected (0.41 sec)
10. 查看表頭
11. mysql> desc db1.user3;
12. +----------+--------------+------+-----+---------+-------+
13. | Field | Type | Null | Key | Default | Extra |
14. +----------+--------------+------+-----+---------+-------+
15. | name | varchar(30) | YES | | NULL | |
16. | password | char(1) | YES | | NULL | |
17. | uid | int | YES | | NULL | |
18. | gid | int | YES | | NULL | |
19. | comment | varchar(200) | YES | | NULL | |
20. | homedir | varchar(50) | YES | | NULL | |
21. | shell | varchar(30) | YES | | NULL | |
22. +----------+--------------+------+-----+---------+-------+
23. 7 rows in set (0.01 sec)
24. 沒有數據
25. mysql> select * from db1.user3;
26. Empty set (0.01 sec)
27. mysql>
28. 拷貝文件到檢索目錄 system 在MySQL 里執行系統命令
29. mysql> system cp /etc/passwd /myload/
30. mysql> system ls /myload/ 查看文件
31. passwd
32. mysql>
33. 導入數據
34. mysql> load data infile "/myload/passwd" into table db1.user3 fields terminated by ":" lines terminated by "\n" ;
35. Query OK, 23 rows affected (0.06 sec)
36. Records: 23 Deleted: 0 Skipped: 0 Warnings: 0
37. 查看表記錄
38. mysql> select count(*) from db1.user3;
39. +----------+
40. | count(*) |
41. +----------+
42. | 23 |
43. +----------+
44. 1 row in set (0.00 sec)
46. mysql> select * from db1.user3;
47. +------------------+----------+-------+-------+-----------------------------+-----------------+----------------+
48. | name | password | uid | gid | comment | homedir | shell |
49. +------------------+----------+-------+-------+-----------------------------+-----------------+----------------+
50. | root | x | 0 | 0 | root | /root | /bin/bash |
51. | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
52. | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
53. | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
54. | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
55. | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
56. | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
57. | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
58. | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
59. | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
60. | games | x | 12 | 100 | games | /usr/games | /sbin/nologin |
61. | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin |
62. | nobody | x | 65534 | 65534 | Kernel Overflow User | / | /sbin/nologin |
63. | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin |
64. | systemd-coredump | x | 999 | 997 | systemd Core Dumper | / | /sbin/nologin |
65. | systemd-resolve | x | 193 | 193 | systemd Resolver | / | /sbin/nologin |
66. | polkitd | x | 998 | 995 | User for polkitd | / | /sbin/nologin |
67. | unbound | x | 997 | 994 | Unbound DNS resolver | /etc/unbound | /sbin/nologin |
68. | tss | x | 59 | 59 | Account used for TPM access | /dev/null | /sbin/nologin |
69. | chrony | x | 996 | 993 | | /var/lib/chrony | /sbin/nologin |
70. | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
71. | tcpdump | x | 72 | 72 | | / | /sbin/nologin |
72. | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /sbin/nologin |
73. +------------------+----------+-------+-------+-----------------------------+-----------------+----------------+
74. 23 rows in set (0.00 sec)
76. mysql>

步驟三：將db1庫user3表所有記錄導出, 存到/myload/user.txt文件里。

命令操作如下所示：

1. mysql> select * from db1.user3 into outfile "/myload/user.txt" ;
2. Query OK, 23 rows affected (0.00 sec)
4. mysql> system ls /myload/
5. passwd user.txt
6. mysql> system wc -l /myload/user.txt
7. 23 /myload/user.txt
8. mysql>
9. mysql> system vim /myload/user.txt
10. root x 0 0 root /root /bin/bash
11. bin x 1 1 bin /bin /sbin/nologin
12. daemon x 2 2 daemon /sbin /sbin/nologin
13. adm x 3 4 adm /var/adm /sbin/nologin
14. lp x 4 7 lp /var/spool/lpd /sbin/nologin
15. sync x 5 0 sync /sbin /bin/sync
16. shutdown x 6 0 shutdown /sbin /sbin/shutdown
17. halt x 7 0 halt /sbin /sbin/halt
18. mail x 8 12 mail /var/spool/mail /sbin/nologin
19. operator x 11 0 operator /root /sbin/nologin
20. games x 12 100 games /usr/games /sbin/nologin
21. ftp x 14 50 FTP User /var/ftp /sbin/nologin
22. nobody x 65534 65534 Kernel Overflow User / /sbin/nologin
23. dbus x 81 81 System message bus / /sbin/nologin
24. systemd-coredump x 999 997 systemd Core Dumper / /sbin/nologin
25. systemd-resolve x 193 193 systemd Resolver / /sbin/nologin
26. polkitd x 998 995 User for polkitd / /sbin/nologin
27. unbound x 997 994 Unbound DNS resolver /etc/unbound /sbin/nologin
28. tss x 59 59 Account used for TPM access /dev/null /sbin/nologin
29. chrony x 996 993 /var/lib/chrony /sbin/nologin
30. sshd x 74 74 Privilege-separated SSH /var/empty/sshd /sbin/nologin
31. tcpdump x 72 72 / /sbin/nologin
32. mysql x 27 27 MySQL Server /var/lib/mysql /sbin/nologin

4 案例4：表頭基本約束

4.1 問題

1. 表頭不允許賦null值練習
2. 表頭加默認值練習
3. 表頭加唯一索引練習

4.2 方案

約束是一種限制，設置在表頭上，用來控制表頭的賦值，包括以下幾種：

1. NOT NULL ：非空，用于保證該字段的值不能為空。
2. DEFAULT：默認值，用于保證該字段有默認值。
3. UNIQUE：唯一索引，用于保證該字段的值具有唯一性，可以為空。
4. PRIMARY KEY：主鍵，用于保證該字段的值具有唯一性并且非空。
5. FOREIGN KEY：外鍵，用于限制兩個表的關系，用于保證該字段的值必須來自于主表的關聯列的值，在從表添加外鍵約束，用于引用主表中某些的值。

4.3 步驟

實現此案例需要按照如下步驟進行。

步驟一：表頭不允許賦空值練習

1. //建表時給表頭設置默認和不允許賦null值????
2. mysql> create database if not exists db1;
3. Query OK, 1 row affected (0.07 sec)
4. //建表
5. mysql> create table db1.t31(
6. -> name char(10) not null ,
7. -> class char(7) default "nsd",
8. -> likes set("money","game","film","music") not null default "film,music" );
9. Query OK, 0 rows affected (0.43 sec)
10. //查看表頭
11. mysql> desc db1.t31;
12. +-------+------------------------------------+------+-----+------------+-------+
13. | Field | Type | Null | Key | Default | Extra |
14. +-------+------------------------------------+------+-----+------------+-------+
15. | name | char(10) | NO | | NULL | |
16. | class | char(7) | YES | | nsd | |
17. | likes | set('money','game','film','music') | NO | | film,music | |
18. +-------+------------------------------------+------+-----+------------+-------+
19. 3 rows in set (0.01 sec)
20. //驗證默認值和不允許為null
21. mysql> insert into db1.t31 values (null, null , null);
22. ERROR 1048 (23000): Column 'name' cannot be null //表頭name賦null值 報錯
23. //表頭likes賦null值 報錯
24. mysql> insert into db1.t31 values ("bob", null , null);
25. ERROR 1048 (23000): Column 'likes' cannot be null
26. //符合約束不報錯
27. mysql> insert into db1.t31 values ("bob",null,"money,game,film");
28. Query OK, 1 row affected (0.06 sec)
29. //不賦值的表頭使用默認值賦值
30. mysql> insert into db1.t31(name) values("jim");
31. //根據需要自定義表頭的值
32. mysql> insert into db1.t31 values ("lucy","nsd2108","game,film");
33. //查看表記錄
34. mysql> select * from db1.t31;
35. +------+---------+-----------------+
36. | name | class | likes |
37. +------+---------+-----------------+
38. | bob | NULL | money,game,film |
39. | jim | nsd | film,music |
40. | lucy | nsd2108 | game,film |
41. +------+---------+-----------------+
42. 3 rows in set (0.00 sec)

步驟二：表頭加唯一索引練習

唯一索引 （unique）

約束的方式：表頭值唯一 , 但可以賦null 值

1. //建表
2. create ???? table db1.t43 (姓名 char(10) , 護照 char(18) unique );
3. //查看表頭 唯一索引標志UNI
4. mysql> desc db1.t32 ;
5. +--------+----------+------+-----+---------+-------+
6. | Field | Type | Null | Key | Default | Extra |
7. +--------+----------+------+-----+---------+-------+
8. | 姓名 | char(10) | YES | | NULL | |
9. | 護照 | char(18) | YES | UNI | NULL | |
10. +--------+----------+------+-----+---------+-------+
11. 2 rows in set (0.00 sec)
12. //賦null值 可以
13. mysql> insert into db1.t32 values("bob",null);
14. Query OK, 1 row affected (0.07 sec)
15. //表頭值重復不可以
16. mysql> insert into db1.t32 values("tom","666888");
17. Query OK, 1 row affected (0.08 sec)
18. mysql> insert into db1.t32 values("jim","666888");
19. ERROR 1062 (23000): Duplicate entry '666888' for key 't32.護照'
20. //不重復 可以
21. mysql> insert into db1.t32 values("jim","766888");
22. Query OK, 1 row affected (0.05 sec)
23. //查看表記錄
24. mysql> select * from DB1.t43;
25. +------+--------+
26. | 姓名 | 護照 |
27. +------+--------+
28. | bob | NULL |
29. | tom | 666888 |
30. | jim | 766888 |
31. +------+--------+
32. 3 rows in set (0.00 sec)