1. 表操作练习题：
    练习：用户表，用户组表，主机表，业务线表
          关联：用户与用户组  主机与业务线  用户与主机

  1. # 用户表
  2. create table user(
  3.     id int not null unique auto_increment,
  4.     username varchar(20) not null,
  5.     password varchar(50) not null,
  6.     primary key(username,password)
  7. );
  8. insert into user(username,password) values
  9. (\'root\',\'123\'),
  10. (\'egon\',\'456\'),
  11. (\'alex\',\'alex3714\')
  12. ;
  13. mysql> desc user;
  14. +----------+-------------+------+-----+---------+----------------+
  15. | Field    | Type        | Null | Key | Default | Extra          |
  16. +----------+-------------+------+-----+---------+----------------+
  17. | id       | int(11)     | NO   | UNI | NULL    | auto_increment |
  18. | username | varchar(20) | NO   | PRI | NULL    |                |
  19. | password | varchar(50) | NO   | PRI | NULL    |                |
  20. +----------+-------------+------+-----+---------+----------------+
  21. 3 rows in set (0.00 sec)
  23. mysql> select * from user;
  24. +----+----------+----------+
  25. | id | username | password |
  26. +----+----------+----------+
  27. |  1 | root     | 123      |
  28. |  2 | egon     | 456      |
  29. |  3 | alex     | alex3714 |
  30. +----+----------+----------+
  31. 3 rows in set (0.00 sec)
  34. # 用户组表
  35. create table usergroup(
  36.     id int primary key auto_increment,
  37.     groupname varchar(20) not null unique
  38. );
  39. insert into usergroup(groupname) values
  40. (\'IT\'),
  41. (\'Sale\'),
  42. (\'Finance\'),
  43. (\'Boss\')
  44. ;
  45. mysql> desc usergroup;
  46. +-----------+-------------+------+-----+---------+----------------+
  47. | Field     | Type        | Null | Key | Default | Extra          |
  48. +-----------+-------------+------+-----+---------+----------------+
  49. | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
  50. | groupname | varchar(20) | NO   | UNI | NULL    |                |
  51. +-----------+-------------+------+-----+---------+----------------+
  52. 2 rows in set (0.00 sec)
  54. mysql> select * from usergroup;
  55. +----+-----------+
  56. | id | groupname |
  57. +----+-----------+
  58. |  4 | Boss      |
  59. |  3 | Finance   |
  60. |  1 | IT        |
  61. |  2 | Sale      |
  62. +----+-----------+
  63. 4 rows in set (0.01 sec)
  65. # 主机表
  66. create table host(
  67.     id int primary key auto_increment,
  68.     ip char(15) not null unique default \'127.0.0.1\'
  69. );
  70. insert into host(ip) values
  71. (\'172.16.45.2\'),
  72. (\'172.16.31.10\'),
  73. (\'172.16.45.3\'),
  74. (\'172.16.31.11\'),
  75. (\'172.10.45.3\'),
  76. (\'172.10.45.4\'),
  77. (\'172.10.45.5\'),
  78. (\'192.168.1.20\'),
  79. (\'192.168.1.21\'),
  80. (\'192.168.1.22\'),
  81. (\'192.168.2.23\'),
  82. (\'192.168.2.223\'),
  83. (\'192.168.2.24\'),
  84. (\'192.168.3.22\'),
  85. (\'192.168.3.23\'),
  86. (\'192.168.3.24\')
  87. ;
  88. mysql> desc host;
  89. +-------+----------+------+-----+-----------+----------------+
  90. | Field | Type     | Null | Key | Default   | Extra          |
  91. +-------+----------+------+-----+-----------+----------------+
  92. | id    | int(11)  | NO   | PRI | NULL      | auto_increment |
  93. | ip    | char(15) | NO   | UNI | 127.0.0.1 |                |
  94. +-------+----------+------+-----+-----------+----------------+
  95. 2 rows in set (0.00 sec)
  97. mysql> select * from host;
  98. +----+---------------+
  99. | id | ip            |
  100. +----+---------------+
  101. |  5 | 172.10.45.3   |
  102. |  6 | 172.10.45.4   |
  103. |  7 | 172.10.45.5   |
  104. |  2 | 172.16.31.10  |
  105. |  4 | 172.16.31.11  |
  106. |  1 | 172.16.45.2   |
  107. |  3 | 172.16.45.3   |
  108. |  8 | 192.168.1.20  |
  109. |  9 | 192.168.1.21  |
  110. | 10 | 192.168.1.22  |
  111. | 12 | 192.168.2.223 |
  112. | 11 | 192.168.2.23  |
  113. | 13 | 192.168.2.24  |
  114. | 14 | 192.168.3.22  |
  115. | 15 | 192.168.3.23  |
  116. | 16 | 192.168.3.24  |
  117. +----+---------------+
  118. 16 rows in set (0.01 sec)
  120. # 业务线表
  121. create table business(
  122.     id int primary key auto_increment,
  123.     business varchar(20) not null unique
  124. );
  125. insert into business(business) values
  126. (\'轻松贷\'),
  127. (\'随便花\'),
  128. (\'大富翁\'),
  129. (\'穷一生\')
  130. ;
  131. mysql> desc business;
  132. +----------+-------------+------+-----+---------+----------------+
  133. | Field    | Type        | Null | Key | Default | Extra          |
  134. +----------+-------------+------+-----+---------+----------------+
  135. | id       | int(11)     | NO   | PRI | NULL    | auto_increment |
  136. | business | varchar(20) | NO   | UNI | NULL    |                |
  137. +----------+-------------+------+-----+---------+----------------+
  138. 2 rows in set (0.00 sec)
  140. mysql> select * from business;
  141. +----+-----------+
  142. | id | business  |
  143. +----+-----------+
  144. |  3 | 大富翁    |
  145. |  4 | 穷一生    |
  146. |  1 | 轻松贷    |
  147. |  2 | 随便花    |
  148. +----+-----------+
  149. 4 rows in set (0.00 sec)
  152. # 建关系 user usergroup
  153. create table user2usergroup(
  154.     id int not null unique auto_increment ,
  155.     user_id int not null,
  156.     group_id int not null,
  157.     primary key(user_id,group_id),
  158.     foreign key(user_id) references user(id)
  159.     on delete cascade
  160.     on update cascade,
  161.     foreign key(group_id) references usergroup(id)
  162.     on delete cascade
  163.     on update cascade
  164. );
  165. insert into user2usergroup(user_id,group_id) values
  166. (1,1),
  167. (1,2),
  168. (1,3),
  169. (1,4),
  170. (2,3),
  171. (2,4),
  172. (3,4)
  173. ;
  174. mysql> desc user2usergroup;
  175. +----------+---------+------+-----+---------+----------------+
  176. | Field    | Type    | Null | Key | Default | Extra          |
  177. +----------+---------+------+-----+---------+----------------+
  178. | id       | int(11) | NO   | UNI | NULL    | auto_increment |
  179. | user_id  | int(11) | NO   | PRI | NULL    |                |
  180. | group_id | int(11) | NO   | PRI | NULL    |                |
  181. +----------+---------+------+-----+---------+----------------+
  182. 3 rows in set (0.00 sec)
  184. mysql> select * from user2usergroup;
  185. +----+---------+----------+
  186. | id | user_id | group_id |
  187. +----+---------+----------+
  188. |  1 |       1 |        1 |
  189. |  2 |       1 |        2 |
  190. |  3 |       1 |        3 |
  191. |  4 |       1 |        4 |
  192. |  5 |       2 |        3 |
  193. |  6 |       2 |        4 |
  194. |  7 |       3 |        4 |
  195. +----+---------+----------+
  196. 7 rows in set (0.00 sec)
  198. # 建关系 host business
  199. create table host2business(
  200.     id int not null unique auto_increment,
  201.     host_id int not null,
  202.     business_id int not null,
  203.     primary key(host_id,business_id),
  204.     foreign key(host_id) references host(id)
  205.     on delete cascade
  206.     on update cascade,
  207.     foreign key(business_id) references business(id)
  208.     on delete cascade
  209.     on update cascade
  210. );
  211. insert into host2business(host_id,business_id) values
  212. (1,1),
  213. (1,2),
  214. (1,3),
  215. (2,2),
  216. (2,3),
  217. (3,4)
  218. ;
  219. mysql> desc host2business;
  220. +-------------+---------+------+-----+---------+----------------+
  221. | Field       | Type    | Null | Key | Default | Extra          |
  222. +-------------+---------+------+-----+---------+----------------+
  223. | id          | int(11) | NO   | UNI | NULL    | auto_increment |
  224. | host_id     | int(11) | NO   | PRI | NULL    |                |
  225. | business_id | int(11) | NO   | PRI | NULL    |                |
  226. +-------------+---------+------+-----+---------+----------------+
  227. 3 rows in set (0.00 sec)
  229. mysql> select * from host2business;
  230. +----+---------+-------------+
  231. | id | host_id | business_id |
  232. +----+---------+-------------+
  233. |  1 |       1 |           1 |
  234. |  2 |       1 |           2 |
  235. |  3 |       1 |           3 |
  236. |  4 |       2 |           2 |
  237. |  5 |       2 |           3 |
  238. |  6 |       3 |           4 |
  239. +----+---------+-------------+
  240. 6 rows in set (0.00 sec)
  243. # 建关系 user host
  244. create table user2host(
  245.     id int not null unique auto_increment,
  246.     user_id int not null,
  247.     host_id int not null,
  248.     primary key(user_id,host_id),
  249.     foreign key(user_id) references user(id)
  250.     on delete cascade
  251.     on update cascade,
  252.     foreign key(host_id) references host(id)
  253.     on delete cascade
  254.     on update cascade
  255. );
  256. insert into user2host(user_id,host_id) values
  257. (1,1),
  258. (1,2),
  259. (1,3),
  260. (1,4),
  261. (1,5),
  262. (1,6),
  263. (1,7),
  264. (1,8),
  265. (1,9),
  266. (1,10),
  267. (1,11),
  268. (1,12),
  269. (1,13),
  270. (1,14),
  271. (1,15),
  272. (1,16),
  273. (2,2),
  274. (2,3),
  275. (2,4),
  276. (2,5),
  277. (3,10),
  278. (3,11),
  279. (3,12)
  280. ;
  281. mysql> desc user2host;
  282. +---------+---------+------+-----+---------+----------------+
  283. | Field   | Type    | Null | Key | Default | Extra          |
  284. +---------+---------+------+-----+---------+----------------+
  285. | id      | int(11) | NO   | UNI | NULL    | auto_increment |
  286. | user_id | int(11) | NO   | PRI | NULL    |                |
  287. | host_id | int(11) | NO   | PRI | NULL    |                |
  288. +---------+---------+------+-----+---------+----------------+
  289. 3 rows in set (0.00 sec)
  291. mysql> select * from user2host;
  292. +----+---------+---------+
  293. | id | user_id | host_id |
  294. +----+---------+---------+
  295. |  1 |       1 |       1 |
  296. |  2 |       1 |       2 |
  297. |  3 |       1 |       3 |
  298. |  4 |       1 |       4 |
  299. |  5 |       1 |       5 |
  300. |  6 |       1 |       6 |
  301. |  7 |       1 |       7 |
  302. |  8 |       1 |       8 |
  303. |  9 |       1 |       9 |
  304. | 10 |       1 |      10 |
  305. | 11 |       1 |      11 |
  306. | 12 |       1 |      12 |
  307. | 13 |       1 |      13 |
  308. | 14 |       1 |      14 |
  309. | 15 |       1 |      15 |
  310. | 16 |       1 |      16 |
  311. | 17 |       2 |       2 |
  312. | 18 |       2 |       3 |
  313. | 19 |       2 |       4 |
  314. | 20 |       2 |       5 |
  315. | 21 |       3 |      10 |
  316. | 22 |       3 |      11 |
  317. | 23 |       3 |      12 |
  318. +----+---------+---------+
  319. 23 rows in set (0.00 sec)

 




版权声明：本文为alice-bj原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/alice-bj/p/8830569.html