1. P13
    2. CREATE TABLE `tbl_emp` (
    3. `id` int(11) NOT NULL AUTO_INCREMENT,
    4. `name` varchar(20) DEFAULT NULL,
    5. `deptId` int(11) DEFAULT NULL,
    6. PRIMARY KEY (`id`) ,
    7. KEY `fk_dept_id`(`deptId`)
    8. )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
    10. CREATE TABLE `tbl_dept` (
    11. `id` int(11) NOT NULL AUTO_INCREMENT,
    12. `deptName` varchar(30) DEFAULT NULL,
    13. `locAdd` varchar(40) DEFAULT NULL,
    14. PRIMARY KEY (`id`)
    15. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
    17. insert into tbl_dept(deptName,locAdd) values('RD',11);
    18. insert into tbl_dept(deptName,locAdd) values('HR',12);
    19. insert into tbl_dept(deptName,locAdd) values('MK',13);
    20. insert into tbl_dept(deptName,locAdd) values('MIS',14);
    21. insert into tbl_dept(deptName,locAdd) values('FD',15);
    23. insert into tbl_emp(NAME,deptId) values('z3',1);
    24. insert into tbl_emp(NAME,deptId) values('z4',1);
    25. insert into tbl_emp(NAME,deptId) values('z5',1);
    26. insert into tbl_emp(NAME,deptId) values('w5',2);
    27. insert into tbl_emp(NAME,deptId) values('w6',2);
    28. insert into tbl_emp(NAME,deptId) values('s7',3);
    29. insert into tbl_emp(NAME,deptId) values('s8',4);
    30. insert into tbl_emp(NAME,deptId) values('s9',51);
    33. P31
    34. CREATE TABLE IF NOT EXISTS `article`(
    35. `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    36. `author_id` INT (10) UNSIGNED NOT NULL,
    37. `category_id` INT(10) UNSIGNED NOT NULL , 
    38. `views` INT(10) UNSIGNED NOT NULL , 
    39. `comments` INT(10) UNSIGNED NOT NULL,
    40. `title` VARBINARY(255) NOT NULL,
    41. `content` TEXT NOT NULL
    42. );
    44. insert into `article`(author_id,category_id,views,comments,title,content) values
    45. (1,1,1,1,'1','1'),
    46. (2,2,2,2,'2','2'),
    47. (1,1,3,3,'3','3');
    51. P32
    52. CREATE TABLE IF NOT EXISTS `class`(
    53. `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    54. `card` INT (10) UNSIGNED NOT NULL
    55. );
    56. CREATE TABLE IF NOT EXISTS `book`(
    57. `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    58. `card` INT (10) UNSIGNED NOT NULL
    59. );
    61. insert into class(card) values(floor(1+(rand()*20)));
    62. insert into class(card) values(floor(1+(rand()*20)));
    63. insert into class(card) values(floor(1+(rand()*20)));
    64. insert into class(card) values(floor(1+(rand()*20)));
    65. insert into class(card) values(floor(1+(rand()*20)));
    66. insert into class(card) values(floor(1+(rand()*20)));
    67. insert into class(card) values(floor(1+(rand()*20)));
    68. insert into class(card) values(floor(1+(rand()*20)));
    69. insert into class(card) values(floor(1+(rand()*20)));
    70. insert into class(card) values(floor(1+(rand()*20)));
    71. insert into class(card) values(floor(1+(rand()*20)));
    72. insert into class(card) values(floor(1+(rand()*20)));
    73. insert into class(card) values(floor(1+(rand()*20)));
    74. insert into class(card) values(floor(1+(rand()*20)));
    75. insert into class(card) values(floor(1+(rand()*20)));
    76. insert into class(card) values(floor(1+(rand()*20)));
    77. insert into class(card) values(floor(1+(rand()*20)));
    78. insert into class(card) values(floor(1+(rand()*20)));
    79. insert into class(card) values(floor(1+(rand()*20)));
    80. insert into class(card) values(floor(1+(rand()*20)));
    82. insert into book(card) values(floor(1+(rand()*20)));
    83. insert into book(card) values(floor(1+(rand()*20)));
    84. insert into book(card) values(floor(1+(rand()*20)));
    85. insert into book(card) values(floor(1+(rand()*20)));
    86. insert into book(card) values(floor(1+(rand()*20)));
    87. insert into book(card) values(floor(1+(rand()*20)));
    88. insert into book(card) values(floor(1+(rand()*20)));
    89. insert into book(card) values(floor(1+(rand()*20)));
    90. insert into book(card) values(floor(1+(rand()*20)));
    91. insert into book(card) values(floor(1+(rand()*20)));
    92. insert into book(card) values(floor(1+(rand()*20)));
    93. insert into book(card) values(floor(1+(rand()*20)));
    94. insert into book(card) values(floor(1+(rand()*20)));
    95. insert into book(card) values(floor(1+(rand()*20)));
    96. insert into book(card) values(floor(1+(rand()*20)));
    97. insert into book(card) values(floor(1+(rand()*20)));
    98. insert into book(card) values(floor(1+(rand()*20)));
    99. insert into book(card) values(floor(1+(rand()*20)));
    100. insert into book(card) values(floor(1+(rand()*20)));
    101. insert into book(card) values(floor(1+(rand()*20)));
    106. P33
    107. CREATE TABLE IF NOT EXISTS `phone`(
    108. `phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    109. `card` INT (10) UNSIGNED NOT NULL
    110. )ENGINE = INNODB;
    112. insert into phone(card) values(floor(1+(rand()*20)));
    113. insert into phone(card) values(floor(1+(rand()*20)));
    114. insert into phone(card) values(floor(1+(rand()*20)));
    115. insert into phone(card) values(floor(1+(rand()*20)));
    116. insert into phone(card) values(floor(1+(rand()*20)));
    117. insert into phone(card) values(floor(1+(rand()*20)));
    118. insert into phone(card) values(floor(1+(rand()*20)));
    119. insert into phone(card) values(floor(1+(rand()*20)));
    120. insert into phone(card) values(floor(1+(rand()*20)));
    121. insert into phone(card) values(floor(1+(rand()*20)));
    122. insert into phone(card) values(floor(1+(rand()*20)));
    123. insert into phone(card) values(floor(1+(rand()*20)));
    124. insert into phone(card) values(floor(1+(rand()*20)));
    125. insert into phone(card) values(floor(1+(rand()*20)));
    126. insert into phone(card) values(floor(1+(rand()*20)));
    127. insert into phone(card) values(floor(1+(rand()*20)));
    128. insert into phone(card) values(floor(1+(rand()*20)));
    129. insert into phone(card) values(floor(1+(rand()*20)));
    130. insert into phone(card) values(floor(1+(rand()*20)));
    131. insert into phone(card) values(floor(1+(rand()*20)));
    136. P34
    137. CREATE TABLE staffs(
    138. id INT PRIMARY KEY AUTO_INCREMENT,
    139. `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
    140. `age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
    141. `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
    142. `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
    143. )CHARSET utf8 COMMENT'员工记录表';
    145. insert into staffs(NAME,age,pos,add_time) values('z3',22,'manager',NOW());
    146. insert into staffs(NAME,age,pos,add_time) values('July',23,'dev',NOW());
    147. insert into staffs(NAME,age,pos,add_time) values('2000',23,'dev',NOW());
    151. P40
    152. CREATE TABLE tbl_user(
    153. `id` INT(11) NOT NULL AUTO_INCREMENT,
    154. `name` VARCHAR(20) DEFAULT NULL,
    155. `age`INT(11) DEFAULT NULL,
    156. `email` VARCHAR(20) DEFAULT NULL,
    157. PRIMARY KEY(`id`)
    158. )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    160. insert into tbl_user(NAME,age,email) values('1aa1',21,'b@163.com');
    161. insert into tbl_user(NAME,age,email) values('2aa2',222,'a@163.com');
    162. insert into tbl_user(NAME,age,email) values('3aa3',265,'c@163.com');
    163. insert into tbl_user(NAME,age,email) values('4aa4',21,'d@163.com');
    168. P44
    169. create table test03(
    170. id int primary key not null auto_increment,
    171. c1 char(10),
    172. c2 char(10),
    173. c3 char(10),
    174. c4 char(10),
    175. c5 char(10)
    176. );
    178. insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
    179. insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
    180. insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
    181. insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
    182. insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
    188. P48
    189. create table tblA(
    190. age int,
    191. birth timestamp not null
    192. );
    194. insert into tblA(age,birth) values(22,now());
    195. insert into tblA(age,birth) values(23,now());
    196. insert into tblA(age,birth) values(24,now());
    204. P50
    205. create table dept(
    206. id int unsigned primary key auto_increment,
    207. deptno mediumint unsigned not null default 0,
    208. dname varchar(20) not null default "",
    209. loc varchar(13) not null default ""
    210. )engine=innodb default charset=GBK;
    212. CREATE TABLE emp(
    213. id int unsigned primary key auto_increment,
    214. empno mediumint unsigned not null default 0,
    215. ename varchar(20) not null default "",
    216. job varchar(9) not null default "",
    217. mgr mediumint unsigned not null default 0,
    218. hiredate date not null,
    219. sal decimal(7,2) not null,
    220. comm decimal(7,2) not null,
    221. deptno mediumint unsigned not null default 0
    222. )ENGINE=INNODB DEFAULT CHARSET=GBK;
    224. //函数
    225. delimiter $$
    226. create function ran_string(n int) returns varchar(255)
    227. begin
    228. declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    229. declare return_str varchar(255) default '';
    230. declare i int default 0;
    231. while i < n do
    232. set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    233. set i=i+1;
    234. end while;
    235. return return_str;
    236. end $$
    237. //函数
    238. delimiter $$
    239. create function rand_num() returns int(5)
    240. begin
    241. declare i int default 0;
    242. set i=floor(100+rand()*10);
    243. return i;
    244. end $$
    245. //存储过程
    246. delimiter $$ 
    247. create procedure insert_emp(in start int(10),in max_num int(10))
    248. begin
    249. declare i int default 0;
    250. set autocommit = 0;
    251. repeat
    252. set i = i+1;
    253. insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),ran_string(6),'salesman',0001,curdate(),2000,400,rand_num());
    254. until i=max_num
    255. end repeat;
    256. commit;
    257. end $$
    258. //存储过程
    259. delimiter $$ 
    260. create procedure insert_dept(in start int(10),in max_num int(10))
    261. begin
    262. declare i int default 0;
    263. set autocommit = 0;
    264. repeat
    265. set i = i+1;
    266. insert into dept(deptno,dname,loc) values((start+i),ran_string(10),ran_string(8));
    267. until i=max_num
    268. end repeat;
    269. commit;
    270. end $$
    276. P54
    277. create table mylock (
    278. id int not null primary key auto_increment,
    279. name varchar(20) default ''
    280. ) engine myisam;
    282. insert into mylock(name) values('a');
    283. insert into mylock(name) values('b');
    284. insert into mylock(name) values('c');
    285. insert into mylock(name) values('d');
    286. insert into mylock(name) values('e');
    292. P57
    293. CREATE TABLE test_innodb_lock (
    294. a INT(11),
    295. b VARCHAR(16)
    296. )ENGINE=INNODB;